列の値でグルーピングして同じグループの複数行を連結する（Excel/PowerQuery）

2023年2月19日 12:28

こんにちは。今回は趣向を変えて、Excelばなしです。

先日知人に以下のユースケースの様な内容を相談されました。相談されたらお役に立ちたいということで、調べた内容をまとめてみました。最初はデータ行を並べ替えてVlookupを使って、と言った提案をしたのですが、途中で思い直しPowerQueryでできそうだな、と言うことで訂正提案をした経緯があります。

正直PowerQueryはほとんど使ったことがなかったのですが、私も勉強になりましたので、同じ様なことで困っている方の助けになれば幸いです。

ユースケース

まず以下のテーブルをご覧ください。

こういうデータを処理することないですか？たとえば、担当窓口、担当営業の接客や提案などに関するアンケートデータをもらって、担当ごとにコメントをまとめたい。
ゴールは以下のような表です。

こんな表が簡単に作れると良いな、と思われているならご一読くださいませ。無料です。この例では40行なので、コピペしても出来ないことはないですが、数千行・数万行あるよ、という場合や、毎週・毎日やらないといけない、といった場合ですね。

早速ですが説明です。

前提

元データは既にあるものとします。上の青い方の表です。列名と1行目のデータをCSVで書いておきます。
以下の操作はMicrosoft365で行っています。インストール環境によってはこの通り動かないかも知れません。
PowerQueryって初めて聞いたよ、という方向けです。

#,問合せ発生日,担当者名,アンケート回答日,フリーコメント
1,2023/1/1 10:23,ボット,2023/1/1 23:00,とてもご丁寧に説明頂き、問題もすぐに解決しました。ありがとうございました。

やり方

Excelを開く

元データのブックは開いておいても、そうでなくても構いません。まっサラなExcelブックを一つ開きましょう。

元データを読み込む

図のように、【データ】リボンから、【データの取得】-> 【ファイルから】-> 【Excelブックから】と進んで選択します。

すると、図のようなファイルダイアログが開きますね。読み込みたい元データのブックを選択して、右下の【インポート】を押下します。

暫く待っていると、以下のようなダイアログが現れます。

選択した元データのファイル名が見えていることを確認してください。違っていたら、キャンセルしてファイルを選択し直します。

ファイル名の下に【テーブル1】、【Sheet1】などと出ていますが、これは、これから読み込むデータの範囲を指定する箇所です。

例えば【テーブル1】を選択してみると、以下のように元データのブックにある【テーブル1】の内容が右側にプレビューされます。

集約したいデータが見えていることを確認したら、右下の【読み込み】というところをクリックします。下図は▼部分をクリックした場合の図ですが、左の文字をクリックすると、この選択肢は出ずに直ぐに読み込みが始まります。選択肢が出た方は【読み込み】の方を選択しましょう。

すると、元データの【テーブル1】の内容が読み込まれます。

これで第一段階完了です。シートの右側には【クエリと接続】というペインが出ているはずです。

クエリリボンを選択する

【クエリと接続】が出ているなら、上の方に【クエリ】というリボンが出ます。下図がその様子です。

【クエリ】リボンを選択すると、以下のようになります。

PowerQueryエディターを開く

左に見えている【編集】を押下すると別の画面が開きます。

これがPowerQueryエディターです。この画面で色々操作して、ゴールに向かっていきます。
この先は、担当者名でグルーピングして、フリーコメントの内容を集約する、という順序でやっていきます。

担当者名でグルーピングする

【グループ化】を選択します。

すると画面が開きます。

上図で#が表示されているプルダウンは、元データの列名の一覧です。▼を押下すると、リストとして現れます。

グループ化したい列名をここで選択します。ここでは担当者名でグルーピングしたいので、担当者名を選択します。

【新しい列名】には後で分かるように文字列を入力します。ここでは、"担当者名でグルーピング”と入れておきます。
【操作】は【すべての行】を選択しておきます。

【列】は選べないはずですので、【OK】を押下します。
すると、グループ化ダイアログが閉じて、PowerQueryエディターに以下のように表示されます。

右側には行が一つ追加されています。

【担当者名でグルーピング】列の各行をクリックすると、担当者名に該当する行だけが下に表示されます。

ここまで出来たらグルーピングは終わりです。もしおかしな表示になった場合は、右側の【クエリの設定】の【グループ化された行】の左側の✕を押すと、やり直しが出来ます。

グルーピングした行の列を集約する

PowerQueryエディターのリボンから【列の追加】を選びます。

【カスタム列】を押下してください。【カスタム列】ダイアログが開きます。

【新しい列名】に後で分かるように文字列を入力します。そのままでも構いませんが、ここでは"フリーコメント抽出"と入力しておきます。

新しい列名に入力

【使用できる列】の【担当者名でグルーピング】を選択します。前のステップで違う名前をつけていた場合はその名前の文字列を選択します。

その下の【＜＜挿入】ボタンを押下すると、【カスタム列の式】の中身が少し変わります。

この状態から、【カスタム列の式】の最後に[フリーコメント]と入力します。[と]は半角で入力してください。下の表に出ている列名に一致する文字列を入力することが大切です。

　”構文エラーが検出されませんでした。"と表示されていることを確認して【OK】を押下します。うまく出来たら、以下のようになります。

【クエリの設定】も一行追加されています。

【フリーコメント抽出】の各行をクリックすると、担当者名別のフリーコメントが下の表に表示されます。

ここから、フリーコメントの集約のための作業です。【変換】リボンを選択して、右側の【値を抽出する】を選択します。

【リストから値を抽出する】ダイアログが表示されます。

プルダウンの▼を押して、選択肢の中から【--カスタム--】を選びます。すると、下の方に項目が増えます。

これは、フリーコメントを集約するときに、各コメントをどの様に連結しますか、と言う設定です。
今回はコメント同士を以下のように連結して一つのセルに表示したいので、そうなるように設定していきます。

コメント甲
======
コメント乙
======
コメント丙

上の様にするには、コメント甲のあと、改行して"====="を入れて、また改行して、コメント乙をつなぎます。複数行をつなぐときに間に何を入れるかを指定しますので、改行 + "=====" + 改行が指定されるようにする、ということです。
改行は「特殊文字」なので、まず【特殊文字を使用して連結】をOnにします。

選択肢の中から【復帰改行】を選択します。そうすると、上のテキストボックスに#(cr)#(lf)が入力されます。これが復帰改行、という意味の記号です。【復帰】や【改行】を選んでも大丈夫だとは思いますが、ここでは【復帰改行】としておきます。

ここまでで、改行 + "=====" + 改行の最初の改行までを指定したことになります。次は"====="の追加です。テキストボックスに直接入力します。

この状態から、もう一度、【特殊文字を挿入】から【復帰改行】を選択します。最初からこの文字列（#(cr)#(lf)=====#(cr)#(lf)）を直接手で入力しても大丈夫です。

これで【OK】を押下します。

目的の状態になりました！＼^^／

【クエリの設定】にも一行追加されています。

クエリをシートに反映する

ここまで出来たら、この内容をシートに反映するだけです。
【ホーム】リボンから【閉じて読み込む】--> 【閉じて読み込む】を選択します。

できました！

改行されてませんね。【フリーコメント抽出】列を選択して、【ホーム】リボンの【折り返して全体を表示する】を設定しましょう。

改行されました。

少々詳しい方向け

M数式言語、と言われて何のことかわかる方のために、式を書いておきます。

M数式言語

let
    ソース = Excel.Workbook(File.Contents("C:Users\void\Documents\Personal\窓口業務フリーコメント集計.xlsx"), null, true),
    テーブル1_Table = ソース{[Item="テーブル1",Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(テーブル1_Table,{{"#", Int64.Type}, {"問合せ発生日", type datetime}, {"担当者名", type text}, {"アンケート回答日", type datetime}, {"フリーコメント", type text}}),
    グループ化された行 = Table.Group(変更された型, {"担当者名"}, {{"担当者名でグルーピング", each _, type table [#"#"=nullable number, 問合せ発生日=nullable datetime, 担当者名=nullable text, アンケート回答日=nullable datetime, フリーコメント=nullable text]}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "フリーコメント抽出", each [担当者名でグルーピング][フリーコメント]),
    抽出した値 = Table.TransformColumns(追加されたカスタム, {"フリーコメント抽出", each Text.Combine(List.Transform(_, Text.From), "#(cr)#(lf)=====#(cr)#(lf)"), type text})
in
    抽出した値

この式は、PowerQueryエディターから【ホーム】リボンの【詳細エディター】を選択すると、表示されます。

【詳細エディター】はこんな感じです。

なんか変！という時

結果がおかしい、とか、PowerQueryエディターをもう一度出したいけどわからない、という方のために。

結果がおかしい時はやり直せる

PowerQueryは操作した内容を順番に【クエリの設定】の下に追加していきます。思った通りにならない！と言う時は、【クエリの設定】を下から選択して行って、おかしくなった所の左側の✕をクリックすると、その操作が消えますので、もう一度やり直せます。ただし、削除した行の下の行も全部消えてしまいますので、そこは注意しましょう。

あのPowerQueryエディターをもう一度

一度設定したものの、列名が気に入らない、条件を変えたい、やり方を確認したい、という時、もう一度たどり着けないと困りますよね。そんな時のやり方です。まず選択肢だけ先に書きます。

【クエリ】リボンが出ている場合はそこから出せる
【データ】リボンの【クエリと接続】からも行ける

まず１番から。ブックに【クエリ】リボンが表示されている時は、【クエリ】リボンを選択して【編集】を押すとPowerQueryエディターに会えます。

もし出ていなくても、抽出結果の表の中のセルを選択すると出てきます。大抵はこれで行けるはずです。

次に２番です。もし【クエリ】リボンが出てこない場合、【データ】リボンを選択して、【クエリと接続】を選択しましょう。

【クエリと接続】はトグルスイッチになっていて、押すたびに、右側に【クエリと接続】が表示されたり非表示になったりします。

表示された状態で、【XXX行読み込まれました。】と出ている箇所にマウスカーソルを重ねると、フローティングダイアログが出ますので、そこから【編集】を選ぶと行けます。

元データを更新した場合でも簡単

先月作ったけど、今月データ増えたからまた作んなきゃ！と思う必要はありません。【クエリ】リボンから【更新】とやれば、自動的に反映されます。逆に反映されてないな、と思う方も同様に【更新】してみて下さい。

他のPCで開くと更新出来ない

元データが参照できなくなったからです。VLookupなどで、別のブックを参照していて、そのブックを他のパソコンで見るとエラーになるのと同じ現象です。
OneDriveを使っている方なら、そこに元データを置いてから作業を始めるのも良いですし、或いは元データ情報も一緒に別のパソコンに持って行ってしまえば良いと思います。ただし、その場合はクエリを作ったパソコンと同じフォルダ構成で置かないとエラーになります。同じ構成に出来ない（したくない）場合は、【データソースの設定】から、参照先を変更しましょう。

【データソースの設定】を選ぶと、元データのパスを変更できますので、適宜修正して更新して下さい。

ちょっとした思いつきで書き始めたのですが、５時間位経ってました…。PowerQueryの勉強にもなったので、良い経験になりました。

では。

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