Excel Onlineとデータ入力規則と日付表示形式

エクセルで作ったファイルをexcelOnlineで開いたら・・・うまく動かない。

どうも日付を比較する数式のところの動作がおかしい。
表示形式が違うので見た目には確かに違うが、内部的には同じなので、エクセルではちゃんと意図した動作をしている。しかし、Onlineでは意図したようには動いていない。

起こった現象：

青枠のセルに赤枠のリストから選択できるように、入力規則を設定する。そして、これらのセルには表示形式として上にあるようなユーザ定義の表示形式を設定する。
そして、青枠と緑枠のセルを比較して結果を表示する。
というような処理だ。
いくつかのパターンで実験してみたが、エクセルでは、すべて、上のように見た目は違うが青と緑は同じと判定された。

このファイルをOneDriveに保存してExcelOnlineで開くと、

上の図は開いた直後だ。一見、問題なさそうに見える。
数式バーにも表示されている形式ではなく、"2015/6/1"と表示されている。
ちなみに他のパターンの青枠もすべて"2015/6/1"だ。

ところが、ドロップダウンリストから改めて、今と同じ６月１日を選択し直すと、

判定はNGとなり、数式バーの表示も"平成27年06月01日(月)"と見た目と同じになってしまった。

面白いのは、さらに３番のパターン以外も試してみたところ、

と、問題ない場合もあることが分かった。
数式バーも"2015/6/1"と変わっていない。
なお、４番の青枠には"2015/6/1"と直接入力した。

以上の現象から問題点を確認すると、
ドロップダウンリストから選択したときに、エクセルでは日付シリアル値としてセルに入力されるがOnlineでは文字列となる場合がある。
というところだ。

原因推測：

ExcelOnlineでは入力規則で範囲をリストにしたときに、表示形式を適用した文字列データになって、元のデータが失われているのではないか？

確認：

表示形式を数値にして確認。まずはエクセルから。

ドロップダウンリストの表示は赤枠内の表示と同様に表示形式適用後のデータが表示されるが、実際に選択してセルに入力されると、青枠内の表示形式に従って、日付シリアル値がそのまま表示された。
１～３すべて同じだ。
確かにエクセルでは、元データはちゃんと保持されている。

一方Onlineでは、

ドロップダウンリストの表示はエクセルと同じだが、選択後のセルには日付シリアル値ではなく、リスト表示と同じ文字が入力されてしまった。
確かに、元データは失われているように見える。
しかし、３番のパターンではこのようになったが、１，２はまたちょっと違った。

こんな風に、ドロップダウンリストの表示とは関係なく、ちゃんと日付シリアル値が入力される。
これは、どう考えればいいのか？元データが残る場合もあるのか？
最初はまったく訳が分からなかったが、どうもエクセル（Onlineでも）の自動解釈で文字列が日付と判断されているようだ。
しかし、１番の”平成27年06月01日”なら解るが、２番の”６月１日”とか日付シリアル値に変換できないだろ。年が解らない。
と思ったが、一応、実際に入力して確認してみたところ・・・

これはエンターキーを押す前。





エンターキーを押すと、こうなる。

という具合に、（おそらく今年が）自動的に補完されるとは思わなかった。
次に一応、曜日が入るとやっぱりだめなのか、を確認。

エンターキーを押す前。

エンターキーを押すと・・・
やっぱりダメか。





結論：
１．入力規則のドロップダウンリストにリスト化されるのは、

　　　　エクセル→元データと表示形式適用後のデータ両方

　　　　Online  →表示形式を適用後のテキストデータのみ

２．年の情報はなくてもエクセルは補完してくれる。

今回の調査の発端になった現象を回避するには、
ドロップダウンリストには、エクセルが日付と解釈可能な表示形式を使う。
ということだな。

最後にいうのもなんだが、
実はExcelOnlineにはドロップダウンリスト（データの入力規則）もユーザ定義表示形式もない。
ただし、エクセルで作成したファイルを開いた場合には、ちゃんと機能した状態が再現されている。

というわけで、まあこんな現象もしょうがないのか。

以上。

ドロップダウンリストの矢印が消えた！

ある日突然、矢印が消えた。

入力規則でドロップダウンリストを設定したセルをセレクトすると横に表示されるやつだ。
ただし、いつも消えているわけじゃなくて、カーソルを合わせてマウスボタンを押している間は表示されているが、離すと消えるという、謎な現象だ。

調査：

ネットで調べたが、あまり有用な情報（ドロップダウンリストから選択するにチェックを入れましょうとか、オプション設定のすべてのオブジェクトを表示するにチェックを入れましょう。というのはもちろんあったが・・・）がなかったので、調査してみた。

まず、単純にブックをコピー(別名保存）してみたが変わらず。
次にすべてのシートを個別に新規ブックにコピーしてみたが、これも変わらず。
しかし、ここで、気になる点が。
シートを１つづつコピーしていったのだが、ある特定シートをコピーするまでは、正常動作だったのだ。

これで問題のあるシートが特定できたわけだ。
あとは、このシートのどこに問題があるのか？ということだ。

一番怪しいのは、このシートで最近変更した箇所だな。
図のリンクの貼り付けか・・・・

試しにこの図を削除してみたら・・・

当たり。

矢印が現れた。

念のため、新規ブックで同じ状況を作ったら再現したので、間違いないだろう。

原因：

なぜかは不明だが、リンクされた図の貼り付けがまずいらしい。
オプションのオブジェクト表示の切り替えで表示されたり消えたりすることからもわかるように、この矢印はオブジェクトの１つだろう。
その辺が関連しそうだが、これ以上はわからないな。

（ちなみに、ドロップダウンリストとリンクされた図が同じシートにある場合は、この現象は起こらない。また、リンクされた図の貼り付けをアンドゥ機能でなかったことにしても、矢印は消えたまま。
これはこれで謎だが。）

これで何が悪さをしているのかは判明したが、はたして対処方法は？

対策：

いまのところなし・・・

では困るので、色々と試行錯誤してみた結果、

ドロップダウンリストと同じシートでコピーすれば、どこにリンクされた図を貼り付けても問題ないことがわかった。
すなわち、

コピー元のセルを一旦ドロップダウンリストと同じシートで参照させて、そのセルをコピー元にして、必要なシートにリンクされた図を貼り付ける。

これで矢印が表示されるようになった。

図のリンク状態も問題ないようなので、正常に戻ったと見ていいだろう。

というような、原因も謎だが、対策も謎な結果になってしまった。

まあ、解決したからいいか。

追加

これで対策できたと思っていたら、また同じ現象が発生した・・・

というわけで、対策については、調査中。

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

事例：

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

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


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

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

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

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

だが、

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

らしい。

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

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

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

ということだな。

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

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

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

結果はこうだ。

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

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

とは言ったものの、上のように単に足していく方法と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を除いたリストが表示される。

よさげな感じだ。


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

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

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

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