Python | 自動化：Excel テンプレ自動集計

Python
スポンサーリンク

概要（「決まったExcel」ならPythonで一気に集計できる）

毎月・毎店舗・毎担当者から、同じ形式のExcelが集まってきて、
それを手でコピペして集計していませんか。

「テンプレが決まっているExcel」なら、Python＋pandasで

  • 複数ファイルの読み込み
  • 必要なセル・シートだけの取得
  • 1枚の集計表への結合
  • 集計結果をExcelテンプレに自動出力

までを一気に自動化できます。

ここでは「毎月の売上テンプレを自動集計する」という具体的な例で、
仕組みと実装を、初心者向けにかみ砕いて説明します。

基本の流れ（テンプレ分析 → 読み込み → 結合 → 集計 → 書き出し）

どんなテンプレを想定するか

例えば、こんなExcelファイルを毎店舗から回収しているとします。

  • ファイル名: store_A.xlsx, store_B.xlsx など
  • シート名: 入力（全店舗共通）
  • 行1: 項目名（ヘッダー）
  • A列: 日付
  • B列: 商品カテゴリ
  • C列: 売上金額

イメージ：

日付カテゴリ売上
2025/01/01A10000
2025/01/01B8000
………

この「同じ形」さえ守られていれば、Pythonからは非常に扱いやすくなります。

必要なライブラリ

Python側では主に pandas を使います。Excelを扱うときは、エンジンとして openpyxl を一緒に入れておくのが無難です。

pip install pandas openpyxl

複数のテンプレExcelを読み込んで「縦にくっつける」

1ファイルを pandas で読む基本

まずは1店舗分を読み込むイメージから。

import pandas as pd

path = "data/store_A.xlsx"

df = pd.read_excel(
    path,
    sheet_name="入力",    # シート名を指定
    engine="openpyxl"    # ほぼおまじない
)

print(df.head())
Python

ここで大事なのは「テンプレのどのシート・どの行から・どの列を読むか」を、最初にしっかり決めておくことです。

もし1行目がタイトルで、2行目からヘッダー、3行目からデータ…などの場合は、ヘッダーの行を指定できます。

df = pd.read_excel(
    path,
    sheet_name="入力",
    header=1             # 0始まりなので、「2行目がヘッダー」の意味
)
Python

複数ファイルをループでまとめて読み込む

次に、フォルダ内にある複数のExcelファイルを全部読むパターンです。
globで「*.xlsx」を拾って、それをforループで回します。

from pathlib import Path
import pandas as pd

folder = Path("data")   # Excelが入っているフォルダ
files = sorted(folder.glob("store_*.xlsx"))

all_list = []

for f in files:
    df = pd.read_excel(f, sheet_name="入力", engine="openpyxl")
    df["店舗"] = f.stem.replace("store_", "")   # ファイル名から店舗名を付与
    all_list.append(df)

all_data = pd.concat(all_list, ignore_index=True)
print(all_data.head())
Python

ポイントは、ファイルごとの差（店舗名や担当者名）を、
「列として追加しておく」ことです。後の集計で効きます。

pandas で集計（groupby）して「欲しい形」にする

店舗×カテゴリ×月ごとの売上集計

例えば「店舗別・カテゴリ別・月別の売上合計」が欲しいとします。
日付から「月」を取り出し、groupbyで集計します。

all_data["日付"] = pd.to_datetime(all_data["日付"])
all_data["月"] = all_data["日付"].dt.to_period("M").dt.to_timestamp()

summary = (
    all_data
    .groupby(["店舗", "月", "カテゴリ"], as_index=False)["売上"]
    .sum()
)

print(summary.head())
Python

ここで重要なのは

  • 日付列を必ず datetime 型に変換する（to_datetime）
  • groupby のキーを「どの粒度で集計したいか」を意識して決める

という2点です。

集計結果を「表形式」に整える（ピボット）

店舗を行、カテゴリを列、セルに売上…というExcelらしい形にしたいときは、pivot_table を使います。

pivot = summary.pivot_table(
    index=["店舗", "月"],       # 行方向
    columns="カテゴリ",        # 列方向
    values="売上",             # セルの値
    aggfunc="sum",            # 念のため
    fill_value=0              # 欠損は0で埋める
)

print(pivot.head())
Python

これで、「店舗と月の組み合わせごとに、カテゴリ別売上が横一列に並んだ表」ができます。

集計結果を Excel テンプレに自動出力する

「集計結果用テンプレ」を用意するイメージ

多くの現場では、集計用のExcelにもフォーマット（罫線や色、固定セルなど）が決まっています。

例：

  • template_summary.xlsx というテンプレファイルがあり、
  • 集計 シートの B3 から、店舗×カテゴリの表を書き込む

とします。

一番シンプルな「テンプレを無視して上書き」パターン

フォーマットにこだわらず、「純粋な集計結果だけをExcelに出したい」なら、この方法が一番簡単です。

