Bạn đã biết cách lọc dữ liệu trong Google Sheets bằng cách sử dụng nút Lọc trên thanh công cụ. Đây là một phương pháp khá tiện dụng nhưng lại phải thao tác bằng tay. Vì vậy, nếu bạn muốn tự động hóa quá trình lọc dữ liệu trong Google Sheets, nên sử dụng hàm FILTRE. Trong hướng dẫn này, chúng tôi sẽ giải thích cách làm điều đó.
Hiểu về hàm FILTRE trong Google Sheets
Hàm FILTRE trong Google Sheets cho phép lọc các tập con dữ liệu từ một phạm vi dữ liệu đã được xác định dựa trên điều kiện cụ thể.
Hàm và tính năng lọc là hai khái niệm khác nhau. Hàm được sử dụng trong các công thức để lọc các phần dữ liệu con, trong khi tính năng là một nút trên thanh công cụ để lọc toàn bộ dữ liệu.
Cú pháp của hàm FILTRE trong Google Sheets
=FILTRE(phạm_vi_dữ_liệu, điều_kiện_1, điều_kiện_2, ...)phạm_vi_dữ_liệu: phạm vi các ô cần lọc. Ví dụ: A2:Ađiều_kiện: phạm vi các ô chứa giá trị TRUE hoặc FALSE cho các tiêu chí lọc. Các tiêu chí lọc thường chứa các toán tử so sánh (“=”, “<” và “>”), ví dụ: A2:A>20. Tuy nhiên, bạn cũng có thể sử dụng hàm REGEXMATCH trong điều kiện lọc.
Nếu một ô chứa các tiêu chí lọc, bạn có thể tham chiếu đến ô đó trong điều kiện lọc. Ví dụ: A2:A>C1. Để biết thêm thông tin về cách tham chiếu một phạm vi dữ liệu cụ thể từ một sheet khác, hãy đọc bài viết trên trang web của chúng tôi về cách liên kết dữ liệu giữa nhiều bảng tính.
Ví dụ về công thức FILTRE trong Google Sheets
Hãy xem xét ví dụ trong video “Google Sheets FILTER – 5 tips for beginners”. Video này cung cấp các thông tin cơ bản về hàm FILTRE, vì vậy chúng tôi rất đề nghị bạn xem nó.
Đây là một tập dữ liệu mà chúng tôi sẽ sử dụng:
Trong tập dữ liệu này, chúng ta cần lọc khách hàng chỉ từ khu vực EMEA (Châu Âu, Trung Đông và Châu Phi). Dưới đây là công thức để làm điều đó:
=FILTRE('Tập dữ liệu'!A2:A, 'Tập dữ liệu'!C2:C="EMEA")'Tập dữ liệu'!A2:A: cột chứa các khách hàng cần lọc.'Tập dữ liệu'!C2:C="EMEA": điều kiện lọc: tất cả các giá trị “EMEA” trong cột Khu vực.
Đây là ví dụ về công thức FILTRE đơn giản nhất với một điều kiện duy nhất. Tuy nhiên, bạn có thể lọc theo nhiều điều kiện, kết hợp FILTRE với các hàm khác trong Google Sheets và nhiều hơn nữa.
FILTRE theo nhiều điều kiện (toán tử VÀ)
Lưu ý: Hàm FILTRE trong Google Sheets không hỗ trợ các điều kiện kết hợp: các điều kiện chỉ có thể là theo cột hoặc theo hàng.
FILTRER bằng cách sử dụng toán tử VÀ có nghĩa là công thức sẽ trả về kết quả nếu tất cả các điều kiện được chỉ định được đáp ứng.
Cú pháp của công thức FILTRE theo nhiều điều kiện (toán tử VÀ) trong Google Sheets
=FILTRE(phạm_vi_dữ_liệu, điều_kiện_1, điều_kiện_2, điều_kiện_3, ...)Ví dụ về công thức FILTRE theo nhiều điều kiện (toán tử VÀ) trong Google Sheets
Giả sử bạn muốn lọc khách hàng (‘Tập dữ liệu mẫu’!A2:A) theo ba điều kiện sau:
- Loại thuê bao: Squad (‘Tập dữ liệu mẫu’!B2:B=”Squad”)
- Quốc gia: Ba Lan (‘Tập dữ liệu mẫu’!C2:C=”Pologne”)
- Số tiền thanh toán: nhỏ hơn 100,000đ (‘Tập dữ liệu mẫu’!F2:F<100000)
Dưới đây là công thức:
=FILTRE( 'Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!B2:B="Squad", 'Tập dữ liệu mẫu'!C2:C="Pologne", 'Tập dữ liệu mẫu'!F2:F<100000 )'Tập dữ liệu mẫu'!A2:A: cột chứa các khách hàng cần lọc.'Tập dữ liệu mẫu'!B2:B="Squad": điều kiện lọc: tất cả các giá trị “Squad” trong cột Loại thuê bao.'Tập dữ liệu mẫu'!C2:C="Pologne": điều kiện lọc: tất cả các giá trị “Pologne” trong cột Quốc gia.'Tập dữ liệu mẫu'!F2:F<100000: điều kiện lọc: giá trị nhỏ hơn 100,000đ trong cột Số tiền thanh toán.
Đó là ví dụ về công thức FILTRE theo nhiều điều kiện, kết hợp toán tử VÀ trong Google Sheets.
FILTRE theo nhiều điều kiện (toán tử HOẶC)
FILTRER bằng cách sử dụng toán tử HOẶC có nghĩa là công thức FILTRE sẽ trả về kết quả nếu một hoặc tất cả các điều kiện được chỉ định được đáp ứng.
Cú pháp của công thức FILTRE theo nhiều điều kiện (toán tử HOẶC) trong Google Sheets
=FILTRE(phạm_vi_dữ_liệu, (điều_kiện_1.1)+(điều_kiện_1.2), điều_kiện_2, (điều_kiện_3.1)+(điều_kiện_3.2), ...)Ví dụ về công thức FILTRE theo nhiều điều kiện (toán tử HOẶC) trong Google Sheets
Giả sử bạn cần lọc khách hàng (‘Tập dữ liệu mẫu’!A2:A) theo các điều kiện khác nhau cùng một cột:
- Loại thuê bao: Squad (‘Tập dữ liệu mẫu’!B2:B=”Squad”)
- Quốc gia: Ba Lan (‘Tập dữ liệu mẫu’!C2:C=”Pologne”) hoặc Thụy Điển (‘Tập dữ liệu mẫu’!C2:C=”Thụy Điển”)
- Số tiền thanh toán: nhỏ hơn 100,000đ (‘Tập dữ liệu mẫu’!F2:F<100000) hoặc lớn hơn 50,000đ (‘Tập dữ liệu mẫu’!F2:F>50000)
Dưới đây là công thức:
=FILTRE( 'Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!B2:B="Squad", ('Tập dữ liệu mẫu'!C2:C="Pologne")+ ('Tập dữ liệu mẫu'!C2:C="Thụy Điển"), ('Tập dữ liệu mẫu'!F2:F<100000)+ ('Tập dữ liệu mẫu'!F2:F>50000) )'Tập dữ liệu mẫu'!A2:A: cột chứa các khách hàng cần lọc.'Tập dữ liệu mẫu'!B2:B="Squad": điều kiện lọc: tất cả các giá trị “Squad” trong cột Loại thuê bao.('Tập dữ liệu mẫu'!C2:C="Pologne")+ ('Tập dữ liệu mẫu'!C2:C="Thụy Điển"): điều kiện lọc: tất cả các giá trị “Pologne” hoặc “Thụy Điển” trong cột Quốc gia.('Tập dữ liệu mẫu'!F2:F<100000)+ ('Tập dữ liệu mẫu'!F2:F>50000): điều kiện lọc: giá trị nhỏ hơn 100,000đ hoặc lớn hơn 50,000đ trong cột Số tiền thanh toán.
Đây là ví dụ về công thức FILTRE theo nhiều điều kiện, kết hợp toán tử HOẶC trong Google Sheets.
Nhập dữ liệu vào Google Sheets và lọc mà không sử dụng các công thức
Bây giờ bạn đã biết cách lọc dữ liệu trong Google Sheets với hàm FILTRE, nhưng nếu bạn cần trước tiên nhập dữ liệu từ một bảng tính khác hoặc nguồn ngoại部, thì sẽ mất thời gian để tải dữ liệu vào bảng tính của bạn, sau đó áp dụng bộ lọc. Tuy nhiên, bạn có thể sử dụng Coupler.io để lọc dữ liệu trực tiếp mà không cần sử dụng các công thức, bằng cách nhập dữ liệu từ hơn 50 nguồn khác nhau.
Hãy thử ngay với biểu mẫu bên dưới. Để bắt đầu, hãy chọn nguồn dữ liệu và nhấp vào “Tiếp tục”. Bạn có thể đăng ký miễn phí với tài khoản Google của mình.
Hãy xem cách bạn có thể lọc dữ liệu từ bảng tính đã đề cập ở trên.
Hãy chọn hàm FILTRE như là tệp nguồn. Sau đó, chỉ định bảng tính của bạn – Tập dữ liệu mẫu. Bạn cũng có thể chọn một phạm vi cụ thể mà bạn muốn xuất.
Ở bước tiếp theo, bạn có thể lọc dữ liệu của mình theo một hoặc nhiều tiêu chí trước khi tải dữ liệu nguồn vào bảng tính của bạn. Ví dụ, nhấp vào “Lọc” sau đó nhấp vào “VÀ” để lọc các khách hàng hoạt động từ Trung Quốc với thuê bao Chuyên gia:
- Chọn cột tên có tên “Quốc gia”, chỉ định điều kiện “là” và nhập “Trung Quốc” làm giá trị.
- Nhấp vào “+VÀ”, chọn cột có tên “Loại thuê bao”, chỉ định điều kiện “là”, sau đó nhập “Chuyên gia” làm giá trị.
- Nhấp vào “+VÀ”, chọn cột có tên “Tình trạng khách hàng”, chỉ định điều kiện “là”, sau đó nhập “Hoạt động” làm giá trị.
Hãy thử lọc các khách hàng từ Trung Quốc nhưng lần này với loại thuê bao miễn phí và tình trạng khách hàng bị mất:
- Nhấp vào “HOẶC”.
- Chọn cột có tên “Quốc gia”, chỉ định điều kiện “là”, sau đó nhập “Trung Quốc” làm giá trị.
- Nhấp vào “+VÀ”, chọn cột có tên “Loại thuê bao”, chỉ định điều kiện “là”, sau đó nhập “Miễn phí” làm giá trị.
- Nhấp vào “+VÀ”, chọn cột có tên “Tình trạng khách hàng”, chỉ định điều kiện “là”, sau đó nhập “Bị mất” làm giá trị.
- Nhấp vào “Áp dụng bộ lọc”.
Ngoài ra, bạn có thể ẩn, thêm và chỉnh sửa các cột, cũng như sắp xếp dữ liệu theo thứ tự tăng dần hoặc giảm dần.
Khi dữ liệu đã được biến đổi, điều hướng đến cài đặt đích. Bạn cần chọn tệp và bảng tính để nhập dữ liệu yêu cầu.
Bạn cũng có thể chọn tần suất cập nhật dữ liệu để bảng tính của bạn được cập nhật đều đặn. Điều này sẽ đảm bảo bạn không bỏ lỡ bất kỳ thay đổi nào trong dữ liệu nguồn của mình.
Cuối cùng, nhấp vào “Chạy importer”. Trong nháy mắt, dữ liệu của bạn sẽ có sẵn trong bảng tính đích.
Với Coupler.io, bạn có thể tiết kiệm thời gian, tránh sai sót con người và truy cập vào dữ liệu mới nhất bạn cần.
Lỗi công thức FILTRE trong Google Sheets: không tìm thấy sự khớp trong đánh giá FILTRE
Lỗi “Không tìm thấy sự khớp” của hàm FILTRE xảy ra khi không có giá trị nào khớp với các điều kiện đã chỉ định. Ví dụ, chúng ta biết rằng không có khách hàng nào từ Đức có số tiền thanh toán nhỏ hơn 100,000đ. Vì vậy, công thức sau sẽ trả về “#N/A”:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!B2:B="Squad", 'Tập dữ liệu mẫu'!C2:C="Đức", 'Tập dữ liệu mẫu'!F2:F<100000)Để công thức FILTRE trả về ô trống thay vì lỗi “#N/A”, hãy thêm hàm IFERROR vào đầu công thức:
=IFERROR(FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!B2:B="Squad", 'Tập dữ liệu mẫu'!C2:C="Đức", 'Tập dữ liệu mẫu'!F2:F<100000),"")FILTRE theo số trong Google Sheets
Dưới đây là các điều kiện bạn có thể áp dụng để lọc các giá trị số:
Điều kiện lọc trong Google Sheets: Bằng X
Giả sử bạn muốn lọc tất cả khách hàng với số tiền thanh toán bằng 56,298đ:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!F2:F=56298)Điều kiện lọc trong Google Sheets: Lớn hơn (“>”) X
Giả sử bạn muốn lọc tất cả khách hàng với số tiền thanh toán lớn hơn 56,298đ:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!F2:F>56298)Điều kiện lọc trong Google Sheets: Nhỏ hơn (“<“) X
Giả sử bạn muốn lọc tất cả khách hàng với số tiền thanh toán nhỏ hơn 56,298đ:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!F2:F<56298)Điều kiện lọc trong Google Sheets: Khác X
Giả sử bạn muốn lọc tất cả khách hàng với số tiền thanh toán khác 56,298đ:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!F2:F<>56298)Điều kiện lọc trong Google Sheets: Giá trị chẵn
Giả sử bạn muốn lọc tất cả khách hàng với số tiền thanh toán là số chẵn:
=FILTRE('Tập dữ liệu mẫu'!A2:A, ISODD('Tập dữ liệu mẫu'!F2:F))Điều kiện lọc trong Google Sheets: Giá trị lẻ
Giả sử bạn muốn lọc tất cả khách hàng với số tiền thanh toán là số lẻ:
=FILTRE('Tập dữ liệu mẫu'!A2:A, ISEVEN('Tập dữ liệu mẫu'!F2:F))FILTRE theo văn bản trong Google Sheets (so khớp/không so khớp chính xác)
Lọc cơ bản theo văn bản bao gồm hai điều kiện:
- Trả về các giá trị chính xác giống với chuỗi văn bản đã chỉ định.
- Trả về các giá trị không chính xác giống với chuỗi văn bản đã chỉ định.
Ví dụ, giả sử bạn muốn lọc các khách hàng với trạng thái “mất”:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!E2:E="Mất")và những người có trạng thái khác với “mất”:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!E2:E<>"Mất")FILTRE theo văn bản trong Google Sheets (so khớp một phần)
Trong bài viết trên blog về COUNTIF vs. COUNTIFS, chúng tôi đã giải thích rằng bạn có thể sử dụng các ký tự đại diện (“?” và “*”) để kiểm tra số phần của các dữ liệu. Các ký tự đại diện không hoạt động với FILTRE. Tuy nhiên, các hàm SEARCH, FIND và REGEXMATCH được lồng ghép trong công thức FILTRE có thể làm nhiệm vụ.
Sử dụng hàm SEARCH để lọc theo văn bản được tìm kiếm với phân biệt chữ hoa chữ thường:
=SEARCH("văn_bản_cần_tìm_kiếm", phạm_vi_ô, [vị_trí_bắt_đầu])Ví dụ, lồng SEARCH vào FILTRE để lọc tất cả khách hàng có “oo” (tức là có hai chữ “o”) trong tên của họ:
=FILTRE('Tập dữ liệu mẫu'!A2:A, SEARCH("oo",'Tập dữ liệu mẫu'!A2:A))Sử dụng hàm FIND để lọc theo văn bản được tìm kiếm có phân biệt chữ hoa chữ thường:
=FIND("văn_bản_cần_tìm_kiếm", phạm_vi_ô, [vị_trí_bắt_đầu])Ví dụ, nếu văn bản trong tiêu chí lọc phân biệt chữ hoa chữ thường, hãy sử dụng hàm FIND. Hãy lọc tất cả khách hàng có “Oo” trong tên của họ.
=FILTRE('Tập dữ liệu mẫu'!A2:A, FIND("Oo",'Tập dữ liệu mẫu'!A2:A))Sử dụng FILTRE + REGEXMATCH để lọc nâng cao văn bản trong Google Sheets.
=REGEXMATCH(phạm_vi_ô,"biểu_thức_chính_quy")Ví dụ, hãy lọc tất cả khách hàng có “Oo” và “atz” trong tên của họ:
=FILTRE('Tập dữ liệu mẫu'!A2:A, REGEXMATCH('Tập dữ liệu mẫu'!A2:A,"Oo|atz"))FILTRE theo ngày và giờ trong Google Sheets
Để lọc các giá trị theo ngày (ngày đầy đủ, tháng, năm, v.v.), bạn cần kết hợp FILTRE với các hàm bổ sung.
Lưu ý: Nếu bạn chỉ cố gắng sử dụng một giá trị ngày làm chuỗi văn bản trong tiêu chí lọc, bạn sẽ nhận được lỗi “Không tìm thấy sự khớp”:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!G2:G="9/3/2020")Hãy làm đúng cách.
FILTRE theo ngày chính xác
Áp dụng hàm DATE trong công thức FILTRE như sau:
=FILTRE(phạm_vi_ô, phạm_vi_ô=DATE(YYYY,MM,DD))Ví dụ, công thức để lọc khách hàng theo ngày giao dịch là ngày 9 tháng 3 năm 2020 như sau:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!I2:I>=DATE(2020,3,9), 'Tập dữ liệu mẫu'!I2:I<DATE(2020,3,10))Trong tập dữ liệu của chúng ta, chúng ta có các giá trị ngày+giờ. Chia cột này thành hai cột riêng biệt (cho ngày và cho giờ) và xem cách lọc các giá trị này. Dưới đây là công thức để sử dụng:
={"Ngày","Giờ"; arrayformula(split(G2:G," "))}Bây giờ, chúng ta có thể áp dụng FILTRE+DATE để lọc khách hàng theo ngày giao dịch là ngày 9 tháng 3 năm 2020:
=FILTRE('Tập dữ liệu mẫu'!A2:A, 'Tập dữ liệu mẫu'!I2:I=DATE(2020,3,9))Đó là những ví dụ về việc lọc bằng FILTRE theo nhiều điều kiện, kết hợp toán tử VÀ và HOẶC trong Google Sheets.
