【Python】openpyxlを使ってExcelのデータを集計しグラフを作成するサンプルコードを解説

開発環境

• Python version: python 3.10.11

Pythonのopenpyxlとは

openpyxl（オープンパイエクセル）とは、Pythonというプログラミング言語を使って、Excelファイル（.xlsx形式）を操作するためのライブラリです。 システムエンジニアとして、Excelファイルをプログラムで自動的に処理できることは、データの集計やレポート作成、あるいは日々の業務の効率化において非常に役立ちます。

主にopenpyxlでは、以下のようなことができます。

• Excelファイルの作成と読み込み: 新しいExcelファイルを作ったり、既に存在するExcelファイルの内容をPythonプログラムに取り込んだりすることができます。
• セルの操作: Excelシート内の個々のマス目である「セル」に対して、値の書き込みや読み取り、文字の色や背景色などの書式設定を行うことができます。
• シートの追加・削除: 一つのExcelファイルに含まれる複数の「シート」（タブで切り替える部分）を追加したり、不要なシートを削除したりすることが可能です。
• グラフや図形の挿入: データを視覚的に表現するためのグラフや、画像をExcelシートに挿入することもできます。
• ワークブックのプロパティの操作: Excelファイル全体の情報、例えばファイルの作成者名やタイトル、最終更新者といった情報を設定したり、取得したりすることができます。
• 式の計算: Excelファイルに記述された計算式（例えば「=SUM(A1:A5)」のようなもの）をプログラムから設定したり、その計算結果を取得したりすることができます。

公式ドキュメント：https://openpyxl.readthedocs.io/

Pythonのopenpyxlの使い方

openpyxlは、Pythonを使ってExcelファイルを読み込んだり、書き込んだりするための便利なライブラリです。Excelファイルを自動で操作したい場合や、PythonのプログラムでExcelのデータを扱いたい場合に利用します。 システムエンジニアとして働く上で、Excelファイルはデータの管理やレポート作成など、さまざまな場面で利用されるため、PythonでExcelを操作するスキルは非常に役立ちます。 この解説では、openpyxlの基本的な使い方についてご紹介します。

openpyxlをインストール

openpyxlはPythonに標準で備わっている機能ではなく、外部から追加するライブラリです。 そのため、openpyxlを使用する前に、ご自身のパソコンにインストールする必要があります。 インストールには、Pythonのパッケージ管理ツールであるpipコマンドを使用します。 コマンドプロンプトやターミナルといった画面を開き、以下のコマンドを実行してください。

Copy
1pip install openpyxl

このコマンドを実行すると、openpyxlライブラリがあなたのPython環境に導入されます。 これで、Pythonプログラムからopenpyxlを使ってExcelファイルを操作するための準備が完了しました。

Pythonのopenpyxlでデータを集計してグラフを作成する

この章では、Pythonのopenpyxlライブラリを使って、Excelファイルの売上データから月ごとの合計金額を集計し、その結果を新しいシートに書き出して線グラフを作成する方法を学びます。Excelデータの自動処理は、システムエンジニアの業務で非常に役立つスキルです。

今回使用するExcelファイル

今回はsummary.xlsxという名前の売上一覧のExcelファイルを使用します。このファイルは、架空の企業の売上データが記録されていると想定してください。

summary.xlsxの「Sheet1」には、以下の項目が格納されています。1行目は各項目の名前を示す「ヘッダー」として使われ、2行目以降に実際の売上データが入っています。

列	項目
A	SheetName（シート名）
B	CompanyNmae（会社名）
C	BillingDate（請求日）
D	Product（商品名）
E	Quantity（数量）
F	UnitPrice（単価）
G	TotalAmount（合計金額）

例えば、「C」列には「請求日」、「G」列には「合計金額」といったデータが入っており、これらの情報を利用して集計とグラフ作成を行います。

集計とグラフ作成のサンプルコード

下記はopenpyxlライブラリを使用してsummary.xlsxの売上データを請求月ごとに集計し、集計表と線グラフを作成してresults.xlsxという新しいExcelファイルに保存するサンプルコードです。このコードを実行することで、手作業で行っていたデータ集計とグラフ作成のプロセスを自動化できます。

