数式バーで見ていたのでは、どんな数式なのかがさっぱりわからなくなります。このような複雑な数式を分解し、分かり易く表示するVBAを作成しました。最初に言っておきますが、そもそも、そのような複雑な数式は作らないほうが良いというのが基本です。作業セル(計算セル)を使い、順序立ててわかるようにしておくべきものです。追加々で組み立てていくと割と簡単に複雑な数式が出来上がってしまいます。数式全体に無駄が多く条件が整理されていないなど最適化されていない数式である場合が多々あります。変更が必要になった時など、いずれその数式を解析しなければならない時がきます。今回のVBAは、そんな時の手助けツールとして作成したものです。今回のVBAを作ろうとしたとき、最初はどうしてよいか戸惑いました。複雑なことを考えては、VBAでは厳しいかなと諦めてしまったり。今回のVBA完成に至っています。解析の中核としては、一番外側の関数の引数を分解しているだけです。これは人が(私が)複雑な数式を読むときの基本手順です。これを繰り返して最小単位の関数にまで掘り下げています。今回のVBAは、これをそのままVBAにしたということです。=IF(COUNTIF(範囲,検索条件)=1,A1,VLOOKUP(検索値,範囲,列番号,検索方法))このような数式の場合、まず最初にIF関数の引数に分解します。論理式:COUNTIF(範囲,検索条件)=1真の場合:A1真の場合:VLOOKUP(検索値,範囲,列番号,検索方法そして次に、引数の数式を確認し、これが複雑なら同じことを繰り返します。私はこれを数式バーを見ながら、必要に応じて数式内で改行(Alt+Enter)したりしながら解読します。複雑で長い数式であれば、メモ帳に数式を貼り付けて、適宜改行したり、字下げしながら解読していきます。複雑な数式の解析としては、実務的にはこれで十分なはずです。自由にカスタマイズして使ってもらえれば良いと思います。複雑な計算式を解析するVBAの概要シート構成シートは以下の2つです。シート名はVBAでは使っていないので何でも構いません。VBA内のモジュールレベル定数で名称を指定しています。処理メインのシートになります。B1:K1はセル結合します。数式が折り返されて、数式全体がみえるようにする目的があります。1行目の行高さは、VBA内で自動調整しています。B1の番地は、VBA内のモジュールレベル定数で指定していますので変更可能です。このB1セルに数式を文字列として貼り付け解析します。(先頭の=はあってもなくても良い)「消去」:2行目以下の解析結果部分を消去します。「全解析」:数式を全て最小単位の関数になるまで解析します。B1セルをダブルクリック:一番外側の数式だけを分解します。2行目や4行目の数式(のある行)をダブルクリックすることで、その数式を分解し、当該行の下に行挿入して引数を出力します。これを繰り返すことで、数式内の必要な部分を解析していけます。A列はネストの深さレベルを表す数字になります。数式を分解したときに、関数の引数名の表示に使います。A列に関数名、B列以降に引数名を入れておきます。ここに設定した引数名が、解析結果の時に表示されます。未登録の関数は、1行目の名称(引数1,引数2,・・・)が表示されますので、1行目以外は必須ではありません。解析結果を見やすくするためだけが目的のシートになります。関数構文のサンプル(ほとんどの関数を入れています。SubBeforeDoubleClick数式をダブルクリックしたときに、AnalyzeFormula(Target)をCallます。数式解析のメイン処理です。SelectionChange解析結果の数式をセル選択したときに、AnalyzeHighlight(Target)を呼びます。元の数式(B1)内で、選択セルの文字列と同じ文字列部分をハイライト(赤)表示します。標準モジュール※以下のVBAコードは当初掲載以降に何度か修正しています。'処理文字位置'引数を入れる配列:(0)関数名、(1)~引数'ixが最後の引数のインデックスになります。'ix+1に関数以降(+○)の数式を入れます。Function外部から呼ばれる公開プロシージャーは4つです。AnalyzeHighlight(・・・)AnalyzeFormulaこれが数式解析の心臓部です。ここで、一番外側の関数を引数に分解しています。PutArrayでシートに出力しています。AnalyzeAllFormulaAnalyzeFormulaを繰り返し呼ぶ事で一気に全解析するようにしているだけです。VBAを読むのに必要最低限のコメントは入れましたので、VBA内のコメントを頼りにVBAコードを読み解いてください。分かりづらいとしたら配列の部分だと思います。String関数を分解して引数を入れる配列です。255というのは、Excel関数の引数の仕様上限数です。上限が決まっていて、かつ大きな数字ではないので静的配列にしました。いえ、そんなにたくさん引数を使っていたとしたら複雑とは別の問題がありますね。VBA内にも少し詳しくコメント入れましたが、'引数を入れる配列:(0)関数名、(1)~引数'ixが最後の引数のインデックスになります。'ix+1に関数以降(+○)の数式を入れます例えば、=IF(論理式,真の場合,偽の場合)+B1この場合は、ixが3になり(0):IF(1):論理式(2):真の場合(3):偽の場合(4):+B1このように入ります。解析結果としては、すぐ下の行に4行挿入して、これらを引数名とともに出力します。汎用的に使える記述もありますので参考にしてください。複雑な計算式を解析した結果の表示B1の数式:IF(A1<"A",VLOOKUP(A1,Sheet2!$A:$C,3,FALSE),OFFSET(Sheet3!$A$1,COUNTA(Sheet2!$A:$A),0,1,1))この数式は掲載用サンプルとして完全にデタラメな数式です。この程度の数式なら、ツールを使うまでもないのですが、雰囲気を伝えるためのものです。C3セルを選択しているので、B1セルの当該文字列が赤表示になっているのも見て取れるはずです。VBA内にコメントを入れましたが、同じ文字列があれば全てハイライトしています。これは同一数式が複数存在する場合は、それを作業セルにする時の参考になるという趣旨です。実際の関数ネストとしては3重ネストということになります。解析結果を見るときの参考になると思い入れたものです。作業セル(計算セル)を使い、順序立ててわかるようにしておき、そもそもこんなツールが必要ないようにExcelを使うべきというのが基本原則になります。今回一番感じたのは、一見複雑に見えるものも、その成り立ちと自身の思考を、整理・単純化することが大切だということです。これはエクセルだけの話ではなく、全てに通じるものだと思います。※検証テストが不足しているので、バグがあった時ご報告いただければ幸いです。(さすがに実務ではそうそう複雑な数式はないのでテスト材料が不足です)また、使ってみた感想などお送りいただければ励みになります。見るTRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)無効な前方参照か、コンパイルされていない種類への参照です。Case)|VBA入門9.メッセージボックス(MsgBox関数)|VBA入門10.マクロとは?VBAとは?VBAでできること|VBA入門ホームマクロVBA応用編マクロVBAサンプル集Excelシートの複雑な計算式を解析するVBAこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。アクセスランキングサイトマップ事業案内お問い合わせホームマクロVBA応用編マクロVBAサンプル集Excelシートの複雑な計算式を解析するVBAこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。