Download eksemplet på projektmappe
Denne vejledning viser, hvordan du finder den største celleværdi, der opfylder specifikke betingelser i Excel og Google Sheets.
Max If Array -funktion
MAX -funktionen identificerer den største værdi i en række tal.
Vi kan bruge MAX -funktionen kombineret med en IF -funktion til at identificere den største værdi, der opfylder en bestemt betingelse.
Brugere af Google Sheets og Excel 2022 eller nyere anbefales at bruge den enklere MAXIFS -funktion. Dette forklares i et senere afsnit.
Dette eksempel vil bruge MAX- og IF -funktionerne i en matrixformel til at identificere de største Ordrestørrelse for hver Butiksnavn
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
I Office 365 og versioner af Excel efter 2022 kan du blot indtaste ovenstående formel som du normalt ville (ved at trykke på ENTER).
For Excel 2022 og tidligere skal du dog indtaste formlen ved at trykke på CTRL + SKIFT + ENTER. Når du har gjort det, vil du bemærke, at krøllede array -parenteser vises omkring formlen.
For at vise, hvordan denne formel fungerer, lad os opdele den i trin.
Dette er vores endelige formel (vist uden de automatisk tilføjede arrayformelparenteser):
| 1 | = MAX (HVIS (B3: B8 = "A", D3: D8)) |
For det første tilføjes celleintervalværdierne til formlen som arrays:
| 1 | = MAX (HVIS ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Næste Butiksnavn = "A" -tilstanden producerer en række SANDE/FALSKE værdier:
| 1 | = MAKS (HVIS ({SAND; FALSK; SAND; FALSK; SAND; FALSK}, {500; 400; 300; 700; 600; 200})) |
Derefter ændrer IF -funktionen alle SANDE værdier til det relevante Ordrestørrelse:
| 1 | = MAKS ({500; FALSK; 300; FALSK; 600; FALSK}) |
MAX -funktionen identificerer det største tal i arrayet, mens der ignoreres eventuelle FALSE -værdier for at vise det største Ordrestørrelse for Butiksnavn = "A":
| 1 | =600 |
Max If - Flere kriterier
Vi kan også identificere den største værdi baseret på flere kriterier ved hjælp af boolsk logik.
Dette eksempel viser det største Ordrestørrelse for hver Butiksnavn, men for Bestillingsdatoer før 30-04-2021 ved hjælp af funktionerne MAX, IF og DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Bemærk her, at vi multiplicerer to sæt SAND/FALSK kriterier sammen:
| 1 | (B3: B8 = "A") * (C3: C8 |
Hvis begge kriterier er SAND, beregnes den samlede betingelse som SAND, men hvis et (eller flere) kriterier er FALSKT, vil det blive beregnet som FALSKT.
Ved hjælp af denne metode er det muligt at tilføje mange forskellige kriterier til denne formel.
Max If - Flere kriterier med cellereferencer
Normalt er det ikke god praksis at hard-kode værdier i formler. I stedet er det mere fleksibelt at bruge separate celler til at definere kriterierne.
At matche Butiksnavn til værdien vist i kolonne F, kan vi opdatere formlen til at være:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Låsning af cellereferencer
For at gøre vores formler lettere at læse har vi vist formlerne uden låste cellereferencer:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Men disse formler fungerer ikke korrekt, når de kopieres og indsættes andre steder i din fil. I stedet skal du bruge låste cellereferencer som denne:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8 |
Læs vores artikel om låsning af cellereferencer for at lære mere.
MAXIFS -funktion
Brugere af Google Sheets og Excel 2022 eller nyere kan bruge den enkelte MAXIFS -funktion til at replikere adfærden for MAX- og IF -funktionerne vist i de tidligere eksempler.
Dette næste eksempel bruger MAXIFS og DATE -funktionerne til at vise de største Ordrestørrelse for hver Butiksnavn til Bestillingsdatoer inden 30.04.2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
MAXIFS -funktionen kræver ikke, at brugeren trykker på CTRL + SKIFT + ENTER, når formlen indtastes.
Max If (Max Value with Condition) i Google Sheets
Eksemplerne ovenfor fungerer nøjagtigt det samme i Google Sheets som i Excel, men da MAXIFS -funktionen er tilgængelig, anbefales det at bruge denne enkeltfunktion i stedet for at kombinere MAX- og IF -funktionerne.
Hvis eksemplerne ved hjælp af MAX- og IF -funktioner skal bruges, kræver Google Sheets, at du indtaster disse som matrixformler. I stedet for at vise formlen med Excel -krøllede array -parenteser {}, tilføjer ARRAYFORMULA -funktionen automatisk omkring formlen ved at trykke på CTRL + SHIFT + ENTER:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A")*(C3: C8 |
