Cara membaca dan memproses lembar Excel dengan pandas dan openpyxl: dari awal hingga pro

Jika Anda terlibat dalam analisis data atau perlu mengotomatiskan tugas-tugas berulang dalam spreadsheet, menggabungkan Ular sanca dengan Excel adalah langkah yang tepat untuk mempercepat alur kerja AndaExcel masih menjadi alat paling populer di banyak perusahaan, dan pelajari cara mencegah Excel mengonversi angka menjadi tanggal, sementara Python memberi Anda kekuatan, fleksibilitas, dan ekosistem pustaka yang dirancang untuk data. Dalam panduan ini, Anda akan melihat secara detail cara membaca dan memproses lembar Excel dengan panda y openpyxl, kapan harus menggunakan masing-masingnya dan bagaimana cara memaksimalkannya dalam situasi kehidupan nyata.

Selain membuka file dan melihat beberapa sel, di sini Anda akan mempelajari cara memuat lembar dan rentang tertentu, menyaring, mengubah, dan menyimpan hasil, untuk format sel dengan gaya tingkat lanjut, membuat buku kerja dan lembar kerja baru, membuat laporan otomatis, dan bahkan membuat bagan atau dasbor kecil. Kita akan membahasnya dari dasar hingga contoh praktis, dengan kode yang siap diadaptasi, dan dengan rekomendasi kinerja dan praktik terbaik untuk menghindari kemacetan dan kesalahan umum.

Mempersiapkan lingkungan dan perpustakaan yang diperlukan

Sebelum memulai, pastikan Anda telah menginstal Python versi terbaru; disarankan Python 3.7 atau lebih tinggi untuk memastikan kompatibilitas dengan pustaka yang akan kita gunakan. Untuk memeriksa versi Anda, Anda dapat menjalankan perintah berikut di terminal.

python --version

Untuk memanipulasi Excel dalam Python, pustaka kunci yang akan Anda gunakan adalah panda y openpyxl; masing-masing memenuhi kebutuhan yang berbeda. Dengan biji Anda dapat memasangnya sebentar lagi dan mulai bereksperimen.

pip install pandas openpyxl

Jika Anda lebih suka mengelola dependensi dengan manajer seperti Poetry, Anda juga dapat menginstal kedua paket dengan perintah sederhana, misalnya puisi tambahkan panda y puisi tambahkan openpyxl, yang membantu Anda mempertahankan lingkungan yang dapat direproduksi per proyek tanpa pusing.

Membaca buku, lembar, dan sel dengan openpyxl

Perpustakaan openpyxl Berfungsi langsung pada file .xlsx, memungkinkan Anda membuka buku kerja, memanipulasi lembar kerja, dan membaca/menulis sel secara akurat. Ideal saat Anda membutuhkan kontrol format halus, menerapkan gaya, rumus, dan bekerja dengan struktur Excel sebagaimana mestinya.

from openpyxl import load_workbook

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

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

Setelah buku kerja terbuka, Anda dapat memilih lembar berdasarkan nama dan melihat nilai tertentu. Pendekatan ini berguna ketika Anda ingin memeriksa sel titik atau melintasi rentang tanpa mengubah semuanya ke dalam struktur tabel.

# Seleccionar una hoja concreta
sheet = workbook

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

Untuk mengulangi baris atau rentang, iter_baris adalah sekutu Anda. Anda dapat membatasi baris dan kolom serta memproses setiap sel. Jika Anda hanya membaca, mengaktifkan mode baca-saja mengurangi memori dan meningkatkan kecepatan pada berkas besar.

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

Membaca dan menulis data secara efisien dengan pandas

Jika tujuan Anda adalah analisis dan manipulasi dataPandas adalah pilihan terbaik Anda. Pandas mengubah spreadsheet Excel menjadi DataFrame (tabel yang sangat canggih) untuk memfilter, mengagregasi, mentransformasi, dan mengekspor hasil lebih cepat daripada dengan loop manual.

import pandas as pd

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

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

Fungsi itu baca_excel memungkinkan Anda memuat lembar tertentu, kolom tertentu, atau melewati baris awal (sangat berguna untuk file dengan tajuk atau catatan yang rumit). Ini memberi Anda kontrol dan kinerja karena Anda menghindari membawa data yang tidak Anda perlukan.

