Hvordan skrive en Excel-formel som vil slå opp en verdi på et annet ark basert på hvilket produkt som er valgt. Hvordan hente data fra et annet regneark for hvert produkt.
Se på video
- Rhonda fra Cincinnati: Hvordan slå opp både rad og regneark?
- Bruk dato-kolonnen for å finne ut hvilket ark du skal bruke
- Trinn 1: Bygg en vanlig VLOOKUP og bruk FORMULATEKST for å se hvordan referansen skal se ut
- Trinn 2: Bruk sammenkobling og TEKST-funksjonen til å bygge en referanse som ser ut som tabelloppstillingsreferansen i formelen
- Trinn 3: Bygg din VLOOKUP, men for tabelloppsettet, bruk INDIRECT (resultater fra trinn 2)
- Trinn 4: Kopier formelen fra trinn 2 (uten likhetstegnet) og lim inn i formelen fra trinn 3
Videoutskrift
Lær Excel fra Podcast, episode 2173: Slå opp arket og raden.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Jeg var i Cincinnati forrige uke, og Rhonda i Cincinnati hadde dette store spørsmålet. Rhonda må slå opp dette produktet, men Look Up-tabellen er forskjellig, avhengig av hvilken måned det er. Se, vi har forskjellige oppslagstabeller her for januar til april, og antagelig også for de andre månedene. Ok.
Så jeg skal bruke INDIRECT for å løse dette, men før jeg gjør INDIRECT, synes jeg det alltid er lettere bare å gjøre en rett VLOOKUP. Så vi kan se hvordan skjemaet skal se ut. Så vi ser opp A1 i denne tabellen i januar, og vi vil ha den syvende kolonnen, komma FALSE, alle VLOOKUP-er ender på FALSE. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)). Ok.
Og vel, det er det riktige svaret. Det jeg virkelig er interessert i, er å få formelteksten til det. Så det viser meg hvordan formelen skal se ut. Og hele trikset her er, jeg prøver å bygge en Helper-kolonne som kommer til å se akkurat ut som denne referansen, ikke sant? Så denne delen - akkurat den delen der. Ok. Så denne Helper-kolonnen må se ut som den tingen ser ut. Og det første jeg vil gjøre er at vi skal bruke TEKST-funksjonen til Datoen - TEKST-funksjonen til datoen - for å få mmm, mellomrom, åååå-- så, "mmm åååå" sånn- - som skal returnere, for hver av cellene, hvilken måned vi ser opp. Nå må jeg pakke det inn i apostrofer. Hvis det ikke hadde vært et mellomrom der inne, ville jeg ikke trengt apostrofene, men det gjør jeg. Så vi skal konsentrere oss foran,apostrofen, så det er sitat - apostrof, sitat - ampersand, og så her borte, et annet sitat, apostrof og utropstegn, A1: G13, avsluttende sitat, ampersand der.
Ok. Så det vi vellykket har gjort her i Helper-kolonnen, er at vi har bygget noe som ser ut akkurat som tabelloppsettet i VLOOKUP. Ok. Så svaret vårt, da blir det = VLOOKUP av denne cellen, A2, komma, og når vi kommer til tabellmatrisen, skal vi bruke INDIRECT. INDIREKTE er denne kule funksjonen som sier "Hei, her er en celle som ser ut som en cellehenvisning, og jeg vil at du skal gå til F2, ta den tingen som ser ut som en cellehenvisning, og deretter bruke det som er i den cellehenvisningen som svaret, "komma, 7, komma, FALSK", slik. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)) Greit, så nå, på farten, velger vi en forskjellige Slå opp-tabeller og returnere verdiene avhengig av om det er april eller hva.
Ok. Så la oss ta dette 4/24, jeg endrer det til 17.02.2018, sånn, og vi skal se at 403 endres til 203 - perfekt. Det fungerer. Ok. Nå trenger vi selvfølgelig ikke disse to kolonnene, og hvis du tenker på det, trenger vi ikke hele denne kolonnen. Vi kan ta det hele, bortsett fra likhetstegnet, Ctrl + C for å kopiere det, og der hvor vi har D2, bare lim inn, slik. Perfekt. Dobbeltklikk for å skyte det ned og bli kvitt dette. Det er vårt svar. Greit, vi bruker formelteksten vår her, bare for å se på det endelige svaret.
Jeg må fortelle deg at hvis jeg måtte bygge den formelen bare fra bunnen av, ville jeg ikke gjort det. Jeg ville ikke klart det. Jeg ville slått til, helt sikkert. Derfor bygger jeg den alltid i trinn-- Jeg finner ut hvordan formelen kommer til å se ut, og deretter Concactenate Helper-kolonnen som skal brukes inne i INDIREKTE, og til slutt, kanskje her på slutten, setter alt sammen igjen.
Hei, mange tips som dette tipset i boka, Power Excel med. Dette er 2017-utgaven med 617 Excel-mysterium løst. Klikk på "I" øverst til høyre for mer informasjon.
Greit, oppsummering fra denne episoden: Rhonda fra Cincinnati - hvordan du kan slå opp både raden og regnearket. Jeg bruker kolonnen Dato for å finne ut hvilket ark jeg skal bruke; så jeg bygger en vanlig VLOOKUP og bruker formeltekst for å se hvordan referansen skal se ut; og bygg deretter noe som ser ut som referansen ved hjelp av tekstfunksjonen for å konvertere datoen til en måned og et år; bruk Concactenation for å bygge noe som ser ut som referansen; og så når du bygger din VLOOKUP for det andre argumentet, tabellmatrisen, bruker du INDIRECT; og pek deretter på resultatene fra trinn 2; og deretter det valgfrie fjerde trinnet der, kopier formelen fra trinn 2, uten likhetstegnet, og lim den inn i formelen fra trinn 3, slik at du ender opp med en enkelt formel.
Vel, jeg vil takke Rhonda for at du møtte opp til seminaret mitt i Cincinnati, og jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2173.xlsm
