エクセルでプルダウンリストの追加・削除に自動対応する方法
エクセルでプルダウンリスト（入力規則）を作ると、
入力したいデータの追加・削除への対応
（＝プルダウンリストの表示個数を可変にする）が、大変です。
（詳細はエクセルでプルダウンリストを表示させる（入力規則）参照）
そこで、今回は、
プルダウンリストで、データの追加・削除に自動対応する方法を書いていきます。
- 行の挿入・削除を使う
- counta関数とoffset関数の組み合わせ
- match関数とoffset関数の組み合わせ
- sumproduct関数とoffset関数の組み合わせ
- 作業列を使う（＋offset関数）
- まとめ
プルダウンリストでデータ追加・削除に対応する方法
いくつか方法があるので、
紹介していきます。
それぞれ一長一短があるので、
状況に合ったものを使ってください。
C2セルに、
「リスト」シートのA列に入っている勘定科目
（＝今回はA2セルからA39セルまで）」
を選択できるリストを表示させます。
１．行の挿入・削除を使う
一番、簡単なのがこの方法です。
まず、
データの入力規則の「元の値」の欄に、
「=リスト!$A$2:$A$7」
というように、
今入っている行より１つだけ多く行を指定します。
このままで、
プルダウンリストを表示させると、
次のようになります。
余計な空欄が一つ表示されますが、
この方法だと仕方がありません。
行を追加するときは、
「リスト」シートの
A6セルとA7セルの「間に」行を挿入します。
例えば、
１行挿入すると、
データの入力規則に入力されているセル範囲も、
自動でひとつ増えて、
「=リスト!$A$2:$A$8」
に変わります。
あとは、
A7セルに追加で項目を１つ入力すれば、
プルダウンリストに表示させる項目を増やすことができます。
逆に削除をするときは、
リストシートの「行の削除」をすればOKです。
この方法のメリットは、
「元の値」欄に入れる計算式が単純でわかりやすい、ということ。
逆に次のような欠点もあります。
- プルダウンリストの最後に、空行ができてしまうこと
- 値を挿入・削除するときに、適切な行で、行の挿入・行の削除をしないと、式が壊れてしまうこと
- メンテナンスに行の挿入・削除が必須であることから、
現実的には、１つのプルダウンリストの選択肢メンテナンス用に１つのシートを作らなければならず、面倒
とはいえ、
計算式が単純、というメリットは非常に大きいため、
運用がきちんとできるのであれば、
個人的には、単純なので、一番おすすめです。
２．counta関数とoffset関数の組み合わせ
入力規則の「元の値」欄に、
counta関数とoffset関数を組み合わせて、
次のように入力すると、リストが表示されます。
↓
入力した計算式については、
ちょっと難しいので、図解しました。
counta関数で「値が入っているセルの件数」を取得し、
その結果をoffset関数で使っています。
今回は、
次のような形で使っているのですが、
これで、
F2セルを含め指定した行数分の「セル範囲」
を表すことができます。
例えば、
| 「=offset(F2, 0, 0, 3, 1)」 | → | F2セル～F4セル（F2セル含め3行分） | → | 「F2:F4」 |
| 「=offset(F2, 0, 0, 5, 1)」 | → | F2セル～F6セル（F2セル含め5行分） | → | 「F2:F6」 |
| 「=offset(F2, 0, 0, 8, 1)」 | → | F2セル～F9セル（F2セル含め8行分） | → | 「F2:F8」 |
という感じです。
この「行数」のところに、
先ほどの、counta関数を入れることで、
F2セルから「F2セルからF999セルまでに何か入力されているセルの数の合計」行分の
「セル範囲」を指定することができます。
例えば、
冒頭の状況であれば、
F2セルからF999セルまでには、
5つのセルに文字が入力されていますので、
=offset(F2, 0, 0, counta(F2:F999), 1)
→offset(F2, 0, 0, 5, 1)
→F2セル～F6セル
→F2:F6
という意味になり、
ちょうど、リスト化したい範囲を指定することができるわけです。
この方法は、
マイクロソフトが、自身のホームページに記載している方法ですので、
いわば、マイクロソフト公認(?)の方法で、
offset関数さえ理解できれば
そこそこ、いろいろな場面で使えると思います。
ただし、
- リストの途中に空白セルがあるとうまくいかない（左図）
- 計算式で空白になっているようなセルがあるとうまくいかない（右図）
といった欠点もあります。
３．match関数とoffset関数の組み合わせ
かなりマニアックになってきますが、
match関数とoffset関数の組み合わせ、という方法もあります。
ちょうど、
先ほどのcounta関数のところを、match関数に置き換えています。
match関数の中で「黑」という文字が出てきていますが、
これは、黒という漢字の旧字体です。
この「黑」は、
Windowsベースのエクセルでは、
文字列の比較をした時に、一番大きいと判定される文字です。
（細かい説明は省略しますが）
match関数の「近似値検索」で「黑」を探すことで、
（元のプルダウンリストに、この文字が入っていない限り）
「A2セルから数えて、データが入っている一番下の行が何行目か？」
が求められます。
あとは、先ほどのcountaを使った結果と同様に、
offset関数に入れればOKです。
メリットとしては、
先ほどのcountaではダメだった、
「リストの途中に空白セルがある場合」
でも、うまく動きます。
ただし、次のようなデメリットがあります。
- 計算式で空白になっているようなセルがあるとうまくいかない（先ほどと同様）
- プルダウンリストに表示するデータが「文字列」データでないとダメ（※）
- 計算式がかなりマニアック
※プルダウンリストに表示するデータが「数値」データであれば、
matchの部分を次のように書き換えればOKです。
４．sumproduct関数とoffset関数の組み合わせ
次のように、
sumproduct関数とoffset関数を組み合わせるやり方もあります。
この方法だと、
プルダウンリストの最後に、
計算式で空白になるようなセルがあっても対応できるのですが、
逆に、
選択肢の中に空白セルがあるとうまく動きません。
５．作業列を使う（＋offset関数）
次のように、
「リスト」シートのほうに作業列を作るやり方もあります。
作業列では、
「左隣のセルに値が入っているとき」は、
選択肢の先頭（2行目）から、現在の行までの「行数」を計算しています。
逆に、値が入っていないときは、
すぐ上の作業列の数値をそのまま持ってきます。
こうすることで、
「リスト」シートのB39セルには、
「先頭（2行目）」から「最後に値が入っている行」までの「行数」が入ることになります。
あとは、その行数
先ほどからでてきている「offset関数」に入れてやればいいわけです。
これだと、
ほぼ制約なく、プルダウンリストを増減できますが、
作業列を作らないといけないのが面倒です。
まとめ
ということで、今回は、
プルダウンリストの挿入・削除に対応する方法５つを紹介しました。
どれも、一長一短あるので、
どの方法が一番、というのはないのですが、
どれを使うか迷った場合には、
特別な理由が無い限り、１番目の方法がいいと思います。
やはり、計算式は単純なのが一番です。
次点は、
２番目のcountaを使う方法。
やはり、マイクロソフト公認（？）の方法だけあって、
情報が、豊富です。
他は、
どうしても、上の２つの方法ではダメ、という場合に、
適宜使い分けてもらえればいいと思います。