Hoe u een berekening uitvoert (zoals VERT.ZOEKEN) voor elk item dat in een cel is Alt + ingevoerd.
Bekijk video
- Een kijker downloadt gegevens van een systeem waarbij elk item wordt gescheiden door Alt + Enter
- Bill: Waarom doe je dit? Kijker: zo erf ik de gegevens. Ik wil het zo houden.
- Bill: Wat wil je doen met de 40% waarden die niet in de tabel staan? Kijker: geen antwoord
- Bill: er is een gecompliceerde manier om dit op te lossen als u over de nieuwste Power Query-tools beschikt.
- In plaats daarvan een VBA-macro om het op te lossen - de macro zou helemaal terug moeten werken naar Excel 2007
- In plaats van VERT.ZOEKEN, voert u een reeks Zoeken en vervangen uit met VBA
Videotranscriptie
Leer Excel van, Podcast-aflevering 2150: VERT.ZOEKEN Elke Alt + ingevoerde waarde in elke cel.
Hallo. Welkom terug bij de netcast. Ik ben Bill Jelen. Vandaag een van de meer bizarre vragen. Iemand zei, hé, ik wil een VERT.ZOEKEN doen voor elke waarde in de cel, en toen ik het Excel-bestand opende, waren de gegevens ALT + ingevoerd. Er zijn dus 4 items in deze volgorde en ze zijn allemaal gescheiden door ALT + ENTER, en dan slechts 2 hier en 6 hier, enzovoort.
Ik ging terug naar de persoon die dit opstuurde. Ik dacht: dit is echt een slechte manier om deze gegevens op te slaan. Waarom doe je dit? En hij had zoiets van, ik ben alsof ik het niet doe. Dit is de manier waarop de gegevens worden gedownload. Ik zei: is het oké als ik het opsplit in afzonderlijke rijen? Nee, u moet het zo houden.
Oké. Er is dus geen goede manier om een VERT.ZOEKEN uit te voeren voor elk afzonderlijk item, en morgen, in aflevering van morgen, 2151, laat ik je zien hoe we een gloednieuwe functie in Power Query kunnen gebruiken om dit te doen, maar je zou moeten om Office 365 te hebben om dat te hebben.
Dus vandaag wil ik een methode gebruiken die helemaal teruggaat, en wat ik hier heb gedaan, is dat ik een nieuw werkblad heb gemaakt met de OPZOEKBAAR, dus dit zijn de items. Ik heb ook gemerkt dat er een heleboel dingen zijn, ongeveer 40% van de dingen hier, niet in de OPZOEKBARE. Ik zei: wat wil je daar doen, en geen antwoord op die vraag, dus ik laat ze gewoon zoals ze zijn als ik geen match vind.
Oké, dus wat ik hier heb, is dat ik een blad heb met de naam LOOKUPTABLE en je zult zien dat mijn bestand nu is opgeslagen als xlsx en dat ik een VBA-macro ga gebruiken. Om een VBA-macro te gebruiken, kunt u deze niet als xlsx hebben. Het is tegen de regels. Dus je moet OPSLAAN ALS en opslaan dat dit xlsm is. BESTAND, OPSLAAN ALS, en verander het van WERKBOEK in ofwel een MACRO-INGESCHAKELD WERKBOEK XLSM, of een BINAIR WERKBOEK - een van beide zal werken - goed, en klik op OPSLAAN.
Oké, dus nu mogen we macro's uitvoeren. ALT + F11 om naar de macrorecorder te gaan. Je begint met dit grote grijze scherm. INSERT, MODULE, en daar is onze module, en hier is de code. Dus ik noemde het ReplaceInPlace en ik definieer één werkblad. Dat is de LookupTable. Je zou de naam van je echte opzoektabelwerkblad daar zetten, en dan begint mijn opzoektabel in kolom A, dat is kolom 1. Dus ik ga naar de allerlaatste rij in kolom 1, druk op de END-toets en de pijl OMHOOG, of Natuurlijk zou CONTROL + UP-pijl hetzelfde doen, uitvinden welke rij het is, en dan gaan we van elke rij van 2 naar FinalRow. Waarom 2? Nou, omdat de koppen in rij 1 staan. Dus ik wil deze vervangen, beginnend bij rij 2 helemaal tot aan de laatste rij, en dus is voor elke rij van 2 tot FinalRow de FromValue wat 's in kolom A en de ToValue is wat in kolom B staat.
Als je gegevens om de een of andere reden in J en K stonden, dan zou deze J de 10e kolom zijn, dus je zet daar een 10, en K zou de 11e kolom zijn, en dan, in de Selectie, gaan we vervangen de FromValue naar de ToValue. Dit is hier echt belangrijk. xlPart, xlPart - en dat is een L, geen nummer 1 - xlPart dat zegt dat we een deel van de cel kunnen vervangen, omdat die onderdeelnummers allemaal gescheiden zijn door een lijninvoer. Ook al kun je het niet zien, het is er. Dus dat zou ons in staat moeten stellen om niet per ongeluk het verkeerde te updaten, en dan xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Oké. Dit is dus onze kleine macro hier. Laten we het proberen. We nemen deze gegevens, en ik wil niets vernietigen, dus ik neem gewoon de originele gegevens en kopieer deze naar rechts. Oké. Dus we hebben onze selectie daar. Eigenlijk ga ik vanaf dit punt beginnen. CONTROL + BACKSPACE en vervolgens ALT + F8 om een lijst met alle macro's te krijgen. Daar is onze REPLACEINPLACE. Ik klik op UITVOEREN, en overal waar het een item in de LOOKUPTABLE vond, verving het dat itemnummer door het item, schijnbaar bezig met VERT.ZOEKEN, hoewel we het helemaal niet oplossen met VERT.ZOEKEN.
Oké. Dus, hé, het gloednieuwe boek dat uitkwam - Power Excel With, de editie 2017, 617 Excel Mysteries Solved - bevat allerlei geweldige nieuwe tips.
Afronding van vandaag: de kijker downloadt gegevens van een systeem waarin elk item wordt gescheiden door een ALT + ENTER, en moet vervolgens bij elk item een VERT.ZOEKEN uitvoeren, en, weet je, waarom doe ik dit; dus, zei de persoon, ik doe het niet, maar ik moet het zo houden; en dan staat 40% van de waarden niet in de tabel, nou ja, geen antwoord; dus ik denk dat ze die items aan de tafel gaan toevoegen; nu, morgen gaan we praten over hoe we dit kunnen oplossen met Power Query, maar vandaag werkt deze macro helemaal terug in alle Windows-versies van Excel, in ieder geval teruggaand naar Excel 2007; dus, in plaats van een VERT.ZOEKEN, gewoon een reeks zoeken en vervangen door VBA.
Hallo daar. Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2150.xlsm
