Power Queryでデータベースに整形（マトリックス表をテーブルに）

Power Query（パワークエリ）は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とExcel2013は、Power Queryアドインをインストールすることで使用することができます。

このPower Queryを使用すると、どう便利なのか事例をもとに解説します。

たとえば、以下のようなマトリックス表（クロス集計）もPower Queryでは、少しの操作でテーブルに変換できます。

Excel2019（永続ライセンス）バージョン1906（ビルド11727.20244）での解説です。

Power Query（パワークエリ）については、以下の記事が参考になると思います。分かりやすく解説されています。Power Queryって何？って方は、ぜひお読みください。お勧めの記事です。

【初心者向け】Power Queryで元データを整形しよう（概要編）

以下は、Microsoftの解説ページです。

Power Query の概要 - Excel（Microsoft）

参考Power Queryを使用しない場合の方法は、以下の記事で解説しています。

Power Queryを利用するには

Excel2019では、［データ］タブの［データの取得と変換］グループにある［テーブルまたは範囲から］を使用するか、

［データの取得］をクリックして、［ファイルから］をポイントして［ブックから］をクリックします。

参考［テーブルまたは範囲から］をクリックすると、テーブルではない範囲の場合は、自動的にテーブルに変換されます。もし、範囲をテーブルに変換したくない場合は、事前にデータ範囲をコピーして操作するか、［データの取得］の［ブックから］を選択してデータをインポートして操作してください。

変換する範囲がすでにテーブル形式の場合は、［テーブルまたは範囲から］をクリックして、同一ブック内に作成してもいいと思います。［データの取得］の［ブックから］を選択して操作する場合も、範囲をテーブルに変換しておくと、クエリでの操作がより簡単になります。

Excel2016の場合

Excel2016（永続ライセンス）の場合は、［データ］タブの［取得と変換］グループにある［テーブルから］をクリックします。

あるいは、［データ］タブの［取得と変換］グループにある［新しいクエリ］をクリックして、［ファイルから］をポイントして［ブックから］をクリックします。データをインポートして操作します。

Excel2013またはExcel2010の場合

Excel2013とExcel2010でPower Queryを使用するには、Power Queryアドインをインストールする必要があります。

インストール方法については、以下の記事で解説しています。

［POWER QUERY］タブをクリックして、［Excelデータ］グループにある［テーブル/範囲から］をクリックします。

または、［POWER QUERY］タブをクリックして、［外部データの取り込み］グループにある［ファイルから］をクリックして［Excelから］をクリックします。データをインポートして操作します。

参考ご使用中のOfficeのバージョンを確認する方法は、以下の記事で解説しています。

サンプルのマトリックス表について

ここでは、以下のようなマトリックス表（クロス集計表）をサンプルとして使用します。

県名のセルは、［セルを結合して中央揃え］が設定してあります。

Power Queryなら、セルの結合がされていても大丈夫です。

このマトリックス表として完成された表（テーブルではない範囲）を、ピボットテーブルにできるテーブルに変換します。

Power Queryでは、接続された状態なので、元の表で値が変更されると、更新して変更された値を反映させることもできます。

ここでは、まず［テーブルまたは範囲から］をクリックして操作します。

［テーブルまたは範囲から］を選択してクエリを作成

表内の1つのセルを選択して、あるいは表の範囲を選択します。

ここでは、セル［A3］を選択します。結合したセルがある場合は、結合したセルは選択しないほうが効率的です。結合されたセルを選択していると、自動的に範囲を選択されない場合があります。

［テーブルの作成］ダイアログボックスが表示されます。

テーブルに変換される範囲が間違っていないかを確認します。もし、データ範囲を修正する必要があれば、［テーブルの作成］ダイアログボックスの［拡大/縮小ボタン］をクリックして、範囲を指定します。

確認して問題がなければ、［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 エディターの［ホーム］タブの左端に［閉じて読み込む］ボタンがあります。

［ホーム］タブの［閉じて読み込む］の下側の文字の箇所をクリックします。すると、［閉じて読み込む］と［閉じて次に読み込む...］のメニューが表示されます。

［閉じて読み込む］をクリックします。

新規ワークシートにテーブルとして読み込まれます。右側には、［クエリと接続］作業ウィンドウが表示されます。

［テーブルデザイン］タブが表示されているので、この後はテーブルの機能を使って、さまざまな操作をすることができます。また、ピボットテーブルに変換することもできます。

テーブルの便利機能については、以下の記事で解説しています。

