Office
 Computer >> コンピューター >  >> ソフトウェア >> Office

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

Excelでデータの入力規則を扱っていると、同じ項目を二重に割り当ててしまうのを防ぐために、ドロップダウンリストから使用済みの項目を削除したい場面があります。たとえば、複数の従業員を異なるシフトに割り振る際、同一人物を複数回割り当てたくないケースが挙げられます。また、試合で選手を各ポジションに配置する際も、一人の選手を一つのポジションにしか割り当てられないという状況があります。こうした場合、ドロップダウンリストから一度選択した名前が自動的に消えてくれると非常に便利です。本記事では、Excelのドロップダウンリストから使用済み項目を削除する方法をわかりやすく解説します。

Excelでドロップダウンリストから使用済み項目を削除する2つの簡単な方法

ここでは、ある組織の従業員名が入力されたワークシートを例に考えてみましょう。各従業員をそれぞれ異なる勤務シフトに割り当てる必要があり、同じ従業員を重複して割り当てることはできません。そのため、従業員名のドロップダウンリストを用意し、一度割り当てられた従業員は自動的にリストから除外される仕組みを作ります。このワークシートを使って、2つの簡単な方法でドロップダウンリストから使用済み項目を削除する手順をご紹介します。下の画像は、使用済み項目が除外されたドロップダウンリストを持つワークシートの例です。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

方法1：作業列を活用してドロップダウンリストから使用済み項目を削除する

ドロップダウンリストから使用済み項目を除外する最も基本的な方法は、2つの作業列（ヘルパー列）を使うことです。具体的な手順を見ていきましょう。

ステップ1：行番号を抽出する列を作成する

  • まず、「行番号」列のセルC5に以下の数式を入力します。
=IF(COUNTIF($F$5:$F$14,B5)>=1,"",ROW())

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

数式の解説：

  • IF関数が論理式 COUNTIF($F$5:$F$14,B5)>=1 を判定します。
  • COUNTIF関数により、セルB5の値が絶対参照範囲$F$5:$F$14内に1回以上出現しているかどうかを調べます。
  • セルB5の値が範囲$F$5:$F$14内に1回以上存在する場合、IF関数は空文字列（""）を返します。
  • 存在しない場合は、ROW関数によってセルB5の行番号を返します。
  • Enterキーを押すと、セルC5にセルB5の行番号が表示されます。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • 次に、セルC5のフィルハンドルを下方向へドラッグし、「行番号」列の残りのセルにも数式を適用します。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • これで「従業員」列のすべてのセルに対する行番号が取得できました。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

ステップ2：未使用の従業員名を抽出する列を作成する

  • 続いて、「従業員名」列のセルD5に以下の数式を入力します。
=IF(ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14),"",INDEX(B:B,SMALL(C$5:C$14,1+ROW(B5)-ROW(B$5))))

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

数式の解説：

  • IF関数が論理式 ROW(B5)-ROW(B$5)+1>COUNT(C$5:C$14) を判定します。
  • COUNT関数は、絶対参照範囲C$5:C$14内の数値が入力されたセルの個数を数えます。
  • SMALL関数は、絶対参照範囲C$5:C$14の中からk番目に小さい値を求めます。ここでのkは 1+ROW(B5)-ROW(B$5) によって決まります。
  • INDEX関数は、SMALL関数で求めたk番目に小さい値を行番号（row_num）として受け取り、対応するセルの参照を返します。
  • Enterキーを押すと、セルD5にセルB5の従業員名が表示されます。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • セルD5のフィルハンドルを下方向へドラッグし、「従業員名」列の残りのセルにも数式を適用します。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • これで「従業員」列にすべての従業員名が表示されました。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

ステップ3：名前の定義を作成する

  • 次に、リボンの「数式」タブから「名前の定義」をクリックします。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • 新しいダイアログボックスが開きます。「名前」欄にEmployeeと入力します。
  • 続いて、「参照範囲」欄に以下の数式を入力します。
=Helper!$B$4:$D$14=OFFSET(Helper!$D$5,0,0, COUNTA(Helper!$D$5:$D$14)-COUNTBLANK(Helper!$D$5:$D$14),1)

数式の解説：

  • Helperは、現在作業しているワークシート名です。
  • COUNTA関数は、絶対参照範囲$D$5:$D$14内の値が入力されているセルの個数を数えます。
  • COUNTBLANK関数は、絶対参照範囲$D$5:$D$14内の空白セルの数を数えます。
  • 最後に「OK」をクリックします。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

ステップ4：データの入力規則でドロップダウンリストを作成する

  • まず、「ドロップダウン」列のすべてのセルを選択します。
  • リボンの「データ」タブにある「データの入力規則」のドロップダウンをクリックします。
  • ドロップダウンメニューから「データの入力規則」を選択します。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • 「データの入力規則」ダイアログボックスが開いたら、「入力値の種類」のドロップダウンから「リスト」を選択します。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • 「元の値」欄に=Employeeと入力します。
  • 「OK」をクリックします。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • これで「ドロップダウン」列の各セルにドロップダウンリストが表示されます。
  • セルF5のドロップダウンリストからGus Fringを選択してみましょう。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • 次に2つ目のドロップダウンをクリックすると、Gus Fringの名前がリストに含まれていないことが確認できます。すでに使用済みの項目なので、以降のドロップダウンリストからは自動的に除外されるのです。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • さらに他のドロップダウンリストから名前を選択すると、選択済みの項目が後続のリストから順に除外されていくのがわかります。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

