見出し画像

Excel（エクセル）のPower Query（パワークエリ）Tips！ - 縦型データ表を横型データ表に変換する

出戻りガツオ🐟

こんばんは！出戻りガツオです。
しばらくゴリラ🦍になったり、更新しなかったり申し訳ない。

気持ちの上がり下がりが激しい数か月で更新にいたりませんでした。
育休の体験の更新も忘れておりませんし、自分の2022年の挑戦も継続中です。どうか長い目でお待ちください。

さて、今日のテーマは「Excel（エクセル）のPower Query（パワークエリ）」です。
昨晩Twitterで仲良くさせていただいている方の課題を拝見させていただき、
自分がやってみたいな！と思い、夜の酒の肴でトライしてみました！
※Excelジャンキーなので相談いただければ気分次第でやってみますｗ

大変ありがたいことに、いい言葉もいただき
「このテクニックに興味があるひといますよ！」
とお声をいただいたので自分の備忘録としてシェアさせてください。

テーマは「縦型データ表を横型データ表に変換する」です。

縦型データ表を横型データ表に変換する

イメージ

イメージ

お題は下記のサイトの例を引用させていただいています。

Power Query（パワークエリ）について

さて「Power Query」の記事は初めてですね。結構やってるんだけどなｗ
前書きですが長いのでこのプロット読み飛ばしてください笑

Power Query (Excel で get & Transform と呼ばれる) を使用すると、外部データをインポートまたは接続し、そのデータを作成できます (たとえば、列の削除、データ型の変更、テーブルのマージなど)。 次に、クエリをグラフに読み込Excelグラフとレポートを作成できます。 定期的にデータを更新して最新の情報に更新できます。 Power Query は、Web 用の Excel、Excel、Windows、Excel for Mac、Excel アプリケーションの 3 つで利用できます。

Excel の Power Query について

相変わらずMicrosoft語は全くわからないZE☆
私の解説だと表形式のデータを鮮やかに加工することでできる機能です。

Excelのピボットテーブルは有名な機能ですがそれ以上の凄まじい機能になります。データの加工派一般的に「集計（ピボット）」の使われ方の印象もありますがPower Queryは収集・整形・レポート化をワンクリックで実施できる強力な機能です。

このPower Query機能は「Power BI」というMicrosoftのいずれBI界のスタンダードになるソフトウェアでも扱われます。

Power BIのプロフェッショナルはそのデータの一要素から鮮やかな関連データ、レポートを作られていたりもします。

課題にトライ！

参考データ

今回のテーマは縦の列に「氏名」、隣に「保有資格」が記載されています。
データの蓄積としてはあるべき姿です。
ただし実際に見る資料としてはニーズはこうだと思います。

人別に保有資格を見る場合の横型データ

このような整形はPower Queryでどうやるのでしょうか？
イケんじゃね？という甘い発想でまずやってみました！

