1. Home » 
2. Excel講座の総目次 » 
3. Excel基本講座の目次 » 
4. 入力規則のリスト範囲を自動で増減する(TrimRange,Offset,テーブル)

入力規則のリスト範囲を自動で増減する(TrimRange,Offset,テーブル)：Excel基本講座

更新:2025/7/20;作成:2011/4/12

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

• TrimRange関数がExcel for Microsoft365で使えるようになり、入力規則のリストに参照するセル範囲を数式で設定するのが容易になりました。
  データの増減などによる名前の変更などのメンテナンスが非常に楽になると思いますが、いかがでしょう。
  なお、以前の方法はそのまま残しておきますので比較することもできます。
• このページでは入力規則のリスト範囲や名前を定義したセル範囲を可変にする方法を書いています。
  おすすめは最後の「テーブル機能を利用する」です。
• また、セル範囲を定義した名前は数式にも使えますので、参照範囲が変化するようなケースでは重宝するものと思われます。
  リストにデータを追加/削除した場合に名前を定義した範囲を変更する必要があります。
  • 名前を定義した範囲内のセルを挿入してから、データを入力すると、名前を定義した範囲も自動的に拡張されます。
    (名前定義した範囲の下のセルにデータを入力した場合は、名前を定義した範囲外になります。)

TrimRange関数など数式でリスト範囲を設定する　　　　topへ

• ここでは連動するドロップダウンリストを作成したいと思います。
  TrimRange関数の詳細な使い方は　TRIMRANGE関数とトリム参照を使って空白の行または列を除外する　をご覧ください。
• リストに使用するデータをSheet2に作成しています。
  

  	B	C	D	E	F	G	H	I
  2	九州地方	四国地方	中国地方	近畿地方	中部地方	関東地方	東北地方	北海道地方
  3	福岡県	徳島県	鳥取県	三重県	新潟県	茨城県	青森県	北海道
  4	佐賀県	香川県	島根県	滋賀県	富山県	栃木県	岩手県
  5	長崎県	愛媛県	岡山県	京都府	石川県	群馬県	宮城県
  6	熊本県	高知県	広島県	大阪府	福井県	埼玉県	秋田県
  7	大分県		山口県	兵庫県	山梨県	千葉県	山形県
  8	宮崎県			奈良県	長野県	東京都	福島県
  9	鹿児島県			和歌山県	岐阜県	神奈川県
  10	沖縄県				静岡県
  11					愛知県

• B3セルのドロップダウンリストで選択したものをC3セルのドロップダウンリストに表示します。
  
  
  

1. リストを設定するB3セルを選択して、[データ]タブの[データの入力規則]を実行します。
   
2. 入力値の種類では「リスト」を選択します。
   元の値には =TRIMRANGE(Sheet2!2:2) と入力します。
   
   • 元の値に設定した数式のTrimRange関数の働きは下図のようになります。
     =TRIMRANGE(Sheet2!2:2) は2行目の空白列をトリム(削除)します。
     下図のように、地方を取り出すことができます。
     これがドロップダウンリストに表示されます。
     
3. B3セルのドロップダウンリストができました。
   
4. リストを設定するC3セルを選択して、[データ]タブの[データの入力規則]を実行します。
   
5. 入力値の種類では「リスト」を選択します。
   元の値には　=TRIMRANGE(XLOOKUP(B3,Sheet2!2:2,Sheet2!3:20))　と入力します。
   実際のデータより広い範囲を指定して新たなデータの増加に備えています。
   
   • 元の値に設定した数式のTrimRange関数とXlookup関数の働きは下図のようになります。
     =XLOOKUP(B3,Sheet2!2:2,Sheet2!3:20)　
     は、B3セル「九州地方」と一致する列「J列」のデータの 3:20行 を下図のように取り出すことができます。
     
     • =TRIMRANGE(XLOOKUP(B3,Sheet2!2:2,Sheet2!3:20))
       とすることで、入力されている値だけが取り出せます。
       0が表示されているセルがトリミングされます。
     • ちょっと長くなりますが、↓のような数式でもOKです。ほぼ同じことをやっています。
       Xlookup関数の代わりにIndex関数とXmatch関数を使っています。
       =TRIMRANGE(INDEX(Sheet2!B3:I14,,XMATCH(B3,Sheet2!B2:I2)))
     下図のJ3:J10の値がドロップダウンリストに表示されます。
     
6. B3セルのドロップダウンリストで選択したものをC3セルのドロップダウンリストに表示されました。
   
7. J列に新たなデータを入力しました。
   
8. 数式はそのまま、入力規則もそのままで、新たに入力したデータもドロップダウンリストに表示されます。
   
   
   

