Formula generik
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Ringkasan
Untuk MEMILIH dan mengekstrak nilai n pertama atau terakhir (iaitu 3 nilai pertama, 5 nilai pertama, dll.), Anda boleh menggunakan fungsi FILTER bersama dengan INDEX dan SEQUENCE. Dalam contoh yang ditunjukkan, formula dalam D5 adalah:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
di mana data adalah julat bernama B5: B15.
Penjelasan
Bekerja dari dalam ke luar, kami menggunakan fungsi SEQUENCE untuk membina nilai nombor baris untuk INDEX seperti ini:
SEQUENCE(3,1,1,1)
Kami meminta SEQUENCE untuk susunan 3 baris x 1 lajur, bermula pada 1, dengan nilai langkah 1. Hasilnya adalah susunan seperti ini:
(1;2;3)
yang dikembalikan terus ke fungsi INDEX sebagai argumen baris_num:
=INDEX(FILTER(data,data""),(1;2;3))
Untuk membina array untuk INDEX, kami menggunakan fungsi FILTER untuk mendapatkan senarai entri yang tidak kosong dari data julat bernama (B5: B15) seperti ini:
FILTER(data,data"")
Argumen array adalah data, dan argumen include adalah data ekspresi "". Ini boleh diterjemahkan secara harfiah sebagai "nilai kembali dari data julat yang dinamakan di mana nilai dalam data tidak kosong". Hasilnya adalah susunan dengan 9 nilai seperti ini:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Nilai pemberitahuan yang berkaitan dengan dua sel kosong telah dikeluarkan. Array ini dikembalikan ke fungsi INDEX sebagai argumen arraynya.
Akhirnya, INDEX mengembalikan nilai 1, 2, dan 3 dari tatasusunan yang dikembalikan oleh FILTER:
("Atlanta";"Chicago";"Dallas")
Nilai n terakhir
Untuk mendapatkan nilai n terakhir dengan FILTER, anda menggunakan struktur formula yang sama, dengan input untuk SEQUENCE diubah untuk membina susunan nombor baris "n terakhir". Contohnya, untuk mendapatkan 3 nilai bukan kosong terakhir dalam contoh yang ditunjukkan, anda boleh menggunakan formula seperti ini:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Trik utama di sini ialah mengira entri yang tidak kosong dalam data julat bernama seperti ini:
SUM(--(data""))
Kami menggunakan negatif dua untuk memaksa nilai BENAR BENAR ke 1s dan 0s, kemudian menggunakan fungsi SUM untuk mendapatkan kiraan. Hasilnya dikembalikan sebagai argumen awal dalam SEQUENCE. Kami menyediakan -1 untuk langkah ke belakang dari awal.
Kami juga membungkus fungsi SORT di sekitar SEQUENCE sehingga array yang dikembalikan adalah (7; 8; 9) dan bukan (9; 8; 7). Ini memastikan bahawa nilai dikembalikan dalam urutan yang sama seperti yang muncul dalam data sumber.
