メンテナンスいらず!可変範囲のプルダウンリストの作り方

アイキャッチ

皆様、おはこんばんちは!まおすけです!

みなさん、エクセルでプルダウンリスト使ってますか?

使ってない方はこの記事を見る前に、是非こちらの記事を見てください。

初心者必見!エクセルでプルダウンリストを簡単に作る方法
皆さん、おはこんばんちは!まおすけです!皆さん、エクセルでこんな感じで入力したことはありませんか?これは、プルダウンリストと呼ばれるもので...

で、これだけでプルダウンリストを使うことはできます。

でも実は、この作り方だと項目が増えた時に範囲の変更が必要になってしまうんです。

つまり、

・直接項目を手入力してプルダウンリスト作る場合

項目が増えたら増えた分を手入力する必要がある

・予め入力してある項目を参照してプルダウンリストを作る場合

項目が増えたら参照範囲を変える必要がある

ということなんですが、そんなのめんどくせぇ!という方に朗報です。

この記事では項目に変更があってもそのまま使えるプルダウンリストの作り方を解説していきます。

これができれば、業務が短縮できる上に、うっかりミスも防げるようになりますよ!

それでは説明していきます!

今回の事例

事例

まず今回の説明をしていくにあたって、事例がある方がわかりやすいので、

①Dの列にクソゲーリストが並んでいる

②B3のセルにプルダウンリストを設定して、①のクソゲーリストの中から選択をする

③①のクソゲーリストが増えたら、②のプルダウンリストの項目も自動で増えるようにする

という事例を考えていきます。

可変範囲プルダウンリストの事例

今回のメインは③の設定方法です。

結論

結論

まず、結論から書いてしまいます。

わかる人にはこれだけでわかってしまうと思うので、コレ見てわかった!という人は、後の記事は読まなくて大丈夫です。

今回の場合はこんな感じで指定してください。

①「データの入力規則」の「設定」から

②「入力値の種類」を「リスト」にして、

③「元の値」に「=OFFSET(D4,0,0,COUNTA(D:D)-1,1)」と入力

可変範囲のプルダウンリストを作るための設定

(実際に作る時は、自分の作る資料に合わせて列やOFFSETの起点となるセルは変更してくださいね!)

関数の説明

関数の説明

結論だけ見てもわからない!という方はここからの記事を読んでください。

結論で見ての通り、「COUNTA」と「OFFSET」という関数を組み合わせています。

まずこれらの関数について順番に説明します。

両方とも知ってるよ!って人は、もう少し下の「可変範囲の指定」まで飛ばしてもらって大丈夫です。

COUNTA関数とは

日本語に翻訳すると、こんな感じ。

COUNTA関数の構造

日本語訳:

①の範囲にあるデータの数を数えてね!

ということで、例えば「COUNTA(D:D)」の場合、

D列のデータの数を数えてね!

ってことなので、下の図において「COUNTA(D:D)」は「6 」を意味します。

COUNTA関数の例

これは理解しやすいと思います。

OFFSET関数とは

こっちはすごくわかり辛い関数ですが、一回理解すると他にも応用が効く関数なので、頑張って付いてきてください!

OFFSET関数の構造と日本語訳は以下のとおりです。

OFFSET関数の構造

日本語訳:

・①をスタートとして、

行、列だけ移動したところを基準に、

行×列の範囲を選択する(は1以上の数字が入ります)

なお、は省略できます。省略した場合は両方とも「1」になります。

文字だけじゃすごくわかりにくいと思うので、3つほど具体例を出します。

1.を指定

例:

=OFFSET(B4,2,1)

日本語訳:

・B4をスタートにして、

2行、1列だけ移動したところを基準に

1行×1列の範囲を選択する

は省略してるので、両方とも「1」になります。)

図で表すとこんな感じになります。

OFFSET関数の事例1

2.④、を指定

例:

=OFFSET(B4,0,0,2,2)

日本語訳:

・B4をスタートとして、

0行、0列だけ移動したところを基準に

(つまりB4から移動しない)

2行×2列の範囲を選択する。

図で表すと下記のような感じになります。

OFFSET関数の事例2

3.を指定

例:

=OFFSET(B4,2,1,2,2)

日本語訳:

・B4をスタートとして、

