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

Excelで使用済みの項目が非表示になるドロップダウンリストを作る方法

1回選んだ項目はドロップダウンリストに入れたくない!

 ドロップダウンリストは、一覧から項目を選択・入力できることで操作性が向上し、誤入力の防止にも役立ちます。所属部署や製品カテゴリー、会員ランクなど、表内にあるデータに対して、決まった項目を割り当てて分類する用途が一般的です。

 リストに表示する項目が多い場合は、以前に紹介した大・中・小項目が連動するドロップダウンリストで階層ごとに絞り込む仕掛けを利用することもあるでしょう。

大・中・小項目で項目を絞り込むドロップダウンリスト

 今回は応用的なケースとして、選択済みの項目をリストに非表示にするテクニックを紹介します。例えば、あるプロジェクトに割り当てた人は、ほかのプロジェクトの担当候補には表示させたくない場合や、グループ分けで同一人物を選択できないようにしたい場合などに使えます。

一般的なドロップダウンリスト

 ここでは、社員研修の参加者をグループ分けすることを想定します。例えば「青山 翔太」を1回選んだら、ほかのドロップダウンリストには表示させないようにしたいと思います。ただ、[氏名]列を参照してドロップダウンリストを作成すると、以下のように繰り返し選択可能な状態になってしまいます。

[氏名]列(セルB3~B22)を参照して、セルF3~J7に入力規則を設定すると、セルB3~B22の値がドロップダウンリストとして表示される
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))」としても同じ結果となります。

任意のセルに「COUNTIF(F3:J7,B3:B22)<1」と入力する
現状はセルF3~J7が空白(重複がない)なので、すべて「TRUE」と表示される

 後は、FILTER関数と組み合わせます。FILTER関数の構文は以下の通り。引数[配列]は[氏名]列のセルB3~B22となり、[含む]に上記のCOUNTIF関数の数式を指定します。

=FILTER(B3:B22,COUNTIF(F3:J7,B3:B22)<1)

 数式の修正後に任意の人物を選択すると、スピルで表示された結果が変化することを確認できます。

FILTER関数の構文。引数[配列]には対象のセル範囲を指定する。[含む]には[配列]から抽出する条件を指定する。[空の場合]は条件に一致するデータがない場合に表示する値を指定する(省略可能)。
セルL3の数式を「=FILTER(B3:B22,COUNTIF(F3:J7,B3:B22)<1)」と修正する
[氏名]列の一覧がスピルで表示される
任意の人物を選択すると、スピルの結果が変化することがわかる

スピルの結果を参照する

 スピルで表示されている一覧をドロップダウンリストの参照元として指定すれば完成です。入力規則の設定されているセル範囲を選択して、[データ]タブにある[データの入力規則]をクリック。[元の値]のセル参照を書き換えましょう。

 その際、「=$L$3:$L$22」と指定するのではなく、“スピル範囲演算子”と呼ばれる「#」を使って「=$L$3#」と指定します。これはセルに入力された数式のスピルの結果を参照するという意味で、結果が変化した場合にも対応できます。

入力規則を設定するセル範囲を選択しておき、[データ]タブにある[データの入力規則]をクリックする。[元の値]に設定されている「=$B$3:$B$22」を修正する
「=$L$3#」と修正して、[OK]をクリックする
「青山 翔太」は選択済みなので、ほかのドロップダウンリストには表示されない。参照元のL列からも「青山 翔太」が非表示になっている
次のドロップダウンリストも同様。「青山 翔太」と「田中 美咲」は選択済みなので表示されない

 この例であれば、単純に作業用の氏名一覧から切り取り&貼り付けする手もありますが、スピル範囲演算子は参照元のデータが別のシートにある場合などには重宝するはずです。