シート上の数式・関数を解読するマクロ
関数を分析するマクロを作成した経緯
こちらのシートには数式や関数がふんだんに埋め込まれていて1つ1つを追っていく作業が必要でした。
元々はExcelの機能を使って解析していたのですが、どうにもこうにも仕組み自体がよく分からず、ツールを作って分析への近道を作りました。
数式や関数を分析するためのツールなどがExcel開発系会社からもリリースされていますが、今回の作業ではどうも痒いところに届きそうにないようなシート構成ということもあって参考にできず、自作に至った次第です。
なお、分析対象となったシートは「人事評価」関連のシートでした。
そもそも数式がどこで使われているのか分からない
・数式、関数が1シート、あるいは1ファイル(ブック)で完結しているか
・そのシートを使う業務内容が理解できるか
どちらかの難易度、前者であれば、数式の参照先がブック間になるとあちこちと追いかけまわす(トレースする)ことになるだけでなく、そもそもブック間でリンク(パス)が有効になるフォルダ構成が必要であったり、後者であれば、業務内容が全く分からないと機械的な理解となり、「なぜここで関数が使われているのか？そもそもこの関数で良いのか」の判断が困難になるからです。
分析ツール概要
(どんなシートにも対応し得る機能は事前に把握すべき点が多すぎて実装が困難そうです)
このツールは基本的に実体を持ちません。従い必要な時に応じて、ツール(の機能)を呼び出すようにしました。
この機能は大きく3点です。
・数式、関数のあるセルを洗い出す
・数式(関数)のあるセルが「どこのセルを見ているのか」、「どこのセルから見られているのか」を表示する
・テストデータを入れた場合にどこのセルがどのように変わるのかシミュレーションで見れる
実例で説明
今回の表は1シートで完結しており、しかも数式は掛け算、関数はSUMのみのシンプルな表です。
(実際の作業はIF、IFERROR、VLOOKUP等がふんだんに組み込まれていました)
この表では数式と関数が使われているセルはD列の4か所のみです。
数式、関数のあるセルを洗い出す
これは一般機能の「ジャンプ」にある機能をVBAから使っています。
どのセルに数式関数が使われているのかを知って作業が開始できます。
このツールではセルの色を緑色にして視覚的にわかりやすくしました。
セルの参照先、参照元を見る
次にセルの参照先、参照元を見ますが、これもExcelにあるトレース機能を使っています。
ただし、このままでは使いづらかったので、トレース線だけでなく、該当するセルの上にボックスを表示するようにしました。
画像では伝わりにくいですが、このキモはポップアップのようなイメージで自動的にボックスが表示されるようにしていることです。
セルD1(りんご：計)を対象に参照元、参照先を確認
セルD6(合計)を対象に参照元、参照先を確認
シミュレーションで関連するセルがどのような影響を受けるか確認
これは独自に作成しました。
「このセルの値を変えると、他のセルにどのような影響を与えるのか」を知る必要がある場合に使います。
上記のトレースで「値が使用されている(参照元)セル」を見つけた後、この機能を使います。
下記ではセルB2(リンゴ：単価)の値をテスト値に変更します。
「50」→「100」変更したときの状態です。
画面下にリストボックスがあり、そこに影響を受けた「シート名」、「セル番地」、「値」が表示されます。
※今回は不要ですが、影響を受けるセルがシートをまたぐ場合は、リストで該当の項目をクリックするとそこのセルにジャンプします。
そのため、画面下に「元の場所に戻る」ボタンがあります。
カテゴリ：ツール・システム紹介
◆お問い合わせ◆
原則3時間以内にご連絡
お問い合わせフォーム(SSL対応)
弊社ではお急ぎのお客差にもご対応できるよう、迅速な返信を行っております。
特にお問い合わせフォームの場合は業務時間外でもスタッフが確認できればご連絡するという体制をとっております。
◆VBAツール・システム◆
Excel VBAの基本・事例
VBAはExcelだけでなく、Word、AccessなどMicrosoft Officeで共通して使えるプログラム言語です。細かい部分ではExcelで使えてWordで使えないなどもありますが、文法的には同じです。
こちらのページではExcel VBAの基本やツール、システムの紹介しております。
◆Excelレッスン◆
Excelについて勉強をしたい方
Excelについて分からないところだけを優先にしりたい、教えてほしい等。
授業のテキストの通りではなく、必要なところだけを効率的に勉強されたい方などは下記のページからご覧ください。