Khám Phá Sức Mạnh Hàm FILTER Google Sheets: Hướng Dẫn Chi Tiết

Google Sheets không chỉ dừng lại ở việc tạo bảng tính cơ bản. Nếu bạn mới chỉ biết đến bộ lọc tiêu chuẩn, thì đây là một khám phá thú vị: hàm FILTER. Bài viết này sẽ cùng bạn đi sâu vào cách sử dụng hàm FILTER mạnh mẽ, cung cấp nhiều công thức sẵn có và một công cụ bổ trợ đắc lực, giúp bạn làm chủ việc trích xuất dữ liệu hiệu quả.

I. Giới Thiệu Về Hàm FILTER Trong Google Sheets

Hàm FILTER trong Google Sheets là một công cụ mạnh mẽ cho phép bạn quét dữ liệu và trả về thông tin cần thiết dựa trên các tiêu chí bạn đặt ra. Khác biệt với bộ lọc tiêu chuẩn, hàm FILTER không thay đổi dữ liệu gốc mà thay vào đó, nó sao chép các hàng thỏa mãn điều kiện và đặt chúng ở một vị trí mới do bạn chỉ định. Điều này đảm bảo tính toàn vẹn của dữ liệu gốc trong khi vẫn cung cấp khả năng lọc linh hoạt.

1. Cú Pháp Của Hàm FILTER

Cú pháp của hàm FILTER khá đơn giản và dễ hiểu:

FILTER(range, condition1, [condition2, ...])

• range: Vùng dữ liệu bạn muốn lọc. Đây là tham số bắt buộc.
• condition1: Một cột hoặc hàng cùng với tiêu chí TRUE/FALSE mà dữ liệu cần đáp ứng. Đây là tham số bắt buộc.
• condition2,...: Các cột/hàng và/hoặc tiêu chí bổ sung. Đây là các tham số tùy chọn.

Lưu ý quan trọng: Mỗi condition phải có cùng kích thước với range. Nếu bạn lọc theo nhiều điều kiện, tất cả các điều kiện này phải áp dụng cho cột hoặc hàng. Hàm FILTER không cho phép kết hợp điều kiện cột và hàng.

II. Sử Dụng Hàm FILTER Với Dữ Liệu Văn Bản

Hãy cùng khám phá cách áp dụng hàm FILTER với các loại dữ liệu văn bản thông qua một bảng dữ liệu đơn giản về các đơn hàng:

Bảng dữ liệu đơn hàng để lọc

1. Lọc Văn Bản Chính Xác

Để lấy ra các đơn hàng có trạng thái chính xác là “Late”, bạn thực hiện như sau:

=FILTER(A2:E15,E2:E15="Late")

Công thức này sẽ trả về tất cả các hàng trong vùng A2:E15 mà cột E có giá trị là “Late”.

Lọc Google Sheets theo văn bản chính xác

2. Lọc Văn Bản Không Trùng Khớp

Để lấy tất cả các đơn hàng trừ những đơn hàng bị “Late”, bạn sử dụng toán tử “không bằng” (<>):

=FILTER(A2:E15,E2:E15<>"Late")

Công thức này sẽ trả về tất cả các hàng mà cột E không có giá trị là “Late”.

Lấy tất cả các hàng có cột E khác với một từ nhất định

3. Lọc Văn Bản Chứa (Khớp Một Phần)

Để lọc các đơn hàng có chứa một phần văn bản nhất định, bạn có thể sử dụng kết hợp hàm SEARCH (không phân biệt chữ hoa/thường) hoặc FIND (phân biệt chữ hoa/thường). Ví dụ, lọc các đơn hàng từ Canada (CA) dựa trên mã đơn hàng ở cột A:

=FILTER(A2:E15,SEARCH("ca",A2:A15,8))

Trong đó, SEARCH("ca",A2:A15,8) tìm kiếm chuỗi “ca” trong vùng A2:A15 bắt đầu từ ký tự thứ 8.

Hàm FILTER Google Sheets chứa văn bản

III. Sử Dụng Tham Chiếu Ô Để Lọc

