Formula generik
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Ringkasan
Untuk mencari beberapa lembaran kerja dalam buku kerja untuk mendapatkan nilai dan mengembalikan kiraan, anda boleh menggunakan formula berdasarkan fungsi COUNTIF dan INDIRECT. Dengan beberapa persediaan awal, anda boleh menggunakan pendekatan ini untuk mencari keseluruhan buku kerja untuk mendapatkan nilai tertentu. Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Konteks - data sampel
Buku kerja mengandungi 4 lembaran kerja keseluruhan. Sheet1 , Sheet2 , dan Sheet3 masing-masing mengandungi 1000 nama pertama rawak yang kelihatan seperti ini:
Penjelasan
Julat B7: B9 mengandungi nama helaian yang ingin kami sertakan dalam carian. Ini hanya rentetan teks, dan kita perlu melakukan beberapa usaha agar ia dikenali sebagai rujukan helaian yang sah.
Berfungsi dari dalam ke luar, ungkapan ini digunakan untuk membina rujukan lembaran penuh:
"'"&B7&"'!"&"1:1048576"
Petikan tunggal ditambahkan untuk membolehkan nama lembaran dengan spasi, dan tanda seru adalah sintaks standard untuk julat yang merangkumi nama lembaran. Teks "1: 1048576" adalah julat yang merangkumi setiap baris dalam lembaran kerja.
Setelah B7 dinilai, dan nilai digabungkan, ungkapan di atas mengembalikan:
"'Sheet1'!1:1048576"
yang masuk ke fungsi INDIRECT sebagai argumen 'ref_text'. INDIRECT menilai teks ini dan mengembalikan rujukan standard ke setiap sel di Helaian1 . Ini masuk ke fungsi COUNTIF sebagai julat. Kriteria diberikan sebagai rujukan mutlak untuk C4 (dikunci sehingga formula dapat disalin ke bawah ruang C).
COUNTIF kemudian mengembalikan kiraan semua sel dengan nilai yang sama dengan "mary", 25 dalam kes ini.
Nota: COUNTIF tidak peka huruf besar kecil.
Mengandungi vs Sama
Sekiranya anda ingin mengira semua sel yang mengandungi nilai dalam C4, dan bukannya semua sel yang sama dengan C4, anda boleh menambahkan wildcard ke kriteria seperti ini:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Sekarang COUNTIF akan mengira sel dengan substring "John" di mana sahaja di dalam sel.
Persembahan
Secara amnya, bukan merupakan amalan yang baik untuk menentukan julat yang merangkumi semua sel lembaran kerja. Melakukannya boleh menyebabkan masalah prestasi, kerana jaraknya merangkumi berjuta-juta dan berjuta-juta sel. Dalam contoh ini, masalahnya diperparah, kerana rumus menggunakan fungsi INDIRECT, yang merupakan fungsi tidak menentu. Fungsi meruap dikira semula pada setiap perubahan lembaran kerja, sehingga impak terhadap prestasi sangat besar.
Sekiranya boleh, hadkan jarak ke ukuran yang masuk akal. Sebagai contoh, jika anda tahu data tidak akan muncul setelah baris 1000, anda boleh mencari 1000 baris pertama seperti ini:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
