上図のようにA列に項目が追加された時、プルダウンリストの項目も自動で更新されたら便利ですよね？
通常、プルダウンリストの項目は手動で追加する必要がありますが、OFFSET関数を使えばこのような自動追加が可能になります。
この記事では、OFFSET関数の使い方とプルダウンリストに項目を自動追加する方法を解説いたします。
初心者でも理解しやすいように、できるだけわかりやすく解説いたしますので、安心してご覧ください。
－著者情報－
名前：Utatane
Excel使用歴：15年以上
ひとこと：初心者だった当時に戻った気持ちになって解説いたします
※わかりやすさを重視しております。厳密には解釈が異なる場合がありますことをご了承ください。
実演パソコンのスペック（ソフトやバージョンによって結果が異なる場合があります）
- OS：Windows11 Home 64bit
- Excel：Microsoft Office Home and Business 2021
【Excelプルダウンリスト】OFFSET関数を使って項目を自動追加する方法
「データの入力規則」の「元の値」欄に以下の数式を書き込むことで実現できます。
▼テンプレ
=OFFSET($リスト項目データ一番上のセル,0,0,COUNTA($リスト項目データの列),1)
▼例
=OFFSET($A$1,0,0,COUNTA($A:$A),1)では１からプルダウンリストを作ってみましょう。
手順は以下の通りです。
- リスト項目のデータを準備
- プルダウンリストを設置したいセルを選択
- 上部メニューの「データ」→「データの入力規則」をクリック
- 入力値の種類をリストに変更
- 元の値にOFFSET関数が入った数式を書き込んでOKをクリック
１～４までは、別の記事で詳しく解説しておりますので、今回は割愛いたします。
知りたい方は下記記事をご参考ください。
5.元の値にOFFSET関数が入った数式を書き込んでOKをクリック
セルの指定を間違えないようにしましょう。
書き間違いが嫌な方は、下記の方法をお試しください。
まず、元の値欄に「=OFFSET(」を入力します。
右端の矢印「↑」をクリックして画面を縮小させます。
リスト項目データの一番上のセルをクリックします。
セルが絶対参照「$付き」で入力されたら、
元の値欄右端の矢印「↓」をクリックします。
【絶対参照とは？】
Excelのセル参照には「絶対参照」と「相対参照」があります。
通常の参照方法（「=A1」など）は相対参照となり、セルをコピーすることで参照先が変わるのが特徴です。
例えば「A2セル」に「=A1」と相対参照を書き込んだ場合、このA2セルを右隣りにコピーすると参照は「=B1」になり、下にコピーすると「=A2」と変化します。
一方、絶対参照はセルの参照位置を固定できるのが特徴です。
絶対参照にすると「$」が追加され、「=$A$1」となります。
例えば「A2セル」に「=$A$1」と絶対参照を書き込んだ場合、この「A2」セルをどこにコピーしても「=$A$1」から変わることがなく、固定されます。
今回はリスト項目データが右や下にズレると動作がおかしくなるため、絶対参照を採用しております。
参照セルに続けて、「,0,0,COUNTA(」を入力し、右端の矢印「↑」をクリックします。
A列を選択し、右端の矢印「↓」をクリックします。
（A付近をクリックすることで、A列全体を選択できます）
続きに「),1)」を入力して完了です。
では動作を確認してみましょう。
現在、下図のようなプルダウンリストができています。
A列最後尾に、「A6」を追加してみます。
すると、プルダウンリストの項目も自動追加されています。
最後尾ではなく途中に追加したり、削除してもキッチリ反映されています。
頻繁に項目が追加・削除される場合は非常に便利ですので、ぜひご活用ください。
OFFSET関数とCOUNTA関数について
今回使用した２つの関数について、簡単に解説いたします。
OFFSET関数は、指定したセルを基準に、指定した行数と列数だけ移動した位置にあるセルまたは範囲を参照する関数です。
要は、参照するセルをズラすことができる関数ということです。
関数の引数（引き渡す数）は以下のとおりです。
▼基本形
=OFFSET(基準のセル, ズラす行数, ズラす列数, [表示する行数], [表示する列数])
▼今回使用した例
=OFFSET($A$1,0,0,COUNTA($A:$A),1)今回使用した例を見てわかるかと思いますが、今回はズラす機能は使っていません。
使ったのは表示する行数で、ここに10という指定をした場合、A1を基準に10行分の項目を表示させることができます。
今回は、A列すべてにCOUNTA関数を絡めた値が最終値となります。
COUNTA関数は、空白ではないセルの数を返してくれます。
例えば、A列に項目が3つある場合は、「3」が返ってきますので、OFFSET関数の「表示する行数」は「3」となり、A1を基準として3行分の項目がプルダウンリストに表示されるというわけです。
COUNTA関数を使用しなかった場合、空白も含まれることになります。
A列を指定した場合は、一番下までの100万行ほどが含まれることになってしまいます。
最後に
OFFSET関数を用いたExcelプルダウンリストの項目自動更新ついて解説いたしました。
当ブログでは、VBAマクロやPythonなど、時間を生み出すプログラミング術を公開しております。
この記事がわかりやすいと感じた方は、他の記事も読んでいってください。
最後までお読みいただき、ありがとうございました。がんばってください！
