Sådan foretages en beregning (såsom VLOOKUP) for hvert element, der er blevet Alt + indtastet i en celle.
Se video
- En seer downloader data fra et system, hvor hvert element er adskilt af Alt + Enter
- Bill: Hvorfor gør du det? Seer: Det er sådan, jeg arver dataene. Jeg vil holde det sådan.
- Bill: Hvad vil du gøre med de 40% af værdierne, der ikke er i tabellen? Seer: Intet svar
- Bill: Der er en kompliceret måde at løse dette på, hvis du har de nyeste Power Query-værktøjer.
- I stedet for en VBA-makro til at løse det - makroen skal fungere helt tilbage til Excel 2007
- I stedet for at lave VLOOKUP skal du lave en række Find & Erstat med VBA
Videoudskrift
Lær Excel fra, Podcast Episode 2150: VLOOKUP Hver Alt + indtastet værdi i hver celle.
Hej. Velkommen tilbage til netcast. Jeg er Bill Jelen. I dag er et af de mere bizarre spørgsmål. Nogen sagde, hej, jeg vil lave en VLOOKUP for hver værdi i cellen, og da jeg åbnede Excel-filen, er dataene ALT + indtastet. Så der er 4 elementer i denne rækkefølge, og de er alle adskilt af ALT + ENTER, og derefter kun 2 her og 6 her og så videre.
Jeg gik tilbage til den person, der sendte dette ind. Jeg kunne godt lide, det er en rigtig dårlig måde at gemme disse data på. Hvorfor gør du det her? Og han var ligesom, jeg er som om jeg ikke gør det. Sådan downloades dataene. Jeg sagde, er det okay, hvis jeg deler det op i separate rækker? Nej, du skal holde det sådan.
I orden. Så der er ingen god måde at lave en VLOOKUP for hver enkelt vare, og i morgen på morgendagens episode, 2151, vil jeg vise dig, hvordan vi kan bruge en helt ny funktion i Power Query til at gøre dette, men du ville have at have Office 365 til at have det.
Så i dag vil jeg bruge en metode, der kommer helt tilbage, og hvad jeg har gjort her er, at jeg har oprettet et nyt regneark med LOOKUPTABLE, så disse er emnerne. Jeg bemærkede også, at der er en hel masse ting, ca. 40% af tingene her er ikke i LOOKUPTABLE. Jeg sagde, hvad vil du gøre der, og intet svar på dette spørgsmål, så jeg vil bare lade dem være, som de er, hvis jeg ikke finder en match.
Okay, så hvad jeg har her er, at jeg har et ark, der hedder LOOKUPTABLE, og du vil se, at min fil lige nu er gemt som xlsx, og jeg vil bruge en VBA-makro. For at kunne bruge en VBA-makro kan du ikke have den som xlsx. Det er imod reglerne. Så du skal GEM AS og gemme dette er xlsm. FIL, GEM SOM, og skift den fra WORKBOOK til enten en MAKRO-AKTIVERET WORKBOOK XLSM eller en BINÆR WORKBOOK - en af dem fungerer - okay, og klik på GEM.
Okay, så nu har vi lov til at køre makroer. ALT + F11 for at komme til makrooptageren. Du starter med denne store grå skærm. INDSÆT, MODUL, og der er vores modul, og her er koden. Så jeg kaldte det ReplaceInPlace, og jeg definerer et regneark. Det er LookupTable. Du ville lægge dit rigtige opslagstabel regnearknavn der, og så starter min opslagstabel i kolonne A, som er kolonne 1. Så jeg går til den sidste række i kolonne 1, tryk på END-tasten og OP-pilen, eller selvfølgelig ville CONTROL + UP-pilen gøre det samme, finde ud af, hvilken række det er, og så går vi fra hver række fra 2 til FinalRow. Hvorfor 2? Nå, fordi overskrifterne er i række 1. Så jeg vil erstatte, startende ved række 2 helt ned til sidste række, og så er FromValue for hver række fra 2 til FinalRow hvad 's i kolonne A og ToValue er hvad der er i kolonne B.
Hvis dine data af en eller anden grund var i J og K, ville denne J være den 10. kolonne, så du satte en 10 der, og K ville være den 11. kolonne, og derefter, i markeringen, skal vi erstatte FromValue til ToValue. Dette er virkelig vigtigt her. xlPart, xlPart - og det er en L, ikke et nummer 1 - xlPart, der siger, at det vil give os mulighed for at erstatte en del af cellen, fordi disse delnumre alle er adskilt af et linefeed-tegn. Selvom du ikke kan se det, er det der. Så det skulle give os mulighed for ikke ved et uheld at opdatere den forkerte ting, og derefter xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
I orden. Så dette er vores lille makro her. Lad os prøve det. Vi tager disse data, og jeg vil ikke ødelægge noget, så jeg skal bare tage de originale data og kopiere dem til højre. I orden. Så vi har vores valg der. Faktisk skal jeg starte fra dette punkt. CONTROL + BACKSPACE, og derefter ALT + F8 for at få en liste over alle makroer. Der er vores REPLACEINPLACE. Jeg klikker på KØR, og overalt, hvor den fandt et element i LOOKUPTABLE, erstattede det varenummeret med varen og tilsyneladende lavede en VLOOKUP, selvom vi slet ikke løser det med en VLOOKUP.
I orden. Så hej, den helt nye bog, der kom ud - Power Excel With, 2017 Edition, 617 Excel Mysteries Solved - alle mulige gode nye tip derinde.
Dagens wrap-up: seer downloader data fra et system, hvor hvert emne er adskilt af en ALT + ENTER, og derefter skal foretage en VLOOKUP på hvert emne, og ved du, hvorfor gør jeg det; så, sagde personen, jeg gør det ikke, men jeg er nødt til at holde det på denne måde; og så er 40% af værdierne ikke i tabellen, ja, intet svar; så jeg antager, at de vil føje disse ting til bordet; nu i morgen skal vi tale om, hvordan man løser dette med Power Query, men i dag vil denne makro arbejde helt tilbage i alle Windows-versioner af Excel, i det mindste tilbage til Excel 2007; så i stedet for en VLOOKUP, bare en række find og erstat med VBA.
Nå, hej. Jeg vil gerne takke dig, fordi du kom forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2150.xlsm
