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).
Bild 1: Ihre Ausgangsdaten müssen in einer Excel-Tabelle vorliegen
Bild 2: Unter «Daten» > «Neue Abfrage» können Sie nun zu Ihrem Ausgangsdatenblatt navigieren
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).
Bild 3: In der Registerkarte «Transformieren» gehen Sie auf «Werte ersetzen»
Bild 4: Unter «Transformieren» > «Werte ersetzen» können Sie Regeln zum Ändern von Werten festlegen.
Bild 5: Unter der Registerkarte «Spalte hinzufügen» können Sie eine Regel zum Hinzufügen von einer bedingten Spalte festlegen.
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.
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.
Text: Oliver Prinzing
