「一致するデータ」をすべて取り出したい！ VLOOKUP関数ではできない抽出テク

検索値に一致するデータを“すべて”取り出すには？

　一覧からデータを検索して対応する値を取り出したい時、VLOOKUP関数やXLOOKUP関数の利用を検討しますよね。商品名に対応する単価を取り出したい場合などには有効です。しかし、対応する値が複数ある場合はどうしましょう？

　以下はある店舗の売り上げデータです。例えば、この一覧から「ブレンドコーヒー」のカテゴリーに属する商品名を“すべて”取り出したい場合、VLOOKUP関数やXLOOKUP関数は使えません。

［日付］［カテゴリー］［商品名］［価格］［売上］のフィールドが並ぶ売上データ

　商品別の売上を集計したい場合も同様です。該当する最初の1行が抽出できても意味がありませんよね。このようなケースでは、仕方なくフィルターを使う人もいるのではないでしょうか。

データをまとめて抽出できる名関数「FILTER」フィルターと似ているけれど、関数ならでは便利さが

　そんな時は、Excel 2021やMicrosoft 365のExcelで使えるようになったFILTER関数を試してみてください。

　指定した条件に該当するデータをまとめて抽出できます。構文は以下の通り。［配列］と［含む］の2つの引数を指定すれば動作します。

FILTER関数の構文。引数［配列］には対象のセル範囲を指定する。［含む］には［配列］から抽出する条件を指定する。［空の場合］は条件に一致するデータがない場合に表示する値を指定する（省略可能）。

　フィルターと似たような動作からあまり注目されませんが、便利に使える名関数です。今回は、FILTER関数の活用例をいくつか紹介します。

カテゴリーに一致するデータを抽出する

　冒頭に紹介したカテゴリーに属するデータを抽出してみましょう。

　単純にFILTER関数を使うと、一致するデータを“すべて”抽出できます。しかし、重複しているデータは不要なので、UNIQUE関数を組み合わせます。

　なお、ここでは対象のデータを「注文表」という名前のテーブルに変換してあるので、数式中の引数は構造化参照で表現されます。

FILTER関数を利用した数式は「=FILTER(注文表[商品名],注文表[カテゴリー]=H2)」となる。「注文表[商品名]」は抽出したいセル範囲。「注文表[カテゴリー]=H2」は、カテゴリー列のうちセルH2（ブレンドコーヒー）と一致する、という条件となる

結果はスピルで表示される

重複を取り除くためにUNIQUE関数を組み合わせる。引数は先ほど入力した数式そのままとなるので、先頭に「UNIQUE(」を追加して、末尾に「)」を追加すればいい

重複が取り除かれて、「ブレンドコーヒー」のカテゴリーに属する商品が取り出せた

TRANSPOSE関数で横に並べる

　結果を横方向に並べたいときはどうすればいいでしょうか？ コピーして貼り付け時に［行/列の入れ替え］を選択する手もありますが、TRANSPOSE関数を組み合わせれば、そのまま横に並びます。引数は先ほどの数式そのままです。

　このようにしておけば、カテゴリー名の一覧を用意し、数式をコピーすることで、重複のない横方向のリストを一気に作成する、といったこともできます。

縦横を入れ替えるにはTRANSPOSE関数が便利。先ほどの数式にさらに組み合わせるだけだ

結果は横方向にスピルで表示される

例えば、カテゴリー名の一覧を用意しておき数式をコピーすれば、重複のない横方向のリストを一気に作成できる

カテゴリーごとに売上を集計する

　SUM関数と組み合わせることで集計することも可能です。FILTER関数の引数［配列］に抽出対象の「売上」列、［含む］にはカテゴリーを絞り込む条件を指定します。

FILTER関数の部分は「FILTER(注文表[売上],注文表[カテゴリー]=H2)」となる。前述の数式との違いは「FILTER(注文表[売上]」のみ。全体をSUM関数で囲む

ブレンドコーヒーの売上集計が表示される

数式をコピーすれば、カテゴリーごとの売上を集計できる

　こうすると、引数［含む］に指定する条件を変更するだけで、商品名ごとの集計も可能です。条件を指定した集計では、SUMIFS関数が使われることが多いですが、FILTER関数でも同じように処理できます。

引数［含む］の条件を変えれば、商品名ごとの集計も可能