Office 2019 Excel 2019

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

投稿日:2019年7月31日 更新日:

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(永続ライセンス)の場合は、[データ]タブの[取得と変換]グループにある[テーブルから]をクリックします。

Excel2016の[データ]タブの[テーブルから]

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

Excel2016[データ]タブの[取得と変換]グループにある[新しいクエリ]

Excel2013またはExcel2010の場合

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

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

Excel2013にPower Queryをインストールして[POWER QUERY]タブを表示

Excel2013にPower QueryアドインをMicrosoftのダウンロードセンターのページからインストールしてみました。OSは、W ...

続きを見る

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

Excel2013の[POWER QUERY]タブの[Excelデータ]グループにある[テーブル/範囲から]

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

Excel2013[POWER QUERY]タブの[ファイルから]-[Excelから]

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

Office(Word、Excel、PowerPointなど)のバージョンを確認する方法

Microsoft Officeも長い時間を経て、大きく様変わりしてきました。 トラブルを解決しようとするとき、あるいは、何かの操作を探すと ...

続きを見る

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

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

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

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

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

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

サンプルのマトリックス表

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

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

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

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

テーブルまたは範囲から

選択したExcelテーブルにリンクされる新しいクエリを作成します。

選択したデータがテーブルまたは名前付き範囲ではない場合は、テーブルに変換されます。

[データ]タブの[テーブルまたは範囲から]

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

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

確認して問題がなければ、[OK]ボタンをクリックします。

[テーブルの作成]ダイアログボックス

Power Query エディターが表示されます。

参考ウィンドウ右側にある[クエリの設定]の[適用したステップ]にはクエリに記録された作業が順に表示されていきます。通常のワークシートのように元に戻すボタンなどはありませんが、[適用したステップ]の[×]をクリックすることで、操作を取り消すことができます。

Power Queryエディター

ここから先の操作は、以下へ続きます。

[フィル]ボタンでセルの値を下方向へコピー

[ブックから]をクリックしてクエリを作成

新規ブックを起動して、[データ]タブの[データの取得]をクリックして、[ファイルから]の[ブックから]をクリックして操作する方法です。

この方法では、元のブックはそのままです。範囲が自動的にテーブルに変換されることはありません。

まず、Power Queryでデータを整形したブックのシート名を確認しておきます。取り込む前にプレビューで確認することもできますが、先に確認しておくことをお勧めします。

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

[データの取得]の[ファイルから]-[ブックから]

[データの取り込み]ダイアログボックスが表示されます。

目的のブックスを選択して[インポート]ボタンをクリックします。

[データの取り込み]ダイアログボックス

[ナビゲーター]ウィンドウが表示されます。目的のシート名をクリックすると、右側にプレビューが表示されます。[データの変換]ボタンをクリックします。

[ナビゲーター]ウィンドウ

Power Query エディターが表示されます。

Power Queryエディター

行の削除

Power Query エディターで、少し整えます。

1行目は不要なので、削除します。

[ホーム]タブの[行の削減]グループにある[行の削除]ボタンをクリックして、メニューから[上位の行の削除]をクリックします。

[ホーム]タブの[行の削減]グループにある[行の削除]ボタン

以下の[上位の行の削除]ウィンドウが表示されます。

先頭から削除する行の数を指定します。

[行数]のテキストボックスに[1]と入力して、[OK]ボタンをクリックします。

[上位の行の削除]ウィンドウ

1行目が削除されます。

1行削除されたクエリ

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

1行目をヘッダーにしたいので、[ホーム]タブの[変換]グループにある[1行目をヘッダーとして使用]をクリックします。

[ホーム]タブの[変換]グループにある[1行目をヘッダーとして使用]

以下のように整います。

1行目をヘッダーとしたクエリ

参考取り込む元となるデータがテーブルになっている場合は、上記の行を削除したり、1行目をヘッダーとして使用する作業は不要になります。

[フィル]ボタンでセルの値を下方向へコピー

[県名]の列には、[null]が表示されています。この[null]のセルを上のセルの値に変換します。

[県名]の列を選択します。

[変換]タブをクリックします。[任意の列]グループにある[フィル]の▼ボタンをクリックして、[下]をクリックします。

ポップヒントには以下のように記されています。

現在選択されている列のセルの値を隣接する空のセルに下方向へコピーします。

[変換]タブの[任意の列]グループにある[フィル]

以下のように上のセルの値がコピーされます。

フィルを実行したクエリ

参考上のセルの値をコピーする方法は、以下の記事で解説しているように、Power Queryを使用しなくてもできますが、Power Query エディターで操作するほうが簡単です。

no image
空白セルに上のセルの値を貼り付けるには

表の空白セルに上のセルの値を貼り付けたい場合の方法です。データが少ない場合は、コピー&ペーストでいいのですが、データが膨大になると大変な作業 ...

続きを見る

列のピボット解除

マトリックス表をテーブル形式にするための変換を行います。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 エディターを表示します。

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

[適用したステップ]の[ソース]をダブルクリック

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

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

[参照]ボタンをクリックして保存先のブックを指定

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

[ホーム]タブの[データソース設定]ボタン

クエリの削除

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

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

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

クエリで右クリック、ショートカットメニューの[削除]

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

○○を削除しますか?このクエリを削除すると、このクエリによって読み込まれたデータを更新できなくなります。

[クエリの削除]メッセージウィンドウ

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

[クエリ]タブの[削除]ボタン

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

先頭行に複数の同じ項目名がある表をPower Queryでテーブルに変換

Excel2016から標準で装備されているPower Query(パワークエリ)を使用すると、効率よくデータを加工できます。 Excelの表 ...

続きを見る

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

1列の複数行で1レコードになっている表をPower Queryで複数列のテーブルに変換

1列の複数行に1レコードのデータが並んでいる表も、Power Query(パワークエリ)エディターを使って、複数列のテーブルに整形することが ...

続きを見る

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

Power Queryでフォルダー内のすべてのファイルのデータを結合

ExcelのPower Queryを使って、指定したフォルダー内の複数ファイルのデータを1つのテーブルに結合できます。 後でそのフォルダー内 ...

続きを見る

Power Queryで複数シートの結合

Power Queryを使って、同一ブック内の複数シートを結合する方法を紹介します。同じレイアウトの表の場合は、簡単に結合できます。 結合だ ...

続きを見る

複数のマトリックス表をPower Queryでピボットテーブルに変換

Power Queryを使って、複数のマトリックス表を結合して1つのピボットテーブルに変換することができます。一度、ピボットテーブルを作成す ...

続きを見る

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

Excel2019でAccessのデータをPower Queryで取り込むと更新も簡単

AccessのデータベースをExcelに取り込むと、データベースの分析などはExcelの機能を使って行うことができます。 Excel2019 ...

続きを見る

Excel2019でテキストファイルを読み込む(Power Queryとテキストファイルウィザード)

テキストファイルをExcelに取り込む方法は、外部データとして読み込む方法と、Excelで開く方法がありますが、Excel2019では外部デ ...

続きを見る

Webページからデータをインポート(Power Queryで2つのテーブルの行を連結)

Excelでは、HTML形式のデータを取り込むことができます。社内イントラネットのデータや、一般的なWebページのデータなどを取り込むことが ...

続きを見る

Power Queryでマトリックス型の表をデータベース型の表に変換する方法は以下のテキスト内でも紹介されています。お勧めです。

Profile

執筆者/はま

バージョンの確認

もっと理解したいあなたへ

おすすめ記事と広告

-Office 2019 Excel 2019

Copyright © 2001-2021 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2021 AllRights Reserved Powered by AFFINGER4.