IF関数の仕様による落とし穴
「とあるセルの値によって表示する内容を切り替えたい」
条件付きの(条件に応じた)データの表示にIF関数はとても勝手がよく、利用している人も多いと思います。
ただし、使い勝手のよい便利なIF関数にも一定のルールが存在します。ここでは、作業者からみると気づきにくい点をいくつかご紹介します。
IF関数で「比較できる値」と「比較できない値」がある！？
IF関数で「セルとセルの値を比較する」というのは、よく見かけます。
しかし、IF関数を使うことで、
「どのようなときにどのような結果を返すのか」
これを知っておかないと気づかぬうちに、予想外の結果を返すこともあります。
IF関数を使う上で大事なポイントは、期待通り比較できているのかどうかですね。
この点に気づかずにIF関数の結果を見て「ふむふむ、なるほど、こういう結果になるのか・・」と納得すると、思わぬミスにつながります。
ここでは、その気づきにくい点を見るために、下記の表を用意しました。
A列の値とB列の値をIF関数を使って、「値が同じかどうか」を確認したいと思います。
■データ比較表
「A列の値」と「B列の値」の比較を試したいと思います。
データは「人が見れば同じ」あるいは、「同じであると判断し得る」ものを用意しました。
C列には、「同じように見せている仕掛け(設定)内容」を入れています。
上記のD列に、IF関数を入れて、
・値Aと値Bが同じ場合：「同じ」と表示
・値Aと値Bが違う場合：「違う」と表示
と、そのままですが(笑)表示し、その結果を見てみたいと思います。
「実際に入力されている値」と「見た目の値」には、「超注意」！
では、実際にIF関数を入力して値Aと値Bの値が同じかどうかを検証してみたいと思います。
※自信のある方は上記の画像からIF関数の結果、「同じ」または「違う」のどちらが表示されるか考えてみてくださいませ。
■IF関数を入れた結果
いかがでしょうか。
既にご存じの方もいらっしゃるかもしれませんが、IF関数(に限った話ではありませんが)を使うときには、「実際に入力されている値」と「見た目の値」に注意する必要があります。
Excelでは、セルに色々な形(フォーマット、書式)で、値を入力できます。
今回のIF関数の結果を見ると分かるように、私達の目で見れば、「同じ(違う)と判断できる値」にも関わらず、Excelの中では、「違う値、あるいは同じ値」として管理されていることが分かります。
つまり
セルの値(見た目)が同じでもExcelの内部では別物として扱っている
ということですね。
IF関数での比較の回避方法は限定的(かもです)
まずはIF関数を作る前に、みなさんの作った表に、このような状況が起こり得るかどうかの判断ができることがあります。
例えば、各セルの値の「表示されている位置」に注目します。
セルの値が、左側に寄っているセルと右側に寄っているセルがあることが分かります(一部表示幅ギリギリになってしまってわからないものもありますが)。
実は、これは関数を使う上での大きなヒントになります。
Excelでは、セル内の値の位置が
- 左側にある：文字として(Excelは)取り扱う
- 右側にある：数値として(Excelは)取り扱う
というルールがあります。このルールを知っておくことで、IF関数を使うことで期待する結果が得られるかの判断材料になります。
とは言え、誰もが使うだろうあの機能のため、Excelでは、セル内に表示する値の位置を自在にいじれてしまいます。
このように、絶対的ではないのがたまにキズですが知っておいて損はないと思います。
では、もし「文字と数値が混ざっている」ことが判明したら、IF関数は使えないのか。いえ、これも対策はあります。
「文字と数値が混ざっている」セルがあるならば、どちらかに統一してしまえばよいということです。
すなわちIF関数を使う前に全て「文字列」としてセルに入れておくということになります。
(「数値」にすると数値化できないセル値があると面倒なので文字列にする)
セルの値を文字列化し、IF関数で対応する方法
では、セルの値を文字列化させることで対応を取る方法について説明したいと思います。
それは、上記のIF関数の比較対象である、「2つのセル」に仕掛けを施します。
セルを文字列化するにはTEXT関数を使います。
上記のように、2つのセルをそれぞれ「文字に変えた後に比較」させることで対応ができます。
(「同じ列に数値や文字のセルがある」という場合でも関係なく、IF関数の対象となる「セル全てにTEXT関数を挟む(ネストする)」ことで、どこのセルが数値なのか、といったことを考慮する必要がなくなります)
一番右の列「IF関数の結果2」がTEXT関数を入れてIF関数を使用した結果です。
しかし、それでもまだ十分ではない場合もあります。その場合は、それぞれのセルを考えられる形に変えてしまうことで対応ができます。
上記はさらにTRIM関数を入れて、前後の空白を削除しました。
このように、対処できることはありますが、やはり限界もあります。
また、どこまで対応するべきか、表の内容によって変わるでしょう。
私の場合は、セルの値が完全に同じかどうかを知るために「EXACT関数」をよく使用します。
IF関数で比較したいことは内容次第
いかがでしょうか。繰り返しますが、Excelは、私達の一般的な理解とは裏腹に思ってない形でデータを扱うという側面があります。
一言で言えば、「IF関数で比較ができない場合がある」、この点を把握して置かない状態で関数の結果を受け入れると、想定していない結果になることがあるということが分かって頂けたと思います。
ただし、どこまで考慮するべきかは、みなさんの作っている表次第とも言えます。
「バランスを取りながら関数を使う」、結構難しく面倒な作業ですが、この知識がお役にたてば幸いです。