土曜日がいっぱい

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

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

１．起こった現象

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

土曜日の平均(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"),"",分析範囲)))}

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

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

名前の定義の注意

シートに問題が生じたので、シートを丸ごとコピーして、そこでテスト・確認する。

なんて言うシチュエーションはよくあると思う。

しかし、そのシートに名前の定義されているエリアがある場合、ちょっと注意が必要だ。

１．シートをコピーしたとき名前の定義はどうコピーされるのか？

テストのため、Sheet1に範囲１という名前を定義して、範囲１の最大値を表示するような場合を考えて、これで説明することにする。

まずSheet1の名前の定義は下のようになっている。

次にSheet1をコピーしてSheet1(2)を作成。

この時の名前の定義は下のようになる。

これを見て明らかなように、”範囲１”という名前の定義が２つになっている。違いは、参照範囲のシート名。それと、最初に定義したものは範囲が”ブック”になっているが、シートコピー時にエクセルが自動的に作成した定義の方は範囲が”Sheet1(2)”となっているところだ。

そして、このSheet1(2)のMAX関数が使っている”範囲１”は新しいSheet1(2)の”範囲１”だ。

２．どんな問題が起きるのか？

実は上の例では問題は起こらない。

Sheet1を削除した場合に上のようにゴミが残るのと、”範囲１”が他のシートから参照できない程度だ。しかし、この他のシートから参照できないというのがまずい。

というのも、問題が起こるのは、”範囲１”を別シートから参照していた場合だ。
例えば、範囲１がSheet1に、MAX関数がSheet2にあって、その後Sheet1をコピーしてSheet1(2)を作成した場合。

はたして、このMAX関数が参照している”範囲１”はどっちの”範囲１”だろうか？
当然、元からあったSheet1の”範囲１”の方なのだが、見た目ではわからないし、以外と盲点だ。

そして、コピーしたSheet1(2)は念のためのバックアップで、変更修正自体は元のSheet1の方に対して行う場合、例えば範囲１の5を10に変更するような場合は、

とMAX関数は元のSheet1の範囲１を参照しているので問題は起こらない（この時は・・・というのも、何か問題が発生して、バックアップのSheet1(2)を使う必要になったときには、後述する場合と同じことだから。）

しかし、元のSheet1をバックアップとして残し、Sheet1(2)の方に変更修正を加える場合には問題が起こる。

と、意図した結果にならない（まあ、間違って意図しているわけだが）。

しかし、この場合は、すぐに何かおかしいと気が付くので、まだ被害は少ない。

問題なのは、一目ではわからないような変更を行った場合だ。例えばMAXではなくてMIN関数だった場合、結果は変わらないので、問題に気づきにくい。

そして、Sheet1(2)の変更を確定して、オリジナルのSheet1を破棄して、Sheet1(2)をＳｈｅｅｔ１と変更してしまった場合。これは最悪だ。

当たり前と言えばそうだが、MAX関数で使用していた”範囲１”は元のSheet1と共に消えてなくなっているので、下のようにREFエラーとなってしまう。
しかも、新Sheet1で定義されている（コピー時にエクセルが自動的に定義した）”範囲１”の範囲はSheet1となっていて、MAX関数が記述してあるSheet2では使えない。

ならば、新Sheet1の”範囲１”の範囲をブックにしてやれば・・・・

しかし、これが変更不可ときている。

こうなると打つ手なし。もはや、”範囲１”を一旦削除して、新たに定義し直すしかないだろう。
トホホ・・・

３．対策はあるのか？

というわけで、名前の定義を使用しているシートをコピーする場合、というかシートのコピーはするな。
コピーするならファイルごとコピーする。
これが正解のような気がする。

そう言ってしまうと身もふたもないので、ちょっと考えてみる。
・・・・こういうのはどうだろう？

とういう具合に名前の定義の参照範囲をINDIRECT関数で指定するというのはどうだろうか。

この状態でSheet1をコピーして、Sheet2のB3の内容を”Sheet1 (2)!$A$1:$A$5”としてみたところ、

あれ？
どうやらINDIRECT関数がエラーを吐いているようだ。
どうも、スペースとカッコがまずいみたいなので、シート名を変えることにした。

よし。動作しているようだ。
（Sheet1とSheet12を区別するため、Sheet12では最大値を10と変えてある）

名前の定義を直接修正しなくても良い分、うん。こっちの方がいいな。
どっちにしろ、こういうことが起こることを考慮しておく必要はあるな。

優先順位に従って、一定量になるまで累計をとる。

事例：

ある物が格納されている機器が数台あるのだが、そこから毎朝一定数の物を抜き出して別の箇所に移動させないといけない。
一定数というのは、１台につき何個じゃなくて全体で何個ということだ。
例えば、

上のような機器と格納数で、全体で３００個抜かないとならない。
というようなシチュエーションだ。


”物”についての具体的な話は問題があるので、今回は”物”ということで。

単純に必要な数量に達するまで順番に抜いていけば良いのだが、どうも制限があるらしい。それは、

　　　　　”ある機器からは、なるべくなら抜きたくない。とっておきたい”

なら、機器に優先順位を付けておけばだいじょうぶ。

だが、

　　　　　”物”を抜きたくない機器は、店舗ごとに違う

らしい。

つまり、優先順位を変えられるようにしておく必要があるということだ。

ということで、今回のネタは、

「一定の数値になるまで、いくつかのセルの値を順番に足していく。ただし、この順番は設定で変えられるようにする。」

ということだな。

やり方としては、
①まず優先順位の順番に並べ替える
②次に行ごと累計を必要数になるまで計算する。
という２段階にわけて考えよう。

ステップ１:行ごと累計を必要数になるまで計算する。

まずは簡単そうな②から。
とは言っても、１つのセル内に数式を詰め込むと複雑になりそうなので、
　１．単純な累計を求める列を追加
　２．それに対して一定数に達しているか判断して、抜き取り数を決定。
と２段構えでいくことにしよう。

結果はこうだ。

１．単純な累計を求める列を追加

Ｄ列の単純な累計については説明する必要はないだろう。

とは言ったものの、上のように単に足していく方法とSUM関数の範囲を変えていく方法がある。
SUM関数のメリットは最初の行に
　=SUM($C$3:C3)
と入力して、後の行はコピーで行ける点。まあ、足し算でいく場合も２行目からはコピーで行けるのでメリットと言えるかどうか・・・

それに対してデメリットは、
行数が増えていくと、SUM関数の足し算の計算量がNx(N-1)/2となる。オーダー表記で書くと

となる。
一方、ただの足し算はN-1回なので、

どっちにしろ５行程度なら影響ないな。お好みで。

２．必要数に達しているか判断して、抜き取り数を決定。

抜き取り数を決めているＥ列について説明すると、
まずは最初の行、E3の式について。
　
　”格納数が必要数より多かったら、必要数分抜き取る。必要数以下だったら、全部抜き取る”

ということ。

それ以降の行について。

”累計数が必要数より多かったら、必要数に足りない分を抜き取る。ただし、すでに前の行までに必要数に達していたら、何も抜き取らない。
累計数が必要数以下だったら、全部抜き取る”

言葉で説明するとこうなるが、分かりにくいな。

というわけで図にしてみた。

これで多少すっきりしたかな。

参考までに累計を別列で計算するのではなく、式の中で計算する場合の例も挙げておく。

ここでは累計の計算はSUM関数を使っている。なぜならセルの足し算だと、２行目まで違う式を入れないとダメなため。全体を把握しづらい。
どっちにしろ、ちょっと面倒そうな式だな。
やっぱり累計は分けた方がよさそうだ。

問題があるとすれば、実際に機器を操作して”物”を抜き取る作業者に余計な情報を渡すことのになるくらいか。

ステップ１の結果の表

これでＯＫ。

さて、やっとステップ２だ。

ステップ２：優先順位の順番に並べ替える

単純に機器名と格納数が入っているセルを並べる順番を変えれば、対応はできる。
そして、店舗ごとにエクセルのファイルの種類を変えれば。

しかしそれだと管理が面倒だし、第一面白くない。

１．機器名を直接指定する場合

さて、どうするか。
機器の優先順位を設定する別表が必要だろうな。
こんな感じの。

機器名に直接入力する

ちなみに、ダブったところの色を変えるのは条件付き書式で対応。
この表の優先順位に対応する箇所に機器名を直接入力することにしよう。


更に、それぞれの機器の格納数も隣の機器名に従って可変にする必要があるので、これも別表にしておく。

格納数は直接入力

ここの格納数には直接、数値を入力する。
なんか、こう、表を分けていく過程はデータベースの正規化にちょっと似てるな。
そしてこれらの表から、おきまりのMATCHとINDEXの組み合わせで問題なく目的は達成できる。

あと、優先順位に何も設定されていないときなど、MATCH関数がN/Aを吐いたときのために、IF文とISNAを追加すると下のような式になる。

＊上の式には配列数式を使っているが、普通の式を下の行にコピーしていっても同じ。
ただ、配列数式の方が、処理内容が解りやすい。と思う。

２．機器名をリストから選択する場合

だが、ちょっと待て。
機器名を直接入力というのは、イマイチかな。
AとかBなんて簡単な名前は稀だろう。番号を決めてそれで入力という手もあるが、もうちょっとスマートに行きたいな。

やはり入力規則を使ってリストから選択できるようにした方がいいかも。
問題になりそうなのは、すでに優先順位を設定してある機器名をリストから排除していくやり方だろう。

また、機器リストを自動的に順番に並べるためには機器の大小関係が必要なので、機器番号を導入する。

この上の表のセルには数式ではなく値を直接入力する。
これをベースにして、すでに優先順位に設定してある機器名を排除していく。

１．Q列の式では、機器番号のリストから、すでに設定されている機器の番号を""で置き換える。





２．R列の式では、SMALL関数を使ってQ列を小さい順に並べ替える。



３．S列の式では、並べ替え後の番号に対応する機器名を表示する。

ちなみに、上のQ～Sの式は１列にまとめることもできる。

しかし、複雑になりすぎるので、分けておいた方が解りやすいだろう。

さて、もう少しだ。
入力規則の範囲指定に使うために、S3～S7の範囲で、エラーが発生していない範囲を、”選択可能機器リスト”という名前として定義する。
（可変範囲はOFFSET関数を、行数のカウントにはCOUNT関数を使用している。）

そしてセルK3～K7に次のように入力規則を設定する。

これで完了だ。

最後に動作テストをしてみよう。
まず、優先順位に何も設定されていない状態。

A～Eすべての機器が選択リストに表示されている。

次に、優先順位１位にAを設定したあと、最も優先順位が低い機器を設定するというシチュエーションを考える。

ちゃんと選択リストにはAを除いた機器が表示されてる。

そして、優先順位最下位をDとしたあとに、優先順位２位の機器を設定する場合。

A,Dを除いたリストが表示される。

よさげな感じだ。


ついでに入力時とエラー時にメッセージを表示するようにしてみた。

テストのために、直に機器名を入力してみた。

正しい機器名だが、エラーではじかれるな。
しかし、これは致し方ないか。
ちょっと気に入らないが。

というわけで、今回はこれで終了。
あまり纏まっていないが・・・

OneDrive公開リンクテスト

１．OneDriveの共有機能でリンクを作成

血糖値管理表
http://1drv.ms/1s7dUO5

上のリンクをクリックするとExcel Onlineが起動する。
しかし、残念なことに表、図形は表示されないようだ。
しかし、Online版起動後にインストールされているエクセルで起動することはできるようだ。
こうすると、ちゃんと図形も表示されていた。

２．OneDriveの埋め込み機能




こうすると、HTMLに直接埋め込むこともできるようだ。
なお、下のバーの右端をクリックするとExcel Onlineが起動する。

図形をセルに入力されている値によって自動変更

事例：
ワードアートや図形のテキストをセルの内容によって自動変更する。
これは、数式バーに参照セルを指定してやればOK。
（ただし、ここで直接、数式を使っての文字列合成はできないようだ。あくまでも参照するセルを指定するということ。）

これは単純で解りやすいのだが、図形そのもの、例えば、○を△にするとか、というような変更するとなると・・・あれ？

解決策：
というわけで、今回は、リンクされた図形について。
これは、貼り付けオプションの１つで、コピー元のセルを”図形”として、リンクするという機能だ。

左の例では、D6、D7、D8にAAA・・・、BBB・・・、CCC・・・とそれぞれ入力されている状態で、D6：E8の範囲をコピー（Ctrl+C）、そして、D1：E3の範囲を選択して、貼り付け→その他の貼り付けオプション→リンクされた図形としている。

ちょっと解りずらいが、コピー元が図形としてコピー先にリンクされる。
ここで注意が必要なのは、あくまでも”範囲”が図形としてリンクされるというところ。左の例ではAAA・・・が選択範囲を越えているので、その部分がカットされた図形になっている。

そして、図形なので、こんな感じに色々とデザインできる。

更に、リンクなので、このように、元のセルが変更されると、それに連動して、図形の見た目も変わる。

左の例では、D8の内容をDDD・・・に変更して、さらにD6：E8の範囲にワードアートを新たに貼り付けた。すると、D1:E3の図形にもそれが反映されている。







と、いうような、非常に面白い機能だ。



具体例：
この機能と、名前の定義を使えば、今回の目的を達成できるだろう。
例として、
「セルの値を”右、上、左、下”のどれかを選択すると、それに応じた矢印を表示する」
というような機能を実装してみよう。

１．まず元になる矢印画像を用意して、どこか適当な場所に貼り付ける。
　　このとき、実際に画像を表示したいセルと範囲の広さを合わせておく必要がある。
　　その理由は前にも言ったように、あくまでも範囲が図形としてリンクされるため。
　　今回はA1：A3に矢印画像を表示させる予定なので元の画像を３x1に収まるように調整しておく。

２．元画像を含むような範囲を選択して、この範囲に名前を付ける。
　　A6：A8の範囲を選択して”右”という名前を定義する
　　この名前は、後の手順７で矢印選択用リストの値として使う。




３．以下同様に、全部の元画像に名前を定義する。

４．元画像の範囲をコピー
　A6：A8の範囲をコピー（Ctrl+C）
















５．実際に画像を表示したい範囲を選択
　　A1：A3を選択




６．リンクされた図形を貼り付け
　貼り付け→その他の貼り付けオプション→リンクされた図形

そうすると、左のように、コピー元の範囲が図形としてリンクされて表示される。












７．選択リストの用意
　セルに直接でも良いが、今回はリストから選択できるようにする。
　下のように、B1に入力規則を適用
　（ここで、リストの値は先に元画像の範囲に付けた名前と同じにしておくこと。こうすることで、手順８で設定する参照範囲の式が簡単になる。）



































なお、入力規則を適用したあと、そのセルは、空欄ではなく、どれかを表示した状態にしておく。（空欄だと、このあとの手順９でエラーが起こるため）








８．表示する画像に名前を付ける。
　矢印という名前を新しく定義して、参照範囲を以下のような式にする。

　　　　　INDIRECT(矢印!$B$1)

この式の意味は、セルB1の値が示す参照を示す・・・
分かりにくいな。
要は、B1で選択されている文字列が示す範囲を返す。ということ。
例えば、B1が”右”となっていれば、”右”という文字列が示す、すなわち、”右”という名前の範囲（右という名前が定義されているので）を返すということ。
なお、この新しく定義した名前はまだ、どこにも適用されていない。手順９で使う。



















これを追加したあとの名前の管理ウィンドウはこうなる。

９．リンクされた図形に名前を適用する。
　さて、いよいよ大詰め。
　リンクされた図形を選択して、数式バーに手順８で定義した名前を入力する。
　こうすると、この”矢印”という名前を遡っていくことにより、元画像のどれかに行き着き、その画像が表示されることになる。





















１０．完成

















たまに使おうとするとやり方を忘れていたりするので、ちょっと長くなったが、詳細な手順を書いてみた。これでもう安心だ。