Ako napísať vzorec programu Excel, ktorý vyhľadá hodnotu na inom hárku podľa toho, ktorý produkt je vybraný. Ako pre každý produkt načítať údaje z iného pracovného hárka.
Pozeraj video
- Rhonda z Cincinnati: Ako vyhľadať riadok aj pracovný hárok?
- V stĺpci Dátum môžete zistiť, ktorý hárok sa má použiť
- Krok 1: Vytvorte bežný VLOOKUP a pomocou formulára FORMULATEXT zistite, ako by mal odkaz vyzerať
- Krok 2: Pomocou reťazenia a funkcie TEXT vytvorte odkaz, ktorý vo vzorci vyzerá ako odkaz na pole tabuľky
- Krok 3: Vytvorte si VLOOKUP, ale pre pole tabuľky použite INDIRECT (výsledky z kroku 2)
- Krok 4: Skopírujte vzorec z kroku 2 (bez znamienka rovnosti) a vložte ho do vzorca z kroku 3
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2173: Vyhľadajte hárok a riadok.
Ahoj, vitaj späť na netcastu, som Bill Jelen. Bol som minulý týždeň v Cincinnati a Rhonda v Cincinnati mala túto skvelú otázku. Rhonda musí vyhľadať tento produkt, ale tabuľka Look Up sa líši v závislosti od toho, o ktorý mesiac ide. Vidíte, máme tu rôzne tabuľky vyhľadávania pre január až apríl a pravdepodobne aj pre ostatné mesiace. Dobre.
Použijem na to teda riešenie INDIRECT, ale skôr ako urobím INDIRECT, vždy je pre mňa jednoduchšie urobiť priamy VLOOKUP. Takže môžeme vidieť, ako bude vyzerať formulár. V januári teda hľadáme v tejto tabuľke A1 a chceme, aby siedmy stĺpec, čiarka FALSE, všetky VLOOKUPy končili FALSE. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)). Dobre.
A je to správna odpoveď. To, čo ma skutočne zaujíma, je dostať text vzorca. Takže mi ukazuje, ako bude vyzerať vzorec. Celý trik je taký, že sa snažím vytvoriť stĺpec Pomocník, ktorý bude vyzerať presne ako tento Reference, že? Takže táto časť - iba táto časť práve tam. Dobre. Takže tento stĺpec Pomocník musí vyzerať tak, ako vyzerá. A prvá vec, ktorú chcem urobiť, je to, že použijeme funkciu TEXT dátumu - funkciu TEXT dátumu - aby sme dostali mmm, medzeru, rrrr-- takže, „mmm rrrr“ takto - - ktoré by sa mali vrátiť pre každý z buniek, v ktorom mesiaci hľadáme. Teraz to musím zabaliť do apostrofov. Keby tam nebol vesmírny názov, nepotreboval by som apostrofy, ale áno. Takže pôjdeme do akcie vpredu,apostrof, takže to je citát - apostrof, citát - ampersand a potom sem, ďalší citát, apostrof a výkričník, A1: G13, záverečný citát, ampersand.
Dobre. Takže, čo sme tu v stĺpci Pomocník úspešne vykonali, je to, že sme vytvorili niečo, čo vyzerá presne ako tabuľkové pole vo VLOOKUPE. Dobre. Naša odpoveď teda bude = VLOOKUP tejto bunky, čiarka A2, a potom, keď sa dostaneme k poľu tabuľky, použijeme NEPRIAME. INDIRECT je táto skvelá funkcia, ktorá hovorí: „Hej, tu je bunka, ktorá vyzerá ako odkaz na bunku, a chcem, aby ste prešli na F2, zoberte vec, ktorá vyzerá ako odkaz na bunku, a potom použite čokoľvek, čo je v tejto bunkovej referencii ako odpoveď: „comma, 7, comma, FALSE,“ like that. (= VLOOKUP (A2, „Jan 2018“! A1: G13,7, FALSE)) V poriadku, takže teraz, za behu, vyberáme iná vyhľadávacia tabuľka a vrátenie hodnôt podľa toho, či je apríl alebo čo.
Dobre. Vezmime si teda toto 24. 4., zmeníme to takto na 17. 2. 2018 a mali by sme vidieť, že 403 sa zmení na 203 - perfektné. Funguje to. Dobre. Teraz tu samozrejme nepotrebujeme tieto dva stĺpce a naozaj, ak sa nad tým zamyslíte, nepotrebujeme celý tento stĺpec. Mohli by sme to celé vziať, až na znamienko rovnosti, Ctrl + C, aby sme to skopírovali, a potom tam, kde máme D2, len tak prilepíme. Perfektné. Dvojitým kliknutím to vystrelíte a zbavíte sa toho. Existuje naša odpoveď. Dobre, použijeme tu náš vzorec, len aby sme sa pozreli na túto konečnú odpoveď.
Musím vám povedať, že keby som mal vytvoriť tento vzorec iba od nuly, neurobil by som to. Ja by som to nedokázal. Ja by som to prešibal. Preto to vždy zostavujem v krokoch-- Prišiel som na to, ako bude vyzerať vzorec, a potom spojil stĺpec Pomocník, ktorý sa použije vo vnútri NEPRIAME, a nakoniec, možno tu na konci, dal všetko dohromady.
Ahoj, veľa tipov ako tento tip v knihe, Power Excel with. Toto je vydanie z roku 2017 s vyriešenou záhadou 617 Excel. Kliknutím na „I“ v pravom hornom rohu získate ďalšie informácie.
Dobre, záver z tejto epizódy: Rhonda z Cincinnati - ako vyhľadať riadok aj pracovný hárok. Pomocou stĺpca Dátum zistím, ktorý hárok použiť; takže zostavím bežný VLOOKUP a pomocou textu vzorca zistím, ako by mal odkaz vyzerať; a potom pomocou textovej funkcie na skonvertovanie dátumu na mesiac a rok vytvoriť niečo, čo vyzerá ako tento odkaz; použite Concactenation na vytvorenie niečoho, čo vyzerá ako referencia; a potom, keď zostavujete VLOOKUP pre druhý argument, tabuľkové pole, použite INDIRECT; a potom ukážte na výsledky z kroku 2; a potom tam nepovinný štvrtý krok, skopírujte vzorec z kroku 2 bez znamienka rovnosti a vložte ho do vzorca z kroku 3, aby ste nakoniec získali jeden vzorec.
Chcem poďakovať Rhonde za účasť na mojom seminári v Cincinnati a chcem sa vám poďakovať za návštevu. Uvidíme sa nabudúce na ďalšom netcaste od.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2173.xlsm
