- Home »
- Excel講座の総目次 »
- Excel基本講座の目次 »
- 入力規則のリスト範囲を自動で増減する
更新:2025/4/29;作成:2011/4/12
リスト範囲を自動でのばす topへ
- このページでは入力規則のリスト範囲や名前を定義したセル範囲を可変にする方法を書いています。
おすすめは最後の「テーブル機能を利用する」です。
- また、セル範囲を定義した名前は数式にも使えますので、参照範囲が変化するようなケースでは重宝するものと思われます。
リストにデータを追加/削除した場合に名前を定義した範囲を変更する必要があります。
- 名前を定義した範囲内のセルを挿入してから、データを入力すると、名前を定義した範囲も自動的に拡張されます。
(名前定義した範囲の下のセルにデータを入力した場合は、名前を定義した範囲外になります。)
- テーブルを利用すると、テーブルのデータ範囲が自動で拡張されますので、データの増減に対処するのが容易になります。
- 入力規則の設定にテーブルの名前だけでは指定することができません。
テーブルの名前を指定するのにINDIRCT関数を利用することで自動でリスト範囲を伸ばすことが可能になります。
- データリスト内の1つのセルを選択して、[挿入]タブの[テーブル]を実行します。
ショートカットキーは [Ctrl]+[T] です。
- テーブルの作成 ダイアログボックスが表示されます。
「先頭行をテーブルの見出しとして使用する」にチェックを入れて、[OK]ボタンをクリックします。
- データ範囲をテーブルに変換しました。
[テーブルデザイン]タブを選択すると、テーブル名は テーブル1となっているのを確認できます。
- リストを設定するセルを選択して、[データ]タブの[データの入力規則]を実行します。
- データの入力規則で、元の値に =INDIRECT("テーブル1") と入力します。テーブル名は "" (ダブルクォーテーション)でくくります。
- リストにテーブルの内容が表示されました。
- テーブルにテータを追加したら、入力規則のリストも自動で伸びました。
- テーブルの中の1つの列を指定することもできます。
下図のように複数列あるテーブルがあります。
↓
数式で =INDIRECT("テーブル1[野菜]") と列見出しを使って指定します。構造化参照の書き方と同じです。
入力規則のリストでテーブルの列を指定することができました。
- INDIRECT関数を使ってテーブルの列を指定することができますが、ちょっと煩わしい・・・
という方は、テーブルの列データに名前の定義を利用する方法もあります。
- データリスト内の1つのセルを選択して、[挿入]タブの[テーブル]を実行します。
ショートカットキーは [Ctrl]+[T] です。
- テーブルの作成 ダイアログボックスが表示されます。
「先頭行をテーブルの見出しとして使用する」にチェックを入れて、[OK]ボタンをクリックします。
- データ範囲をテーブルに変換しました。
[テーブルデザイン]タブを選択すると、テーブル名は テーブル1となっているのを確認できます。
- B2:B5セルを選択し、[数式]タブの定義された名前 グループの[選択範囲から作成]を実行します。
「選択範囲から名前を作成」で「上端行」にチェックを入れて、[OK]ボタンをクリックします。
データ範囲に「野菜」と名前を定義します。ここがこの方法のポイントになります。
- 入力規則を設定するセルを選択し、[データ]タブの[データの入力規則]を実行します。
- 入力値の種類で「リスト」を選択します。
元の値に 「=野菜」 と定義した名前を入力します。
- ドロップダウンリストから選択して入力できるようになりました。
- テーブルに品名を追加します。
- 追加した品名をドロップダウンリストから選択できるようになっています。
入力規則のリストが自動でのびました。
- OFFSET関数とCOUNTA関数を組み合わせてセル範囲を指定します。
- Excel2007以降では数式で別シートの参照が可能になりました。
Excel2003以前のバージョンではこの方法で別シートのリストは利用できません。名前の定義を利用してください。 - 【操作例】現在、リストはSheet3のA1:A3に入力されています。必要に応じてデータをA4セル以降に順次追加したい・・・と思います。
- ここでは、入力規則はSheet1のA2セルへ設定します。元になるリストはSheet3のA1セル以降に入力されることを想定します。
別シートへリストを作成するのが実用的であるとの考えです。
- Sheet3のA1セル以降にリストの元データを入力しました。
- Sheet1のA2セルを選択し、[データ]タブの[データツール]グループの[データの入力規則]を実行します。
- [設定]タブの「入力値の種類」で「リスト」を選択します。
- 「元の値」の欄をクリックし、『=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A1:$A100),1)』と入力します。
- Sheet3のA1:A3に入力されたものがリストに表示されました。
- Sheet3のA4:A5にデータを追加してみました。
- Sheet1のA2セルに設定した入力規則のリストは自動でのびました。
- 名前の定義を利用します。
- 【操作例】Sheet3のA1から下方向へリストを入力するものとします。
- Sheet3のA1セル以降にリストの元データを入力しました。
- [数式]タブの[定義された名前]グループの[名前の定義]を実行します。
- 新しい名前を作成します。
- 名前に「商品名」と入力しました。
- 参照範囲に「=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A1:$A100),1)」と入力しました。
- Sheet1のA2セルを選択して、[データ]タブの[データツール]グループにある[データの入力規則]を実行します。
- [設定]タブの「入力値の種類」で「リスト」を選択します。
- 「元の値」の欄をクリックし、『=商品名』と入力します。
- Sheet3のA1:A3に入力されたものがリストに表示されました。
- Sheet3のA4:A5にデータを追加してみました。
- Sheet1のA2セルに設定した入力規則のリストは自動でのびました。
スポンサードリンク
Home｜Excel講座の総目次｜Excel基本講座の目次｜入力規則のリスト範囲を自動で増減する
PageViewCounter
Since2006/2/27
