今回は、Excelのデータテーブルを使って、条件比較を行う方法を紹介します。
データテーブルとは
Excelのデータテーブルは、数式の条件を変えたときに結果がどう変わるかを確認するための機能です。価格、数量、利率、単価、工数など、1つまたは2つの条件を変えて結果を比べたいときに使えます。
たとえば、単価を変えた場合の売上、数量を変えた場合の利益、工数を変えた場合の費用などを並べて確認できます。手作業で条件を入れ替えて計算するより、表として比較できるため、検討用の資料を作りやすくなります。
データテーブルは、数式の結果を条件別に並べて確認するための機能です。結果の良し悪しを自動で判断するものではないため、前提条件を理解して使うことが大切です。
使う前に計算モデルを作る
データテーブルを使うには、先に基準となる計算式を作ります。条件を入力するセル、計算結果を出すセルを決めておく必要があります。
準備で確認したい点は次の通りです。
- 変更したい条件のセルを決める
- 結果を表示するセルを決める
- 数式が条件セルを参照しているか確認する
- 単位や表示形式をそろえる
- 前提条件を近くにメモする
計算モデルが分かりにくいと、データテーブルの結果も確認しにくくなります。入力セルには色を付ける、結果セルには見出しを付けるなど、どの値を変えているのか分かるようにしておきます。
1変数のデータテーブル
1変数のデータテーブルは、1つの条件を変えて結果を確認する方法です。たとえば、販売数量を変えたときの売上や、単価を変えたときの利益を見る場合に使います。
基本的な流れは次の通りです。
- 基準となる計算式を作る
- 比較したい条件値を縦または横に並べる
- 結果を表示したい位置に計算結果セルへの参照を置く
- 範囲を選択してデータテーブルを設定する
- 行または列の代入セルを指定する
条件値を縦に並べる場合は、列の代入セルを指定します。横に並べる場合は、行の代入セルを指定します。この指定を間違えると、期待した結果にならないため注意します。
2変数のデータテーブル
2変数のデータテーブルは、2つの条件を組み合わせて結果を確認する方法です。単価と数量、利率と期間、工数と単価など、条件の組み合わせによる結果を表で見たいときに使います。
表の左上に計算結果セルへの参照を置き、上方向に1つ目の条件、左方向に2つ目の条件を並べます。その範囲を選んで、行の代入セルと列の代入セルを指定します。
2変数のデータテーブルは、条件の組み合わせを確認しやすい反面、表が大きくなりやすいです。条件値を細かくしすぎると読みにくくなるため、検討に必要な範囲に絞ります。
条件値の並べ方
データテーブルでは、条件値の並べ方が見やすさに影響します。小さい値から大きい値へ並べる、現実的な範囲にする、単位をそろえるなど、読み手が比較しやすい形にします。
条件値を決めるときのポイントは次の通りです。
- 現在値を含める
- 検討したい範囲に絞る
- 増減の幅をそろえる
- 単位を見出しに書く
- 極端な条件を入れすぎない
現在の条件を表の中に入れておくと、比較の基準が分かりやすくなります。条件値だけが並んでいると、どれが現実的な案なのか判断しにくくなることがあります。
結果を見やすくする
データテーブルで作った結果は、数字が並ぶだけでは読み取りにくい場合があります。表示形式、罫線、色分けを使って、比較しやすく整えます。
たとえば、金額なら通貨や桁区切りを設定し、割合ならパーセント表示にします。重要な条件や基準値には薄い色を付けると、表の中で見つけやすくなります。
条件付き書式を使って、結果の高低を色で示す方法もあります。ただし、色が多すぎると読みづらくなるため、目的に合う範囲で使います。
前提条件を残す
データテーブルは、元の計算式や入力条件に依存します。後から見た人が意味を理解できるように、前提条件を表の近くに残しておくことが大切です。
残しておきたい情報は次の通りです。
- 何を変化させているか
- 結果セルが何を表しているか
- 固定している条件は何か
- 対象期間や単位
- 作成日や確認者
前提がない表は、数字だけが独り歩きしやすくなります。検討用の資料では、条件と結果をセットで示します。
再計算と編集時の注意
データテーブルは計算結果をまとめて表示するため、ファイルによっては再計算に時間がかかることがあります。条件数が多い場合や、元の数式が複雑な場合は、表を必要な範囲に絞ります。
また、データテーブルの結果範囲は通常のセルのように一部だけ編集できないことがあります。結果を手入力で直すのではなく、元の計算式や条件値を修正して更新します。
資料用に結果を固定したい場合は、別シートへ値として貼り付ける方法もあります。元の計算用シートと資料用シートを分けると管理しやすくなります。
まとめ
Excelのデータテーブルは、条件を変えたときの計算結果を並べて比較できる機能です。1つの条件を変える1変数、2つの条件を組み合わせる2変数を使い分けます。
使う前には、条件セルと結果セルを決め、基準となる計算モデルを作ります。条件値は現実的な範囲に絞り、単位や前提条件を明記します。
データテーブルを使うと、条件比較を表として確認できます。計画や見積もり、費用検討などで、数値の変化を整理したいときに役立ちます。