2行、1列だけ移動したところを基準に

2行×2列の範囲を選択する。

図で表すと下記のような感じになります。

OFFSET関数の事例3

いままでの2つを組み合わせただけなので、パッと見でわからない人はゆっくり考えてみてください。

ちなみに・・・・

今回の3番目の例のように、基準を動かす(②、)のと、範囲を選択する()のを同時に指定することはほとんどありません。

わけわかんなくなっちゃいますしね。

一応こういうこともできるよ!という解説のために載せています。

これがOFFSET関数です。

可変範囲の指定

可変範囲の指定

やっと、ここからが本番です。

2つの関数を組み合わせてプルダウンリストの範囲が変化するように範囲を指定します。

今回の結論はこんな感じでしたね。

可変範囲のプルダウンリストを作るための設定

結論のOFFSET関数を色分けしてみるとこうなります。

OFFSET関数で可変範囲のプルダウンリストを出す結論

今回はOFFSET関数のの部分に「COUNTA(D:D)-1」という関数が入っています。

ほかにはゼロ、は1が入っています。

これを日本語訳するとこんな感じになります。

・D4を基準として、

0行、0列移動したところを基準に

 (つまりD4から動かない)

・「COUNTA(D:D)-1」行×「1」列の範囲を選択してね。

となります。

ここで「COUNTA(D:D)-1」という意味わからないものが出てきましたが、すごく簡単です

COUNTA(D:D)はD列の項目を数えてるだけです。なので、「6 」です。

そこから、1を引いてます。

なので、今回の場合は「COUNTA(D:D)-1 = 6-1 =5」となります。

なんで-1するのかというと、実際の項目数は5なのに、COUNTA(D:D)のままだと「クソゲーリスト」という項目名までカウントしてしまうからです。

これでOFFSET関数によって、下記の図の青枠の範囲を指定したことになるので、リストもその通りに表示されます。

OFFSET関数でプルダウンリストの参照範囲が表す場所

項目が増えた場合

では、項目が1つ増えたらどうなるかを見ていきます。

1つ項目が増えると、D列の項目数を数えているCOUNTA(D:D)が1つ増えます。

つまり「COUNTA(D:D)-1」は「7-1=6」を表します。

なので、結論の「=OFFSET(D4,0,0,COUNTA(D:D)-1,1)」が表す範囲は以下の通りになります。

項目が増えた場合のプルダウンリストでOFFSET関数が表す範囲

どれだけ下に項目が追加されても、勝手にCOUNTA(D:D)が増えていくのでプルダウンリストの範囲も勝手に増えていくというスンポーです。

注意!

COUNTAは空白を数えません。なので、リストの途中で空白が入っちゃうと、OFFSET関数で表す範囲が一番下の項目まで伸びなくなります。

リストは空白無しで詰めて入力してくださいね。

空白がある場合のOFFSET関数とCOUNTA関数の組み合わせが参照する範囲

まとめ

まとめ

今回はCOUNTA関数とOFFSET関数を使った、可変範囲のプルダウンリストの作り方の解説でした!

今回はちょっと応用的な使い方なので、ぱっと見では難しいと思います。

でも、1つずつ理解していけば絶対に理解できるようになります。

ここで覚えてしまえば他にも応用が効く内容ですので、ぜひともマスターしてください!

________________________________________

その他のエクセルを使った業務効率化について知りたい方はエクセルの目次へどうぞ。

このブログの目次
(随時更新)<ショートカット>このブログのメインコンテンツ。覚えれば覚えるほど人生変わります。<エクセルにまつわるエトセトラ>エクセルにまつ...

************編集後記***************

この記事書くのに丸2日以上かかってもーた!

COUNTAとか、OFFSETの説明入れたのがまずかった!

めちゃくちゃ長い記事になってしまったので、関数部分は分離して別記事にするかもしれません。

*******************************

円形に切り抜いた自撮写真

最後まで読んでいただきありがとうございました!
下手くそながらこんな感じでコツコツ記事を書いてます。
もしちょっとでも!ほんのちょっとでもあなたのお役に立てたなら!
あなたが参加しているSNSボタンでフォローお願い致します!

一人で小躍りしながら喜びます!

シェアする

  • このエントリーをはてなブックマークに追加

フォローする