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

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

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

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

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

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

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

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

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

Excel の Power Query について - Microsoft サポート（Microsoft）

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

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

元となる表の確認

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

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

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

Power Queryを使用する方法

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel2016の場合

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

Excel2013またはExcel2010の場合

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

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

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

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

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

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

列のマージ

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

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

注意［列のマージ］は［列の追加］タブにもありますので、間違えないようにしてください。ここでは、［変換］タブの［列のマージ］を選択します。右クリックのショートカットメニューにもあります。

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

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

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

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

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

同様に次の４つの列を選択して、［列のマージ］を実行します。

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

もう一度、お尻の４列を選択して、［列のマージ］を実行します。

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

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

列のピボット解除

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

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

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

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

列の削除

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

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

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

列の分割

［値］の列を分割します。

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

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

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

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

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

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

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

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

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

［列の分割］については、以下の記事でも解説しています。セル内改行やスペースで区切られたデータを別々のセルへ分割する場合に便利です。

列の名前の変更

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

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

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

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

データ型の変更

［日付］のデータ型を変更します。

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

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

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

閉じて読み込む

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

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

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

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

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

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

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

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

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

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

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

閉じて次に読み込む

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel2016の場合

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

Excel2013またはExcel2010の場合

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

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

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

関連以下の記事では、マトリックス表（クロス集計）をテーブルに変換する方法を解説しています。

データの更新や、ブックから取り込む方法、クエリの削除についても解説していますので、合わせて読んでいただくと理解が深まると思います。

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

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

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

Power Queryエディターを起動するショートカットキーは、［Alt］+［F12］です。慣れたらPower Queryエディターを直接開いて操作してみてください。