CSV のデータを Excel で使う/更新できるようにする

今回は、私がやっている Excel の集計コースの中から 1 つ、CSV ファイルの取り扱いについて書きます。

集計するデータが Excel ブックに保存されていて、リスト化されていれば、そのまま集計や分析を開始できますが、組織内には、たくさんの基幹システムやデータベースがあり、集計をするべきデータが Excel 以外の場所で保管されている可能性もあります。たとえば、集計や分析をしたいデータが、システム部門が管理しているデータベースにあるとき、このデータを入手してから、Excel で集計や分析を行ったりします。

Excel には、SQL Serverなどのデータベースにデータ接続をしたり、業務システムから抽出した CSV 形式のデータをインポートしたりして、Excel ブック以外の場所 (外部データソース) で管理されているデータを読み込む機能があります。
このため、業務システム自体に高度な集計・分析機能がなくとも、ユーザーが Excel の標準機能を活用することでデータの集計や分析を行うことができます。

こんな風に↓ CSV ファイルをデータの担当部門から入手しているよ、という方にご覧いただきたい内容です。

Excel での CSV データの利用

CSV ファイルをダブルクリックして開くと、たいていの場合は Excel が立ち上がります。しかし、この方法で CSV ファイルを開いたときには、ユーザーにとっての問題が発生したり、ユーザーが思うように集計作業を進められなかったりします。
たとえば、「001」などの固定桁数の数字文字列が「1」となって表示されてしまう、表示形式やその他の書式を設定したが、ファイルを上書き保存したときに書式がクリアされてしまう、上書き保存をしたのに「変更を保存しますか？」と表示されてとまどうなど、集計を行う、という本来の目的以外の作業に時間を取られてしまいます。

Excel を使って CSV ファイルのデータを集計するときは、集計場所となる Excel ブックを用意して、そのファイルに CSV ファイルのデータをインポートし (取り込み)、Excel ブックとして保存してからデータを利用するべきです。

CSV ファイルをダブルクリックで開くことはおすすめしません。
(やらないで！って言いたいくらい)

テキストファイルウィザードによるデータのインポート

Excel ブックへの CSV ファイルなどの外部データのインポートは、テキストファイルウィザードを使用して行うとスムーズです。

インポートするデータが含まれる CSV ファイルを、デスクトップなどの参照しやすい場所に配置しておき、インポートを行います。
なお、定期的に CSV ファイルを入手して Excel ブックに最新の結果を表示したい場合は、特定のフォルダーなどを作成して、CSV ファイルを保存しておいたほうがいいです。

Step1. データのインポート

ここでは、Excel ブック (集計作業.xlsx) に CSV データ (来客データ.csv) をインポートします。

今回使う [来客データ.csv] には、こんな感じのデータが入っています。

[集計作業.xlsx] を開いて、シート1 のセル A3 (データを取り込む位置) を選択して、リボンの [データ] タブの [外部データの取り込み] の [テキストファイルからデータを取り込み] をクリックします。
[テキストファイルのインポート] ダイアログボックスで [来客データ.csv] を選択して [インポート] をクリックします。
[テキストファイルウィザード - 1 / 3] で下記を選択して [次へ] をクリックします。
下記以外の設定は既定値とします。

[先頭行をデータの見出しとして使用する]：オン
※ [先頭行をデータの見出しとして使用する] は、Excel 2013 のみのオプションです。なければ何もしなくて OK。1 行目はデータではなくて列名だよ、という区別です。
[テキストファイルウィザード - 2 / 3] で [区切り文字] の [カンマ] をオンにして、[次へ] をクリックします。

※ 今回使うテキストファイルが CSV であり、データがカンマで区切られているので [カンマ] を追加しています。利用するテキストファイルのデータで使用されている区切り文字によって適切なものを選んでください。
[テキストファイルウィザード - 3 / 3] で [期間] フィールドを選択してから、[文字列] をオンにして [完了] をクリックします。

[期間] フィールドの選択は、下記の丸い枠の部分をクリックして行えます。

※ [期間] フィールドの値を [G/標準] のままで取り込むと数値に変換されるため、「001」が「1」となって取り込まれます。フィールドを選んでデータ形式を選ぶ、ということができるんだ！ということを知っていただきたいのです。
[データの取り込み] ダイアログボックスで [このデータをデータモデルに追加する] をオフのまま [OK] をクリックします。

