Formula umum
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Ringkasan
Untuk mendapatkan posisi pertandingan terakhir (yaitu kemunculan terakhir) dari nilai pencarian, Anda bisa menggunakan rumus array berdasarkan fungsi IF, ROW, INDEX, MATCH, dan MAX. Pada contoh yang ditampilkan, rumus di H6 adalah:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Di mana "nama" adalah rentang bernama C4: C11.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Inti dari rumus ini adalah kita membuat daftar nomor baris untuk rentang tertentu, mencocokkan nilai, lalu menggunakan fungsi MAX untuk mendapatkan nomor baris terbesar, yang sesuai dengan nilai pencocokan terakhir. Kami menggunakan rentang bernama "nama" hanya untuk kenyamanan.
Bekerja dari dalam ke luar, bagian dari rumus ini akan menghasilkan sekumpulan nomor baris yang relatif:
ROW(names)-MIN(ROW(names))+1
Hasil dari ekspresi di atas adalah deretan angka seperti ini:
(1;2;3;4;5;6;7;8)
Perhatikan bahwa kita mendapatkan 8 angka, sesuai dengan 8 baris pada tabel. Lihat halaman ini untuk detail tentang cara kerja bagian rumus ini.
Untuk tujuan rumus ini, kami hanya menginginkan nomor baris untuk nilai yang cocok, jadi kami menggunakan fungsi IF untuk memfilter nilai seperti ini:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Ini menghasilkan larik yang terlihat seperti ini:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Perhatikan bahwa array ini masih berisi delapan item. Namun, hanya nomor baris di mana nilai dalam rentang bernama "nama" sama dengan "amy" yang bertahan (yaitu 1, 4, 7). Semua item lain dalam larik adalah FALSE, karena gagal dalam pengujian logika dalam fungsi IF.
Terakhir, fungsi IF mengirimkan larik ini ke fungsi MAX. MAX mengembalikan nilai tertinggi dalam larik, angka 7, yang sesuai dengan nomor baris terakhir di mana namanya adalah "amy". Setelah kita mengetahui nomor baris terakhir yang cocok, kita dapat menggunakan INDEX untuk mengambil nilai pada posisi itu.
Detik terakhir, dll.
Untuk mendapatkan posisi kedua ke terakhir, ketiga ke terakhir, dll. Anda dapat beralih dari fungsi MIN ke fungsi LARGE seperti ini:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
di mana k mewakili "terbesar ke-n". Misalnya, untuk mendapatkan kecocokan kedua hingga terakhir pada contoh di atas, Anda dapat menggunakan:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Seperti sebelumnya, ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
