Googleスプレッドシートに列を1本足すと売上集計が壊れる｜列番号で読む危険と直し方

Googleスプレッドシートに列を1本足す。現場から見れば「メモ欄を作っただけ」です。ところがその表をPythonで集計している場合、その日から売上の数字が静かにずれます。エラーは出ません。Slackにはいつもどおり表が届きます。

結論を先に書きます。危ないのは、プログラムが場所を「番号」で覚えている場合です。「B列が1日、C列が2日」と数えている作りだと、頭に列が1本入った瞬間、全部が1つずつずれます。ずれても止まりません。プログラムには、そこに何が入っているべきかの知識が無いからです。

私は清掃業を経営していて、プログラマーではありません。この記事は、うちのMacで実際に毎月動いている着地予想のスクリプトを読み返して、自分のところがどれくらい危ないかを点検した記録です。

うちの売上集計は、日付を「列の番号」で読んでいた
列が1本増えると、具体的に何が起きるか
同じスクリプトでも、行は壊れず列だけ壊れる
「動かないはず」は動く。実際に1枚ずれていた
直し方1：見出しの名前で位置を決める
直し方2：ずれていたら止める見張りを1本置く
直し方3：ずれていたら集計そのものを止める
明日できる点検
まとめ

うちの売上集計は、日付を「列の番号」で読んでいた

うちの利益表は、拠点ごと・月ごとに1枚のシートがあります。縦に項目（清掃数・人数・売上・利益）、横に日付が並ぶ形です。B列が1日、C列が2日、そのままずっと行ってAF列が31日になります。

その1か月ぶんを読み取っている部分が、これです。実物からそのまま持ってきました。

def daily(ws, row):
    return [ws.cell(row, c).value for c in range(2, 33)]  # B..AF = 1..31日

range(2, 33) は「2番目の列から32番目の列まで」という意味です。2番目がB列、32番目がAF列。ちょうど31個です。

この1行には、日付という言葉がどこにも出てきません。「2番目の列は1日である」という前提が、コメントの中にしか書かれていない。プログラム自身は、それを確かめる手段を持っていません。

列が1本増えると、具体的に何が起きるか

たとえば現場が、日付の手前に「備考」の列を1本足したとします。表はこうなります。

列	足す前	足した後
B	1日	備考
C	2日	1日
D	3日	2日
…	…	…
AF	31日	30日
AG	（空）	31日

プログラムは今までどおりB列からAF列までを読みます。すると起きることは3つです。

31日ぶんが集計から丸ごと落ちる。AG列まで読みに行く指示がどこにもないからです。
備考欄を1日として数えてしまう。文字が入っていれば金額には足されませんが、「1日目の枠」としては数え続けます。
経過日数が1日多く出る。うちの着地予想は「累計 ÷ 入力済みの日数 × 月の日数」で出しています。割る数だけが1増えるので、着地予想は実際より低く出ます。

低く出るのが、いちばん気づきにくいと思っています。高く出れば「そんなに行くはずがない」と誰かが引っかかります。少し低いだけの数字は、悪い月だったと解釈されて素通りします。

同じスクリプトでも、行は壊れず列だけ壊れる

ここが今回いちばん学びになったところです。同じスクリプトの中で、行と列で扱いがまったく違いました。

行のほうは、こう書いてあります。

def first_row(ws, label):
    for r in range(1, ws.max_row + 1):
        if str(ws.cell(r, 1).value).strip() == label:
            return r
    return None

これは「一番左の列を上から見て、『売上』と書いてある行を探す」という処理です。番号ではなく名前で探しています。だから行が1本増えても平気です。売上の行が5行目から6行目に動いても、探して見つけてくれます。

一方、列は先ほどの range(2, 33)。番号べた書きです。同じファイルの中に、名前で探す作りと番号で決め打ちする作りが同居していました。

	探し方	行や列が増えたら
行（売上・利益）	ラベルの名前で探す	ついてくる
列（1日〜31日）	番号で決め打ち	ずれる。しかも黙ってずれる

意識して書き分けたわけではなく、単に「日付は絶対に動かないだろう」と思い込んでいただけです。

「動かないはず」は動く。実際に1枚ずれていた

これは想像の話ではありません。別件で利益表の全シート（45枚）を1枚ずつ調べたことがあります。人件費の計算式を書き換える作業の前に、対象が本当に想定どおりの形かを確かめたときです。

そのとき、1枚だけ、項目の配置が1行ずれているシートが見つかりました。誰が、いつ、なぜずらしたのかは分かりません。分かったのは、45枚のうち44枚が同じ形だからといって、45枚目も同じとは限らないということだけです。

