Stephan's questions

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

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:

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

Tenho uma string "2023-1" que representa o primeiro trimestre de 2023. No Excel, tenho uma fórmula:

=IF(AND($A2 => [value to calculate];$A2 <= "2023-1");$B2;0)

Agora [valor a ser calculado] deve ter o valor "2022-2" (sempre o 4º trimestre anterior ao segundo valor na fórmula (neste caso 2023-1).

Mais exemplos:

• 2023-4 deve resultar em 2023-1
• 2023-3 deve resultar em 2022-4
• 2023-2 deve resultar em 2022-3

e assim por diante.

Não quero usar VBA, apenas fórmulas comuns do Excel. Qual seria uma maneira de fazer isso?

Já experimentei usar TEXTBEFORE e TEXTAFTER e subtrair 4 da segunda parte da string. Mas ainda preciso fazer alguns ajustes, ou seja, 4-4 vira 0, mas deveria ser 1 (primeiro trimestre). 3-4 = -1, mas deveria ser o 4º trimestre do ano anterior.

Existem soluções melhores para resolver isso sem algumas instruções IF aninhadas?

no Excel, tenho algumas células contendo números separados por um travessão, como:

Eu uso uma fórmula como

=TRIM(TEKSTBEFORE(G30:G40;"/";1))