Generisk formel
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Sammanfattning
För att få det maximala värdet i en datamängd baserat på mer än ett kriterium kan du använda en matrisformel baserad på MAX- och IF-funktionerna. I exemplet som visas är formeln i I6:
(=MAX(IF(color=G6,IF(item=H6,price))))
Med en färg på "röd" och "hatt" blir resultatet $ 11,00
Obs: Detta är en matrisformel och måste anges med Ctrl + Skift + in
Förklaring
I det här exemplet används följande namnområden: "färg" = B6: B14, "artikel" = C6: C14 och "pris" = E6: E14. Målet är att hitta det högsta priset för en viss färg och artikel.
Denna formel använder två kapslade IF-funktioner, insvept inuti MAX för att returnera maxpriset med två kriterier. Från och med ett logiskt test av det första IF-uttalandet, färg = G6, kontrolleras värdena i det angivna området "färg" (B6: B14) mot värdet i cell G6, "rött". Resultatet är en array som denna:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
I det logiska testet för det andra IF-uttalandet, objekt = H6, kontrolleras värdena i det angivna intervallobjektet (C6: C14) mot värdet i cell H6, "hat". Resultatet är en array som denna:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
"Värdet om det är sant" för det andra IF-uttalandet är det namngivna intervallet "priser" (E6: E14), vilket är en array som denna:
(11;8;9;12;9;10;9;8;7)
Ett pris returneras för varje artikel i detta intervall endast när resultatet av de två första grupperna ovan är SANT för artiklar i motsvarande positioner. I det visade exemplet ser den slutliga matrisen inuti MAX så här:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Observera att de enda priserna som "överlever" är de i en position där färgen är "röd" och artikeln är "hatt".
MAX-funktionen returnerar sedan det högsta priset och ignorerar automatiskt FALSE-värden.
Alternativ syntax med boolesk logik
Du kan också använda följande matrisformel, som bara använder en IF-funktion tillsammans med boolesk logik:
(=MAX(IF((color=G6)*(item=H6),price)))
Fördelen med denna syntax är att det är lättare att lägga till ytterligare kriterier utan att lägga till ytterligare kapslade IF-funktioner. Om du behöver ELLER logik, använd addition istället för multiplikation mellan villkoren.
Med MAXIFS
MAXIFS-funktionen, som introducerades i Excel 2016, är utformad för att beräkna maximum baserat på ett eller flera kriterier utan att det behövs en matrisformel. Med MAXIFS är formeln i I6:
=MAXIFS(price,color,G6,item,H6)
Obs! MAXIFS ignorerar automatiskt tomma celler som uppfyller kriterierna. Med andra ord kommer MAXIFS inte att behandla tomma celler som uppfyller kriterierna som noll. Å andra sidan kommer MAXIFS att returnera noll (0) om inga celler matchar kriterierna.
