Formula umum
(=ROW(range)-ROW(range.firstcell)+1)
Ringkasan
Untuk mendapatkan sekumpulan lengkap nomor baris relatif dalam suatu rentang, Anda bisa menggunakan rumus array berdasarkan fungsi ROW. Dalam contoh yang ditampilkan, rumus di B5: B11 adalah:
(=ROW(B5:B11)-ROW(B5)+1)
Catatan: ini adalah rumus array yang harus dimasukkan dengan Control + Shift + Enter. Jika Anda memasukkan ini di lembar kerja (dan tidak di dalam rumus lain), buat pilihan yang menyertakan lebih dari satu baris, masukkan rumus, dan konfirmasikan dengan Control + Shift + Enter.
Rumus ini akan terus menghasilkan angka relatif bahkan ketika kisaran dipindahkan. Namun, ini bukan pilihan yang baik jika baris perlu diurutkan, dihapus, atau ditambahkan, karena rumus array akan mencegah perubahan. Opsi formula yang dijelaskan di sini akan bekerja lebih baik.
Penjelasan
Fungsi ROW pertama menghasilkan array 7 angka seperti ini:
(5;6;7;8;9;10;11)
Fungsi ROW kedua menghasilkan array hanya dengan satu item seperti ini:
(5)
yang kemudian dikurangi dari larik pertama untuk menghasilkan:
(0;1;2;3;4;5;6)
Akhirnya, 1 ditambahkan untuk mendapatkan:
(1;2;3;4;5;6;7)
Versi generik dengan rentang bernama
Dengan rentang bernama, Anda dapat membuat versi rumus yang lebih umum menggunakan fungsi MIN atau fungsi INDEX. Misalnya, dengan rentang bernama "list", Anda dapat menggunakan MIN seperti ini:
(ROW(list)-MIN(ROW(list))+1)
Dengan INDEX, kami mengambil referensi pertama dalam rentang bernama, dan menggunakan ROW untuk itu:
(=ROW(list)-ROW(INDEX(list,1,1))+1)
Anda akan sering melihat rumus "baris relatif" seperti ini di dalam rumus array kompleks yang membutuhkan nomor baris untuk menghitung hasilnya.
Dengan URUTAN
Dengan fungsi SEQUENCE, rumus untuk mengembalikan nomor baris relatif untuk suatu rentang sederhana:
=SEQUENCE(ROWS(range))
Fungsi ROWS menyediakan jumlah baris, yang dikembalikan ke fungsi SEQUENCE. SEQUENCE kemudian membangun array angka, dimulai dengan angka 1. Jadi, mengikuti contoh asli di atas, rumus di bawah ini mengembalikan hasil yang sama:
=SEQUENCE(ROWS(B5:B11)) // returns (1;2;3;4;5;6;7)
Catatan: rumus SEQUENCE adalah fungsi array dinamis baru yang hanya tersedia di Excel 365.
