Ringkasan
Untuk mengkonfigurasi COUNTIFS (atau COUNTIF) dengan julat pemboleh ubah, anda boleh menggunakan fungsi OFFSET. Dalam contoh yang ditunjukkan, formula dalam B11 adalah:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Formula ini mengira sel-sel yang tidak kosong dalam julat yang bermula pada B5 dan berakhir 2 baris di atas sel tempat formula itu tinggal. Formula yang sama disalin dan ditampal 2 baris di bawah entri terakhir dalam data seperti yang ditunjukkan.
Penjelasan
Dalam contoh yang ditunjukkan, formula dalam B11 adalah:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Bekerja dari dalam ke luar, kerja menyusun julat berubah dilakukan oleh fungsi OFFSET di sini:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET mempunyai lima argumen dan dikonfigurasi seperti ini:
- rujukan = B $ 5, bermula pada sel B5, baris terkunci
- baris = 0, mengimbangi baris sifar dari sel permulaan
- cols = 0, mengimbangi lajur sifar permulaan sel
- tinggi = ROW () - ROW (B $ 5) -1 = 5 baris tinggi
- lebar = lebar 1 lajur
Untuk mengetahui ketinggian julat dalam baris, kami menggunakan fungsi ROW seperti ini:
ROW()-ROW(B$5)-1 // work out height
Oleh kerana ROW () mengembalikan nombor baris sel "semasa" (iaitu sel yang terdapat dalam formula), kita dapat mempermudah seperti ini:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Dengan konfigurasi di atas, OFFSET mengembalikan julat B5: B9 terus ke COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Perhatikan rujukan ke B $ 5 dalam formula di atas adalah rujukan campuran, dengan lajur relatif dan baris terkunci. Ini membolehkan formula disalin ke lajur lain dan masih berfungsi. Contohnya, setelah disalin ke C12, formula adalah:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Catatan: OFFSET adalah fungsi yang tidak menentu dan boleh menyebabkan masalah prestasi pada lembaran kerja yang besar atau kompleks.
Dengan LANGSUNG dan ALAMAT
Pendekatan lain adalah menggunakan formula berdasarkan fungsi LANGSUNG dan ALAMAT. Dalam kes ini, kami mengumpulkan julat sebagai teks, kemudian menggunakan LANGSUNG untuk menilai teks sebagai rujukan. Rumus dalam B11 adalah:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Fungsi ADDRESS digunakan untuk membina julat seperti ini:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Dalam contoh pertama ALAMAT, kami memberikan nombor baris sebagai nilai hardcoded 5, dan memberikan nombor lajur dengan fungsi COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Dalam contoh kedua, kami menyediakan nombor baris "semasa" tolak 2, dan lajur semasa dengan fungsi COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Setelah menggabungkan kedua-dua nilai ini bersama-sama, kami mempunyai:
"$B$5:$B$9" // as text
Perhatikan ini adalah rentetan teks. Untuk menukar ke rujukan yang sah, kita perlu menggunakan LANGSUNG:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Akhirnya, formula dalam B11 menjadi:
=COUNTIFS($B$5:$B$9,"") // returns 4
Catatan: INDIRECT adalah fungsi yang tidak menentu dan boleh menyebabkan masalah prestasi pada lembaran kerja yang besar atau kompleks.