最初にまずコード全体から

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    グループ化された行 = Table.Group(ソース, {"氏名"}, {{"テーブル化", each _, type table [氏名=nullable text, 保有資格=nullable text, カスタム=any]}}),
    追加されたカスタム1 = Table.AddColumn(グループ化された行, "カスタム", each Table.AddIndexColumn([テーブル化],"連番",1)),
    #"展開された カスタム" = Table.ExpandTableColumn(追加されたカスタム1, "カスタム", {"保有資格", "連番"}, {"保有資格", "連番"}),
    変更された型 = Table.TransformColumnTypes(#"展開された カスタム",{{"連番", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "カスタム", each "保有資格_" & [連番]),
    削除された他の列 = Table.SelectColumns(追加されたカスタム,{"氏名", "カスタム" , "保有資格"}),
    ピボットされた列 = Table.Pivot(削除された他の列, List.Distinct(削除された他の列[氏名]), "氏名", "保有資格"),
    降格されたヘッダー数 = Table.DemoteHeaders(ピボットされた列),
    転置されたテーブル = Table.Transpose(降格されたヘッダー数),
    昇格されたヘッダー数 = Table.PromoteHeaders(転置されたテーブル, [PromoteAllScalars=true]),
    #"名前が変更された列 " = Table.RenameColumns(昇格されたヘッダー数,{{"カスタム", "氏名"}})
in
    #"名前が変更された列 "
ステップ

Power Queryの機能はコードをコピペしてそのままそっくりファイルにセットできます。

1. Power Query エディターで「詳細エディター」をクリック
2. 詳細エディターの中の赤枠の中に貼り付けるとそのまま機能の導入が可能です！

1. Power Query エディターで「詳細エディター」をクリック
2. 詳細エディターの中の赤枠の中に貼り付けるとそのまま機能の導入が可能

・お願い

  • Power Queryのステップは本来的にもっとわかりやすく記載する事が望ましいですが、今回は同じ操作をしたらそのまま同じ内容になります

  • もっと良いやり方は絶対あると思ってます。それは教えを乞いたいです笑
    ※たたき台くらいで見てください

ぶっちゃけスグ終わらなかったｗ

イメージとしては
保有資格をピボットすればいいよなーと思いましたが
そんなわけない。せいぜいできることは数のカウントくらいです。
（↑当たり前

あれ？間違って全然できないことできるって言ってしまったのでは・・・と青ざめましたが、視点を変えてみました。

やりたいことを整理する

今回のポイントは

保有資格を列として作成できる下ごしらえをする。

というです。「保有資格」という情報を横に展開する列を作ること。
そのために「人」ごとに持つ「保有資格」にキー（インデックス）があれば
列の加工が可能と考えました。

氏名 & 保有資格のキーが必要

ちなみにExcelの集計でも他人事にそのデータが何番目の情報なのか、
という関数の設定は良くやります。
例えばこの氏名の列について、
この氏名が出てきたのは何回目かな？ということを調べるのはこんな関数。

=COUNTIF(変わらない起点:動的な終点,検索値)
↑解説

フィル機能を使ったテクニックですがセル単位でデータを設定するPower Queryではそうはいきません。どうやってやるのかな～

Google先生に相談だ！

参考にしたのはこれ！やっぱりあるじゃん！便利技！「Table.AddIndexColumn」

TwitterでExcelテクニックを発信されていらっしゃる「ガッツ鶴岡」先生が素晴らしい記事を挙げられていらっしゃいました！

Table.AddIndexColumn

MS語の翻訳は難解なので省くと、グループ別の連番の作成が可能！
つまり！

氏名でグループ化してカスタム列を追加すると・・・！！！
連番ができました！！

いや～えげつない機能ですね。調べりゃデータの調理はなんでもできる。
そして素晴らしい方々が惜しみなくテクニックをインターネットでシェアされる。私、こんな優しいテックワールドLOVEです。

ガッツ鶴岡先生ありがとうございました！！

ここまでできればもう簡単！

ん！って感じられるかもしれませんが、ここでやることはピボットです。
は？って思われるかもしれませんが「連番」でピボットすればいいんです。

列のピボット

  1. 列のピボット


設定はこんな感じ

保有資格を値として、集計はしない形にしましょう！すると！

連番によるピボットができました！

これを行列入れ替えてみましょう
その前に「ヘッダー」を一行目と使用を押してください。これをしないとヘッダーの行列入れ替えが上手くいかんのです・・・。

ヘッダーを1行目として使用！
行列の入れ替え

ここで行列を入れ替えます。必殺！「転置」

とりあえず形にはなった！

連番が列名になった集計が可能です！
ここで凄いな！って思うところアピールさせてください。

誰が何個資格持ってようが列数は自動的に変わります！！！

今日の格言

連番の項目数に合わせて列数が決まるので、動的に列が広がるんです！
自動ですよ！自動！凄すぎます！！！
こういう機能がパワークエリのえげつない素晴らしさなんです！！！

私はExcel VBA Loverですがスマートにこういう機能がついちゃうともうやみつきになっちゃいますよ！！

興奮しつれいしました。ちなみに列名が1，2，3・・・みたいな数字だと味気ないので項目少しいじってます。

連番のデータ型をテキスト型にします

勉強不足であれば申し訳ないですが私の検証段階だと、Power Queryはデータ型が厳密です。たとえば [ 数字 ] と [ 文字 ]のつなぎ合わせはできない、とか縛りがあるようです。Pythonっぽくていいですね。

カスタム関数で番号の前に保有資格と入れる

[ = "保有資格_" & [連番] ]こんな関数で文字の結合ができちゃいます。
こういうヘッダーのほうが私は見やすいです。

結果は！！

できたー(((o(*ﾟ▽ﾟ*)o)))

横型の表できちゃいましたね！HAPPY！！
こういうニーズがあれば是非やってみてくださいな！

Appendix

CLIP STUDIO PAINT（クリスタ）練習中

DX🦍
Excel🦍
帰ってきた出戻りガツオ
今のアイコン

買ってよかったもの

ハマりまくりです

エコ、風船も作れる🎈

裏通りのドンダバダ

美味すぎる禁断のビール🍺です！ローソンに急げ！！

この記事が参加している募集

休日のすごし方

53,541件

この記事が気に入ったらサポートをしてみませんか？