Tenk deg at du har flere identiske ark i en arbeidsbok som inneholder identiske tabeller (som oppmøteoppføringer for hver måned i et eget ark). Nå vil du lage et dashbord som viser det totale oppmøtet for måneden. Å ha hver måneds data på dashbordet samtidig er ikke et godt alternativ. Vi ønsker en nedtrekksmeny for å velge måned. Vi trenger en VLOOKUP -formel å se på fra den valgte månedens ark.
I enkle ord trenger vi en oppslagsformel for å slå opp fra variable ark.
Som gifen ovenfor viser, bruker vi VLOOKUP og INDIRECT -funksjonen sammen for å slå opp fra flere ark, basert på navnene deres.
Generisk formel for å slå opp flere ark
| =VLOOKUP(oppslagsverdi,INDIREKT(""&sheet_name_reference&"! lookup_table "), col_index, 0) |
Oppslagsverdi: Dette er verdien du leter etter i oppslaget bord.
Sheet_name_reference: Dette er referansen til cellen som inneholder navnet på arket.
Oppslagstabell: Dette er tabellreferansen du vil se etter oppslag_verdi. Det kan være et navngitt område, tabell eller absolutt referanse. Det skal være det samme i alle arkene.
Col_Index: Dette er kolonnenummeret i tabellen du vil hente verdien fra. Hvis du er kjent med VLOOKUP -funksjon, vet du hva det er.
Så la oss hoppe på et eksempel.
Eksempel: Hent oppmøte for valgt måned
Så vi har en arbeidsbok som opprettholder oppmøtet til mine Excel -studenter. Hver måneds data lagres separat i forskjellige ark. Navnet på arket er satt til navnet på måneder. Foreløpig har jeg tre måneders data, men det kommer selvfølgelig flere.
Jeg vil lage en rapport som viser deltakelsen i den valgte måneden. Måneden kan velges fra en rullegardinliste. Formelen skal kunne slå opp fra det arket automatisk, selv om et nytt ark legges til.
Så vi forbereder tabellen ovenfor. Cell G3 vi opprettet en rullegardinliste ved hjelp av en rullegardinliste.
Oppslagsverdien er i B4. Sheet_name_reference er inne G3. Oppslagstabellen i alle arkene er B3: AZ100. Vi ønsker å hente verdien fra 2 kolonner. Så vi skriver denne formelen i C4 og drar den ned. På samme måte endrer vi kolonneindeksen for fraværende verdier.
| =VLOOKUP(B4,INDIREKT("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Hvordan virker det?
Formelen er løst ut og inn. La oss først se hvordan det løses trinnvis.
Forutsatt at G3 inneholder Jan.
| =VLOOKUP(B4,INDIREKT("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = VISNING (B4, INDIREKTE ("Jan! $ B $ 3: $ Az $ 100"),2,0) |
| = VISNING (B4,Jan! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Så først blir setningen "" & $ G $ 3 & "! $ B $ 3: $ Az $ 100" løst til en streng "Jan! $ B $ 3: $ Az $ 100". Deretter konverterer INDIRECT -funksjonen denne strengen til faktisk referanse. Og til slutt fikk vi formelen VLOOKUP (B4, Jan! $ B $ 3: $ Az $ 100,2,0). Og dette løser seg til slutt til 7. Hvis du endrer arknavnet i G3, vil verdienes referansetekst bli endret. Og slik ser du opp fra variable ark i Excel.
Jeg håper dette var nyttig for deg. Hvis du har spørsmål eller har et annet oppslag å utføre, gi meg beskjed i kommentarfeltet nedenfor. Jeg hjelper deg gjerne. Frem til da fortsetter Excelling.
Bruk VLOOKUP fra to eller flere oppslagstabeller | For å slå opp fra flere tabeller kan vi ta en IFERROR -tilnærming. Å se opp fra flere tabeller tar feilen som en bryter for neste tabell. En annen metode kan være en If -tilnærming.
Hvordan gjøre saksfølsom oppslag i Excel | excels VLOOKUP -funksjon er ikke mellom store og små bokstaver, og den returnerer den første matchede verdien fra listen. INDEX-MATCH er intet unntak, men det kan endres for å gjøre det saksfølsomt. La oss se hvordan …
Oppslag som ofte vises tekst med kriterier i Excel | Søket vises oftest i tekst i et område vi bruker INDEX-MATCH with MODE-funksjonen. Her er metoden.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du Excel VLOOKUP -funksjon| Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du Excel COUNTIF -funksjon| Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.