CSVをExcelで開いたときに数値の先頭の"0"を表示させるには

きりんです。

プログラムでデータをCSVに出力、それをExcelで開くことって結構あると思いますが、「先頭が"0"の数字列は、Excelで開くと先頭の"0”が表示されない」って問題に前から悩まされていました。

社内で使用するプログラムやデータであればExcelの「外部データの取り込み」で列の形式を「文字列」にして読み込んでくれ、で済ませてしまうところですが、今回はお客様からご要望なので何とかするしかありません。

値をシングルクォートやダブルクォートで括ってみましたが、やはりダメ。 majirou君の記事を参考に、Excelで出力するしかない？

PHP-XLSXWriterの導入とはじめの一歩 - あるまげどーーーん

でも、お客様の使用しているレンタルサーバー上で動作するかどうか確認している時間もないし...。 半ば諦めつつネットでいろいろ検索したところ、以下のページに行き当たりました。

www.webimpact.co.jp

通常のCSVファイルは以下の様な形式ですね？

地区名,市外局番
東京,03
大阪,06

これを読み込んでも、Excelでは「03」は【数値】として扱ってしまい「3」になります。
これが0が消える原因です。
一方で、Excelでは【数値】ではなく【文字列】なら先頭の0が消えること無く扱えます。
そこで、次のようにCSVファイルを作ってみましょう。

地区名,市外局番
東京,=”03″
大阪,=”06″

=で始まる項目はExcelの方言で【数式】を表します。

CSVに記述された数式はExcelで開いた際にちゃんと解釈されるんですね。CSVデータをExcelで開くと、どんなデータであれテキストデータとして解釈される（数式、関数は実行されない）と思い込んでいました。

追加情報

これにて一件落着なのですが、他人の記事を紹介しただけでは何の学習にもつながらないので、ちょっとだけ追加で実験してみました。

使用したExcelは2016になります。

実験1

まず、出力したデータをExcelで取り込み、そこで集計等を行うとどうなるか試してみました。

セルA1とA2がCSVの出力データです。="001"と="002"ですね。ちゃんと先頭に0が表示されています。

CSVファイルをダブルクリックしてExcelで開き、そこに集計等を追加していきます。 A3で=SUM(A1:A2)、A4で=A1+A2、A5で=A1&A2の演算を行ってみました。

• A3のSUM関数は引数に数値しかとらない、あるいは数値以外は無視する（あるいは0として扱う）ため、合計が0となる
• 演算子を使用した場合、セルの値に応じて書式（型）が変換される
  • A4の数値を対象とした演算子（＋）の場合、セルの値が計算可能な内容なら数値型に変換し計算が行われる
  • A5の文字列を対象とした演算子（＆）の場合は文字列型に変換し処理が行われる

元データがどんな形式であれExcel上で開いているので、当然ですがExcel本来の挙動となります。

実験2

次に、CSV生成時点で上記の数式・関数を出力し、それをExcelで開いたときにどうなるかを試してみました。

こちらがCSVデータ。

="001"
="002"
=SUM(A1:A2)
=A1+A2
=A1&A2

それをExcelで開いた結果。

実験1と同じ結果となりました。 CSVに数式や関数を書いても、それをExcelで開けばちゃんと動くんですね。

実験3

これは確認するまでもありませんが、実験1、2においてExcelで開いたCSVファイルを、Excel上でそのままCSVで保存したらどうなるかを確認しました。

保存前

="001"
="002"
=SUM(A1:A2)
=A1+A2
=A1&A2

保存後

001
002
0
3
001002

Excel上で関数や演算が実行された結果のみがCSVに出力されました。

以上、CSVに関する小ネタでした。