1. Home » 
2. エクセル2010基本講座：目次 » 
3. 入力規則 » 
4. 入力規則のリスト範囲を自動で増減する

入力規則のリスト範囲を自動で増減する

リスト範囲を自動でのばす　　　　　topへ

• このページでは入力規則のリスト範囲や名前を定義したセル範囲を可変にする方法を書いています。
  おすすめは最後の「テーブル機能を利用する」です。
• また、セル範囲を定義した名前は数式にも使えますので、参照範囲が変化するようなケースでは重宝するものと思われます。
  リストにデータを追加/削除した場合に名前を定義した範囲を変更する必要があります。
  • 名前を定義した範囲内のセルを挿入してから、データを入力すると、名前を定義した範囲も自動的に拡張されます。
    (名前定義した範囲の下のセルにデータを入力した場合は、名前を定義した範囲外になります。)
• なお、この項の方法と「リストの切り替え」との併用はできませんので、ご注意ください。
  • VBAを利用すれば可能です。Excel(エクセル) VBA入門：入力規則を設定するをご覧ください

数式を使ってリスト範囲を伸ばす　　　topへ

• OFFSET関数とCOUNTA関数を組み合わせてセル範囲を指定します。
• Ｅｘｃｅｌ2007以降では数式で別シートの参照が可能になりました。
  Ｅｘｃｅｌ2003以前のバージョンではこの方法で別シートのリストは利用できません。名前の定義を利用してください。
• 【操作例】現在、リストはSheet3のA1:A3に入力されています。必要に応じてデータをA4セル以降に順次追加したい・・・と思います。
  • ここでは、入力規則はSheet1のA2セルへ設定します。元になるリストはSheet3のA1セル以降に入力されることを想定します。
    別シートへリストを作成するのが実用的であるとの考えです。
  1. Sheet3のA1セル以降にリストの元データを入力しました。
     
  2. Sheet1のA2セルを選択し、[データ]タブの[データツール]グループの[データの入力規則]を実行します。
     • [設定]タブの「入力値の種類」で「リスト」を選択します。
     • 「元の値」の欄をクリックし、『=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A1:$A100),1)』と入力します。
     
  3. Sheet3のA1:A3に入力されたものがリストに表示されました。
     
  4. Sheet3のA4:A5にデータを追加してみました。
     
  5. Sheet1のA2セルに設定した入力規則のリストは自動でのびました。
     

名前の定義を利用する　　　　topへ

• 名前の定義を利用します。
• 【操作例】Sheet3のA1から下方向へリストを入力するものとします。

1. Sheet3のA1セル以降にリストの元データを入力しました。
   
2. [数式]タブの[定義された名前]グループの[名前の定義]を実行します。
   
3. 新しい名前を作成します。
   • 名前に「商品名」と入力しました。
   • 参照範囲に「=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A1:$A100),1)」と入力しました。
   
4. Sheet1のA2セルを選択して、[データ]タブの[データツール]グループにある[データの入力規則]を実行します。
   • [設定]タブの「入力値の種類」で「リスト」を選択します。
   • 「元の値」の欄をクリックし、『=商品名』と入力します。
   
5. Sheet3のA1:A3に入力されたものがリストに表示されました。
   
6. Sheet3のA4:A5にデータを追加してみました。
   
7. Sheet1のA2セルに設定した入力規則のリストは自動でのびました。
   

テーブル機能を利用する　　　　topへ

• テーブルを利用すると、テーブルのデータ範囲が自動で拡張されますので、データの増減に対処するのが容易になります。
  ただ、列のデータ範囲の指定でテーブル名を利用することができないようですので、名前の定義を利用します。

1. Sheet2のA1セルに列見出しを入力します。ここでは　「野菜」と入力しました。
   A2セル〜A4セルにリストに表示したい品名を入力しました。
   
2. A1を選択した状態で、[Ctrl]+[T]キーを押して、テーブルを作成します。
   「先頭行をテーブルの見出しとして使用する」にチェックを入れます。
   [OK]ボタンをクリックします。
   
3. テーブルが作成され、テーブルの書式(スタイル)が設定されました。
   
4. A1:A4セルを選択し、[数式]タブの定義された名前 グループの[選択範囲から作成]を実行します。
   「選択範囲から名前を作成」で「上端行」にチェックを入れて、[OK]ボタンをクリックします。
   データ範囲に「野菜」と名前を定義します。ここがこの方法のポイントになります。
   
5. Sheet1の　B3セルを選択し、[データ]タブの[データの入力規則]→[データの入力規則]を実行します。
   
6. 入力値の種類で「リスト」を選択します。
   元の値に　「=野菜」　と定義した名前を入力します。
   
7. ドロップダウンリストから選択して入力できるようになりました。
   
8. テーブルに品名を追加します。
   
9. 追加した品名をドロップダウンリストから選択できるようになっています。
   入力規則のリストが自動でのびました。
   

テーブルの列を利用する　　　　topへ

• 上記のテーブルを利用する方法では名前の定義を利用しました。
  テーブルの名前を指定することができなかったための苦肉の策でしたが、リストの切り替えにINDIRCT関数を使う方法がありました。
• テーブルの名前を指定するのにINDIRCT関数を利用することで自動でリスト範囲を伸ばすことが可能になります。

1. データ範囲をテーブルに変換しました。テーブル名は　テーブル1となっています。
   
2. リストを設定するセルを選択して、[データ]タブの[データの入力規則]を実行します。
   
3. データの入力規則で、元の値に　=INDIRECT("テーブル1")　と入力します。テーブル名は "" (ダブルクォーテーション)でくくります。
   
4. リストにテーブルの内容が表示されました。
   
5. テーブルにテータを追加したら、入力規則のリストも自動で伸びました。
   

• テーブルの中の1つの列を指定することもできます。
  下図のように複数列あるテーブルがあります。
  
  ↓
  数式で　=INDIRECT("テーブル1[野菜]")　と列見出しを使って指定します。
  
  ↓
  入力規則のリストでテーブルの列を指定することができました。
  

• 関連事項
  • 入力規則の使い方
  • 入力規則の使い方(ドロップダウンリスト)
  • リストを切り替える（連動させる）
  • 入力規則の使い方(ユーザー設定)