0落ち？シリアル値？CSVファイルをExcelで開くときの注意点

はじめに

皆さんは普段の生活で、Excelを使う機会はありますか？
Excelはデータの分析やグラフ作成にとても便利なソフトのため、仕事のみならず、家計簿の作成、年賀状の名簿一覧など、日常生活で使っている人も多いかもしれません。

タブレットタイムレコーダーを利用している中では、例えば次のような場合にExcelを使います。

自動集計できない項目を、CSVファイルとExcelの関数から求めたい
出力した給与のCSVファイルを他の形式に変更したい
従業員マスタの取り込みを行うため、CSVファイルを加工したい

しかし、上記のようなケースで、CSVファイルをExcelで編集しようとすると、困ってしまうことがあります。
この記事では、そんな場合にどうしたらよいか、ご紹介します。

1.CSVファイルとは

そもそもCSVファイルって何？というところから説明します。

CSVはComma Separated Value（カンマで区切られた値）の略であり、CSVファイルはテキストデータの各項目を、カンマ（,）で区切って表現したファイルです。
言葉では理解しにくいので、画像で見てみましょう。

まず、こちらがExcelでCSVファイルを開いたときの画像です。

普段、Excelでデータを作成・編集しているときと同じように見えますよね？CSVといっても、特に変わったことはなさそうです。

では、Windowsの「メモ帳」のような、テキストエディタと呼ばれるソフトでCSVファイルを開いてみましょう。以下はメモ帳を使った場合の画像です。

先程のExcelを使った場合と比較してみると、2つの違いが見つかります。

メモ帳では項目の区切りが「,（カンマ）」になっている
メモ帳ではメンバーコード「001」だが、Excelでは「1」になってしまっている

1つ目はCSVファイルの正体を示しています。Excelで開いた際には、閲覧・編集しやすいように、表形式でデータを表示しますが、実は、CSVファイルは、各データの列を「,（カンマ）」で、行を「改行」で区切った形式の、ただのテキストファイルです。そのため、扱いが容易で互換性が高く、色々なシステム・ソフトで採用されています。タブレットタイムレコーダーでも様々な給与システムと連携できるように、CSV形式でファイルを出力しています。

2つ目はExcelでCSVファイルを編集する際、発生しがちな問題です。ここでは、本来「001」のデータが、Excelで開くと意図せずゼロが消えてしまい、「1」になっています。このようにCSVファイルをExcelで取り扱う際には、注意しなければ、問題が起きてしまうケースがあります。次のセクションでは、どのような問題が起きる可能性があるか、ご紹介します。

2.ExcelでCSVファイルを編集するときの注意点

ExcelはCSVファイルを見やすく表示してくれますが、編集する場合は注意が必要です。

システムから出力したCSVファイルをダブルクリックして、Excelで開いてしまうと、以下のような問題が起きる場合があります。

従業員番号「000123」が「123」になってしまった（ゼロ落ち、0落ち）
時間数「27:00」が「1900/01/01 03:00:00」になってしまった（シリアル値）
日付「7/1」が「7月1日」になってしまった（表示形式の変換）
文字化けしてしまった（文字コードの不一致）

Excelには、セルに入力された数値の形式（日付、時刻、分数等）に基づき、書式設定を自動的に変更する機能があります。しかも、このまま保存してしまうと、例えば、本来「000123」であるはずのデータが「123」に変換されたままになってしまいます。
そして、データが変換された状態で、他のシステムに連携しようとすると、エラーが起きてしまうことも…。
他にもExcelでCSVファイルを開いた際、文字化けが発生する場合があります。文字化けは、CSVファイルの文字コードがExcelの想定する文字コードと異なる場合、文字が正しく表示されないという現象です。
次に、これらの問題への対処方法の例をご紹介します。

3.対処方法の例

この記事では、以下の3つを紹介します。

CSVファイルをインポートする
テキストエディタで開いたCSVデータをExcelへ貼り付ける
外部のサービス、システムを利用する

画像付きで手順を紹介するので、参考にしてみてください。
※各項目をダブルクォーテーション（”）で囲っている形式のCSVファイルの場合、この記事で紹介する方法では、ファイルを開くことはできますが、保存時にダブルクォーテーションが削除されてしまいます。多くのシステムで利用する上では問題ありませんが、ご留意ください。

