Kako napisati Excelovo formulo, ki bo iskala vrednost na drugem listu glede na izbrani izdelek. Kako povleči podatke iz drugega delovnega lista za vsak izdelek.
Oglejte si video
- Rhonda iz Cincinnatija: Kako poiskati vrstice in delovni list?
- V stolpcu Datum določite, kateri list uporabiti
- 1. korak: Zgradite redni VLOOKUP in z FORMULATEXT preverite, kako naj bo referenca videti
- 2. korak: Uporabite povezovanje in funkcijo TEXT za izdelavo sklica, ki je podoben sklicu na matrično tabelo v formuli
- 3. korak: zgradite svoj VLOOKUP, za matrično tabelo pa uporabite INDIRECT (rezultati 2. koraka)
- 4. korak: Kopirajte formulo iz 2. koraka (brez znaka enačbe) in prilepite v formulo iz 3. koraka
Video zapis
Naučite se Excel iz podcasta, epizoda 2173: Poiščite list in vrstico.
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Prejšnji teden sem bil v Cincinnatiju in Rhonda v Cincinnatiju je imela to čudovito vprašanje. Rhonda mora poiskati ta izdelek, vendar je tabela Look Up drugačna, odvisno od tega, kateri mesec je. Glej, tukaj imamo različne tabele za iskanje od januarja do aprila, verjetno pa tudi za druge mesece. Vredu.
Torej bom za rešitev tega uporabil INDIRECT, toda preden naredim INDIRECT, vedno lažje naredim naravnost VLOOKUP. Tako lahko vidimo, kako bo videti obrazec. Torej januarja poiščemo A1 v tej tabeli in želimo, da se sedmi stolpec, vejica FALSE, vsi VLOOKUP-i končajo z FALSE. (= OGLED (A2, 'januar 2018'! A1: G13,7, FALSE)). Vredu.
No, to je pravi odgovor. V resnici me zanima, kako dobiti besedilo formule tega. Tako mi pokaže, kako bo videti formula. In celoten trik je v tem, da poskušam zgraditi stolpec Helper, ki bo videti povsem tako kot ta referenca, kajne? Torej ta del - ravno tisti del tam. Vredu. Torej mora biti ta stolpec Helper videti tako, kot je videti. In prva stvar, ki jo želim narediti, je, da bomo uporabili funkcijo TEXT datuma - funkcijo TEXT datuma - da dobimo mmm, presledek, llll - tako, "mmm yyyy", - ki naj za vsako celico vrne, kateri mesec iščemo. Zdaj moram to zaviti v apostrofe. Če notri ne bi bilo vesoljskega imena, ne bi potreboval apostrofov, vendar jih potrebujem. Torej gremo v Concactenate spredaj,apostrof, torej to je citat-- apostrof, citat-- ampersand, nato pa sem, še en citat, apostrof in klicaj, A1: G13, zaključni citat, ampersand.
Vredu. Torej, kar smo tukaj v stolpcu Helper uspešno naredili, je, da smo zgradili nekaj, kar je videti popolnoma tako kot matrična matrika v VLOOKUP. Vredu. Torej, naš odgovor bo = VLOOKUP te celice, A2, vejica, in potem, ko pridemo do tabele, bomo uporabili INDIRECT. INDIRECT je ta kul funkcija, ki pravi: "Hej, tukaj je celica, ki je videti kot referenca celice, in želim, da greš na F2, vzameš stvar, ki je videti kot referenca celice, in nato uporabiš vse, kar je v tej referenci celice, kot odgovor, "vejica, 7, vejica, FALSE," tako. (= VLOOKUP (A2, 'januar 2018'! A1: G13,7, FALSE)) V redu, zato zdaj na poti izberemo različna tabela Poišči in vrne vrednosti, odvisno od tega, ali je april ali kaj drugega.
Vredu. Torej, vzemimo to 24. 4., jaz jo spremenim v 17. 2. 2018, in videli bi, da se 403 spremeni v 203 - popolno. Deluje. Vredu. Zdaj tukaj seveda ne rabimo teh dveh stolpcev in res, če dobro premislite, ne potrebujemo celotnega stolpca. Lahko bi vzeli celo to stvar, razen znaka enačbe, Ctrl + C, da bi jo kopirali, nato pa tam, kjer imamo D2, samo prilepite. Odlično. Dvokliknite, da ga ustrelite in se tega znebite. Tu je naš odgovor. V redu, tukaj bomo uporabili naše besedilo formule, samo da si bomo ogledali ta končni odgovor.
Moram vam povedati, če bi moral to formulo zgraditi samo iz nič, tega ne bi storil. Ne bi mogel. Zagotovo bi ga zamočil. Zato ga vedno zgradim po korakih - ugotovim, kako bo videti formula, nato združim stolpec Helper, ki bo uporabljen znotraj INDIRECT, nato pa na koncu, morda tukaj na koncu, vse sestavim.
Hej, veliko nasvetov, kot je ta nasvet v knjigi, Power Excel z. To je izdaja 2017 z rešeno skrivnostjo 617 Excel. Za več informacij kliknite ta "I" v zgornjem desnem kotu.
V redu, zaključek te epizode: Rhonda iz Cincinnatija - kako poiskati vrstico in delovni list. S pomočjo stolpca Datum ugotovim, kateri list uporabiti; zato zgradim navaden VLOOKUP in z besedilom formule vidim, kako naj bo referenca videti; in nato z uporabo funkcije besedila za pretvorbo datuma v mesec in leto zgradite nekaj, kar je videti kot referenca; uporabite Concactenation, da ustvarite nekaj, kar je videti kot referenca; in nato, ko zgradite svoj VLOOKUP za drugi argument, matriko tabel, uporabite INDIRECT; in nato pokažite na rezultate iz 2. koraka; in nato neobvezni četrti korak, kopirajte formulo iz koraka 2 brez znaka enačbe in jo prilepite v formulo iz koraka 3, tako da boste dobili eno formulo.
No, rad bi se zahvalil Rhondi, ker se je pojavila na mojem seminarju v Cincinnatiju, in zahvalil bi se vam, da ste se ustavili. Se vidimo naslednjič za še eno oddajo od.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2173.xlsm