テーブル機能とINDIRECT関数を利用する　　　　topへ

• テーブルを利用すると、テーブルのデータ範囲が自動で拡張されますので、データの増減に対処するのが容易になります。
• 入力規則の設定にテーブルの名前だけでは指定することができません。
  テーブルの名前を指定するのにINDIRCT関数を利用することで自動でリスト範囲を伸ばすことが可能になります。

1. データリスト内の1つのセルを選択して、[挿入]タブの[テーブル]を実行します。
   ショートカットキーは　[Ctrl]+[T] です。
   
2. テーブルの作成 ダイアログボックスが表示されます。
   「先頭行をテーブルの見出しとして使用する」にチェックを入れて、[OK]ボタンをクリックします。
   
3. データ範囲をテーブルに変換しました。
   [テーブルデザイン]タブを選択すると、テーブル名は　テーブル1となっているのを確認できます。
   
4. リストを設定するセルを選択して、[データ]タブの[データの入力規則]を実行します。
   
5. データの入力規則で、元の値に　=INDIRECT("テーブル1")　と入力します。テーブル名は "" (ダブルクォーテーション)でくくります。
   
6. リストにテーブルの内容が表示されました。
   
7. テーブルにテータを追加したら、入力規則のリストも自動で伸びました。
   

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

テーブル機能と名前の定義　　　　topへ

• INDIRECT関数を使ってテーブルの列を指定することができますが、ちょっと煩わしい・・・
  という方は、テーブルの列データに名前の定義を利用する方法もあります。

1. データリスト内の1つのセルを選択して、[挿入]タブの[テーブル]を実行します。
   ショートカットキーは　[Ctrl]+[T] です。
   
2. テーブルの作成 ダイアログボックスが表示されます。
   「先頭行をテーブルの見出しとして使用する」にチェックを入れて、[OK]ボタンをクリックします。
   
3. データ範囲をテーブルに変換しました。
   [テーブルデザイン]タブを選択すると、テーブル名は　テーブル1となっているのを確認できます。
   
4. B2:B5セルを選択し、[数式]タブの定義された名前 グループの[選択範囲から作成]を実行します。
   「選択範囲から名前を作成」で「上端行」にチェックを入れて、[OK]ボタンをクリックします。
   データ範囲に「野菜」と名前を定義します。ここがこの方法のポイントになります。
   
   
5. 入力規則を設定するセルを選択し、[データ]タブの[データの入力規則]を実行します。
   
6. 入力値の種類で「リスト」を選択します。
   元の値に　「=野菜」　と定義した名前を入力します。
   • 元の値にカーソルがあるとき、[F3]キーを押すと名前の一覧が表示されます。
     「野菜」を選択して、[OK]をクリックすると、「=野菜」と入力できます。
     
   
7. ドロップダウンリストから選択して入力できるようになりました。
   
8. テーブルに品名を追加します。
   
9. 追加した品名をドロップダウンリストから選択できるようになっています。
   入力規則のリストが自動でのびました。
   

OFFSET関数などを使ってリスト範囲を伸ばす　　　topへ

• OFFSET関数とCOUNTA関数を組み合わせてセル範囲を指定します。
• Excel2007以降では数式で別シートの参照が可能になりました。
  Excel2003以前のバージョンではこの方法で別シートのリストは利用できません。名前の定義を利用してください。

【操作例】現在、リストはSheet3のA1:A3に入力されています。必要に応じてデータをA4セル以降に順次追加したい・・・と思います。

• ここでは、入力規則はSheet1のA2セルへ設定します。元になるリストはSheet3のA1セル以降に入力されることを想定します。
  別シートへリストを作成するのが実用的であるとの考えです。

1. Sheet3のA1セル以降にリストの元データを入力しました。
   
2. Sheet3のC3セルを選択し、[データ]タブの[データツール]グループの[データの入力規則]を実行します。
   • [設定]タブの「入力値の種類」で「リスト」を選択します。
   • 「元の値」の欄をクリックし、『=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A1:$A100),1)』と入力します。
     数式は A1セルからCountA関数で入力されたデータ数のセル範囲　A1:A3 を取り出しています。
     　
   
3. Sheet3のA1:A3に入力されたものがリストに表示されました。
   
4. Sheet3のA4:A5にデータを追加してみました。
   入力規則のリストは自動でのびました。
   

OFFSET関数と名前の定義を利用する　　　　topへ

• Offset関数を使った数式に名前の定義をして利用します。
• 【操作例】Sheet3のA1から下方向へリストを入力するものとします。

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

スポンサードリンク

PageViewCounter

Since2006/2/27