Trong Excel, hàm được lọc cho phép bạn lọc một phạm vi dữ liệu dựa trên một hoặc nhiều tiêu chí được xác định. Các bộ lọc này yêu cầu các công thức (hoặc hàm) chính xác, nhưng các mẫu cơ bản luôn giống nhau. Các công thức bạn cần biết như sau:
=FILTER(mảng;tiêu chí),
=FILTER(mảng;(tiêu chí1)*(tiêu chí2)), =FILTER(mảng;(tiêu chí1)+(tiêu chí2)), =FILTER(mảng;tiêu chí;“nếu không có kết quả“).
Một khi được đưa vào thực tế, những mô hình này rất dễ tiếp thu. Chúng tôi cung cấp cho bạn 5 ví dụ để hiểu mọi thứ về chức năng được lọc từ Excel.
Ví dụ 1: tạo bộ lọc Excel cho một giá trị
Mục đích của ví dụ đầu tiên này là lọc ra các công ty bán ít nhất 8 000 đơn vị sản phẩm từ tất cả dữ liệu ở bảng bên trái và nhận kết quả lọc ở bảng bên phải.
Để thiết lập bộ lọc này, trước tiên bạn phải đi đến ô (trong trường hợp của chúng tôi là ô H4), ô này phải nhận kết quả của quá trình lọc để viết công thức vào đó. Đây là như sau: =BỘ LỌC(A4:D18;D4:D18>=8000). Làm thế nào để hiểu nó:
A4:D18 tương ứng với phạm vi được tính đến trong hoạt động, ở đây là toàn bộ mảng của chúng tôi.
D4:D18 tương ứng với phạm vi mà tiêu chí lọc phải được áp dụng, tức là các đơn vị sản phẩm của chúng tôi,
>=8000 tương ứng với điều kiện, chúng tôi chỉ muốn giữ lại những công ty bán 8 000 đơn vị trở lên.
Tất cả những gì bạn phải làm là nhấn Cổng vào để xác thực bộ lọc và xem kết quả được hiển thị. Ở đây, chúng ta có một bảng gồm các công ty bán nhiều hơn 8 000 đơn vị của bất kỳ sản phẩm nào.
Ví dụ thứ 2: tạo bộ lọc Excel cho thành phần văn bản
Thứ hai, chúng tôi muốn lọc và hiển thị các công ty bán lốp xe, bất kể họ ở đâu trên thế giới. Để làm điều này, chúng ta sẽ nhập công thức sau: =FILTER(A4:D18;C4:C18=”Lốp”). Một lần nữa, đây là cách để tìm ra nó:
A4:D18 tương ứng với phạm vi được tính đến trong hoạt động, ở đây toàn bộ mảng của chúng tôi,
C4:C18 tương ứng với phạm vi theo tiêu chí lọc của chúng tôi,
=“Lốp xe” đáp ứng tiêu chí của chúng tôi.
Có một khả năng khác để áp dụng bộ lọc như vậy. Bạn có thể, thay vì đăng ký thủ công “Lốp xe” trong công thức của bạn, hãy chọn ô nơi tiêu chí của bạn được đề cập. Dưới đây, chúng tôi đã tạo một danh sách thả xuống để có sẵn các điều kiện của chúng tôi. Sau đó chúng ta nhập công thức =BỘ LỌC(A4:D18;C4:C18=F4)ô F4 thay thế toàn bộ phần đề cập đến lốp xe vì nó được viết ở đó.
Ví dụ thứ 3: tạo bộ lọc Excel dựa trên một số tiêu chí
Bây giờ chúng tôi muốn áp dụng hai điều kiện cho bộ lọc của mình. Trong trường hợp ví dụ của chúng tôi, vấn đề là phải lọc các công ty bán 8 000 chiếc lốp trở lên. Do đó, chúng ta vẫn sẽ ở ô H4 dành cho kết quả, nhập công thức sau: =FILTER(A4:D18;(D4:D18>=8000)*(C4:C18=“Lốp xe“)). Đây là ý nghĩa của nó:
A4:D18 luôn tương ứng với phạm vi dữ liệu cần tính đến,
(D4:D18>=8000)*(C4:C18=“Lốp“) tương ứng với hai điều kiện đã được sử dụng trong các ví dụ trước của chúng tôi. Để tương quan giữa chúng, hãy đảm bảo đặt cả hai trong dấu ngoặc đơn và tách chúng ra khỏi dấu * (dấu hoa thị hoặc dấu nhân).
Bộ lọc áp dụng và hiển thị cho bạn những công ty đã bán được nhiều hơn 8 000 lốp xe.
Ví dụ thứ 4: tạo bộ lọc Excel dựa trên ít nhất một tiêu chí
Cũng có thể lọc bảng dữ liệu theo ít nhất một tiêu chí trong số một số tiêu chí được yêu cầu. Ví dụ: khi kết thúc quá trình lọc, chúng tôi muốn tất cả các công ty Châu Âu và tất cả những công ty bán ít hơn 9 000 đơn vị sản phẩm. Công thức lọc phải được viết theo cách sao cho chúng ta có được tất cả các công ty ở Châu Âu bất kể khối lượng bán hàng của họ như thế nào, cũng như tất cả các công ty ở phần còn lại của thế giới bán khối lượng thấp hơn 9 000. Đây là: =FILTER(A4:D18;(B4:B18=“Châu Âu“)+(D4:D18<=9000)).
Ở đây, sự khác biệt duy nhất là việc sử dụng dấu hiệu + để cho bộ lọc Excel của bạn biết rằng hai tiêu chí này được thêm độc lập và không phụ thuộc vào nhau.
Ví dụ thứ 5: tạo bộ lọc Excel nâng cao với hai tiêu chí
Đối với ví dụ cuối cùng về bộ lọc Excel này, chúng tôi muốn nhanh chóng có được danh sách các công ty bán giữa 8 000 và 10.000 đơn vị sản phẩm. Nhờ những tình huống được mô tả ở trên, chúng ta có đủ kiến thức để thiết lập một bộ lọc như vậy. Đây là một trong những công thức có thể được sử dụng: =FILTER(A4:D18;(D4:D18>=8000)*(D4:D18<=10000)). Lời giải thích của anh ấy là:
A4:D18 khớp toàn bộ với bảng dữ liệu,
(D4:D18>=8000)*(D4:D18<=10000) tương ứng với liên kết hai tiêu chí của chúng tôi, ban đầu các giá trị nằm trong cột D4:D18 hoàn toàn lớn hơn hoặc bằng 8 000 và sau đó có giá trị nhỏ hơn hoặc bằng 10.000.
Một lần nữa, có thể bỏ qua công thức hoàn chỉnh bao gồm các dấu hiệu >= bằng cách nhập dữ liệu có giá trị tiêu chí lọc vào ô. Ở đây, giá trị tối thiểu và tối đa của chúng ta đã được nhập vào ô F6 và F8 nên chúng ta có thể sử dụng chúng trong công thức như sau: =FILTER(A4:D18;(D4:D18>=F6)*(D4:D18<=F8)). Tế bào F6 Và F8 do đó thay thế đề cập >=8000 Và <=10000.
Mẹo cuối cùng trong trường hợp lọc không có kết quả
Có thể bạn đã thiết lập một bộ lọc mà không có kết quả nào xuất hiện. Để ngăn Excel hiển thị lỗi hoặc để chỉ định rằng không tìm thấy dữ liệu nào liên quan đến bộ lọc của bạn, bạn có thể tự đặt cấu hình trong công thức của mình, đề cập đến việc chỉ định dữ liệu đó. Chỉ cần thêm văn bản ngắn của bạn vào dấu ngoặc kép ở cuối công thức.
Ví dụ: chúng tôi muốn bộ lọc của mình cung cấp cho chúng tôi các công ty bán được 13.000 đơn vị sản phẩm trở lên. Không có, nhưng chúng tôi tin rằng chúng tôi không biết. Do đó, chúng tôi sẽ nhập công thức thích hợp cho bộ lọc của mình, kèm theo văn bản trong trường hợp thiếu kết quả: =FILTER(A4:D18;D4:D18>=13000;“không có kết quả“).
Bạn cũng có thể quyết định rằng trường này sẽ trống nếu không có kết quả nào xuất hiện. Đối với điều này, công thức giống hệt nhau nhưng không nhập bất kỳ nội dung nào vào giữa dấu ngoặc kép. Ví dụ : =BỘ LỌC(A4:D18;D4:D18>=13000;““).
