Tentativa de transposição de duas tabelas de colunas no excel usando a primeira coluna para agrupar valores na segunda coluna

Question

Eu tenho uma tabela de duas colunas no Microsoft Excel 2016 que lista apenas uma categoria de despesa na primeira coluna e o valor da despesa na segunda coluna. categoria de despesa não é única e será repetida várias vezes.

A saída que estou procurando terá categorias de despesas exclusivas como cabeçalhos de coluna e todos os valores de despesas listados nessa categoria de despesa específica.

Eu tentei isso usando o Excel Query. Depois de duas etapas padrão na consulta, minha primeira etapa é agrupar linhas por coluna "Category" com o novo nome de coluna definido como "CategoryValues" no meu caso e a operação nessa coluna é "All Rows". Isso produz tabelas com categorias exclusivas na primeira coluna e referências a tabelas na segunda coluna. O próximo passo é transpor a tabela e promover a primeira linha para os cabeçalhos. Após essas duas etapas, eu tenho categorias exclusivas como colunas com cabeçalhos corretos e a primeira linha de dados para cada coluna contém referência de tabela para outra tabela em que a primeira coluna é única categoria e valores apenas nessa categoria.

Alémdisso,possoclicaremumaúnicareferênciadetabela,oquemelevaráàtabelaacimamencionadaparacategoriaespecífica.Aprimeiracolunapodeserexcluídaeeuficocomumacolunadecategoriaúnicacomvaloreslistadoscomolinhas.

Isso é exatamente o que eu estou tentando alcançar, mas com todas as categorias.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Pancake\Documents\TestQuery.xlsx"), null, true),
    TblExpenses_Table = Source{[Item="TblExpenses",Kind="Table"]}[Data],
    #"Grouped Rows" = Table.Group(TblExpenses_Table, {"Category"}, {{"CategoryValues", each _, type table}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Adv  Fee" = #"Promoted Headers"{0}[Adv. Fee],
    #"Removed Columns" = Table.RemoveColumns(#"Adv  Fee",{"Category"})
in
    #"Removed Columns"

Aqui está um exemplo de dados

Category    Amount
Toll        3.65
Toll        4.8
Toll        120.35
Toll        10
DEF         23.32
DEF         15
Toll        13.25
Toll        122.35
DEF         8.66
Fax         2
Fax         2
Scale       11
Scale       2
Toll        3.5
Adv. Fee    0.99
Adv. Fee    12.95
Oil         17.98
Fax         2
Fax         5
DEF         30

    

Answer 1

Aplicando este Muito boa referência ao seu Q, aqui está a minha versão .. Supondo que os dados estejam em A1: B21.

1. Selecione a coluna A e copie-a para a coluna D (coluna vazia). Usar dados > remover duplicatas para obter valores exclusivos da lista. Nós vamos entrar em D1: D7:

D1 ---> Category D2 ---> Toll D3 ---> DEF D4 ---> Fax D5 ---> Scale D6 ---> Adv. Fee D7 ---> Oil

2. Em seguida, selecione os valores exclusivos, copie. então clique com o botão direito em F1 e escolha Transpose. (Ele colará os dados da coluna copiados como linhas. Portanto, na F1: K1, obteremos:

F1 ---> Toll G1 ---> DEF H1 ---> Fax I1 ---> Scale J1 ---> Adv. Fee K1 ---> Oil

3. Em F2, coloque isso e pressione Ctrl + Shift + Enter:

=IFERROR(INDEX($B:$B,SMALL(IF($A:$A=F$1,ROW($B:$B)-MIN(ROW($B:$B))+1),$E2)),"")

4. Arraste até o K9. Feito.

Espero que ajude .. (: