らくらくIT活用術
PC活用講座 - Excel
「Excelの便利機能活用術」
単価を変えた売上結果をシミュレーションしたい！ 一発で反映する「データテーブル」機能
ITライター 立山秀利
シミュレーションにおいて、試算結果の一覧表を作成したい場合、What-If分析の機能の1つである「データテーブル」を利用するのが効率的だ。
「データテーブル」機能で試算表を作成
例えば図1のように、製品の単価と数量から売上を試算したいとする。試算の条件として単価はB1セル、数量はB2セルにそれぞれ値を入力し、B3セルに売上を求めるための数式「=B1*B2」を入力している。このような試算が先にあり、後から、数量を何パターンか変えた試算表を作成することになったとする。試算表は今回、A6～B11セルの通りとする。数量はA7～A11セルに5パターン用意し、売上をそれぞれB7～B11セルに求めたいとする。
図1
B7～B11に試算の数式をそれぞれ入力してもよいが、「データテーブル」機能を使うと、計算表をより素早く作成できる。データテーブルは指定した数式に、あらかじめ用意しておいた複数のデータを次々に代入していき、その結果の一覧表を作成する機能だ。
それでは、作成手順を解説しよう。まずは試算表の売上の先頭であるB7セルに、試算の数式を設定しておく。試算の数式「=B1*B2」を直接入力してもよいが、B3セルにすでに数式「=B1*B2」が入力されているので、B7セルには「=B3」と入力し、B3セルを参照するだけでよい。実際に入力すると、図2のように試算結果が表示される。
次に試算表のデータの範囲を選択する。試算に用いるデータが入ったA7～A11セルと、試算結果を求めるB7～B11セルも含め、A7～B11セルを選択する必要がある。この状態で、［データ］タブの［What-If分析］→［データテーブル］をクリックする。
図2
（画像をクリックすると拡大表示します）
すると、「データテーブル」ダイアログボックスが表示される。「列の代入セル」には、数式「=B1*B2」において、試算の際に変化させるセルを指定する。今回は数量を変化させるので、B2セルを「列の代入セル」に指定する。指定したら［OK］をクリックする（図3）。
図3
すると、B8～B11セルに試算結果が求められ、試算表が一発で作成された。
図4
試算の数式の変更は1カ所だけでOK
B7～B11に試算結果を求めるだけなら、B7セルに数式「=$B$1*A7」を入力し、B8～B11セルにオートフィルなどでコピーしてもよいので、データテーブルを使うメリットはあまり大きくない。データテーブルを使うメリットが大きいのは、数式を変更した場合の反映だ。たとえば、「セールなどにおいて1割引で販売した際の売上を知りたい」となった場合、単価と数量と0.9を掛けた数式に変更する必要がある。
もし、データテーブルを使わなければ、B7セルの数式を「=A7*B1*0.9」に変更し、B8～B11セルに再びオートフィルなどでコピーしなければならず手間がかかる。
一方、データテーブルの場合、元の数式であるB3セルを「=B1*B2*0.9」に変更すればよい（図5）。
図5
そうすると、図6のようにB7～B11セルすべてに自動で反映される。このように数式を変更するセルは1つだけで済むので効率的だ。
図6
2つのデータを変化させる試算表も一発で変換
先ほどの例では、試算で変化させるデータは「数量」の1つだけであった。今度は「単価」も4パターンで変化させて試算したいとする。その場合もデータテーブルを利用するとよい。
この試算表は図7のA6～E12セルとする。列見出しはなくして、A7～A11セルに数量の5パターンのデータ、B6～E4セルに単価の4パターンのデータを入力しておくとする。
試算表を作成するには、まずは数量と単価のデータのセルの交点となるA6セルに、試算の数式を設定する。先ほどの例と同じく、「=B3」と入力してもよい。そして、表全体のA6～E11セルを選択し、［データ］タブの［What-If分析］→［データテーブル］をクリックする。
図7
（画像をクリックすると拡大表示します）
「データテーブル」ダイアログボックスが表示される。「行の代入セル」には、数式「=B1*B2」において単価に該当するB1セルを指定する。「列の代入セル」には、数量に該当するB2セルを指定し、［OK］をクリックする（図8）。
図8
これで、B7～E11セルに、それぞれの単価と数量から試算した売上が求められる（図9）。
図9
（画像をクリックすると拡大表示します）
このようにデータテーブル機能を利用すれば、試算表を素早く作成できるだけでなく、数式の変更の反映も容易なので、シミュレーションをより効率的に行えるようになる。
その他の【Excel】を見る
立山秀利（たてやま・ひでとし）
カーナビのソフトウェア開発、Webプロデュース業務を経て、現在は、システムやネットワーク、Microsoft Officeを中心に執筆中。 主な著書に『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』などがある。
（監修：日経BPコンサルティング）