Excel

Maksimum hvis flere kriterier

Maximum If Multiple Criteria

Excel-formel: Maksimum hvis flere kriterierGenerisk formel
{= MAX ( IF (rng1=criteria1, IF (rng2=criteria2,values)))}
Resumé

For at få den maksimale værdi i et datasæt baseret på mere end et kriterium kan du bruge en matrixformel baseret på MAX- og IF-funktionerne. I det viste eksempel er formlen i I6:

 
{= MAX ( IF (color=G6, IF (item=H6,price)))}

Med en farve på 'rød' og 'hat' er resultatet $ 11,00



Bemærk: Dette er en matrixformel og skal indtastes ved hjælp af Ctrl + Skift + indtastet



Forklaring

Dette eksempel bruger følgende navngivne intervaller : 'farve' = B6: B14, 'vare' = C6: C14 og 'pris' = E6: E14. Målet er at finde den maksimale pris for en given farve og vare.

Denne formel bruger to indlejrede IF-funktioner, pakket ind i MAX for at returnere den maksimale pris med to kriterier. Startende med en logisk test af den første IF-sætning, farve = G6, kontrolleres værdierne i det navngivne område 'farve' (B6: B14) mod værdien i celle G6, 'rød'. Resultatet er en matrix som denne:



 
{TRUETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSE}

I den logiske test for den anden IF-sætning, element = H6, kontrolleres værdierne i det navngivne intervalelement (C6: C14) mod værdien i celle H6, 'hat'. Resultatet er en matrix som denne:

 
{TRUETRUETRUETRUETRUETRUEFALSEFALSEFALSE}

'Værdien hvis sand' for 2. IF-sætning er det navngivne interval 'priser' (E6: E14), som er en matrix som denne:

 
{118912910987}

En pris returneres for hver vare i dette interval kun når resultatet af de første to arrays ovenfor er SAND for varer i tilsvarende positioner. I det viste eksempel ser den endelige matrix inde i MAX sådan ud:



 
{1189FALSEFALSEFALSEFALSEFALSEFALSE}

Bemærk de eneste priser, der 'overlever', er de i en position, hvor farven er 'rød', og varen er 'hat'.

MAX-funktionen returnerer derefter den højeste pris og ignorerer automatisk FALSE-værdier.

Alternativ syntaks ved hjælp af boolsk logik

Du kan også bruge følgende matrixformel, som kun bruger en IF-funktion sammen med boolsk logik :

hvilket af følgende bruges til at vælge tilstødende ark?
 
{= MAX ( IF ((color=G6)*(item=H6),price))}

Fordelen ved denne syntaks er, at det er lettere at tilføje yderligere kriterier uden at tilføje yderligere indlejrede IF-funktioner. Hvis du har brug for ELLER logik, skal du bruge tilføjelse i stedet for multiplikation mellem betingelser.

Med MAXIFS

Det MAXIFS-funktion , introduceret i Excel 2016, er designet til at beregne maksimumsniveauer baseret på et eller flere kriterier uden behov for en matrixformel. Med MAXIFS er formlen i I6:

 
= MAXIFS (price,color,G6,item,H6)

Bemærk: MAXIFS vil automatisk ignorere tomme celler, der opfylder kriterierne. Med andre ord behandler MAXIFS ikke tomme celler, der opfylder kriterierne, som nul. På den anden side, MAXIFS vilje returner nul (0), hvis ingen celler matcher kriterierne.

Forfatter Dave Bruns


^