テーブルから範囲（通常の表）にしたい場合は、［テーブルデザイン］タブの［ツール］グループにある［範囲に変換］をクリックします。

ただし、範囲に変換すると、クエリとの接続は解除されます。

また、セルの背景色、文字色、罫線などの書式はテーブルのままになります。

範囲に変換した後、書式をクリアする方法については、以下を参照してください。

テーブルを解除して通常の表に変換するには

また、書式を残したくなければ、先にテーブルのスタイルをクリアして、［範囲に変換］を実行する方法もあります。

テーブルのスタイルをクリアして［範囲に変換］

参考［ホーム］タブの［閉じて読み込む］の下側の文字の箇所をクリックすると、［閉じて読み込む］と［閉じて次に読み込む...］のメニューが表示されます。［閉じて次に読み込む...］を選択すると、［データのインポート］ダイアログボックスが表示されます。

［データのインポート］ダイアログボックスで表示する形式とデータの出力先を選択できます。

ピボットテーブルを作成したいのであれば、ここで選択すると効率的です。

既定では、［テーブル］と［新規ワークシート］が選択されています。

データの更新

取り込み元のデータが変更されると、作成したテーブルにも反映されます。

以下の画像は、［テーブルまたは範囲から］を選択してクエリを作成し、同一ワークシートに読み込んだものです。

例として、青森県の第一営業部のA製品の値を［120］から［130］に変更してみます。

テーブル内のセルを選択して、［データ］タブの［すべて更新］から［更新］をクリックします。

参考ブック内で複数の外部データとの接続がある場合、［すべて更新］をクリックすると、ブック内のすべての外部データの接続が更新されます。特定のデータのみ更新したい場合は、［更新］をクリックします。

ポップヒントで分かりますが、ショートカットキーは以下のとおりです。

• すべて更新［Ctrl］+［Alt］+［F5］
• 更新［Alt］+［F5］

以下のようにテーブルの値も変更されます。

データの増減があった場合も反映されます。

［クエリ］タブから更新

更新は、［クエリ］タブをクリックして、［読み込み］グループにある［更新］ボタンをクリックしても同じです。

注意Power Query エディターから読み込んだテーブルをクリックしないと［クエリ］タブは表示されません。

Power Query エディターの再表示

Power Query エディターを再表示する方法は、いくつかあります。

クエリをダブルクリック

［クエリと接続］作業ウィンドウのクエリでダブルクリックします。

ショートカットメニューから

クエリで右クリックしてショートカットメニューから［編集］をクリックします。

［クエリ］タブの［編集」ボタンから

または、［クエリ］タブの［編集］グループの［編集］ボタンをクリックします。ポップヒントには、以下のように記されています。

ショートカットキー

Power Queryエディターを起動するショートカットキーは、［Alt］+［F12］です。

ファイルパスの変更

ブックから取り込んだ後、ブックの保存先が変更になった場合は、パスを変更することもできます。

上記のどの方法でも良いので、Power Query エディターを表示します。

そして、［クエリの設定］の［適用したステップ］の［ソース］をクリックします。

以下のウィンドウが表示されますので、［参照］ボタンをクリックして、保存先のブックを指定します。

フィルパスが変更されたことを確認して、［OK］ボタンをクリックします。

あるいは、［ホーム］タブの［データソース設定］ボタンをクリックして、［データソース設定］ウィンドウを表示します。そして、［ソースの変更］ボタンをクリックすると、上記の同じファイルパスを変更するウィンドウが表示されます。

クエリの削除

テーブルをクエリと接続しておきたくない場合は、クエリを削除します。クエリを削除しても、テーブルはそのまま残ります。更新ができなくなるというだけです。

［クエリと接続］作業ウィンドウのクエリで右クリックして、ショートカットメニューから［削除］をクリックします。

［クエリと接続］作業ウィンドウが表示されていない場合は、［データ］タブの［クエリと接続］グループにある［クエリと接続］をクリックします。

［クエリの削除］メッセージウィンドウが表示されますので、［削除］をクリックします。

クエリの削除は、［クエリ］タブの［編集］グループの［削除］ボタンをクリックしても同じです。

関連以下の記事では、1行に複数の情報が入力された表をPower Query エディターを使用してテーブルに変換する方法を解説しています。

1列の複数行で1レコードになっているような表もPower Query エディターで複数列のテーブルに整形することができます。

Power Queryはデータを結合する場合も便利です。

参考Power Queryを使った操作は、以下の記事でも解説しています。