ExcelでIF関数を使用することは良くあると思います。しかし、思った結果にならないということも良くあります。
条件を満たしているように見えるのに「偽」と判定されたり、条件を満たしていないはずなのに「真」と判定されたり、その時に考えられる原因として以下のようなことがあげられます。
- 数値の場合
- 値は違うが桁数の問題で見た目が同じになっている
- 表示形式が文字列になっている
- 数値を文字列として入力している
- 文字列の場合
- 空白(スペース)が含まれている
- 制御コードが含まれている
- その他
- 文字列を数値比較している
これだけではわかりにくいので、次から1つ1つ説明していこうと思います。
値は違うが桁数の問題で見た目が同じになっている
数値の場合「セルの書式設定」で小数点以下の桁数が指定できます。
数値を入力してもその桁数となるように四捨五入されるため、実際に入力された値が異なっても見た目が同じとなります。
例えば「0.5」と「1.4」を入力しても小数点以下の桁数が「0」となっていれば、見た目は「1」と表示されます。
しかし、IF関数で比較した場合は実際に入力された値で比較するため2つのセルを比較した場合「違う」という結果になります。
解決策
この場合、見た目だけの問題で実際は値が違うためIF関数の結果が正しいので、対処としては「セルの書式設定」の「表示形式」で小数点以下の桁数を実際のデータと同じ桁数にするという形となるのではないかと思います。
数値が文字列になっている
数字が入力されていてもセルの書式設定で表示形式に「文字列」が指定されていると文字とみなされます。
「数値」の「1」と「文字列」の「1」を比較しても、IF関数では「違う」と判定されてしまいます。
解決策
IF関数で比較する場合は表示形式を合わせる必要があります。
対処方法としては表示形式をどちらかに合わす形になります。
ただし、表示形式を変更してもすぐに有効にはならず、そのセルの次回編集時から有効になります。
データ量が多い場合、1つ1つ編集モードに切り替えてから[Enter]をしていると日が暮れるので、一括で変更したい場合は以下を参考にしてください。
数値を文字列として入力している
「表示形式」が「数値」となっていても「文字列」で入力されることがあります。
セルの頭に'(シングルクォーテーション)が含まれている
セルの頭に'(シングルクォーテーション)を入力して値を入れると、そのセルは「文字列」とみなされます。
そのため、数値と比較していると「違う」と判定されます。
数式の中で"(ダブルクォーテーション)で囲っている
数式の中で"(ダブルクォーテーション)で囲って値を入れると文字列とみなされます。
例えば、IF関数の結果等で数字をダブルクォーテーションで囲って入力してしまうと、セルの「表示形式」を「数値」としても、中身は文字列となってしまいます。
そのため、数値と比較していると「違う」と判定されます。
解決策
この点については文字列とみなされるような入力方法にならないように気をつけるしかありません。
ただし、'(シングルクォーテーション)に関してはコピーして別のセルに「値」として貼り付けると'(シングルクォーテーション)が消えます。
そのままでは文字列となったままのため、以下の記事の方法を使用することで数値に変換してください。
空白(スペース)が含まれている
例えば以下のように文字列を比較した時にぱっと見は同じ値が入っているように見えますが、IF関数での比較結果は「違う」となります。
実は片方の文字の後ろに空白(スペース)が含まれているため「違う」結果となっています。
普通に資料を作成している場合はなかなかこんなことはないかもしれませんが、別の資料(ウェブページ等)からコピー＆ペーストした時などには、結構起こります。
解決策
この場合の解決策を2つ紹介します。
元のデータから空白(スペース)を省く
後々のことも考え、元のデータから空白(スペース)を省いてしまうのが正しい方法かと思います。
データ量が多くて大変な時は、対象のセルを選択し[Ctrl]キー + [H]キーを押下して「検索と置換」画面を表示します。
「検索する文字列」に空白(スペース)を入力し「置換後の文字列」を空にして[すべて置換]をクリックします。
これで選択していたセル内の空白(スペース)がすべて削除されます。
ただし、文字列の間に空白が入っているセルでは一緒に消してしまうためこの方法は使用できません。
IF関数の中でTRIM関数を使用する
前述の方法が難しい場合では、IF関数の中でTRIM関数を使用すればよいかと思います。
TRIM関数は次のような条件で空白(スペース)を削除してくれます。
- 文字列の先頭や末尾の空白(スペース)を削除
- 文字列の間に挿入されている空白(スペース)は、1つだけを残して削除
制御コードが含まれている
文字列が一致しない原因として制御コードが含まれていることがあります。
制御コードとは文字コードで規定された文字の中で、周辺機器の制御や通信の制御などに用いる特殊な文字のことです。
改行などもこれにあたり、制御コード自体をExcel上で確認することはできません。
改行であれば見た目で判断できることもありますが「折り返して全体を表示する」がオフになっていると1行で表示されてしまいます。
そのため、以下の場合は違うように見えてもIF関数での比較結果は「同じ」となり
以下の場合は同じように見えてもIF関数での比較結果は「違う」となります。
なぜ制御コードが入ることがあるかというと、改行またはその他制御コードが含まれたデータを一括でExcelにインポートしたりコピー＆ペーストした場合等があげられます。
解決策
この場合の解決策を3つ紹介します。
元のデータから制御コードを省く
空白(スペース)と同じように制御コードを省くのが正しい形かと思います。
制御コードが改行だけだとわかっている場合は以下の方法が使えます。
ただし、セル内であえて入れている改行も削除されます。
CLEAN関数を使用する
改行以外の制御コードが含まれている場合はCLEAN関数を使用します。
=CLEAN(A1)
のように指定することでそのセル内の制御コードを削除した結果を表示することができます。
この方法で制御コードの入っていない列(行)を作成し
元のセルに「値」を貼り付ければOKです。
IF関数の中でCLEAN関数を使用する
前述の方法が難しい場合や、元のデータを変更したくない場合はIF関数の中でCLEAN関数を使用します。
=IF(CLEAN(A1)=CLEAN(A2),"同じ","違う")
このように指定すると比較結果は「同じ」となります。
文字列を「>」「<」で数値比較している
これは、セル同士の比較ではなく「>」「<」を用いた数値比較を実施した場合に起こります。
数値比較したつもりで片方が文字列となっていると、以下のように判別されます。
真とみなされる
文字列 > 数値 数値 < 文字列
偽とみなされる
文字列 < 数値 数値 > 文字列
解決策
この場合の解決策を2つ紹介します。
数値比較の時は数値しか入力しない
数値比較を実施する時にはセルの「表示形式」を「文字列」ではなく「数値」にし、文字列を入力しないことが重要です。
数式の中であれば、数値をダブルクォーテーションで括ってはいけません。
ISNUMBER関数を使用する
数値と文字の両方が入る可能性があるセルで数字の場合は数値比較したいという場合もあると思います。
その場合はISNUMBER関数を併用すると解決します。
ISNUMBER関数は「数値の場合に真を返す」という関数です。
ISMUNBER関数を使用すると文字列だった場合に偽と判定させることができ、真の場合は数値比較を実施するということができます。
まとめ
IF関数の結果がおかしい時の原因として以下の6項目を紹介しました。
- 数値の場合
- 値は違うが桁数の問題で見た目が同じになっている
- 表示形式が文字列になっている
- 数値を文字列として入力している
- 文字列の場合
- 空白(スペース)が含まれている
- 制御コードが含まれている
- その他
- 文字列を数値比較している
基本的には目に見えない文字が入っているか、数値と文字列の問題だと思います。
長くなり、説明もわかりづらいところもあったかもしれませんが、問題解決の一助になれば幸いです。
コメント