Ringkasan
Untuk mengkonfigurasi COUNTIFS (atau COUNTIF) dengan rentang variabel, Anda dapat menggunakan fungsi OFFSET. Dalam contoh yang ditunjukkan, rumus di B11 adalah:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Rumus ini menghitung sel yang tidak kosong dalam rentang yang dimulai dari B5 dan mengakhiri 2 baris di atas sel tempat rumus berada. Rumus yang sama disalin dan ditempelkan 2 baris di bawah entri terakhir dalam data seperti yang ditunjukkan.
Penjelasan
Dalam contoh yang ditampilkan, rumus di B11 adalah:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Bekerja dari dalam ke luar, pekerjaan menyiapkan rentang variabel dilakukan oleh fungsi OFFSET di sini:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET memiliki lima argumen dan dikonfigurasi seperti ini:
- referensi = B $ 5, dimulai dari sel B5, baris terkunci
- baris = 0, offset baris nol dari sel awal
- cols = 0, offset sel awal kolom nol
- tinggi = ROW () - ROW (B $ 5) -1 = tinggi 5 baris
- lebar = lebar 1 kolom
Untuk mengetahui ketinggian rentang dalam baris, kami menggunakan fungsi ROW seperti ini:
ROW()-ROW(B$5)-1 // work out height
Karena ROW () mengembalikan nomor baris dari sel "saat ini" (yaitu sel tempat rumus berada), kita dapat menyederhanakan seperti ini:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Dengan konfigurasi di atas, OFFSET mengembalikan rentang B5: B9 langsung ke COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Perhatikan referensi B $ 5 pada rumus di atas adalah referensi campuran, dengan kolom relatif dan baris terkunci. Ini memungkinkan rumus disalin ke kolom lain dan masih berfungsi. Misalnya, setelah disalin ke C12, rumusnya adalah:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Catatan: OFFSET adalah fungsi yang mudah menguap dan dapat menyebabkan masalah kinerja di lembar kerja yang besar atau kompleks.
Dengan INDIRECT dan ADDRESS
Pendekatan lain adalah dengan menggunakan rumus yang didasarkan pada fungsi INDIRECT dan ADDRESS. Dalam hal ini, kami mengumpulkan rentang sebagai teks, kemudian menggunakan INDIRECT untuk mengevaluasi teks tersebut sebagai referensi. Rumus di B11 adalah:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Fungsi ADDRESS digunakan untuk membuat rentang seperti ini:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Pada contoh pertama ADDRESS, kami memberikan nomor baris sebagai nilai hardcode 5, dan memberikan nomor kolom dengan fungsi COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Dalam contoh kedua, kami menyediakan nomor baris "saat ini" dikurangi 2, dan kolom saat ini dengan fungsi COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Setelah menggabungkan kedua nilai ini bersama-sama, kami memiliki:
"$B$5:$B$9" // as text
Perhatikan ini adalah string teks. Untuk mengonversi ke referensi yang valid, kita perlu menggunakan TIDAK LANGSUNG:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Akhirnya, rumus di B11 menjadi:
=COUNTIFS($B$5:$B$9,"") // returns 4
Catatan: INDIRECT adalah fungsi yang mudah menguap dan dapat menyebabkan masalah kinerja di lembar kerja yang besar atau kompleks.
