Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du beregner "stor hvis" eller "liten hvis", og henter den nth største (eller minste) verdien basert på kriterier.
STORE & SMÅ funksjoner
LARGE -funksjonen brukes til å beregne den niende største verdien (k) i en matrise, mens SMALL -funksjonen returnerer den minste nth -verdien.
For å lage et "Large If", bruker vi LARGE -funksjonen sammen med IF -funksjonen i en matriseformel.
STORT HVIS
Ved å kombinere LARGE (eller SMALL) og IF i en matriseformel, kan vi i hovedsak lage en “LARGE IF” -funksjon som fungerer omtrent som den innebygde SUMIF-formelen. La oss gå gjennom et eksempel.
Vi har en liste over karakterer oppnådd av studenter i to forskjellige fag:
Anta at vi blir bedt om å finne de tre beste karakterene oppnådd for hvert emne slik:
For å oppnå dette kan vi hekke en IF -funksjon med Emne som våre kriterier inne i LARGE -funksjonen slik:
= STOR (HVIS (=,),)
= STOR (HVIS ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Når du bruker Excel 2022 og tidligere, må du angi formelen ved å trykke CTRL + SKIFT + ENTER for å få de krøllete parentesene rundt formelen.
Hvordan fungerer formelen?
Formelen fungerer ved å evaluere hver celle i våre kriterier som SANN eller FALSK.
Finne toppkarakterverdien (k = 1) i matematikk:
= STOR (HVIS ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= STOR (HVIS ({SANN; FALSK; FALSK; SANN; FALSK; FALSK; SANN; FALSK}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Deretter erstatter IF -funksjonen hver verdi med FALSE hvis betingelsen ikke er oppfylt.
= STOR ({0,81; FALSK; FALSK; 0,42; FALSK; 0,63; FALSK; 0,58; FALSK}, 1)
Nå hopper LARGE -funksjonen over FALSE -verdiene og beregner den største (k = 1) av de gjenværende verdiene (0,81 er de største verdiene mellom 0,42 og 0,81).
LITT HVIS
Den samme teknikken kan også brukes med SMALL -funksjonen i stedet.
= LITEN (HVIS ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
STORT HVIS med flere kriterier
Hvis du vil bruke LARGE IF med flere kriterier (ligner på hvordan den innebygde SUMIFS-formelen fungerer), legger du bare inn flere IF-funksjoner i LARGE-funksjonen slik:
= STOR (HVIS (=, HVIS (=,)),)
= STOR (HVIS ($ D $ 2: $ D $ 18 = $ H3, IF ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
En annen måte å inkludere flere kriterier på er å multiplisere kriteriene sammen som vist i denne artikkelen
Tips og triks:
- Der det er mulig, må du alltid referere til posisjonen (k) fra en hjelpecelle og låsehenvisning (F4), da dette vil gjøre formler for automatisk fylling enklere.
- Hvis du bruker Excel 2022 eller nyere, kan du skrive inn formelen uten Ctrl + Shift + Enter.
- For å hente navnene på elevene som oppnådde toppkarakterene, kombiner med dette med INDEX MATCH
