業務システムを改修したあと、請求書や納品書のExcel帳票をまとめて出力し、内容を確認することがあります。
1枚ならまだ目で追えます。ところが、請求書を30件出して、請求先名、明細金額、小計、消費税、合計金額を順番に見るとなると話が変わります。Excelを開いて、同じセルを見て、閉じて、次のファイルを開く。途中から「確認した」のか「眺めた」のか分からなくなります。
この記事では、Excel帳票の確認を全部自動化する話はしません。目視確認に入る前に、機械で拾える空欄と金額ズレを先に出す方法を整理します。題材は、業務システムが出力した請求書Excelです。Pythonとopenpyxlで複数ファイルを読み、問題があるファイル名、セル位置、内容をCSVに残します。
この記事で作るのは、帳票を正しく見た目まで判定する仕組みではありません。目視確認の前に、空欄と金額ズレを先に拾うための確認スクリプトです。
サンプルコードは、記事とあわせて公開しているコード置き場にあります。今回使うファイルは、サンプルコードの python/excel-report-batch-check/ です。
Excel帳票の確認で目視だけに頼ると何が起きるか
30件、50件になると同じセルの確認が抜ける
帳票確認でつらいのは、難しい判断だけではありません。同じ確認を何度も繰り返すことです。
請求書なら、請求書番号、請求日、請求先名、小計、消費税、合計金額を見る。明細があれば、品名、数量、単価、金額も見る。これを何十件も続けると、目は同じ場所を見ているつもりでも、空欄や金額ズレを見落としやすくなります。
特に、システム改修後の確認では「全部の帳票をざっと見る」になりがちです。ざっと見る確認は、レイアウト崩れには気づきやすい一方で、セル単位の値のズレには弱いです。
金額ズレは見た目だけでは気づきにくい
金額のズレは、帳票の見た目が整っていても起きます。
たとえば、明細行の金額が 数量 * 単価 と一致していない。小計が明細金額の合計と違う。消費税の丸めがシステムの仕様と違う。合計金額が小計と消費税に合っていない。こういう問題は、帳票全体の見た目だけでは分かりません。
目視で確認するなら、明細金額を足し、小計と比べ、消費税を計算し、合計金額を見ることになります。これは人が繰り返すには重い確認です。
レイアウト確認と値の確認を一緒にすると疲れる
帳票確認には、人が見た方がよいものと、機械で拾いやすいものがあります。
| 確認するもの | 向いている確認方法 | 理由 |
|---|---|---|
| 印字位置、文字切れ、見た目の違和感 | 人が見る | 帳票ごとの例外や見た目の判断が混ざるため |
| 請求先名や請求日の空欄 | 機械で先に拾う | セル位置が決まっていれば同じ条件で確認できるため |
| 明細金額や合計金額のズレ | 機械で先に拾う | 計算式として確認でき、件数が増えても繰り返せるため |
全部を人が見るのではなく、機械で拾えるものを先に出します。そのあとで、人がレイアウトや例外表示を見る。この順番にするだけでも、確認の負担はかなり変わります。
帳票確認を最初から全部自動化しようとすると、例外対応が増えてスクリプト側の確認が重くなります。まずはセル位置と金額計算で判定できる範囲に絞ります。
先に機械で拾う確認項目を決める
空欄はセル位置が決まっていれば拾える
請求書番号、請求日、請求先名のような項目は、帳票上のセル位置が決まっていることが多いです。
今回のサンプルでは、次のセルを必須セルとして扱います。
| セル | 項目 | チェック内容 |
|---|---|---|
| B2 | 請求書番号 | 空でないこと |
| B3 | 請求日 | 空でないこと |
| B5 | 請求先名 | 空でないこと |
ここはPythonで難しいことをする必要はありません。指定したセルを読み、空ならエラーにするだけです。
明細金額は数量と単価から計算できる
明細行の金額は、数量と単価から期待値を作れます。
たとえば、数量が 2、単価が 12000 なら、明細金額の期待値は 24000 です。帳票上の金額セルが 23000 ならズレています。
明細行は固定セルより少しだけ面倒です。1行だけではなく、8行目から17行目までのように範囲で見る必要があるからです。それでも、開始行、終了行、数量列、単価列、金額列が決まっていれば、機械で繰り返し確認できます。
合計金額は小計と消費税から期待値を作れる
合計欄も同じです。
明細金額を足して小計を作る。小計に税率を掛けて消費税を作る。小計と消費税を足して合計金額を作る。帳票上の値と期待値を比べれば、ズレを検出できます。
ただし、税計算には注意が必要です。切り捨て、切り上げ、四捨五入、明細単位の税計算、請求書単位の税計算など、システムごとにルールが違います。サンプルでは円単位の四捨五入にしていますが、実務では自分のシステムの計算ルールに合わせます。
丸め方が違うと、スクリプトの方が間違ったエラーを出します。 合計金額を見る前に、システム側の端数処理を確認しておきます。
見た目や例外表示は最後に人が見る
Pythonで見る対象を増やしすぎると、確認スクリプト自体が重くなります。
レイアウト崩れ、印字位置、文字切れ、得意先ごとの例外文言は、人が見た方が早い場合があります。ここまで最初から自動化しようとすると、帳票ごとの例外をスクリプトに詰め込み始めることになります。
最初は、空欄と金額ズレだけを機械で拾う。見た目は最後に人が見る。 この分担にしておくと、確認スクリプトを使い続けやすくなります。
請求書Excelをチェックしやすい形に分解する
固定セルは請求書番号、請求日、請求先名を見る
帳票は一覧データと違い、すべての値が行と列にきれいに並ぶわけではありません。
請求書番号はB2、請求日はB3、請求先名はB5のように、特定のセルに置かれている項目があります。こうした項目は、固定セルとして扱います。
固定セルのチェックでは、まず「空だと困る項目」を選びます。いきなり全項目を対象にすると、帳票の少しの変更でエラーが増えます。最初は、請求書として最低限必要な項目だけで十分です。
明細行は品名、数量、単価、金額を見る
明細行は、固定セルとは別に考えます。
今回のサンプルでは、8行目から17行目までを明細範囲にしています。B列が品名、D列が数量、E列が単価、F列が金額です。
| 範囲 | 項目 | 見方 |
|---|---|---|
| B8:B17 | 品名 | 明細がある行では空でないこと |
| D8:D17 | 数量 | 数値として読めること |
| E8:E17 | 単価 | 数値として読めること |
| F8:F17 | 金額 | 数量×単価と一致すること |
空行は未使用行として飛ばします。品名だけ入っている、数量だけ空になっている、といった行はエラーにします。
合計欄は小計、消費税、合計金額を見る
合計欄は、明細行から作った期待値と比べます。
サンプルでは、F18を小計、F19を消費税、F20を合計金額として扱います。明細金額の合計とF18を比べ、小計から消費税を計算してF19と比べ、小計と消費税を足した値をF20と比べます。
ここまで見ると、請求書の値の整合性はかなり確認できます。レイアウトまでは分かりませんが、少なくとも「金額が合っているか」を毎回手で足し直す必要は減ります。
帳票ごとに変わるものは設定に逃がす
セル位置や税率は、帳票ごとに変わります。
請求書ではB5が請求先名でも、別の帳票ではC6かもしれません。明細行も10行目から始まるかもしれません。税率や丸め方も、案件によって変わります。
そのため、帳票の形に関する情報はPythonコードに埋め込みすぎず、設定ファイルに分けます。今回のサンプルでは rules.json に分けています。帳票の違いは、まず設定ファイルで吸収する と決めておくと、コードを直す回数を減らせます。
rules.json に帳票の見方を書く
必須セルを required_cells に書く
rules.json の required_cells には、空だと困るセルを書きます。
"required_cells": {
"B2": "請求書番号",
"B3": "請求日",
"B5": "請求先名"
}キーがセル位置、値がエラーメッセージに使う項目名です。たとえばB5が空なら、請求先名が空です というエラーにできます。
自分の帳票に合わせる場合は、ここを最初に直します。請求先名がC7にあるなら、"B5" を "C7" に変えます。支払期限も必須にしたいなら、セル位置と項目名を追加します。
明細行の範囲と列を detail に書く
detail には、明細行の開始行、終了行、各項目の列を書きます。
"detail": {
"start_row": 8,
"end_row": 17,
"item_col": "B",
"quantity_col": "D",
"unit_price_col": "E",
"amount_col": "F"
}この設定から、B8、D8、E8、F8を見て、次にB9、D9、E9、F9を見る、という処理を作れます。
明細が30行ある帳票なら end_row を変えます。金額列がG列なら amount_col を変えます。帳票ごとの違いをここで吸収できるようにしておくと、Pythonコードの変更を減らせます。
合計欄と税率を totals に書く
totals には、小計、消費税、合計金額のセルと税率を書きます。
"totals": {
"subtotal_cell": "F18",
"tax_cell": "F19",
"total_cell": "F20",
"tax_rate": 0.1
}ここで大事なのは、帳票上の値と、スクリプトが計算した期待値を分けて考えることです。帳票上のF20をそのまま信用するのではなく、明細合計と税額から期待値を作り、F20と比べます。
自分の帳票ではセル番地と税率を置き換える
このサンプルをそのまま実務帳票に当てることは、あまりありません。
まずやることは、Excelを開いて、確認したい項目のセル位置を拾うことです。請求書番号はどこか。請求先名はどこか。明細行は何行目から何行目までか。小計、消費税、合計金額はどのセルか。
そのセル位置を rules.json に写します。Pythonコードを直すのは、そのあとで足りない確認が出てきたときです。
tomo最初に作るべきなのは、万能なチェック処理ではなく、自分の帳票をどう見るかを書いた rules.json です。
自分の帳票に置き換えるときは、まず required_cells、detail、totals の3か所だけを見ます。Pythonコードを触る前に、Excel上のセル位置と税率を設定へ写すのが先です。
Pythonで出力済みExcelをまとめて読む
対象フォルダから .xlsx を集める
スクリプトの入口では、対象フォルダ内の .xlsx を集めます。
rules = json.loads(Path(args.rules).read_text(encoding="utf-8"))
errors = []
for workbook_path in sorted(Path(args.input_dir).glob("*.xlsx")):
errors.extend(check_workbook(workbook_path, rules))これは、手作業でいう「出力フォルダにあるExcelを順番に開く」に対応します。
業務システムから請求書を30件出力したら、そのフォルダを指定して同じチェックを流します。ファイルが増えても、確認条件は同じです。
1ファイルずつ load_workbook() で開く
Excelファイルは、openpyxlの load_workbook() で開きます。
workbook = load_workbook(workbook_path, data_only=True)
sheet_name = rules["sheet_name"]ここでは data_only=True を指定しています。これは、数式セルについて、数式そのものではなく保存済みの計算結果を読むための指定です。
data_only=True は、PythonがExcelの数式を再計算する指定ではありません。 数式入り帳票をチェックする場合は、出力時点で計算結果が保存されているかを確認してください。
シートがない場合もエラーとして残す
帳票のシート名が変わっている場合もあります。指定したシートがなければ、その時点でエラーにします。
if sheet_name not in workbook.sheetnames:
return [error(workbook_path, "", f"シート「{sheet_name}」がありません")]シートがない状態で後続処理を続けると、原因が分かりにくくなります。先に「指定シートがない」と出した方が、確認担当者はすぐに判断できます。
エラーは最後にまとめてCSVへ渡す
check_workbook() は、1つのExcelファイルで見つかったエラーを配列で返します。メイン処理では、それを errors に集めます。
画面にすぐ表示するのではなく、最後にまとめてCSVへ書き出します。確認結果を残すことが目的なので、画面表示だけで終わらせない形にしています。
空欄と明細金額をチェックする
必須セルが空ならファイル名とセルを記録する
必須セルのチェックは、required_cells を順番に見るだけです。
def check_required_cells(workbook_path, sheet, rules, errors):
for cell, label in rules["required_cells"].items():
if is_blank(sheet[cell].value):
errors.append(error(workbook_path, cell, f"{label}が空です"))エラーには、ファイル名、セル位置、メッセージを残します。
def error(workbook_path, cell, message):
return {
"file": workbook_path.name,
"cell": cell,
"level": "error",
"message": message
}この形にしておくと、CSVを見た人が対象ファイルを開き、該当セルを確認できます。
明細行が空なら未使用行として飛ばす
明細行には、使われていない空行があります。サンプルでは、品名、数量、単価、金額がすべて空なら、その行は未使用として飛ばします。
if is_blank(item) and is_blank(quantity) and is_blank(unit_price) and is_blank(amount):
continueこれを入れないと、未使用行まで「品名が空です」「数量が空です」と出てしまいます。帳票確認では、空行と入力漏れを分けることが大事です。
数量、単価、金額を数値として読む
数量、単価、金額は、数値として読める必要があります。
quantity_value = to_decimal(quantity)
unit_price_value = to_decimal(unit_price)
amount_value = to_decimal(amount)サンプルでは Decimal に変換しています。金額計算では小数の扱いや丸めが絡むため、単純な浮動小数点数より扱いやすいからです。
数値として読めない場合は、計算に進まずエラーにします。空欄と数値不正を分けて出せるようにしておくと、原因を追いやすくなります。
金額が数量×単価と違えばエラーにする
数値として読めたら、明細金額の期待値を作ります。
expected_amount = quantity_value * unit_price_value
if amount_value != expected_amount:
errors.append(error(workbook_path, amount_cell, f"金額が数量×単価と一致しません。期待値: {expected_amount}"))この処理は地味ですが、目視で繰り返すと疲れます。特に明細行が多い帳票では、先に機械で拾う価値があります。
小計、消費税、合計金額をチェックする
明細金額を足して小計の期待値を作る
明細行を見ながら、金額を合計します。
total += amount_valueこの合計値が、帳票上の小計と一致するかを後で見ます。帳票上の小計をそのまま信用するのではなく、明細から期待値を作るのがポイントです。
税率と丸め方を決めて消費税を計算する
サンプルでは、税率を rules.json から読み、円単位で四捨五入しています。
expected_tax = yen(detail_total * Decimal(str(totals["tax_rate"])))
expected_total = detail_total + expected_tax丸め処理は次の関数です。
def yen(value):
return value.quantize(Decimal("1"), rounding=ROUND_HALF_UP)ここは実務で必ず確認するところです。税計算は、明細単位で丸めるのか、請求書単位で丸めるのか、切り捨てなのか、四捨五入なのかで結果が変わります。
帳票上の合計金額と期待値を比べる
小計や合計金額が期待値と違えば、該当セルをエラーにします。
if subtotal != detail_total:
errors.append(error(workbook_path, totals["subtotal_cell"], f"小計が明細合計と一致しません。期待値: {detail_total}"))
if total != expected_total:
errors.append(error(workbook_path, totals["total_cell"], f"合計金額が小計+消費税と一致しません。期待値: {expected_total}"))エラーには期待値も入れています。単に「合計金額が違う」と出すより、確認時にどちらへ直すべきか判断しやすくなります。エラーCSVには、確認担当者が次に開くファイルとセルを残す ことが大事です。
丸めルールは実際のシステムに合わせる
サンプルの丸め方は、あくまで例です。
実際の業務システムでは、税率、課税区分、端数処理、税込・税抜、軽減税率などが絡むことがあります。この記事のスクリプトを使うなら、まず自分のシステムがどう計算しているかを確認してください。
最初から税区分まで全部見る必要はありません。ただ、合計金額をチェック対象にするなら、丸め方だけは曖昧にしない方がよいです。
result.csv を確認記録として使う
ファイル名、セル、エラー内容を残す
エラーはCSVに出します。
def write_result(path, errors):
with path.open("w", newline="", encoding="utf-8-sig") as output:
writer = csv.DictWriter(output, fieldnames=["file", "cell", "level", "message"])
writer.writeheader()
writer.writerows(errors)utf-8-sig にしているのは、ExcelでCSVを開いたときに文字化けしにくくするためです。
出力する列は次の4つです。
| 列 | 意味 |
|---|---|
| file | 問題が見つかったExcelファイル名 |
| cell | 確認すべきセル位置 |
| level | エラーや警告の区分 |
| message | 何が問題かを示す説明 |
エラーがある帳票だけを開いて確認する
サンプルを実行すると、次のようなCSVが出ます。
file,cell,level,message
sample_invoice_002_missing_customer.xlsx,B5,error,請求先名が空です
sample_invoice_003_wrong_total.xlsx,F20,error,合計金額が小計+消費税と一致しません。期待値: 137500この結果があれば、確認担当者は全ファイルを順番に開く必要がありません。sample_invoice_002_missing_customer.xlsx のB5を見る。sample_invoice_003_wrong_total.xlsx のF20を見る。そういう確認に変えられます。
tomoこのCSVが出るだけでも、確認作業はかなり変わります。全部の帳票を開く前に、まず見るべきファイルとセルを絞れます。
修正後に再出力して同じチェックを流す
業務システム側を直したら、帳票を再出力し、同じコマンドをもう一度実行します。
同じ rules.json を使うので、確認条件は変わりません。前回と同じ観点で再チェックできます。
この繰り返しができると、リリース前確認で説明しやすくなります。「目視で見ました」だけでなく、「必須セルと金額整合性はこのルールで確認し、エラーCSVが空になるまで再確認しました」と言えます。
CSVを残すと再確認の説明がしやすい
確認結果が画面に流れるだけだと、あとから見返せません。
CSVとして残しておけば、修正前後の確認結果を比較できます。担当者に渡すこともできます。レビューで「どの帳票を見たのか」と聞かれたときにも、ファイル名とセル位置が残っています。
tomoスクリプトの目的は、人の判断を消すことではありません。確認する場所を減らし、確認結果を残しやすくすることです。
実際にサンプルを動かして確認する
サンプルコードの場所へ移動する
サンプルコード置き場の python/excel-report-batch-check/ に移動してから実行します。
中身は次のような構成です。
python/excel-report-batch-check/
README.md
requirements.txt
check_reports.py
rules.json
samples/
sample_invoice_001_ok.xlsx
sample_invoice_002_missing_customer.xlsx
sample_invoice_003_wrong_total.xlsx仮想環境を作ってライブラリを入れる
Windows PowerShellなら、次のように実行します。
python -m venv .venv
.\.venv\Scripts\python -m pip install -r requirements.txtrequirements.txt には openpyxl を入れています。
チェックスクリプトを実行する
サンプルのExcelに対してチェックを実行します。
.\.venv\Scripts\python check_reports.py samples rules.json --out result.csvサンプルにはエラー入りの請求書を含めているため、実行結果はNGになります。
NG: 2件の問題が見つかりました。結果: result.csvこれは失敗ではなく、サンプルとして期待している結果です。result.csv を開き、請求先名の空欄と合計金額ズレが出ていることを確認します。
正常にしたら result.csv が空に近づく
実務では、エラーが出た帳票を直し、再出力し、もう一度スクリプトを流します。
最終的には、result.csv にエラー行が出ない状態を目指します。そこまで確認できたら、次に人がレイアウトや例外表示を見ます。値の確認を先に終わらせてから、見た目の確認に移る という順番にできます。
この順番にすると、目視確認の前に分かる問題を先に潰せます。
実務で最初に目指す状態
まずは必須セルと合計金額だけでよい
最初から帳票のすべてをチェックしようとすると、すぐ重くなります。
まずは、請求先名の空欄、明細金額、小計、消費税、合計金額だけで十分です。この範囲でも、目視確認の前に拾える問題はかなりあります。
特に、改修後の回帰確認では「前と同じ条件で何度も確認できる」ことが効きます。対象を絞ってでも、同じ確認を繰り返せる形にしておく方が使いやすいです。
result.csv が空になるまで繰り返せるようにする
最初の到達点は、result.csv を確認記録として使える状態です。
エラーが出たら、ファイル名とセル位置を見て直す。帳票を再出力する。同じコマンドをもう一度実行する。エラーが消えるまで繰り返す。
ここまでできれば、確認作業はかなり整理されます。人が全部を覚えておく必要がなくなります。
レイアウト確認は最後に人が見る
値の確認が終わっても、帳票確認が終わったわけではありません。
印字位置、文字切れ、改ページ、得意先ごとの表示は、最後に人が見ます。ただし、その時点では空欄や合計金額ズレの確認は終わっています。人は見た目の確認に集中できます。
この分担が大事です。機械で拾えるものを先に拾い、人は人が見るべきところを見る。帳票確認では、この切り分けが一番効きます。
次に増やすならマスタ照合や税区分チェックにする
次に広げるなら、確認項目を少しずつ増やします。
たとえば、請求先名を得意先マスタと照合する。品番が存在するか確認する。税区分ごとに税率を変える。請求書番号の重複を確認する。こうした確認は、空欄と金額ズレの次に検討しやすいです。
ただし、増やすたびに、確認スクリプトの保守も必要になります。最初から大きく作るより、rules.json とCSV出力の形を保ったまま、必要な確認を1つずつ足す方が現場では続けやすいです。
目視確認をなくすのではなく、目視に入る前の単純な確認を減らす。Pythonとopenpyxlは、そのための道具として使うのがちょうどよいです。
