Hàm LỌC trong Excel và ví dụ về công thức - Excel FILTER function


Hàm LỌC trong Excel và ví dụ về công thức

Hàm LỌC trong Excel và ví dụ về công thức - Excel FILTER function Hàm FILTER  trong Excel được sử dụng để lọc một phạm vi dữ liệu dựa trên các tiêu chí mà bạn chỉ định.Hàm thuộc về loại hàm Mảng động 
Hướng dẫn cho thấy cách lọc động trong Excel bằng các công thức. Ví dụ để lọc các bản sao, các ô chứa văn bản nhất định, với nhiều tiêu chí và hơn thế nữa.
 
Làm thế nào để bạn thường lọc trong Excel? Đối với hầu hết các phần, bằng cách sử dụng Bộ lọc tự động và trong các tình huống phức tạp hơn với Bộ lọc nâng cao . Nhanh và mạnh, các phương thức này có một nhược điểm đáng kể - chúng không tự động cập nhật khi dữ liệu của bạn thay đổi, nghĩa là bạn sẽ phải dọn dẹp và lọc lại. Việc giới thiệu chức năng LỌC trong Excel 365 trở thành một sự thay thế được chờ đợi từ lâu so với các tính năng thông thường. Không giống như chúng, các công thức Excel sẽ tự động tính toán lại với mỗi thay đổi bảng tính, vì vậy bạn sẽ cần thiết lập bộ lọc của mình chỉ một lần!
 
Hàm LỌC trong Excel
Công thức LỌC Excel cơ bản
Cách lọc trong Excel bằng công thức - ví dụ
Bộ lọc với nhiều tiêu chí AND
Lọc với nhiều tiêu chí OR
Bộ lọc dựa trên tiêu chí AND cũng như HOẶC
Cách lọc trùng lặp trong Excel
Cách lọc các ô trống
Lọc các ô có chứa văn bản nhất định
Bộ lọc và tổng hợp (Tổng, Trung bình, Tối thiểu, Tối đa, v.v.)
Công thức LỌC trường hợp nhạy cảm
Chức năng LỌC Excel không hoạt động

Hàm LỌC Excel - Excel FILTER function

Hàm FILTER  trong Excel được sử dụng để lọc một phạm vi dữ liệu dựa trên các tiêu chí mà bạn chỉ định.
 
Hàm thuộc về loại hàm Mảng động . Kết quả là một mảng các giá trị tự động tràn vào một phạm vi ô, bắt đầu từ ô nơi bạn nhập công thức.
 
Cú pháp của hàm FILTER  như sau:
FILTER(array, include, [if_empty])
 
Array (bắt buộc) - phạm vi hoặc mảng các giá trị mà bạn muốn lọc.
Include (required)  - tiêu chí được cung cấp dưới dạng mảng Boolean (giá trị TRUE và FALSE).
Chiều cao của nó (khi dữ liệu nằm trong cột) hoặc chiều rộng (khi dữ liệu ở hàng) phải bằng giá trị của đối số mảng .
If_empty (optional)  - giá trị trả về khi không có mục nào đáp ứng tiêu chí.
Ghi chú.  Hiện tại, chức năng LỌC chỉ khả dụng trong kênh Đăng ký hàng tháng của Office 365 . Bắt đầu từ tháng 7 năm 2020, nó sẽ được đưa vào kênh Bán niên.

Công thức LỌC Excel cơ bản

Để bắt đầu, hãy thảo luận về một vài trường hợp rất đơn giản chỉ để hiểu thêm về cách một công thức Excel để lọc dữ liệu hoạt động.
 
Từ tập dữ liệu bên dưới, giả sử bạn muốn trích xuất các bản ghi với một giá trị cụ thể trong Nhóm , cột, giả sử nhóm C. Để thực hiện xong, chúng tôi cung cấp biểu thức B2: B13 = "C" cho đối số bao gồm , sẽ tạo ra một mảng Boolean cần thiết, với TRUE tương ứng với các giá trị "C".
 
=FILTER(A2:C13, B2:B13="C", "No results")
 