# 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)

Setelah selesai mengubah DataFrame, Anda dapat mentransfernya ke Excel dengan satu metode. indeks=Salah Hindari menulis indeks sebagai kolom tambahan, sesuatu yang sangat umum ketika menyiapkan laporan bisnis.

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

Kapan menggunakan pandas dan kapan menggunakan openpyxl

Meskipun saling melengkapi, keduanya tidak mengatasi masalah yang sama: Panda bersinar dalam pemrosesan massal (penyaringan, agregasi, gabungan, pembersihan), sementara openpyxl mengatur formatnya (gaya, batas, lebar, rumus, bagan, pembuatan/penghapusan lembar, dll.). Memilih dengan bijak akan menghemat waktu Anda.

Jika Anda perlu memodifikasi ribuan sel dengan aturan sederhana (misalnya, menambahkan 10% ke kolom), dengan pandas Anda akan melakukannya dalam satu baris; di openpyxl Anda akan membutuhkannya melintasi sel dengan loop dan mengelola referensi. Namun, jika Anda ingin menerapkan format, gaya, atau menambahkan bagan perusahaan ke lembar kerja Excel akhir, OpenPyxl adalah solusinya.

Strategi yang sangat berguna adalah menggabungkan keduanya: proses dengan pandas dan, setelah tabel akhir dibuat, gunakan openpyxl untuk poles hasil akhir yang profesional (judul tebal, tengah, warna, format angka, dll.). Dengan cara ini, Anda memiliki kinerja dan hasil yang siap dipresentasikan.

Operasi umum dengan panda: pemilihan, pemfilteran, dan modifikasi

Dengan Pandas, pemilihan kolom dan pemfilteran bersyarat menjadi sangat mudah. ​​Hal ini memungkinkan Anda untuk mentransformasi kumpulan data besar dengan operasi vektorisasi tanpa menulis loop, menghasilkan kode yang lebih mudah dibaca dan lebih cepat.

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)

Untuk memeriksa hanya satu bagian, Anda dapat menggunakan .kepala() atau pengindeksan .iloc ketika Anda membutuhkan baris/kolom berdasarkan posisi. Selain itu, saat mengekspor, pandas mendukung beberapa format (CSV, Parquet, dll.), yang membuka opsi di luar Excel. Pandas juga umum dilengkapi dengan panduan tentang operasi aritmatika di Excel saat melakukan migrasi logika antara kedua lingkungan.

Membaca dan mengedit dengan openpyxl: dari sel ke rentang

Jika fokusnya adalah dokumen Excel itu sendiri, OpenPyxl memungkinkan Anda membuat buku kerja, menambahkan lembar, mengganti nama, dan menghapus lembar yang tidak diperlukan. Kontrol detail ini penting ketika Anda perlu beradaptasi dengan tata letak template atau mempertahankan rumus yang ada.

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")

Mengakses sel atau rentang individual sangatlah mudah. ​​Anda juga bisa membaca, mengubah nilai, dan menulis Kembali lagi. Untuk perubahan besar, menggunakan rentang dan memiliki struktur Excel yang jelas akan mempermudah pekerjaan Anda.

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)

Terapkan gaya, format, dan angka dengan OpenPyxl

Salah satu keuntungan dari openpyxl adalah Anda dapat mempercantik laporan Anda dengan font, batas, bantalan, dan perataan, serta format numerik (seperti dua angka desimal). Ini penting untuk laporan yang akan dilihat oleh orang awam.

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")

Anda juga bisa masuk rumus excel langsung ke dalam sel, yang akan dihitung ulang saat file dibuka di Excel. Perlu diingat Kesalahan paling umum dalam rumus Excel, yang sering muncul saat mencampur data yang dihasilkan kode dan logika spreadsheet.

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

Bagan dan visualisasi sederhana di Excel dengan OpenPyxl

Untuk menyelesaikan laporan, terkadang Anda perlu menyertakan bagan di dalam buku kerja itu sendiri. Dengan OpenPyxl, Anda dapat membuat diagram batang, garis, atau diagram lainnya dari rentang data dan menempatkannya di lokasi tertentu.

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")

