Obecný vzorec
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
souhrn
Chcete-li zvýraznit 3 nejmenší hodnoty, které splňují konkrétní kritéria, můžete použít maticový vzorec založený na funkcích AND a SMALL. V zobrazeném příkladu je vzorec použitý pro podmíněné formátování:
=AND($B5=$E$5,$C5<=SMALL(IF(color=$E$5,amount),3))
Kde „barva“ je pojmenovaný rozsah B5: B12 a „množství“ je pojmenovaný rozsah C5: C12.
Vysvětlení
Uvnitř funkce AND jsou dvě logická kritéria. První je přímočarý a zajišťuje, že jsou zvýrazněny pouze buňky, které odpovídají barvě v E5:
$B3=$E$5
Druhý test je složitější:
$C3<=SMALL(IF(color=$E$5,amount),3)
Zde filtrujeme částky, abychom se ujistili, že budou zachovány pouze hodnoty spojené s barvou v E5 (modrá). Filtrování se provádí pomocí funkce IF takto:
IF(color=$E$5,amount)
Výsledné pole vypadá takto:
(FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500)
Všimněte si, že hodnota ze sloupce částky přežije, pouze pokud je barva „modrá“. Ostatní částky jsou nyní NEPRAVDA.
Dále toto pole přejde do funkce SMALL s hodnotou ak 3 a funkce SMALL vrátí hodnotu „3. nejmenší“ 300. Logika druhého logického testu se sníží na:
$C3<=300
Když jsou obě logické podmínky vráceny PRAVDA, spustí se podmíněné formátování a buňky se zvýrazní.
Poznámka: toto je maticový vzorec, ale nevyžaduje ovládání + shift + enter.
