Sådan skriver du en Excel-formel, der vil slå en værdi op på et andet ark baseret på hvilket produkt der er valgt. Sådan trækkes data fra et andet regneark for hvert produkt.
Se video
- Rhonda fra Cincinnati: Hvordan ser man både række og regneark op?
- Brug kolonnen Dato til at finde ud af, hvilket ark du skal bruge
- Trin 1: Byg en regelmæssig VLOOKUP og brug FORMULATEXT til at se, hvordan referencen skal se ud
- Trin 2: Brug sammenkædning og TEKST-funktionen til at oprette en reference, der ligner tabelarrayreferencen i formlen
- Trin 3: Byg din VLOOKUP, men til tabelarrayet skal du bruge INDIRECT (resultater fra trin 2)
- Trin 4: Kopier formlen fra trin 2 (uden ligetegnet) og indsæt formlen fra trin 3
Videoudskrift
Lær Excel fra Podcast, afsnit 2173: Slå op på arket og rækken.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Jeg var i Cincinnati i sidste uge, og Rhonda i Cincinnati havde dette store spørgsmål. Rhonda har brug for at slå dette produkt op, men Look Up-tabellen er forskellig, afhængigt af hvilken måned det er. Se, vi har forskellige opslagstabeller her i januar til april og sandsynligvis også i de andre måneder. I orden.
Så jeg vil bruge INDIRECT til at løse dette, men før jeg gør INDIRECT, finder jeg det altid nemmere bare at lave en lige VLOOKUP. Så vi kan se, hvordan formularen vil se ud. Så vi ser op på A1 i denne tabel i januar, og vi vil have den syvende kolonne, komma FALSK, alle VLOOKUP'er slutter på FALSE. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)). I orden.
Og det er det rigtige svar. Det, jeg virkelig er interesseret i, er at få formelteksten til det. Så det viser mig, hvordan formlen vil se ud. Og hele tricket her er, jeg prøver at opbygge en hjælpekolonne, der kommer til at se nøjagtigt ud som denne reference, ikke? Så denne del - netop den del lige der. I orden. Så denne hjælpekolonne skal se ud som den ting ser ud. Og det første jeg vil gøre er, at vi bruger datoen TEXT-funktionen - datoen TEXT-funktion - for at få mmm, mellemrum, åååå-- så, "mmm åååå" sådan- - som skal returnere for hver af cellerne, hvilken måned vi ser op. Nu er jeg nødt til at pakke det ind i apostrofer. Hvis der ikke havde været et rumnavn derinde, ville jeg ikke have brug for apostroferne, men det gør jeg. Så vi skal koncentrere os foran,apostrofen, så det er citat - apostrof, citat - ampersand, og så herover, endnu et citat, apostrof og udråbstegn, A1: G13, afsluttende citat, ampersand der.
I orden. Så nu, hvad vi med succes har gjort her i Helper-kolonnen, er, at vi har bygget noget, der ligner nøjagtigt tabelarrayet i VLOOKUP. I orden. Så vores svar, det bliver så = VLOOKUP af denne celle, A2, komma, og når vi kommer til tabelarrayet, skal vi bruge INDIRECT. INDIREKTE er denne seje funktion, der siger, "Hej, her er en celle, der ligner en cellehenvisning, og jeg vil have dig til at gå til F2, tage den ting, der ligner en cellehenvisning, og brug derefter hvad der er i denne cellehenvisning som svaret, "komma, 7, komma, FALSK," sådan. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)) Okay, så nu, i farten, vælger vi en forskellige Look Up-tabeller og returnere værdierne afhængigt af om det er april eller hvad.
I orden. Så lad os tage dette 4/24, jeg ændrer det til 2/17/2018 sådan, og vi skal se, at 403 ændres til 203 - perfekt. Det fungerer. I orden. Nu har vi selvfølgelig ikke brug for disse to kolonner, og hvis du tænker over det, har vi ikke brug for hele denne kolonne. Vi kunne tage det hele, bortset fra lighedstegnet Ctrl + C for at kopiere det, og derefter, hvor vi har D2, skal du bare indsætte sådan. Perfekt. Dobbeltklik for at skyde det ned og slippe af med dette. Der er vores svar. Okay, vi bruger vores formeltekst her bare for at se på det endelige svar.
Jeg er nødt til at fortælle dig, at hvis jeg skulle bygge den formel lige fra bunden, ville jeg ikke gøre det. Jeg ville ikke være i stand til det. Jeg ville helt sikkert skrue det op. Derfor bygger jeg det altid i trin-- Jeg finder ud af, hvordan formlen vil se ud, og derefter koncentrerer jeg Helper-søjlen, der skal bruges inde i INDIREKTE, og til sidst, måske her i slutningen, sæt alt sammen igen.
Hej, mange tip som dette tip i bogen, Power Excel med. Dette er 2017-udgaven med 617 Excel-mysterium løst. Klik på det "I" i øverste højre hjørne for at få flere oplysninger.
Okay, afslutning fra denne episode: Rhonda fra Cincinnati - hvordan man ser både rækken og regnearket op. Jeg bruger kolonnen Dato til at finde ud af, hvilket ark jeg skal bruge; så jeg bygger en almindelig VLOOKUP og bruger formeltekst til at se, hvordan referencen skal se ud; og bygg derefter noget, der ligner den reference ved hjælp af tekstfunktionen til at konvertere datoen til en måned og et år; brug Concactenation til at opbygge noget, der ligner referencen; og derefter, når du bygger din VLOOKUP til det andet argument, tabelarrayet, skal du bruge INDIRECT; og peg derefter på resultaterne fra trin 2; og derefter det valgfri fjerde trin der, kopier formlen fra trin 2 uden lige tegn, og indsæt den i formlen fra trin 3, så du ender med en enkelt formel.
Jeg vil gerne takke Rhonda for at dukke op til mit seminar i Cincinnati, og jeg vil takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2173.xlsm
