Bài viết này tôi hướng dẫn các bạn dùng hàm BS_DLOOKUP trong Add-in A-Tools. Đây là hàm tìm kiếm nhiều điều kiện và trả về một hay nhiều cột các dòng tìm được. Dữ liệu nguồn là Excel hay các CSDL bên ngoài.

Cấu trúc hàm

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

(Hàm BS_TABLE dùng tương tự hàm BS_DLOOKUP. Tham số expr và select_list dùng như nhau.)

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

- expr:
Là chuỗi mô tả tên một cột hoặc nhiều cột (ngăn cách nhau dấu phảy (,) ) cần lấy thông tin, 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. Trong hàm BS_TABLE tên tham số này tên là là select_list

- 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 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.
 
- Các hàm BS_DSUM, BS_DCOUNT, BS_DMIN, BS_DMAX, BS_DAVG, BS_DSTDEV, BS_DFUNC cũng có cấu trúc và cách dùng tương tự BS_DLOOKUP.

Ví dụ

Các ví dụ dưới đây dùng tương tự cho hàm BS_TABLE.

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ìm mã hàng mà có số lượng nhập kho là 10
+ Phân tích: Trong sổ KHO có thông tin về mã hàng và số lượng nhập (LOAI_PHIEU = 'N')

+ Công thức là:
=BS_DLOOKUP("TOP 1 MA_VLSPHH","KHO","LOAI_PHIEU = 'N' AND SLG = 10")

+ Kết quả là "BM"
Nếu xóa bỏ TOP 1 thì công thức trên trả về tất cả các mặt hàng thỏa mãn điều kiện.

- Ví dụ 2: Tìm tên hàng mà có số lượng nhập kho là 10
+ Phân tích: Trong bảng KHO có thông tin về mã hàng (MA_VLSPHH) và số lượng nhập (LOAI_PHIEU = 'N'). Tên hàng thì trong bảng DMVLSPHH. Khi thông tin ở hai bảng thì ta cần JOIN lại theo khóa MA_VLSPHH (cột này là thông tin mã hàng ở cả hai bảng.

+ Công thức là:
=BS_DLOOKUP("TOP 1 h.TEN",
"KHO k INNER JOIN DMVLSPHH h ON k.MA_VLSPHH = h.MA_VLSPHH" ,
"k.LOAI_PHIEU = 'N' AND k.SLG = 10")

(Công thức trên tôi dùng phương pháp đặt alias cho tên bảng: KHO là k, DMVLSPHH là h vì vậy câu lệnh ngắn gọn lại. Bạn nên áp dụng phương pháp này với các câu lệnh SQL)

+ Kết quả là: "Bò mông"
Nếu xóa bỏ TOP 1 thì công thức trên trả về tất cả các mặt hàng thỏa mãn điều kiện.

- Ví dụ 3: Yêu cầu giống Ví dụ 2 nhưng dữ liệu nguồn nằm trong file Access. File này được tạo DBKEY với mã là MDB.

Công thức là:
=BS_DLOOKUP("TOP 1 h.TEN",
"KHO k INNER JOIN DMVLSPHH h ON k.MA_VLSPHH = h.MA_VLSPHH" ,
"k.LOAI_PHIEU = 'N' AND k.SLG = 10" ,
"DBKEY=MDB;")
 
- Ví dụ 4: Tìm (thông tin từ 2 bảng của CSDL Access) tên khách hàng mà có TỔNG số tiền phát sinh >4000000 với mã tài khoản Nợ là '131' (lấy 3 ký tự đầu của mã TK).
(Khi cài đặt Add-in A-Tools đã tạo DBKEY tới file Access này với mã kết nối là MDB. Bạn vào menu A-Tools -> DBKEY để xem và tạo DBKEY).

+ Phân tích: 
Hướng giải quyết: Cần kết nối 2 bảng NKC và DMKH vì tại đó có các thông tin để tìm và trả về.
Điều kiện lọc NOTK LIKE '131%' (so sánh 3 ký tự đầu tiên)
HAVING==SUM(n.THANH_TIEN) > 4000000 (vì câu điều kiện có hàm SUM nên phải dùng HAVING)

+ Công thức là:
=BS_DLOOKUP("h.TEN_KH",
"NKC n INNER JOIN DMKH h ON n.MA_KH = h.MA_KH" ,
"n.NOTK LIKE '131%' " ,
"DBKEY=MDB; HAVING=SUM(n.THANH_TIEN) > 4000000;")

+ Kết quả là:

Cty IPQ

Cty TNHH Hoàng Long

Cửa hàng máy tính Đông Đô

Cửa hàng Thành Đông

(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