Excelファイルからデータを取得する方法

パワークエリを使用するにあたって、最初に行うべき作業は「データの取得」となる。そこで今回は、Excelファイルからデータを取得するときの操作手順を解説していこう。また、複数のワークシートに保存されているデータ表を結合して取得する方法も紹介しておこう。

Excelファイルからデータを取得するときの操作手順

パワークエリを使って自動処理の手順を登録するときは、

（1）データを取得する
（2）用途に合わせてデータ表を加工する
（3）データ表をExcelに出力する

という流れで作業を進めていくのが基本だ。つまり、「データの取得」が最初に行うべき作業となる。ということで、今回は「Excelファイル」からデータを取得するときの操作手順を詳しく解説していこう。

Excelファイルからデータを取得

クエリを登録するExcel（空白のブックなど）を開き、「データ」タブにある「データの取得」コマンドをクリックする。続いて、「ファイルから」→「Excelブックから」を選択する。

「データの取得」コマンドの操作

「データの取り込み」ダイアログが表示されるので、データの取得元となるExcelファイルを指定し、「インポート」ボタンをクリックする。

データの取得元になるExcelファイルの指定

すると、「ナビゲーター」ウィンドウが表示される。ここでは「どのワークシートからデータを取得するか？」を指定すればよい。今回の例のようにワークシートが1枚しかない場合も、ワークシートの指定が必要だ。

ワークシートを指定すると、右側に「取得されるデータのプレビュー」が表示される。これを確認し、「データの変換」ボタンをクリックする。

ワークシートの選択とプレビュー

データの取得が実行され、「Power Query エディター」が起動する。念のため、プレビュー画面を上下左右にスクロールして、データが正しく取得されていることを確認しておこう。

「Power Query エディター」に取得されたデータ

スクロールしてデータを確認

以上で「データの取得」は完了だ。難しい点は特に見当たらないので、すぐに操作手順を覚えられるだろう。

ただし、状況によっては、各列の「見出し」がヘッダー（列名）として正しく認識されないケースもある。この場合は「Power Query エディター」で適切な処理を行い、ヘッダーを自分で指定しなおさなければならない。

この操作手順は状況に応じてケース by ケースになるため、一概には説明できない。一般的には、（1）不要な行を削除する、（2）データの1行目をヘッダーに昇格させる、という処理を行えばよいが、そのためには「Power Query エディター」を自由に扱えるスキルを習得しておく必要がある。「不要な行の削除」については第11回の連載で詳しく紹介する予定なので、もう少しだけ待って頂けると幸いだ。

そのほか、取得元のExcelファイルを修正して「データの取得」をやり直す、という方法も考えられる。表のタイトル文字などを削除し、各列の「見出し」がワークシートの1行目になるように修正すると、たいていの場合、ヘッダーが正しく認識されるはずだ。

「読み込み」ボタンをクリックした場合は？

先ほど紹介した「ナビゲーター」ウィンドウには、「データの変換」ボタンのほかに、「読み込み」というボタンも用意されている。

「読み込み」ボタンをクリック

こちらのボタンをクリックした場合は、「Power Query エディター」を経由することなく、取得したデータ表がそのままExcelに出力される仕組みになっている。

Excelに出力されたデータ表（テーブル）

つまり、「取得したデータ表を一切加工しないで、そのままExcelに出力する」という動作になる。データ表を一切加工しないのであれば、取得元のExcelファイルをコピーして利用する場合とたいして変わらないので、このボタンを利用する機会は滅多にない。

よって、通常は「データの変換」ボタンをクリックしてデータを取得する、と覚えておけばよい。

ワークシートが複数ある場合は？

続いては、取得元のExcelファイルに「複数のワークシート」が含まれていた場合について解説していこう。

たとえば、以下の図のように「各日の売上データ」が個別のワークシートに記録されているExcelファイルがあったとしよう。「4月1日」～「4月5日」のワークシートには、同じ形式で各日の売上データが記録されている。

各日の売上をワークシートで管理しているExcelファイル

この場合、「ナビゲーター」ウィンドウにも「4月1日」～「4月5日」のシート名が表示される。いずれかのワークシートを選択して「データの変換」ボタンをクリックすると……、

ワークシートの選択とプレビュー

そのワークシートに記録されているデータだけを取得した状態で「Power Query エディター」が表示される。上図のように操作した場合、「4月3日」のワークシートに記録されているデータだけが取得されることになる。

「Power Query エディター」に取得されたデータ

とはいえ、すべてのワークシートを結合してデータを取得したいケースもあるだろう。続いては、各ワークシートのデータを結合するときの操作手順を紹介していこう。

各ワークシートにあるデータ表を結合したい場合は？

すべてのワークシートからデータを一括取得するときは、「シート名」ではなく、「ファイル名」を選択する。この場合、プレビューは表示されないが、そのまま「データの変換」ボタンをクリックすればよい。

「Excelファイル名」を選択してデータを取得

「Power Query エディター」が起動し、各ワークシートの情報が表示される。

「Power Query エディター」に取得されたデータ

続いて、各ワークシートのデータ（テーブル）を展開する。「Data」列の右端にあるボタンをクリックし、展開する列を指定する。通常は、すべて列が選択された状態のまま「OK」ボタンをクリックすればよい。

展開する列の指定

テーブルが展開され、各ワークシートのデータを結合した表が表示される。ただし、各列のヘッダーは「Data.Column1」などの列名になっており、正しく認識されていない。このため、データ表を整理していく必要がある。

展開されたデータ

まずは、不要な列（各ワークシートの情報）を削除しよう。「Item」、「Kind」、「Hidden」の列を選択し、「列の削除」コマンドから「列の削除」を選択する。なお、表の左端にある「Name」の列は日付データとして活用できるので、そのまま残しておく。

不要な列の削除

続いて、ヘッダーを指定する。現時点では、「列名」となるべき文字が「データの1行目」に配置されている。これをヘッダーに昇格させたいときは、「1行目をヘッダーとして使用」をクリックする。

1行目をヘッダーとして使用（1）

1行目をヘッダーとして使用（2）

各列の1行目がヘッダーになり、そのデータが「列名」に指定される。このとき、表の左端にある「Name」の列も、列名が「4月1日」に置き換えられる。このままでは適切でないので、列名の部分をダブルクリックし、列名を「日付」などに変更する。

列名の変更

データ表の2行目以降をよく見ていくと、各ワークシートのデータを結合した部分に「見出し」の文字列データが残っていることに気付くと思う。これらの行は不要だ。また、「売上」の合計を算出した行も不要であると考えられる。

不要な行にあるデータ

これらの行はフィルターを使って削除する。今回の例の場合、「数量」の列が「合計」または「数量」の行を削除すればよい。よって、「数量」の列にある「▼」ボタンをクリックし、「合計」と「数量」のチェックを外してから「OK」ボタンをクリックする。

フィルターを使って不要な行を削除

「合計の行」と「見出しの行」が削除され、データ部分だけを結合した表になる。これでデータ表を適切な形に加工することができた。

不要な行を削除したデータ表

このように、各ワークシートのデータを結合して取得するには、データ表を適切な形に整形する処理を指定しなければならない。

「Power Query エディター」に慣れていないと少し難しく感じるかもしれないが、今回の連載で紹介したコマンド（処理）は、どれも基本的なものでしかなく、いつかは覚えなければならいコマンドといえる。これらのコマンドすら使えないようでは、「用途に合わせてデータ表を加工する」なんて、とうてい不可能だ。

各コマンドの使い方は、第11回以降の連載で詳しく説明していくので、焦らずに、ひとつずつ順番にマスターしていくとよいだろう。