• #CF精算表
  • #SUMIFS
  • #Python
  • #Excel自動化
  • #年次推移表
  • #リファクタリング
  • #openpyxl
開発eurekapu-nuxt4メモ

CF精算表パイプラインの大幅強化

朝、年次推移表のExcelを開いて目が止まった。セルをクリックしても数式バーに数値しか表示されない。Pythonで計算した値をベタ打ちしているから当然だが、「この数字はどこから来たのか」をExcel上で追えない。教材として渡すファイルがこれでは、開いた瞬間にブラックボックスに見える。年次推移表をSUMIFS数式に全面置換する作業を起点に、共通モジュール化と5論点への横展開まで一気に走った一日だった。

301借入金に営業経費シートを追加

顧問料と家賃 --- 未払費用と前払費用の対比

借入金の精算表に営業経費シートを追加した。月次の固定費として税理士顧問料33,000円と家賃110,000円を計上する。

この2つを同じシートに並べたのは、経過勘定の対比を見せるためだ。顧問料は後払いなので、役務提供を受けた月に費用が発生し、翌月の支払時までは「未払費用」になる。家賃は前払いなので、支払月に現金が出ていき、翌月の費用発生まで「前払費用」として資産に載る。同じ固定費でもキャッシュフローへの影響タイミングが逆になる。1シートに並べることで、BS科目がどう動くかを追える教材になった。

現金売上と仕入の追加

営業経費だけを追加すると、PLが費用のみで赤字になり、普通預金残高がマイナスに沈む。ファイルを開いたとき「この会社は何で稼いでいるのか」が見えない。教材として不自然だった。

現金売上と現金仕入のシートを追加して解消した。ここでCodexが指摘を入れてきた。generate_daily_purchases 関数が在庫バッファを持つ設計になっており、仕入量が売上量を上回る期間が生じる。教材にはゼロ在庫（仕入=売上原価）のモデルが適切なので、在庫バッファのロジックを削除した。

中間テーブルSUMIFSの簡素化

J列（勘定科目フィルタ）が不要だった

中間テーブルにはSUMIFS数式が並んでおり、J列に勘定科目名のフィルタ条件を持たせていた。しかしデータ構造を見直すと、各行は既に特定の勘定科目に対応している。フィルタ条件が冗長だった。

J列を削除すると、SUMIFS数式から条件が1つ減り、数式の長さが半分近くになった。数式バーを見ただけで何を集計しているか読み取れるようになった。

合計テーブルの新設と撤回

中間テーブルの集計結果をさらに合計テーブルにまとめる案を実装した。しかし動かしてみると、中間テーブルと合計テーブルで同じ数値を二重に持つだけで、CFWSから参照するのは結局どちらか一方だけだった。冗長なレイヤーを一枚剥がして合計テーブルを削除した。

合計テーブル廃止に伴い、利息のINDEX/MATCH数式のスコープを中間テーブルの該当範囲に限定し直した。

年次推移表のSUMIFS化

ここが今日の最大の作業だった。

構造の変更

ベタ打ちの値を消し、仕訳シートを参照するSUMIFS数式に全面置換した。年次推移表の各セルが「仕訳シートの取引日がFrom〜Toの範囲で、かつ勘定科目が一致する行の金額を合計する」数式になる。

追加した要素:

  • From/To行: 各年度列のヘッダ直下にFrom日付とTo日付を配置。SUMIFS数式の日付範囲条件がこの行を参照する
  • BS科目のFrom固定: PL科目のFromは期首日（4/1）だが、BS科目のFromは設立日に固定。BS残高は設立以来の累積だから、期首リセットしない

仕訳シートの日付がテキスト文字列だった

SUMIFS数式を入れて再計算させると、全セルが0を返した。数式の構文は正しいのに、SUMIFSが1行もヒットしない。

仕訳シートの取引日列をクリックすると、セルの表示形式が「文字列」になっていた。Pythonで仕訳データを書き込む際、日付をISO文字列（"2024-04-01"）としてセルに入れていた。SUMIFSの日付比較は数値型のシリアル値を期待するため、文字列のままでは条件が一致しない。

# Before: 文字列として書き込み
ws.cell(row=r, column=1, value="2024-04-01")

# After: date型に変換して書き込み
from datetime import date
ws.cell(row=r, column=1, value=date(2024, 4, 1))
ws.cell(row=r, column=1).number_format = "YYYY-MM-DD"

date型に変換して書き込むようにしたところ、SUMIFSが正しく集計を始めた。表示上は同じ 2024-04-01 でも、Excelの内部表現が文字列と数値で異なる。

ACCOUNT_DBのキー名が'sign'だった

年次推移表のSUMIFS数式で借方・貸方の符号を正しく反映するには、ACCOUNT_DBから勘定科目の正常残高方向を取得する必要がある。コード上で account['normal'] と書いて KeyError が出た。

ACCOUNT_DBの定義を開いて確認すると、キー名は 'normal' ではなく 'sign' だった。過去のどこかでリネームされたまま、年次推移表の新コードが旧名を参照していた。

# Before: KeyError
sign = ACCOUNT_DB[account_name]['normal']

