数式設計支援改良版

前回の数式設計支援ツールに以前作成した数式分解ツールの改良版を一緒にして、１つのエクセルファイルにまとめてみた。

サンプルとダウンロードリンク

数式設計支援ツール

複雑な数式を組み立てていくのは一苦労。
なので、少しでも楽になるように数式の全体構成を確認しながら作成していけるように、ツールを作成した。
まあまあ使えるレベルになったので、ここで公開することにする。
番号の振り方が最初は複雑に感じると思うが、サンプル見れば分かると思う。

数式設計支援

番号、名称、説明、タイプ、値に情報を入力。
結果の式は式構成および式総合に表示される。

使い方

１．まずトップレベルから式構成を見ながら式（番号、名称、説明、タイプ、値）を記述する
２．名称どうしを四則演算や簡単な関数で繋げた式として記述する
３．複雑になる場合は参照として別式にする
４．その後、徐々に下のレベルの式を記述していく
５．値の欄に実際に式で使用する値を記述するのは、ある程度完成してからでよい
６．完成したら、トップレベルの式総合セルをコピーする（Ctrl+C）
７．実際に式を入れたいセルにカーソルを移動して、値の貼り付け（貼り付けオプションにあります）
８．先頭に"="を追加して完成

＊）行の高さ、列の幅は適当に調整してください。
＊）行のコピー追加や切り取り移動できるようにシート保護していません。
＊）行追加は式を記述していない行をコピーして、追加したい位置で挿入
＊）セルをクリアする場合は数式を削除してしまわないように注意。番号～値までをクリアする。
＊）式番号の検索範囲を番号の最大値で判断しているので、最後の行に10000とかの大きな番号をあらかじめ設定しておく

列の説明

番号：
整数ならば式
数値が同じならば後の要素を普通に繋げる（同じ式の構成要素）
小数の桁は関数のパラメータ、および式、関数の入れ子関係。
小数の数値が変わったら、カンマで繋げる
小数で桁が減った場合は変わった桁数に応じて閉じカッコを追加
空欄行で区切ると別の式扱い

タイプ：
rならば式の参照（値は式番号）
fならば関数。値の後ろに開きカッコを追加

値： 全て文字列扱い

式構成：
名称を連結
空欄の場合は値
参照の場合は[式番号]を追加

式総合：
参照元も含めた総合の式
値や参照元の式を連結（タイプが参照で値が空欄時は名称で代用）

スクリーンショット：

サンプルとダウンロードリンク

Excel Onlineでシートコピーはどうやるの？

最近Excel Onlineも時々使うのだが、やはり色々と違いがある。
データ入力規則やユーザ定義表示形式については、すでに解っていたが、もっとよく使う機能がないことに気が付いた。
シートコピー、シート名のタブを右クリックして出てくるメニューにある、あれのことだが、Excel Onlineでは、ここにコピーの選択肢はない。

出来ないはずはないだろうと調べてはみたが、無理みたいだ。
オフィスサポートページによると、シートコピーするのには、

1.CTRL+SPACE,SHIFT+SPACEで全範囲選択
2.CTRL+Cでコピー
3.新規シート追加
4.CTRL+Vで貼り付け

または、一旦、通常のエクセルで開いてシートコピー

と、なんとも心苦しい説明がされていた。

なぜこれが実装されていないのかは全く持って不明。
通常のエクセルとの差別化？
違うな。
技術的な問題？
たぶんそんなことはないだろう。
謎だ。

ちなみにGoogle Spreadsheetは普通にできる。
（もっとも、こちらはOnlineしかないが）

それと、ちょっとしたTipsを
全行選択　：　CTRL+SPACE
全列選択　：　SHIFT+SPACE
なのだが、SHIFT+SPACEが機能しない現象に出くわして、
イラついたことはないだろうか？
自分はある。

そういう人は「半角/全角」キーを押すと幸せになれるかも。

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

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

以上。

OneDrive公開リンクテスト

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

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

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

２．OneDriveの埋め込み機能




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