【Python】pandasを使ってExcelの請求書から売上一覧を作成するサンプルコードを解説
Pythonとデータ分析ライブラリpandasを使い、複数のExcel請求書から企業名や請求日、商品明細などの必要な情報を自動で抽出し、一つの売上一覧を作成する方法を、サンプルコードを交えて解説します。データフレームの操作やExcelファイルへの書き出し方を学べます。
開発環境
- Python version: python 3.10.11
Pythonのpandasとは
pandas（パンダス）とは、データ解析に特化したPythonのライブラリです。大量のデータを効率的に分析したり、統計処理を行ったりする際に非常に便利なツールとして利用されています。
このpandasを使うと、Excelファイルをプログラムにデータフレームとして読み込んだり、プログラムで処理したデータフレームをExcelファイルとして書き出したりすることができます。
データフレームとは、Excelのシートのように、行と列で構成される2次元の表形式のデータ構造のことです。このデータフレームを使うことで、Excelで扱うような表形式のデータをPythonプログラムで効率的に操作・分析することが可能になります。
なお、PythonでExcelファイルを操作するライブラリはいくつか存在します。例えば、openpyxl（オープンパイエクセル）というライブラリもあります。openpyxlは、Excelファイルのセルの値を個別に読み書きしたり、シートを作成・編集したり、グラフを挿入したりするなど、Excelファイルそのものの内容を細かく操作するのに適しています。一方、pandasは、主にExcelファイルからデータを読み込んで、そのデータをプログラム内で分析・加工したり、結果を新しいExcelファイルにまとめたりする、データ解析に特化した目的で使用されます。今回は、データの解析や操作に便利なpandasについて説明を進めます。
公式ドキュメント：https://pandas.pydata.org/docs/
Pythonのpandasの使い方
Pythonでデータを扱う際に非常に便利なライブラリとして「pandas」があります。pandasは、表形式のデータ（エクセルシートのようなデータ）を効率的に操作・分析するための強力なツールです。システムエンジニアの仕事では、データベースから取得したデータ、ログファイル、CSVファイルなど、さまざまな形式のデータを扱う機会が多くあります。pandasを使うことで、これらのデータをPythonプログラム内で読み込み、整理し、加工するといった作業を簡単に行うことができます。
pandasをインストール
pandasを使うためには、まずパソコンにインストールする必要があります。Pythonのライブラリは、pipというツールを使ってインストールするのが一般的です。pipは、Pythonの「パッケージ管理システム」と呼ばれ、必要なライブラリをインターネットから探し出して、自動的にダウンロードし、使えるように設定してくれます。
pandasをインストールするには、以下のコマンドをコマンドプロンプトやターミナルで実行します。
1pip install pandas
このコマンドを実行すると、インターネット経由でpandasライブラリがダウンロードされ、ご自身のPython環境に導入されます。 これで準備完了です。pandasを使ってデータの操作や分析を始めることができます。
PythonのpandasでExcelの請求書から売上一覧を作成する
Excelの請求書データは、ビジネスの現場で頻繁に利用されています。しかし、複数の請求書から売上データを手作業で集計するのは非常に手間がかかり、ミスも発生しやすくなります。 そこで、Pythonのデータ分析ライブラリである「pandas（パンダス）」を使うと、このようなExcelファイルを効率的に処理し、必要な情報を自動的に抽出してまとめることができます。 ここでは、pandasを使って複数のExcel請求書から売上データを抽出し、1つの売上一覧表としてまとめる方法について、システムエンジニアを目指す初心者の方にも分かりやすく解説していきます。
今回使用するExcelファイル
今回はinvoice.xlsxという名前の請求書ファイルを例に説明します。
このinvoice.xlsxファイルには、「請求書1」と「請求書2」という2つのシートがあります。どちらのシートも同じフォーマットで作成されており、以下の情報が含まれています。
- 請求先の企業名
- 請求日
- 商品ごとの明細（品名、数量、単価、金額）
私たちは、この2つのシートから、企業名、請求日、そして各商品の明細データ（品名、数量、単価、金額）を抽出します。そして、それらのデータを集めて、最終的に1つの新しいExcelファイル（summary.xlsx）に「売上一覧」としてまとめます。
売上一覧を作成するサンプルコード
下記はpandasを使用してinvoice.xlsxの各シートから請求先・請求日・明細を取り出し、summary.xlsxに売上一覧として書き込むサンプルコードです。
このコードは、複数の請求書データから必要な情報だけを抽出し、きれいに整理された売上一覧を作成する一連の処理を示しています。
1import pandas as pd 2 3# エクセルファイルのパス（必要に応じて変更） 4excel_file_path = './invoice.xlsx' 5 6# Excelファイルを開いてシート名一覧を取得する 7with pd.ExcelFile(excel_file_path) as excel_file: 8 # シート名一覧を取得 9 sheet_names = excel_file.sheet_names # ['請求書1', '請求書2'] 10 11# 新しいシートにデータを書き込むためのDataFrameを作成 12summary_data = pd.DataFrame(columns=['SheetName', 'CompanyNmae', 'BillingDate', 'Product', 'Quantity', 'UnitPrice', 'TotalAmount']) 13 14# 取得したシート一覧から売り上げデータを全て取得し、新しいシートに追記する 15for sheet_name in sheet_names: 16 # シートのデータを取得 17 sheet_data = pd.read_excel(excel_file_path, sheet_name=sheet_name, header=None) 18 19 # 企業名を抽出する 20 company_name = sheet_data.iloc[1, 0].replace("御中", "").replace(" ", "") 21 22 # 請求日を抽出する 23 billing_date = sheet_data.iloc[2, 6].strftime("%Y/%m/%d") 24 25 # 請求書データから売り上げデータを抽出する 26 sales_data = sheet_data.iloc[14:28, [0, 3, 5 ,6]] 27 28 # 各行ごとにデータを追記 29 for index, row in sales_data.iterrows(): 30 # 空白の行をスキップする 31 if pd.isna(row.iloc[0]): 32 continue 33 34 summary_data = pd.concat( 35 [ 36 summary_data, 37 pd.DataFrame( 38 { 39 'SheetName': sheet_name, 40 'CompanyNmae': company_name, 41 'BillingDate': billing_date, 42 'Product': row.iloc[0], 43 'Quantity': row.iloc[1], 44 'UnitPrice': row.iloc[2], 45 'TotalAmount': row.iloc[3], 46 }, 47 index=[0] 48 ) 49 ], 50 ignore_index=True 51 ) 52 53# 新しいエクセルファイルに一覧を書き込む 54summary_excel_path = './summary.xlsx' 55summary_data.to_excel(summary_excel_path, index=False)
作成したファイルは、以下のコマンドで実行できます。
Pythonがインストールされた環境で、このコードをexcel.pyなどのファイル名で保存し、ターミナルやコマンドプロンプトで以下のコマンドを実行してください。
1python excel.py
Excelファイルのシート名一覧を取得する
まず、PythonでExcelファイルを操作するために、そのファイルを開き、中にどのようなシートがあるか（シート名の一覧）を取得します。
1excel_file_path = './invoice.xlsx' 2 3with pd.ExcelFile(excel_file_path) as excel_file: 4 sheet_names = excel_file.sheet_names
excel_file_path = './invoice.xlsx'で、今回扱うExcelファイルの場所（パス）を指定しています。./は、Pythonのスクリプトファイルと同じ場所にあることを意味します。pd.ExcelFile(excel_file_path)は、指定したExcelファイルをPythonで扱うための準備をします。これをwith構文で使うことで、ファイルを開いた後の閉じる処理を自動的に行ってくれます。excel_file.sheet_namesを使うと、開いたExcelファイルに含まれるすべてのシートの名前をリスト形式で取得できます。- 今回の
invoice.xlsxの場合、['請求書1', '請求書2']のように、シート名がリストとして取得されます。
売上一覧のデータフレームを作成する
次に、抽出した売上データを格納するための「データフレーム」という表形式のデータ構造をpandasで作成します。最初は空のデータフレームとして準備します。
1summary_data = pd.DataFrame(columns=['SheetName', 'CompanyNmae', 'BillingDate', 'Product', 'Quantity', 'UnitPrice', 'TotalAmount'])
pd.DataFrame()は、pandasで表形式のデータ（データフレーム）を作成する関数です。columns引数にリストで['SheetName', 'CompanyNmae', 'BillingDate', 'Product', 'Quantity', 'UnitPrice', 'TotalAmount']を指定することで、新しいデータフレームにこれらの項目（列名）を持つ空の表を作成しています。- この
summary_dataに、後ほど各請求書から抽出したデータを行として追加していきます。
各シートのデータを読み込む
シート名の一覧が取得できたので、for文を使ってシート名を1つずつ取り出し、それぞれのシートのデータを読み込みます。
1for sheet_name in sheet_names: 2 sheet_data = pd.read_excel(excel_file_path, sheet_name=sheet_name, header=None)
for sheet_name in sheet_names:は、先ほど取得した['請求書1', '請求書2']というリストから、まず'請求書1'、次に'請求書2'というように、シート名を順番にsheet_name変数に取り出して処理を繰り返すことを意味します。pd.read_excel(excel_file_path, sheet_name=sheet_name, header=None)は、指定したExcelファイルから、現在のsheet_nameで指定されたシートのデータを読み込みます。header=Noneは重要な設定です。これは「Excelファイルの1行目をデータの見出し（ヘッダー）として扱わない」という指示です。請求書のように決まった場所にデータが配置されている場合、ヘッダーとして扱わないことで、行のインデックス番号がExcelファイルの見た目と一致しやすくなり、データ抽出がしやすくなります。この指定がない場合、1行目がヘッダーと見なされ、データの行インデックスが1つずれてしまうことがあります。
企業名と請求日を抽出する
シートのデータがsheet_dataというデータフレームに読み込まれたら、次にその中から「企業名」と「請求日」という個別の情報を抽出します。
1company_name = sheet_data.iloc[1, 0].replace("御中", "").replace(" ", "") 2 3billing_date = sheet_data.iloc[2, 6].strftime("%Y/%m/%d")
sheet_data.iloc[行番号, 列番号]は、データフレームの中から特定の行と列のデータを番号で指定して取り出すための機能です。company_name = sheet_data.iloc[1, 0]：Excelの「2行目、1列目」（Pythonのインデックスは0から始まるので1行目、0列目）にあるデータを企業名として取得しています。.replace("御中", "").replace(" ", "")は、取得した企業名の文字列から「御中」という文字や半角・全角の空白を取り除き、企業名だけをきれいに抽出するための処理です。billing_date = sheet_data.iloc[2, 6]：Excelの「3行目、7列目」（Pythonのインデックスは0から始まるので2行目、6列目）にあるデータを請求日として取得しています。.strftime("%Y/%m/%d")は、取得した日付データを「2024/04/30」のような指定された形式の文字列に変換する機能です。これにより、日付の表示形式を統一できます。
売上明細データを抽出する
次に、請求書の中で商品ごとの「売上明細」が記載されている部分をまとめて抽出します。
1sales_data = sheet_data.iloc[14:28, [0, 3, 5 ,6]]
sales_data = sheet_data.iloc[14:28, [0, 3, 5 ,6]]は、明細データを抽出するための範囲を指定しています。14:28は行の範囲指定です。Pythonのリストのスライスと同じように、14行目から27行目まで（28は含まない）のデータを取り出します。[0, 3, 5, 6]は列の指定です。リストで指定することで、0列目（品名）、3列目（数量）、5列目（単価）、6列目（金額）という、離れた列のデータをまとめて取得できます。これにより、必要な明細情報だけを効率よく抽出しています。
明細を1行ずつ売上一覧に追加する
抽出した明細データ（sales_data）は、まだ1つのシート内のまとまった形です。これを、先ほど作成した全体の売上一覧（summary_data）に、1行ずつ追加していきます。
1for index, row in sales_data.iterrows(): 2 if pd.isna(row.iloc[0]): 3 continue 4 5 summary_data = pd.concat( 6 [ 7 summary_data, 8 pd.DataFrame( 9 { 10 'SheetName': sheet_name, 11 'CompanyNmae': company_name, 12 'BillingDate': billing_date, 13 'Product': row.iloc[0], 14 'Quantity': row.iloc[1], 15 'UnitPrice': row.iloc[2], 16 'TotalAmount': row.iloc[3], 17 }, 18 index=[0] 19 ) 20 ], 21 ignore_index=True 22 )
for index, row in sales_data.iterrows():は、sales_dataというデータフレームの各行を1つずつ取り出すための処理です。iterrows()を使うと、行のインデックス（index）と行のデータ（row）をセットで取得できます。if pd.isna(row.iloc[0]): continue：明細の品名（0列目）が空欄（pd.isnaはデータが空、つまりNaNであるかを判定します）である場合は、その行はデータがないので、処理をスキップして次の行へ進みます。これにより、空白の行が売上一覧に含まれるのを防ぎます。pd.concat([...], ignore_index=True)：これが新しい行をsummary_dataに追加する部分です。pd.concatは、複数のデータフレームを結合する機能です。ここでは、既存のsummary_dataと、新しく作成した1行分のデータフレームを結合しています。- 新しい1行分のデータフレームは、現在処理しているシートの
sheet_name、company_name、billing_dateと、現在のrow（明細の1行）から取得したProduct、Quantity、UnitPrice、TotalAmountをまとめて作られています。 ignore_index=Trueは、結合する際に既存の行番号を無視し、新しい通し番号（インデックス）を自動的に振り直すための設定です。これにより、データが正しく連続した行番号で追加されていきます。
売上一覧をExcelファイルに書き込む
すべてのシートからデータを抽出し、summary_dataにまとめる処理が終わったら、最後にこのsummary_dataを新しいExcelファイルとして保存します。
1summary_excel_path = './summary.xlsx' 2summary_data.to_excel(summary_excel_path, index=False)
summary_excel_path = './summary.xlsx'で、出力する新しいExcelファイルのファイル名と場所を指定しています。summary_data.to_excel(summary_excel_path, index=False)は、summary_dataというデータフレームの内容を、指定したパスのExcelファイルとして書き出すための関数です。index=Falseは、データフレームの左端にある自動生成される行番号（インデックス）をExcelファイルに書き込まないという指定です。これにより、データフレームで定義した列名がExcelファイルの1行目のヘッダーとして表示され、きれいな表が作成されます。
このコードを実行すると、summary.xlsxというファイルが作成され、invoice.xlsxに含まれていた「請求書1」と「請求書2」の売上データが、1つにまとまった一覧形式で確認できるようになります。
おわりに
今回は、Pythonのデータ分析ライブラリであるpandasを用いて、複数のExcel請求書から売上データを自動的に抽出し、一つの売上一覧を作成する方法を学習しました。
具体的には、pd.read_excelでExcelシートのデータを読み込み、sheet_data.ilocを活用して特定のセルや範囲から企業名、請求日、商品明細といった必要な情報を抽出する手順を学びました。
抽出したデータはpd.DataFrameで表形式のデータ（データフレーム）として扱い、pd.concatで複数の明細を結合して整理し、最終的にto_excelで整形された売上一覧を新しいExcelファイルとして出力する一連の流れを体験しました。
このようにpandasを使うことで、手作業では時間と手間がかかるExcelデータの集計や加工作業を、プログラムで効率的かつ正確に自動化できるようになります。