Cách sử dụng hàm FILTER trong Excel

Nếu thường xuyên làm việc với các tập dữ liệu trong Excel, thì bạn sẽ biết được tầm quan trọng của việc có thể nhanh chóng tìm thấy thông tin mình cần. Cho dù bạn đang phân tích số liệu bán hàng, theo dõi hàng tồn kho hay quản lý ngân sách, khả năng lọc dữ liệu là điều cần thiết.

May mắn thay, Microsoft Excel có một công cụ tích hợp mạnh mẽ có thể giúp ích: Hàm FILTER. Bằng cách sử dụng hàm FILTER, bạn có thể nhanh chóng sắp xếp qua các tập dữ liệu lớn và trích xuất dữ liệu mình cần dựa trên những điều kiện đã chỉ định.

 

Hàm FILTER là gì?

Hàm FILTER trong Excel cho phép bạn trích xuất một tập hợp con dữ liệu từ một phạm vi dựa trên một hoặc nhiều tiêu chí. Hàm này đánh giá từng giá trị trong một phạm vi dữ liệu và trả về các hàng hoặc cột đáp ứng những tiêu chí bạn đặt. Các tiêu chí được thể hiện dưới dạng công thức đánh giá thành một giá trị logic.

Hàm FILTER sử dụng cú pháp sau:

=FILTER(array, include, [if_empty])

Trong đó:

• array là phạm vi ô mà bạn muốn lọc.
• include biểu thị điều kiện bạn muốn sử dụng để lọc dữ liệu của mình. Đây có thể là một tiêu chí duy nhất hoặc nhiều tiêu chí được phân tách bằng các hàm logic của Excel.
• [if_empty] là một đối số tùy chọn chỉ định giá trị trả về nếu không có dữ liệu nào đáp ứng điều kiện đã chỉ định.

Cách sử dụng hàm FILTER trong Excel

Hãy xem xét một ví dụ để hiểu cách sử dụng hàm FILTER trong Excel. Giả sử bạn có một bảng dữ liệu bao gồm các cột cho Name, Age và Gender và bạn muốn lọc bảng để chỉ hiển thị nữ. Bạn sẽ sử dụng công thức sau:

=FILTER(A2:C10, C2:C10="Female")

Để phân tích công thức, đối số mảng là A2:C10 và đối số tiêu chí là C2:C10="Female". Công thức này sẽ trả về một mảng các giá trị chỉ bao gồm các hàng có Gender là "Female".

Hãy cùng xem một ví dụ khác! Giả sử bạn có một bảng dữ liệu bao gồm các cột cho Product Name, Category và Price. Bạn muốn lọc bảng để chỉ hiển thị các hàng có giá nhỏ hơn hoặc bằng 400 USD. Bạn sẽ sử dụng công thức sau:

=FILTER(A2:C10, C2:C10<=400)

Hàm FILTER sử dụng toán tử logic (<=) để kiểm tra cột Price theo điều kiện nhỏ hơn hoặc bằng 400 USD.

Sử dụng nhiều tiêu chí trong hàm FILTER

Hàm FILTER của Excel cho phép bạn chỉ định nhiều hơn một tiêu chí để lọc dữ liệu. Để thêm nhiều tiêu chí trong hàm FILTER, bạn cần sử dụng các hàm logic "AND" hoặc "OR".

Hàm AND yêu cầu tất cả các tiêu chí phải đúng để một hàng được đưa vào kết quả lọc, trong khi hàm OR yêu cầu ít nhất một trong các tiêu chí phải đúng để một hàng được đưa vào kết quả.

Sau đây là ví dụ về việc sử dụng hàm AND trong hàm FILTER để trích xuất dữ liệu từ một phạm vi dựa trên hai tiêu chí:

=FILTER(A2:C10, (B2:B10="Tech")*(C2:C10>400))

Công thức này trả về các hàng đáp ứng hai tiêu chí đã chỉ định. Tiêu chí đầu tiên là giá trị trong cột B phải là Tech và tiêu chí thứ hai là giá trị trong cột C phải lớn hơn 400. Biểu tượng dấu hoa thị (*) biểu thị toán tử AND, kết hợp hai tiêu chí.

