Como usar a Função Pivotar para Substituir a Tabela Dinâmica

Neste artigo você aprenderá como usar a função PIVOTAR no Excel passo a passo para agrupar, resumir e analisar dados utilizando uma única fórmula. Veremos exemplos práticos de vendas, conciliação de dados, orçamento, filtros dinâmicos e detalhamento das informações.

A função PIVOTAR permite criar estruturas semelhantes a uma Tabela Dinâmica diretamente por meio de fórmulas, trazendo novas possibilidades para relatórios, análises financeiras e dashboards no Excel.

Ao final do artigo você também poderá fazer o download gratuito da planilha com todos os exemplos para acompanhar as fórmulas e adaptar os modelos para seus próprios projetos.

Introdução

Resumir uma grande quantidade de dados é uma das tarefas mais comuns no Excel. Em uma base com milhares de registros, normalmente não queremos analisar cada linha individualmente, mas responder perguntas como: quanto vendemos por vendedor? Qual foi o faturamento de cada região? Quanto foi gasto em cada mês? Qual é a diferença entre o valor orçado e o realizado?

Para responder a essas perguntas podemos utilizar recursos como Tabela Dinâmica, SOMASES, CONT.SES, FILTRO e outras funções do Excel.

A função PIVOTAR traz uma nova possibilidade: criar resumos semelhantes aos de uma Tabela Dinâmica utilizando diretamente uma fórmula.

Com ela podemos determinar quais informações serão apresentadas nas linhas, quais serão apresentadas nas colunas e qual cálculo deverá ser realizado no cruzamento dessas informações.

Por exemplo, imagine uma base contendo Data, Vendedor, Produto, Região e Valor. Podemos utilizar a função PIVOTAR para colocar os vendedores nas linhas, os anos nas colunas e calcular automaticamente o total ou a média das vendas.

O resultado é uma matriz dinâmica. Isso significa que, dependendo dos dados utilizados, a área retornada pela fórmula pode aumentar ou diminuir automaticamente.

Outra grande vantagem é poder combinar PIVOTAR com outras funções do Excel. Podemos utilizar SE, TEXTO, FILTRO, EMPILHARH, INDIRETO e diversas outras funções para criar relatórios personalizados.

Neste artigo veremos exemplos utilizando a função PIVOTAR para resumir vendas, aplicar filtros, realizar conciliação de dados, comparar orçamento e realizado e detalhar informações.

O que é a Função PIVOTAR no Excel?

A função PIVOTAR é uma função do Excel que permite agrupar e resumir informações em linhas e colunas utilizando uma fórmula.

Seu funcionamento lembra bastante uma Tabela Dinâmica. Podemos escolher uma ou mais informações para formar as linhas, definir outra dimensão para as colunas e depois informar qual campo deverá ser calculado no cruzamento dessas dimensões.

Imagine uma base de vendas com as colunas:

• Data;

• Vendedor;

• Produto;

• Região;

• Quantidade;

• Valor.

Com PIVOTAR podemos criar, por exemplo, um relatório em que cada vendedor aparece em uma linha e cada ano aparece em uma coluna. No cruzamento podemos calcular a soma ou a média das vendas.

Podemos pensar na estrutura de forma bastante simples:

Linhas + Colunas + Valores + Cálculo = Resumo dos dados

A principal diferença em relação à Tabela Dinâmica tradicional é que o resultado é produzido por uma fórmula.

Isso permite utilizar referências de células, condições, filtros e outras funções diretamente na construção do relatório.

Por esse motivo, PIVOTAR pode ser muito interessante para quem trabalha com dashboards, relatórios financeiros, conciliações, análises comerciais e modelos automatizados.

Como Funciona a Função PIVOTAR no Excel

A função PIVOTAR recebe os campos que deverão ser agrupados e aplica uma função de agregação sobre os valores.

Por exemplo, podemos agrupar os dados por vendedor nas linhas, por ano nas colunas e utilizar SOMA para calcular o total vendido.

Sintaxe:

=PIVOTAR(campos_linha;campos_coluna;valores;função;[cabeçalhos_campos];[profundidade_total_linha];[ordem_classificação_linha];[profundidade_total_coluna];[ordem_classificação_coluna];[matriz_filtro];[relativo_a])

Apesar de a sintaxe parecer extensa inicialmente, os primeiros argumentos são os mais importantes. Os demais servem para controlar detalhes do resultado, como cabeçalhos, totais, classificação e filtros.

