Excelを使っていて、こう思ったことはないでしょうか──
COUNTIF(S)関数を使うと#VALUEエラーを返してしまうような長い値が含まれているセルの数を、どうにかしてカウントできないだろうか？
と。
私もその1人で、どうにかしてかなりの長文をカウントしたいと試行錯誤していました。
どうにかしてカウントする方法を編み出したので、同じような悩みを抱えている方の参考になればよいと思い、記事にしてみようと思う。
例文を用意しました。
A2セルには538文字あり、これをCOUNTIF関数でB2と一致、またはB2に含まれているかを調べようとしても#VALUEエラーを返します。
そこで、XLOOKUP関数を使って無理やりカウントすることにします。
記述する式は以下のとおり。
=XLOOKUP((A2,B2,$C$2,”–“)
B2とA2の内容が一致していたら、$C$2を返します。
C2には1と入力しているので、返す値は1。
いくつか行を増やしてみました。
6,7行目は”–“となっています。
※XLOOKUPで見つからないときに”–“と返すように指定している。
見つからないと判定されている理由は、このように一番最後の項目が4.ではなく、6.になっているから。
とはいえ、ほとんど誤差レベルということで、以下2つの条件に一致する場合1を返すようにしてみたいと思います。
①「ホームボタンのない〜シャットダウンをタップします。」までの文字列を含む
②「**スライダーの操作**:〜電源をオフにします。」までの文字列を含む
新たに書く関数は以下のとおり。
=XLOOKUP((“*”&A2&”*”)&(“*”&B2&”*”),C2,$D$2,”–“,2)
あいまい検索にして、A2セルとB2セルの内容を含んでいるかどうか、という形式で判別するようにします。
たとえば、C7セルは「6.**スライダーの〜・・・」となっており、完全一致ではありません。
しかし、=XLOOKUP((“*”&A7&”*”)&(“*”&B7&”*”),C7,$D$2,”–“,2)
と記述すると下図のように1を返してくれます。
1を返してくれているので、SUM関数などで合計すれば検索値①と②を含むC列のセルの数をカウントできます。
例2
B4とB5セルの内容は、C4, C5それぞれに含まれていないので、E4, E5に”–“と返されている。
もちろん戻り値には、たとえば「これはテンプレート①です」といった文言を返すことも可能。
なので、検索値を複数用意しておいて、
検索値①＆②→テンプレート①、
検索値①＆③→テンプレート②
といったように使われたテンプレートを区別する、といった使い方もできると思います。
さいごに
結構無理やりな方法であること、また、計算は重くなることを承知していただけると幸いです。
COUNTIFでは文字が多すぎるとエラーを吐いてしまうので、無理やり判別できるようにしてみました。
参考になれば幸いです。