Aさん エクセルで関数を使った作業をしていると、選択している範囲は間違いないのに、たまに計算が狂ったり、エラーになってしまうことがあります。。
何が考えられますかね？？
森田 よくあるのは、関数の選択範囲のセルが本来数値や日付なのに、一部のセルの書式が文字列になっているケースですね。
関数の対象範囲にしているデータがパッと見でおかしくないのであれば、まさにそのケースが該当している可能性がありますよ！
こういった、入力すべきデータの種類が決まっているセルに対し、実際のセルのデータが正しく入力されているかを調べるには、IF関数を応用してあげると簡単に判定できます。
それでは、詳しく解説していきますね！
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- セルの入力チェックを行なう機会がある人
- エクセルを扱う機会がある人
- 事務職を目指している人
ちなみに、最低限「IF関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【エクセル初心者向け】データの条件分岐を自動化してストレスフリー！IF関数の使い方
セルの値が見た目と違う場合がある。。
数式・関数で選択している範囲で、想定していない種類のデータになっている場合があります。
たとえば、1～5の各数値が入った5つのセルの合計を出すためのSUM関数があったとしますね。
これが、何らかの事情で”5”だけ数値ではなく文字列になっていたとしたら、次のような結果になります。
おかしいところにお気づきでしょうか？
文字列である”5”を除いた1～4の合計値が、SUM関数の計算結果になっていますね。
このように、パッと見の見た目が問題なくても、何らかの事情で、実は異なるデータであることは、ビジネスでは発生する可能性があります。
今回はシンプルなケースで、確認するセルの数が少ないため、SUM関数の結果がおかしくなった原因を突き止めるのは簡単でした。
しかし、一般的にはもっと大量のセルを使いますので、パッと見では問題なくとも、セル内のデータ種類がおかしい場合の原因特定は想像するだけでも骨が折れますね。。
その解決策のひとつとして、IF関数を応用して、自動的におかしいデータ種類かどうかを判定する今回のテクニックは非常に有効ですよ！
IF関数と他の関数を組み合わせて「セルのデータ種類」を調べよう！
IF関数単体だと、もちろんセルのデータ種類を調べることができないため、IF関数の引数「論理式」の部分へセルのデータ種類を調べることに特化した次の3種類の関数を使いましょう。
- IS系関数
- CELL関数
- TYPE関数
それでは、順番に解説していきましょう。
【関数その1】IS系関数
まず、最初はIS系関数です。
IS系関数とは？
このIS系関数は情報関数の中で、”IS”～から始まる関数名のものの総称です。
いずれの関数も次の構文になりますので、使い方はいっしょですね。
IS●●(テストの対象)
ちなみに、IS系関数は複数種類があり、調べたいデータ種類に応じて関数を使い分けるイメージです。
いずれの関数も、引数「テストの対象」に指定した値が、その関数に対応した種類のデータかどうかを判定し、該当すれば”TRUE”、該当しない場合は”FALSE”を関数の戻り値とします。
| 関数名 | 対応するデータ種類（該当すれば”TURE”） |
|---|---|
| ISBLANK関数 | 空白 |
| ISEVEN関数 | 偶数 |
| ISFORMULA関数 | 数式 |
| ISLOGICAL関数 | 論理値（”TRUE”か”FALSE”） |
| ISNONTEXT関数 | 文字列（テキスト）以外 |
| ISNUMBER関数 | 数値 |
| ISODD関数 | 奇数 |
| ISREF関数 | 参照 |
| ISTEXT関数 | 文字列（テキスト） |
ちなみに、エラー値かどうかを判定するISERROR関数・ISERR関数・ISNA関数もIS系関数といえますが、別記事（下記参照）でこってりと解説しているため、今回は除外しています。
【エクセル中級者向け】数式・関数のエラー表示の回避テクニック①【IF関数＋ISERROR関数】 | Excelを制する者は人生を制す ～No Excel No Life～
目次1 はじめに2 数式・関数でエラー表示になってしまう！3 引数「論理式」にISERROR関数を組み合わせる・・・
【エクセル中級者向け】数式・関数のエラー表示の回避テクニック③④【IF関数＋ISERR関数・ISNA関数】 | Excelを制する者は人生を制す ～No Excel No Life～
目次1 はじめに2 数式・関数でエラー表示になってしまう！3 引数「論理式」にISERR関数またはISNA関数・・・
では、いずれの関数も使い方や戻り値自体はいっしょなので、上記の表からISBLANK関数を代表例として、単体で使ってみようと思います。
たとえば、次のようにA1セル・A2セルの値が空白（ブランク）になっているか調べるため、ISBLANK関数を試してみましょう。
上記のとおり、B1・B2セルへISBLANK関数をセットし、引数「テストの対象」に左隣のセルを指定しています。
結果、B1セルは”TRUE”なのでA1セルは空白、B2セルは”FALSE”なのでA2セルは空白ではないことがわかりました。
B2セルはパッと見では空白に見えましたが、A2セルをよく確認すると、スペースが入っていたために、空白ではなかったと判定されています。
このように、スペースやセル内の改行した情報が残っていると、空白とみなされないため、注意しましょうね。
IF関数の引数「論理式」にIS系関数を組み合わせる！
このIS系関数をIF関数の引数「論理式」に入れてあげましょう。
IF関数と組み合わせることで、指定のデータ種類に該当する場合、該当しない場合のそれぞれで任意の値を表示することが可能になります。
今回は、該当する場合は”空白”、しない場合は”データあり！”という文字列を表示させてみましたよ。
IS系関数の使い分け以外は、一般的なIF関数の使い方なので慣れれば難しくないですね。
【参考】セルがブランクか否かの簡単な確認方法
ちなみに、今回はISBLANK関数で空白かどうかを確認していますが、もっと簡単な数式で同じことができます。（私は実務ではこちらのテクニックしか使っていません）
上記のように、ISBLANK関数の代わりに「A1=””」とすることで、「A1セルが空白の場合」という条件判定が可能となります。（数式上で空白は””と、ダブルクォーテーション×2で表現されます）
【関数その2】CELL関数
続いて、CELL関数です。
CELL関数とは？
このCELL関数は情報関数の一種で、任意のセルの書式、位置、または内容に関する情報を返してくれる関数です。
CELL(検査の種類, [対象範囲])
CELL関数の面白いところは、引数「検査の種類」に指定した内容に応じて、CELL関数自体の戻り値が変わるため、玄人向けの関数ですが、応用次第で思わぬ工夫ができますよ。
この部分の説明は、だいぶ深くなってしまうため、今回はこの引数「検査の種類」が”format”の場合に限って説明していきます。
この”format”を指定すると、引数「対象範囲」に指定したセルの表示形式に対応する文字列（「文字列定数」といいます）をCELL関数の結果（戻り値）として返してくれます。
各種表示形式に対応する文字列定数については、以下をご参照ください。
| 表示形式 | 戻り値（文字列定数） |
|---|---|
| 全般 | G |
| 0 | F0 |
| #,##0 | ,0 |
| 0.00 | F2 |
| #,##0.00 | ,2 |
| $#,##0_);($#,##0) | C0 |
| $#,##0_);[赤]($#,##0) | C0- |
| $#,##0.00_);($#,##0.00) | C2 |
| $#,##0.00_);[赤]($#,##0.00) | C2- |
| 0% | P0 |
| 0.00% | P2 |
| 0.00E+00 | S2 |
| # ?/?（または）# ??/?? | G |
| m/d/yy（または）m/d/yy h:mm（または）mm/dd/yy | D4 |
| d-mmm-yy（または）dd-mmm-yy（または）yyyy/m/d | D1 |
| d-mmm（または）dd-mmm | D2 |
| mmm-yy | D3 |
| mm/dd | D5 |
| h:mm AM/PM | D7 |
| h:mm:ss AM/PM | D6 |
| h:mm | D9 |
| h:mm:ss | D8 |
このCELL関数を単体で使ってみるとイメージが湧きやすいと思います。
たとえば、次のように”2017/7/7”という値となっているA1セルの文字列定数を調べるため、CELL関数を試してみましょう。
上記のとおり、B1セルへCELL関数をセットし、引数「対象範囲」にA1セルを指定しています。
結果、B1セルのCELL関数の戻り値は”D1”だったので、A1セルの表示形式は”yyyy/m/d”のような年月日がある日付だということがわかりました。
なお、上記の文字列定数の表は、Microsoftの公式サイトから引用したものですが、日本語用の表示形式は一部記述がないものもあるため、CELL関数で実際に試して、どんな文字列定数になるかを試すと良いですね。
また、この文字列定数ですが、特殊なルールが2点あります。
まず、1つ目ですが、セルが負数に対応する色で書式設定されている場合、文字列定数の末尾に”-“が付きます。
具体的には、上記でいうと「$#,##0_);[赤]($#,##0)」という表示形式の場合、”[赤]”というマイナスの値の場合に赤色にする書式になっているため、戻り値が”C0-“のように末尾へ”-”が付与されるということですね。
続いて、2つ目ですが、正数またはすべての値をかっこで囲む書式がセルに設定されている場合、結果の文字列定数の末尾に”()”が付きます。
上記の表には、該当する例はありませんが、たとえば「(yyyymmdd)」という表示形式にすると、戻り値が”D1()”のように末尾へ”()”が付与されるということですね。
なお、CELL関数に指定しているセルの表示形式を変更した場合は、CELL関数を手動更新しないと、戻り値に変更が反映されませんので、ご注意ください。
IF関数の引数「論理式」にCELL関数を組み合わせる！
このCELL系関数をIF関数の引数「論理式」に入れてあげましょう。
なお、「CELL(“format”,A1)=”D1″」のようにCELL関数とその戻り値（文字列定数）までを指定してあげないとIF関数で条件判定できないので、注意しましょう。
上記でいえば、IF関数と組み合わせることで、CELL関数の判定結果が”D1”という文字列定数に該当する場合、該当しない場合のそれぞれで任意の値を表示することが可能になります。
今回は、該当する場合は”日付です！”、しない場合は”日付ではありません。。”という文字列を表示させてみましたよ。
【関数その3】TYPE関数
最後に、TYPE関数です。
TYPE関数とは？
このTYPE関数は情報関数の一種で、指定したセルの値のデータ種類（データ型）に対応した数値を返してくれる関数です。
TYPE(データタイプ)
ちなみに、データ種類に応じた数値については、以下をご参照ください。
| データ種類 | 戻り値 |
|---|---|
| 数値 | 1 |
| 文字列（テキスト） | 2 |
| 論理値（”TRUE”か”FALSE”） | 4 |
| エラー値 | 16 |
| 配列 | 64 |
このTYPE関数を単体で使ってみるとイメージが湧きやすいと思います。
たとえば、次のようにA1～A3セルの値がどのデータ種類か調べるため、TYPE関数を試してみましょう。
上記のとおり、B1～B3セルへTYPE関数をセットし、引数「データタイプ」に左隣のセルを指定しています。
結果、B1セルは”1”なのでA1セルの”1234”は数値、B2セルは”2”なのでA2セルの”文字列”は文字列、B3セルは”4”なのでA3セルの”FALSE”は論理値であることがわかりました。
IF関数の引数「論理式」にTYPE関数を組み合わせる！
このTYPE系関数をIF関数の引数「論理式」に入れてあげましょう。
なお、「TYPE(A1)=1」のようにTYPE関数とその戻り値（データ種類に応じた数値）までを指定してあげないとIF関数で条件判定できないので、注意しましょう。
上記でいえば、IF関数と組み合わせることで、TYPE関数の判定結果がデータ種類「数値」を示す”1”に該当する場合、該当しない場合のそれぞれで任意の値を表示することが可能になります。
今回は、該当する場合は”データ種類は「数値」です！”、しない場合は” 数値以外のデータ種類です。”という文字列を表示させてみましたよ。
サンプルファイルで練習しよう！
では、今回のテクニックを使ってみましょう！
サンプルの条件
今回の題材は次のとおりです。
「入力判定」というシートがあります。
入力項目が複数種類ありますが、それぞれ以下のように必要な「データ種類」が決まっているとします。
- 氏名 → 文字列
- 年齢 → 数値
- 誕生日 → 日付
- 成人 → 論理値
- 予備1 → 空白
- 予備2 → 空白
それぞれ、上記の指定通りのデータがB2～B7セルに入力されたかどうか、判定する必要があります。
D2～D7セルへB列のデータが指定通りのデータ種類になっているかを判定してみましょう。
なお、各IF関数は、指定通りのデータ種類であれば”OK”、指定通りでなければ”NG”の戻り値になるように設定しましょうね。
実際に練習してみよう！
可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。
サンプルファイル_IF関数_セルデータ種類
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ファイルを開いたら次の手順を実施してください。
- 「入力判定」シートを選択
- D2セルへ「=IF(ISTEXT(B2),”OK”,”NG”)」を入力
※赤字の部分をコピーして貼り付けてください。 - D3セルへ「=IF(ISNUMBER(B3),”OK”,”NG”)」を入力
※赤字の部分をコピーして貼り付けてください。 - D4セルへ「=IF(CELL(“format”,B4)=”D1″,”OK”,”NG”)」を入力
※赤字の部分をコピーして貼り付けてください。 - D5セルへ「=IF(ISLOGICAL(B5),”OK”,”NG”)」を入力
※赤字の部分をコピーして貼り付けてください。 - D6セルへ「=IF(ISBLANK(B6),”OK”,”NG”)」を入力
※赤字の部分をコピーして貼り付けてください。 - D7セルへ「=IF(B7=””,”OK”,”NG”)」を入力
※赤字の部分をコピーして貼り付けてください。
D2～D7セルの各セルの数式が以下のとおりであればOKです！
B列の各値を変えてみて、D2～D7セルの値がどう変わるかも試してみてくださいね。
もし、表や数式を加工してしまった場合は、上記手順を実施済みの「入力判定 (関数あり)」シートもサンプルファイル内に用意していますので、必要に応じてご活用くださいね。
なお、一部IS系関数はTYPE関数で代用できるため、そちらは「入力判定 (関数あり_一部TYPE関数)」シートをご参考にしてください。
さいごに
セルのデータ種類を調べるテクニックとして、3種類の関数をご紹介しましたが、表示形式が関係ないデータそのものの種類を特定したいならIS系関数かTYPE関数、表示形式をもとにデータ種類を特定したいならCELL関数、という使い分けになりますね。
もっと具体的にいうと、日付や時刻はエクセルではシリアル値（1900年1月1日から数えて何日目の日付かを数値で表したもの）で管理されるために数値と同じ扱いになり、CELL関数でないと特定できません。
よって、日付や時刻を調べる場合はCELL関数、それ以外はデータ種類別にIS系関数とTYPE関数の調べられる方を使う、という流れで判断するとシンプルですね。
他にも、IF関数を便利に使うための応用テクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼IF関数を基本から応用まで体系的に学びたい方向け
▼IF関数の応用テクニックに加えて、その他VLOOKUP関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 今後、VBAを学びたい方は、このデータ種類（データ型）を調べることは、よく使う概念になりますので、今回のテクニックを通して知っておくと良いですね。
なお、VBAならIsDate関数というデータ種類が日付かどうか判断できるVBA関数があって便利だったりします。