# After: 正しいキー名
sign = ACCOUNT_DB[account_name]['sign']

補助科目の統合

二重シート問題

これまで年次推移表には2種類のシートがあった。「年次推移表」（補助科目なし）と「年次推移表_補助科目あり」の2枚だ。両方に同じ親科目の数値が入っており、メンテナンスコストが二重に発生していた。

補助科目ありシートを廃止し、アンダースコア命名（未払費用_未払利息、未払費用_未払営業経費）で1シートに統合した。親科目の行には子科目のSUM数式を入れ、展開しなくても合計値が見える構造にした。

xlsx_helpers.py共通モジュール化

Phase 1: 5スクリプトから共通関数を抽出

借入金・株主資本・固定資産・運転資本・貸付金の5つの生成スクリプトに、同じセル書き込み関数やフォーマット設定関数が散在していた。

xlsx_helpers.py を新設し、共通関数を集約した。対象は以下の5スクリプト:

  • gen_301_borrowing.py
  • gen_302_equity.py
  • gen_303_fixed_asset.py
  • gen_304_working_capital.py
  • gen_305_lending.py

各スクリプトから write_header_row, apply_number_format, set_column_width などの関数を抽出し、importに切り替えた。

Phase 2: auto_fit_columnsの移動と改善

列幅を自動調整する auto_fit_columns 関数が、数式セルの文字列長（=SUMIFS(...) のような長い文字列）をそのまま幅計算に含めていた。数式が長い列だけ異常に幅が広がる。

数式セルを幅計算から除外し、最大幅を20文字にキャップして修正した。この関数も xlsx_helpers.py に移動し、全スクリプトから共通利用する形にした。

def auto_fit_columns(ws, max_width=20):
    for col in ws.columns:
        lengths = []
        for cell in col:
            if cell.data_type == 'f':  # 数式セルは除外
                continue
            if cell.value is not None:
                lengths.append(len(str(cell.value)))
        if lengths:
            width = min(max(lengths) + 2, max_width)
            ws.column_dimensions[col[0].column_letter].width = width

5論点への横展開

SUMIFS化と共通モジュール化を301借入金で確立した後、残り4論点（302株主資本、303固定資産、304運転資本、305貸付金）に同じ変更を適用した。

各論点で固有の対応が必要だった箇所:

  • 303固定資産: パソコン（工具器具備品）を資産として追加。償却計算表に1行増える
  • 302株主資本: BS科目のFrom日付を設立日に固定する処理が、他の論点と微妙に異なる（資本金は増減がないためSUMIFSではなく固定値参照にした）

5論点すべてで年次推移表のSUMIFS化とxlsx_helpers.pyへの移行が完了した。

Excel生成ルールの整備

横展開の過程で、スクリプト間でフォーマットの揺れが目についた。ルールとして明文化した主な項目:

  • 合計列にはSUM関数を必ず入れる: ベタ打ちの合計値は禁止。数式で集計することで検算が自動化される
  • A列スペーサーは新規シートのみ: 既存シートにA列を挿入すると、他のシートからの参照がズレる
  • auto_fit_columnsの最大幅20、数式除外: 前述のルール

その他の整備

「当期」表示から実年度名に統一

年次推移表の列ヘッダが「前々期」「前期」「当期」という相対表記だった。ファイルを翌年に開いたとき「当期」がどの年度を指すのか分からなくなる。「2022年度」「2023年度」「2024年度」のような実年度名に統一した。

IBフォーマット適用

全シートにIB（投資銀行）フォーマットのカラーリングルールを適用した。入力セルは青字、参照セルは黒字、ハードコードは緑字。セルの性質が色で区別できる。

命名規則違反の修正

一部のスクリプトやシート名が CF_xxx プレフィックスを使っていた。現行の命名規則は cf_xxx（小文字）統一なので、grepで CF_ を洗い出し全箇所を cf_ に置換した。

振り返り

SUMIFSの日付型不一致で全セルが0を返したとき、数式の構文ばかりを疑って30分近く空転した。セルをダブルクリックして参照先のハイライトを確認し、「条件は合っているのにヒットしない」と首をひねった。仕訳シートの日付列を右クリックして「セルの書式設定」を開いた瞬間、「文字列」の表示が目に飛び込んできて原因がつながった。見た目が同じでも内部表現が違う --- この落とし穴を踏んだことで、以後のスクリプトではdate型で書き込むことを徹底するようになった。

ACCOUNT_DBの 'sign' キー問題も似た構造だ。「あるはずのキーがない」というエラーメッセージだけ見て新規追加しかけた。定義ファイルを開いてキー名の一覧を目で確認したら、リネームされた痕跡がそのまま残っていた。辞書のキー名を変更したら、参照側を全文検索で洗い出さないと漏れる。

xlsx_helpers.pyへの共通化で、5スクリプトに散らばっていた同一コードが1箇所に集まった。auto_fit_columnsの数式幅問題のような修正が、1回の変更で全論点に行き渡る。「同じ関数を5回コピペしている」と気づいた時点でもっと早く手を動かせたはずだ、という反省が残る。