【パワークエリ】ワークシート上のマトリックス表をピボット解除する際に元の表を残したままエディターに取込む方法

【目次】

 1.やりたいこと

現在開いているエクセルのワークシートに次のようなマトリックス表(見出し等がセル結合されている)があります。これをエディターに取り込んでピボット解除して一覧データ化したい場合に、このマトリックス表は残したままエディターに取込む方法を説明します。

f:id:shusoshin:20210402204700g:plain

まず、一般的なテーブルから取込み方法でやろうとするとどうなるか見てみます。

メニューバー → データタブ → テーブルまたは範囲から をクリックします。

(又はCtrl+T)

 f:id:shusoshin:20210402204727g:plain

範囲を選択し、OKをクリックします。

f:id:shusoshin:20210402204734g:plain

 

すると、下図のようにセル結合していたセルが自動的に解除されてしまいます。

これを回避する別の方法を次の項目で説明します。

f:id:shusoshin:20210402204744g:plain

2.実践

まず、対象範囲に名前をつけます。対象範囲を選択し、

メニューバー → 数式タブ → 名前の定義 をクリックします。(又はCtrl+F3)

f:id:shusoshin:20210402204752g:plain

任意の名前を付けて(ここでは、売上利益集計表 とします。)OKをクリックします。

f:id:shusoshin:20210402204800g:plain

次に、メニューバー →データタブ → データの取得 → その他のデータソースから →空のクエリ をクリックします。

f:id:shusoshin:20210402204808g:plain

パワークエリエディターが立ち上がります。

数式バーに次の数式を入力します。

= Excel.CurrentWorkbook()

f:id:shusoshin:20210402204815g:plain

確定すると、先ほど名前をつけた「売上粗利集計表」が表示されます。

Table と表示されているところで右クリックし、ドリルダウンをクリックします。

f:id:shusoshin:20210402204822g:plain

名前を付けた範囲のデータを取り込めました。

f:id:shusoshin:20210402204829g:plain

この後のエディターでデータ化するところの操作説明は省略しますが、鷹尾祥さんのブログ(以下リンク)に詳しく書かれていますのでこちらを参照ください。

modernexcel7.hatenablog.com

 

エディターでのピボット解除が完了し、一覧データに整形したものをワークシートに取り込んだものが、次の画像となります。

f:id:shusoshin:20210402204847g:plain

 

元の集計表シートを選択し、試しにX事業A部門のQ1の利益を30 → 50,000 に上書きしてみます。

f:id:shusoshin:20210402204856g:plain

取り込んだ一覧データに戻り、データ上にカーソルを置いた状態で右クリックし、更新をクリックします。

f:id:shusoshin:20210402204902g:plain

X事業 A部門 利益 Q1 の金額が変更した通り、50,000 に更新されました。

f:id:shusoshin:20210402204910g:plain

 

以上が今回の内容となります。