【Excel】セルのドロップダウンリストを別シートから参照!Excelの名前定義で動的リストを作る

Excelで入力規則のドロップダウンリストを作成する際、リストの内容を別シートにまとめて管理したい場面があります。しかし、単純にリスト範囲を指定すると、リストの項目が増減したときに手動で範囲を修正する必要が生じます。この手間を解消し、リストを自動的に更新できるようにするには、Excelの名前定義機能が役立ちます。

本記事では、別シートに作成したリストをドロップダウンリストの参照元とし、項目が増減しても自動で追従する「動的リスト」を作成する方法を解説します。これにより、リスト管理の効率が格段に向上します。

別シートのリストをドロップダウンリストにする基本

Excelでセルの入力規則機能を使うと、特定のセルにプルダウンメニュー形式のリストを表示させることができます。これにより、ユーザーはリストから項目を選択するだけで入力でき、誤入力を防ぎ、データの均一性を保つことが可能です。

通常、このドロップダウンリストの元となるデータは、同じシート上にある範囲を指定します。しかし、リストの項目数が増えたり減ったりした場合、その都度、入力規則の設定画面を開いて参照範囲を修正する必要が生じます。これは、リストが長くなるほど、または頻繁に更新される場合に大きな手間となります。

動的リスト作成の仕組み:OFFSET関数とCOUNTA関数

リストの項目が増減しても自動で追従する「動的リスト」を実現するには、Excelの関数を組み合わせた「名前定義」が不可欠です。ここで中心となるのが、OFFSET関数とCOUNTA関数です。

OFFSET関数は、指定したセルを基準に、指定した行数・列数だけ移動した位置にあるセル範囲を返します。この関数の特徴は、返される範囲の大きさを指定できる点です。COUNTA関数は、指定した範囲内の空白でないセルの数を数えます。この2つを組み合わせることで、「リストの開始セルから、空白でないセルの数だけ範囲を広げる」という動的な範囲定義が可能になります。

別シートのリストを動的に参照する名前の定義手順

ここでは、具体的な手順を解説します。まず、リストの元データとなる項目を別シートに準備し、そのリスト範囲を動的に参照する名前を定義します。

1. リスト元データの準備
   Excelファイルを開き、リストの項目を格納するための別シートを作成します。例えば、「リストデータ」という名前のシートを作成し、A1セルからリスト項目を入力していきます。A1セルに「りんご」、A2セルに「みかん」のように、連続したセルに入力してください。
2. 名前定義の画面を開く
   「数式」タブをクリックし、「名前定義」グループにある「名前の管理」をクリックします。
3. 新しい名前の定義
   「名前の管理」ダイアログボックスが表示されたら、「新規作成」ボタンをクリックします。
4. 名前の入力
   「新しい名前」ダイアログボックスの「名前」欄に、このリストに付ける名前を入力します。後でドロップダウンリストから参照する際に使用する名前です。例えば、「商品リスト」と入力します。この名前は、英数字で始まり、スペースを含まない必要があります。
5. 参照範囲の設定
   「名前」欄の下にある「参照範囲」欄に、動的な範囲を指定する数式を入力します。ここでは、OFFSET関数とCOUNTA関数を組み合わせます。数式は以下のようになります。(「リストデータ」シートのA1セルからリストが始まると仮定)

   =OFFSET(リストデータ!$A$1,0,0,COUNTA(リストデータ!$A:$A),1)

   この数式の意味は以下の通りです。

   • リストデータ!$A$1: 参照の基点となるセル(リストの先頭セル)を指定します。
   • 0,0: 基点セルから移動しない(行・列ともに0移動)ことを意味します。
   • COUNTA(リストデータ!$A:$A): A列全体で空白でないセルの数を数え、リストの行数を動的に取得します。
   • 1: 範囲の列数を1列に指定します。

   ※COUNTA関数でリストの行数を数える際、A列全体を指定していますが、リスト項目以外のセルにデータが入っていると、意図しない範囲が取得される可能性があります。リスト項目のみが連続して入力されている列を指定するか、あるいはリストの終了位置をある程度予測できる範囲(例: `リストデータ!$A$1:$A$100`)を指定する方が安全な場合もあります。

6. OKをクリック
   数式を入力したら、「OK」ボタンをクリックして名前定義を完了します。

ドロップダウンリストへの適用手順

名前定義が完了したら、実際にドロップダウンリストを設定したいセルに、この名前を適用します。ここでは、例えば「Sheet1」のB1セルにドロップダウンリストを設定する手順を説明します。

1. リストを設定したいセルを選択
   「Sheet1」のB1セルを選択します。
2. 入力規則を開く
   「データ」タブをクリックし、「データツール」グループにある「データの入力規則」をクリックします。
3. 設定タブの入力
   「データの入力規則」ダイアログボックスが表示されたら、「設定」タブを選択します。
4. 入力値の種類を選択
   「入力値の種類」ドロップダウンリストから「リスト」を選択します。
5. 元の値に名前定義を入力
   「元の値」欄に、先ほど名前定義で作成した名前を、半角の等号(=)に続けて入力します。例えば、「商品リスト」という名前を付けた場合は、「=商品リスト」と入力します。
