残業を減らす!Officeテクニック

テーブルを使ったドロップダウンリストは時代遅れに!? エクセルの新関数でもっと便利に

UNIQUE関数+SORT関数+FILTER関数でリストの管理はさらに楽になる!

 特定の項目を正確に入力するために「ドロップダウンリスト」がよく使われますよね。セルの横に表示される[▼]をクリックしてリストから項目を選択できる仕掛けです。何度も同じデータを入力する手間も省けますし、誤変換などによる入力ミスも防げます。複数人が入力するシートでも有効ですよね。

Excelの「ドロップダウンリスト」。登録された項目を選択できる

 ドロップダウンリストは、3、4項目であれば直接指定でも構いませんが、項目の追加・削除などの可能性を考えると、ドロップダウンリストから参照する専用のリストを別途作成しておくのが一般的でしょう。ただ、専用リストについて項目の追加・削除がある場合、今度は参照するセル範囲を指定し直すのが面倒です。

[データの入力規則]ダイアログボックス。[リスト]を選択して[元の値]に直接入力すればドロップダウンリストが作成できるがメンテナンスが面倒
ドロップダウンリストから参照するリストに項目の追加・削除がある場合、セル範囲を指定し直すのが面倒

 この問題の解決には「テーブル」の機能が定番です。「テーブル」の機能は目新しくありませんが、今回はUNIQUE関数を利用するテクニックを紹介します。

 また、SORT関数とFILTER関数と組み合わせるとさらに活用可能です。UNIQUE関数、SORT関数、FILTER関数の詳細は以前の記事を参考にしてください。

テーブル+UNIQUE関数でもっと便利になる

 まずは「ドロップダウンリスト」から参照する専用のリストを作成しておきます。ここでは別シートに「製品マスタ」の表がある前提です。表の範囲を選択して[挿入]タブの[テーブル]をクリックします。

表を「テーブル」に変換した状態。表の範囲を選択して[挿入]タブの[テーブル]をクリックする

 このまま「テーブル」の[製品コード]を参照するのが定番の操作ですが、今回はUNIQUE関数を組み合わせます。UNIQUE関数は、Excel 2021/2019/2016、およびMicrosoft 365のExcelで利用できます。セルに「=U」と入力した時に「UNIQUE」が表示できるかどうかで判断できます。

セルE2に「=U」と入力して「UNIQUE」を選択する
セルA2~A7までをドラックすると、自動的に「テーブル1[製品コード]」と変換される(テーブル名は環境により異なる)。「)」を入力して[Enter]キーを押す
セルA2~A7の一意の値を抽出できた。結果はスピルで表示される

 [発注表]シートに切り替えて、ドロップダウンリストを設定したいセル範囲を選択。[データ]タブの[データの入力規則]をクリックして、[データの入力規則]ダイアログボックスを表示します。

 [リスト]を選択して[元の値]の入力欄に「=製品マスタ!$E$2#」と入力します。ここがポイントです。UNIQUE関数の結果はスピルで抽出されます。先ほど[製品マスタ]シートのセルE2に入力したUNIQUE関数の結果はセルE2~E7に表示されており、この動的配列を参照する記号が最後の「#」になります。

 [OK]をクリックして[発注表]シートを確認すると、UNIQUE関数の結果を参照したドロップダウンリストが作成できています。

ドロップダウンリストを設定したいセル範囲を選択して[データ]タブの[データの入力規則]をクリックする
[リスト]を選択して[元の値]の入力欄に「=製品マスタ!$E$2#」と入力して[OK]をクリックする
ドロップダウンリストが作成された

SORT関数でドロップダウンリストを並べ替える

 現状では、UNIQUE関数を利用したメリットが感じられないので、よくあるケースで検証してみましょう。例えば、製品マスタに新製品「A003」を追加することを考えます。新規データはマスタテーブルの最終行に追加して以下のような状態になるはずです。また、[発注表]シートに切り替えてドロップダウンリストを確認すると、一番下に「A003」が追加されていることがわかります。

新製品「A003」を追加した状態。セルE2に入力したUNIQUE関数の結果も自動的に切り替わっている
ドロップダウンリストの一番下に「A003」が追加されている

 「A003」を追加したので「A002」のすぐ下に表示して欲しいですよね? そこでSORT関数を組み合わせます。先ほど入力した関数式を「=SORT(UNIQUE(テーブル1[製品コード]))」と書き換えます。

[製品マスタ]シートのセルE2の関数式を「=SORT(UNIQUE(テーブル1[製品コード]))」と書き換える
結果の並び順が変わった
[発注表]シートを確認すると、ドロップダウンリストの内容も変更されている

FILTER関数と組み合わせる

 マスタテーブルを[製品コード]で並べ替えれば、UNIQUE関数もSORT関数も不要なのでは? という声もありそうなので、次のような場合はどうでしょうか。[旧製品]列で旧製品のフラグを判別できるようにしました。ドロップダウンリストに表示させるデータは現行製品のみです。

「旧製品」に「○」が付いている製品はドロップダウンリストに表示させたくない

 FILTER関数は名前の通り、データをフィルター(絞り込み)できる関数です。ここでは『[旧製品]列に「○」が付いていたらドロップダウンリストに表示させない』という条件で絞り込みます。

[製品マスタ]シートのセルE2の関数式を「=SORT(FILTER(UNIQUE(テーブル1[製品コード]),テーブル1[旧製品]=""))」と書き換える
現行品のみが抽出された
[発注表]シートを確認すると、ドロップダウンリストの内容も変更されている