※ [このデータをデータモデルに追加する] は、Excel 2013 のみのオプションです。このオプションをオンにすると、テーブルなど、データの表示の種類を選択できます。
ワークシートに CSV ファイルのデータがインポートされます。

[期間] フィールドの値 (001 など) が固定桁数の文字列であることを確認します。
CSV ファイルのデータをインポートしたとき、Excel ブックと CSV ファイルとの間に「接続」が設定されます。

この Excel ブックには、マイドキュメントの [集計データ] フォルダーの [来客データ.csv] への接続が設定されている、ということです。

この外部接続の設定を確認したり、削除したり、または変更したりするには、リボンの [データ] タブの [接続] の [接続] をクリックします。
[ブックの接続] ダイアログボックスが表示され、[来客データ] という接続が作成されていることが確認できます。
確認ができたら [閉じる] をクリックします。

接続を削除すると CSV ファイルとのつながりがなくなるので、CSV ファイルが更新されたときに Excel ブックも更新する、ということができません。そういった更新は必要ない、このデータが欲しかっただけ、という場合は、接続を選択して [削除] をクリックしてください。この場合もワークシート上のデータは残ります。(今回は削除せずに進めます。)

※ 名前は取り込んだ CSV ファイルの名前です。実際のデータとはおそらく異なるのでお気をつけください。
ファイルを上書き保存して閉じます。

Step2. データの編集と更新

CSV ファイルなどの外部データをインポートすると、Excel ブックの中に Excel と外部データをつなぐデータ接続が作成されます。
データ接続の設定には、参照する CSV ファイルなどの外部データへのパスや、更新を行う際の設定が含まれており、利用シーンに合わせて設定を変更できます。

ここでは、手動で CSV ファイルの内容を編集してファイルが更新された状態を作り、更新をすると Excel ブックの内容も更新されることを確認します。

[来客データ.csv] を右クリックして、[編集] をクリックします。
1 レコード目の [来客数] の値 (300) を 1000 に変更します。
CSV ファイルを上書き保存して閉じます。
Excel ブック (集計作業.xlsx) を開きます。
このとき、セキュリティの警告が表示された場合は [コンテンツの有効化] をクリックしてください。有効にしないと更新できません。
Excel ブックを更新します。
セル F4 の更新前の値が「300」であることを確認して、リボンの [データ] タブの [接続] の [すべて更新] の ▼ (文字の部分) をクリックし、[更新] をクリックします。
既定では [テキストファイルのインポート] ダイアログボックスが表示されます。
CSV ファイルを選択して [インポート] をクリックします。

CSV ファイルを外部データとしてインポートしたとき、既定では、更新時にデータソースとなる CSV ファイルの選択が促され、更新に利用するファイルを選択する必要があります。CSV ファイルの保存場所が固定されており、ファイル名も変更されないことがわかっている場合は、ファイル名の確認や指定を行わずに更新ができるように設定しておくと [更新] ボタンをクリックするだけで Excel ブックを最新の状態に更新できます。この設定をしたい！という方は手順の 8．に進んでください。
最新の結果がワークシート上に表示されます。セル F4 の値が「1000」に更新されていることを確認します。
更新時にファイル名の確認をせずに、接続されているファイルのデータで更新するには、接続設定にその指定をします。

[集計作業.xlsx] を開き、リボンの [データ] タブの [接続] の [接続] をクリックします。
[ブックの接続] ダイアログボックスに [来客データ] という接続が作成されていることを確認し、[来客データ] を選択して [プロパティ] をクリックします。
[接続のプロパティ] ダイアログボックスの [使用] タブの [更新時にファイル名を確認] をオフにして [OK] をクリックします。

この設定をオフにしておくと、更新時に [テキストファイルのインポート] ダイアログボックスは表示されません。
ただし、CSV ファイルの名前が変わったり、保存場所が変わったりすると更新できないので注意してください。

ちなみに、[定義] タブの [接続ファイル] で接続先ファイルのパスを確認できます。
[ブックの接続] ダイアログボックスの [閉じる] をクリックします。
ファイルを上書き保存します。