Formula generik
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Ringkasan
Untuk mengira perbezaan minimum antara dua set nilai, mengabaikan kes di mana salah satu nilai kosong, anda boleh menggunakan formula larik berdasarkan fungsi MIN dan IF. Dalam contoh yang ditunjukkan, formula dalam F4 adalah:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
yang mengembalikan 115, minimum kos penjualan, mengabaikan kes di mana salah satu nilai kosong.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.
Penjelasan
Dalam contoh yang ditunjukkan, tujuannya adalah untuk mengira perbezaan minimum penjualan tolak kos, tetapi hanya apabila kedua-dua nilai tersebut dimasukkan. Sekiranya salah satu nilai kosong, hasilnya harus diabaikan. Untuk mengesahkan bahawa kedua-dua nilai tersebut tersedia, fungsi IF dikonfigurasi untuk menggunakan logik boolean dengan ungkapan ini:
(B5:B12"")*(C5:C12"")
Kerana setiap julat mengandungi 8 sel, hasil operasi ini adalah array seperti ini:
(1;1;1;0;1;1;0;0)
Susunan ini bertindak sebagai penapis. Sekiranya nilainya adalah 1, JIKA membenarkan nilai melewati MIN. Nilai perbezaan sebenar dikira dengan operasi array yang lain:
B5:B12-C5:C12
yang menghasilkan hasil ini:
(150;255;125;1100;150;115;-890;1025)
Setelah ujian logik dinilai, susunan yang dilewatkan ke dalam fungsi MIN kelihatan seperti ini:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Perhatikan bahawa "nilai perbezaan" untuk baris di mana Jualan atau Kos kosong sekarang SALAH. Fungsi MIN secara automatik mengabaikan nilai PALSU dan mengembalikan minimum bilangan yang tinggal, 115.
Perbezaan maksimum mengabaikan kosong
Untuk mengembalikan perbezaan maksimum tanpa mengira nilai kosong, anda boleh menggantikan MAX dengan MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Formula ini berfungsi dengan cara yang sama seperti yang dijelaskan di atas.
Dengan MINIFS dan tiang penolong
Fungsi MINIFS dapat digunakan untuk menyelesaikan masalah ini, tetapi memerlukan penggunaan kolom penolong dengan formula seperti ini:
=B5-C5
Dengan formula di atas di ruangan D, MINIFS dapat digunakan seperti ini:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Ini bukan formula larik, dan tidak perlu dimasukkan dengan kawalan + shift + enter.
