Hàm tự tạo EXCELQUERY truy vấn dữ liệu file Excel (1 người xem)

Ngô Hải Đăng · 1/6/21

Công dụng: Truy vấn dữ liệu và trả kết quả trên file Excel thông qua câu lệnh SQL.
Yêu cầu: Biết sử dụng SQL để truy vấn dữ liệu trên Excel. (Có thể đọc qua loạt bài về ADO của anh Hai Lúa Miền Tây trên diễn đàn này).

#Cập nhật 02/06/2021
- Kết nối với file CSV và Access.

Cú pháp hàm: =EXCELQUERY(iSQL,[iDataHasColumnName],[iHeader],[iConnectType],[iConnectInfo])
Giải thích các tham số:

iSQL (String)	Câu lệnh SQL. Nếu iDataHasColumnName = 0 thì dùng f1,f2,…,fn thay cho tên cột. Nếu iConnectType = 2 thì phải dùng tên cột.
iDataHasColumnName (Boolean)	Là 0 hoặc không nhập nếu dữ liệu không có tên cột, là 1 nếu dữ liệu có tên cột. Mặc định là 0.
iHeader (String)	Tiêu đề cột cách nhau bởi dấu .. (CỘT A..CỘT B..CỘT C). Mặc định là không có tiêu đề cột.
iConnectType (Byte)	Không nhâp hoặc 0: kết nối file Excel; 1: kết nối file CSV; 2: kết nối file Access. Mặc định là 0.
iConnectInfo (String)	Nếu iConnectType=0 hoặc 2 thì nhập dầy đủ đường dẫn bao gồm tên File; iConnectType=1 thì nhập đường dẫn đến Folder chứa File. Không nhập đường dẫn thì là Folder hoặc File Excel hiện hành.

Cập nhật thêm hàm FROM để bổ trợ cho câu lệnh SQL. Hàm FROM nhận tham số là Range và trả về chuỗi "_FROM [TênSheet$ĐịaChỉ]_", nếu Range là 1 ô thì trả về chuỗi "_FROM [TênSheet$]_". Copy code vào Module1.

PHP:

Function FROM(iRange As Range) As String
  If Application.Caller.Parent.Parent Is iRange.Parent.Parent Then
    If iRange.CountLarge = 1 Then
      FROM = " FROM [" & iRange.Parent.Name & "$" & "] "
    Else
      FROM = " FROM [" & iRange.Parent.Name & "$" & iRange.Address(0, 0) & "] "
    End If
  End If
End Function

Thay vì viết "Select .... From...." thì viết thành "Select...."&FROM(iRange)

File ví dụ mình đính kèm bên dưới. Rất mong nhận được ý kiến đóng góp của mọi người.

befaint · 1/6/21

Cấu trúc một Workbook: Worksheets >> Range (Columns, Rows).

Không thấy tham số để gọi tới Worksheets thì có vẻ hơi thiêu thiếu rồi.

Maika8008 · 1/6/21

Ngô Hải Đăng đã viết:
Công dụng: Truy vấn dữ liệu và trả kết quả trên file Excel thông qua câu lệnh SQL.
Yêu cầu: Biết sử dụng SQL để truy vấn dữ liệu trên Excel. (Có thể đọc qua loạt bài về ADO của anh Hai Lúa Miền Tây trên diễn đàn này).
Cú pháp hàm: =EXCELQUERY(iSQL,[iDataHasColumnName],[iHeader],[iFilePath])
Giải thích các tham số:

iSQL (String) Câu lệnh SQL, nếu iDataHasColumnName = 0 thì dùng f1,f2,…,fn thay cho tên cột.
iDataHasColumnName (Boolean) Là 0 hoặc không nhập nếu dữ liệu không có tên cột, là 1 nếu dữ liệu có tên cột. Mặc định là 0.
iHeader(String) Tiêu đề cột cách nhau bởi dấu .. (CỘT A..CỘT B..CỘT C). Mặc định là không có tiêu đề cột.
iFilePath (String) Nếu lấy dữ liệu từ file Excel khác thì nhập đường dẫn. Không nhập đường dẫn thì là file Excel hiện hành.

