Aprenda como usar a função PIVOTAR no Excel com exemplos práticos, fórmulas e dicas avançadas. Guia completo para iniciantes e profissionais com tabelas prontas e casos de uso reais.
A função PIVOTAR no Excel é uma das ferramentas mais poderosas para transformar e reorganizar dados de forma dinâmica. Esta função permite rotacionar linhas em colunas ou vice-versa, facilitando a análise de grandes volumes de informações e criando relatórios mais eficientes.
Neste guia completo, você aprenderá desde os conceitos básicos até técnicas avançadas para dominar completamente a função PIVOTAR, com exemplos práticos que podem ser aplicados imediatamente no seu trabalho.
Índice
- O Que é a Função PIVOTAR no Excel?
- Sintaxe da Função PIVOTAR
- Exemplos Práticos Para Usar a Função PIVOTAR
- Dicas Avançadas para Usar a Função PIVOTAR
- Fórmulas Essenciais Relacionadas
- Vantagens da Função PIVOTAR
- Desvantagens e Limitações
- Casos de Uso Profissionais
- Conclusão
- Perguntas Frequentes
- 1. A função PIVOTAR funciona com dados de texto e números misturados?
- 2. É possível pivotar apenas parte de uma tabela?
- 3. Como posso pivotar dados que se atualizam constantemente?
- 4. A função PIVOTAR preserva a formatação original dos dados?
- 5. Posso usar PIVOTAR com dados que contêm fórmulas?
- 6. Existe limite de tamanho para usar a função PIVOTAR?
- 7. Como posso pivotar dados que estão em planilhas diferentes?
- 8. A função PIVOTAR funciona com tabelas filtradas?
O Que é a Função PIVOTAR no Excel?
A função PIVOTAR (TRANSPOSE em inglês) é uma ferramenta que converte dados organizados em linhas para colunas, ou o contrário. É especialmente útil quando você precisa:
- Reorganizar dados para melhor visualização
- Preparar dados para gráficos específicos
- Converter listas verticais em horizontais
- Facilitar análises comparativas
Sintaxe da Função PIVOTAR
=PIVOTAR(matriz)Parâmetros:
- matriz: O intervalo de células que você deseja pivotar. E geralmente inclui os rótulos das linhas, rótulos das colunas e os valores a serem agregados. Certifique-se de que a matriz contenha todos os dados necessários para a tabela dinâmica que você deseja criar.
Exemplos Práticos Para Usar a Função PIVOTAR
Não é tão difícil usar a Função PIVOTAR, quando você entende a lógica dela. Espero que eu consiga lhe mostrar da forma mais fácil!
Exemplo 1: Pivotando Lista de Vendas do Mês de Setembro
Nesta tabela, mostramos os exemplos sobre determinadas perguntas. Por exemplo:
- Qual foi o valor total das vendas em setembro?
- Qual vendedor teve o melhor desempenho?
- Qual produto foi mais vendido em Setembro?
| Data | Categoria | Produto | QTDE | Preço Uni. | Valor Total | Vendedor | Estado |
|---|---|---|---|---|---|---|---|
| 10/set | Sementes | Milho Kg | 100 | R$ 2,00 | R$ 200,00 | João | SP |
| 10/set | Frutas | Laranja Kg | 130 | R$ 2,50 | R$ 325,00 | Maria | MG |
| 10/set | Legumes | Beterraba Kg | 120 | R$ 5,00 | R$ 600,00 | Sara | RJ |
| 11/set | Carnes | Frango Congelado Kg | 100 | R$ 20,00 | R$ 2.000,00 | Maria | PR |
| 12/set | Sementes | Milho Kg | 90 | R$ 2,00 | R$ 180,00 | Pedro | SP |
| 14/set | Sementes | Milho Kg | 80 | R$ 2,00 | R$ 160,00 | João | SP |
| 14/set | Sementes | Feijão Kg | 90 | R$ 6,00 | R$ 540,00 | João | PR |
| 15/set | Legumes | Cenoura Kg | 70 | R$ 7,00 | R$ 490,00 | Maria | RJ |
| 15/set | Legumes | Beterraba Kg | 100 | R$ 5,00 | R$ 500,00 | Maria | MG |
| 15/set | Carnes | Frango Congelado Kg | 110 | R$ 20,00 | R$ 2.200,00 | Maria | MG |
| 15/set | Frutas | Maçã Kg | 120 | R$ 6,50 | R$ 780,00 | Maria | MG |
| 15/set | Frutas | Laranja Kg | 95 | R$ 2,50 | R$ 237,50 | Sara | SP |
| 18/set | Sementes | Feijão Kg | 85 | R$ 6,00 | R$ 510,00 | Maria | SP |
| 19/set | Carnes | Frango Congelado Kg | 125 | R$ 20,00 | R$ 2.500,00 | Sara | PR |
| 20/set | Frutas | Laranja Kg | 120 | R$ 2,50 | R$ 300,00 | Pedro | SP |
| 21/set | Legumes | Beterraba Kg | 110 | R$ 5,00 | R$ 550,00 | Pedro | MG |
| 22/set | Carnes | Frango Congelado Kg | 90 | R$ 20,00 | R$ 1.800,00 | Maria | RJ |
| 23/set | Carnes | Frango Congelado Kg | 80 | R$ 20,00 | R$ 1.600,00 | João | PR |
| 23/set | Frutas | Laranja Kg | 140 | R$ 2,50 | R$ 350,00 | Sara | PR |
| 23/set | Frutas | Laranja Kg | 80 | R$ 2,50 | R$ 200,00 | Sara | SP |
| 23/set | Frutas | Maçã Kg | 130 | R$ 6,50 | R$ 845,00 | João | RJ |
| 23/set | Legumes | Cenoura Kg | 70 | R$ 7,00 | R$ 490,00 | João | RJ |
| 25/set | Sementes | Milho Kg | 70 | R$ 2,00 | R$ 140,00 | Pedro | SP |
| 27/set | Sementes | Feijão Kg | 100 | R$ 6,00 | R$ 600,00 | Pedro | RJ |
| 29/set | Carnes | Frango Congelado Kg | 100 | R$ 20,00 | R$ 2.000,00 | Maria | SP |
| 29/set | Carnes | Frango Congelado Kg | 100 | R$ 20,00 | R$ 2.000,00 | João | MG |
| 29/set | Legumes | Beterraba Kg | 100 | R$ 5,00 | R$ 500,00 | Pedro | MG |
| 29/set | Frutas | Laranja Kg | 100 | R$ 2,50 | R$ 250,00 | Sara | SP |
| 29/set | Sementes | Milho Kg | 90 | R$ 2,00 | R$ 180,00 | Maria | RJ |
Qual foi o valor total das vendas em setembro?
| Total das vendas | Mês |
|---|---|
| R$ 23.027,50 | Setembro |
Fórmula usada:
=PIVOTAR(;;F2:F30;SOMA)
Note que, na função PIVOTAR, quando abrimos o parêntese, ela traz vários argumentos, conforme mostrados na imagem a seguir. Por exemplo row_fields; col_fields; values... E assim por diante.
Para esta primeira fórmula eu quero o valor, então adicionei ";;" (dois pontos e vírgulas) até chegar ao argumento Values. Em seguida eu montei a fórmula:
=PIVOTAR(;;F2:F30;SOMA)
O que eu quero neste resultado, é a soma de todas as vendas do mês. Então, eu selecionei SOMA, no propósito da função PIVOTAR. O resultado você já viu na tabelinha acima!
Qual vendedor teve o melhor desempenho?
Agora vou editar a fórmula, ou criar outra em uma nova tabela, para saber quem vendeu mais no mês de setembro.
A fórmula foi editada em outra tabela:
=PIVOTAR(G2:G30;;F2:F30;SOMA)
O resultado:
| Desempenho dos Vendedores | Totais |
|---|---|
| João | R$ 5.835,00 |
| Maria | R$ 10.785,00 |
| Pedro | R$ 2.270,00 |
| Sara | R$ 4.137,50 |
| Total | R$ 23.027,50 |
Desta vez eu quero saber quais foram os produtos mais vendidos durantes um mês. Neste caso o mês de setembro, seguindo os exemplos anteriores.
=PIVOTAR(B2:B30;;D2:D30;SOMA)
| Produtos Mais Vendidos | Totais em Quilos (Kg) |
|---|---|
| Carnes | 705 |
| Frutas | 915 |
| Legumes | 570 |
| Sementes | 705 |
| Total | 2895 |
Eu tenho todas as respostas que preciso, para o departamento de vendas, usando apenas a função PIVOTAR, que pode, em parte, substituir a tabela dinâmica no Excel. Além disso é mais fácil de usar, depois que você compreende a função.
Agora vamos mostrar outros exemplos mais básicos, para que você possa entender rapidamente o uso desta extraordinária função do Excel. Lembrando que você pode copiar os exemplos das tabelas e colar na célula A1 de uma nova planilha e adicionar as respostas com formulações da função PIVOTAR.
Exemplo 2: Reorganizando Dados de Funcionários
Dados Originais (A1:D4):
| Nome | Cargo | Salário | Departamento |
|---|---|---|---|
| João | Analista | 4.500 | TI |
| Maria | Gerente | 7.200 | Vendas |
| Pedro | Assistente | 2.800 | RH |
Fórmula:
=PIVOTAR(A1:D4)Resultado:
| Nome | João | Maria | Pedro |
|---|---|---|---|
| Cargo | Analista | Gerente | Assistente |
| Salário | 4.500 | 7.200 | 2.800 |
| Departamento | TI | Vendas | RH |
Exemplo 3: Transformando Dados Trimestrais
Dados Originais (A1:A4):
| Trimestre |
|---|
| Q1: 45.000 |
| Q2: 52.000 |
| Q3: 48.500 |
| Q4: 61.200 |
Fórmula:
=PIVOTAR(A1:A4)
Resultado: | Q1: 45.000 | Q2: 52.000 | Q3: 48.500 | Q4: 61.200 |
Dicas Avançadas para Usar a Função PIVOTAR
Este guia se aprofunda em dicas e técnicas que otimizarão seu uso da função PIVOTAR. Aprenderemos como lidar com cenários complexos, como personalizar a agregação de dados e como otimizar o desempenho ao trabalhar com grandes conjuntos de dados.
Exploraremos estratégias para manipular e apresentar seus dados de forma mais eficaz, permitindo insights mais profundos e decisões mais informadas. Ao dominar estas técnicas avançadas, você poderá transformar dados brutos em informações valiosas com a função PIVOTAR.
1. Combinando com Outras Funções
Exemplo com ÍNDICE e CORRESP:
=PIVOTAR(ÍNDICE(A:C,CORRESP("Vendas",A:A,0):CORRESP("Vendas",A:A,0)+2,1:3))2. Pivotando Dados Dinâmicos
Para criar uma tabela pivotada que se atualiza automaticamente:
=PIVOTAR(DESLOC(A1,0,0,CONT.VALORES(A:A),CONT.VALORES(1:1)))3. Tratando Erros na Função PIVOTAR
=SEERRO(PIVOTAR(A1:C10),"Dados não encontrados")4. Pivotando Apenas Valores Numéricos
=PIVOTAR(SE(ÉNÚM(A1:A10),A1:A10,""))Fórmulas Essenciais Relacionadas
1. PIVOTAR com Filtro
=PIVOTAR(FILTRO(A1:C10,B1:B10="Vendas"))2. PIVOTAR com CLASSIFICAR
=PIVOTAR(CLASSIFICAR(A1:B10,2,-1))
3. Contando Elementos Pivotados
=COLS(PIVOTAR(A1:A10))
Vantagens da Função PIVOTAR
Principais Benefícios:
- Flexibilidade: Permite reorganizar dados rapidamente sem copiar e colar manualmente.
- Eficiência: Economiza tempo significativo em tarefas de reorganização de dados.
- Dinâmica: Atualiza automaticamente quando os dados originais mudam.
- Compatibilidade: Funciona bem com outras funções do Excel.
- Visualização: Melhora a apresentação de dados para relatórios.
- Análise: Facilita comparações e identificação de padrões.
- Automação: Pode ser incorporada em macros e fórmulas complexas.
Desvantagens e Limitações
Principais Limitações:
- Tamanho: Limitada pelo tamanho máximo das planilhas do Excel.
- Tipos de Dados: Pode ter problemas com dados mistos (texto e números).
- Complexidade: Fórmulas muito complexas podem ser difíceis de manter.
- Performance: Pode ser lenta com grandes volumes de dados.
- Formatação: Não preserva formatação original dos dados.
- Referências: Cria referências absolutas, dificultando cópias.
- Erros: Sensível a células vazias e dados inconsistentes.
Casos de Uso Profissionais
Relatórios Financeiros
Transformar dados mensais em visualização anual para dashboards executivos.
Análise de Vendas
Converter listas de produtos em comparativos horizontais por período.
Gestão de Recursos Humanos
Reorganizar informações de funcionários para relatórios administrativos.
Controle de Estoque
Pivotar dados de inventário para facilitar análises por categoria.
Conclusão
A função PIVOTAR é uma ferramenta fundamental para qualquer profissional que trabalha com dados no Excel. Sua capacidade de transformar e reorganizar informações de forma dinâmica a torna indispensável para criar relatórios eficientes e análises mais claras.
Dominar esta função, junto com suas variações e combinações com outras fórmulas, pode significativamente aumentar sua produtividade e a qualidade de suas análises de dados. Pratique com os exemplos apresentados e experimente diferentes combinações para descobrir todo o potencial desta poderosa ferramenta.
Lembre-se de sempre verificar seus dados antes de aplicar a função e considere as limitações mencionadas para evitar problemas em seus projetos.
Perguntas Frequentes
1. A função PIVOTAR funciona com dados de texto e números misturados?
Sim, a função PIVOTAR funciona com dados mistos, mas é importante verificar se não há células vazias que possam causar erros na transformação dos dados.
2. É possível pivotar apenas parte de uma tabela?
Absolutamente. Você pode especificar exatamente o intervalo que deseja pivotar usando referências de células específicas, como A1:C5 em vez da tabela inteira.
3. Como posso pivotar dados que se atualizam constantemente?
Use referências dinâmicas com funções como DESLOC ou crie uma Tabela do Excel (Ctrl+T) antes de aplicar a função PIVOTAR para garantir atualizações automáticas.
4. A função PIVOTAR preserva a formatação original dos dados?
Não, a função PIVOTAR não preserva formatação. Você precisará reaplicar formatações como cores, bordas e estilos de número após a transformação.
5. Posso usar PIVOTAR com dados que contêm fórmulas?
Sim, mas a função retornará os valores resultantes das fórmulas, não as fórmulas em si. Se precisar manter as fórmulas, considere usar cópia especial com transposição.
6. Existe limite de tamanho para usar a função PIVOTAR?
O limite é determinado pelas limitações gerais do Excel: 1.048.576 linhas por 16.384 colunas. Para dados muito grandes, considere usar Power Query como alternativa.
7. Como posso pivotar dados que estão em planilhas diferentes?
Use referências externas na sintaxe: =PIVOTAR(Planilha2!A1:C10). Certifique-se de que a planilha referenciada esteja acessível.
8. A função PIVOTAR funciona com tabelas filtradas?
Sim, mas aplicará a transformação apenas aos dados visíveis. Para melhores resultados, remova filtros antes de usar PIVOTAR ou combine com a função FILTRO.
