【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を使って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／テキストファイルの読み込みを行う（7）ダイアログを閉じるとファイルが読み込まれシートに表が作られる。もし複数のCSV／テキストファイルを読み込む場合には、別のシートを開いて［データ］タブの［外部データの取り込み］−［テキストファイル］からの手順を再度繰り返す。
QueryでCSV／テキストファイルの読み込みを行う（8）対象ファイルが更新されたら、［データ］タブの［接続］−［すべて更新］で再度読み込みを行うことができる。［すべて更新］ではブック内のQuery全てが、［更新］では開いているシート上のQueryが更新される。逆にこの操作を行わないと、Queryで作られた表データはそのままになる。
プロパティを設定する
対象のCSV／テキストファイルが定期的に更新され、同じような作業を繰り返すような場合には、プロパティを設定すると作業効率が上がる。
Office 2016では、対象シートを開き、［データ］タブの［接続］−［プロパティ］を使って設定を変更できる。例えば、「更新時にファイル名を確認」を「オフ」にすると、更新作業を行うときにファイル名を確認するダイアログが省略され、前回と同じデータソースが読み込まれるため、手順を1つ省くことができる。また「ファイルを開くときにデータを更新する」を「オン」にすると、ブックファイルを開いたときに自動的にQueryの更新が行われるため、手動で更新操作を行う必要がなく、すぐに作業を開始できるようになる。
QueryでCSV／テキストファイルの読み込みを行う（Office 365の場合）
サブスクリプションのOffice 365版のExcelでは、Queryの名称などが、Office 2016などと少々異なっている。ただし、ダイアログなどが違うだけで基本的な考え方は同じである。注意するのは、Office 2016版と違って、CSV／テキストファイルの拡張子が「.csv」「.txt」以外の場合に初回のみデータ形式を指定する必要があることなどだ。
Office 365のQueryでCSV／テキストファイルの読み込みを行う（1）［データ］タブの［データの取得と変換］セクションにある［テスキトまたはCSVから］をクリックする。キーボードからなら［Alt］＋［A］キーの後に［T］キー、［1］キーである。Office 365版では、デフォルトでは新規シートに表データが作られる。
Office 365のQueryでCSV／テキストファイルの読み込みを行う（2）［データの取り込み］ダイアログが表示されるので、ここで対象ファイルを指定する。このとき拡張子が「.csv」なら、その中身はタブ区切りファイルなどExcelが対応可能な形式であれば問題はない。
Office 365のQueryでCSV／テキストファイルの読み込みを行う（3）ファイルを指定するとテーブル読み込み用のダイアログが開くので、プレビューを確認して問題なければ、右下の「読み込み」をクリックする。
Office 365のQueryでCSV／テキストファイルの読み込みを行う（4）対象ファイルの拡張子が「.csv」「.txt」以外の場合でも、中身がCSVやタブ区切りファイルであれば対応が可能だ。こうしたファイルを指定すると、Power Queryエディターが開く。ここで、左上の［形式を指定して開く］を使い、CSVファイルを選ぶ。これで読み込みが行われ、表データのプレビューが表示されるようになる。最後に［閉じて読み込む］をクリックしてPower Queryエディターを終了させる。
Office 365のQueryでCSV／テキストファイルの読み込みを行う（5） 新規シートにデフォルトの「テーブルスタイル」書式設定されたテーブルが読み込まれ、自動的に先頭行に見出しが設定される。テーブルスタイルは表示だけなので、気にならなければこのままで問題はない。なお、どうしてもデザインが気に入らないなら［テーブルデザイン］タブの［テーブルスタイル］で変更することもできる。
複数のCSV／テキストファイルがある場合には、上記の手順を繰り返す。
2回目以降のQueryによる読み込みは、「更新」で行う。これには［データ］タブの［クエリと接続］−［すべて更新］を使う。また、プロパティを設定することもできる。
Office 365のQueryで「更新」を設定する2回目以降は、［データ］タブの［すべて更新］メニューの［更新］または［すべて更新］を使って、CSV／テキストファイルの内容をExcel側に反映させる。
プロパティを設定する
プロパティは、［データ］タブの［更新］−［接続のプロパティ］で［クエリプロパティ］ダイアログを開く。ここで、「ファイルを開くきときにデータを更新する」を「オン」にすると次回ブックを開いたときに前回と同じデータソースから更新が行われるようになる。繰り返し同じ作業をする場合には便利だ。
Office 365でプロパティを設定するブックを読み込むときに自動的にQueryを更新したいなら、［テータ］タブの［すべて更新］メニューの［接続のプロパティ］を開き、「ファイルを開くときにデータを更新する」を「オン」にする。
読み込んだデータを使う
作業が終わったら、シートにCSV／テキストデータが入る。Office 365版では、自動的に先頭行が追加されてフィルターモードがオンになるため、並べ替えや抽出がすぐにできる。特に加工集計する必要がなければ、このままでも構わないし、作られた表データの上に行を挿入して、そこで集計関数などを使うことも可能だ。
あるいは、条件付き書式を使った色分けもできる。なお、こうした集計データを追加する場合、表データの上に数式などを追加するとよい。表データの下は、ソースが更新されたときに位置が変わるため、見るのが面倒になる。Office 365版では、表データはテーブル形式となっているため、表データの左側などに数式を入れると、残りのセル全てに自動的に数式がコピーされる。
その他、必要なデータを取り出して、別のシートを作るといったこともあるだろう。この場合、処理先のシートにデータをコピーするには、［Ctrl］＋［A］キーで表データ全体を選択（Office 365版では先頭行の見出しを外して選択してくれる）できるので、［Ctrl］＋［C］キーで全部をコピーして貼り付けることができる。
処理先のシートに過去のデータなどが入っている場合、テーブル内で［Ctrl］＋［↓］キーで最下行にアクティブセルを移動して、［Ctrl］＋［←］キーで先頭列に移動できる。この方法を使えば、簡単に表の下のセルに移動できるので、後は［Ctrl］＋［V］キーで貼り付けを行えばよい。
なお、CSV／テキストファイルが追記されるものである場合、データが重複することがある。この場合もExcelの機能を使って重複を削除できる。重複データを削除する方法は、Tech TIPS「Excelで住所録などの重複データを一発で削除する」を参照してほしい。
エクスプローラーからCSVファイルを開いていたのでは、いつまでも楽にならない。Query機能を使うことで、2回目以降の読み込み操作が格段に楽になり、繰り返し作業が簡単に行えるようになる。
Copyright© Digital Advantage Corp. All Rights Reserved.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。