1. Home » 
2. エクセル Q&A(Tips)：目次 » 
3. グラフの参照範囲を自動的に変更したい(SERIES関数)

グラフの参照範囲を自動的に変更したい(SERIES関数)：エクセル Q&A(Tips)

SERIES関数　　　　topへ

• 下図のような折れ線グラフを作成しています。
  系列を選択すると、数式バーに SERIES関数を使った数式が表示されます。
  
• SERIES関数の構文は　=SERIES(name ,category_labels ,values ,order) となっています。
  

  引数	必須/オプション	指定内容
  name	オプション	凡例に表示される名前
  category_labels	オプション	項目軸に表示されるラベル (省略した場合は、連続した整数がラベルとして使用されます
  values	必須	プロットされる値
  order	必須	系列のプロット順

参照範囲を関数で表す　　　　topへ

• SERIES関数の中のcategory_labels　と　values　を可変可能な形にすればよいので
  OFFSET関数[構文:=OFFSET(基準, 行数, 列数, [高さ], [幅])]を利用して、可変にすることができそうです。
• この例ではデータ数をカウントしてOFFSET関数の「高さ」に入れればよいと考えました。
  • 項目範囲　=OFFSET(Sheet1!$B$2,1,0,COUNT(Sheet1!$C:$C),1)
    データA　=OFFSET(Sheet1!$C$2,1,0,COUNT(Sheet1!$C:$C),1)
    データB　=OFFSET(Sheet1!$D$2,1,0,COUNT(Sheet1!$C:$C),1)
  • これらの数式に名前を付けます。
    1. [数式]タブの[名前の管理]を実行します。
       
    2. [新規作成]ボタンをクリックします。
       
    3. 名前に「項目範囲」と入力しました、範囲で「Sheet1」を選択しました。
       参照範囲に数式　=OFFSET(Sheet1!$B$2,1,0,COUNT(Sheet1!$C:$C),1)　を入力しました。
       
       • 同様にプロット範囲にデータA、データBと名前を付けた数式を登録しました。
         
         
• グラフの数式をこれらの登録した名前を使ったものに編集します。
  • 系列Aを選択して、数式を　=SERIES(Sheet1!$C$2,Sheet1!項目範囲,Sheet1!データA,1)　としました。
    
  • 系列Bを選択して、数式を　=SERIES(Sheet1!$D$2,Sheet1!項目範囲,Sheet1!データB,2)　としました。
    
• グラフの参照範囲が可変になったかを確認してみます。
  • データを削除してみました。
    • 
  • データを追加してみました。
    • 

データのある部分をグラフにする　　　　topへ

• データリストがあらかじめ作成されているケースです。
  さらに、途中のデータが歯抜けになっているケースではどうすればよいのでしょうか?
• 下図のように、途中のデータがところどころ抜けています。
  
• データ数をカウントする方法を変更します。
  データの最下行を　MAX(MATCH(10^99,$C:$C),MATCH(10^99,$D:$D))　で求めて、C,D列の大きい方を使うことにしました。
  • 項目範囲2：=OFFSET($B$2,1,0,MAX(MATCH(10^99,$C:$C),MATCH(10^99,$D:$D))-2,1)
    データA2：=OFFSET($C$2,1,0,MAX(MATCH(10^99,$C:$C),MATCH(10^99,$D:$D))-2,1)
    データB2：=OFFSET($D$2,1,0,MAX(MATCH(10^99,$C:$C),MATCH(10^99,$D:$D))-2,1)
    • データの開始が3行目なので、データ(数値データ)の最下行から　-2　として調整しています。
  • それぞれの名前にそれぞれの数式を登録します。(手順は上記と同様ですので省略します)
  • 下図の例は「Sheet3」を使っています。
    • =SERIES(Sheet3!$C$2,Sheet3!項目範囲2,Sheet3!データA2,1)
    • =SERIES(Sheet3!$D$2,Sheet3!項目範囲2,Sheet3!データB2,2)
    
• データを追加してみます。
  • 期待したようにプロット範囲が変化しました。