Power Query（パワークエリ）は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とExcel2013は、Power Queryアドインをインストールすることで使用することができます。
このPower Queryを使用すると、どう便利なのか事例をもとに解説します。
たとえば、以下のようなマトリックス形式のクロス表もPower Queryでは、少しの操作でテーブルに変換できます。
Excel2019 バージョン1906（ビルド11727.20244）での解説です。
Power Query（パワークエリ）については、以下の記事が参考になると思います。分かりやすく解説されています。Power Queryって何？って方は、ぜひお読みください。お勧めの記事です。
【初心者向け】Power Queryで元データを整形しよう（概要編）
以下は、Microsoftの解説ページです。
Excel の Power Query について - Microsoft サポート（Microsoft）
参考Power Queryを使用しない場合の方法は、以下の記事で解説しています。
-
ピボットテーブルウィザードを使ってクロス集計をテーブル形式に変換
行と列で構成されたクロス集計表は、ピボットテーブルウィザードの［複数のワークシート範囲］を選択して、リスト形式に変換することができます。 ピ ...
Power Queryを利用するには
Excel2019では、［データ］タブの［データの取得と変換］グループにある［テーブルまたは範囲から］を使用するか、
［データの取得］をクリックして、［ファイルから］をポイントして［ブックから］をクリックします。
参考［テーブルまたは範囲から］をクリックすると、テーブルではない範囲の場合は、自動的にテーブルに変換されます。もし、範囲をテーブルに変換したくない場合は、事前にデータ範囲をコピーして操作するか、［データの取得］の［ブックから］を選択してデータをインポートして操作してください。
変換する範囲がすでにテーブル形式の場合は、［テーブルまたは範囲から］をクリックして、同一ブック内に作成してもいいと思います。［データの取得］の［ブックから］を選択して操作する場合も、範囲をテーブルに変換しておくと、クエリでの操作がより簡単になります。
Excel2016の場合
Excel2016の場合は、［データ］タブの［取得と変換］グループにある［テーブルから］をクリックします。
あるいは、［データ］タブの［取得と変換］グループにある［新しいクエリ］をクリックして、［ファイルから］をポイントして［ブックから］をクリックします。データをインポートして操作します。
Excel2013またはExcel2010の場合
Excel2013とExcel2010でPower Queryを使用するには、Power Queryアドインをインストールする必要があります。
インストール方法については、以下の記事で解説しています。
-
Excel 2013にPower Queryをインストールして［POWER QUERY］タブを表示
Excel 2013にPower QueryアドインをMicrosoftのダウンロードセンターのページからインストールしてみました。 OSは ...
［POWER QUERY］タブをクリックして、［Excelデータ］グループにある［テーブル/範囲から］をクリックします。
または、［POWER QUERY］タブをクリックして、［外部データの取り込み］グループにある［ファイルから］をクリックして［Excelから］をクリックします。データをインポートして操作します。
参考ご使用中のOfficeのバージョンを確認する方法は、以下の記事で解説しています。
-
Office（Word、Excel、PowerPointなど）のバージョンを確認する方法
Microsoft Officeも長い時間を経て、大きく様変わりしてきました。 トラブルを解決しようとするとき、あるいは、何かの操作を探すと ...
サンプルのクロス集計表について
ここでは、以下のようなマトリックス形式のクロス表をサンプルとして使用します。
県名のセルは、［セルを結合して中央揃え］が設定してあります。
Power Queryなら、セルの結合がされていても大丈夫です。
この表として完成された表（テーブルではない範囲）を、ピボットテーブルにできるテーブルに変換します。
Power Queryでは、接続された状態なので、元の表で値が変更されると、更新して変更された値を反映させることもできます。
ここでは、まず［テーブルまたは範囲から］をクリックして操作します。
［テーブルまたは範囲から］を選択してクエリを作成
表内の1つのセルを選択して、あるいは表の範囲を選択します。
ここでは、セル［A3］を選択します。結合したセルがある場合は、結合したセルは選択しないほうが効率的です。結合されたセルを選択していると、自動的に範囲を選択されない場合があります。
テーブルまたは範囲から
選択したExcelテーブルにリンクされる新しいクエリを作成します。
選択したデータがテーブルまたは名前付き範囲ではない場合は、テーブルに変換されます。
［テーブルの作成］ダイアログボックスが表示されます。
テーブルに変換される範囲が間違っていないかを確認します。もし、データ範囲を修正する必要があれば、［テーブルの作成］ダイアログボックスの［拡大/縮小ボタン］をクリックして、範囲を指定します。
確認して問題がなければ、［OK］ボタンをクリックします。
Power Query エディターが表示されます。
参考ウィンドウ右側にある［クエリの設定］の［適用したステップ］にはクエリに記録された作業が順に表示されていきます。通常のワークシートのように元に戻すボタンなどはありませんが、［適用したステップ］の［×］をクリックすることで、操作を取り消すことができます。
ここから先の操作は、以下へ続きます。
［ブックから］をクリックしてクエリを作成
新規ブックを起動して、［データ］タブの［データの取得］をクリックして、［ファイルから］の［ブックから］をクリックして操作する方法です。
この方法では、元のブックはそのままです。範囲が自動的にテーブルに変換されることはありません。
まず、Power Queryでデータを整形したブックのシート名を確認しておきます。取り込む前にプレビューで確認することもできますが、先に確認しておくことをお勧めします。
［データの取得］をクリックして、［ファイルから］をポイントして［ブックから］をクリックします。
［データの取り込み］ダイアログボックスが表示されます。
目的のブックスを選択して［インポート］ボタンをクリックします。
［ナビゲーター］ウィンドウが表示されます。目的のシート名をクリックすると、右側にプレビューが表示されます。［データの変換］ボタンをクリックします。
Power Query エディターが表示されます。
行の削除
Power Query エディターで、少し整えます。
1行目は不要なので、削除します。
［ホーム］タブの［行の削減］グループにある［行の削除］ボタンをクリックして、メニューから［上位の行の削除］をクリックします。
以下の［上位の行の削除］ウィンドウが表示されます。
先頭から削除する行の数を指定します。
［行数］のテキストボックスに［1］と入力して、［OK］ボタンをクリックします。
1行目が削除されます。
1行目をヘッダーとして使用
1行目をヘッダーにしたいので、［ホーム］タブの［変換］グループにある［1行目をヘッダーとして使用］をクリックします。
以下のように整います。
参考取り込む元となるデータがテーブルになっている場合は、上記の行を削除したり、1行目をヘッダーとして使用する作業は不要になります。
［フィル］ボタンでセルの値を下方向へコピー
［県名］の列には、［null］が表示されています。この［null］のセルを上のセルの値に変換します。
［県名］の列を選択します。
［変換］タブをクリックします。［任意の列］グループにある［フィル］の▼ボタンをクリックして、［下］をクリックします。
ポップヒントには以下のように記されています。
現在選択されている列のセルの値を隣接する空のセルに下方向へコピーします。
以下のように上のセルの値がコピーされます。
参考上のセルの値をコピーする方法は、以下の記事で解説しているように、Power Queryを使用しなくてもできますが、Power Query エディターで操作するほうが簡単です。
-
空白セルに上のセルの値を入力するには簡単な数式を使ってできる
表の空白セルに上のセルの値を入力したい場合の方法です。データが少ない場合は、コピー＆ペーストでいいのですが、データが膨大になると大変な作業に ...
列のピボット解除
マトリックス形式のクロス表をテーブル形式にするための変換を行います。Power Query エディターでは、［列のピボット解除］という機能になります。
［A製品］から［C製品］の列までを選択します。
そして、［変換］タブの［任意の列］グループにある［列のピボット解除］の▼ボタンをクリックして、メニューの中から［選択した列のみをピボット解除］をクリックします。
ポップヒントには以下のように記されています。
現在選択されている列のみ、属性/値のペアに変換します。
以下のようにピボットが解除されて、［属性］と［値］の列になります。
列の名前の変更
［属性］と［値］の列の名前を変更します。
［属性］の列を選択して、［変換］タブの［任意の列］グループにある［名前の変更］をクリックします。
列の名前が反転しますので、そのまま名前を入力します。ここでは、［製品名］とします。
参考列の名前の変更は、列名の箇所でダブルクリックしても入力できる状態になります。
同様に［値］の列も名前を変更します。ここでは［個数］にします。
閉じて読み込む
求めたいテーブルの形にできたので、Excelのワークシートへ読み込みます。
Power Query エディターの［ホーム］タブの左端に［閉じて読み込む］ボタンがあります。
［ホーム］タブの［閉じて読み込む］の下側の文字の箇所をクリックします。すると、［閉じて読み込む］と［閉じて次に読み込む...］のメニューが表示されます。
［閉じて読み込む］をクリックします。
このクエリへの変更を保存します。クエリエディターウィンドウを閉じて、結果を既定の読み込み先に読み込んでください。
新規ワークシートにテーブルとして読み込まれます。右側には、［クエリと接続］作業ウィンドウが表示されます。
［テーブルデザイン］タブが表示されているので、この後はテーブルの機能を使って、さまざまな操作をすることができます。また、ピボットテーブルに変換することもできます。
テーブルの便利機能については、以下の記事で解説しています。
-
表をテーブルに変換すると何ができる？変換方法と便利機能まとめ
Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...
テーブルから範囲（通常の表）にしたい場合は、［テーブルデザイン］タブの［ツール］グループにある［範囲に変換］をクリックします。
ただし、範囲に変換すると、クエリとの接続は解除されます。
また、セルの背景色、文字色、罫線などの書式はテーブルのままになります。
範囲に変換した後、書式をクリアする方法については、以下を参照してください。
また、書式を残したくなければ、先にテーブルのスタイルをクリアして、［範囲に変換］を実行する方法もあります。
参考［ホーム］タブの［閉じて読み込む］の下側の文字の箇所をクリックすると、［閉じて読み込む］と［閉じて次に読み込む...］のメニューが表示されます。［閉じて次に読み込む...］を選択すると、［データのインポート］ダイアログボックスが表示されます。
このクエリへの変更を保存します。クエリエディターウィンドウを閉じて、結果の既定の読み込み先を指定してください。
［データのインポート］ダイアログボックスで表示する形式とデータの出力先を選択できます。
ピボットテーブルを作成したいのであれば、ここで選択すると効率的です。
既定では、［テーブル］と［新規ワークシート］が選択されています。
データの更新
取り込み元のデータが変更されると、作成したテーブルにも反映されます。
以下の画像は、［テーブルまたは範囲から］を選択してクエリを作成し、同一ワークシートに読み込んだものです。
例として、青森県の第一営業部のA製品の値を［120］から［130］に変更してみます。
テーブル内のセルを選択して、［データ］タブの［すべて更新］から［更新］をクリックします。
参考ブック内で複数の外部データとの接続がある場合、［すべて更新］をクリックすると、ブック内のすべての外部データの接続が更新されます。特定のデータのみ更新したい場合は、［更新］をクリックします。
ポップヒントで分かりますが、ショートカットキーは以下のとおりです。
- すべて更新［Ctrl］+［Alt］+［F5］
- 更新［Alt］+［F5］
以下のようにテーブルの値も変更されます。
データの増減があった場合も反映されます。
［クエリ］タブから更新
更新は、［クエリ］タブをクリックして、［読み込み］グループにある［更新］ボタンをクリックしても同じです。
注意Power Query エディターから読み込んだテーブルをクリックしないと［クエリ］タブは表示されません。
Power Query エディターの再表示
Power Query エディターを再表示する方法は、いくつかあります。
クエリをダブルクリック
［クエリと接続］作業ウィンドウのクエリでダブルクリックします。
ショートカットメニューから
クエリで右クリックしてショートカットメニューから［編集］をクリックします。
［クエリ］タブの［編集」ボタンから
または、［クエリ］タブの［編集］グループの［編集］ボタンをクリックします。ポップヒントには、以下のように記されています。
編集
Power Query エディターを使って、このクエリをフィルター処理または修正します。
ショートカットキー
Power Queryエディターを起動するショートカットキーは、［Alt］+［F12］です。
ファイルパスの変更
ブックから取り込んだ後、ブックの保存先が変更になった場合は、パスを変更することもできます。
上記のどの方法でも良いので、Power Query エディターを表示します。
そして、［クエリの設定］の［適用したステップ］の［ソース］をクリックします。
以下のウィンドウが表示されますので、［参照］ボタンをクリックして、保存先のブックを指定します。
フィルパスが変更されたことを確認して、［OK］ボタンをクリックします。
あるいは、［ホーム］タブの［データソース設定］ボタンをクリックして、［データソース設定］ウィンドウを表示します。そして、［ソースの変更］ボタンをクリックすると、上記の同じファイルパスを変更するウィンドウが表示されます。
クエリの削除
テーブルをクエリと接続しておきたくない場合は、クエリを削除します。クエリを削除しても、テーブルはそのまま残ります。更新ができなくなるというだけです。
［クエリと接続］作業ウィンドウのクエリで右クリックして、ショートカットメニューから［削除］をクリックします。
［クエリと接続］作業ウィンドウが表示されていない場合は、［データ］タブの［クエリと接続］グループにある［クエリと接続］をクリックします。
［クエリの削除］メッセージウィンドウが表示されますので、［削除］をクリックします。
○○を削除しますか？このクエリを削除すると、このクエリによって読み込まれたデータを更新できなくなります。
クエリの削除は、［クエリ］タブの［編集］グループの［削除］ボタンをクリックしても同じです。
関連以下の記事では、1行に複数の情報が入力された表をPower Query エディターを使用してテーブルに変換する方法を解説しています。
-
先頭行に複数の同じ項目名がある表をPower Queryでテーブルに変換
Excel2016から標準で装備されているPower Query（パワークエリ）を使用すると、効率よくデータを加工できます。 Excelの表 ...
1列の複数行で1レコードになっているような表もPower Query エディターで複数列のテーブルに整形することができます。
-
1列の複数行で1レコードになっている表をPower Queryでテーブルに変換
１列の複数行に１レコードのデータが並んでいる表も、Power Query（パワークエリ）エディターを使って、複数列のテーブルに整形することが ...
Power Queryはデータを結合する場合も便利です。
-
Power Queryでフォルダー内のすべてのファイルのデータを結合
ExcelのPower Queryを使って、指定したフォルダー内の複数ファイルのデータを1つのテーブルに結合できます。 後でそのフォルダー内 ...
-
Power Queryで同一ブック内の複数シートを結合するには
Power Queryを使って、同一ブック内の複数シートを結合する方法を紹介します。 同じレイアウトの表の場合は、簡単に結合できます。 結合 ...
-
複数のクロス集計表をPower Queryでピボットテーブルに変換
Power Queryを使って、複数のクロス集計表を結合して1つのピボットテーブルに変換することができます。 一度、ピボットテーブルを作成す ...
参考Power Queryを使った操作は、以下の記事でも解説しています。
-
AccessのデータをPower Queryで取り込むと更新も楽になる
AccessのデータベースをExcelに取り込むと、データベースの分析などはExcelの機能を使って行うことができます。 Excel2019 ...
-
Excelでテキストファイルを読み込む（Power Queryとテキストファイルウィザード）
テキストファイルをExcelに取り込む方法は、外部データとして読み込む方法と、Excelで開く方法がありますが、Excel 2019では外部 ...
-
Webページからデータをインポート（Power Queryで2つのテーブルの行を連結）
Excelでは、HTML形式のデータを取り込むことができます。社内イントラネットのデータや、一般的なWebページのデータなどを取り込むことが ...
