So lesen und verarbeiten Sie Excel-Tabellen mit Pandas und OpenPyXL: von Grund auf zum Profi

Wenn Sie an der Datenanalyse beteiligt sind oder wiederkehrende Aufgaben in Tabellenkalkulationen automatisieren müssen, kombinieren Sie Python mit Excel ist ein Gewinnender Schritt zur Beschleunigung Ihres ArbeitsablaufsExcel ist in vielen Unternehmen noch immer das beliebteste Tool, und erfahren Sie, wie Sie Excel daran hindern, Zahlen in Datumsangaben umzuwandeln, während Python Ihnen Leistung, Flexibilität und ein Ökosystem von Bibliotheken bietet, die für Daten entwickelt wurden. In diesem Handbuch erfahren Sie im Detail, wie Sie Excel-Tabellen mit Pandas y openpyxl, wann man welche davon verwendet und wie man in realen Situationen das Beste daraus macht.

Hier erfahren Sie nicht nur, wie Sie eine Datei öffnen und ein paar Zellen betrachten, sondern auch, wie Sie bestimmte Blätter und Bereiche laden. Ergebnisse filtern, transformieren und speichern, formatear Zellen mit erweiterten Stilen, erstellen neue Arbeitsmappen und Tabellen, generieren automatisierte Berichte und sogar Diagramme oder kleine Dashboards. Wir gehen von den Grundlagen zu praktischen Beispielen über, mit anpassbarem Code und mit Leistungsempfehlungen und Best Practices um Engpässe und typische Fehler zu vermeiden.

Vorbereiten der Umgebung und der erforderlichen Bibliotheken

Bevor Sie beginnen, stellen Sie sicher, dass Sie eine aktuelle Version von Python installiert haben. Es wird empfohlen Python 3.7 oder höher um die Kompatibilität mit den von uns verwendeten Bibliotheken sicherzustellen. Um Ihre Version zu überprüfen, können Sie den folgenden Befehl im Terminal.

python --version

Um Excel in Python zu bearbeiten, verwenden Sie die folgenden Schlüsselbibliotheken: Pandas y openpyxl; jedes deckt unterschiedliche Bedürfnisse ab. Mit Pip Sie können sie im Handumdrehen installieren und mit dem Experimentieren beginnen.

pip install pandas openpyxl

Wenn Sie Abhängigkeiten lieber mit einem Manager wie Poetry verwalten möchten, können Sie beide Pakete auch mit installieren Befehle einfach, zum Beispiel Poesie hinzufügen Pandas y Poesie hinzufügen openpyxl, was Ihnen hilft Aufrechterhaltung einer reproduzierbaren Umgebung pro Projekt ohne Kopfschmerzen.

Bücher, Blätter und Zellen mit openpyxl lesen

Die Bibliothek openpyxl Funktioniert direkt mit XLSX-Dateien und ermöglicht Ihnen das Öffnen von Arbeitsmappen, Bearbeiten von Tabellenblättern und präzises Lesen/Schreiben von Zellen. Es ist ideal, wenn Sie Feinformatkontrolle, wenden Sie Stile und Formeln an und arbeiten Sie mit der Excel-Struktur als solcher.

from openpyxl import load_workbook

# Cargar un archivo Excel
workbook = load_workbook("example.xlsx")

# Ver nombres de hojas disponibles
print(workbook.sheetnames)

Sobald die Arbeitsmappe geöffnet ist, können Sie ein Blatt nach Namen auswählen und bestimmte Werte anzeigen. Diese Vorgehensweise ist nützlich, wenn Sie Punktzellen untersuchen oder Bereiche durchqueren ohne alles in eine tabellarische Struktur umzuwandeln.

# Seleccionar una hoja concreta
sheet = workbook

# Leer el valor de una celda
valor = sheet.value
print(f"Valor de A1: {valor}")

Um durch Zeilen oder Bereiche zu iterieren, iter_rows ist Ihr Verbündeter. Sie können Zeilen und Spalten abgrenzen und jede Zelle verarbeiten. Wenn Sie nur lesen, reduziert die Aktivierung des Nur-Lese-Modus den Speicherbedarf und verbessert die Geschwindigkeit bei großen Dateien.

