CSVファイルをExcelが勝手解釈してしまう時の「押さえどころ」について

Excelの勝手解釈でCSVデータが変わる

CSVファイルをExcelで開くことは当たり前のようにできますが、実はその一方で幾つかの部分では「ExcelがCSVデータの内容を勝手に解釈して自動変換する」ことをご存知でしょうか?
既に別ページで既出の点もありますが、ここではそれらの点に絞ってまとめています。

ExcelでCSVファイルを開いた時に「何かが違う」を感じたら・・

ExcelはCSVデータをシートに表示するときに、CSVファイルのデータの内容に応じて自動的に変換してしまいます。
しかも画面上に変換したことを告げるメッセージ等もありません。CSVデータが「所定の条件」にヒットすると、Excelは自動的に変換し表示してしまうのです。

これで助けられている部分もあれば、大きなお世話な時もあるかもしれません。
どのように変換されるのは以下に例示しますが、「Excelで開いたCSVのデータの何かが違う?」と感じた時はこの変換を疑って見る必要があるかもしれません。

CSVファイルについて簡単におさらいすると

例を示す前に、「CSVって言われてもイマイチ・・・」だと理解も進まないと思います。そこで簡単なおさらいをしたいと思います。

CSVファイルの内容(データ)は、Excelで見ることができる

CSVファイルとExcelファイル(種類)は別物です。ただExcelではCSVファイルを開き、その中身を見ることができます。
しかも通常だとExcel(ソフト)とCSV(ファイル)は、予め「Excelで開ける」ように設定されています(ファイルの関連付け)。従いCSVファイルのアイコンをダブルクリックすれば自動的にExcel上からデータを見ることができるわけです。
csvとexcelファイルのアイコンとexcelバージョン拡張子の違い
※右側の「Excelバージョンの違い」は補足・余談です・・

CSVファイルのデータは「カンマで区切られた」テキストファイル

CSVデータと言えば、項目同士をカンマで区切ったテキストデータですし、その仕組自体はかなりシンプルです。
またCSVファイルはExcel以外でも開くことができます。CSVファイルは単なるテキストデータの集まりで、そのテキストデータを扱えるソフトならExcelに限らず開いたりすることができます。

csvファイルをexcelで開く

セルのデータによって、「(セル内で)位置を変えて表示」する

今回のCSVデータに限らずですが、Excelはセルに入力されたデータを「自動的に識別」し、それによりセルの中で表示する位置、すなわち「左寄せ」あるいは「右寄せ」して表示します。
(これは、単なる見た目だけの話ではなく、思ったように集計できない原因にも繋がります)

簡単に言うと、セルに入力されている値が
・文字の場合:左側に表示
・数値の場合:右側に表示

セル上では文字の属性に応じてセル内での位置が変わる
※上記の1行目は「意図的」に中央寄せにしています。

これはCSVファイルのデータに限った話ではないですが、Excelによって勝手気ままに変換された時、どのデータがいじられたかの手がかりになります。

Excelはシート上のデータをCSVファイルとして保存することができる

ExcelはCSVファイルを開くだけでなく、セルに文字等を入力しCSVファイルとして保存することもできます。
exceファイルをcsvとして保存可能

Excelから見れば、入口と出口の両方を押さえていることになりExcelさえあれば他のソフトは要らないとも言えます。
ですが実際にはそうはいかない場合もあるんです。。。

それはどういうときか、それでは以下をどうぞ。

CSVファイルに「時間」を表すデータがある場合

皆さんがお持ちのCSVファイルに「時間を表すデータ」は含まれているでしょうか?
もしもある場合、少し知っておいたほうが良い場面があります。

【サンプルデータ】
出勤月,就業日数,担当者名
2015年10月,10日,東京太郎
2015年10月,22日,山田花子

csvファイルのサンプルデータ

上記データをExcelで開くと↓こうなります。
数式バーで値を確認

一見すると「見た目通りに取り込めている」様に見えますが、どこが違うか分かるでしょうか?
セル内の位置的に言うと、A列が右寄せになっていることに気づきます。つまりExcelではA列の値は、「数値として認識」していることになります。
「2015年10月が数値」とはどういうことですかね?
数式バーで実際に入力されている値を直接見てみると・・・
日にちが自動的に入力されている

よく見ると、「日にちが入力」されています。これ(2015/10/1)は、CSVファイルの時には無かった文字です。
つまり、Excelが勝手に日にち「1日」を入れたということです。B列も同じような値にも関わらずCSVデータを正しく表示できている分、余計わかりにくく感じますね。
しかも、ご丁寧に?見た目上だけは表記を合わせてくれています。
セルの書式設定で日にちを消している

その結果、やっかいなことにオートフィルタは、しっかり日付扱いのデータとなっています(手作業なら気づきますが、マクロ、VBAで自動化する時は注意ですね)。
オートフィルタに日にちが含まれている

CSVファイルに「時間」を示すデータが無い場合でも・・

このように時間を表すデータが有るとき、セルへの展開時には「過敏になったほうが良い」というのが経験則ですが、他にもあります。
例えば、電話番号のように「0」で始まる「数値のみのデータ」も自動変換の対象になってしまいます。

