さまざまな集計結果を毎日Excelで処理しなければならない、という人もいるのではないだろうか。集計結果がCSVファイルなどで出力されるのであれば、「Query(クエリ)」機能を利用すると、簡単にファイルの読み込みが簡単になる。このQuery機能の使い方を紹介しよう。
この記事は会員限定です。会員登録(無料)すると全てご覧いただけます。
対象:Office 2013/2016/2019/365
カンマ区切りファイル形式(以下、CSV:Comma Separated Values)やタブ区切りのテキストファイルは、さまざまなプログラムで出力形式としてサポートされている。そのため、これらの形式のファイルを「Microsoft Excel(エクセル)」で読み込んで、集計するといった作業が比較的広く行われている。
CSVファイルなどは、デフォルトでExcelと関連付けされているため、エクスプローラーでこれをダブルクリックして開いている人も少なくないだろう。しかし、CSVファイルなどをエクスプローラーでダブルクリックしてExcelを起動するのは、はっきりいって「間違い」だ。特に繰り返し作業する場合は、効率が悪い。
CSVファイルを直接開いてしまうと、編集した後の保存時には、別の形式を選ぶ必要がある。というのは、CSVファイルはセルの値を保持することはできても、セル書式や数式といったExcelが持つさまざまな形式を保存できないからだ。うっかり、そのまま保存しようとして警告のダイアログを見て、[名前を付けて保存]メニューから選び直す様子をしばしば見かける。
また、CSVファイルやテキストファイルをExcelで開いている間、そのファイルはロック状態となり、上書きや追記、削除などができなくなる。このとき、外部のプログラムがCSV/テキストファイルを更新しようとすると最悪クラッシュなどを引き起こしてしまう。
作業途中のExcelを開きっぱなしにしておいて、CSVファイルがロックされたので外部プログラムがエラーになったという経験をされたユーザーもいるのではないだろうか? 特にログ関連の作業では、こうした事故が起こりやすい。スクリプトを起動して、エラーになったのでウィンドウを移動してみたら、Excelが起動したままだった、という経験を筆者もしたことがある。
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/テキストファイルは、読み込まれた後、ロック状態にならない。
また、1回Queryを行うと、そのときのソースや読み込みに関する情報を記憶するため、2回目以降はQueryの「更新」を行うだけで、最新情報(例えば、上書や追記きされたファイル)に更新できる。
前置きが長くなってしまったが、ここではQueryを使ってCSV/テキストファイル(複数も可)をシートに読み込み、その後別のシートにコピーするなどして、データを集計、処理する方法を解説する。ただし、対象となるCSV/テキストファイルが外部のプログラムが出力することを想定しており、フォルダやファイル名(つまりファイルのパス)は固定されているものとする。例えば、「C:\temp\excel\file.csv」がパスであり、外部プログラムは、常にこのファイルを上書きまたは追記することを想定している。
なおQueryは、Office 2013/2016と、Office 2019/365で名称などが異なっている。そこで、ここではバージョンごとに説明を行う。
永続ライセンス版のExcel 2016では、以下の方法でQueryによるCSV/テキストファイルの読み込みを行う。
対象のCSV/テキストファイルが定期的に更新され、同じような作業を繰り返すような場合には、プロパティを設定すると作業効率が上がる。
Office 2016では、対象シートを開き、[データ]タブの[接続]−[プロパティ]を使って設定を変更できる。例えば、「更新時にファイル名を確認」を「オフ」にすると、更新作業を行うときにファイル名を確認するダイアログが省略され、前回と同じデータソースが読み込まれるため、手順を1つ省くことができる。また「ファイルを開くときにデータを更新する」を「オン」にすると、ブックファイルを開いたときに自動的にQueryの更新が行われるため、手動で更新操作を行う必要がなく、すぐに作業を開始できるようになる。
サブスクリプションのOffice 365版のExcelでは、Queryの名称などが、Office 2016などと少々異なっている。ただし、ダイアログなどが違うだけで基本的な考え方は同じである。注意するのは、Office 2016版と違って、CSV/テキストファイルの拡張子が「.csv」「.txt」以外の場合に初回のみデータ形式を指定する必要があることなどだ。
複数のCSV/テキストファイルがある場合には、上記の手順を繰り返す。
2回目以降の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.