2-1 数 式 の 分 析 と 数 式 オプション 数 式 の 分 析 機 能 を 利 用 すると ワークシート 内 にエラーが 発 生 していないかをチェックしたり トレース 矢 印 を 表 示 して 数 式 が 参 照 しているセルに 誤 りがないかを 視 覚 的 に 確 認 したり 複 雑 な

数 式 の 分 析 2 この Chapter では 数 式 の 計 算 結 果 がエラー 値 で 表 示 されたり 意 図 した 値 が 得 られなかっ た 場 合 に 数 式 の 構 成 や 参 照 関 係 を 検 証 したり エラーの 修 正 に 役 立 つ 数 式 の 分 析 機 能 や Excel の 数 式 に 関 する 設 定 について 学 習 します 数 式 の 分 析 は 主 に[ 数 式 ]タブの[ワークシー ト 分 析 ]グループを 使 用 し 数 式 に 関 する 設 定 は [ファイル]タブの[オプション]や[ 数 式 ]タブ の[ 計 算 方 法 ]グループを 使 用 します 映 像 Chapter2 で 習 得 する 内 容 を 確 認 しましょう 2 月 売 上 シート 3 月 売 上 シート [ファイル]タブ [オプション] [ 数 式 ]タブ [ワークシート 分 析 ]/[ 計 算 方 法 ]グループ 12 Chapter2 数 式 の 分 析

2-1 数 式 の 分 析 と 数 式 オプション 数 式 の 分 析 機 能 を 利 用 すると ワークシート 内 にエラーが 発 生 していないかをチェックしたり トレース 矢 印 を 表 示 して 数 式 が 参 照 しているセルに 誤 りがないかを 視 覚 的 に 確 認 したり 複 雑 な 数 式 の 計 算 過 程 を 1 ス テップずつ 確 認 して エラーがどの 部 分 で 発 生 したかを 検 証 することができます また 数 式 オプションを 変 更 すると 通 常 はエラーとみなされる 循 環 参 照 を 利 用 した 反 復 計 算 を 行 ったり 自 動 で 行 われる 再 計 算 を 手 動 に 変 更 することができます 数 式 の 分 析 は 主 に[ 数 式 ]タブの[ワークシート 分 析 ]グループを 使 用 し 数 式 オプションの 設 定 は [ファイル]タブの[オプション]をクリックして 表 示 される[Excel のオプション]ダイア ログボックスを 使 用 します ここでは 次 のチェック 項 目 について 学 習 しましょう [ 数 式 ]タブ-[ワークシート 分 析 ]/[ 計 算 方 法 ] [Excel のオプション]ダイアログボックス 1 2 3 5 4 6 7 8 チェック チェック 項 目 ボタン/ コマンド STEP 問 題 入 門 / 応 用 / スヘ シャリスト 数 式 の 参 照 元 をトレースできる 1 2 3 10 1 3 参 照 先 をトレースできる 2 7 4 エラーをトレースできる 3 12 21 5 ワークシート 内 の 無 効 なデータや 無 効 な 数 式 を 検 出 して 修 正 できる 3 20~26 3 数 式 を 検 証 して 修 正 できる 4 13 16 23 2 [エラーチェックオプション]ボタンを 使 用 して エラーの 原 因 を 確 認 し 対 処 ができる 1 入 P66 ブックの 計 算 方 法 ( 自 動 と 手 動 )を 設 定 できる 56 38 5 反 復 計 算 を 行 うように 設 定 できる 7 34 4 エラーインジケーターの 色 を 変 更 できる 8 19 1 映 像 Section1 で 習 得 する 内 容 を 確 認 しましょう 1. Excel を 起 動 し ドキュメントライブラリ 内 Chapter2 フォルダーから 2-1.xlsx ブックを 開 きましょう 2. 1 月 売 上 シートを 表 示 し セル A6 の 数 式 が 参 照 しているセルを トレース 矢 印 を 表 示 して 確 認 しましょう 3. 2.で 確 認 したセル A6 の 数 式 の 参 照 元 のセルが さらに 参 照 しているすべてのセルを トレース 矢 印 を 表 示 して 確 認 しましょう ( 参 照 しているセルが これ 以 上 ない 場 合 はエラー 音 が 鳴 ります) 他 のワークシートを 参 照 している 場 合 は 黒 の 点 線 のトレース 矢 印 とワークシートアイコン が 表 示 されます トレース 矢 印 の 両 端 には と が 表 示 され 側 は 参 照 元 側 は 参 照 先 を 表 しま す トレース 矢 印 をダブルク リックすると 参 照 元 と 参 照 先 のセル 間 をジャンプする ことができます Chapter2 数 式 の 分 析 13

