VLOOKUP er fantastisk og min favorittfunksjon
Ikke bare gjør disse tabellene forfriskende data lettere, de gjør også leseformler mye enklere! Det eneste du trenger å gjøre er å trykke Ctrl + T før du skriver formelen.
La oss gå tilbake til VLOOKUP-formelen ovenfra. Denne gangen konverterer du varetabellen og kjøpstabellen til en Excel-tabell med Ctrl + T helt fra starten! For å gjøre ting enklere, gi hver tabell et vennlig navn ved å bruke kategorien Tabellverktøy:
Skriv inn VLOOKUP igjen uten å gjøre noe annerledes enn du vanligvis gjør, formelen din i C2 er nå i =VLOOKUP((@Item),Items,2,0)stedet for =VLOOKUP(B2,$E$5:$F$10,2,0)!
Selv om varetabellen er på et annet regneark, er formelen den samme, i stedet for den mindre lesbare =VLOOKUP(B2,Items!$A$2:$B$7,2,0).
(@Item) i formelen refererer til cellen i kolonnen Element i denne tabellen (i samme rad som formelen) og er derfor den samme i hele kolonnen. Og Items refererer til hele varetabellen (uten overskrifter). Best av alt, du trenger ikke å skrive noe av dette. Når dette er en tabell, vil Excel plassere disse navnene i formelen din når du velger celler / områder!
La oss ta dette et skritt videre. Legg til en annen kolonne i salgstabellen for å beregne inntektene med formelen =(@Price)*(@Qty). Hvis du nå vil beregne den totale inntekten, er formelen =SUM(Sales(Revenue)); som er veldig lett å forstå, uansett hvor dataene er eller hvor mange rader de dekker!
Se på video
- VLOOKUP er fantastisk og min favorittfunksjon
- VLOOKUP-hatere klager over at det er skjørt på grunn av det tredje argumentet
- Hvis formen på oppslagstabellen endres, kan svarene endres
- En løsning er å erstatte det tredje argumentet med MATCH
- Men forestill deg å gjøre et KAMP for 1000 rader med VLOOKUP
- Gjør oppslagstabellen din til en tabell før du gjør VLOOKUP
- Den strukturerte tabellreferansen vil håndtere hvis tabellformen endres
- Pluss at det ikke krever å gjøre en MATCH om og om igjen
- Peter Albert sendte inn dette tipset
Videoutskrift
Lær Excel for Podcast, episode 2003 - Lesbare referanser
Ikke glem å abonnere på XL-spillelisten. Jeg podcaster hele denne boka.
Greit dagens tips fra Peter Albert. Peter Albert. La oss nå snakke om VLOOKUP. Jeg er en stor VLOOKUP-fan. For meg er VLOOKUP skillelinjen. Hvis du kan gjøre VLOOKUPs, vil alt annet i Excel være enkelt for deg. Så VLOOKUP lar oss slå opp prisen fra tabellen, og vi snakker om VLOOKUP mer senere.
Så kopier dette ned, og alt fungerer bra, men jeg må si deg det. Jeg har sett dem. Jeg har snakket med dem. Jeg har møtt dem. Det er VLOOKUP hatere der ute. Folk som hater hvis du ser opp og andre klager er at det er så skjørt, det tredje argumentet, der vi sa at vi vil ha den tredje kolonnen, at hvis noen senere vil bestemme at vi trenger et nytt felt her, kanskje like, størrelse . OK, for det første ser det ut til å være en slags feil som Excel ikke beregner hele saken på nytt. La meg angre, angre og deretter gjøre om. Der går vi. Det er rart, jeg må rapportere det til Excel-teamet, men du ser at der hvor vi fikk pris, fikk det nå farge, fordi det var hardkodet å si at de vil ha den tredje kolonnen. Greit og hva folk gjør for å omgå dette er denne sprø tingen med = MATCH.Gå og se etter ordet Pris i første rad i tabellen, F4,0, og det vil fortelle oss at prisen på dette punktet er den fjerde kolonnen. Så de vil faktisk gjøre = VLOOKUP. Vi ser opp A104, i denne tabellen. F4, og i stedet for å hardkode nummer fire, gjør de en MATCH og MATCH kommer til å bli låst til prisen. Så F4, to ganger for å sette $ før 1, og det kommer til å se gjennom den første raden i tabellen. Ups, F4 to ganger, komma, savnet kommaet. OK, trykk F4 her komma 0 for en nøyaktig samsvar med kampen, og deretter faller komma for en nøyaktig samsvar med VLOOKUP. Ja og hei dette fungerer bra, og her har jeg bare seks av dem, så det er ikke så farlig.i denne tabellen. F4, og i stedet for å hardkode nummer fire, gjør de en MATCH og MATCH kommer til å bli låst til prisen. Så F4, to ganger for å sette $ før 1, og det kommer til å se gjennom den første raden i tabellen. Ups, F4 to ganger, komma, savnet kommaet. OK, trykk F4 her komma 0 for en nøyaktig samsvar med kampen, og deretter faller komma for en nøyaktig samsvar med VLOOKUP. Ja og hei dette fungerer bra, og her har jeg bare seks av dem, så det er ikke så farlig.i denne tabellen. F4, og i stedet for å hardkode nummer fire, gjør de en MATCH og MATCH kommer til å bli låst til prisen. Så F4, to ganger for å sette $ før 1, og det kommer til å se gjennom den første raden i tabellen. Ups, F4 to ganger, komma, savnet kommaet. OK, trykk F4 her komma 0 for en nøyaktig samsvar med kampen, og deretter faller komma for en nøyaktig samsvar med VLOOKUP. Ja og hei dette fungerer bra, og her har jeg bare seks av dem, så det er ikke så farlig.OK, trykk F4 her komma 0 for en nøyaktig samsvar med kampen, og deretter faller komma for en nøyaktig samsvar med VLOOKUP. Ja og hei dette fungerer bra, og her har jeg bare seks av dem, så det er ikke så farlig.OK, trykk F4 her komma 0 for en nøyaktig samsvar med kampen, og deretter faller komma for en nøyaktig samsvar med VLOOKUP. Ja og hei dette fungerer bra, og her har jeg bare seks av dem, så det er ikke så farlig.
Se om jeg setter inn en ny, den vil automatisk justere seg og fortsette å få prisen, men bare forestill deg om du hadde tusen VLOOKUP, og hver eneste VLOOKUP kommer til å gjøre om den kampen for å finne ut at prisene i femte kolonne eller fjerde kolonne. Det er forferdelig. Tabeller løser ganske enkelt dette problemet. Så her er mitt VLOOKUP-bord, det være lenge før jeg gjør noe, jeg skal gå hit og CTRL T for å gjøre det til et ekte bord. De skal kalle det tabell 1, men jeg skal kalle det ProductTable, alt ett ord, ingen mellomrom: ProductTable. Så nå har den et navn. OK, så nå har vi en tabell som heter ProductTable. Så kommer vi hit og sier at vi skal gjøre = INDEKS for disse prisene. Hvilken pris ønsker vi? Vi vil ha resultatet fra kampen fra A104 til disse elementene. Nøyaktig samsvar, lukk parentes for INDEX.Dette gjør bare en enkelt kamp. Det gjør ikke en kamp og en VLOOKUP. Slags, vil være mye, mye raskere. Kopier det ned. OK, og senere, hvis vi setter inn størrelsen, så sett inn kolonne, fortsetter alt å fungere fordi den leter etter kolonnen kalt Pris, og la oss si at hvis vi endrer dette til listepris, blir den formelen omskrevet. Riktig, så mye, mye tryggere, tryggere vei å gå.
Greit, så mange kule triks i bordene. Sjekk ut denne boka fra Kevin Jones og Zach Barresse på Excel Tables. Alle slags triks der inne og alt det vi podcaster i august og september er i denne fullpakket boken. Pluss mye moro. Excel vitser. Excel-cocktailer. Excel tweets. Excel-opplevelser. Stappfull i full farge. Sjekk den, kjøp denne boka. Jeg vil virkelig sette pris på det.
Greit dagens episode. VLOOKUP er fantastisk, og det er min favorittfunksjon, men det er VLOOKUP-hatere der ute som klager over at det er skjørt på grunn av det tredje argumentet. Hvis formen på tabellen VLOOKUP-tabellen endres, vil svarene endres. En løsning er å erstatte det tredje argumentet med en MATCH, men jammen, forestill deg å gjøre en MATCH i tusen rader med VLOOKUP. Så gjør VLOOKUP til et bord før du gjør VLOOKUP. Strukturtabellreferansene håndteres hvis tabellformen endres. I tillegg gjør du ikke en VLOOKUP og en kamp. Bare en enkelt kamp sammen med en INDEX og INDEX er lynrask, lynrask.
Takk til Peter Robert for dette tipset og takk for at du kom innom. Vi sees neste gang, for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2003.xlsx