3-1. CSVファイルをインポートする

Excelには、CSVファイルのインポート（取り込み）用の機能が用意されていて、それを活用すれば、元の形式を保ったまま、CSVファイルを開くことができます。
Excelのバージョンによっては、操作が一部異なる場合がありますので、ご注意ください。
ここでは、Excel 2019（またはOffice 365）とExcel 2013の2つのバージョンでご紹介します。

3-1-1. Excel2019（Office 365）

Excel 2019ではPowerQueryを利用する方法と従来のウィザードを利用する方法の2つがあります。PowerQueryを利用する方法では表形式で分析しやすい結果になり、従来のウィザードではExcel2013以前と同じ操作でインポートすることができます。お好きな方を使ってみてください。

PowerQueryを利用する方法
1. 新しいExcelファイル（「空白のブック」）を開き、「データ」タブの「データの取得と変換」グループから「テキストまたはCSVから」を選択する。
2. 編集したいCSVファイルを選択する。
3. 以下の画面が開いたら、左上の「元のファイル」に適切な文字コードが選択されていることを確認し、右下の「データの変換」を選択する。
4. 以下の画面が開いたら、画面中央部に表示されるデータを「Shift＋矢印キー」で全選択し、画面上部の「変換」グループから「データ型：テキスト」を選択する。
5. 列タイプの変更ダイアログが表示されたら、「現在のものを置換」を選択する。
6. 画面左上の「閉じて読み込み」を選択するとインポート完了です。
  
  ※インポート後にExcelの関数を利用して、日数や時間数を集計する場合は以下の手順でデータを調整してください。
  1. 集計したいデータの列を選択し、右クリックで「セルの書式選択」を選択する。
  2. 「ユーザー定義」の「種類」に、集計するデータに応じて、以下を入力する。
    集計するデータが日数や回数の場合　：0
    　※小数点第1位まで必要な場合は0.0としてください。
    集計するデータが時間数の場合：[h]:mm
  3. IIで設定した「セルの書式設定」はセルをダブルクリックするなどして編集モードにしないと反映されないため、このままではExcelの関数を使った集計ができません。１セルずつ書式設定を反映させるには手間がかかりますが、「区切り位置選択ウィザード」を使うと一括で書式設定を反映させることができます。
    集計するデータの列を選択したまま、「データ」タブから「データツール」グループの「区切り位置」を選択する。
  4. 「完了」を押すと、データを集計する準備は完了です。
7. 編集後、保存する際は「ファイルの種類」で「CSV」、または保存したい形式を選択する。

従来のウィザードを利用する方法
1. 新しいExcelファイル（「空白のブック」）を開き、「データ」タブの「データの取得と変換」グループから「従来のウィザード」、「テキストから(レガシ)」を選択する。
  
  ※「従来のウィザード」ボタンがない場合は、「ファイル」を選択後、左下に表示される「オプション」から、「データ」タブ内にある「テキストから(レガシ)」にチェックを入れてください。
2. 編集したいCSVファイルを選択する。
3. 以下の画面が開いたら、「元のデータ形式」に「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていること、中央部右の「元のファイル」に適切な文字コードが選択されていることをそれぞれ確認し、「次へ」を選択する。
4. 区切り文字で「コンマ」が選択されていることを確認し、「次へ」を選択
5. 以下の画面が開いたら、「データのプレビュー」から全ての列を選択し、左上の「列のデータ形式」から「文字列」を選択します。全ての列を選択する際は、左端の列を選択してから、Shiftキーを押しながら、右端の列を選択するとスムーズです。その後、右下の「完了」を選択する。
6. データの展開先を指定することができます。特に指定が無ければ、そのまま「OK」を選択すると取り込み完了です。
  
  ※インポート後にExcelの関数を利用して、日数や時間数を集計する場合は以下の手順でデータを調整してください。
  1. 集計したいデータの列を選択し、右クリックで「セルの書式選択」を選択する。
  2. 「ユーザー定義」の「種類」に、集計するデータに応じて、以下を入力する。
    集計するデータが日数や回数の場合　：0
    　※小数点第1位まで必要な場合は0.0としてください。
    集計するデータが時間数の場合：[h]:mm
  3. IIで設定した「セルの書式設定」はセルをダブルクリックするなどして編集モードにしないと反映されないため、このままではExcelの関数を使った集計ができません。１セルずつ書式設定を反映させるには手間がかかりますが、「区切り位置選択ウィザード」を使うと一括で書式設定を反映させることができます。
    集計するデータの列を選択したまま、「データ」タブから「データツール」グループの「区切り位置」を選択する。
  4. 「完了」を押すと、データを集計する準備は完了です。
