Google Sheets là công cụ mạnh mẽ cho việc xử lý dữ liệu. Bài viết này sẽ hướng dẫn bạn cách tạo thanh tìm kiếm tùy chỉnh, cho phép lọc dữ liệu dựa trên nhiều tiêu chí và cột được chọn, sử dụng hoàn toàn các hàm có sẵn trong Google Sheets, không cần dùng Apps Script.
Trong ví dụ này, chúng ta sẽ làm việc với bảng dữ liệu bán hàng, và muốn tạo thanh tìm kiếm trên một sheet khác để lọc dữ liệu dựa trên các tiêu chí nhập vào. Thanh tìm kiếm sẽ kiểm tra năm cột đầu tiên (ID, Ngày, Doanh số, Khu vực, Đơn vị) nhưng chỉ những cột được chọn thông qua các ô checkbox. Nếu tất cả các ô checkbox không được chọn, nó sẽ hiển thị tất cả dữ liệu.
Xây Dựng Công Thức Lọc Tùy Chỉnh
Thách thức chính là kiểm tra từng hàng so với cụm từ tìm kiếm trong các cột đã chọn. Đối với mỗi hàng, nếu bất kỳ cột nào được chọn chứa cụm từ tìm kiếm, hàng đó sẽ được đưa vào kết quả.
Chúng ta sẽ phân tích từng bước:
1. Xử Lý Checkbox:
Mỗi checkbox (ví dụ: trong các ô B1:B5) tương ứng với một cột. Khi một checkbox được chọn, cột đó sẽ được đưa vào tìm kiếm. Ví dụ: nếu checkbox Khu vực được chọn, công thức sẽ kiểm tra cột Khu vực cho cụm từ tìm kiếm.
2. Cụm Từ Tìm Kiếm:
Người dùng nhập cụm từ tìm kiếm vào một ô cụ thể, ví dụ: A1. Công thức cần tham chiếu đến ô này.
3. Hàm FILTER:
Sử dụng hàm FILTER để hiển thị kết quả. Tuy nhiên, điều kiện cho FILTER cần kiểm tra động từng cột đã chọn cho cụm từ tìm kiếm.
4. Điều Kiện Động:
Đối với mỗi hàng, tạo một điều kiện kiểm tra xem cụm từ tìm kiếm có tồn tại trong bất kỳ cột nào đã chọn hay không. Đối với mỗi cột, nếu checkbox được chọn, hãy kiểm tra xem cụm từ tìm kiếm có khớp với dữ liệu của cột đó hay không. Sau đó kết hợp các điều kiện này với logic OR.
5. Xử Lý Kiểu Dữ Liệu:
Một số cột là số (ID, Đơn vị) và các cột khác là văn bản (Ngày dưới dạng ngày giờ, Doanh số, Khu vực). Cụm từ tìm kiếm là văn bản, vì vậy khi tìm kiếm trong các cột số, chúng ta có thể cần chuyển đổi chúng thành văn bản để so sánh. Ví dụ: nếu người dùng tìm kiếm “123”, nó sẽ khớp với ID là 123.
6. Không Phân Biệt Chữ Hoa Chữ Thường:
Việc tìm kiếm không nên phân biệt chữ hoa chữ thường. Sử dụng LOWER trên cả cụm từ tìm kiếm và dữ liệu cột có thể giúp thực hiện điều này.
7. Xử Lý Ngày:
Nếu cột Ngày là ngày giờ, việc chuyển đổi nó thành văn bản có thể phức tạp. Sử dụng TEXT(cột_ngày, "yyyy-mm-dd hh:mm:ss") có thể chuẩn hóa định dạng để so sánh.
Công Thức Hoàn Chỉnh
Giả sử:
- Cụm từ tìm kiếm nằm trong ô A1 của sheet thứ hai.
- Các checkbox cho năm cột (ID, Ngày, Doanh số, Khu vực, Đơn vị) nằm trong các ô B1:B5, với B1=ID, B2=Ngày, v.v., mỗi ô được liên kết với một checkbox, do đó B1 là TRUE khi được chọn.
- Dữ liệu nằm trong Data!A2:G (giả sử hàng 1 là tiêu đề).
Công thức trong sheet thứ hai, nơi kết quả sẽ xuất hiện (bắt đầu từ A2 chẳng hạn):
=IF(OR(A1="", COUNTIF(B1:B5, TRUE)=0), Data!A2:G,
FILTER(Data!A2:G,
ARRAYFORMULA(
(B1*(LOWER(TO_TEXT(Data!A2:A))=LOWER(A1))) +
(B2*(LOWER(TEXT(Data!B2:B, "yyyy-mm-dd hh:mm:ss"))=LOWER(A1))) +
(B3*(LOWER(Data!C2:C)=LOWER(A1))) +
(B4*(LOWER(Data!D2:D)=LOWER(A1))) +
(B5*(LOWER(TO_TEXT(Data!E2:E))=LOWER(A1)))
)>0)
)
Công thức Google Sheets
Lưu ý: Đảm bảo rằng các checkbox được liên kết với các ô B1 đến B5 và cụm từ tìm kiếm nằm trong ô A1. Điều chỉnh tham chiếu ô khi cần thiết dựa trên thiết lập thực tế. Hãy điều chỉnh phạm vi (A2:A, B2:B, v.v.) để khớp với phạm vi dữ liệu thực tế. Nếu dữ liệu có 1000 hàng, tốt hơn là sử dụng Data!A2:A1000, v.v., để tránh xử lý toàn bộ cột, điều này có thể làm chậm sheet.
Kết Luận
Với công thức này, bạn đã tạo thành công một thanh tìm kiếm tùy chỉnh mạnh mẽ trong Google Sheets, cho phép lọc dữ liệu linh hoạt dựa trên nhiều tiêu chí và cột. Việc sử dụng các hàm có sẵn giúp đơn giản hóa quá trình và tránh việc phải sử dụng Apps Script, làm cho nó dễ dàng hơn cho người dùng không chuyên về lập trình. Hãy nhớ điều chỉnh công thức cho phù hợp với dữ liệu và yêu cầu cụ thể của bạn.
Discussion about this post