スプレッドシートの空欄を「0円」として集計すると、合計は合っているのに、着地予想（月末にいくらになりそうかの見込み）だけが低く出ます。エラーは出ません。Slackにはいつもどおり表が届きます。
結論を先に書きます。0円と未入力は、まったく違う情報です。0円は「その日は売上が無かった」という事実。空欄は「まだ誰も入力していない」という、事実が分からない状態。この2つを同じ扱いにすると、足し算は無事でも割り算が壊れます。
私は清掃業を経営していて、プログラマーではありません。この記事は、うちのMacで毎月動いている着地予想のスクリプトと、実際に出力されたレポートを読み返して、どこで低く出るのかを確かめた記録です。
合計は無事なのに、なぜ予想だけ狂うのか
表計算ソフトの合計は、空欄を無視します。空欄が10個あっても、合計は変わりません。だから合計だけを見ている限り、空欄には一生気づけません。
狂うのは、割り算をしたときです。うちの着地予想は、この式で出しています。
着地予想 ＝ 今月の累計 ÷ 入力済みの日数 × 月の日数ここで空欄を「0円の日」として日数に数えてしまうと、割られる数（累計）はそのままで、割る数（日数）だけが増えます。1日あたりの平均が下がり、そのまま月末の見込みも下がります。
| 空欄を数えない（正しい） | 空欄を0円として数える | |
|---|---|---|
| 累計 | 10日ぶん | 10日ぶん（同じ） |
| 割る日数 | 10日 | 14日 |
| 1日あたり | 10分の1 | 14分の1 |
| 着地予想 | 正しい | 約7割（3割低い） |
3割低く出るのは、いちばん見つけにくいずれ方だと思っています。倍に出れば「そんなはずはない」と誰かが引っかかります。少し低い数字は、「今月は苦戦している」と解釈されて素通りします。
うちの表は、未入力の日も0として見える
やっかいなのは、うちの利益表では未入力の日も休業日も、プログラムからは同じ0に見えることです。これは実データで確かめてあって、経理の作業ルールにもそのまま書いてあります。
未入力の日も休業日も同じ 0。売上の値だけでは区別できない。経過日数は「売上が0でない最後の日」で判定する。非ゼロ日の個数ではない。
マスが本当に空っぽなら、プログラムは「空っぽ」だと分かります。ところが実際の表には計算式が入っていて、まだ何も入力していない日にも、計算の結果として0が表示されてしまいます。見た目は0円の日と区別がつきません。
だから、うちは値そのものではなく「並び方」で判断しています。実物のコードはこうなっています。
active = [i for i, v in enumerate(svals) if isinstance(v, (int, float)) and v != 0]
if not active:
notes.append(f"{loc}: 未入力")
continue
elapsed = active[-1] + 1
closed = elapsed - len(active)やっていることは3つだけです。
active… その月のうち、売上が0でない日を全部拾うelapsed… そのうちいちばん後ろの日を「ここまで入力済み」とみなすclosed… 入力済みの範囲の中にある0の日を「休業日」として数える
1日も入っていなければ、その拠点は計算から外して「未入力」と書き残します。
数え方は3通りある。どれを選ぶかで答えが変わる
ここが今回いちばん整理になったところです。同じデータでも、日数の数え方は3通りあって、それぞれ違う方向にずれます。
| 数え方 | 何が起きるか |
|---|---|
| ①月の日数で割る（空欄を0円として全部数える） | 低く出る。まだ来ていない日まで「売上0の日」に数えるため |
| ②売上が0でない日の個数で割る | 高く出る。途中の休業日が日数から消えるため |
| ③0でない最後の日までを日数とする | 途中の休業日も含めたうえで、未来の空欄は数えない |
②は一見よさそうに見えて危ないです。月の途中で1日休んだら、その日を「無かったこと」にして平均を出すので、実力より良い数字が出ます。間違った方向に間違えるなら、まだ低く出るほうがましです。だからうちは③にしています。
実際、入力は4日ぶん遅れていた
これは想像の話ではありません。うちは毎月15日の朝8時に、着地予想を自動で計算してSlackに流しています。保存されているレポートを見ると、15日に実行しているのに、入力済みの日数は5拠点とも10日ぶんでした。前日ぶんまで入っていれば14日のはずですから、4日ぶんが空欄だったことになります。
この4日を0円として一緒に割っていたら、着地予想は本来の10÷14、およそ7割の数字になっていました。金額はここには書きませんが、3割低い数字がそのままSlackに流れていたということです。
もうひとつ、別の月のレポートで分かったことがあります。同じ日に同時に計算したのに、拠点によって入力済みの日数が違いました。4拠点は19日ぶん入っているのに、1拠点だけ13日ぶんしか入っていない。同じ会社の同じ表でも、入力の進み方は現場ごとにばらばらです。
もし「今日は22日だから22で割る」という作りにしていたら、その拠点だけ13÷22、6割ほどの数字になっていました。しかも全社合計にそれが混ざるので、他の拠点まで巻き添えで低く見えます。
売上の行で数える。利益の行で数えてはいけない
「入力がどこまで進んでいるか」を調べるとき、どの行を見るかも大事でした。うちの表では、利益の行を見てはいけません。
拠点によっては、まだ来ていない先の日にも固定費があらかじめ入力されているからです。人件費や決まった経費を先に並べておく運用です。利益の行で「値が入っている最後の日」を探すと、来月・再来月まで「もう入力済み」と判定してしまいます。
売上の行なら、実際に働いた日にしか数字が立ちません。だから判定は売上の行だけで行います。これも作業ルールに「利益の行を経過日数の判定に使ってはいけない」と明記してあります。同じ表の中でも、行によって信用できる度合いが違うということです。
直し方1：表のほうで0と空欄を分けてもらう
いちばん筋がいいのは、入力する人に「休業日は0と書く」「まだ入力していない日は空欄のままにする」と決めてもらうことです。プログラム側はこう書けます。
if v is None:
未入力の日 # まだ誰も入力していない
elif v == 0:
休業した日 # 入力されている。売上が無かったという事実None は「マスが空っぽ」という意味です。この形にできれば、判定は一行で済みます。
ただし条件があります。そのマスに計算式を入れないことです。計算式が入っていると、入力がなくても結果の0が返ってきて、空っぽには見えません。うちの表はすでに計算式で組まれていて、そこを作り直すのは現場の入力方法ごと変えることになるので、今のところ手を付けていません。
直し方2：入力が止まっていないか、集計の前に見張る
表を直せない場合は、集計を始める前に「どこまで入力されているか」を数えて、遅れていたら止めるのが現実的です。うちの表の形（縦に項目、横に1日〜31日）に合わせた点検スクリプトを載せます。
まず --見る を付けて実行し、自分のところの入力状況が想定どおりに読めているかを目で確かめてから本番に使ってください。金額は一切表示しません。日数だけを見ます。
# -*- coding: utf-8 -*-
"""
利益表の入力が止まっていないかを確かめる。金額は表示しない。
空欄チェック.py … 今月を点検する。遅れがあれば終了コード1
空欄チェック.py --見る … 拠点ごとの最終入力日を表示するだけ
空欄チェック.py --許容 3 … 何日までの遅れを許すか（既定は2日）
"""
import datetime
import os
import sys
import unicodedata
import openpyxl
ブック = os.path.expanduser("~/scripts/keiri/利益表.xlsx")
拠点 = ["岐阜", "羽島", "四日市", "沼津", "下呂"]
売上ラベル = {"下呂": "売上"} # 書いていない拠点は「清掃売上」
既定の許容日数 = 2
def そろえる(s):
"""シート名を突き合わせ用にそろえる（全角数字→半角、空白を除去）"""
# " " は全角スペースのこと
return unicodedata.normalize("NFKC", s).replace(" ", "").replace(" ", "")
def 行を探す(ws, ラベル):
for r in range(1, ws.max_row + 1):
if str(ws.cell(r, 1).value).strip() == ラベル:
return r
return None
def 最終入力日(ws, 行):
"""売上が0でない最後の日を返す。1日も入っていなければ0"""
最後 = 0
for i in range(31): # B列=1日 … AF列=31日
v = ws.cell(行, 2 + i).value
if isinstance(v, (int, float)) and v != 0:
最後 = i + 1
return 最後
def main():
許容 = 既定の許容日数
if "--許容" in sys.argv:
許容 = int(sys.argv[sys.argv.index("--許容") + 1])
見るだけ = "--見る" in sys.argv
今日 = datetime.date.today()
入っているはずの日 = 今日.day - 1 # 前日ぶんまでは入力済みとみなす
if 入っているはずの日 < 1:
print("今日は1日なので点検しません。")
return 0
wb = openpyxl.load_workbook(ブック, data_only=True)
問題 = []
for loc in 拠点:
名 = f"{今日.month}月{loc}"
候補 = [s for s in wb.sheetnames if そろえる(s) == そろえる(名)]
if not 候補:
問題.append(f"{loc}: 今月のシートがありません")
continue
ws = wb[候補[0]]
行 = 行を探す(ws, 売上ラベル.get(loc, "清掃売上"))
if 行 is None:
問題.append(f"{loc}: 売上の行が見つかりません")
continue
最後 = 最終入力日(ws, 行)
遅れ = 入っているはずの日 - 最後
if 見るだけ:
print(f" {loc}: 最終入力 {最後}日 / 遅れ {遅れ}日")
continue
if 最後 == 0:
問題.append(f"{loc}: 今月まだ1日も入力されていません")
elif 遅れ > 許容:
問題.append(f"{loc}: {最後}日までしか入っていません（{遅れ}日ぶん未入力）")
if 見るだけ:
return 0
if 問題:
print("✖ 入力が追いついていない拠点があります。着地予想は低く出ます。")
for x in 問題:
print(" " + x)
return 1
print(f"✔ {len(拠点)}拠点とも、前日ぶんまで入力されています。")
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のあいだです。取り込んだ直後、まだ何も計算していない段階で確かめます。
# 取り込みのあと、計算を始める前に入力の遅れを点検する
if ! "$(dirname "$0")/.venv/bin/python" 空欄チェック.py > "$TMP" 2>&1; then
chuushi "入力が追いついていないため中止しました"
fichuushi は、うちの月次スクリプトにもとから入れてある「中止して人に知らせる」処理です。やることは3つです。
- Macの通知を音つきで出す
- 実行ログに理由を書き残す
- Slackには投稿しない。前回の正しいレポートも壊さない
正直に書いておくと、この点検はまだうちの月次処理には組み込んでいません。今入っているのは、拠点まるごと未入力だったときにレポートの末尾へ「注: 未入力」の1行を出す仕組みだけです。レポート全文がそのままSlackに流れるので注記も届きますが、途中まで入力されていて残りが空欄という、いちばん起きやすい形は黙って通ります。ここが今の弱点です。
明日できる点検
- 集計スクリプトの割り算を探す。
/や÷で割っている場所の「割る数」が、どこから来ているかを見ます。月の日数や今日の日付をそのまま使っていたら、そこが弱点です。 - 空欄を0にしている場所を探す。
or 0fillna(0)int(値 or 0)のような書き方です。合計を出すだけなら無害ですが、その値を数える対象にもしているなら危険です。 - 先月のレポートを開いて「入力日数」を見る。実行日と比べて何日遅れていたかを確かめます。うちは4日でした。この日数の差が、そのまま予想のずれになります。
- 入力する人に締めのタイミングを聞く。「前日ぶんはいつ入りますか」。答えが人によって違うなら、集計の実行日はいちばん遅い人に合わせるか、点検で止めるかのどちらかです。
スクリプトを直す前に、日付を付けたコピーを必ず残してください。うちのMacにはgitが入っていないので、これが唯一の戻し道です。
cp ~/scripts/keiri/predict.py ~/scripts/keiri/predict.py.bak-$(date '+%Y%m%d-%H%M%S')直したあとは手で1回実行して、完了した月の数字が前と1円も変わらないことを確かめます。数え方を直す修正は、過ぎた月の答えが変わらないのが正解です。
まとめ
- 空欄を0円として集計しても合計は変わりません。だから合計を見ていても気づけません。狂うのは平均と着地予想です。
- 空欄を日数に数えると、割る数だけが増えて予想が低く出ます。うちの実例では、15日に実行して入力は10日ぶん。差の4日を数えていたら3割低く出ていました。
- 日数は「売上が0でない最後の日」で数えます。非ゼロの日の個数で数えると、今度は休業日が消えて高く出ます。
- 入力の進み方は拠点ごとに違います。1つの拠点の遅れが、全社合計まで低く見せます。
- できるなら表のほうで0と空欄を分ける。できないなら、集計の前に入力の遅れを数えて、遅れていたら止める。
「動いているのに数字が違う」失敗は、これが初めてではありませんでした。日付の列が1本ずれただけで、同じように静かに低く出ます。あわせて読んでみてください。
→ Googleスプレッドシートに列を1本足すと売上集計が壊れる｜列番号で読む危険と直し方
コメント