Công thức Excel: Lọc dữ liệu theo chiều ngang -

Mục lục

Công thức chung

=FILTER(data,logic)

Tóm lược

Để lọc dữ liệu được sắp xếp theo chiều ngang trong các cột, bạn có thể sử dụng chức năng FILTER. Trong ví dụ được hiển thị, công thức trong C9 là:

=TRANSPOSE(FILTER(data,group="fox"))

trong đó dữ liệu (C4: L6) và nhóm (C5: L5) là các phạm vi được đặt tên.

Giải trình

Lưu ý: FILTER là một hàm mảng động mới trong Excel 365. Trong các phiên bản Excel khác, có các lựa chọn thay thế, nhưng chúng phức tạp hơn.

Có mười cột dữ liệu trong phạm vi C4: L6. Mục đích là để lọc dữ liệu theo chiều ngang này và chỉ trích xuất các cột (bản ghi) mà nhóm là "cáo". Để thuận tiện và dễ đọc, trang tính chứa ba phạm vi được đặt tên: dữ liệu (C4: L6) và nhóm (C5: L5) và tuổi (C6: L6).

Hàm FILTER có thể được sử dụng để trích xuất dữ liệu được sắp xếp theo chiều dọc (theo hàng) hoặc theo chiều ngang (theo cột). FILTER sẽ trả về dữ liệu phù hợp theo cùng một hướng. Không cần thiết lập đặc biệt. Trong ví dụ được hiển thị, công thức trong C9 là:

=FILTER(data,group="fox")

Làm việc từ trong ra ngoài, đối số include cho FILTER là một biểu thức logic:

group="fox" // test for "fox"

Khi biểu thức logic được đánh giá, nó trả về một mảng 10 giá trị TRUE và FALSE:

(TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE)

Lưu ý: dấu phẩy (,) trong mảng này biểu thị cột. Dấu chấm phẩy (;) sẽ biểu thị các hàng.

Mảng chứa một giá trị cho mỗi cột trong dữ liệu và mỗi TRUE tương ứng với một cột mà nhóm là "cáo". Mảng này được trả về trực tiếp cho FILTER dưới dạng đối số bao gồm và nó thực hiện lọc thực tế:

FILTER(data,(TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE))

Chỉ dữ liệu tương ứng với các giá trị TRUE mới qua bộ lọc, vì vậy FILTER trả về 6 cột mà nhóm là "cáo". FILTER trả về dữ liệu này theo cấu trúc ngang ban đầu. Vì FILTER là một hàm mảng động, kết quả tràn vào phạm vi C9: H11.

Đây là một giải pháp động - nếu bất kỳ dữ liệu nguồn nào trong C4: L6 thay đổi, kết quả từ FILTER sẽ tự động cập nhật.

Chuyển sang định dạng dọc

Để chuyển kết quả từ bộ lọc sang định dạng dọc (hàng), bạn có thể bọc hàm TRANSPOSE xung quanh hàm FILTER như sau:

=TRANSPOSE(FILTER(data,group="fox"))

Kết quả trông như thế này:

Công thức này được giải thích chi tiết hơn ở đây.

Lọc theo độ tuổi

Cùng một công thức cơ bản có thể được sử dụng để lọc dữ liệu theo những cách khác nhau. Ví dụ: để lọc dữ liệu để chỉ hiển thị các cột có độ tuổi nhỏ hơn 22, bạn có thể sử dụng công thức như sau: