Pare de usar PROCX no Excel: Veja Isto Mais Rápido e Limpo

Pare de usar PROCX no Excel: Veja Isto Mais Rápido e Limpo

Pare de usar o Procx no Excel e veja isto: um método mais rápido e limpo para consultar dados de outras listas e tabelas sem encher a base principal com várias fórmulas auxiliares. A ideia é usar o Power Pivot para criar relacionamentos entre as tabelas e montar análises mais organizadas no Excel.

Introdução

O PROCX é uma das melhores funções do Excel moderno. Ele ajuda a procurar informações em tabelas, substitui muitos usos do PROCV e deixa as consultas mais flexíveis. Com ele, você pode buscar o nome de um produto pelo código, retornar o gerente de uma loja pelo ID ou localizar rapidamente uma informação em outra lista.

Mas existe uma situação em que o PROCX começa a deixar a planilha mais pesada e trabalhosa: quando você precisa montar relatórios com várias tabelas relacionadas.

Imagine uma base de vendas com 1.000 registros. Cada linha possui pedido, data, código do produto, código da loja e quantidade vendida. Em outra tabela estão os produtos, com nome, categoria e preço unitário. Em outra, as lojas, com nome, cidade, UF e gerente. O caminho mais comum seria criar uma coluna com PROCX para buscar o produto, outra para categoria, outra para preço, outra para loja, outra para cidade, e assim por diante.

Funciona? Sim. O problema é que você passa a duplicar informações, aumenta a quantidade de fórmulas, cria mais pontos de manutenção e deixa a base de vendas menos limpa.

Quando o objetivo é analisar dados, cruzar vendas por produto, loja, categoria, cidade ou gerente, o Power Pivot pode ser uma alternativa muito mais elegante. Em vez de buscar os dados linha por linha, você cria relacionamentos entre tabelas e deixa o Modelo de Dados fazer esse trabalho.

Neste artigo, você vai ver primeiro como o PROCX resolveria a consulta. Depois, verá como usar o Power Pivot no Excel para criar relacionamentos e montar uma Tabela Dinâmica mais limpa.

Como o PROCX funciona neste caso

O PROCX procura um valor em uma matriz e retorna o valor correspondente em outra matriz. A Microsoft apresenta a função como uma forma de pesquisar em um intervalo ou tabela e retornar o item relacionado: função PROCX no Excel.

Na planilha de exemplo, a tabela de vendas possui o campo ID Produto. A tabela de produtos também possui ID Produto. Então, para trazer o nome do produto para a base de vendas, você poderia criar uma nova coluna e usar:

=PROCX([@[ID Produto]];tProdutos[ID Produto];tProdutos[Produto];"Não encontrado")

Para buscar a categoria, seria outra fórmula:

=PROCX([@[ID Produto]];tProdutos[ID Produto];tProdutos[Categoria];"Não encontrado")

E para buscar o preço unitário:

=PROCX([@[ID Produto]];tProdutos[ID Produto];tProdutos[Preço Unidade];0)

A mesma lógica serviria para a loja. Com ID Loja, você poderia buscar nome da loja, cidade, UF e gerente na tabela tLojas.

Perceba que o PROCX não está errado. Ele é excelente para consultas pontuais, preenchimento de campos, conferências e modelos menores. A questão é que, quando a planilha vira um relatório com tabelas relacionadas, talvez você não precise trazer todos os campos para dentro da base principal.

Por que usar Power Pivot no lugar de várias fórmulas

O Power Pivot permite trabalhar com várias tabelas dentro do Modelo de Dados do Excel. Em vez de copiar informações de uma tabela para outra, você cria relacionamentos.

Na prática, você informa ao Excel que:

• tVendas registra os pedidos;

• tProdutos descreve os produtos;

• tLojas descreve as lojas;

• ID Produto conecta vendas com produtos;

• ID Loja conecta vendas com lojas.

Depois disso, uma Tabela Dinâmica consegue usar campos das três tabelas. Você pode colocar produto nas linhas, nome da loja nas colunas e valores de vendas no campo de valores, sem criar várias colunas de PROCX.

A Microsoft explica que o Modelo de Dados permite integrar informações de várias tabelas por meio de relacionamentos: criar um Modelo de Dados no Excel. Esse é exatamente o cenário da planilha deste artigo.

O principal ganho é organização. A base de vendas continua enxuta, os cadastros ficam separados e cada tabela guarda apenas as informações que pertencem a ela.

Conheça a planilha de exemplo

A planilha usada neste artigo tem uma estrutura muito comum em empresas: uma base de movimentos e duas tabelas de cadastro.

A tabela tVendas registra o que aconteceu. Ela mostra cada pedido, quando foi vendido, qual produto saiu, em qual loja e em qual quantidade.

A tabela tProdutos descreve os produtos. Ela traz o nome, a categoria e o preço unitário. Já a tabela tLojas descreve cada loja, com nome, cidade, estado e gerente.

