×
ブログ

2022/09/30/

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

はじめに

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

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

  1. 自動集計できない項目を、CSVファイルとExcelの関数から求めたい
  2. 出力した給与のCSVファイルを他の形式に変更したい
  3. 従業員マスタの取り込みを行うため、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ファイルの場合、この記事で紹介する方法では、ファイルを開くことはできますが、保存時にダブルクォーテーションが削除されてしまいます。多くのシステムで利用する上では問題ありませんが、ご留意ください。

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の関数を使った集計ができません。1セルずつ書式設定を反映させるには手間がかかりますが、「区切り位置選択ウィザード」を使うと一括で書式設定を反映させることができます。
        集計するデータの列を選択したまま、「データ」タブから「データツール」グループの「区切り位置」を選択する。

      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の関数を使った集計ができません。1セルずつ書式設定を反映させるには手間がかかりますが、「区切り位置選択ウィザード」を使うと一括で書式設定を反映させることができます。
        集計するデータの列を選択したまま、「データ」タブから「データツール」グループの「区切り位置」を選択する。

      4. 「完了」を押すと、データを集計する準備は完了です。
    7. 編集後、保存する際は「ファイルの種類」で「CSV」、または保存したい形式を選択する。
  •  

    3-1-2. Excel2013

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

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

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

    1. CSVファイルを右クリックして、「プログラムから開く」を選択する。その後、メモ帳などのテキストエディタを選択する。
    2. テキストエディタの「置換」機能を利用して、「,(カンマ)」を「 (タブ文字)」に置き換える。注意:タブ文字は「置換後の文字列」のテキストボックスに直接入力することができません。別のテキストファイルにTabキーで入力したものを、「置換後の文字列」にコピーペーストしてください。
      ※以下はWindowsの「メモ帳」を利用した場合の画像です。メモ帳の場合、「置換」機能は「編集」から選択可能です。

    3. 新しいExcelファイル(「空白のブック」)を開き、Ctrlキー+Aキーでセルを全選択する。そして、右クリックから「セルの書式選択」、表示形式の分類から「文字列」を選択する。
    4. テキストエディタのCSVデータをコピーしてから、Excelに貼り付けると完了です。

      ※貼り付け後にExcelの関数を利用して日数や時間数を集計する場合は以下の手順でデータを調整してください。

      1. 集計したいデータの列を選択し、右クリックで「セルの書式選択」を選択する。
      2. 「ユーザー定義」の「種類」に、集計するデータに応じて、以下を入力する。
        集計するデータが日数や回数の場合 :0
         ※小数点第1位まで必要な場合は0.0としてください。
        集計するデータが時間数の場合:[h]:mm

      3. 式や関数を入力する先のセルや列に対して、セルの書式設定を「標準」に変更してください。
      4. IIとⅢで設定した「セルの書式設定」はセルをダブルクリックするなどして編集モードにしないと反映されないため、このままではExcelの関数を使った集計ができません。1セルずつ書式設定を反映させるには手間がかかりますが、「区切り位置選択ウィザード」を使うと一括で書式設定を反映させることができます。
        Ⅰ~Ⅲで書式を設定したセル、列を選択したまま、「データ」タブから「データツール」グループの「区切り位置」を選択する。

      5. 「完了」を押す。
    5. 編集後、保存する際は「ファイルの種類」で「CSV」、または保存したい形式を選択してください。

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

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

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

      データを集計する場合、以下の手順で、表示形式を変更してください。

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

    4.まとめ

    本記事ではExcelでCSVファイルを取り扱うときの注意点、対処方法の例を紹介しました。CSVファイルを取り扱っていて、「あれ?」と思うことがあれば、是非この記事で紹介した方法を試してみてください。

    こちらの記事もオススメです

iPadでタイムレコーダー
月額0円の勤怠管理