Início / coding / Perguntas / 79566257
Accepted
Stephan
Asked: 2025-04-10 17:34:00 +0800 CST

Tabela Dinâmica do Excel com valores únicos com base no valor de duas colunas e soma das respectivas pontuações e opção de filtragem

  • 772

Tenho um conjunto de dados (simplificado) que se parece com o exemplo abaixo

Tabela de dados do Excel

Pontuação Primeira Língua FLPorcentagem Pontuação FLS Segunda Língua Porcentagem SL Pontuação SLS Ano Trimestre
120 Java 90 108 C# 10 12 2024 4
200 C# 75 150 C++ 25 50 2025 1
50 Fá# 25 12,5 Java 75 37,5 2024 3
100 Cobol 100 100 - 0 0 2024 4
80 C# 50 40 VB.NET 50 40 2024 2

Preciso criar uma tabela dinâmica para esta tabela, mas a tabela dinâmica deve conter os valores exclusivos das colunas "Primeiro idioma" e "Segundo idioma" como uma coluna e as pontuações somadas das colunas FLScore e SLScore.

Neste exemplo, o resultado seria algo como:

Resultado simplificado

Eu usei:

=UNIQUE(VSTACK(dtScore[First Language];dtScore[Second Language]))

O resultado está no intervalo M10:M16 (segunda imagem) E

=SUMIFS(dtScore[FLScore];dtScore[First Language];M10)+SUMIFS(dtScore[SLScore];dtScore[Second Language];M10)

para obter a soma total para cada idioma.

Mas preciso transformar os dados de forma que eu possa usá-los em uma tabela dinâmica que também possa filtrar por ano e trimestre.

Tenho pensado em usar as funções GROUPBY, FILTER, HSTACK, VSTACK e lambda para transformar os dados, mas não sei por onde começar e como obter o resultado em uma tabela dinâmica que atenda aos critérios mencionados.

Prefiro não usar VBA, apenas funções do Excel

Alguém pode indicar uma direção?

Atualização : Embora eu tenha usado a solução PQ, por ser a mais adequada às minhas necessidades, não parei de experimentar. Consegui usar o PIVOTBY para ter uma matriz por idioma, pontuação e anos como colunas.

A fórmula parece

=PIVOTBY(VSTACK(dtScore[First Language];dtScore[Second Language]);VSTACK(dtScore[Year];dtScore[Year]);VSTACK(dtScore[FLScore];dtScore[SLScore]);SUM)

Uma observação: vou usar VSTACK também para o parâmetro colunas. Embora a coluna seja a mesma (dtScore[Ano]). Se eu apenas inserir dtScore[Ano], a fórmula gera um erro #Value, indicando que há um número inconsistente de linhas.

O resultado do PIVOTBY

Resultado do PIVOTBY

excel

3 respostas

  1. Você pode usar GROUPBY()com VSTACK(). Try-

    =GROUPBY(VSTACK(B2:B6,E2:E6),VSTACK(D2:D6,G2:G6),SUM)

    Usando TRIMRANGEo recurso.

    =LET(arr,DROP(A.:.I,1),fn,CHOOSECOLS,GROUPBY(VSTACK(fn(arr,2),fn(arr,5)),VSTACK(fn(arr,4),fn(arr,7)),SUM))

    Dados de entrada:

    Primeira Língua FLPecentagee Pontuação FLS segunda língua. SLPercentagee SLScoreeyear EQuartee P
    Java 90 108 C# 10 12 2024 4
    C# 75 150 C++ 25 50 2025 1
    Fá# 25 12,5 Java 75 37,5 2024 3
    Cobol 100 100 - 0 0 2024 4
    C# 50 40 VB.NET 50 40 2024 2

    Saída da fórmula acima-

    Linguagem Pontuação
    - 0
    C# 202
    C++ 50
    Cobol 100
    Fá# 12,5
    Java 145,5
    VB.NET 40
    Total 550
    • 0
  2. Best Answer

    Eu abordaria criando um Power Query, mas tenho certeza de que outros fornecerão soluções semelhantes usando fórmulas do Excel:

    let
    // 1. Reference your original table. 
     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // 2. Create a subset for the First Language
    FLTable = Table.SelectColumns(Source, {"First Language", "Fl Score"}),
    //    Rename columns to a generic [Language] and [Score]
    FLRenamed = Table.RenameColumns(
        FLTable,
        {
            {"First Language", "Language"}, 
            {"Fl Score", "Score"}
        }
    ),

    // 3. Create a subset for the Second Language
    SLTable = Table.SelectColumns(Source, {"Second Language", "SL Score"}),
    //    Rename columns to the same [Language] and [Score] for easy append
    SLRenamed = Table.RenameColumns(
        SLTable,
        {
            {"Second Language", "Language"}, 
            {"SL Score", "Score"}
        }
    ),

    // 4. Append the two subtables 
    Appended = Table.Combine({FLRenamed, SLRenamed}),

    // 5. Group by Language, summing up the scores
    Grouped = Table.Group(
        Appended, 
        {"Language"}, 
        {
            {"Total Score", each List.Sum([Score]), type nullable number}
        }
    )

in
    Grouped

    Ou menos prolixo e mais compacto:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Combined = Table.Combine({
        Table.RenameColumns(Table.SelectColumns(Source, {"First Language", "Fl Score"}), {{"First Language", "Language"}, {"Fl Score", "Score"}}),
        Table.RenameColumns(Table.SelectColumns(Source, {"Second Language", "SL Score"}), {{"Second Language", "Language"}, {"SL Score", "Score"}})
    }),
    Result = Table.SelectRows(
        Table.Group(Combined, {"Language"}, {{"Total Score", each List.Sum([Score]), type nullable number}}),
        each [Language] <> null and [Language] <> ""
    )
in
    Result
    • 0

  3. Há um argumento de filtro em GROUPBY, então se você usar duas células para selecionar o ano e o trimestre pelos quais deseja filtrar (usando validação de dados), o seguinte lhe dará o que você deseja:

    =GROUPBY(VSTACK(dtScore[First Language],dtScore[Second Language]),VSTACK(dtScore[FLScore],dtScore[SLScore]),SUM,0,0,-2,(dtScore[Year]=year)*(dtScore[Quarter]=quarter))

    onde ano se refere ao ano desejado e trimestre se refere ao trimestre (adicione as funções IF necessárias no filtro se desejar a opção de não filtrar)

    • 0

relate perguntas