# Recorrer las primeras 10 filas
for fila in sheet.iter_rows(min_row=1, max_row=10):
    valores = 
    print(" ".join(valores))

Effizientes Lesen und Schreiben von Daten mit Pandas

Wenn dein Ziel ist Datenanalyse und -manipulationPandas ist Ihre beste Wahl. Es wandelt Excel-Tabellen in DataFrames (sehr leistungsstarke Tabellen) um, um Ergebnisse schneller zu filtern, zu aggregieren, zu transformieren und zu exportieren als mit manuellen Schleifen.

import pandas as pd

# Leer un Excel a DataFrame
df = pd.read_excel("datos.xlsx")

# Ver las primeras filas
print(df.head())

Die Funktion read_excel ermöglicht das Laden bestimmter Blätter, bestimmter Spalten oder das Überspringen der ersten Zeilen (sehr nützlich für Dateien mit komplexen Kopfzeilen oder Notizen). Dies gibt Ihnen Kontrolle und Leistung weil Sie vermeiden, Daten mitzunehmen, die Sie nicht benötigen.

# Hoja específica
df = pd.read_excel("datos.xlsx", sheet_name="Hoja2")

# Importar solo ciertas columnas (por etiqueta de Excel o nombre de columna)
df = pd.read_excel("datos.xlsx", usecols=)  # Por letras

# Omitir filas del principio
df = pd.read_excel("datos.xlsx", skiprows=4)

Wenn Sie mit der Transformation Ihres DataFrame fertig sind, können Sie ihn mit einer einzigen Methode nach Excel übertragen. index=Falsch Vermeiden Sie es, den Index als zusätzliche Spalte zu schreiben, was bei der Erstellung von Geschäftsberichten sehr üblich ist.

# Guardar el DataFrame en Excel
 df.to_excel("datos_procesados.xlsx", index=False)

Wann verwendet man Pandas und wann OpenPyXL?

Obwohl sie sich gegenseitig ergänzen, behandeln sie nicht dasselbe Problem: Pandas glänzt bei der Massenverarbeitung (Filterung, Aggregationen, Verknüpfungen, Bereinigung), während openpyxl regelt das Format (Stile, Rahmen, Breiten, Formeln, Diagramme, Erstellen/Löschen von Blättern usw.). Durch die kluge Auswahl sparen Sie Zeit.

Wenn Sie Tausende von Zellen mit einer einfachen Regel ändern müssen (z. B. 10 % zu einer Spalte hinzufügen), können Sie dies mit Pandas in einer Zeile tun. In OpenPyXL benötigen Sie Zellen mit Schleifen durchlaufen und Referenzen verwalten. Wenn Sie jedoch unternehmensspezifische Formatierungen und Stile anwenden oder Diagramme zur endgültigen Excel-Tabelle hinzufügen möchten, ist openpyxl die richtige Wahl.

Eine sehr nützliche Strategie besteht darin, beides zu kombinieren: mit Pandas verarbeiten und, sobald die endgültige Tabelle generiert ist, openpyxl verwenden, um Polieren Sie das professionelle Finish (Fettdruck, zentrierte Überschriften, Farben, Zahlenformate etc.) So haben Sie Leistung und ein präsentationsreifes Ergebnis.

Häufige Operationen mit Pandas: Auswahl, Filterung und Änderungen

Mit Pandas sind Spaltenauswahl und bedingte Filterung ein Kinderspiel. So können Sie große Datensätze mit vektorisierte Operationen ohne Schleifen zu schreiben, wodurch besser lesbarer und schnellerer Code erreicht wird.

import pandas as pd

archivo_excel = "ejemplo_excel.xlsx"
df = pd.read_excel(archivo_excel)

# Selección de columnas
df_col = df]

# Filtrado por condición
filtrado = df > 10]

# Nuevas columnas y transformaciones
df = df * 2
df = df.apply(lambda x: x + 5)

# Guardar resultado
df.to_excel("resultado_excel.xlsx", index=False)

Um nur ein Teil zu prüfen, können Sie .Kopf() oder Indizierung .iloc wenn Sie Zeilen/Spalten nach Position benötigen. Darüber hinaus unterstützt Pandas beim Exportieren mehrere Formate (CSV, Parquet usw.), was Optionen jenseits von Excel eröffnet. Es ist auch üblich, Pandas mit Anleitungen zu ergänzen Arithmetische Operationen in Excel beim Migrieren der Logik zwischen beiden Umgebungen.