File ví dụ mình đính kèm bên dưới. Rất mong nhân được ý kiến đóng góp của mọi người.

Thấy từ hàm UDF trước nhưng quên nhắc: trong Workbook_SheetChange thừa dòng Debug.Print rX.Address

Ngô Hải Đăng · 1/6/21

befaint đã viết:
Cấu trúc một Workbook: Worksheets >> Range (Columns, Rows).

Không thấy tham số để gọi tới Worksheets thì có vẻ hơi thiêu thiếu rồi.

Do nó nằm luôn trong câu truy vấn SQL nên em không để tham số này.

Bài đã được tự động gộp: 1/6/21

Maika8008 đã viết:
Thấy từ hàm UDF trước nhưng quên nhắc: trong Workbook_SheetChange thừa dòng Debug.Print rX.Address

Bài này mình bỏ đoạn đó ra rồi.

befaint · 1/6/21

Ngô Hải Đăng đã viết:
Do nó nằm luôn trong câu truy vấn SQL nên em không để tham số này.

Nên để tham số cho tên Worksheet thì hay hơn.

Kiểu như vậy này.

Ngô Hải Đăng · 1/6/21

befaint đã viết:
Nên để tham số cho tên Worksheet thì hay hơn.

Kiểu như vậy này.
View attachment 259830

Nếu để tên Worksheet thì bị vướng khi kết hợp dữ liệu từ 2 sheet của cùng một file nên em viết thêm 1 hàm FROM để khi Select bảng nào thì sẽ truyền tham số cho hàm FROM. Cập nhật trên bài 1#.

Pi-Pikachu · 1/6/21

Chủ đề hay quá, cảm ơn cậu.

Nhattanktnn · 1/6/21

Thêm tham số dữ liệu đầu vào là file .csv nữa bạn

truongvu317 · 1/6/21

Code chạy đã quá, xem thế nào bạn cho nó là addin.

Yeuvoyeucon · 1/6/21

Ngô Hải Đăng đã viết:
Công dụng: Truy vấn dữ liệu và trả kết quả trên file Excel thông qua câu lệnh SQL.
Yêu cầu: Biết sử dụng SQL để truy vấn dữ liệu trên Excel. (Có thể đọc qua loạt bài về ADO của anh Hai Lúa Miền Tây trên diễn đàn này).
Cú pháp hàm: =EXCELQUERY(iSQL,[iDataHasColumnName],[iHeader],[iFilePath])
Giải thích các tham số:

iSQL (String) Câu lệnh SQL, nếu iDataHasColumnName = 0 thì dùng f1,f2,…,fn thay cho tên cột.
iDataHasColumnName (Boolean) Là 0 hoặc không nhập nếu dữ liệu không có tên cột, là 1 nếu dữ liệu có tên cột. Mặc định là 0.
iHeader(String) Tiêu đề cột cách nhau bởi dấu .. (CỘT A..CỘT B..CỘT C). Mặc định là không có tiêu đề cột.
iFilePath (String) Nếu lấy dữ liệu từ file Excel khác thì nhập đường dẫn. Không nhập đường dẫn thì là file Excel hiện hành.

Cập nhật thêm hàm FROM để bổ trợ cho câu lệnh SQL. Hàm FROM nhận tham số là Range và trả về chuỗi "_FROM [TênSheet$ĐịaChỉ]_", nếu Range là 1 ô thì trả về chuỗi "_FROM [TênSheet$]_". Copy code vào Module1.

PHP:

Function FROM(iRange As Range) As String If Application.Caller.Parent.Parent Is iRange.Parent.Parent Then If iRange.CountLarge = 1 Then FROM = " FROM [" & iRange.Parent.Name & "$" & "] " Else FROM = " FROM [" & iRange.Parent.Name & "$" & iRange.Address(0, 0) & "] " End If End If End Function

