Download eksemplet på projektmappe
Denne vejledning vil demonstrere, hvordan man beregner "stor hvis" eller "lille hvis", og henter den nth største (eller mindste) værdi baseret på kriterier.
STORE & SMÅ funktioner
LARGE -funktionen bruges til at beregne den nth største værdi (k) i et array, mens SMALL -funktionen returnerer den mindste nth -værdi.
For at oprette et "Large If" bruger vi LARGE -funktionen sammen med IF -funktionen i en matrixformel.
STORT HVIS
Ved at kombinere LARGE (eller SMALL) og IF i en matrixformel kan vi i det væsentlige oprette en "LARGE IF" -funktion, der fungerer på samme måde som den indbyggede SUMIF-formel fungerer. Lad os gå gennem et eksempel.
Vi har en liste over karakterer opnået af elever i to forskellige fag:
Antages, at vi bliver bedt om at finde de tre bedste karakterer opnået for hvert fag sådan:
For at opnå dette kan vi indlejre en IF -funktion med emne som vores kriterier inde i LARGE fungerer sådan:
= STOR (HVIS (=,),)
= STOR (HVIS ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Når du bruger Excel 2022 og tidligere, skal du indtaste formlen ved at trykke på CTRL + SKIFT + ENTER for at få de krøllede parenteser omkring formlen.
Hvordan fungerer formlen?
Formlen fungerer ved at evaluere hver celle i vores kriterieområde som SAND eller FALSK.
Find den bedste karakterværdi (k = 1) i matematik:
= STOR (HVIS ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= STORT (HVIS ({SAND; FALSK; FALSK; SAND; FALSK; FALSK; SAND; FALSK}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Dernæst erstatter IF -funktionen hver værdi med FALSK, hvis betingelsen ikke er opfyldt.
= STOR ({0,81; FALSK; FALSK; 0,42; FALSK; 0,63; FALSK; 0,58; FALSK}, 1)
Nu springer LARGE -funktionen de FALSKE værdier over og beregner den største (k = 1) af de resterende værdier (0,81 er de største værdier mellem 0,42 og 0,81).
LILLE HVIS
Den samme teknik kan også anvendes med SMALL -funktionen i stedet.
= LILLE (HVIS ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
STORT HVIS med flere kriterier
Hvis du vil bruge LARGE IF med flere kriterier (svarende til hvordan den indbyggede SUMIFS-formel fungerer), skal du simpelthen nest flere IF-funktioner i LARGE-funktionen sådan:
= STORT (HVIS (=, HVIS (=,)),)
= STOR (HVIS ($ D $ 2: $ D $ 18 = $ H3, IF ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
En anden måde at inkludere flere kriterier på er at gange kriterierne sammen som vist i denne artikel
Tips og tricks:
- Hvor det er muligt, skal du altid henvise til positionen (k) fra en hjælpercelle og låsehenvisning (F4), da dette vil gøre formler til automatisk udfyldning lettere.
- Hvis du bruger Excel 2022 eller nyere, kan du indtaste formlen uden Ctrl + Shift + Enter.
- For at hente navne på elever, der opnåede topkarakterer, kombineres med dette med INDEX MATCH
