Formula generik
=COUNTIF(range,""&high)
Ringkasan
Untuk mengira nilai sel yang bukan antara dua nombor, anda boleh menggunakan fungsi COUNTIF. Dalam contoh yang ditunjukkan, formula dalam sel K5, disalin ke bawah, adalah:
=COUNTIF(C5:G5,""&J5)
Pada setiap baris baru, formula ini mengembalikan bilangan nilai bukan antara nilai rendah dan tinggi pada lajur I dan J.
Penjelasan
Matlamat contoh ini adalah untuk mengira nilai nombor yang dicatat selama 5 hari yang tidak jatuh antara dua nombor, nilai rendah, dan nilai tinggi. Dengan kata lain, untuk mengira nilai yang "berada di luar jangkauan". Perhatikan bahawa setiap baris, berlabel AG, mempunyai had rendah dan tinggi tersendiri, pada lajur I dan J.
Anda mungkin pada mulanya berfikir untuk menggunakan fungsi COUNTIFS dengan dua kriteria. Namun, kerana COUNTIFS bergabung dengan kriteria dengan logik AND, ia tidak dapat digunakan dengan dua kriteria dalam senario ini. Logik dengan nilai yang lebih rendah daripada nilai yang lebih rendah DAN lebih besar daripada nilai yang lebih tinggi akan selalu gagal dan hasilnya akan sentiasa sifar. Sebaliknya, kita memerlukan logik ATAU.
Satu penyelesaian langsung adalah menggunakan fungsi COUNTIF dua kali seperti ini:
=COUNTIF(C5:G5,""&J5)
COUNTIF pertama mengira nilai di bawah nilai di I5, dan COUNTIF kedua mengira nilai di atas nilai di J5. Apabila ditambahkan kedua-dua hasil ini menangani logik yang diperlukan dengan betul: kurang dari I5 ATAU lebih besar daripada J5. Perhatikan operator yang lebih besar daripada (">") dan kurang daripada ("<") digabungkan dengan rujukan sel dengan operator ampersand (&), satu kebiasaan fungsi RACON.
Dengan SUMPRODUCT
Penyelesaian yang sedikit lebih elegan adalah menggunakan fungsi SUMPRODUCT dengan dua ungkapan logik seperti ini:
=SUMPRODUCT((C5:G5J5))
Perhatikan bahawa kita tidak perlu menggunakan gabungan dengan rujukan sel seperti COUNTIF di atas; ungkapan standard berfungsi dengan baik.
Ini adalah contoh penggunaan algebra boolean dengan penambahan (+), yang menghasilkan logik ATAU. Apabila ungkapan ini dinilai, kita mempunyai dua susunan nilai BENAR dan SALAH seperti ini:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,TRUE)+(FALSE,FALSE,TRUE,FALSE,FALSE))
Operasi matematik secara automatik menjadikan nilai BETUL dan SALAH kepada 1 dan 0. Hasilnya dapat dilihat seperti ini:
=SUMPRODUCT((0,0,0,0,1)+(0,0,1,0,0))
Ini menghasilkan satu array yang mengandungi dua 1s:
=SUMPRODUCT((0,0,1,0,1))
Dengan hanya satu array untuk diproses, SUMPRODUCT menjumlahkan item dalam array dan mengembalikan hasil akhir dari 2.
