Generieke formule
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Samenvatting
Om de maximale waarde in een gegevensset op basis van meer dan één criterium te krijgen, kunt u een matrixformule gebruiken op basis van de MAX- en IF-functies. In het getoonde voorbeeld is de formule in I6:
(=MAX(IF(color=G6,IF(item=H6,price))))
Met een kleur van "rood" en item van "hoed" is het resultaat $ 11,00
Opmerking: dit is een matrixformule en moet worden ingevoerd met Ctrl + Shift + ingevoerd
Uitleg
In dit voorbeeld worden de volgende benoemde bereiken gebruikt: "kleur" = B6: B14, "item" = C6: C14 en "prijs" = E6: E14. Het doel is om de maximale prijs voor een bepaalde kleur en artikel te vinden.
Deze formule gebruikt twee geneste IF-functies, verpakt in MAX om de maximale prijs met twee criteria te retourneren. Beginnend met een logische test van de eerste IF-instructie, color = G6, worden de waarden in het genoemde bereik "kleur" (B6: B14) vergeleken met de waarde in cel G6, "rood". Het resultaat is een array als deze:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
In de logische test voor de tweede IF-instructie, item = H6, worden de waarden in het benoemde bereikitem (C6: C14) vergeleken met de waarde in cel H6, "hat". Het resultaat is een array als deze:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
De "waarde indien waar" voor de 2e IF-instructie is het benoemde bereik "prijzen" (E6: E14), wat een array is zoals deze:
(11;8;9;12;9;10;9;8;7)
Er wordt alleen een prijs geretourneerd voor elk item in dit bereik als het resultaat van de eerste twee arrays hierboven WAAR is voor items op overeenkomstige posities. In het getoonde voorbeeld ziet de laatste array binnen MAX er als volgt uit:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Merk op dat de enige prijzen die "overleven" die zijn in een positie waar de kleur "rood" is en het item "hoed" is.
De MAX-functie retourneert vervolgens de hoogste prijs en negeert automatisch FALSE-waarden.
Alternatieve syntaxis met behulp van booleaanse logica
U kunt ook de volgende matrixformule gebruiken, die slechts één ALS-functie gebruikt in combinatie met booleaanse logica:
(=MAX(IF((color=G6)*(item=H6),price)))
Het voordeel van deze syntaxis is dat het gemakkelijker is om extra criteria toe te voegen zonder extra geneste IF-functies toe te voegen. Als je OR-logica nodig hebt, gebruik dan optellen in plaats van vermenigvuldigen tussen voorwaarden.
Met MAXIFS
De MAXIFS-functie, geïntroduceerd in Excel 2016, is ontworpen om maxima te berekenen op basis van een of meer criteria zonder dat een matrixformule nodig is. Met MAXIFS is de formule in I6:
=MAXIFS(price,color,G6,item,H6)
Opmerking: MAXIFS negeert automatisch lege cellen die voldoen aan de criteria. Met andere woorden, MAXIFS behandelt lege cellen die aan de criteria voldoen niet als nul. Aan de andere kant retourneert MAXIFS nul (0) als er geen cellen overeenkomen met de criteria.
