【Excel】毎日集計のCSVファイルもクエリ機能で一発自動更新：Tech TIPS

さまざまな集計結果を毎日Excelで処理しなければならない、という人もいるのではないだろうか。集計結果がCSVファイルなどで出力されるのであれば、「Query（クエリ）」機能を利用すると、簡単にファイルの読み込みが簡単になる。このQuery機能の使い方を紹介しよう。

[塩田紳二，著]

連載目次

対象：Office 2013／2016／2019／365

ExcelでCSV／テキストファイルを読み込む
拡張子が「.csv」のタブ区切りファイルを読み込むと、正しくセルに読み込めないことがある。毎回、セルに正しく値を読み込ませるために区切り記号を置換するなどしていないだろうか。CSV／テキストファイルを効率よく読み込む方法を解説する。

　カンマ区切りファイル形式（以下、CSV：Comma Separated Values）やタブ区切りのテキストファイルは、さまざまなプログラムで出力形式としてサポートされている。そのため、これらの形式のファイルを「Microsoft Excel（エクセル）」で読み込んで、集計するといった作業が比較的広く行われている。

　CSVファイルなどは、デフォルトでExcelと関連付けされているため、エクスプローラーでこれをダブルクリックして開いている人も少なくないだろう。しかし、CSVファイルなどをエクスプローラーでダブルクリックしてExcelを起動するのは、はっきりいって「間違い」だ。特に繰り返し作業する場合は、効率が悪い。

　CSVファイルを直接開いてしまうと、編集した後の保存時には、別の形式を選ぶ必要がある。というのは、CSVファイルはセルの値を保持することはできても、セル書式や数式といったExcelが持つさまざまな形式を保存できないからだ。うっかり、そのまま保存しようとして警告のダイアログを見て、［名前を付けて保存］メニューから選び直す様子をしばしば見かける。

　また、CSVファイルやテキストファイルをExcelで開いている間、そのファイルはロック状態となり、上書きや追記、削除などができなくなる。このとき、外部のプログラムがCSV／テキストファイルを更新しようとすると最悪クラッシュなどを引き起こしてしまう。

　作業途中のExcelを開きっぱなしにしておいて、CSVファイルがロックされたので外部プログラムがエラーになったという経験をされたユーザーもいるのではないだろうか？　特にログ関連の作業では、こうした事故が起こりやすい。スクリプトを起動して、エラーになったのでウィンドウを移動してみたら、Excelが起動したままだった、という経験を筆者もしたことがある。

そんな場合には「Query」を使う

　Excelには、「Query（クエリ）」という機能があり、これを使うと、シートに自動的にCSVファイルを読み込めるようになる。Queryは、テキストファイルやCSVファイルをデータベースに見立て、これをシートに自動的に読み込むことができる。そしてQueryは、そのデータソースと読み込み方法を記憶するため、2回目以降はQueryの「更新」を行うだけで最新情報に更新が行われる。また、設定によっては、ブックを開いたときに自動的に更新させることも可能なので、2回目以降の作業が格段に楽になる。

　Queryは、各種のデータベースを解釈する「データベースプロバイダー」というソフトウェアモジュールを介して、さまざまな「データベース」から情報を取り出しシートに転送する機能だ。このとき、対象のデータベースを「ソース」と呼ぶ。ExcelのQueryには、CSVファイルやテキストファイルをデータベースソースと見立てて扱う「CSVファイルプロバイダー」が標準で組み込まれている。

　Queryは、かつては、Excelとは別のプログラムだったこともあった。しかし現在ではExcel自体に組み込まれ、Excel 2016以降は「Power Query」と呼ばれている。ただし、Office 2016版とOffice 365版では、同等の機能ながら名称や操作に違いがある（リボン上の名称などは気軽に変えないでほしいものである）。なお、Office 2013向けには、Power Queryのアドインが提供されている（「Download Microsoft Power Query for Excel from Official Microsoft Download Center」からダウンロード可能）。

　Queryでは、ソースから情報を得た後、これを表データとしてセルに転送する。このため、CSV／テキストファイルは、読み込まれた後、ロック状態にならない。

Queryを使えばCSVファイルはロックされない
CSVファイルを直接開くと、ExcelはCSVファイルをロックし、開いている間に勝手に書き換えなどが行われないようにする。しかしQuery経由だと、最初にCSVファイルを読み込んだ後、ファイルを解放するため、ファイルはロックされない。このため、Excelが起動していても、外部プログラムがCSVファイルを上書き追記することが可能になる。

　また、1回Queryを行うと、そのときのソースや読み込みに関する情報を記憶するため、2回目以降はQueryの「更新」を行うだけで、最新情報（例えば、上書や追記きされたファイル）に更新できる。

Queryを使うとExcelブックにソースや変換方法も記録される
Queryを使うと、対象ファイルや変換方法などを記憶しブックに保存するため、2回目からは、更新処理が簡単に行える。