Copy
1from openpyxl import load_workbook
2from openpyxl.chart import LineChart, Reference
3from collections import defaultdict
4
5# ワークブックの読み込み
6file_path = 'summary.xlsx'
7wb = load_workbook(file_path)
8
9# デフォルトのアクティブなシートにアクセス
10sheet = wb['Sheet1']
11
12# シート内の全てのセルの値を取得して月ごとのデータをまとめる
13campany_invoice_totals = defaultdict(int)
14
15for row in sheet.iter_rows(min_row=2, min_col=1, max_row=sheet.max_row, max_col=sheet.max_column):
16    invoice_date = row[2].value.strftime("%Y年%m月")
17    total_amount = row[6].value
18
19    # 請求月ごとに合計金額を加算
20    campany_invoice_totals[invoice_date] += total_amount
21
22# 集計表がなければ新しいシートを作成、集計表があれば既存のデータをクリアして書き込み
23sheet_exists = "集計表" in wb.sheetnames
24
25if sheet_exists:
26    new_sheet = wb["集計表"]
27    new_sheet.delete_rows(1, new_sheet.max_row)
28else:
29    new_sheet = wb.create_sheet("集計表")
30
31# 集計結果を新しいシートに書き込む
32new_sheet.append(["請求月", "合計金額"])
33
34for invoice_date, total_amount in campany_invoice_totals.items():
35    new_sheet.append([invoice_date, total_amount])
36
37# 線グラフを作成
38chart = LineChart()
39chart.title = "請求月ごとの合計金額"
40chart.x_axis.title = "請求月"
41chart.y_axis.title = "合計金額"
42
43# データの範囲を指定
44data_rows = Reference(new_sheet, min_col=2, min_row=1, max_col=2, max_row=new_sheet.max_row)
45categories = Reference(new_sheet, min_col=1, min_row=2, max_row=new_sheet.max_row)
46
47# グラフにデータを追加
48chart.add_data(data_rows, titles_from_data=True)
49chart.set_categories(categories)
50
51# グラフを挿入
52new_sheet.add_chart(chart, "A" + str(new_sheet.max_row + 2))
53
54# ワークブックを保存
55wb.save("results.xlsx")

作成したファイルは、以下のコマンドをターミナルやコマンドプロンプトで実行することで、Pythonプログラムとして動作させることができます。

Copy
1python excel.py

上記のコマンドを実行する前に、このPythonコードをexcel.pyというファイル名で保存しておく必要があります。

ライブラリをインポートする

まず、PythonのプログラムでExcelファイルを操作するために必要なライブラリをインポートします。

Copy
1from openpyxl import load_workbook
2from openpyxl.chart import LineChart, Reference
3from collections import defaultdict

• load_workbookは、既存のExcelファイル（ワークブック）をPythonプログラムで読み込むために使用します。
• LineChartは線グラフを作成するためのクラスで、Referenceはグラフのデータ範囲をExcelのセル範囲のように指定するために使用します。これらはopenpyxl.chartモジュールに含まれています。
• defaultdictは、Pythonの標準ライブラリであるcollectionsモジュールに含まれる特別な辞書（ディクショナリ）です。通常の辞書では、存在しないキーにアクセスしようとするとエラーが発生しますが、defaultdictは、アクセスされたキーが存在しない場合に、あらかじめ指定された型の初期値を自動的に作成してくれます。今回の場合はdefaultdict(int)と指定しているため、存在しないキーにアクセスすると自動的に初期値0が設定され、集計処理をより簡単に行うことができます。

Excelファイルを読み込む

次に、処理したいExcelファイル（summary.xlsx）をPythonプログラムで読み込み、その中の特定のシートにアクセスします。

Copy
1file_path = 'summary.xlsx'
2wb = load_workbook(file_path)
3
4sheet = wb['Sheet1']

1. file_path = 'summary.xlsx'：処理対象のExcelファイル名をfile_pathという変数に代入しています。
2. wb = load_workbook(file_path)：load_workbook関数を使って、summary.xlsxファイルを読み込みます。このとき、ファイル全体をメモリ上に展開したものがwbという変数（ワークブックオブジェクト）に格納されます。
3. sheet = wb['Sheet1']：読み込んだワークブックwbの中から、シート名「Sheet1」を指定してアクセスし、そのシートをsheetという変数（ワークシートオブジェクト）に代入しています。これで、Sheet1のデータに対して操作ができるようになります。

請求月ごとに合計金額を集計する

ここからは、Sheet1のデータを1行ずつ読み込み、請求月ごとに合計金額を集計していきます。

Copy
1campany_invoice_totals = defaultdict(int)
2
3for row in sheet.iter_rows(min_row=2, min_col=1, max_row=sheet.max_row, max_col=sheet.max_column):
4    invoice_date = row[2].value.strftime("%Y年%m月")
5    total_amount = row[6].value
6
7    campany_invoice_totals[invoice_date] += total_amount

