outubro 23, 2025

César Fontanella

Excel Avançado para Programadores: modelos tabulares, DAX profundo, Power Query M, LAMBDA, VBA/Office Scripts e performance

Chegou ao nível avançado: agora você trata milhões de linhas, modela dados em star schema, escreve DAX expressivo, cria pipelines M reutilizáveis, encapsula lógica com LAMBDA e automatiza tarefas com VBA/Office Scripts. Este guia é focado em produtividade de engenheiro: governança, teste, versionamento e performance.

1) Arquitetura de dados no Excel (stack do dev)

Camada de ingestão: Power Query (M) lê CSV/Parquet/SQL/API, aplica query folding sempre que possível.
Camada de modelo: Power Pivot (xVelocity) com Dimensões (Data, Produto, Cliente) e Fato (Transações).
Camada de métricas: DAX com medidas reutilizáveis (contexto de filtro/linha, time intelligence).
Camada de experiência: Tabela Dinâmica + gráficos + segmentações; ou CUBE functions para relatórios flexíveis em células.
Automação: LAMBDA (sem macro), VBA (desktop), Office Scripts (web, TypeScript) e Power Automate.

2) Power Query (M) avançado — ETL resiliente e dobrável

Padrões de projeto

Camada de staging (tipos, limpeza), camada de negócios (regras), camada de saída (tabelas consumíveis).
Use Parâmetros (datas, caminhos) e Consultas de Função para reaproveitar transformações em várias fontes.

Query folding (desempenho)

Prefira etapas que “dobram” (SelectColumns, FilterRows, GroupRows, JoinKind.Inner) para empurrar operações ao banco.
Evite romper o folding cedo (ex.: adicionar coluna com lógica complexa em M antes dos filtros principais).

Exemplos M úteis

Função de paginação para REST com token/offset

(Page as number) as table =>
let
  Source = Json.Document(Web.Contents(apiUrl, [Query=[page=Text.From(Page)]])),
  Rows   = Table.FromList(Source[data], Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  Rows

Mesclar (join) dimensional com normalização

let
  Fato = ...,
  Dim  = ...,
  Join = Table.NestedJoin(Fato, {"sku"}, Dim, {"sku"}, "Dim", JoinKind.LeftOuter),
  Exp  = Table.ExpandTableColumn(Join, "Dim", {"categoria","marca"})
in
  Exp

Parametro de período rolling

let
  Hoje = Date.From(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), -3)),
  Inicio = Date.AddMonths(Hoje, -12)
in
  [Hoje=Hoje, Inicio=Inicio]

3) Power Pivot + DAX — contexto, iteração e tempo

Fundamentos que diferenciam um avançado

Contexto de filtro vs contexto de linha; transição de contexto (linhas → filtro em medidas).
Iteradores (SUMX, AVERAGEX, FILTER) para lógica por linha com agregação.
Time intelligence nativa com tabela de datas marcada.

Medidas de referência

Receita := SUM(Fato[Valor])

Receita LY (ano anterior) :=
CALCULATE([Receita], SAMEPERIODLASTYEAR(DimData[Data]))

Crescimento % :=
DIVIDE([Receita] - [Receita LY], [Receita LY])

Ticket Médio :=
DIVIDE([Receita], DISTINCTCOUNT(Fato[PedidoId]))

Receita Top N Produtos :=
VAR TopN = TOPN(10, SUMMARIZE(DimProduto, DimProduto[Produto], "Rec", [Receita]), [Rec], DESC)
RETURN CALCULATE([Receita], KEEPFILTERS(TopN))

Dicas pro:

Evite colunas calculadas para métricas; prefira medidas (memória e velocidade).
Use variables (VAR) para clareza e reuso dentro da medida.
REMOVEFILTERS / ALLEXCEPT para controlar granularidade de comparação.

4) CUBE functions — relatórios flexíveis em células

CUBEVALUE/CUBEMEMBER consultam o Modelo de Dados interno (Tabular) sem Pivot.
Úteis em painéis “pixel-perfect” e exports.
Exemplo:

=CUBEMEMBER("ThisWorkbookDataModel","[DimProduto].[Categoria].&[Bebidas]")
=CUBEVALUE("ThisWorkbookDataModel",$B$2,"[Measures].[Receita]")

5) Arrays dinâmicos e LAMBDA avançados

Compõe funções reutilizáveis sem macro:

=LET(
  base; FILTER(Tabela; (Tabela[status]="Pago")*(Tabela[data]>=G2));
  topN; 10;
  SORT(base; 3; -1)(1:topN; )
)

LAMBDA com MAP/REDUCE:

=REDUCE(0; Tabela[valor]; LAMBDA(acum;x; acum + x*1,07))