Cách sử dụng FILTER với các hàm Excel khác

Hàm FILTER trong Excel có thể được sử dụng với nhiều hàm khác để thực hiện các phép tính và phân tích nâng cao trên những tập hợp dữ liệu cụ thể trong một bảng lớn.

Một cách phổ biến để sử dụng FILTER với các hàm khác là trước tiên lọc một phạm vi dữ liệu bằng hàm FILTER, sau đó áp dụng một hàm khác vào danh sách đã lọc. Ví dụ, bạn có thể sắp xếp dữ liệu bằng hàm SORT cùng với hàm FILTER.

Sau đây là một ví dụ: Giả sử bạn có một bảng chứa thông tin về các sản phẩm khác nhau bao gồm tên, giá và trạng thái có sẵn của chúng. Bạn muốn lọc bảng để chỉ hiển thị các sản phẩm còn hàng và sắp xếp danh sách đã lọc theo giá.

Công thức, trong trường hợp này, sẽ là:

=SORT(FILTER(A2:C10, C2:C10="In Stock"), 2, TRUE)

Công thức này sẽ trả về một bảng đã lọc chỉ có các sản phẩm hiện đang có trong kho, được sắp xếp theo giá của chúng theo thứ tự tăng dần. Đối số thứ hai trong hàm SORT (tức là 2) biểu thị số cột mà bạn muốn sắp xếp theo.

Trong trường hợp này, chúng ta muốn sắp xếp theo giá trong cột B, vì vậy ta sử dụng số 2. Đối số thứ ba trong hàm SORT (tức là TRUE) chỉ định rằng chúng ta muốn sắp xếp các giá trị theo thứ tự tăng dần.

Sau đây là các hàm Excel khác mà bạn có thể kết hợp với hàm FILTER:

• SUMIF hoặc AVERAGEIF: Bạn có thể sử dụng hàm FILTER để tạo một tập hợp con dữ liệu đáp ứng các tiêu chí nhất định, sau đó sử dụng hàm SUMIF hoặc AVERAGEIF để tính tổng hoặc giá trị trung bình của tập hợp con dữ liệu đó.
  • Ví dụ, nếu bạn có danh sách dữ liệu bán hàng cho các sản phẩm khác nhau và muốn tính tổng doanh số cho một sản phẩm cụ thể, bạn có thể sử dụng FILTER để tạo một tập hợp con dữ liệu cho sản phẩm đó, sau đó sử dụng hàm SUMIF để tính tổng doanh số.
• MAX và MIN: Bạn có thể sử dụng hàm FILTER để tìm giá trị lớn nhất hoặc nhỏ nhất trong một tập hợp con dữ liệu đáp ứng các tiêu chí nhất định. Trong ví dụ về dữ liệu bán hàng, bạn có thể sử dụng hàm FILTER với MAX hoặc MIN để tìm sản phẩm có doanh số cao nhất hoặc thấp nhất.
• COUNT: Bạn cũng có thể sử dụng hàm COUNT với FILTER để lấy số ô đáp ứng các tiêu chí nhất định.

Nhìn chung, việc kết hợp hàm FILTER với các hàm Excel khác có thể giúp bạn thực hiện phân tích nâng cao hơn và làm cho dữ liệu của bạn có ý nghĩa và hữu ích hơn.

Lọc dữ liệu hiệu quả với hàm FILTER của Excel

Hàm FILTER của Excel cho phép bạn lọc dữ liệu nhanh chóng và dễ dàng dựa trên các điều kiện đã chỉ định. Bằng cách sử dụng hàm FILTER, bạn có thể tránh được sự phiền phức khi phải sắp xếp thủ công qua các tập dữ liệu lớn để tìm dữ liệu mình cần. Bạn có thể chỉ định nhiều tiêu chí và kết hợp chúng bằng các toán tử như "AND" và "OR", giúp bạn linh hoạt và kiểm soát được công thức lọc.