7. 編集後、保存する際は「ファイルの種類」で「CSV」、または保存したい形式を選択する。

3-1-2. Excel2013

１．新しいExcelファイルを開き、「データ」タブの「外部データの取込」グループから「テキストファイル」を選択する。
編集したいCSVファイルを選択する。
以下の画面が開いたら、「元のデータ形式」に「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていること、中央部右の「元のファイル」に適切な文字コードが選択されていることをそれぞれ確認し、「次へ」を選択する。
区切り文字で「カンマ」が選択されていることを確認し、「次へ」を選択する。
以降の手順は(Excel 2019)向けの「・従来のウィザードを利用する方法」の５．以降と同じです。

3-2. テキストエディタで開いたCSVデータをExcelへ貼り付ける

CSVファイルをExcelで直接開くのではなく、テキストエディタを経由することで、データの形式を保ったまま、Excelでデータを扱うことができます。

CSVファイルを右クリックして、「プログラムから開く」を選択する。その後、メモ帳などのテキストエディタを選択する。
テキストエディタの「置換」機能を利用して、「,（カンマ）」を「（タブ文字）」に置き換える。注意：タブ文字は「置換後の文字列」のテキストボックスに直接入力することができません。別のテキストファイルにTabキーで入力したものを、「置換後の文字列」にコピーペーストしてください。
※以下はWindowsの「メモ帳」を利用した場合の画像です。メモ帳の場合、「置換」機能は「編集」から選択可能です。
新しいExcelファイル（「空白のブック」）を開き、Ctrlキー＋Aキーでセルを全選択する。そして、右クリックから「セルの書式選択」、表示形式の分類から「文字列」を選択する。
テキストエディタのCSVデータをコピーしてから、Excelに貼り付けると完了です。

※貼り付け後にExcelの関数を利用して日数や時間数を集計する場合は以下の手順でデータを調整してください。
1. 集計したいデータの列を選択し、右クリックで「セルの書式選択」を選択する。
2. 「ユーザー定義」の「種類」に、集計するデータに応じて、以下を入力する。
  集計するデータが日数や回数の場合　：0
  　※小数点第1位まで必要な場合は0.0としてください。
  集計するデータが時間数の場合：[h]:mm
3. 式や関数を入力する先のセルや列に対して、セルの書式設定を「標準」に変更してください。
4. IIとⅢで設定した「セルの書式設定」はセルをダブルクリックするなどして編集モードにしないと反映されないため、このままではExcelの関数を使った集計ができません。１セルずつ書式設定を反映させるには手間がかかりますが、「区切り位置選択ウィザード」を使うと一括で書式設定を反映させることができます。
  Ⅰ～Ⅲで書式を設定したセル、列を選択したまま、「データ」タブから「データツール」グループの「区切り位置」を選択する。
5. 「完了」を押す。
編集後、保存する際は「ファイルの種類」で「CSV」、または保存したい形式を選択してください。

3-3. 外部のサービス、システムを利用する

CSVファイルのデータを編集するためにExcelではなく、外部のサービス、システムを使うという方法もあります。
例えば、Googleスプレッドシートを使うことでCSVファイルを簡単に取り扱うことができます。ここではGoogleスプレッドシートを使った方法をご紹介します。

新規のスプレッドシートを開く。
画面左上の「ファイル」から「インポート」を選択し、対象のCSVファイルをアップロードする。
「テキストを数値、日付、数式に変換する」のチェックが外れていることを確認し、インポートを実行すると完了です。

データを集計する場合、以下の手順で、表示形式を変更してください。
1. 集計したいデータを選択する。
2. 画面左上の「表示形式」→「数字」→「カスタム数値形式」を選択する。
3. 集計したいデータが日数や回数の場合は「0」または「0.0」、時間数の場合は「[h]:mm」を入力する。
編集が完了したら、「ファイル」の「ダウンロード」から「カンマ区切り形式（.csv）」を選択すると、保存が可能です。