Trong thực tế, sẽ thuận tiện hơn khi nhập tiêu chí vào một ô riêng, ví dụ F1 và sử dụng tham chiếu ô thay vì mã hóa trực tiếp giá trị trong công thức:
 
=FILTER(A2:C13, B2:B13=F1, "No results")
 
Không giống như tính năng Bộ lọc của Excel, chức năng này không thực hiện bất kỳ thay đổi nào đối với dữ liệu gốc. Nó trích xuất các bản ghi đã lọc vào phạm vi được gọi là phạm vi tràn (E4: G7 trong ảnh chụp màn hình bên dưới), bắt đầu trong ô nơi nhập công thức:
 
 
Nếu không có bản ghi nào khớp với tiêu chí đã chỉ định, công thức trả về giá trị bạn đặt trong đối số if_empty , "Không có kết quả" trong ví dụ này:
 
Nếu bạn không trả lại gì trong trường hợp này, thì hãy cung cấp một chuỗi rỗng ("") cho đối số cuối cùng:
 
=FILTER(A2:C13, B2:B13=F1, "")
 
Trong trường hợp dữ liệu của bạn được sắp xếp theo chiều ngang từ trái sang phải như được hiển thị trong ảnh chụp màn hình bên dưới, chức năng LỌC cũng sẽ hoạt động tốt. Chỉ cần đảm bảo rằng bạn xác định phạm vi phù hợp cho mảng và bao gồm các đối số, để mảng nguồn và mảng Boolean có cùng chiều rộng:
 
=FILTER(B2:M4, B3:M3= B7, "No results")

Hàm LỌC Excel - ghi chú sử dụng

Để lọc hiệu quả trong Excel bằng các công thức, đây là một số điểm quan trọng cần lưu ý:
 
Hàm LỌC tự động làm đổ kết quả theo chiều dọc hoặc chiều ngang trong bảng tính, tùy thuộc vào cách tổ chức dữ liệu gốc của bạn. Vì vậy, vui lòng đảm bảo rằng bạn luôn có đủ các ô trống bên phải và bên phải, nếu không bạn sẽ gặp lỗi #SPILL .
Kết quả của hàm Excel LỌC là động, nghĩa là chúng tự động cập nhật khi các giá trị trong tập dữ liệu gốc thay đổi. Tuy nhiên, phạm vi được cung cấp cho đối số mảng không được cập nhật khi các mục nhập mới được thêm vào dữ liệu nguồn. Nếu bạn muốn mảng tự động thay đổi kích thước, sau đó chuyển đổi nó thành bảng Excel và xây dựng các công thức với các tham chiếu có cấu trúc hoặc tạo một phạm vi có tên động .

Cách lọc trong Excel - ví dụ về công thức

Bây giờ bạn đã biết công thức bộ lọc Excel cơ bản hoạt động như thế nào, đã đến lúc cần có một số hiểu biết về cách nó có thể được mở rộng để giải quyết các tác vụ phức tạp hơn.
 
Bộ lọc với nhiều tiêu chí (AND logic)
Để lọc dữ liệu với nhiều tiêu chí, bạn cung cấp hai hoặc nhiều biểu thức logic cho đối số bao gồm :
 
LỌC (mảng, ( RANGE1 = Criteria1 ) * ( RANGE2 = criteria2 ), "Không có kết quả")
Hoạt động nhân xử lý các mảng với logic AND , đảm bảo rằng chỉ các bản ghi đáp ứng tất cả các tiêu chí được trả về. Về mặt kỹ thuật, nó hoạt động theo cách này:
 
Kết quả của mỗi biểu thức logic là một mảng các giá trị Boolean, trong đó TRUE tương đương với 1 và FALSE thành 0. Sau đó, các phần tử của tất cả các mảng trong cùng một vị trí được nhân lên. Vì nhân với số 0 luôn cho số không, chỉ có các mục mà tất cả các tiêu chí là TRUE được đưa vào mảng kết quả và do đó chỉ những mục đó được trích xuất.
 