Lesen und Bearbeiten mit openpyxl: von Zellen zu Bereichen

Wenn der Fokus auf dem Excel-Dokument selbst liegt, können Sie mit openpyxl Arbeitsmappen erstellen, Blätter hinzufügen, umbenennen und unnötige löschen. Diese detaillierte Kontrolle ist unerlässlich, wenn Sie Folgendes benötigen: an das Layout einer Vorlage anpassen oder bestehende Formeln beibehalten.

from openpyxl import Workbook, load_workbook

# Crear un libro nuevo
ewb = Workbook()
ws = ewb.active
ws.title = "Hoja Principal"
ewb.save("nuevo.xlsx")

# Cargar y manipular un libro existente
wb = load_workbook("datos_openpyxl.xlsx")
wb.create_sheet("Nueva Hoja")
del wb
wb.save("datos_openpyxl.xlsx")

Der Zugriff auf einzelne Zellen oder Bereiche ist unkompliziert. Sie können auch lesen, Werte ändern und schreiben Wieder zurück. Bei massiven Änderungen erleichtern Ihnen die Verwendung von Bereichen und eine klare Excel-Struktur die Arbeit.

ws = wb
celda = ws
print(celda.value)

# Modificar valores
ws = "Nuevo Nombre"

# Recorrer un rango
for fila in ws:
    for c in fila:
        print(c.value)

Wenden Sie Stile, Formate und Zahlen mit OpenPyxl an

Einer der Vorteile von openpyxl ist, dass Sie Ihren Bericht mit Schriftarten, Rahmen, Auffüllungen und Ausrichtungensowie numerische Formate (z. B. zwei Dezimalstellen). Dies ist wichtig für Berichte, die von Laien gesehen werden.

from openpyxl.styles import Font, Border, Side, PatternFill, Alignment

ws = wb

# Estilos
fuente = Font(name="Arial", size=12, bold=True, color="FF000000")
borde = Border(left=Side(style="thin"), right=Side(style="thin"),
               top=Side(style="thin"), bottom=Side(style="thin"))
relleno = PatternFill(start_color="FFFF0000", end_color="FFFF0000", fill_type="solid")

# Aplicar a una celda
c = ws
c.font = fuente
c.border = borde
c.fill = relleno
c.alignment = Alignment(horizontal="center", vertical="center")
c.number_format = "0.00"  # Dos decimales

wb.save("datos_openpyxl.xlsx")

Sie können auch Excel-Formeln direkt in die Zellen, die beim Öffnen der Datei in Excel neu berechnet werden. Beachten Sie die Die häufigsten Fehler in Excel-Formeln, die häufig beim Mischen von codegenerierten Daten und Tabellenkalkulationslogik auftreten.

ws.value = "=SUM(A1:B1)"
wb.save("datos_openpyxl.xlsx")

Einfache Diagramme und Visualisierungen in Excel mit OpenPyxl

Um einen Bericht zu vervollständigen, müssen Sie manchmal ein Diagramm in die Arbeitsmappe selbst einfügen. Mit openpyxl können Sie Erstellen Sie Balken-, Linien- oder andere Diagramme aus Datenbereichen und platzieren Sie sie an einem bestimmten Ort.

from openpyxl.chart import BarChart, Reference

chart = BarChart()
datos = Reference(ws, min_col=1, min_row=1, max_col=2, max_row=5)
chart.add_data(datos, titles_from_data=False)
ws.add_chart(chart, "E1")
wb.save("datos_openpyxl.xlsx")

Berichtsautomatisierung: Kombination von Pandas und OpenPyxl

Ein sehr nützliches Rezept besteht darin, Daten mit Pandas zu verarbeiten (Summen, Durchschnitte, Gruppierungen) und die Ergebnisse in eine neue Arbeitsmappe zu übertragen, die Sie dann mit openpyxl dekorieren, um einen gut formatierten Bericht liefernDieses Muster lässt sich gut für regelmäßige Berichte skalieren.

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

# Leer ventas
ventas = pd.read_excel("ventas.xlsx")