Otomatisasi Laporan: Menggabungkan Pandas dan OpenPyxl

Resep yang sangat berguna adalah memproses data dengan panda (total, rata-rata, pengelompokan) dan membuang hasilnya ke dalam buku kerja baru, yang kemudian Anda hias dengan openpyxl untuk menyampaikan laporan yang diformat dengan baikPola ini cocok untuk laporan berkala.

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")

Jika Anda juga ingin memberikan koherensi visual, tambahkan batas tipis dan format angka ke kolom jumlah. Dengan cara ini, laporan Anda siap dibagikan tanpa penyesuaian manual di Excel; dan jika Anda perlu mengotomatiskan pengisian, Anda dapat mengandalkan teknik untuk mengisi data secara otomatis berdasarkan pola.

Proses umum: penyaringan, transformasi, dan penyimpanan dengan Pandas

Kasus yang umum adalah memfilter berdasarkan suatu kondisi dan menyimpan hasilnya ke berkas baru. Hal ini dilakukan dalam beberapa baris dengan pandas dan menghasilkan ideal untuk membersihkan pipa atau persiapan data untuk tim bisnis.

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")

Jika Anda perlu mempertahankan kolom asli dan membuat kolom yang dimodifikasi (misalnya, menambahkan 10% ke "Luas Total"), operasi vektorisasi menghindari loop dan meninggalkan Anda dengan DataFrame bersih dan mudah diperiksa.

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)

Jika Anda juga lebih suka menyembunyikan hasil sementara di file akhir daripada menghapusnya, pertimbangkan aturan untuk sembunyikan baris berdasarkan nilai sel dan memfasilitasi peninjauan tim.

Menghapus baris dan membersihkan data

Tugas umum lainnya adalah menghapus data berdasarkan posisi atau kondisi. Dengan Pandas, menghapus 10 baris pertama mudah dilakukan menggunakan indeks; untuk filter yang lebih kompleks, menggunakan ekspresi Boolean tanpa for-loop.

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

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

Jika pembersihan Anda bergantung pada aturan seperti "hapus baris dengan Total Area genap", Anda dapat membuat kondisi dan menerapkannya, dengan tetap menjaga kode ekspresif dan dapat dipertahankan terhadap loop dan penghitung manual. Dan jika lembarannya terlindungi, ingatlah bagaimana buka proteksi lembar Excel dengan kata sandi sebelum memodifikasinya.

Mengedit dan menghapus dengan openpyxl: sel dan baris

Saat menggunakan OpenPyxl, memodifikasi seluruh kolom melibatkan pengulangan sel. Keuntungannya adalah Anda dapat menyisipkan kolom baru, hormati tata letak asli dan simpan ke berkas lain tanpa merusak formatnya.

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")

Untuk menghapus baris berdasarkan posisi, openpyxl juga menyelesaikannya dengan panggilan, meskipun untuk kondisi lanjut Anda harus mengulangi dan memutuskan apa yang akan dihapus berdasarkan konten setiap baris.

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")

Konsolidasikan beberapa file Excel menjadi satu

Klasik: Anda memiliki lusinan file .xlsx dengan skema yang sama dan Anda ingin gabungkan mereka ke dalam satu tabel untuk menganalisisnya. Dengan pandas dan glob, Anda dapat melakukannya dalam beberapa baris, tanpa membuka setiap berkas secara manual.

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)

Pendekatan ini sempurna untuk integrasi bulanan, pelaporan multi-delegasi, atau proses apa pun tempat Anda menerima beberapa buku dengan struktur yang homogen.

Hasilkan laporan berdasarkan departemen secara otomatis

Dari file dengan penjualan global, Anda dapat melakukan segmentasi berdasarkan departemen dan menghasilkan laporan yang dipersonalisasi untuk masing-masing otomatis. Setiap file siap dibagikan dengan tim Anda.

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)

Jika Anda juga perlu memformat setiap laporan, Anda dapat memuat file yang dihasilkan dengan openpyxl dan menata tajuk, sesuaikan lebar kolom, dan tambahkan warna korporat. Anda juga dapat mengotomatiskan pengaturan file dan membuat folder dan subfolder bertingkat untuk setiap departemen.

Dasbor interaktif kecil dengan Tkinter dan pandas

