Bài viết này tôi hướng dẫn các bạn dùng hàm BS_SQL trong Add-in A-Tools. Đây là hàm rất mạng, đa năng cho phép bạn tạo báo cáo động trong Excel. Hàm sử dụng ngôn ngữ chuẩn T-SQL và hàm mở rộng của A-Tools. Tốc độ chạy báo cáo rất nhanh. Dữ liệu nguồn là Excel hay các CSDL bên ngoài.

Đây là siêu hàm của Add-in A-Tools bạn làm mọi báo cáo chi tiết đến tổng hợp dù khó vẫn có thể làm được một cách dễ dàng. Bạn hãy đầu tư thời gian để từng bước học và làm chủ hàm này sẽ giúp bạn rất nhiều công việc trích lọc và tổng hợp dữ liệu trên Excel. 


Cấu trúc hàm

BS_SQL(SQL, [Options], [FormatRangeAddress])

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

- SQL:

Là câu lệnh T-SQL để truy vấn dữ liệu. Bạn có thể sử dụng những hàm mở rộng A-Tools cho phép như: RECNO() - thêm cột số thứ tự; DATA() lấy giá trị trong mảng kết quả; VTC()- tách cột tự động; GROUP_CONCAT() - nhóm và liệt kê giá trị,..

Ví dụ trong tập tin của bạn đã tạo vùng KHO - được Define Name tới vùng dữ liệu A3:L100. Câu lệnh SQL để lấy tất cả dữ liệu là:

=BS_SQL("SELECT * FROM KHO")

Lấy tất cả, lọc theo mã khách hàng (MA_KH) là KH001 thì công thức là

=BS_SQL("SELECT * FROM KHO WHERE MA_KH='KH001' ")

Nếu Mã khách hàng (giá trị làm điều kiện) đặt ở ô C4 thì công thức là

=BS_SQL("SELECT * FROM KHO WHERE MA_KH=CELL(C4) " & LEFT(c4,0) )

Giải thích:
- CELL(tham chieus/name) là hàm bổ trợ cho SQL bà A-Tools cung cấp, nó sẽ lấy giá trị trong tham chiếu.
- LEFT(tham chiếu, 0) để báo A-Tools cập nhật lại công thức mỗi thì giá trị trong tham chiếu điều kiện thay đổi.

Nếu bạn tổ chức lưu dữ liệu nguồn cho cả sheet thì hãy dùng tên sheet làm dữ liệu nguồn (sau từ khóa FROM).

Giả sử Sheet1 chứa dữ liệu KHO - Nguyên tắc dòng đầu là tiêu đề bảng.

=BS_SQL("SELECT * FROM [Sheet1$] WHERE MA_KH=CELL(C4) " & LEFT(c4,0) )

(*) Hãy đọc hiểu bài viết "Nguyễn tắc tạo bảng dữ liệu nguồn - Chuẩn hóa dữ liệu" trước khi sử dụng hàm BS_SQL.

(*) Có một số hàm A-Tools hỗ trợ để dùng trong khai báo SQL như: VTC, GROUP_CONCAT, DATA, RANGE, CELL, GETONCE, IIF, MAX, MIN. Bãn hãy mở các file đề xuất học cuối bài viết này để học cách sử dụng.

- 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:

"INSERT=YES/NO;" Ngầm định là NO; Nếu là YES - Cho phép kết quả trả về co giãn dòng và cột trên bảng tính.

"HR=YES/NO;" Ngầm định là YES - Hiển thị dòng tiêu đề bảng; NO - Bỏ dòng tiêu đề.

"DBKEY=dbkeyCode;" Nếu bỏ qua A-Tools lấy dữ liệu tại tập tin đang chạy hàm. Nếu khai báo mã dbKeyCode A-Tools sẽ truy vấn đến cơ sở dữ liệu mà DBKEY đó đang liên kết. Ví dụ:

"DBKEY=MDB;"

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

"SVR=YES;" - Nếu không khai báo (NO) A-Tools truy vấn dữ liệu tại máy tính đang chạy hàm. Nếu khai báo YES - Báo cho A-Tools lấy dữ liệu ở máy tính khác, máy đó chạy chức năng "Tạo máy chủ". Hãy xem hướng dẫn "Chia sẻ Excel qua mạng với Add-in A-Tools".

"SORT=n [ASC/DESC];" Cho phép sắp xếp một hay nhiều cột trong bảng kết quả. Tức bảng kết quả trả về bởi SQL thì được sắp xếp lần nữa bởi khai báo này. 
n: có thể là số nguyên chỉ ra vị trí cột cần sắp xếp, nếu nhiều cột thì liệt kê các vị trí cách nhau bởi dấu phảy (,), cột nào đứng trước thì ưu tiên trước. Nếu là số âm thì giảm dần, hoặc khai báo DESC đằng sau.

Ví dụ: sắp xếp cột đầu tăng dần, cột thứ hai giảm dần

"SORT=0, -1;" là

Hoặc

"SORT=0, 1 DESC;"

(*) Lưu ý: Riêng hàm BS_SQL vị trí cột đánh số từ 0. Các hàm khác của A-Tools đánh số từ 1.
Bạn cần chú ý điều này khi dùng hàm DATA để làm biểu thức tính toán trong từ khóa SELECT của tham số SQL.

- FormatRangeAddress: 

Chỉ ra vùng định có dạng mẫu. Tham số này có thể dùng hoặc không. Nó cần dùng nếu bạn sử dụng thuộc tính GROUP, FOOTER để nhóm dữ liệu trong báo cáo theo dạng chia nhánh giống SUBTOTAL.

Hãy xem bài viết "Tạo báo cáo phân nhóm trong Excel bằng Add-in A-Tools".
File ví dụ: "C:\A-Tools\Help & Demos\BS_SQL_Create report with GROUPING and FOOTER - Báo cáo phân nhóm.xls"

Ví dụ đơn giản

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: 

(*) Các ví dụ cơ bản về hàm BS_SQL bạn hãy mở file trong bộ cài đặt để xem
- File1: "C:\A-Tools\Help & Demos\Bai 1 - Thuc hanh trich loc du lieu nhieu dieu kien can ban.xls"
- File 2: "C:\A-Tools\Help & Demos\Bai 2 - Cac ham ho tro nhom ham 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