リンクユニット

Google検索

Office2019 Excel2019

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

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

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

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

1列の複数行に1レコードのデータが並んでいる表をPower Queryで複数列のテーブルに整形

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

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レコード)に変更

A列の複数行に1レコードが並んでいる表を、横に項目を並べた表に変更する方法を紹介します。ここでは、数式を使った方法を紹介します。Excel2 ...

続きを見る

元となる表の確認

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

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

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

5行ごとが1データになっている表

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

ワークシートのコピー

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

続きを見る

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

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

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

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

インデックス列

インデックスが0で始まる新しい列を作成します。

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

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

インデックス列が追加されたクエリ

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

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

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

選択されている最初の列の値を2番目の列の値で割ったときの整数剰余を格納する新しい列を作成します。単一の列が選択されると、選択されている列のそれぞれの値を指定された値で割ったときの整数剰余が新しい列に格納されます。

[列の追加]タブの[標準]ボタン

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

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

[剰余]ウィンドウ

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

[剰余]列が追加されたクエリ

列のピボット

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

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

列のピボット

現在選択されている列の名前を使用して新しい列を作成します。ネストされた列がある表はサポートされません。

[変換]タブの[任意の列]グループにある[列のピボット]

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

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

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

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

[列のピボット]ウィンドウ

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

設定した[列のピボット]ウィンドウ

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

列のピボットを実行したクエリ

[null]のセルへ下の値をコピー

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

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

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

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

以下のようになります。

フィルを実行した後のクエリ

氏名列の[null]を非表示に

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

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

氏名の列のフィルターボタン

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

フィルターで[null]をオフに設定

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

[null]を非表示にしたクエリ

列の削除

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

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

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

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

右クリックした時のショートカットメニュー

列の名前を変更

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

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

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

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

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

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

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

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

閉じて読み込む

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

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

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

[閉じて読み込む]をクリックします。

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

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

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

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

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

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

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

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

続きを見る

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

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

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

閉じて次に読み込む

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

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

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

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

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

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

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

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

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

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

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

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

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

ブックから

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

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

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

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

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

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

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

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

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

続きを見る

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

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

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

続きを見る

参考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.