Excelでセルにプルダウンリストを設定することはとても多いと思います。
プルダウンリストを使えば、単純に入力する手間は省けますし、誤字脱字を防ぎ、正確なデータの作成、ひいては正確な集計などにも繋がります。
特に複数人で扱う業務用ファイルなどではその効果が大きく表れることでしょう。
このプルダウンリストについて、基本的な設定方法は多くの方がご存知のことと思います。
ですが、プルダウンで表示できる範囲をリストの数に合わせて自動で変更する方法は意外と知らない方も多いのではないでしょうか。
このページではプルダウンリストの基本的な設定方法をはじめ、リストを変更した場合にプルダウンの表示数を自動的にぴったり合わせる方法を紹介します。
基本的な設定方法（セルの入力規則）
設定方法
まずは基本的な設定方法から説明します。
セルのプルダウンリストは「データの入力規則」から設定します。
「データの入力規則」を開き、リストを選択します。
次に、その下の「元の値」という欄にリストの範囲を指定します。
これで次のようにプルダウンリストの表示ができます。
問題点
先ほどの図を見ていただくとわかるとおり、リストに「B3～B10」セルを指定したことによって、「ばなな」の下部分に空欄ができてしまっています。
空欄は少なければそれほど気にはなりませんが、仮にリスト範囲を「B3～B1000」で指定するとどうなるでしょう。
下図のように大量の空欄リストが発生してしまいます。
プルダウンリストは、リストを開いたとき、現在選択しているセルの値を先頭に表示しようとします。
この例ではD2セルが空欄（未入力）なので、リストも空欄が先頭表示されています。
赤枠のスクロールバーを上に上げると、りんご、みかん、ばななのリストも入っているのですが、これではリストを開いたとき、何もないように見えてしまいます。
さらに、リストを選ぶためにスクロールバーを上に移動させるアクションも必要になることを考えれば、使い勝手は悪いと言わざるを得ません。
一方で、ユーザーがリストを追加していく必要がある場合、プルダウンリストとして指定する範囲に余裕を持たせておく必要もあります。
そこで、次にこの欠点をなくすため、リスト数に応じて表示範囲を自動的に変更してくれる方法を紹介します。
リスト数に応じて表示範囲を自動調整
じつはこういった方法はやり方を変えればたくさんあります。
でもたくさん紹介してもややこしいと思うので、ここではオールマイティに使える２つの方法に絞って紹介します。
途中に空欄のないリストの場合
途中に空欄がない場合は簡単です。
入力規則の設定方法は先ほどと同じ要領ですが、リストの範囲を指定する欄に、次のような数式を入力します。
=OFFSET($B$3,0,0,COUNTA($B$3:$B$10))
これで、プルダウンリストも下図のように入力されているリスト分だけ表示できるようになります。
もちろん範囲として指定した「B3～B10」セルの範囲内であれば、リストを追加しても反映されます。
数式はコピペでOKですが、リストによって範囲指定などの変更が必要です。
変更が必要な部分は赤字にしてあります。
実際の数式と並べて見ると変更点と指定方法がわかりやすいのではないでしょうか。
=OFFSET($B$3,0,0,COUNTA($B$3:$B$10))
=OFFSET(リストの先頭セル,0,0,COUNTA(リストのセル範囲))
この数式のポイントを簡単に言うと、リストの個数をCOUNTA関数で数えることによってリストの増減に対応しています。
セル数が適切に数えられるのであればCOUNTAである必要は特になく、COUNTIF（指定条件に合うセル個数）などほかの関数に置き換えてもかまいません。
ただし、見出しにも記載したとおり、これは「途中に空欄がないリスト」にしか使えません。
COUNTA関数は何らかの入力のあるセル数を数えています。そしてCOUNTAで数えた入力済みセル数がリストの表示行数になるのです。
例としては、この図のように空欄セルの個数は数えることができないため、途中に空欄が入ると空欄分の表示行が減ってしまいます。
リストの範囲をCOUNTAで数える方法は数式も短くて便利ですが、このような欠点があるため、次のような対策を取ることをおすすめします。
- 条件付き書式を使って、途中に空欄セルが発生したら赤い網掛けで警告する。
- 若干の空欄が入ることを想定し、リストに余分を持たせておく。
１に関しては条件付き書式のページを別途ご確認ください。
２に関しては指定した数式に、次のように好きな数値を足せばいいだけです。
=OFFSET($B$3,0,0,COUNTA($B$3:$B$10)＋２)
この場合はプルダウンリストを開いたとき、2行分の余白を持たせておくことができます。
途中に空欄があるリストの場合
先に断っておきますが、こちらは少しややこしいので仕組みを理解する必要はありません。
こういったものは実際に使えるだけでいいのです。
そういう意味では決して難しくないので安心してください。
基本的な設定方法はこれまでとすべて同じで、指定する数式を変えるだけです。
例えば次の数式をご覧ください。数式の型は同じですが、赤字部分が前段と違う部分です。
=OFFSET($B$3,0,0,MAX(INDEX((LEN($B$3:$B$10)>0)*ROW($B$3:$B$10),0))-2)
いや～、ややこしいですね。我ながらどうなのかと思います。
しかし、これなら次のように途中に空欄が入ったとしてもリストの最終行まで表示することができます。
ただ、これではまだ完全とは言えません。
この数式ではリストが空っぽの場合、（条件によってはですが）正常にリストを表示できません。
とはいえ、リストが空になることが想定されないシーンではこれでもじゅうぶんです。
使う場所によって変更するのは次の赤字部分です。
=OFFSET($B$3,0,0,MAX(INDEX((LEN($B$3:$B$10)>0)*ROW($B$3:$B$10),0))-2)
=OFFSET(リストの先頭セル,0,0,MAX(INDEX((LEN(リストの範囲)>0)*ROW(リストの範囲),0))-リストのヘッダー行数)
赤字で示したリストのヘッダー行の例としては次の部分です。
サンプルで示した数式で最後に「－２」をしているのはこのヘッダー行の行数です。
では、リストが空っぽの場合でも正常に表示するにはどうすればいいでしょうか。
簡単なのは数式の中で条件分岐してやることです。
次の数式を見てください。先ほどの数式に、赤字の部分を追加しています。（青字の部分は先ほどの数式とまったく同じです。）
=OFFSET($B$3,0,0,IF(COUNTA($B$3:$B$10)=0,1,MAX(INDEX((LEN($B$3:$B$10)>0)*ROW($B$3:$B$10),0))-2))
赤字の部分を和訳すると「もし、リストに何も入力されていなかったら１とする」という意味です。
逆に、リストに何か入力されていれば青字の部分で処理されます。
さて、もう説明するまでないとは思いますが、リストによって変更が必要な部分は次のとおりです。
=OFFSET($B$3,0,0,IF(COUNTA($B$3:$B$10)=0,1,MAX(INDEX((LEN($B$3:$B$10)>0)*ROW($B$3:$B$10),0))-2))
=OFFSET(リストの先頭セル,0,0,IF(COUNTA(リストの範囲)=0,1,MAX(INDEX((LEN(リストの範囲)>0)*ROW(リストの範囲),0))-リストのヘッダー行数))
数式は長いですが、これで拡張性があり、途中に空欄があっても漏れなく表示でき、もしリストが空っぽになっても正常動作するプルダウンリストが完成します。
まとめ
プルダウンリストの数式指定にはほかにもたくさんの方法があります。
例えば、数値のみ、または文字列のみのリストにしか使えないが、もっと数式が短く簡易な方法もあります。
ただ、現実的にはどんな場合でも使える数式を２パターンくらい覚えておけばじゅうぶんな場合も多いのではないでしょうか。
それを前提に、できるだけオールマイティな２つの方法をご紹介しました。
なお、今回紹介した数式の作成（部分変更）にあたって、入力規則の入力欄で直接編集するのはあまりおすすめしません。
長い数式を入れることが想定されていないのか、とても入力し辛いのです。
おすすめは、上記の数式をExcelのどこか適当なセルにコピペして編集し、編集後の数式を入力規則の入力欄に貼り付ける方法です。
作業性が圧倒的に良くなりますのでぜひお試しください。
では、少しでも参考になれば幸いです。
コメント