outubro 21, 2025

César Fontanella

Excel Intermediário para Programadores Iniciantes: do PROCV ao Power Query + LAMBDA (guia prático e SEO-friendly)

Chegou a hora de sair do básico: neste guia de Excel intermediário você vai dominar buscas robustas, arrays dinâmicos, Power Query, Tabelas Dinâmicas avançadas, modelagem com DAX (no Power Pivot), validação dependente, automação leve (LAMBDA/VBA/Office Scripts) e boas práticas de performance — sempre com exemplos copiáveis.

Sumário rápido

Lookup moderno (ÍNDICE+CORRESP, XLOOKUP/PROCX)
Arrays dinâmicos: FILTER, UNIQUE, SORT, SEQUENCE, BYROW/BYCOL
LET, LAMBDA, MAP, REDUCE, SCAN (Excel 365)
Tratamento de erros e lógica composta (SE encadeado, IFS, SWITCH)
Tabelas & referências estruturadas (produtividade e legibilidade)
Power Query (merge, append, parâmetros, agendamento)
Pivot avançado (medidas, campos calculados, segmentações)
Power Pivot + DAX (modelo star, SUMX, CALCULATE, FILTER)
Validação dependente (listas em cascata) e formatação condicional avançada
Automação leve (LAMBDA, VBA, Office Scripts)
Conexão com Python/SQL e boas práticas de performance

1) Lookup moderno: do clássico ao robusto

ÍNDICE + CORRESP (INDEX + MATCH)

Robusto, flexível e rápido (busca em qualquer coluna/linha).

=ÍNDICE(Tabela[Preço]; CORRESP(E2; Tabela[SKU]; 0))

Troque Tabela pelos nomes reais da sua tabela (Ctrl+T cria Tabela).
CORRESP retorna a posição; ÍNDICE retorna o valor naquela posição.

XLOOKUP / PROCX (Excel 365+)

Mais legível que PROCV, suporta busca à esquerda, intervalo, valor padrão:

=PROCX(E2; Tabela[SKU]; Tabela[Preço]; "SKU não encontrado")

Use o 6º argumento para modo de correspondência (exata, próxima, coringa).

2) Arrays dinâmicos na prática

FILTER (filtra linhas por condição):

=FILTER(Tabela; (Tabela[Status]="Pago")*(Tabela[Valor]>100))

UNIQUE (categorias distintas):

=UNIQUE(Tabela[Categoria])

SORT (ordena):

=SORT(FILTER(Tabela; Tabela[Canal]="Online"); 1; -1)

SEQUENCE (gerar sequências numéricas):

=SEQUENCE(12;1;1;1)  // 1..12

BYROW/BYCOL (aplica expressão linha a linha):

=BYROW(Tabela[Valores]; LAMBDA(l; SOMA(l)))

3) LET, LAMBDA, MAP, REDUCE, SCAN (Excel 365)

LET: nomeie subexpressões (mais performance e clareza)

=LET(
  valores; Tabela[Valor];
  taxa; 0,07;
  total; SOMA(valores)*(1+taxa);
  total
)

LAMBDA: crie “funções” sem VBA

=LAMBDA(valor; taxa; valor*(1+taxa))

Salve em Gerenciador de Nomes como APLICA_TAXA e use:

=APLICA_TAXA(1000; 0,07)

MAP/REDUCE/SCAN: processamento funcional

=MAP(Tabela[Valor]; LAMBDA(x; x*1,07))

=REDUCE(0; Tabela[Valor]; LAMBDA(acum; x; acum+x))

4) Erros e lógica composta

IFERROR/SEERRO:

=SEERRO(PROCX(E2; SKU[Id]; SKU[Nome]); "N/D")

IFS:

=IFS(B2>=90;"A"; B2>=70;"B"; B2>=60;"C"; VERDADEIRO;"D")

SWITCH para mapeamentos curtos:

=ESCOLHER(CORRESP(B2; {"BR";"PT";"US"}; 0); "Real"; "Euro"; "Dólar")

5) Tabelas e referências estruturadas

Use Ctrl+T e trabalhe com Tabela[Coluna] — fórmulas autoexpandidas e legíveis.
Nomes de colunas sem espaços, snake_case, facilitam integração com Power Query e pandas.

6) Power Query (Obter & Transformar) — ETL visual e repetível

