Google検索

Office2019 Excel2019

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

投稿日:2019年8月1日 更新日:

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

Excelの表の作り方は様々です。分析に向いていない表を分析ができるテーブルに変換しなければならない場合があります。

先頭行に同じ項目名が複数出現するような表は、Power Queryエディターで列をマージして、ピボット解除、そして結合した列を分解することで分析可能なテーブルに整形することができます。

以下のように1行に複数のレコードが入力されているような表があります。B列から4列ずつで区切ってテーブルに変換する方法です。

テーブルやピボットテーブルの作成ができると、データの分析が容易になります。

Power Queryでデータの整形

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

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

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

Power Query の概要 - Excel

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

Power Queryは、Excel2010とExcel2013ではアドインとしてインストールすることで使用できるようになります。

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

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

続きを見る

参考Power Queryエディターを使用しない方法は、以下の記事で解説しています。

1行に複数の同じ項目名がある表を1行1レコードの表に変更するには

Excelの表の作り方は様々です。リストとなる表は、1行に1レコード(1行に1個の情報)となっていなければいけません。 横に長い表を4列ずつ ...

続きを見る

元となる表の確認


例として下のような表があります。

1行に3つのデータが入っているような表ですね。これをPower Queryエディターを使って、加工します。

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

横に長い表

Power Queryを使用する方法

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

[データ]タブの[データの取得と変換]グループにある[テーブルまたは範囲から]

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

この場合はデータをインポートして操作しますので、元の表はそのままになります。

[データの取得]の[ファイルから]をポイントして[ブックから]を選択

注意[データ]タブの[テーブルまたは範囲から]をクリックすると、テーブルではない範囲の場合は、自動的にテーブルに変換されます。もし、範囲をテーブルに変換したくない場合は、必ず事前にデータ範囲をコピーするなどの操作をしてください。

[ファイルから]をポイントして[ブックから]をクリックして操作する場合は、元の表はそのままになります。以下から操作してください。

データをインポートして操作する場合

元となる表の大きさにもよりますし、使用される環境によって、どちらを選択されるか判断してください。ここでは、[テーブルまたは範囲から]を選択してクエリを作成します。

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

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

ここでは、セル[A1]を選択しています。

