Formula generik
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Ringkasan
Untuk mendapatkan kedudukan perlawanan terakhir (iaitu kejadian terakhir) dari nilai pencarian, anda boleh menggunakan formula larik berdasarkan fungsi IF, ROW, INDEX, MATCH, dan MAX. Dalam contoh yang ditunjukkan, formula dalam H6 adalah:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Di mana "nama" adalah julat bernama C4: C11.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Inti dari formula ini ialah kita membina senarai nombor baris untuk julat tertentu, sepadan dengan nilai, dan kemudian menggunakan fungsi MAX untuk mendapatkan nombor baris terbesar, yang sepadan dengan nilai pencocokan terakhir. Kami menggunakan julat "nama" yang diberi nama untuk kemudahan sahaja.
Berfungsi dari dalam ke luar, bahagian formula ini akan menghasilkan set nombor baris relatif:
ROW(names)-MIN(ROW(names))+1
Hasil ungkapan di atas adalah susunan nombor seperti ini:
(1;2;3;4;5;6;7;8)
Perhatikan kita mendapat 8 nombor, sepadan dengan 8 baris dalam jadual. Lihat halaman ini untuk perincian bagaimana bahagian formula ini berfungsi.
Untuk tujuan formula ini, kami hanya mahukan nombor baris untuk nilai yang sepadan, jadi kami menggunakan fungsi IF untuk menyaring nilai seperti:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Ini menghasilkan susunan yang kelihatan seperti ini:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Perhatikan bahawa array ini masih mengandungi lapan item. Walau bagaimanapun, hanya nombor baris di mana nilai dalam julat "nama" yang sama dengan "amy" telah bertahan (iaitu 1, 4, 7). Semua item lain dalam array adalah SALAH, kerana mereka gagal dalam ujian logik dalam fungsi IF.
Akhirnya, fungsi IF menyampaikan array ini ke fungsi MAX. MAX mengembalikan nilai tertinggi dalam array, nombor 7, yang sesuai dengan nombor baris terakhir di mana namanya "amy". Sebaik sahaja kita mengetahui nombor baris pencocokan terakhir, kita dapat menggunakan INDEX untuk mendapatkan nilai pada kedudukan tersebut.
Kedua hingga terakhir, dll.
Untuk mendapatkan kedudukan kedua hingga terakhir, ketiga hingga terakhir, dll. Anda boleh beralih dari fungsi MIN ke fungsi TERBESAR seperti ini:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
di mana k mewakili "nth terbesar". Sebagai contoh, untuk mendapatkan perlawanan kedua hingga terakhir dalam contoh di atas, anda boleh menggunakan:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Seperti sebelumnya, ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
