前任者の作ったエクセルシートの数式解析に困った時に行うべき４つの方法
うーん。
どうしたの？
この前退職した人が作ったエクセルシートを修正しなきゃいけなくなったんですけど、どうしてこう他人が作ったものってよく分からないんでしょうか。
まあね。ある程度は仕方ないよ。他人の作ったシートを解析するのもいい経験だから頑張って！
ええー、何かコツを教えてくれないんですか？
コツと言っても地道に解析していくしか無いんだけど、まあ僕がどうやっているかくらいなら。
ぜひぜひお願いします。
●このエントリの内容
F2で数式がどのセルを参照しているか調べる
エクセルシートを解析するっていうのは、すなわちどんな数式がどのセルを参照しているか解析するってことなんだけど、数式バーの数式をただ見ていてもわかりづらいよね。
その場合、F2を押してあげると、そのセルに入力されている数式が、どのセルを参照しているかが分かるんだ。
F2って編集モードにするんですよね。押してみますね。あ、なるほど。
数式で参照しているセルに色がつきますね。こうやって見るとわかりやすいですね。
列全体を参照している場合は、こんな風に表示されるんですねー。へー。
あ、でもこれだと、VLOOKUP関数とかで別のシートを参照している場合わかりませんね？そういう場合はどうしているんですか？
参照元のトレースを使ってみる
別のシートのセルを参照している場合は、どんな方法でもわかりづらいんだけど、参照元トレースを行うと多少はわかりやすくなるかな。
参照元トレースなんて初めて聞きました。どうやってやるんですか？
調べたいセルをアクティブセルにして、リボンの数式から参照元のトレースをクリックするだけ。
そうすると、そのセルに影響を与えるセル、引数として参照しているセルを矢印で確認する事ができるんだ。
へー。でも、範囲を参照している場合はF2で編集モードにした方がわかりやすいですね。
参照元トレースの場合、別のシートを参照しているとアイコンで表示される。黒点線をダブルクリックすると、ジャンプのダイアログが表示されて、どこのセルを参照しているかわかるようになる。
なるほどです。でも、これだけわかれば助かりますよー。
トレースの矢印消すには・・・あ、リボンのトレース矢印の削除をクリックすれば良いんですね。
ちなみに参照先のトレースは使わないんですか？
このセルの値は、どのセルから参照されているか？というのを調べるのに便利なんだけど、実はそれほど使った事はない。
でもなんか、参照元と参照先がごっちゃになってきちゃいましたよ。何が参照元で、何が参照先でしたっけ？D21が参照している先だから参照先のトレースじゃないんですか？
例えば、下図の例なら「D21を参照元としているセルをトレースする」って事じゃないかな。
うーーーーん。ややこしい。
数式をセルに表示させて、どこに数式が入っているかを確認する
あと、直接値を入力したセルなのか、数式が入力されているセルなのかを一目瞭然にする方法を割りと使うね。明らかに変な数式が入っている場合のチェックにもなるしね。
それは助かります。いちいち数式が入力されているところなのか、直接値が入力されているところなのか探すの大変ですもん。どうやってやるんですか？
Excelのオプションから詳細設定を開き、「計算結果の代わりに数式をセルに表示する」のチェックをONにしてみて。
そうすると、数式の結果ではなく数式自体がセルに表示される。更に数式が入力されているセルを選択すると、F2で編集モードにしたように参照しているセルがカラー表示されるから集中して解析する時に便利なんだ。
きゃーーーー。でもセルの幅が変わっちゃいましたよ。それに書式も変になっちゃいました。桁区切りも飛んじゃっているし、数値はセルの右側に表示されるのに、左側に表示されちゃってます。
あー、それは大丈夫、Excelのオプションから詳細設定を開き、「計算結果の代わりに数式をセルに表示する」のチェックをOFFにすれば、ちゃんと表示が戻るから。
あー、戻りました。良かったです！一瞬壊しちゃったかと焦りましたよ。
そんな簡単には壊れないから・・・・。
ネストされた関数は一番内側から見る
余計なお節介を焼くけど、関数の引数に別の関数が使われているような場合は、内側の関数から見ていくとわかりやすいと思うよ。
内側からですか？
簡単な例で言うと、=ROUND(SUM(D64:D69),0)という数式の場合、ROUND関数の引数として使われている、SUM(D64:D69)から見るってこと。
数式を最初から見ていくと、四捨五入する、D64:D69を合計したものを。という感じになる。ちょっと分かりづらいよね。慣れちゃえば平気なんだけど。
内側からみて、D64:D69を合計したものを四捨五入するって考えた方が、日本語の語順的にもわかりやすいよね。
内側からって、そういう意味なんですね。
確かに数式を最初からみていくと少しわかりづらいですね。四捨五入する、合計をって、なんか英語直訳みたいですよね。私、やる、それ！みたいな感じで。
これくらいなら、数式の最初から見ても内側から見てもすぐにわかるから、もう少し複雑な例でみてみようか。
=SUBSTITUTE(ASC(MID(D2,2,8)),"ｰ","-")
という数式を解析してみよう。
これは、〒６８９ー４５２５と入力されている郵便番号を、689-4525と変換する数式になるんだけど、これも内側からみていこう。
まず、MID(D2,2,8)で、〒６８９ー４５２５の２文字目から８文字取り出す。これはいいね？
はい。大丈夫です。
続いてASC関数で、さっき取り出した６８９ー４５２５を半角に変換する。
最後にSUBSTITUTE関数で半角の長音があったら、半角のハイフンに置換する。
こんな感じで、処理の順番的に、日本語的に、数式の一番内側から見ていくと処理が解析しやすくなるよ。
なるほどです。シラトリさんはこのくらいの数式はパッと一瞬で書けちゃうんですか？
まあ、調子が良ければ・・・。
でも一発では書かないかな。=ASC(MID(D2,2,8))までは一気に書いてみて、返り値が想定通りか確認する。
正しい返り値だったら、F2キーを押して、SUBSTITUTE関数を付け加えるという感じで書くかな。
最初のうちは、まずMID関数から書いてみて、良ければF2を押してASC関数を付け加え、よければF2を押して・・・みたいな手順で継ぎ足して書いて行くと良いんじゃないかな？
継ぎ足して書いていくですか・・・やることが頭の中で整理できていないと、どんな手順でも書けないですよね。・・・頑張ります！
何にせよF2は超重要キーなんですね。
これで前任者のエクセルシート解析も捗りそうです。ありがとうございました。
最後に
最後までお読みいただきありがとうございました。
人が作ったエクセルシートの解析って手間取ります。実際、前任者の作ったシートが解析できずに、私の方で解析し対応する、作りなおすという例もとても多く、その経験から役立つ機能、考え方をピックアップしてみました。
シート解析には、F2（編集状態）による参照先の確認、参照元のトレースは必須ですが、数式を表示するようにすると、どこに数式が設定されているかを把握しやすいので便利です。また、明らかにおかしい数式の発見もしやすくなりますので、数式が多いシートや変更が多いシートなどでも一度確認してみると良いかも知れません。
IF関数がネストされていると使いづらいのですが、関数の組み合わせが複雑な場合は、数式の内側に書かれているものから処理をたどって行くと解析しやすくなります。ぜひお試しください。