[データ]タブの[データの取得と変換」グループにある[テーブルまたは範囲から]をクリックします。

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

テーブルまたは範囲から

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

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

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

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

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

[先頭行をテーブルの見出しとして使用する]のチェックボックスはオンのままにします。

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

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

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エディターが表示されます。

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

Power Queryエディター

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

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

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

続きを見る

列のマージ

[商品A][担当者][数量][金額]が一つのかたまりになりますので、[商品A]から[金額]までの4列を選択して、列のマージを行います。

[変換]タブの[テキストの列]グループにある[列のマージ]をクリックします。

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

列のマージ

現在選択されている複数の列を連結して1つの列にします。

[変換]タブの[テキストの列]グループにある[列のマージ]

[列のマージ]ウィンドウが表示されます。[区切り記号]の▼ボタンをクリックします。

[列のマージ]ウィンドウ

メニューの中から[コンマ]を選択して、[OK]ボタンをクリックします。

区切り位置は、他のものでも構いませんが、この後のマージの操作の時も同じ区切り記号を使うようにします。ここでは、[コンマ]を選択します。

[列のマージ]ウィンドウで[区切り記号]の選択

以下のように4つの列がマージされて、[結合済み]という名前の列になります。

マージして結合された列

同様に次の4つの列を選択して、[列のマージ]を実行します。

ここでは[商品B][担当者2][数量3][金額4]の列を選択します。区切り記号は、上記と同じ[コンマ]にします。

次の4列をマージ

もう一度、お尻の4列を選択して、[列のマージ]を実行します。

ここでは、[商品C][担当者5][数量6][金額7]の列を選択します。区切り記号は、同じ[コンマ]にします。

最後の4列をマージ

これで3つの結合済みの列ができました。

3つの結合済みの列

列のピボット解除

次に[日付]の列を選択して、[変換]タブの[任意の列]グループにある[列のピボット解除]の▼ボタンをクリックして、[その他の列のピボット解除]をクリックします。

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

現在選択されている列以外のすべての列を属性/値のペアに変換します。

その他の列のピボット解除

以下のように[属性]と[値]の列ができます。

[属性]と[値]の列

参考ピボット解除のコマンドは、日付の列を選択して、右クリックした時のショートカットメニューの中にもあります。

日付の列を選択して、右クリックした時のショートカットメニュー

列の削除

[属性]の列は不要なので、削除します。

[属性]の列を選択して、[ホーム]タブの[列の管理]グループにある[列の削除]から[列の削除]をクリックします。

現在選択されている列をこのテーブルから削除します。

[ホーム]タブの[列の管理]グループにある[列の削除]

[属性]の列が削除されて、[日付]と[値]の列になります。

[属性]の列が削除されたクエリ

列の分割

[値]の列を分割します。

[値]の列を選択して、[変換]タブの[変換]グループにある[列の分割」をクリックします。

メニューの中から[区切り記号による分割]をクリックします。

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

区切り記号による列の分割

指定された区切り記号に基づいて、選択された列の値を分割します。

[変換]タブの[変換]グループにある[列の分割」

[区切り記号による列の分割]ウィンドウが表示されます。

区切り記号は、[コンマ]を選択して、分割は、[区切り記号の出現ごと]を選択して[OK]ボタンをクリックします。

既定で選択されていると思います。

[区切り記号による列の分割]ウィンドウ

以下のように[値1][値2][値3][値4]の列に分割されます。

だんだん求めるテーブルの形式になってきました。

列を分割したクエリ

参考列の分割は、[値]の列を選択して右クリックした時のショートカットメニューの中にもあります。

[値]の列を選択して右クリックした時のショートカットメニュー

列の名前の変更

[値1][値2][値3][値4]の列の名前を変更します。

列を分割した後の列の名前

列の名前の箇所でダブルクリックすると、入力できる状態になるので、そのまま入力します。

ダブルクリックして列の名前の変更

以下のように[商品名][担当者][数量][金額]という名前に変更しました。

列の名前を変更したクエリ

参考名前の変更は、各列を選択して、[変換]タブの[任意の列]グループにある[名前の変更]をクリックしても同じです。

[変換]タブの[任意の列]グループにある[名前の変更]

データ型の変更

[日付]のデータ型を変更します。

[日付/時刻]型になっているので、[日付]の左のアイコンをクリックして、メニューから[日付]をクリックします。

データ型の変更

[日付/時刻]型から[日付]型に変更できました。

日付/時刻型を日付型に変更

参考[データ型]の変更は、[ホーム]タブの[変換]グループに[データ型]から変更することもできます。各列を選択すると、ここでデータ型を確認することができます。

[ホーム]タブの[変換]グループに[データ型]

閉じて読み込む

Excelのワークシートに読み込みます。

[ホーム]タブの[閉じて読み込む]をクリックします。

このクエリへの変更を保存します。クエリエディターウィンドウを閉じて、結果を既定の読み込み先に読み込んでください。

[ホーム]タブの[閉じて読み込む]

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

ワークシートに読み込まれたテーブル

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

テーブルの便利機能(テーブルに変換して効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

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

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

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

[テーブルデザイン]タブの[ツール]グループにある[範囲に変換]

閉じて次に読み込む

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

このクエリへの変更を保存します。クエリエディターウィンドウを閉じて、結果の既定の読み込み先を指定してください。

[ホーム]タブの[閉じて次に読み込む...]

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

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

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

[データのインポート]ダイアログボックス

データをインポートして操作する場合

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

データをインポートして操作しますので、元のブックはそのままです。範囲が自動的にテーブルに変換されることもありません。

操作する前にインポートするブック名とシート名を確認しておくことをお勧めします。

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

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

ブックから

Microsoft Excelブックからデータをインポートします。

[データの取得]の[ファイルから]をポイントして[ブックから]を選択

[データの取り込み]ダイアログボックスが表示されますので、目的のブックを選択して、[インポート]ボタンをクリックします。

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

[ナビゲーター]ウィンドウが表示されます。

Power Queryエディターで加工したいシートを選択します。右側にワークシートのプレビューが表示されますので、間違いがなければ[データの変換]ボタンをクリックします。

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

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

Power Queryエディター

この後の操作は、上記の[列のマージ]からの操作と同じになります。

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でデータベースに整形(マトリックス表をテーブルに)

Power Query(パワークエリ)は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とE ...

続きを見る

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を使った操作は、以下の記事でも解説しています。

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

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

続きを見る

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

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

続きを見る

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

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

続きを見る

Power Queryについて学習するなら、以下のテキストがお勧めです。

Profile

執筆者/はま

おすすめ記事と広告

-Office2019 Excel2019

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

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