You are here:: Sử dụng SQL kết hợp với công thức Excel - Hàm DATA trong Add-in A-Tools
 
 

Sử dụng SQL kết hợp với công thức Excel - Hàm DATA trong Add-in A-Tools

Hôm nay Bluesofts giới thiệu với các bạn cách để sử dụng hàm DATA trong Add-in A-Tools để tạo số thứ tự tự động và kết hợp công SQL và công thức Excel để tính lũy kế.
 
Video hướng dẫn:
 
Ví dụ:
Cho bảng dữ liệu KHO, lấy ra công thức để đánh số thứ tự và tính lũy kế cộng dồn.
Bảng KHO có dạng như sau:
 
Bước 1:  Đặt tên bảng KHO.
 
- Bôi đen vùng dữ liệu.
- Chọn "Formulas", sau đó chọn "Define Name" (hoặc thao tác nhanh bằng cách nhấn tổ hợp phím Ctrl + F3 và chọn New để đặt tên bảng mới)
 
 
Bước 2: Sử dụng công thức BS_SQL hoặc công cụ SQL Builder lọc dữ liệu
 
 
- Thực hiện tuần tự theo các bước hướng dẫn sau:
+ (1) Vào A-Tools. 
+ (2) Chọn SQL Builder.
+ (3) Kéo bảng KHO vào màn hình chính, tích chọn tên cột muốn lấy ra.
+ (4) Tích chọn hoặc bỏ tích chọn đối với tên cột muốn hoặc không muốn hiển thị.
tại cột "Aggreagate", ta tích chọn hàm SUM để tiến hành tổng hợp cột THANH_TIEN
+ (5) Đồng thời với thao tác này, ta phải tích chọn "For groups" tại cột "Criteria for" để làm cơ sở tổng hợp.
+ (6) Chọn tiếp tục
- Tích chọn "Hàm "BS_SQL" ", tích chọn "Thực hiện" để hiển thị kết quả.
 
- Hoặc các bạn cũng có thể tự gõ công thức BS_SQL như sau:
- Kết quả thực hiện các bước trên:
 
 
Bước 3: Tạo cột số thứ tự bằng hàm DATA
- Cấu trúc và cách sử dụng hàm DATA
Hàm DATA dùng trong khai báo SQL _SELECT:
Cấu trúc hàm: 
            data(Row,Col
Trả về giá trị tại dòng (Row) và cột (Col) trong mảng giá trị.
 
Row và Col là số nguyên dương (>=0) có thể nhận các biến hệ thống dưới đây:
 
PrevRow: Trả về dòng liền trước
Row: Trả về dòng đang hoạt động
PrevCol: Trả về cột liền trước
Col: Trả về cột đang hoạt động
 
Lưu ý: Thứ tự dòng và cột trong mảng bắt đầu từ vị trí 0
 
Ví dụ
Slg luỹ kế = Slg liền trước của cột + Giá trị mới tại cột Slg ( thứ tự cột là 2)
 
data(PrevRow,Col)+data(Row,2) AS [Luỹ kế]
- Sử dụng hàm DATA tính cột số thứ tự tự động
Tại ô bất kỳ tại vùng công thức chúng ta vừa tạo với hàm BS_SQL, chúng ta gõ thêm hàm DATA() để tạo số thứ tự tự động
 
Công thức được thêm vào sau câu lệnh SELECT như sau:

=BS_SQL("SELECT DATA(ROW-1,0)+1,KHO.MA_VLSPHH,VLOOKUP(DATA(ROW,1),DMVLSPHH,2,0) AS [TEN],Sum(KHO.THANH_TIEN)
FROM KHO
GROUP BY KHO.MA_VLSPHH","INSERT=YES;")
 
- Sau khi thay thêm hàm DATA(), ta nhấn tổ hợp phím Ctrl+Shift+ Enter để chạy công thức. 
 
Bước 4: Sử dụng hàm VLOOKUP kết hợp với hàm DATA của Add-in A-Tools
 
- Tại ô bất kỳ tại vùng công thức chúng ta vừa tạo với hàm BS_SQL, chúng ta gõ thêm hàm VLOOKUP của Excel kết hợp với hàm DATA() của SQL để tạo hàm tìm kiếm trong A-Tools như sau:
 
 
Công thức được thêm vào sau câu lệnh SELECT như sau: 
=BS_SQL("SELECT DATA(ROW-1,0)+1,KHO.MA_VLSPHH,VLOOKUP(DATA(ROW,1),DMVLSPHH,2,0) AS [TEN],Sum(KHO.THANH_TIEN)
FROM KHO
GROUP BY KHO.MA_VLSPHH","INSERT=YES;")
 
Bước 5: Sử dụng hàm DATA tính công thức lũy kế cộng dồn.
- Tại ô bất kỳ tại vùng công thức chúng ta vừa tạo với hàm BS_SQL, chúng ta gõ thêm hàm DATA() để tạo công thức tính cộng dồn:
 
Công thức được thêm vào sau câu lệnh SELECT như sau: 
=BS_SQL("SELECT DATA(ROW-1,0)+1 as [STT],KHO.MA_VLSPHH,VLOOKUP(DATA(ROW,1),DMVLSPHH,2,0) AS [TEN],SUM(THANH_TIEN) AS [THANH_TIEN],DATA(ROW-1,4)+DATA(ROW,3) AS [LUY_KE]
FROM KHO 
GROUP BY KHO.MA_VLSPHH","INSERT=YES;")
Kết quả sau khi chúng ta hoàn thành như sau:
 
Các bạn download Add-in A-Tools Free tại đây