PR

日々の仕事で活用するExcelが、予想外の挙動をして驚いた経験はないだろうか。自分のミスならまだしも、正しく操作しているのに奇妙に振る舞われると「不具合?」と疑いたくなる。そんなトラブルの例を仕組みから解き明かし、対処法を解説しよう。

謎1.入力した文字が勝手に変化する

【現象】 「0」が消える、日付に変わる、「:」が増える・・・

⇒【解説】 データの種類を間違って認識していることが原因

 「0001」と入力したのに「1」としか表示されない(図1)、「6-2」と入力したら「6月2日」になってしまった(図2)。Excelでそんな現象に遭遇したことはないだろうか。「3000:1」と入れたのに「3000:01:00」と表示(図3)、「(1)」と入れたのに「-1」と表示されるなど、セルに入力した内容が勝手に変わることがある。その仕組みを知らなければ、どうしてよいか分からなくなるだろう。

図1 「0001」のように0で始まる番号をセルに入力し、[Enter]キーで確定すると、手前に入れた0が消えてしまう
図2 「6-2」のようなハイフン区切りの数字を入力すると、「6月2日」のように日付に変わってしまうことがある
図3 比率を表そうと「3000:1」(3000対1)などと入力すると、「3000:01:00」という不可解な表示になる

 入力した通りに表示されない原因は、Excelが備えるデータの自動認識機能にある。

 Excelは、セルに入力されたデータを自動的に認識し、数値、日付、時刻、文字列などと判別した上でセルに格納する。そして、数値の場合は「¥」記号を付けたりカンマで3桁区切りにしたりと、必要に応じて表示を変えられる状態にする。日付の場合は「12/27」と入れるだけで「12月27日」のように表示し、さらには「December-10」「2010年12月27日」「平成22年12月27日」などと自在に表示を変えられるようにする。

 問題が生じるのは、ユーザーの意図に反してデータの自動認識が行われた場合だ。図2の例で説明しよう。

 「6-2」とセルに入力すると、標準でExcelは「日付が入力された」と判断する。欧米では日付を「2010-12-27」のようにハイフン区切りで表記するケースがあり、ハイフン区切りの数値を日付と認識する仕様になっているからだ(図4)。結果として「6-2」は「6月2日」と認識され、そのようにセルに表示される。「6-2」だけでは年の情報がないが、Excelはその年の西暦年を自動で付与するので、データとしては2010年6月2日となる。

タイトル
図4 図2の現象が起きる仕組み。「6/2」のようなスラッシュ区切りのデータだけでなく、「6-2」のようなハイフン区切りのデータも、Excelは“日付”と認識する。すると、その年の西暦年を加えて日付データ化し、標準の表示形式である「○月○日」の形でセルに表示する

 「0001」が「1」となるのも同様だ。「0001」は数値と認識されるので、余計な0が省略されて「1」と表示される。「3000:1」に至っては、コロン区切りであることから時間と誤解され、「3000時間1分」と認識される。その上で「0秒」が追加されて「3000:01:00」という表示になる。「(1)」が「-1」になるのも、かっこ付きの数値を「マイナスの値」と自動認識するため。会計の世界でマイナス値がかっこ付きで表記されるからだ。