Generisk formel
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Sammendrag
For å få maksimal verdi i et datasett basert på mer enn ett kriterium, kan du bruke en matriseformel basert på MAX- og IF-funksjonene. I eksemplet vist er formelen i I6:
(=MAX(IF(color=G6,IF(item=H6,price))))
Med fargen "rød" og "hatt" er resultatet $ 11,00
Merk: Dette er en matriseformel og må angis ved hjelp av Ctrl + Skift + angitt
Forklaring
Dette eksemplet bruker følgende navngitte områder: "farge" = B6: B14, "element" = C6: C14 og "pris" = E6: E14. Målet er å finne maksimumsprisen for en gitt farge og vare.
Denne formelen bruker to nestede IF-funksjoner, pakket inn i MAX for å returnere maksimumsprisen med to kriterier. Fra og med en logisk test av den første IF-setningen, farge = G6, blir verdiene i det nevnte området "farge" (B6: B14) sjekket mot verdien i celle G6, "rød". Resultatet er en matrise som dette:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
I den logiske testen for den andre IF-setningen, element = H6, blir verdiene i det nevnte områdeposten (C6: C14) sjekket mot verdien i celle H6, "hat". Resultatet er en matrise som dette:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
"Verdien hvis sant" for den andre IF-setningen er det navngitte området "priser" (E6: E14), som er en matrise som dette:
(11;8;9;12;9;10;9;8;7)
En pris returneres kun for hver vare i dette området når resultatet av de to første gruppene ovenfor er SANT for varer i tilsvarende posisjoner. I det viste eksemplet ser den endelige matrisen inne i MAX slik ut:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Merk at de eneste prisene som "overlever" er de som er i en posisjon der fargen er "rød" og varen er "hatt".
MAX-funksjonen returnerer deretter den høyeste prisen, og ignorerer automatisk FALSE-verdier.
Alternativ syntaks ved bruk av boolsk logikk
Du kan også bruke følgende matriseformel, som bare bruker én IF-funksjon sammen med boolsk logikk:
(=MAX(IF((color=G6)*(item=H6),price)))
Fordelen med denne syntaksen er at det er lettere å legge til flere kriterier uten å legge til flere nestede IF-funksjoner. Hvis du trenger ELLER logikk, bruk tillegg i stedet for multiplikasjon mellom forholdene.
Med MAXIFS
MAXIFS-funksjonen, introdusert i Excel 2016, er designet for å beregne maksimum basert på ett eller flere kriterier uten behov for en matriseformel. Med MAXIFS er formelen i I6:
=MAXIFS(price,color,G6,item,H6)
Merk: MAXIFS vil automatisk ignorere tomme celler som oppfyller kriteriene. Med andre ord, vil MAXIFS ikke behandle tomme celler som oppfyller kriteriene som null. På den annen side vil MAXIFS returnere null (0) hvis ingen celler samsvarer med kriteriene.
