Excel。セルの部分一致が複数条件の時はAND関数が使えない
＜IF+COUNTIFS関数＞
新宿店の女性の顧客というような複数条件のデータをわかるようにする場合は、IF+AND関数を使うことで、簡単に算出することができるわけですが、AND関数が意外とオールマイティーではなくて、現場で困ることがあります。今回は、その例をご紹介していきます。
次の表があります。
例えば、顧客コードの第2レベルが18で、住所は、川崎市のデータには”○”を表示させるようにしたいわけです。
簡単にいかない理由は、セルの一部のデータが含まれているかどうか、ということなわけです。しかも、複数列。
先程の、新宿店の女性のデータということならば、
=IF(AND(A3=”新宿店”,B3=”女性”),”○”,””)
というような数式を作れば、いいわけです。
ところが、セルの一部のデータが複数列で含まれているという場合は、AND関数が使えません。
では、C3に数式をIF+AND関数で作って確認してみましょう。
C3に作成する数式は、
=IF(AND(MID(A3,3,2)=18,MID(B3,5,3)="川崎市"),"○","")
MID関数は、セル内の文字データの何文字目からいくつ目までの文字というように文字データの途中を算出する関数ですね。
では、オートフィルを使って数式をコピーしてみましょう。
何も表示されません。
確認のために、C3の数式をAND関数ではなくて、OR関数に変更してみましょう。
=IF(OR(MID(A3,3,2)=18,MID(B3,5,3)="川崎市"),"○","")
としてみると、算出されていますので、AND関数内の引数にミスはないわけです。
AND関数は、完全一致を求めるので、セル内の一部では使うことができないようです。
【COUNTIFS関数を使う】
そこで、今回のようなケースの場合は、COUNTIFS関数を使うことで解決できます。
D3をクリックして、IF関数ダイアログボックスを表示させます。
論理式に、COUNTIFS関数をネストします。
COUNTIFS関数ダイアログボックスが表示されますので、設定していきます。
検索条件範囲1には、A3。通常COUNTIFS関数は、A3:A10のような範囲選択をしますが、このセルの値がということなので、一つだけのセルを選択します。
検索条件1には、"*18*"
*（アスタリスク）はワイルドカードですね。
前後で挟めば、挟んだ文字列を含むという意味ですね。
検索条件範囲2には、B3。
検索条件2には、"*川崎市*"
IF関数ダイアログボックスに戻ります。
値が真の場合には、”○”
値が偽の場合には、”” 空白を設定して、OKボタンをクリックします。
オートフィルを使って数式をコピーしましょう。
D3の数式は、
=IF(COUNTIFS(A3,"*18*",B3,"*川崎市*"),"○","")
となっています。
今回は、条件に合致するものを算出することができました。
今回のように、セル内の一部のデータが複数条件で合致するものを見つけたい場合には、IF+AND関数ではなくて、IF+COUNTIFS関数を使うと解決できます。