Desduplicação hierárquica (categoria→produto):

=BYROW(UNIQUE(Tabela[categoria]); LAMBDA(cat;
  HSTACK(cat; TEXTJOIN(", "; TRUE; UNIQUE(FILTER(Tabela[produto]; Tabela[categoria]=cat))))
))

6) Automação: VBA vs Office Scripts (TypeScript)

Quando usar cada um

VBA (desktop): interação profunda com objetos do Excel, legado corporativo, eventos complexos.
Office Scripts (web): integração com Power Automate, fácil CI/CD (código em TypeScript), multiplataforma.

Exemplo Office Script (atualiza consultas e salva PDF):

function main(workbook: ExcelScript.Workbook) {
  workbook.refreshAllDataConnections();
  const sheet = workbook.getWorksheet("Relatorio");
  sheet.getRange("A1").getFormat().setBold(true);
  workbook.getPdf(); // no Power Automate: exportar e enviar
}

Exemplo VBA (validar planilhas antes de publicar):

Sub ValidarAntesDeSalvar()
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    If WorksheetFunction.CountBlank(ws.Range("A1:D1")) > 0 Then
      MsgBox "Cabeçalhos faltando em " & ws.Name: Exit Sub
    End If
  Next ws
  ThisWorkbook.Save
End Sub

7) Conexões de dados, APIs e formatos modernos

SQL: use consultas parametrizadas (Views/Stored Procedures) e inclua credenciais seguras (Single Sign-On/KeyVault).
APIs REST: Power Query com Web.Contents, headers, paginação e retry exponencial.
Parquet/Delta: leia camadas de lakehouse para volumes grandes (conectores atualizados).
SharePoint/OneDrive: caminhos organizados e fixos para refreshes estáveis.

8) Segurança, governança e versionamento

Proteja células críticas, bloqueie estrutura do modelo e oculte consultas sensíveis.
Versione em Git via pasta sincronizada (exportando .pq/.dax/.bas quando aplicável) ou em SharePoint com histórico.
Padronize nomes de medidas ([Rec], [Qtde], [Margem %]) e pastas de exibição no modelo.

9) Performance checklist

Power Query: maximize folding; adie transformações pesadas; tipagem precoce; remova Changed Type redundantes.
DAX: prefira medidas e SUMX sobre colunas; minimize CALCULATE aninhado; use tabelas de dimensões enxutas (encodes melhores).
Relatórios: limite visuais/cálculos voláteis; use calcular manual ao editar e pre-calc quando possível.
Armazenamento: evite colunas de texto longas no Fato; normalize para Dimensões.

10) Excel no ecossistema de linguagens (por que isso importa)

SQL: fonte primária, modelagem relacional, performance de leitura — Excel consome e publica extratos.
Python: orquestra ETL fora do Excel (pandas/duckdb), gera XLSX estilizado (openpyxl/xlsxwriter), scripts de QA.
C/C++: add-ins de alto desempenho e bibliotecas numéricas quando o cálculo exige otimização nativa.
C#/.NET/JS: Office Add-ins modernos (Web Add-ins) para experiências customizadas.
Resultado: Excel vira frontend analítico para pipelines confiáveis e reproduzíveis.

Projeto avançado (portfólio sênior)

Ingestão: Power Query puxa 24 CSVs mensais (append) + cadastro de produtos via API.
Modelo Tabular: FatoVendas (10M linhas), DimData, DimProduto, DimCliente; relacionamentos 1:*; tabela de datas marcada.
Medidas DAX: Receita, Margem, Ticket Médio, Receita LY, % Crescimento, Top N por Sazonalidade.
Relatório: painéis com segmentações hierárquicas e CUBE functions para layout editorial.
Automação: Office Script que atualiza tudo, exporta PDF e envia via Power Automate para um canal do Teams.
Governança: dicionário de dados (worksheet), convenções de nomes, versionamento e checklist de refresh.

FAQ (rápido)

Quando usar CUBE functions em vez de Pivot?
Quando você precisa de layout totalmente livre em células, mantendo medidas DAX do modelo.

Vale aprender M a fundo?
Sim. Em pipelines complexos com APIs/JSON/paginações e tratamento de erros, M dá controle fino e reprodutibilidade.

DAX ou fórmulas?
Para métricas de negócio reusáveis e grandes volumes, DAX no modelo tabular. Fórmulas ficam para camadas de apresentação.

Conclusão

Excel avançado é engenharia de dados aplicada: ETL dobrável em M, modelo tabular com DAX, relatórios de alta fidelidade e automação com LAMBDA/VBA/Office Scripts. Integrado a SQL e Python, você entrega análises confiáveis, rápidas e escaláveis — do notebook ao boardroom.