Thay vì viết "Select .... From...." thì viết thành "Select...."&FROM(iRange)

File ví dụ mình đính kèm bên dưới. Rất mong nhận được ý kiến đóng góp của mọi người.

Code hay nhìn thấy bóng dáng SQL trên excel

Maika8008 · 2/6/21

Ngô Hải Đăng đã viết:
Bài này mình bỏ đoạn đó ra rồi.

Có 2 chỗ trong đó á bạn!

Ngô Hải Đăng · 2/6/21

Nhattanktnn đã viết:
Thêm tham số dữ liệu đầu vào là file .csv nữa bạn

Đã cập nhật code bài #1. Bạn test thử xem.

truongvu317 đã viết:
Code chạy đã quá, xem thế nào bạn cho nó là addin.

Addin thì phải chỉnh lại một chút trong Class Module vì code hiện tại chỉ bắt sự kiện trên Workbook.

FatBear · 10/6/21

wow.code hoành tráng quá. rất kì công và chi tiết. Cám ơn chủ thớt nhiều !

FatBear · 11/6/21

Ngô Hải Đăng đã viết:
Đã cập nhật code bài #1. Bạn test thử xem.

Addin thì phải chỉnh lại một chút trong Class Module vì code hiện tại chỉ bắt sự kiện trên Workbook.

hóng bản add-in của bác. quá hay và hiệu quả !

befaint · 11/6/21

langtusau9x đã viết:
quá hay và hiệu quả !

Lại có cả chấm than nữa!

Mình đã thử xóa các cells trong vùng kết quả xem có chuyện gì xảy ra chưa?

FatBear · 11/6/21

befaint đã viết:
Lại có cả chấm than nữa!

Mình đã thử xóa các cells trong vùng kết quả xem có chuyện gì xảy ra chưa?

ý bác là sao. kết quả sau khi được gán ra bảng tính hoàn toàn là value mà.

befaint · 11/6/21

langtusau9x đã viết:
ý bác là sao. kết quả sau khi được gán ra bảng tính hoàn toàn là value mà.

Lại còn ý nào nữa. Bạn cứ đọc thật ---- chậm thôi và làm theo.
Ví dụ công thức trả về kết quả ở 20 ô, bạn xóa đại 3 ô kết quả đi (tất nhiên là không xóa ô công thức). Rồi xem kết quả sau khi xóa là gì, rồi đối chiếu với mục đích cuối cùng của hàm đó. Đơn giản thế thôi.

FatBear · 11/6/21

befaint đã viết:
Lại còn ý nào nữa. Bạn cứ đọc thật ---- chậm thôi và làm theo.
Ví dụ công thức trả về kết quả ở 20 ô, bạn xóa đại 3 ô kết quả đi (tất nhiên là không xóa ô công thức). Rồi xem kết quả sau khi xóa là gì, rồi đối chiếu với mục đích cuối cùng của hàm đó. Đơn giản thế thôi.

Ý bác là xóa đi, nhưng công thức không báo lỗi, cũng không có việc gì xảy ra khi xóa.
Còn riêng tôi thì thấy hàm hoàn toàn đáp ứng được mục đích truy vấn dữ liệu file Excel ở tiêu đề của bài viết.

Ngô Hải Đăng · 11/6/21

Hàm này thực chất không phải hàm mảng mặc dù nó trả ra mảng kết quả. Nó chỉ lợi dụng sự kiện calculate của worksheet để gọi 1 sub chạy sau đó.

Pi-Pikachu · 12/6/21

Ngô Hải Đăng đã viết:
Công dụng: Truy vấn dữ liệu và trả kết quả trên file Excel thông qua câu lệnh SQL.
Yêu cầu: Biết sử dụng SQL để truy vấn dữ liệu trên Excel. (Có thể đọc qua loạt bài về ADO của anh Hai Lúa Miền Tây trên diễn đàn này).

#Cập nhật 02/06/2021
- Kết nối với file CSV và Access.

