Formula generik
=MATCH(bignum,range)
Ringkasan
Untuk mendapatkan kedudukan relatif terakhir (iaitu baris terakhir, lajur terakhir) untuk data berangka (dengan atau tanpa sel kosong), anda boleh menggunakan fungsi MATCH dengan apa yang disebut "angka besar".
Dalam contoh yang ditunjukkan, formula di E5 adalah:
=MATCH(9.99E+307,B4:B9)
Kedudukan * relatif * terakhir, bukan baris pada lembaran kerja
Semasa membina formula lanjutan yang membuat julat dinamik, sering kali perlu mengetahui lokasi data terakhir dalam senarai. Bergantung pada data, ini mungkin baris terakhir dengan data, lajur terakhir dengan data, atau persimpangan keduanya. Nota: kami mahukan * kedudukan relatif * terakhir dalam julat tertentu, bukan nombor baris pada lembaran kerja:
Penjelasan
Rumus ini menggunakan fungsi MATCH dalam mod padanan anggaran untuk mencari nilai angka terakhir dalam julat. Pencocokan anggaran diaktifkan dengan menetapkan argumen ke-3 dalam PERTANDINGAN 1, atau menghilangkan argumen ini, yang secara lalai menjadi 1.
Nilai carian adalah apa yang disebut "angka besar" (kadang-kadang disingkat "bignum") yang sengaja lebih besar daripada nilai apa pun yang akan muncul dalam julat.
Hasilnya ialah MATCH akan "mundur" ke nilai angka terakhir dalam julat, dan mengembalikan kedudukan itu.
Catatan: pendekatan ini berfungsi dengan baik dengan sel kosong dalam julat, tetapi tidak boleh dipercayai dengan data campuran yang merangkumi nombor dan teks.
Mengenai bignum
Nombor terbesar yang dapat dikendalikan oleh Excel ialah 9.99999999999999E + 307.
Apabila menggunakan MATCH dengan cara ini, anda dapat menggunakan sebilangan besar yang dijamin lebih besar daripada nilai dalam julat, misalnya:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
Kelebihan menggunakan 9.99E + 307 atau yang serupa, adalah bahawa ia (1) sebilangan besar dan (2) dapat dikenali sebagai tempat letak untuk "bilangan besar". Anda akan melihatnya digunakan dalam pelbagai formula Excel lanjutan.
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 cemerlang Mike Girvin Control + Shift + Enter, di mana Mike menerangkan konsep "kedudukan relatif terakhir".
