Bài viết này tôi hướng dẫn các bạn dùng hàm BS_DSUM trong Add-in A-Tools. Đây là hàm tính tổng với nhiều điều kiện, điều kiện có thể ở nhiều bảng khác nhau. Dữ liệu nguồn là Excel hay các CSDL bên ngoài.

Cấu trúc hàm

BS_DSUM(expr, table_source, [search_condition], [options])

(Các hàm BS_DCOUNT, BS_DMIN, BS_DMAX, BS_DAVG, BS_DVAR, BS_DSTDEV, BS_DFUNC có cấu trúc và cách dùng tương tự BS_DSUM)

Các tham số
Các tham số trong [ ] có thể bỏ qua.

- expr:
Là chuỗi mô tả tên một hoặc nhiều cột (cách nhau dáu phảy (,) ) cần thống kê, nó có thể là biểu thức tính. Ví dụ: "[AMOUNT]" hoặc "[AMOUNT]*0.1", AMOUNT - là tên cột trong table_source. Cột dữ liệu bắt buộc phải kiểu số - Number.

- table_source:
Là chuỗi chỉ ra tên bảng dữ liệu nguồn. Ví dụ "[tblSales]", "[Sheet Name$]"
Bạn có thể kết hợp các bảng dữ liệu với nhau theo phương thức JOIN. Ví dụ:
"KHO k INNER JOIN DMKH.h ON k.MA_KH=m.MA_KH". Khai báo tham số này giống sau từ khoa FROM trong SQL.
(Xem thêm bài viết Hướng dẫn tạo dữ liệu nguồn cho SQL trong Excel)

- search_condition:
Là chuỗi, có thể bỏ qua, là câu lệnh điều kiện để tính toán, cách tạo câu điều kiện giống tạo điều kiện cho từ khóa WHERE trong lệnh SQL. Ví dụ "item_code='HH001' ".

- options:
Là chuỗi, có thể bỏ qua, cho phép khai báo các thuộc tính của hàm. Bạn có thể dùng các khai báo:

"SVR=YES;" - để báo A-Tools lấy dữ liệu ở máy tính khác;

"HAVING = search_condition;" - là điều kiện có sử dụng các hàm thống kê như SUM, COUNT, AVG, MIN, MAX. ví dụ "HAVING=SUM(slg) > 10;". Giá trị khai báo này giống sau từ khóa HAVING trong câu lệnh SQL.

"GROUPBY = group_by_expression;" - Chỉ ra danh sách các cột cần nhóm. Dùng khi có các hàm thống kê và cần nhóm dữ liệu. Ví dụ
"GROUPBY=MONTH(NGAY_CT), MA_KH;".
Giá trị khai báo này giống sau từ khóa GROUP BY trong câu lệnh SQL.

"ORDERBY = order_expression;" - Chỉ ra danh sách các cột cần sắp xếp, các cột ngăn cách nhau bởi dấu phảy (,). Ví dụ:
"ORDERBY=AMMOUNT DESC;". 
Giá trị khai báo này giống sau từ khóa ORDER BY trong câu lệnh SQL.

"DBKEY=dbkeyCode;" - Khai báo mã liên kết - DBKEY tới cơ sở dữ liệu ngoài. Ví dụ:
"DBKEY=MDB;"

Xem thêm hướng dẫn về Cách tạo DBKEY kết nối CSDL bên ngoài.

(*) Lưu ý: 
- Khi bạn phải dùng nhiều công thức có các hàm BS_DSUM, BS_DCOUNT, BS_DLOOKUP,.... BS_D* mà dữ liệu cùng trong một bảng hay chung nguồn thì KHÔNG NÊN dùng các hàm này mà nên THAY THẾ bằng hàm BS_SQL với cách nhóm dữ liệu với từ khóa GROUP BY, sau đó dùng các hàm Excel để nhặt dữ liệu từ bảng kết quả trả về bởi BS_SQL sẽ làm tốc độ file Excel chạy nhanh hơn.

- Với hàm BS_DFUNC có tham số đầu tiên là func_name kiểu chuỗi, là các tên hàm để thống kê như: "SUM", "AVG", "COUNT", "MIN", "MAX", "VAR", "STDEV

Ví dụ

Giả sử có bảng dữ liệu nguồn được đặt tên là KHO (Define Name với Refers To ='Du lieu KHO'!$A$3:$K$68)
 

Bảng dữ liệu thứ hai là DMVLSPHH được Define Name bao vùng ='DM VLSPHH'!$A$3:$D$15



- Ví dụ 1: Tính tổng số lượng nhập kho mà mã hàng là HH001
+ Phân tích: Trong sổ KHO có thông tin về mã hàng và số lượng nhập (LOAI_PHIEU = 'N')
Chỉ cần tìm trên một sổ KHO vì tại đó có các thông tin để tìm và tính tổng

+ Công thức là:
=BS_DSUM("SLG","KHO","LOAI_PHIEU = 'N' AND MA_VLSPHH = 'HH001'")

+ Kết quả là: 16

- Ví dụ 2: Tính tổng số lượng nhập kho mà 2 ký tự đầu của mã hàng là HH
+ Phân tích: Trong sổ KHO có thông tin về mã hàng và số lượng nhập (LOAI_PHIEU = 'N')
Chỉ cần tìm trên một sổ KHO vì tại đó có các thông tin để tìm và tính tổng

+ Công thức là:
=BS_DSUM("SLG","KHO",
LOAI_PHIEU = 'N' AND MA_VLSPHH LIKE 'HH%')

+ Kết quả là: 87

- Ví dụ 3: Tính tổng (thông tin từ 2 sổ) số lượng nhập kho mà LOẠI hàng có mã là VL (vật liệu)
+ Phân tích: Trong sổ KHO có thông tin về mã hàng và số lượng nhập (LOAI_PHIEU = 'N'). Biết trong DMVLSPHH có thông tin về loại (LOAI).
Cần kết nối 2 sổ KHO và DMVLSPHH vì tại đó có các thông tin để tìm và tính tổng
Đặt Alias cho các bảng: KHO là k và DMVLSPHH là h

+ Công thức là:
=BS_DSUM("k.SLG",
"KHO k INNER JOIN DMVLSPHH h ON k.MA_VLSPHH = h.MA_VLSPHH",
"k.LOAI_PHIEU = 'N' AND h.LOAI='VL'")

+ Kết quả là: 15

- Ví dụ 4: Tính tổng (thông tin từ CSDL Access) số tiền trong sổ NKC mà cột NOTK có 3 ký tự đầu là 131 (NOTK -> Tài khoản ghi Nợ)
+ Phân tích:
Bảng (table) NKC có trong file Access "Examble.mdb" đã được kết nối bằng mã DBKEY là MDB
Trong bảng NKC có thông tin về mã tài khoản Nợ (NOTK) và số tiền (THANH_TIEN)
Chỉ cần tìm trên một sổ NKC vì tại đó có các thông tin để tìm và tính tổng

+ Công thức là:
=BS_DSUM("THANH_TIEN",
"NKC",
"NOTK LIKE '131%'",
"DBKEY=MDB")

+ Kết quả là:  148,210,000.00

(Các ví dụ về hàm Database hãy mở file trong bộ cài đặt để xem "C:\A-Tools\Help & Demos\Bai 3 Hàm CSDL (Database Functions).xls")

Đọc thêm về các bài học cơ bản về SQL cho Tạo báo cáo động trong Excel và Add-in A-Tools.

Download Add-in A-Tools để thực hành
Tác giả Nguyễn Duy Tuân