Ladda ner exempelarbetsboken
Denna handledning visar hur du hittar det största cellvärdet som uppfyller specifika villkor i Excel och Google Sheets.
Max If Array -funktion
MAX -funktionen identifierar det största värdet i en rad nummer.
Vi kan använda MAX -funktionen i kombination med en IF -funktion för att identifiera det största värdet som uppfyller ett specifikt villkor.
Användare av Google Kalkylark och Excel 2022 eller senare rekommenderas att använda den enklare MAXIFS -funktionen. Detta förklaras i ett senare avsnitt.
I detta exempel används MAX- och IF -funktionerna i en matrisformel för att identifiera de största Beställningsstorlek för varje Affärsnamn
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
I Office 365 och versioner av Excel efter 2022 kan du helt enkelt ange ovanstående formel som du normalt skulle göra (genom att trycka på ENTER).
För Excel 2022 och tidigare måste du dock ange formeln genom att trycka på CTRL + SKIFT + ENTER. Efter att du gjort det kommer du att märka att lockiga matrisparenteser visas runt formeln.
För att visa hur denna formel fungerar, låt oss dela upp den i steg.
Detta är vår slutliga formel (visas utan de automatiskt tillagda gruppformelparenteserna):
| 1 | = MAX (OM (B3: B8 = "A", D3: D8)) |
Först läggs cellintervallets värden till formeln som matriser:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Nästa Affärsnamn = "A" -tillståndet ger en uppsättning SANT/FALSKA värden:
| 1 | = MAX (OM ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Då ändrar IF -funktionen alla SAN -värden till det relevanta Beställningsstorlek:
| 1 | = MAX ({500; FALSKT; 300; FALSKT; 600; FALSKT}) |
MAX -funktionen identifierar det största antalet i matrisen, samtidigt som alla FALSE -värden ignoreras för att visa det största Beställningsstorlek för Affärsnamn = "A":
| 1 | =600 |
Max If - Flera kriterier
Vi kan också identifiera det största värdet baserat på flera kriterier med hjälp av boolsk logik.
Detta exempel visar det största Beställningsstorlek för varje Affärsnamn, men för Beställ datum före 30.4.2021 med MAX-, IF- och DATE -funktionerna:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Lägg märke till att vi multiplicerar två uppsättningar av SANT/FALSK kriterier tillsammans:
| 1 | (B3: B8 = "A") * (C3: C8 |
Om båda kriterierna är SANT beräknas det totala villkoret som SANT, men om ett (eller flera) kriterier är FALSKT beräknas det som FALSKT.
Med denna metod är det möjligt att lägga till många olika kriterier till denna formel.
Max If - Flera kriterier med cellreferenser
Vanligtvis är det inte bra att koda värden i formler. Istället är det mer flexibelt att använda separata celler för att definiera kriterierna.
För att matcha Affärsnamn till värdet som visas i kolumn F, kan vi uppdatera formeln till att vara:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Låsning av cellreferenser
För att göra våra formler lättare att läsa har vi visat formlerna utan låsta cellreferenser:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Men dessa formler fungerar inte korrekt när de kopieras och klistras in någon annanstans i din fil. Istället bör du använda låsta cellreferenser så här:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8 |
Läs vår artikel om låsa cellreferenser för att lära dig mer.
MAXIFS -funktion
Användare av Google Kalkylark och Excel 2022 eller senare kan använda den enda MAXIFS -funktionen för att replikera beteendet för MAX- och IF -funktionerna som visas i de tidigare exemplen.
I nästa exempel används MAXIFS- och DATE -funktionerna för att visa de största Beställningsstorlek för varje Affärsnamn för Beställ datum före 30.4.2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
MAXIFS -funktionen kräver inte att användaren trycker på CTRL + SKIFT + ENTER när du anger formeln.
Max If (Max Value with Condition) i Google Kalkylark
Exemplen som visas ovan fungerar exakt likadant i Google Kalkylark som i Excel, men eftersom MAXIFS -funktionen är tillgänglig rekommenderas att du använder denna enda funktion istället för att kombinera MAX- och IF -funktionerna.
Om exemplen med MAX- och IF -funktioner måste användas, kräver Google Sheets att du anger dessa som matrisformler. Istället för att visa formeln med Excel -lockiga arrayparenteser {}, lägger du till CTRL + SKIFT + ENTER automatiskt lägger till ARRAYFORMULA -funktionen runt formeln:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A")*(C3: C8 |
