【Excel】データの入力規則ドロップダウンリストでデータを抽出・絞り込む方法(2つの実例付き)

この記事では、データの入力規則(Data Validation)によるドロップダウンリストを使って、Excelのデータを抽出・絞り込む方法を解説します。通常、Microsoft Excelで特定のデータを取り出すにはフィルター機能を使用しますが、ドロップダウンリストでも同様のことが可能です。まずExcelのデータの入力規則でドロップダウンリストを作成し、その後、リストでの選択内容に応じて該当する行を自動的に抽出する手順をご紹介します。

この記事で使用している練習用ワークブックはダウンロードしてご利用いただけます。

データの入力規則ドロップダウンリストでフィルタリングする2つの方法

ここでは、複数の果物の地域別売上データが含まれたデータセットを例に考えます。まず、データセット内の地域(Area)の一覧からデータの入力規則によるドロップダウンリストを作成し、次にそのリストを使って果物の売上データを抽出します。

方法1:作業用列(ヘルパー列)を使ってドロップダウンリストの選択値で抽出する

この方法では、元のデータセットに3つの作業用列(ヘルパー列)を追加し、ドロップダウンの選択に基づいてデータを取り出します。ヘルパー数式を入力する前に、重複のない地域の一覧からドロップダウンリストを作成しておきましょう。以下の手順に従ってください。

手順:

• ドロップダウンリストを作成する前に、下図のように重複のない地域の一覧を作成します。
• 次に、ドロップダウンリストを配置したいセル(ここではセルH5)をクリックします。
• Excelリボンから データ > データツール > データの入力規則 > データの入力規則 の順にクリックします。
• データの入力規則ダイアログボックスが表示されます。設定タブを開き、入力値の種類で「リスト」を選択し、元の値に範囲を指定してOKを押します。
• OKを押すと、ドロップダウンリストが作成されます。

続いて作業用列に入力していきます。1つ目の作業用列(セルD5)に、ROWS関数を使った以下の数式を入力し、Enterキーを押します。その後、フィルハンドル(+)を使って数式を列全体にコピーしましょう。

=ROWS($A5:A$5)

すると、以下のような連番が出力されます。

次に、2つ目の作業用列(Helper 2)には、IF関数を使った以下の数式を入力します。

=IF(C5=$H$5,D5,"")

さらに、3つ目の作業用列(Helper 3)には、以下の数式を使用します。

=IFERROR(SMALL($E$5:$E$14,D5),"")

ここでは、SMALL関数が範囲E5:E14の中からk番目に小さい値を返します。その後、IFERROR関数により、SMALL関数の結果がエラーの場合は空白を返すようにしています。

それでは、例としてBaltimore(ボルチモア)地域の売上データをすべて抽出してみましょう。期待する結果を得るために、セルJ5に以下の数式を入力してEnterキーを押します。

=IFERROR(INDEX($A$5:$C$14,$F5,COLUMNS($J$5:J5)),"")

ここで、INDEX関数は行番号に基づいてデータを取り出し、COLUMNS関数は範囲$J$5:J5内の列番号を返します。最後にIFERROR関数が、結果がエラーの場合に空白を返します。

上記の数式を入力したら、フィルハンドルを右方向へドラッグして行全体のデータを取得します。

続けて、フィルハンドルを下方向へドラッグすれば、Baltimore地域の果物売上データがすべて表示されます。

この状態でドロップダウンリストからPhoenix(フェニックス)地域を選択すると、Phoenixに対応する行だけが以下のように抽出されます。

関連記事: データの入力規則でドロップダウンリストを作成する8つの方法

方法2:FILTER関数を使ってドロップダウンリストの選択に基づいてデータを抽出する(Excel 365)

Excel 365をお使いの場合は、FILTER関数を使ってデータを抽出できます。作業を始める前に、Ctrl + Tキーでデータ範囲をExcelテーブルに変換しておきました。これは、テーブルに新しいレコードを追加すると、ドロップダウンリストも新しく追加されたデータに合わせて自動更新されるためです。

操作を簡単にするため、作成したテーブルに名前を付けておきます(ここでは「Table4」)。

手順:

• まず、UNIQUE関数を使って地域の一意なリストを作成します。セルF5に以下の数式を入力してEnterキーを押してください。

=SORT(UNIQUE(Table4[Area]))

ここでは、SORT関数とUNIQUE関数を組み合わせて、地域データを並べ替えた一意なリストを生成しています。

数式を入力すると、並べ替えられた一意のデータが配列として(青色の枠線で)返されます。

• 次に、セルH5にドロップダウンリストを作成します。データ > データツール > データの入力規則 > データの入力規則 の順にクリックしてダイアログボックスを開き、入力値の種類で「リスト」を選択し、元の値欄に以下の数式を入力してOKを押します。

=F5#

ここで#記号は、セルF5の配列全体をドロップダウンリストのソースとして扱うことを意味します。

• OKを押すと、以下のようなドロップダウンリストが作成されます。
• 続いて、Long Beach(ロングビーチ)地域の果物売上データを抽出してみましょう。セルF11に以下の数式を入力してEnterキーを押します。

=FILTER(Table4,Table4[Area]=H5,"No Data Found")

• 最後に、FILTER数式を入力すると、Long Beach地域のすべての売上データが表示されます。ドロップダウンリストから地域を変更すれば、選択した地域に応じて該当する行が自動的に絞り込まれます。

関連記事: 別のセルの値に基づくExcelのデータの入力規則の設定方法

まとめ

この記事では、Excelのデータの入力規則によるドロップダウンリストを使ってデータを抽出する2つの方法を詳しく解説しました。これらの方法と説明が、皆さまの課題解決の一助になれば幸いです。ご不明な点があれば、お気軽にお問い合わせください。

関連記事

• カスタム数式を使った英数字のみのデータの入力規則の設定方法
• 複数条件に対応するカスタムデータの入力規則の適用方法(4つの例)
• ドロップダウンリストのオートコンプリート機能を実現する2つの方法
• テーブルからデータの入力規則リストを作成する3つの方法
• 複数選択に対応したデータの入力規則ドロップダウンリストの作成方法
• 1つのセルに複数のデータの入力規則を適用する方法(3つの例)