Việc sử dụng tham chiếu ô giúp công thức của bạn trở nên linh hoạt hơn. Thay vì nhập trực tiếp điều kiện, bạn có thể trỏ đến một ô chứa điều kiện đó. Ví dụ, để lọc các đơn hàng “Late” bằng cách tham chiếu đến ô E4 chứa văn bản “Late”:

=FILTER(A2:E15,E2:E15=E4)

Bạn cũng có thể áp dụng điều này cho ngày tháng hoặc các giá trị khác. Nếu bạn cần lọc dữ liệu từ một trang tính khác, hãy thêm tên trang tính vào trước tham chiếu ô, ví dụ:

=FILTER(Orders!A2:E15,Orders!C2:C15=Orders!C15)

Tham chiếu ô điều kiện trong hàm FILTER Google Sheets

IV. Lọc Theo Ngày Tháng và Thời Gian

Để lọc theo ngày tháng và thời gian, bạn cần kết hợp hàm FILTER với các hàm như DATE, MONTH, YEAR, TIME.

1. Lọc Theo Ngày Cụ Thể

Để tìm các đơn hàng có ngày đến hạn là ngày 9 tháng 1 năm 2020:

=FILTER(A2:E15,C2:C15=DATE(2020,1,9))

Hàm FILTER Google Sheets với hàm DATE

2. Lọc Theo Tháng Hoặc Năm

Để chỉ lấy các đơn hàng trong tháng 1:

=FILTER(A2:E15,MONTH(C2:C15)=1)

Tương tự, bạn có thể dùng hàm YEAR để lọc theo năm.

Lấy tất cả bản ghi cho tháng 1

3. Lọc Trước/Sau Một Ngày Cụ Thể

Để lọc các đơn hàng đến hạn từ ngày 1 tháng 1 năm 2020 trở đi:

=FILTER(A2:E15,D2:D15>=DATE(2020,1,1))

Bạn cũng có thể sử dụng hàm YEAR để lọc theo năm tương tự.

Tìm đơn hàng đến sau một ngày cụ thể

4. Lọc Theo Thời Gian

Để lọc các bản ghi có thời gian sau 2:00 PM:

=FILTER(A2:B10,A2:A10>TIME(14,0,0))

Để lọc theo giờ, bạn có thể sử dụng hàm HOUR:

=FILTER(A2:B10,HOUR(A2:A10)>=HOUR("2:00:00 PM"))

Cách sử dụng hàm HOUR trong hàm FILTER

V. Lọc Theo Màu Sắc Với Công Thức Tùy Chỉnh

Hàm FILTER có thể xử lý màu sắc bằng sự trợ giúp của hàm tùy chỉnh CELLCOLOR từ tiện ích mở rộng “Function by Color”. Ví dụ, để lọc các đơn hàng được tô màu tím nhạt (tượng trưng cho đơn hàng quan trọng nhất):

=FILTER(A2:E15,ArrayFormula(CELLCOLOR(A2:A15,"fill",TRUE)="light purple 3"))

Lưu ý: Nếu bạn thay đổi màu sắc trong dữ liệu gốc, kết quả lọc sẽ không tự động cập nhật. Bạn cần làm mới kết quả bằng cách vào Extensions > Function by Color > Refresh results.

Lọc và trích xuất dữ liệu theo màu bằng công thức tùy chỉnh

VI. Lọc Với Nhiều Tiêu Chí

1. Hàm FILTER Kết Hợp AND

Để lọc các đơn hàng có giá trị từ $200 đến dưới $350:

=FILTER(A2:E15,B2:B15>=200,B2:B15<350)

Cách lọc trên Google Sheets với nhiều tiêu chí

2. Hàm FILTER Với Điều Kiện OR

Để lọc các đơn hàng có trạng thái là “On time” HOẶC “On the way”:

=FILTER(A2:E15,(E2:E15="On time")+(E2:E15="On the way"))

Hàm FILTER Google Sheets cho điều kiện OR

3. Hàm FILTER Với Nhiều Cột

Để lọc các đơn hàng có giá trị từ $200 đến $400, đến hạn trong tháng 1 năm 2020 và đang “on the way”:

=FILTER(A2:E15,B2:B15>=200,B2:B15<=400,MONTH(C2:C15)=1,E2:E15="on the way")

Lọc dựa trên nhiều cột

