Všeobecný vzorec
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
Zhrnutie
Ak chcete zvýrazniť 3 najmenšie hodnoty, ktoré vyhovujú konkrétnym kritériám, môžete použiť vzorec poľa založený na funkciách AND a SMALL. V zobrazenom príklade je vzorec použitý na podmienené formátovanie:
=AND($B5=$E$5,$C5<=SMALL(IF(color=$E$5,amount),3))
Kde „farba“ je pomenovaný rozsah B5: B12 a „množstvo“ je pomenovaný rozsah C5: C12.
Vysvetlenie
Vo vnútri funkcie AND existujú dve logické kritériá. Prvý je priamy a zaisťuje, že sú zvýraznené iba bunky, ktoré zodpovedajú farbe v E5:
$B3=$E$5
Druhý test je zložitejší:
$C3<=SMALL(IF(color=$E$5,amount),3)
Tu filtrujeme množstvá, aby sme sa ubezpečili, že sa zachovajú iba hodnoty spojené s farbou v E5 (modrá). Filtrovanie sa vykonáva pomocou funkcie IF takto:
IF(color=$E$5,amount)
Výsledné pole vyzerá takto:
(FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500)
Všimnite si, že hodnota zo stĺpca sumy zostane zachovaná, iba ak je farba „modrá“. Ostatné sumy sú teraz NEPRAVDIVÉ.
Ďalej toto pole prechádza do funkcie SMALL s ak hodnotou 3 a SMALL vráti hodnotu „3. najmenšej“, 300. Logika druhého logického testu sa zníži na:
$C3<=300
Keď sú obidve logické podmienky vrátené TRUE, spustí sa podmienené formátovanie a bunky sa zvýraznia.
Poznámka: toto je vzorec poľa, ale nevyžaduje ovládanie + shift + enter.