　前置きが長くなってしまったが、ここではQueryを使ってCSV／テキストファイル（複数も可）をシートに読み込み、その後別のシートにコピーするなどして、データを集計、処理する方法を解説する。ただし、対象となるCSV／テキストファイルが外部のプログラムが出力することを想定しており、フォルダやファイル名（つまりファイルのパス）は固定されているものとする。例えば、「C:\temp\excel\file.csv」がパスであり、外部プログラムは、常にこのファイルを上書きまたは追記することを想定している。

　なおQueryは、Office 2013／2016と、Office 2019／365で名称などが異なっている。そこで、ここではバージョンごとに説明を行う。

QueryでCSV／テキストファイルの読み込みを行う（Office 2016の場合）

　永続ライセンス版のExcel 2016では、以下の方法でQueryによるCSV／テキストファイルの読み込みを行う。

QueryでCSV／テキストファイルの読み込みを行う（1）
空のシートを選択する。Excel 2016では、アクティブシートにQueryの表データが作られる。［データ］タブの［外部データの取り込み］−［テキストファイル］を使って対象CSV／テキストファイルを読み込む。キーボードからなら［Alt］＋［A］キーの後に［F］キー、［T］キーである。

▼

QueryでCSV／テキストファイルの読み込みを行う（2）
［ファイルオープン］ダイアログで、CSV／テキストファイルを指定する。なお、拡張子が「.csv」で中身がタブ区切りというファイルでも構わない。また、対象ファイルはテキスト型式であれば、拡張子は何であってもよい。

▼

QueryでCSV／テキストファイルの読み込みを行う（3）
［テキストファイル］ウィザードが開くので、最初のページで区切り方法の指定を行う。何も指定する必要がなければ、［Enter］キーで次のページに進むことができる。

▼

QueryでCSV／テキストファイルの読み込みを行う（4）
［テキストファイル］ウィザードの2ページ目では、区切り文字（タブやカンマ）を指定する。このとき、プレビュー領域に縦線が入り、区切り位置を示すので正しく分割されているかを確認すること。特に指定が必要なければ、［Enter］キーで先に進むことができる。

▼

QueryでCSV／テキストファイルの読み込みを行う（5）
［テキストファイル］ウィザードの3ページ目では、列単位でデータ書式の指定を行う。もし列に日付データが含まれていて、日本国内で標準的な形式でない場合（海外で作られたソフトウェアの場合、「M/D/Y」などの形式が使われることがある）には、ここで形式を指定することで、セルを日付データとして解釈させることができる。ここも特に指定が必要なければ、［Enter］キーで終了させることができる。

▼

QueryでCSV／テキストファイルの読み込みを行う（6）
［データの取り込み］ダイアログが開くので、読み込んだデータを置く先頭セルを指定する。

▼

QueryでCSV／テキストファイルの読み込みを行う（7）
ダイアログを閉じるとファイルが読み込まれシートに表が作られる。もし複数のCSV／テキストファイルを読み込む場合には、別のシートを開いて［データ］タブの［外部データの取り込み］−［テキストファイル］からの手順を再度繰り返す。

▼

QueryでCSV／テキストファイルの読み込みを行う（8）
対象ファイルが更新されたら、［データ］タブの［接続］−［すべて更新］で再度読み込みを行うことができる。［すべて更新］ではブック内のQuery全てが、［更新］では開いているシート上のQueryが更新される。逆にこの操作を行わないと、Queryで作られた表データはそのままになる。

プロパティを設定する

　対象のCSV／テキストファイルが定期的に更新され、同じような作業を繰り返すような場合には、プロパティを設定すると作業効率が上がる。

　Office 2016では、対象シートを開き、［データ］タブの［接続］−［プロパティ］を使って設定を変更できる。例えば、「更新時にファイル名を確認」を「オフ」にすると、更新作業を行うときにファイル名を確認するダイアログが省略され、前回と同じデータソースが読み込まれるため、手順を1つ省くことができる。また「ファイルを開くときにデータを更新する」を「オン」にすると、ブックファイルを開いたときに自動的にQueryの更新が行われるため、手動で更新操作を行う必要がなく、すぐに作業を開始できるようになる。

Office 2016でプロパティを設定する
プロパティを設定すると、更新時に読み込みファイル名を確認しなくなり、ブックファイルを開くときに自動的にQueryを更新してくれるようになる。

QueryでCSV／テキストファイルの読み込みを行う（Office 365の場合）

　サブスクリプションのOffice 365版のExcelでは、Queryの名称などが、Office 2016などと少々異なっている。ただし、ダイアログなどが違うだけで基本的な考え方は同じである。注意するのは、Office 2016版と違って、CSV／テキストファイルの拡張子が「.csv」「.txt」以外の場合に初回のみデータ形式を指定する必要があることなどだ。