Bạn đã từng gặp phải việc lọc dữ liệu trong Google Sheets và bạn cần tìm hiểu cách lọc các nhóm thỏa ít nhất một điều kiện/cấu trúc dữ liệu trong Google Sheets.
Hãy thảo luận về việc lọc dòng và việc lọc nhóm. Đầu tiên, hãy hiểu sự khác biệt giữa hai khái niệm này và sau đó bạn có thể tìm hiểu cách lọc các nhóm thỏa ít nhất một điều kiện trong Google Sheets.
Dữ liệu mẫu của tôi được sử dụng để giúp bạn hiểu rõ hơn về cách lọc dòng. Dữ liệu này chỉ là dữ liệu mẫu với ba cột có tên là “Mã mặt hàng”, “Hàng tồn kho” và “Số lượng”.
Đầu tiên, tôi sẽ lọc các dòng. Dưới đây là bộ dữ liệu mẫu tôi sử dụng trong toàn bộ hướng dẫn này.
| Mã mặt hàng | Hàng tồn kho | Số lượng |
|---|---|---|
| 2A1111 | NO | 10 |
| 3A1111 | NO | 15 |
| 4A1111 | YES | 6 |
| 5A1111 | NO | 8 |
| 6A2222 | NO | 16 |
| 7A2222 | NO | 20 |
| 8A2222 | NO | 25 |
| 9A3333 | NO | 14 |
| 10A3333 | YES | 10 |
| 11A3333 | YES | 15 |
Tất nhiên, bạn có thể sử dụng một bộ dữ liệu hoàn toàn khác với số lượng dòng và cột tùy ý. Nhưng trước tiên, hãy theo dõi bộ dữ liệu trên.
Dưới đây là công thức FILTER để lọc tất cả các dòng chứa “YES”, đó là điều kiện của chúng ta, trong cột B.
=filter(A2:C,B2:B="YES")
Công thức lọc trên sẽ lọc dòng #5, #10 và #11 từ bộ dữ liệu trên. Xem cột “Hàng tồn kho” (cột 2) để tìm hiểu lý do. Vậy còn việc lọc nhóm thì sao?
Thực tế là chúng ta muốn lọc bất kỳ một nhóm (hoặc nhiều hơn) nào như dưới đây.
=filter(A2:C,A2:A="A2222")
Công thức lọc trên sẽ lọc ra dòng #6, #7 và #8. Hy vọng bạn đã hiểu được sự khác biệt giữa việc lọc dòng và việc lọc nhóm trong Google Sheets.
Cách lọc các nhóm thỏa ít nhất một điều kiện trong Sheets
Trong việc lọc các nhóm thỏa ít nhất một điều kiện, chúng ta không sẽ lọc bất kỳ nhóm cụ thể nào như trên (công thức thứ hai ở trên). Thay vào đó, chúng ta sẽ lọc tất cả các nhóm thỏa ít nhất một điều kiện trong cột thứ hai. Điều kiện này là “YES” trong cột B.
Ở đây, có ba nhóm là “A1111”, “A2222” và “A3333”. Trong ba nhóm này, nhóm đầu tiên và cuối cùng có ít nhất một điều kiện phù hợp, tức là “YES” trong cột B.
Công thức lọc của tôi, là một công thức kết hợp, trong ô E1 sẽ lọc các nhóm phù hợp như đã nêu.
Công thức Google Sheets để lọc nhóm các dòng phù hợp với một tiêu chí:
Tôi đã sử dụng một kết hợp Vlookup và Filter để lọc nhóm như trên. Ở ô E2, tôi có công thức kết hợp dưới đây.
=filter(A2:C,IFNA(vlookup(A2:A,filter(A2:A,B2:B="YES"),1,0))<>"")
Tôi sẽ giải thích cách công thức này lọc nhóm dựa trên một tiêu chí phù hợp. Có ba bước được thực hiện.
Bước 1: Lọc các dòng (chỉ cột đầu tiên) phù hợp với một tiêu chí
Trong công thức chính trên, hãy xem công thức Lọc bên trong Vlookup. Nó như sau.
filter(A2:A,B2:B="YES")
Nó lọc các dòng và trả về cột đầu tiên nếu giá trị cột B là “YES”.
Bước 2: Vlookup nhóm phù hợp ít nhất một điều kiện trong Google Sheets
Công thức Lọc ở trên giúp chúng ta Vlookup nhóm phù hợp ít nhất một điều kiện.
Công thức:
=ArrayFormula(IFNA(vlookup(A2:A,filter(A2:A,B2:B="YES"),1,0)))
Giải thích:
Công thức Vlookup tìm kiếm các khóa (nhóm) nằm trong cột A2:A trong cột đầu tiên của đầu ra lọc (công thức bước #1).
VLOOKUP(search_key, range, index, [is_sorted])
Tôi nghĩ rằng ‘search_keys’ nằm trong A2:A và ‘range’ là công thức bước #1. Cột ‘index’ (cột đầu ra Vlookup từ ‘range’) là 1.
Vì đầu ra lọc (‘range’) thiếu khóa “A2222”, Vlookup sẽ trả về N/A trong dòng #6, #7 và #8, mà tôi đã loại bỏ bằng cách sử dụng IFNA bên ngoài Vlookup.
Lưu ý: Trước đây, tôi đã sử dụng IFERROR thay vì IFNA. IFNA là một hàm ‘mới’ (tôi mới nhận thấy gần đây) trong Sheets. Tôi khuyến nghị bạn sử dụng IFNA thay vì IFERROR trong Vlookup vì chúng ta chỉ đơn giản muốn loại bỏ lỗi N/A.
Bước 3: Công thức kết hợp cuối cùng
Để lọc nhóm thỏa ít nhất một điều kiện, đơn giản là lọc phạm vi A1:C bằng cách sử dụng đầu ra Công thức #2 là tiêu chí. Nó phải được sử dụng như sau. Xem <>”” ở phần cuối công thức này.
IFNA(vlookup(A2:A,filter(A2:A,B2:B="YES"),1,0))<>""
Điều đó có nghĩa là lọc tất cả các dòng, bất kể nơi đầu ra công thức #2 không trống.
Hy vọng những hình ảnh và giải thích trên sẽ giúp bạn hiểu rõ cách tôi đã kết hợp các công thức này. Chúc bạn thành công!
Nếu bạn muốn tìm hiểu thêm về danh sách các công thức Google Sheets hữu ích khác, hãy ghé thăm Crawlan.com.
