MicrosoftExcelのデータテーブルで結果を比較する方法

数式のさまざまな値を試して、それぞれに対してどのような出力が得られるかを確認したいと思ったことはありませんか? Excelのデータテーブルは、可能性を一瞥するための優れたツールです。

ExcelでのWhat-If分析:データテーブル

ExcelのWhat-If分析ツールは、値の変化に関する出力データの変化を予測できる優れた関数セットです。 Excelには、シナリオマネージャー、ゴールシーク、データテーブルの3つのWhat-If分析ツールがあります。

データテーブルツールを使用すると、さまざまな入力が数式の結果にどのように影響するかを確認できます。データテーブルを使用して、数式の見通しを取得し、さまざまな入力から取得できる出力を確認できます。

データテーブルのしくみ

Excelのデータテーブルは一連の入力を受け取り、それらを数式に配置し、最後にすべての入力の出力のテーブルを作成します。

データテーブルをExcelで利用するには、最初に数式を用意する必要があります。次に、別のセルの数式を参照して、そのセルでデータテーブルを操作できます。最後に、行入力セル列入力セルの2つのデータセットをデータテーブルに入力できます

次に、データテーブルは、隣接する行の値を[行]入力セルの入力として受け取り、隣接する列の値を[列]入力セルの入力として受け取り、式の出力のテーブルを作成します。

関連: Excelでピボットテーブルを作成する方法

例1:2変数データテーブル

この例では、価格の異なる6台のおもちゃの車があり、それらを所定の数量で販売することでどれだけの収益が得られるかを知りたいとします。

これを行うには、あるタイプのおもちゃの車の販売数に価格を掛け、最後に税率を差し引いて収益を得る必要があります。

したがって、全体として、このデータテーブルには、数量と価格の2つの変数入力があります。まず、式を作成しましょう。

  1. セルA1B1C1 、およびD1に、それぞれ価格数量税金、および収益を入力します。
  2. セルA1D1を選択します。
  3. [ホーム]タブの[数値]セクションで、[ $ ]記号をクリックして、これらのセルの数値形式を[アカウンティング]に変更します(これは、これらのセルにアカウンティング値が格納されるためです)。
  4. セルC2を選択します。
  5. [ホーム]タブの[数値]セクションで、 記号をクリックして、このセルの[数値の書式設定]を[パーセンテージ]に変更します。
  6. [収益]の下のセルD2を選択し、数式バーに次の数式を入力して、Enterキーを押します。
     =(A2*B2)-(A2*B2*C2)

    この式は、販売されたユニットの価格(A2)に数量(B2)を掛けてから、税額(A2 * B2 * C2)を減算します。

先に進んでセルにサンプル値を与え、Excelが売上から得られた収益を計算するのを観察できます。

関連: Excelで列と行を非表示または再表示する方法

Excelのさまざまな入力データテーブル

さまざまな入力のデータテーブルを作成するには、2つの入力を含むベアテーブルが必要です。

  1. セルG2を選択し、数式バーに以下の数式を入力します。
     =D2

    これにより、セルG2が前に作成した数式と等しくなります。

  2. G2の下のセル(列G)に、販売されたピースの可能な数量を入力します。この例では、番号は5、10、15、20、25、および30です。
  3. G2 (行2)の横のセルに、各ピースの価格を入力します。この例では、価格は10、20、30、40、50、および60です。
  4. 価格を挿入したセルと下のセルを選択すると、可能な収益が表示され、数値の書式設定が会計に変更されます。

最後に、行と列を設定したので、このテーブルをデータテーブルに変換します。

  1. G1をクリックし、 M7までドラッグして、テーブルを選択します。
  2. [データ]タブに移動し、 [予測]セクションで[仮定の分析]をクリックします。 3つのアイテムのリストが表示されます。
  3. リストから、[データテーブル]を選択します。これにより、[データテーブル]ダイアログが表示されます。
  4. [行]入力セルに、 A2と入力します。表の行には価格が含まれており、元の数式に入力された価格はセルA2です。
  5. [列]入力セルに「 B2」と入力します。表の列には、販売されたピースの数量が含まれています。
  6. 2つの入力を設定したら、[ OK ]をクリックします。 Excelはデータテーブルを生成します。

これで、数式のデータテーブルができました。データテーブルは、潜在的な売上に関する貴重な情報を提供します。

たとえば、右下隅にある6つのセルを見ると、1,000ドル以上の収益を得る方法がわかります。または、20ドルのおもちゃの車を25台販売すると、30ドルのおもちゃの車を15台販売するよりも収益が増えることを比較して知ることができます。

例2:1変数データテーブル

結局のところ、データテーブルはテーブルであるため、入力を行と列にのみ格納できます。これは、データテーブルに3つ以上の変数入力を含めることができないことを意味します。ただし、確かに2つ未満にすることができます。単一の変数入力を持つデータテーブル。

この例では、前の例を念頭に置いてください。ただし、今回は、50ドルのおもちゃの車だけの潜在的な収益の表を取得したいとします。

位置は少し異なりますが、方法は2変数データテーブルと同じです。

関連: MicrosoftExcelで数式をコピーする方法

データテーブルの作成を開始するには、数式を作成する必要があります。この例の式は前の例と同じです。数式の準備ができたら、データを設定します。

  1. セルH1を選択し、数式バーに以下の数式を入力してEnterキーを押します。
     =D2
  2. セルG2以下に番号を入力します。この例では、セルG2からG7に5、10、15、20、25 、および30と入力します。

次に、データテーブルを作成します。

  1. G1をクリックし、 H7にドラッグして、テーブルを選択します。
  2. [データ]タブに移動し、 [予測]セクションで[仮定の分析]をクリックします。
  3. 「仮定分析」リストから、「データ表」を選択します。
  4. [データテーブル]ダイアログで、[]入力セルをクリックし、 B2入力します。
  5. 行入力セルは空のままにします。
  6. 目標は固定価格の収益データテーブルを取得することであるため、データテーブルにフィードする必要があるのは、販売されたおもちゃの車の数だけであり、価格は入力しません。数量はデータテーブルの行に設定され、数式への入力はセルB2です。
  7. [ OK]をクリックします。 Excelは1変数のデータテーブルを作成します。

これで、もちろん税金を差し引いた50ドルのおもちゃの車を販売することでどれだけの収益を生み出すことができるかを把握できます。

テーブルに可能性を設定する

データテーブルを使用すると、さまざまな入力を使用した数式の結果をよく見ることができます。これで、数式を作成する方法がわかりました。

データテーブルは、ExcelのWhat-If分析ツールの1つにすぎません。 ExcelですべてのWhat-Ifの質問に答えたい場合は、さらに学ぶことがあります。