【Excel関数/VBA】データ手入力時、既入力済データをドロップダウンリストで選択して入力する

（１）やりたいこと
（２）実践：ドロップダウン用のリストを関数で作成
（３）実践： VBA Worksheet_Changeイベントでセルの値を分割
（４）終わりに

（１）やりたいこと

何でもよいのですが、例えば下図のようなExcelの手入力シートがあります。

部門コードと部門名、取引先コードと取引先名、商品コードと商品名はそれぞれ対になっており、新規の行を入力する際に下図のように過去に入力されたデータをドロップダウンリストから選択して入力できるようにします。ドロップダウンリストは各コードと名称を重複なく、コード順に表示して、選択（確定）した際にコードと名称を分割してそれぞれが各セルに入力されるようにします。

f:id:shusoshin:20211221203217g:plain

（２）実践：ドロップダウン用のリストを関数で作成

ここが一番のポイントとなります。ドロップダウンリスト用に参照する各リストをワークシート上に作るのですが、シートは入力シートと別シートでも構いません。今回はわかりやすいように入力シートの右側に作成します。

下図の通り、A列からH列までが入力データとなっています。部門のリストをK2セル以下に作成します。K1セルの「部門リスト」は文字列ですので無くても構いません。

K2セルに次の数式を入力します。(説明用に色を付けています）

=SORT(UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))&" "&UNIQUE(INDIRECT("C2:C"&COUNTA(C:C))))

数式の説明をします。

まず、INDIRECT("B2:B"&COUNTA(B:B))　の部分。

INDIRECT関数は、指定した文字列の参照を返す関数です。引数に”B2:B”という文字列が入っており、これにB列の入力データ数を返す、COUNTA(B:B) を＆でくっつけていますので、結果的に「B2セルからB列で入力がされているセルまで」が返されます。つまり、参照範囲が可変となります。B10セルまで入力されていれば、B2セルからB10セルを返し、B100セルまで入力されていればB2セルからB100までを返します。（途中に空白セルが入った場合はズレますが今回は考慮していません）

なぜ、B列全体を返す B:B とせずにINDIRECT関数を使うかというと、B列全体を参照すると不要な見出し行(1行目）や空白行が含まれてしまうこと（これはFILTER関数で除くことは可）、何より列全体をみにいくことでかなり重くなってしまうことが一番の理由です。

次に前述の式をUNIQUE関数で囲います。

UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))

UNIQUE関数は、一覧または範囲内の一意の一覧を返します。重複するデータが除かれたユニークなデータとなります。

次は単純に、半角スペースでつなぎます。

UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))&" "&

次のUNIQUE関数以下は前述の式と同じで列をC列に変えただけのものです。

UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))&" "&UNIQUE(INDIRECT("C2:C"&COUNTA(C:C)))

ここまでＢ、Ｃ列に入力された部門コードと部門名を半角スペースで繋いだ一意のリストとなります。最後にSORT関数で囲ってあげることでコードの昇順に並べ替えられた結果が返されることとなります。

=SORT(UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))&" "&UNIQUE(INDIRECT("C2:C"&COUNTA(C:C))))

出力結果は下図K2セル以下の通りです。この一覧は、スピルした配列数式として返されます。

f:id:shusoshin:20211221203226g:plain