Hàm truy vấn dữ liệu
Ba hàm đọc dữ liệu từ file Excel đang đóng — không cần mở file, không cần liên kết, không cần cài thêm gì.
| Hàm | Công dụng |
|---|---|
dvdSQL | Truy vấn và trả về cả một vùng kết quả. |
dvdSQLValue | Như trên nhưng chỉ trả về một giá trị — dùng cho tổng, đếm, tra cứu một ô. |
dvdSheets | Liệt kê tên các sheet của một file đang đóng. |
Các ví dụ dưới đây dùng dấu chấm phẩy ; ngăn cách tham số (Regional format Việt Nam). Máy chạy English (US) thì gõ dấu phẩy ,.
Vì sao không cần mở file
Hàm đọc thẳng file bằng thư viện nằm sẵn trong add-in. Hệ quả thực tế:
- Không cần cài thêm driver. Cách làm phổ biến của các add-in khác là dùng provider ACE OLEDB — phải cài riêng và phải đúng 32/64-bit với Office, máy công trường rất hay thiếu.
- File nguồn không bị mở, không bị khoá, không bị sửa.
- Kết quả được nhớ tạm theo thời điểm sửa file, nên một trăm ô công thức chỉ đọc file một lần. Sửa file nguồn rồi tính lại thì tự đọc lại.
- Ô hiện
#N/Atrong chốc lát rồi ra kết quả — hàm chạy nền để Excel không bị đứng.
Cú pháp
SELECT * | cột [AS tên] | SUM(cột) | COUNT(*) | COUNT|AVG|MIN|MAX(cột)
FROM 'đường dẫn file'!tên sheet
[WHERE điều kiện]
[GROUP BY cột[, cột…]]
[ORDER BY cột [ASC|DESC][, …]]- Tên cột có dấu cách đặt trong ngoặc vuông:
[Khối lượng]. - Chuỗi đặt trong nháy đơn:
'Móng'. Muốn có dấu nháy bên trong thì gõ hai lần:'Bê tông ''M250'''. - Số luôn viết dấu chấm thập phân — câu lệnh là văn bản, không phụ thuộc Regional format.
- Điều kiện:
= <> > >= < <=,LIKE(%là chuỗi bất kỳ,_là một ký tự),IN (…),IS NULL,IS NOT NULL, kết hợp bằngAND/OR/NOTvà dấu ngoặc.
Hàm không hỗ trợ JOIN, truy vấn lồng, HAVING, DISTINCT. Đó là chủ ý: phần việc thật sự cần là lọc, cộng và gom nhóm dữ liệu nằm rải trong nhiều file.
dvdSQL
=dvdSQL(câu lệnh; [dòng tiêu đề]; [kèm tiêu đề])| Tham số | Mặc định | Ý nghĩa |
|---|---|---|
| câu lệnh | — | Câu lệnh SQL rút gọn. |
| dòng tiêu đề | 1 | Dòng chứa tên cột của sheet nguồn. 0 = không có tiêu đề, cột được gọi là F1, F2… |
| kèm tiêu đề | TRUE | Dòng đầu của kết quả là tên cột. |
Kết quả tràn ra vùng bên dưới (Excel 365 / 2021). Bản Excel cũ hơn thì chọn sẵn vùng rồi nhấn Ctrl+Shift+Enter.
=dvdSQL("SELECT [Mã hiệu], [Khối lượng] FROM 'D:\HS\KL.xlsx'!TH WHERE [Hạng mục]='Móng'")Dấu chấm phẩy bên trong câu lệnh
Câu lệnh là một chuỗi, nên dấu ngăn cách cột bên trong nó luôn là dấu phẩy , theo chuẩn SQL — không đổi theo Regional format. Chỉ dấu ngăn tham số của hàm mới đổi giữa ; và ,.
Hướng dẫn bằng hình
Thực hành
Tải file mẫu của hàm này — dữ liệu y như hình dưới, ô cần gõ công thức đã được chọn sẵn.
Bước 1. File nguồn D:\DuAn\KhoiLuong.xlsx (KHÔNG cần mở): sheet TH (3) có tiêu đề cột ở dòng 3 (1), cột Hạng mục (2) dùng để lọc.
Bước 2. Trong file đang làm, chọn ô rồi gõ =dvdsq — danh sách gợi ý hiện DVDSQL kèm mô tả hàm (1). Nhấn Tab để chọn.
Bước 3. Gõ câu lệnh trong nháy kép (1) — bên trong câu lệnh, cột ngăn bằng dấu phẩy, chuỗi đặt trong nháy đơn, tên cột có dấu cách đặt trong [ ]. Tiêu đề nằm ở dòng 3 nên gõ thêm ; 3 (2).
Bước 4. Nhấn Enter: ô hiện #N/A trong chốc lát rồi kết quả tràn xuống (1), dòng đầu là tên cột. File nguồn không bị mở hay khoá; câu lệnh sai thì ô báo #SQL: <lý do>.
dvdSQLValue
Dùng khi chỉ cần một con số — tra cứu chéo file thay cho VLOOKUP trỏ sang file đóng.
=dvdSQLValue("SELECT SUM([Khối lượng]) FROM 'D:\HS\KL.xlsx'!TH WHERE [Mã hiệu]='AF.211'")Không dòng nào khớp thì trả #N/A, để IFNA bắt được như hàm dò tìm bình thường.
Hướng dẫn bằng hình
Thực hành
Tải file mẫu của hàm này — dữ liệu y như hình dưới, ô cần gõ công thức đã được chọn sẵn.
Bước 1. Chọn ô cạnh đường dẫn file đầu tiên rồi gõ =dvdsqlv — Excel gợi ý DVDSQLVALUE kèm mô tả hàm (1). Nhấn Tab để chọn.
Bước 2. Ghép đường dẫn ở ô A4 (1) vào câu lệnh bằng &: FROM '" & A4 & "'!TH. Câu lệnh chỉ lấy một giá trị — ở đây là SUM; tiêu đề cột của các file nằm ở dòng 3 nên gõ thêm ; 3 (2).
Bước 3. Nhấn Enter rồi kéo xuống: mỗi ô ra tổng bê tông của một file (1) mà không mở file nào; ô cộng =SUM(B4:B6) (2) tự tính. Không dòng nào khớp thì ra #N/A, bắt bằng IFNA như hàm dò tìm.
dvdSheets
=dvdSheets("D:\HS\KL.xlsx")Trả về danh sách tên sheet theo cột — tiện khi chưa nhớ tên sheet để viết câu lệnh.
Hướng dẫn bằng hình
Thực hành
Tải file mẫu của hàm này — dữ liệu y như hình dưới, ô cần gõ công thức đã được chọn sẵn.
Bước 1. Chọn ô đầu cột kết quả rồi gõ =dvdsh — Excel gợi ý DVDSHEETS kèm mô tả hàm (1). Nhấn Tab để chọn.
Bước 2. Bấm ô chứa đường dẫn file (1) (hoặc gõ thẳng đường dẫn trong nháy kép). Hàm chỉ có một đối số (2); file không cần mở.
Bước 3. Nhấn Enter: sau chốc lát #N/A, tên các sheet tràn xuống theo cột (1) — chép tên sheet vào câu lệnh của dvdSQL / dvdSQLValue. Không đọc được file thì ô báo #SQL: ….
Ví dụ trong công việc
Tổng khối lượng bê tông của 12 file đội gửi về, mỗi file một ô:
=dvdSQLValue("SELECT SUM([Khối lượng]) FROM '" & A2 & "'!TH WHERE [Tên công tác] LIKE 'Bê tông%'")Cột A chứa đường dẫn từng file, kéo công thức xuống là xong — không mở file nào.
Bảng tổng hợp theo hạng mục từ một file khác:
=dvdSQL("SELECT [Hạng mục], SUM([Khối lượng]) AS Tổng FROM 'D:\HS\KL.xlsx'!TH GROUP BY [Hạng mục] ORDER BY Tổng DESC")Lưu ý
- Câu lệnh sai thì ô hiện
#SQL: <lý do>chứ không phải#VALUE!— đọc là biết sai chỗ nào. - File có mật khẩu không đọc được; ô sẽ báo rõ.
- Kết quả của
dvdSQLbị cắt ở 50.000 dòng. Khi đó vùng kết quả có thêm một dòng cuối mà ô đầu ghi⚠ Kết quả bị cắt còn 50.000 dòng — thu hẹp bằng WHERE / GROUP BY(các ô khác của dòng để trống) — thu hẹp bằngWHEREhoặc gom bằngGROUP BY. Không bị cắt thì kết quả không có dòng này. ORDER BYdùng được cả cột chưa có trongSELECT, trừ khi cóGROUP BYhoặc hàm tổng hợp (SUM,COUNT…): khi đó chỉ sắp được theo cột kết quả — tên cột hoặc tên đặt bằngAS. Trỏ vào cột khác thì ô báo#SQL: ORDER BY cột "…" không có trong kết quả…thay vì trả bảng không sắp.- Ô trống không lọt vào điều kiện
>hay<, giống cách SQL đối xử với giá trị rỗng.
Không muốn gõ câu lệnh?
Lệnh Truy vấn dữ liệu dựng câu lệnh giúp bạn: chọn file, tick cột, đặt điều kiện, xem trước dữ liệu thật rồi chèn công thức vào ô hoặc đổ giá trị ra sheet.
Liên quan
- Truy vấn dữ liệu — cửa sổ dựng câu lệnh.
- Gộp file — khi cần ghép hẳn nhiều file thành một.
- Cầu nối dự toán — nhận bảng khối lượng từ phần mềm dự toán khác.