数式設計支援改良版

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

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

数式設計支援ツール

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

数式設計支援

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

使い方

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

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

列の説明

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

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

値： 全て文字列扱い

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

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

スクリーンショット：

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

集計対象セルの切り取り移動で集計計算がずれる

問題：

表にデータを入力する途中、気がつくと入力行がずれていた。とか、いくつかデータを飛ばして入力していた。
なんてことは、よくある事。

例えば、下の赤の網掛け部分は本当は１行下に入力しなければならなかったような場合。

そんなとき、また入力し直すか？
面倒だな。
やはり、すでに入力されているセルを正しい位置に切り取り移動(Ctrl+X,Ctrl+V)したいところだろう。

ところが、これをやると

上のように集計計算がずれてしまう。

これは、計算対象セルの移動に対応して、エクセルが数式に使われているセル範囲を追従して自動的に変更するために起こることだ。

表の作成中は、非常に便利な機能なのだが、表の完成後には、こんな風に、やっかいなことも。

コピー貼り付けではこんなことは起こらないので、そっちで対応してもいいんだが、元の空欄にしたいセルの内容を削除するという、１ステップ余計に必要だし、間違いの元になることも。

できれば切り取り移動で済ませたい。

という訳で考えてみた。

対策：

問題は計算対象範囲のセルが移動した時に数式に使われているセル範囲が自動的に追従して変わるということ。

ということで、数式に計算対象のセルを直接使わないようにすることを考える。

上の例でいうと、SUM関数の中にC8:E8というような移動する可能性のあるセルを使わないようにする。

では、どうするのかというと、移動する可能性のない、基準セルからの位置で計算範囲をしてやれば良い。

実際の数式：

具体的にはOFFSET関数を使って、次のように範囲指定する。

F7の数式：

=SUM(OFFSET($B$2,ROW($B7)-ROW($B$2),1,1,COLUMN(F$2)-COLUMN($B$2)-1))

OFFSET関数のパラメータ

　基準セル：$B$2

　行オフセット：ROW($B7)-ROW($B$2)・・・基準行との差

　列オフセット：1・・・基準セルの隣

　範囲の高さ：1

　範囲の幅：COLUMN(F$2)-COLUMN($B$2)-1・・・基準列と”計”列との間に含まれる列数

＊）基準との差で行オフセットや幅を求めているのは、行、列の挿入・削除を考慮しての処理。

この状態で切り取り移動すると、

という具合に計算結果はずれなくなった。

これはこれでいいんだが、行の色はずれたままだな。

このように書式のずれは、これだけでは対応できない。

やはり書式には条件付書式。

　条件式　=ISEVEN(ROW(B3))・・・行番号が偶数ならば

　書式：　薄い青で塗りつぶし

　適用範囲　=$B$3:$F$12

こうすれば、

と、行の色も問題ない。

上の方法だと切り取られた７行目の条件付書式もなくなるのでダメでした。
たまたま７行目が塗りつぶしなしだったので見逃してしまっていた。
書式の問題は数式だけでは無理っぽいな。

ちなみに、左右の罫線はそれぞれ計算範囲外の隣の列のセルで書式指定してあるので、条件付書式で指定しなくても問題ない。

これで、入力ミス時の修正作業が、少し楽になるかも。

以上、今回はこれで解決。

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

問題？

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

みたいな表があって、

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

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

文字列の結合には、ご存知のように、&演算子、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や空白にしておけばある程度は回避できると思うが・・・）

結論：

結局、結論は、

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

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

ということだ。

結論を覆して解決：

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

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

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

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

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

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

ヒントは、

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

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

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

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

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

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

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

これで本当におしまい。

分かりにくい互換性メッセージ

エクセルの便利な機能の１つに互換性チェックがある。
大抵は、どのセルに問題があるかというところまで教えてくれる。
ところが、そこまで特定してくれない場合もある。
こんな感じ。

メッセージを読む限り、あまり大したことはないようだが、気になったので調べてみた。

その結果、色の再現性だとわかった。（あくまでも今回の場合。）
「Excel97-2003」の色はRGBがそれぞれ1bitづつの計８色カラーだった（かな？）。

メッセージを読む限り、一番近い色に変換してくれるようなので、「Excel97-2003」でも問題なく開けると思われるが、ユーザに色で判断させているような場合には問題になるだろうな。