Bài viết này tôi hướng dẫn các bạn dùng SQL nói chung và Add-in A-Tools nói riêng trên Excel về nguyên tắc tạo bảng dữ liệu nguồn trên tập tin Excel. Đây là kiến thức đầu tiên cũng là quan trọng nhất trước khi bạn sử dụng các lệnh SQL cũng như các hàm trúy vấn cơ sở dữ liệu (CSDL) - làm việc với SQL, tạo báo cáo động như BS_SQL, BS_JOIN, BS_TABLE, BS_DSUM, BS_DVLOOKUP,...

Trong Excel dữ liệu nhập tự do trên bảng tính chứ không theo nguyên tắc CSDL như SQL Server, MySQL, Oracle, Access,... Vì vậy chúng ta phải định nghĩa nó để Excel coi nó là một table chuẩn để chạy SQL.

Tạo table trong Excel

Bạn cần nắm làm các nguyên tắc sau:

- Dsòng đầu tiên cảu bảng gọi là dòng tiêu đề. Tiêu đề các cột nên viết ngắn gọn, dễ nhớ, không nên viết chữ có dấu, ký tự toán học dù không cấm. Vì khi làm lệnh những tên cột ngắn gọn sẽ giảm bớt nhưng lỗi soạn thảo. Bạn hãy xem hai câu lệnh dưới đây để thấy sự khác nhau:

Tên bảng và tên cột đặt tự do câu lệnh SQL là

SELECT [Mã hàng], [Số lượng], [Thành tiền] FROM [Dữ liệu] WHERE [Mã hàng] = 'HH001' AND [Số lượng] <> 0

Tên bảng đặt là DATA, các cột đặt không dâu,  câu lệnh SQL là

SELECT MA_HH, SLG, TTIEN FROM DATA WHERE MA_HH = 'HH001' AND SLG <> 0

Bạn thấy việc đặt tên không dấu giúp soạn thảo lệnh SQL ngắn gọn, giảm bớt sai sót rất nhiều.

- Mỗi cột thông tin chỉ được phép ghi một kiểu dữ liệu. Ví dụ cột số lượng, số tiền (Số lượng hàng, Số tiền, số người) chỉ để ghi số tiền, không được nhập văn bản, ngày tháng, logic,... Cột thời gian cũng như số lượng, không được nhập các loại giá trị khác. Ví dụ cột Bill_Date chỉ vô tình bạn nhập một ký tự văn bản thì cột đó không còn là kiểu DATE nữa.

-  Các cột số lượng, những dòng trống của cột đó nên nhập số 0 để Excel chắc chắn không hiểu nhầm là kiểu dữ liệu Text hay loại khác.

Dùng tên sheet làm dữ liệu nguồn - Table

Lợi ích của việc này là hàm BS_SQL lấy được toàn bộ dữ liệu trong sheet. Yêu cầu bảng dữ liệu là:
 
- Dòng tiêu đề của bảng phải từ dòng 1 của sheet
- Tên dữ liệu nguồn trong lệnh SQL phải ghi [Tên sheets$]

Ví dụ
=BS_SQL("SELECT * FROM [Data$] d WHERE d.MA_HH='HH001' ")

Dùng NAME để làm dữ liệu nguồn - Table

Lợi ích của việc này là bảng dữ liệu nguồn đặt tự do ở bất kỳ vị trí nào trong sheet.
(*) Nhược điểm cách này số dòng tối đa lấy được là 65536 dòng.

Cách tạo NAME - Define Name trong Excel
 
- Bước 1: Chọn vùng dữ liệu nguồn, từ dòng tiêu đề của bảng. Ví dụ A1:L100
- Bước 2:  Tạo hoặc sửa NAME

Nếu lần đầu tạo NAME. Vào menu Formulas -> Define Name. Tại sửa sổ "New Name"
+ Name: Đặt tên bảng, ví dụ DATA
+ Scope: Để Workbook (ngầm định)
+ Refers To: Địa chỉ vùng dữ liệu.Ngầm định Excel lấy vùng chọn ở Bước 1.
+ Nhấn nút "Ok' để hoàn tất.

Nếu NAME đã có, cần sửa lại: Giả sử bảng dữ liệu DATA đã có, bạn cần điều chỉnh lại vùng địa chỉ.
+ Vào menu Formulas -> Name Manager.
+ Tại cửa sổ Name Mânger, chọn dòng có tên là DATA, chọn nút "Edit..."
+ Tại cửa sổ "Edit Name", sửa lại Refers To về địa chỉ bạn muốn.
+ Nhấn nút "Ok" để hoàn tất.




Công thức với hàm BS_SQL với NAME là

=BS_SQL("SELECT * FROM DATA d WHERE d.MA_HH='HH001' ")

(*) Lưu ý:
- Lần đầu tạo NAME hoặc khi thay đổi tên cột, thêm cột nên lưu và đóng file Excel sau đó mở lại để cập nhật.
- File Excel, Access làm dữ liệu nguồn cho SQL không được lưu trên các dịch vụ lưu trữ cloud như Google Drive, OneDrive. Với File tạo báo cáo (không chứa dữ liệu nguồn) mà dùng công thức với hàm BS_SQL thì lưu ở đâu cũng được.

Đọ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