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

Office 365 mang đến một số tính năng tuyệt vời như XLOOKUP, SORT và FILTER.

Khi lọc dữ liệu trong Excel, trong thế giới trước Office 365, chúng tôi chủ yếu dựa vào các bộ lọc tích hợp sẵn trong Excel hoặc hầu hết các bộ lọc nâng cao hoặc các công thức SUMPRODUCT phức tạp.Đây thường là một cách giải quyết phức tạp nếu bạn phải lọc một phần của tập dữ liệu.

Nhưng với tính năng FILTER mới, giờ đây việc lọc nhanh các phần của tập dữ liệu dựa trên các điều kiện thực sự dễ dàng.

Trong hướng dẫn này, tôi sẽ cho bạn thấy tính năng FILTER mới tuyệt vời như thế nào và một số điều hữu ích bạn có thể làm với nó.

Nhưng trước khi đi vào ví dụ, chúng ta hãy xem nhanh cú pháp của hàm FILTER.

Nếu bạn muốn có những tính năng mới này trong Excel, bạn có thểNâng cấp lên Office 365(Tham gia chương trình nội bộ để truy cập tất cả các tính năng / công thức)

Chức năng Bộ lọc Excel - Cú pháp

Sau đây là cú pháp của hàm FILTER:

= FILTER (mảng, bao gồm, [if_empty])

• mảng - đây là phạm vi ô mà bạn có dữ liệu và muốn lọc một số dữ liệu từ đó
• bao gồm - Đây là điều kiện cho hàm biết bản ghi nào cần lọc
• [nếu_trống] - Đây là một tham số tùy chọn, nơi bạn có thể chỉ định những gì sẽ trả về nếu hàm FILTER không tìm thấy kết quả.Theo mặc định (khi không được chỉ định) nó trả về #CALC!Sai

Bây giờ chúng ta hãy xem xét một số ví dụ tuyệt vời về các chức năng của bộ lọc và những gì nó có thể làm mà trước đây rất phức tạp nếu không có nó.

Ví dụ 1: Lọc dữ liệu dựa trên một điều kiện (vùng)

Giả sử bạn có một tập dữ liệu như bên dưới và bạn chỉ muốn lọc tất cả các bản ghi ở Hoa Kỳ.

Đây là công thức FILTER thực hiện điều này:

= BỘ LỌC ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "US")

Công thức trên nhận tập dữ liệu dưới dạng một mảng và điều kiện là $ B $ 2: $ B $ 11 = ”US”

Điều kiện này sẽ khiến hàm FILTER kiểm tra mọi ô trong cột B (các ô có phạm vi) và chỉ lọc những bản ghi phù hợp với điều kiện này.

Ngoài ra, trong ví dụ này, tôi đặt dữ liệu gốc và dữ liệu đã lọc trên cùng một trang tính, nhưng bạn cũng có thể đặt chúng trong các trang tính riêng biệt hoặc thậm chí là sổ làm việc.

Hàm bộ lọc trả về một mảng kết quả động (có nghĩa là thay vì trả về một giá trị, nó sẽ trả về một mảng tràn sang các ô khác).

Đối với điều này, bạn cần phải có một khu vực nơi kết quả trống rỗng.Đã có một ô nào đó trong bất kỳ ô nào trong phạm vi (trong ví dụ này là E2: G5) và hàm sẽ cung cấp cho bạn lỗi #SPILL.

Ngoài ra, vì đây là một mảng động nên bạn không thể thay đổi một phần kết quả.Bạn có thể xóa toàn bộ phạm vi có kết quả hoặc ô E2 (nơi công thức được nhập).Cả hai điều này sẽ xóa toàn bộ mảng kết quả.Nhưng bạn không thể thay đổi bất kỳ ô nào (hoặc xóa nó).

Trong công thức ở trên, tôi đã mã hóa cứng giá trị phạm vi, nhưng bạn cũng có thể đặt nó vào một ô và tham chiếu ô đó với giá trị phạm vi.

