ゲームより面白いExcelちょこっと問題[第009弾]

文字列を比較するいろんな関数

この単語とあの単語は同じかな? を調べる方法をいくつか。Excelでは，基本的に大文字と小文字を同じとして扱いますよね。Windowsとwindowsは同じ，としちゃうのです。ま，いいか，という時もありますが，いや，異なるものとして欲しいなあ，と思うこともあります。

で，いろんな関数を使って，戻り値の違いを調べました。

単語同士の比較

1行目の各列には，比較の対象となる文字が入力されています。全部「windows」です。2行目は，比較したい文字というか検索文字というか，比較のもう一方の文字です。小文字，大文字，先頭だけ大文字，そしてワイルドカードと組み合わせたもの，などです。

A3以下には，使用した関数名があります。以下にそれぞれの式を示します。式と戻り値をよく見比べてくださいね。式は，すべて右側に複写してください。

IF関数

B3：=IF(B1=B2,"TRUE","FALSE")

• B2〜D2がTRUEになっています。つまり，大文字と小文字を同じとしているんですね。
• E2〜G2はFALSE。ワイルドカードは使えません。

EXACT関数

B4：=EXACT(B1,B2)

• TRUEになるのは，比較する二つの文字列が完全に同じ場合のみ。大文字と小文字を異なるものとして比較するんですね。
• ワイルドカードを使うことは出来ません。エラーにはなりませんが。

FIND関数

B5：=FIND(B2,B1)

• この間数は，探した文字が見つかると，その位置を返す関数です。見つからないとエラーになります。大文字・小文字を区別しますから，TRUEを返したのは，一つだけです。

SEARCH関数

B6：=SEARCH(B2,B1)

• この間数は，探した文字が見つかると，その位置を返す関数です。大文字・小文字を同じに扱います。
• ワイルドカードを使用できます。したがって，全部「見つけた」という結果になりましたね。

演算子（＝）で比較

B7：=B1=B2

• 二つの文字列を単純に等号（＝）で比較しました。大文字と小文字を区別しません。
• ワイルドカードは使えません。

SUBSTITUTE関数

B8：=SUBSTITUTE(B1,B2,"TRUE")

（対象）「文字列」内に，「検索文字」を探し，見つかれば，「置換文字列」に置き換える関数です。

• 見つけたのは，B8だけ。つまり，大文字・小文字を区別し，ワイルドカードは使えない，ということですね。

COUNTIF関数

B9：=COUNTIF(B1,B2)

• 大文字・小文字を区別しない。
• ワイルドカードも有効です。

COUNTIF関数の引数「範囲」に一つのセルを指定すると，引数「検索条件」に指定した文字列があるか，どうかの判定をすることが出来ます。気をつけなければいけないのは，COUNTIF関数は，一つのセル内に，検索する文字がいくつあるかを調べるものではない，ということ。セルA1に『win win win』と入力されている時，=COUNTIF(A1,"*win*")の戻り値は「1」です，「3」ではありません。

文章と単語を比較する

文章の中に，ある単語が含まれているかどうかを調べてみましょう。

セルB1に『This is W3C''s home page for the HTML Activity. 』が入力されている時のそれぞれの関数を使用して検索文字を探すような感じです。どの位置の式も対象文字列（文章）はB1ですから絶対参照にしています。

IF関数

B3：=IF($B$1=B2,"TRUE","FALSE")

比較する一方の文字列の中に，ある文字列があるかどうか，という使い方は出来ない，ってことですね。ワイルドカードが有効であればいいのですが，残念ですねえ。

EXACT関数

B4：=EXACT($B$1,B2)

文章とそこに含まれる一部の文字列を比較しようたって，そりゃ無理ってもんです。単語であろうと，文章であろうと，厳密に同じかどうかを比較する関数なんですから。

FIND関数

B5：=FIND(B2,$B$1)

完全に一致する文字列（単語）を見つけた時だけ，その位置を返す関数ですから，「Activity」の時だけ位置を返し，それ以外ではエラーになります。

SEARCH関数

B6：=SEARCH(B2,$B$1)