campos_linha

O argumento campos_linha determina quais informações serão utilizadas para agrupar as linhas do relatório.

Em uma base comercial, por exemplo, podemos utilizar Vendedor. Nesse caso, cada vendedor diferente poderá gerar uma linha no resultado.

Também podemos trabalhar com mais de um campo, criando níveis de agrupamento. Podemos ter, por exemplo, Região e Vendedor nas linhas.

campos_coluna

O argumento campos_coluna determina como as informações serão agrupadas horizontalmente.

Podemos utilizar Ano, Mês, Categoria ou qualquer outro campo adequado para a análise.

Um exemplo bastante comum seria colocar Vendedor nas linhas e Ano nas colunas. Dessa forma, conseguimos analisar rapidamente o desempenho de cada vendedor ao longo dos anos.

valores

O argumento valores determina quais números serão utilizados no cálculo.

Se estivermos analisando faturamento, podemos informar a coluna Valor. Para analisar quantidades, podemos utilizar a coluna Quantidade.

Esse argumento trabalha em conjunto com a função de agregação definida no argumento seguinte.

função

O argumento função determina como os valores deverão ser resumidos.

Podemos utilizar, por exemplo, SOMA para totalizar os valores ou MÉDIA para calcular a média dos registros de cada agrupamento.

Essa escolha depende da pergunta que o relatório deverá responder. Para faturamento, normalmente utilizamos SOMA. Para analisar o valor médio das vendas, podemos utilizar MÉDIA.

cabeçalhos_campos

Esse argumento permite controlar como os cabeçalhos dos campos serão tratados e apresentados no resultado.

Ele é útil principalmente quando queremos definir de forma mais precisa se os dados de origem possuem cabeçalhos e como essas informações deverão aparecer na matriz gerada.

profundidade_total_linha

Esse argumento controla a apresentação dos totais e subtotais nas linhas.

Quando trabalhamos com mais de um nível de agrupamento, podemos utilizar os subtotais para facilitar a análise. Por exemplo, se tivermos Região e Vendedor nas linhas, podemos apresentar o subtotal de cada região.

ordem_classificação_linha

Permite controlar a ordem em que as linhas serão apresentadas.

Dependendo da análise, podemos querer classificar alfabeticamente ou organizar o relatório com base nos valores calculados.

profundidade_total_coluna

Funciona de maneira semelhante ao controle de totais das linhas, mas aplicado às colunas do relatório.

Podemos utilizar esse argumento quando precisamos apresentar totais ou subtotais para os agrupamentos definidos nas colunas.

ordem_classificação_coluna

Permite definir como as colunas deverão ser classificadas dentro do resultado da função.

Esse argumento pode ser útil quando temos diversos grupos e queremos controlar a sequência em que serão apresentados.

matriz_filtro

O argumento matriz_filtro é um dos recursos mais interessantes da função PIVOTAR.

Ele permite definir quais registros da base serão considerados no cálculo. Podemos, por exemplo, criar um relatório que apresente somente as vendas de determinado vendedor, ano, produto ou região.

Também podemos combinar várias condições, tornando o relatório controlado por células de seleção.

Como entender a sintaxe da função PIVOTAR de forma simples

Apesar de possuir vários argumentos, você não precisa utilizar todos eles para começar.

Os quatro argumentos principais são:

• campos_linha: o que você deseja colocar nas linhas;

• campos_coluna: o que você deseja colocar nas colunas;

• valores: quais números deseja analisar;

• função: qual cálculo deseja realizar.

Portanto, para entender a lógica da PIVOTAR, pense inicialmente desta forma:

=PIVOTAR(Linhas;Colunas;Valores;Cálculo)

Imagine que temos uma tabela chamada Vendas. Queremos vendedores nas linhas, anos nas colunas e o total das vendas nos valores.

A lógica seria:

=PIVOTAR(Vendedor;Ano;Valor;SOMA)

Depois de entender essa estrutura básica, podemos começar a utilizar os argumentos opcionais para controlar filtros, cabeçalhos, classificação, totais e subtotais.

Como preparar os dados para usar a função PIVOTAR

Antes de utilizar PIVOTAR, é importante que a base esteja organizada corretamente.

O ideal é trabalhar com uma estrutura tabular em que cada coluna possua um tipo de informação e cada linha represente um registro.

Por exemplo:

• Data;

• Número do Pedido;

• Cliente;

• Vendedor;

• Produto;

• Região;

