Beitrag aus SmartTools Excel Weekly
Python in Excel – Das müssen Sie wissen, Teil 2
Excel 365
Teilnehmer am Microsoft 365 Insider-Programm mit Betakanal-Updates haben die Möglichkeit, Python in Excel auszuprobieren. Nach dem allgemeinen Überblick aus dem ersten Teil dieses Tipps widmen wir uns jetzt der Praxis. Dabei geht es zunächst darum, wie Sie vorhandene Tabellendaten in Python integrieren. Im Mittelpunkt stehen die Schritte, mit denen Sie Python-Code in Excel-Tabellenblätter eingeben. Dabei erfahren Sie gleichzeitig, wie Python die Datenanalyse und -visualisierung bereichert. Erwarten Sie jedoch keine Einführung in die Programmiersprache. Ein Grundverständnis von Python setzen wir voraus.
Grundlage der weiteren Betrachtungen soll eine Excel-Tabelle mit Wetterdaten sein, - konkret eine Liste mit den Höchst- und Tiefsttemperaturen, die täglich über mehrere Jahre an einer Wetterstation erfasst worden sind. Der Bereich hat die Spaltenüberschriften "Datum", "Temp. max." sowie "Temp. min." und ist als Tabelle mit dem Namen "tblTemperaturen" formatiert.
Um mit den Excel-Daten in Python arbeiten zu können, müssen Sie sie zuerst in ein Python-Objekt umwandeln. Dafür können Sie im Python-Code eine spezielle Funktion namens "xl" einsetzen, der Sie zum Beispiel eine Bereichsadresse oder einen strukturierten Verweis auf eine Excel-Tabelle übergeben.
Den entsprechenden Python-Code geben Sie direkt in eine Tabellenzelle ein. Damit Excel weiß, dass Sie Python-Code und keine normale Excel-Formel eingeben wollen, müssen Sie die Eingabe auf eine der folgenden drei Arten beginnen:
- Wählen Sie Formeln-Python (Vorschau)-Python einfügen an,
- geben Sie "=PY" in die Zelle ein und drücken Sie dann die Tab-Taste
- oder drücken Sie die Tastenkombination Strg + Alt + Umschalt + P
Wenn sich die Tabelle mit den Wetterdaten beispielsweise in den Spalten A bis C befindet und die Spaltenüberschriften in Zeile 3 stehen, könnten Sie in Zelle E3 ein Python-Objekt erstellen, das sich auf die Tabellendaten bezieht. Das geht wie folgt:
- Setzen Sie den Cursor in Zelle E3 und aktivieren Sie den Python-Modus mit einer der oben genannten Methoden.
- Geben Sie folgenden Code ein:
data_gesamt = xl(
- Markieren Sie nun mit der Maus den gesamten Bereich der Wetterdaten. Sie werden feststellen, dass Excel automatisch die Bereichsadresse (hier den strukturierten Verweis auf die Tabelle) in die Python-Codezeile einträgt und erkennt, dass der Bereich Überschriften enthält. Die vollständige Codezeile lautet jetzt:
data_gesamt = xl("tblTemperatur[#Alle]", headers=True)
- Um die Codeeingabe abzuschließen und um den Python-Code auszuführen, müssen Sie nun Strg + Return drücken.
Während der Codeausführung erscheint "@#BELEGT!" in der Zelle, - ein Zeichen dafür, dass Ihre Daten im Internet unterwegs sind und der Python-Code von Microsoft-Servern ausgewertet wird. Das zurückgegebene Ergebnis erscheint als Python-Objekt in Excel. Im Beispielfall wird ein Objektsymbol und der Text "DataFrame" angezeigt. In Python ist ein DataFrame eine zweidimensionale Datenstruktur, also so etwas wie eine Tabelle mit Zeilen und Spalten.
Sie können nun direkt mit dem Objekt weiterarbeiten. Sie können sich auch eine Vorschau anzeigen lassen, indem Sie auf das Objektsymbol in der Zelle klicken. Oder Sie geben den DataFrame in einem Excel-Bereich aus. Dazu klicken Sie in der Bearbeitungsleiste links neben dem Python-Code auf das Objektsymbol und wählen im Dropdownmenü Excel-Wert an. Daraufhin fügt Excel den Inhalt des DataFrame in den Überlaufbereich der Python-Formelzelle ein.
Der Excel-Wert des Python-Objekts ist im Beispiel eine 1:1-Kopie der Wetterdatentabelle. Das ist zunächst wenig hilfreich. Aber der Vorteil liegt darin, dass Sie die Daten mit Python analysieren und bereinigen können. Nehmen wir für unser Beispiel an, dass es Tage mit Messwertfehlern gab, für die automatisch der Temperaturwert "-999" eingetragen wurde. Außerdem gibt es Lücken in der Datumsfolge, das heißt, es fehlen einzelne Tage komplett.
Mit Python lassen sich die Daten mit wenigen Codezeilen bereinigen: Der Wert "-999" wird pauschal durch Leerwerte ("Not a Number" oder "nan" in Python) ersetzt. Und fehlende Tage werden aufgefüllt und die entsprechenden Temperaturwerte durch Interpolation berechnet. Dazu erweitern Sie den Python-Code in Zelle E3:
- Setzen Sie den Cursor in Zelle E3 und wählen Sie Formeln-Python (Vorschau)-Python einfügen an. Da die Zelle bereits Python-Code enthält, würde es auch genügen, einmal in die Bearbeitungsleiste zu klicken.
- Gehen Sie in der Bearbeitungsleiste ans Ende der aktuellen Codezeile und drücken Sie Return, um eine neue Zeile einzufügen.
- Da Python-Code häufig aus mehreren Zeilen besteht, empfiehlt es sich, die Bearbeitungsleiste zu erweitern. Dazu klicken Sie auf den Dropdownpfeil am rechten Ende der Leiste. Mit der Maus lässt sich die Höhe des Bearbeitungsbereichs individuell anpassen.
- Ergänzen Sie den Code durch folgende Anweisungszeilen:
data_gesamt = data_gesamt.replace(-999, np.nan)
data_gesamt.set_index('Datum', inplace=True)
data_gesamt = data_gesamt.resample('D').interpolate()
- Drücken Sie Strg + Return, um die Codeausführung zu starten.
Sie haben nun eine vollständige Datenreihe ohne Datumslücken und ohne störende Fehlerwerte (-999), die sich hervorragend auswerten lässt.
Mehr zu den Auswertungsmöglichkeiten mit Python erfahren Sie im folgenden Teil dieses Tipps.