6. OKをクリック
   「OK」ボタンをクリックして、入力規則の設定を完了します。

これで、「Sheet1」のB1セルには、別シート「リストデータ」のA列にある項目を元にしたドロップダウンリストが表示されるようになります。リストの項目を「リストデータ」シートで追加・削除すると、B1セルのドロップダウンリストも自動的に更新されます。

Excel 2019・2021との違い

今回解説したOFFSET関数とCOUNTA関数を組み合わせた名前定義による動的リストの作成方法は、Excelの多くのバージョンで利用可能です。Excel 2019やExcel 2021でも、この手順で問題なく設定できます。

Microsoft 365版Excelでは、より新しい関数(例: FILTER関数)や動的配列機能の登場により、さらに柔軟なリスト作成が可能になっています。しかし、OFFSET関数とCOUNTA関数を用いたこの方法は、互換性が高く、多くの環境で利用できるため、依然として有効なテクニックです。

よくある質問とトラブルシューティング

リスト項目が増減してもドロップダウンリストが更新されない

この問題が発生する主な原因は、名前定義の参照範囲の設定が正しくないことです。以下の点を確認してください。

1. 数式の誤り
   「名前の管理」で定義した数式が、=OFFSET(シート名!$A$1,0,0,COUNTA(シート名!$A:$A),1)の形式になっているか確認してください。シート名、開始セル、COUNTA関数の参照範囲が正しいか、スペルミスがないかなどを注意深くチェックします。
2. COUNTA関数の参照範囲
   COUNTA関数で指定している列に、リスト項目以外のデータが入力されていると、リストの範囲が意図せず広くなってしまいます。リスト項目のみが入力されている列を指定するか、あるいはリストの最大項目数をある程度想定して範囲を指定し直すことを検討してください。例えば、最大100件のリストになると想定する場合、COUNTA(リストデータ!$A$1:$A$100)のように範囲を固定することも有効です。
3. 名前定義の再設定
   参照範囲を修正した場合は、必ず「名前の管理」画面でその名前を選択し、「編集」ボタンから数式を修正して「OK」をクリックしてください。

ドロップダウンリストに何も表示されない

この場合、入力規則の設定、または名前定義のどちらかに問題がある可能性が高いです。

1. 入力規則の「元の値」
   入力規則の設定画面で、「元の値」欄に=商品リストのように、定義した名前が正しく入力されているか確認してください。名前の前に「=」が付いている必要があります。
2. リスト元データの空白セル
   OFFSET関数は、COUNTA関数で取得した行数分の範囲を返します。もし、リストの元データ(例:「リストデータ」シートのA1セル)が空白の場合、COUNTA関数は0を返すため、範囲が取得できず、ドロップダウンリストは空になってしまいます。リストの先頭セルに必ず項目が入力されているか確認してください。
3. 名前定義の参照範囲が0行を返す
   COUNTA関数が0を返すと、OFFSET関数も0行の範囲を返します。これもリストが表示されない原因となります。

リストの途中に空白セルがある場合

COUNTA関数は、指定した範囲内の空白でないセルの数を数えます。そのため、リストの途中に空白セルがあると、COUNTA関数はその空白セル以降をリストの終わりと認識してしまいます。結果として、ドロップダウンリストには空白セル以降の項目が表示されなくなります。

対処法

1. リスト項目を連続させる
   最も確実な方法は、リストの項目を空白セルを挟まずに連続して入力することです。
2. INDIRECT関数と組み合わせる(上級者向け)
   もしリストの途中に空白セルが入ることを避けられない場合は、COUNTA関数の代わりにINDIRECT関数などを用いて、より複雑な範囲定義を行う方法もあります。しかし、これは数式が複雑になり、管理が難しくなるため、基本的にはリスト項目を連続させることを推奨します。

Excelの名前定義機能の応用

Excelの名前定義機能は、ドロップダウンリストの動的作成以外にも、様々な場面で活用できます。例えば、複雑な数式の一部に名前を付けることで、数式が読みやすくなり、メンテナンス性が向上します。

また、特定のセル範囲に名前を付けておけば、その名前をクリックするだけで、すぐにそのセル範囲にジャンプできます。これは、大きなシートや複数のシートを扱う際に、ナビゲーションを助ける機能となります。「名前の管理」画面で、定義済みの名前を選択し、右側にある「ジャンプ」ボタンをクリックすることで、その機能を利用できます。

まとめ

本記事では、Excelで別シートのリストを参照し、項目が増減しても自動で更新される「動的ドロップダウンリスト」を作成する方法を解説しました。OFFSET関数とCOUNTA関数を組み合わせた名前定義を用いることで、リスト管理の手間を大幅に削減できます。

このテクニックを習得すれば、入力規則の管理が効率化され、データ入力作業の正確性も向上します。ぜひ、ご自身のExcelファイルでこの設定を試してみてください。さらに応用として、この動的リストを他の関数と組み合わせることで、より高度なデータ管理が可能になるでしょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】