残業を減らす!Officeテクニック
Excelで使用済みの項目が非表示になるドロップダウンリストを作る方法
2025年3月31日 06:55
ドロップダウンリストは、一覧から項目を選択・入力できることで操作性が向上し、誤入力の防止にも役立ちます。所属部署や製品カテゴリー、会員ランクなど、表内にあるデータに対して、決まった項目を割り当てて分類する用途が一般的です。
リストに表示する項目が多い場合は、以前に紹介した大・中・小項目が連動するドロップダウンリストで階層ごとに絞り込む仕掛けを利用することもあるでしょう。
今回は応用的なケースとして、選択済みの項目をリストに非表示にするテクニックを紹介します。例えば、あるプロジェクトに割り当てた人は、ほかのプロジェクトの担当候補には表示させたくない場合や、グループ分けで同一人物を選択できないようにしたい場合などに使えます。
一般的なドロップダウンリスト
ここでは、社員研修の参加者をグループ分けすることを想定します。例えば「青山 翔太」を1回選んだら、ほかのドロップダウンリストには表示させないようにしたいと思います。ただ、[氏名]列を参照してドロップダウンリストを作成すると、以下のように繰り返し選択可能な状態になってしまいます。
セルB3~B22の値を参照してドロップダウンリストを作成しているので当然の挙動ですよね。つまり、「1回選んだら」という条件の判定方法がポイントです。
元データを参照するリストを用意する
1回選んだかどうかは、[氏名]列(セルB3~B22)と入力規則を設定するセル範囲(セルF3~J7)を比較して、重複があるかどうかをチェックすれば判定できます。定番のCOUNTIF関数を使いましょう。以下のような数式になります。
=COUNTIF(F3:J7,B3:B22)<1
重複がない、つまりカウントが「1未満」であることが条件となります。引数[範囲]と[検索条件]にセル範囲を指定すると、結果はスピルで表示されます。現状ではセルF3~J7は空白なので、すべて「TRUE」と表示されます。条件が満たされていないことを判定するNOT関数を組み合わせて、「NOT(COUNTIF(F3:J7,B3:B22))」としても同じ結果となります。
後は、FILTER関数と組み合わせます。FILTER関数の構文は以下の通り。引数[配列]は[氏名]列のセルB3~B22となり、[含む]に上記のCOUNTIF関数の数式を指定します。
=FILTER(B3:B22,COUNTIF(F3:J7,B3:B22)<1)
数式の修正後に任意の人物を選択すると、スピルで表示された結果が変化することを確認できます。
スピルの結果を参照する
スピルで表示されている一覧をドロップダウンリストの参照元として指定すれば完成です。入力規則の設定されているセル範囲を選択して、[データ]タブにある[データの入力規則]をクリック。[元の値]のセル参照を書き換えましょう。
その際、「=$L$3:$L$22」と指定するのではなく、“スピル範囲演算子”と呼ばれる「#」を使って「=$L$3#」と指定します。これはセルに入力された数式のスピルの結果を参照するという意味で、結果が変化した場合にも対応できます。
この例であれば、単純に作業用の氏名一覧から切り取り&貼り付けする手もありますが、スピル範囲演算子は参照元のデータが別のシートにある場合などには重宝するはずです。