Formula umum
=MATCH(bignum,range)
Ringkasan
Untuk mendapatkan posisi relatif terakhir (yaitu baris terakhir, kolom terakhir) untuk data numerik (dengan atau tanpa sel kosong), Anda dapat menggunakan fungsi MATCH dengan apa yang disebut "bilangan besar".
Dalam contoh yang ditampilkan, rumus dalam E5 adalah:
=MATCH(9.99E+307,B4:B9)
Posisi * relatif * terakhir, bukan baris pada lembar kerja
Saat membuat rumus tingkat lanjut yang membuat rentang dinamis, 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. Catatan: kami menginginkan * posisi relatif * terakhir di dalam rentang tertentu, bukan nomor baris pada lembar kerja:
Penjelasan
Rumus ini menggunakan fungsi MATCH dalam mode perkiraan pencocokan untuk menemukan nilai numerik terakhir dalam suatu rentang. Perkiraan kecocokan diaktifkan dengan menyetel oleh argumen ke-3 dalam MATCH ke 1, atau menghilangkan argumen ini, yang defaultnya ke 1.
Nilai pencarian adalah apa yang disebut "bilangan besar" (kadang-kadang disingkat "bignum") yang sengaja lebih besar daripada nilai apa pun yang akan muncul dalam rentang.
Hasilnya adalah MATCH akan "mundur" ke nilai numerik terakhir dalam rentang, dan mengembalikan posisi tersebut.
Catatan: pendekatan ini berfungsi baik dengan sel kosong dalam rentang, tetapi tidak dapat diandalkan dengan data campuran yang menyertakan angka dan teks.
Tentang bignum
Angka terbesar yang dapat ditangani Excel adalah 9.99999999999999E + 307.
Saat menggunakan MATCH dengan cara ini, Anda dapat menggunakan bilangan besar apa pun yang dijamin lebih besar dari nilai apa pun dalam rentang tersebut, misalnya:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
Keuntungan menggunakan 9.99E + 307 atau yang serupa, adalah (1) angka yang sangat besar dan (2) dapat dikenali sebagai placeholder untuk "angka besar". Anda akan melihatnya digunakan dalam berbagai rumus Excel tingkat lanjut.
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 menjelaskan konsep "posisi relatif terakhir".
