Formula generik
=SUMPRODUCT(--NOT(ISERROR(range)))
Ringkasan
Untuk mengira jumlah sel yang tidak mengandungi ralat, anda boleh menggunakan fungsi ISERROR dan NOT, yang dibungkus dalam fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di E5 adalah:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Penjelasan
Dalam contoh ini, tujuannya adalah untuk mengira jumlah sel dalam julat yang tidak mengandungi kesalahan.
Bekerja dari dalam ke luar, pertama kami menggunakan fungsi ISERROR pada keseluruhan julat:
ISERROR(B5:B14) // check all 10 cells
Oleh kerana terdapat sepuluh sel dalam julat B5: B14, ISERROR mengembalikan array dengan sepuluh hasil seperti ini:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Di sini, setiap nilai BENAR menunjukkan nilai sel yang merupakan ralat. Oleh kerana tujuannya adalah untuk mengira sel yang tidak mengandungi kesalahan, kami membalikkan hasil ini dengan fungsi NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
yang mengembalikan:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Perhatikan bahawa setiap nilai BENAR sekarang sesuai dengan sel yang tidak mengandungi kesalahan. Susunan ini sekarang dalam format yang betul - nilai BENAR menunjukkan sel tanpa kesalahan, nilai SALAH menunjukkan sel dengan kesalahan.
Oleh kerana SUMPRODUCT hanya berfungsi dengan data berangka, langkah seterusnya adalah menukar nilai BENAR dan SALAH ke setara angka mereka, 1 dan 0. Kami melakukan ini dengan negatif ganda (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Susunan yang dihasilkan kelihatan seperti ini:
(1;0;1;1;1;0;1;1;1;0)
Akhirnya, SUMPRODUCT menjumlahkan item dalam array ini dan mengembalikan jumlahnya, yang dalam contohnya adalah nombor 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Fungsi ISERR
Seperti fungsi ISERROR, fungsi ISERR mengembalikan BENAR apabila nilai adalah ralat. Perbezaannya ialah ISERR mengabaikan kesalahan # N / A. Sekiranya anda ingin mengira sel yang tidak mengandungi kesalahan, dan mengabaikan kesalahan # N / A, anda boleh menggantikan ISERR dengan ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
Pilihan SUM
Anda juga dapat menggunakan fungsi SUM untuk menghitung kesalahan. Struktur formula adalah sama:
=SUM(--NOT(ISERROR(B5:B14)))
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
