数式設計支援改良版

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

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

数式設計支援ツール

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

数式設計支援

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

使い方

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

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

列の説明

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

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

値： 全て文字列扱い

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

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

スクリーンショット：

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

範囲内の文字列を結合するには？

問題？

範囲内の文字列を結合と言っているのは、

みたいな表があって、

　　地名リスト：福岡県&熊本県&熊本市&佐賀県&佐賀市&長崎県&大分市

とセル内の文字列を結合して表示するような場合のことだ。

文字列の結合には、ご存知のように、&演算子、CONCATENATE関数が使える。
しかし、上のようにセパレータで区切ったり、数が多いと結構大変だ。

数値の場合、SUM関数のように範囲指定して演算をするような関数は用意されているので、範囲指定して、SUM関数等を使えばいいだけ。
しかし、文字列の場合、こんな風に範囲指定できる関数は用意されていないようだ。

文字列の場合、１個づつ&演算子やCONCATENATE関数で結合するしかないのか？
まあこれでも目的を果たせるならいいが、結合する範囲が変わったり、途中に行や列が挿入されたら？
という訳で、今回は範囲内の文字列の結合。

考え方：

文字列の結合には結局のところ&かCONCATENATEを使う必要がある。
途中にセルが挿入されても、数式のコピペで対応できるようにする。

ということで、採用したのが単純な方法。
隣のセルと結合した結果をそのまた隣と結合していくチェーン方式。

実際の数式：

計算用セルを横に追加する。（通常は非表示）

D4が最終結果となる。

D4の式：

=IF(OR(B4="",OFFSET(C4,-1,0,1,1)=""),"",$E$3)  &IF(OFFSET(C4,1,0,1,1)="",T(B4),B4&OFFSET(D4,1,0,1,1))

C4の式（結合対象範囲を判断するために使用）：
=ROW(C4)

D4式の解説
　基本処理：
　　(セパレータ$E$3)と(現在行のB列)と(下の行のD列)を結合。
　　文字列結合には&演算子を使用（CONCATENATE関数でもＯＫ）。

　前方にセパレータを追加しない条件：
　　B列が空欄または、一番上の行。

　最下行の処理：
　　下の行と結合しない。

*)上下のセルをC3やC5と直接指定せずに、OFFSET関数を使っているのは、途中に行を追加した場合に正しく直下や直上のセルを指定できるようにするため。
*)T関数は最下行のB列が空欄だった場合に0ではなく空欄("")とするために追加。
*)C列の上下は空欄にする。

これで完成。途中に行を挿入しても行コピーでＯＫだ。

さらに応用として、IFを追加して、条件にあう場合だけ結合というのもできるだろう。

今回のねた、範囲内の文字列を結合するなんていう場合は稀だとは思うが、たまたま別件で必要になったので考えてみた。

以上、終了。

数式分解ツール

複雑に入れ子になった数式を分解表示するツールを数式で作成したので公開してみる。
オレンジの箇所に数式を入力すると下に分解表示されます。
作成途中から、コレを使いながらコレを作りました。
使えるかも？

循環参照（反復計算）で累計計算

事例：

以前こんなことを聞かれた事がある。

「このセルに毎日の実績を入力すれば累計値がどんどん増えていくようにできませんかね～？」

”このセル”というのは一箇所。すなわち同じセルに入力するたびに累計値に加算されていくようにしたいらしかった。イメージ的にはこんな感じか。

当時の私の答えは、

「無理です。」

上のような数式を入力すると、こんなメッセージが現れて警告される。

循環参照、すなわち再帰的な計算はエクセルではできないということだ。

しかし今更ながら、エクセルのオプションにこんな項目があったのを思い出した。

最初に見た時に、すぐに循環参照のことを言っているんだろうなと思いながらも、なぜか今まで試さず、このこと自体を思考の隅っこに放置していた。

というわけで、やっと重い腰を上げて実験してみた。

実験：

まずこのオプション設定をこんな感じに変更。

「反復計算を行う」にチェックを入れて、「最大反復回数」を”１”に設定してみた。この「最大反復回数」というのはいわゆる無限ループを回避するための設定だろう。

変化の最大値についても同様に無限ループ回避のための設定だろうが、動作が想像できないので今はそのまま。

そして再び、この数式を。

こんどは警告メッセージは現れない。

そして、これが初期状態。

本日実績としてB3に最初から"100"が入力されていたのでC3の累計は"100"となっている。

そして、この状態からB3に数値を入力してみる。

例えば、"50"。

と、ここまで予想通りの動きだ。

問題点：

これで、最初の事例の件は解決だな。とりあえず保存しておこう。とセーブボタンをポチっとしたとたん・・・

ん？今なにか変わったような？

じつは、セーブしただけで、こんな風に。

見てのとおり、もう"50"増えて累計値が"200"になってしまった。

最初は意味がわからなかったが、これはエクセルが保存時に”再計算”されるために起こる現象だ。

数式が実行されるタイミングは、多くの人が通常”自動”に設定していると思う。この”自動”の中に”保存時”というのが入っているのだ。

これは問題だ。

このままでは、使えない。

なにか、策を考えねば。

解決策：

というわけで、ロック機能とリセット機能を付けてみた。

