エクセルでプルダウンリストの追加・削除に自動対応する方法

エクセルでプルダウンリスト(入力規則)を作ると、

入力したいデータの追加・削除への対応
(=プルダウンリストの表示個数を可変にする)が、大変です。

(詳細はエクセルでプルダウンリストを表示させる(入力規則)参照)

そこで、今回は、
プルダウンリストで、データの追加・削除に自動対応する方法を書いていきます。

  1. 行の挿入・削除を使う
  2. counta関数とoffset関数の組み合わせ
  3. match関数とoffset関数の組み合わせ
  4. sumproduct関数とoffset関数の組み合わせ
  5. 作業列を使う(+offset関数)
  6. まとめ

プルダウンリストでデータ追加・削除に対応する方法

いくつか方法があるので、
紹介していきます。
それぞれ一長一短があるので、
状況に合ったものを使ってください。

C2セルに、
「リスト」シートのA列に入っている勘定科目
 (=今回はA2セルからA39セルまで)」
を選択できるリストを表示させます。

pulldown3_1_0

1.行の挿入・削除を使う

一番、簡単なのがこの方法です。

まず、
データの入力規則の「元の値」の欄に、
「=リスト!$A$2:$A$7」
というように、
今入っている行より1つだけ多く行を指定します。

pulldown3_1_1

このままで、
プルダウンリストを表示させると、
次のようになります。

pulldown3_1_2

余計な空欄が一つ表示されますが、
この方法だと仕方がありません。

行を追加するときは、
「リスト」シートの
A6セルとA7セルの「間に」行を挿入します。

例えば、
1行挿入すると、
データの入力規則に入力されているセル範囲も、
自動でひとつ増えて、
「=リスト!$A$2:$A$8」
に変わります。

pulldown3_2_0

あとは、
A7セルに追加で項目を1つ入力すれば、
プルダウンリストに表示させる項目を増やすことができます。

pulldown3_2_1

逆に削除をするときは、
リストシートの「行の削除」をすればOKです。

この方法のメリットは、
「元の値」欄に入れる計算式が単純でわかりやすい、ということ。

逆に次のような欠点もあります。

  • プルダウンリストの最後に、空行ができてしまうこと
  • 値を挿入・削除するときに、適切な行で、行の挿入・行の削除をしないと、式が壊れてしまうこと
  • メンテナンスに行の挿入・削除が必須であることから、
    現実的には、1つのプルダウンリストの選択肢メンテナンス用に1つのシートを作らなければならず、面倒

とはいえ、
計算式が単純、というメリットは非常に大きいため、
運用がきちんとできるのであれば、
個人的には、単純なので、一番おすすめです。

2.counta関数とoffset関数の組み合わせ

入力規則の「元の値」欄に、
counta関数とoffset関数を組み合わせて、
次のように入力すると、リストが表示されます。

pulldown3_3_1

pulldown3_3_2

入力した計算式については、
ちょっと難しいので、図解しました。

pulldown3_0_1

counta関数で「値が入っているセルの件数」を取得し、
その結果をoffset関数で使っています。

今回は、
次のような形で使っているのですが、

=offset(F2, 0, 0, 「行数」, 1)

これで、
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関数さえ理解できれば
そこそこ、いろいろな場面で使えると思います。

ただし、

  • リストの途中に空白セルがあるとうまくいかない(左図)
  • 計算式で空白になっているようなセルがあるとうまくいかない(右図)

といった欠点もあります。

pulldown3_4_1

3.match関数とoffset関数の組み合わせ

かなりマニアックになってきますが、
match関数とoffset関数の組み合わせ、という方法もあります。

pulldown3_5_1

ちょうど、
先ほどのcounta関数のところを、match関数に置き換えています。

match関数の中で「黑」という文字が出てきていますが、
これは、黒という漢字の旧字体です。

この「黑」は、
Windowsベースのエクセルでは、
文字列の比較をした時に、一番大きいと判定される文字です。

(細かい説明は省略しますが)
match関数の「近似値検索」で「黑」を探すことで、

(元のプルダウンリストに、この文字が入っていない限り)
「A2セルから数えて、データが入っている一番下の行が何行目か?」
が求められます。

あとは、先ほどのcountaを使った結果と同様に、
offset関数に入れればOKです。

メリットとしては、
先ほどのcountaではダメだった、
「リストの途中に空白セルがある場合」
でも、うまく動きます。

ただし、次のようなデメリットがあります。

  • 計算式で空白になっているようなセルがあるとうまくいかない(先ほどと同様)
  • プルダウンリストに表示するデータが「文字列」データでないとダメ(※)
  • 計算式がかなりマニアック

※プルダウンリストに表示するデータが「数値」データであれば、
matchの部分を次のように書き換えればOKです。

pulldown3_5_2

4.sumproduct関数とoffset関数の組み合わせ

次のように、
sumproduct関数とoffset関数を組み合わせるやり方もあります。

pulldown3_6_1

この方法だと、
プルダウンリストの最後に、
計算式で空白になるようなセルがあっても対応できるのですが、

逆に、
選択肢の中に空白セルがあるとうまく動きません。

5.作業列を使う(+offset関数)

次のように、
「リスト」シートのほうに作業列を作るやり方もあります。

pulldown3_7_0

作業列では、

「左隣のセルに値が入っているとき」は、
選択肢の先頭(2行目)から、現在の行までの「行数」を計算しています。

逆に、値が入っていないときは、
すぐ上の作業列の数値をそのまま持ってきます。

こうすることで、
「リスト」シートのB39セルには、
「先頭(2行目)」から「最後に値が入っている行」までの「行数」が入ることになります。

あとは、その行数
先ほどからでてきている「offset関数」に入れてやればいいわけです。

pulldown3_7_1

これだと、
ほぼ制約なく、プルダウンリストを増減できますが、
作業列を作らないといけないのが面倒です。

まとめ

ということで、今回は、
プルダウンリストの挿入・削除に対応する方法5つを紹介しました。

どれも、一長一短あるので、
どの方法が一番、というのはないのですが、

どれを使うか迷った場合には、
特別な理由が無い限り、1番目の方法がいいと思います。
やはり、計算式は単純なのが一番です。

次点は、
2番目のcountaを使う方法。
やはり、マイクロソフト公認(?)の方法だけあって、
情報が、豊富です。

他は、
どうしても、上の2つの方法ではダメ、という場合に、
適宜使い分けてもらえればいいと思います。

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。