Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du finner den største celleverdien som oppfyller spesifikke betingelser i Excel og Google Sheets.
Max If Array -funksjon
MAX -funksjonen identifiserer den største verdien i en tallrekke.
Vi kan bruke MAX -funksjonen kombinert med en IF -funksjon for å identifisere den største verdien som oppfyller en spesifisert betingelse.
Brukere av Google Sheets og Excel 2022 eller nyere anbefales å bruke den enklere MAXIFS -funksjonen. Dette er forklart i et senere avsnitt.
Dette eksemplet vil bruke MAX- og IF -funksjonene i en matriseformel for å identifisere de største Bestillingsstørrelse for hver Butikknavn
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
I Office 365 og versjoner av Excel etter 2022 kan du ganske enkelt skrive inn formelen ovenfor som du vanligvis ville gjort (ved å trykke ENTER).
For Excel 2022 og tidligere må du imidlertid angi formelen ved å trykke CTRL + SKIFT + ENTER. Etter at du har gjort det, vil du legge merke til krøllete array -parenteser rundt formelen.
For å vise hvordan denne formelen fungerer, la oss dele den ned i trinn.
Dette er vår endelige formel (vist uten de automatisk lagt matriseformelbrakettene):
| 1 | = MAKS (HVIS (B3: B8 = "A", D3: D8)) |
Først legges celleområdets verdier til formelen som matriser:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Neste Butikknavn = "A" -tilstanden produserer en rekke SANN/FALSKE verdier:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Deretter endrer IF -funksjonen alle SANNE verdier til det relevante Bestillingsstørrelse:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
MAX -funksjonen identifiserer det største tallet i matrisen, mens du ignorerer FALSE -verdier for å vise det største Bestillingsstørrelse for Butikknavn = "A":
| 1 | =600 |
Maks hvis - Flere kriterier
Vi kan også identifisere den største verdien basert på flere kriterier ved å bruke boolsk logikk.
Dette eksemplet viser det største Bestillingsstørrelse for hver Butikknavn, men for Bestill datoer før 30.04.2021 ved å bruke funksjonene MAX, IF og DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Legg merke til at vi multipliserer to sett med TRUE/FALSE kriterier sammen:
| 1 | (B3: B8 = "A") * (C3: C8 |
Hvis begge kriteriene er SANN, vil den totale tilstanden beregnes som SANN, men hvis ett (eller flere) kriterier er FALSK, vil det beregnes som FALSKT.
Ved å bruke denne metodikken er det mulig å legge mange forskjellige kriterier til denne formelen.
Max If - Flere kriterier med cellereferanser
Vanligvis er det ikke god praksis å kode verdier inn i formler. I stedet er det mer fleksibelt å bruke separate celler for å definere kriteriene.
For å matche Butikknavn til verdien vist i kolonne F, kan vi oppdatere formelen til å være:
| 1 | {= MAKS (HVIS ((B3: B8 = F3)*(C3: C8 |
Låsing av cellereferanser
For å gjøre formlene våre lettere å lese, har vi vist formlene uten låste cellereferanser:
| 1 | {= MAKS (HVIS ((B3: B8 = F3)*(C3: C8 |
Men disse formlene fungerer ikke som de skal når de kopieres og limes inn andre steder i filen. I stedet bør du bruke låste cellereferanser slik:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8 |
Les artikkelen vår om låsing av cellereferanser for å lære mer.
MAXIFS -funksjon
Brukere av Google Sheets og Excel 2022 eller nyere kan bruke enkelt MAXIFS -funksjonen til å gjenskape oppførselen til MAX- og IF -funksjonene vist i de tidligere eksemplene.
Dette neste eksemplet bruker MAXIFS og DATE -funksjonene for å vise de største Bestillingsstørrelse for hver Butikknavn til Bestill datoer før 30.04.2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
MAXIFS -funksjonen krever ikke at brukeren trykker CTRL + SKIFT + ENTER når du angir formelen.
Maks hvis (maks verdi med tilstand) i Google Regneark
Eksemplene vist ovenfor fungerer nøyaktig det samme i Google Regneark som i Excel, men ettersom MAXIFS -funksjonen er tilgjengelig, anbefales det å bruke denne enkeltfunksjonen i stedet for å kombinere MAX- og IF -funksjonene.
Hvis eksemplene som bruker MAX og IF Functions må brukes, krever Google Sheets at du angir disse som matriseformler. I stedet for å vise formelen med Excel -krøllete parenteser {}, legger du til CTRL + SKIFT + ENTER automatisk med ARRAYFORMULA -funksjonen rundt formelen:
| 1 | = ARRAYFORMULA (MAKS (HVIS ((B3: B8 = "A")*(C3: C8 |