そのシートは書き換えの対象から外して、手を触れないことにしました。もし「全部同じ形だ」と決めつけて一気に書き換えていたら、そのシートだけ壊れていたはずです。

同じ作業で、もうひとつ分かったことがあります。時給を書き換えるときに、こちらで数字を決め打ちせず「今そのシートに入っている式から読み取る」形にしていました。実際に調べてみると、シートによって使われている時給が違っていた。決め打ちしていたら、そこも壊していました。

スプレッドシートを何年も使っていれば、表記ゆれも配置のずれも必ず混ざります。うちの場合、シート名にも「7月◯◯」「７月◯◯（全角）」「8月◯◯␣（末尾に空白）」が混在していて、名前で探す処理のほうにも吸収する仕掛けを足しています。きれいに揃っている前提で書いたプログラムは、いつか必ず現実に負けます。

直し方1：見出しの名前で位置を決める

いちばん素直なのは、毎回、見出しを読んで場所を決めることです。「1日はB列だ」と覚えるのをやめて、「1日と書いてある列がどこかを毎回探す」に変えます。

1行目が見出しで、1行1件の形の表（売上台帳のようなCSV）なら、Pythonの標準機能でこう書けます。

import csv

要る列 = {"日付", "拠点", "売上"}

with open("売上.csv", encoding="utf-8-sig", newline="") as f:
    表 = csv.DictReader(f)
    足りない = 要る列 - set(表.fieldnames or [])
    if 足りない:
        raise SystemExit("必要な列がありません: " + "・".join(sorted(足りない)))
    合計 = sum(int(行["売上"] or 0) for 行 in 表)

print(f"売上の合計: {合計:,}円")

csv.DictReader は、1行目を見出しとして覚えて、あとは名前で取り出せるようにしてくれる読み方です。行["売上"] と書けるので、売上の列が3番目でも7番目でも動きます。関係のない列が何本増えても平気です。

そして大事なのは、その次の3行です。必要な列がそろっているかを、集計を始める前に確かめて、無ければ止める。「日付」が「日付」（末尾に空白）に変わっただけでも、ここで気づけます。

直し方2：ずれていたら止める見張りを1本置く

うちの利益表は縦横が逆（横が日付）なので、上の書き方はそのままは使えません。代わりに、集計を始める前に「日付の列が1日から順に並んでいるか」だけを確かめるスクリプトを用意しました。

これは実際にシートの見出し行を読んで確かめるものです。まず --見る を付けて実行し、自分のシートの見出しに何が入っているか（日付なのか、1・2・3という数字なのか）を目で確認してから使ってください。

# -*- coding: utf-8 -*-
"""
利益表の「日付の列」がずれていないか確かめる。
  列チェック.py         … 全シートを点検する。1枚でもずれていたら終了コード1
  列チェック.py --見る   … 見出し行に実際に何が入っているかを表示する
"""
import datetime
import os
import sys

import openpyxl

ブック = os.path.expanduser("~/scripts/keiri/利益表.xlsx")
最初の列 = 2      # B列 = 1日のはず
日数 = 31         # B列〜AF列


def 列記号(n):
    s = ""
    while n > 0:
        n, r = divmod(n - 1, 26)
        s = chr(65 + r) + s
    return s


def 日にちにする(v):
    """見出しのマスから「何日か」を取り出す。分からなければ None"""
    if isinstance(v, datetime.datetime):
        return v.day
    if isinstance(v, (int, float)) and 1 <= v <= 31:
        return int(v)
    s = str(v or "").strip().replace("日", "")
    return int(s) if s.isdigit() and 1 <= int(s) <= 31 else None


def 見出し行を探す(ws):
    """上から数行を見て、1・2・3・4・5 と並んでいる行を見つける"""
    for r in range(1, 7):
        並び = [日にちにする(ws.cell(r, 最初の列 + i).value) for i in range(5)]
        if 並び == [1, 2, 3, 4, 5]:
            return r
    return None


def 点検(ws):
    """おかしいところを文章のリストで返す。空なら正常"""
    r = 見出し行を探す(ws)
    if r is None:
        return ["日付の見出しが見つかりません（列が増減した可能性があります）"]
    問題 = []
    for i in range(日数):
        c = 最初の列 + i
        中身 = ws.cell(r, c).value
        if 日にちにする(中身) != i + 1:
            問題.append(
                f"{列記号(c)}列は{i + 1}日のはずですが「{中身}」でした（見出しは{r}行目）")
            break          # 1つずれたら以降は全部ずれるので、最初の1件だけ出す
    return 問題


