この記事でわかること

  • 1つのExcelの表を、担当者などの列の値で分ける考え方
  • pandasのgroupbyとExcelWriterで、別々のシートに保存するコード
  • 分けた後の行数と合計を、元の表と見比べて確かめる方法
  • 架空の表で試してわかった、つまずきやすいところ

売上や経費の一覧を、担当者ごとや月ごとのシートに分けて配りたいことがあります。フィルターで絞り込んでコピーし、新しいシートに貼り付ける作業を人数分くり返すと、貼り付け漏れや、ほかの人の行が混ざるまちがいが起きやすくなります。この記事では、pandasで表を分けて、1つのブックの別々のシートに保存する手順を紹介します。

関数の説明はpandas 3.0.6の公式の資料で確かめ、コードは架空の売上の表を使って実際に動かしました。

先に結論: 分ける列を決め、行数と合計を見比べてから使う

先に結論を書くと、分ける列を1つ決め、その列の値ごとに表を分けて、シートの名前に値を使って保存します。保存した後は、シートごとの行数と合計を足した値が、元の表と同じかを見比べます。 元の表とは別の名前のファイルに保存し、元の表には手を加えません。

作業 担当
分ける列を決める 人
値ごとに分ける Python
シートに保存 Python
合計を見比べる 人

例にする売上の表

この記事では、次のような架空の売上の表(sales_all.xlsx)を例にします。伝票番号・担当・金額の3つの列があり、8行あります。

  • 担当: 佐藤・鈴木・高橋の3人
  • 担当が空の行: 1行(まだ担当が決まっていない伝票)
  • 金額の合計: 13,000

この表を、担当者ごとのシートに分けたブック(sales_by_person.xlsx)を作ります。月ごとのシートを1つにまとめる逆の操作は、PythonでExcelの複数シートを読み込んでまとめる手順で紹介しています。

手順: 担当者ごとにシートを分けて保存する

次のコードは、sales_all.xlsx を読み、担当の列の値ごとに表を分けて、sales_by_person.xlsx の別々のシートに保存します。担当が空の行は「未入力」というシートにまとめます。最後に、元の表と分けた後の金額の合計を表示します。

# 担当者ごとに、売上の表を別々のシートに分けて保存する
import pandas as pd

sales = pd.read_excel("sales_all.xlsx")
print("元の表", len(sales), "行 金額の合計", sales["金額"].sum())

total = 0
with pd.ExcelWriter("sales_by_person.xlsx") as writer:
    for person, part in sales.groupby("担当", dropna=False, sort=False):
        name = "未入力" if pd.isna(person) else str(person)
        part.to_excel(writer, sheet_name=name, index=False)
        total += part["金額"].sum()
        print(name, len(part), "行 金額の合計", part["金額"].sum())

print("分けた後の合計", total)
  • pd.ExcelWriter: pandasの資料では、表をExcelのシートに書き込むためのクラスで、コンテキストマネージャーとして使うとしています。コードでは、字下げした範囲を抜けたところで保存されます
  • sheet_name: 書き込むシートの名前です。pandasの資料では、複数のシートに書き込むときは、ExcelWriterを作り、シートごとに別々のsheet_nameを指定するとしています
  • groupby("担当", ...): 担当の列の値ごとに、行をまとまりに分けます
  • dropna=False: pandasの資料では、初期の設定(True)では分ける列が空の行は外れ、Falseにすると空の値も1つのまとまりとして扱われるとしています
  • sort=False: pandasの資料では、Falseにすると、まとまりが元の表に出てきた順に並ぶとしています。シートも、元の表で担当者が出てきた順になります
  • index=False: pandasの資料では、to_excelの初期の設定では行の番号(index)も書き込まれます。Falseにすると、元の表と同じ列だけが保存されます

架空の表で試した結果

架空の表で試すと、「佐藤・鈴木・高橋・未入力」の4つのシートができました。行数は佐藤が3行、鈴木が2行、高橋が2行、未入力が1行で、足すと元の表の8行と同じでした。金額の合計も、シートごとの3,500・5,900・3,000・600を足した13,000と、元の表の合計が同じでした。どのシートにも、伝票番号・担当・金額の3つの列だけが入っていて、元の sales_all.xlsx は変わりませんでした。

検証に使ったのは、作業PCのPython 3.12.10と、pandas 3.0.6・openpyxl 3.1.5です。

分けた後に確かめること

分けたブックは、次の3つを見てから配ります。

  • 行数: シートごとの行数を足した数が、元の表の行数と同じか
  • 合計: シートごとの金額の合計を足した値が、元の表の合計と同じか
  • シートの名前: 分けたい担当者が全員そろっていて、「未入力」のシートに入った行の理由が分かるか

「未入力」のシートに行があるときは、元の表で担当を入れてから、もう一度コードを実行します。担当者ごとの件数と合計だけが欲しいときは、シートに分けずに集計する方法が合います。手順はPythonでCSVを集計する手順で紹介しています。

Pythonで分けるかの判断

迷ったときは、「同じ分け方を、くり返すか」で決めます。毎月の表を同じ担当者で分けて配るなら、コードにしておくと、毎回同じ確かめ方で分けられます。一度だけで、分ける先が2〜3人なら、Excelのフィルターでコピーして、行数を見比べるほうが早いこともあります。

分けたシートをファイルごとに配るときや、配った後に集めたファイルをまとめ直すときの選び方は、Excelの複数ファイルを1つにまとめる方法が参考になります。

つまずきやすいところ

  • 空の担当の行が消える: groupbyの初期の設定のままだと、担当が空の行はどのシートにも入りません。架空の表でも、dropna=Falseを付けないと、8行のうち7行しか分けられませんでした
  • シートの名前に使えない文字: 架空の表で「営業/東京」という名前のシートを書こうとすると、「Invalid character / found in sheet title」というエラーで止まりました。名前に「/」などが入るときは、別の文字に置き換えます
  • 長すぎるシートの名前: 32文字の名前のシートを書くと、31文字を超えると読めないアプリがあるという警告が出ました。長い名前は短くしてから使います
  • 同じ名前のファイルを上書きする: pandasの資料では、ExcelWriterは初期の設定では書き込みのモードで、同じ名前のファイルがあると上書きされます。架空の表で試したときも、前からあったシートは残りませんでした。元の表と同じ名前で保存しないようにします

まとめ

  • 分ける列を1つ決め、groupbyで値ごとに分けて、ExcelWriterで別々のシートに保存します
  • 担当が空の行も残すときは dropna=False を付け、シートの名前に使えない文字は置き換えます
  • 保存した後は、シートごとの行数と合計を足した値を、元の表と見比べます
  • 元の表とは別の名前のファイルに保存し、元の表は書き換えません