【保存版】Excelでセルの値に基づいてリストを作成する6つの方法

Excelでは、リストの中から特定の条件に合う値を抽出したり、条件に基づいて値を判定したりしたい場面がよくあります。たとえば、各タスクに担当者名が割り振られたタスク計画があり、「特定の担当者が担当しているタスク名をすべて一覧表示したい」といったケースです。このように、Excelにはセルの値をもとにリストを自動生成するための機能が多数用意されています。

本記事では、セルの値に基づいてリストを作成する6つの方法を、実際の操作手順と数式の解説つきで詳しく紹介します。

6つの方法：セルの値に基づいてリストを作成する

方法1：オートフィルでセルの値に基づくリストを作成する

まず、プロジェクト担当者名のリストを例に見てみましょう。各プロジェクトの担当者は「Project_Number_Name_Serial」という形式で登録されています。ここでの課題は、プロジェクト名から該当する担当者をすべて抽出することです。

ステップ1： セルD17に以下の数式を入力し、Enterキーを押します。

=IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"")

数式の解説：

• MATCH($C$16,$B$3:$D$3,0):入力されたプロジェクト名とデータセットを照合します。完全一致のみを対象とします。
• ROW(B2:D11):データセットの行番号をカウントします。
• INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)):一致したプロジェクトの担当者名を検索します。データセット内に該当データがない場合は#N/Aエラーが返されます。
• 最後のIFERRORは、あらゆる種類のエラーを処理するために使用しています。

ステップ2: ドロップダウンリストから任意のプロジェクト名を選択します。

ステップ3: すべての担当者名が一覧で表示されます。

方法2:FILTER関数で特定のセルの値に基づいて行を抽出する

続いて、別のアプローチで担当者を検索する方法を紹介します。このデータセットでは、1人の担当者が複数のプロジェクトに参加できる構成になっています。今度の課題は、担当者名からプロジェクト名を検索することです。メインのデータセットは以下のようになっています。

ステップ1: セルG6に以下の数式を入力し、Enterキーを押します。

=FILTER(B4:B16, G5=C4:C16)

数式の解説：

• FILTER関数のB4:B16は、データを抽出する対象範囲です。
• セルG5に入力された名前が、名前の範囲C4:C16と照合されます。
• FILTER関数についてさらに詳しく知りたい方は、関連記事をご参照ください。

ステップ2: セルG5に任意の名前を入力し、Enterキーを押します。

方法3:最初のドロップダウンの変更を禁止する

複数の食品リストがあるとします。それぞれのリスト内容は互いに異なり、選択される食品項目は対応する有効なリスト上のものである必要があります。別のワークシートで、食品を種類ごとに選択していく流れを考えます。

ここで重要なのは、列Bで食品の種類を選択したら、列C(項目)にはその種類に属する項目しか選べないようにすることです。

ステップ1: 食品項目のセルを選択し、「データの入力規則」を開きます。

ステップ2: 「元の値」欄に以下の数式を入力します。

=IF(B4="",Foods, INDIRECT("FakeRange"))

ステップ3: 警告ダイアログが表示されるので、「はい」をクリックします。

ステップ4: 食品の種類を選択した後、対応する項目を選択します。

ステップ5: 一度食品の種類と項目を入力すると、食品項目を変更できなくなります。これにより、誤った組み合わせ(ミスマッチ)を防ぐことができます。

方法4:条件に基づいて重複のないリストを作成する

重複しない値のリストを取得したい場合にも、Excelには複数の方法があります。ここでは、方法2と同じデータセット(重複値を含む)を使用し、数式だけでユニークなリストを抽出することを目標にします。

ステップ1: セルG6に以下の数式を入力します。

=UNIQUE(FILTER(B4:B22,C4:C22=G5))

数式の解説：

• FILTER(B4:B22,C4:C22=G5):方法2と同様に、データセットから一致するすべての名前を抽出します。重複する一致がある場合も、FILTER関数はそのまま含めてしまいます。
• FILTER関数が返す重複値を取り除くために、UNIQUE関数を組み合わせています。この関数により、一致したデータからすべての重複値が除去されます。

ステップ2: セルG5に任意の名前を入力し、Enterキーを押します。

方法5:配列数式で1つの列の条件を満たす行をすべて抽出する

ID、ブランド、モデル、単価を含む製品データセットがあるとします。ここでの課題は、セルH5とH7に入力したブランド名と一致する行をすべて抽出することです。

ステップ1: セルB19に以下の数式を入力し、Ctrl + Shift + Enterキーを押します。その後、数式を表全体にコピーしてください。

=INDEX($B$4:$E$15, SMALL(IF(COUNTIF($H$5:$H$7,$C$4:$C$15), MATCH(ROW($B$4:$E$15), ROW($B$4:$E$15)), ""), ROWS(B19:$B$19)), COLUMNS($B$3:B3))

ステップ2: セルH5とH7にブランド名を入力し、Enterキーを押します。

方法6:依存型(連動)ドロップダウンリストを作成する

Excelのドロップダウンリストは、データ入力フォームやダッシュボードを作成する際に非常に便利な機能です。セル内にアイテムの一覧をドロップダウン形式で表示でき、ユーザーはそこから選択するだけで済みます。氏名、製品、地域など、何度も入力する必要があるリストがあるときに特に役立ちます。

ここでは、3つの異なる食品リストを使って、依存型(連動)ドロップダウンリストを作成してみましょう。

ステップ1: 「データの入力規則」を開きます。

ステップ2: 「データの入力規則」ウィンドウで、「入力値の種類」を「リスト」に設定し、「元の値」を以下のように選択します。

ステップ3: 「食品の種類」列にドロップダウンリストが表示されます。

ステップ4: 次に、すべてのデータセットを選択し、「数式」タブの「選択範囲から作成」をクリックします。

ステップ5: ダイアログが表示されるので、「最上行」にチェックを入れ、「OK」を押します。

ステップ6: セルD14に移動して「データの入力規則」を開きます。「入力値の種類」が「リスト」になっていることを確認し、「元の値」に以下の数式を入力して「OK」を押します。

=INDIRECT(B14)

ステップ7: 警告ダイアログが表示されるので、「はい」を押します。

ステップ8: 最初のドロップダウンリストから任意の食品の種類を選択すると、隣のドロップダウンリストに関連する項目だけが表示されます。

ステップ9: 最終的な完成イメージは以下の通りです。

覚えておきたい注意点

まとめ

今回は、Excelでセルの値に基づいてリストを作成する6つの方法を紹介しました。すべての手法を具体例とともに解説しましたが、組み合わせ方によってはさらに多くの応用が可能です。使用した関数(INDEX、MATCH、FILTER、UNIQUE、INDIRECTなど)の基本的な仕組みについても触れましたので、ぜひ自分の業務に合わせて活用してみてください。他におすすめの方法があれば、ぜひコメントでお知らせください。

関連記事

• Excelワークシート間でデータを自動的に転送する方法
• Excelで別シートから自動入力する方法
• Excelでデータの最終行まで下方向にフィルする方法(3つのクイックテクニック)