皆様、おはこんばんちは！まおすけです！
みなさん、エクセルでプルダウンリスト使ってますか？
使ってない方はこの記事を見る前に、是非こちらの記事を見てください。
で、これだけでプルダウンリストを使うことはできます。
でも実は、この作り方だと項目が増えた時に範囲の変更が必要になってしまうんです。
つまり、
・直接項目を手入力してプルダウンリスト作る場合
→項目が増えたら増えた分を手入力する必要がある
・予め入力してある項目を参照してプルダウンリストを作る場合
→項目が増えたら参照範囲を変える必要がある
ということなんですが、そんなのめんどくせぇ！という方に朗報です。
この記事では項目に変更があってもそのまま使えるプルダウンリストの作り方を解説していきます。
これができれば、業務が短縮できる上に、うっかりミスも防げるようになりますよ！
それでは説明していきます！
今回の事例
まず今回の説明をしていくにあたって、事例がある方がわかりやすいので、
①Dの列にクソゲーリストが並んでいる
②B3のセルにプルダウンリストを設定して、①のクソゲーリストの中から選択をする
③①のクソゲーリストが増えたら、②のプルダウンリストの項目も自動で増えるようにする
という事例を考えていきます。
今回のメインは③の設定方法です。
結論
まず、結論から書いてしまいます。
わかる人にはこれだけでわかってしまうと思うので、コレ見てわかった！という人は、後の記事は読まなくて大丈夫です。
今回の場合はこんな感じで指定してください。
①「データの入力規則」の「設定」から
②「入力値の種類」を「リスト」にして、
③「元の値」に「=OFFSET(D4,0,0,COUNTA(D:D)-1,1）」と入力
（実際に作る時は、自分の作る資料に合わせて列やOFFSETの起点となるセルは変更してくださいね！）
関数の説明
結論だけ見てもわからない！という方はここからの記事を読んでください。
結論で見ての通り、「COUNTA」と「OFFSET」という関数を組み合わせています。
まずこれらの関数について順番に説明します。
両方とも知ってるよ！って人は、もう少し下の「可変範囲の指定」まで飛ばしてもらって大丈夫です。
COUNTA関数とは
日本語に翻訳すると、こんな感じ。
日本語訳：
①の範囲にあるデータの数を数えてね！
ということで、例えば「COUNTA(D:D)」の場合、
D列のデータの数を数えてね！
ってことなので、下の図において「COUNTA(D:D)」は「6 」を意味します。
これは理解しやすいと思います。
OFFSET関数とは
こっちはすごくわかり辛い関数ですが、一回理解すると他にも応用が効く関数なので、頑張って付いてきてください！
OFFSET関数の構造と日本語訳は以下のとおりです。
日本語訳：
・①をスタートとして、
・②行、③列だけ移動したところを基準に、
・④行×⑤列の範囲を選択する（④、⑤は1以上の数字が入ります）
なお、④と⑤は省略できます。省略した場合は両方とも「1」になります。
文字だけじゃすごくわかりにくいと思うので、3つほど具体例を出します。
１．②、③を指定
例：
=OFFSET（B4,2,1)
日本語訳：
・B4をスタートにして、
・2行、1列だけ移動したところを基準に
・1行×1列の範囲を選択する
（④と⑤は省略してるので、両方とも「1」になります。）
図で表すとこんな感じになります。
２．④、⑤を指定
例：
=OFFSET（B4,0,0,2,2)
日本語訳：
・B4をスタートとして、
・0行、0列だけ移動したところを基準に
（つまりB4から移動しない）
・2行×2列の範囲を選択する。
図で表すと下記のような感じになります。
３．②、③、④、⑤を指定
例：
=OFFSET（B4,2,1,2,2)
日本語訳：
・B4をスタートとして、
・2行、1列だけ移動したところを基準に
・2行×2列の範囲を選択する。
図で表すと下記のような感じになります。
いままでの2つを組み合わせただけなので、パッと見でわからない人はゆっくり考えてみてください。
ちなみに・・・・
今回の3番目の例のように、基準を動かす（②、③）のと、範囲を選択する（④、⑤）のを同時に指定することはほとんどありません。
わけわかんなくなっちゃいますしね。
一応こういうこともできるよ！という解説のために載せています。
これがOFFSET関数です。
可変範囲の指定
やっと、ここからが本番です。
2つの関数を組み合わせてプルダウンリストの範囲が変化するように範囲を指定します。
今回の結論はこんな感じでしたね。
結論の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関数によって、下記の図の青枠の範囲を指定したことになるので、リストもその通りに表示されます。
項目が増えた場合
では、項目が1つ増えたらどうなるかを見ていきます。
1つ項目が増えると、D列の項目数を数えているCOUNTA（D:D）が1つ増えます。
つまり「COUNTA（D:D）-1」は「7-1＝6」を表します。
なので、結論の「＝OFFSET（D4,0,0,COUNTA(D:D)-1,1)」が表す範囲は以下の通りになります。
どれだけ下に項目が追加されても、勝手にCOUNTA（D:D）が増えていくのでプルダウンリストの範囲も勝手に増えていくというスンポーです。
注意！
COUNTAは空白を数えません。なので、リストの途中で空白が入っちゃうと、OFFSET関数で表す範囲が一番下の項目まで伸びなくなります。
リストは空白無しで詰めて入力してくださいね。
まとめ
今回はCOUNTA関数とOFFSET関数を使った、可変範囲のプルダウンリストの作り方の解説でした！
今回はちょっと応用的な使い方なので、ぱっと見では難しいと思います。
でも、1つずつ理解していけば絶対に理解できるようになります。
ここで覚えてしまえば他にも応用が効く内容ですので、ぜひともマスターしてください！
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
エクセル初心者に向けた本を5冊厳選して紹介してみました。
エクセルのショートカットについて知りたい方はこちらの記事をどうぞ！
エクセルのショートカットの覚え方を知りたい人はこちらの記事をどうぞ！
コメント