大文字・小文字を同じと見なし，しかも，ワイルドカードを使えるので，すべて「位置」が返っていますね。ちょっと気をつけなければいけないのは，「*act*」などのように，検索する文字列の両端にワイルドカードが付いている場合です。「*」は「0文字以上の任意の文字列」ですから，文章全体を意味しているんです。そのため，戻り値が「1」なんですよ。

等号演算子（＝）

B7：=$B$1=B2

IF関数や，EXACT関数と同じです。文章と単語を比較したって，同じであるわけありません。

SUBSTITUTE関数

B8：=SUBSTITUTE($B$1,B2,"TRUE")

「TRUE」に置き換えたのは，完全に一致する文字列を指定した時だけですね。C8んとこです。SUBSTITUTE関数は，大文字・小文字を異なるものとし，ワイルドカードは使用できません。

COUNTIF関数

B9：=COUNTIF($B$1,B2)

ワイルドカードを使わなければ，駄目なんですよ。あるセルに，ある単語があるかどうかを調べる時は，ワイルドカードを使用します。セル範囲を対象にする時もワイルドカードを上手に使ってくださいね。

戻り値を「1」や「0」にしたいんだけど

戻り値がTRUEやFALSE，1や0だと戻り値を使って分岐処理できますよね。でも，今のままでは，エラーなどがあって，不可能です。これまでの式を使って，TRUEを意味する「1」，FALSEとして扱える「0」が戻るように式を書き換えてみましょう。

IF関数

B3：=IF($B$1=B2,1,0)

説明いりませんよね? 真の場合「1」，偽の場合「0」。基本中の基本的な式です。

EXACT関数

B4：=EXACT($B$1,B2)*1

EXACT関数は，比較した文字列が等しければ「TRUE」，異なっていれば「FALSE」を返しますから，「×」とするだけで，数値になります。

FIND関数

B5：=ISNUMBER(FIND(B2,$B$1))*1

FIND関数は「検索文字列」が見つかるとその位置を返しますが，見つからないとエラーになります。見つかった時数値を返すんですから，ISNUMBER関数にネストしてやります。そうすると，数値の場合「TRUE」，数値でない場合「FALSE」になります。それに「×1」をして，数値に変換しました。

SEARCH関数

B6：=ISNUMBER(SEARCH(B2,$B$1))*1

FIND関数と同じ考えです。

等号演算子（＝）

B7：=($B$1=B2)*1

等号演算子を使って左辺と右辺を比較すると，戻り値はTRUEかFALSEになります。これも「×1」でいいですね。

SUBSTITUTE関数

B8：=NOT(EXACT($B$1,SUBSTITUTE($B$1,B2,"TRUE")))*1

SUBSTITUTE関数で，検索文字が見つからなかったら「TRUE」なんて使い方することないと思うんですよ。見つからなかったら，文章そのままがでるんですから，それでいいんじゃないかなあ，と。でも，ま，練習ですから，強引に作ってみました。元の文章と，置き換え後の文章をEXACT関数で比較すると，戻り値はTRUEかFALSEになります。見つからなかった（置き換えなかった）時TRUEですから，このまま「×1」をすると，おかしなことになっちゃいます。で，NOT関数に渡して，真偽（TRUE｜FALSE）を反転し，「×1」で数値に変換しました。

COUNTIF関数

B9：=SIGN(COUNTIF($B$1,B2))

引数「範囲」にセルを一つしか指定していないんですから，見つかれば「1」，なければ「0」が返ります。ですから，今の例ではこのままでいいんですが，範囲を指定した時のことも考えて，上の式にしました。SIGN関数は，符号を調べる関数で，整数が「1」，0は「0」，負数の場合「−1」が返ります。もっとも「0」以外はTRUEと見なされるので，この式を使わなくてもいいんです，じつは。

たとえば，A1からA5にすべて『win』が入力されている時，
=IF(COUNTIF(A1:A5,"win"),"あります","ありません")
とすると，「あります」が返ります。FALSEは「0」ですが，TRUEは「1」に限らず，「0」以外の数値なら何でもいいんです。「0.5」，「-0.12」，「10^2」……みんなTRUEと見なされます。