リンクユニット

Google検索

Office2019 Excel2019

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

Power Query を使用すると、データソースを検索し、接続を作成し、必要に応じてデータを図形(列を削除したり、データ型を変更したり、テーブルを結合したりするなど) を行うことができます。データを整形したら、結果を共有したり、クエリを使用してレポートを作成したりすることができます。(上記ページより)

参考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エディター

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

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

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

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

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

まず、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(パワークエリ)を使用すると、効率よくデータを加工することができます。 Ex ...

続きを見る

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

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

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

続きを見る

参考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について学習するなら、以下のテキストがお勧めです。

おすすめ記事と広告

-Office2019 Excel2019

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

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