Představte si, že máte v sešitu více identických listů, které obsahují identické tabulky (například záznamy o docházce každého měsíce v samostatném listu). Nyní chcete vytvořit řídicí panel, který vám ukáže celkovou návštěvnost za měsíc. Nyní mít data každého měsíce na hlavním panelu současně není dobrá volba. Chceme rozevírací seznam pro výběr měsíce. Potřebujeme vzorec VLOOKUP, na který se podíváme z listu zvoleného měsíce.
Jednoduše řečeno, pro vyhledávání z proměnných listů potřebujeme vzorec pro vyhledávání.
Jak ukazuje výše uvedený gif, použijeme společně funkci VLOOKUP a INDIRECT k vyhledávání z více listů na základě jejich názvů.
Obecný vzorec pro vyhledávání více listů
| =VLOOKUP(lookup_value,NEPŘÍMÝ(""&reference_name_reference&"! lookup_table "), col_index, 0) |
Lookup_value: To je hodnota, kterou hledáte při vyhledávání stůl.
Reference_název_listu: Toto je odkaz na buňku, která obsahuje název listu.
Lookup_table: Toto je referenční tabulka, ve které chcete vyhledat lookup_value. Může to být pojmenovaný rozsah, tabulka nebo absolutní reference. Mělo by to být stejné ve všech listech.
Col_Index: Toto je číslo sloupce v tabulce, ze které chcete načíst hodnotu. Pokud jste obeznámeni s funkcí VLOOKUP, víte, co to je.
Pojďme tedy na příklad.
Příklad: Získat docházku za vybraný měsíc
Takže máme sešit, který udržuje účast mých studentů Excelu. Data každého měsíce jsou uložena samostatně v různých listech. Název listu je nastaven na název měsíců. Prozatím mám data za tři měsíce, ale bude jich samozřejmě více.
Chci vytvořit sestavu, která bude ukazovat návštěvnost vybraného měsíce. Měsíc lze vybrat z rozevíracího seznamu. Vzorec by měl být schopen automaticky vyhledat z tohoto listu, i když je k němu přidán nový list.
Připravíme tedy výše uvedenou tabulku. Buňku G3 jsme vytvořili rozevírací seznam pomocí rozevíracího seznamu.
Vyhledávací hodnota je v B4. Odkaz na název_listu je v G3. Vyhledávací tabulka ve všech listech je B3: AZ100. Chtěli bychom načíst hodnotu ze 2 sloupců. Tento vzorec tedy napíšeme do C4 a přetáhneme dolů. Podobně pro chybějící hodnoty měníme sloupcový index.
| =VLOOKUP(B4,NEPŘÍMÝ("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Jak to funguje?
Vzorec je vyřešen naruby. Nejprve se podívejme, jak je krok za krokem řešen.
Za předpokladu, že G3 obsahuje Jan.
| =VLOOKUP(B4,NEPŘÍMÝ("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = VLOOKUP (B4, NEPŘÍMÝ ("Jan! $ B $ 3: $ Az $ 100"),2,0) |
| = VLOOKUP (B4,Jan! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Nejprve je tedy výrok „“ & $ G $ 3 & „! $ B $ 3: $ Az $ 100“ vyřešen na řetězec „Jan! $ B $ 3: $ Az $ 100“. Dále funkce INDIRECT převede tento řetězec na skutečnou referenci. A nakonec jsme dostali vzorec VLOOKUP (B4, Jan! $ B $ 3: $ Az $ 100,2,0). A toto se nakonec vyřeší na 7. Nyní, pokud změníte název listu v G3, text odkazu na hodnotu se změní. A takto se díváte nahoru z variabilních listů v Excelu.
Doufám, že vám to pomohlo. Pokud máte nějaké dotazy nebo chcete provést jiné vyhledávání, dejte mi vědět v sekci komentáře níže. Rád vám pomůžu. Do té doby pokračujte v excelování.
Použijte VLOOKUP ze dvou nebo více vyhledávacích tabulek | Pro vyhledávání z více tabulek můžeme použít přístup IFERROR. Vyhledávání z více tabulek bere chybu jako přepínač pro další tabulku. Další metodou může být přístup If.
Jak provádět vyhledávání rozlišující malá a velká písmena v aplikaci Excel | funkce VLOOKUP aplikace Excel nerozlišuje velká a malá písmena a vrátí první odpovídající hodnotu ze seznamu. INDEX-MATCH není výjimkou, ale lze jej upravit tak, aby rozlišoval velká a malá písmena. Podívejme se, jak…
Vyhledávání často se vyskytujícího textu s kritérii v aplikaci Excel | Vyhledávání se nejčastěji objevuje v textu v rozsahu, který používáme INDEX-MATCH s funkcí MODE. Zde je metoda.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat funkci Excel VLOOKUP| Jedná se o jednu z nejpoužívanějších a nejoblíbenějších funkcí aplikace Excel, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat Excel Funkce COUNTIF| Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.