• Home » 
  • Excel Tips » 
  • Hàm FILTER Trong Excel: Hướng Dẫn Sử Dụng Và Ví Dụ Thực Tế Từ A-Z

Hàm FILTER Trong Excel: Hướng Dẫn Sử Dụng Và Ví Dụ Thực Tế Từ A-Z

By 0
Dữ liệu mẫu bao gồm Tên, Khu vực và Doanh số bán hàng dùng cho hàm FILTER
Table of Contents

Thời gian đọc ước tính: 12 phút

Những điểm chính:

  • Cú pháp chuẩn: Hiểu rõ cấu trúc =FILTER(array, include, [if_empty]) để áp dụng chính xác.
  • Mảng động (Dynamic Arrays): Cơ chế tự động tràn kết quả (Spill) giúp loại bỏ thao tác copy-paste thủ công.
  • Đa điều kiện: Cách kết hợp toán tử nhân * (AND) và cộng + (OR) để lọc dữ liệu phức tạp.
  • Kết hợp hàm: Tối ưu hóa báo cáo bằng cách lồng ghép FILTER với SORT, LARGE và AVERAGE.
  • Xử lý lỗi: Khắc phục lỗi #CALC! và #SPILL! thường gặp khi làm việc với mảng.

Trước khi Microsoft 365 ra mắt, việc lọc dữ liệu trong Excel thường phụ thuộc vào tính năng Filter có sẵn trên thanh công cụ hoặc Advanced Filter. Mặc dù hữu ích, các phương pháp này có nhược điểm lớn: kết quả tĩnh, không tự động cập nhật khi dữ liệu nguồn thay đổi và quy trình thiết lập khá rườm rà. Nếu muốn dùng công thức, người dùng buộc phải viết các hàm mảng phức tạp kết hợp giữa INDEX, SMALL, IF và ROW.

Hàm FILTER xuất hiện như một giải pháp mạnh mẽ dành cho người dùng hiện đại. Đây là một trong những hàm thuộc nhóm Dynamic Array (Mảng động), cho phép bạn trích xuất dữ liệu dựa trên một hoặc nhiều điều kiện chỉ với một công thức duy nhất. Bài viết này sẽ hướng dẫn bạn cách làm chủ hàm FILTER qua các ví dụ minh họa cụ thể, giúp bạn xử lý dữ liệu nhanh chóng và chính xác.

1. Cấu Trúc Và Cú Pháp Hàm FILTER

Để sử dụng hàm FILTER hiệu quả, trước tiên bạn cần nắm vững ba thành phần chính trong cú pháp của nó.

Cú pháp cơ bản

Công thức chuẩn của hàm như sau:

=FILTER(array, include, [if_empty])

Giải thích các tham số:

  1. array (Bắt buộc): Vùng dữ liệu nguồn mà bạn muốn lọc. Đây có thể là một bảng hoặc một phạm vi ô (ví dụ: A2:C100).
  2. include (Bắt buộc): Điều kiện lọc. Đây là một mảng logic (True/False) có cùng chiều cao với vùng dữ liệu nguồn. Excel sẽ giữ lại các hàng tương ứng với giá trị TRUE.
  3. [if_empty] (Tùy chọn): Giá trị trả về nếu không có dữ liệu nào thỏa mãn điều kiện. Nếu bỏ qua tham số này và không tìm thấy kết quả, Excel sẽ trả về lỗi #CALC!. Bạn nên thiết lập giá trị này là “Không tìm thấy” hoặc chuỗi rỗng "" để báo cáo đẹp hơn.

Lưu ý quan trọng về Mảng động (Spill Range):
Khi bạn nhấn Enter, hàm FILTER sẽ không chỉ trả về kết quả trong một ô duy nhất. Nó sẽ “tràn” (spill) kết quả ra các ô lân cận để hiển thị toàn bộ danh sách lọc được. Do đó, hãy đảm bảo vùng ô bên dưới và bên phải công thức hoàn toàn trống để tránh lỗi #SPILL!.

Dữ liệu mẫu bao gồm Tên, Khu vực và Doanh số bán hàng dùng cho hàm FILTERDữ liệu mẫu bao gồm Tên, Khu vực và Doanh số bán hàng dùng cho hàm FILTER

Hình 1: Dữ liệu mẫu chúng ta sẽ sử dụng xuyên suốt bài viết này.

2. Lọc Dữ Liệu Theo Một Điều Kiện Đơn Giản

