Formula umum
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
Ringkasan
Untuk menyorot 3 nilai terkecil yang memenuhi kriteria tertentu, Anda dapat menggunakan rumus array berdasarkan fungsi DAN dan KECIL. Dalam contoh yang diperlihatkan, rumus yang digunakan untuk pemformatan bersyarat adalah:
=AND($B5=$E$5,$C5<=SMALL(IF(color=$E$5,amount),3))
Di mana "warna" adalah rentang bernama B5: B12 dan "jumlah" adalah rentang bernama C5: C12.
Penjelasan
Di dalam fungsi AND ada dua kriteria logis. Yang pertama sangat mudah, dan memastikan bahwa hanya sel yang cocok dengan warna di E5 yang disorot:
$B3=$E$5
Tes kedua lebih kompleks:
$C3<=SMALL(IF(color=$E$5,amount),3)
Di sini, kami memfilter jumlah untuk memastikan bahwa hanya nilai yang terkait dengan warna dalam E5 (biru) yang dipertahankan. Pemfilteran dilakukan dengan fungsi IF seperti ini:
IF(color=$E$5,amount)
Array yang dihasilkan terlihat seperti ini:
(FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500)
Perhatikan nilai dari kolom jumlah hanya bertahan jika warnanya "biru". Jumlah lainnya sekarang SALAH.
Selanjutnya, array ini masuk ke fungsi SMALL dengan nilai ak 3, dan SMALL mengembalikan nilai "terkecil ke-3", 300. Logika untuk uji logika kedua direduksi menjadi:
$C3<=300
Saat kedua kondisi logis mengembalikan TRUE, pemformatan bersyarat dipicu dan sel disorot.
Catatan: ini adalah rumus array, tapi tidak memerlukan control + shift + enter.
