Excel 2016: Wie Sie mit dem Power Query Ordnung in Ihre Excel-Dateien bringen
Microsoft Excel ist für viele Bundesangestellte ein unverzichtbares Arbeitsinstrument. Oftmals bergen komplexere Analysen aber ungeahnte Komplikationen. Der «Eisbrecher» stellt Ihnen einige Kniffe vor, um in Zukunft noch schneller und besser mit Excel arbeiten zu können.
Datenabfragen leichtgemacht
Haben Sie genug von endlosem Copy-Pasten? Erscheinen beim Einsetzen der zwischengespeicherten Daten kryptische Werte? Müssen Sie diese Schritte jedes Mal durchführen, wenn neue Daten vorliegen? Mit Datenabfragen können Sie Ihren Arbeitsaufwand ganz einfach verringern, und zwar ohne lästige Makros. Vor allem wenn die Daten die gleiche Form behalten, erlaubt das sogenannte Power Query ein schnelles und intuitives Automatisieren von etlichen Arbeitsschritten.
In diesem Beispiel berechnen Sie die Ausgaben einer Reisegruppe und versuchen herauszufinden, wer wie viel einbezahlt hat, beziehungsweise den Anderen schuldet. Dazu speichern Sie Ihr Excel-File mit den Daten ab (siehe Bild 1), und öffnen ein neues Excel-Dokument.
Im Reiter «Daten» klicken Sie auf die Registerkarte «Neue Abfrage». Hier haben Sie verschiedene Abfragemöglichkeiten, in unserem Beispiel gehen Sie über «Aus Datei» auf «Aus Arbeitsmappe» (Bild 2).
Im neuen Fenster navigieren Sie nun zu der zuvor abgespeicherten Excel-Datei, die Ihre Daten enthält. Im «Navigator» angekommen, wählen Sie in der linken Spalte das richtige Arbeitsblatt und klicken dann rechts unten auf «Bearbeiten». Sie sind nun im Power-Query-Editor angekommen.
Der Editor ist ein sehr nützliches Tool. Sie können beispielsweise in der rechten Spalte einen Namen für die Abfrage festlegen. Im Beispiel verwenden Sie den Namen «Ausgaben». Noch wichtiger ist, dass Sie hier Arbeitsschritte festlegen können, die bei jeder Aktualisierung der Daten automatisch durchgeführt werden.
Im Beispiel berechnen Sie die individuellen Ausgaben während eines Teamausflugs. Zuerst sollten Sie die letzte Spalte «einbezahlt» vereinheitlichen. Excel speichert die Werte in dieser Spalte als Text ab, da es das Wort «nichts» erkannt hat. Zuerst ersetzen Sie «nichts» mit – Sie haben es geahnt – nichts. Dazu wählen Sie die Spalte aus und gehen dann über die Registerkarte «Transformieren» auf «Werte ersetzen» (Bild 3). Wenn Sie «Ersetzen durch» leer lassen, löscht Excel den betreffenden Zellenwert. Haben Sie alle problematischen Werte korrigiert, können Sie unter Datentyp noch «Dezimalzahl» auswählen. Die Spalte kann nun ohne Probleme in Zahlen konvertiert werden, da keine Wörter mehr gespeichert sind.
Damit die Lernenden nicht in den Ausgabenpott einzahlen müssen, kreieren Sie zum Schluss noch eine Spalte, die angibt, ob jemand aus der Liste noch nicht 18 Jahre alt ist. Dazu fügen Sie über «Spalte hinzufügen» eine «Bedingte Spalte» hinzu. Hier geben Sie die Altersgrenze ein und legen die neuen Werte fest, welche die neue Spalte haben wird (Bild 5).
Sind Sie mit den Manipulationen zufrieden, können Sie den Datensatz unter «Schliessen & Laden» in Ihrem neuen Excel-Blatt abrufen.
Im Beispiel sind nun neue Personen hinzugekommen und das Ausgangsdatenblatt liegt in aktualisierter Form vor.
In Ihrem neu erstellten Excel-File klicken Sie unter der Registerkarte «Daten» auf «Alle aktualisieren». Da Excel jeden Ihrer Berechnungsschritte automatisch abgespeichert hat, werden automatisch zuerst die Spalte «einbezahlt» bereinigt und dann die Spalte «Lernende?» neu berechnet. Somit können Sie mit einem Knopfdruck viele kleinere, idealerweise sich wiederholende, Arbeitsschritte automatisieren und haben Ordnung in Ihren Dateien geschaffen.
Fertig Buchstabensuppe – strukturierte Verweise
Ein angenehmer Nebeneffekt dieses Prozesses ist, dass mittels Power Query automatisch Tabellenformatierungen entstehen. Alternativ können Sie unter der Registerkarte «Start» Ihre Daten markieren und dann manuell «Als Tabelle formatieren» (Bild 6). Dies ermöglicht die Verwendung strukturierter Verweise. Mit diesen erhöhen Sie die Lesbarkeit ihrer Bezüge – beispielsweise in Formeln – und machen Sie robuster gegenüber Daten- und Spaltenänderungen. Strukturierte Verweise sind somit die ideale Ergänzung zum Power Query. Anstatt die Buchstabenbezeichnungen der jeweiligen Spalten zu verwenden, funktionieren strukturierte Verweise immer prinzipiell nach der Struktur «Tabellennamen[Spaltennamen]».
Im Beispiel öffnen Sie ein neues Tabellenblatt. Zur Berechnung benötigen Sie drei Zahlen: die Gesamtsumme, die Anzahl Erwachsener und der Betrag pro Kopf. Analog funktioniert das am Besten im Tabellenformat.
Die Gesamtsumme berechnet sich als der totale Betrag aller einbezahlten Ausgaben. Dazu navigieren Sie in das entsprechende Feld und können mit der Formel =SUMME(Ausgaben[einbezahlt]) die Formel für die Summe mittels eines strukturierten Verweises berechnen.
Analog können Sie die Anzahl Erwachsener im Datensatz berechnen. Dazu navigieren Sie wieder in das entsprechende Feld und schreiben die Formel =ZÄHLENWENN(Ausgaben[Lernende?];’Nein’).
Als nächstes ist die Berechnung der Ausgaben pro Kopf das Ergebnis der Division der Gesamtausgaben und der Anzahl Erwachsenen. Um die Ausgaben pro Person festzulegen, fügen Sie neben Ihren Daten eine neue Spalte ein.
![Formel Ausgaben pro Kopf: =Tabelle2[Zu Zahlen]-[@einbezahlt]). Das @ referenziert innerhalb der Spalte auf den Wert mit der gleichen Zeilennummer.](https://prod-bitadminch-hcms-sdweb.imgix.net/dam/de/sd-web/W9O8fDtMNmwm/eisbrecher-75-inhalt-tipps-excel-10-DE.jpg?auto=format)
Um den Namen Ihrer Tabelle in Erfahrung zu bringen, können Sie die Daten in Ihrer Tabelle markieren und sehen dann links oben den Tabellennamen:
Wenn Sie die Spaltenformeln kopieren, haben Sie für alle Personen berechnet, wie viel sie einbezahlt haben und wie viel sie zurückerhalten.
Das Beispiel deckt nur ein kleines Spektrum der Möglichkeiten von Power Query und strukturierten Verweisen ab. Durch die intuitive Programmierung von Arbeitsschritten können Sie mühselige Aufgaben rasch und unkompliziert automatisieren. Zudem haben Sie immer eine hohe Transparenz, was im Hintergrund geschieht.
Power Query und strukturierte Verweise sind somit unverzichtbare Hilfsmittel im täglichen Umgang mit Excel und helfen Ihnen aus Ihren Daten noch mehr Erkenntnisse zu gewinnen. Die Daten aus diesem Beispiel sind weiter unten angehängt. Probieren Sie es doch mal aus.
Download
Text: Oliver Prinzing
Das Bundesarchiv – Digitalisierung on demand
Der Online-Zugang des Bundesarchivs (BAR) ist ein Projekt, welches die Digitalisierung des gesamten Archives in den nächsten Jahren vorsieht. Um auch einen schnellen Zugang zu gewährleisten, steht nun den Kunden neu ein Onlineportal zur Verfügung.
Kundenzufriedenheit: Fachleute stabil, leichter Rückgang bei den Benutzern
Die Resultate der Kundenzufriedenheitsumfrage 2019 liegen vor. Die Kunden bewerten die Leistungen des BIT mit der Note 4.65. Während die Fachleute das BIT gleich beurteilen wie im Vorjahr, ist die Zufriedenheit der Benutzerinnen und Benutzer leicht gesunken.
App Store Bund: Fachanwendungen auf dem Mobiltelefon zugänglich machen
Das BIT führt eine neue Marktleistung ein, mit der sich Fachanwendungs-Apps in den App Store Bund aufnehmen lassen. Das Bundesamt für Polizei (fedpol) bietet bereits zwei Anwendungen im App Store an. Neu können Ämter eigene Apps beim BIT entwickeln lassen.
Die digitale Transformation der Bundesverwaltung unterstützen
Aus der Geschäftsleitung – BIT-Direktor Dirk Lindemann teilt seine Gedanken zur Rolle des BIT in der digitalen Transformation der Bundesverwaltung
«Reicht es für einen Sieg?»
Vom 7. bis am 8. November fand der BIT-Hackathon in der Titanic II statt. Die teilnehmenden Teams versuchten innerhalb von 24 Stunden ein Produkt zu entwickeln, welches die Jury überzeugt. Philippe Krüttli, Lernender im BIT, berichtet über seine Erlebnisse am Hackathon.
Vorsicht vor betrügerischen Webshops
In letzter Zeit tauchen vermehrt betrügerische Webshops in den Google-Suchergebnissen auf. Der «Eisbrecher» zeigt Ihnen, bei welchen Merkmalen Sie misstrauisch sein sollten.