def main():
    wb = openpyxl.load_workbook(ブック, data_only=True)
    見るだけ = "--見る" in sys.argv
    だめ = 0

    for 名 in wb.sheetnames:
        ws = wb[名]
        if 見るだけ:
            r = 見出し行を探す(ws)
            見出し = [ws.cell(r, 最初の列 + i).value for i in range(5)] if r else []
            print(f"  {名}: 見出し行={r} 先頭5マス={見出し}")
            continue
        問題 = 点検(ws)
        if 問題:
            だめ += 1
            print(f"  ✖ {名}")
            for x in 問題:
                print(f"      {x}")

    if 見るだけ:
        return 0
    if だめ:
        print(f"\n✖ {だめ}枚のシートで日付の列がずれています。集計を回さないでください。")
        return 1
    print(f"✔ {len(wb.sheetnames)}枚すべて、日付の列は正しく並んでいます。")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

このMacで動かすときは、こう打ちます。/usr/bin/python3 は入っていないので使えません。

~/scripts/keiri/.venv/bin/python ~/scripts/keiri/列チェック.py --見る

見出しが想定どおりだと確認できたら、--見る を外して本番の点検にします。

~/scripts/keiri/.venv/bin/python ~/scripts/keiri/列チェック.py

直し方3：ずれていたら集計そのものを止める

点検スクリプトを作っても、人が思い出したときだけ実行するのでは意味がありません。列が増えたことに気づいていないから困っているのですから。毎月の処理の先頭に組み込みます。

うちの着地予想は、こういう順番で動いています。

Googleスプレッドシートの最新版を取り込む
着地予想を計算する
結果をSlackとMacの通知に流す

ここで大事なのは、1と2のあいだに点検を挟むことです。取り込んだ直後、まだ何も計算していない段階で確かめます。

# 1) 取り込みのあと、計算を始める前に日付の列を点検する
if ! "$(dirname "$0")/.venv/bin/python" 列チェック.py > "$TMP" 2>&1; then
  chuushi "日付の列がずれているため中止しました"
fi

chuushi は、うちのスクリプトにもとから入れてある「中止して人に知らせる」処理です。やることは3つだけです。

Macの通知を音つきで出す
実行ログに理由を書き残す
Slackには投稿しない。前回の正しいレポートも壊さない

最後のひとつが肝心です。怪しいときに何も出さないのは、間違った数字を出すよりずっと安全です。数字が届かなければ人は「今月どうなった？」と聞きに来ます。少しずれた数字が届いた場合は、誰も聞きに来ません。

明日できる点検

プログラムを書き換える前に、まず自分のところが危ないかどうかを確かめるだけでも価値があります。順番に見てください。

集計しているスクリプトを開いて、数字を探す。range(2, 33) [3] iloc[:, 5] ws.cell(行, 7) のような、列を数字で指している場所です。1か所でもあれば、そこが弱点です。
その数字が何を指しているか、コメントに書いてあるかを見る。コメントにしか書いていないなら、プログラムはそれを確かめていません。
スプレッドシートを共同編集している人に聞く。「この表に列を足したいと思ったことはありますか」。あると答えたら、それは時間の問題です。
点検を集計の前に置く。おかしければ止める。止まったことが人に伝わるようにする。

もうひとつ、gitを入れていないMacで作業する場合の決まりごとを付け加えておきます。スクリプトを直す前に、必ず日付を付けたコピーを残してください。うちはこうしています。

cp ~/scripts/keiri/predict.py ~/scripts/keiri/predict.py.bak-$(date '+%Y%m%d-%H%M%S')

直したあとは、手で1回実行して、先月ぶんの数字が前と同じに出るかを確かめます。読み方を変える修正は、過去の数字が1円も変わらないのが正解です。変わったら、その修正は間違っています。

まとめ

列を番号で読むと、列が1本増えた日から黙って別の列を集計します。エラーは出ません。
うちの着地予想は、行は名前で探しているのに、日付の列だけ番号で決め打ちしていました。同じファイルの中で扱いが割れていたのは、意識していなかったからです。
「配置は動かないはず」は動きます。うちでは全45枚を調べて、1枚だけ配置がずれているシートが実際に見つかりました。
直す順番は、①名前で場所を決める ②必要な列がそろっているか集計前に確かめる ③そろっていなければ止めて人に知らせる、の3つです。
迷ったら、間違った数字を出すより、何も出さないほうを選んでください。

「動いているのに数字が違う」という失敗は、これが初めてではありませんでした。取り込みに失敗しても前回のファイルが残っているせいで、正常な顔をした古い数字が出続けたことがあります。あわせて読んでみてください。
→ 売上集計は動いていたのに数字が古かった｜前回のファイルを使い続けていた失敗