Edited at

[Excel]データが欲しいのに表をもらったのでノンプログラミングでデータに変える

More than 1 year has passed since last update.

データを集計してほしいと言われてもらったものが表だった時にExcelでデータに変換する方法をメモっておく。

これまでVBAでゴリゴリ頑張っていた部分がノンプロでできるのは非常にありがたい。


方法

PowerQueryを使う。PowerQueryについては詳しく書いている方がいらっしゃったので省略。

ExcelでPowerQueryを使ってデータ収集分析


やりたいこと

以下のような表をもらったときに、

1.png

このようなデータを作りたい。

10.png


やり方

表を選択し、「データ」タブの「テーブルまたは範囲から」を選択する。

2.png

「テーブルの作成」のダイアログが出てくるので、データ範囲が間違っていないか確認しOKを選択する。

3.png

PowerQueryが立ち上がる。

新潟、富山、石川の列を選択して「変換」タブの「列のピボット解除」を選択する。

6.png

一瞬でほしいデータに代わってしまった。

7.png

見出しをダブルクリックすると名称を変更できる。

8.png

「ファイル」タブから「閉じて読み込む」を選択してPowerQueryを終了させる。

9.png

出来上がり

10.png


結合セルがあったらどうするの?


1.縦に結合したセルがあった場合

11.png

前述したようにまずはPowerQueryに取り込む。

12.png

すると今度はこんな形になる。nullが入ってるね。

13.png

「変換」タブに「フィル」があるので下方向にフィルを行う。

14.png

するとnullが埋まるので、あとの操作は同じ。

新潟、富山、石川の列を選択した状態でピボットテーブルの解除を行い見出しを整えてPowerQueryを終了させればよい。

15.png

出来上がり

16.png

このあたりの操作はもっと詳しく書かれている方がいるのでそちらを参考にした方が良い。

[Excel 取得と変換] Power Query でクロス集計表・マトリックス表を表形式・テーブル形式に変換する | Road to Cloud Office


2.横に結合したセルがあった場合

17.png

「先頭行をテーブルの見出しとして使用する」のチェックを外して取り込む。

19.png



20.png

「横にフィル」というものはないので一旦表の行列を入れ替える。

「変換」タブの「入れ替え」を選択する。

21.png



22.png

1列目で「下にフィル」を行う。

23.png

1月2月3月がレコードとして扱われてしまっているので「ホーム」タブの「1行目をヘッダーとして使用」を選択する。

23 - コピー.png



24.png

見出しを整えてピボット解除して出来上がり。

25.png



26.png


3.縦と横どちらにも結合したセルがあった場合

27.png

これまでのやり方+αでできる。

まずは取り込んで1列目を下にフィル。

28.png



29.png

ここでちょっと一工夫。列1と列2を結合しておく。後で行列を入れ替えたときに年と月の列を年月として見出しにできるようになる。

「変換」タブの「列のマージ」を選択する。

30.png



31.png

区切り記号は何でもよい。とりあえず大概のデータに被らなそうな「★」が個人的にはお気に入り。

32.png



33.png

この状態で行列を入れ替える

34.png

1列目を下にフィルして、1行目を見出しとして使用する。1列名2列目の見出しを適宜変更しておく。

35.png

あともう少し、「2017★1」から「2018★3」を選択してピボット解除する。

36.png

属性(=年月)の列を選択して「変換」タブの「列の分割」を行い年と月に分割する。

37 - コピー.png



39.png

見出しを整えて出来上がり。お疲れ様。

40.png


おまけ

こんな表をもらったら。

41.png

PowerQueryに取り込む範囲を分ける。まずはA列からD列を選択して取り込む。

42.png

そのあとは何もしないで「ファイル」タブの「閉じて次に読み込む」を選択する。

43.png

「接続の作成のみ」を選択。

44.png

この作業をE列からH列、I列からL列にも行って3つのテーブルをPowerQueryに取り込む。

左ペインを開くと3つのテーブルが取り込まれているのが分かる。

45.png

「ホーム」タブの「クエリの追加」-「クエリを新規クエリとして追加」を選択して3つのテーブルを結合する。

46.png



47.png



48.png

ピボットを解除して出来上がり。


ということで

変なデータを送られてきても、まあ何とかしてやろうかと思えるようになった。