0で始まる数値も先頭文字が削られる

ちなみに、こうなってしまった対処として見かけるのは以下のように「= “0” & B2」のように数式を作り別セルに表示するやり方です。

0を含む数式で対処

色々なCSVのデータをExcelで開いてみる

思う限りで色々な種類のデータを含むCSVファイルを作成してみました。
※興味有る方は以下をコピーして、下の画像にあるようにそのままメモ帳等に貼り付け後、CSV保存してExcelで開いてみてください
※作成したファイルは起動済みのExcelに直接ドラッグしてください(その理由の一部は後ほど・・)
※文字化けするときは文字コードをUTF8で保存してみてください

【サンプルデータ】

データ,備考
03-1111-2222,ハイフン付き電話番号
1-1,数値とハイフン
1/1,数値とスラッシュ
1+1,計算式(のつもり)1
=1+1,計算式(のつもり)2
¥100,円マークあり
10,全角の10
10,全角、半角混在の10
10,半角、全角混在の10
01/10/01,YY/MM/DD(2001年10月1日のつもり)1
10/01/01,YY/MM/DD(2010年1月1日のつもり)2
10/15/01,MM/YY/DD(2015年10月1日のつもり)3
平成27年10月1日,和暦の日付
2015/2/29,有り得ない日付
13:30:00,時間(24時間表記)
1:30:00 PM,時間(12時間表記1)
PM 1:30:00,時間(12時間表記2)
=A1,CSV読み込み時にA1を表示?
=A1+2,A1の値に2を加算できる?
=LEN(A1),関数が使える?
50%,パーセント表示
‘100,先頭にシングルクォート
“1000”,ダブルクォートくくりの1000
“2015/10/1”,ダブルクォートくくりの日付

様々なデータを含むcsvファイル

これらをExcelで開いた時にどのようになるのか予想がつきますか?
で、見てみると・・・

excelで色々なデータをcsvファイルを開いてみる

セルに展開されたが文字が「左寄せ」と「右寄せ」でExcelの解釈を見てみます。
私的には気になる結果がいくつか出てきました。
・6、19~21行目で「=」を含む文字は数式や関数として表示
・7行目の、「¥」マークは、右寄せで表示されているので文字として見られてない
・14行目の和暦の日付も日付として表示
・23行目も数値を文字化させるのに、シングルクォート(アポストロフィ?)を使いますが、直接セルで入力せずに今回のようにCSVとして取り込むと文字として認識されるようです(LEN関数等を使う場合に気をつける必要がありますね)。

CSVファイルのデータを「ありのまま」で表示するには

上記のような事象(Excelの解釈)を回避するには、「文字列」として取り込んでしまうのが最も早い方法のように思います。
別ページでも一部触れていますが、「テキストファイルとして開く」形でシートに展開する方法です。
ちなみに上記のファイルをExcelから呼び出す(「ファイルを開くダイアログ」からCSVファイルを選択する)ときに「テキストファイルウィザード」が開いたでしょうか?
恐らく色々な文字種類があり、Excel側で気をきかせてくれたのだと思いますが、この「テキストファイルとして開く」形にすれば、ありのままのデータで表示ができます。

テキストファイルとして開く場合のウィザード画面
テキストファイルとして開く場合のウィザード画面
カンマ区切りと(今回のデータはダブルクォートを含んでいるので)「文字列の引用符」でダブルクォーテーションを入れます。

テキストファイルとして開く場合のウィザード画面
ここで全ての項目(列)を「文字列」にします。

テキストファイルウィザードで「文字列」として取り込んだ結果が下記です。
各項目を文字列として取り込む

ただし、この場合、(後の加工作業等で)数値として扱いずらい側面もあります。実際にはウィザード時に列ごとに適宜「G/標準」や「日付」等にして設定すると良いでしょう(要はケースバイケース)。
あるいは、Excelで開く以前の「CSVファイル作成時」に「=」と「ダブルクォート」で括ることでも「見た目上は」回避できます。

CSVファイル同士を確認(比較)するには

これまでの説明で見たように、とある作業で、CSVファイル同士を比較する必要する時等に気をつけなければならない点が見えてきます。
例えば
CSVファイル1のデータ:2015年10月1日
CSVファイル2のデータ:2015年10月

とある場合、CSVで見るとデータは「違います」が、Excelで展開してしまうと共に「2015年10月1日」なってしまい、「同じ」となります。
他にも例で示したように全角数値も半角扱いされるなどもあります。
仮にそれでもよければ、問題無いです。しかしながら、その他にも上記で示したようにデータ内容次第でExcel側の色々な解釈があり得るため、潜在的な問題を考慮したほうが良いように思います。
そのため、もしExcel上で比較するなら、やはり「テキストファイルウィザード」で文字列として表示する形が無難かと思います。
あるいは、弊社ではプログラムを作成し、そこで比較するようにしています。

このようにCSVファイルはExcelで開くことができます。そして多くの場合はそれで解決となりますが、一方で今回のようなExcel側の勝手気ままな解釈が問題となりうることもあるということは知っておく必要があると思います。
(ExcelのCSVデータの解釈は今回挙げた内容が全てではない点にも注意してください)

タイトルとURLをコピーしました