2015年8月
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          

最近のトラックバック

最近買った本(Amazon.co.jp)

広告(Amazon.co.jp)

広告(Google AdSense)

無料ブログはココログ

« Windows入門: エクスプローラーの新規作成の「テキストドキュメント」を復活させる。 | トップページ | Eclipse入門: 日本語APIドキュメントをローカル環境、及び、ホバー表示で使用する。 »

2011年9月 1日 (木)

Excel入門: CSVファイルをダブルクリックで開いたとき、数値の先頭の0が削除されないようにする。(増補版)

はじめに

ExcelではCSVファイルをダブルクリックで開いたとき、各列の表示形式が一律に標準となる。そのためデータが数字列の場合、自動的に数値に変換されて読み込まれる。通常ではこれで問題ないが、0011のように先頭に0が付いたデータの場合、先頭の0が削除されてしまう。同様に1-2-1、1/2/2ようにハイフン、または、スラッシュで区切られた数字が日付に変換される、1:2:3のようにコロンで区切られた数字が時間に変換される、全角数字が半角に変換されるなどの問題がある。

期待した結果

001,002,003           → |001     |002     |003     |
1-2-1,1/2/2,1:2:3     → |1-2-1   |1/2/2   |1:2:3   |
121,122,123  → |121  |122  |123  |

実際の結果

001,002,003           → |       1|       2|       3|
1-2-1,1/2/2,1:2:3     → |2001/2/1|2001/2/2| 1:02:03|
121,122,123  → |     121|     122|     123|

回避方法としては、先にExcelを起動して、ファイルメニューの開くから(Excel 2007以前の場合)、または、データメニューのテキストファイルから(Excel 2010の場合)ファイルを読み込み、テキストファイルウイザードで各列に表示形式を指定する方法が一般的だが、これをダブルクリックしか知らない相手に一から説明するのはなかなか面倒である。

この件については、以前某所に回避方法を書いたことがあったが、今回追加の調査を行ったので増補版としてあらためてまとめてみた。

回避方法の考察

とにかくデータを文字列として認識させれば良いということで、ダブルクォーテーションで括ってみる。→だめである。読み込み時にダブルクォーテーションが削除されてしまうので同じ結果になってしまう。

"001","002","003"           → |       1|       2|       3|
"1-2-1","1/2/2","1:2:3"     → |2001/2/1|2001/2/2| 1:02:03|
"121","122","123"  → |     121|     122|     123|

では、データの先頭に文字列をあらわすシングルクォーテーションを付けてみる。→文字列として読み込まれるが、シングルクォーテーションが表示されたままとなる。セルを一個一個確定し直せば意図した結果になるが…。

'001,'002,'003           → |'001    |'002    |'003    |
'1-2-1,'1/2/2,'1:2:3     → |'1-2-1  |'1/2/2  |'1:2:3  |
'121,'122,'123  → |'121 |'122 |'123 |

回避方法

データをダブルクォーテーションで括った上で先頭にイコールを付けて数式にしてしまう。こうすると読み込み時にダブルクォーテーションが削除されないので文字列として表示される。しかし、このCSVファイルをExcel以外のアプリケーションで読み込むと当然問題が発生する。

="001",="002",="003"           → |001     |002     |003     |
="1-2-1",="1/2/2",="1:2:3"     → |1-2-1   |1/2/2   |1:2:3   |
="121",="122",="123"  → |121  |122  |123  |

データの先頭にタブ文字を付ける(以下の<tab>は実際にはタブ文字)。こちらの方がスマートで良い。しかも、タブ文字は空白文字の一つなのでExcel以外のアプリケーションでも問題が発生しにくそうである。

<tab>001,<tab>002,<tab>003           → |001     |002     |003     |
<tab>1-2-1,<tab>1/2/2,<tab>1:2:3     → |1-2-1   |1/2/2   |1:2:3   |
<tab>121,<tab>122,<tab>123  → |121  |122  |123  |

同様にデータの末尾にタブ文字を付けても良い。むしろこちらの方が問題が発生しにくいかも。

001<tab>,002<tab>,003<tab>           → |001     |002     |003     |
1-2-1<tab>,1/2/2<tab>,1:2:3<tab>     → |1-2-1   |1/2/2   |1:2:3   |
121<tab>,122<tab>,123<tab>  → |121  |122  |123  |

Excelでのタブ文字の扱い

データにタブ文字を付ける方法でCSVファイルを読み込んだ場合、タブ文字は読み込み時に削除されたわけではなく、読み込まれたデータにしっかり付加されている。実際にセルのデータにカーソルを立て、矢印キーで移動すると横幅0の文字があることが分かる。以下のようにLEN()関数でデータの文字数を表示すると一文字分多く表示される。

A B:数式 B:表示
1 001 =LEN(A1) 4
2 1-2-1 =LEN(A2) 6
3 121 =LEN(A3) 4

しかも、このタブ文字は前後の空白文字を削除するTRIM()関数では削除できない。

A B:数式 B:表示
1 001 =LEN(TRIM(A1)) 4
2 1-2-1 =LEN(TRIM(A2)) 6
3 121 =LEN(TRIM(A3)) 4

タブ文字を削除するにはSUBSTITUTE()関数を使用する。尚、Excelではタブ文字を直接入力する方法がないので代わりにCHAR(9)を使用する。

A B:数式 B:表示
1 001 =LEN(SUBSTITUTE(A1,CHAR(9),"")) 3
2 1-2-1 =LEN(SUBSTITUTE(A1,CHAR(9),"")) 5
3 121 =LEN(SUBSTITUTE(A1,CHAR(9),"")) 3

以上

2011/09/02 記事のカテゴリーを変更した。

« Windows入門: エクスプローラーの新規作成の「テキストドキュメント」を復活させる。 | トップページ | Eclipse入門: 日本語APIドキュメントをローカル環境、及び、ホバー表示で使用する。 »

Excel」カテゴリの記事

Windows」カテゴリの記事

コメント

素晴らしい!!! おかげで助かりました。ありがとうございます!

コメントを書く

(ウェブ上には掲載しません)

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/578502/52620084

この記事へのトラックバック一覧です: Excel入門: CSVファイルをダブルクリックで開いたとき、数値の先頭の0が削除されないようにする。(増補版):

« Windows入門: エクスプローラーの新規作成の「テキストドキュメント」を復活させる。 | トップページ | Eclipse入門: 日本語APIドキュメントをローカル環境、及び、ホバー表示で使用する。 »