Agrupando rótulos e concatenando seus valores de texto (como uma tabela dinâmica)

Question

Eu tenho uma planilha com dados como este:

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

O que eu gostaria de agrupar tudo pela Coluna A e ter a Coluna B seria uma lista de valores delimitados por vírgulas que compartilham a Coluna A em comum, assim:

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

Infelizmente, as Tabelas Dinâmicas só sabem como trabalhar com valores numéricos, e o mais distante em relação a isso é contar o número de vezes que a Coluna A ocorre.

Eu consegui fazer isso importando os dados em um banco de dados MySQL e usando a função GROUP_CONCAT(Attribute) do MySQL em uma consulta com uma cláusula GROUP BY Product , mas depois de bater minha cabeça repetidamente na minha mesa enquanto tentava descobrir uma solução do Excel.

Para referência futura, isso é possível no Excel sem macros? Quer seja ou não, como alguém conseguiria isso?

    

Answer 1

   |     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

Supondo que a linha 1: 1 é linha de cabeçalho.

1. Classifique pela coluna A para agrupar por produto

2. Prepare os dados em formato separado por vírgula na coluna C inserindo em C2 a seguinte fórmula e copie para C3: C10.

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

3. Identifique linhas úteis digitando D2 =A2<>A3 e copiando para D3: D10.

4. Copie a coluna C: D, então cole especial como valor ( Alt E - S - V - Digite ). Você agora irá receber:

   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. Remova linhas inúteis filtrando FALSE na coluna D com o AutoFiltro e exclua essas linhas.

6. Concluir . Coluna A e amp; C é o que você precisa.

Answer 2

Eu sei que é um post antigo, mas eu tive esse desafio hoje. Eu usei o PowerQuery add-in da Microsoft (NOTA: está embutido no Excel 2016 por padrão).

1. Selecione sua tabela
2. Na guia CONSULTA DE ENERGIA (ou em DATA em 2016), selecione "Da tabela"
3. Clique na coluna "Produto"
4. na guia "Transformar", selecione "Agrupar por"
5. Na guia "Exibir", verifique se "Barra de fórmulas" está marcada

6. Altere a fórmula

   DE:

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

   PARA:

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

   

O passo 6 é aproveitar as fórmulas Power Query (M) para realizar manipulações de dados não expostas através das operações básicas fornecidas na interface do usuário. A Microsoft tem uma referência completa disponível on-line para todas as funções avançadas disponíveis no Power Query.

    

Answer 3

Aqui estão algumas abordagens, ambas "não macro" ...

1. Com um pequeno conjunto de dados, depois de primeiro classificá-lo por produto (semelhante ao Produto GROUP BY), você pode primeiro copiar a coluna "Produto", colá-la em outro local e remover duplicatas. Em seguida, copie os "Atributos" para cada produto e "cole especial, TRANSPOSE" ao lado de cada Produto. Em seguida, concatene uma vírgula com cada um dos seus atributos transpostos em uma coluna de resultados final. É certo que tudo isso "copiar / colar especial / transpor" iria envelhecer rapidamente se você tem uma longa lista de produtos.

2. Se você tiver muitos dados, usando algumas fórmulas, poderá trabalhar até chegar ao resultado final, conforme mostrado abaixo. As fórmulas em F2, G2, H2, I2 e N2 são indicadas pelas setas azuis. Copie-os para as linhas abaixo conforme necessário. Observe que J2: L2 usa a mesma fórmula que I2. Além disso, a fórmula F2 refere-se a um intervalo nomeado "Produtos" que abrange o intervalo A: A.