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

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

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

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

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

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

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

Power Query の概要 - Excel（Microsoft）

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

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

元となる表の確認

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

A列のみにデータが入力されていて、５行ずつが１レコードになっています。サンプルのような少ない行数であれば、コピー＆ペーストで手作業で行うこともできますが、行数が多くなると大変な作業です。関数やVBAが得意な方なら、すぐにできるかもしれないですが。

Power Queryが実装された環境であれば、簡単に加工して整形することができます。

この表をそのまま使用すると、テーブルに変換されてしまいますので、ここではシートごとコピーして操作します。

Power Queryを使用する方法

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel2016の場合

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

Excel2013またはExcel2010の場合

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

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

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

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

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

Power Query エディターでインデックス列の追加

インデックス列を追加します。［列の追加］タブの［全般］グループにある［インデックス列］をクリックします。

カスタムで始まる数値を設定することもできますが、ここでは、既定のまま0から始まるインデックス列でOKなので、そのままクリックします。

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

以下のようにインデックス列が追加されます。

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

インデックス列を5で割った時の剰余列を追加

インデックス列を選択し他状態で、［列の追加］タブの［数値から］グループにある［標準］ボタンをクリックします。

メニューから［剰余］をクリックします。ポップヒントには以下のように記されています。

［剰余］ウィンドウが表示されます。［値］のテキストボックスに、［5］と半角で入力して［OK］ボタンをクリックします。

5行で1つのレコードになっているので、ここでは、5 となります。

以下のように［剰余］の列が追加されます。

列のピボット

［剰余］列を選択して、［変換］タブの［任意の列］グループにある［列のピボット］をクリックします。

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

［列のピボット］ウィンドウが表示されます。

［値列］は［列1］が選択されていることを確認します。

［詳細設定オプション］をクリックして、［値の集計個数］の▼ボタンをクリックします。

プルダウンメニューから［集計しない］を選択します。

［列のピボット］ウィンドウは、以下のようになっていることを確認して、［OK］ボタンをクリックします。

クエリは以下のようになります。

［null］のセルへ下の値をコピー

［null］と表示されたセルへ下の値をコピーします。

列1から列4までを選択して、［変換］タブの［任意の列］グループにある［フィル］ボタンの▼をクリックして、メニューから［上］をクリックします。

以下のようになります。

氏名列の［null］を非表示に

［氏名］の列の［null］をフィルターを使って、非表示にします。

0列のフィルターボタンをクリックします。

［null］のチェックボックスをオフにして、［OK］ボタンをクリックします。

クエリは、以下のようになります。

列の削除

インデックス列は不要なので、削除します。

［インデックス列］を選択して、［ホーム］タブの［列の管理］グループにある［列の削除］をクリックします。

参考列の削除は、右クリックしたショートカットメニューの中の［削除］をクリックして行うこともできます。

列の名前を変更

各列の名前を変更します。

0と表示された列を［氏名］に変更します。列名は、ダブルクリックすると反転して、入力できる状態になりますので、そのまま入力します。

左から順番に［氏名］［会社名］［部署］［郵便番号］［住所］とします。

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

Power Query エディターでの加工方法は、複数あります。もっと少ないステップで完成させることもできます。ここで紹介した加工法は1つの方法と思っていただければ幸いです。

閉じて読み込む

Power Queryで加工したものをワークシートに読み込みます。

Power Query エディターの［ホーム］タブの左端に［閉じて読み込む］ボタンがあります。

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

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

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

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

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

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

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

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

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

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

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

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

閉じて次に読み込む

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

［閉じて次に読み込む...］を選択すると、［データのインポート］ダイアログボックスが表示されます。

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

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

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

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

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

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

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

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

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

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

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

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

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

この後の操作は、上記の［Power Query エディターでインデックス列の追加］からの操作と同じになります。

Excel2016の場合

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

Excel2013またはExcel2010の場合

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

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

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

関連Power Queryを使用すると、マトリックス（クロス集計）表なども分析ができるテーブルに整形することができます。

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

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

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

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

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