Ví dụ: trong ví dụ dưới đây, tôi có giá trị phạm vi trong ô I2 và sau đó tham chiếu nó trong công thức:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Điều này làm cho công thức thậm chí còn hữu ích hơn, bây giờ bạn chỉ cần thay đổi giá trị phạm vi trong ô I2 và bộ lọc sẽ tự động thay đổi.

Bạn cũng có thể có một danh sách thả xuống trong ô I2, nơi bạn có thể chỉ cần thực hiện lựa chọn và nó sẽ cập nhật ngay lập tức dữ liệu đã lọc.

Ví dụ 2: Lọc dữ liệu dựa trên một tiêu chí (lớn hơn hoặc nhỏ hơn)

Bạn cũng có thể sử dụng toán tử so sánh trong các chức năng lọc và trích xuất tất cả các bản ghi lớn hơn hoặc nhỏ hơn một giá trị nhất định.

Ví dụ: giả sử bạn có tập dữ liệu được hiển thị bên dưới và bạn muốn lọc tất cả các bản ghi có doanh số bán hàng trên 10000.

Công thức sau có thể thực hiện điều này:

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

Đối số mảng tham chiếu đến toàn bộ tập dữ liệu, trong trường hợp này là điều kiện ($ C $ 2: $ C $ 11> 10000).

Công thức kiểm tra mỗi bản ghi để tìm giá trị trong cột C.Nếu giá trị lớn hơn 10000, nó được lọc, nếu không nó bị bỏ qua.

Nếu bạn muốn nhận tất cả các bản ghi nhỏ hơn 10000, bạn có thể sử dụng công thức sau:

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

Bạn cũng có thể sáng tạo hơn với công thức FILTER.Ví dụ: nếu bạn muốn lọc ba bản ghi hàng đầu dựa trên doanh số bán hàng, bạn có thể sử dụng công thức sau:

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

Công thức trên sử dụng hàm LARGE để nhận giá trị lớn thứ ba trong tập dữ liệu.Sau đó, sử dụng giá trị đó trong điều kiện hàm FILTER để lấy tất cả các bản ghi có doanh số bán hàng lớn hơn hoặc bằng giá trị lớn thứ ba.

Ví dụ 3: Lọc dữ liệu bằng nhiều điều kiện (AND)

Giả sử bạn có tập dữ liệu sau và bạn muốn lọc tất cả các bản ghi ở Hoa Kỳ có giá trị bán hàng lớn hơn 10000.

Đây là điều kiện VÀ, bạn cần kiểm tra hai điều - khu vực cần phải ở Mỹ và doanh số bán hàng cần trên 10000.Nếu chỉ đáp ứng một điều kiện, kết quả sẽ không được lọc.

Đây là công thức bộ lọc sẽ lọc các bản ghi có Hoa Kỳ là khu vực và với hơn 10000 lần bán hàng:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Vì tôi đang sử dụng hai điều kiện và tôi cần cả hai điều kiện đều đúng, tôi đã sử dụng toán tử nhân để kết hợp hai điều kiện.Điều này trả về một mảng 0 và 1, trong đó 1 chỉ được trả về nếu cả hai điều kiện được đáp ứng.

Nếu không có bản ghi nào phù hợp, hàm sẽ trả về lỗi #CALC!sai lầm, điều sai, ngộ nhận.

Nếu bạn muốn trả về điều gì đó có ý nghĩa (không phải lỗi), bạn có thể sử dụng công thức như sau:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Ở đây, tôi đã sử dụng "not found" làm tham số thứ ba, được sử dụng khi không tìm thấy bản ghi phù hợp nào.

Ví dụ 4: Lọc dữ liệu bằng nhiều tiêu chí (HOẶC)

Bạn cũng có thể sửa đổi tham số "chứa" trong hàm FILTER để kiểm tra các điều kiện HOẶC (trong đó bất kỳ điều kiện nhất định nào cũng có thể đúng).

Ví dụ: giả sử bạn có tập dữ liệu được hiển thị bên dưới và bạn muốn lọc các bản ghi có quốc gia là Hoa Kỳ hoặc Canada.

Đây là công thức để làm điều này:

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