Trường hợp sử dụng phổ biến nhất là trích xuất dữ liệu dựa trên một tiêu chí cụ thể, ví dụ như lọc danh sách nhân viên theo phòng ban hoặc doanh số theo khu vực.

Ví dụ 1: Lọc dữ liệu theo Khu vực (Region)

Giả sử bạn có bảng dữ liệu bán hàng (như Hình 1) và muốn lọc ra tất cả các bản ghi thuộc khu vực “US”.

Công thức:

=FILTER($A$2:$C$11, $B$2:$B$11="US", "Không có dữ liệu")

Phân tích công thức:

  • $A$2:$C$11: Là toàn bộ vùng dữ liệu cần lấy thông tin.
  • $B$2:$B$11="US": Excel sẽ kiểm tra từng ô trong cột B. Nếu ô nào chứa “US”, công thức sẽ trả về TRUE và giữ lại hàng đó.

Kết quả lọc dữ liệu bán hàng chỉ hiển thị khu vực USKết quả lọc dữ liệu bán hàng chỉ hiển thị khu vực US

Kết quả trả về là một danh sách động. Nếu bạn thay đổi bất kỳ giá trị nào trong bảng gốc từ “Canada” sang “US”, danh sách kết quả bên phải sẽ tự động cập nhật ngay lập tức.

Mẹo nâng cao:
Thay vì nhập trực tiếp chữ “US” vào công thức (hard-code), bạn nên tham chiếu đến một ô chứa điều kiện (ví dụ ô I1). Khi đó công thức sẽ là: =FILTER($A$2:$C$11, $B$2:$B$11=I1). Bạn có thể tạo danh sách thả xuống (Data Validation) tại ô I1 để người xem báo cáo dễ dàng chọn khu vực muốn xem.

3. Lọc Dữ Liệu Sử Dụng Phép So Sánh Số Học

Hàm FILTER cực kỳ hữu ích khi làm việc với các con số. Bạn có thể sử dụng các toán tử so sánh như lớn hơn (>), nhỏ hơn (<), hoặc bằng (=) để lọc các giá trị định lượng.

Ví dụ 2: Lọc các giao dịch có doanh số lớn hơn 10.000

Trong quản trị kinh doanh, việc lọc ra các đơn hàng giá trị cao hoặc các nhân viên đạt KPI là nhu cầu thường xuyên.

Công thức:

=FILTER($A$2:$C$11, $C$2:$C$11 > 10000)

Danh sách các bản ghi có doanh số bán hàng lớn hơn 10000Danh sách các bản ghi có doanh số bán hàng lớn hơn 10000

Công thức này quét qua cột C (Doanh số). Bất kỳ giá trị nào lớn hơn 10.000 sẽ được đưa vào danh sách kết quả. Ngược lại, nếu bạn muốn tìm các giao dịch dưới 10.000, chỉ cần đổi toán tử thành nhỏ hơn (<).

Ứng dụng thực tế:
Bạn có thể dùng cách này để lọc danh sách sinh viên có điểm thi dưới trung bình để tổ chức học phụ đạo, hoặc lọc các mặt hàng tồn kho số lượng lớn cần giải phóng.

Ví dụ 3: Lọc Top 3 doanh số cao nhất

Để làm điều này, chúng ta cần kết hợp hàm FILTER với hàm LARGE. Hàm LARGE giúp xác định giá trị lớn thứ n trong một mảng.

Công thức:

=FILTER($A$2:$C$11, $C$2:$C$11 >= LARGE($C$2:$C$11, 3))

Kết quả lọc 3 giao dịch có doanh số cao nhất bằng hàm LARGEKết quả lọc 3 giao dịch có doanh số cao nhất bằng hàm LARGE

Cách hoạt động:

  1. LARGE($C$2:$C$11, 3) sẽ tìm ra con số lớn thứ 3 trong cột doanh số.
  2. Hàm FILTER sau đó so sánh toàn bộ cột doanh số với con số này.
  3. Bất kỳ giá trị nào lớn hơn hoặc bằng giá trị top 3 sẽ được giữ lại.

Lưu ý: Nếu có nhiều giá trị bằng nhau ở vị trí thứ 3, công thức có thể trả về nhiều hơn 3 dòng kết quả.

4. Kỹ Thuật Lọc Đa Điều Kiện (AND và OR)

