Forestil dig, at du har flere identiske ark i en projektmappe, der indeholder identiske tabeller (som tilstedeværelsesregistre for hver måned i et separat ark). Nu vil du oprette et dashboard, der viser dig det samlede fremmøde for måneden. At have hver måneds data på instrumentbrættet på samme tid er ikke en god mulighed. Vi ønsker en drop -down for at vælge måneden. Vi har brug for en VLOOKUP -formel at se på fra den valgte måneds ark.
I enkle ord har vi brug for en opslagsformel for at slå op fra variable ark.
Som ovenstående gif viser, bruger vi VLOOKUP og INDIRECT -funktion sammen til at slå op fra flere ark baseret på deres navne.
Generisk formel til at slå flere ark op
| =VLOOKUP(opslagsværdi,INDIREKT(""&sheet_name_reference&"! opslagstabel "), col_index, 0) |
Opslag_værdi: Dette er den værdi, du leder efter i opslaget bord.
Sheet_name_reference: Dette er referencen til cellen, der indeholder arkets navn.
Opslagstabel: Dette er den tabelreference, som du vil søge efter opslagsværdi. Det kan være et navngivet område, en tabel eller en absolut reference. Det skal være det samme i alle arkene.
Col_Index: Dette er kolonnenummeret i tabellen, hvorfra du vil hente værdi. Hvis du kender VLOOKUP -funktionen, ved du, hvad det er.
Så lad os hoppe på et eksempel.
Eksempel: Hent fremmøde i den valgte måned
Så vi har en projektmappe, der fastholder mine Excel -studerendes fremmøde. Hver måneds data gemmes separat i forskellige ark. Navnet på arket er indstillet til navnet på måneder. Foreløbig har jeg tre måneders data, men der kommer selvfølgelig flere.
Jeg vil oprette en rapport, der viser deltagelsen i den valgte måned. Måneden kan vælges fra en rulleliste. Formlen skal være i stand til automatisk at slå op fra det ark, selvom der tilføjes et nyt ark til det.
Så vi forbereder ovenstående tabel. Cell G3 oprettede vi en rulleliste ved hjælp af en rulleliste.
Opslagsværdien er i B4. Sheet_name_reference er i G3. Opslagstabellen i alle arkene er B3: AZ100. Vi vil gerne hente værdi fra 2 kolonner. Så vi skriver denne formel i C4 og trækker den ned. Tilsvarende ændrer vi kolonneindekset for fraværende værdier.
| =VLOOKUP(B4,INDIREKT("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Hvordan virker det?
Formlen løses indefra og ud. Lad os først se, hvordan det løses trin for trin.
Forudsat at G3 indeholder Jan.
| =VLOOKUP(B4,INDIREKT("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = OPLYSNING (B4, INDIREKTE ("Jan! $ B $ 3: $ Az $ 100"),2,0) |
| = OPLYSNING (B4,Jan! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Så først løses sætningen "" & $ G $ 3 & "! $ B $ 3: $ Az $ 100" til en streng "Jan! $ B $ 3: $ Az $ 100". Dernæst konverterer INDIRECT -funktionen denne streng til faktisk reference. Og endelig fik vi formlen VLOOKUP (B4, Jan! $ B $ 3: $ Az $ 100,2,0). Og dette løser sig endelig til 7. Hvis du nu ændrer arknavnet i G3, ændres værdireference teksten. Og sådan ser du op fra variable ark i Excel.
Jeg håber, at dette var nyttigt for dig. Hvis du har spørgsmål eller har et andet opslag at udføre, så lad mig vide det i kommentarfeltet herunder. Jeg hjælper dig gerne. Indtil da fortsæt med at udmærke sig.
Brug VLOOKUP fra to eller flere opslagstabeller | For at slå op fra flere tabeller kan vi tage en IFERROR -tilgang. At kigge op fra flere tabeller tager fejlen som en switch til den næste tabel. En anden metode kan være en If -tilgang.
Sådan laver du store og små bogstaver i Excel | excels VLOOKUP -funktion er ikke store og små bogstaver, og den returnerer den første matchede værdi fra listen. INDEX-MATCH er ingen undtagelse, men det kan ændres for at gøre det store og små bogstaver. Lad os se, hvordan…
Opslag, der ofte vises tekst med kriterier i Excel | Opslaget vises oftest i tekst i et område, vi bruger INDEX-MATCH med MODE-funktionen. Her er metoden.
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges Excel VLOOKUP -funktion| Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige intervaller og ark.
Sådan bruges Excel COUNTIF -funktion| Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.