1. campany_invoice_totals = defaultdict(int)：請求月ごとの合計金額を保存するためのdefaultdictを作成します。キーには請求月（例: "2024年04月"）、値にはその月の合計金額が整数型（int）で格納されます。
2. for row in sheet.iter_rows(...)：iter_rowsは、openpyxlのシートオブジェクトが持つメソッドで、指定した範囲の行を1行ずつ取り出して処理するために使います。
   • min_row=2：Excelファイルの1行目はヘッダーなので、データが始まる2行目から読み込みを開始するように指定しています。
   • min_col=1：A列（1列目）からデータを取得するように指定しています。
   • max_row=sheet.max_row：シートの最終行までデータを取得するように指定しています。これにより、データが増減しても自動的に対応できます。
   • max_col=sheet.max_column：シートの最終列までデータを取得するように指定しています。
3. invoice_date = row[2].value.strftime("%Y年%m月")：
   • rowは、現在の行のすべてのセルを要素とするリストのようなオブジェクトです。
   • row[2]は、0から始まるインデックスで3番目の要素、つまりExcelのC列（請求日）のセルを指します。.valueでそのセルの値を取得します。
   • 取得した請求日（日付オブジェクト）を.strftime("%Y年%m月")という書式設定で、「2024年04月」のような「年と月」の形式の文字列に変換しています。
4. total_amount = row[6].value：同様に、row[6]は7番目の要素、つまりExcelのG列（合計金額）のセルを指し、その値を取得しています。
5. campany_invoice_totals[invoice_date] += total_amount：
   • campany_invoice_totals辞書に、invoice_date（請求月）をキーとしてアクセスします。
   • もしその請求月が初めて登場する場合でも、defaultdict(int)のおかげで自動的に値が0で初期化されます。
   • その月の既存の合計金額に、現在の行のtotal_amountを加算していくことで、請求月ごとの合計金額を集計しています。

集計表のシートを用意する

集計結果を書き込むための「集計表」という新しいシートを用意します。この際、もしすでに「集計表」というシートが存在する場合は、既存のデータを削除してから新しい集計結果を書き込むようにします。

Copy
1sheet_exists = "集計表" in wb.sheetnames
2
3if sheet_exists:
4    new_sheet = wb["集計表"]
5    new_sheet.delete_rows(1, new_sheet.max_row)
6else:
7    new_sheet = wb.create_sheet("集計表")

1. sheet_exists = "集計表" in wb.sheetnames：
   • wb.sheetnamesは、現在のワークブックに含まれるすべてのシート名のリストです。
   • このリストの中に文字列「集計表」が含まれているかどうかをチェックし、その結果をsheet_exists変数に真偽値（True/False）で格納します。
2. if sheet_exists:：もし「集計表」というシートが存在する場合の処理です。
   • new_sheet = wb["集計表"]：既存の「集計表」シートにアクセスします。
   • new_sheet.delete_rows(1, new_sheet.max_row)：そのシートの1行目から最終行まですべてのデータを削除します。これにより、以前の集計結果が残っていてもクリアされ、常に最新のデータが書き込まれるようになります。
3. else:：もし「集計表」というシートが存在しない場合の処理です。
   • new_sheet = wb.create_sheet("集計表")：新しく「集計表」という名前のシートを作成し、new_sheet変数に代入します。

この条件分岐により、プログラムを何回実行しても、常に適切な状態で集計結果を書き込めるようになります。

集計結果を書き込む

用意した「集計表」シートに、先ほど集計した請求月ごとの合計金額を書き込みます。

Copy
1new_sheet.append(["請求月", "合計金額"])
2
3for invoice_date, total_amount in campany_invoice_totals.items():
4    new_sheet.append([invoice_date, total_amount])

1. new_sheet.append(["請求月", "合計金額"])：
   • appendメソッドは、指定されたリストをシートの次の利用可能な行（つまり、現在の最終行のすぐ下）に新しい行として追加します。
   • ここではまず、集計表のヘッダーとして「請求月」と「合計金額」というテキストを1行目に追加しています。
2. for invoice_date, total_amount in campany_invoice_totals.items():：
   • campany_invoice_totals.items()は、集計結果が格納されているdefaultdictから、キー（invoice_date、請求月）とその値（total_amount、合計金額）のペアを1つずつ取り出します。
3. new_sheet.append([invoice_date, total_amount])：
   • ループの各回で、取得したinvoice_dateとtotal_amountを要素とするリストをnew_sheetに追加します。これにより、「請求月」列と「合計金額」列に集計されたデータが順に書き込まれていきます。