VII. Lọc Từ Trang Tính Khác

Để lọc dữ liệu từ một trang tính khác, bạn chỉ cần thêm tên trang tính vào trước mỗi tham chiếu ô. Nếu tên trang tính có nhiều hơn một từ, hãy đặt nó trong dấu ngoặc đơn:

=FILTER(Orders!A2:E15,Orders!C2:C15=Orders!C15)

=FILTER('May orders'!A2:E15,'May orders'!C2:C15='May orders'!C15)

Sử dụng tên trang tính khác cho tất cả các tham chiếu ô để lọc từ trang tính đó

VIII. Lọc Từ Tệp Google Khác Với IMPORTRANGE

Để lọc từ một tệp Google Sheets khác, bạn sử dụng hàm IMPORTRANGE. Bạn cần cung cấp ID của tệp nguồn và dải ô. Sau khi nhập công thức, bạn có thể cần cấp quyền truy cập.

=FILTER(IMPORTRANGE("YOUR_SPREADSHEET_ID","Orders!A2:E15"),IMPORTRANGE("YOUR_SPREADSHEET_ID","Orders!E2:E15")="Late")

Cho phép truy cập IMPORTRANGEKết hợp IMPORTRANGE để lọc dữ liệu từ một tệp Google Sheets khác

IX. Xử Lý Lỗi Với Hàm FILTER

1. Lỗi #REF!

• Tham chiếu ô không hợp lệ: Kiểm tra lại tất cả các dải ô và tên trang tính.
• IMPORTRANGE không truy cập được tệp: Đảm bảo bạn đã kết nối đúng tệp và ID tệp chính xác.

2. Lỗi #N/A

• Điều kiện không chính xác: Kiểm tra kỹ các tiêu chí của bạn.
• Không có kết quả khớp: Đảm bảo có dữ liệu thực sự khớp với điều kiện bạn đặt ra.
• Tên hàm sai: Ví dụ, gõ nhầm IMPORTRANGE thành IMPPORTRANGE.

3. Lỗi #VALUE!

• Kích thước dải ô khác nhau: Đảm bảo tất cả các dải ô trong range và các condition có cùng kích thước.

4. Lỗi #NAME?

• Tiện ích mở rộng không được cài đặt: Nếu bạn dùng CELLCOLOR, hãy đảm bảo tiện ích “Function by Color” đã được cài đặt.
• Cài đặt vùng miền (Locale): Kiểm tra cài đặt vùng miền trong File > Settings.

5. Thay Thế Lỗi Bằng Văn Bản Tùy Chỉnh

Sử dụng hàm IFERROR để hiển thị văn bản tùy chỉnh khi có lỗi xảy ra:

=IFERROR(FILTER(A2:E15,E2:E15="Arrived"),"Không tìm thấy kết quả phù hợp")

IFERROR với hàm FILTER

X. Giải Pháp Lọc Không Cần Công Thức

Nếu bạn thấy việc sử dụng công thức quá phức tạp, tiện ích “Filter & Extract Data for Google Sheets” là một giải pháp thay thế tuyệt vời. Công cụ này cho phép bạn lọc dữ liệu một cách trực quan với giao diện người dùng thân thiện, không yêu cầu kiến thức sâu về công thức.

• Chọn điều kiện dễ dàng: Chỉ cần chọn từ danh sách thay vì nhập toán tử.
• Nhập ngày tháng tự nhiên: Không cần các hàm MONTH hay YEAR.
• Thêm/xóa nhiều điều kiện nhanh chóng.
• Xem trước kết quả trước khi áp dụng.
• Xuất kết quả dưới dạng giá trị hoặc công thức.

XI. Tệp Trang Tính Với Các Ví Dụ Công Thức

Bạn có thể thực hành với các ví dụ công thức tại đây: Google Sheets FILTER function (Hãy tạo bản sao để chỉnh sửa).

Hàm FILTER là một công cụ vô cùng hữu ích, giúp bạn quản lý và trích xuất dữ liệu một cách hiệu quả trong Google Sheets. Bằng cách nắm vững các cú pháp và kết hợp với các hàm khác, bạn có thể giải quyết hầu hết các nhu cầu lọc dữ liệu phức tạp.