Excelピボット作成は「大量データを一瞬で集計し、分析レポートを自動生成する」ための最強スキル
Excel業務自動化の中でも ピボットテーブルの自動生成 は効果が非常に大きいです。
なぜなら、実務では次のような場面が多いからです。
- 大量データを毎回手作業で集計している
- 店舗別・商品別・担当者別などの集計を自動化したい
- 月次レポートを毎回同じ形式で作りたい
- データ更新 → ピボット更新を自動化したい
ただし openpyxl は Excel のピボットテーブルを完全には作れません。
Excel内部の「PivotCache」や「PivotTableDefinition」を扱う必要があり、
openpyxl はこれを部分的にしかサポートしていません。
しかし、Pythonでは pandas + ExcelWriter（openpyxl） を使うことで
「Excelにピボットテーブルを自動生成する」ことができます。
ここでは初心者向けに、
openpyxlでの基本構造 → pandasでの実務テンプレ
の順で丁寧に解説します。
openpyxlでのピボット作成（基礎）
※ Excel のピボットを完全に再現するのは難しいため、
openpyxl では「ピボットの定義ファイル」を作る形になります。
最小限のピボットテーブル作成コード
from openpyxl import Workbook
from openpyxl.pivot.table import PivotTable
from openpyxl.pivot.cache import PivotCache, PivotCacheDefinition
wb = Workbook()
ws = wb.active
# データ作成
data = [
["商品", "数量", "売上"],
["A", 10, 1000],
["B", 5, 500],
["A", 3, 300],
]
for row in data:
ws.append(row)
# ピボットキャッシュ
cache = PivotCache(cacheId=1)
cache_def = PivotCacheDefinition(cacheId=1, recordCount=3)
wb._pivots.append(cache)
wb._pivot_caches.append(cache_def)
# ピボットテーブル
pivot = PivotTable(name="Pivot1", cache=cache, ref="E5")
pivot.add_field("商品")
pivot.add_field("数量")
pivot.add_field("売上")
ws.add_pivot(pivot)
wb.save("pivot_openpyxl.xlsx")
深掘りポイント
- openpyxl のピボットは Excelの完全互換ではない
- 実務で使うには制限が多い
- そのため、実務では pandas で集計 → Excelに書き込む が主流
実務で最も使われる方法：pandasでピボット集計 → Excelに書き込む
pandas の pivot_table を使うと Excel と同じ集計ができる
import pandas as pd
df = pd.read_excel("sales.xlsx")
pivot = pd.pivot_table(
df,
index="商品",
values="売上",
aggfunc="sum"
)
pivot.to_excel("pivot_report.xlsx", sheet_name="集計")
深掘りポイント
pivot_table()は Excel のピボットと同じ動作- index（行）、columns（列）、values（値）、aggfunc（集計方法）を指定
- Excelに書き込むだけで「ピボット集計済みの表」が完成
実務テンプレ①：商品別 × 月別 売上ピボット
A列：日付、B列：商品、C列：売上 のデータを集計
import pandas as pd
df = pd.read_excel("sales.xlsx")
df["月"] = df["日付"].dt.month
pivot = pd.pivot_table(
df,
index="商品",
columns="月",
values="売上",
aggfunc="sum",
fill_value=0
)
pivot.to_excel("pivot_sales.xlsx", sheet_name="商品×月")
ポイント
- 月別集計は実務で最も多い
fill_value=0で空欄を0にする
実務テンプレ②：担当者別 × 商品別 数量ピボット
pivot = pd.pivot_table(
df,
index="担当者",
columns="商品",
values="数量",
aggfunc="sum",
fill_value=0
)
pivot.to_excel("pivot_staff.xlsx", sheet_name="担当者×商品")
ポイント
- 担当者別の集計は営業部門でよく使う
- 商品別の比較が一目で分かる
実務テンプレ③：複数集計（数量と売上を同時に集計）
pivot = pd.pivot_table(
df,
index="商品",
values=["数量", "売上"],
aggfunc={"数量": "sum", "売上": "sum"}
)
pivot.to_excel("pivot_multi.xlsx", sheet_name="複数集計")
ポイント
- Excelの「値フィールドを複数追加」と同じ
- 商品別の総数量・総売上が一瞬で出せる
実務テンプレ④：Excelテンプレートにピボット結果を書き込む
from openpyxl import load_workbook
pivot = pd.pivot_table(
df,
index="商品",
values="売上",
aggfunc="sum"
)
wb = load_workbook("template.xlsx")
ws = wb["集計"]
for r_idx, row in enumerate(pivot.itertuples(), start=2):
ws.cell(row=r_idx, column=1, value=row.Index)
ws.cell(row=r_idx, column=2, value=row.売上)
wb.save("pivot_filled.xlsx")
ポイント
- テンプレートに書き込むことで「フォーマット付きレポート」が作れる
- 実務で最も使われるパターン
Excelピボット作成を業務で設計するときの視点
- openpyxl のピボットは制限が多く、実務では pandas が最適
- pivot_table() は Excel のピボットと同じ感覚で使える
- テンプレートに書き込むと「フォーマット付きレポート」が自動生成できる
- 月次・担当者別・商品別などの集計はすべて自動化可能