# Agregaciones
por_producto = ventas.groupby("Producto").sum()
promedio = ventas.mean()

# Crear libro de reporte
wb = Workbook()
ws = wb.active
ws.title = "Reporte de Ventas"

# Cabeceras
ws = "Producto"
ws = "Total Ventas"
ws = "Promedio Ventas Mensual"

enc = Font(bold=True)
for celda in ("A1", "B1", "C1"):
    ws.font = enc
    ws.alignment = Alignment(horizontal="center")

# Datos
fila = 2
for producto, total in por_producto.items():
    ws = producto
    ws = total
    ws = promedio
    fila += 1

wb.save("reporte_ventas.xlsx")

Wenn Sie auch visuelle Kohärenz schaffen möchten, fügen Sie hinzu dünne Ränder und Zahlenformate zu den Betragsspalten. Auf diese Weise ist Ihr Bericht bereit, ohne manuelle Anpassungen in Excel freigegeben zu werden; und wenn Sie das Ausfüllen automatisieren müssen, können Sie sich auf Techniken verlassen, um Daten automatisch anhand von Mustern ausfüllen.

Typische Prozesse: Filtern, Transformieren und Speichern mit Pandas

Ein häufiger Fall ist das Filtern nach einer Bedingung und das Speichern des Ergebnisses in einer neuen Datei. Dies geschieht in wenigen Zeilen mit Pandas und führt zu ideal zur Reinigung von Rohrleitungen oder Datenaufbereitung für Geschäftsteams.

import pandas as pd

df = pd.read_excel("example.xlsx")

# Filtrar ventas > 1000
filtrado = df > 1000]

# Guardar sin índice
filtrado.to_excel("filtered.xlsx", index=False)
print("Archivo guardado")

Wenn Sie eine ursprüngliche Spalte beibehalten und eine geänderte Spalte erstellen müssen (z. B. 10 % zur „Gesamtfläche“ hinzufügen), vermeidet die vektorisierte Operation Schleifen und hinterlässt einen DataFrame sauber und leicht zu inspizieren.

df = pd.read_excel("cultivos.xlsx")

df = df
df = df * 1.1

# Mostrar primeras 10 filas a partir de la tercera columna
print(df.iloc)

Wenn Sie Ergebnisse in der endgültigen Datei vorübergehend ausblenden möchten, anstatt sie zu löschen, beachten Sie die Regeln für Zeilen basierend auf dem Zellenwert ausblenden und erleichtern Sie die Teamüberprüfung.

Löschen von Zeilen und Bereinigen von Daten

Eine weitere häufige Aufgabe ist das Löschen von Datensätzen nach Position oder Bedingung. Mit Pandas ist das Löschen der ersten 10 Zeilen mithilfe des Indexes unkompliziert; für komplexere Filter Verwenden Sie Boolesche Ausdrücke ohne For-Schleifen.

df = pd.read_excel("cultivos.xlsx")

# Quitar las 10 primeras filas por índice
df.drop(df.index, inplace=True)
print(df.head(10))

Wenn Ihre Bereinigung von Regeln wie „Zeilen mit gerader Gesamtfläche löschen“ abhängt, können Sie eine Bedingung erstellen und anwenden, wobei der Code erhalten bleibt ausdrucksstark und wartbar gegen Schleifen und manuelle Zähler. Und wenn das Blatt geschützt ist, denken Sie daran, wie Den Schutz einer Excel-Tabelle mit einem Passwort aufheben bevor Sie es ändern.

Bearbeiten und Löschen mit openpyxl: Zellen und Zeilen

Wenn Sie mit OpenPyxl arbeiten, müssen Sie zum Ändern einer ganzen Spalte die Zellen durchlaufen. Der Vorteil besteht darin, dass Sie neue Spalten einfügen können, Respektieren Sie das ursprüngliche Layout und in einer anderen Datei speichern, ohne das Format zu beschädigen.

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook("cultivos.xlsx")
ws = wb

# Insertar columna G y titularla
ws.insert_cols(7)
ws = "Area Total Modificada"

# Sumar 10% a la columna F y guardar antiguo valor en G
for fila in ws.iter_rows(min_row=2):
    valor_antiguo = None
    for celda in fila:
        col = get_column_letter(celda.column)
        if col == "F":
            valor_antiguo = celda.value
            celda.value = float(celda.value) * 1.1
        if col == "G":
            celda.value = valor_antiguo