pivot.to_excel("output/summary_simple.xlsx", sheet_name="集計")
Python

ただしこの方法だと、テンプレの罫線やフォーマットは全て無視され、pivotの内容だけが書かれます。

openpyxl を使って「テンプレに書き込む」

テンプレの装飾を保ちつつ、中身だけ差し替えたい場合は、openpyxl で Workbook を開き、そのセルに pandas の値を流し込みます。

from openpyxl import load_workbook

# テンプレを読み込む
wb = load_workbook("template/template_summary.xlsx")
ws = wb["集計"]

# pivot を普通の DataFrame に整える（マルチインデックスをリセット）
pivot_reset = pivot.reset_index()

start_row = 3
start_col = 2  # B列

# ヘッダー行（カテゴリ名など）を書き込む
for j, col_name in enumerate(pivot_reset.columns, start=start_col):
    ws.cell(row=start_row, column=j, value=str(col_name))

# データ部分を書き込む
for i, (_, row) in enumerate(pivot_reset.iterrows(), start=start_row + 1):
    for j, val in enumerate(row, start=start_col):
        ws.cell(row=i, column=j, value=val)

# 保存
wb.save("output/summary_from_template.xlsx")
Python

ここでポイントになるのは次の3つです。

  1. 「テンプレのセル位置」をコードで固定しておく（start_row, start_col）
  2. マルチインデックス（店舗・月とカテゴリ）を一度 reset_index して、
    「列見出し」と「値」をシンプルな形に戻してから書き込む
  3. ws.cell(row=..., column=...) で1セルずつ書くので、
    罫線や色など「セルの見た目」はテンプレ側が維持される

重要な設計ポイントの深掘り（失敗しやすい所を先に潰す）

1. テンプレの変更に耐えられるようにする

現場あるあるとして、「Excelテンプレが途中で変わる」があります。
列名が変わる、列の順番が変わる、シート名が変わる…。

防ぐには、

  • 読み込み時は「列の順番」ではなく「列名」で指定する
  • シート名は一定にしてもらう（変更時はコードも変更）
  • 列名が変わったら、読み込み直後に rename で統一する

という運用・コードの両面からの対策が効きます。

df = pd.read_excel(path, sheet_name="入力", engine="openpyxl")

df = df.rename(columns={
    "売上": "売上金額",   # 古いテンプレにも対応
    "売上金額": "売上金額"
})
Python

2. データ型（数値・文字・日付）を必ず揃える

Excel由来のデータは、同じ列なのに

  • 数値と文字列が混ざる
  • 日付が文字のまま入っている

などが頻発します。これをそのまま集計すると、
「思ったように groupby されない」「ソートが変になる」などの事故に繋がります。

対策としては、

  • 数値列は pd.to_numeric(errors="coerce") で数値化＋変換不可は NaN
  • 日付列は pd.to_datetime(errors="coerce") で datetime 化
  • 集計前に dtypes を確認して、意図通りかチェック

を型として徹底すると安心です。

df["売上"] = pd.to_numeric(df["売上"], errors="coerce")
df["日付"] = pd.to_datetime(df["日付"], errors="coerce")
Python

3. ファイル名からメタ情報を取る設計

店舗名・拠点名・月などが、Excelの中にも書いてある場合でも、
「ファイル名からも取っておく」と後で助かります。

例）store_A_2025-01.xlsx から、店舗A・2025-01月を取る。

import re

f = Path("data/store_A_2025-01.xlsx")
m = re.match(r"store_(.+)_(\d{4}-\d{2})\.xlsx", f.name)
store = m.group(1)
month = m.group(2)
Python

Excel側の入力ミス（店舗名のタイプミスなど）があっても、
ファイル名からの情報を信頼するようにしておけば、
データの品質が安定しやすくなります。

4. フォルダ構成とログ出力

実運用では、

  • data/raw：元のExcel
  • data/processed：処理済みの中間ファイル（必要なら）
  • output：集計結果・レポートExcel
  • template：テンプレ置き場

のようにフォルダを分けておくとトラブルが少なくなります。

さらに、「どのファイルを読んで」「何件レコードがあり」「エラーはあったか」
を print やログに出しておくと、後から原因追跡がしやすくなります。

まとめ（「テンプレの形を固定 → pandasで読み込み → groupby → Excelへ書き込み」を型にする）

Excelテンプレを使った自動集計の肝は、

  • テンプレのシート名・列名・セル位置を“仕様”として固定する
  • pandas で「複数ファイルを縦結合→groupby→pivot」で欲しい形に整える
  • 集計結果を to_excel か、openpyxl経由でテンプレに書き戻す
  • データ型（数値・日付）と、ファイル名からのメタ情報をきちんと揃える

この型さえ押さえれば、毎月の「地獄のコピペ作業」を、
ワンクリックや1コマンドで終わらせることができます。

スポンサーリンク
タイトルとURLをコピーしました