定義された名前を使用して Excel でグラフ範囲を自動的に更新する方法

概要

Microsoft Excel の既存のグラフ範囲に新しい情報を追加するときに自動的に更新されるグラフを設定するには、データの追加または削除時に動的に変更される定義済みの名前を作成します。

詳細情報

このセクションには、定義された名前を使用してグラフ範囲を自動的に更新するための 2 つの方法が含まれています。

方法 1: 定義された名前で OFFSET を使用する

この方法を使用するには、実行している Excel のバージョンに応じて、次の手順に従います。

Microsoft Office Excel 2007 以降のバージョン

  1. 新しいワークシートで、次のデータを入力します。

    A1: Month B1: Sales
    A2: Jan B2: 10
    A3: Feb B3: 20
    A4: Mar B4: 30
    
  2. [数式] タブ [ 定義名 ] グループで [ 名前の定義 ] をクリックします。

  3. [ 名前 ] ボックスに「Date」と入力します。

  4. [参照] ボックス 「=OFFSET($A$2,0,0,COUNTA($A:$A)-1)」と入力し、[OK] をクリック します

  5. [数式] タブ [ 定義名 ] グループで [ 名前の定義 ] をクリックします。

  6. [ 名前 ] ボックスに「Sales」と入力します。

  7. [参照] ボックス 「=OFFSET($B$2,0,0,COUNTA($B:$B)-1)」と入力し、[OK] をクリック します

  8. セル B2 をクリアし、「=RAND()*0+10」と入力します

    注:

    この数式では、volatile RAND 関数を使用します。 列 B に新しいデータを入力すると、定義された名前 "Sales" で使用される OFFSET 数式が自動的に更新されます。この数式で使用される値 10 は、セル B2 の元の値です。

  9. セル A1:B4 を選択します。

  10. [ 挿入 ] タブでグラフをクリックし、グラフの種類をクリックします。

  11. [デザイン] タブをクリックし、[データ] グループの [データの選択] をクリックします。

  12. [ 凡例エントリ (系列)] で、[ 編集] をクリックします。

  13. [ 系列の値 ] ボックスに 「=Sheet1!Sales、 [OK] の順にクリック します

  14. [ 水平 (カテゴリ)] 軸ラベルで、[ 編集] をクリックします。

  15. [ 軸ラベル範囲 ] ボックスに 「=Sheet1![日付] をクリックし、[ OK] をクリックします

Microsoft Office Excel 2003 以前のバージョン

  1. 新しいワークシートで、次のデータを入力します。

    A1: Month B1: Sales
    A2: Jan B2: 10
    A3: Feb B3: 20
    A4: Mar B4: 30
    
  2. [挿入] メニューの [名前] をポイントし、[定義] をクリックします。

  3. [ ブック内の名前] ボックスに 「Date」と入力します。

  4. [参照] ボックス 「=OFFSET($A$2,0,0,COUNTA($A:$A)-1)」と入力します。

  5. [追加] をクリックします。

  6. [ ブック内の名前] ボックスに 「Sales」と入力します。

  7. [参照] ボックスに「=OFFSET($B$2,0,0,COUNT($B$2:$B$200)-1)」と入力します。

  8. [追加] をクリックし、[OK] をクリックします。

  9. セル B2 をクリアし、「=RAND()*0+10」と入力します

    注:

    この数式では、volatile RAND 関数を使用します。 列 B に新しいデータを入力すると、定義された名前 "Sales" で使用される OFFSET 数式が自動的に更新されます。この数式で使用される値 10 は、セル B2 の元の値です。

  10. [$A$1:$B$4] を選択します。

  11. グラフを作成し、定義された名前をグラフに追加します。 これを行うには、実行している Excel のバージョンに応じて、次の手順に従います。

