Formula generik
(=MATCH(2,1/(range"")))
Ringkasan
Untuk mendapatkan kedudukan relatif terakhir (iaitu baris terakhir, lajur terakhir) untuk data campuran yang mungkin mengandungi sel kosong, anda boleh menggunakan fungsi MATCH seperti yang dijelaskan di bawah.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.
Dalam contoh yang ditunjukkan, formula di E5 adalah:
(=MATCH(2,1/(B4:B10"")))
Kedudukan * relatif * terakhir, bukan baris pada lembaran kerja
Semasa membina formula yang lebih maju, selalunya perlu mengetahui lokasi data terakhir dalam senarai. Bergantung pada data, ini mungkin baris terakhir dengan data, lajur terakhir dengan data, atau persimpangan keduanya. Kami mahukan * kedudukan relatif * terakhir dalam julat tertentu bukan nombor baris pada lembaran kerja:
Penjelasan
Rumus ini menggunakan fungsi MATCH yang dikonfigurasi untuk mencari kedudukan sel tidak kosong terakhir dalam julat.
Berfungsi dari dalam ke luar, susunan carian di dalam MATCH dibina seperti ini:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Catatan: semua nilai dalam array sama ada 1 atau # DIV / 0! kesilapan.
MATCH kemudian diatur agar sesuai dengan nilai 2 dalam "modus perkiraan hampir", dengan menghilangkan argumen ke-3 dihilangkan.
Kerana nilai carian 2 tidak akan pernah dijumpai, MATCH akan selalu mencari 1 terakhir dalam tatasusunan pencarian, yang sesuai dengan sel terakhir yang tidak kosong.
Pendekatan ini akan berfungsi dengan segala jenis data, termasuk nombor, teks, tarikh, dll. Ia juga berfungsi dengan rentetan teks kosong yang dikembalikan dengan formula seperti ini:
=IF(A1<100,"")
Julat dinamik
Anda boleh menggunakan formula ini untuk membuat julat dinamik dengan fungsi lain seperti INDEX dan OFFSET. Lihat pautan di bawah untuk contoh dan penjelasan:
- Julat dinamik dengan INDEX dan COUNTA
- Julat dinamik dengan OFFSET dan COUNTA
Inspirasi untuk artikel ini berasal dari buku Mike Girvin yang sangat baik iaitu Control + Shift + Enter, di mana Mike melakukan pekerjaan yang hebat untuk menjelaskan konsep "kedudukan relatif terakhir".
