2017年07月30日

参照元のトレースでシートの計算式をチェックしてみた(一括チェックも)/ Excel / ワークシート分析

Excelでワークシートの計算式の参照元や参照範囲を「ワークシート分析」の「参照元のトレース」を使ってチェックしてみます。

長い間使いまわししている、ワークシートや他の人が作ったワークシートの計算式の参照元や参照範囲が何かの拍子でズレたり、範囲が変わってしまったりすることってたまーにあります。
それを知らずに使っていた大変なことになってしまってからでは時既に遅し!
と、言うことで事前にチェックするようにしましょう。

どうするかと言うと、、、
こんな、家計の月毎の集計表があります。
一番下の行には毎月の合計、右端の列には各項目の合計が計算されていて、右下のセルには総合計が計算されています。
20170729_01.JPG

右下のセルを選択します。
“列O”の合計が“SUM関数”で計算されています。
20170729_02.JPG

ここでメニューの「数式」、「ワークシート分析」から「参照元のトレース」を選択します。
20170729_03.JPG

すると、“列O”の範囲が青枠で囲まれて(図だと分かり辛くてスミマセン(;'∀'))矢印がひかれました。
総合計のセルの参照範囲が見て分かるようになりました。
20170729_04.JPG

で、更に「参照元のトレース」を選択すると、今度はその“列O”の各項目の合計の参照範囲が同じ様に表示されました。
20170729_05.JPG

ちなみに、矢印を消去するには「トレース矢印の削除」をクリックすると消えます。
20170729_06.JPG

で、12月の合計のセルを選択して
20170729_07.JPG

「参照元のトレース」を選択するとこの様になります。
20170729_08.JPG

と言うわけで、シートの中の計算式が入力してあるセルを一つ一つ選択して「参照元のトレース」を行うのは面倒くさいんで、何か一括で全ての計算してあるセルの参照元のトレースが出来ないかとググっていたらありました。裏技が、、、

複数のサイトで同じやり方が載っていたので、元々考案した人がどなたか分かりませんが、ここからは引用です。

まず、どこか適当なセルに“=”を入力します。
で、まだ「enter」は押しません。
20170729_09.JPG

で、そのまま“列A”と“行1”の角をクリックしてワークシート全体を選択します。
すると、先ほどのセルは“=1:1048576”となります。
20170729_10.JPG

ちまり、このセルはワークシート全体を参照元にしたということになります。
なので、このセルの参照元をたどれば、ワークシートに含まれる計算式が入力されたセルの参照元が辿れるということになるわけです。
これを考えた人、あっタマいいなー。。。
まずこのセルをせんたくして、
20170729_11.JPG

「参照元のトレース」をクリックします。
20170729_12.JPG

で、ここからです!
もう一度「参照元のトレース」をクリックします。
と、こんな感じになりますした。
20170729_13.JPG

がー、、、ごちゃごちゃしすぎて分かり辛いですねー。。。
試しにわざと間違えた式にしてみます。
1月の合計を“家賃”から“教育費”までにして、“交通費”と“医療費”が外れた状態にしてみます。
さて、これを発見することが出来るでしょか?
20170729_14.JPG

( ゚Д゚)ハァ?
これじゃ、さっきと一緒!漏れてるのがわかりません!
20170729_15.JPG

1月だけトレースしてみると分かりますが、
20170729_16.JPG

ここに、医療費の合計のトレースも入るともう分からなくなってしまいます。
こんなクロス集計の場合は分かり辛いですね。。。
20170729_17.JPG

一つ一つ確認しなければならないのなら、「F2」キーでもよさそうですね。。
20170729_18.JPG

用途に応じて上手く使い分けして、計算式の参照が正しいか確認しながら使いましょう!!




関連ページ
posted by haku1569 at 15:41| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする