Hvordan gjøre en beregning (for eksempel VLOOKUP) for hvert element som har blitt Alt + lagt inn i en celle.
Se på video
- En seer laster ned data fra et system der hvert element er skilt av Alt + Enter
- Bill: Hvorfor gjør du dette? Seer: Det er slik jeg arver dataene. Jeg vil beholde det slik.
- Bill: Hva vil du gjøre med 40% av verdiene som ikke er i tabellen? Seer: Ikke noe svar
- Bill: Det er en komplisert måte å løse dette på hvis du har de nyeste Power Query-verktøyene.
- I stedet en VBA-makro for å løse det - makroen skal fungere helt tilbake til Excel 2007
- I stedet for å gjøre VLOOKUP, gjør en serie Find & Replace med VBA
Videoutskrift
Lær Excel fra, Podcast Episode 2150: VLOOKUP Each Alt + Entered Value in Each Cell.
Hei. Velkommen tilbake til netcast. Jeg er Bill Jelen. I dag, et av de mer bisarre spørsmålene. Noen sa, hei, jeg vil gjøre en VLOOKUP for hver verdi i cellen, og da jeg åpnet Excel-filen, har data blitt ALT + lagt inn. Så det er 4 elementer i denne rekkefølgen, og de er alle atskilt med ALT + ENTER, og bare to her og 6 her og så videre.
Jeg gikk tilbake til personen som sendte dette inn. Jeg var som, vel, dette er en veldig dårlig måte å lagre disse dataene på. Hvorfor gjør du dette? Og han var som, jeg er som om jeg ikke gjør det. Dette er måten dataene lastes ned. Jeg sa, er det greit hvis jeg deler den ut i separate rader? Nei, du må holde det slik.
Ok. Så det er ingen god måte å gjøre en VLOOKUP for hvert enkelt element, og i morgen, på morgendagens episode, 2151, skal jeg vise deg hvordan vi kan bruke en helt ny funksjon i Power Query til å gjøre dette, men du ville ha å ha Office 365 for å ha det.
Så i dag vil jeg bruke en metode som kommer til å gå helt tilbake, og det jeg har gjort her er at jeg har laget et nytt regneark med LOOKUPTABLE, så dette er elementene. Jeg la også merke til at det er en hel haug med ting, omtrent 40% av tingene her, er ikke i LOOKUPTABLE. Jeg sa, hva vil du gjøre der, og ikke noe svar på det spørsmålet, så jeg skal bare la dem være som de er hvis jeg ikke finner en kamp.
Ok, så det jeg har her er at jeg har et ark som heter LOOKUPTABLE, og du vil se at filen min akkurat nå er lagret som xlsx, og jeg skal bruke en VBA-makro. For å bruke en VBA-makro, kan du ikke ha den som xlsx. Det er mot reglene. Så du må LAGRE SÅ og lagre dette er xlsm. FIL, LAGRE AS, og endre den fra WORKBOOK til enten en MAKROAKTIVERT WORKBOOK XLSM, eller en BINÆR WORKBOOK - en av dem vil fungere - ok, og klikk på LAGRE.
Ok, så, nå har vi lov til å kjøre makroer. ALT + F11 for å komme til makroopptakeren. Du starter med denne store grå skjermen. INSERT, MODULE, og det er vår modul, og her er koden. Så jeg kalte det ReplaceInPlace og jeg definerer ett regneark. Det er LookupTable. Du vil legge navnet på det virkelige oppslagstabellets regneark der, og så begynner oppslagstabellen min i kolonne A, som er kolonne 1. Så jeg går til den siste raden i kolonne 1, trykker END-tasten og UP-pilen, eller , selvfølgelig, ville CONTROL + UP-pilen gjøre det samme, finne ut hvilken rad det er, og så skal vi gå fra hver rad fra 2 til FinalRow. Hvorfor 2? Vel, fordi overskriftene er i rad 1. Så jeg vil erstatte, fra rad 2 helt ned til siste rad, og så, for hver rad fra 2 til FinalRow, er FromValue det 's i kolonne A og ToValue er det som er i kolonne B.
Hvis dataene dine av en eller annen grunn var i J og K, ville denne J være den 10. kolonnen, så du satte en 10 der, og K ville være den 11. kolonnen, og deretter, i Valget, skal vi erstatte FromValue til ToValue. Dette er veldig viktig her. xlPart, xlPart - og det er en L, ikke et nummer 1 - xlPart som sier at vil tillate oss å erstatte en del av cellen fordi disse delenumrene er atskilt med et linjematingstegn. Selv om du ikke kan se det, er det der. Så det burde tillate oss å ikke ved et uhell oppdatere feil ting, og deretter xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Neste jeg.
Ok. Så dette er vår lille makro her. La oss prøve det. Vi tar disse dataene, og jeg vil ikke ødelegge noe, så jeg skal bare ta de originale dataene og kopiere dem til høyre. Ok. Så vi har vårt utvalg der. Egentlig skal jeg starte fra dette punktet. CONTROL + BACKSPACE, og deretter ALT + F8 for å få en liste over alle makroene. Det er REPLACEINPLACE. Jeg klikker RUN, og overalt hvor den fant et element i LOOKUPTABLE, erstattet det varenummeret med varen, tilsynelatende gjør det en VLOOKUP, selv om vi ikke løser det med en VLOOKUP i det hele tatt.
Ok. Så hei, den splitter nye boka som kom ut - Power Excel With, 2017 Edition, 617 Excel Mysteries Solved - alle slags flotte nye tips der inne.
Dagens wrap-up: seer laster ned data fra et system der hvert element er atskilt med en ALT + ENTER, og deretter trenger å gjøre en VLOOKUP på hvert element, og du vet, hvorfor gjør jeg dette; så, sa personen, jeg gjør det ikke, men jeg må holde det slik; og da er 40% av verdiene ikke i tabellen, vel, ikke noe svar; så jeg antar at de kommer til å legge disse elementene til bordet; nå, i morgen, skal vi snakke om hvordan vi kan løse dette med Power Query, men i dag vil denne makroen fungere helt tilbake i alle Windows-versjoner av Excel, i det minste tilbake til Excel 2007; så, i stedet for en VLOOKUP, bare en serie finne og erstatte med VBA.
Vel hei. 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: Podcast2150.xlsm