Sức mạnh thực sự của hàm FILTER nằm ở khả năng xử lý nhiều điều kiện cùng lúc. Trong Excel, logic mảng hoạt động hơi khác so với hàm IF truyền thống.

  • *Phép nhân (``): Đại diện cho điều kiện AND** (Tất cả điều kiện phải đúng).
  • Phép cộng (+): Đại diện cho điều kiện OR (Chỉ cần một trong các điều kiện đúng).

Ví dụ 4: Điều kiện AND (Và)

Giả sử bạn muốn lọc ra các dòng dữ liệu thỏa mãn CẢ HAI điều kiện: Khu vực là “US” VÀ Doanh số lớn hơn 10.000.

Công thức:

=FILTER($A$2:$C$11, ($B$2:$B$11="US") * ($C$2:$C$11 > 10000), "Không tìm thấy")

Kết quả lọc theo điều kiện AND: Khu vực US và Doanh số trên 10000Kết quả lọc theo điều kiện AND: Khu vực US và Doanh số trên 10000

Giải thích logic:
Excel sẽ tạo ra hai mảng True/False (tương ứng là 1/0).

  • Mảng 1 (Khu vực): {1; 0; 1;...}
  • Mảng 2 (Doanh số): {0; 1; 1;...}
    Khi nhân hai mảng này với nhau, chỉ khi cả hai đều là 1 (True), kết quả mới là 1. Nếu một trong hai là 0 (False), kết quả sẽ là 0 và dòng đó bị loại bỏ.

Ví dụ 5: Điều kiện OR (Hoặc)

Bây giờ, tình huống thay đổi: Bạn muốn lấy danh sách các giao dịch thuộc khu vực “US” HOẶC khu vực “Canada”.

Công thức:

=FILTER($A$2:$C$11, ($B$2:$B$11="US") + ($B$2:$B$11="Canada"))

Kết quả lọc theo điều kiện OR: Khu vực là US hoặc CanadaKết quả lọc theo điều kiện OR: Khu vực là US hoặc Canada

Tương tự, bạn có thể kết hợp các trường khác nhau. Ví dụ: Lọc các dòng có Khu vực là “US” hoặc Doanh số > 10.000:
=FILTER($A$2:$C$11, ($B$2:$B$11="US") + ($C$2:$C$11 > 10000))

Quy tắc cần nhớ: Dùng dấu * cho các điều kiện bắt buộc phải xảy ra cùng lúc, và dấu + cho các tùy chọn linh hoạt.

5. Ứng Dụng Nâng Cao Với Các Hàm Thống Kê

Bạn có thể lồng ghép các hàm tính toán trực tiếp vào trong tham số include để tạo ra các bộ lọc thông minh dựa trên đặc điểm thống kê của dữ liệu.

Ví dụ 6: Lọc dữ liệu trên/dưới mức trung bình

Để tìm ra các nhân viên xuất sắc có doanh số cao hơn mức trung bình của cả nhóm, chúng ta kết hợp hàm AVERAGE.

Công thức:

=FILTER($A$2:$C$11, C2:C11 > AVERAGE(C2:C11))

Kết quả lọc các bản ghi có giá trị lớn hơn mức trung bìnhKết quả lọc các bản ghi có giá trị lớn hơn mức trung bình

Ngược lại, để lọc các giá trị dưới mức trung bình, bạn chỉ cần thay dấu > bằng dấu <. Cách làm này rất hiệu quả để phát hiện các ngoại lệ (outliers) hoặc đánh giá hiệu suất tương đối mà không cần tính toán thủ công số trung bình trước.

Ví dụ 7: Lọc các hàng chẵn hoặc lẻ

Kỹ thuật này thường được dùng khi bạn cần lấy mẫu dữ liệu ngẫu nhiên hoặc khi cấu trúc dữ liệu xen kẽ nhau. Chúng ta sẽ sử dụng hàm MOD và ROW.

Công thức lọc hàng chẵn:

=FILTER($A$2:$C$11, MOD(ROW(A2:A11)-1, 2) = 0)

Kết quả lọc chỉ hiển thị các hàng chẵn trong bảng dữ liệuKết quả lọc chỉ hiển thị các hàng chẵn trong bảng dữ liệu

Giải thích:

  • ROW(A2:A11): Trả về số thứ tự dòng của dữ liệu (bắt đầu từ 2).
  • -1: Để điều chỉnh dòng 2 thành số thứ tự 1 (dòng dữ liệu đầu tiên).
  • MOD(..., 2): Chia số thứ tự cho 2 lấy phần dư. Nếu dư 0 là chẵn, dư 1 là lẻ.