4. セル H9 に 向 かって 表 示 されている 黒 の 点 線 のトレース 矢 印 を 利 用 して 単 価 (セル H9)に 入 力 され た 数 式 が 参 照 している マスターリスト シートのセル 範 囲 にジャンプしましょう 5. F5 キーを 押 して[ジャンプ]ダイアログボックスを 表 示 し 直 前 に 選 択 していたセル( 1 月 売 上 シートのセル A6)に 戻 りましょう [トレース 矢 印 の 削 除 ]ボタン ンの から[ 参 照 元 トレース 矢 印 の 削 除 ]や[ 参 照 先 トレー ス 矢 印 の 削 除 ]をクリックす ると アクティブセルの 参 照 元 や 参 照 先 のトレース 矢 印 を 段 階 的 に 削 除 することが できます 6. ワークシート 上 のトレース 矢 印 をすべて 削 除 しましょう トレース 矢 印 を 保 存 することはできません ブックを 保 存 したり 閉 じると 削 除 されます 7. セル I9 の 売 上 冊 数 を 参 照 している 数 式 のすべてのセルを トレース 矢 印 を 表 示 して 確 認 しましょう 8. セル I9 を 15 に 変 更 し 参 照 先 のセルの 数 式 が 再 計 算 されることを 確 認 しましょう 9. ワークシート 上 のトレース 矢 印 をすべて 削 除 しましょう エラー 値 は 数 式 の 参 照 元 の セルにエラーが 発 生 してい る 場 合 参 照 先 のセルにも 同 じエラー 値 が 表 示 されるた め そのセル 自 体 の 数 式 に 問 題 があるとは 限 りません 10. 2 月 売 上 シートを 表 示 し エラー 値 #REF! が 表 示 されたセル A6 の 数 式 の 参 照 元 をトレー スして 売 上 金 額 (セル I9 から I23) 内 に 発 生 しているエラーが 原 因 であることを 確 認 しましょう 参 照 元 のセルにエラーが 含 まれる 場 合 赤 いトレース 矢 印 が 表 示 されます 11. ワークシート 上 のトレース 矢 印 をすべて 削 除 しましょう 12. セル A6 の 数 式 のエラーをトレースし エラーの 原 因 となるセルを トレース 矢 印 を 表 示 して 確 認 しましょう エラーの 原 因 が 参 照 元 のセルのエラーである 場 合 [エラーのトレース]を 使 用 すると エラー のおおもととなったセルにアクティブセルが 移 動 し そのセルの 参 照 元 のトレース 矢 印 が 表 示 されます #REF!(リファレンス): 数 式 内 のセル 参 照 が 無 効 な 場 合 に 表 示 されます 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. ワークシート 上 のトレース 矢 印 をすべて 削 除 しましょう [ 数 式 の 検 証 ]ダイアログボッ クスの ステップイン ボタン を 使 用 すると 数 式 内 の 参 照 元 のセルをアクティブにし 入 力 されている 値 や 数 式 をさ らに 検 証 することができま す ステップアウト ボタン を 使 用 して 検 証 中 の 元 のセ ルに 戻 ることができます Excel の 既 定 では エラーの 可 能 性 のあるセルの 左 上 隅 に エラーインジケーター( 緑 色 の 三 角 形 )が 表 示 されます このセルを 選 択 すると[エラ ーチェックオプション] ボタンが 表 示 され クリック して 表 示 される 一 覧 からオ プションを 選 択 して エラー を 解 決 したり エラーを 無 視 することができます 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 のエラーであることを 確 認 しましょう 確 認 後 次 に 進 みましょう 14 Chapter2 数 式 の 分 析

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 ブックを 開 きましょう 既 定 では エラー 値 だけでな く 上 下 や 左 右 のセルと 異 な る 数 式 が 入 力 されている 場 合 や 数 値 が 文 字 列 データと して 入 力 されていた 場 合 に ミスの 可 能 性 が 高 いと 判 断 され エラーチェックの 対 象 となります エラーチェック の 対 象 となる 項 目 は [Excel のオプション]ダイアログボ ックスの[ 数 式 ]の[エラーチ ェックルール]で 設 定 するこ とができます 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 のいずれかが 表 示 されます 循 環 参 照 とは 数 式 内 で 数 式 を 入 力 したセル 自 身 を 参 照 している 状 態 のことで す Excel ではエラーの 一 種 としてみなされ 警 告 メッセ ージが 表 示 されます 循 環 参 照 を 解 決 するには 循 環 参 照 にならないように 数 式 を 修 正 するか 反 復 計 算 が 行 える ように 設 定 します 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 のオプショ ン] ダイアログボックスの [ 数 式 ]の[ブックの 保 存 前 に 再 計 算 を 行 う]チェックボッ クスがオンになります Chapter2 数 式 の 分 析 15

Chapter 2 力 試 し 以 下 は この 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 ボタンをクリックします) セル C4 には 循 環 参 照 を 利 用 した 数 式 が 入 力 されてい ます =IF(D6>=50000,C4,C4+1) 1. 反 復 計 算 を 最 大 50 回 まで 有 効 にし 売 上 金 額 の 合 計 が 5 万 円 以 上 になるように 日 替 わりラン チの 限 定 数 を 求 めなさい 2. 日 替 わりランチの 価 格 のすべての 参 照 先 をトレース 矢 印 で 表 示 しなさい 問 題 5 2- 問 題 5.xlsx ブックを 開 いて 次 の 2 つの 問 題 を 解 答 しなさい 1. ブックの 計 算 方 法 を 自 動 に 設 定 しなさい 2. セル C9 のエラーをトレースし エラー 値 #N/A! の 原 因 となったおおもとのセルを 選 択 しなさい 16 Chapter2 数 式 の 分 析