wb.save("cultivos_modify.xlsx")

Um Zeilen nach Position zu löschen, löst openpyxl es auch mit einem Aufruf, obwohl für fortgeschrittene Bedingungen Sie müssen iterieren und basierend auf dem Inhalt jeder Zeile entscheiden, was gelöscht werden soll.

wb = load_workbook("cultivos.xlsx")
ws = wb

# Eliminar las 10 primeras filas (ajusta idx si hay cabeceras)
ws.delete_rows(idx=1, amount=10)
wb.save("cultivos_modify.xlsx")

Konsolidieren Sie mehrere Excel-Dateien in einer

Ein Klassiker: Sie haben Dutzende von .xlsx-Dateien mit dem gleichen Schema und Sie möchten Fügen Sie sie zu einer einzigen Tabelle zusammen um sie zu analysieren. Mit Pandas und Glob können Sie dies in wenigen Zeilen tun, ohne jede Datei manuell öffnen zu müssen.

import pandas as pd
import glob

excel_files = glob.glob("*.xlsx")

# Concatenar todo en un DataFrame
todos = pd.concat(, ignore_index=True)

todos.to_excel("consolidated_data.xlsx", index=False)

Dieser Ansatz eignet sich perfekt für monatliche Integrationen, Multi-Delegations-Reporting oder jeden Prozess, bei dem Sie mehrere Bücher mit homogener Struktur.

Berichte automatisch nach Abteilungen erstellen

Aus einer Datei mit weltweiten Umsätzen können Sie nach Abteilungen segmentieren und eine personalisierter Bericht für jeden automatisch. Jede Datei ist bereit, mit Ihrem Team geteilt zu werden.

import pandas as pd

sales = pd.read_excel("sales_data.xlsx")
departamentos = sales.unique()

for dpto in departamentos:
    df_dpto = sales == dpto]
    df_dpto.to_excel(f"{dpto}_report.xlsx", index=False)

Wenn Sie jeden Bericht auch formatieren müssen, können Sie die resultierende Datei mit openpyxl laden und Styling-Header, passen Sie die Spaltenbreiten an und fügen Sie eine Unternehmensfarbe hinzu. Sie können auch die Dateiorganisation automatisieren und Erstellen Sie kaskadierende Ordner und Unterordner für jede Abteilung.

Kleines interaktives Dashboard mit Tkinter und Pandas

Für schnelles Prototyping können Sie ein einfaches Fenster erstellen, das Spalten anzeigt und den Durchschnitt der ausgewählten Spalte berechnet. Es ist kein vollständiges BI, aber es funktioniert für Express-Validierungen ohne Python zu verlassen.

import tkinter as tk
import pandas as pd
from tkinter import messagebox

file = "data.xlsx"
data = pd.read_excel(file)

def calcular_media():
    col = listbox.get(listbox.curselection())
    media = data.mean()
    messagebox.showinfo("Resultado", f"Promedio en {col}: {media:.2f}")

root = tk.Tk()
root.title("Dashboard interactivo")

listbox = tk.Listbox(root)
listbox.pack()
for c in data.columns:
    listbox.insert(tk.END, c)

btn = tk.Button(root, text="Calcular promedio", command=calcular_media)
btn.pack()

root.mainloop()

Für komplexere Reporting-Projekte möchten Sie dies möglicherweise in eine Web-App verschieben mit Streamlit oder Dash, aber als schnelles lokales Dienstprogramm kann Tkinter Sie mit sehr wenig Code aus der Klemme holen.

Explorative Analyse: Statistik und Grafik mit Pandas + Matplotlib

Wenn Ihre Excel-Datei Kunden- oder Verkaufsinformationen enthält, ist es sinnvoll, einen kurzen Blick auf Verteilungen und Beziehungen zu werfen. Mit Pandas erhalten Sie beschreibende Statistiken und mit Matplotlib generieren Sie Histogramme und Streudiagramme sehr hilfreich.

import pandas as pd
import matplotlib.pyplot as plt

clientes = pd.read_excel("clientes.xlsx")

# Estadísticas generales
print(clientes.describe())

