Generisk 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 + Shift + indtastet
Forklaring
Dette eksempel bruger følgende navngivne områder: "color" = B6: B14, "item" = C6: C14 og "price" = 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:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
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:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
"Værdien hvis den er sand" for 2. IF-sætning er det navngivne interval "priser" (E6: E14), som er en matrix som denne:
(11;8;9;12;9;10;9;8;7)
En pris returneres kun for hver vare i dette interval, 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:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
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:
(=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
MAXIFS-funktionen, der blev introduceret i Excel 2016, er designet til at beregne maksimumsbeløb 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 ignorerer automatisk tomme celler, der opfylder kriterierne. Med andre ord behandler MAXIFS ikke tomme celler, der opfylder kriterierne, som nul. På den anden side returnerer MAXIFS nul (0), hvis ingen celler matcher kriterierne.
