So führen Sie eine Berechnung (z. B. VLOOKUP) für jedes Element durch, das in einer Zelle mit Alt + eingegeben wurde.
Schau Video
- Ein Viewer lädt Daten von einem System herunter, bei dem jedes Element durch Alt + Eingabetaste getrennt ist
- Bill: Warum machst du das? Viewer: So erbe ich die Daten. Ich möchte es so halten.
- Bill: Was möchten Sie mit den 40% der Werte tun, die nicht in der Tabelle enthalten sind? Zuschauer: Keine Antwort
- Bill: Es gibt eine komplizierte Möglichkeit, dies zu lösen, wenn Sie über die neuesten Power Query-Tools verfügen.
- Stattdessen ein VBA-Makro, um es zu lösen - das Makro sollte bis zu Excel 2007 zurück funktionieren
- Führen Sie anstelle von VLOOKUP eine Reihe von Suchen und Ersetzen mit VBA durch
Video-Transkript
Lernen Sie Excel aus, Podcast-Episode 2150: VLOOKUP Jeder Alt + Eingegebener Wert in jeder Zelle.
Hallo. Willkommen zurück im Netcast. Ich bin Bill Jelen. Heute eine der bizarreren Fragen. Jemand sagte, hey, ich möchte für jeden Wert in der Zelle einen VLOOKUP erstellen, und als ich die Excel-Datei öffnete, wurden die Daten mit ALT + Eingetragen. Es gibt also 4 Elemente in dieser Reihenfolge, die alle durch ALT + EINGABETASTE getrennt sind, und dann nur 2 hier und 6 hier und so weiter.
Ich ging zurück zu der Person, die dies eingeschickt hat. Ich dachte, dies ist eine wirklich schlechte Art, diese Daten zu speichern. Warum tust du das? Und er meinte, ich mache es nicht. Auf diese Weise werden die Daten heruntergeladen. Ich sagte, ist es in Ordnung, wenn ich es in separate Zeilen aufteile? Nein, du musst es so halten.
In Ordung. Es gibt also keine gute Möglichkeit, einen VLOOKUP für jedes einzelne Element zu erstellen, und morgen, in der morgigen Folge 2151, werde ich Ihnen zeigen, wie wir eine brandneue Funktion in Power Query verwenden können, um dies zu tun, aber Sie hätten es getan Office 365 zu haben, um das zu haben.
Daher möchte ich heute eine Methode verwenden, die bis in die Vergangenheit zurückreicht. Hier habe ich ein neues Arbeitsblatt mit LOOKUPTABLE erstellt. Dies sind also die Elemente. Mir ist auch aufgefallen, dass es eine ganze Reihe von Dingen gibt, etwa 40% der Dinge hier, die nicht in der LOOKUPTABLE enthalten sind. Ich sagte, was willst du dort machen und keine Antwort auf diese Frage, also werde ich sie einfach so lassen, wie sie sind, wenn ich keine Übereinstimmung finde.
Also gut, ich habe hier ein Blatt mit dem Namen LOOKUPTABLE und Sie werden sehen, dass meine Datei gerade als xlsx gespeichert ist und ich ein VBA-Makro verwenden werde. Um ein VBA-Makro zu verwenden, können Sie es nicht als xlsx verwenden. Es ist gegen die Regeln. Sie müssen also SPEICHERN ALS und speichern, dies ist xlsm. DATEI, SPEICHERN ALS und ändern Sie es von WORKBOOK in ein MACRO-AKTIVIERTES WORKBOOK XLSM oder ein BINARY WORKBOOK - eines davon funktioniert - in Ordnung, und klicken Sie auf SPEICHERN.
Okay, jetzt dürfen wir Makros ausführen. ALT + F11, um zum Makrorecorder zu gelangen. Sie beginnen mit diesem großen grauen Bildschirm. INSERT, MODULE und da ist unser Modul, und hier ist der Code. Also habe ich es ReplaceInPlace genannt und ein Arbeitsblatt definiert. Das ist die Nachschlagetabelle. Sie würden dort Ihren echten Arbeitsblattnamen für die Nachschlagetabelle einfügen, und dann beginnt meine Nachschlagetabelle in Spalte A, die Spalte 1 ist. Also gehe ich zur allerletzten Zeile in Spalte 1, drücke die END-Taste und den UP-Pfeil oder Natürlich würde der CONTROL + UP-Pfeil dasselbe tun, herausfinden, um welche Zeile es sich handelt, und dann werden wir von jeder Zeile von 2 zu FinalRow wechseln. Warum 2? Nun, weil sich die Überschriften in Zeile 1 befinden. Ich möchte also ersetzen, beginnend bei Zeile 2 bis zur letzten Zeile, und für jede Zeile von 2 bis FinalRow ist der FromValue das, was 's in Spalte A und der ToValue ist das, was in Spalte B steht.
Wenn Ihre Daten aus irgendeinem Grund in J und K wären, wäre dieses J die 10. Spalte, also setzen Sie dort eine 10, und K wäre die 11. Spalte, und dann werden wir in der Auswahl ersetzen der FromValue zum ToValue. Das ist hier wirklich wichtig. xlPart, xlPart - und das ist ein L, keine Zahl 1 - xlPart, das besagt, dass wir einen Teil der Zelle ersetzen können, da diese Teilenummern alle durch ein Zeilenvorschubzeichen getrennt sind. Auch wenn Sie es nicht sehen können, ist es da. Das sollte es uns also ermöglichen, nicht versehentlich das Falsche zu aktualisieren und dann xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
In Ordung. Das ist also unser kleines Makro hier. Lass es uns versuchen. Wir nehmen diese Daten und ich möchte nichts zerstören, also nehme ich einfach die Originaldaten und kopiere sie nach rechts. In Ordung. Wir haben dort also unsere Auswahl. Eigentlich werde ich von diesem Punkt aus beginnen. CONTROL + BACKSPACE und dann ALT + F8, um eine Liste aller Makros zu erhalten. Da ist unser REPLACEINPLACE. Ich klicke auf RUN und überall dort, wo ein Element in der LOOKUPTABLE gefunden wurde, wurde diese Artikelnummer durch das Element ersetzt, wobei anscheinend ein VLOOKUP ausgeführt wurde, obwohl wir es überhaupt nicht mit einem VLOOKUP lösen.
In Ordung. Also, hey, das brandneue Buch, das herauskam - Power Excel With, die Ausgabe 2017, 617 Excel Mysteries Solved - alle möglichen tollen neuen Tipps.
Heutige Zusammenfassung: Der Viewer lädt Daten von einem System herunter, bei dem jedes Element durch ALT + EINGABETASTE getrennt ist, und muss dann bei jedem Element einen VLOOKUP durchführen. Warum mache ich das? Also, sagte die Person, ich mache es nicht, aber ich muss es so halten. und dann sind 40% der Werte nicht in der Tabelle, na ja, keine Antwort; Ich denke, sie werden diese Gegenstände zur Tabelle hinzufügen. Jetzt, morgen, werden wir darüber sprechen, wie dies mit Power Query gelöst werden kann. Heute funktioniert dieses Makro jedoch in allen Windows-Versionen von Excel vollständig, zumindest in Excel 2007. Anstelle eines VLOOKUP nur eine Reihe von Suchen und Ersetzen durch VBA.
Nun, hey. Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2150.xlsm
