Formula generik
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Ringkasan
Untuk menjumlahkan julat identik secara bersyarat yang terdapat dalam lembaran kerja yang berasingan, semuanya dalam satu formula, anda boleh menggunakan fungsi SUMIF dengan LANGSUNG, dibungkus dalam SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di C9 adalah:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Penjelasan
Data pada setiap tiga helaian yang diproses kelihatan seperti ini:
Pertama sekali, perhatikan bahawa anda tidak boleh menggunakan SUMIF dengan rujukan 3D "normal" seperti ini:
Sheet1:Sheet3!D4:D5
Ini adalah "sintaks 3D" standard tetapi jika anda cuba menggunakannya dengan SUMIF, anda akan mendapat ralat # NILAI. Oleh itu, untuk mengatasi masalah ini, anda boleh menggunakan "lembaran" julat bernama yang menyenaraikan setiap helaian (tab lembaran kerja) yang ingin anda sertakan. Walau bagaimanapun, untuk membina rujukan yang akan ditafsirkan oleh Excel dengan betul, kita perlu menggabungkan nama-nama lembaran dengan julat yang perlu kita bekerjasama dan kemudian menggunakan INDIRECT untuk mendapatkan Excel untuk mengenalinya dengan betul.
Juga, kerana julat "lembaran" yang dinamakan mengandungi beberapa nilai (iaitu susunannya), hasil SUMIF dalam kes ini juga merupakan array (kadang-kadang disebut "susunan hasil). Oleh itu, kami menggunakan SUMPRODUCT untuk menanganinya, kerana SUMPRODUCT mempunyai kemampuan untuk menangani susunan secara semula jadi tanpa memerlukan Ctrl-Shift-Enter, seperti banyak formula array yang lain.
Cara lain
Contoh di atas agak rumit. Cara lain untuk mengatasi masalah ini adalah dengan melakukan jumlah bersyarat "tempatan" pada setiap helaian, kemudian gunakan jumlah 3D biasa untuk menambahkan setiap nilai pada tab ringkasan.
Untuk melakukan ini, tambahkan formula SUMIF ke setiap helaian helaian yang menggunakan sel kriteria pada helaian ringkasan. Kemudian apabila anda mengubah kriteria, semua formula SUMIF yang dipautkan akan dikemas kini.
