Predstavljajte si, da imate v delovnem zvezku več enakih listov, ki vsebujejo enake tabele (na primer zapise o prisotnosti za vsak mesec na ločenem listu). Zdaj želite ustvariti nadzorno ploščo, ki prikazuje skupno udeležbo v mesecu. Zdaj, če imate podatke o vsakem mesecu na nadzorni plošči hkrati, ni dobra možnost. Za izbiro meseca želimo spustni meni. Za ogled z liste izbranega meseca potrebujemo formulo VLOOKUP.
Z enostavnimi besedami potrebujemo iskalno formulo za iskanje po spremenljivih listih.
Kot prikazuje zgornji gif, bomo skupaj uporabljali funkcijo VLOOKUP in INDIRECT za iskanje iz več listov glede na njihova imena.
Splošna formula za iskanje več listov
| =VLOOKUP(lookup_value,POSREDNO(""&referenca ime_list&"! lookup_table "), col_index, 0) |
Lookup_value: To je vrednost, ki jo iščete pri iskanju miza.
Referenca imena_ lista: To je referenca celice, ki vsebuje ime lista.
Lookup_table: To je sklic tabele, v katerem želite poiskati lookup_value. Lahko je imenovani obseg, tabela ali absolutna referenca. V vseh listih mora biti enako.
Col_Index: To je številka stolpca v tabeli, iz katere želite pridobiti vrednost. Če poznate funkcijo VLOOKUP, veste, kaj je to.
Pa pojdimo na primer.
Primer: Pridobite prisotnost za izbrani mesec
Tako imamo delovni zvezek, ki ohranja obisk mojih študentov programa Excel. Podatki za vsak mesec se shranijo ločeno na različne liste. Ime lista je nastavljeno na ime mesecev. Zaenkrat imam podatke za tri mesece, bo pa seveda več.
Želim ustvariti poročilo, ki prikazuje obiskanost izbranega meseca. Mesec lahko izberete s spustnega seznama. Formula bi morala imeti možnost samodejnega iskanja s tega lista, tudi če mu dodate nov list.
Torej pripravimo zgornjo tabelo. Cell G3 smo ustvarili s spustnim seznamom.
Iskalna vrednost je v B4. Referenca ime -lista je v G3. Iskalna tabela na vseh listih je B3: AZ100. Vrednost želimo pridobiti iz dveh stolpcev. Zato to formulo zapišemo v C4 in jo povlečemo navzdol. Podobno za odsotne vrednosti spremenimo indeks stolpca.
| =VLOOKUP(B4,POSREDNO("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Kako deluje?
Formula je rešena navznoter. Najprej poglejmo, kako se to rešuje korak za korakom.
Ob predpostavki, da G3 vsebuje Jan.
| =VLOOKUP(B4,POSREDNO("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = VLOOKUP (B4, INDIREKTNO ("Jan! $ B $ 3: $ Az $ 100"),2,0) |
| = VLOOKUP (B4,Januar! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Torej, najprej je stavek "" & $ G $ 3 & "! $ B $ 3: $ Az $ 100" razrešen v niz "Jan! $ B $ 3: $ Az $ 100". Nato funkcija INDIRECT pretvori ta niz v dejanski sklic. In končno smo dobili formulo VLOOKUP (B4, jan! $ B $ 3: $ Az $ 100,2,0). In to se končno razreši na 7. Zdaj, če spremenite ime lista v G3, se bo referenčno besedilo vrednosti spremenilo. Takole iščete iz spremenljivih listov v Excelu.
Upam, da vam je bilo to v pomoč. Če imate kakršna koli vprašanja ali želite drugačno iskanje, mi to sporočite v spodnjem razdelku za komentarje. Z veseljem vam bom pomagal. Do takrat nadaljujte z odličnostjo.
Uporabite VLOOKUP iz dveh ali več iskalnih tabel | Za iskanje iz več tabel lahko uporabimo pristop IFERROR. Če pogledate iz več tabel, se napaka šteje za stikalo za naslednjo tabelo. Druga metoda je lahko pristop If.
Kako v Excelu poiskati velike in male črke | Excelova funkcija VLOOKUP ne razlikuje velikih in malih črk in bo vrnila prvo ujemajočo se vrednost s seznama. INDEX-MATCH ni izjema, vendar ga je mogoče spremeniti tako, da je občutljiv na velike in male črke. Poglejmo, kako…
Iskanje pogosto pojavljajočega se besedila z merili v Excelu | Iskanje se najpogosteje pojavi v besedilu v obsegu, ki ga uporabljamo INDEX-MATCH with MODE. Tukaj je metoda.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati funkcijo Excel VLOOKUP| To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih razponov in listov.
Kako uporabljati Excel COUNTIF funkcija| Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.