エクセル(Excel)の数値と文字列の扱いにご注意を！

エクセルの数値と文字列の扱いに注意！

「入力した関数に問題はないのに結果がおかしい」
「今まで問題なかったのに、急に関数の結果がおかしくなった」

このような経験はありませんか？ データの内容によっては大変なことになります。この記事は、実際のビジネス現場で起きた事故を参考にして作成しました。1つの事例なので、すべての問題を解決することはできませんが、特に気付きにくい危険な状況を解説しています。ぜひご参考下さい。
 

MAX関数の実験

突然ですが、ひとつ実験をしてみましょう次のようにいくつか数値を入力して、MAX関数で最大値を表示させてみます。当然、最大値が表示されます。
 

そこで、数値を入力したセルの表示形式を「文字列」に変更して下さい。
 

※上図の「セルの書式設定」ダイアログボックスを表示するには、数値を入力したセル範囲を選択し、選択範囲上で右クリックして「セルの書式設定」をクリックします。

表示位置が左詰めになりました。この時点で、「あれ？MAX関数って、文字列に対しても使えるの？」と感じる方がいらっしゃるかもしれませんね。そして、最大値をさらに大きな数値に変更してみましょう。ここでは「96」に変更してみます。
 
おやっ！　変更した最大値が表示されるはずなのに、次に大きな最大値が表示されました。最大値を変更したセルの値が関知されていないようです。しかも、緑色の三角形がセルの左上に表示されています。
   

現場で実際に起きた事故

この実験の結果が示している危険にお気付きでしょうか？ この実験、ビジネスの現場で実際に目撃した事故をもとに考えたものです。

その事故とは、数千行からなる比較的大きな表で起きていました。ある列には数値が入力されていて、その列内の最大値をMAX関数で表示させていました。しかし、その最大値が明らかに小さすぎるのです。入力されていた数値データはすべて左詰めになっていて、調べてみたら、セルの表示形式が「文字列」になっています。

試しにその列をコピーして、別シートに値のみ貼り付けてMAX関数を入力。案の定、全く違う最大値が表示されました。

聞いてみたら、これまで、MAX関数で表示される最大値を疑うことなく運用していたとのこと。上記の実験でセルに表示された緑色の三角形に気付かなかったのかな？と考える方もいらっしゃるかもしれませんが、この機能はExcel2002以降のもの。この現場ではExcel2000を使っていたため、画面上では何事もない状況だったのです。

このときは、これから紹介する処置により事なきを得ましたが、データの内容によっては、大変な問題にもなりかねません。この実験では何が起きていたのでしょうか？
 

問題は「数値文字列」

先ほどの実験に話を戻しましょう。まず、数値データを入力したあと、セルの表示形式を「文字列」に変更しました。実は、このとき、入力した数値データは「数値文字列」と呼ばれる形式に変更されています。
 

数値文字列とは「表示形式が文字列に設定された数値」のことです。「表示形式」が文字列になっただけで「実態」は数値。だから表面上は文字列でもMAX関数が使えたのです。

ところが、この数値文字列。次のようにセルを操作した時点で「数値文字列」から「文字列」に勝手に変更されてしまいます。
●セルの内容を変更したとき
●セルをダブルクリックしてからEnterキーを押したとき
●F2キーを押してからEnterキーを押したとき

つまり、表示形式を文字列に変更したら、聞いたこともない「数値文字列」という形式になっていて、操作したセルの値が勝手に「文字列」に変わっていた、というわけです。

そして、MAX関数は数値を対象にして最大値を求めます。したがって、文字列に変更されてしまったデータはMAX関数に関知されなくなります。

 

当面の解決策は？

ひとまず、実験で起きた状況を解決しましょう。ここでは、表示形式は文字列のままでデータを数値文字列に戻します。

まず、任意のセルに「1」を入力してコピーします。
 

MAX関数の引数で指定しているセル範囲を選択して右クリックし、表示されたショートカットメニューで「形式を選択して貼り付け」をクリックします。
   
