土曜日がいっぱい

曜日ごとの集計処理を作成中に不思議な出来事に遭遇した。
最初は何が起こっているのか皆目見当もつかなかった。
まさかバグ？
いやいや、何かやり方が間違っているはず。

というわけで調査してみた。

１．起こった現象

下のような感じの、日々の売上を記録するような表があって、これから、曜日ごとの売上を分析するような数式を作っていたところ、

土曜日の平均(AVERAGE)だけが実際の数値と合わない。

というようなことが起こった。

なお、分析は週単位で区切って行った。

例えば、４月５日から３週間分、というように。

また、曜日の判定にはWEEKDAY関数を使っている。

現象を、もう少し詳しく説明すると、
ある数週間分の平均までは正常だが、それを越えると平均値がどんどん減り始める。

まとめると、
１．分析する範囲を週単位で増やして行くと、
２．土曜日だけ、
３．平均値が減ってい行く。
というわけだ。

２．原因推測

１．まず考えられるのは、分析する範囲を増やして行くと異常が起こることから、
範囲の設定
に問題がないかということ。

２．そしてもう一つは、土曜日だけに異常が見られることから、
日付の処理
に関して問題があるのではないかということ。

３．それから、平均値が減るということは、分子の合計が減っているか、分母の個数が増えているということだ。しかし、分析週数を増やして行く過程で起こっていることから考えてみれば、合計値が減っているとは考え難い。従って、何らかの原因で
個数が増えている
のではないか。

大きく分けるとこれら３つについて、考える必要があるだろう。


そういえば、週数を増やして行くと、まだ日付が入力されていないセルが分析範囲に入ってきている。この辺が何か怪しい。
しかし、土曜日以外の曜日については、分析週数を増やして行っても、異常は起きない。
よくわからないが、土曜日だけがおかしいということで、曜日の判定に使っているWEEKDAY関数の動作を調査した方がよさそうだ。

３．調査する内容

１．範囲の設定について
分析範囲はOFFSET関数を使って可変にしてあったので、これを直接指定にして確認してみた。
しかし結果は変わらず。相変わらず同じ現象が起こった。
従って、範囲の設定については問題ないだろう。

２．日付の処理について、
土曜日だけおかしいということで、WEEKDAY関数を中心に調査してみた。
特に気になるのは、

WEEKDAY関数で未入力セルを参照するとどうなるのか？

というところだ。

４．調査結果

で、その結果が下の表だ。

なるほど。
未入力セルは日付シリアル値に変換するとゼロ扱いなのか。

そこまでは解るとして、なぜWEEKDAY(0)が土曜日(7)になるのか？

そこで今度は日付シリアル値の入力されたセルをベースに再調査した。

なお、##...は負の値や大きすぎる値を日付表示にしようとすると、こう表示される。ということらしい。
そして、その場合、WEEKDAY関数は#NUM!!エラーを吐くようだ。

さて、肝心の土曜日について見てみると・・・
・・・これは・・・
シリアル値”１”に対応する”1900/1/1”は日曜日。
だから、その前日のシリアル値”０”に対応する”1900/1/0”は土曜日。
という理屈だろうか？

実際のところは不明だが、おそらくWEEKDAY関数の内部処理の都合だろう。
というのも、想像するに、WEEKDAY関数の中身はただ日付シリアル値を7で割った余りを計算しているだけじゃないかと思うからだ。

・・・まあ、例外処理とかまで考えると、そう単純じゃなさそうだが、当たらずとも遠からずと言ったところか。

で、結局原因は、
１．分析範囲を広げていくと、日付未入力のセルが含まれてしまう。
２．日付未入力のセルは日付シリアル値０とエクセルは認識する。
３．日付シリアル値０は土曜日。
４．土曜日の平均値を求めるときの分母だけが増える。
これにより、
１．分析する範囲を週単位で増やして行くと、
２．土曜日だけ、
３．平均値が減ってい行く。

という現象が発生していたわけだ。

５．対策

さて、これで原因はわかった。
あとは、これをどう防ぐかだ。

WEEKDAY関数に０を与えたときの動きはどうしようもないので、０を与えないようにする必要がある。
一方、未入力セルだった場合には、WEEKDAY関数に渡せる引数が何かあるかと言えば・・・
日付シリアル値の範囲外の値を渡して意図的にエラーを発生させるか・・・
いや、そもそもWEEKDAY関数を使わないようにした方が簡単だろう。

というわけで、ISBLANK関数を使ってIF関数を入れ子にして、未入力セルの処理を分けることで対応することにした。

改良前：
{=AVERAGE(IF(D10<>TEXT(WEEKDAY(分析期間),"aaa"),"",分析範囲))}

改良後：
{=AVERAGE(IF(ISBLANK(分析期間),"",IF(D10<>TEXT(WEEKDAY(分析期間),"aaa"),"",分析範囲)))}
※D10="土"
これで、問題は無事に解決された。

６．疑問点浮上

と、ここまで書いて、別の問題が隠れていたことに、今更ながら気が付いた。それは、
例え、未入力セルが土曜日だと判断されても、そのセルに対応する売上個数の列も未入力セルなので、AVERAGE関数の仕様としては、そのセルは無視されて、平均値計算には影響しないはずだ。
と、いうことだ。

これは、別の角度からの調査が必要だな。

７．追加調査

AVERAGE関数の範囲指定には、配列数式を使っているので、この部分を抜き出して、売上個数の隣のＣ列に表示させてみた。

Ｃ列の式でこっちが期待しているのは、
土曜日以外の売上を""に置き換えて、土曜日の売上はそのまま。
ということだが、調査結果は、その期待通りにはなっていないな。

土曜日と判断されているＡ１２，１３に対応するＢ１２，１３は未入力なので、Ｃ１２，１３も未入力状態を期待していたが、ここは０となっている。
まあ、考えてみればただの＝を使った参照でも未入力セルを参照すると０となるので、それと同じか。
どーも、こっちの方が主原因のような気がするな。

８．結論

色々と調査したが、今回の現象の原因は、
売上個数の未入力セルに対する対策が不足していた。
ということだろう。
”AVERAGE関数は未入力セルは無視する”
ということで、そこから先をあまり考えていなかったせいだろう。
実際には、未入力セルを参照したセルがAVERAGE関数に渡っていたのにだ。
一番の問題と思っていたWEEKDAY関数の動作の方はオマケみたいなもんだ。

しかし、結局対策としては、

{=AVERAGE(IF(ISBLANK(分析期間),"",IF(D10<>TEXT(WEEKDAY(分析期間),"aaa"),"",分析範囲)))}

でも問題はない。
この対策は、偶然にも、売上個数未入力の対策にもなっている。
日付未入力セルに対応する売上個数セルも未入力となっているからだ。

というわけで、何とか解決できたな。