# Histograma de edades
clientes.hist(bins=20)
plt.xlabel("Edad")
plt.ylabel("Frecuencia")
plt.title("Distribución de edades")
plt.show()

# Dispersión ingresos vs satisfacción
clientes.plot.scatter(x="Ingresos", y="Satisfaccion")
plt.xlabel("Ingresos")
plt.ylabel("Satisfacción")
plt.title("Ingresos vs. Satisfacción")
plt.show()

Damit können Sie Ausreißer, Verzerrungen oder interessante Zusammenhänge erkennen, die Sie später genauer untersuchen können. Wenn Sie dies in Excel berichten möchten, exportieren Sie Übersichtstabellen mit Pandas und Grafiken im Buch erstellen mit openpyxl für ein abgerundetes Ergebnis. Für spezifische statistische Analysen können Sie auch die Quartilfunktion in Excel als Referenz bei der Migration von Indikatoren.

Ergebnisse speichern mit Pandas und OpenPyXL

Mit Pandas können Sie sofort in Excel schreiben. Außerdem haben Sie den Vorteil, in andere Formate wie CSV oder Parquet exportieren zu können. Wenn die Datei geschäftlich ist, fügen Sie hinzu Blätter mit unterschiedlichen Details oder nach Segmenten im selben Buch gefilterte Daten.

df = pd.read_excel("cultivos.xlsx")

df = "SI"
df.to_excel("cultivos_modify_pandas.xlsx", index=False)

Wenn Sie mit openpyxl arbeiten, denken Sie daran, dass Sie neue Spalten erstellen und diese nach Bereich füllen können. Dies ist eine einfache Möglichkeit, Markierte Datensätze überprüft in Berichten mit fester Struktur.

from openpyxl import load_workbook

wb = load_workbook("cultivos.xlsx")
ws = wb

ws = "Revisado"
for celda in ws:
    celda.value = "SI"

wb.save("cultivos_modify_openpyxl.xlsx")

Leistung und bewährte Verfahren

Bei großen Dateien empfiehlt es sich, die Datenmenge zu begrenzen, die Sie in den Speicher holen. In Pandas Lesen Sie nur die erforderlichen Spalten mit usecols und vermeiden Sie die Verarbeitung irrelevanter Blätter. Verwenden Sie in openpyxl schreibgeschützt=Wahr zu lesen und write_only=Wahr schreiben.

Achten Sie auf Datentypen: Manchmal speichert Excel Zahlen als Text, was Filter und Aggregationen beeinträchtigen kann. Beim Laden mit Pandas können Sie dtypes oder Spalten normalisieren nach dem Lesen, um Überraschungen mit Daten, Beträgen oder IDs zu vermeiden.

Bei Excel-Formeln ist zu beachten, dass nicht alle Funktionen von den Bibliotheken gleichermaßen unterstützt werden. werden beim Öffnen in Excel neu berechnetWenn Sie feste Werte benötigen, werten Sie diese zuerst mit Pandas aus und schreiben Sie vorberechnete Zahlen zurück.

Priorisieren Sie Dateien hinsichtlich der Kompatibilität . Xlsx (modernes Format) und testen Sie Ihre Skripte mit verschiedenen Excel-Versionen, wenn Ihre Benutzer heterogene Umgebungen haben. Dies reduziert Probleme aufgrund von Funktionen oder nicht unterstützte Funktionen in älteren Versionen.

Denken Sie abschließend an reproduzierbare Pipelines: Legen Sie Abhängigkeiten fest (Poetry, requirements.txt), dokumentieren Sie Ihre Parameter (sheet_name, usecols, skiprows) und fügen Sie hinzu Protokolle Minimum zum Debuggen. Dies spart Ihnen Zeit beim Skalieren Ihres Projekts oder beim Ändern Ihrer Datenquelle.

Wenn Sie es bis hierher geschafft haben, beherrschen Sie bereits das Lesen, Verarbeiten und Schreiben von Excel mit Pandas und OpenPyXL. Sie wissen wann welches Werkzeug geeignet ist und wie Sie diese für professionelle Berichte kombinieren. Von nun an sehen Sie Ihre Tabellenkalkulationen als Quellen und Ziele in einem robusten, automatisierten und viel schnelleren Workflow.