表示された「形式を選択して貼り付け」ダイアログボックスの「貼り付け」で「値」を選択し、「演算」で「乗算」を選択して「OK」ボタンをクリックします。
 

この操作で、データの形式が数値文字列に統一され、MAX関数も期待通りの結果を返しています。
 

この方法なら、どのセルで問題が起きているのかわからなくても、関数の引数に指定したセル範囲の全データを数値文字列に戻すことができます。
 

表示形式を「標準」に戻すとどうなる？

状況を解決するのなら、単純にセルの表示形式を「標準」に戻せば良いのでは？と考えた方もいらっしゃるかもしれませんね。確かに間違いではないのですが、問題を解決できない場合があります。

試しに、先ほど数値文字列に統一したデータのうち、最大値のデータを文字列に変換（F2キーを押してからEnterキーを押すなど）してから、データが入力されているセル範囲の表示形式を標準に戻してみましょう。
 

数値文字列だったセルの表示形式は標準に戻り、データも数値に戻ります。ところが、文字列に変更したセルについては、表示形式は標準に変更されるのですが、データが文字列のままなのでMAX関数の戻り値も解決されません。
 

表示形式を「標準」に戻すだけでは問題が解決できない場合があることをおわかり頂けたでしょうか？

なお、この場合の解決策は次の通りです。

文字列のセルを、F2キーを押してからEnterキーを押すなどして操作してみます。Excel2002以降なら、緑色の三角形が表示されたセルをクリックして、表示された「エラーチェックオプション」ボタンの「▼」をクリックして「数値に変換する」をクリックしても同じです。
 

これで、データが標準に変更されたようですね。MAX関数の戻り値も解決されました。
   

数値・数値文字列・文字列の関係について

これまでの実験で、次のような現象が確認できました。
※青文字は表示形式、赤文字は実際のデータの形式を表しています。

●表示形式を標準から文字列に変更したとき、表示形式は文字列になるが、実際のデータは数値の性質をもつ数値文字列に変更されていた
●その後、数値文字列のセルを操作すると、数値文字列が文字列に変換された
●表示形式を文字列から標準に戻すと、数値文字列は問題なく数値に戻るが、文字列のデータは、表示形式は標準に戻っても、実際のデータは文字列のままだった
●その後、文字列のセルを操作すると、データが数値に変換された

つまり、セルの表示形式で変更できるのは、文字通り「表示する形式」だけで、実際のデータの形式（データ型）は、セルを操作することで初めて変更される、ということです。この関係をまとめて図に表すと、次のようになります。
 

※ExcelVBAでデータ型を調べてみると、数値と数値文字列は、内部的にDouble型（倍精度浮動小数点型）で扱われていることがわかります。一方、文字列は、内部的にString型（文字列型）で扱われています。

単純に表示形式を標準に戻せば良いのでは？と思った方の中には、数値文字列が「数値と文字列の中間的なもの」というイメージを持った方がいらっしゃるかもしれません。しかし、上図のとおり、これらの関係は、「表示形式」の違いと実質的なデータ型の違いという観点で見る必要があります。
   

初めから表示形式を文字列に設定した場合

ちなみに、セルの表示形式をはじめから文字列に設定した場合、入力されたデータは文字列として扱われます。当然、こういったセルに数値を入力すると、初めから文字列として扱われます。数式を入力すると、入力した内容が文字列として表示されるだけで関数として機能しません。
   

根本的な問題解決に向けて

セルの表示形式の変更だけで「数値」と「文字列」を変更できるわけではないので、数値が入力されているセルの表示形式を、むやみに文字列に変更することは避けるべきです。

数値を左詰めで表示したいだけなら、表示位置を左詰めに変更すればよいだけのこと。

数値を厳密に文字列、つまり「数字」として扱いたい場合、関数で利用しない数値であることを確認して、初めから表示形式を「文字列」に設定しておきましょう。

※この場合、数字を入力したセルに、いちいち緑色の三角形が表示されます。エラーチェックオプションで非表示にすることもできますが、他の場所のエラーを関知できなくなるので、ちょっとうるさくても表示しておいた方が良いと思います。