
PowerQueryピボット解除+再ピボットで横持ち表を縦にする(横持ち兼務複数リストを縦にする)
逆引きVLOOKUPで案内した次課長リストですが、
兼務の場合「一人で複数行のデータを作る必要がある」旨、説明しました。
PowerQueryで対応できるので、ご案内します。
今回は、PowerQueryの「ピボット解除」+「ピボット設定」を活用します。
他の横持ち表でも活用できるノウハウです。
PowerQueryの画面構成や手順については以下をご参照ください。
(5年前にシステムの情報交流会で書いた記事です)
PowerQuery【02】PowerQuery(パワークエリー)の動かし方
これを ↓

こうします。↓

Power Queryとは? 〜Excelユーザーのための“データ整形エンジン”〜
Power Query(パワークエリ)は、ExcelやPower BIに搭載されている「データ整形の自動処理ツール」です。
関数でもVBAでもない、けれどもその中間のような存在で、手作業でやっていた以下のような処理を、繰り返し使えるかたちで自動化できます。
データの整形(横持ち⇔縦持ち、列の抽出・結合など)
複数ファイルの統合(たとえばフォルダ内のExcelやCSVを全部つなげる)
繰り返し処理の自動化(一度設定すれば再利用可能)
数式不要、クリック操作が中心(コードが不要)
こんな方におすすめです
「毎回似たような手作業を繰り返している」
「VBAは難しそう。でも効率化したい」
「Excel関数ではどうにもならない整形をやりたい」
Power Queryは、そんな場合の【表のかたちを整える便利道具箱】です。
ポイントは「VBA不要。でもちょっとだけ考え方が必要」ChatGPTに聞こう!
Power Queryでは M言語(ちょっとしたスクリプト) を使いますが、実際にはクリック操作でほとんどの処理が可能。
それでも、「ここってどう書けばいいの?」と迷うことはあります。ここでは、ChatGPT等の生成AIがとても役立ちます。
Power Queryは、Excel関数やVBAの前に覚えておいて損はない
“中間ステップ”の名人です。
さて、本題の今回の事例です。
横持ちの表を縦に変換する(PowerQuery手順付き解説)
今回やりたかったこと:
横に並んでいる兼務所属や職位のデータ(横持ち表)を、
1件ずつ縦にまとめて縦持ち表にしたい。
(兼務の数が1つ以上あれば、一人2行以上になる)
もとの表(横持ち)
兼務があるときは、一人で複数の列に所属があります。(所属2、所属3)
※冒頭では、コードを省略しましたが、実際にはコードまでついています。

作りたい表(縦持ち)
変換後(縦持ち) 兼務があるときは、一人で複数行のデータが出来ています。CC宛名リストを作るときだけでなく、人員集計等も含めて、こちらの方が断然扱いやすいです。

Power Queryでどう処理していくか
以下はすべてPowerQuery上での処理で、ピボットもPowerQuery上でのピボット処理です。
1.所属1~職位名3まですべてをピボットの横軸とみなし、ピボットを解除する。

2.所属1~職位名3の[分類名]から数字を抽出して、
[グループ分けする数字][数字を除外したヘッダ名]に分ける。

3.元の[分類名]を削除して[数字を除外したヘッダ名]でピボット
(数字部分は、ID・氏名同様に縦軸でグループ化)
グループ分けする数字][数字を除外したヘッダ名]に分ける。

3.元の[分類名]を削除して[数字を除外したヘッダ名]でピボット

Power Queryの実際の手順
🔹Step 1:Power Queryを起動
Excelの「データ」タブ →「テーブルまたは範囲から」
(元表をテーブルにしておくと扱いやすい)

テーブルになっていない場合、以下のようにテーブルの作成画面が出ます。

※PowerQueryの案内(PowerQuery【02】PowerQuery(パワークエリー)の動かし方)に記載していますが、PowerQueryで扱うExcelはテーブルです。テーブルにしてない場合は、「範囲」を選択しますが、この「範囲」はテーブル化されます。
「クエリの設定」表示今回のPowerQuery処理内容
一番右側に「クエリの設定」が表示されます。
ここで、どのように処理しているかもわかるし、修正もできます。
なお、取り消すときは、左側の✖をクリックします。
(今回久しぶりで、Ctrl+Zで戻れなくてアタフタしたので、書いておきますね)
どうやっているか確認したり直すのは、右の歯車です。

🔹Step 2:列を行に変換(ピボット解除)
氏名以外の列(第1所属〜第3職位)を選択
「変換」タブ →「列のピボット解除」

