Ladda ner exempelarbetsboken
Denna handledning visar hur man beräknar "stort om" eller "litet om", och hämtar det n: e största (eller minsta) värdet baserat på kriterier.
STORA & SMÅ funktioner
LARGE -funktionen används för att beräkna det n: e största värdet (k) i en array, medan SMALL -funktionen returnerar det minsta n -värdet.
För att skapa ett "Large If" använder vi LARGE -funktionen tillsammans med IF -funktionen i en matrisformel.
STORT OM
Genom att kombinera LARGE (eller SMALL) och IF i en matrisformel kan vi i huvudsak skapa en "LARGE IF" -funktion som fungerar ungefär som hur den inbyggda SUMIF-formeln fungerar. Låt oss gå igenom ett exempel.
Vi har en lista över betyg som elever uppnått i två olika ämnen:
Antag att vi blir ombedda att hitta de tre bästa betygen som uppnåtts för varje ämne så här:
För att åstadkomma detta kan vi häcka en IF -funktion med ämne som våra kriterier inuti LARGE -funktionen så här:
= STOR (OM (=,),)
= STOR (OM ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
När du använder Excel 2022 och tidigare måste du ange formeln genom att trycka på CTRL + SKIFT + ENTER för att få de lockiga parenteserna runt formeln.
Hur fungerar formeln?
Formeln fungerar genom att utvärdera varje cell i vårt kriterieintervall som SANT eller FALSKT.
Hitta det högsta betygsvärdet (k = 1) i matematik:
= STOR (OM ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= STOR (OM ({SANT; FALSKT; FALSKT; SANT; FALSKT; FALSKT; SANT; FALSKT}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Därefter ersätter IF -funktionen varje värde med FALSE om dess villkor inte uppfylls.
= STORT ({0,81; FALSKT; FALSKT; 0,42; FALSKT; 0,63; FALSKT; 0,58; FALSKT}, 1)
Nu hoppar LARGE -funktionen över FALSE -värdena och beräknar det största (k = 1) av de återstående värdena (0,81 är de största värdena mellan 0,42 och 0,81).
LITT OM
Samma teknik kan också tillämpas med SMALL -funktionen istället.
= LITEN (OM ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
STORT OM med flera kriterier
För att använda LARGE IF med flera kriterier (liknande hur den inbyggda SUMIFS-formeln fungerar), helt enkelt häckar fler IF-funktioner i LARGE-funktionen så här:
= STOR (OM (=, OM (=,)),)
= STOR (OM ($ D $ 2: $ D $ 18 = $ H3, IF ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Ett annat sätt att inkludera flera kriterier är att multiplicera kriterierna tillsammans som visas i den här artikeln
Tips och tricks:
- Om möjligt, hänvisa alltid till positionen (k) från en hjälparcell och låsreferens (F4) eftersom detta kommer att göra formler för automatisk fyllning enklare.
- Om du använder Excel 2022 eller senare kan du ange formeln utan Ctrl + Skift + Retur.
- För att hämta namnen på elever som uppnådde toppbetyg, kombinera med detta med INDEX MATCH