Microsoft Excel 97 から Excel 2003

  1. [挿入] メニューの [グラフ] をクリックして、グラフ ウィザードを開始します。

  2. グラフの種類をクリックし、[次へ] をクリックします。

  3. [系列] タブをクリックします。[シリーズ] の一覧で [Sales] をクリックします。

  4. [ カテゴリ (X) 軸ラベル ] ボックスで、セル参照を定義された名前 Date に置き換えます。

    たとえば、数式は次のようになります。

    =Sheet1!日付

  5. [値] ボックスで、セル参照を定義された名前 Sales に置き換えます。

    たとえば、数式は次のようになります。

    =Sheet1!販売

  6. [次へ] をクリックします。

  7. グラフ ウィザードの手順 3 で必要な変更を行い、[次へ] をクリックします。

  8. グラフの場所を指定し、[完了] をクリックします。

Microsoft Excel 5.0 または Microsoft Excel 7.0

  1. [挿入] メニューの [グラフ] をポイントし、[新しいシートとして] をクリックしてグラフ ウィザードを開始します。

  2. [次へ] をクリックします。

  3. グラフの種類をクリックし、[次へ] をクリックします。

  4. グラフのサブタイプをクリックし、[次へ] をクリックします。

  5. [ データ系列の列] をクリックし、[ カテゴリ (x) 軸ラベルに最初の 1 列を使用する] に「1」と入力します。 [次へ] をクリックします。

  6. 表示するタイトルをクリックし、[完了] をクリックします。

    グラフが新しいグラフに表示されます。

  7. データ系列を選択します。 [書式] メニューの [データ系列の選択] をクリックします。

  8. [X 値] タブをクリックします。[X 値] ボックスで、セル参照を定義された名前 Date に置き換えます。

    たとえば、数式は次のようになります。

    =Sheet1!日付

  9. [名前と値] タブをクリックします。[Y 値] ボックスで、セル参照を定義された名前 Sales に置き換えます。

    たとえば、数式は次のようになります。

    =Sheet1!販売

  10. [OK] をクリックします。

方法 2: Excel 2003 および以前のバージョンの Excel でデータベース、OFFSET、および定義された名前を使用する

また、データをデータベースとして定義し、グラフ データ系列ごとに定義された名前を作成することもできます。 このメソッドを使用するには、次の手順に従います。

  1. 新しいワークシートで、次のデータを入力します。

    A1: Month B1: Sales
    A2: Jan B2: 10
    A3: Feb B3: 20
    A4: Mar B4: 30
    
  2. 範囲 A1:B4 を選択し、[データ] メニューの [データベースの設定] をクリックします。

  3. [数式] メニューの [名前の定義] をクリックします。

  4. [名前] ボックスに「Date」と入力します。

  5. [参照] ボックス 「=OFFSET(Database,1,0,ROWS(Database)-1,1)」と入力します。

  6. [追加] をクリックします。

  7. [名前] ボックスに「Sales」と入力します。

  8. [参照] ボックス 「=OFFSET(Database,1,1,ROWS(Database)-1,1)」と入力します。

  9. [追加] をクリックし、[OK] をクリックします。

  10. [$A$1:$B$4] を選択します

  11. 方法 1 の手順 10 を繰り返してグラフを作成し、定義済みの名前をグラフに追加します。

グラフに表示するデータがデータベースとして定義されている限り、新しいデータを追加するとグラフが自動的に更新されます。

注:

隣接するセル ブロック内のすべての値を 1 列にプロットする系列グラフを作成していて、セルのブロックが最初の行で始まる場合は、定義された名前の [参照先] ボックスで次のいずれかの数式を使用できます。

=INDIRECT("Sheet1!$a$1:$a"&COUNT(Sheet1!$A:$A))

=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A),0)

最初の行以外の行のセルで始まるセルブロックを使用するには、最初のセル参照でその行を参照し、開始行番号をカウントに追加して最後の行番号を見つけます。 隣接する数値以外のエントリ (ラベルなど) をプロットするには、COUNT ではなく COUNTA を使用します。