今回はデータの行列を入れ替える方法をマスターしましょう！
例えばこの画像の様に
データを横から縦に、縦から横に
並び替えたい時ってありますよね？
単純にデータをコピペする時に
「行/列の入れ替え」で貼り付ければ
入れ替えることはできます。
ただ毎回手動で行列入れ替えて貼り付けるのは
めんどくさいですよね！
ここでPowerQueryを使うと
自動で！一発で！解決する事ができます！
縦(たて)から横(よこ)に変換する方法
数値データを横に転置させよう！
まずは
左の「月別の商品売上げ」のデータは
商品の売上を月別に管理しているファイルです。
期間は1月～3月まであります。
そしてこれを右の様に
商品名を列名にして、売り上げを月別に出そうと思います！
まずは月別売上のエクセルに接続します。
次に今回「カテゴリ」列は不要なので列削除しておきます。
「商品名」の列を選択し
「変換」タブ→「列のピボット」を押します！
この選択した列のデータ達(商品名)を
新しい列名として作成します。
つまり商品名がそれぞれが新しい列名として生成されます。
次に新しく生成される列に対して
各行の値はどこから持ってきますか？という事で
この「売上」列を選んであげます。
最後その値をどうするか？ですね。
これはExcelで使ってるピボットと同じ考え方です。
データの集約です。
最後値を、合計するのか？平均するのか？
今回は「合計」します。
はい、そうすれば・・・
簡単に出来上がりました！
たったこれだけです！！！
個人的にこれを一発で出せる機能って凄すぎると思います！
これだけでもPowerQueryを使う価値があると思います！
そしてこれ、数値じゃなくてもできるんです！
数値以外のデータを横に転置させよう！
まずは
左の「日別の出勤時間」のデータは
11月1日～7日までの「山田」「鈴木」「佐藤」「田中」の
4名の出勤時間を表にしています。
そしてこれを右の様に
社員名を列名にして、出勤時間を日別に出そうと思います！
ファイルに接続したら・・・
「名前」列を選択して「列のピボット」を押して
「値」列に「出勤時間」列を選び
ここで「集計しない」を選んであげます。
そうすると・・・できましたね！☆
でもこれ、きちんと原理を理解してないと
中々思った通りの形になってくれません！
列のピボットの注意点
かじむー
まず数値集計する場合について
先程の「月別売上」で考えてみます！
ここでもし「カテゴリ」列も残したいな～と思って
「カテゴリ」列を消さずに列のピボットをやってしまうと・・・
なんかスッキリした形にはなってないですよね。
これなんでだと思いますか？
実際どんな風に列のピボットになっていくのか、
データを絞って見てみましょう！
まず初めに「カテゴリ」列を消した場合を見ていきます。
このまま列のピボットをした場合・・・
■Step1：列を生成
まず「商品名」の「カツオ」と「すいか」が
新しい列名に移動すると思ってください！
■Step2：値を集計
ここで集約対象の「売上」の値がそれぞれ入ってますよね！
■Step3：ピボット対象の2列以外を集約
で、「商品名」と「売上」列以外の列、
つまり「月」列が「1月」という1種類だけなので
1行として集約できるのです！
もし「カテゴリ」列を残した場合・・・
・「1月」と「食品魚介類」
・「1月」と「食品フルーツ」
という2種類の組み合わせが存在します。
なのでこれを1行にすることはできませんよね。
実際に列のピボットをしてみると・・・
ご覧の通り「月」列の「1月」は集約できず
2行になってしまいました。
当たり前なのですが列の数が多いと
「あれ？思っていた形にならない」という事があります。
きちんとデータの中身を理解し
不要な列は削除してから列のピボットを行いましょう。
次に集計をしない場合について
先程の「勤怠表」で考えてみます！
ではこれもデータを絞って見てみます。
11月1日だけのデータに絞ります！
もし11月1日に2人「山田」が居たとします。
するとこのようにエラーとなってしまいます。
どうしてでしょうか？
もし集計対象が時間ではなく売上などの数値で
「合計」させる、としていれば・・・
この山田の2行のデータは合計されて出力されます。
でもこれは「集計をしない」なので
1月の山田に対しての出勤時間は1種類じゃないといけません。
何故なら出力先のセルは1つだからです。
ここに出勤時間を2種類出すことはできません。
このように「集計をしない」場合は
「値」列として選択した以外の列
つまり「出勤時間」以外の列の組み合わせが
重複しない様にしてから列のピボットを行いましょう。
まとめ
▼列データを横に転置させる方法
列のピボット
▼列のピボットの注意点
①集計をする場合
┗ 不要な列は削除する
②集計をしない場合
┗ 「値」列以外の列の組合わせは重複させない