Essa separação é importante. Se todos os dados ficassem repetidos na base de vendas, qualquer alteração em um cadastro poderia exigir correções em várias linhas. Com cadastros separados, cada informação tem um lugar certo.

No Power Pivot, a tabela de vendas funciona como tabela de fatos, pois guarda os eventos e números. As tabelas de produtos e lojas funcionam como tabelas de dimensão, pois descrevem os códigos usados na análise.

Como ativar o Power Pivot no Excel

Antes de criar os relacionamentos, verifique se a guia Power Pivot aparece no Excel. Se ela não aparecer, siga este caminho:

1. Clique em Arquivo.

2. Clique em Opções.

3. Entre em Suplementos.

4. Em Gerenciar, escolha Suplementos COM.

5. Clique em Ir.

6. Marque Microsoft Power Pivot para Excel.

7. Clique em OK.

A Microsoft também mostra esse processo na página sobre como iniciar o suplemento: iniciar o Power Pivot no Excel.

Depois disso, a guia Power Pivot deve aparecer na faixa de opções.

Como usar o Power Pivot no Excel passo a passo

Agora vamos ao processo principal: adicionar as tabelas ao Modelo de Dados, criar os relacionamentos e montar o relatório.

1. Transforme os intervalos em tabelas

Na planilha de exemplo, isso já está pronto. A base de vendas está como tVendas, o cadastro de produtos está como tProdutos e o cadastro de lojas está como tLojas.

Se estiver fazendo em outra planilha, selecione o intervalo de dados e pressione Ctrl+T. Marque que a tabela possui cabeçalhos e depois dê um nome claro para ela na guia Design da Tabela.

Nomes claros ajudam muito. É melhor trabalhar com tVendas e tProdutos do que com nomes genéricos como Tabela1 e Tabela2.

2. Adicione as tabelas ao Modelo de Dados

Clique em qualquer célula da tabela tVendas, vá até a guia Power Pivot e clique em Adicionar ao Modelo de Dados. Repita o processo para tProdutos e tLojas.

Essas tabelas passam a ficar disponíveis dentro da janela do Power Pivot. A Microsoft chama esse tipo de origem de tabela vinculada quando os dados vêm da planilha: adicionar dados da planilha ao Modelo de Dados.

3. Abra a visualização de diagrama

Dentro do Power Pivot, clique em Exibição de Diagrama. Essa tela mostra cada tabela como uma caixa, com seus respectivos campos.

Você deverá visualizar tVendas, tProdutos e tLojas. Essa visualização facilita entender quais campos ligam uma tabela à outra. A Microsoft também recomenda a Visualização de Diagrama para criar relações de forma visual: criar relações na Visualização de Diagrama.

4. Relacione vendas com produtos

Na tabela tProdutos, o campo ID Produto identifica cada produto uma única vez. Na tabela tVendas, esse campo aparece várias vezes, porque o mesmo produto pode ser vendido em muitos pedidos.

Crie o relacionamento assim:

1. Em tProdutos, localize ID Produto.

2. Arraste esse campo até ID Produto em tVendas.

3. Solte o campo para criar a conexão.

O relacionamento correto é:

Agora o Excel entende qual produto pertence a cada venda sem precisar de uma fórmula PROCX em cada linha.

5. Relacione vendas com lojas

Repita a mesma ideia com as lojas. Na tabela tLojas, ID Loja identifica cada loja uma única vez. Na tabela tVendas, ele aparece em muitas linhas.

Crie este relacionamento:

É importante conferir se os campos estão corretos e se os tipos de dados são compatíveis. A Microsoft orienta validar os relacionamentos e os resultados da análise: criar uma relação entre tabelas no Excel.

6. Crie a Tabela Dinâmica

Com os relacionamentos criados, monte o relatório:

1. No Power Pivot, clique em Tabela Dinâmica.

2. Escolha uma nova planilha ou uma planilha existente.

3. Arraste Produto, de tProdutos, para Linhas.

4. Arraste Nome, de tLojas, para Colunas.

5. Arraste Quantidade, de tVendas, para Valores.

Pronto. Você criou uma análise por produto e por loja sem buscar o nome do produto e da loja com fórmulas auxiliares.

Esse é o ponto principal: o relacionamento substitui a necessidade de repetir consultas quando o objetivo é criar análises.

Como calcular o valor total das vendas

Para somar quantidade, basta usar o campo Quantidade. Mas, se você quiser calcular o valor total das vendas, precisa multiplicar a quantidade pelo preço unitário do produto.

Como a quantidade está em tVendas e o preço está em tProdutos, crie uma medida DAX no Power Pivot:

=SUMX(tVendas;tVendas[Quantidade]*RELATED(tProdutos[Preço Unidade]))

A função SUMX percorre uma tabela e soma o resultado de uma expressão: função SUMX em DAX. A função RELATED busca um valor em uma tabela relacionada: função RELATED em DAX.

