Ich habe Excel immer für schnelle Berechnungen und das Erstellen einfacher Tabellen verwendet. Abgesehen von gängigen Formeln und grundlegenden Datenmanipulationstechniken hatte ich jedoch nie das Bedürfnis, zusätzliche Excel-Funktionen zu erlernen – bis meine Projekte komplexer wurden.
Schnelle Links
Das Problem, das mich endlich aufmerksam machte
Aufgrund verschiedener Marktfaktoren und Einfuhrzölle ist der Kauf von Computerkomponenten in meiner Gegend oft teurer als in den USA. Ich wollte wissen, wie viel mehr ich für die gleichen Komponenten bezahle und ob es besser ist, direkt bei Amazon oder Newegg zu bestellen, anstatt bei lokalen Händlern. Also sammelte ich über mehrere Monate Preisdaten für wichtige Computerkomponenten (CPUs, GPUs und RAM), die lokale Geschäfte typischerweise importieren. Ein einfaches Tracking-Projekt, oder? Falsch.
Ich hatte schnell ein komplettes Datenchaos. Jeder Händler exportierte seine Informationen mit unterschiedlichen Formatierungskonventionen, was es nahezu unmöglich machte, die Dateien zusammenzuführen. Amazon verwendete die Daten im Format MM/TT/JJJJ, Newegg im Format JJJJMMTT und Shopee (mein lokaler Händler) im Format TT-MM-JJJJ.
Doch damit nicht genug der Inkonsistenzen. Die Spaltennamen variierten stark. Newegg bezeichnete die Preise als „retail_price“, Amazon hingegen als „unit_price_usd“ und Shopee als „price_php“. Auch die Preisformatierung war problematisch: In einigen Dateien wurde „₱18,600“ inklusive Währungssymbolen angezeigt, in anderen hingegen normale Zahlen wie „320“. Selbst die Markennamen waren inkonsistent und erschienen in verschiedenen Dateien für denselben Hersteller als „Gigabyte“, „GIGABYTE INC.“ oder „Gigabyte Tech“.
Das manuelle Bereinigen und Zusammenführen dieser Daten hat mich bereits Stunden gekostet. Ich musste zwischen Dateien kopieren und einfügen, inkonsistente Werte suchen und ersetzen und leere Zeilen einzeln löschen. Die Umrechnung von PHP in USD für Preisvergleiche bedeutete, dass ich ständig auf einem anderen Bildschirm nach Wechselkursen suchen musste. Insgesamt war die Arbeit mühsam, fehleranfällig und hätte mich fast zum Aufgeben gebracht.
Da kam mir endlich die Idee, eine der Funktionen zu nutzen, von denen Excel-Enthusiasten immer sprechen – Power Query. Dort Viele weitere leistungsstarke Funktionen von ExcelAber ich hatte gehört, dass Power Query das perfekte Tool für mein spezielles Problem sei. Nachdem ich mir einige YouTube-Tutorials angesehen hatte, wurde mir sofort klar, wie viel Zeit ich sparen konnte, wenn ich den Power Query-Editor zum Bereinigen all meiner unübersichtlichen Daten aus dem Internet verwendete. Mit Power Query kann ich jetzt problemlos Daten aus verschiedenen Quellen importieren, in ein standardisiertes Format konvertieren und effizient analysieren. Das spart mir wertvolle Zeit und Mühe bei meinen Projekten zur Preisanalyse von Computerkomponenten.
Wie verwende ich Power Query, um unstrukturierte Daten zu bereinigen?
Nach einiger Zeit habe ich mich für einen einfachen, schrittweisen Prozess im Power Query-Editor entschieden. So habe ich meine unübersichtlichen CSV-Exporte bereinigt und in eine konsistente, übersichtliche Tabelle umgewandelt.
Zuerst habe ich meine Daten in den Power Query-Editor importiert, indem ich eine leere Arbeitsmappe geöffnet und auf Datum Wählen Sie im Menüband Aus Text/CSV.Dann habe ich meine CSV-Datei ausgewählt und geklickt Erfolgsfaktor So öffnen Sie es mit dem Power Query-Editor.
Ich begann mit der Korrektur der Datumsspalte. Da ich Daten aus zwei Quellen mit einem Zeitunterschied von 12 Stunden sammelte, musste ich die Daten vereinheitlichen. Es stellte sich als ganz einfach heraus. Ich definierte die Spalte Datum, klicken Sie mit der rechten Maustaste, um das Kontextmenü zu öffnen, und wählen Sie Typ ändern > Gebietsschema verwendenIm Popup-Menü stelle ich den Typ auf Datum und identifiziert Englisch (USA) Um eine konsistente Formatierung sicherzustellen, erkennt Power Query automatisch verschiedene Formate wie MM/TT/JJJJ, JJJJ/MM/TT und Variablen, die Symbole wie TT-MM-JJ verwenden, und vereint sie dann alle in einem einzigen Datumsformat.
Nachdem ich das Datumsformat korrigiert hatte, musste ich nur noch die Spalte bereinigen. هناك Verschiedene Möglichkeiten zum Bereinigen einer Excel-TabelleDa es sich bei allen Fehlern jedoch um fehlerhafte Einträge handelte, die von meinem Scraper generiert wurden, habe ich mich einfach für die Verwendung eines Filters entschieden. Fehler entfernen Um diese Einträge zu entfernen. Durch diesen Schritt wurden Nullwerte und alle verbleibenden problematischen Daten entfernt, die nicht ordnungsgemäß aufgezeichnet wurden, sodass ich in allen meinen Dateien saubere, konsistente Daten habe.
Als nächstes habe ich das Markennamen-Chaos mit einer Funktion behoben. Werte ersetzenWie zuvor habe ich die Zielspalte ausgewählt, dann mit der rechten Maustaste geklickt, um das Kontextmenü zu öffnen und ausgewählt Werte ersetzenGeben Sie im Popup-Fenster den inkonsistenten Wert in das Feld ein. Zu suchender Wert und mein Standardwert im Feld Feld „Ersetzen durch“.
Ich habe dies noch zweimal wiederholt und schließlich alle Einträge „Gigabyte“ und „GIGABYTE Inc.“ in ein einziges, konsistentes „GIGABYTE“ für alle meine Dateien umgewandelt. Dasselbe habe ich mit AMD gemacht, und jetzt verwendet die gesamte Spalte „Marke“ für GPUs Standardmarkennamen.
Power Query: Wie es mir viele Arbeitsstunden erspart hat
Einer der Gründe, warum ich Power Query vermieden habe, war, dass ich dachte, es wäre eine weitere komplexe Funktion, deren Erlernen lange dauern würde. Aber es stellte sich als viel einfacher heraus als erwartet. Anstatt endlose Suchen-und-Ersetzen-Befehle auszuführen, kann ich mit Power Query Daten aus meinen Datenerfassungstools schnell und automatisch bereinigen.
Was mich an Power Query am meisten überrascht hat, ist, dass jeder von mir ausgeführte Befehl aufgezeichnet und immer wieder wiederholt werden konnte. Damit erhalten Sie im Wesentlichen ein automatisiertes Bereinigungsskript, das unordentliche CSV-Dateien in saubere, geordnete Tabellen umwandeln kann – perfekt, wenn Sie an einem Erstellen Sie benutzerdefinierte Datensätze mithilfe von Web Scraping, da diese Tools oft unsaubere Daten produzieren.
Für alle, die mit wiederkehrenden Datenbereinigungen, inkonsistenten Formaten oder mehreren Datenquellen zu kämpfen haben, verwandelt Power Query diese Belastungen in einen einfachen, automatisierten Prozess. Anstatt jede Woche Stunden mit manuellen Korrekturen zu verbringen, können Sie einfach auf „Aktualisieren“ klicken und mit der Analyse beginnen. Diese Excel-Funktion hätte ich mir schon längst gewünscht. Wer einmal die Leistungsfähigkeit eines automatisierten, wiederholbaren Bereinigungsskripts erlebt hat, wird es nicht mehr missen wollen. Power Query ist ein leistungsstarkes Tool, das Zeit und Aufwand bei der Datenverarbeitung spart und fortschrittliche Lösungen für effektive Datenbereinigung und -transformation bietet.