Lưu ý rằng trong công thức trên, tôi chỉ thêm hai điều kiện bằng cách sử dụng toán tử cộng.Vì mỗi điều kiện này trả về một mảng TRUE và FALSE, tôi có thể thêm một mảng kết hợp sẽ là TRUE nếu một trong hai điều kiện được đáp ứng.

Một ví dụ khác có thể là khi bạn muốn lọc tất cả các bản ghi có quốc gia là Hoa Kỳ hoặc giá trị bán hàng trên 10000.

Công thức sau sẽ thực hiện điều này:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

LƯU Ý: Khi sử dụng điều kiện VÀ trong hàm FILTER, hãy sử dụng toán tử nhân (*) và khi sử dụng điều kiện OR, hãy sử dụng toán tử cộng (+).

Ví dụ 5: Lọc dữ liệu cho các bản ghi trên / dưới trung bình

Bạn có thể sử dụng công thức trong hàm bộ lọc để lọc và trích xuất các bản ghi trên hoặc thấp hơn mức trung bình.

Ví dụ: giả sử bạn có tập dữ liệu được hiển thị bên dưới và bạn muốn lọc tất cả các bản ghi có giá trị bán hàng trên mức trung bình.

Bạn có thể làm điều này với công thức sau:

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

Một lần nữa, đối với mức dưới trung bình, bạn có thể sử dụng công thức sau:

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

Ví dụ 6: Chỉ lọc các bản ghi chẵn (hoặc các bản ghi lẻ)

Nếu bạn cần nhanh chóng lọc và trích xuất tất cả các bản ghi theo hàng chẵn hoặc hàng lẻ, bạn có thể sử dụng chức năng FILTER để thực hiện.

Để thực hiện việc này, bạn cần kiểm tra số dòng trong hàm FILTER và chỉ lọc các số dòng phù hợp với điều kiện số dòng.

Giả sử bạn có một bộ dữ liệu như dưới đây và tôi chỉ muốn trích xuất ngay cả các bản ghi từ bộ dữ liệu này.

Đây là công thức để làm điều này:

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

Công thức trên sử dụng hàm MOD để kiểm tra số hàng (được cung cấp bởi hàm ROW) của mỗi bản ghi.

Công thức MOD (ROW (A2: A11) -1,2) = 0 trả về TRUE nếu số hàng là chẵn và FALSE nếu là số lẻ.Lưu ý rằng tôi đã trừ 2 từ phần ROW (A11: A1) vì bản ghi đầu tiên nằm ở hàng thứ hai, điều này sẽ điều chỉnh số hàng để coi hàng thứ hai là bản ghi đầu tiên.

Tương tự như vậy, bạn có thể lọc tất cả các bản ghi lẻ bằng công thức sau:

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

Ví dụ 7: Sắp xếp dữ liệu đã lọc bằng công thức

Sử dụng chức năng FILTER với các chức năng khác cho phép chúng ta làm được nhiều việc hơn.

Ví dụ: nếu bạn sử dụng hàm FILTER để lọc tập dữ liệu, bạn có thể sử dụng hàm SORT để nhận các kết quả đã được sắp xếp.

Giả sử bạn có một tập dữ liệu như được hiển thị bên dưới và bạn muốn lọc tất cả các bản ghi có doanh số bán hàng trên 10000.Bạn có thể sử dụng chức năng SORT với chức năng này để đảm bảo rằng dữ liệu kết quả được sắp xếp theo doanh số bán hàng.

Công thức sau sẽ thực hiện điều này:

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

Hàm trên sử dụng hàm FILTER để lấy dữ liệu ở cột C có doanh số lớn hơn 10000.Sau đó, sử dụng mảng được trả về bởi hàm FILTER trong hàm SORT để sắp xếp dữ liệu theo doanh số bán hàng.

Tham số thứ hai trong hàm SORT là 3, là để sắp xếp theo cột thứ ba.Tham số thứ tư là -1, sắp xếp dữ liệu theo thứ tự giảm dần.

Vì vậy, đây là 7 ví dụ về việc sử dụng hàm FILTER trong Excel.

Hy vọng bạn thấy hướng dẫn này hữu ích.