Formula umum
(=MATCH(2,1/(range"")))
Ringkasan
Untuk mendapatkan posisi relatif terakhir (yaitu baris terakhir, kolom terakhir) untuk data campuran yang mungkin berisi sel kosong, Anda dapat menggunakan fungsi MATCH seperti yang dijelaskan di bawah ini.
Catatan: ini adalah rumus array dan harus dimasukkan dengan Control + Shift + Enter.
Dalam contoh yang ditampilkan, rumus dalam E5 adalah:
(=MATCH(2,1/(B4:B10"")))
Posisi * relatif * terakhir, bukan baris pada lembar kerja
Saat membuat rumus yang lebih canggih, seringkali perlu untuk mencari tahu lokasi terakhir data dalam daftar. Bergantung pada datanya, ini bisa menjadi baris terakhir dengan data, kolom terakhir dengan data, atau perpotongan keduanya. Kami ingin * posisi relatif * terakhir di dalam rentang tertentu, bukan nomor baris pada lembar kerja:
Penjelasan
Rumus ini menggunakan fungsi MATCH yang dikonfigurasi untuk menemukan posisi sel tidak kosong terakhir dalam suatu rentang.
Bekerja dari dalam ke luar, array pencarian di dalam MATCH dibangun 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 larik adalah 1 atau # DIV / 0! kesalahan.
MATCH kemudian disetel untuk mencocokkan nilai 2 dalam "mode pertandingan perkiraan", dengan menghilangkan argumen ke-3 dihilangkan.
Karena nilai pencarian 2 tidak akan pernah ditemukan, MATCH akan selalu menemukan 1 terakhir dalam array pencarian, yang sesuai dengan sel tidak kosong terakhir.
Pendekatan ini akan bekerja dengan semua jenis data, termasuk angka, teks, tanggal, dll. Ia juga bekerja dengan string teks nol yang dikembalikan oleh rumus seperti ini:
=IF(A1<100,"")
Rentang dinamis
Anda dapat menggunakan rumus ini untuk membuat rentang dinamis dengan fungsi lain seperti INDEX dan OFFSET. Lihat tautan di bawah untuk contoh dan penjelasan:
- Rentang dinamis dengan INDEX dan COUNTA
- Rentang dinamis dengan OFFSET dan COUNTA
Inspirasi untuk artikel ini datang dari buku Mike Girvin yang luar biasa, Control + Shift + Enter, di mana Mike melakukan pekerjaan yang hebat dalam menjelaskan konsep "posisi relatif terakhir".
