【Excel #34】Excelの数式チェック(参照元のトレース)でセル参照の間違いを瞬時に発見!

この記事を読んでわかること
  • セル参照に問題があるセルを瞬時に見つける方法
  • セルの参照元を可視化するテクニック
  • セルの参照元を一括選択・移動するテクニック

Excelでデータを管理する際、セル参照を使って関数を組んでいる方も多いと思います。

特に、作成したExcelファイルをチームやほかの人に共有する際には、すべてのセルで正しいセル参照が行えているかどうかを確認してから共有することが大切です。

本記事では、眺めているだけでは発見できない数式エラーを確認する、「数式チェック」についてご紹介します

本記事はYouTube『Youseful / 人材教育の教科書』チャンネルより、以下の動画を記事
にまとめたものです。動画だけではなく、テキストで復習したい方もいるはず。全38の記事と動画を組み合わせて、Excelの基本操作をマスターしていきましょう。

▼前回の記事では、他社とシートを共有する際に重要な「ブックの共有・変更履歴」についてご紹介しています。

目次

数式チェックの方法は2つある

数式チェックの方法は、大きく分けると2つあります。

数式チェックの2つの方法
  • まとまった範囲の中にある、おかしな数式を見つけに行く方法
  • ひとつひとつのセルの内容を、都度見ていく方法

本記事では、2つの方法を順にご紹介します。

以下のワークシートを使って説明します。

「どの従業員が」(従業員ID、氏名)、「いつ」(1Q~4Q)、「いくら売ったのか」(D3~G11)という情報が、累計も含めてまとまっています。

売上金額のうち、

  • 青文字の部分(各従業員、各Qの売上金額)はベタ打ちの数値
  • 黒文字の部分(累計金額)はSUM関数を使った数値

が入っています。

黒文字の部分には数式(セル参照)が使われているため、数式チェックが必要です。

方法1:まとまった範囲の中にある、おかしな数式を見つけに行く方法

1つ目の数式チェックの方法は、まとまった範囲の中にある、おかしな数式を見つける方法です。

「累計」列に入っている数値(H3~H11)を使って、操作方法を説明します。

手順
  1. H3~H11セルを選択
  2. 選択範囲にジャンプ
    ショートカット:Ctrl+G
    ~ジャンプダイアログが表示される~
  3. セル選択(S)」をクリック
    ショートカット:Alt+S
    ~選択オプションダイアログが表示される~
  4. アクティブ列との相違(M)」を選択
    ※列方向にまとまったセルについて調べるため
  5. [OK]をクリック
    ~チェック結果が表示される~
2. 選択範囲にジャンプ〜3. 「セル選択(S)」をクリック
4. 「アクティブ列との相違(M)」を選択

これで、まとまった範囲のセル(今回はH3~H11)の中から、おかしな数式になっているセルを見つけ出すことができました。

①H5セル(1つ目の検索結果であるため、アクティブセルになっている)と②H7セル(2つ目以降の検索結果であるため、グレーの網掛けされている)に、チェックがついていることがわかります。

方法2:ひとつひとつのセルの内容を、都度見ていく方法

広範囲からおかしなセルのみ発見できたら、あとはひとつひとつ確認していきます。

まずはH5セル(「伊藤修平」さんの累計売上金額)を見ていきましょう。

手順

6. 編集したいセルを選択(アクティブセルにする)

7. 選択中のセルを編集モードにする
 ショートカット:F2
 ~セルの入力内容や参照範囲が表示される~

すると、「累計」(H列)の入力規則は、同じ行の「1Q」(D列)~「4Q」(G列)の合計であるのに対して、H5セルでは5行目だけでなく6行目の「1Q」~「4Q」の値まで選択されていることがわかります。

問題がわかれば、間違っている箇所を修正すれば完了です。

(今回は「=SUM(D5:G6)」を「=SUM(D5:G5)」に変更)

以上の手順を、おかしなセルがなくなるまで(=手順5. の後に印が付くセルがなくなるまで)繰り返します。

次に手順1.~5.を繰り返すと、先ほど2つ目の検索結果だったH7セルのみに印が付くはずです。

H7セルで手順6.~7.を繰り返すと、「累計」(H列)の入力規則は、同じ行の「1Q」(D列)~「4Q」(G列)の合計であるのに対して、H7セルは数式ではなくベタ貼りの数値が入力されています。

問題がわかったら、間違っている箇所を修正していきます。

(今回はベタ貼りの数値を「=SUM(D7:G7)」に変更)

ここでもう一度1.~5.の手順を繰り返すと、セルが何も選択されず、「該当するセルが見つかりません。」というエラーメッセージが表示されます。

これで、おかしなセルをすべて修正できました。

次に、「累計」行に入っている数値(D12~G12)の範囲もチェックしていきます。

行で範囲を選択する場合も、基本的な操作方法は前述の手順と同じです。

手順
  1. D12~G12セルを選択
  2. 選択範囲にジャンプ
    ショートカット:Ctrl+G
    ~ジャンプダイアログが表示される~
  3. セル選択(S)」をクリック
    ショートカット:Alt+S
    ~選択オプションダイアログが表示される~
  4. アクティブ行との相違(W)」を選択
    ※行方向にまとまったセルについて調べるため
  5. [OK]をクリック
    ~チェック結果が表示される~
4. 「アクティブ行との相違(W)」を選択

これで、まとまった範囲のセル(今回はD12~G12)の中から、おかしな数式になっているF12セルを見つけ出すことができました。

F12セルで手順6.~7.を繰り返すと、「累計」(12行)の入力規則は、同じ列の各従業員の売り上げ(3~11行)の合計であるのに対して、F12セルは数式ではなくベタ貼りの数値が入力されています。

