Jak napsat vzorec aplikace Excel, který vyhledá hodnotu na jiném listu podle toho, který produkt je vybrán. Jak pro každý produkt získat data z jiného listu.
Sledovat video
- Rhonda z Cincinnati: Jak vyhledat řádek i list?
- Ve sloupci Datum můžete zjistit, který list použít
- Krok 1: Vytvořte běžný VLOOKUP a pomocí FORMULATEXT zjistěte, jak by měl vypadat odkaz
- Krok 2: Použijte Zřetězení a funkci TEXT k vytvoření odkazu, který vypadá jako odkaz na pole tabulky ve vzorci
- Krok 3: Vytvořte si SVYHLEDÁVÁNÍ, ale pro pole tabulky použijte NEPŘÍMÉ (výsledky z kroku 2)
- Krok 4: Zkopírujte vzorec z kroku 2 (bez znaménka rovnosti) a vložte jej do vzorce z kroku 3
Přepis videa
Naučte se Excel z Podcastu, epizoda 2173: Vyhledejte list a řádek.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Byl jsem minulý týden v Cincinnati a Rhonda v Cincinnati měla tuto skvělou otázku. Rhonda musí tento produkt vyhledat, ale tabulka Look Up se liší podle toho, o který měsíc se jedná. Podívejte se, máme zde různé vyhledávací tabulky pro leden až duben a pravděpodobně i pro ostatní měsíce. V pořádku.
Takže to vyřeším pomocí NEPŘÍMÉHO, ale než to udělám NEPŘÍMÉ, vždy mi připadá snazší udělat přímý VHLEDÁNÍ. Takže můžeme vidět, jak bude formulář vypadat. V lednu tedy v této tabulce vyhledáváme A1 a chceme, aby sedmý sloupec, čárka FALSE, všechny VLOOKUPy skončily FALSE. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)). V pořádku.
A to je správná odpověď. To, co mě opravdu zajímá, je dostat text vzorce. Ukazuje mi tedy, jak bude vzorec vypadat. A celý trik je, že se snažím vytvořit sloupec Pomocník, který bude vypadat přesně jako tato Reference, že? Takže tato část - právě ta část právě tam. V pořádku. Takže tento sloupec Pomocník musí vypadat, že ta věc vypadá. A první věc, kterou chci udělat, je, že použijeme funkci TEXT Date - funkci TEXT data - abychom dostali mmm, mezeru, rrrr-- tak, „mmm rrrr“ takhle- - který by se měl vrátit pro každou z buněk, jaký měsíc vzhlížíme. Teď to musím zabalit do apostrofů. Kdyby tam nebyl vesmírný název, nepotřeboval bych apostrofy, ale ano. Takže pojedeme dopředu,apostrof, takže to je citát - apostrof, citát - ampersand, a pak tady, další citát, apostrof a vykřičník, A1: G13, závěrečný citát, ampersand tam.
V pořádku. Takže teď, co jsme zde úspěšně provedli ve sloupci Pomocník, jsme vytvořili něco, co vypadá přesně jako pole tabulky ve VLOOKUP. V pořádku. Naše odpověď tedy bude = VLOOKUP této buňky, čárka A2, a pak, když se dostaneme k poli tabulky, použijeme NEPŘÍMÉ. INDIRECT je tato skvělá funkce, která říká: „Hej, tady je buňka, která vypadá jako odkaz na buňku, a já chci, abys šel na F2, vzal si věc, která vypadá jako odkaz na buňku, a pak použil, co je v tomto odkazu na buňku jako odpověď: „čárka, 7, čárka, FALSE,“ taková. (= VLOOKUP (A2, „Jan 2018“! A1: G13,7, FALSE)) Dobře, takže za běhu volíme jinou vyhledávací tabulku a vrácení hodnot podle toho, zda je duben nebo co.
V pořádku. Vezměme si to 24. 4., změním to na 17. 2. 2018, a tak bychom měli vidět, že 403 se změní na 203 - perfektní. Funguje to. V pořádku. Nyní tu samozřejmě tyto dva sloupce nepotřebujeme a opravdu, pokud o tom přemýšlíte, nepotřebujeme celý tento sloupec. Mohli bychom to celé vzít, až na znaménko rovná se, Ctrl + C, aby se to zkopírovalo, a pak tam, kde máme D2, stačí vložit, takhle. Perfektní. Poklepáním to sestřelte a zbavte se toho. Tady je naše odpověď. Dobře, použijeme zde náš vzorec, abychom se podívali na tu konečnou odpověď.
Musím vám říci, že kdybych měl ten vzorec postavit úplně od začátku, neudělal bych to. Nebyl bych schopen to udělat. Určitě bych to pokazil. Proto to vždy vytvářím v krocích - zjistím, jak bude vzorec vypadat, a poté spojím sloupec Pomocník, který bude použit uvnitř NEPŘÍMÉ, a nakonec, možná tady na konci, dám vše dohromady.
Ahoj, mnoho tipů, jako je tento tip v knize, Power Excel s. Toto je vydání z roku 2017 s vyřešenou záhadou 617 Excel. Další informace zobrazíte kliknutím na „I“ v pravém horním rohu.
Dobře, shrnutí z této epizody: Rhonda z Cincinnati - jak vyhledat řádek i list. Pomocí sloupce Datum zjistím, který list použít; vytvořím tedy pravidelný VLOOKUP a pomocí textu vzorce zjistím, jak by měl vypadat odkaz; a poté pomocí textové funkce vytvořte něco, co vypadá jako tento odkaz, abyste převedli Datum na Měsíc a Rok; použijte Concactenation k vytvoření něčeho, co vypadá jako reference; a poté, když vytvoříte VLOOKUP pro druhý argument, pole tabulky, použijte INDIRECT; a poté přejděte na výsledky z kroku 2; a potom tam volitelný čtvrtý krok, zkopírujte vzorec z kroku 2 bez znaménka rovná se a vložte jej do vzorce z kroku 3, takže skončíte s jediným vzorcem.
Chci poděkovat Rhondě za to, že jste se zúčastnili mého semináře v Cincinnati, a chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2173.xlsm
