Excel帳票を受け取ったあと、空欄や金額ズレを目で追う作業はかなり危ないです。
件数が少ないうちは何とかなりますが、明細が増えたり、似たような帳票を何枚も見るようになると、空欄の見落としや合計金額の確認漏れが出ます。人間だもの・・・。
ということで、今回は検証用の請求書Excelを作り、Pythonのopenpyxl で読み取り自動でチェックする方法を説明します。
最後は、見つけた不備をCSVに出します。 Excelを自動で直すところまではやりませんが、 まずは、人が確認すべき箇所を一覧にするところまでに絞ります。
この記事で使っているチェック用スクリプト、サンプルExcel、期待結果CSVはGitHubに置いています。同じデータで動かして確認したい場合は参照してください。
Excel帳票で確認したいこと
今回の対象は、請求書のようなExcel帳票です。 帳票には、請求番号、取引先名、請求日、請求金額があり、その下に明細行があるものを例とします。
空欄は後続処理で止まりやすい
確認したいのは、まず空欄です。
たとえば 取引先名が空のままになっていないか。 明細行の品目コード、作業内容、数量、単価、金額が抜けていないか。 ここが抜けていると、後続の確認や請求処理で止まります。
空欄は、帳票を開いた瞬間には見つけやすそうに見えます。 ただ、似た形の帳票を何枚も見ると、見ているつもりのセルを飛ばします。 特に、明細行の一部だけ空欄になっている場合は、表の形が残っているので見落としやすいです。
金額ズレは目視だと気づきにくい
もうひとつ見るのが、金額のズレです。
明細行の金額を足した値と、帳票上の請求金額が一致しているかを確認します。 数字が合わない場合、帳票側の合計が間違っているのか、明細の入力が抜けているのかを人が見に行く必要があります。
この記事では、Excelそのものを更新して正しい値に直すところまでは扱いません。 先に、どのセルに何の不備があるのかをCSVに出します。 直す作業の前に、確認対象をはっきりさせるためです。
検証用のExcel帳票
ここでは、請求書に見立てたExcelを1つ作りました。
帳票に入れる項目
ヘッダー部分には、請求番号、取引先名、請求日、請求金額を置きます。 明細部分には、明細No、品目コード、作業内容、数量、単価、金額を置きます。
確認対象は次のセルです。
| 場所 | 項目 | 確認内容 |
|---|---|---|
| B3 | 請求番号 | 空欄でないこと |
| B4 | 取引先名 | 空欄でないこと |
| B5 | 請求日 | 空欄でないこと |
| B6 | 請求金額 | 明細金額の合計と一致すること |
| B10:F13 | 明細行 | 品目コード、作業内容、数量、単価、金額が空欄でないこと |
帳票チェックで最初に決めるべきなのは、どのセルを必須にするかです。
ここが曖昧なままだと、スクリプト側で何をエラーにしてよいか決まりません。 この例では、セル位置が固定された帳票として扱います。
tomo先に「どのセルを必須にするか」を決めておくと、あとでチェック処理がぶれません。ここでは、ヘッダーの必須項目と明細行の必須項目を分けて見ます。
わざと入れる不備
作ったExcelには、最初から不備を入れています。 正常な帳票ではなく、チェック処理が何を拾うのかを見るためのデータです。
入れている不備は次の4つです。
B4の取引先名を空欄にするC12の明細3の作業内容を空欄にするF13の明細4の金額を空欄にするB6の請求金額を120000にし、明細合計115000と合わない状態にする
この状態なら、空欄チェックと金額チェックの両方が動いているかを見られます。 空欄だけを拾えても不十分ですし、合計ズレだけ拾えても帳票確認としては足りません。
わかりやすくするため、先ほどのExcelで、不備となる部分に色を付けました。オレンジが空白、赤が金額不一致です。
openpyxlで帳票を読み取る
次に、作ったExcelを読み取ります。 ここからがチェック本体です。 読み取った値をそのまま眺めるのではなく、空欄と金額ズレを Issue として集め、最後にCSVへ出します。
ブックとシートを開く
Excelファイルは load_workbook で開きます。 今回は、帳票に入っている値を確認したいので data_only=True を付けています。
wb = load_workbook(INPUT_PATH, data_only=True)
ws = wb["invoice"]
issues: list[Issue] = []data_only=True は、数式セルを読むときに関係します。
openpyxlのドキュメントでは、数式そのものを読むか、最後に保存された計算結果を読むかをこの指定で切り替える説明があります。 今回は固定値中心ですが、帳票では合計欄が数式になっていることも多いので、読み方を最初に決めておきます。
ただし、openpyxl自身がExcelの数式を再計算してくれるわけではありません。 数式結果を読む場合は、Excel側で保存済みの計算結果を読む前提になります。 この点は、金額チェックで地味にはまったりしますのでご注意を。
tomo数式セルを読む帳票では、data_only=True を付けるかどうかで取得できる値が変わります。今回は、Excel上で保存済みの計算結果を読む前提にしています。
必須項目の空欄を見る
空欄判定は、None と空白だけの文字列をまとめて見る関数にしています。 Excel上で何も入っていないセルは None として読めます。 ただ、スペースだけ入っているセルも帳票上は空欄扱いにしたいので、文字列の場合は strip() して見ます。
def is_blank(value: Any) -> bool:
if value is None:
return True
if isinstance(value, str) and value.strip() == "":
return True
return Falseこの関数を使って、ヘッダー部分と明細部分を同じ考え方で確認します。 ヘッダーはセル番地と項目名が固定なので、辞書で持たせています。
required_header_cells = {
"B3": "請求番号",
"B4": "取引先名",
"B5": "請求日",
"B6": "請求金額",
}
for cell, item in required_header_cells.items():
add_blank_issue(issues, cell, item, ws[cell].value)明細行は、10行目から13行目までを見ています。 明細Noが空なら、その行は対象外にします。 明細Noがある行では、品目コード、作業内容、数量、単価、金額を必須として見ます。
明細合計と帳票の合計金額を比べる
空欄を見ながら、明細金額も合計します。 金額欄が空欄の行は、空欄エラーとして拾ったうえで、合計からは外しています。
detail_total = 0
for row in range(10, 14):
row_no = ws.cell(row=row, column=1).value
if is_blank(row_no):
continue
required_detail_cells = {
f"B{row}": "品目コード",
f"C{row}": "作業内容",
f"D{row}": "数量",
f"E{row}": "単価",
f"F{row}": "金額",
}
for cell, item in required_detail_cells.items():
add_blank_issue(issues, cell, f"明細{row_no}の{item}", ws[cell].value)
amount = ws.cell(row=row, column=6).value
if not is_blank(amount):
detail_total += int(amount)最後に、帳票上の請求金額 B6 と明細合計を比べます。 一致しなければ、total_mismatch としてCSVに出す対象にします。
billed_total = ws["B6"].value
if not is_blank(billed_total) and int(billed_total) != detail_total:
issues.append(
Issue(
issue_type="total_mismatch",
cell="B6",
item="請求金額",
value=billed_total,
message=f"請求金額 {billed_total} と明細合計 {detail_total} が一致しません",
)
)ここで大事なのは、単に False を返して終わらせないことです。 どのセルで、何の項目が、なぜエラーなのかを残します。 あとで人がExcelを開いて直すなら、この情報がないと困ります。
チェック結果をCSVに出す
チェック結果は、画面表示だけで終わらせません。 CSVに残します。 Excelを修正する人、レビューする人、あとから確認する人が同じ結果を見られるようにするためです。
見つけた不備を行単位で残す
CSVには、issue_type、cell、item、value、message を出します。 エラーの種類、セル番地、項目名、実際の値、説明を分けると、あとでフィルタしやすくなります。
with OUTPUT_PATH.open("w", encoding="utf-8-sig", newline="") as f:
writer = csv.DictWriter(
f,
fieldnames=["issue_type", "cell", "item", "value", "message"],
)
writer.writeheader()
for issue in issues:
writer.writerow(issue.__dict__)encoding="utf-8-sig" にしているのは、ExcelでCSVを開いたときの文字化けを避けるためです。 UTF-8のままでも問題ない環境なら、通常の utf-8 でも構いません。
実行結果を見る
実行コマンドは次です（gitからclone前提としています）。
git clone https://github.com/nexive-tech/nexive-lab.git
cd nexive-lab/python/openpyxl-excel-report-check
python -m pip install -r requirements.txt
python create_sample_workbook.py
python check_invoice_report.py samples/sample_invoice_report_check.xlsx --out check_results.csv実行すると、Excelを作ったあとにチェック処理が走り、CSVが出ます。
created: samples/sample_invoice_report_check.xlsx
checked: samples/sample_invoice_report_check.xlsx
issues: 4
output: check_results.csv出力されたCSVは次です。
この結果なら、取引先名、明細3の作業内容、明細4の金額が空欄だと分かります。 さらに、請求金額 120000 に対して、明細合計が 115000 しかないことも分かります。
このCSVを見てからExcelを開けば、どこを見るべきかが決まっています。 全部のセルを目で追うより、だいぶ楽です。
このチェックで拾えるもの、拾えないもの
今回のスクリプトは、帳票チェックの入口です。 何でも拾えるわけではありません。
拾えるものと拾えないものを分けておかないと、あとで「自動チェックしたのに漏れた」という話になります。
空欄と金額不一致は拾える
セル位置が決まっている帳票なら、必須項目の空欄は拾いやすいです。 今回なら、B4 の取引先名、C12 の作業内容、F13 の金額が空欄として出ています。
金額不一致も、計算ルールが決まっていれば拾えます。 今回のように、明細行の金額を合計し、帳票上の請求金額と比べるだけなら難しくありません。
ただし、どの行までが明細なのか、税額をどう丸めるのか、値引きをどこで引くのかは帳票ごとに違います。 実際の帳票に合わせるなら、ここを先に決めます。 スクリプトを書く前に決めないと、コードの中に例外処理が増えて読みにくくなります。
レイアウト変更や数式の再計算は別で見る
このチェックでは、セルの見た目までは見ていません。 印字位置、文字切れ、改ページ、罫線、セル結合の崩れは、人がExcelを開いて確認する範囲です。
また、openpyxlはExcelの計算エンジンではありません。 数式の計算結果を読む場合でも、Excel側で保存された値を読む前提になります。 帳票の数式そのものが壊れているか、最新状態に再計算されているかまで見るなら、別の確認が必要です。
だから、最初から帳票確認を全部自動化しようとしない方がよいです。 まずは、空欄と金額ズレのように機械で拾いやすいところをCSVに出す。 そのあと、人が見るべきレイアウトや表示を確認する。 この分け方の方が、運用に乗せやすいです。
まとめ
Excel帳票の確認は、目視だけに寄せると抜けやすいです。 特に、必須項目の空欄と金額ズレは、同じ形の帳票を何枚も見るほど見落としやすくなります。
openpyxlでExcelを読み取れば、固定セルや明細行の空欄を一覧化できます。- 明細金額の合計と帳票上の請求金額を比べれば、金額ズレもCSVに残せます。
- CSVに
issue_type、cell、item、messageを出しておくと、あとで人が確認する場所を絞れます。
今回の検証では、取引先名、明細3の作業内容、明細4の金額が空欄として出ました。 請求金額 120000 と明細合計 115000 のズレも出ています。
この形まで作っておくと、次に確認項目を増やすのも楽です。 たとえば、請求日の形式、品目コードの存在チェック、複数ファイルの一括チェックを足せます。 いきなり全部を自動化するより、まずは値の確認結果を残すところから始める方が、あとで直しやすいです。