• Quantidade;

• Valor.

Evite células mescladas, subtotais inseridos no meio da base, linhas completamente vazias entre os registros e informações diferentes misturadas na mesma coluna.

Também recomendo transformar o intervalo em uma Tabela do Excel. Para isso, selecione os dados e pressione CTRL+T.

As Tabelas do Excel facilitam bastante a criação das fórmulas porque podemos utilizar referências estruturadas, como vemos nos exemplos deste artigo.

Além disso, quando novos registros são adicionados à tabela, as referências ficam mais fáceis de manter do que quando trabalhamos somente com intervalos fixos.

Uma base organizada também facilita a utilização de Power Query, Tabelas Dinâmicas, gráficos e outras ferramentas de análise.

Exemplo de Criação de Tabela com a Função PIVOTAR

No exemplo abaixo temos uma tabela que iremos resumir utilizando a função PIVOTAR.

A função aplicada foi:

=PIVOTAR(Table1[[#Tudo];[IMAGEM]:[REGIÃO]];Table1[[#Tudo];[ANO]];Table1[[#Tudo];[VALOR]];MÉDIA;3;2;;;;(Table1[[#Tudo];[VENDEDOR]]=J5)*(Table1[[#Tudo];[ANO]]=J4))

Como resultado temos a seguinte tabela:

Perceba que na função temos as informações agrupadas nas linhas, o ano nas colunas e os valores sendo resumidos pela função MÉDIA.

Nesse exemplo também estamos utilizando o argumento de filtro da função. Com isso, o resultado considera somente os registros que atendem aos critérios definidos nas células J5 e J4.

Essa é uma característica muito útil da PIVOTAR. Em vez de criar uma fórmula fixa para cada vendedor ou período, podemos utilizar células como critérios e deixar o usuário selecionar o que deseja analisar.

Quando J5 ou J4 forem alteradas, a condição utilizada no filtro também será modificada e o resultado poderá ser recalculado.

Esse conceito pode ser aplicado em dashboards e relatórios gerenciais. Podemos criar listas suspensas para vendedor, ano, região ou produto e utilizar essas escolhas como critérios da fórmula.

Como usar filtros na função PIVOTAR

O argumento de filtro permite determinar quais registros da base deverão participar do cálculo.

No exemplo anterior utilizamos duas condições:

(Table1[[#Tudo];[VENDEDOR]]=J5)*(Table1[[#Tudo];[ANO]]=J4)

A primeira condição verifica se o vendedor da linha é igual ao vendedor selecionado na célula J5.

A segunda verifica se o ano corresponde ao valor informado em J4.

Quando multiplicamos as duas condições, estamos criando uma lógica semelhante ao operador E. Portanto, o registro precisa atender às duas condições para fazer parte do resultado.

Podemos interpretar a expressão desta forma:

VENDEDOR = J5 E ANO = J4

Esse conceito é extremamente útil porque permite construir filtros sem alterar manualmente a fórmula sempre que quisermos analisar outro cenário.

Podemos utilizar o mesmo princípio para criar filtros por:

• Vendedor;

• Ano;

• Produto;

• Cliente;

• Região;

• Departamento;

• Categoria;

• Status.

Com isso, a PIVOTAR deixa de ser apenas uma fórmula de resumo e passa a fazer parte de uma estrutura de relatório interativo.

Como criar relatórios dinâmicos com PIVOTAR

Uma aplicação interessante da função é criar relatórios que mudam de acordo com as escolhas realizadas pelo usuário.

Podemos criar, por exemplo, uma célula contendo uma lista suspensa com os vendedores da empresa. Outra célula pode permitir escolher o ano.

Essas duas células podem ser utilizadas diretamente no argumento de filtro da PIVOTAR.

Quando o usuário selecionar outro vendedor, a fórmula será recalculada e apresentará somente os dados correspondentes à nova escolha.

Podemos utilizar o resultado da matriz para alimentar gráficos. Dessa forma, ao alterar o filtro, os dados da PIVOTAR mudam e o gráfico também pode acompanhar a nova análise.

Esse tipo de estrutura é muito útil para dashboards baseados em fórmulas, principalmente quando queremos criar uma área de análise sem utilizar uma Tabela Dinâmica tradicional.

Como Conciliar Dados com a Função PIVOTAR no Excel

Outra aplicação bastante interessante da função PIVOTAR é a conciliação de dados no Excel.

A conciliação é utilizada quando precisamos comparar informações provenientes de duas ou mais fontes e identificar diferenças.

Imagine que temos valores registrados no sistema financeiro e no sistema contábil. Precisamos verificar se as mesmas notas fiscais e valores aparecem nos dois sistemas.

Fazer essa conferência manualmente pode ser trabalhoso, principalmente quando existem centenas ou milhares de registros.

No exemplo abaixo temos uma lista com o número da nota fiscal, o valor e a origem dos dados.

Para realizar a conciliação copiamos todos os dados para uma tabela única, como temos abaixo:

E aplicamos a seguinte fórmula:

=PIVOTAR(Table2[[#Tudo];[Nota]];Table2[[#Tudo];[Origem]];SE(Table2[[#Tudo];[Origem]]="Contábil";-Table2[[#Tudo];[Valor]];Table2[[#Tudo];[Valor]]);SOMA;;;)

Nessa fórmula utilizamos a nota fiscal como agrupamento das linhas e a origem dos dados como agrupamento das colunas.

Nos valores utilizamos a função SE para verificar a origem de cada registro.

Quando a origem é igual a "Contábil", multiplicamos o valor por -1. Caso contrário, mantemos o valor positivo.

Depois utilizamos SOMA como função de agregação.

A lógica permite colocar os valores das duas origens em lados opostos da operação. Quando os valores correspondem corretamente, a diferença esperada será zero.

Temos com isso os dados dispostos como vemos abaixo, com uma coluna contendo a chave e as demais informações utilizadas na conciliação.

Após isso clique no filtro e desmarque os itens com diferença 0:

Como resultado temos a seguinte lista somente com as notas fiscais com diferenças:

Essa estrutura facilita muito a conferência. Em vez de analisar todos os registros, podemos direcionar nossa atenção somente para os documentos que possuem divergências.

Por que a função PIVOTAR é útil para conciliação?

A principal vantagem nesse tipo de aplicação é conseguir transformar uma lista de registros em uma estrutura organizada por chave e origem.

Em uma conciliação bancária, por exemplo, poderíamos utilizar como chave um número de documento, identificador da transação ou outra informação existente nas duas fontes.

O mesmo conceito pode ser utilizado em diferentes situações:

• Financeiro versus Contábil;

• Pedidos versus Notas Fiscais;

• Estoque físico versus sistema;

• Extrato bancário versus lançamentos financeiros;

• Comissões calculadas versus comissões pagas;

• Dados importados de dois sistemas diferentes.

O ponto mais importante é possuir uma chave que permita relacionar as informações que deverão ser comparadas.

Exemplo de Orçamento com a Função PIVOTAR

Outra aplicação prática é utilizar a função PIVOTAR para criar um relatório de orçamento no Excel.

Neste exemplo temos os valores orçados e realizados de cada despesa de uma empresa, separados por departamento e data.

No nosso exemplo aplicamos a seguinte fórmula:

=SE($G$2="Gasto";PIVOTAR(Table3[[#Tudo];[Despesas]];TEXTO(Table3[[#Tudo];[Data]];"MM/AA");Table3[[#Tudo];[Orçado]]-Table3[[#Tudo];[Realizado]];SOMA;1);
SE($G$2="Percentual";PIVOTAR(Table3[[#Tudo];[Despesas]];TEXTO(Table3[[#Tudo];[Data]];"MM/AA");1-(Table3[[#Tudo];[Realizado]]/Table3[[#Tudo];[Orçado]]);SOMA;1);
PIVOTAR(Table3[[#Tudo];[Despesas]];TEXTO(Table3[[#Tudo];[Data]];"MM/AA");INDIRETO("Table3[[#Tudo];["&G2&"]]");SOMA;1)))

Nesse caso, a célula G2 controla qual informação será apresentada pelo relatório.

A fórmula começa utilizando a função SE para verificar a opção selecionada.

Se G2 for igual a "Gasto", utilizamos a diferença entre Orçado e Realizado.

Se G2 for igual a "Percentual", calculamos a relação entre os valores.

Para as demais opções, utilizamos INDIRETO para determinar qual coluna da tabela deverá fornecer os valores.

Como resultado temos a tabela seguinte, separada por valores GASTO, ORÇADO, PERCENTUAL e REALIZADO:

Esse exemplo demonstra que não precisamos utilizar PIVOTAR isoladamente. Podemos colocar a função dentro de estruturas maiores e fazer com que o próprio usuário escolha qual indicador deseja analisar.

Como analisar Orçado x Realizado com PIVOTAR

A análise de Orçado x Realizado é muito utilizada em controles financeiros e gerenciais.

O valor orçado representa aquilo que estava previsto para determinado período, departamento ou categoria. O realizado representa aquilo que efetivamente aconteceu.

A diferença entre esses valores ajuda a identificar desvios do planejamento.

Utilizando PIVOTAR, podemos organizar as despesas nas linhas e os meses nas colunas. Isso cria uma visão bastante simples para comparar o comportamento das despesas ao longo do tempo.

Podemos ainda utilizar formatação condicional para destacar valores negativos, percentuais fora da meta ou despesas que ultrapassaram determinado limite.

O resultado também pode servir como base para gráficos e dashboards financeiros.

Como detalhar os valores da PIVOTAR com a função FILTRO

Um relatório resumido é excelente para análise, mas em alguns momentos precisamos descobrir quais registros formaram determinado resultado.

Para isso podemos combinar a PIVOTAR com funções de matrizes dinâmicas.

No exemplo utilizamos a seguinte fórmula para retornar os detalhes relacionados ao período selecionado:

=FILTRO(EMPILHARH(Table3[#Tudo];Table3[[#Tudo];[Orçado]]-Table3[[#Tudo];[Realizado]]);(TEXTO(Table3[[#Tudo];[Data]];"MM/AA")=INDIRETO(ENDEREÇO(5;COL(INDIRETO(CÉL("endereço"))))));"")

Nesse exemplo utilizamos outras funções em conjunto com a PIVOTAR.

A função FILTRO retorna somente os registros que atendem ao critério informado.

A função EMPILHARH permite combinar matrizes horizontalmente, acrescentando informações ao resultado.

Também utilizamos TEXTO, INDIRETO, ENDEREÇO, COL e CÉL para identificar dinamicamente a posição e o período que deverá ser detalhado.

Com isso conseguimos trabalhar em dois níveis:

Resumo → PIVOTAR

Detalhamento → FILTRO

Essa combinação é muito útil em relatórios gerenciais. Primeiro o usuário identifica uma informação importante no resumo e depois consulta os registros que deram origem ao resultado.

Como combinar PIVOTAR com outras funções do Excel

Uma das grandes vantagens de PIVOTAR é que estamos trabalhando com uma função do Excel. Portanto, ela pode participar de fórmulas maiores e trabalhar em conjunto com outras funções.

Nos exemplos deste artigo utilizamos diversas combinações.

PIVOTAR com SE

A função SE pode controlar qual versão do relatório deverá ser apresentada.

No exemplo de orçamento, verificamos o conteúdo da célula G2 e alteramos o cálculo conforme a opção escolhida.

Isso permite criar relatórios com diferentes visões utilizando a mesma área da planilha.

PIVOTAR com TEXTO

A função TEXTO pode ser utilizada para transformar datas em períodos utilizados no agrupamento.

No exemplo utilizamos:

TEXTO(Table3[[#Tudo];[Data]];"MM/AA")

Dessa forma, conseguimos trabalhar com mês e ano no relatório.

PIVOTAR com INDIRETO

INDIRETO pode ajudar a tornar referências variáveis.

No exemplo de orçamento, utilizamos o conteúdo de uma célula para determinar qual coluna da tabela será considerada pela fórmula.

Esse recurso deve ser utilizado com cuidado, mas permite criar relatórios bastante flexíveis.

PIVOTAR com FILTRO

Enquanto PIVOTAR apresenta uma visão resumida, FILTRO pode ser utilizado para apresentar os registros detalhados.

As duas funções juntas permitem criar uma experiência semelhante a um relatório com visão analítica e consulta dos dados de origem.

PIVOTAR com EMPILHARH

EMPILHARH permite acrescentar matrizes lado a lado.

No exemplo de orçamento utilizamos essa função para combinar os dados originais com uma informação calculada, aumentando o detalhamento apresentado ao usuário.

PIVOTAR ou Tabela Dinâmica: qual utilizar?

A função PIVOTAR e a Tabela Dinâmica possuem objetivos semelhantes em algumas situações, mas funcionam de maneiras diferentes.

A Tabela Dinâmica continua sendo uma excelente ferramenta para explorar dados rapidamente. Ela permite arrastar campos, aplicar filtros, alterar agrupamentos e reorganizar o relatório através de uma interface visual.

Isso é muito útil principalmente quando estamos explorando uma base e ainda não sabemos exatamente qual análise desejamos realizar.

Já a PIVOTAR é interessante quando queremos que o resumo faça parte de uma estrutura baseada em fórmulas.

Podemos utilizar referências em células, criar condições, combinar funções e integrar o resultado com outras áreas da planilha.

Por exemplo, se temos um dashboard completamente baseado em fórmulas e queremos que determinado relatório mude conforme uma lista suspensa, PIVOTAR pode ser uma excelente alternativa.

Já se queremos explorar rapidamente diferentes dimensões de uma grande base, a Tabela Dinâmica pode ser mais prática.

Portanto, não devemos pensar na PIVOTAR como uma substituição obrigatória da Tabela Dinâmica.

As duas ferramentas podem inclusive coexistir na mesma pasta de trabalho. A melhor escolha dependerá da estrutura do projeto e da experiência que desejamos entregar ao usuário.

Vantagens da função PIVOTAR no Excel

A função PIVOTAR possui várias características interessantes para quem trabalha com análise de dados.

• Resultado por fórmula: o resumo faz parte diretamente da estrutura de fórmulas da planilha.

• Resultado dinâmico: a matriz retornada pode se adaptar aos dados.

• Filtros: podemos determinar quais registros deverão participar do cálculo.

• Integração: o resultado pode ser combinado com outras funções do Excel.

• Automação: referências em células podem controlar o relatório.

• Dashboards: os resultados podem servir como base para gráficos e indicadores.

• Conciliação: podemos organizar informações provenientes de diferentes origens.

• Flexibilidade: é possível criar diferentes estruturas conforme a necessidade do projeto.

Cuidados ao utilizar a função PIVOTAR

Apesar das vantagens, existem alguns cuidados importantes.

Primeiro, organize corretamente a base. Fórmulas avançadas não compensam uma estrutura de dados mal organizada.

Também é importante verificar se as colunas utilizadas nos valores possuem o tipo de dado esperado. Se uma coluna deveria conter números, por exemplo, verifique se não existem valores armazenados como texto.

Outro cuidado é reservar espaço suficiente para a matriz dinâmica retornada pela função. Como o resultado pode crescer, outras informações existentes na área de saída podem impedir a expansão.

Ao utilizar filtros, confira se os intervalos envolvidos possuem dimensões compatíveis. O critério precisa corresponder aos registros utilizados na análise.

Em fórmulas maiores, procure construir a solução por etapas. Primeiro valide uma PIVOTAR simples. Depois acrescente filtros e outras funções.

Essa abordagem facilita bastante a identificação de erros.

Erros comuns ao usar a função PIVOTAR

Um erro comum é tentar criar uma fórmula muito complexa logo no início.

Comece testando os quatro elementos principais:

Linhas → Colunas → Valores → Cálculo

Depois que o resultado básico estiver correto, adicione filtros, totais e demais argumentos.

Outro problema comum é selecionar intervalos incorretos ou trabalhar com dados inconsistentes. Verifique sempre se as colunas utilizadas possuem registros correspondentes.

Também confira se existem células ocupando a área onde a matriz precisa ser expandida.

Quando utilizar referências estruturadas, observe se está trabalhando com a tabela e as colunas corretas.

Por fim, ao copiar fórmulas de exemplos encontrados na internet, verifique se elas estão utilizando os nomes das funções em português do Brasil e o separador ponto e vírgula, conforme a configuração do seu Excel.

Principais aplicações da função PIVOTAR no Excel

Como vimos nos exemplos, a função PIVOTAR pode ser utilizada em diferentes tipos de análise.

• Resumo de vendas: agrupar vendas por vendedor, produto, região, mês ou ano.

• Conciliação: comparar valores provenientes de sistemas ou fontes diferentes.

• Orçamento: comparar valores orçados e realizados por período.

• Dashboards: gerar matrizes dinâmicas que alimentam indicadores e gráficos.

• Financeiro: resumir receitas e despesas por categoria e período.

• Relatórios gerenciais: criar estruturas que mudam conforme critérios selecionados em outras células.

• Controle de estoque: resumir quantidades por produto, grupo, depósito ou período.

• Análise comercial: comparar vendedores, clientes, regiões e produtos.

• Análise por período: organizar informações por mês, trimestre ou ano.

Por ser uma função, o resultado também pode servir como entrada para outros cálculos, gráficos e análises, aumentando bastante as possibilidades de utilização.

Como usar PIVOTAR em Dashboards no Excel

A função PIVOTAR também pode ser bastante útil na construção de dashboards.

Imagine que o usuário possua uma lista suspensa para selecionar um vendedor. Outra lista permite selecionar o ano.

Essas células podem ser utilizadas como critérios do argumento de filtro.

A PIVOTAR gera então uma matriz contendo somente as informações correspondentes às escolhas realizadas.

Essa matriz pode alimentar um gráfico ou outra área do dashboard.

Ao mudar a seleção, a fórmula é recalculada e o relatório pode apresentar uma nova visão.

Podemos ainda combinar PIVOTAR com funções como FILTRO, ÚNICO e CLASSIFICAR em outras áreas do projeto para criar listas e análises completamente dinâmicas.

Isso permite desenvolver dashboards modernos utilizando os recursos de matrizes dinâmicas do Excel.

Quando vale a pena usar a função PIVOTAR?

PIVOTAR é especialmente interessante quando você precisa de um resumo estruturado que faça parte de um modelo baseado em fórmulas.

Ela pode ser uma boa escolha quando o resultado precisa responder automaticamente às alterações realizadas em células de controle.

Também é interessante quando queremos combinar o resumo com outras funções e utilizar o resultado em gráficos, dashboards ou outras fórmulas.

Por outro lado, quando a necessidade é simplesmente explorar os dados rapidamente, alternando campos e filtros de forma manual, a Tabela Dinâmica continua sendo uma ferramenta extremamente prática.

O mais importante é conhecer as duas possibilidades e escolher a ferramenta mais adequada para cada situação.

Download Planilha Função PIVOTAR Excel

Faça o download gratuito da planilha com os exemplos da função PIVOTAR no Excel apresentados neste artigo.

No arquivo você poderá acompanhar as fórmulas utilizadas nos exemplos de resumo de dados, conciliação e orçamento e adaptar os modelos para suas próprias necessidades.

Recomendo que você altere os critérios, valores e campos utilizados nas fórmulas para entender melhor o comportamento da função.

Depois, utilize os mesmos conceitos em uma base própria e experimente criar novos agrupamentos, filtros e análises.

Clique no botão abaixo para realizar o download do arquivo de exemplo:

Perguntas Frequentes sobre a Função PIVOTAR no Excel

1. O que é a função PIVOTAR no Excel?

A função PIVOTAR permite agrupar e resumir dados em linhas e colunas utilizando uma fórmula. Podemos definir os campos de agrupamento, os valores analisados e a função utilizada para realizar o cálculo.

2. Para que serve a função PIVOTAR?

A função pode ser utilizada para criar resumos de vendas, relatórios financeiros, conciliações, análises de orçamento, relatórios gerenciais e matrizes utilizadas em dashboards.

3. Qual é a diferença entre PIVOTAR e Tabela Dinâmica?

A Tabela Dinâmica possui uma interface visual para organização dos campos e exploração dos dados. A função PIVOTAR gera o resumo através de uma fórmula, permitindo integrar o resultado com outras funções e referências da planilha.

4. A função PIVOTAR substitui a Tabela Dinâmica?

Não necessariamente. As duas ferramentas possuem vantagens diferentes. PIVOTAR pode ser interessante em modelos baseados em fórmulas, enquanto a Tabela Dinâmica continua sendo excelente para exploração visual dos dados.

5. É possível filtrar dados dentro da função PIVOTAR?

Sim. A função possui um argumento de filtro que permite determinar quais registros da base deverão ser considerados no cálculo.

6. É possível utilizar mais de um critério na PIVOTAR?

Sim. Podemos combinar condições dentro do argumento de filtro. Por exemplo, podemos determinar que o vendedor seja igual ao selecionado em uma célula e que o ano seja igual ao informado em outra.

7. Quais cálculos podem ser realizados com PIVOTAR?

A função trabalha com funções de agregação. Nos exemplos deste artigo utilizamos SOMA para totalizar valores e MÉDIA para calcular médias dentro dos agrupamentos.

8. Posso combinar PIVOTAR com outras funções do Excel?

Sim. Essa é uma das principais vantagens da função. Nos exemplos deste artigo utilizamos PIVOTAR em conjunto com SE, SOMA, MÉDIA, TEXTO, FILTRO, EMPILHARH, INDIRETO, ENDEREÇO, COL e CÉL.

9. É possível usar PIVOTAR para conciliação de dados?

Sim. Podemos utilizar uma informação em comum, como número da nota fiscal, como chave e organizar valores provenientes de fontes diferentes. Dessa forma, conseguimos identificar com mais facilidade registros que possuem divergências.

10. Posso utilizar PIVOTAR para analisar Orçado x Realizado?

Sim. Podemos colocar as despesas nas linhas, os períodos nas colunas e calcular valores orçados, realizados, diferenças e outros indicadores utilizados na análise financeira.

11. Posso utilizar PIVOTAR para criar dashboards?

Sim. O resultado da função pode servir como base para gráficos e outras análises. Também podemos utilizar células como critérios para criar dashboards que mudam conforme a seleção realizada pelo usuário.

12. É possível utilizar PIVOTAR com datas?

Sim. Nos exemplos deste artigo utilizamos a função TEXTO para transformar datas em períodos no formato MM/AA e utilizar esses valores na organização das colunas.

13. Como mostrar os registros que formaram um valor da PIVOTAR?

Podemos combinar o relatório resumido com a função FILTRO. Dessa forma, PIVOTAR apresenta o resumo e FILTRO pode retornar os registros detalhados correspondentes ao critério selecionado.

14. A função PIVOTAR pode trabalhar com Tabelas do Excel?

Sim. Os exemplos deste artigo utilizam referências estruturadas de Tabelas do Excel, o que ajuda a deixar as fórmulas mais fáceis de compreender e manter.

15. Por que transformar a base em Tabela antes de usar PIVOTAR?

Não é obrigatório em todas as situações, mas trabalhar com uma Tabela do Excel facilita a organização dos dados e permite utilizar referências estruturadas. Também facilita a inclusão de novos registros na base.

16. É possível usar PIVOTAR para comparar informações de dois sistemas?

Sim. O exemplo de conciliação deste artigo demonstra justamente esse conceito. Podemos reunir os dados das duas origens, identificar cada registro por uma chave e utilizar PIVOTAR para organizar a comparação.

17. Posso usar PIVOTAR junto com gráficos?

Sim. Como a função gera uma matriz de resultados, essas informações podem ser utilizadas em análises e estruturas que alimentam gráficos no Excel.

18. PIVOTAR pode ser usada em relatórios financeiros?

Sim. Podemos utilizar a função para resumir receitas, despesas, orçamento, realizado, categorias, centros de custo e períodos, dependendo da estrutura disponível na base.

19. O que fazer quando a fórmula PIVOTAR apresenta erro?

Comece simplificando a fórmula. Teste primeiro os campos de linha, coluna, valores e função de agregação. Depois adicione filtros e demais argumentos. Também verifique a estrutura dos dados e se existe espaço disponível para expansão do resultado.

20. Qual é a melhor forma de aprender a função PIVOTAR?

A melhor forma é começar com um exemplo simples. Utilize uma pequena base, escolha um campo para as linhas, outro para as colunas e um valor para SOMA. Depois acrescente filtros e outras funções gradualmente.

Conclusão

A função PIVOTAR no Excel amplia as possibilidades de criação de relatórios utilizando fórmulas, permitindo agrupar informações em linhas e colunas e realizar cálculos diretamente sobre os dados.

Ao entender sua estrutura básica, o funcionamento fica muito mais simples: definimos o que será apresentado nas linhas, o que será apresentado nas colunas, quais valores serão analisados e qual cálculo deverá ser realizado.

Neste artigo vimos exemplos de utilização da função para criar um resumo de vendas, aplicar filtros, realizar uma conciliação entre informações de origens diferentes e analisar valores orçados e realizados.

Também vimos que PIVOTAR pode ser combinada com outras funções do Excel, como SE, SOMA, MÉDIA, TEXTO, FILTRO, EMPILHARH e INDIRETO, permitindo criar estruturas muito mais dinâmicas e personalizadas.

Outro ponto importante é que a função não precisa substituir a Tabela Dinâmica. Cada recurso possui características próprias e podemos escolher a melhor solução conforme o objetivo do projeto.

Para relatórios baseados em fórmulas, dashboards dinâmicos, conciliações e análises controladas por células, PIVOTAR abre novas possibilidades interessantes dentro do Excel.

Faça o download da planilha disponibilizada neste artigo, acompanhe os exemplos e altere as dimensões, filtros e cálculos. Depois aplique a mesma lógica em suas próprias bases para criar relatórios de vendas, financeiro, orçamento, estoque ou qualquer outra análise que precise resumir informações.