Casos comuns:

Merge (join) de duas tabelas por SKU (Inner/Left/Right/Full).
Append (empilhar) vários CSVs (pasta inteira).
Dividir Coluna por delimitador, pivot/unpivot (desnormalizar/normalizar).
Parâmetros (ex.: caminho da pasta, data inicial).
Atualização com 1 clique quando chegar um novo arquivo.

Dica: promova cabeçalhos, defina tipo de dados logo após a importação, e remova etapas voláteis para performance.

7) Tabela Dinâmica (Pivot) avançada

Segmentações (Slicers) e Linha do Tempo para filtros visuais.
Campos Calculados simples (média ponderada, % da coluna).
Pivot Charts para gráficos dinâmicos sincronizados com os filtros.

8) Power Pivot + DAX (modelo tabular dentro do Excel)

Modelo Star: Tabelas de fatos (transações) ligadas a dimensões (datas, produto, cliente).
Medidas úteis:

Receita := SUM(Fato[Valor])
Receita_12m := CALCULATE([Receita]; DATESINPERIOD(DimData[Data]; MAX(DimData[Data]); -12; MONTH))
%_Crescimento := DIVIDE([Receita]-[Receita_12m]; [Receita_12m])

Vantagem: desempenho em grandes volumes, medidas reutilizáveis, linguagem expressiva para análise.

9) Validação dependente e formatação condicional avançada

Listas em cascata (Categoria → Produto):

Crie lista de Categorias com =UNIQUE(Tabela[Categoria]).
Para Produtos, use =FILTER(Tabela[Produto]; Tabela[Categoria]=$G$2) na lista de validação.
Atualize a Validação de Dados para referenciar o resultado derramado.

Formatação Condicional com fórmulas:

Destacar top 10%, valores acima da média.
Regras com =B2>TETO(MÉDIA($B:$B);1) para outliers.

10) Automação leve

LAMBDA cobre muitos casos sem VBA.
VBA (quando precisa acessar eventos, laços em objetos, interagir com arquivos/gráficos de forma detalhada).
Office Scripts (Excel na Web, TypeScript) para rotinas “dev-friendly” e integração com Power Automate.

11) Conexão com Python/SQL e performance

Python (pandas/openpyxl/xlwings):

Gere relatórios XLSX, aplique estilos, dispare atualização diária.
Use read_excel()/to_excel() mantendo schema consistente.

SQL:

Traga dados “na fonte” (MySQL/Postgres/SQL Server) com consultas parametrizadas.
No Excel: Dados > De Banco de Dados (ou via ODBC).

Performance & boas práticas

Evite funções voláteis em excesso (AGORA, HOJE, DESLOC).
Prefira Tabelas, XLOOKUP, arrays dinâmicos, Power Query/Power Pivot para grandes volumes.
Calcular Manual em planilhas pesadas (F9) e quebre em “camadas” (staging → modelo → relatório).
Versione com OneDrive/SharePoint; proteja células críticas (Revisão > Permitir edição).

Projeto prático (portfolio)

Fonte: pasta com CSVs mensais de vendas.
ETL: Power Query (append + merge com cadastro de produtos).
Modelo: Power Pivot (DimData, DimProduto, FatoVendas).
Medidas DAX: Receita, Ticket Médio, Receita 12m, % Crescimento.
Relatório: Pivot com slicers (canal, região) + gráficos dinâmicos.
Automação: LAMBDA APLICA_TAXA() + Office Script para atualizar e salvar PDF.

FAQ

XLOOKUP vs. ÍNDICE+CORRESP?
XLOOKUP é mais simples e legível, mas ÍNDICE+CORRESP performa muito bem e funciona em versões antigas.

Power Query ou fórmulas?
Para ETL repetível e dados grandes: Power Query. Fórmulas para cálculos de célula/relatório.

Quando usar DAX?
Quando precisar de medidas reusáveis, time intelligence (YTD, 12m), grandes volumes e modelo tabular limpo.

Conclusão

No nível intermediário, Excel deixa de ser “planilha” e vira plataforma de dados: você constrói ETLs com Power Query, modela com DAX, cria relatórios dinâmicos e ainda automatiza com LAMBDA/VBA/Office Scripts. Para quem programa, isso significa menos trabalho manual, dados confiáveis e um pipeline pronto para conversar com Python e SQL.