Predstavte si, že máte v zošite viacero rovnakých listov, ktoré obsahujú identické tabuľky (napríklad záznamy o dochádzke každého mesiaca v samostatnom hárku). Teraz chcete vytvoriť informačný panel, ktorý vám ukáže celkovú návštevnosť za mesiac. Teraz mať údaje o každom mesiaci na hlavnom paneli súčasne nie je dobrá voľba. Chceme rozbaľovací zoznam vybrať mesiac. Potrebujeme vzorec VLOOKUP, na ktorý sa musíme pozrieť z listu zvoleného mesiaca.
Jednoducho povedané, potrebujeme vzorec na vyhľadávanie, aby sme mohli vyhľadávať z variabilných hárkov.
Ako ukazuje vyššie uvedený gif, budeme používať funkciu VLOOKUP a INDIRECT spoločne na vyhľadávanie hore z viacerých hárkov na základe ich názvov.
Obecný vzorec na vyhľadanie viacerých hárkov
| =VLOOKUP(lookup_value,NEPRIAMY(""&referencia_nazov_listu&"! lookup_table "), col_index, 0) |
Lookup_value: To je hodnota, ktorú hľadáte pri vyhľadávaní stôl.
Referencia_nazov_listu: Toto je odkaz na bunku, ktorá obsahuje názov hárka.
Vyhľadávacia tabuľka: Toto je odkaz na tabuľku, v ktorej chcete hľadať lookup_value. Môže to byť pomenovaný rozsah, tabuľka alebo absolútna referencia. Malo by to byť rovnaké vo všetkých listoch.
Col_Index: Toto je číslo stĺpca v tabuľke, z ktorého chcete získať hodnotu. Ak poznáte funkciu VLOOKUP, viete, o čo ide.
Poďme teda na príklad.
Príklad: Získanie dochádzky za vybraný mesiac
Máme teda pracovný zošit, ktorý udržiava dochádzku mojich študentov z Excelu. Údaje za každý mesiac sa ukladajú oddelene do rôznych hárkov. Názov listu je nastavený na názov mesiacov. Zatiaľ mám údaje za tri mesiace, ale bude ich samozrejme viac.
Chcem vytvoriť prehľad, ktorý ukazuje návštevnosť vybraného mesiaca. Mesiac je možné vybrať z rozbaľovacieho zoznamu. Vzorec by mal byť schopný automaticky vyhľadať hore z tohto hárka, aj keď k nemu je pridaný nový hárok.
Preto pripravíme vyššie uvedenú tabuľku. Bunku G3 sme vytvorili rozbaľovací zoznam pomocou rozbaľovacieho zoznamu.
Hodnota vyhľadávania je v B4. Odkaz na názov_listu je v G3. Vyhľadávacia tabuľka vo všetkých hárkoch je B3: AZ100. Chceli by sme získať hodnotu z 2 stĺpcov. Tento vzorec teda napíšeme do C4 a potiahneme ho nadol. Podobne pre chýbajúce hodnoty zmeníme stĺpcový index.
| =VLOOKUP(B4,NEPRIAMY("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Ako to funguje?
Vzorec je vyriešený naruby. Najprv sa pozrime, ako je krok za krokom vyriešený.
Za predpokladu, že G3 obsahuje Jan.
| =VLOOKUP(B4,NEPRIAMY("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = VLOOKUP (B4, NEPRIAMY ("Jan! $ B $ 3: $ Az $ 100"),2,0) |
| = VLOOKUP (B4,Jan! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Takže najskôr je vyhlásenie „“ & $ G $ 3 & ”! $ B $ 3: $ Az $ 100“ vyriešené na reťazec „Jan! $ B $ 3: $ Az $ 100“. Ďalej funkcia INDIRECT prevádza tento reťazec na skutočnú referenciu. A nakoniec sme dostali vzorec VLOOKUP (B4, Jan! $ B $ 3: $ Az $ 100,2,0). A to sa nakoniec vyrieši na 7. Teraz, ak zmeníte názov listu v G3, referenčný text hodnoty sa zmení. A takto sa pozeráte hore z variabilných hárkov v Exceli.
Dúfam, že vám to pomohlo. Ak máte akékoľvek otázky alebo chcete vykonať iné vyhľadávanie, dajte mi vedieť v sekcii komentárov nižšie. Rád vám pomôžem. Do tej doby pokračujte v excelovaní.
Použite VLOOKUP z dvoch alebo viacerých vyhľadávacích tabuliek | Na vyhľadávanie z viacerých tabuliek môžeme použiť prístup IFERROR. Pri pohľade hore z viacerých tabuliek sa chyba použije ako prepínač pre nasledujúcu tabuľku. Ďalšou metódou môže byť prístup If.
Ako vyhľadávať veľké a malé písmena v programe Excel | funkcia VLOOKUP v Exceli nerozlišuje malé a veľké písmená a vráti prvú zodpovedajúcu hodnotu zo zoznamu. INDEX-MATCH nie je výnimkou, ale môže byť upravený tak, aby rozlišoval veľké a malé písmena. Pozrime sa, ako…
Vyhľadávanie často sa vyskytujúceho textu s kritériami v programe Excel | Vyhľadávanie sa najčastejšie vyskytuje v texte v rozsahu, v ktorom používame funkciu INDEX-MATCH s funkciou REŽIM. Tu je metóda.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať funkciu Excel VLOOKUP| Jedná sa o jednu z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať Excel Funkcia COUNTIF| Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.