Slå op og hent hele rækken
For at slå op og hente en hel række af data ved at matche en bestemt værdi, kan du bruge INDEX , MATCH funktioner til at oprette en matrixformel.
Slå op og hent en hel række baseret på en bestemt værdi
Sum en hel række baseret på en bestemt værdi
Yderligere analyse af en hel række baseret på en bestemt værdi
Slå op og hent en hel række baseret på en bestemt værdi
At hente liste over salg lavet af Jimmy i henhold til tabellen ovenfor, kan du først bruge MATCH-funktionen til at returnere positionen for de salg foretaget af Jimmy, og som vil blive ført til INDEX for at hente værdierne på positionen.
Generisk syntaks
=INDEX(return_range,MATCH(lookup_value,lookup_array,0),0)
√ Bemærk: Dette er en matrixformel, der kræver, at du indtaster med Ctrl + Flytte + Indtast.
- returområde: Det område, der indeholder hele den række, du vil returnere. Her refererer til salgssortimentet.
- opslagsværdi: Værdien kombinationsformlen brugte til at finde dens tilsvarende salgsoplysninger. Her refererer til den givne sælger.
- opslag_array: Området af celler, hvor der skal matches opslagsværdi. Her refererer til navneområdet.
- match_type 0: Tvinger MATCH til at finde den første værdi, der nøjagtigt er lig med opslagsværdi.
For at få listen over salg lavet af Jimmy, kopier venligst eller indtast formlen nedenfor i celle I6, tryk Ctrl + Flytte + Indtast, og dobbeltklik derefter på cellen og tryk på F9 for at få resultatet:
= INDEX (C5:F11,MATCH("Jimmy",B5: B11, 0), 0)
Eller brug en cellereference til at gøre formlen dynamisk:
= INDEX (C5:F11,MATCH(I5,B5: B11, 0), 0)
Forklaring af formlen
=INDEX(C5:F11,MATCH(I5,B5:B11,0),0)
- MATCH(I5;B5:B11,0;XNUMX): match_type 0 tvinger MATCH-funktionen til at returnere positionen af Jimmy, værdien i I5, i området B5: B11, Hvilket er 7 siden Jimmy er ved 7position.
- INDEKS(C5:F11,MATCH(I5;B5:B11,0;0),XNUMX) = INDEX (C5:F11,7, 0): INDEX-funktionen returnerer alle værdierne i 7række i rækken C5:F11 i et array som dette: 6678,3654,3278,8398 {}. Bemærk, at for at gøre arrayet synligt i Excel, skal du dobbeltklikke på cellen, hvor du indtastede formlen, og derefter trykke på F9.
Sum en hel række baseret på en bestemt værdi
Nu hvor vi har alle salgsoplysninger lavet af Jimmy, for at få årlige salgsvolumen foretaget af Jimmy, kan vi blot tilføje SUM-funktionen til formlen for at summere alle salgsværdierne fra listen.
Generisk syntaks
=SUM(INDEX(return_range,MATCH(lookup_value,lookup_array,0),0))
I dette eksempel, for at få årlige salgsvolumen foretaget af Jimmy, kopier eller indtast formlen nedenfor i cellen I7, og tryk Indtast for at få resultatet:
=SUM(INDEKS(C5:F11,MATCH(I5,B5: B11,0),0))
Forklaring af formlen
=SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))
- MATCH(I5;B5:B11,0;XNUMX): match_type 0 tvinger MATCH-funktionen til at returnere positionen af Jimmy, værdien i I5, i området B5: B11, Hvilket er 7 siden Jimmy er ved 7position.
- INDEKS(C5:F11,MATCH(I5;B5:B11,0;XNUMX), 0) = INDEKS(C5:F11,7, 0): INDEX-funktionen returnerer alle værdierne i 7række i rækken C5:F11 i et array som dette: 6678,3654,3278,8398 {}. Bemærk, at for at gøre arrayet synligt i Excel, skal du dobbeltklikke på cellen, hvor du indtastede formlen, og derefter trykke på F9.
- SUM(INDEKS(C5:F11,MATCH(I5;B5:B11,0;XNUMX)),0) = SUM(6678,3654,3278,8398 {}): SUM-funktionen summerer alle værdierne i arrayet og får derefter det årlige salgsvolumen lavet af Jimmy, $22,008.
Yderligere analyse af en hel række baseret på en bestemt værdi
For yderligere behandling af salg foretaget af Jimmy, kan du blot tilføje andre funktioner såsom SUM, AVERAGE, MAX, MIN, LARGE osv. til formlen.
For eksempel for at få gennemsnittet af Jimmys salg for hvert kvartal, kan du bruge formlen:
=MIDDEL(INDEKS(C5:F11,MATCH(I5,B5: B11,0),0))
For at finde ud af laveste salg foretaget af Jimmy, brug en af nedenstående formler:
=MIN(INDEKS(C5:F11,MATCH(I5,B5: B11,0),0))
OR
=SMALL(INDEKS(C5:F11,MATCH(I5,B5: B11,0),0),1)
Relaterede funktioner
Excel INDEX -funktionen returnerer den viste værdi baseret på en given position fra et område eller en matrix.
Funktionen Excel MATCH søger efter en bestemt værdi i et celleområde og returnerer værdiens relative position.
Relaterede formler
For at slå op og hente en hel kolonne ved at matche en bestemt værdi, vil en INDEX- og MATCH-formel gøre dig en tjeneste.
Præcis match med INDEX og MATCH
Hvis du har brug for at finde ud af de oplysninger, der er angivet i Excel om et specifikt produkt, film eller en person osv., Bør du udnytte kombinationen af INDEX- og MATCH -funktioner godt.
Omtrentlig match med INDEX og MATCH
Der er tidspunkter, hvor vi skal finde omtrentlige kampe i Excel for at evaluere medarbejdernes præstationer, bedømme elevernes score, beregne porto baseret på vægt osv. I denne vejledning vil vi tale om, hvordan man bruger INDEX- og MATCH -funktionerne til at hente resultater, vi har brug for.
Du ved måske, at du kan kombinere INDEX- og MATCH -funktionerne, eller bruge VLOOKUP -funktionen til at slå værdier op i Excel. Opslagene er dog ikke store og små bogstaver. Så, for at udføre en sag-følsom kamp, bør du drage fordel af de EXAKTE og VÆLG funktioner.
De bedste Office-produktivitetsværktøjer
Kutools til Excel - Hjælper dig med at skille dig ud fra mængden
Kutools til Excel kan prale af over 300 funktioner, At sikre, at det, du har brug for, kun er et klik væk...
Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)
- Et sekund til at skifte mellem snesevis af åbne dokumenter!
- Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
- Øger din produktivitet med 50 %, når du ser og redigerer flere dokumenter.
- Giver effektive faner til Office (inkluderer Excel), ligesom Chrome, Edge og Firefox.
