12 Chapter2 数式の分析 [数式]タブ‐[ワークシート分析]／[計算方法]グループ [ファイル]タブ‐[オプション]
2
この Chapter では、数式の計算結果がエラー値で表示されたり、意図した値が得られなかっ数式の分析
た場合に、数式の構成や参照関係を検証したり、エラーの修正に役立つ数式の分析機能や Excel の数式に関する設定について学習します。数式の分析は、主に[数式]タブの[ワークシー ト分析]グループを使用し、数式に関する設定は、[ファイル]タブの[オプション]や[数式]タブ の[計算方法]グループを使用します。映 像
Chapter2 で習得する内容を確認しましょう
“3 月売上”シート “2 月売上”シートChapter2 数式の分析 13 チェック チェック項目 ボタン/ コマンド 番号 STEP 番号 問題 番号 入門/ 応用/ ｽﾍﾟｼｬﾘｽﾄ □ 数式の参照元をトレースできる ① 2、3、10 1、3 □ 参照先をトレースできる ② 7 4 □ エラーをトレースできる ③ 12、21 5 □ ワークシート内の無効なデータや無効な数式を検出して修正できる ③ 20～26 3 □ 数式を検証して修正できる ④ 13、16、23 2 □ [エラーチェックオプション]ボタンを使用して、エラーの原因を確認し、対処ができる 1 入P66 □ ブックの計算方法(自動と手動)を設定できる ⑤⑥ 38 5 □ 反復計算を行うように設定できる ⑦ 34 4 □ エラーインジケーターの色を変更できる ⑧ 19 1 映 像
Section1 で習得する内容を確認しましょう
1. Excel を起動し、ドキュメントライブラリ内“Chapter2”フォルダーから“2-1.xlsx”ブックを開 きましょう。 2. “1 月売上”シートを表示し、セル A6 の数式が参照しているセルを、トレース矢印を表示して確 認しましょう。 3. 2.で確認したセル A6 の数式の参照元のセルが、さらに参照しているすべてのセルを、トレース矢 印を表示して確認しましょう。(参照しているセルが、これ以上ない場合はエラー音が鳴ります) ※ 他のワークシートを参照している場合は、黒の点線のトレース矢印とワークシートアイコン が表示されます。 トレース矢印の両端には、● と▲が表示され、●側は参照 元、▲側は参照先を表しま す。トレース矢印をダブルク リックすると、参照元と参照 先のセル間をジャンプする ことができます。数式の分析と数式オプション
2-1
数式の分析機能を利用すると、ワークシート内にエラーが発生していないかをチェックしたり、トレース矢 印を表示して数式が参照しているセルに誤りがないかを視覚的に確認したり、複雑な数式の計算過程を 1 ス テップずつ確認して、エラーがどの部分で発生したかを検証することができます。また、数式オプションを 変更すると、通常はエラーとみなされる循環参照を利用した反復計算を行ったり、自動で行われる再計算を 手動に変更することができます。数式の分析は、主に[数式]タブの[ワークシート分析]グループを使用し、数 式オプションの設定は、[ファイル]タブの[オプション]をクリックして表示される[Excel のオプション]ダイア ログボックスを使用します。ここでは、次のチェック項目について学習しましょう。 [数式]タブ-[ワークシート分析]／[計算方法] ① ② ④ ⑤ ⑥ ⑦ ③ ⑧ [Excel のオプション]ダイアログボックス14 Chapter2 数式の分析 [トレース矢印の削除]ボタン ンの▼から[参照元トレース 矢印の削除]や[参照先トレー ス矢印の削除]をクリックす ると、アクティブセルの参照 元や参照先のトレース矢印 を段階的に削除することが できます。 エラー値は、数式の参照元の セルにエラーが発生してい る場合、参照先のセルにも同 じエラー値が表示されるた め、そのセル自体の数式に問 題があるとは限りません。 #REF!(リファレンス)： 数式内のセル参照が無効な 場合に表示されます。 [数式の検証]ダイアログボッ クスの ステップイン ボタン を使用すると、数式内の参照 元のセルをアクティブにし、 入力されている値や数式をさ らに検証することができま す。 ステップアウト ボタン を使用して、検証中の元のセ ルに戻ることができます。 Excel の既定では、エラーの 可能性のあるセルの左上隅 に、エラーインジケーター(緑 色の三角形)が表示されます。 このセルを選択すると[エラ ーチェックオプション] ボタンが表示され、クリック して表示される一覧からオ プションを選択して、エラー を解決したり、エラーを無視 することができます。 4. セル H9 に向かって表示されている黒の点線のトレース矢印を利用して、単価(セル H9)に入力され た数式が参照している“マスターリスト”シートのセル範囲にジャンプしましょう。 5. F5 キーを押して[ジャンプ]ダイアログボックスを表示し、直前に選択していたセル(“1 月売上” シートのセル A6)に戻りましょう。 6. ワークシート上のトレース矢印をすべて削除しましょう。 ※ トレース矢印を保存することはできません。ブックを保存したり、閉じると削除されます。 7. セル I9 の売上冊数を参照している数式のすべてのセルを、トレース矢印を表示して確認しましょう。 8. セル I9 を「15」に変更し、参照先のセルの数式が再計算されることを確認しましょう。 9. ワークシート上のトレース矢印をすべて削除しましょう。 10. “2 月売上”シートを表示し、エラー値“#REF!”が表示されたセル A6 の数式の参照元をトレー スして、売上金額(セル I9 から I23)内に発生しているエラーが原因であることを確認しましょう。 ※ 参照元のセルにエラーが含まれる場合、赤いトレース矢印が表示されます。 11. ワークシート上のトレース矢印をすべて削除しましょう。 12. セル A6 の数式のエラーをトレースし、エラーの原因となるセルを、トレース矢印を表示して確認 しましょう。 ※ エラーの原因が参照元のセルのエラーである場合、[エラーのトレース]を使用すると、エラー のおおもととなったセルにアクティブセルが移動し、そのセルの参照元のトレース矢印が表示 されます。 13. セル H21 の数式を検証し、エラー値“#REF!”が、VLOOKUP 関数内で発生していることを確認 しましょう。 ※ エラー値“#REF!”は、VLOOKUP 関数の引数[範囲]で指定したセル範囲(“マスタ－リスト” シートのセル A4 から C22)が 3 列分であるのに対し、引数[列番号]で“4”と指定しているた めに発生しています。 14. セル H21 の数式 を「 =IF(D21="","",VLOOKUP(D21, マスターリスト!$A$4:$C$22,3,FALSE))」 (VLOOKUP 関数の引数[列番号]を「3」)と修正し、セル A6、セル H21、セル J21、セル J24 のエ ラーが解消されることを確認しましょう。 15. ワークシート上のトレース矢印をすべて削除しましょう。
16. セル F23 の数式を検証し、エラー値“#N/A!”が、参照元のセル E23 の LEFT 関数が原因で発生し ていることを確認しましょう。
※ エラー値“#N/A!”は、セル E23 に入力された LEFT 関数で、書籍コードの左端から 2 文字分 を取り出すところを、引数[文字数]に“3”と指定しているために発生しています。 17. セル E23 の数式を「=LEFT(D23,2)」(引数[文字数]を「2」)と修正し、セル F23 のエラーが解消さ れることを確認しましょう。 18. “3 月売上”シートを表示し、ワークシート内に複数の緑色のエラーインジケーターが表示されて いることを確認しましょう。 ※ エラーインジケーターは印刷されません。 19. エラーインジケーターの色を赤に変更しましょう。(Excel の既定値が変更されます) ※ [Excel のオプション]ダイアログボックスの[数式]の[バックグウンドでエラーチェックを行う] チェックボックスをオフにすると、エラーインジケーターは表示されなくなります。 20. セル A1 を選択し、[エラーチェック]ダイアログボックスを使用して、ワークシート内で発生して いるエラーを確認しましょう。確認するエラーの内容や解決方法は、21.～26.の指示に従って進め ましょう。 21. セル D6 のエラーをトレースし、エラー値“#VALUE!”の原因が、セル J9 のエラーであることを 確認しましょう。確認後、次に進みましょう。
Chapter2 数式の分析 15 22. セル J9 の数式を、売上金額を求める正しい数式に修正しましょう。修正後、エラーチェックを再 開しましょう。 23. セル C17 の数式を検証し、エラー値“#N/A!”が、HLOOKUP 関数で発生していることを確認しま しょう。 ※ エラー値“#N/A!”は、セル B17 に入力された店舗コードが、HLOOKUP 関数の引数[範囲]で 指定したセル範囲(セル A4 から D5)に存在しないために発生しています。 24. セル B17 の値を「K2」に修正しましょう。修正後、エラーチェックを再開しましょう。 25. セル E27 の矛盾した数式を確認し、数式を上のセルからコピーして修正しましょう。 ※ 数式を修正する必要がない場合は、 エラーを無視する ボタンをクリックし、エラーチェックの 対象外にすることができます。(エラーを無視すると、エラーインジケーターも非表示になります) 26. セル I28 の文字列として保存されている数値を数値に変換しましょう。 27. 現在と同じファイル名で、ドキュメントライブラリ内“Chapter2”フォルダーの“保存用”フォル ダーに保存しましょう。ブックを閉じましょう。 28. ドキュメントライブラリ内“Chapter2”フォルダーから“ランチ価格.xlsx”ブックを開きましょう。 29. “価格試算表”シートに入力されている数式をセルに表示し、売上金額(セル D4 から D5)と合計(セ ル C6 から D6)に入力されている数式を確認しましょう。確認後、数式を非表示にしましょう。 30. 日替わりランチの価格(セル B4)に「980」と入力しましょう。 31. スペシャルランチの価格(セル B5)を、次のような条件で IF 関数を使用して求めましょう。 日替わりランチ(980 円)を 35 食、スペシャルランチを 20 食完売することを前提とし、売上金額の 合計が 55,000 円を超えるようにしましょう。 ※ 売上金額の合計が 55,000 円に満たない場合は、現在の価格(セル B5)に 10 円を加算し、55,000 円を超えた場合は、現在の価格をそのまま表示する数式を作成します。 ([循環参照に関する警告]メッセージが表示されたら、 OK ボタンをクリックします) ※ 循環参照のセル B5 には“0”(ゼロ)と表示され、トレース矢印が表示されます。また、ステータ スバーには“循環参照：B5”、“循環参照：D5”、“循環参照：D6”のいずれかが表示されます。 32. 現在と同じファイル名で、ドキュメントライブラリ内“Chapter2”フォルダーの“保存用”フォル ダーに保存し、ブックを閉じましょう。 33. “ランチ価格.xlsx”ブックを再度開き、循環参照が解決されていないブックを開くと、[循環参照 に関する警告]メッセージが表示されることを確認しましょう。 34. 反復計算が最大 120 回まで実行できるように設定し、スペシャルランチの価格を求めましょう。 35. 上書き保存し、ブックを閉じましょう。 36. ドキュメントライブラリ内“Chapter2”フォルダーから“契約実績.xlsx”ブックを開きましょう。 37. “第 1 四半期”シートの 1 月(C 列)に、「720」、「350」、「1700」、「550」と契約件数を入力し、“第 1 四半期”シートと“前年比較”シート内の数式が再計算されることを確認しましょう。 38. ブック内の数式が自動で計算されないように設定し、“第 1 四半期”シートの 2 月(D 列)に、「600」、 「450」、「2400」、「420」と入力して、再計算されないことを確認しましょう。 ※ 計算方法を手動に設定した後、数式の参照元にデータを入力すると、ステータスバーに“再計 算”と表示され、この部分をクリックしても再計算を実行することができます。 39. ブック内の数式を手動で再計算し、“第 1 四半期”シートと“前年比較”シート内の数式が再計算さ れることを確認しましょう。 40. “第 1 四半期”シートの 3 月(E 列)に、「850」、「600」、「3500」、「800」と入力しましょう。 41. 現在と同じファイル名で、ドキュメントライブラリ内“Chapter2”フォルダーの“保存用”フォル ダーに保存し、ブックを保存する前に再計算されることを確認しましょう。 既定では、エラー値だけでな く、上下や左右のセルと異な る数式が入力されている場 合や数値が文字列データと して入力されていた場合に、 ミスの可能性が高いと判断 され、エラーチェックの対象 となります。エラーチェック の対象となる項目は、[Excel のオプション]ダイアログボ ックスの[数式]の[エラーチ ェックルール]で設定するこ とができます。 『循環参照』とは、数式内で 数式を入力したセル自身を 参照している状態のことで す。Excel ではエラーの一種 としてみなされ、警告メッセ ージが表示されます。循環参 照を解決するには、循環参照 にならないように数式を修 正するか、反復計算が行える ように設定します。 『反復計算』とは、特定の数 値条件が満たされるまで、繰 り返し再計算を行う処理のこ とです。反復計算の設定は、 ブックごとに保存されます。 計算方法の設定は、ブックご とに保存されます。計算方法 を手動に設定したブックを 再計算するには、[数式]タブ -[計算方法]-[再計算実行]ボ タンや[シート再計算]ボタ ンをクリックします。 計算方法を手動にすると、既 定では、[Excel のオプショ ン]ダイアロ グボック スの [数式]の[ブックの保存前に 再計算を行う]チェックボッ クスがオンになります。
16 Chapter2 数式の分析 [エラーチェックオプション] ボタンを使用して、一度にエ ラーを無視します。 セルC4 には、循環参照を利 用した数式が入力されてい ます。 =IF(D6>=50000,C4,C4+1) 以下は、この Chapter で学習した機能を問題形式にしたものです。短時間で正確に解答できるように、 効率の良い方法を確認しながら解答しましょう。 解答用のブックは、ドキュメントライブラリ内“Chapter2”フォルダーから開きましょう。 特に問題文に指示が無い限り、解答(操作)後のブックは、[ファイル]タブ-[名前を付けて保存]をクリック し、[名前を付けて保存]ダイアログボックスを表示して、現在と同じファイル名、同じファイルの種類 で、ドキュメントライブラリ内“Chapter2”フォルダーの“保存用”フォルダーに保存し、ブックを 閉じて、次の問題に進みましょう。 問題 1 “2-問題 1.xlsx”ブックを開いて、次の 2 つの問題を解答しなさい。 1. エラーインジケーターの色をオレンジに変更しなさい。 2. “第 1 四半期”シートのセル G4 のエラーの原因を確認し、参照元をトレース矢印で表示しな さい。確認後、セル G4 から G7 のエラーを無視しなさい。 問題 2 “2-問題 2.xlsx”ブックを開いて、次の問題を解答しなさい。 1. “試験結果”シートに発生しているエラー値“#VALUE!”の数式を検証し、エラーの原因を確 認して、数式を修正しなさい。 問題 3 “2-問題 3.xlsx”ブックを開いて、次の問題を解答しなさい。 1. “前年比較”シート内に発生しているエラーをチェックし、矛盾した数式のすべての参照元を トレース矢印で表示しなさい。 問題 4 “2-問題 4.xlsx”ブックを開いて、次の 2 つの問題を解答しなさい。(ブックを開く際に表示される[循 環参照に関する警告]メッセージは、 OK ボタンをクリックします) 1. 反復計算を最大 50 回まで有効にし、売上金額の合計が 5 万円以上になるように、日替わりラン チの限定数を求めなさい。 2. 日替わりランチの価格のすべての参照先をトレース矢印で表示しなさい。 問題 5 “2-問題 5.xlsx”ブックを開いて、次の 2 つの問題を解答しなさい。 1. ブックの計算方法を自動に設定しなさい。 2. セル C9 のエラーをトレースし、エラー値“#N/A!”の原因となったおおもとのセルを選択しなさい。
