Hur man gör en beräkning (till exempel VLOOKUP) för varje objekt som har Alt + skrivits in i en cell.
Kolla på video
- En tittare laddar ner data från ett system där varje objekt separeras av Alt + Enter
- Bill: Varför gör du det här? Betraktare: Det är så jag ärver data. Jag vill hålla det så.
- Bill: Vad vill du göra med de 40% av värdena som inte finns i tabellen? Betraktare: Inget svar
- Bill: Det finns ett komplicerat sätt att lösa detta om du har de senaste Power Query-verktygen.
- Istället en VBA-makro för att lösa det - makrot ska fungera helt tillbaka till Excel 2007
- Istället för att göra VLOOKUP, gör en serie Sök och ersätt med VBA
Videoutskrift
Lär dig Excel från, Podcast avsnitt 2150: VLOOKUP Varje Alt + inmatat värde i varje cell.
Hallå. Välkommen tillbaka till netcast. Jag heter Bill Jelen. Idag en av de mer bisarra frågorna. Någon sa, hej, jag vill göra en VLOOKUP för varje värde i cellen, och när jag öppnade Excel-filen har data ALT + matats in. Så det finns fyra artiklar i den här ordningen och de är alla åtskilda av ALT + ENTER, och sedan bara 2 här och 6 här och så vidare.
Jag gick tillbaka till personen som skickade in det. Jag var som, ja, det här är ett riktigt dåligt sätt att lagra denna information. Varför gör du detta? Och han var som, jag är som om jag inte gör det. Det här är hur data laddas ner. Jag sa, är det okej om jag delar upp det i separata rader? Nej, du måste behålla det här.
OK. Så det finns inget bra sätt att göra en VLOOKUP för varje enskild artikel, och imorgon, i morgondagens avsnitt, 2151, ska jag visa dig hur vi kan använda en helt ny funktion i Power Query för att göra detta men du skulle ha att ha Office 365 för att ha det.
Så i dag vill jag använda en metod som kommer att gå hela vägen tillbaka, och det jag har gjort här är att jag har skapat ett nytt kalkylblad med LOOKUPTABLE, så det här är artiklarna. Jag märkte också att det finns en hel massa saker, cirka 40% av sakerna här, finns inte i LOOKUPTABLE. Jag sa, vad vill du göra där, och inget svar på den frågan, så jag ska bara lämna dem som de är om jag inte hittar en matchning.
Okej, så vad jag har här är att jag har ett ark som heter LOOKUPTABLE och du ser att min fil just nu är lagrad som xlsx och jag ska använda ett VBA-makro. För att kunna använda ett VBA-makro kan du inte ha det som xlsx. Det strider mot reglerna. Så du måste SPARA AS och spara detta är xlsm. FILER, SPARA AS och ändra den från WORKBOOK till antingen en MACRO-AKTIVAD WORKBOOK XLSM eller en BINÄR WORKBOOK - någon av dem fungerar - okej och klicka på SPARA.
Okej, så nu får vi köra makron. ALT + F11 för att komma till makroinspelaren. Du börjar med den här stora grå skärmen. INSERT, MODULE, och det finns vår modul, och här är koden. Så jag kallade det ReplaceInPlace och jag definierar ett kalkylblad. Det är LookupTable. Du skulle placera ditt riktiga uppslagstabellnamn där, och sedan börjar min uppslagstabell i kolumn A, vilket är kolumn 1. Så jag går till den sista raden i kolumn 1, trycker på END-tangenten och UPP-pilen, eller naturligtvis skulle CONTROL + UP-pilen göra samma sak, ta reda på vilken rad det är, och sedan kommer vi att gå från varje rad från 2 till FinalRow. Varför 2? Tja, eftersom rubrikerna är i rad 1. Så jag vill byta ut, från rad 2 ända ner till sista raden, och så, för varje rad från 2 till FinalRow, är FromValue vad 's i kolumn A och ToValue är vad som finns i kolumn B.
Om dina data av någon anledning var i J och K, skulle denna J vara den 10: e kolumnen så att du sätter en 10 där, och K skulle vara den 11: e kolumnen, och sedan, i valet, kommer vi att ersätta FromValue till ToValue. Det här är verkligen viktigt här. xlPart, xlPart - och det är en L, inte ett nummer 1 - xlPart som säger att gör det möjligt för oss att ersätta en del av cellen eftersom alla delnumren är åtskilda av ett linjematstecken. Även om du inte kan se det är det där. Så det borde tillåta oss att inte av misstag uppdatera fel sak, och sedan xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Nästa jag.
OK. Så det här är vårt lilla makro här. Vi prövar. Vi tar de här uppgifterna och jag vill inte förstöra någonting så jag tar bara originaldata och kopierar dem till höger. OK. Så vi har vårt urval där. Egentligen kommer jag att börja från denna punkt. CONTROL + BACKSPACE, och sedan ALT + F8 för att få en lista över alla makron. Det finns vår REPLACEINPLACE. Jag klickar på KÖR, och överallt där den hittade ett objekt i LOOKUPTABLE ersatte det artikelnumret med artikeln och verkade göra en VLOOKUP, även om vi inte löser det med en VLOOKUP alls.
OK. Så hej, den helt nya boken som kom ut - Power Excel With, 2017 Edition, 617 Excel Mysteries Solved - alla typer av fantastiska nya tips där inne.
Dagens wrap-up: tittaren laddar ner data från ett system där varje objekt separeras av en ALT + ENTER och sedan behöver göra en VLOOKUP vid varje objekt, och du vet, varför gör jag det här; så, sa personen, jag gör det inte men jag måste behålla det på detta sätt; och då finns inte 40% av värdena i tabellen, ja, inget svar; så jag antar att de kommer att lägga till dessa saker i tabellen; nu imorgon ska vi prata om hur man löser detta med Power Query, men i dag kommer detta makro att fungera hela vägen tillbaka i alla Windows-versioner av Excel, åtminstone tillbaka till Excel 2007; så istället för en VLOOKUP, bara en serie hitta och ersätt med VBA.
Men Hej. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2150.xlsm
