Rumus Excel: Hitung nilai yang tidak bertolak ansur -

Isi kandungan

Formula generik

=SUMPRODUCT(--(ABS(data-target)>tolerance))

Ringkasan

Untuk menghitung nilai yang tidak bertoleransi dalam satu set data, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT dan ABS. Dalam contoh yang ditunjukkan, formula dalam F6 adalah:

=SUMPRODUCT(--(ABS(data-target)>tolerance))

di mana "data" adalah julat bernama B5: B14, "target" adalah julat bernama F4, dan "toleransi" adalah julat bernama F5.

Penjelasan

Formula ini mengira berapa banyak nilai yang tidak berada dalam lingkungan toleransi tetap. Variasi setiap nilai dikira dengan ini:

ABS(data-target)

Kerana julat bernama "data" berisi 10 nilai, mengurangi nilai target di F4 akan membuat array dengan 10 hasil:

(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)

Fungsi ABS mengubah sebarang nilai negatif menjadi positif:

(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)

Susunan ini dibandingkan dengan toleransi tetap di F5:

ABS(data-target)>tolerance

Hasilnya adalah nilai array atau TRUE FALSE, dan negatif berganda mengubahnya menjadi nilai nol dan angka nol. Di dalam SUMPRODUCT, susunan terakhir kelihatan seperti ini:

(0;0;1;0;1;0;0;1;0;1)

di mana nol mewakili nilai dalam toleransi, dan 1 menunjukkan nilai di luar toleransi. SUMPRODUCT kemudian menjumlahkan item dalam array, dan mengembalikan hasil akhir, 4.

Semua nilai dalam toleransi

Untuk mengembalikan "Ya" jika semua nilai dalam julat data berada dalam toleransi tertentu, dan "Tidak" jika tidak, anda boleh menyesuaikan formula seperti ini:

=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")

Sekiranya SUMPRODUCT mengembalikan nombor yang lebih besar daripada sifar, JIKA akan menilai ujian logik sebagai BENAR. Hasil sifar akan dinilai sebagai SALAH.