Excel（エクセル）のデータテーブルとは｜使い方と「代入セルの参照が正しくありません」の原因

Introduction

Ver.　all　2016　2019　2021　2024　365

Excelのデータテーブル機能の使い方です。Excelに用意されているデータ分析の一つで、数式を１つ用意して様々な組み合わせを試すことができます。

 

データテーブル機能

１種類のデータを使用する

では、下の表のように１つのデータを試算したい場合の方法です。

商品の値段は1000円と決まっていて、割引率をどれぐらいにしようか悩んでいるとします。

まず、試算するための表を用意します。

今回は下画像のような表にしました。

試算したい割引率を左側に入力して、右側に割り引いた価格を表示することにします。

準備ができたら、割引価格の最初のセルに必要な数式を入力します。

データテーブル機能を使用する際は、セルの先頭に試算に必要な数式が必要です。

今回の場合は、「B5」に

=B1*(1-B2)

の式を入力します。

計算式の内容も確認しておいてください。

上の「単価」の「1000」と入力されているセルと、すぐ下の「割引率」のセルを使用しています。

【Enter】キーを押すと、「1000」と表示されます。

割引率が「0」なので、そのまま「単価」が表示されているだけですね。

では、ここから下の割引率の結果を、データテーブル機能を使用して算出します。

試算する表の範囲を、数式を入れたセルを含めて選択します。

［データ］タブ　→　［What-If 分析］　→　［データ テーブル］をクリックします。

表示された画面では、［列の代入セル］に、「B2」を指定して［OK］。

すると、「B5」に作成した計算式の「B2」セルに、割引率の数値が代入されて結果が表示されます。

もし、下画像の行方向に試算したい場合は、［行の代入セル］を使用します。

結果を表示したいセルが

• 下方向の場合は［列の代入セル］
• 右方向の場合は［行の代入セル］

になります。

2種類のデータを使用する

次に2種類のデータを使用してデータテーブルを使ってみます。

下画像のように、販売数量と価格がどの程度だと、売上はどうなるかな？という感じ。

試算結果を表示するために下のような表を作成します。

表ができたら、左上に計算式を入力します。

今回の場合は「販売数量」と「価格」を掛け算する式になります。

=B2*B3

【Enter】キーを押すと「0」が表示されます。

意味が無さそうな作業ですが、この式が右下の空欄全体に適用されていきます。

今数式を入力したセルを含んで、計算結果を表示したい表全体を選択します。

［データ］タブ　→　［What-If 分析］　→　［データ テーブル］をクリックします。

表示された画面では、［行の代入セル］に「B2」、［列の代入セル］に「B3」を指定して［OK］。

これで、全ての場所の計算結果が表示されます。

「代入セルの参照が正しくありません」の原因

データテーブルを使用していると、「代入セルが正しくありません」というエラーメッセージが表示されることがあります。

今回の例を参考にすると、「B2」を代入セルとして指定しています。

この代入セルを、最初に選択した範囲内のセルを指定したり、

他のワークシートを指定すると表示されます。

代入セルは、同じワークシート内の離れた場所を指定するようにしましょう。

まとめ

Excelにはデータ分析機能としてゴールシークやシナリオ機能もあります。

それぞれ使用する状況によって使い分けが必要です。

決められた複数の条件で試算をしたい時に活用したいのが今回のデータテーブル機能です。

ただ、気付いた方もいらっしゃるのでは。

絶対参照や相対参照を理解していれば、普通に数式で同じことを実現できてしまいます。

ということで、あまり出番は少ない機能かもしれません。