Để lọc hàng lẻ, bạn chỉ cần đổi điều kiện thành =1.

6. Sắp Xếp Dữ Liệu Sau Khi Lọc (SORT + FILTER)

Một trong những hạn chế của Filter truyền thống là dữ liệu trích xuất ra thường giữ nguyên thứ tự gốc. Với hàm mảng động, bạn có thể bọc hàm FILTER bên trong hàm SORT để kết quả đầu ra vừa được lọc, vừa được sắp xếp ngăn nắp.

Bài toán: Lọc các giao dịch có doanh số > 10.000 và sắp xếp danh sách này theo doanh số giảm dần.

Công thức:

=SORT(FILTER($A$2:$C$11, $C$2:$C$11 > 10000), 3, -1)

Dữ liệu được lọc và sắp xếp giảm dần theo cột doanh sốDữ liệu được lọc và sắp xếp giảm dần theo cột doanh số

Các tham số trong hàm SORT:

  1. Mảng cần sắp xếp: Chính là kết quả của hàm FILTER(...).
  2. 3: Sắp xếp dựa trên cột thứ 3 của mảng kết quả (Cột Doanh số).
  3. -1: Sắp xếp theo thứ tự giảm dần (Descending). Nếu muốn tăng dần, hãy dùng 1.

Sự kết hợp này tạo ra một bảng điều khiển (Dashboard) mini cực kỳ mạnh mẽ, nơi dữ liệu luôn được trình bày theo thứ tự ưu tiên quan trọng nhất.

Câu Hỏi Thường Gặp (FAQ)

1. Hàm FILTER có hoạt động trên Excel 2016 hay 2019 không?
Không. Hàm FILTER chỉ khả dụng trên Excel 365 và Excel 2021 trở đi. Nếu bạn sử dụng phiên bản cũ hơn, bạn sẽ thấy lỗi #NAME?.

2. Làm thế nào để xử lý lỗi #SPILL!?
Lỗi #SPILL! xuất hiện khi vùng không gian cần thiết để hiển thị kết quả bị chặn bởi dữ liệu khác (văn bản, số, hoặc khoảng trắng). Hãy dọn sạch các ô bên dưới và bên phải công thức, hoặc di chuyển công thức đến một vùng trống trải hơn.

3. Tôi có thể lọc dữ liệu từ sheet khác sang sheet hiện tại không?
Hoàn toàn được. Trong tham số array, bạn chỉ cần trỏ chuột sang sheet chứa dữ liệu nguồn (ví dụ: Sheet1!A2:C100). Đây là cách tuyệt vời để tạo một sheet báo cáo riêng biệt sạch sẽ, tách biệt với dữ liệu thô.

4. Tại sao tôi nhận được lỗi #CALC!?
Lỗi này thường xảy ra khi không có dữ liệu nào thỏa mãn điều kiện lọc của bạn. Để khắc phục, hãy luôn sử dụng tham số thứ 3 [if_empty] trong công thức, ví dụ: "Không tìm thấy dữ liệu".

5. Hàm FILTER có phân biệt chữ hoa chữ thường không?
Mặc định hàm FILTER không phân biệt chữ hoa thường (ví dụ: “us” và “US” là như nhau). Nếu bạn cần phân biệt chính xác, hãy kết hợp với hàm EXACT.

Nguồn Tham Khảo

Để hiểu sâu hơn về các hàm mảng động và cập nhật các tính năng mới nhất của Excel, bạn có thể tham khảo các tài liệu chính thống sau:

  1. Hỗ trợ Microsoft: Hàm FILTER – Hướng dẫn chính thức
  2. Hỗ trợ Microsoft: Các hành vi của Mảng động và Lỗi tràn #SPILL!
  3. Cộng đồng kỹ thuật Microsoft: Excel Dynamic Arrays – Giới thiệu và ứng dụng

Hàm FILTER không chỉ giúp tiết kiệm thời gian mà còn giảm thiểu rủi ro sai sót so với các thao tác thủ công. Bằng cách kết hợp linh hoạt các toán tử và hàm bổ trợ, bạn có thể xây dựng những báo cáo động chuyên nghiệp, đáp ứng nhanh chóng mọi yêu cầu phân tích dữ liệu. Hãy bắt đầu thực hành ngay hôm nay với dữ liệu của riêng bạn!

Edupedia

Related Posts

Leave a Comment

Offcanvas
Offcanvas