Untuk pembuatan prototipe cepat, Anda dapat membuat jendela sederhana yang menampilkan kolom dan menghitung rata-rata kolom yang dipilih. Ini bukan BI yang lengkap, tetapi berfungsi untuk validasi ekspres tanpa meninggalkan Python.

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()

Untuk proyek pelaporan yang lebih kompleks, Anda mungkin ingin memindahkannya ke aplikasi web dengan Streamlit atau Dash, tetapi untuk utilitas lokal yang cepat, Tkinter dapat membantu Anda keluar dari masalah dengan kode yang sangat sedikit.

Analisis Eksploratori: Statistik dan Grafik dengan Pandas + Matplotlib

Ketika file Excel Anda berisi informasi pelanggan atau penjualan, ada baiknya untuk melihat distribusi dan hubungannya. Dengan Pandas, Anda dapat memperoleh statistik deskriptif, dan dengan Matplotlib, menghasilkan histogram dan diagram sebar sangat berguna.

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()

Dengan ini, Anda dapat mendeteksi outlier, bias, atau hubungan menarik untuk ditelusuri lebih lanjut nanti. Jika Anda ingin melaporkannya di Excel, ekspor tabel ringkasan dengan pandas dan membuat grafik dalam buku dengan OpenPyxl untuk hasil yang menyeluruh. Untuk analisis statistik spesifik, Anda juga dapat berkonsultasi dengan fungsi kuartil di Excel sebagai referensi saat migrasi indikator.

Menyimpan hasil dengan pandas dan openpyxl

Dengan Pandas, menulis ke Excel menjadi instan; plus, Anda memiliki keuntungan mengekspor ke format lain seperti CSV atau Parquet. Jika file tersebut untuk bisnis, tambahkan daun dengan detail berbeda atau data yang difilter berdasarkan segmen dalam buku yang sama.

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

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

Jika Anda menggunakan OpenPyxl, ingatlah bahwa Anda dapat membuat kolom baru dan mengisinya berdasarkan rentang. Ini cara yang mudah untuk catatan tanda ditinjau dalam laporan dengan struktur tetap.

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")

Kinerja dan praktik baik

Untuk file besar, sebaiknya batasi jumlah data yang Anda ambil ke dalam memori. Di Pandas, baca hanya kolom yang diperlukan dengan usecols dan hindari pemrosesan lembar yang tidak relevan. Di OpenPyxl, gunakan hanya_baca=Benar untuk membaca dan tulis_saja=Benar untuk menulis.

Waspadai tipe data: Terkadang Excel menyimpan angka sebagai teks, dan hal itu dapat merusak filter dan agregasi. Saat memuat dengan pandas, Anda dapat menentukan tipe data atau menormalkan kolom setelah membaca untuk menghindari kejutan dengan tanggal, jumlah atau ID.

Dengan rumus Excel, ingatlah bahwa tidak semua fungsi didukung secara setara oleh pustaka; kemungkinan besar dihitung ulang saat dibuka di ExcelJika Anda memerlukan nilai tetap, evaluasi dengan pandas terlebih dahulu dan tulis kembali angka yang telah dihitung sebelumnya.

Mengenai kompatibilitas, prioritaskan file . Xlsx (format modern) dan uji skrip Anda dengan versi Excel yang berbeda ketika pengguna Anda memiliki lingkungan yang heterogen. Hal ini mengurangi masalah akibat fungsi atau fitur yang tidak didukung dalam versi lama.

Sebagai catatan akhir, pikirkan tentang jalur pipa yang dapat direproduksi: tetapkan dependensi (Puisi, requirements.txt), dokumentasikan parameter Anda (sheet_name, usecols, skiprows) dan tambahkan log minimal untuk debugging. Ini akan menghemat waktu Anda saat menskalakan proyek atau mengubah sumber data.

Jika Anda sudah sampai sejauh ini, Anda sudah menguasai membaca, memproses, dan menulis Excel dengan pandas dan openpyxl, Anda tahu ketika setiap alat sesuai dan cara menggabungkannya untuk laporan profesional. Mulai sekarang, Anda akan melihat spreadsheet Anda sebagai sumber dan tujuan dalam alur kerja yang andal, otomatis, dan jauh lebih cepat.