残業を減らす！Officeテクニック
テーブルを使ったドロップダウンリストは時代遅れに!? エクセルの新関数でもっと便利に
UNIQUE関数＋SORT関数＋FILTER関数でリストの管理はさらに楽になる！
2022年4月4日 06:55
特定の項目を正確に入力するために「ドロップダウンリスト」がよく使われますよね。セルの横に表示される［▼］をクリックしてリストから項目を選択できる仕掛けです。何度も同じデータを入力する手間も省けますし、誤変換などによる入力ミスも防げます。複数人が入力するシートでも有効ですよね。
ドロップダウンリストは、3、4項目であれば直接指定でも構いませんが、項目の追加・削除などの可能性を考えると、ドロップダウンリストから参照する専用のリストを別途作成しておくのが一般的でしょう。ただ、専用リストについて項目の追加・削除がある場合、今度は参照するセル範囲を指定し直すのが面倒です。
この問題の解決には「テーブル」の機能が定番です。「テーブル」の機能は目新しくありませんが、今回はUNIQUE関数を利用するテクニックを紹介します。
また、SORT関数とFILTER関数と組み合わせるとさらに活用可能です。UNIQUE関数、SORT関数、FILTER関数の詳細は以前の記事を参考にしてください。
テーブル＋UNIQUE関数でもっと便利になる
まずは「ドロップダウンリスト」から参照する専用のリストを作成しておきます。ここでは別シートに「製品マスタ」の表がある前提です。表の範囲を選択して［挿入］タブの［テーブル］をクリックします。
このまま「テーブル」の［製品コード］を参照するのが定番の操作ですが、今回はUNIQUE関数を組み合わせます。UNIQUE関数は、Excel 2021およびMicrosoft 365のExcelで利用できます。セルに「=U」と入力した時に「UNIQUE」が表示できるかどうかで判断できます。
［発注表］シートに切り替えて、ドロップダウンリストを設定したいセル範囲を選択。［データ］タブの［データの入力規則］をクリックして、［データの入力規則］ダイアログボックスを表示します。
［リスト］を選択して［元の値］の入力欄に「=製品マスタ!$E$2#」と入力します。ここがポイントです。UNIQUE関数の結果はスピルで抽出されます。先ほど［製品マスタ］シートのセルE2に入力したUNIQUE関数の結果はセルE2～E7に表示されており、この動的配列を参照する記号が最後の「#」になります。
［OK］をクリックして［発注表］シートを確認すると、UNIQUE関数の結果を参照したドロップダウンリストが作成できています。
SORT関数でドロップダウンリストを並べ替える
現状では、UNIQUE関数を利用したメリットが感じられないので、よくあるケースで検証してみましょう。例えば、製品マスタに新製品「A003」を追加することを考えます。新規データはマスタテーブルの最終行に追加して以下のような状態になるはずです。また、［発注表］シートに切り替えてドロップダウンリストを確認すると、一番下に「A003」が追加されていることがわかります。
「A003」を追加したので「A002」のすぐ下に表示して欲しいですよね？ そこでSORT関数を組み合わせます。先ほど入力した関数式を「=SORT(UNIQUE(テーブル1[製品コード]))」と書き換えます。
FILTER関数と組み合わせる
マスタテーブルを［製品コード］で並べ替えれば、UNIQUE関数もSORT関数も不要なのでは？ という声もありそうなので、次のような場合はどうでしょうか。［旧製品］列で旧製品のフラグを判別できるようにしました。ドロップダウンリストに表示させるデータは現行製品のみです。
FILTER関数は名前の通り、データをフィルター（絞り込み）できる関数です。ここでは『［旧製品］列に「○」が付いていたらドロップダウンリストに表示させない』という条件で絞り込みます。