C3の数式：

=IF($D$2="計算実行",C3+B3,IF($D$2="リセット",0,C3))

使い方は、

最初にD2を”リセット”にして、C3を0クリア。

次に、”計算実行”にして、B3に数値入力。

入力終了後にD2を”ロック”にする。

あとはお好きな操作を。

これで、今回は終了・・・

でもない。

再び問題発生：

実は再計算処理が悪さをするシチュエーションがまだあったのだ。

それは、本日実績の数値をそのままの状態で、”ロック”から”計算実行”や”リセット”から”計算実行”に切り替えた場合だ。

このとき発生するのは、「本日実績が累計値に２回足されてしまう」という現象。

これは調査の結果、理由は不明だが、

ドロップダウンリストから選択した場合、なぜか再計算が２回発生してしまう。

ということが原因になっていた。

これからも分かるように、（考えてみれば当たり前だが）要は何らかの原因で再計算が発生するたびに累計値も再計算されるということ。

例えば、他の無関係のセルへの入力とか。

つまりほとんど何もできないっていうこと。

（もちろん再計算のタイミングを熟知していて、そのときに累計値に足されるセルの内容を0や空白にしておけばある程度は回避できると思うが・・・）

結論：

結局、結論は、

循環参照（反復計算）を使った、累計値の計算は、やはり、

「無理です。」（現実的には）

ということだ。

結論を覆して解決：

と、一旦は結論を出したが、もうちょっと粘ってみた。

その結果、循環参照（反復計算）を使った、累計値の計算を実装できた。

ある程度シチュエーションは限定されるが、今回の「本日実績」のように、累積をとる回数が数えられる場合には適用可能なやり方だ。

なお、このやり方でも、今回実装したロック機能はやはり必要。

そして、本日実績の項目が複数あったりして途中に再計算が入っても累計値には影響しない。

そのやり方は、今はまだ非公開。興味がある人は考えてみよう。

ヒントは、

”反復計算はやはり必要”

”操作者には見せないが、こっそり毎日の数値を保持している”

”累計値はそっちの累計をとる”

”もちろん数式だけで実現”

サンプルは作ったのでこちらのサンプルページの

「日々の実績値入力＆累計計算」

というところにあります。

これで本当におしまい。

セルの内容をコピーしてるだけなのに、何故か再計算に入るエクセル

今回はVBAネタ。
シートに配置したフォームボタンを使って、ある範囲のセルの内容を別シートのセルの範囲にコピーする。
これだけなんだが、ボタンをクリックすると、カーソルが青いリングに変わって、何やら裏でやっているらしくて、なかなか戻ってこない。よく見ると下のステータスバーに「再計算・・・」というようなメッセージが、何度も現れては消えている。
これは？コピー先を参照しているセルはないはずだが？


原因調査

というわけで、どういう場合にcalculateイベントが発生しているか、ちょっと調査してみた。

１．まず、VBAのコード部分について、
　セルの内容をコピーするというコードだったので、セル操作に関するコードを色々とためしてみた結果、

　Ａ．セルの内容を変更するようなコードでイベント発生
　Range("A1").Value = 1
　といったような操作でイベント発生。一方、幅や色を変更といった操作では、イベントは発生しなかった。

　Ｂ．対象となるシートにはよらない
　ことが判明。例えば、シート１、２、３とあり、内容が変更になるシートが”シート３”だったとしても、”シート１”、”シート２”でも、calculateイベントが発生する。

　ただし、Ａ、Ｂとも、後述する特定の種類の数式がそのシートに記述されていることが条件。

２．数式部分について。
　再計算が発生しているので、どこかの数式が悪さをしているのだろう。ということで、数式を色々と試してみた。その結果、calclateイベントが発生したのは、
　
　日付/時刻関連　NOW、TODAY
　検索/行列関連　OFFSET、INDIRECT
　情報関連　CELL、INFO

上記の関数を使用した場合、イベントが発生した。よく使うIF、SUM、INDEX、MATCHといった関数は問題ない。

（ただし、全部の関数は試していないので、これ以外にもあるかも）

すなわち、上述したように、これらの数式が記述されているシート内のセルに対して、その内容を書き換えるような処理が行われた場合、calclateイベントが発生する。
（実際に問題になったエクセルではINDIRECT関数を使用していた。）

解決策

さて、いろいろと解ってきたが、なぜ、こうなるかは不明のまま。
ちょっと解りそうにない。
とは言っても、なんとかしなくては使い物にならない。
上記の関数を使用しないというのも、１つの手ではあるが、そうもいかない。
そこで思い出したのが、エクセル自体の設定で、再計算を自動かマニュアルに変更できることだ。
イベント発生をコントロールする”Application.EnableEvents”はよく使うが、再計算についても同様の処理ができるのでは？
ということで調べた結果、やはりあった。

　Application.Calculation = xlCalculationManual

このコードを、セルの内容を書き換える処理の前に入れてやれば良い。
そうすると、calclateイベントは発生しなくなる。
終わったら、

　Application.Calculation = xlCalculationAutomatic

として自動に戻しておくことも忘れずに。


根本原因は不明なままなので、ちょっと不完全燃焼だが、しょうがない。
今回はこのくらいにしておこう。