Excelクロスデータを一覧形式に変換する

はじめに

分析用のDBが存在していて、業務部門に予算などのデータ登録用のデータを提出してもらう際は大抵はクロスデータで提出してもらっています。

理由はSUMなどを使って検算がしやすいので、入力間違いを減らせるのが主な理由です。

一般的なDBのテーブル構成を業務担当者に渡しても入力し辛いのでなるべく回避しています。

ただクロスデータではDBに登録する形とは違うため、データ整形が必要になります。

これまでは手で一つずつ修正していましたが、時間がかかりすぎるので、パパッと変換したいと思います。

Excel医の見るだけでわかる! Excel最速仕事術 (TJMOOK)

Excel 最強の教科書[完全版] 【2nd Edition】

Excel 2021 やさしい教科書 [Office 2021/Microsoft 365対応] (一冊に凝縮)

今回のデータ

氏名は個人情報テストデータジェネレータで作成しました

testdata.userlocal.jp

予算金額も適当に作成した数値になります。

実際のデータ

こんな感じのデータ

こうしたい

書式設定でm月で表示しています。

※今回は日付型の書式設定をしていますが、変換では使われないので、改めて日付型に直す作業をしています。

変換作業

1. 範囲選択 注意点は見出しの列、行は1つにします。 複数の見出しを使うと正しく変換ができないので、変換後に改めてデータ成形をします。

データの取得と変換 - テーブルまたは範囲から をクリック

1. テーブルの作成
   
   選択された範囲が指定されているか確認して、正しければOKをクリックします

2. PowerQueryエディターが起動します 任意のデータが取り込まれているか確認します。

3. 変換処理をします
   
   変換 - 任意の列 - その他の列のピボット解除
   
   変換されます

4. 変換データを確定させます
   
   ホーム - 閉じて読み込む をクリックして画面を閉じます

5. 変換完了
   

6. データ成形 あとは月が書式の表示値になってしまったのでvlookupなどで置換や変換などして任意の形に整形してあげます。 見出しが複数あった場合などもこのタイミングでデータを装飾してあげればいいと思います。

Excel医の見るだけでわかる! Excel最速仕事術 (TJMOOK)

Excel 最強の教科書[完全版] 【2nd Edition】

Excel 2021 やさしい教科書 [Office 2021/Microsoft 365対応] (一冊に凝縮)