Chúng ta có thể sử dụng kết quả của một hàm lọc như tiêu chí/điều kiện trong một hàm lọc khác trong Google Sheets.
Nếu kết quả của hàm lọc, được sử dụng như tiêu chí trong hàm lọc khác, là một giá trị đơn, bạn sẽ không gặp bất kỳ vấn đề nào trong việc sử dụng như vậy.
Nhưng trong trường hợp có nhiều giá trị trong kết quả của công thức lọc, thì có một phương pháp khác trong việc sử dụng tiêu chí!
Với một ví dụ chi tiết, chúng ta có thể tìm hiểu cách sử dụng kết quả của hàm lọc làm tiêu chí trong một hàm lọc khác trong Google Sheets.
Cách sử dụng kết quả của hàm lọc làm tiêu chí trong một hàm lọc khác trong Google Sheets
Việc lọc một tập dữ liệu (nhỏ hoặc lớn) có thể được thực hiện bằng cách sử dụng các phương pháp khác nhau trong Google Sheets. Đối với điều này, chúng ta có thể sử dụng hai lệnh menu cũng như hai hàm.
Những gì chúng ta có?
- Lệnh menu (tùy chọn menu) để lọc các tập dữ liệu trong Google Sheets là Slicer và Create a filter. Cả hai đều có sẵn trong menu Data trong Sheets.
- Hai hàm đó là Query và Filter. Query phức tạp và tốt hơn hàm Filter.
Học cách sử dụng Query là điều cần thiết để xử lý các tập dữ liệu lớn một cách dễ dàng. Tuy nhiên, bất kỳ khi nào có thể, hãy sử dụng hàm Filter vì được cho là tiết kiệm tài nguyên hơn trong Sheets.
Hãy quay lại vấn đề cần giải quyết hoặc kịch bản.
Tôi có hai bảng. Một bảng chứa danh sách các loại trái cây và số lượng tương ứng của chúng. Hãy gọi bảng này là Bảng 1.
Bảng thứ hai, gọi là Bảng 2, chứa cùng danh sách (nhưng chỉ chứa các loại trái cây duy nhất). Thay vì số lượng, nó chứa giá thành theo đơn vị.
Tôi muốn lọc Bảng 1 dựa trên Bảng 2. Tức là lọc các loại trái cây trong Bảng 1 nếu giá thành theo đơn vị trong Bảng 2 thấp hơn một giá trị cụ thể, ví dụ như <5.
Điều đó có nghĩa là trước tiên chúng ta cần lọc bảng thứ hai với tiêu chí, tức giá đơn vị <5.
Nếu bộ lọc chỉ trả về một tên trái cây duy nhất, thì không có vấn đề gì khi sử dụng nó làm điều kiện trong công thức lọc thứ hai để lọc Bảng 1. Nếu không, chúng ta phải sử dụng JOIN/TEXTJOIN và REGEXMATCH cùng với nhau.
Tất cả những điều này tôi sẽ giải thích chi tiết bên dưới. Trước đó, hãy xem hai bảng đó.
Bảng 1 và 2:
Lọc trong lọc trong Google Sheets – Một Tiêu chí đơn
Làm thế nào để lọc các loại trái cây trong Bảng 1 có giá thành theo đơn vị <2 trong Bảng 2?
Khi bạn kiểm tra giá thành theo đơn vị (cột E) trong Bảng 2, bạn có thể thấy rằng chỉ có một loại trái cây nằm trong khoảng giá đó, tức là “watermelon”.
Công thức #1:
=filter(A3:B,A3:A="watermelon")
Công thức trên sẽ lọc Bảng 1 nếu các hàng trong dải A3:A khớp với “watermelon”.
Trong công thức trên, “watermelon” là tiêu chí/điều kiện. Chúng ta có thể thay thế nó bằng một công thức lọc khác.
Công thức sau sẽ truy xuất mục “watermelon” từ Bảng 2 vì nó là mục duy nhất với giá thành theo đơn vị <2.
Công thức #2:
=filter(D3:D9,E3:E9<2)
Điều đó có nghĩa là, chúng ta có thể thay thế tiêu chí “watermelon” trong Công thức Lọc #1 như dưới đây.
Công thức #3:
=filter(A3:B,A3:A=filter(D3:D9,E3:E9<2))
Trên đây là một ví dụ cơ bản về cách sử dụng kết quả của hàm lọc như tiêu chí/điều kiện trong một hàm lọc khác trong Google Sheets.
Chú ý
Trong công thức lọc trên (Công thức #3), bạn có thể đã nhận thấy tôi đã sử dụng một dải kín trong bộ lọc nội bộ. Hãy thay đổi D3:D9 và E3:E9 thành D3:D và E3:E tương ứng.
Công thức #4:
=filter(A3:B,A3:A=filter(D3:D,E3:E<2))
Công thức trên sẽ trả về một đầu ra trống!
Nguyên nhân là công thức lọc nội bộ không chỉ trả về “watermelon” mà còn trả về một số ô trống do sử dụng các dải mở (vô hạn).
Trong trường hợp như vậy, tức là sử dụng kết quả của một hàm lọc như tiêu chí trong một hàm lọc khác, rào lại kết quả lọc nội bộ để loại trừ những ô trống. Làm thế nào?
Hãy sử dụng bộ lọc dưới đây, loại bỏ các ô trống như tiêu chí.
Công thức #5:
=filter(D3:D,(E3:E<2)*(E3:E<>""))
Vì vậy công thức cuối cùng sẽ là;
Công thức #6:
=filter(A3:B,A3:A=filter(D3:D,(E3:E<2)*(E3:E<>"")))
Lọc trong lọc trong Google Sheets – Nhiều Tiêu chí
Chỉ cần thay đổi tiêu chí trong công thức lọc nội bộ thành <5.
Công thức #7:
=filter(D3:D,(E3:E<5)*(E3:E<>""))
Nó sẽ trả về các trái cây “apple”, “pineapple”, “banana” và “watermelon”. Đây là các điều kiện để lọc Bảng 1.
Nhưng công thức lọc trên như tiêu chí trong một hàm lọc như trước đây sẽ trả về một lỗi #N/A!.
Công thức #8:
=filter(A3:B,A3:A=filter(D3:D,(E3:E<5)*(E3:E<>"")))
Ở đây, để sử dụng một kết quả hàm lọc như tiêu chí trong một hàm lọc khác, chúng ta có thể sử dụng REGEXMATCH và JOIN trong Google Sheets.
Đọc thêm: Regexmatch in Filter Criteria in Google Sheets.
Nó giống như việc sử dụng toán tử IN (thay thế) trong Query.
Liên quan: The Alternative to SQL IN Operator in Google Sheets Query.
Các bước
Sử dụng hàm JOIN để kết hợp các tiêu chí nhiều (Công thức #7) như dưới đây.
Công thức #9:
=join("|",filter(D3:D,(E3:E<5)*(E3:E<>"")))
Tuy nhiên, nếu bạn muốn, bạn cũng có thể sử dụng như sau.
=textjoin("|",true,FILTER(D3:D,E3:E<5))
Trong công thức đầu tiên (dựa trên JOIN), hàm Filter chính loại bỏ các ô trống khỏi kết quả. Nhưng trong công thức thứ hai (dựa trên TEXTJOIN), hàm Textjoin loại bỏ các ô trống. Tôi thích công thức thứ hai hơn.
Thay thế A3:A=filter(D3:D,(E3:E<5)*(E3:E<>””)) trong Công thức #8 bằng =REGEXMATCH(A3:A,textjoin("|",true,FILTER(D3:D,E3:E<5))).
Vì vậy, công thức cuối cùng sẽ là;
Công thức #10:
=filter(A3:B,REGEXMATCH(A3:A,textjoin("|",true,FILTER(D3:D,E3:E<5))))
Đó là tất cả về cách sử dụng một công thức lọc như tiêu chí trong một công thức lọc khác trong Google Sheets. Cảm ơn mọi người đã đọc, chúc vui vẻ!