Các ví dụ dưới đây cho thấy công thức chung này trong hành động.
 

Ví dụ 1: Lọc nhiều cột trong Excel

Mở rộng công thức LỌC Excel cơ bản của chúng tôi thêm một chút nữa, hãy lọc dữ liệu theo hai cột: Nhóm (cột B) và Thắng (cột C).
 
Để làm điều này, chúng tôi thiết lập các tiêu chí sau: nhập tên của nhóm mục tiêu vào F2 ( tiêu chí1 ) và số lần thắng tối thiểu được yêu cầu trong F3 ( tiêu chí2 ).
 
Cho rằng nguồn dữ liệu của chúng tôi là trong A2: C13 ( mảng ), nhóm này trong B2: B13 ( RANGE1 ) và chiến thắng là trong C2: C13 ( RANGE2 ), công thức có dạng như sau:
 
=FILTER(A2:C13, (B2:B13=F2) * (C2:C13>=F3), "No results")
 
Kết quả là bạn nhận được một danh sách những người chơi trong nhóm A đã bảo đảm 2 trận thắng trở lên:
 
 

Ví dụ 2: Lọc dữ liệu giữa các ngày

Trước hết, cần lưu ý rằng không thể tạo một công thức chung để lọc theo ngày trong Excel. Trong các tình huống khác nhau, bạn sẽ cần xây dựng các tiêu chí khác nhau, tùy thuộc vào việc bạn muốn lọc theo một ngày cụ thể, theo tháng hoặc theo năm. Mục đích của ví dụ này là để chứng minh cách tiếp cận chung.
 
Để dữ liệu mẫu của chúng tôi, chúng tôi thêm một cột chứa ngày của chiến thắng cuối cùng (cột D). Và bây giờ, chúng tôi sẽ trích xuất các chiến thắng xảy ra trong một khoảng thời gian cụ thể, từ ngày 17 tháng 5 đến ngày 31 tháng 5.
 
Xin lưu ý rằng trong trường hợp này, cả hai tiêu chí áp dụng cho cùng một phạm vi:
 
=FILTER(A2:D13, (D2:D13>=G2) * (D2:D13<=G3), "No results")
 
Trong đó G2 và G3 là ngày để lọc giữa.

Chức năng LỌC Excel không hoạt động

Trong trường hợp khi công thức LỌC Excel của bạn bị lỗi, rất có thể đó sẽ là một trong những điều sau đây:
 
#CALC! lỗi
Xảy ra nếu đối số if_empty tùy chọn bị bỏ qua và không có kết quả nào đáp ứng các tiêu chí được tìm thấy. Lý do là hiện tại Excel không hỗ trợ mảng trống. Để ngăn chặn các lỗi như vậy, hãy đảm bảo luôn xác định giá trị if_empty trong công thức của bạn.
 
 Lỗi #VALUE
Xảy ra khi mảng và bao gồm đối số có kích thước không tương thích.
 
# N / A, #VALUE, v.v.
Các lỗi khác nhau có thể xảy ra nếu một số giá trị trong đối số bao gồm là lỗi hoặc không thể được chuyển đổi thành giá trị Boolean.
 
 Lỗi #NAME
Xảy ra khi cố gắng sử dụng LỌC trong phiên bản Excel cũ hơn. Hãy nhớ rằng đó là một chức năng mới, chỉ có sẵn trong các đăng ký Office 365.
 
Trong Excel 365, lỗi #NAME xảy ra nếu bạn vô tình viết sai tên của hàm.
 
Lỗi #SPILL
Xảy ra nếu một hoặc nhiều ô trong phạm vi tràn không hoàn toàn trống. Để khắc phục, chỉ cần xóa hoặc xóa các ô không trống.
 
#REF! lỗi
Xảy ra khi một công thức LỌC được sử dụng giữa các sổ làm việc khác nhau và sổ làm việc nguồn được đóng lại.
 
Đó là cách quay dữ liệu trong Excel một cách linh hoạt. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
 
Chúc bạn thành công !

Các tin cũ hơn