Na prática, a medida calcula quantidade vezes preço para cada venda e soma tudo conforme os filtros da Tabela Dinâmica.

Um cuidado: se o preço do produto muda com o tempo, o ideal é gravar o preço unitário na própria venda no momento da transação. Assim, o histórico não muda quando o cadastro de produtos for atualizado.

PROCX ou Power Pivot: qual usar?

Não se trata de abandonar o PROCX. Trata-se de escolher a ferramenta certa.

Use PROCX quando precisar retornar um valor específico para uma célula. Use Power Pivot quando precisar analisar dados de várias tabelas relacionadas.

Como atualizar os dados depois

Depois que as tabelas estão no Modelo de Dados, você pode continuar incluindo registros na planilha. Ao adicionar novas vendas em tVendas, atualize a Tabela Dinâmica para que os novos dados apareçam.

Na maioria dos casos, clique com o botão direito na Tabela Dinâmica e escolha Atualizar. Também é possível usar a guia Dados e clicar em Atualizar Tudo.

A Microsoft explica o processo de atualização na documentação sobre Tabelas Dinâmicas: atualizar dados da Tabela Dinâmica.

Esse cuidado é importante. O Power Pivot organiza melhor a análise, mas o relatório ainda precisa ser atualizado quando os dados mudam.

Erros comuns ao usar Power Pivot

O primeiro erro é relacionar tabelas pelo campo errado. Em vez de usar o nome do produto, prefira o código. Nomes podem ter espaços extras, alterações de texto ou duplicidades.

O segundo erro é ter duplicidade na tabela de cadastro. Em tProdutos, cada ID Produto deve aparecer apenas uma vez. Em tLojas, cada ID Loja também deve aparecer apenas uma vez.

O terceiro erro é misturar tipos de dados. Se o código estiver como texto em uma tabela e como número em outra, o relacionamento pode falhar.

O quarto erro é acreditar que o Power Pivot substitui todas as fórmulas. Ele não substitui tudo. Ele resolve muito bem o excesso de buscas quando o objetivo é criar relatórios relacionais.

Download Planilha de Exemplo

Faça o download da planilha do artigo e da vídeo-aula no botão abaixo e receba o material no seu email.

FAQ sobre PROCX e Power Pivot

Power Pivot substitui o PROCX?

Não em todos os casos. O Power Pivot é melhor para análises com várias tabelas relacionadas. O PROCX continua excelente para buscas pontuais.

Preciso saber DAX para usar Power Pivot?

Não obrigatoriamente. Você pode criar relacionamentos e Tabelas Dinâmicas simples sem escrever DAX. Mas, para indicadores como valor total, margem e ticket médio, as medidas DAX ajudam bastante.

O relacionamento é criado automaticamente?

Em alguns casos o Excel pode sugerir relações, mas o ideal é criar e validar os relacionamentos. Neste exemplo, os campos corretos são ID Produto e ID Loja.

Posso usar Power Pivot em qualquer versão do Excel?

O Power Pivot está disponível em várias versões do Excel para Windows, especialmente nas versões modernas do Microsoft 365 e em algumas licenças perpétuas. A disponibilidade pode variar conforme a edição.

O arquivo fica mais leve sem PROCX?

Depende do tamanho da base e da quantidade de fórmulas. Em geral, reduzir centenas ou milhares de fórmulas auxiliares simplifica a manutenção. O principal ganho, porém, é a organização.

Posso usar Power Query junto com Power Pivot?

Sim. O Power Query é ótimo para importar, limpar e transformar dados. O Power Pivot é ótimo para modelar, relacionar e calcular indicadores. A Microsoft mostra que os dois recursos podem trabalhar juntos: Power Query e Power Pivot.

Quando devo continuar usando PROCX?

Use PROCX quando precisar retornar um valor específico para uma célula, montar uma consulta simples ou fazer uma conferência rápida. Ele continua sendo uma das melhores funções do Excel para esse tipo de tarefa.

Conclusão

O PROCX é uma função poderosa e deve continuar no seu repertório. Mas, quando você trabalha com vendas, produtos, lojas, categorias e outras tabelas relacionadas, repetir fórmulas de busca em várias colunas nem sempre é o melhor caminho.

Com o Power Pivot, você organiza os dados em tabelas separadas, cria relacionamentos e monta relatórios mais limpos no Excel. A base de vendas continua enxuta, os cadastros ficam nos lugares certos e a análise ganha flexibilidade.

Neste exemplo, tVendas se conecta com tProdutos pelo campo ID Produto e com tLojas pelo campo ID Loja. A partir daí, é possível analisar quantidade e valor total por produto, loja, categoria ou cidade sem transformar a planilha em uma sequência de colunas auxiliares.

Portanto, antes de criar várias colunas com PROCX apenas para alimentar uma Tabela Dinâmica, teste o Power Pivot. Para relatórios, ele pode ser mais organizado, escalável e profissional.