Formula generică
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
rezumat
Pentru a evidenția cele mai mici 3 valori care îndeplinesc criterii specifice, puteți utiliza o formulă matrice bazată pe funcțiile ȘI ȘI MIC. În exemplul prezentat, formula utilizată pentru formatarea condiționată este:
=AND($B5=$E$5,$C5<=SMALL(IF(color=$E$5,amount),3))
Unde „culoare” este intervalul numit B5: B12 și „suma” este intervalul numit C5: C12.
Explicaţie
În interiorul funcției ȘI există două criterii logice. Prima este simplă și se asigură că sunt evidențiate doar celulele care se potrivesc culorii din E5:
$B3=$E$5
Al doilea test este mai complex:
$C3<=SMALL(IF(color=$E$5,amount),3)
Aici, filtrăm cantitățile pentru a ne asigura că numai valorile asociate culorii din E5 (albastru) sunt păstrate. Filtrarea se face cu funcția IF astfel:
IF(color=$E$5,amount)
Matricea rezultată arată astfel:
(FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500)
Observați că valoarea din coloana sumă supraviețuiește numai dacă culoarea este „albastru”. Alte sume sunt acum FALSE.
Apoi, această matrice intră în funcția SMALL cu o valoare ak de 3, iar SMALL returnează valoarea "a 3-a cea mai mică", 300. Logica pentru al doilea test logic se reduce la:
$C3<=300
Când ambele condiții logice sunt returnate TRUE, formatarea condițională este declanșată și celulele sunt evidențiate.
Notă: aceasta este o formulă matrice, dar nu necesită control + shift + enter.
