Nội dung chính
Nếu cách duy nhất bạn biết để tạo bộ lọc trong Google Sheets là công cụ tiêu chuẩn, tôi có một bất ngờ dành cho bạn. 🙂 Hãy khám phá chức năng LỌC với tôi. Có rất nhiều công thức làm sẵn mà bạn có thể mượn, cùng với một công cụ mạnh mẽ mới bổ sung cho bộ công cụ lọc vô cùng.
Cách đây một thời gian, chúng tôi đã giải thích cách lọc trong Google Sheets bằng công cụ tiêu chuẩn. Chúng tôi đã đề cập làm thế nào để lọc theo giá trị và theo điều kiện. Tuy nhiên, bảng tính luôn có nhiều trong chúng hơn chúng ta biết. Và lần này tôi sẽ cùng bạn khám phá chức năng Google Sheets LỌC.
Bạn sẽ không tìm thấy nó trong Excel, vì vậy nó chắc chắn đáng để kiểm tra.
Cú pháp của hàm Google Sheets LỌC
LỌC trong Google Sheets quét dữ liệu của bạn và trả về thông tin bắt buộc đáp ứng tiêu chí của bạn.
Không giống như bộ lọc Google Sheets tiêu chuẩn, chức năng này không làm gì với dữ liệu gốc của bạn. Nó sao chép các hàng tìm thấy và đặt chúng bất cứ nơi nào bạn xây dựng công thức.
Cú pháp khá dễ vì mỗi đối số tự nói lên:
= LỌC (phạm vi, điều kiện1, [condition2, …])
- phạm vi là dữ liệu bạn muốn lọc. Cần thiết.
- điều kiện1 là một cột hoặc hàng cùng với tiêu chí TRUE / FALSE mà nó phải nằm trong. Cần thiết.
- điều kiện2, …, v.v., đại diện cho các cột / hàng khác và / hoặc các tiêu chí khác. Không bắt buộc.
Ghi chú. Mỗi tình trạng nên có cùng kích thước với phạm vi.
Ghi chú. Nếu bạn sử dụng nhiều điều kiện, tất cả chúng phải là cho cột hoặc hàng. Chức năng Google Sheets LỌC không cho phép các điều kiện hỗn hợp.
Bây giờ, với những lưu ý này, hãy xem cách các đối số hình thành các công thức khác nhau.
Cách sử dụng chức năng LỌC trong Google Sheets
Tôi sẽ cho bạn xem tất cả các ví dụ trong khi lọc một bảng nhỏ nơi tôi theo dõi một số đơn hàng:
Bảng chứa 20 hàng với nhiều loại dữ liệu hoàn hảo để tìm hiểu hàm.
Cách lọc trong Google Sheets bằng văn bản
Ví dụ 1. Văn bản chính xác
Đầu tiên, tôi sẽ yêu cầu chức năng chỉ hiển thị những đơn hàng đang trễ. Tôi nhập phạm vi để lọc – A1: E20 – và sau đó đặt điều kiện – cột E nên bằng Muộn:
=FILTER(A1:E20,E1:E20="Late")
Ví dụ 2. Văn bản chính xác là không
Tôi có thể yêu cầu chức năng nhận cho tôi tất cả các đơn đặt hàng nhưng những đơn hàng bị trễ. Vì thế, tôi sẽ cần một toán tử so sánh đặc biệt (<>) có nghĩa là không bằng:
=FILTER(A1:E20,E1:E20<>"Late")
Ví dụ 3. Văn bản chứa
Bây giờ tôi muốn chỉ cho bạn cách xây dựng chức năng Google Sheets LỌC dựa trên khớp một phần. Hay nói cách khác – nếu văn bản chứa.
Bạn có để ý rằng ID đơn hàng trong cột A chứa chữ viết tắt quốc gia ở cuối không? Hãy tạo một công thức để chỉ lấy các đơn đặt hàng được vận chuyển từ Canada (CA).
Thông thường, bạn sẽ sử dụng ký tự đại diện cho nhiệm vụ này. Nhưng khi nói đến công thức LỌC, đó là các hàm TÌM và TÌM KIẾM hoạt động theo cách này.
Ghi chú. Nếu trường hợp văn bản là quan trọng, hãy sử dụng TÌM, nếu không, hãy chọn TÌM KIẾM.
Hàm SEARCH sẽ làm tốt cho ví dụ của tôi vì trường hợp văn bản không liên quan:
= TÌM KIẾM (search_for, text_to_search, [starting_at])
- tìm kiếm là văn bản tôi muốn tìm. Nó thực sự quan trọng để bọc nó với dấu ngoặc kép: “ca”. Cần thiết.
- text_to_search là phạm vi để quét các văn bản cần thiết. Cần thiết. nó là A1: A20 cho tôi.
- bắt đầu tại cho biết vị trí bắt đầu tìm kiếm – số lượng ký tự bắt đầu tìm kiếm. Nó hoàn toàn tùy chọn nhưng tôi cần sử dụng nó. Bạn thấy, tất cả các ID đơn hàng bao gồm các chữ cái và số, có nghĩa là một cặp CA có thể xảy ra ở đâu đó ở giữa. Mẫu giống hệt nhau của tất cả các ID cho phép tôi tìm kiếm CA bắt đầu từ nhân vật thứ 8
Sau khi thu thập tất cả các phần này với nhau, tôi nhận được kết quả mong muốn:
=FILTER(A1:E20,SEARCH("ca",A1:A20,8))
Cách lọc theo ngày và giờ trong Google Sheets
Lọc theo ngày và thời gian cũng yêu cầu sử dụng các chức năng bổ sung. Tùy thuộc vào tiêu chí của bạn, bạn có thể cần phải nhúng NGÀY, THÁNG, NĂM hoặc thậm chí NGÀY và THỜI GIAN trong chức năng LỌC LỌC Google Sheets chính.
Ví dụ 1. Ngày là
Để nhận được các đơn đặt hàng đến hạn vào ngày 9 tháng 1 năm 2020, tôi sẽ mời chức năng NGÀY:
=FILTER(A1:E20,C1:C20=DATE(2020,1,9))
Ghi chú. Điều này chỉ hoạt động nếu các ô của bạn không chứa đơn vị thời gian cùng với ngày (bảng tính của bạn có thể thêm chúng theo mặc định). Để đảm bảo, chỉ cần chọn một ô và kiểm tra những gì xuất hiện trong thanh công thức:
Nếu thời gian ở đó và loại bỏ nó không phải là một tùy chọn, bạn nên sử dụng một trong hai TRUY VẤN hoặc một điều kiện phức tạp hơn trong chức năng Google Sheets LỌC của bạn, như thế này:
=FILTER(A1:E20,C1:C20>=DATE(2020,1,9),C1:C20<DATE(2020,1,10))
Ví dụ 2. Ngày chứa
Nếu bạn chỉ quan tâm đến một tháng hoặc một năm cụ thể, bạn có thể nhận được bằng các chức năng THÁNG và NĂM. Đặt phạm vi với ngày ngay vào đó (C1: C20) và chỉ định số tháng (hoặc năm) phải bằng (= 1):
=FILTER(A1:E20,MONTH(C1:C20)=1)
Ví dụ 3. Ngày là trước / sau
Để có được dữ liệu rơi trước hoặc sau ngày đã chỉ định, bạn sẽ cần hàm DATE và các toán tử so sánh đó lớn hơn (>), lớn hơn hoặc bằng (> =), nhỏ hơn (<), nhỏ hơn hoặc bằng đến (<=).
Dưới đây là các đơn đặt hàng đã nhận được vào và sau ngày 1 tháng 1 năm 2020:
=FILTER(A1:E20,D1:D20>=DATE(2020,1,1))
Tất nhiên, bạn có thể dễ dàng thay thế NGÀY bằng THÁNG hoặc NĂM tại đây. Kết quả sẽ không khác với kết quả ở trên:
=FILTER(A1:E20,YEAR(D1:D20)>=2020)
Ví dụ 4. Thời gian
Khi lọc trên Google Sheets theo thời gian, mũi khoan hoàn toàn giống với ngày. Bạn sử dụng chức năng TIME bổ sung.
Chẳng hạn, để chỉ nhận được vài ngày với dấu thời gian sau 2:00 PM, công thức sẽ là:
=FILTER(A1:B10,A1:A10>TIME(14,0,0))
Tuy nhiên, khi sử dụng chức năng HOUR (như với MONTH cho ngày), trò chơi sẽ thay đổi một chút. Thời gian là đủ khó khăn trong bảng tính, vì vậy một vài điều chỉnh là cần thiết.
Để trả về tất cả các hàng có dấu thời gian giữa 2:00 chiều và 12:00 chiều, làm cái này:
- Kèm theo phạm vi với dấu thời gian (A1: A10) trong một hàm HOUR riêng. Điều này sẽ chỉ ra nơi để tìm.
- Sau đó thêm một hàm HOUR khác để đặt thời gian.
=FILTER(A1:B10,HOUR(A1:A10)>=HOUR("2:00:00 PM"))
Tiền boa. Xem kết quả không bao gồm 12:41 chiều? Đó là bởi vì bảng tính coi nó là 00:41 ít hơn 2:00.
Nếu bạn tìm thấy một giải pháp thanh lịch hơn, xin vui lòng chia sẻ nó trong phần bình luận bên dưới.
Cách lọc trong Google Sheets bằng cách sử dụng tham chiếu ô
Mỗi lần bạn tạo công thức bộ lọc Google Sheets, bạn cần nhập điều kiện như: dù một từ hay phần của nó, ngày, v.v. Trừ khi bạn quen thuộc với tham chiếu ô.
Họ làm cho nhiều thứ về công thức dễ dàng hơn. Bởi vì thay vì gõ mọi thứ ra, bạn chỉ cần tham khảo các ô có điều kiện.
Hãy nhớ làm thế nào tôi tìm kiếm tất cả các đơn đặt hàng bị trễ? Tôi có thể nhanh chóng tham khảo E4 với văn bản Muộn để làm cái tương tự:
=FILTER(A1:E20,E1:E20=E4)
Kết quả sẽ không khác nhau chút nào:
Bạn có thể lặp lại điều này với tất cả các công thức đã nói ở trên. Ví dụ: tránh thêm nhiều chức năng như DATE và chỉ tham khảo ô có ngày quan tâm:
=FILTER(A1:E20,C1:C20=C15)
Tiền boa. Tham chiếu ô cũng cho phép bạn lọc từ một tờ khác. Bạn chỉ cần mang tên tờ:
=FILTER(Orders!A1:E20,Orders!C1:C20=Orders!C15)
Công thức Google Sheets LỌC với nhiều tiêu chí
Mặc dù trước đây tôi chủ yếu sử dụng một điều kiện trong tất cả các công thức lọc của Google Sheets, nhưng nhiều khả năng bạn sẽ cần lọc một bảng theo một vài điều kiện tại một thời điểm.
Ví dụ 1. LÀ GIỮA logic
Để tìm tất cả các hàng nằm giữa hai số / ngày / lần, các đối số tùy chọn của hàm sẽ có ích – điều kiện2, điều kiện3, v.v. Bạn chỉ cần nhân đôi cùng một phạm vi mỗi lần nhưng với một điều kiện mới.
Hãy nhìn xem, tôi sẽ chỉ trả lại những đơn hàng có giá hơn 250 đô la nhưng dưới 350 đô la:
=FILTER(A1:E20,B1:B20>=250,B1:B20<350)
Ví dụ 2. HOẶC logic trong hàm Google Sheets LỌC
Đáng buồn thay, để có được tất cả các hàng có chứa các bản ghi khác nhau trong một cột quan tâm, cách trước đó sẽ không làm. Vì vậy, làm thế nào tôi có thể kiểm tra tất cả các đơn đặt hàng cả trên đường đi và muộn?
Nếu tôi thử phương pháp trước đó và nhập từng trạng thái đơn hàng vào một điều kiện riêng, tôi sẽ gặp lỗi # N / A:
Do đó, để đặt chính xác logic OR trong hàm LỌC, tôi nên tổng hợp hai tiêu chí này trong một điều kiện:
=FILTER(A1:E20,(E1:E20="Late")+(E1:E20="On the way"))
Thêm bộ lọc vào Google Sheets vào nhiều cột
Điều thậm chí còn hơn cả việc áp dụng một vài điều kiện cho một cột là tạo bộ lọc trong Google Sheets cho nhiều cột.
Các đối số đều giống nhau. Nhưng mỗi phần mới của công thức đòi hỏi một phạm vi mới với các tiêu chí riêng.
Hãy thử và thực hiện chức năng LỌC trong Google Sheets trả lại các đơn đặt hàng tuân theo tất cả các quy tắc sau:
- Chúng phải có giá trị 200-400 đô la:
A1:E20,B1:B20>=200,B1:B20<=400 - Được dự kiến vào tháng 1 năm 2020:
MONTH(C1:C20)=1 - Và vẫn đang trên đường:
E1:E20="on the way"
Đặt tất cả các phần này lại với nhau và công thức bộ lọc Google Sheets của bạn cho nhiều cột đã sẵn sàng:
=FILTER(A1:E20,B1:B20>=200,B1:B20<=400,MONTH(C1:C20)=1,E1:E20="on the way")
Cách không có công thức cho bộ lọc Google Sheets nâng cao
Chức năng LỌC là tuyệt vời và tất cả, nhưng đôi khi nó có thể là quá nhiều. Theo dõi tất cả các đối số, dấu phân cách, hàm lồng nhau và không có gì có thể cực kỳ khó hiểu và tốn thời gian.
May mắn thay, chúng tôi có một giải pháp tốt hơn vượt qua cả chức năng Google Sheets LỌC và công cụ tiêu chuẩn của họ – Nhiều trận đấu VLOOKUP.
Đừng bối rối với tên của nó. Nó giống Chức năng VLOOKUP của Google Sheets bởi vì nó tìm kiếm các trận đấu Cũng giống như chức năng LỌC. Cũng như tôi đã làm ở trên.
Đây là 5 ưu điểm chính của công cụ qua chức năng Google Sheets LỌC:
- Bạn sẽ không phải suy nghĩ về khai thác cho các điều kiện khác nhau – chỉ cần chọn một từ danh sách:
- Nhập ngày và giờ như bạn luôn làm trong bảng tính – không có chức năng đặc biệt hơn:
- Tạo và xóa nhiều điều kiện cho nhiều cột thực sự nhanh chóng:
- Xem trước kết quả và điều chỉnh các điều kiện (nếu cần) trước khi dán mọi thứ vào trang tính của bạn:
- Lấy kết quả làm giá trị hoặc như là một sẵn sàng công thức.
Tôi thực sự khuyến khích bạn cài đặt Nhiều trận đấu VLOOKUP và cho nó đi Để xem qua các tùy chọn của nó gần hơn, hãy truy cập vào nó trang hướng dẫn hoặc xem đặc biệt video hướng dẫn.
Chat với tư vấn viên
Gọi ngay