関連記事： Excelで複数列にドロップダウンリストを作成する方法（3選）

あわせて読みたい記事：

  • Excelで複数選択可能なリストボックスを作成する方法
  • Excelで選択内容に応じて変化するドロップダウンリストの作成方法
  • Excelでセルの値とドロップダウンリストを連動させる方法（5選）
  • Excelでの条件付きドロップダウンリスト（作成・並べ替え・活用）
  • Excelで動的な連動型ドロップダウンリストを作成する方法

方法2：FILTER関数とCOUNTIF関数を組み合わせて使用済み項目を削除する

Microsoft Office 365をお使いの場合は、Excel 365限定のFILTER関数を使うのが最も簡単な方法です。以下の手順に従いましょう。

ステップ1：FILTER関数で未使用の名前を抽出する

  • まず、セルC5に以下の数式を入力します。
=FILTER(B5:B14, COUNTIF(E5:E14,B5:B14)=0)

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

数式の解説：

  • FILTER関数は、条件 COUNTIF(E5:E14,B5:B14)=0 に基づいて範囲B5:B14をフィルタリングします。
  • COUNTIF関数は、範囲B5:B14の各値が範囲E5:E14内に存在するかどうかを判定します。
  • Enterキーを押すと、「従業員」列のすべての従業員名がスピル表示されます。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

ステップ2：データの入力規則でドロップダウンリストを作成する

  • まず、「ドロップダウン」列のすべてのセルを選択します。
  • リボンの「データ」タブにある「データの入力規則」のドロップダウンをクリックします。
  • ドロップダウンメニューから「データの入力規則」を選択します。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • 「データの入力規則」ダイアログボックスが開いたら、「入力値の種類」から「リスト」を選択します。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • 「元の値」欄に$C$5:$C$14と入力します。なお、代わりに=$C$5#と入力してスピル範囲を参照することもできます。
  • 「OK」をクリックします。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • これで「ドロップダウン」列の各セルにドロップダウンリストが表示されます。
  • セルF5のドロップダウンリストからStuart Bloomを選択してみましょう。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • 次に2つ目のドロップダウンをクリックすると、Stuart Bloomの名前がリストに含まれていないことが確認できます。使用済みの項目は、以降のドロップダウンリストから自動的に除外されます。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

  • さらに他のドロップダウンリストから名前を選択すると、選択済みの項目が後続のリストから順に除外されていくのがわかります。

Excelのドロップダウンリストから使用済み項目を自動的に削除する2つの方法

関連記事： Excelで選択内容に基づいてデータを抽出するドロップダウンフィルターの作成方法

注意点

🎯 FILTER関数は現在Excel 365のみで利用できる関数です。PCにExcel 365がインストールされていない環境では、この方法は使用できませんのでご注意ください。

🎯 Excelで重複しない一意の値によるドロップダウンリストの作成方法については、こちらの記事もぜひご覧ください。

まとめ

本記事では、Excelのドロップダウンリストから使用済み項目を削除する方法を2通り解説しました。これらのテクニックを習得すれば、重複選択を防ぎながら効率的にリストを運用できるようになります。本記事についてご不明な点やご要望がありましたら、ぜひコメント欄でお知らせください。それでは、快適なExcelライフを！

関連記事

  • Excelで検索可能なドロップダウンリストを作成する方法（2選）
  • Excelで色付きのドロップダウンリストを作成する方法（2選）
  • Excelのドロップダウンリストが動作しないときの原因と対策（8つの事例）
  • Excelでセル範囲からリストを作成する方法（3選）
  • Excelのドロップダウンリストを自動更新する方法（3選）
  1. 【Excel】テーブルからデータの入力規則リストを動的に作成する3つの方法

    Excelのテーブルは、最下部に新しい要素を挿入すると自動的に拡張される動的な特性を持っています。この優れた機能があるため、テーブルはExcelユーザーのツールボックスの中でも最も有効な機能の一つとされています。データの入力規則(データバリデーション)リストを活用すれば、テーブルのデータに入力ミスや誤りが混入するのを防ぐことができます。ただし、テーブルに新しいデータを追加した際には、入力規則リスト側も更新が必要になる点に注意しましょう。本記事では、テーブルから動的なExcelデータ入力規則リストを作成する方法を詳しく解説します。 テーブルからデータの入力規則リストを作成する3つの効果的な方法

  2. Excelでドロップダウンリスト付きのデータ入力フォームを作成する2つの方法

    Microsoft Excelでは、データ入力フォームや計算フォームなど、さまざまな種類のフォームを作成できます。こうしたフォームを活用すれば、データ入力が格段に楽になり、作業時間の大幅な節約にもつながります。また、Excelには「ドロップダウンリスト」という便利な機能もあります。限られた値を何度も手入力するのは面倒ですが、ドロップダウンリストを使えば、リストから選ぶだけで簡単に値を入力できます。この記事では、Excelでドロップダウンリスト付きのデータ入力フォームを作成する方法を、具体的な操作画面とともにわかりやすく解説します。 Excelでドロップダウンリスト付きデータ入力フォームを