Criar relatórios do tipo Tabela Dinâmica, com as funções AGRUPARPOR e DINAMIZARPOR
Neste novo artigo, vamos analisar as novas funções disponíveis no Microsoft Excel: AGRUPARPOR [GROUPBY] e DINAMIZARPOR [PIVOTBY]. Estas funções, permitem criar relatórios “tipo” Tabela Dinâmica, com a vantagem de poderem ser atualizados instantaneamente.
A primeira função que vamos analisar: AGRUPARPOR [GROUPBY] - permite criar um resumo dos dados, agrupando um ou mais intervalos, utilizando as várias funções de agregação para calcular outro intervalo de dados.
A função DINAMIZARPOR [PIVOTBY] apresenta a mesma lógica, mas podemos incluir grupos de colunas juntamente com as linhas.
Dentro das funções de agregação existe, também, a nova função PERCENTAGEMDE [PERCENTOF]. É usada nestas funções para calcular os valores parciais sobre o total, em percentagem!
Não percas aqui o cenário de exemplo que partilho contigo, assim como o processo passo a passo. Se tiveres alguma dúvida, envia-nos a uma mensagem... ficamos à tua espera!
Faça aqui o download dos seus ficheiros de apoio!
Argumentos da função AGRUPARPOR
- Campo_de_linha: Argumento obrigatório, que consiste no intervalo usado para agrupar as linhas do relatório.
- Valores: Argumento obrigatório com os dados a serem agregados
- Função: Argumento obrigatório, onde selecionamos a função que vai agregar os dados. Aqui encontramos as tradicionais funções de agregação em conjunto com a nova função PERCENTAGEMDE [PERCENTOF]
Com os 3 argumentos obrigatórios construímos logo o relatório resumido.
Como podes verificar, o relatório apresenta os dados resumidos por marca.
Mostrar os cabeçalhos com a função AGRUPARPOR
O primeiro argumento opcional da função permite aplicar os cabeçalhos para o grupo. Basicamente temos opções que permitem definir se os campos de linha e os valores têm cabeçalhos e se os cabeçalhos de campo devem ser devolvidos nos resultados. Os valores possíveis são:
Em falta: Automático.
0: Não
1: Sim e não mostrar
2: Não, mas gerar
3: Sim e mostrar
Se não for colocado nenhum argumento, o valor é automático e assume que os dados contêm cabeçalhos com base no argumento de valores. Se o primeiro valor for texto e o segundo valor for um número, presume-se que os dados têm cabeçalhos. Os cabeçalhos de campos são apresentados se existirem vários níveis de grupo de linhas ou colunas.
Se optarmos pela opção 0, não mostramos os cabeçalhos e como podes ver o campo “Marca” é considerado como um valor “normal”.
Se colocarmos o valor 1 assumimos que temos cabeçalhos, mas com a opção de não mostrar.
Com a opção 2, não é definido o cabeçalho, mas mostra um valor para o mesmo.
A opção 3 é a opção mais recomendada caso pretendas ver os cabeçalhos.
Aplicar Totais e Subtotais no relatório
Este próximo argumento determina se os cabeçalhos de linha devem conter totais. Os valores possíveis a colocar são:
0: Sem Totais
1: Totais Gerais
2: Totais Gerais e Subtotais
-1: Totais Gerais no Topo
-2: Totais Gerais e Subtotais no Topo
Para poderes aplicar Subtotais, os campos têm de ter, pelo menos, 2 colunas e números superiores a 2 são suportados, desde que o campo tenha colunas suficientes. Tens ainda a opção omissa ou automática onde tens totais gerais e, sempre que possível, subtotais.
Com a opção [0] podes ver que não temos totais no relatório.
Com a opção [1] já são apresentados os totais gerais.
Com a opção [-1] podes ver os totais gerais no topo.
Ordenar as colunas no relatório
Neste argumento indicamos um número que representa a forma como as linhas devem ser ordenadas. Os números correspondem aos intervalos definidos pelo primeiro argumento [campo_de_linha] seguidos pelas colunas com os valores. Se o número for negativo, as linhas são ordenadas por ordem descendente/inversa.
Na imagem em baixo podes ver os dados ordenados pela 2ª coluna, dos valores, e ascendente.
Definir o filtro para o relatório
Os resultados apresentados podem ser filtrados, onde podes incluir ou excluir por um determinado fator os valores.
No exemplo acima, retiramos a marca “Joma”: A4:A30 <>M1
Definir a profundidade dos campos (Aplicar Totais e Subtotais)
Quando temos 2 grupos de colunas no argumento campo_de_linha podemos incluir subtotais com a opção 2: Totais Gerais e Subtotais
Na imagem em baixo podes ver um exemplo de aplicação de formatação condicional para destacar os subtotais.
Basta identificarmos quando a célula está em branco…
Função DINAMIZARPOR [PIVOTBY]
A função DINAMIZARPOR é semelhante em vários aspetos à função AGRUPARPOR, com a possibilidade de incluirmos mais argumentos que representam os campos de coluna, criando uma estrutura PIVOT com os dados representados em 2 dimensões.
Utilizando os mesmos argumentos, temos o argumento extra, que são os campos_de_coluna, que representam a matriz ou intervalo orientado para colunas que contém os valores que são utilizados para agrupar colunas e gerar cabeçalhos de coluna.
A partir daqui o conceito é o mesmo se pensares no campo adicional das COLUNAS, que pode ser utilizado para obter Totais e Subtotais e ser também ordenado.
No exemplo aplicamos os Cabeçalhos em ambos os campos.
E os totais gerais…
Assim como a ordenação dos campos…
Se ignorares o campo_de_coluna, podes ter um relatório semelhante à função AGRUPARPOR.
Com estas 2 novas funções podes então gerar relatórios resumidos, muito mais rapidamente, sem utilizares tabelas dinâmicas, e não te esqueças que os dados são atualizados também mais rapidamente.
Outros conteúdos relevantes:
Ordenar dados dinamicamente no Excel
Aprenda aqui como pode usar a nova função ORDENAR [SORT] para ordenar dados de forma dinâmica no Microsoft Excel.
Power Apps: Trabalhar com funções personalizadas
Neste novo vídeo, vamos ver alguns exemplos de funções personalizadas que podemos definir em Power Apps. Vamos lá?