Formula generik
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
Ringkasan
Untuk menonjolkan 3 nilai terkecil yang memenuhi kriteria tertentu, anda boleh menggunakan formula larik berdasarkan fungsi DAN dan KECIL. Dalam contoh yang ditunjukkan, formula yang digunakan untuk pemformatan bersyarat adalah:
=AND($B5=$E$5,$C5<=SMALL(IF(color=$E$5,amount),3))
Di mana "warna" adalah julat bernama B5: B12 dan "jumlah" adalah julat bernama C5: C12.
Penjelasan
Di dalam fungsi AND terdapat dua kriteria logik. Yang pertama mudah, dan memastikan bahawa hanya sel yang sesuai dengan warna di E5 yang diserlahkan:
$B3=$E$5
Ujian kedua lebih kompleks:
$C3<=SMALL(IF(color=$E$5,amount),3)
Di sini, kami menyaring jumlah untuk memastikan bahawa hanya nilai yang berkaitan dengan warna dalam E5 (biru) yang dipertahankan. Penapisan dilakukan dengan fungsi IF seperti ini:
IF(color=$E$5,amount)
Susunan yang dihasilkan kelihatan seperti ini:
(FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500)
Perhatikan nilai dari lajur jumlah hanya akan bertahan jika warnanya "biru". Jumlah lain sekarang SALAH.
Seterusnya, susunan ini masuk ke fungsi KECIL dengan nilai ak 3, dan KECIL mengembalikan nilai "terkecil ke-3", 300. Logik untuk ujian logik kedua mengurangkan kepada:
$C3<=300
Apabila kedua-dua keadaan logik kembali BETUL, pemformatan bersyarat dipicu dan sel diserlahkan.
Catatan: ini adalah formula larik, tetapi tidak memerlukan kawalan + shift + enter.
