Formula generik
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria))
Ringkasan
Untuk mengira padanan dalam keseluruhan buku kerja, anda boleh menggunakan formula berdasarkan fungsi COUNTIF dan SUMPRODUCT. Dalam contoh yang ditunjukkan, formula dalam D5 adalah:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))
di mana "helaian" adalah julat bernama B8: B10.
Konteks
Buku kerja ini mempunyai empat lembaran kerja. Tiga lembaran kerja, "Sheet1", "Sheet2", dan "Sheet3" mengandungi 1000 nama pertama rawak dalam jadual yang kelihatan seperti ini:
Catatan: julat yang kami gunakan dalam formula, A1: Z10000, sewenang-wenang dan harus disesuaikan agar sesuai dengan data anda.
Penjelasan
Bekerja dari dalam ke luar, pertama kali kita mempunyai ungkapan ini di dalam fungsi LANGSUNG:
"'"&sheets&"'!A1:Z10000"
Kerana "helaian" adalah julat bernama yang berisi "Sheet1", "Sheet2", dan "Sheet3", kami mendapat susunan seperti ini setelah ungkapan dinilai:
("'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000")
Di dalam array, kami mempunyai tiga nilai, dan masing-masing adalah nama lembaran yang digabungkan melalui gabungan ke julat A1: Z10000. Perhatikan bahawa ini semua nilai teks.
Selanjutnya, fungsi INDIRECT digunakan untuk mengubah setiap nilai teks menjadi rujukan yang tepat, yang diberikan ke fungsi COUNTIF sebagai argumen jangkauan, bersama dengan nilai dalam D5 untuk kriteria.
Oleh kerana kami telah memberikan COUNTIF tiga julat yang berasingan, kami memperoleh tiga hasil dalam susunan seperti ini:
(5;6;5)
Setiap item adalah jumlah untuk satu helaian.
Akhirnya, SUMPRODUCT digunakan untuk menjumlahkan array, dan mengembalikan hasil 16.
