Formula umum
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Ringkasan
Untuk MENYARING dan mengekstrak nilai n pertama atau terakhir (yaitu 3 nilai pertama, 5 nilai pertama, dll.), Anda dapat menggunakan fungsi FILTER bersama dengan INDEX dan SEQUENCE. Dalam contoh yang ditampilkan, rumus di D5 adalah:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
dimana datanya adalah rentang bernama B5: B15.
Penjelasan
Bekerja dari dalam ke luar, kami menggunakan fungsi SEQUENCE untuk membuat nilai nomor baris untuk INDEX seperti ini:
SEQUENCE(3,1,1,1)
Kami meminta SEQUENCE untuk larik 3 baris x 1 kolom, dimulai dari 1, dengan nilai langkah 1. Hasilnya adalah larik seperti ini:
(1;2;3)
yang dikembalikan langsung ke fungsi INDEX sebagai argumen row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Untuk membuat array untuk INDEX, kami menggunakan fungsi FILTER untuk mengambil daftar entri yang tidak kosong dari data rentang bernama (B5: B15) seperti ini:
FILTER(data,data"")
Argumen array adalah data, dan argumen include adalah data ekspresi "". Ini dapat diterjemahkan secara harfiah sebagai "nilai kembali dari data rentang bernama di mana nilai dalam data tidak kosong". Hasilnya adalah array dengan 9 nilai seperti ini:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Perhatikan nilai yang terkait dengan dua sel kosong telah dihapus. Larik ini dikembalikan ke fungsi INDEX sebagai argumen lariknya.
Terakhir, INDEX mengembalikan nilai ke-1, ke-2, dan ke-3 dari larik yang dikembalikan oleh FILTER:
("Atlanta";"Chicago";"Dallas")
Nilai n terakhir
Untuk mendapatkan nilai n terakhir dengan FILTER, Anda menggunakan struktur rumus yang sama, dengan input ke SEQUENCE yang dimodifikasi untuk membuat larik "n terakhir" dari nomor baris. Misalnya, untuk mendapatkan 3 nilai tidak kosong terakhir dalam contoh yang diperlihatkan, Anda dapat menggunakan rumus seperti ini:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Trik utama di sini adalah menghitung entri yang tidak kosong dalam data rentang bernama seperti ini:
SUM(--(data""))
Kami menggunakan negatif ganda untuk memaksa nilai TRUE FALSE menjadi 1 dan 0, lalu gunakan fungsi SUM untuk menghitungnya. Hasilnya dikembalikan sebagai argumen awal di dalam SEQUENCE. Kami menyediakan -1 untuk langkah mundur 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 bahwa nilai-nilai dikembalikan dalam urutan yang sama dengan kemunculannya di data sumber.
