【コード掲載】Pythonで複数のExcelファイルから
データを抽出し新規Excelファイルへ一括出力
こんにちは、てつをです。今回は、複数のExcelファイルからシートを指定して特定のデータを抽出し、新たな別のExcelファイルへ全データを転記する方法をお伝えします。以前、「PDF出力の記事」にてExcelからPDF出力する方法をお伝えしたかと思います。例えば注文書情報をExcelで作成していた場合、注文が増えてくるとExcelファイルの数も膨大になってきます。一方、半期や年間でいったいどれくらいの注文があったかを集計することになるでしょう。この集計を手作業で実施した場合は、1つ1つのファイルを開いて情報を転記してをファイル数分繰り返すことになります。さすがに時間がもったいないので、今回は複数のExcelファイルから、シートを指定し特定のセルの情報を一括で抽出する方法をお伝えしたいと思います。
目次
-
今回の場面設定～複数の注文書から売上合計を算出する～
-
Pythonの実行環境を整える
-
Pythonスクリプトの編集と実行
１．今回の場面設定～複数の注文書から売上合計を算出する～
取得するデータについて
今回のケーススタディでは、複数の見積から①見積番号と②顧客名と③合計金額の3点を一括で抽出したいと思います。サンプルの見積として、以前「【最新】Excelマクロでテーブルを操作して見積を作成｜コピーして利用可能なVBAコード掲載」で紹介した結婚式の見積を利用していきたいと思います。まず、織田、豊臣、徳川の3家に対して、結婚式の見積を以下の用に作成してみました。
実際にはその他にも多くの家族向けに見積を作成していますが、今回はサンプルということで3家の見積から情報を一括で抽出していきたいと思います。上記の様に、「見積シート」のC3セルに顧客名、C37セルに見積番号、H38セルに合計金額が記載されています。後程、これらのシートおよびセルを指定して、情報を抽出していけたらと思います。
データ取得後イメージ
データ取得後のイメージについては、以下のようになることを想定しています。1行に1つの見積情報が記載される形です。今回は見積に記載されている各小項目を抽出してはいませんが、もし仮に全見積の見積詳細まで抽出されたい場合は、シートを見積集計シートと見積詳細集計シートの2つに分けてデータをまとめることを推奨します。少し余談ですが、見積と見積詳細というデータの関係をデータベースの世界ではスーパータイプとサブタイプと呼んでいます。気になる方は、スーパータイプとサブタイプで検索してみてください。
２．Pythonの実行環境を整える
では早速、VBAの時と同様にコードを見ていきましょう。と言えたらよかったのですが、今回はPythonを利用して参りますので事前にPythonのコードを実行する環境を整える必要があります。私のおすすめを２つ紹介しますので、どちらかご選択いただきPythonの実行環境を整えていただければと思います。ちなみに、私はAnacondaにも入っているJupiter Note Bookを利用しています。
2-1. Anacondaを使ってPython環境を整える（おすすめ）
Anacondaは、Pythonの実行環境を簡単に整えることができるツールです。特にデータ分析や機械学習に必要なライブラリが最初から含まれているため、とても便利です。以下の手順でセットアップできます。
1. Anacondaをダウンロードする
- Anaconda公式サイト にアクセスします。
- 「Download」ボタンをクリックし、お使いのOS（Windows、Macなど）に対応したインストーラーをダウンロードしてください。
2. Anacondaをインストールする
- ダウンロードしたファイルをダブルクリックしてインストールを開始します。
- 表示される手順に従い、「Next」をクリックして進みます。
- 「Add Anaconda to my PATH environment variable」というチェックボックスは外したままにします。
- インストールが完了したら、「Finish」をクリックします。
3. Jupyter Notebookを起動する
- スタートメニューから「Anaconda Navigator」を開きます。
- 「Jupyter Notebook」を選択し、「Launch」をクリックします。
- ブラウザが自動的に開き、コードを実行できるノートブック環境が表示されます。
これでPythonのコードを簡単に実行できる環境が整いました！
2-2. VSCodeを使ってPython環境を整える（初心者にもおすすめ）
Anaconda以外の選択肢として、Microsoftが提供する「Visual Studio Code（VSCode）」を使う方法があります。VSCodeは軽量で使いやすいエディタで、多くのプログラマーに愛用されています。Python環境を簡単に整えられるので、こちらもおすすめです。
1. VSCodeをダウンロードする
- VSCode公式サイト にアクセスします。
- ページ中央の「Download for (OS名)」ボタンをクリックし、インストーラーをダウンロードします。
2. VSCodeをインストールする
- ダウンロードしたファイルをダブルクリックしてインストールを開始します。
- 表示される手順に従い、必要に応じて「Add to PATH」にチェックを入れて進めます。
- インストールが完了したら、VSCodeを起動します。
3. Python拡張機能をインストールする
- VSCodeの左側にある「拡張機能」アイコン（四角形が4つ並んだもの）をクリックします。
- 検索ボックスに「Python」と入力し、Microsoftが提供する公式のPython拡張機能をインストールします。
4. Pythonをインストールする
VSCodeを使うには、Python本体も必要です。以下の手順でインストールしてください。
- Python公式サイト にアクセスします。
- ページ上部の「Downloads」から、お使いのOSに対応するPythonをダウンロードします。
- ダウンロードしたインストーラーを起動し、必ず「Add Python to PATH」にチェックを入れてからインストールを進めます。
5. 動作確認をする
- VSCodeを起動し、新しいファイルを作成して保存します（例:
test.py）。 - 次のような簡単なPythonコードを入力します：
- 画面右上の「再生ボタン」をクリックすると、コードが実行されます。
これでVSCodeを使ったPython環境が整いました！
2-3. どちらを選ぶべき？
- Anaconda: Jupyter Notebookやデータ分析ツールを使いたい方に最適。
- VSCode: シンプルで柔軟なエディタ環境を求める方におすすめ。
３．Pythonスクリプトの編集と実行
スクリプト実行に向けた準備
では、スクリプト実行に向けてまずは３つのExcelファイルを同一フォルダに保存したいと思います。今回私は、以下の様にデスクトップ上に「見積サンプル」というフォルダを作成し、その中に３つのファイルを保存しました。皆さんも同じようにExcelを一か所に保存してください。
続いて、今回使用するサンプルマクロは以下になります。 このあと、４か所ほどスクリプトを修正してから実行します。修正箇所はこちらになります。（スクリプト内にもコメントで１～４の変更箇所を記載しています。）
- 全注文書が保存されているフォルダのパスを入力
- 集計したExcelファイルの保存先を入力
- 注文書のデータが記載されているシート名を入力
- 抽出するデータのセルの位置を指定
import os
from openpyxl import load_workbook
import pandas as pd
# ディレクトリ内のExcelファイルを取得
directory = r'＜＜1.全注文書が保存されているフォルダを指定＞＞'
output_file = r'＜＜2.集計したExcelファイルの保存先を指定＞＞\summary_output.xlsx'
# 出力用のデータフレームを作成
summary_data = []
# ディレクトリ内のすべてのマクロ専用Excelファイル（*.xlsm）を処理
for filename in os.listdir(directory):
if filename.endswith('.xlsx'): ←もし、マクロありのExcelファイルの場合は.xlsmへ変更する
file_path = os.path.join(directory, filename)
wb = load_workbook(file_path, data_only=True)
sheet = wb['＜＜3.ここにシート名を入れる＞＞']
# 注文テーブルのデータを辞書型で抽出
order_data = {
'合計金額': sheet['＜＜4.抽出するデータのセルを指定、抽出するデータ分行を増やす＞＞'].value,
}
summary_data.append(order_data)
# 抽出したデータを確認するために表示
print("見積テーブル:")
for order in summary_data:
print(order)
# まとめ用のExcelファイルにデータを転記
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
df_summary = pd.DataFrame(summary_data)
# 注文データを保存
if not df_summary.empty:
df_summary.to_excel(writer, sheet_name='Summary', index=False)
print("データの抽出と転記が完了しました。")
では、4か所の修正を進めていきましょう。まず、１番については先ほど作成したフォルダのパスを指定します。続いて2番はお好きは場所をご指定下さい。3番については「見積シート」と入力し、4番については3か所のセル情報を記載すればOKです。修正後のスクリプトを以下に示します。こちらのスクリプトを実行していただければ、指定した場所にデータ抽出されたExcelが出現します！
import os
from openpyxl import load_workbook
import pandas as pd
# ディレクトリ内のExcelファイルを取得
directory = r'C:\Users\nakano\Desktop\見積サンプル' #1.全注文書が保存されているフォルダのパスを入力
output_file = r'C:\Users\nakano\Desktop\summary_output.xlsx' #2.集計したExcelファイルの保存先を入力
# 出力用のデータフレームを作成
summary_data = []
# ディレクトリ内のすべてのマクロ専用Excelファイル（*.xlsm）を処理
for filename in os.listdir(directory):
if filename.endswith('.xlsx'):#←もし、マクロありのExcelの場合は.xlsmへ変更する
file_path = os.path.join(directory, filename)
wb = load_workbook(file_path, data_only=True)
sheet = wb['見積シート'] #3.注文書のデータが記載されているシート名を入力
# 注文テーブルのデータを辞書型で抽出
order_data = {
'見積番号': sheet['C37'].value, #4.抽出するデータのセルの位置を指定
'顧客名': sheet['C3'].value,
'合計金額': sheet['H38'].value,
}
summary_data.append(order_data)
# 抽出したデータを確認するために表示
print("見積テーブル:")
for order in summary_data:
print(order)
# まとめ用のExcelファイルにデータを転記
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
df_summary = pd.DataFrame(summary_data)
# 注文データを保存
if not df_summary.empty:
df_summary.to_excel(writer, sheet_name='Summary', index=False)
print("データの抽出と転記が完了しました。")
上記スクリプトを実行すると、デスクトップ上に「」というExcelファイルが保存されているかと思います。ファイルを開き、以下の様にデータが抽出できていれば成功です！！
今回の記事はここまでとなります。次回以降も、Excel VBAやPythonでの業務効率化に関する記事を書いていきたいと思いますので、引き続きよろしくお願いいたします。
以上
