Como utilizar a função PIVOTAR no Excel com artigo com imagens, vídeo-aula e download da planilha de exemplo gratuita.
Sintaxe da Função Pivotar Excel
A função Pivotar do Excel tem por finalidade redimensionar e deslocar o intervalos de células.
A sintaxe da função é: =PIVOTAR(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array])
- row_fields:
- Uma matriz ou intervalo orientado a colunas que contém os valores usados para agrupar linhas e gerar cabeçalhos de linha.A matriz ou intervalo pode conter várias colunas. Nesse caso, a saída terá vários níveis de grupo de linhas.
- col_fields: Uma matriz ou intervalo orientado a colunas que contém os valores usados para agrupar colunas e gerar cabeçalhos de colunas. A matriz ou intervalo pode conter várias colunas. Nesse caso, a saída terá vários níveis de grupo de colunas.
- values: Uma matriz ou intervalo orientado a colunas de dados a serem agregados. A matriz ou intervalo pode conter várias colunas. Nesse caso, a saída terá múltiplas agregações.
- function: Uma função lambda ou lambda eta reduzida (SUM, AVERAGE, COUNT, etc) que define como agregar os valores. Um vetor de lambdas pode ser fornecido. Nesse caso, a saída terá múltiplas agregações. A orientação do vetor determinará se eles serão dispostos em linhas ou colunas.
- field_headers: Um número que especifica se row_fields , col_fields e valores têm cabeçalhos e se os cabeçalhos dos campos devem ser retornados nos resultados. Os valores possíveis são:
- Ausente : Automático.
- 0 : Não
- 1 : Sim e não mostra
- 2 : Não, mas gera
- 3 : Sim e mostra
- Nota: Automático 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 tenham cabeçalhos. Os cabeçalhos dos campos serão mostrados se houver vários níveis de grupo de linhas ou colunas.
- row_total_depth: Determina se os cabeçalhos das linhas devem conter totais. Os valores possíveis são:
- Ausente : Automático: totais gerais e, sempre que possível, subtotais.
- 0 : Sem totais
- 1 : Totais gerais
- 2 : Totais gerais e subtotais
- -1 : Totais gerais no topo
- -2 : Totais gerais e subtotais no topo
- Nota: Para subtotais, row_fields deve ter pelo menos 2 colunas. Números maiores que 2 são suportados, desde que row_field tenha colunas suficientes.
- row_sort_order: Um número que indica como as linhas devem ser classificadas. Os números correspondem às colunas em row_fields seguidas pelas colunas em values . Se o número for negativo, as linhas serão classificadas em ordem decrescente/inversa. Um vetor de números pode ser fornecido ao classificar com base apenas em row_fields.
- col_total_depth: Determines whether the column headers should contain totals. The possible values are:
- Missing: Automatic: Grand totals and, where possible, subtotals.
- 0: No Totals
- 1: Grand Totals
- 2: Grand and Subtotals
- -1: Grand Totals at Top
- -2: Grand and Subtotals at Top
- Note: For subtotals, col_fields must have at least 2 columns. Numbers greater than 2 are supported provided col_field has sufficient columns.
- col_sort_order: Um número que indica como as linhas devem ser classificadas. Os números correspondem às colunas em col_fields seguidas pelas colunas em values . Se o número for negativo, as linhas serão classificadas em ordem decrescente/inversa. Um vetor de números pode ser fornecido ao classificar com base apenas em col_fields .
- filter_array: Uma matriz 1D de booleanos orientada a colunas que indica se a linha de dados correspondente deve ser considerada.Nota: O comprimento da matriz deve corresponder ao comprimento daqueles fornecidos para row_fields e col_fields.
Exemplo de Criação de Tabela Dinâmica com Fórmula no Excel
A função Pivotar do Excel tem por finalidade criar uma tabela dinâmica no Excel com uma função.
A nossa tabela de exemplo possui dados de vendas com a imagem, região, vendedor, ano, quantidade e valor.
No exemplo usamos a função =PIVOTAR(Table1[[#Tudo];[IMAGEM]:[REGIÃO]];Table1[[#Tudo];[ANO]];Table1[[#Tudo];[VALOR]];MÉDIA;3;2;;;;(Table1[[#Tudo];[ANO]]=J4)*(Table1[[#Tudo];[VENDEDOR]]=J5))
Perceba que com isso temos uma tabela criada com agrupamentos por imagem, região e também por meses, tudo criado de forma dinâmica.
No vídeo ao topo do artigo é demonstrado como criar do zero a função e explicado detalhes do funcionamento também.
Igualmente pode baixar o arquivo base de exemplo no botão ao final deste artigo.
Na fórmula aplicada note que também utilizamos a função FILTRO, e que nela filtramos o ano e o Vendedor.
Como resultado temos:
Conciliar Dados com Pivotar Excel
Também é possível realizar conciliação de dados no Excel utilizando apenas a função Pivotar do Excel. Que é o ato de identificarmos diferenças entre listas.
Na tabela contábil temos uma tabela com os dados contábeis que desejamos comparar com a tabela financeira e identificar a diferença de valores.
Abaixo temos também a tabela financeira.
Perceba que estas duas bases tem em comum a coluna de Nota, que é considerada a chave para comparação.
A coluna Valor, que é temos daquela nota no Financeiro e no Contábil.
E a coluna Origem é aonde temos a origem dos dados, que identifica qual a base original.
A função utilizada é
=PIVOTAR(Table2[[#Tudo];[Nota]];Table2[[#Tudo];[Origem]];SE(Table2[[#Tudo];[Origem]]=”Contábil”;-Table2[[#Tudo];[Valor]];Table2[[#Tudo];[Valor]]);SOMA)
Com isso temos o resultado abaixo, aonde é retornada uma lista com a comparação entre as notas.
Por fim temos a diferença das notas fiscais com a diferença entre o contábil e o financeiro utilizando o filtro no cabeçalho.
Download da Planilha Função Pivotar Excel
Para realizar o download desta planilha de exemplo clique no botão abaixo.