→「属性」「値」という2列に変換されます

🔹Step 3:「番号」と「種類」列を分けて追加
この辺はChatGPTに聞けば、詳しく教えてくれます。
列の追加/カスタム列を使います。ウィザードが立ち上がります。
① 「番号」列を追加(式をコピペ)
属性名の中の数字(1〜9など)を抽出:Text.Select([属性], {"0".."9"})
→ 第2所属 → 2、第3職位 → 3


② 「種類」列を追加(式をコピペ)
数字を除いた文字列:(=種類名)を抽出:Text.Remove([属性], {"0".."9"})
→ 第2所属 → 所属、第3職位 → 職位


🔹Step 4:「属性」列を削除しておく
ここまでで「番号」と「種類」が別々に取得できたら、「属性」列は不要なので削除


🔹Step 5:「氏名」と「番号」でグループ化 → ピボット
「氏名」「番号」でグループ化(またはそのまま次のピボットへ)
「種類」列を基準に「ピボット」
「値」列を集計対象に設定(集計方法は「最初の値or最小値」でOK)


🔹Step 6:終了後、閉じて読み込み

Step 7:Excelの画面に戻る
クエリ表示になっているがそのまま使えます。

更新と編集
更新
元データ変更後、すべて更新 すれば、元データの情報が反映される

編集
画面の右側にクエリと接続ウィンドウが表示されるので
右クリックして編集 をクリックするとクエリーウィザード画面に進む。

処理のまとめ
「種類」を判別するには、Text.Remove([属性], {"0".."9"}) がシンプルで便利
「番号」と「種類」の2軸で情報を整理しておくと、ピボット処理が簡単
「属性」列は補助列扱いなので、不要になったら削除してOK
わからなければ、ChatGPTに聞こう!
私も大分聞きました!
ChatGPTへの聞き方:PowerQueryで困ったらこう聞こう!
こんな風に聞きました。
私:powerqueryのピボット解除は1要素ならできるが 複数要素のときは
どうすれば 例 兼務の第2 3所属と職位
ChatGPT:
Power Query(パワークエリ)で「ピボット解除」(列のアンピボット=列を行に変換)をする際に、**複数要素(例:第1所属・第2所属・第3所属、職位など)**がある場合は、以下の手順で整えることが可能です。
(例示部分省略)
手順(Power Query)Power Query に取り込む
Excel → データ → テーブルまたは範囲から → Power Query エディター起動
列の選択と「列のピボット解除」
氏名以外のすべての列(例:第1所属~第3職位)を選択
「変換」→「列のピボット解除」
「属性」列を分解
「属性」列(例:第1所属, 第2職位)を、
「番号」と「種類」(所属or職位)に分ける
例: 「第1所属」 → 1(番号)と 所属(種類)
これには、「列の分割」→「文字数または区切り記号(数字の前)」を使うか、
Text.BeforeDelimiter / Text.AfterDelimiter 関数をカスタム列で使います
所属と職位のペアに変換
「種類」列でフィルターまたは条件分岐(所属と職位に分かれている)
「種類」が所属のものと、職位のものを 番号列(1,2,3)で結合するため、
両者を分けて処理し、Merge Queriesで番号と氏名で結合します。
不要列を削除して整形
例1:「列名の末尾にある数字を取り出したい」
以下のようになっているもので、 PowerQueryで 番号と種類別でピボットして 番号毎1行にできないか?
氏名 番号 種類別 値
山田 1 所属 営業部
山田 2 所属 経理部
山田 3 所属 開発部
山田 1 職位 主任
山田 2 職位 係長
山田 3 職位 課長
powerQuery で ② 種類列(所属 or 職位)を追加(カスタム列):の部分で、 別途取得している番号だけ、除く 所属名1 → 1を除いて所属名のみにする方法を教えて
Text.Select([列名], {"0".."9"})
例2:「属性列から数字を除いて文字列だけ取りたい」
質問例:
Power Queryで「第1職位」「第2所属」のような列から、数字だけを除いて「職位」「所属」だけ取り出したいです。
Text.Remove([列名], {"0".."9"})
コツは「何をしたいかを例示しながら書く」こと
PowerQueryの言語である「M言語」の関数名がわからなくても大丈夫。
ChatGPTに「やりたいこと」をそのまま伝えれば、関数や構文で教えてくれます。
Power Query + ChatGPT = 最強の効率化コンビ
昔なら、マクロ(VBA)を書ける人しかできなかったような処理も、
今はPower QueryとChatGPTを組み合わせることで、誰でも再利用できる自動処理にできます。かなり便利につかえます!