【Excel】複数条件をすべて満たすデータを探したい？ SEARCHやFIND関数は使っちゃダメ！

指定した条件をすべて満たす会場を探す

　一覧表から条件に合うデータを探す作業は、日常的な事務処理でもよくあります。例えば、必要な設備を備えた会場を探したり、対応可能なサービスから取引先を絞り込んだり、複数の条件を満たす商品や施設を選んだりするケースです。

　条件が1つなら簡単ですが、「AとBとCをすべて満たす」となると確認は少し面倒です。さらに、「Wi-Fi,プロジェクター,音響設備」のように、複数の項目が1つのセルにまとめて入力されている表も珍しくありません。Webサイトや別のシステムからコピー＆ペーストしたデータでは、このような形式になっていることもあります。

　目視だけで確認するのは、避けたいものです。例えば、チェックを付けた条件をすべて満たす会場を塗り分けられたら便利ですよね？

必須設備を指定したら、強調表示できるようにしたい！

　今回は、イベント会場の一覧を例に、必要な条件をすべて満たすデータを数式で判定する方法を紹介します。ポイントは、セル内の文字列をそのまま検索するのではなく、個々の項目に分解してから照合することです。

TEXTSPLIT関数で文字列を分解する

　会場一覧の「利用可能な設備」列に、「Wi-Fi,プロジェクター,スクリーン,音響設備」のような文字列が入力されているとします。特定の設備が含まれているかを判定するだけなら、SEARCH関数やFIND関数で検索する方法を思い浮かべるかもしれません。しかし、この方法では意図しない一致が起こる可能性があります。

　例えば、「マイク」を探した時に、設備一覧に「マイクスタンド」しか登録されていなくても、「マイク」という文字列自体は含まれているため一致します。「マイク」と「マイクスタンド」を別の設備として扱いたい場合には誤判定です。

　また、「Wi-Fi」「プロジェクター」「配信設備」の3つを必須とする場合は、それぞれが存在するかどうかを確認したうえで、3つとも含まれていることを判定しなければなりません。

例えば、必須設備の「Wi-Fi」「プロジェクター」「配信設備」の3つがすべて含まれていれば「TRUE」、そうでなければ「FALSE」と判定する

　まず、設備名を個別に扱えるように、TEXTSPLIT関数を使って、区切り文字の「,」で文字列を分解します。「Wi-Fi」「プロジェクター」「スクリーン」「音響設備」といった個別の値からなる配列として扱えるようになります。文字列の一部分を探すのではなく、設備名そのものを比較できるようになります。

セルC12の値をTEXTSPLIT関数を使ってスピルで表示する。入力する数式は「=TEXTSPLIT(C12,",")」となる

　なお、「、」や「/」などの区切り記号が使われている場合は、SUBSTITUTE関数で区切り文字を統一してからTEXTSPLIT関数で分割すると扱いやすくなります。例えば、「、」をカンマに置き換えるなら、以下のように記述できます。

　こうした前処理を加えておけば、元データの区切り文字に多少ばらつきがあっても、同じ考え方で処理できます。

FILTER関数で比較するデータを用意する

　次にチェックを付けた必須の設備を、FILTER関数を使って一覧にします。B列のチェックがONの設備を並べます。

　なお、この例では、必須設備を1つ以上選択することを前提とします。1つも選択していない場合は「#CALC!」エラーになります。

セルC2に「=FILTER($A$2:$A$9,$B$2:$B$9=TRUE)」と入力すると、B列のチェックがONの設備がスピルで表示される

チェックをONにした設備が結果に追加される

　今回のイベントで必要な設備がスピルで取り出せました。会場側の設備は、TEXTSPLIT関数の結果として、スピルで取り出してあります。つまり、「必要な設備」と「その会場で利用できる設備」の両方を比較できる準備が整いました。

　ここが今回の数式のポイントです。文字列同士を無理に検索するのではなく、比較する単位に分解してから照合します。この考え方は、取引先ごとの「対応可能な業務」、店舗ごとの「対応サービス」、機器ごとの「搭載機能」などが1セルにまとめられている表でも応用できます。

XMATCH関数とISNUMBER関数で条件を判定する

　では、抽出した必須設備が会場の設備一覧に存在するかを調べてみましょう。XMATCH関数を利用して、結果を確認してみます。

　なお、FILTER関数やTEXTSPLIT関数の結果のように、スピルで表示されたセル範囲を引数に指定すると、自動的に「C2#」のようなスピル範囲演算子で表示されます。

今回の例では、XMATCH関数に指定する引数は［検索値］と［検索範囲］の2つのみでよい

必須設備と会場の設備一覧の結果はスピルなので、結果のセル範囲は「C2#」のように参照できる

結果は「1」「2」「#N/A」と表示された

　「1」と「2」は、それぞれ会場の設備一覧の何番目にあるかを表しています。「Wi-Fi」は1番目、「プロジェクター」は2番目ということです。仮に「マイク」をチェックONにすれば、結果は「7」と返ってきます。

　「#N/A」は、一致する結果が存在しないという意味です。つまり、3つめの必須設備の「配信設備」に該当する値が、会場の設備一覧にはないということです。

　しかし、このままでは条件を満たすかどうかの判定ができません。「TRUE」か「FALSE」の論理値に変換するために、ISNUMBER関数を組み合わせます。数値であれば「TRUE」、数値以外は「FALSE」として表示されます。

=ISNUMBER(XMATCH(C2#,E12#))

XMATCH関数の数式に、ISNUMBER関数を組み合わせて「=ISNUMBER(XMATCH(C2#,E12#))」と修正する

結果が「TRUE」「TRUE」「FALSE」と切り替わった

AND関数で最終判定する

　今回の条件は、必須設備を「すべて含む」ですから、最後にAND関数で結果をまとめます。先ほどのISNUMBER関数とXMATCH関数を組み合わせた数式の結果がすべて「TRUE」であれば、「TRUE」、1つでも「FALSE」なら「FALSE」が返されます。

=AND(ISNUMBER(XMATCH(C2#,E12#)))

AND関数を組み合わせた数式「=AND(ISNUMBER(XMATCH(C2#,E12#)))」を入力すると、必須設備が会場の設備にすべて含まれているかどうかを判定できる

　これで、必須設備がすべて、会場の設備一覧に存在するかどうかを判定できるようになりました。ここまで使用した「C2#」と「E12#」の参照部分を、それぞれFILTER関数とTEXTSPLIT関数の数式に置き換えて一覧表に組み込んでみましょう。

=AND(
ISNUMBER(
XMATCH(FILTER($A$2:$A$9,$B$2:$B$9=TRUE),TEXTSPLIT(C12,","))
))

　なお、この例では、D列が「TRUE」なら行全体を強調する条件付き書式を組み込んであります。

上記の数式を一覧に組み込むと、必須設備が会場の設備一覧に存在するかどうかを判定できる。途中で利用した数式は削除して構わない

数式をコピーした結果。「=$D12=TRUE」という条件で条件付き書式を設定してあるため、「TRUE」の行は強調されている

　複数の情報が1つのセルにまとめられている表では、文字列をそのまま検索するよりも、いったん個々の項目に分解してから照合したほうが正確に判定できます。TEXTSPLIT関数、FILTER関数、XMATCH関数、AND関数を組み合わせる方法は、複数の条件を「すべて満たす」候補を探したい時に覚えておくと役立ちます。