Công thức Excel: LỌC trên n giá trị đầu tiên hoặc cuối cùng
Công thức Excel: LỌC trên n giá trị đầu tiên hoặc cuối cùng

Công thức chung
=INDEX(FILTER(data,data<>""),SEQUENCE(n,1,1,1))
Tóm lược

Để LỌC và trích xuất n giá trị đầu tiên hoặc cuối cùng (tức là 3 giá trị đầu tiên, 5 giá trị đầu tiên, v.v.), bạn có thể sử dụng Chức năng FILTER cùng với MỤC LỤCSỰ NỐI TIẾP. Trong ví dụ được hiển thị, công thức trong D5 là:

=INDEX(FILTER(data,data<>""),SEQUENCE(3,1,1,1))

Ở đâu dữ liệuphạm vi được đặt tên B5: B15.

Giải trình

Làm việc từ trong ra ngoài, chúng tôi sử dụng hàm SEQUENCE để tạo giá trị số hàng cho INDEX như sau:

SEQUENCE(3,1,1,1)

Chúng tôi đang yêu cầu SEQUENCE cho một mảng 3 hàng x 1 cột, bắt đầu từ 1, với giá trị bước là 1. Kết quả là một mảng như thế này:

{1;2;3}

được trả về trực tiếp cho hàm INDEX dưới dạng row_num tranh luận:

=INDEX(FILTER(data,data<>""),{1;2;3})

Để xây dựng mảng cho INDEX, chúng tôi sử dụng hàm FILTER để truy xuất danh sách các mục nhập không trống từ phạm vi đã đặt tên dữ liệu (B5: B15) như thế này:

FILTER(data,data<>"")

Các mảng đối số là dữ liệu và bao gồm đối số là dữ liệu biểu thức “”. Điều này có thể được dịch theo nghĩa đen là “trả về giá trị từ phạm vi được đặt tên dữ liệu giá trị ở đâu dữ liệu không trống “. Kết quả là một mảng với 9 giá trị như sau:

{"Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis"}

Giá trị thông báo liên quan đến hai ô trống đã bị loại bỏ. Mảng này được trả về hàm INDEX dưới dạng đối số mảng của nó.

Cuối cùng, INDEX trả về các giá trị thứ 1, thứ 2 và thứ 3 từ mảng được FILTER trả về:

{"Atlanta";"Chicago";"Dallas"}

N giá trị cuối cùng

Để nhận n giá trị cuối cùng với FILTER, bạn sử dụng cùng một cấu trúc công thức, với các đầu vào cho SEQUENCE được sửa đổi để tạo mảng “n cuối cùng” gồm các số hàng. Ví dụ: để nhận 3 giá trị không trống cuối cùng trong ví dụ được hiển thị, bạn có thể sử dụng công thức như sau:

=INDEX(FILTER(data,data<>""),SORT(SEQUENCE(3,1,SUM(--(data<>"")),-1)))

Thủ thuật chính ở đây là đếm các mục không trống trong phạm vi được đặt tên dữ liệu như thế này:

SUM(--(data<>""))

Chúng tôi sử dụng một Âm kép để buộc các giá trị TRUE FALSE thành 1s và 0s, sau đó sử dụng Hàm SUM để đếm. Kết quả được trả về là khởi đầu đối số bên trong SEQUENCE. Chúng tôi cung cấp -1 cho bươc lùi lại từ khởi đầu.

Chúng tôi cũng quấn Hàm SORT xung quanh SEQUENCE nên mảng được trả về là {7; 8; 9} và không phải là {9; 8; 7}. Điều này đảm bảo rằng các giá trị được trả về theo thứ tự mà chúng xuất hiện trong dữ liệu nguồn.

https://exceljet.net/formula/filter-on-first-or-last-n-values