microsoft excel – ラベルをグループ化し、そのテキスト値を連結する（ピボットテーブルのような）

microsoft-excel microsoft-excel-2010 pivot-table

こんな感じのデータが入ったスプレッドシートを持っています

Product   | Attribute
----------+----------
Product A | Cyan
Product B | Cyan
Product C | Cyan
Product A | Magenta
Product C | Magenta
Product B | Yellow
Product C | Yellow
Product A | Black
Product B | Black

私がやりたいのは、A列ですべてをグループ化して、B列をカンマ区切りのリストにして、A列を共通にする値のリストを作成することです

Product   | Attribute
----------+--------------------------
Product A | Cyan,Magenta,Black
Product B | Cyan,Yellow,Black
Product C | Cyan,Magenta,Yellow,Black

残念なことに、ピボットテーブルは数値の扱い方しか知らず、A列の発生回数を数えることが一番遠いです

最終的には、MySQLデータベースにデータをインポートして、MySQLのGROUP_CONCAT(Attribute)関数をGROUP BY Product句のあるクエリで使用することで、これを成功させることができましたが、Excelでの解決策を考えようとしながら、机の上で何度も頭を叩いた後でした

参考までに、これはマクロなしでExcelで可能なのでしょうか？可能かどうかは別として、どのようにしてこれを実現するのでしょうか？

　 24　 p0lar_bear　 2012-07-24

ベストアンサー

   |     A     |     B
---+-----------+-----------
1 |  PRODUCT  | ATTRIBUTE
2 | Product A | Cyan
3 | Product B | Cyan
4 | Product C | Cyan
5 | Product A | Magenta
6 | Product C | Magenta
7 | Product B | Yellow
8 | Product C | Yellow
9 | Product A | Black
10 | Product B | Black

1:1行目をヘッダ行とする

  1. A列でソートして製品ごとにグループ化します

  2. C2に次の式を入力し、C3:C10にコピーダウンして、C列のデータをカンマ区切りで用意します

    =IF(A2<>A1, B2, C1 & "," & B2)

  3. D2 =A2<>A3に入力して有用な行を識別し、D3:D10にコピーダウンします

  4. C:D列をコピーし、値としてspecialを貼り付けます。これで取得できます

    Product A    Cyan       Cyan                   FALSE
Product A    Magenta    Cyan,Magenta           FALSE
Product A    Black      Cyan,Magenta,Black     TRUE
Product B    Cyan       Cyan                   FALSE
Product B    Yellow     Cyan,Yellow            FALSE
Product B    Black      Cyan,Yellow,Black      TRUE
Product C    Cyan       Cyan                   FALSE
Product C    Magenta    Cyan,Magenta           FALSE
Product C    Yellow     Cyan,Magenta,Yellow    TRUE

  5. D列のFALSEをAutoFilterでフィルタリングして不要な行を削除し、それらの行を削除します

  6. 終了です。A＆amp; C列は必要なものです

31　 wilson　 2012-07-25

古い記事になってしまいましたが、今日はこんなチャレンジをしました。使ったのは、MicrosoftのPowerQueryアドインです（注意：デフォルトでExcel 2016に組み込まれています）

  1. テーブルの選択
  2. POWER QUERYタブ（2016年はDATA）で「テーブルから」を選択します
  3. 商品」欄をクリックします
  4. 変形タブの下で、”Group By “を選択します
  5. 表示] タブで、[数式バー] がチェックされていることを確認します

  6. 式の変更

    FROM:

    = Table.Group(#"Changed Type", {"Product"}, {{"Count", each Table.RowCount(_), type number}})

    TO:

    = Table.Group(#"Changed Type", {"Product"}, {{"Attributes", each Text.Combine([Attribute], ", "), type text}})

    Click here for picture depicting steps from above

ステップ 6 は、Power Query (M) 式を活用して、UI で提供されている基本的な操作では公開されていないデータ操作を実行します。マイクロソフトでは、Power Query で利用できるすべての高度な機能について、オンラインで完全なリファレンスを公開しています

14　 Matt Poland　 2016-03-18

ここにいくつかのアプローチがありますが、どちらも「非マクロ」です

  1. 小さなデータセットでは、最初に商品ごとにソートした後（GROUP BY 商品と同様）、まず「商品」列をコピーして別の場所に貼り付け、重複を削除することができます。次に、各商品の「属性」をコピーして、各商品の横に「特殊、transpose」を貼り付けます。そして、最終的な結果の列に、転置された属性のそれぞれとコンマを連結します。確かに、この “コピー/特殊ペースト/トランスポーズ “は、長い製品リストを持っている場合、すぐに古くなってしまうでしょう

  2. データがたくさんある場合は、いくつかの式を使って、以下のように最終的な結果を得ることができます。F2、G2、H2、I2、N2の数式は青い矢印で示されています。必要に応じてこれらを下の行にコピーしてください。なお、J2:L2はI2と同じ式を使用しています。また、F2 式は、範囲 A:A にまたがる名前付き範囲 “製品” を参照しています

enter image description here

3　 F106dart　 2012-07-24

スポンサーリンク
タイトルとURLをコピーしました