Cho dù bạn là nhà phân tích giàu kinh nghiệm hay người mới bắt đầu, hàm FILTER của Excel có thể giúp bạn hợp lý hóa quy trình làm việc và tận dụng tối đa dữ liệu của mình.

Lọc bằng logic AND

Logic AND yêu cầu mọi điều kiện phải được đáp ứng đồng thời. Ở đây, bài viết sử dụng toán tử dấu sao (*) để nhân các phép kiểm tra logic với nhau. Mỗi điều kiện đóng vai trò như một cổng mà dữ liệu phải đi qua.

Ví dụ về dữ liệu nhân viên, công thức sau sẽ chỉ trả về những nhân viên đang hoạt động, làm việc trong bộ phận bán hàng và làm việc toàn thời gian.

=FILTER(A2:AC3004, (K2:K3004="Active") * (Q2:Q3004="Sales") * (L2:L3004="Full-Time"))

Cả ba điều kiện phải đúng thì nhân viên mới xuất hiện trong kết quả của bạn. Nếu thiếu một điều kiện, nhân viên đó sẽ bị lọc hoàn toàn. Phương pháp này hoạt động hoàn hảo khi bạn cần thu hẹp danh sách ứng viên cho các vai trò cụ thể hoặc yêu cầu tuân thủ.

Bạn có thể xếp chồng nhiều điều kiện AND tùy ý, chẳng hạn như thêm tiêu chí hiệu suất để tìm kiếm nhân viên bán hàng đang hoạt động với điểm đánh giá cao và đáp ứng đầy đủ các yêu cầu.

=FILTER(D2:D3004, (K2:K3004="Active") * (Q2:Q3004="Sales") * (AB2:AB3004>=4) * (AA2:AA3004="Fully Meets"))

Lọc bằng logic OR

Logic OR cho kết quả khi bất kỳ điều kiện nào được đáp ứng. Trong trường hợp này, bài viết sử dụng toán tử cộng (+) giữa các phép kiểm tra logic.

=FILTER(D2:D3004, (Q2:Q3004="Sales") + (Q2:Q3004="Production") + (Q2:Q3004="Technology"))

Công thức trên lọc nhân viên từ phòng ban bán hàng, sản xuất hoặc công nghệ. Không giống như lọc AND, chỉ cần đáp ứng một điều kiện là đủ để một bản ghi đủ điều kiện đưa vào. Vì vậy, logic OR mở rộng kết quả của bạn thay vì thu hẹp chúng.

Kiểu lọc này hữu ích khi bạn cần mở rộng phạm vi. Ví dụ, nếu bạn cần nhân viên từ nhiều phòng ban cho một dự án liên chức năng, bạn có thể sử dụng logic OR để thu thập tất cả những người liên quan mà không cần công thức riêng cho từng phòng ban.

Kết hợp logic AND và OR

Bạn có thể kết hợp logic AND và OR cho các truy vấn phức tạp. Nếu muốn tìm kiếm nhân viên có hiệu suất cao từ các phòng ban chủ chốt, bạn có thể thử kết hợp sau.

=FILTER(A2:AC3004, ((Q2:Q3004="Sales") + (Q2:Q3004="Production")) * (AB2:AB3004>=4))

Công thức này tìm kiếm những nhân viên bán hàng hoặc sản xuất có xếp hạng hiệu suất từ 4 trở lên. Dấu ngoặc đơn kiểm soát thứ tự các phép toán, giống như những phương trình toán học.

Bạn thậm chí có thể áp dụng công thức này trong các tình huống phức tạp hơn, chẳng hạn như lọc nhân viên bị chấm dứt hợp đồng vì những lý do cụ thể. Công thức kết hợp này có thể bao gồm những nhân viên tự nguyện nghỉ việc hoặc bị chấm dứt hợp đồng có lý do, nhưng chỉ những nhân viên có mô tả chấm dứt hợp đồng đã được điền đầy đủ.

Việc hiểu cách các toán tử logic này hoạt động cùng nhau có thể hữu ích cho các khả năng phân tích dữ liệu nâng cao, giúp giảm thiểu sự hỗn loạn của bảng tính thành những thông tin chi tiết hữu ích.