問題がわかったら、間違っている箇所を修正していきます。

(今回はベタ貼りの数値を「=SUM(F3:F11)」に変更)

手順
  1. 編集したいセルを選択(アクティブセルにする)SUM関数を適用する(「=SUM(F3:F11)」と入力)
    ショートカット:Alt+Shift+「=」

ここでもう一度1.~5.の手順を繰り返すと、セルが何も選択されず、「該当するセルが見つかりません。」というエラーメッセージが表示されます。

これで、選択した範囲での数式の修正は完了です。

ショートカットまとめ
  • ジャンプする:Ctrl+G
  • ジャンプダイアログで「セル選択(S)」に移動:Alt+S
  • 選択中のセルを編集モードにする:F2
  • SUM関数を適用する(Auto SUM):Alt+Shift+「=」

参照元のセルを視覚的に表示する「参照元のトレース」

セル参照を行った際、「どこのセルから値を引っ張ってきているのか」を視覚的に表示する機能として、「参照元のトレース」という機能があります。

参照元のトレースも数式をチェックする際には非常に便利な機能ですので、ぜひマスターしておきましょう。

手順:参照元のトレース
  • 参照元を表示したいセルを選択する(アクティブセルにする)
  • メニューバー>数式>ワークシート分析>「参照元のトレース」をクリック
    ショートカット:Alt+T→U→T
    ~選択したセルで参照されたセルが「」で表示される~
2. メニューバー>数式>ワークシート分析>「参照元のトレース」をクリック
参照元のトレース(Alt+T→U→T)でセルの参照元セルを青矢印で表示することができます。

これで、選択したセルに参照されているセルが青い矢印で表示されました。

解除したい場合は、以下の手順で可能です。

手順:参照元のトレースの解除
  • 参照元を表示したいセルを選択する(アクティブセルにする)
  • メニューバー>数式>ワークシート分析>「トレース矢印の解除」をクリック
    ショートカット:Alt+T→U→A
    ~参照元のトレースが解除される(「」が消える)~
参照元のトレースを解除(Alt+T→U→A)で表示した参照元セルを示す青矢印を削除することができます。
ショートカットまとめ
  • 参照元のトレース:Alt+T→U→T
  • 参照元のトレースの解除:Alt+T→U→A

おまけ:参照元のセルへの移動

最後に、数式チェックで使える便利なショートカットをご紹介します。

このショートカットを使うことで、選択したセルの参照元セルを一気に選択・移動することができます。

手順
  1. 任意のセルを選択
  2. 参照元のセルへ移動
    ショートカット:Ctrl+[
    ~参照元のセルが選択され、アクティブセルが1つ目の参照元のセルに移動する~
  3. (任意:アクティブセルを移動してい場合)Tabキーをクリック
Ctrl+[をクリックで、選択中のセル内で参照されているセルを一括選択するうえ、移動することができます。

これで、参照元のセルを一括選択したうえで、1つ目の参照元に移動することができました。

このショートカットが特に活躍するのが、参照元のセルの値が、さらに別シートから参照された値であるケースです。

参照元が別シートである場合でも、Ctrl+[をクリックすると、シート間の移動も含めてジャンプすることが可能です。

まとめ

本記事では、数式チェックについてご紹介しました。
数式チェックのポイントは3つです。

  1. 第3者にファイルを共有する前に数式チェックを行う
  2. まとまった範囲の中から変なものを見つけたいときはジャンプ機能を使う
  3. セルの参照元をチェックする方法は、①F2キー②Alt+T→U→Tの2つの方法がある

次回の記事では、「美しいシートに仕上げるデザインのルール」についてご紹介します。
▼次回の記事はこちら

ここまで読んで「Excelをもっと学びたい」「実務で活かせるようになりたい」と思った方におすすめなのが、ユースフルが提供するオンライン講座の『ExcelPro(エクセルプロ)』とMOS Excel 365&2019スペシャリスト完全対策講座』。

講座①:ExcelPro(エクセルプロ)

ExcelPro(エクセルプロ)

ExcelProは108本の動画と15の演習問題で実務に直結するExcelスキルが学べるオンライン講座。全てのレッスンにサンプルシートがついており、講義と同じデータで学習を進めることができます。

さらに、充実した動画内容に加えてメールでの質問サポート付き。受講時にわからなかったことは質問することで復習につなげることができます。

口コミが気になる方は下記の記事をご一読ください。実際のExcelProの受講者の声から口コミや評判を紹介しています。

講座②:MOS Excel 365&2019完全対策講座

ユースフルではMicrosoft MVPの監修のもと、MOS試験(スペシャリスト・エキスパート)対策講座を提供しています。

約70の豊富な動画講座・各章末の確認テスト・5回の模擬試験を通じて試験の出題範囲をすみずみまで学び、最短合格を目指しましょう!

【ユースフル提供】MOSスペシャリスト完全対策講座

9,800円(税込)

おすすめポイント
  • 全69動画
  • 各章末に確認テスト
  • 5回の模擬試験
【ユースフル提供】MOSエキスパート完全対策講座

12,800円(税込)

おすすめポイント
  • 全74動画
  • 各章末に確認テスト
  • 5回の模擬試験

こちらの人気記事にも注目!

ユースフル編集部では、ExcelやMOS関連の情報はもちろん、AccessなどMicrosoftツールの使い方も解説しています。こちらの人気記事もあわせてご覧ください!

気に入ったらシェア!
URLをコピーする
URLをコピーしました!

この記事を書いた人

記事をお読みいただきありがとうございます!ユースフル編集部では、Excelをはじめとするマイクロソフト製品の活用法をわかりやすく紹介しています。ご興味ある方は、ぜひ他の記事もご覧ください!

目次
閉じる