Cú pháp hàm: =EXCELQUERY(iSQL,[iDataHasColumnName],[iHeader],[iConnectType],[iConnectInfo])
Giải thích các tham số:

iSQL (String) Câu lệnh SQL. Nếu iDataHasColumnName = 0 thì dùng f1,f2,…,fn thay cho tên cột. Nếu iConnectType = 2 thì phải dùng tên cột.
iDataHasColumnName (Boolean) Là 0 hoặc không nhập nếu dữ liệu không có tên cột, là 1 nếu dữ liệu có tên cột. Mặc định là 0.
iHeader (String) Tiêu đề cột cách nhau bởi dấu .. (CỘT A..CỘT B..CỘT C). Mặc định là không có tiêu đề cột.
iConnectType (Byte) Không nhâp hoặc 0: kết nối file Excel; 1: kết nối file CSV; 2: kết nối file Access. Mặc định là 0.
iConnectInfo (String) Nếu iConnectType=0 hoặc 2 thì nhập dầy đủ đường dẫn bao gồm tên File; iConnectType=1 thì nhập đường dẫn đến Folder chứa File. Không nhập đường dẫn thì là Folder hoặc File Excel hiện hành.

Cập nhật thêm hàm FROM để bổ trợ cho câu lệnh SQL. Hàm FROM nhận tham số là Range và trả về chuỗi "_FROM [TênSheet$ĐịaChỉ]_", nếu Range là 1 ô thì trả về chuỗi "_FROM [TênSheet$]_". Copy code vào Module1.

PHP:

Function FROM(iRange As Range) As String If Application.Caller.Parent.Parent Is iRange.Parent.Parent Then If iRange.CountLarge = 1 Then FROM = " FROM [" & iRange.Parent.Name & "$" & "] " Else FROM = " FROM [" & iRange.Parent.Name & "$" & iRange.Address(0, 0) & "] " End If End If End Function

Thay vì viết "Select .... From...." thì viết thành "Select...."&FROM(iRange)

File ví dụ mình đính kèm bên dưới. Rất mong nhận được ý kiến đóng góp của mọi người.

Bạn ơi với câu lệnh này:
=EXCELQUERY("SELECT *"&FROM(Data!A2)&"WHERE khach_hang like'%"&D8&"%'",1,"SỐ TỜ KHAI..NGÀY ĐĂNG KÝ..KHÁCH HÀNG..INV..USD")
thì sẽ trả về theo khách trong ô D8 ?
Vậy khi tôi muốn kết quả trả về cho vài chục khách hàng trong một danh sách hàng trăm khách hàng thì sẽ xử lý thế nào vậy bạn hoặc giả sử các khách hàng tôi muốn lấy dữ liệu sẽ liệt kê trong vùng A8:A100 thì sẽ xử lý thế nào vậy bạn?

Ngô Hải Đăng · 12/6/21

Pi-Pikachu đã viết:
Bạn ơi với câu lệnh này:
=EXCELQUERY("SELECT *"&FROM(Data!A2)&"WHERE khach_hang like'%"&D8&"%'",1,"SỐ TỜ KHAI..NGÀY ĐĂNG KÝ..KHÁCH HÀNG..INV..USD")
thì sẽ trả về theo khách trong ô D8 ?
Vậy khi tôi muốn kết quả trả về cho vài chục khách hàng trong một danh sách hàng trăm khách hàng thì sẽ xử lý thế nào vậy bạn hoặc giả sử các khách hàng tôi muốn lấy dữ liệu sẽ liệt kê trong vùng A8:A100 thì sẽ xử lý thế nào vậy bạn?

Nếu ít thì Where khach_hang in ('a','b','c'). Nhiều thì Where khach_hang in (select * from [tên sheet$A7:A100]). Trong đó ô A7 là tiêu đề, từ A8 đến A100 là danh sách khách hàng. Bạn tìm hiểu thêm về các lệnh truy vấn sql là sẽ thấy thôi.

weddingnight · 19/9/21

Thấy code hay quá xin phép mình cập nhật một số nội dung cho bạn nào cần:

- Bỏ iDataHasColumnName mặc định là sẽ có tên cột
- Bỏ iConnectType, nếu có iConnectInfo thì sẽ lấy theo iConnectInfo nếu không là lấy bản thân file excel
- Sửa hàm FROM bỏ "from" để viết được câu lệnh sql phức tạp hơn: join, union
- Bổ sung khả năng xóa dòng (không phải clear) dữ liệu cũ và insert dòng dữ liệu mới, tạo dữ liệu động, thiết kế khung báo cáo 1 lần có thay đổi dữ liệu sẽ tự động co dãn

=> Tự test nha

mrbl4c · 22/4/23

Cho mình hỏi chút, mình copy modul + Class Modul vào để tạo file add in nhưng hàm này chạy lại cho ra kết quả sai, các bạn giúp mình tạo file add in để dùng với ạ. Thanks

Maika8008 · 22/4/23

mrbl4c đã viết:
Cho mình hỏi chút, mình copy modul + Class Modul vào để tạo file add in nhưng hàm này chạy lại cho ra kết quả sai, các bạn giúp mình tạo file add in để dùng với ạ. ThanksView attachment 289326

Sao có ký tự @ ở đầu công thức vậy nhỉ? File ví dụ ở bài #1 có viết vậy đâu.

mrbl4c · 22/4/23

Maika8008 đã viết:
Sao có ký tự @ ở đầu công thức vậy nhỉ? File ví dụ ở bài #1 có viết vậy đâu.

Mình không rõ tại sao nhưng thi thoảng 1 số công thức vẫn có, k rõ nguyên nhân ạ

HeSanbi · 22/4/23

Trong hàm Query tác giả sử dụng giải thuật gán lại hàm sau khi có kết quả, Nếu mã chạy ở phiên bản Excel mới với @ (Toán tử giao nhau)
Phương thức Range.Formula ở phiên bản cũ chính là Range.Formula2 ở phiên bản mới. Range.Formula ở phiên bản mới tự động thêm toán tử @.

Phải thêm mã phù hợp để bỏ qua lỗi này. Trước hết là tìm phiên bản Excel. Và với mã gán như sau:

JavaScript:

If NewExcelVersion Then
   [Range].Formula2 = "=EXCELQUERY(...)"
Else
   [Range].Formula = "=EXCELQUERY(...)"
End if

Nếu các bạn có thời gian có thể tham khảo thêm hàm Query dưới đây

https://www.giaiphapexcel.com/diendan/threads/164425/

iSQL (String)	Câu lệnh SQL, nếu iDataHasColumnName = 0 thì dùng f1,f2,…,fn thay cho tên cột.
iDataHasColumnName (Boolean)	Là 0 hoặc không nhập nếu dữ liệu không có tên cột, là 1 nếu dữ liệu có tên cột. Mặc định là 0.
iHeader(String)	Tiêu đề cột cách nhau bởi dấu .. (CỘT A..CỘT B..CỘT C). Mặc định là không có tiêu đề cột.
iFilePath (String)	Nếu lấy dữ liệu từ file Excel khác thì nhập đường dẫn. Không nhập đường dẫn thì là file Excel hiện hành.

Hàm tự tạo EXCELQUERY truy vấn dữ liệu file Excel (1 người xem)

Người dùng đang xem chủ đề này

Thành viên hoạt động

File đính kèm

|||||||||||||

Thành viên gạo cội

Thành viên hoạt động

|||||||||||||

Thành viên hoạt động

Thành viên chính thức

Thành viên gắn bó

Thành viên tích cực

Thành viên hoạt động

Thành viên gạo cội

Thành viên hoạt động

Thành viên hoạt động

Thành viên hoạt động

|||||||||||||

Thành viên hoạt động

|||||||||||||

Thành viên hoạt động

Thành viên hoạt động

Thành viên chính thức

Thành viên hoạt động

Thành viên mới

File đính kèm

Thành viên mới

Thành viên gạo cội

Thành viên mới

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Thời gian đếm ngược.