線グラフを作成して挿入する

集計結果が書き込まれたシートのデータを使って、線グラフを作成し、そのグラフをシート内に挿入します。

Copy
1chart = LineChart()
2chart.title = "請求月ごとの合計金額"
3chart.x_axis.title = "請求月"
4chart.y_axis.title = "合計金額"
5
6data_rows = Reference(new_sheet, min_col=2, min_row=1, max_col=2, max_row=new_sheet.max_row)
7categories = Reference(new_sheet, min_col=1, min_row=2, max_row=new_sheet.max_row)
8
9chart.add_data(data_rows, titles_from_data=True)
10chart.set_categories(categories)
11
12new_sheet.add_chart(chart, "A" + str(new_sheet.max_row + 2))

1. chart = LineChart()：LineChartクラスのインスタンスを作成し、chart変数に代入します。これで線グラフのオブジェクトが作成されます。
2. chart.title = "請求月ごとの合計金額"：グラフ全体のタイトルを設定します。
3. chart.x_axis.title = "請求月"：横軸（X軸）のタイトルを「請求月」に設定します。
4. chart.y_axis.title = "合計金額"：縦軸（Y軸）のタイトルを「合計金額」に設定します。
5. data_rows = Reference(new_sheet, min_col=2, min_row=1, max_col=2, max_row=new_sheet.max_row)：
   • Referenceオブジェクトは、グラフのデータとして使用するExcelシート上のセル範囲を指定するために使います。
   • ここでは、「集計表」シート（new_sheet）のB列（min_col=2, max_col=2）の1行目から最終行まで（min_row=1, max_row=new_sheet.max_row）を、グラフのデータ系列（この場合は合計金額）として指定しています。
6. categories = Reference(new_sheet, min_col=1, min_row=2, max_row=new_sheet.max_row)：
   • 同様にReferenceを使って、グラフの項目（カテゴリー）として使用するセル範囲を指定します。
   • ここでは、「集計表」シートのA列（min_col=1）の2行目から最終行まで（min_row=2, max_row=new_sheet.max_row）を、グラフの横軸のラベル（この場合は請求月）として指定しています。1行目はヘッダーなので、2行目から開始しています。
7. chart.add_data(data_rows, titles_from_data=True)：
   • chartオブジェクトに、data_rowsで指定したデータを追加します。
   • titles_from_data=Trueとすることで、data_rowsの範囲の1行目（つまり「合計金額」というセル）を、グラフの凡例（系列名）として自動的に使用するように設定しています。
8. chart.set_categories(categories)：chartオブジェクトに、categoriesで指定した範囲を横軸の項目として設定します。
9. new_sheet.add_chart(chart, "A" + str(new_sheet.max_row + 2))：
   • 作成したchartオブジェクトを、「集計表」シート（new_sheet）に挿入します。
   • "A" + str(new_sheet.max_row + 2)は、グラフを挿入する左上のセル位置を指定しています。new_sheet.max_rowは集計表の最終行なので、その2行下（+ 2）のA列にグラフが配置されることになります。これにより、集計データとグラフが重ならずに表示されます。

ワークブックを保存する

最後に、これまでの変更内容を新しいExcelファイルに保存します。

Copy
1wb.save("results.xlsx")

wb.save("results.xlsx")：saveメソッドを呼び出すことで、ワークブックwbに行ったすべての変更（新しいシートの追加、データの書き込み、グラフの挿入など）がresults.xlsxという名前の新しいExcelファイルとしてディスクに書き出されます。この処理を行わないと、プログラムが終了すると変更内容は失われてしまうため、非常に重要なステップです。

プログラムを実行すると、results.xlsxというファイルが作成され、その中に「集計表」というシートが追加されています。このシートには、請求月ごとの合計金額が一覧表示され、さらにその下に、請求月の推移を視覚的に捉えられる線グラフが挿入されていることを確認できるはずです。

おわりに

本記事では、Pythonのopenpyxlライブラリを使用し、Excelファイルのデータ集計とグラフ作成を自動化する具体的な方法を学びました。特に、defaultdictを活用して請求月ごとの合計金額を効率的に集計し、LineChartとReferenceを用いて視覚的な線グラフを作成する手順を深く理解いただけたと思います。このスキルは、手作業で行っていたExcel業務をPythonで効率化し、日々のデータ処理やレポート作成を自動化する上で非常に役立ちます。今回学んだ知識を活かして、ぜひシステムエンジニアとしてさまざまなExcel業務の自動化に挑戦してみてください。