「月々の返済額から逆算して借入可能額を知りたい」「価格を変えたとき利益がどう動くかを一覧で確認したい」——こうした目標値からの逆算・多変数シミュレーションをExcelで実現するのがゴールシークとデータテーブル（What-If分析）です。関数を組むより少ない手順で、ビジネスの意思決定に直結する分析ができます。
ゴールシークは「目標の結果から逆算して入力値を求める」機能です。例えば「利益を100万円にするために必要な販売数量は？」という問いにワンクリックで答えを出します。データテーブルは「1つまたは2つの変数を変化させたときの結果を表形式で一覧表示する」機能で、価格感度分析・利率シナリオ比較・損益分岐表の作成に活用されます。
本記事では、ゴールシークの基本操作手順・3つの実務シナリオ、1変数・2変数データテーブルの設定手順・実務への応用、両機能の使い分け、よくあるエラーと対処法、MOS Excel試験での出題ポイントを体系的に解説します。
ゴールシーク・データテーブルの位置づけ
ゴールシークとデータテーブルはどちらも「データ」タブ→「What-If分析」から起動します。シナリオ管理も同じメニューにあり、3つを合わせてExcelのWhat-If分析ツールと呼びます。
| 機能 | 使い方 | 典型的な用途 |
|---|---|---|
| ゴールシーク | 目標セル・目標値・変数セルを指定して逆算 | 損益分岐点・目標販売数・必要利率の計算 |
| データテーブル（1変数） | 1つの変数を変えた場合の結果を縦または横に一覧 | 利率別返済額・価格別利益・数量別売上 |
| データテーブル（2変数） | 2つの変数を同時に変えた結果をマトリクス表示 | 価格×数量の利益マトリクス・利率×期間の返済額比較 |
| シナリオ管理 | 複数の変数セットを保存して切り替え・レポート作成 | 楽観・中立・悲観の3シナリオ比較 |
ゴールシークの基本操作手順
ゴールシークは「結果セルに目標値を設定し、変化させるセルをExcelに探させる」機能です。関数の逆算とも言えます。
操作の前提条件
- 目標セル（数式セル）：結果を計算している数式が入っているセル。「目標値」として指定したい値のセルです
- 変化させるセル（変数セル）：Excelが自動で変更して目標を達成するセル。数式ではなく数値が直接入力されているセルを指定します
- 目標セルは変化させるセルを直接または間接的に参照していなければなりません
ゴールシークの起動と設定手順
- 「データ」タブ→「予測」グループ→「What-If分析」→「ゴールシーク」をクリック
- 「数式入力セル」（目標セル）を指定（例：利益を計算しているセルC10）
- 「目標値」に達成したい数値を入力（例：1000000）
- 「変化させるセル」に逆算したい変数セルを指定（例：販売数量セルB3）
- 「OK」をクリック→Excelが自動計算し「ゴールシーク実行状態」ダイアログで結果を表示
- 「OK」で結果を確定、「キャンセル」で元の値に戻す
重要：ゴールシークは変化させるセルを直接書き換えます。確定前に元の値をどこかにメモしておくか、「キャンセル」で戻す習慣をつけておきましょう。
ゴールシーク実務シナリオ
シナリオ1：損益分岐点の販売数量を逆算する
固定費・変動費・単価が決まっているとき、利益がゼロ（損益分岐点）になる販売数量を求めます。
' セル構成
B2: 販売単価（例: 3000円）
B3: 販売数量（例: 200個）← ゴールシークで変化させるセル
B4: 変動費単価（例: 1200円）
B5: 固定費（例: 300000円）
B8: 売上合計 =B2*B3
B9: 変動費合計 =B4*B3
B10: 利益 =B8-B9-B5 ← 目標セル（これをゼロにしたい）ゴールシーク設定：数式入力セル=B10、目標値=0、変化させるセル=B3。実行結果：B3に損益分岐点の販売数量（この例では167個）が自動入力されます。
シナリオ2：住宅ローンの借入可能額を逆算する
毎月の返済可能額が決まっているとき、PMT関数を使ってその返済額になる借入元本を逆算します。
' セル構成
B2: 年利（例: 0.02 = 2%）
B3: 借入期間（例: 35年）
B4: 借入金額（例: 30000000）← ゴールシークで変化させるセル
B7: 月々の返済額 =PMT(B2/12, B3*12, -B4) ← 目標セル
' ゴールシーク設定
' 数式入力セル: B7
' 目標値: 80000（月8万円に抑えたい）
' 変化させるセル: B4（借入金額）PMT関数は返済額を負数で返すため、-B4としてプラスの値にする工夫が必要です。目標値に80000を設定して実行すると、月8万円の返済に収まる借入金額が求まります。
シナリオ3：目標達成に必要な値引き率を求める
予算内に収めるために必要な値引き率を逆算します。
' セル構成
B2: 定価（例: 150000円）
B3: 値引き率（例: 0.10 = 10%）← ゴールシークで変化させるセル
B4: 消費税率（例: 0.10）
B7: 税込最終価格 =B2*(1-B3)*(1+B4) ← 目標セル
' ゴールシーク設定
' 数式入力セル: B7
' 目標値: 130000（予算13万円）
' 変化させるセル: B3（値引き率）データテーブルの基本概念
データテーブルは「変数の値を複数パターン用意して、それぞれの計算結果を表として一括出力する」機能です。同じ数式を何十行にもコピーして手動で変数を書き換える作業を自動化できます。
| 種別 | 変数の数 | レイアウト | 出力できる結果の数 |
|---|---|---|---|
| 1変数データテーブル | 1つ（行または列方向） | 変数値を縦1列または横1行に並べる | 複数の結果を同時出力可能 |
| 2変数データテーブル | 2つ（行と列） | 変数1を縦、変数2を横に並べる交差表 | 1つの結果のみ |
PR
できるExcel関数 Copilot対応 Office 2024/2021/2019＆Microsoft 365版（インプレス）
関数の基本から応用まで手順つきで解説する定番シリーズ。似た関数の使い分けに迷ったときの早見表としても使えます。
1変数データテーブルの設定手順
利率を1.0%～3.0%まで0.5%刻みで変化させたときの月々の返済額を一覧表示する例で解説します。
レイアウトの準備（列方向の場合）
' 元の計算セル
B2: 利率（変数セル）例: 0.02
B3: 期間（月数）例: 360
B4: 借入金額 例: 30000000
B7: 返済額 =PMT(B2/12, B3, -B4) ← 参照数式
' データテーブルのレイアウト
D2: （空白） ← 左上角（1変数列方向では空白でよい）
E2: =B7 ← 参照数式を上端に入れる
D3: 0.010 ← 変数値1
D4: 0.015 ← 変数値2
D5: 0.020 ← 変数値3
D6: 0.025 ← 変数値4
D7: 0.030 ← 変数値51変数データテーブルの正しいレイアウト（表）
| D列（変数値） | E列（月々の返済額） | |
|---|---|---|
| 2行目 | （空白） | =B7（参照数式） |
| 3行目 | 0.010 | ←ここに結果が入る |
| 4行目 | 0.015 | ←ここに結果が入る |
| 5行目 | 0.020 | ←ここに結果が入る |
| 6行目 | 0.025 | ←ここに結果が入る |
| 7行目 | 0.030 | ←ここに結果が入る |
データテーブルの適用操作
- データテーブル範囲（D2:E7）を選択
- 「データ」タブ→「What-If分析」→「データ テーブル」をクリック
- 変数セルが列方向（縦）に並ぶ場合は「列の代入セル」にB2（利率セル）を指定
- 「OK」をクリック→E3:E7に各利率での返済額が自動入力される
複数の結果を同時表示：1変数データテーブルでは、E列以降に参照数式を複数並べることで、利率別の「返済額」「総返済額」「利息総額」など複数項目を同時に一覧化できます。
' D2:G7を選択してデータテーブルを適用する例
D2: （空白）
E2: =PMT(B2/12, B3, -B4) ← 月々の返済額
F2: =PMT(B2/12, B3, -B4)*B3 ← 総返済額
G2: =PMT(B2/12, B3, -B4)*B3-B4 ← 利息総額2変数データテーブルの設定手順
2変数データテーブルは、縦軸と横軸の2つの変数を同時に動かした結果をマトリクス形式で表示します。ただし出力できる数式は1つだけです。
レイアウト：価格×数量の利益マトリクス
' 元のセル
B2: 販売単価（変数1）例: 2000
B3: 販売数量（変数2）例: 500
B4: 変動費単価 例: 800
B5: 固定費 例: 400000
B8: 利益 =(B2-B4)*B3-B5 ← 参照数式
' データテーブルのレイアウト
D2: =B8 ← 左上角に参照数式（2変数テーブルでは必須）
E2: 1500 ← 横軸の変数値（販売単価）
F2: 2000
G2: 2500
H2: 3000
D3: 300 ← 縦軸の変数値（販売数量）
D4: 400
D5: 500
D6: 600
D7: 700上記レイアウトに基づくデータテーブルの計算結果（利益）の例です。
| （利益） | 単価1,500円 | 単価2,000円 | 単価2,500円 | 単価3,000円 |
|---|---|---|---|---|
| 数量300個 | -190,000 | 10,000 | 210,000 | 410,000 |
| 数量400個 | -120,000 | 80,000 | 280,000 | 480,000 |
| 数量500個 | -50,000 | 150,000 | 350,000 | 550,000 |
| 数量600個 | 20,000 | 220,000 | 420,000 | 620,000 |
| 数量700個 | 90,000 | 290,000 | 490,000 | 690,000 |
2変数データテーブルの適用操作
- データテーブル範囲（D2:H7）を選択
- 「データ」タブ→「What-If分析」→「データ テーブル」をクリック
- 「行の代入セル」に横軸（行に並ぶ値）の変数セルを指定（例：B2 = 販売単価）
- 「列の代入セル」に縦軸（列に並ぶ値）の変数セルを指定（例：B3 = 販売数量）
- 「OK」をクリック→マトリクス全体に結果が一括入力される
「行の代入セル」「列の代入セル」の覚え方：横に並ぶ値は「行（の変数）→行の代入セル」、縦に並ぶ値は「列（の変数）→列の代入セル」と対応します。混乱しやすいポイントなので「横軸=行の代入」と覚えましょう。
データテーブルの実務活用パターン
パターン1：ローン比較表（利率×期間の2変数テーブル）
住宅ローンや設備投資ローンの検討時に、利率と返済期間を同時に変化させた月々の返済額比較表を作成します。金融機関に持参するシミュレーション表として実務で多用されます。
' 元のセル
B2: 年利（変数1）例: 0.02
B3: 期間月数（変数2）例: 360
B4: 借入金額 例: 30000000
B7: =PMT(B2/12, B3, -B4) ← 参照数式
' 横軸（行）= 利率: 0.01, 0.015, 0.02, 0.025, 0.03
' 縦軸（列）= 期間: 120か月, 180か月, 240か月, 300か月, 360か月
' → 行の代入セル: B2（年利）、列の代入セル: B3（期間月数）パターン2：価格感度分析（価格別・複数指標の1変数テーブル）
新製品の価格帯を検討する際、単価を変えたときの売上・利益・利益率を横並びで比較します。1変数データテーブルで複数結果列を使うパターンの典型例です。
' 参照数式列（E列以降）
E2: =B2*B3 ← 売上合計
F2: =(B2-B4)*B3-B5 ← 利益
G2: =((B2-B4)*B3-B5)/(B2*B3) ← 利益率
' D列に価格候補を縦に列挙: 1500, 2000, 2500, 3000, 3500, 4000
' 列の代入セル: B2（単価）でデータテーブルを適用
' → 価格6段階×3指標の感度分析表が1操作で完成パターン3：投資回収期間シミュレーション
設備投資額と年間コスト削減額を変化させた投資回収期間（ペイバック期間）の感度分析に2変数テーブルを使います。経営会議向けの提案資料作成に活用できます。
' 横軸: 年間コスト削減額（300万, 400万, 500万, 600万円）
' 縦軸: 初期投資額（1000万, 1500万, 2000万, 2500万円）
' 左上角の参照数式: =B4/B5 （初期投資÷年間削減額 = 回収年数）
' 行の代入セル: B5（年間削減額）、列の代入セル: B4（初期投資額）PR
今すぐ使えるかんたんbiz Excel関数＋組み合わせ 効率UPスキル大全 Copilot対応（技術評論社）
関数単体だけでなく組み合わせ活用まで実務目線でまとめた1冊。業務でそのまま使えるパターン集として手元に置きやすい内容です。
ゴールシークとデータテーブルの使い分け
| やりたいこと | 使う機能 | 理由 |
|---|---|---|
| 目標値を達成する変数を1つ求めたい | ゴールシーク | 「逆算」が目的。変数は1つのみ対応 |
| 変数を少しずつ変えた結果を一覧で比較したい | データテーブル（1変数） | 複数パターンを一括で計算・表示できる |
| 2つの変数の組み合わせ結果をマトリクスで見たい | データテーブル（2変数） | 縦横の交差テーブルで全パターンを視覚化 |
| 「もし○○だったら」を複数シナリオで保存・比較したい | シナリオ管理 | 変数値のセットを名前付きで保存して切り替えられる |
判断フロー：① 「目標値が決まっていて変数を求めたい」→ゴールシーク。② 「変数の候補が複数あって結果の一覧が欲しい」→データテーブル。③ 変数が2つでマトリクスを作りたい→2変数データテーブル。④ シナリオを保存して後で見返したい・レポートを作りたい→シナリオ管理。
よくある設定ミスとエラー対処法
| 症状 | 原因 | 対処法 |
|---|---|---|
| ゴールシークが「解が見つかりません」で終了する | 目標セルと変数セルが参照でつながっていない | 目標セルの数式が変数セルを直接・間接に参照しているか確認する |
| ゴールシークで近似値になる・収束しない | 反復計算の最大誤差・最大反復回数の設定が不足 | 「ファイル」→「オプション」→「数式」→反復計算の設定を増やす |
| データテーブルの結果が空のままになる | 2変数テーブルの左上角に参照数式を入れていない | 交差セル（左上角）に必ず参照数式（=B8など）を入れる |
| 「行の代入セル」と「列の代入セル」を逆に設定した | 横軸変数を列の代入セルに指定している | 横軸（行に並ぶ値）→行の代入セル、縦軸（列に並ぶ値）→列の代入セル |
| データテーブルの計算式を個別に編集できない | 配列数式（{=TABLE(…)}）として書き込まれるため | データテーブル全体を選択してDeleteキーで削除してから再設定する |
| ブックを開くたびに再計算されて動作が遅い | 大量のデータテーブルは開時に全再計算される | 「数式」タブ→「計算方法の設定」→「データテーブル以外の自動」を選択 |
MOS Excel試験での出題ポイント
MOS Excel 365＆2019では「ブックの管理」または「高度なブック機能」のスキル項目として、What-If分析ツールが出題されます。
- ゴールシークの3つの設定項目：「数式入力セル」「目標値」「変化させるセル」を正確に識別して設定できるか問われる。特に「変化させるセルは数値が直接入力されているセルでなければならない」という制約が頻出
- データテーブルの「代入セル」の方向対応：行の代入セル（横軸の変数）と列の代入セル（縦軸の変数）の対応を正確に設定できるか確認される。試験では逆に設定させる引っかけ問題が出やすい
- データテーブルの左上角の参照数式：2変数データテーブルでは左上角に必ず参照数式を置く。試験問題の事前設定で用意されていることが多いが、内容を理解して確認できるようにしておく
- ゴールシーク結果の確定とキャンセル：「OK」で変数セルを書き換えて確定する操作と「キャンセル」で元の値に戻す違いを理解する
- What-If分析メニューの場所：「データ」タブ→「予測」グループ→「What-If分析」という経路を暗記する
MOS試験 What-If分析チェックリスト
| 確認ポイント | 操作内容 | 難易度 |
|---|---|---|
| ゴールシークの起動 | データ→What-If分析→ゴールシークを開ける | ★☆☆ |
| 3項目の正確な設定 | 数式入力セル・目標値・変化させるセルを正しく指定できる | ★★☆ |
| 変数セルの条件理解 | 変化させるセルは数値直接入力セルであることを理解している | ★★☆ |
| 1変数データテーブルの設定 | 変数リストを縦に並べて正しい代入セルを指定できる | ★★☆ |
| 2変数データテーブルの設定 | 左上角に参照数式・行/列の代入セルを正しく設定できる | ★★★ |
| 計算方法の制御 | データテーブル以外の自動計算設定を変更できる | ★★★ |
まとめ：ゴールシーク・データテーブルで意思決定を加速する
本記事のポイントをまとめます。
- ゴールシーク：目標値を決めて変数を逆算する機能。損益分岐点・借入可能額・必要値引き率など「○○にするためにいくら必要か」という問いに一発で答えられる
- 1変数データテーブル：変数を段階的に変化させた結果を一覧化。複数の結果列を同時出力できるため、価格感度分析・ローン比較表に最適
- 2変数データテーブル：2つの変数の全組み合わせ結果をマトリクスで表示。価格×数量の利益表・利率×期間の返済額比較など、2軸分析に使う
- 使い分けの軸：「1変数で逆算したい」→ゴールシーク、「複数パターンを一覧で比べたい」→データテーブル（変数数で1変数か2変数かを選択）
- よくあるミスの防止：ゴールシークの変化させるセルは「直接数値入力セル」、データテーブルは「行/列の代入セルの方向」「左上角の参照数式」を正確に設定する
- MOS試験の核心：3つの設定項目の正確な識別・行と列の代入セルの対応・What-If分析メニューの場所の3点が頻出
ゴールシークとデータテーブルは「関数の入力→手計算のループ」を断ち切る強力なツールです。ローン検討・価格設定・予算計画など、変数を動かしながら最適値を探す業務に積極的に活用し、MOS試験の練習問題で操作手順を繰り返し確認することで実践力が身につきます。
