O curso tem dois grandes blocos. No Tools você conhece as peças do Excel: telas, atalhos, fórmulas e as funções modernas. Na Modelagem você usa essas peças para montar modelos que resolvem problemas de negócio. Uma boa imagem é a de um balde de Lego: primeiro você vira o balde e conhece cada peça; depois aprende a montar algo útil com elas. Este capítulo é o primeiro contato com o balde. O assunto é básico, mas é a base de todo o resto: quem se move pela planilha com segurança, pelo teclado, constrói e audita modelos muito mais rápido.

O objetivo também não é decorar um dicionário de funções. É aprender a "falar a língua" do Excel: combinar poucas peças bem escolhidas para responder a uma pergunta. Por isso, desde a primeira aula, o curso já usa o Excel moderno, com fórmulas que devolvem um intervalo inteiro (as matrizes dinâmicas), Tabelas e nomes.

1.1A tela do Excel

Antes de qualquer fórmula, vale conhecer o vocabulário da tela. São esses nomes que aparecem nas dicas, nos menus e nas buscas de ajuda.

Tabela 1.1. Elementos da tela do Excel.

A Caixa de Nome é mais útil do que parece. Digite G10 e tecle Enter para ir até a célula G10; digite B2:B5 para selecionar esse intervalo. Quando há um intervalo selecionado, ela mostra apenas a primeira célula.

O cursor do mouse também "fala". A cruz branca seleciona células; a seta preta sobre um cabeçalho seleciona a coluna ou a linha inteira; a seta dupla entre dois cabeçalhos ajusta a largura ou a altura. Na borda inferior da Barra de Fórmulas, a seta dupla aumenta a altura da barra, recurso útil para ler fórmulas longas.

A Barra de Ferramentas de Acesso Rápido pode ser personalizada pela setinha ao lado dela, em Mais Comandos: adicione, reordene ou remova botões. Cada botão ganha um número que funciona com Alt: se Salvar for o primeiro, Alt 1 salva o arquivo.

Pasta de trabalho, planilha, célula e intervalo

• Pasta de trabalho é o arquivo (.xlsx).
• Planilha é cada aba da pasta.
• Célula é o cruzamento de uma coluna com uma linha, identificada pelo endereço: B3 é a coluna B, linha 3.
• Intervalo é um conjunto de células, escrito com dois-pontos: A1:C10 vai de A1 até C10.

1.2Navegar e selecionar pelo teclado

O mouse é ótimo para explorar, mas lento para trabalhar. Os atalhos abaixo resolvem a maior parte da navegação.

Tabela 1.2. Atalhos de navegação e seleção. Em notebooks, Page Up, Page Down, Home e End podem exigir a tecla Fn.

Ir Para Especial

O F5 abre a janela Ir Para. Ela guarda a última referência usada, então F5 seguido de Enter alterna entre duas células distantes. O botão Especial… seleciona células por tipo: Em branco, Constantes (números, texto, lógicos), Fórmulas e outros.

1.3Digitar, editar e preencher

Tabela 1.3. Entrada, edição e cópia. No Excel em português, Ctrl+Z e Ctrl+Y são os mesmos; Ctrl+T, porém, cria Tabela (equivale ao Ctrl+A do Excel em inglês).

Dentro de uma célula em edição, as setas andam caractere a caractere; Ctrl+setas pulam blocos de texto; Shift+setas selecionam trechos. Para sair da fórmula e apontar uma célula com as setas, tecle F2 de novo: ele alterna entre "editar o texto" e "apontar células".

Colar especial

O Ctrl+Alt+V abre o Colar Especial. Os usos mais comuns:

• Valores: cola só o resultado, sem a fórmula. Útil para "congelar" um número.
• Fórmulas: cola a fórmula sem trazer a formatação da origem.
• Formatos: cola só a aparência. Equivale ao Pincel de Formatação.
• Operação › Adicionar: soma o valor copiado ao destino. Copiar um 0 e colar como Valores + Adicionar sobre números gravados como texto converte esses textos em números.

Fórmulas longas legíveis

Ao digitar uma função, o Excel sugere nomes compatíveis. Escolha com as setas e tecle Tab: ele completa o nome e abre o parêntese. Feche sempre os parênteses antes do Enter; o Excel tenta completar sozinho, mas pode errar em fórmulas complexas.

Fórmulas longas ficam muito mais fáceis de ler quando quebradas em linhas com Alt+Enter, com espaços para alinhar os argumentos, como parágrafos de um texto:

=SOMASES(tbl_Vendas[Qtd];
         tbl_Vendas[Região]; $C$6;
         tbl_Vendas[Produto]; $C$7)

Essa função e as Tabelas aparecem no Capítulo 2. O ponto aqui é o formato: aumente a altura da Barra de Fórmulas e escreva para quem vai ler depois.

1.4Linhas, colunas e formatação

Inserir, excluir e mover sem desalinhar

Tabela 1.4. Linhas e colunas.

Para mover um bloco de linhas para outro ponto da base, selecione as linhas, recorte com Ctrl+X, selecione a linha de destino e, em vez de Ctrl+V, use Ctrl++. O Excel insere as linhas recortadas ali, sem sobrescrever nada. Com Ctrl+C no lugar do Ctrl+X, o bloco é duplicado.

Ocultar ou agrupar

Ocultar linhas e colunas serve para organizar a visão: tirar do caminho o que o usuário não precisa ver. O Excel deixa uma pista visual (a numeração pula de 13 para 15, por exemplo), e para reexibir basta selecionar as linhas ao redor, clicar com o botão direito e escolher Reexibir.

Quando você vai mostrar e esconder o mesmo trecho com frequência (por exemplo, colunas de detalhe que não devem sair na impressão), prefira Dados › Agrupar (Alt+Shift+→). O agrupamento cria botões de + e – e níveis numerados na margem, que abrem e fecham o trecho com um clique.

Atalhos com Alt em sequência

Tecle Alt e solte: aparecem letras sobre cada guia da Faixa de Opções. Digitando as letras em sequência, você chega a qualquer comando sem o mouse. Alguns exemplos no Excel em português:

Tabela 1.5. Exemplos de atalhos com Alt. As letras mudam com o idioma do Excel: a guia Página Inicial é C em português e H em inglês (Alt H 0 / Alt H 9; Alt A V V).

Dentro de um menu aberto por Alt, as setas navegam, Tab desce um nível, Shift+Tab sobe e Enter confirma. A mesma lógica vale na janela Formatar Células.

Formatar números

O Ctrl+1 abre Formatar Células, onde estão todos os formatos: Geral, Número, Moeda, Contábil, Data, Hora, Porcentagem e Personalizado. Para os formatos mais comuns, há atalhos diretos:

Tabela 1.6. Atalhos de formatação de número.

O formato Personalizado permite escrever o código de exibição. Ele tem até quatro partes separadas por ponto e vírgula: positivo; negativo; zero; texto.

Tabela 1.7. Exemplos de formatos personalizados. O texto entre aspas aparece na tela, mas a célula continua numérica.

O formato 0"x" é um bom exemplo da diferença entre valor e exibição: a célula mostra "3x" (um múltiplo, por exemplo) e continua servindo em contas. Se você digitasse o texto "3x", qualquer conta com ele daria erro #VALOR!. O traço no lugar do zero (terceira parte do código) deixa relatórios mais limpos.

Datas são números

O Excel guarda cada data como um número inteiro: o dia 1 é 01/01/1900, e cada dia seguinte soma 1. A data de 22/08/2026, por exemplo, é o número 46.256; formate essa célula como Geral e é isso que aparece. A hora é a fração do dia: 0,5 é meio-dia.

Como data é número, dá para fazer contas com ela:

=HOJE()+100              → a data daqui a 100 dias
=DATA(2026;12;31)-HOJE() → quantos dias faltam para o fim do ano

Com HOJE() = 22/08/2026, a primeira fórmula devolve 30/11/2026 e a segunda, 131 dias.

C2: =A2+B2                  → 19/05/2025   (vencimento)
D2: =C2                     → formato dddd: segunda-feira
E2: =DATA(2025;12;31)-A2    → 256           (dias até o fim do ano)

Tabela 1.8. Códigos de formato de data. No Excel em inglês o ano é `yy`/`yyyy`.

Formatação condicional

A formatação condicional (Página Inicial › Formatação Condicional) pinta células por regra: escalas de cor, barras de dados, ícones ou uma fórmula que destaca exceções. Ela é aprofundada na Sessão 4 do módulo, quando o painel de análise destaca as regiões abaixo da média.

1.5Primeiras fórmulas

Toda fórmula começa com =. A partir daí, você combina valores, endereços, operadores e funções.

Tabela 1.9. Operadores. Em alguns teclados o ^ é tecla "morta": tecle duas vezes ou siga com espaço.

A ordem de cálculo é a da matemática: primeiro parênteses, depois potência, depois multiplicação e divisão, por fim soma e subtração. =8,5/6+4 dá 5,42; =8,5/(6+4) dá 0,85. Na Barra de Fórmulas, cada par de parênteses ganha uma cor, e o Excel destaca o par correspondente quando o cursor passa por ele: é assim que se encontra um parêntese sobrando ou faltando.

=B2-C2/B2      → 49,40   errado: divide 30 por 50 e subtrai de 50
=(B2-C2)/B2    → 40,0%   certo: primeiro a diferença, depois a divisão

Argumentos e o botão fx

Os itens dentro dos parênteses de uma função são os argumentos. No Excel em português eles são separados por ponto e vírgula; no Excel em inglês, por vírgula. O separador vem da configuração regional do Windows, não do idioma do Excel.

Ao digitar uma função, a dica abaixo da célula mostra os argumentos esperados, com o atual em negrito. Argumentos entre colchetes são opcionais. Para quem está começando, o botão fx (ao lado da Barra de Fórmulas) abre um assistente: explica cada argumento, mostra resultados parciais e até coloca as aspas nos textos.

Referências relativas e absolutas

Uma referência como D6 é relativa: ao copiar a fórmula, ela anda junto. Na prática, o Excel guarda "a célula duas colunas à esquerda, na mesma linha". O cifrão trava a parte que não deve andar. A tecla F4, com o cursor sobre a referência, alterna entre as quatro formas:

Tabela 1.10. Referências relativas, absolutas e mistas.

A regra prática do professor: se a referência não precisa andar, trave. É o caso de uma nota de corte, de uma taxa ou de um total geral usado como denominador.

C6: =$B6*(1-C$5)      → copiar para C6:E8

As funções para começar

Tabela 1.11. Primeiras funções.

SE: a primeira decisão do modelo

O SE é a primeira função que faz o modelo "decidir": a mesma célula dá respostas diferentes conforme uma condição.

=SE(teste_lógico; valor_se_verdadeiro; valor_se_falso)
=SE(C6>=7; "Aprovado"; "Reprovado")

Textos dentro de uma fórmula vão entre aspas duplas. Sem aspas, o Excel procura uma função ou um nome chamado Aprovado e devolve #NOME?.

O teste lógico sozinho também é uma fórmula: =C6>=7 devolve VERDADEIRO ou FALSO. Em contas, VERDADEIRO vale 1 e FALSO vale 0. Um SE pode ficar dentro de outro para criar três ou mais resultados.

=SE(C6>=7; "Aprovado";
    SE(C6>=5; "Recuperação"; "Reprovado"))

Por isso =SOMA((D6:D11="Aprovado")*1) conta os aprovados: o *1 (ou -- na frente dos parênteses) transforma os VERDADEIROS em 1. Essa ideia volta com força nas funções de matriz do Capítulo 3.

1.6Uma fórmula, vários resultados: o despejo

O professor apresenta já na primeira aula uma das maiores mudanças do Excel recente. No Microsoft 365, uma fórmula pode devolver um intervalo inteiro, que derrama (spill) pelas células vizinhas. Em vez de escrever =C6*D6 e copiar até a linha 10, você escreve uma vez:

=C6:C10*D6:D10

O resultado ocupa E6:E10. Só E6 contém a fórmula; as outras células mostram a fórmula "fantasma", em cinza, na Barra de Fórmulas. Esse tipo de resultado se chama matriz dinâmica (dynamic array).

A vantagem é a manutenção. Numa fórmula copiada célula a célula, inserir uma linha no meio da base exige lembrar de copiar a fórmula de novo; numa fórmula despejada, o intervalo cresce sozinho e tudo o que aponta para E6# acompanha. Em bases grandes, uma fórmula no lugar de 100 mil também deixa o arquivo mais leve.

O tema volta em profundidade no Capítulo 3, com ÚNICO, FILTRO, CLASSIFICAR e a família EMPILHAR.

1.7Validação de dados: menu suspenso

A validação de dados restringe o que pode ser digitado numa célula. O uso mais comum é o menu suspenso: o usuário escolhe uma opção de uma lista em vez de digitar. Isso evita erros como "Sul", "sul" e "Su" convivendo na mesma base, padroniza a entrada para filtros e somas e é a base de qualquer simulador: a escolha no menu dirige as fórmulas.

A janela tem três guias:

• Configurações: o tipo de restrição. Além de Lista, há Número inteiro, Decimal, Data, Hora, Comprimento do texto e Personalizado (por fórmula). Exemplo: número inteiro entre 1 e 10.
• Mensagem de entrada: um aviso que aparece quando a célula é selecionada, orientando o preenchimento. Só aparece se tiver corpo de mensagem.
• Alerta de erro: o que acontece quando alguém digita fora da regra.

Tabela 1.12. Estilos do alerta de erro.

A lista de origem pode estar em outra aba. Uma prática comum é reunir todas as listas numa aba Auxiliar. Também dá para dar um nome ao intervalo da lista (selecione-o, digite LST_Regiao na Caixa de Nome e tecle Enter) e usar =LST_Regiao como fonte da validação. Prefixos como LST_ para listas e tbl_ para Tabelas fazem todos os nomes do mesmo tipo aparecerem juntos quando você começa a digitar.

1.8Exercícios resolvidos

Os três exercícios da Sessão 1 usam o arquivo EBP_Tools_Fundamentos_2026_Aluno.xlsx. As células amarelas são as que o aluno preenche.

Exercício 1: digitar, formatar e somar (aba F2_Digitar)

Tarefa: completar a tabela de itens, formatar o preço como moeda, calcular o total de cada linha (Qtd × Preço) e o total geral, com o cabeçalho em negrito. Desafio: uma coluna com o percentual de cada item no total.

Caminho clássico. Em E6, =C6*D6; copie para E7:E10 com a alça de preenchimento (ou com Ctrl+C, seleção de E7:E10 e Ctrl+V). Em E12, =SOMA(E6:E10). Formate D6:E12 como moeda com Ctrl+Shift+4. No desafio, =E6/$E$12, com o total geral travado, copiado para baixo e formatado com Ctrl+Shift+5.

Caminho moderno. Uma fórmula só para a coluna inteira:

E6:  =C6:C10*D6:D10        → derrama em E6:E10
E12: =SOMA(E6#)            → R$ 361,35
F6:  =E6#/SOMA(E6#)        → derrama em F6:F10

Tabela 1.13. Resultado do Exercício 1.

Exercício 2: estatísticas e SE (aba F3_Formulas)

Tarefa: calcular soma, média, máximo, mínimo e quantidade das notas, e a situação de cada aluno com SE (aprovado com nota ≥ 7). Desafio: contar os aprovados com CONT.SE e calcular o percentual de aprovação.

C14: =SOMA($C$6:$C$11)        → 42,0
C15: =MÉDIA($C$6:$C$11)       → 7,0
C16: =MÁXIMO($C$6:$C$11)      → 9,0
C17: =MÍNIMO($C$6:$C$11)      → 4,0
C18: =CONT.NÚM($C$6:$C$11)    → 6
D6:  =SE(C6:C11>=$G$6;$H$6;$H$7)

A nota de corte (7) e os rótulos ficam fora da fórmula, em G6, H6 e H7, como recomendado na seção 1.5. A fórmula da situação é uma só e derrama pelas seis linhas. O enunciado também aceita a versão simples, =SE(C6>=7;"Aprovado";"Reprovado") em D6, copiada para baixo.

Para o desafio, conte quantos "Aprovado" há no intervalo despejado e divida pelo total de alunos:

C20: =CONT.SES(D6#;"Aprovado")                      → 4
C21: =CONT.SES(D6#;"Aprovado")/CONT.VALORES(D6#)    → 66,7%

O enunciado cita CONT.SE (COUNTIF). O professor prefere usar sempre CONT.SES (COUNTIFS), mesmo com um critério só: a forma no plural aceita quantos critérios forem necessários e evita trocar de função quando a pergunta cresce. O mesmo vale para SOMASES e MÉDIASES, do Capítulo 2.

Com o critério em uma célula, o modelo responde na hora: com nota de corte 8, a aprovação cai para 33,3% (Ana e Elisa); com 4, todos passam.

Exercício 3: menu suspenso (aba F4_Dropdown)

Tarefa: criar um menu suspenso em C6 com as regiões listadas em H6:H10. Desafio: um segundo menu com as categorias Eletrônicos, Casa e Esporte.

O passo a passo é o do Exemplo 1.9: Dados › Validação de Dados › Lista, com a fonte =$H$6:$H$10. Para o desafio, digite as categorias numa coluna livre (ou na aba Auxiliar), crie a validação em outra célula e aponte a fonte para essa nova lista. Para listas curtas e estáveis, a fonte pode ser digitada direto, separada por ponto e vírgula: Eletrônicos;Casa;Esporte.

1.9Exercícios propostos

Use a aba F1_Explorar (base de 51 vendas, linhas 6 a 56, colunas B a F).

1. Sem usar o mouse, vá até a última venda da base e depois volte para B5. Quais atalhos você usou? Use a Caixa de Nome para ir até H51.
2. Calcule a soma da coluna Qtd e o número de vendas da base.
3. Aplique um filtro e deixe visível só a região Nordeste. Compare o resultado de =SOMA(E6:E56) com o de =SUBTOTAL(9;E6:E56).
4. Calcule o maior preço, o menor preço e o preço médio da base. Que produto tem o maior preço?
5. Quantas vendas têm quantidade maior ou igual a 30?
6. A coluna Data está como texto. Converta a data da linha 6 em número e formate-a como dd/mm/aaaa. Que número aparece com o formato Geral?
7. A fórmula =$B4*C$2 está em D4. Como ela fica ao ser copiada para E5?
8. Calcule o faturamento total da base (Qtd × Preço, somado) com uma única fórmula.
9. Crie um menu suspenso com as regiões da base e, ao lado, use =CONT.SES(D6:D56;célula_do_menu) para mostrar quantas vendas a região escolhida teve.

Material de apoio às aulas, de uso exclusivo dos alunos da BTC. É proibido reproduzir ou compartilhar este conteúdo sem autorização expressa da BTC. Ler o disclaimer

No Capítulo 1 você conheceu as peças soltas: células, referências, primeiras fórmulas e o despejo. Este capítulo trata de duas tarefas que aparecem em quase toda planilha de negócio: organizar uma base de dados de forma que ela possa crescer sem quebrar nada e cruzar essa base com outras para responder perguntas. Quanto vendeu o Sul em janeiro? Essa região bateu a meta? Qual é a categoria do produto P004? Que comissão cabe a uma receita de R$ 37.500?

As ferramentas são poucas e muito usadas: a Tabela (Ctrl+T) e suas referências estruturadas, a família de condicionais (SE, SES, PARÂMETRO, E, OU, NÃO), as agregações condicionais (SOMASES, CONT.SES, MÉDIASES e SOMARPRODUTO), o tratamento de erros e a busca com PROCX. No fim, a tabela dinâmica, ótima para explorar, e o PROCV, que você vai encontrar em planilhas antigas e precisa saber ler.

Todos os exemplos usam o arquivo EBP_Tools_2026_Aluno.xlsx. As abas Vendas, Produtos, Vendedores e Faixas são as fontes de dados; a base de vendas tem 560 linhas, uma por venda, de janeiro a dezembro de 2025. As abas de exercício deste capítulo são T1_Tabelas, T2_Exercicio e T4_Exercicio.

2.1Tabela ou intervalo: por que estruturar

Um intervalo comum, como A1:D500, é uma fotografia dos dados. Uma fórmula como =SOMA(C5:C41) soma exatamente aquelas células. Se entram 20 vendas novas na linha 42 em diante, a soma não as enxerga; as colunas calculadas também não se estendem sozinhas. Alguém precisa lembrar de arrastar fórmulas e corrigir intervalos, e quem lê C5:C41 ainda tem de ir até a base descobrir o que é a coluna C.

A Tabela (guia Inserir › Tabela, ou Ctrl+T) transforma o intervalo num objeto que cresce. O professor resume: no curso, "a Tabela é o nome do jogo". Ela é a base das fórmulas deste capítulo, das tabelas dinâmicas e do Power Query, visto no módulo de Automatização.

Tabela 2.1. Intervalo × Tabela (slide 14).

O arquivo do aluno já traz as bases como Tabelas, com os nomes Vendas, Produtos, Vendedores e Faixas. Para seguir as fórmulas dos slides e desta apostila, renomeie-as com o prefixo: tbl_Vendas, tbl_Produtos, tbl_Vendedores e tbl_Faixas.

2.2Referências estruturadas e nomes

Com a Tabela criada, as fórmulas passam a falar a língua da base. Ao apontar com o mouse ou com as setas para dentro da Tabela, o Excel escreve sozinho a referência estruturada.

Tabela 2.2. Sintaxe das referências estruturadas no Excel em português. No Excel em inglês: [#Headers], [#All], [#Data]; o separador dentro dos colchetes duplos é a vírgula.

O @ significa "esta linha". Ele aparece nas fórmulas escritas dentro da Tabela, como as colunas calculadas. Fórmulas de agregação, fora da Tabela, usam a coluna inteira e não têm @.

=CONT.VALORES(tbl_Vendas[id_Produto])   → 560
=SOMA(tbl_Vendas[Receita])              → 5.274.448,66
=SOMA(tbl_Vendas[Qtd])                  → 7.400

Intervalos nomeados

Qualquer intervalo pode ganhar um nome: selecione-o, digite o nome na Caixa de Nome e tecle Enter, ou use o Gerenciador de Nomes. Um nome também pode apontar para um despejo: =Auxiliar!$D$4# cresce e encolhe com a fórmula de origem. O professor usa prefixos por tipo: tbl_ para Tabelas e LST_ para listas de validação. Digitando =LST aparecem todas as listas; digitando =tbl, todas as Tabelas.

Compare a mesma soma escrita de duas formas:

=SOMASES(F1_Explorar!E7:E93; F1_Explorar!D7:D93; C6; F1_Explorar!C7:C93; C7)

=SOMASES(tbl_Vendas[Qtd];
         tbl_Vendas[Regiao];  C6;
         tbl_Vendas[Produto]; C7)

A segunda diz o que soma, sob quais critérios, e nunca deixa de fora uma venda nova. O Capítulo 3 mostra como gerar a lista de regiões de um menu suspenso a partir da própria Tabela, com ÚNICO e CLASSIFICAR.

2.3A escada da condicional: SE, SES e PARÂMETRO

O SE (IF), visto no Capítulo 1, escolhe entre dois caminhos. Com três ou mais resultados, a solução tradicional é colocar um SE dentro do outro, e a fórmula vai ficando ilegível. O Excel moderno tem duas alternativas:

Tabela 2.3. A escada da condicional (slide 17).

SES: classificação por faixas

O SES testa de cima para baixo e para no primeiro teste verdadeiro. A ordem importa: em faixas, comece pela ponta mais alta (ou pela mais baixa) e siga em sequência. Quem chega ao segundo teste já falhou no primeiro; por isso não é preciso escrever "maior que 40.000 e menor que 80.000".

=SES([@Receita]>=Auxiliar!$H$2; "Grande";
     [@Receita]>=Auxiliar!$H$3; "Média";
     VERDADEIRO;                "Pequena")

PARÂMETRO: um valor, várias opções

O PARÂMETRO (SWITCH) compara uma única expressão com uma lista de valores exatos. Serve para "traduções" curtas, em que não há faixa, só correspondência.

=PARÂMETRO([@Categoria];
           "Eletrônicos"; "Tecnologia";
           "Escritório";  "Corporativo";
           "Varejo")

O PARÂMETRO não aceita comparações como >=, só igualdade. Para faixas, SES ou PROCX aproximado. Para listas longas de correspondências, uma tabela de-para com PROCX (seção 2.8) é mais fácil de manter do que um PARÂMETRO gigante.

2.4E, OU, NÃO: combinar condições

Tabela 2.4. Operadores lógicos. E só dá FALSO se algum teste falhar; OU só dá FALSO se todos falharem.

Essas funções devolvem VERDADEIRO ou FALSO, não um texto. Para mostrar "Bônus" ou "-", embrulhe-as num SE. Elas aparecem dentro de SE e SES, em formatação condicional e em validação de dados.

=SE(E([@Receita]>=20000; [@Regiao]="Sul"); "Bônus"; "-")

O NÃO é útil para validar entradas. No exemplo das notas trabalhado em aula, um texto digitado no lugar da nota passava no teste >=7, porque o Excel considera texto maior que qualquer número. A saída é testar antes se o valor é número:

=SES(NÃO(ÉNÚM(C6)); "Nota fora do formato";
     C6>=7;         "Aprovado";
     C6>=5;         "Recuperação";
     VERDADEIRO;    "Reprovado")

2.5SOMASES e a família condicional

Somar, contar ou tirar a média de uma parte da base é a pergunta mais comum de qualquer análise. A família de funções no plural resolve todas com o mesmo padrão:

=SOMASES(intervalo_soma; intervalo_critério1; critério1; [intervalo_critério2; critério2]; ...)
=CONT.SES(intervalo_critério1; critério1; ...)
=MÉDIASES(intervalo_média; intervalo_critério1; critério1; ...)

• SOMASES (SUMIFS) soma; CONT.SES (COUNTIFS) conta linhas; MÉDIASES (AVERAGEIFS) tira a média. O CONT.SES não tem intervalo de soma: começa direto pelos critérios.
• Cada critério vem em par: onde testar e o quê testar. Todos precisam ser atendidos ao mesmo tempo (é um E).
• Critério de igualdade dispensa o =: basta a célula ou o texto entre aspas.

=SOMASES(tbl_Vendas[Receita]; tbl_Vendas[Regiao]; "Sul"; tbl_Vendas[Mes]; "Jan")   → 50.450,67
=CONT.SES(tbl_Vendas[Regiao]; "Sul"; tbl_Vendas[Mes]; "Jan")                        → 9
=MÉDIASES(tbl_Vendas[Receita]; tbl_Vendas[Regiao]; "Sul"; tbl_Vendas[Mes]; "Jan")  → 5.605,63

Critérios com operadores

O critério pode ser um número, um texto ou uma comparação. A comparação vai entre aspas; para usar o valor de uma célula, junte o operador com &.

Tabela 2.5. Tipos de critério, aplicados a SOMASES(tbl_Vendas[Receita]; coluna; critério). Nos três primeiros a coluna testada é Regiao ou Receita; no último, Produto.

Filtrar por período, com critérios como ">="&DATA(2025;1;1) e "<="&FIMMÊS(...) (o "sanduíche de datas"), exige que a coluna de datas seja numérica. Na aba Vendas, a coluna Data está como texto; o Capítulo 3 trata da conversão e das funções de data.

2.6SOMARPRODUTO: pesos e condições multiplicadas

O SOMARPRODUTO (SUMPRODUCT) multiplica matrizes elemento a elemento e soma tudo. O uso clássico é o de pesos: quantidade × preço, linha a linha, somado no fim, sem precisar de coluna auxiliar.

=SOMARPRODUTO(tbl_Vendas[Qtd]; tbl_Vendas[PrecoUnit]-tbl_Vendas[CustoUnit])   → 1.573.188,66

=SOMARPRODUTO(tbl_Vendas[Qtd]*tbl_Vendas[PrecoUnit]*(tbl_Vendas[Produto]="Notebook Pro"))
 / SOMASES(tbl_Vendas[Qtd]; tbl_Vendas[Produto]; "Notebook Pro")
→ 1.636.792,37 / 483 = 3.388,80

Condições que viram 1 e 0

O segundo uso é a soma condicional. Uma comparação entre parênteses, como (tbl_Vendas[Regiao]="Sul"), devolve uma coluna de VERDADEIRO e FALSO. Numa multiplicação, VERDADEIRO vale 1 e FALSO vale 0. Multiplicando a receita por várias condições, só sobra a receita das linhas que passam em todas.

=SOMARPRODUTO(tbl_Vendas[Receita]*(tbl_Vendas[Regiao]="Sul")*(tbl_Vendas[Mes]="Jan"))   → 50.450,67

É o mesmo número do SOMASES do Exemplo 2.6. A multiplicação faz o papel do E. Para um OU, some as condições: ((tbl_Vendas[Regiao]="Sul")+(tbl_Vendas[Regiao]="Sudeste")) vale 1 nas linhas de uma das duas regiões.

Tabela 2.6. SOMASES ou SOMARPRODUTO. Em aula, a tentativa de passar `tbl_Vendas[Qtd]*tbl_Vendas[PrecoUnit]` como intervalo de soma do SOMASES deu erro: o SOMASES só aceita intervalos, não contas.

2.7Tratamento de erros

Cada erro do Excel diz o que deu errado. Antes de tratar, entenda a causa.

Tabela 2.7. Os erros mais comuns.

Um erro se propaga: qualquer conta que usa uma célula com erro também dá erro. Uma planilha cheia de #REF! passa a impressão de modelo quebrado, e quem a recebe deixa de confiar nos números.

A função SEERRO (IFERROR) devolve o resultado da fórmula ou, se ela der erro, um valor alternativo:

=SEERRO(fórmula; valor_se_erro)

=SEERRO(MÉDIASES(tbl_Vendas[Receita]; tbl_Vendas[Regiao]; A12; tbl_Vendas[Mes]; $B$4); 0)

Outras funções do mesmo grupo: SENÃODISP (IFNA) trata apenas o #N/D; ÉERROS (ISERROR) devolve VERDADEIRO para qualquer erro, útil como teste lógico dentro de um SE. Cuidado com a parecida ÉERRO (ISERR), que não considera o #N/D como erro.

2.8Busca: do PROCV ao PROCX

Buscar é levar uma informação de uma tabela para outra. A base de vendas tem o código do vendedor, mas não o gerente; a lista do exercício tem o código do produto, mas não o nome. O que liga as duas tabelas é a chave.

Por mais de uma década, a função de busca foi o PROCV (VLOOKUP). Desde 2019 o Excel tem o PROCX (XLOOKUP), que resolve as limitações do PROCV. O curso constrói tudo com PROCX; o PROCV aparece na seção 2.10, para leitura.

=PROCX(valor_procurado; matriz_procurada; matriz_retorno; [se_não_encontrada]; [modo_correspondência]; [modo_pesquisa])

Tabela 2.8. Os argumentos do PROCX. Os três últimos são opcionais.

=PROCX("V03"; tbl_Vendedores[id_Vendedor]; tbl_Vendedores[Gerente]; "não achou")   → Rita Alves
=PROCX("V09"; tbl_Vendedores[id_Vendedor]; tbl_Vendedores[Gerente]; "não achou")   → não achou

=PROCX("Elisa Cruz"; tbl_Vendedores[Vendedor]; tbl_Vendedores[id_Vendedor])        → V05

Busca aproximada: faixas

Comissões, alíquotas de imposto e ratings costumam vir em faixas. Na aba Faixas, cada linha traz o piso da faixa e a comissão correspondente: de 0 a 19.999,99, 2%; de 20.000 a 49.999,99, 3%; de 50.000 a 99.999,99, 4,5%; a partir de 100.000, 6%. O modo -1 faz o PROCX achar a faixa, não o valor exato.

=PROCX(37500;    tbl_Faixas[Min]; tbl_Faixas[Comissao]; ; -1)   → 3,0%
=PROCX(100000;   tbl_Faixas[Min]; tbl_Faixas[Comissao]; ; -1)   → 6,0%   (achou o valor exato)
=PROCX(19999,99; tbl_Faixas[Min]; tbl_Faixas[Comissao]; ; -1)   → 2,0%

=PROCX("Cadeira Ergo"; tbl_Vendas[Produto]; tbl_Vendas[Receita]; ; 0; -1)   → 2.029,34

Retorno de várias colunas

A matriz de retorno pode ter mais de uma coluna. Nesse caso, o PROCX devolve a linha encontrada inteira e despeja o resultado nas células à direita:

=PROCX("P004"; tbl_Produtos[id_Produto]; tbl_Produtos[[Produto]:[CustoUnit]])   → Cadeira Ergo | Escritório | 700

O intervalo [[Produto]:[CustoUnit]] pega as colunas vizinhas de Produto até CustoUnit. Para devolver colunas que não são vizinhas, ou em outra ordem, será preciso juntar pedaços com EMPILHARH, assunto do Capítulo 3.

2.9Tabela dinâmica: usos e limites

A tabela dinâmica (pivot table) resume uma base arrastando campos, sem escrever fórmula. É a forma mais rápida de explorar dados desconhecidos.

O que mais ela faz com poucos cliques:

• Mudar a operação: em Configurações do Campo de Valor, troque Soma por Contagem, Média, Máximo...
• Hierarquias: Regiao e, abaixo dela, Vendedor nas Linhas. A ordem dos campos na área define a hierarquia.
• Agrupar datas: com uma coluna de datas numérica nas Linhas ou Colunas, clique com o botão direito › Agrupar e escolha Meses, Trimestres e Anos. (A coluna Data da aba Vendas está como texto; converta-a antes somando zero, como no Capítulo 1.)
• Segmentação de Dados (slicer): Analisar da Tabela Dinâmica › Inserir Segmentação de Dados cria botões de filtro clicáveis. Em Conexões de Relatório, a mesma segmentação filtra várias dinâmicas ao mesmo tempo, a base de um painel simples.
• Gráfico Dinâmico e Campo Calculado (por exemplo, Qtd × PrecoUnit quando a base não tem Receita).

Onde ela limita

A tabela dinâmica não lê a base a cada mudança. Ela guarda uma cópia dos dados (o cache) no momento em que é criada ou atualizada e trabalha sobre essa cópia.

Tabela 2.9. Usos e limites da tabela dinâmica (slide 27).

Por outro lado, por trabalhar sobre uma cópia, a dinâmica pode resumir uma base que nem está no arquivo, carregada pelo Power Query, o que deixa o arquivo bem mais leve. O módulo de Automatização volta a esse ponto. A função AGRUPARPOR (GROUPBY), que monta um resumo como o da dinâmica com uma fórmula viva, é assunto do Capítulo 4.

2.10PROCV: saber ler o legado

Você vai herdar planilhas cheias de PROCV. Saiba ler, mas construa com PROCX.

=PROCV(valor_procurado; matriz_tabela; núm_índice_coluna; [procurar_intervalo])
=PROCV($B2; Base!$A:$D; 3; FALSO)

Leitura: "procure o valor de B2 na primeira coluna de Base!A:D e devolva a 3ª coluna dessa matriz, com correspondência exata (FALSO)". O PROCV percorre a primeira coluna de cima para baixo e para na primeira ocorrência.

Tabela 2.10. Por que o PROCV ficou para leitura (slides 24 e 28).

O PROCV tem um irmão, o PROCH (HLOOKUP), que faz a mesma busca numa base deitada: procura na primeira linha e devolve a linha de número informado. É raro em bases bem montadas, que têm um registro por linha.

=PROCV(B2; $H$2:$I$20; 2; FALSO)

=PROCX(B2; tbl_DePara[Digitado]; tbl_DePara[Padronizado]; B2)

2.11Exercícios resolvidos

Os três exercícios usam o arquivo EBP_Tools_2026_Aluno.xlsx. As células amarelas são as que o aluno preenche. Antes de começar, renomeie as Tabelas de dados para tbl_Vendas, tbl_Produtos, tbl_Vendedores e tbl_Faixas (seção 2.1).

Exercício 1: da base crua à Tabela viva (aba T1_Tabelas)

Tarefa: converter o intervalo A4:D22 em Tabela, nomeá-la e criar a coluna Margem = Qtd × (PrecoUnit − CustoUnit). Depois, acrescentar linhas e ver a Tabela absorvê-las. O enunciado da aba sugere o nome VendasT1; com o prefixo recomendado pelo professor, fica tbl_VendasT1.

1. Clique em A5, tecle Ctrl+T e confirme o intervalo com cabeçalhos.
2. Em Design da Tabela › Nome da Tabela, digite tbl_VendasT1.
3. Em E5, digite = e use as setas para apontar Qtd, PrecoUnit e CustoUnit da mesma linha. A fórmula fica com @ e preenche as 18 linhas.

4. Fora da Tabela, deixando uma linha em branco, calcule os totais com referências estruturadas:

B24: =SOMA(tbl_VendasT1[Qtd])       → 217
B25: =SOMA(tbl_VendasT1[Margem])    → 53.971,98

5. Teste o crescimento: copie a linha 22 e cole-a na linha 23, logo abaixo da Tabela. A Tabela se estende, a margem da linha nova aparece sozinha e os totais vão para 230 e 59.064,73. Desfaça com Ctrl+Z antes de seguir; se a linha de totais estivesse colada na Tabela, ela teria bloqueado o crescimento.

Exercício 2: agregação condicional (aba T2_Exercicio)

Tarefa: para o mês escolhido em B4 (Jan), calcular por região a receita total (SOMASES) e o número de vendas (CONT.SES), e classificar o desempenho com SES lendo os limites de uma tabela de critérios: Alta a partir de R$ 80.000, Média a partir de R$ 40.000, Baixa abaixo disso. Depois, trocar um limite e ver a classificação mudar. Desafio: ticket médio por região com MÉDIASES e destaque, com formatação condicional, das regiões abaixo da média geral.

Passo 1: tabela de critérios. A aba não traz os limites; monte-os ao lado do resumo, em F6:G9 (Faixa e Limite mínimo), com os limites em amarelo, por serem entradas. Valores usados em aula: Alta 80.000, Média 40.000, Baixa 0.

Passo 2: receita e número de vendas. Em B7 e C7, com a região travada na coluna e o mês travado por inteiro, e depois copiadas até a linha 11:

B7: =SOMASES(tbl_Vendas[Receita]; tbl_Vendas[Regiao]; $A7; tbl_Vendas[Mes]; $B$4)
C7: =CONT.SES(tbl_Vendas[Regiao]; $A7; tbl_Vendas[Mes]; $B$4)

Passo 3: classificação. Em D7, copiada para baixo:

D7: =SES(B7>=$G$7; $F$7;
         B7>=$G$8; $F$8;
         VERDADEIRO; $F$9)

Tabela 2.11. Resultado do Exercício 2 para janeiro.

Passo 4: troque um limite. Com o limite de Alta em 90.000 (G7), o Nordeste (87.460,09) cai para Média; só o Sudeste continua Alta. Com o limite de Média em 50.000 (G8), o Centro-Oeste (41.675,93) vira Baixa. Nenhuma fórmula foi editada.

Desafio: ticket médio e destaque. Em E7, copiada para baixo, e a média geral em E13:

E7:  =MÉDIASES(tbl_Vendas[Receita]; tbl_Vendas[Regiao]; $A7; tbl_Vendas[Mes]; $B$4)
E13: =MÉDIASES(tbl_Vendas[Receita]; tbl_Vendas[Mes]; $B$4)     → 9.915,44

A média geral é a média das 38 vendas de janeiro (376.786,75 ÷ 38), não a média dos cinco tickets (9.865,59): regiões com mais vendas pesam mais.

Para o destaque, selecione E7:E11 e use Página Inicial › Formatação Condicional › Regras de Realce das Células › É Menor do que, com a referência =$E$13 e preenchimento vermelho claro. Duplique a regra em Gerenciar Regras para "É Maior do que", em verde. Para pintar a linha inteira (A:E), use Nova Regra › Usar uma fórmula com =$E7<$E$13 aplicada a $A$7:$E$11: a coluna E travada, a linha livre.

Exercício 3: busca e cruzamento (aba T4_Exercicio)

Tarefa: completar a lista de produtos com nome e categoria (PROCX), a comissão pela faixa da receita (PROCX aproximado) e, embaixo, a receita de um produto num mês (busca 2D). Tratar os não encontrados com se_não_encontrada. Desafio: um único PROCX que devolva várias colunas.

Nome e categoria. A chave é o id_Produto, e a busca é feita no cadastro, tbl_Produtos. Uma fórmula em C5 resolve as oito linhas:

C5: =PROCX(A5:A12; tbl_Produtos[id_Produto]; tbl_Produtos[Produto];   "não achou")
D5: =PROCX(A5:A12; tbl_Produtos[id_Produto]; tbl_Produtos[Categoria]; "não achou")

Desafio: várias colunas de uma vez. Com a matriz de retorno [[Produto]:[Categoria]], o PROCX devolve as duas colunas. Como a busca de uma lista inteira com várias colunas de retorno não funciona, a fórmula vai em C5 com uma chave só ($A5) e é copiada até C12; cada uma despeja em duas células. O slide propõe incluir também o custo, com tbl_Produtos[[Produto]:[CustoUnit]]; aqui isso invadiria a coluna E, reservada à comissão, então faça essa versão numa área livre à direita.

Comissão. A receita de cada linha está em B; os pisos das faixas, em tbl_Faixas[Min]. O modo -1 pega o maior piso que não passa da receita:

E5: =PROCX(B5:B12; tbl_Faixas[Min]; tbl_Faixas[Comissao]; ; -1)

Tabela 2.12. Resultado do Exercício 3. A comissão em reais (taxa × receita, `=E5#*B5:B12`) não é pedida no enunciado, mas foi calculada em aula.

Busca 2D: receita de um produto num mês. Duas dimensões (produto e mês) viram dois critérios de um SOMASES:

B19: =SOMASES(tbl_Vendas[Receita]; tbl_Vendas[Produto]; B17; tbl_Vendas[Mes]; B18)   → 77.538,72

O Notebook Pro teve uma única venda em janeiro. A mesma resposta sai com =SOMARPRODUTO(tbl_Vendas[Receita]*(tbl_Vendas[Produto]=B17)*(tbl_Vendas[Mes]=B18)). Outros exemplos para testar: Cadeira Ergo em março, 43.178,86; Monitor 27 em agosto, 84.262,16.

2.12Exercícios propostos

Use a base tbl_Vendas (aba Vendas) e as tabelas de apoio do arquivo do aluno.

1. Qual a receita total da região Nordeste no ano? E quantas vendas ela teve?
2. Qual a receita total de vendas da categoria Eletrônicos com quantidade a partir de 10 unidades?
3. Qual o ticket médio (receita média por venda) do vendedor Hugo Barros em março?
4. Escreva uma coluna calculada Faixa em tbl_Vendas que classifique a quantidade: a partir de 20 unidades, "Atacado"; a partir de 10, "Lote"; abaixo de 10, "Varejo". Quantas vendas caem em cada faixa?
5. Usando SOMARPRODUTO, calcule a margem total (Qtd × (PrecoUnit − CustoUnit)) da região Sul.
6. Com PROCX, traga o gerente da vendedora Gabriela Pinto, partindo do nome (aba Vendedores). Que argumento você usaria para mostrar "sem cadastro" se o nome não existir?
7. Qual comissão a tabela de faixas daria para receitas de 19.999, 50.000 e 120.000? Escreva a fórmula para uma delas.
8. Monte uma tabela dinâmica com Categoria nas Linhas, Receita em Valores e Regiao no Filtro. Qual a receita de Escritório no Sudeste?
9. Uma planilha herdada tem =PROCV(F2;Produtos!A:D;4;FALSO). O que ela devolve para F2 = "P009"? Reescreva com PROCX.

Material de apoio às aulas, de uso exclusivo dos alunos da BTC. É proibido reproduzir ou compartilhar este conteúdo sem autorização expressa da BTC. Ler o disclaimer

Até aqui, cada fórmula respondia a uma célula. Neste capítulo a lógica muda: uma única fórmula responde a uma pergunta inteira e devolve quantas linhas e colunas forem necessárias. "Quais produtos foram vendidos no Sudeste e quanto cada um faturou?" deixa de ser uma coluna digitada mais uma fórmula arrastada até o fim e passa a ser duas fórmulas que se ajustam sozinhas quando a base muda. O professor resume a mudança numa frase: antes, uma fórmula por célula; agora, uma fórmula por pergunta.

O Capítulo 1 apresentou o despejo, a referência # e o erro #DESPEJAR!. Aqui você vai usar esse motor de verdade, com as funções que nasceram para ele: SEQUÊNCIA, ÚNICO, a família EMPILHAR, FILTRO e CLASSIFICAR. Com elas se monta um mini-simulador, um painel dirigido por um menu suspenso, sem macro. Na segunda metade, o mesmo espírito chega a texto e datas: separar nomes, extrair códigos e montar calendários com uma fórmula, em vez de cinco funções aninhadas. No meio do caminho fica um alerta: algumas funções clássicas, as voláteis, deixam a planilha lenta e difícil de auditar, e quase todas já têm substituto moderno.

3.1O motor do despejo: uma fórmula por pergunta

Lembre o essencial do Capítulo 1: no Microsoft 365, uma fórmula pode devolver um intervalo inteiro, que derrama (spill) pelas células vizinhas; só a primeira célula guarda a fórmula, e B8# refere-se a todo o resultado, qualquer que seja o tamanho. O que muda agora é o uso. O intervalo despejado deixa de ser só um resultado e vira matéria-prima de outras fórmulas.

Tabela 3.1. A virada das matrizes dinâmicas, segundo o slide de abertura da Sessão 3.

Três usos do # aparecem o tempo todo neste capítulo:

=SOMA(C8#)                         → soma tudo o que C8 despejou
=C8#/SOMA(C8#)                     → uma conta sobre cada item; derrama do mesmo tamanho
=SOMASES(tbl_Vendas[Receita];
         tbl_Vendas[Produto];B8#)  → um critério para cada item da lista; derrama alinhado

O terceiro é o mais poderoso. Quando você entrega a SOMASES (SUMIFS), vista no Capítulo 2, uma lista de critérios em vez de um só, ela devolve uma soma para cada item. O professor chama isso de "pegar carona" no despejo: a coluna de receita acompanha a lista de produtos, linha a linha, e cresce junto com ela.

Tabela 3.2. Os erros mais comuns com matrizes dinâmicas.

3.2SEQUÊNCIA e ÚNICO

SEQUÊNCIA: índices, escalas e calendários

SEQUÊNCIA (SEQUENCE) gera uma grade de números. Os argumentos são linhas; [colunas]; [início]; [passo]; os três últimos são opcionais e valem 1 quando omitidos.

Sozinha, ela parece um brinquedo. O valor está em usá-la dentro de outras fórmulas: como contador de meses numa data, como número de linhas de um relatório ou como eixo de uma tabela de sensibilidade.

=SEQUÊNCIA(1;11;0;10)%     → 0% 10% 20% ... 100%   (11 colunas)
=SEQUÊNCIA(1;C3)           → 1 2 3 ... N           (N colunas)

ÚNICO: a lista que se atualiza sozinha

ÚNICO (UNIQUE) devolve os valores distintos de um intervalo. É a versão viva do Dados › Remover Duplicatas: o comando do menu age uma vez sobre uma cópia dos dados; a fórmula acompanha a base. Entrou um produto novo em tbl_Vendas, ele aparece na lista. Combinada com CLASSIFICAR (SORT), a lista sai em ordem.

=CLASSIFICAR(ÚNICO(tbl_Vendas[Vendedor]))        → 8 nomes, de Ana Souza a Hugo Barros
=CLASSIFICAR(ÚNICO(tbl_Vendas[Regiao]))          → Centro-Oeste, Nordeste, Norte, Sudeste, Sul
=CONT.VALORES(ÚNICO(tbl_Vendas[Produto]))        → 12

3.3A família EMPILHAR

EMPILHARH (HSTACK) junta intervalos lado a lado; EMPILHARV (VSTACK) junta um sobre o outro. O resultado é uma única matriz, que derrama e pode entrar em qualquer outra fórmula.

EMPILHARH: a peça que faltava para ordenar

Volte à Figura 3.1. A lista de produtos e a receita vêm de duas fórmulas diferentes, em B8 e C8. Para ordenar os produtos pela receita, CLASSIFICAR precisa enxergar as duas colunas juntas, numa matriz só. É exatamente o que EMPILHARH faz.

CLASSIFICAR(matriz; [índice]; [ordem]) ordena pela coluna indicada no índice (1 é a primeira coluna da matriz). A ordem 1 é crescente, o padrão; −1 é decrescente.

=CLASSIFICAR(EMPILHARH(B8#;C8#);2;-1)

Dentro do EMPILHARH cabem também as colunas da Tabela diretamente: =EMPILHARH(tbl_Vendas[Produto];tbl_Vendas[Receita]) devolve as 560 linhas da base com só essas duas colunas. É assim que se escolhe quais colunas um FILTRO vai devolver, como mostra a próxima seção.

EMPILHARV: acrescentar um item a uma lista

O uso clássico do EMPILHARV, consolidar bases de mesmo formato uma embaixo da outra, é o tema do Capítulo 4. Aqui ele aparece num papel menor e muito prático: colocar um item fixo no topo de uma lista dinâmica.

=EMPILHARV("Todos os vendedores";CLASSIFICAR(ÚNICO(tbl_Vendas[Vendedor])))

3.4FILTRO e CLASSIFICAR

FILTRO (FILTER) devolve as linhas de um intervalo que atendem a uma condição:

=FILTRO(matriz; incluir; [se_vazio])

• matriz: o que devolver. Uma coluna, várias colunas montadas com EMPILHARH ou a Tabela inteira.
• incluir: um teste sobre uma coluna inteira, como tbl_Vendas[Vendedor]=$C$5, que gera um VERDADEIRO ou FALSO para cada linha.
• se_vazio: o que mostrar quando nenhuma linha passa. Sem ele, a fórmula devolve #CALC!.

Dá para pensar no FILTRO como mais uma função de busca, ao lado do PROCV e do PROCX do Capítulo 2. A diferença é a quantidade: PROCV traz a primeira ocorrência; PROCX traz a primeira ou a última; FILTRO traz todas. É o mesmo filtro do menu Dados › Filtro, só que escrito como fórmula, sem esconder linhas da base e atualizado a cada mudança.

=FILTRO(EMPILHARH(tbl_Vendas[Produto];tbl_Vendas[Receita]);
        tbl_Vendas[Vendedor]="Carla Reis";
        "Nenhuma venda encontrada")

Vários critérios: multiplicar é E, somar é OU

O FILTRO tem um único argumento de condição. Para usar dois ou mais critérios, combine os testes com aritmética. Cada teste devolve uma coluna de VERDADEIRO/FALSO; em uma conta, VERDADEIRO vale 1 e FALSO vale 0. Multiplicar dois testes dá 1 só quando os dois são verdadeiros: é um E. Somar dá pelo menos 1 quando qualquer um é verdadeiro: é um OU.

=FILTRO(tbl_Vendas[Receita];
        (tbl_Vendas[Vendedor]="Carla Reis")*(tbl_Vendas[Produto]="Organizador");
        0)

=FILTRO(tbl_Vendas[Receita];
        (tbl_Vendas[Regiao]="Norte")+(tbl_Vendas[Regiao]="Sul"))

A mesma mecânica funciona fora do FILTRO. =SOMA(tbl_Vendas[Receita]*(tbl_Vendas[Vendedor]="Carla Reis")*(tbl_Vendas[Produto]="Organizador")) também dá 9.112,65: as linhas que não passam nos testes são multiplicadas por zero. A diferença é que o FILTRO mostra as linhas, e a SOMA mostra só o total.

3.5Mini-simulador: ÚNICO, validação e FILTRO

Juntando as peças, nasce o mini-simulador: um menu suspenso que dirige um relatório inteiro. São três partes.

1. A lista. Uma fórmula com ÚNICO e CLASSIFICAR numa célula auxiliar devolve as opções, sempre atualizadas.
2. O controle. A validação de dados aponta para essa lista com # e transforma uma célula num menu suspenso.
3. A resposta. Um FILTRO (ou SOMASES) lê a célula do menu e reescreve o relatório a cada escolha.

O ponto-chave é a origem da validação: =$I$6#. Com o #, a validação lê o despejo inteiro, e o menu cresce quando a lista cresce. Sem ele, a fonte fica presa a I6:I13 e um vendedor novo nunca aparece. O Capítulo 1 terminou com essa promessa; aqui ela se cumpre.

=CLASSIFICAR(
   FILTRO(EMPILHARH(tbl_Vendas[Produto];tbl_Vendas[Receita]);
          (tbl_Vendas[Vendedor]=$C$5)+($C$5="Todos os vendedores");
          "sem dados");
   2;-1)

3.6Funções voláteis e alternativas modernas

Uma função volátil é recalculada a cada alteração em qualquer célula da pasta, mesmo que nada do que ela usa tenha mudado. Algumas são voláteis por natureza: AGORA e HOJE precisam conferir o relógio; ALEATÓRIO sorteia um número novo a cada cálculo. Outras são voláteis porque o Excel não consegue saber de antemão de quais células dependem: DESLOC monta o endereço na hora, INDIRETO lê um endereço escrito em texto.

Tabela 3.3. As funções voláteis citadas no slide.

Por que doem:

• Lentidão. Uma pasta com 100 mil INDIRETO recalcula 100 mil fórmulas a cada número digitado em qualquer lugar.
• Auditoria cega. A dependência fica escondida num texto ou num deslocamento. O F2+Esc e o rastreamento de precedentes não mostram de onde vem o número.
• Quebram com mudanças de estrutura. Um INDIRETO que monta "Plan1!B5" não acompanha uma linha inserida nem uma aba renomeada.

=DESLOC(B6;2;1)                → carla@atacado.com   (2 linhas abaixo, 1 coluna à direita de B6)
=SOMA(DESLOC(C20;0;0;1;12))    → soma 12 colunas a partir de C20
=INDIRETO("T6_Texto!B"&6)      → Souza, Ana
=INDIRETO("'"&B2&"'!D10")      → D10 da aba cujo nome está em B2

O slide traz o mapa de substituição. Quase todo uso clássico das voláteis tem hoje uma solução mais leve e mais legível:

Tabela 3.4. Voláteis: o problema e a solução moderna.

3.7Texto moderno

Bases chegam sujas: nome e sobrenome numa célula só, códigos com várias partes, e-mails dos quais só interessa o domínio. A caixa de ferramentas clássica resolve tudo, mas exige contar posições.

Tabela 3.5. Funções clássicas de texto. LOCALIZAR e PROCURAR também aceitam a posição inicial da busca, para achar a 2ª ocorrência.

=ESQUERDA(B6;LOCALIZAR(",";B6)-1)                         → Souza
=ARRUMAR(EXT.TEXTO(B6;LOCALIZAR(",";B6)+1;NÚM.CARACT(B6))) → Ana

=DIVIDIRTEXTO(B6;", ")     → Souza | Ana   (derrama em duas colunas)

O kit moderno do slide tem cinco funções:

• DIVIDIRTEXTO(texto; delim_col; [delim_lin]) (TEXTSPLIT): quebra em todos os delimitadores e derrama.
• TEXTOANTES(texto; delim; [ocorrência]) (TEXTBEFORE): tudo antes do delimitador.
• TEXTODEPOIS(texto; delim; [ocorrência]) (TEXTAFTER): tudo depois do delimitador.
• UNIRTEXTO(delim; ignorar_vazio; textos...) (TEXTJOIN): junta vários textos com um separador.
• CONCAT(textos...) (CONCAT): junta textos sem separador; faz o mesmo que o &.

O argumento ocorrência do TEXTOANTES e do TEXTODEPOIS diz qual delimitador usar quando há mais de um. Número negativo conta do fim para o começo: −1 é o último.

=UNIRTEXTO(", ";VERDADEIRO;CLASSIFICAR(ÚNICO(tbl_Vendas[Vendedor])))
→ Ana Souza, Bruno Melo, Carla Reis, Diego Farias, Elisa Cruz, Felipe Ramos, Gabriela Pinto, Hugo Barros

="Receita de "&C5&": R$ "&TEXTO(F5;"#.##0,00")
→ Receita de Bruno Melo: R$ 530.944,04

3.8Datas: o kit essencial

Datas são números (Capítulo 1): 01/01/2026 é o número 46.023. Por isso, somar dias é somar números. Somar meses, porém, não é somar 30: fevereiro tem 28 dias, outros meses 31. Para isso existe um kit próprio.

Tabela 3.6. O kit de datas.

Dois truques valem decorar. FIMMÊS(data;-1)+1 é o primeiro dia do mês: o último dia do mês anterior, mais um. E DATA(ano;mês+1;0) também é o último dia do mês: o "dia zero" de um mês é o último dia do anterior.

=DATA(2027;SEQUÊNCIA(1;C3);1)        → 01/01/2027, 01/02/2027, ... (C3 colunas)
=DATA(2027;SEQUÊNCIA(1;C3)+1;0)      → 31/01/2027, 28/02/2027, ... (fins de mês)

=DATAM(DATA(2025;1;31);1)     → 28/02/2025
=DATA(2025;1;31)+30           → 02/03/2025

3.9Exercícios resolvidos

Os três exercícios da Sessão 3 usam o arquivo EBP_Tools_2026_Aluno.xlsx, com a base tbl_Vendas (560 vendas de 2025). As células amarelas são as que o aluno preenche.

Exercício 1: resumo dinâmico por região (aba T5_Dinamicas1)

Tarefa: escolher uma região em C5 e, a partir de B8, listar os produtos vendidos nela com a receita de cada um, tudo dinâmico. Desafio: ordenar da maior para a menor receita e mostrar só o Top 3.

Passo 1: a lista de regiões e o menu. Numa coluna livre (I6), a lista das regiões; em C5, a validação de dados com fonte =$I$6#.

I6: =CLASSIFICAR(ÚNICO(tbl_Vendas[Regiao]))

Passo 2: os produtos da região. O FILTRO recorta a coluna de produtos pela região; o ÚNICO tira as repetições; o CLASSIFICAR põe em ordem alfabética.

B8: =CLASSIFICAR(ÚNICO(FILTRO(tbl_Vendas[Produto];tbl_Vendas[Regiao]=$C$5;"")))

Leia de dentro para fora: o FILTRO devolve os 131 produtos vendidos no Sudeste, um por venda; o ÚNICO reduz a 12; o CLASSIFICAR ordena.

Passo 3: a receita ao lado. Uma SOMASES com dois critérios, em que o critério de produto é a lista inteira, B8#:

C8: =SOMASES(tbl_Vendas[Receita];
             tbl_Vendas[Produto];B8#;
             tbl_Vendas[Regiao];$C$5)

Confira: =SOMA(C8#) dá 986.849,12, a receita total do Sudeste, igual a =SOMASES(tbl_Vendas[Receita];tbl_Vendas[Regiao];$C$5).

Desafio: ordenar e cortar. Com EMPILHARH, a lista e a receita viram uma matriz de 2 colunas, que o CLASSIFICAR ordena pela 2ª coluna em ordem decrescente:

E8: =CLASSIFICAR(EMPILHARH(B8#;C8#);2;-1)

Tabela 3.7. Resultado do Exercício 1 para o Sudeste.

O Top 3 é o começo desse resultado. A função que corta as primeiras linhas, PEGAR (TAKE), é apresentada no Capítulo 4: =PEGAR(CLASSIFICAR(EMPILHARH(B8#;C8#);2;-1);3) devolve Mesa Ajustável, Notebook Pro e Monitor 27.

Com outras regiões, o Top 3 muda: no Nordeste, Notebook Pro (552.189,11), Monitor 27 (291.358,23) e Mesa Ajustável (267.734,07); no Sul, Notebook Pro (416.936,81), Mesa Ajustável (337.060,02) e Monitor 27 (254.448,03).

Exercício 2: mini-simulador de vendedor (aba T5_Dinamicas2)

Tarefa: criar em C5 um menu suspenso de vendedores, com a lista de origem em I6, e mostrar a partir de B8 o produto e a receita das vendas do vendedor, ordenados pela receita. Desafio: ao lado do menu, a receita total do vendedor (SOMASES) e o número de produtos distintos que ele vendeu (CONT.VALORES de ÚNICO).

Passo 1: a lista e o menu.

I6: =CLASSIFICAR(ÚNICO(tbl_Vendas[Vendedor]))
C5: Dados › Validação de Dados › Lista › Fonte: =$I$6#

Passo 2: o relatório. EMPILHARH escolhe as duas colunas; FILTRO fica só com as linhas do vendedor; CLASSIFICAR ordena pela 2ª coluna, decrescente.

B8: =CLASSIFICAR(
       FILTRO(EMPILHARH(tbl_Vendas[Produto];tbl_Vendas[Receita]);
              tbl_Vendas[Vendedor]=$C$5;
              "sem dados");
       2;-1)

Repare que o relatório mostra vendas, não produtos: Notebook Pro aparece várias vezes, uma para cada venda. Para ver a receita por produto do vendedor, use o padrão do Exercício 1, com o critério do vendedor no lugar da região:

E8: =CLASSIFICAR(ÚNICO(FILTRO(tbl_Vendas[Produto];tbl_Vendas[Vendedor]=$C$5)))
F8: =SOMASES(tbl_Vendas[Receita];tbl_Vendas[Produto];E8#;tbl_Vendas[Vendedor];$C$5)

Para o Bruno Melo, ordenando por receita, o Notebook Pro soma 137.841,99, a Mesa Ajustável 112.310,72 e o Monitor 27 72.970,75. A função AGRUPARPOR (GROUPBY), do Capítulo 4, faz esse agrupamento, a ordenação e o filtro numa fórmula só.

Desafio.

F5: =SOMASES(tbl_Vendas[Receita];tbl_Vendas[Vendedor];$C$5)                → 530.944,04
F6: =CONT.VALORES(ÚNICO(FILTRO(tbl_Vendas[Produto];tbl_Vendas[Vendedor]=$C$5)))  → 12

Tabela 3.8. O painel para cada escolha do menu. Na base do curso, todos os vendedores venderam os 12 produtos; o número de vendas é `=CONT.SES(tbl_Vendas[Vendedor];$C$5)`.

Para a primeira linha do painel, o Exemplo 3.7 mostra como acrescentar a opção "Todos os vendedores" ao menu e ao relatório.

Exercício 3: texto e datas (aba T6_Texto)

Tarefa: numa pequena base de clientes, separar Sobrenome e Nome, extrair o domínio do e-mail e o código final do SKU, calcular o vencimento (+30 dias úteis) e montar o calendário do trimestre com SEQUÊNCIA e DATA. Desafio: montar a chave REGIAO-ANO com UNIRTEXTO e extrair o ano do vencimento com ANO.

Sobrenome e Nome. DIVIDIRTEXTO em F6, copiado até F10; cada cópia derrama em duas colunas.

F6: =DIVIDIRTEXTO(B6;", ")       → Souza | Ana

A alternativa com uma fórmula por coluna: =TEXTOANTES(B6:B10;", ") em F6 e =TEXTODEPOIS(B6:B10;", ") em G6.

Domínio e código do SKU. Os dois aceitam a coluna inteira e derramam:

H6: =TEXTODEPOIS(C6:C10;"@")        → empresa.com, lojasul.com.br, ...
I6: =TEXTODEPOIS(D6:D10;"-";-1)     → 001, 014, 007, 030, 011

Na aula, o professor usou a ocorrência 2 (TEXTODEPOIS(D6;"-";2)), que dá o mesmo resultado nesses SKUs, todos com dois traços. A ocorrência −1 é mais robusta: continua pegando o último pedaço mesmo que algum SKU tenha mais traços. Note que "001" sai como texto, com os zeros à esquerda preservados; se precisar do número 1, use =--I6.

Vencimento. A emissão está como texto; DATA.VALOR converte e DIATRABALHO soma 30 dias úteis.

J6: =DIATRABALHO(DATA.VALOR(E6);30)     → 21/02/2025   (copiar até J10)

Tabela 3.9. Vencimentos do Exercício 3, sem lista de feriados. Com feriados no 3º argumento, as datas avançam.

Calendário do trimestre. Abaixo da base, uma célula de início (C12) e duas linhas despejadas: os meses e os fechamentos.

C12: 01/01/2025
C13: =DATA(ANO(C12);MÊS(C12)+SEQUÊNCIA(1;3)-1;1)     → jan/25  fev/25  mar/25
C14: =DATA(ANO(C12);MÊS(C12)+SEQUÊNCIA(1;3);0)       → 31/01/2025  28/02/2025  31/03/2025

Desafio: chave REGIAO-ANO. A aba não tem a região; ela está na Tabela de vendedores, e o nome do cliente coincide com o do vendedor. Remonte o nome na ordem "Nome Sobrenome" e busque com PROCX (Capítulo 2):

K6: =PROCX(G6&" "&F6;tbl_Vendedores[Vendedor];tbl_Vendedores[Regiao])    → Sul
L6: =UNIRTEXTO("-";VERDADEIRO;K6;ANO(J6))                                → Sul-2025

As chaves saem Sul-2025, Sudeste-2025, Nordeste-2025, Norte-2025 e Centro-Oeste-2025. Chaves assim servem de critério único em SOMASES e PROCX quando a busca depende de dois campos ao mesmo tempo. =K6&"-"&ANO(J6) e =CONCAT(K6;"-";ANO(J6)) dão o mesmo resultado; UNIRTEXTO compensa quando há muitos campos e o mesmo separador entre todos.

3.10Exercícios propostos

Use a base tbl_Vendas do arquivo EBP_Tools_2026_Aluno.xlsx e a aba T6_Texto.

1. Liste as categorias de produto distintas, em ordem alfabética, com uma fórmula. Quantas são?
2. Ao lado da lista do exercício 1, calcule a receita de cada categoria com uma única fórmula despejada e depois ordene categoria e receita da maior para a menor receita.
3. Com FILTRO, liste as vendas do Norte com receita acima de 20.000. Quantas são e quanto somam?
4. Escreva a fórmula que devolve as vendas do Sudeste ou do Nordeste, com produto e receita. Quantas linhas ela devolve?
5. Monte, com uma fórmula, o cabeçalho com os últimos dias dos meses de julho a dezembro de 2026. Quais são as datas?
6. Na aba T6_Texto, extraia de C7 ("bruno@lojasul.com.br") só o nome da loja, "lojasul", e de D8 ("TC-2025-007") o ano, como número.
7. Uma nota emitida em 21/03/2025 tem três prazos possíveis: 2 meses, fim do mês da emissão e 10 dias úteis. Calcule as três datas.
8. Junte numa única célula as regiões da base, em ordem alfabética, separadas por "; ".
9. Destas fórmulas, quais são voláteis? =HOJE()-B2, =SOMA(C6:C10), =DESLOC(A1;3;0), =PROCX(B2;tbl_Produtos[Produto];tbl_Produtos[CustoUnit]), =INDIRETO("B"&C1). Que alternativa você usaria para =SOMA(DESLOC(A1;0;0;CONT.VALORES(A:A);1))?

Material de apoio às aulas, de uso exclusivo dos alunos da BTC. É proibido reproduzir ou compartilhar este conteúdo sem autorização expressa da BTC. Ler o disclaimer

As três primeiras sessões do Tools encheram a caixa de ferramentas: navegação, Tabelas, SOMASES, PROCX, e as fórmulas que derramam resultados, como ÚNICO, FILTRO e CLASSIFICAR. Esta última sessão do módulo junta essas peças em três movimentos que aparecem em quase todo trabalho com dados. Primeiro, consolidar: reunir bases que estão espalhadas em várias abas numa fonte única e viva. Depois, dar legibilidade: escrever fórmulas longas de um jeito que você, ou um colega, consiga ler daqui a seis meses. Por fim, analisar e apresentar: resumir a base, escolher o gráfico certo e destacar o que importa.

O fio condutor é uma frase do checkpoint da sessão: "o júnior refaz; o sênior constrói uma vez e deixa a planilha trabalhar". Cada técnica do capítulo troca um trabalho manual e repetitivo (copiar blocos, ajustar intervalos, atualizar uma tabela dinâmica) por uma fórmula que se ajusta sozinha quando a base cresce.

4.1Consolidar não é buscar cinco vezes

Imagine uma empresa com cinco unidades de negócio (SBUs, do inglês strategic business units). Cada unidade tem a sua aba, com as vendas na mesma estrutura: produto, mês e receita. A diretoria quer a receita por produto somando as cinco. O reflexo antigo é ir buscar a informação cinco vezes: um PROCV em cada base, um INDIRETO montando o nome de cada aba, ou cinco blocos copiados e colados um embaixo do outro. Funciona até o dia em que entra uma linha nova numa das abas e ninguém lembra de estender o bloco.

O caminho moderno inverte a ordem: primeiro empilhe, depois pergunte. Uma fórmula junta as cinco bases numa só, e todas as perguntas (FILTRO, SOMASES, AGRUPARPOR) passam a ser feitas sobre essa fonte única.

Com as bases transformadas em Tabelas nomeadas, a consolidação do slide cabe numa linha:

=EMPILHARV(tbl_SBU1; tbl_SBU2; tbl_SBU3; tbl_SBU4; tbl_SBU5)

Como cada Tabela cresce sozinha, uma venda nova lançada na SBU3 aparece no consolidado na mesma hora, e tudo o que depende dele recalcula. É o que o slide chama de "uma fonte única e viva".

O professor começou a demonstração com o caso mais simples possível, duas sequências, para mostrar o comportamento das duas funções de empilhar quando os blocos não têm o mesmo tamanho.

=EMPILHARV(B6#; D6#)            → 27 linhas: 1 a 12 e, embaixo, 101 a 115
=EMPILHARH(B6#; D6#)            → 15 linhas × 2 colunas; B tem só 12 valores
=SEERRO(EMPILHARH(B6#; D6#); "") → as 3 células que sobram ficam vazias

Empilhar também serve para listas de menu

EMPILHARV não é só para bases grandes. Um uso que aparece em quase todo simulador é acrescentar uma opção fixa no topo de uma lista dinâmica.

I6: =EMPILHARV("Todos os vendedores"; CLASSIFICAR(ÚNICO(tbl_Vendas[Vendedor])))

Referência 3D: somar a mesma célula em várias abas

Quando as abas têm o mesmo layout, célula por célula, existe um atalho antigo e eficiente: a referência 3D. Ao digitar =SOMA(, clique na aba SBU1, segure Shift, clique na aba SBU5 e depois na célula. O Excel escreve:

=SOMA('SBU1:SBU5'!D47)

Isso soma D47 de todas as abas entre SBU1 e SBU5, na ordem em que estão na barra de abas. É o recurso usado no Exercício 1 para consolidar a DRE das cinco unidades. EMPILHARV também aceita referência 3D: =EMPILHARV('SBU1:SBU5'!D47:J47) devolve as cinco linhas, uma de cada aba, uma embaixo da outra.

O enterro do INDIRETO

Antes das matrizes dinâmicas, a forma clássica de "buscar em várias abas" era o INDIRETO. Vale entendê-lo, porque ele aparece em muitas planilhas herdadas.

O detalhe que confunde: digitar =SBU2!F47 numa célula traz o valor de F47, porque o Excel reconhece um endereço. Mas montar o mesmo texto com ="SBU2"&"!F47" devolve só o texto SBU2!F47. O INDIRETO é o jeito de dizer ao Excel "isto não é um texto, é um endereço: vá lá e traga o que estiver". Nomes de aba com espaço precisam de aspas simples dentro do texto ("'Vendas 2025'!B3"), o que leva a sequências como aspas duplas, aspas simples e aspas duplas na mesma fórmula, difíceis de ler e fáceis de errar.

Há três motivos para aposentá-lo:

1. É volátil. Recalcula a cada alteração em qualquer célula da pasta (ver as funções voláteis do Capítulo 3). Mil INDIRETOs numa coluna de 100 mil linhas pesam no arquivo inteiro.
2. É frágil. O endereço está escrito como texto, então o Excel não o atualiza: renomeie a aba SBU2 ou insira uma linha acima de F47 e a fórmula passa a apontar para o lugar errado, ou devolve #REF!.
3. Não deixa rastro. O F2 + Esc do Capítulo 1 não ilumina a origem de um INDIRETO, e as ferramentas de auditoria não conseguem seguir a referência.

A aba Analise_Consolidada do Exercício 1 é uma "aba-museu": um simulador montado com CORRESP, ENDEREÇO e INDIRETO. A resolução mostra a versão antiga e a versão com EMPILHARV, lado a lado, com os mesmos números.

ESCOLHERCOLS: pegar só as colunas que interessam

O consolidado devolve todas as colunas das bases. Para trabalhar com uma só (a lista de produtos, por exemplo), não é preciso empilhar de novo coluna por coluna.

Com ela, a lista de produtos de todas as SBUs sai do consolidado: =CLASSIFICAR(ÚNICO(ESCOLHERCOLS(J4#; 1))). Aplicar ÚNICO direto sobre J4# não resolveria: com três colunas, ÚNICO procura linhas repetidas (mesmo produto, mesmo mês e mesma receita), e quase nenhuma se repete.

4.2PEGAR e DESCARTAR

Duas perguntas comuns em relatórios são "quais são os três maiores?" e "como foram os últimos seis meses?". Antes, elas exigiam DESLOC (OFFSET) e contas de posição. Hoje cabem em duas funções.

Tabela 4.1. PEGAR e DESCARTAR. Numa lista de 12 linhas, `DESCARTAR(X; -9)` e `PEGAR(X; 3)` dão o mesmo resultado.

PEGAR é especialmente útil depois de um CLASSIFICAR: com a lista já ordenada, "top 3" é só pegar as três primeiras linhas.

B8: =CLASSIFICAR(ÚNICO(tbl_Vendas[Produto]))
E8: =PEGAR(
      CLASSIFICAR(
        EMPILHARH(B8#;
                  SOMASES(tbl_Vendas[Receita];
                          tbl_Vendas[Produto]; B8#;
                          tbl_Vendas[Regiao]; C5));
        2; -1);
      3)

C6:  =SOMASES(tbl_Vendas[Receita]; tbl_Vendas[Mes]; B6:B17)
E6:  =PEGAR(EMPILHARH(B6:B17; C6#); -6)
E13: =SOMA(PEGAR(C6#; -6))

DESCARTAR é o par natural para limpar exportações: =DESCARTAR(Base; 1) tira uma primeira linha de lixo que o sistema exporta acima dos cabeçalhos; =DESCARTAR(tbl_Vendas[#Tudo]; 0; -4) traz a base inteira, com cabeçalho, sem as quatro últimas colunas.

4.3Vários caminhos para o mesmo problema

O slide 52 faz uma pergunta só, "qual a receita por produto?", e compara as respostas. A mensagem é que não existe "a" fórmula certa: existem escolhas, cada uma com prós e contras, e algumas que parecem funcionar mas estão erradas. Ver o erro e entender por que ele acontece fixa o conceito melhor do que decorar a fórmula boa.

=SOMASES(tbl_Vendas[Receita]; tbl_Vendas[Produto]; "Notebook Pro")
=SOMARPRODUTO((tbl_Vendas[Produto]="Notebook Pro")*tbl_Vendas[Receita])
=SOMA(FILTRO(tbl_Vendas[Receita]; tbl_Vendas[Produto]="Notebook Pro"))
=SOMA((tbl_Vendas[Produto]="Notebook Pro")*tbl_Vendas[Receita])

Na consolidação das SBUs, os mesmos caminhos ganham uma camada a mais: a base é o resultado de um EMPILHARV, e não uma Tabela. Aí aparecem as diferenças.

Tabela 4.2. Os caminhos do slide 52 para a receita por produto na base consolidada. J4# é o consolidado e B7, o produto.

As funções da família SES (SOMASES, CONT.SES, MÉDIASES) nasceram antes das matrizes dinâmicas e pedem referências nos argumentos de intervalo. Elas aceitam um despejo como critério (SOMASES(...; B7#) funciona e derrama uma soma por produto), mas não aceitam uma matriz calculada no lugar do intervalo somado. Quando isso acontece, os caminhos são: trocar por SOMA(...*...) ou FILTRO, como na Tabela 4.2; ou usar AGRUPARPOR, que já resolve a soma por grupo.

4.4LET: nomes de negócio na fórmula

À medida que as fórmulas modernas vão sendo combinadas (EMPILHARH dentro de CLASSIFICAR dentro de PEGAR, com SOMASES no meio), elas ficam difíceis de ler. E muitas vezes repetem o mesmo trecho duas ou três vezes, o que obriga o Excel a fazer a mesma conta de novo. O LET resolve as duas coisas.

O professor descreve o LET como "uma carta para o seu eu do futuro". Uma fórmula de cinco linhas densas exige um trabalho de detetive para ser entendida; a mesma fórmula com etapas nomeadas se explica sozinha: calcule a receita, calcule o custo, a margem é a diferença.

=LET(
   receita; [@Qtd]*[@PrecoUnit];
   custo;   [@Qtd]*[@CustoUnit];
   receita - custo)

Nesse caso o LET deixa a fórmula mais legível, mas não mais rápida: cada parte é usada uma vez. O ganho de desempenho aparece quando um trecho se repete. Toda vez que você for escrever o mesmo pedaço pela segunda vez, nomeie-o numa variável e use o nome.

B8: =LET(
       produtos;    tbl_Vendas[Produto];
       receita;     tbl_Vendas[Receita];
       vendedores;  tbl_Vendas[Vendedor];
       escolhido;   C5;
       lista_vend;  ÚNICO(FILTRO(produtos; (vendedores=escolhido)*(receita>0)));
       rec_vend;    SOMASES(receita; produtos; lista_vend; vendedores; escolhido);
       lista_geral; ÚNICO(FILTRO(produtos; receita>0));
       rec_geral;   SOMASES(receita; produtos; lista_geral);
       tab_vend;    CLASSIFICAR(EMPILHARH(lista_vend; rec_vend); 2; -1);
       tab_todos;   CLASSIFICAR(EMPILHARH(lista_geral; rec_geral); 2; -1);
       SE(escolhido="Todos os vendedores"; tab_todos; tab_vend))

4.5LAMBDA, MAP e o critério de escolha

Se o LET dá nome às partes de uma fórmula, o LAMBDA dá nome ao todo: com ele você cria a sua própria função, que passa a ser usada como SOMA ou PROCX. Antes, isso exigia VBA.

=LAMBDA(x; x*2)(10)              → 20
=LAMBDA(r; c; (r-c)/r)(100; 70)  → 0,3

=MargemPadrão(1636792,37; 1159200)   → 29,2%

O uso mais poderoso é empacotar um modelo inteiro. A fórmula do Exemplo 4.7 só depende de uma entrada, o vendedor. Envolvida num LAMBDA, ela vira a função ResumoVendedor:

ResumoVendedor  =LAMBDA(vendedor;
                   LET(produtos; tbl_Vendas[Produto];
                       ...
                       escolhido; vendedor;
                       ...))

Depois de salva, =ResumoVendedor("Carla Reis") monta a tabela inteira da vendedora numa célula, e =ResumoVendedor(C5) a amarra a um menu. Numa planilha de financiamento, a tabela completa de prestações poderia ser uma LAMBDA que recebe valor, taxa e prazo.

As LAMBDAs ficam salvas na pasta de trabalho onde foram criadas. Para usar a mesma função em outro arquivo, copie a definição do Gerenciador de Nomes (ou uma célula que a use) para o novo arquivo.

MAP: aplicar a mesma conta a cada item de uma lista

O slide mostra =MAP(prod; LAMBDA(p; SOMASES(...; p))). Para uma soma por produto, SOMASES com B8# como critério já derrama o resultado sem MAP. O MAP vale a pena quando a conta de cada item não aceita uma lista inteira de uma vez: um FILTRO com critério por item, uma LAMBDA própria, uma sequência de etapas.

I6: =MAP(H6#; LAMBDA(p;
       LET(f;      tbl_Vendas[Produto]=p;
           receita; SOMA(FILTRO(tbl_Vendas[Receita]; f));
           custo;   SOMA(FILTRO(tbl_Vendas[Qtd]*tbl_Vendas[CustoUnit]; f));
           MargemPadrão(receita; custo))))

Qual ferramenta usar?

Com tantas opções, o slide 55 propõe um critério simples: use a ferramenta mais simples que resolve o problema.

Tabela 4.3. Critério de escolha do slide 55. Power Query e VBA são os temas do módulo de Automatização.

4.6Análise: tabela dinâmica e AGRUPARPOR

A tabela dinâmica, vista no Capítulo 2, continua sendo a forma mais rápida de explorar uma base: arrastar campos, trocar a soma por média, filtrar. O limite dela é a atualização: a tabela dinâmica lê a base, guarda uma cópia e só enxerga mudanças quando alguém clica em Atualizar. Para um relatório recorrente, isso é um risco. As funções AGRUPARPOR e PIVOTPOR fazem o mesmo trabalho em forma de fórmula: recalculam sozinhas e podem entrar dentro de outras fórmulas.

Tabela 4.4. Tabela dinâmica × funções de resumo.

Tabela 4.5. Argumentos de AGRUPARPOR.

=PEGAR(
   AGRUPARPOR(tbl_Vendas[Produto]; tbl_Vendas[Receita]; SOMA;
              0;                         ← sem cabeçalho
              0;                         ← sem total
              -2;                        ← ordena pela receita, decrescente
              tbl_Vendas[Regiao]=C5);    ← só a região escolhida
   C6)

Para ter duas colunas de resultado (receita e quantidade, por exemplo), passe as duas em valores com EMPILHARH. Já duas colunas em campos_linha mudam o agrupamento: cada combinação vira uma linha (Região e Categoria, no desafio do Exercício 2).

PIVOTPOR: linhas e colunas

Uma base guardada do jeito certo tem uma coluna para cada campo e uma linha para cada registro: é o formato bom para armazenar. Para ler, muitas vezes queremos o formato "pivotado", com uma dimensão nas linhas e outra nas colunas.

=PIVOTPOR(tbl_Vendas[Regiao]; tbl_Vendas[Categoria]; tbl_Vendas[Receita]; SOMA)

4.7Gráfico certo e destaque visual

Um resumo bem calculado ainda precisa ser lido. Dois recursos fazem essa ponte: o gráfico, escolhido pela mensagem, e a formatação condicional, que chama a atenção para a exceção.

O gráfico pela mensagem

Antes de inserir um gráfico, pergunte qual é a mensagem. O tipo vem dela.

Tabela 4.7. O gráfico pela mensagem. Pizza com 12 fatias é ilegível: use barras ordenadas.

Para inserir: selecione os dados (ou uma célula do intervalo despejado) e vá em Inserir › Gráficos Recomendados, ou escolha o tipo direto. Um gráfico feito sobre um intervalo despejado acompanha o despejo: se uma região nova aparecer na base, ela entra no gráfico. Tire o que não ajuda a ler (linhas de grade em excesso, legenda com uma série só, a linha de total, que achataria as outras colunas) e escreva no título a conclusão, não só o assunto.

Formatação condicional por fórmula

A formatação condicional (Página Inicial › Formatação Condicional) tem regras prontas (escalas de cor, barras de dados, conjuntos de ícones, "maior que", "abaixo da média") e uma opção mais flexível: Nova Regra › Usar uma fórmula para determinar quais células devem ser formatadas. A fórmula é escrita para a primeira célula do intervalo selecionado e deve devolver VERDADEIRO nas células que serão pintadas.

Para completar o painel, um filtro por menu: a lista do menu vem de =CLASSIFICAR(ÚNICO(tbl_Vendas[Regiao])) numa célula auxiliar, e a fonte da validação é essa célula com #. É a validação dinâmica antecipada no Capítulo 1: a lista se atualiza sozinha.

4.8Exercícios resolvidos

Os exercícios usam dois arquivos. A consolidação das SBUs está no EBP_Tools_Arquivo3@2026.xlsx (abas SBU1 a SBU5, Consolidado e Analise_Consolidada). A análise e o case usam a base Vendas do EBP_Tools_2026_Aluno.xlsx, com 560 vendas de 2025. As células amarelas são as de resposta.

Exercício 1: consolidar as cinco SBUs (Arquivo3, abas Consolidado e Analise_Consolidada)

Contexto. Cada aba SBU1 a SBU5 tem o modelo financeiro de uma unidade de negócio, com projeções de 2024 a 2030E, e todas têm exatamente o mesmo layout: a Receita Bruta está na linha 47, a Receita Líquida na 51, o Lucro bruto na 55, e assim por diante; os anos estão em D45:J45. É o cenário ideal para consolidar sem buscar aba por aba.

Tarefa 1 (aba Consolidado). Montar a DRE consolidada das cinco unidades.

A aba Consolidado repete o layout da DRE, 35 linhas acima: a Receita Bruta está na linha 12 do Consolidado e na 47 das SBUs. A referência 3D resolve as linhas de valor:

D12: =SOMA('SBU1:SBU5'!D47)      → copiar para D12:J12 e para as demais linhas de valor

O nome da aba aparece entre aspas simples porque "SBU1" também é um endereço de célula válido (coluna SBU, linha 1); o Excel coloca as aspas para não confundir.

Tabela 4.8. Linhas selecionadas da DRE consolidada (R$). 2027E e 2029E foram omitidos.

Tarefa 2 (aba Analise_Consolidada). "Crie um simulador que consolide os dados": escolhido o ano em C8, a tabela B14:J20 mostra oito indicadores de cada SBU (Receita Bruta, Receita Líquida, Lucro bruto, Lucro operacional, Lucro líquido, Total dívida, Caixa e FCFF) e o consolidado.

O jeito antigo (aba-museu). A aba já traz as linhas auxiliares que denunciam o método. Para cada indicador, CORRESP (MATCH) acha a coluna do ano e a linha do indicador na SBU1; ENDEREÇO (ADDRESS) transforma os dois números num texto como "F47"; e INDIRETO junta o nome da aba com esse texto e vai buscar o valor.

C10: =CORRESP($C8; 'SBU1'!$45:$45; 0)           → 6    (coluna F)
C11: =CORRESP(C14; 'SBU1'!$B:$B; 0)             → 47   (linha da Receita Bruta)
C12: =ENDEREÇO(C11; C10; 4)                     → F47
C15: =INDIRETO($B15&"!"&C$12)                   → copiar para C15:J19

Funciona, e o resultado está certo. Mas são 40 funções voláteis, três linhas auxiliares e uma dependência escondida: nada na fórmula de C15 mostra, para o F2 + Esc, de onde vem o número. O Total dívida ainda exige um caso especial, porque soma três linhas (98 a 100), e o endereço vira um intervalo de texto ("F98:F100") dentro de um SOMA(INDIRETO(...)).

O jeito moderno. Como as abas têm o mesmo layout, EMPILHARV com referência 3D traz a linha do indicador das cinco SBUs de uma vez, uma linha por SBU e uma coluna por ano. PROCX escolhe a coluna do ano:

C15: =PROCX($C$8; 'SBU1'!$D$45:$J$45; EMPILHARV('SBU1:SBU5'!$D$47:$J$47))

O PROCX procura "2026E" na linha dos anos e, como a matriz de retorno tem cinco linhas, devolve a coluna inteira daquele ano: os cinco valores derramam em C15:C19. Nas outras colunas, troca-se só a linha (51, 55, 65, 79, 87 e 190). Para o Total dívida, somam-se as três matrizes:

H15: =PROCX($C$8; 'SBU1'!$D$45:$J$45;
            EMPILHARV('SBU1:SBU5'!$D$98:$J$98) +
            EMPILHARV('SBU1:SBU5'!$D$99:$J$99) +
            EMPILHARV('SBU1:SBU5'!$D$100:$J$100))

Tabela 4.9. Simulador com C8 = 2026E (R$). As duas versões, com INDIRETO e com EMPILHARV, dão os mesmos números.

A leitura do simulador já é análise de negócio: em 2026E, a SBU4 tem receita de R$ 12 milhões, mas prejuízo operacional e a maior dívida do grupo, e é ela que leva o FCFF consolidado para o negativo. Troque C8 para 2028E e o quadro muda: o consolidado passa a ter FCFF de 7.510.176 e Lucro líquido de 12.208.306.

A parte de baixo da aba (linhas 27 a 31) pede a análise de uma SBU escolhida em C27. No kit moderno, uma fórmula em C30 traz a linha inteira: =PROCX($C$27; $B$15:$B$19; $C$15:$J$19). Com SBU2, a linha mostra 5.056.200 de Receita Bruta e 743.783 de Lucro líquido, pronta para um gráfico de colunas contra o total da linha 31.

Exercício 2: análise e apresentação (aba T8_Exercicio)

Tarefa. Transformar a base num painel de resumo com destaque visual: (1) receita por região com AGRUPARPOR (ou ÚNICO + SOMASES); (2) gráfico adequado à mensagem; (3) validação dinâmica com ÚNICO para um filtro; (4) formatação condicional destacando as regiões abaixo da média. Desafio: AGRUPARPOR com duas dimensões (Região e Categoria) e linha de total.

No arquivo do aluno a aba se chama T8_Exercicio e a resposta começa em A6; nos slides, o mesmo exercício aparece como T9_Analise.

1. Resumo por região.

A6: =AGRUPARPOR(tbl_Vendas[Regiao]; tbl_Vendas[Receita]; SOMA; 0; 0)

O 0 no quarto argumento diz que não há cabeçalho na seleção; o 0 no quinto desliga a linha de total, que atrapalharia o gráfico e a média. O caminho alternativo, sem AGRUPARPOR, são duas fórmulas: =CLASSIFICAR(ÚNICO(tbl_Vendas[Regiao])) em A6 e =SOMASES(tbl_Vendas[Receita]; tbl_Vendas[Regiao]; A6#) em B6.

2. Gráfico. A mensagem é comparar regiões: colunas. Selecione A6:B10 › Inserir › Colunas Agrupadas. O resultado é o da Figura 4.6. Ordenar pela receita (sexto argumento −2) deixaria o gráfico ainda mais fácil de ler.

3. Filtro com validação dinâmica. Em G6, a lista de regiões: =CLASSIFICAR(ÚNICO(tbl_Vendas[Regiao])). Em D6, a validação de dados (Lista) com fonte =$G$6#. Em D8, o detalhe por categoria da região escolhida, usando o argumento de filtro:

D8: =AGRUPARPOR(tbl_Vendas[Categoria]; tbl_Vendas[Receita]; SOMA; 0; 0; -2;
                tbl_Vendas[Regiao]=D6)

4. Destaque. A regra do Exemplo 4.12 sobre A6:B10: =$B6<MÉDIA($B$6:$B$10).

Tabela 4.10. Resultado do Exercício 2. Com Nordeste no menu, o detalhe mostra Eletrônicos 979.216,33; Escritório 513.171,35; Casa 43.398,38; Esporte 29.416,82.

Repare na leitura: o Sudeste, que muita gente apostaria como a maior região, está abaixo da média; Nordeste e Sul, juntos, respondem por 55% da receita.

Desafio: duas dimensões e total. Duas colunas em campos_linha (com EMPILHARH) e total geral ligado:

I6: =AGRUPARPOR(EMPILHARH(tbl_Vendas[Regiao]; tbl_Vendas[Categoria]);
                tbl_Vendas[Receita]; SOMA; 0; 1)

Com o argumento total igual a 2, o AGRUPARPOR acrescenta também um subtotal por região. A mesma informação, em formato de matriz, é o PIVOTPOR do Exemplo 4.11.

Exercício 3: case integrador, um painel de uma aba

Briefing. "Você recebeu a base bruta de vendas. Sua gestora quer um painel de UMA aba", com: base estruturada em Tabela; relatório por vendedor com FILTRO + EMPILHARH + CLASSIFICAR, controlado por seleção; resumo mensal com SOMASES ou AGRUPARPOR; um gráfico adequado; uma fórmula documentada com LET. Proibido: PROCV, DESLOC, INDIRETO e fórmulas matriciais antigas (Ctrl+Shift+Enter). Só o kit moderno.

A resolução abaixo usa a base Vendas do arquivo do aluno e uma aba nova, Painel.

Passo 1: a base. A base já está numa Tabela; basta conferir o nome (tbl_Vendas, como no box da seção 4.1). Se a base viesse solta, Ctrl+T e um nome com prefixo resolveriam.

Passo 2: seleção e indicadores. Em M5, a lista de vendedores, =CLASSIFICAR(ÚNICO(tbl_Vendas[Vendedor])); em C4, o menu com fonte =$M$5#. Os indicadores:

C6: =SOMASES(tbl_Vendas[Receita]; tbl_Vendas[Vendedor]; C4)    → 700.442,46
C7: =CONT.SES(tbl_Vendas[Vendedor]; C4)                        → 72
C8: =C6/C7                                                     → 9.728,37

Passo 3: relatório controlado pela seleção. As dez maiores vendas do vendedor, com data, produto, quantidade e receita:

E6: =PEGAR(
       CLASSIFICAR(
         FILTRO(EMPILHARH(tbl_Vendas[Data]; tbl_Vendas[Produto];
                          tbl_Vendas[Qtd]; tbl_Vendas[Receita]);
                tbl_Vendas[Vendedor]=C4);
         4; -1);
       10)

EMPILHARH escolhe as colunas que interessam, FILTRO recorta o vendedor, CLASSIFICAR ordena pela quarta coluna (receita) em ordem decrescente e PEGAR fica com as dez primeiras.

Passo 4: resumo mensal documentado com LET. O mês sai da coluna Data, que está como texto: somar zero converte o texto em data, e MÊS extrai o número do mês, que ordena corretamente (ao contrário de "Jan", "Fev"...).

J6: =LET(
       data_txt;  tbl_Vendas[Data];
       mes;       MÊS(data_txt+0);
       receita;   tbl_Vendas[Receita];
       do_vend;   tbl_Vendas[Vendedor]=C4;
       AGRUPARPOR(mes; receita; SOMA; 0; 1; 1; do_vend))

O quinto argumento (1) acrescenta o total geral, que confere com C6; o sexto (1) ordena pelo mês, crescente.

Passo 5: gráfico. A mensagem do resumo mensal é a evolução no tempo: linhas. Selecione os 12 meses (sem a linha de total) e insira um gráfico de linhas. O gráfico mostra uma vendedora com dois picos, em março e setembro, e meses fracos no começo do ano e em novembro.

Tabela 4.11. Resumo mensal de Carla Reis (R$ mil). Total do ano: 700.442,46.

Conferência do briefing. Tabela (tbl_Vendas); relatório com FILTRO + EMPILHARH + CLASSIFICAR (e PEGAR) controlado pelo menu; resumo mensal com AGRUPARPOR; gráfico de linhas para evolução; fórmula documentada com LET. Nenhuma função proibida. Troque o vendedor no menu e o painel inteiro se refaz: indicadores, relatório, resumo e gráfico. Construído uma vez, ele trabalha sozinho.

O caminho à frente

O Tools termina aqui, mas várias perguntas deste capítulo ficam com resposta melhor nos módulos seguintes:

• Power Query: consolidar dezenas de arquivos de uma pasta, tratar bases sujas e atualizar tudo com um clique (Atualizar Tudo). É a resposta para "e se forem 80 tabelas?".
• Modelagem: usar estas peças para construir o modelo dos três demonstrativos, simuladores, Atingir Meta e Tabela de Dados, Monte Carlo, Solver e cadeias de Markov.
• VBA: automação por eventos e rotinas dentro do Excel.
• IA para análise: quando pedir código a um assistente e quando resolver com fórmula.

4.9Exercícios propostos

Use a base Vendas do EBP_Tools_2026_Aluno.xlsx (Tabela renomeada para tbl_Vendas) e, nos itens 8 e 9, o EBP_Tools_Arquivo3@2026.xlsx.

1. Com AGRUPARPOR, monte a receita por vendedor, sem total, ordenada da maior para a menor. Quem lidera e com quanto? Quem fecha o ranking?
2. Com PEGAR sobre o resultado do item 1, mostre só os três primeiros vendedores. Qual fórmula mostraria os dois últimos?
3. Calcule a receita do produto Monitor 27 por três caminhos diferentes (SOMASES, SOMARPRODUTO e SOMA com FILTRO) e confira que dão o mesmo número.
4. Escreva, com LET, a margem bruta percentual da base inteira: receita total, custo total (Qtd × CustoUnit) e margem = (receita − custo) ÷ receita.
5. Crie no Gerenciador de Nomes a função TicketMedio = LAMBDA(r; n; r/n) e use-a para o ticket médio do vendedor Hugo Barros (receita ÷ número de vendas).
6. Monte a lista "Todas as regiões" + regiões em ordem alfabética com EMPILHARV, para um menu suspenso. Quantas opções o menu terá?
7. Com PIVOTPOR, monte a receita por categoria (linhas) e região (colunas). Qual é a receita de Esporte no Sul?
8. Na aba Consolidado, qual fórmula dá a Receita Líquida consolidada de 2028E? Qual é o valor?
9. Na Analise_Consolidada, com C8 = 2028E, qual SBU tem o maior Caixa? Qual o Caixa consolidado?

Material de apoio às aulas, de uso exclusivo dos alunos da BTC. É proibido reproduzir ou compartilhar este conteúdo sem autorização expressa da BTC. Ler o disclaimer

No módulo de Tools você conheceu as peças do Excel: atalhos, referências, PROCX, SOMASES, matrizes dinâmicas, LET e LAMBDA. Volte à imagem do balde de Lego: até aqui você virou o balde e aprendeu o que cada peça faz. Agora é hora de montar. Na Modelagem, as peças viram construções inteiras (um simulador de quadro de funcionários, um modelo dos três demonstrativos, uma simulação de Monte Carlo), e cada construção passa a fazer parte do seu repertório. Quando surgir um projeto maior, você não começa do tijolo: começa de casas já prontas.

O módulo tem quatro sessões. Esta primeira trata dos fundamentos: o que é um modelo, como planejá-lo, como organizar o arquivo para que outra pessoa entenda e confie no resultado, e quais práticas modernas deixam o modelo mais legível e mais fácil de manter. Tudo isso é aplicado no case GAP, que você vai reconstruir do zero: um simulador que dimensiona o quadro de vendedores, apoios e operadores de caixa de 20 lojas e responde se vale a pena investir R$ 500 mil numa convenção de vendas.

1.1O que é um modelo

Um modelo é uma representação simplificada de um pedaço da realidade, feita para apoiar uma decisão. A frase do slide resume bem: modelo não é planilha grande; é uma máquina de responder uma pergunta que foi feita antes de abrir o Excel.

Dois exemplos fora do Excel ajudam a fixar a ideia. O aplicativo de mapas tem muito menos detalhe do que a rua: não mostra as fachadas, os buracos nem as árvores. Isso é proposital, porque o objetivo é chegar ao destino, e detalhe demais atrapalharia. A maquete de um prédio no shopping também é um modelo: preserva a escala e a aparência, mas deixa de fora a altura dos interruptores. Ela existe para apoiar uma decisão de outra pessoa, o visitante que passa pelo corredor e decide se deixa o contato com o corretor. Em ambos os casos, o que entra e o que fica de fora depende de qual decisão o modelo apoia e de quem vai usá-lo.

Daí saem as quatro ideias do slide:

• Todo modelo equaciona um pedaço da realidade para suportar uma decisão.
• Pergunta primeiro, planilha depois. A estrutura do modelo nasce da pergunta de negócio, não do layout que você costuma usar.
• Um bom modelo é auditável: outra pessoa entende o fluxo da informação sem precisar perguntar ao autor.
• Modelagem é disciplina, não talento. O processo importa mais que a genialidade de uma fórmula.

Decompor a pergunta em drivers

A técnica central de quem modela é decompor. Ninguém estima o lucro de uma empresa chutando um número de lucro. Você quebra o lucro em receita menos custos; a receita em preço vezes volume; os custos em fixos e variáveis; e segue até chegar a variáveis que consegue estimar ou buscar numa fonte. Depois, faz o caminho de volta com fórmulas. Cada variável dessa árvore é um driver do resultado.

O nível de detalhe deve ser proporcional ao impacto. Pare de decompor quando o próximo nível não mudaria a resposta. No GAP, por exemplo, não faz sentido modelar a venda minuto a minuto: a hora cheia basta para dimensionar o quadro.

1.2Tipos de modelo

Tabela 1.1. Tipos de modelo. Os modelos de simulação e de otimização são tema dos Capítulos 3 e 4.

Os tipos costumam aparecer em sequência na vida de um modelo. O cálculo de quanto você precisa tirar na última prova para fechar a média é um modelo estático: uma resposta e pronto. Quando você passa a perguntar "e se eu for mal, médio ou bem na segunda prova?", ele vira um modelo de cenários. Cenários também servem para projetos: uma "chave" que liga ou desliga um projeto no modelo mostra o resultado com e sem ele. O modelo de simulação troca as três versões por milhares de sorteios das premissas incertas e conta em quantos deles o resultado cai em cada faixa. O de otimização procura a melhor combinação de decisões, como a rota mais curta para entregar 40 pacotes em endereços diferentes.

Top-down e bottom-up

Outra classificação útil é a direção da estimativa. No modelo top-down você parte do mercado e desce até a empresa. No bottom-up você parte da capacidade ou do histórico da própria operação e sobe. Fazer os dois é uma das melhores checagens que existem: se eles discordam muito, alguma premissa está errada.

Top-down:  200.000.000 × 50% × 60 × 40% × 0,5%  = 12.000.000 L
Bottom-up: 5 × 2.000 × 2 × 50 × (1 − 20%)       =    800.000 L

1.3O processo padrão de modelagem

O slide organiza o trabalho em seis etapas, que também são o esqueleto das quatro sessões do módulo.

1. Problema. Qual decisão o modelo apoia, quem decide e quando.
2. Dados. O que está disponível, de onde vem e com que confiança.
3. Metodologia. Como transformar os dados na resposta.
4. Resposta. Construir o modelo e calcular.
5. Checagem. A resposta faz sentido? Quando uma premissa muda, o resultado se move na direção esperada?
6. Sensibilidade. Quais premissas mais mexem no resultado?

Planejamento a fundo: da pergunta ao modelo

O passo mais barato e mais ignorado é o planejamento. Antes de qualquer célula, três perguntas definem o modelo:

Tabela 1.2. As três perguntas do planejamento.

Checagem, teste de estresse e sensibilidade

A checagem começa pelo bom senso: o modelo responde como a realidade responderia? Em aula, o professor distingue dois testes que usam a mesma mecânica de alterar premissas:

• Teste de estresse: levar uma premissa a valores extremos e ver se o modelo continua coerente.
• Análise de sensibilidade: variar uma premissa em pequenos passos e medir quanto o resultado anda. Feita em várias premissas, mostra a qual delas o modelo é mais sensível.

Lucro = Receita em US$ × Câmbio − Custo em R$
      = 1.000.000 × 5,10 − 3.500.000 = 1.600.000

Descobrir que o modelo é muito sensível a uma premissa não é um problema em si. O problema é ser muito sensível a uma premissa em que você tem pouca confiança. Nesse caso há dois caminhos: decompor a premissa em partes mais confiáveis ou chegar ao número por outro caminho, por exemplo trocando uma estimativa top-down por uma bottom-up baseada no histórico.

1.4Determinístico × estocástico

A pergunta que separa os dois mundos é: a mesma entrada dá sempre a mesma saída?

Tabela 1.3. Problemas determinísticos e estocásticos.

A folha de pagamento tem uma resposta certa: horas trabalhadas, valor da hora, horas extras, faltas, férias. Por mais itens que a conta tenha, ou o número está certo ou está errado. Já a chuva de amanhã não tem resposta certa: o aplicativo informa "50% de chance de 20 mm". Quanto mais perto do evento, melhor a previsão, porque parte da incerteza já foi eliminada.

A maior parte do que se faz no Excel é determinística. O case GAP é um bom exemplo de fronteira: dado o orçamento de vendas, o quadro é uma conta exata; mas o orçamento em si é uma estimativa. O case trata a venda como dada e por isso o modelo é determinístico.

1.5Layout: premissas, cálculo e resultado

A regra mais importante de organização é separar onde se insere informação de onde se calcula. O slide propõe cinco camadas:

Tabela 1.4. Camadas do layout padrão.

Nem todo modelo precisa das cinco. Separar premissas e dados do cálculo vale sempre: facilita a organização, a manutenção e a auditoria. Até onde ir na camada de resultado depende do usuário. Um modelo para uso próprio pode terminar na aba de cálculo; um modelo para a diretoria pede uma aba de resultados e, muitas vezes, uma capa de resumo, que responde em segundos à primeira pergunta de quem abre o arquivo: "tem algum problema aqui que eu preciso investigar?".

Pivotado para ler, tabelado para calcular

Na aula, ao tentar transformar a curva de vendas da aba Premissas em Tabela, apareceu uma lição de estrutura. A curva está pivotada: uma linha por tipo de loja e uma coluna por hora. Esse formato é ótimo para uma pessoa ler, mas ruim para fórmulas, porque cada hora nova vira uma coluna nova. O formato tabelado tem uma linha por combinação (tipo × hora) e uma coluna por variável. É o que o computador lê bem: vira Tabela, filtra, soma com SOMASES e cresce para baixo.

1.6O que fazer e o que não fazer

Tabela 1.5. Boas e más práticas de construção.

Nada de hard number

Hard number é um número digitado dentro de uma fórmula. É o erro mais comum e o mais caro de manter.

Errado:  =176*100                → 17.600
Certo:   C3: 8      (horas por dia)
         C4: 22     (dias úteis por mês)
         C5: 100    (R$ por hora)
         C7: =C3*C4*C5            → 17.600

Vale explicitar até as premissas que parecem óbvias. O case GAP traz meses por ano = 12 na aba Premissas. Assim, toda fórmula que multiplica por essa célula diz, sozinha, que está anualizando um valor mensal.

Para premissas, o professor recomenda quatro colunas. O Excel tem mais de 16 mil colunas; usar quatro em vez de uma não custa nada e deixa o modelo legível.

Tabela 1.6. Premissas documentadas (valores da aba Premissas do GAP; as fontes são ilustrativas).

Padrão de cores

O padrão que o curso usa: fundo cinza com fonte azul para input (onde o usuário pode digitar) e fundo branco com fonte preta para cálculo (onde deve haver uma fórmula). Só isso já entrega a maior parte do valor. Quem quiser ir além marca também o histórico (números digitados que não devem mudar) com uma terceira cor. As abas também ganham cor: cinza para entrada e etapas intermediárias; cores vivas para o que o usuário lê.

1.7Práticas modernas de construção

O que mudou nos últimos anos não foi a lógica dos modelos, mas as ferramentas para escrevê-los.

Tabela 1.7. Práticas antigas e modernas.

Tabelas nomeadas

Uma Tabela (Ctrl+T ou Inserir › Tabela) cresce sozinha quando entram linhas novas, e as fórmulas que apontam para ela acompanham. Dê sempre um nome com prefixo: em Design da Tabela › Nome da Tabela ou em Fórmulas › Gerenciador de Nomes. Com o prefixo tbl_, basta digitar tbl numa fórmula para ver todas as Tabelas do arquivo.

=tbl_Vendas[Loja]                → as 20 lojas (sem o cabeçalho)
=tbl_Vendas[[#Tudo];[Loja]]      → idem, com o cabeçalho
=tbl_Vendas[#Cabeçalhos]         → só a linha de títulos
=PROCX("Loja 15";tbl_Vendas[Loja];tbl_Vendas[Média Mensal])  → 1.502.803,08

LET: nomear as etapas

O LET, visto no Tools, recebe pares nome; valor e termina com o cálculo final, que pode usar todos os nomes. Os ganhos são três: a fórmula se explica sozinha, cada etapa é calculada uma vez só (mais rápido) e, quando o resultado é uma matriz despejada, tudo fica coeso numa única célula.

=PROCX(Loja;tbl_Vendas[Loja];tbl_Vendas[Média Mensal])/Premissas!C15/Premissas!C23

=LET(venda;  PROCX(Loja;tbl_Vendas[Loja];tbl_Vendas[Média Mensal]);
     preco;  Premissas!C15;
     dias;   Premissas!C23;
     venda/preco/dias)

LAMBDA: a regra de negócio vira função

Se o LET nomeia variáveis, o LAMBDA nomeia uma fórmula inteira. Em Fórmulas › Gerenciador de Nomes › Novo, você dá um nome e escreve, em "Refere-se a", os parâmetros e o cálculo. A função passa a aparecer no autocompletar, como qualquer função do Excel, sem VBA.

=LAMBDA(itens; produtividade; ARREDONDAR.PARA.CIMA(itens/produtividade;0))

=POSICOES(30;15)   → 2 vendedores
=POSICOES(30;6)    → 5 apoios
=POSICOES(30;8)    → 4 operadores  (30 ÷ 8 = 3,75, arredondado para cima)

Seletor de cenário moderno

O seletor de cenário tem duas peças: uma célula com validação de dados em lista (Pessimista, Base, Otimista) e uma fórmula que traduz a escolha num número. A forma recomendada no slide é PARÂMETRO (SWITCH):

=PARÂMETRO(cenário; "Otimista"; 1,15; "Base"; 1; "Pessimista"; 0,85)

O primeiro argumento é a expressão avaliada; depois vêm pares valor procurado; resultado. Um último argumento sem par funciona como resultado padrão. Isso substitui o antigo Gerenciador de Cenários (um menu escondido que poucos encontram) e elimina o INDIRETO dos modelos antigos, que é volátil (recalcula a cada alteração em qualquer célula).

=PARÂMETRO(K7;"Pessimista";10%;"Base";15%;"Otimista";20%)
=ESCOLHER(CORRESP(K7;{"Pessimista";"Base";"Otimista"};0);10%;15%;20%)
=PROCX(K7;N4:N6;O4:O6)        (cenários em N4:N6, ganhos em O4:O6)

1.8Checks automáticos e auditoria

Um modelo correto se prova sozinho, não porque o autor jura que está certo. Um check é uma célula dedicada que calcula a mesma coisa por dois caminhos e mostra a diferença, separada do resultado principal.

• Soma cruzada: total por linha = total por coluna. No GAP, a folha somada por loja tem de bater com a folha somada por cargo.
• Totais conhecidos: a curva de vendas de cada tipo de loja soma 100%; não pode distribuir 90% nem 120% das vendas do dia.
• Continuidade e sinais: datas sem buracos, custos sempre negativos (ou sempre positivos), estoques nunca negativos.
• Caminhos diferentes: top-down contra bottom-up; valuation por fluxo de caixa descontado contra múltiplos. No modelo dos três demonstrativos (Capítulo 2), o check por excelência é o Balanço fechar: Ativo = Passivo + Patrimônio Líquido.

R4: =Q4-1          → 0

Ferramentas de auditoria

Quando você herda um modelo, as ferramentas clássicas ajudam a rastrear de onde vem cada número. Quando você constrói, o ideal é que elas quase não sejam necessárias: LET nomeia as etapas, as referências estruturadas dizem que dado está sendo usado e as premissas isoladas garantem uma fonte da verdade por número.

Tabela 1.8. Ferramentas de auditoria. O duplo clique leva aos precedentes só com a edição na célula desligada (Arquivo › Opções › Avançado), como recomendado no Capítulo 1 do Tools.

1.9Case GAP: antes e depois

O case GAP é um clássico do curso. A versão legada resolvia o orçamento de vendas por loja com técnicas que hoje têm substitutas melhores. A versão 2026 é o mesmo case com técnica moderna, e é ela que você vai construir.

Tabela 1.9. O case GAP antes e depois.

1.10Exercícios resolvidos

Exercício 1: reconstruir o GAP

Arquivo: EBP_Mod_GAP@2026.xlsx. As abas visíveis são EBP (capa), Calculos (vazia, para construir), Premissas_>>> (separador), Projecao_Vendas (orçamento de vendas por loja), Premissas (curvas, produtividade, preço, custos e períodos) e Calculos_Modelo (a estrutura do simulador, com valores digitados).

Tarefa do slide: usando as premissas fornecidas, construir o seletor, o cálculo por loja com LET e um check automático. Tarefa do case: desenvolver o simulador do zero, dividido em premissas, área de cálculo e área de consolidação, chegar ao quadro e ao custo total por cargo e por loja e, com o mesmo modelo, analisar a convenção de vendas.

A aba Calculos_Modelo mostra o esqueleto do simulador com números digitados. A forma mais prática, usada em aula, é transformar esse esqueleto num modelo vivo, trocando cada número por uma fórmula (ou copiar a estrutura para a aba Calculos e trabalhar lá). As telas a seguir usam a Calculos_Modelo. A resolução segue as quatro etapas propostas pelo professor.

Etapa 1: premissas ligadas e itens por dia

1. Tabela e lista de lojas. Crie a tbl_Vendas (Exemplo 1.5). A validação de dados não aceita referência estruturada direto na fonte; por isso, numa área auxiliar (uma aba Listas, por exemplo), escreva =tbl_Vendas[Loja] em B5 e dê ao despejo o nome LST_Lojas (Gerenciador de Nomes, Refere-se a =Listas!$B$5#). O prefixo LST_ agrupa as listas no autocompletar.
2. Seletor da loja. Em E4, Dados › Validação de Dados › Lista, fonte =LST_Lojas. Dê à célula E4 o nome Loja (digite na Caixa de Nome e tecle Enter): as fórmulas ficam mais legíveis e a referência já nasce absoluta.
3. Tipo e venda. Duas buscas na Tabela:

E5:  =PROCX(Loja;tbl_Vendas[Loja];tbl_Vendas[Tipo];"";0)            → Grande
E20: =PROCX(Loja;tbl_Vendas[Loja];tbl_Vendas[Média Mensal];"";0)    → 1.502.803,08

4. Premissas. Cada bloco vem da aba Premissas com uma fórmula que derrama:

E8:  =Premissas!$C$10:$C$12    → 15; 6; 8     (derrama em E8:E10)
E14: =Premissas!$C$18:$C$20    → 5.000; 3.500; 2.000
E19: =Premissas!C15            → 200
E21: =Premissas!$C$23:$C$24    → 22; 12

Apague antes os números digitados que estão no caminho; senão, o resultado é #DESPEJAR!.

5. Itens vendidos por dia.

E23: =(E20/E19)/E21    → 341,55

A venda em reais dividida pelo preço médio dá os itens vendidos no mês; dividida pelos dias úteis, os itens por dia. O case assume que a loja só abre em dias úteis.

Etapa 2: itens por hora e posições necessárias

Itens por hora. A curva depende do tipo da loja. O caminho errado é um SE ("se Grande, curva do Grande; senão, curva do Pequeno"): com 200 tipos, seriam 200 SEs. O caminho certo é uma busca que devolve a linha inteira da curva:

B30: =Loja
E30: =E5
F30: =PROCX($E$30;Premissas!$B$4:$B$5;Premissas!$D$4:$P$5;"";0)*$E$23

O PROCX procura "Grande" em B4:B5 e devolve D5:P5, as 13 horas de uma vez; multiplicado pelos itens por dia, derrama em F30:R30. Para uma loja Pequena, as duas últimas horas (21h e 22h) ficam zeradas, porque a loja de rua fecha mais cedo.

Posições por hora. Divida os itens de cada hora pela produtividade de cada cargo e arredonde para cima, porque a premissa é nunca perder venda. Às 15h, a Loja 15 vende 30,74 itens; dois vendedores atendem 30. A GAP prefere um terceiro vendedor parcialmente ocioso a perder uma venda (e a imagem de uma marca premium com cliente esperando). O quadro de cada cargo é o máximo do dia.

A versão célula a célula seria =ARREDONDAR.PARA.CIMA(F$30/$E8;0) copiada em F33:R35, com =MÁXIMO(F33:R33) em S33:S35. A versão moderna, uma única fórmula em F33:

F33: =LET(posicao; ARREDONDAR.PARA.CIMA(F30#/E8#;0);
          EMPILHARH(posicao; BYROW(posicao; MÁXIMO)))

• F30#/E8# divide uma linha (13 horas) por uma coluna (3 cargos) e gera a grade 3 × 13.
• BYROW(posicao; MÁXIMO) aplica MÁXIMO linha a linha (um MÁXIMO comum devolveria o maior valor da grade inteira). Se a sua versão do Excel não aceitar o nome da função direto, escreva BYROW(posicao; LAMBDA(linha; MÁXIMO(linha))).
• EMPILHARH (HSTACK) junta a grade e a coluna de máximos, que vai parar em S33:S35.

O ganho não é só estético. Se a loja Grande passar a abrir às 23h e a curva ganhar uma coluna, a grade cresce e a coluna de máximos anda junto, sem quebrar nada. MAIOR (LARGE) também serviria no lugar de MÁXIMO, com a vantagem de pedir o segundo ou o terceiro maior valor, se um dia isso for útil.

Etapa 3: consolidar as 20 lojas e calcular a folha

O modelo calcula uma loja por vez, a escolhida em E4. Para ver as 20 de uma vez, o professor usa a Tabela de Dados (Data Table), recurso que o Capítulo 3 aprofunda. A ideia: o Excel coloca cada loja da coluna U em E4, recalcula o modelo e anota o resultado ao lado.

1. Linha de referência. Em V6, traga o quadro da loja selecionada, já na horizontal:

V6: =TRANSPOR(PEGAR(F33#;;-1))    → 3   6   4

PEGAR (TAKE) com −1 colunas pega a última coluna do despejo, a dos máximos, qualquer que seja o número de horas. Um =TRANSPOR(S33:S35) funcionaria hoje, mas quebraria se a grade crescesse.

2. Monte a tabela. As lojas ficam em U7:U26 (ou =tbl_Vendas[Loja], se quiser a lista viva). Selecione U6:X26, incluindo a linha de referência.
3. Dados › Teste de Hipóteses › Tabela de Dados. Deixe "Célula de entrada da linha" vazia e, em Célula de entrada da coluna, aponte E4, porque as lojas estão numa coluna e é em E4 que cada uma deve entrar. A célula de entrada precisa estar na mesma aba da tabela.

4. Custos. O custo mensal de cada loja é a soma de quantidade × custo dos três cargos. A dificuldade é que as quantidades estão em linha (V:X) e os custos em coluna (E14:E16). Há dois caminhos:

Z7:  =SOMARPRODUTO(V7:X7;TRANSPOR($E$14:$E$16))    → 10.500 (copiar até Z26)
Z7:  =MATRIZ.MULT(V7:X26;E14#)                     → derrama as 20 lojas
AA7: =Z7#*$E$22                                    → custo anual

MATRIZ.MULT (MMULT) multiplica uma matriz 20 × 3 por uma 3 × 1 e devolve 20 × 1. Na multiplicação de matrizes a ordem importa: MATRIZ.MULT(E14#;V7:X26) dá erro. Quem não se sente à vontade com matrizes pode ficar com SOMARPRODUTO e TRANSPOR, que chegam ao mesmo número.

5. Totais por cargo e check.

V28: =SOMA(V7:V26)                     → 40   (copiar para W28:X28)
V30: =V28:X28*TRANSPOR(E14#)           → 200.000; 294.000; 130.000
V31: =V30#*$E$22                       → 2.400.000; 3.528.000; 1.560.000
Z30: =SOMA(Z7#)                        → 624.000
AA31: =SOMA(AA7#)                      → 7.488.000
AA33: =AA31-SOMA(V31#)                 → 0  (check: por loja − por cargo)

Tabela 1.10. Resultado da primeira requisição: quadro e custo por loja (valores por loja; lojas com o mesmo quadro agrupadas). A folha anual é de R$ 7.488.000: R$ 2,4 milhões em vendedores, R$ 3,53 milhões em apoios e R$ 1,56 milhão em operadores.

Etapa 4: vale a pena fazer a convenção?

A segunda requisição: a convenção custa R$ 500 mil; RH e Comercial acreditam que a produtividade suba entre 10% e 20% no ano; a GAP não aprova projetos com retorno abaixo de 12% ao ano (a taxa mínima de atratividade, TMA). O caminho do modelo: mais produtividade → menos gente no pico → folha menor. A economia de folha paga o investimento?

Monte o bloco numa área livre da aba (I4:K20), com as entradas em azul:

K6:  500000                            Investimento
K8:  10%                               Ganho de produtividade
K10: 12%                               TMA
K12: 7488000                           Folha sem projeto (valor colado)
K13: =AA31                             Folha com projeto (ligada ao modelo)
K15: =K12-K13                          Economia de folha
K17: =K15-K6                           Ganho líquido
K18: =K17/K6                           ROI
K20: =SE(K18>=K10;"Investir";"Não investir")

Dois detalhes fazem o bloco funcionar:

• A folha sem projeto é colada como valor (Ctrl+Alt+V › Valores) enquanto o ganho ainda é 0%. Ela é a fotografia do cenário atual; a folha com projeto continua ligada ao modelo e reage ao ganho.
• O ganho entra nas premissas do modelo. Em E8: =Premissas!$C$10:$C$12*(1+K8). Com 10%, a produtividade vai a 16,5, 6,6 e 8,8 itens por hora, e a Tabela de Dados recalcula as 20 lojas sozinha.

Qual ganho usar? A promessa é "de 10% a 20%". Um analista conservador usa o piso: se o projeto se paga quando a área entrega o mínimo prometido, ele pode ser aprovado.

Com 10% de ganho, a Loja 15 passa de 3 para 2 vendedores e de 6 para 5 apoios, e o quadro total cai de 189 para 176 pessoas (37 vendedores, 78 apoios e 61 operadores). A economia de R$ 528 mil mal cobre o investimento: o ganho líquido é R$ 28 mil, um retorno de 5,6%. Decisão: não investir nas condições propostas. A formatação condicional na célula de decisão (texto que contém "Não" em vermelho; caso contrário, verde) deixa a resposta visível na hora.

Sensibilidade ao investimento. A TMA não está nas mãos do comercial; o ganho de produtividade também não é garantido. O valor investido, sim: uma convenção num destino mais barato poderia entregar os mesmos 10% gastando menos. Uma segunda Tabela de Dados mostra o ROI para vários investimentos:

P5: 500000     P6: -10000     P7: 16      (base, passo e quantidade)
P10: =SEQUÊNCIA(P7;1;P5;P6)              → 500.000 até 350.000
Q9:  =K18                                → linha de referência (ROI)
Selecione P9:Q25 › Tabela de Dados › célula de entrada da coluna: K6

Como a lista de investimentos é uma SEQUÊNCIA, mudar a base ou o passo refaz a tabela inteira. Uma formatação condicional por fórmula pinta a linha toda: aplique a P10:Q25 a regra =$Q10>=$K$10 em verde e =$Q10<$K$10 em vermelho. O $ antes do Q trava a coluna do ROI, e a cor vale para as duas colunas.

A recomendação ao cliente muda de "não" para "sim, com condições": a convenção se paga se custar no máximo R$ 471.428,57. Na prática, o professor sugere liberar algo como R$ 400 a 420 mil, que deixa folga caso o custo estoure em 10% e ainda mantém o retorno acima da TMA (ROI de 32,0% a 400 mil; de 25,7% a 420 mil).

Seletor de cenário. Por fim, troque o ganho digitado em K8 pelo seletor do Exemplo 1.8. Em K7, uma validação de dados com Pessimista, Base e Otimista; em K8:

K8: =PARÂMETRO(K7;"Pessimista";10%;"Base";15%;"Otimista";20%)

Tabela 1.11. A convenção nos três cenários, com investimento de R$ 500 mil e TMA de 12%.

Desafio extra: um segundo seletor (trimestre)

O slide propõe um segundo seletor independente, por exemplo o trimestre, combinado ao primeiro. Crie uma célula Periodo com validação em lista (Ano, 1T, 2T, 3T, 4T) e troque a fórmula da venda em E20 por:

E20: =LET(linha; FILTRO(tbl_Vendas[[Janeiro]:[Dezembro]]; tbl_Vendas[Loja]=Loja);
          ini;   PARÂMETRO(Periodo; "1T";1; "2T";4; "3T";7; "4T";10; 0);
          SE(ini=0; MÉDIA(linha); MÉDIA(ESCOLHERCOLS(linha; SEQUÊNCIA(1;3;ini)))))

O PARÂMETRO traduz o trimestre no mês inicial (o 0 final é o padrão para "Ano"); ESCOLHERCOLS (CHOOSECOLS) pega os três meses do trimestre, e a média deles é a venda mensal daquele período. Os dois seletores convivem: o de produtividade age em E8, o de período em E20, e a Tabela de Dados recalcula as 20 lojas para qualquer combinação.

Para a Loja 15, a venda mensal vai de 1.500.173 (2T) a 1.504.870 (1T), e os itens por dia de 340,95 a 342,02. Em nenhum trimestre o quadro de nenhuma loja muda: a folha continua em R$ 7.488.000. A conclusão também é informação: o orçamento da GAP quase não tem sazonalidade, e dimensionar pela média do ano é seguro.

1.11Exercícios propostos

Use o modelo do GAP construído no Exercício 1 (ganho de produtividade 0%, salvo indicação).

1. Selecione a Loja 9. Quantos itens ela vende por dia, quantos itens vende na hora de pico e qual é o seu quadro?
2. Classifique como determinístico ou estocástico: (a) o 13º salário de um funcionário; (b) o número de clientes que entram numa loja no sábado; (c) o valor de uma parcela de financiamento com taxa fixa; (d) o câmbio no fim do ano.
3. Um colega escreveu em E23 a fórmula =E20/200/22. Que problemas ela tem? Reescreva-a.
4. A Área Comercial revisou o preço médio para R$ 250 (mesma venda em reais). Qual é o novo quadro total e a nova folha anual? Por que a folha cai?
5. Se as lojas abrissem 26 dias por mês, qual seria a folha anual?
6. Use a função POSICOES do Exemplo 1.7 para uma hora com 45 itens vendidos. Quantas pessoas de cada cargo são necessárias?
7. No cenário Otimista (20% de ganho), qual é a economia, o ganho líquido e o ROI da convenção de R$ 500 mil?
8. Com o ganho de 10%, qual é o investimento máximo que mantém o ROI em pelo menos 12%? E o ROI se a convenção custar R$ 450 mil?
9. Proponha dois checks automáticos para o modelo do GAP, além do check por loja × por cargo, e escreva as fórmulas.

Material de apoio às aulas, de uso exclusivo dos alunos da BTC. É proibido reproduzir ou compartilhar este conteúdo sem autorização expressa da BTC. Ler o disclaimer

No Capítulo 1 você montou modelos pequenos, de uma pergunta só. Agora o objeto é outro: o modelo que sustenta a análise de uma empresa inteira. Ele junta três relatórios que todo gestor financeiro conhece, a DRE, o fluxo de caixa e o balanço, e os amarra de tal forma que qualquer mudança numa premissa (o market share, o prazo dado ao cliente, o salário de um cargo) aparece ao mesmo tempo no lucro, no caixa e no patrimônio. No mercado ele é chamado de three statement model, o modelo dos três demonstrativos, e é a base do valuation por fluxo de caixa descontado.

O capítulo segue a construção na ordem em que ela acontece no arquivo: primeiro as abas de detalhe (receita, custos, folha, depreciação), depois a DRE, o capital de giro, o fluxo de caixa com a dívida rotativa e, por fim, o balanço com o check de fechamento. No final, o modelo pronto é avaliado com XVPL e XTIR. O modelo é grande, mas cada bloco usa peças que você já conhece: SE, SOMASES, SOMARPRODUTO, FILTRO, referências mistas e intervalos que crescem.

2.1Três lentes do mesmo negócio

Os três demonstrativos olham para a mesma empresa, mas cada um responde a uma pergunta diferente.

• DRE (demonstração do resultado): quanto a empresa ganhou ou perdeu no período. Registra receitas e gastos pelo regime de competência, ou seja, quando acontecem, não quando o dinheiro entra ou sai.
• DFC (demonstração do fluxo de caixa): quanto dinheiro entrou e saiu do caixa no período, e por quê.
• Balanço patrimonial: o que a empresa tem (ativo) e a quem deve (passivo e patrimônio líquido) num instante. A DRE e o fluxo de caixa são filmes; o balanço é uma foto do fim do período.

A frase do slide resume a lógica: lucro vira caixa, caixa vira patrimônio. Um modelo financeiro não são três abas soltas; são três lentes amarradas por essa seta. Por isso o modelo só está pronto quando os três estão prontos e conversam entre si.

O fluxo de caixa deste capítulo usa o método indireto, o mais comum em finanças corporativas: começa no lucro da DRE e faz dois tipos de ajuste. O primeiro devolve o que reduziu o lucro mas não saiu do caixa (a depreciação). O segundo inclui o que não passou pela DRE mas mexeu no caixa (a variação do capital de giro, o investimento em ativos, a dívida e os dividendos).

2.2Premissas, drivers e o case

O case da sessão é o modelo completo de Empresa de Varejo: uma rede que abre postos comerciais pelo Brasil, com receita detalhada por praça, folha separada em back-office e postos, capital de giro, estrutura de dívida com revolver, impostos e dividendos. O arquivo é o EBP_Mod_ModeloCompleto@2026.xlsx. A estrutura, as premissas e as abas de detalhe vêm prontas; as células em branco são as fórmulas que você constrói.

Tabela 2.1. Premissas do case. Capital social, dívida inicial e caixa mínimo são os valores usados em aula como cenário base.

A regra de organização é a da Sessão 1: premissas separadas do cálculo. Na aba Planilha_Calculos, as células de entrada têm fundo cinza e fonte azul; tudo o que é preto é fórmula. Cada aba de detalhe resolve uma parte do problema e entrega uma linha pronta para a Planilha_Calculos, que junta tudo nos três demonstrativos.

A linha do tempo dinâmica

Antes de qualquer número, o modelo precisa de um cabeçalho de datas. O arquivo traz a data inicial digitada em E3 e =DATAM(E3;1) (EDATE) copiado para a direita. O professor mostra a versão moderna, que deriva tudo de uma fórmula só.

E1: =SEQUÊNCIA(1;8*12)        → 1, 2, ..., 96   (1 linha, 96 colunas)
E3: =DATA(2026;E1#;1)         → jan-26 ... dez-33
E2: =ANO(E3#)                 → 2026 ... 2033

2.3A receita construída por praça

A receita não é um número solto: é construída de baixo para cima, a partir de um estudo de mercado. A aba Receita lista os 5.565 municípios do Brasil com o potencial líquido total de cada um (o tamanho do mercado inteiro, em R$ por ano). A construção tem três etapas.

1. Calcular o potencial da empresa em cada município e quantos postos cabem nele.
2. Montar uma lista dinâmica só com as praças que terão posto, com a data de inauguração.
3. Calcular a receita de cada praça mês a mês e somar.

Etapa 1: potencial X e número de postos

O potencial X é o potencial líquido multiplicado pelo market share alvo (2%). O número de postos segue a regra da venda mínima: cada posto precisa de R$ 1,2 milhão por ano. Uma cidade com potencial X de R$ 1.199.000 não recebe posto; com R$ 1.200.000 recebe um; o segundo só vem com R$ 2.400.000.

E11: =D11:D5575*C3                         → potencial X de todos os municípios
F11: =ARREDONDAR.PARA.BAIXO(E11#/$C$4;0)   → nº de postos
C7:  =SOMA(F11#)                           → 58 postos

Etapa 2: a lista dinâmica de praças

A segunda etapa traz para as colunas N:R só as 20 praças com posto, com potencial X, potencial mensal (÷ 12), nº de postos e data de inauguração. A lista precisa ser dinâmica: se o market share ou a venda mínima mudarem, praças entram e saem sozinhas.

O jeito clássico é criar um identificador único na coluna A, com um intervalo que cresce (o mesmo truque do "máximo até agora"), e buscar por ele:

A11: =SE(F11=0;0;MÁXIMO($A$10:A10)+1)      → 1 em Manaus, 2 em Belém...
N11: =PROCX(M11;$A$11:$A$5575;$B$11:$B$5575)

O jeito moderno, preferido em aula, é FILTRO:

N11: =LET(f; FILTRO(EMPILHARH(B11:B5575;E11:E5575;F11:F5575); F11:F5575>0);
          EMPILHARH(ESCOLHERCOLS(f;1;2); ESCOLHERCOLS(f;2)/$C$5; ESCOLHERCOLS(f;3)))
R11: =DATAM($V$10;SEQUÊNCIA(LINS(N11#);1;0))

Etapa 3: a escadinha de receita

Para cada praça e cada mês há três momentos: antes da inauguração (receita zero), no mês da inauguração (o potencial mensal) e depois. Depois, a receita repete a do mês anterior, a não ser no aniversário, quando cresce 5%.

V11: =SE(V$10<$R11; 0;
       SE(V$10=$R11; $P11;
         SE(CONT.SES($U11:U11;U11)=12; U11*(1+V$6); U11)))

Totais da aba, na coluna de cada mês:

U7: =SOMA(U11:U66)                          → receita bruta mensal
U8: =SOMASES($Q$11:$Q$66;U$11:U$66;">0")    → nº de postos abertos

Contar as células com receita não serve para o nº de postos: Belo Horizonte abre 4 postos de uma vez. SOMASES soma a coluna de postos das praças que já faturam. Em mar-26, com Manaus e Belém abertas, a receita bruta é 111.024,81 + 123.927,19 = 234.952,00 e há 2 postos.

2.4Custos e despesas: do lucro bruto ao EBITDA

A receita bruta entra na DRE com =Receita!U7:DL7 em E4, uma fórmula que derrama os 96 meses. A partir dela, as linhas seguintes já estão no arquivo.

Deduções:        =E4*E7          (E7 = 18% + 9,25% = 27,25%: ICMS e PIS/COFINS)
Receita líquida: =E4-E6
CMV:             =E4*E12         (E12 = CMV em % da receita bruta)
Lucro bruto:     =E9-E11

As deduções são os impostos sobre a venda e os cancelamentos. A receita líquida é a referência de todas as margens: margem bruta, margem EBITDA e o peso de cada despesa são calculados sobre ela.

CMV pela margem e pelo mix

A empresa vende 20 produtos, cada um com a sua margem; a aba CMV traz a margem de cada produto (coluna C) e a participação de cada um nas vendas, mês a mês (o mix).

F29: =SOMARPRODUTO($C$6:$C$25;F6:F25)   → margem média: 61,13%
F30: =1-F29                             → CMV: 38,87% da receita bruta

Folha do back-office

O back-office é a equipe de escritório: diretoria, financeiro, marketing, RH. A aba tem três etapas: custo mensal de cada cargo, quantidade de pessoas e evolução do custo com o dissídio.

G16: =E16*F16                                      → PLR: 60.000 × 6 = 360.000
H16: =E16*(1+SOMA($C$5:$C$7))*$C$9+$C$8*$C$10+G16  → custo anual
I16: =H16/$C$8                                     → custo mensal

A quantidade de pessoas combina duas lógicas. A primeira é o FTE por posto comercial: um analista financeiro dá conta de 30 postos, então o 31º posto exige o segundo analista. A segunda é a data de contratação: o CEO entra em janeiro, antes do primeiro posto. Prevalece a lógica que pede mais gente, e a ferramenta para isso é MÁXIMO, não um SE dentro de outro:

O16: =MÁXIMO(ARREDONDAR.PARA.CIMA(O$9/$D16;0); SE(E($J16<>"";O$15>=$J16);1;0))

Aqui o arredondamento é para cima: 31 postos ÷ 30 = 1,03 analista vira 2. O custo por cargo começa no valor da etapa 1 e cresce 5% todo setembro (mês do dissídio, em C11):

O32: =SE(MÊS(O$31)=$C$11; N32*(1+O$8); N32)
O44: =SOMARPRODUTO(O16:O25;O32:O41)        → folha total do mês

Em jan-26, só o CEO: 121.904. Em fev-26, com o primeiro posto, entram os outros nove cargos: 315.277,60. Em set-26, o dissídio leva a folha a 331.041.

Folha dos postos comerciais

Nos postos a regra se inverte: em vez de uma pessoa cuidar de vários postos, cada posto tem 1 coordenador e 3 vendedores. O custo tem parte fixa (salário com encargos, corrigido em setembro) e parte variável (comissão sobre a receita).

M15: =$D15*M$9                              → pessoas = por posto × postos
M25: =SOMARPRODUTO(M22:M23;M15:M16)         → fixo total
M29: =$F15*M$10                             → comissão = % × receita bruta
M39: =SOMA(M25;M32)                         → folha dos postos

A folha de back-office entra "de uma vez" no começo e depois cresce pouco: 30 postos ainda cabem num analista. A dos postos cresce com cada inauguração. Em 2027, a folha dos postos (11,3 milhões) já é quase o triplo da do back-office (4,2 milhões).

Marketing, aluguéis e outros

F6:  =SEERRO(F5-E5;F5)       → inaugurações do mês (variação do nº de postos)
F9:  =$C9*F4                 → 1% da receita bruta
F10: =$C10*F6                → R$ 15.000 por inauguração
F11: =$C11*F5                → R$ 3.000 por posto aberto
F21: =$C21*F5                → aluguel: R$ 6.000 por posto
F29: =$C29*F4                → outros: 0,3% da receita bruta

O EBITDA e o que ele diz

Depois do EBITDA, o resto da DRE é "tirar as letras de trás para frente": tira a depreciação (D e A) e chega no EBIT; soma o resultado financeiro (I) e chega no EBT; tira o imposto (T) e chega no lucro líquido.

2.5Depreciação e o fechamento da DRE

Cada inauguração exige R$ 70 mil de investimento (obra, mobiliário, vitrine). Esse gasto não entra na DRE de uma vez: vira ativo permanente e é lançado como despesa aos poucos, ao longo da vida útil, pela depreciação. Aqui, depreciação linear em 60 meses, sem valor residual: R$ 70.000 ÷ 60 = R$ 1.166,67 por mês por posto.

O professor avisa que este é o bloco mais desafiador do modelo. Dá para fazer em quatro linhas, mas a estrutura recomendada é uma matriz, como uma tabela de depreciação real: nas linhas, o mês em que o ativo foi adicionado; nas colunas, o mês corrente; no cruzamento, quanto aquele ativo deprecia naquele mês.

F4:  =SEERRO(F3-E3;F3)        → inaugurações
F10: =F4*$C$7                 → ativo permanente adicionado
F9:  =SOMA($F10:F10)          → ativo permanente acumulado

G17: =SE(G$15<$B17; 0;
       SE(G$15=$B17; $C17/$C$9;
         SE(SOMA($E17:F17)>=$C17; 0; $C17/$C$9)))
F7:  =SOMA(F16:F111)          → depreciação do mês
F8:  =SOMA($F7:F7)            → depreciação acumulada

Na Planilha_Calculos, três linhas vêm da aba Depreciacao: a depreciação (linha 27, positiva, porque a DRE subtrai), o ativo permanente (linha 63) e a depreciação acumulada (linha 73, com sinal negativo, porque é uma conta redutora do ativo). Os 58 postos somam 58 × 70.000 = R$ 4.060.000 de ativo permanente; como as inaugurações terminam em set-27, todo o ativo está depreciado em ago-32.

Resultado financeiro, IR/CSLL e dividendos

Essas linhas já vêm montadas no arquivo; vale entender o que cada uma faz.

F31: =E69*E114                     → receita financeira: caixa do mês anterior × 0,8%
F32: =E112*E78+E113*E79            → despesa financeira: revolver × 0,9% + dívida LP × 0,85%
E38: =SE(E36<0;0;E36*E39)          → IR/CSLL: 34% do EBT, só se positivo
E43: =SE(E41<0;0;E44*E41)          → dividendos: 25% do lucro, só se positivo

Os juros usam o saldo do mês anterior. Em fev-26: 11.878.096 × 0,8% = 95.024,77 de receita financeira e 2.000.000 × 0,85% = 17.000 de despesa; o EBT de fev-26 é −322.934,82 + 78.024,77 = −244.910,06.

2.6Capital de giro: onde o caixa fica preso

O lucro da DRE não vira caixa na hora. O estoque e as vendas a prazo seguram dinheiro; os fornecedores, ao dar prazo, financiam a empresa. Essa diferença é o capital de giro, e no varejo ela pesa muito.

Tabela 2.2. Drivers do capital de giro. A conta é uma regra de três: o gasto do mês ÷ 30 dá o gasto por dia; × dias dá o saldo.

F56: =F11*F52/30              → estoque:     43.155,34 × 100 ÷ 30 = 143.851,15
F58: =F9*F54/30               → duplicatas:  80.770,55 × 45 ÷ 30  = 121.155,83
F57: =SOMA(F20:F22)*F53/30    → fornecedores: 25.443,32 × 90 ÷ 30 = 76.329,97
F59: =F56+F58-F57             → capital de giro: 188.677,01
F60: =E59-F59                 → geração/consumo: −188.677,01

A soma dos três prazos dá o ciclo de conversão de caixa: 100 + 45 − 90 = 55 dias. Ciclo positivo significa que crescer consome caixa: cada posto novo prende dinheiro em estoque e recebíveis antes de devolver. É o que acontece com força em mar-27, quando São Paulo abre 20 postos e o capital de giro do mês aumenta R$ 3,4 milhões. No fim de 2033, estoque e duplicatas somam R$ 21,4 milhões: é ali que está o dinheiro da empresa.

2.7Do lucro ao caixa: o fluxo de caixa

O DFC parte do lucro e corrige o que não é caixa (depreciação) e o que ficou preso (capital de giro). Ele começa no lucro antes dos dividendos, porque o dividendo não é operacional: é um fluxo entre a empresa e os sócios.

Tabela 2.3. Os blocos do fluxo de caixa. No arquivo, a linha 94 tem o rótulo "Atividades operacionais", mas contém só a variação do capital de giro; o bloco operacional completo é a soma das linhas 90, 92 e 94.

Lucro antes de dividendos    −244.910,06
(+) Depreciação                 1.166,67
(−) Aumento do capital de giro −188.677,01
(−) Investimento               −70.000,00
(±) Financiamento                    0,00
(=) Fluxo de caixa do período −502.420,40

A fórmula do financiamento no arquivo é =E79-D79-E43: variação da dívida de longo prazo menos dividendos. Se houvesse aporte de sócios, entraria também a variação do capital social (+E81-D81). A dívida rotativa fica de fora de propósito: ela é o resultado do próximo bloco.

2.8O revolver e o caixa mínimo

E se o caixa projetado ficar abaixo do mínimo? O modelo puxa dívida rotativa automaticamente. Isso é o revolver (cash sweep): a dívida aparece quando falta caixa e é paga, com prioridade, quando sobra.

E99:  caixa inicial = caixa final do mês anterior
E103: =E102+E99                          → caixa projetado
E104: =E103-E100                         → disponível acima do caixa mínimo
E106: =SE(E104+D106>0; 0; E104+D106)     → dívida rotativa (saldo, com sinal negativo)
E107: =MÁXIMO(E103+E106+D106; E100)      → caixa no final do período

Na linha 106 o saldo do revolver fica negativo (é dívida); no balanço, a linha 78 traz =-E$106, positivo do lado do passivo. A lógica: se o disponível do mês mais o saldo anterior do revolver é positivo, a dívida é quitada (zero); senão, o saldo novo é exatamente o que falta. Enquanto há dívida, o MÁXIMO segura o caixa no mínimo; quando a dívida é quitada, o excedente volta a se acumular no caixa.

Caixa inicial (fev-27)          1.687.967,72
(+) Fluxo do período           −5.117.001,08
(=) Caixa projetado            −3.429.033,36
(−) Caixa mínimo                   10.000,00
(=) Necessidade de dívida      −3.439.033,36
Dívida rotativa: SE(−3.439.033,36 + 0 > 0; 0; −3.439.033,36) → 3.439.033,36 de dívida
Caixa final:     MÁXIMO(−3.429.033,36 − 3.439.033,36 + 0; 10.000) → 10.000

O revolver fecha o ciclo do modelo: caixa final → caixa do balanço → caixa inicial do mês seguinte → receita financeira do mês seguinte. É o que torna o modelo integrado e "vivo": mude um prazo, e a dívida, os juros, o lucro e o caixa se recalculam juntos. A célula D78, =MÁXIMO(E78:CV78), guarda o pico da dívida rotativa, a métrica que o Capítulo 3 vai analisar com Tabela de Dados.

2.9Fechando o balanço: amarração e check

Tabela 2.4. Composição do balanço do modelo.

Cada linha do balanço vem de um lugar já calculado: nenhuma é digitada, exceto a posição inicial (coluna D: R$ 2 milhões de dívida de longo prazo e R$ 10 milhões de capital social, que somados são o caixa inicial de R$ 12 milhões).

Ativo:  11.375.675,60 + 143.851,15 + 121.155,83 + 70.000 − 1.166,67 = 11.709.515,91
Passivo + PL: 76.329,97 + 0 + 2.000.000 + 10.000.000 − 366.814,06 = 11.709.515,91
E86: =E75-E84   → 0

2.10Funções financeiras: XVPL e XTIR

Com o modelo fechado, a pergunta muda de "quanto" para "quanto vale". As funções VPL e TIR, vistas no início do módulo, supõem períodos iguais. As versões com X trabalham com o calendário real de cada fluxo.

=XVPL(taxa; fluxos; datas)            EN: XNPV(rate; values; dates)
=XTIR(fluxos; datas; [estimativa])    EN: XIRR(values; dates; [guess])

• taxa: taxa de desconto anual.
• fluxos: os valores de caixa; para a XTIR, precisam incluir pelo menos um negativo (o investimento) e um positivo.
• datas: a data de cada fluxo. A primeira é a data-base: o primeiro fluxo não é descontado; cada um dos outros é dividido por (1 + taxa)^(dias desde a data-base ÷ 365).

A XTIR é a taxa que zera a XVPL. Ela é anualizada e consistente com o calendário. A TIR clássica sobre datas irregulares distorce o resultado sem avisar.

2.11Valuation por fluxo de caixa descontado

O valor de uma empresa, pelo método mais usado no mercado, é o valor presente dos caixas que ela vai gerar. Três decisões definem o cálculo: que fluxo descontar, a que taxa e o que fazer depois do horizonte.

O fluxo de caixa do modelo (linha 98) inclui receitas e despesas financeiras e o financiamento; para avaliar a empresa, esses itens saem. No modelo, o IR sobre o EBIT segue a mesma regra da DRE: 34% nos meses de EBIT positivo.

=XVPL(12%; FCL; datas)                     → −14.487.736  (96 meses de projeção)
Valor terminal = FCL de dez-33 × 12 ÷ (12% − 5%)
               = 221.267,63 × 12 ÷ 0,07    →  37.931.594  (em dez-33)
Valor terminal trazido a jan-26            →  15.458.501
Valor da empresa                           →     970.765
=XTIR(FCL com o valor terminal em dez-33; datas)   → 12,88% ao ano

A leitura de negócio é direta: do jeito que está, a expansão quase não cria valor. A empresa prende R$ 19,5 milhões em capital de giro, precisa de até R$ 9,3 milhões de dívida rotativa e só tem lucro anual a partir de 2029. O modelo serve para isso: mostrar onde mexer. Menos dias de estoque, prazos melhores, folha mais enxuta, margens maiores, e cada mudança aparece na hora no lucro, no caixa e no valor. O Capítulo 3 faz essa análise de forma sistemática, com Atingir Meta, Tabela de Dados e mapa de calor.

2.12Exercícios resolvidos

O exercício da sessão é a construção guiada dos três demonstrativos no arquivo EBP_Mod_ModeloCompleto@2026.xlsx, aba Planilha_Calculos. As abas de detalhe, resolvidas ao longo das seções 2.3 a 2.5, entregam as linhas prontas; o trabalho aqui é ligar tudo, mês a mês, e terminar no check igual a zero.

Exercício 1: a DRE, da receita ao lucro retido

Tarefa: preencher as linhas em branco da DRE puxando das abas de detalhe e estender todas as fórmulas até dez-33.

E4:  =Receita!U7:DL7                    E12: =CMV!F30:CW30
E18: =FoPag_Backoffice!O44:DF44         E19: =FoPag_Postos!M39:DD39
E20: ='Mkt&Alugueis&Outros'!F12:CW12    E21: ='Mkt&Alugueis&Outros'!F21:CW21
E22: ='Mkt&Alugueis&Outros'!F29:CW29    E27: =Depreciacao!F7:CW7

Cada ligação traz a linha inteira de uma vez. As demais linhas (deduções, lucro bruto, SG&A, EBITDA, EBIT, financeiro, IR, dividendos) já existem até a coluna J; basta copiá-las até CV. A Tela No Excel 2.10 mostra o resultado; os totais anuais estão na Tabela 2.5.

Exercício 2: capital de giro, ativo permanente e balanço

Tarefa: completar as auxiliares (dias, saldos, variação), trazer o ativo permanente e a depreciação acumulada e ligar cada linha do balanço à sua origem.

E63: =Depreciacao!F9:CW9                E73: =-Depreciacao!F8:CW8
D79: 2.000.000                          D81: 10.000.000
E100: 10.000 (caixa mínimo, copiado até CV)
E86: =E75-E84 (check)

As fórmulas de estoque, fornecedores, duplicatas, capital de giro e balanço seguem o Exemplo 2.10 e a Tabela 2.4; os dias (100, 90 e 45) são copiados até dez-33. Com a posição inicial em D79 e D81, o caixa inicial (D69 = D79 + D81) fica em R$ 12 milhões. A Tela No Excel 2.13 mostra o balanço e o check.

Exercício 3: fluxo de caixa e revolver

Tarefa: estender o DFC e o revolver até dez-33 e conferir o pico da dívida rotativa.

As fórmulas são as das seções 2.8 e 2.9, copiadas para a direita. Resultado: o caixa encosta no mínimo em mar-27, a dívida rotativa chega a R$ 9.300.071 em mar-28 e termina 2033 em R$ 6.904.373. O check dá zero em todos os meses.

Tabela 2.5. Resumo anual do modelo completo no cenário base. O fluxo de caixa de 2026 na tabela exclui o aporte inicial de R$ 12 milhões, que está na coluna D.

Exercício 4: quebrar a amarração de propósito

Tarefa (demo do slide): depois de tudo fechado, quebrar uma ligação e ver o check acusar.

Troque o lucro acumulado para somar o lucro antes dos dividendos: =D82+E41 no lugar de =D82+E46. Nada muda até o primeiro mês com dividendo; em mar-28 o check vira −18,55, exatamente o dividendo do mês, e a diferença cresce até −1.469.336,70 em dez-33, a soma de todos os dividendos. O erro mostra a regra: o dividendo saiu do caixa (no DFC), mas não saiu do patrimônio. Cada ponta precisa ter a sua contrapartida.

Desafio extra: visão anual com ÍNDICE e margem EBITDA com LAMBDA

Tarefa: consolidar os 12 meses de cada ano nas colunas CX:DE e criar a margem EBITDA como uma LAMBDA nomeada.

Para linhas de fluxo (receita, custos, lucro), o ano é a soma dos 12 meses. O arquivo usa SOMASE pela linha de anos; com ÍNDICE, a soma pega o bloco de 12 colunas pela posição:

CX4:  =SOMASE($E$2:$CV$2;CX$3;$E4:$CV4)                          (arquivo)
CX4:  =SOMA(ÍNDICE($E4:$CV4;CX$50-10):ÍNDICE($E4:$CV4;CX$50+1))  (com ÍNDICE)

Para linhas de saldo (estoque, caixa, dívida), o ano é o valor de dezembro, não a soma. O arquivo usa DESLOC com o deslocamento da linha 50 (11, 23, 35...); ÍNDICE faz o mesmo sem ser volátil:

CX56: =ÍNDICE($E56:$CV56;CX$50+1)        → estoque de dez-26: 3.355.482

Para a margem, defina em Fórmulas › Gerenciador de Nomes o nome MargemEBITDA com:

=LAMBDA(ebitda; rl; SEERRO(ebitda/rl; 0))

e use =MargemEBITDA(CX24;CX9): −28,4% em 2026 e 5,6% em 2033. A mesma função serve para as colunas mensais e para qualquer outro modelo do arquivo.

2.13Exercícios propostos

Use o modelo completo já ligado (cenário base: capital de R$ 10 milhões, dívida de longo prazo de R$ 2 milhões, caixa mínimo de R$ 10 mil). Volte ao cenário base depois de cada item.

1. Mude o market share para 3%. Quantas praças e quantos postos a empresa abre? Qual o pico da dívida rotativa?
2. Volte o market share para 2% e mude a venda mínima por posto para R$ 2,4 milhões por ano. Quantas praças e postos? O que acontece com a dívida rotativa e com o lucro?
3. Reajuste o aluguel por posto em 5% todo mês de janeiro, a partir de jan-27 (a lógica é a do dissídio). Qual o EBITDA e a margem EBITDA de 2033? E o pico da dívida rotativa?
4. Reduza os dias de estoque de 100 para 30 em todos os meses. Qual o novo pico da dívida rotativa?
5. Calcule as duplicatas a receber sobre a receita bruta, e não sobre a receita líquida. Qual o novo pico da dívida rotativa?
6. Qual a taxa anual equivalente aos 0,9% ao mês do revolver?
7. Refaça o valuation do Exemplo 2.14 com taxa de desconto de 15% ao ano. O que o resultado diz, comparado com a XTIR?
8. Na Tela No Excel 2.14, o check de mar-28 é −18,55. Que número do modelo é esse? E por que a diferença em dez-33 é −1.469.336,70?

Material de apoio às aulas, de uso exclusivo dos alunos da BTC. É proibido reproduzir ou compartilhar este conteúdo sem autorização expressa da BTC. Ler o disclaimer

No Capítulo 2 você construiu um modelo que responde a uma pergunta por vez: com estas premissas, quanto sobra de caixa, quanto a empresa precisa de dívida, quanto vale o negócio. Só que premissa é estimativa. A receita pode vir 10% abaixo, o CMV pode subir dois pontos, o sócio pode aportar menos capital. Este capítulo ensina a testar o modelo contra a incerteza, em três níveis de sofisticação.

O primeiro nível é perguntar ao contrário: em vez de "qual o resultado com esta premissa?", "que premissa produz este resultado?". É o Atingir Meta. O segundo é varrer uma faixa inteira de valores de uma ou duas premissas e ver como a saída responde: é a Tabela de Dados, que vira um mapa de calor com formatação condicional. O terceiro é deixar várias premissas variarem ao mesmo tempo, de forma aleatória, milhares de vezes, e ler o resultado como uma distribuição: é a simulação de Monte Carlo. O professor chama essa parte de clímax do módulo, porque junta quase tudo o que o curso ensinou até aqui: matrizes dinâmicas, referência de despejo, contagem por critério e gráficos.

3.1Atingir Meta: uma variável por vez

Todo modelo bem construído calcula de cima para baixo: premissas entram, resultado sai. Muitas perguntas de negócio, porém, vêm de baixo para cima. "Quanto preciso vender para empatar?" "Que preço leva o EBITDA a 1 milhão?" Dá para responder na tentativa e erro, digitando 20, 30, 25, 27, 27,5 na premissa até o resultado bater. O Atingir Meta (Goal Seek) faz exatamente isso, de forma automática.

A janela tem três campos:

Tabela 3.1. Os três campos do Atingir Meta.

EBITDA = volume × 2.000 × (1 − 20%) − 300 = 1.000.000
volume = (1.000.000 + 300) ÷ 1.600 = 625,1875

Agora o mesmo recurso numa pergunta clássica de negócio, sobre o arquivo do exercício de simulação. A aba Monte_Carlo_ex traz, na coluna C, a Projeção Base de um resultado operacional (em US$ mil): receita bruta de 1.350.000, deduções de 13%, CMV de 21% da receita líquida, despesas fixas e variáveis, depreciação, juros, IR e dividendos. O lucro antes de IR da projeção base é 83.915.

Os limites do Atingir Meta

O slide resume em uma frase: Atingir Meta encontra UMA solução, não A solução. Três limites decorrem disso.

• Só uma entrada. Se a meta pode ser atingida mexendo em preço, volume e custo, o Atingir Meta só olha uma delas. Para várias variáveis ao mesmo tempo, com restrições, existe o Solver, tema do Capítulo 4.
• Sem restrições. Não dá para pedir "volume inteiro" ou "preço acima de 1.800". No Exemplo 3.1, 625,19 unidades não existem; o Solver aceitaria a restrição de número inteiro.
• Pode não convergir. Se nenhum valor da entrada produz o alvo, ele avisa que não encontrou solução. Exemplo dado em aula: com a fórmula =C2^2, pedir o valor −25 não tem resposta, porque nenhum número real elevado ao quadrado dá negativo. Em modelos com SE e degraus, ele também pode parar numa solução e ignorar outra.

3.2Tabela de Dados: 1 e 2 variáveis

O Atingir Meta responde a uma pergunta pontual. A Tabela de Dados (Data Table) responde a uma família inteira de perguntas: "qual o lucro para cada receita de 1,1 a 1,6 milhão?". Ela pega cada valor de uma lista, coloca na célula de entrada do modelo, recalcula e anota a saída ao lado. É o que você faria copiando e colando cada valor à mão, só que de uma vez.

A estrutura precisa ser preparada antes de abrir o comando:

• 1 variável: os valores a testar ficam numa coluna. Na linha de cima, uma célula à direita, vai a fórmula que aponta para a saída do modelo (pode haver várias saídas lado a lado). Na janela, preencha só a célula de entrada da coluna.
• 2 variáveis: uma matriz. Os valores de uma entrada ficam na primeira coluna, os da outra na primeira linha, e a fórmula da saída vai no canto onde as duas se cruzam. Na janela, preencha as duas células de entrada: a da linha recebe os valores da primeira linha, a da coluna, os da primeira coluna.
• A fórmula de referência aponta para a célula de resultado do modelo. Escreva =C58, não uma conta nova: a Tabela de Dados funciona justamente porque reaproveita o modelo inteiro.

Recalcula tudo N vezes

Para preencher a matriz do Exemplo 3.4, o Excel recalcula o modelo 30 vezes, uma por combinação, e repete isso a cada alteração em qualquer célula da pasta. Num modelo pequeno, não se percebe. No modelo completo do Capítulo 2, com milhares de linhas de receita e 96 meses, uma tabela de 88 combinações deixa cada Enter lento.

Duas outras características da Tabela de Dados:

• O resultado é um bloco único. A fórmula {=TABELA(...)} ocupa a área toda. Não dá para apagar ou editar uma célula do meio; para mudar, selecione o bloco inteiro de resultados e apague.
• As entradas podem ser fórmulas. Se a primeira coluna vier de =SEQUÊNCIA(...) ligada a células de início e passo, você muda a faixa testada sem refazer a tabela. O professor mostrou isso em aula: a tabela "está viva" e se recalcula com os novos valores.

3.3Mapa de calor de sensibilidade

Uma matriz de sensibilidade crua é ilegível de relance. Para achar onde o lucro vira prejuízo no Exemplo 3.4, é preciso ler número por número. A formatação condicional com escala de cor resolve: cada célula ganha uma cor proporcional ao valor, e as zonas de risco e de oportunidade saltam aos olhos.

3.4Cenários × Tabela de Dados: qual usar

No Capítulo 2 você construiu o seletor de cenários moderno: uma validação de dados com Otimista, Base e Pessimista e um PARÂMETRO (SWITCH) que traduz a escolha em premissas. A Tabela de Dados parece fazer algo parecido, mas responde a outra pergunta.

Tabela 3.2. Cenários nomeados e Tabela de Dados se complementam.

Os dois convivem: uma Tabela de Dados pode rodar com o seletor em "Pessimista", e você compara o mapa de calor dos dois cenários. E nenhum dos dois responde à pergunta "qual a chance de dar prejuízo?". Para isso é preciso deixar todas as premissas incertas variarem ao mesmo tempo, que é o tema da seção 3.6.

Sensibilidade no modelo completo

O exercício da sessão aplica a Tabela de Dados ao modelo dos três demonstrativos do Capítulo 2, na aba Planilha_Calculos do arquivo EBP_Mod_ModeloCompleto@2026.xlsx. A pergunta de negócio é a do próprio modelo: com quanto capital social e quanta dívida de longo prazo (BNDES) a empresa consegue operar sem depender demais da dívida rotativa, o revolver que cobre o caixa quando ele fica abaixo do mínimo?

A saída é o pico da dívida rotativa em todo o horizonte, já calculado em D78 com =MÁXIMO(E78:CV78). As entradas são o capital social (D81) e a dívida de longo prazo (D79). A aba já traz o esqueleto da análise nas linhas 117 a 133: dívidas de 500 mil a 4 milhões em E120:L120, capitais de 5 a 15 milhões em D121:D131 e uma meta (Target) de 4 milhões em E133.

3.5Monte Carlo: quando simular

Até aqui, toda análise foi determinística: dada a premissa, sai um resultado. A Tabela de Dados testa várias premissas, mas uma de cada vez e sem dizer qual é mais provável. O Monte Carlo trata a premissa como ela é: uma variável incerta, com um valor mais provável e uma dispersão em torno dele. É a ponte com a distinção do Capítulo 1 entre modelo determinístico e estocástico.

A ideia é sortear um valor plausível para cada premissa incerta, calcular o resultado, anotar, e repetir mil, cinco mil, dez mil vezes. No fim, em vez de um lucro, você tem mil lucros, e pode perguntar: em quantos deu prejuízo? Qual o pior resultado em 20? O nome vem do cassino de Monte Carlo, porque o método funciona como uma roleta girada muitas vezes.

Simular faz sentido quando três condições se juntam:

• Há incerteza relevante nas entradas. Demanda, custo de insumo, câmbio. Uma comissão contratual de 3% da receita praticamente não varia; a folha de pagamento varia com horas extras, faltas, férias e demissões.
• O resultado importa em faixa, não como número único. A diretoria quer saber a chance de prejuízo ou o caixa no pior caso razoável, não só o valor esperado.
• O modelo já está construído e testado. Simular sobre um modelo quebrado só multiplica o erro, mil vezes.

Distribuições na prática

Para sortear, é preciso dizer como a premissa varia. Três formatos cobrem a maioria dos casos de negócio.

Tabela 3.3. Distribuições e fórmulas. ALEATÓRIO devolve um número entre 0 e 1, diferente a cada recálculo; ALEATÓRIOENTRE, um inteiro entre dois limites, útil para sorteios.

A normal (curva de Gauss, ou "de sino") é a mais usada e é a do exercício. Ela fica definida por dois números: a média, que diz onde fica o pico, e o desvio padrão, que diz quão aberta é a curva. Um desvio pequeno dá uma curva estreita, com quase tudo perto da média; um desvio grande, uma curva aberta, com resultados distantes mais frequentes.

O truque para sortear da normal está na função INV.NORM.N. Ela recebe uma probabilidade p entre 0 e 1 e devolve o valor da curva que deixa p da área à sua esquerda. Com p = 50%, devolve a própria média; com p pequeno, um valor baixo; com p perto de 1, um valor alto. Se p for sorteado por ALEATÓRIO, uniforme entre 0 e 1, o resultado é um sorteio da curva normal.

=INV.NORM.N(0,5;$C$6;$D$6)    → 1.350.000   (a média)
=INV.NORM.N(0,05;$C$6;$D$6)   → 1.016.917   (P5: só 5% dos casos abaixo)
=INV.NORM.N(0,95;$C$6;$D$6)   → 1.683.083   (P95: só 5% dos casos acima)
=INV.NORM.N(0;$C$6;$D$6)      → #NÚM!

=PROCX(ALEATÓRIO(); {0;0,3;0,8}; {"Baixa";"Média";"Alta"}; ; -1)

3.6O motor moderno: MATRIZALEATÓRIA

Com INV.NORM.N e ALEATÓRIO você consegue gerar uma projeção: troque cada premissa cinza da coluna C por uma fórmula aleatória e o modelo dá um lucro sorteado. Para mil projeções, é preciso repetir o modelo mil vezes. Aqui aparece a diferença entre o jeito antigo e o moderno.

O jeito antigo era colar a fórmula célula a célula. Na aba Monte_Carlo_ex, cada coluna a partir de F é uma projeção, e cada uma das 40 linhas do modelo (13 premissas e 27 contas) precisa de uma fórmula em cada coluna. Para 1.000 projeções são 40 mil fórmulas. O arquivo fica pesado, o Excel recalcula tudo a toda hora, e mudar para 5.000 projeções exige descobrir qual é a coluna 5.000 e copiar tudo de novo. O slide fala em 86 mil fórmulas na solução antiga completa; o professor sugeriu, como teste, fazer 16 mil projeções desse jeito e ver o tamanho do arquivo.

O jeito moderno usa MATRIZALEATÓRIA (RANDARRAY), uma função de matriz dinâmica que devolve uma matriz inteira de números aleatórios entre 0 e 1.

Dentro de INV.NORM.N, cada um dos mil números uniformes vira um sorteio da normal:

=INV.NORM.N(MATRIZALEATÓRIA(1000;1); 50000; 8000)

Tabela 3.4. O motor de simulação, antes e depois das matrizes dinâmicas.

Volatilidade e controle do recálculo

No Capítulo 3 do Tools você viu a lista das funções voláteis, que se recalculam a cada alteração em qualquer célula da pasta: HOJE, AGORA, DESLOC, INDIRETO, ALEATÓRIO, ALEATÓRIOENTRE e MATRIZALEATÓRIA. O curso recomenda evitá-las. O Monte Carlo é a única exceção tolerada: aqui a volatilidade é o objetivo, porque cada recálculo é uma nova rodada da roleta.

3.7Ligando o motor ao negócio e lendo o risco

Gerar números aleatórios é metade do trabalho. A outra metade é fazer esses números passarem pelo modelo de negócio e resumir o resultado.

• O spill de cenários alimenta o modelo. Cada linha de cálculo passa a operar sobre os intervalos despejados: em vez de =F6*F9, =F6#*F9#. Assim o modelo inteiro roda mil vezes, e cada linha despeja mil resultados.
• Percentis: =PERCENTIL(spill;0,05) e =PERCENTIL(spill;0,95) (PERCENTILE; a forma nova PERCENTIL.INC dá o mesmo resultado) delimitam a faixa onde caem 90% dos cenários.
• Probabilidade de prejuízo: =CONT.SES(spill;"<0")/CONT.VALORES(spill): quantos cenários deram negativo, divididos pelo total.
• Histograma: =FREQUÊNCIA(spill;limites) (FREQUENCY) conta quantos valores caem em cada faixa, numa fórmula só, e um gráfico de colunas mostra a distribuição inteira.

3.8Exercícios resolvidos

Exercício 1: mapa de calor de sensibilidade (aba Planilha_Calculos)

Arquivo: EBP_Mod_ModeloCompleto@2026.xlsx, com o modelo completo do Capítulo 2.

Tarefa: montar uma Tabela de Dados de 2 variáveis sobre uma saída do modelo completo e transformá-la em mapa de calor. Aplicar a escala de cor, fixar o mínimo e o máximo e identificar a zona que exige revolver. Desafio: trocar a saída para a margem EBITDA anual e comparar a que driver a saída é mais sensível.

Passo 1: a saída no canto. D120 já contém =D78, o pico da dívida rotativa. A fonte da célula é branca: o número existe, mas não aparece, para não ser confundido com um resultado da tabela. É uma convenção comum para a célula do canto.

Passo 2: a Tabela de Dados. Selecione D120:L131. Em Dados › Teste de Hipóteses › Tabela de Dados:

Célula de entrada da linha:   $D$79   (dívida de longo prazo, valores em E120:L120)
Célula de entrada da coluna:  $D$81   (capital social, valores em D121:D131)

O Excel preenche E121:L131 com {=TABELA(D79;D81)}. A aba já tem uma regra de formatação condicional nesse intervalo que pinta de verde os valores abaixo da meta de E133.

Passo 3: o mapa de calor. O destaque binário (abaixo ou acima da meta) diz pouco sobre o quanto falta. Substitua a regra por uma escala de 3 cores com os limites fixados pelo negócio e as cores invertidas, porque aqui menos dívida é melhor:

Mínimo:       Número  0          verde
Ponto médio:  Número  4.000.000  amarelo   (a meta de E133)
Máximo:       Número  6.000.000  vermelho

Tabela 3.5. Pico da dívida rotativa por capital social e dívida de longo prazo. As linhas de 10 a 15 milhões de capital, todas zeradas, foram agrupadas.

Leitura:

• Zona que exige revolver: 30 das 88 combinações, todas com capital de até 9 milhões. Acima da meta de 4 milhões ficam só quatro, as de capital de 5 milhões com BNDES de até 1,5 milhão e a de 6 milhões com 500 mil.
• A fronteira é uma diagonal. Cada 500 mil de BNDES reduz o pico em cerca de 499 mil; cada milhão de capital, em cerca de 1,09 milhão. Os dois financiamentos quase se substituem, com uma pequena vantagem do capital, que não paga juros. Com 7 milhões de capital, são precisos 4 milhões de BNDES para zerar o revolver; com 8 milhões, 3 milhões bastam.
• A escala fixa importa. Com mínimo e máximo automáticos, o maior valor da tabela (5,3 milhões) ficaria vermelho mesmo que a meta fosse 10 milhões. Com a meta no ponto médio, o amarelo quer dizer "no limite do aceitável".

Desafio extra. Troque a saída do canto para a margem EBITDA anual. A tabela inteira fica constante: capital social e dívida de longo prazo são decisões de financiamento e só afetam o resultado abaixo do EBITDA (despesa e receita financeiras). É uma lição de sensibilidade: antes de montar a tabela, pergunte se a entrada escolhida chega até a saída. Para estudar a margem EBITDA, use drivers operacionais, como o crescimento da receita ou o percentual de CMV. O slide sugere, por exemplo, crescimento da receita × dias de recebíveis sobre o caixa; nessa combinação, a margem EBITDA reage ao crescimento da receita e é indiferente aos dias de recebíveis, que afetam o caixa mas não o resultado.

Exercício 2: simular e ler o risco (aba Monte_Carlo_ex)

Arquivo: EBP_Mod_MonteCarlo_Solver_Markov@2026.xlsx, aba Monte_Carlo_ex.

Tarefa: construir a fórmula MATRIZALEATÓRIA + INV.NORM.N com 1.000 cenários, ligar o spill ao cálculo de resultado operacional do case, calcular P5, P95 e a probabilidade de prejuízo e montar o histograma com FREQUÊNCIA despejada e um gráfico de colunas. Desafio: comparar 100 e 10.000 cenários.

A coluna C traz a projeção base; a coluna D, o desvio padrão histórico de cada premissa cinza; a linha 5, a partir de F, recebe as projeções. Vamos simular todas as premissas que têm desvio, como foi feito em aula nas duas turmas, e analisar o lucro líquido antes de dividendos (linha 58).

Passo 1: o número de projeções e o cabeçalho. Em C4, digite 1000 (com o rótulo "Nº de projeções" em B4). Em F5:

F5: ="Projeção "&TEXTO(SEQUÊNCIA(1;$C$4);"#.##0")

A SEQUÊNCIA despeja 1, 2, 3... até 1.000 para a direita; o TEXTO põe o separador de milhar, para a última aparecer como "Projeção 1.000".

Passo 2: as premissas aleatórias. Em cada linha com premissa cinza (13 linhas: 6, 9, 14, 22, 26, 28, 32, 35, 37, 43, 49, 56 e 62), na coluna F:

F6:  =INV.NORM.N(MATRIZALEATÓRIA(1;$C$4);$C6;$D6)
F9:  =INV.NORM.N(MATRIZALEATÓRIA(1;$C$4);$C9;$D9)
...  (a mesma fórmula, copiada para as outras 11 linhas)

O (1;$C$4) gera uma linha com 1.000 colunas, porque na aba as projeções correm para a direita. O $ em C e D trava as colunas da média e do desvio; a linha fica livre, então a fórmula copiada de F6 para F9 passa a ler C9 e D9.

Passo 3: as linhas de cálculo com #. As outras 27 linhas repetem a conta da coluna C, mas com referência de despejo:

F8:  =F6#*F9#                         Deduções
F11: =F6#-F8#                         Receita líquida
F13: =F14#*F11#                       CMV
F16: =F11#-F13#                       Lucro bruto
F19: =F22#+F25#+F28#+F31#+F34#+F37#   SG&A
F40: =F16#-F19#                       EBITDA
F52: =F46#-F49#                       Lucro antes de IR
F58: =F52#-F55#                       Lucro líquido antes de dividendos
F61: =SE(F58#>0;F58#*F62#;0)          Dividendos

e assim por diante, inclusive as linhas de percentual (F17: =F16#/F11#). O professor alertou para o ponto que mais gera erro: toda linha precisa de # ou de MATRIZALEATÓRIA. Uma linha esquecida com =F19/F11 calcula só a primeira projeção e deixa as outras 999 em branco, ou com erro mais abaixo. Confira percorrendo a coluna G: toda célula preenchida deve mostrar a fórmula fantasma, em cinza.

Repare em dois detalhes do case. Depreciação, IR e % de distribuição têm desvios minúsculos (3 sobre 30.000; 0,0034 ponto percentual sobre 34%), e por isso quase não variam entre as projeções. E o IR é LAIR × alíquota sem SE: quando o lucro antes de IR é negativo, o IR também fica negativo (Projeção 1.000: −7.390), como um crédito fiscal. É uma simplificação do case; o modelo completo do Capítulo 2 zera o IR no prejuízo.

Passo 4: ler o risco. Abaixo de "Análise", na linha 69:

C72: =MÉDIA(F58#)
C73: =MÍNIMO(F58#)
C74: =MÁXIMO(F58#)
C75: =PERCENTIL(F58#;0,05)
C76: =PERCENTIL(F58#;0,95)
C77: =CONT.SES(F58#;"<0")/CONT.VALORES(F58#)

Tabela 3.6. Lucro líquido antes de dividendos simulado, em US$ mil. A primeira coluna é a rodada mostrada nas telas; a segunda, uma simulação muito maior, feita fora do Excel, que indica para onde as estatísticas convergem. A projeção base, sem incerteza, dava 55.384.

Leitura: o lucro esperado é praticamente o da projeção base, mas a faixa é larga. Em 5% dos cenários, o prejuízo passa de 90 mil; em 5%, o lucro passa de 200 mil. E a chance de fechar o ano no prejuízo é de cerca de 27%, mais de uma em quatro, um número que a projeção base, com seu lucro de 55 mil, escondia. Em aula, as duas turmas chegaram à mesma faixa, entre 25% e 27%.

Passo 5: o histograma. Defina início, passo e número de faixas e deixe as fórmulas fazerem o resto:

C79: -250000      C80: 50000      C81: 13
B84: =SEQUÊNCIA(C81;1;C79;C80)    → limites de -250.000 a 350.000
C84: =FREQUÊNCIA(F58#;B84#)       → 14 contagens (a última: acima de 350.000)
D84: =C84#/SOMA(C84#)             → % de cada faixa
E84: =SOMA($D$84:D84)             → % acumulado (copiado até E97)

Selecione as faixas e as frequências e insira um gráfico de colunas. Para rótulos de faixa legíveis, uma coluna auxiliar com =TEXTO(limite_anterior;"#.##0")&" a "&TEXTO(limite;"#.##0") resolve.

Desafio extra: 100 × 10.000 cenários. Troque C4 para 100 e depois para 10.000 e aperte F9 várias vezes, de olho na probabilidade de prejuízo.

• Com 100, a estatística "dança" muito: numa rodada deu 24%, noutra pode dar 33%. Em 200 rodadas de teste, 95% delas ficaram entre 19% e 36%.
• Com 1.000, a faixa estreita para 23,8% a 29,7%.
• Com 10.000, para 26,0% a 27,7%. O gráfico quase para de mexer.

O tempo de cálculo cresce na mesma proporção, mas com a fórmula despejada continua rápido. O limite prático da aba é de colunas: o Excel tem 16.384, e como as projeções começam em F, cabem no máximo 16.379. Para mais, transponha o modelo e faça uma projeção por linha, com MATRIZALEATÓRIA($C$4;1), como no slide.

De volta ao negócio. Os 27% de chance de prejuízo incomodam? O modelo diz onde agir: reduzir a incerteza de cada linha é tão útil quanto melhorar a média. Na simulação de referência, a receita sozinha (com todas as outras premissas fixas) já daria 23,8% de chance de prejuízo; todas as outras juntas, sem a incerteza da receita, dariam 10,1%. Melhorar a previsão de receita, portanto, pesa mais do que qualquer outra ação isolada. O professor testou em aula um banco de horas que reduz o desvio da folha fixa de 56.000 para 5.600: a chance de prejuízo cai para cerca de 24,9%. Ajuda, mas não resolve sozinho; combinado com renegociar aluguéis, controlar o marketing e ganhar margem no CMV, o risco cai mais.

3.9Exercícios propostos

Use a aba Monte_Carlo_ex (projeção base na coluna C) nos itens 1, 2, 5, 6 e 8, o modelo de quatro premissas do Exemplo 3.1 no item 3 e a Tabela 3.5 no item 4.

1. Com Atingir Meta, descubra a receita bruta que leva o lucro líquido antes de dividendos (C58) a 100.000.
2. Mantendo a receita de 1.350.000, qual percentual de CMV (C14) zera o lucro antes de IR?
3. No modelo do Exemplo 3.1, com volume fixo em 500, que preço leva o EBITDA a 1.000.000?
4. Na Tabela 3.5: (a) quantas combinações exigem algum revolver? (b) com 7 milhões de capital, qual a menor dívida de longo prazo testada que zera o revolver? (c) e com 8 milhões?
5. Monte uma Tabela de Dados de 1 variável com a receita bruta de 1.100.000 a 1.600.000 e o lucro líquido após dividendos (C64) como saída. Por que, nas duas primeiras linhas, ele é igual ao lucro antes de dividendos?
6. Qual o valor de receita bruta que só 10% dos cenários ficam abaixo, segundo a média e o desvio da aba? Escreva a fórmula.
7. Na tabela de demanda do Exemplo 3.8, que categoria sai para os sorteios 0,30, 0,79 e 0,80?
8. Com C4 = 10.000, até que coluna vai o despejo de F6? Qual o número máximo de projeções que cabe na aba, com as projeções começando em F?

Material de apoio às aulas, de uso exclusivo dos alunos da BTC. É proibido reproduzir ou compartilhar este conteúdo sem autorização expressa da BTC. Ler o disclaimer

Os três capítulos anteriores construíram o modelo e o testaram contra a incerteza. Atingir Meta respondeu "que entrada dá esse resultado?", a Tabela de Dados mostrou como a saída reage a uma ou duas premissas e o Monte Carlo transformou incerteza em probabilidade. Este capítulo fecha o módulo com duas perguntas novas e um case. A primeira pergunta é de otimização: qual é o melhor resultado possível, respeitando as regras do negócio? Quem responde é o Solver. A segunda é de longo prazo: para onde um sistema vai se as regras de transição não mudarem? Quem responde é a cadeia de Markov, que no Excel moderno cabe numa fórmula despejada.

No fim, o case P&J junta tudo o que o módulo ensinou numa análise comercial de verdade: uma base de 50 mil vendas, uma pergunta de negócio, uma ferramenta de reunião e um gráfico para o gestor. O capítulo termina com o mapa que organiza o módulo inteiro: qual ferramenta usar para qual pergunta.

4.1Formulando um problema de otimização

Todo problema de otimização tem as mesmas três peças. Antes de abrir qualquer janela do Excel, escreva as três em português, numa folha de papel ou num comentário da planilha. Se uma delas não fica clara, o Solver não vai resolver o problema por você: vai resolver outro problema.

Tabela 4.1. As três peças de um problema de otimização.

O Solver é o primo mais capaz do Atingir Meta, visto no Capítulo 3. O Atingir Meta muda uma célula para levar outra a um valor exato, sem restrições. O Solver muda várias células, aceita restrições e, além de "igualar a um valor", sabe maximizar ou minimizar. Quando o problema cabe no Atingir Meta, use o Atingir Meta, que é mais simples; quando há mais de uma alavanca ou alguma regra que não pode ser quebrada, é hora do Solver.

Retorno, risco e diversificação

O modelo usa duas medidas clássicas de finanças. O retorno de um ativo é a média dos retornos mensais. O risco é o desvio-padrão desses retornos (=DESVPAD(C10:C42), STDEV): quanto mais o ativo sobe e desce, maior o risco, porque maior a chance de você precisar do dinheiro justamente num mês ruim. Dois investimentos que começam e terminam com o mesmo valor podem ter riscos muito diferentes no caminho.

O retorno de uma carteira é a média ponderada dos retornos dos ativos. O risco não é: ativos que não andam juntos se compensam, e o risco da carteira fica abaixo da média dos riscos. Essa é a ideia da teoria de carteiras de Markowitz, base do "não colocar todos os ovos na mesma cesta". O exemplo clássico usado em aula é uma mineradora exportadora, que ganha quando o dólar sobe, e uma companhia aérea, que tem o combustível em dólar e perde quando ele sobe: juntas na carteira, o efeito do câmbio se anula em boa parte. O risco de mercado, que atinge todo mundo ao mesmo tempo, não se elimina com diversificação.

Retorno: 50% × 9,33% + 50% × 13,76% = 11,55%   (exatamente a média)
Risco:   11,70%   (a média dos riscos seria (7,33% + 21,51%) ÷ 2 = 14,42%)

4.2Solver: configurar, rodar e ler o resultado

Ativar o suplemento

O Solver vem com o Excel, mas desligado. Ative uma vez e ele fica disponível em todas as pastas:

1. Arquivo › Opções › Suplementos.
2. Em Gerenciar, escolha Suplementos do Excel e clique em Ir.
3. Marque Solver e dê OK. (Na mesma lista está o Ferramentas de Análise, que traz o histograma estático citado no Capítulo 3.)

O botão aparece em Dados › Análise › Solver, no canto direito da guia. No Mac, o caminho é Ferramentas › Suplementos do Excel.

A janela de parâmetros

• Definir Objetivo: a célula da função objetivo, e o sentido: Máx., Mín. ou Valor de (esta última faz o papel do Atingir Meta, mas com restrições).
• Alterando Células Variáveis: o intervalo das variáveis. Dá para informar vários intervalos separados por ponto e vírgula.
• Sujeito às Restrições: Adicionar abre uma janela com três campos: referência da célula, operador (<=, =, >=, int, bin, dif) e restrição (um número ou outra célula). Um intervalo inteiro pode receber a mesma regra de uma vez, como $N$10:$W$10 >= 0.
• Tornar Variáveis Irrestritas Não Negativas: a caixa equivale a colocar "≥ 0" em todas as variáveis. Mesmo com ela marcada, vale escrever a restrição explicitamente, para que a regra fique visível a quem abrir o modelo.
• Selecionar um Método de Solução: veja a Tabela 4.2.
• Carregar/Salvar: guarda a configuração em células da planilha, útil para alternar entre cenários de otimização. O Solver também salva a última configuração de cada aba junto com o arquivo.

Tabela 4.2. Métodos do Solver. O padrão do curso é o GRG Não Linear; o Simplex dá a resposta exata e mais rápida, mas só quando o problema é linear.

Ler o resultado com senso crítico

Ao terminar, o Solver mostra a janela Resultados do Solver com uma mensagem ("O Solver encontrou uma solução. Todas as restrições e condições de otimização foram atendidas", ou o motivo de não ter encontrado). Ali você escolhe Manter Solução do Solver ou Restaurar Valores Originais e pode marcar relatórios, que o Excel cria em abas novas:

• Relatório de Resposta: os valores finais do objetivo e das variáveis e, para cada restrição, se ela ficou Associada (no limite, apertando a solução) ou Não Associada (com folga).
• Relatório de Sensibilidade: quanto o objetivo melhoraria se cada restrição fosse afrouxada em uma unidade. No Simplex esse número se chama preço-sombra; no GRG aparece como multiplicador de Lagrange. Restrição com folga tem preço zero: afrouxá-la não muda nada.

O ponto de partida importa

O GRG começa nos valores que estão nas variáveis e vai ajustando aos poucos, na direção que melhora o objetivo. Quando chega a um ponto em que qualquer pequena mudança piora o resultado, para. Esse ponto é um ótimo local: o melhor da vizinhança, não necessariamente o melhor de todos (o ótimo global). Pense em subir uma montanha no nevoeiro: você para quando todos os passos em volta descem, mas pode estar num morro ao lado do pico.

4.3Cadeias de Markov

Uma cadeia de Markov descreve um sistema que, a cada período, está em um de alguns estados possíveis e pode passar para outro com probabilidades conhecidas. Exemplos: o cargo de um funcionário (estagiário, analista, coordenador, gerente, desligado); a faixa de atraso de uma conta a receber (em dia, até 30 dias, 30 a 60, acima de 90, perdida, paga); o perfil de um cliente (inativo, ocasional, frequente, assinante); a situação de saúde de um paciente.

O mesmo conteúdo pode ser desenhado como um diagrama de transição: um círculo por estado e uma seta para cada probabilidade diferente de zero. As setas que voltam para o próprio estado mostram quem fica.

O case do medicamento

O exercício da aula usa quatro estados de saúde de uma população: Saudável, Doente, Em tratamento e Óbito. Uma farmacêutica quer saber quanto uma melhora no medicamento estende a vida da população e, com isso, se vale a pena investir em mais uma rodada de desenvolvimento.

Tabela 4.3. Matriz de transição do case do medicamento. Hoje, 20% de quem está em tratamento fica saudável; cada ponto de melhora sai do óbito. A melhora vai de 0% a 50% (no limite, 30% ficam saudáveis e ninguém em tratamento morre).

4.4MATRIZ.MULT despejado e o estado estacionário

Para projetar a população um período à frente, cada estado do próximo período recebe a soma do que vem de todos os estados atuais. Saudável no período 2, por exemplo, é: saudáveis que ficam + doentes que se curam + tratados que se curam + óbitos que "voltam" (zero). Escrever isso à mão para cada célula é trabalhoso e fácil de errar nas travas com $; em aula, a primeira tentativa manual saiu com as referências trocadas. A conta é exatamente uma multiplicação de matrizes.

Período 1: 0%   80%   0%   20%
Período 2: 72%  18%   10%  0%

Estado estacionário

Repetindo a fórmula período após período, a distribuição deixa de mudar. Esse ponto é o estado estacionário (ou equilíbrio): para onde o sistema converge se as regras de transição não mudarem. Markov não pergunta o que acontece amanhã; pergunta onde o sistema termina.

No case do medicamento, o estado estacionário é trivial: 100% em Óbito. O que interessa é a velocidade. Com estado absorvente, a pergunta vira "em que período a população toda chegou lá?", e a resposta exige um critério, a precisão: considera-se que todos morreram quando falta menos de uma pessoa (precisão 1) para os 1.000, ou seja, quando os óbitos passam de 999. A resolução completa está no Exercício 2 da Seção 4.8.

4.5Qual ferramenta para qual pergunta

O módulo apresentou cinco ferramentas de análise. Elas não competem: cada uma responde a um tipo de pergunta. Saber qual pergunta você tem é a maior parte do trabalho de modelagem.

Uma forma de organizar a escolha é voltar à distinção do Capítulo 1 entre modelo determinístico e estocástico. Atingir Meta, Tabela de Dados e Solver trabalham com premissas fixas e perguntam "e se?" ou "qual o melhor?". Monte Carlo assume que as premissas são incertas e devolve uma distribuição. Markov trabalha com probabilidades de transição e olha o longo prazo.

4.6Case integrador: potencial comercial da P&J

O case final aplica o módulo inteiro sobre um problema de negócio. A P&J, fabricante de bens de consumo, enfrenta margens em queda e um mercado mais competitivo. Você é associate da consultoria Brain&Company e recebeu do diretor financeiro a base com todas as vendas do ano, por produto e cliente.

O diagnóstico

A área de pricing define um preço base por produto: o mínimo pelo qual o vendedor pode vender. Ele cobre o custo do produto, um rateio das despesas de estrutura e uma margem que remunera o capital investido. O sistema bloqueia vendas abaixo do preço base, e esse é o único controle. Os vendedores são comissionados sobre o que vendem, então fechar a venda perto do preço base é melhor do que perder a venda. O incentivo do vendedor (não perder nenhuma venda) não é o da empresa (vender pelo maior preço que o cliente aceita). Cada cliente é atendido por um vendedor específico, e o poder de negociação depende muito do produto: uma marca líder, que o varejista não pode deixar de ter na gôndola, sustenta preço; um produto sem diferencial, não.

O case pede duas entregas: o potencial total (a soma de todos os potenciais adicionais, calculados com valores anuais por produto e cliente, não transação a transação) e uma ferramenta com dois gráficos que compare as vendas mensais de um cliente escolhido com as vendas totais da empresa (receita e volume), para usar em reunião com o presidente e o diretor comercial. A planilha acrescenta uma terceira etapa, a consolidação por cliente para apresentação.

Amazon:  Receita Base = 10.969.800,00   Receita Cliente = 14.564.730,00
Margem adicional = 14.564.730 ÷ 10.969.800 − 1 = 32,8%
Margem padrão do produto (média simples dos 19 clientes) = 48,6%
Potencial = 10.969.800 × (1 + 48,6%) − 14.564.730 = 1.733.470

Os três checkpoints

O case é conduzido de forma semiautônoma, com três paradas para conferir o avanço:

1. Consolidação da análise (aba Exemplo_Analise1): a tabela de produto × cliente com margem adicional, margem padrão e potencial, para todos os 150 produtos, e o potencial total.
2. Simulador (aba Exemplo_Analise2): um menu suspenso com o cliente e quatro linhas mensais (quantidade do cliente, quantidade total, receita do cliente, receita total) que alimentam dois gráficos já prontos na aba.
3. Gráfico final (aba UltimoGrafico): receita anual, potencial adicional e margem por cliente, num gráfico de apresentação com duas ou três frases de recomendação.

Os slides descrevem o segundo checkpoint como um simulador de desconto e volume e o primeiro com classificação por faixa de potencial. A planilha e o texto do case pedem o comparativo cliente × empresa, e é isso que a Seção 4.8 resolve; o simulador de desconto fica como desafio nos exercícios propostos.

4.7Fechamento do módulo

O modelo completo do Capítulo 2 (capital de giro, dívida com revolver, impostos e dividendos) segue o mesmo padrão usado em FP&A e valuation no mercado. Os Capítulos 3 e 4 deram a ele as ferramentas de teste: sensibilidade, simulação, otimização e longo prazo. Um próximo passo natural é combinar Monte Carlo com o modelo completo, para simular caixa e valuation sob incerteza.

O bloco final do curso trata de automação: Power Query para trazer e tratar dados, VBA para transformar rotinas em um botão e IA para análise de dados. A distinção entre determinístico e estocástico volta ali para decidir a ferramenta: uma rotina que precisa dar sempre o mesmo resultado vai para fórmula, Power Query ou VBA; uma tarefa pontual e exploratória pode ir para a IA.

4.8Exercícios resolvidos

Os exercícios 1 e 2 usam o arquivo EBP_Mod_MonteCarlo_Solver_Markov@2026.xlsx (abas Solver_ex e Markov_ex; a aba Monte_Carlo_ex é do Capítulo 3). O exercício 3 usa o arquivo do case P&J (EBP_Mod_PJ@2025). Os slides citam os arquivos com outros nomes (EBP_Mod_Simulacao_2026_Aluno e EBP_Mod_PJ_2026_Aluno); o conteúdo é o mesmo.

Exercício 1: carteira ótima com o Solver (aba Solver_ex)

Tarefa: rodar o Solver com as restrições do problema, validar a plausibilidade do resultado, garantir a não negatividade e testar pontos de partida diferentes. Fazer três otimizações: máximo retorno, mínimo risco e máxima relação retorno ÷ risco.

Preparação. Ajuste Y10 para contar "2% ou mais" (=CONT.SES($N$10:$W$10;">="&$E$4), copiada até Y42) e crie a razão em AA46:

Z46:  Retorno / Risco
AA46: =SEERRO(AA44/AA45;0)

Configuração do Solver (Figura 4.2), igual nas três rodadas, mudando só o objetivo:

Variáveis:   $N$10:$W$10
Restrições:  $N$10:$W$10 <= 1        (no máximo 100% num ativo)
             $N$10:$W$10 >= 0        (não negatividade; marque também a caixa)
             $X$10 = 1               (investir exatamente 100%)
             $Y$10 >= $E$5           (pelo menos 6 ativos com 2% ou mais)
Método:      GRG Não Linear
Objetivo:    $AA$44 Máx. | $AA$45 Mín. | $AA$46 Máx.

Comece cada rodada com 10% em cada ativo, rode, anote o resultado e rode de novo a partir de outro ponto (por exemplo, a solução anterior, ou 100% num ativo). Guarde a melhor carteira de cada objetivo numa pequena tabela de registro abaixo do modelo, colando os valores (Ctrl+Alt+V › Valores), como o professor fez em aula.

Tabela 4.4. As três carteiras ótimas. Composições completas no No Excel 4.3; pesos arredondados em duas casas, e o Solver pode diferir na segunda casa conforme o ponto de partida.

Leitura e plausibilidade.

• Todas as carteiras somam 100%, nenhum peso é negativo e cada uma tem pelo menos 6 ativos com 2% ou mais. As restrições foram respeitadas.
• Máximo retorno é quase uma aposta num ativo só: põe o mínimo exigido em cinco ativos e o resto no mais rentável. O risco de 19,31% é quase o do Ativo 10 sozinho. É uma resposta correta para a pergunta, mas mostra que "maximizar retorno" sem olhar risco é uma pergunta ruim. Em aula, o GRG parou perto de 10% de retorno nesta rodada (Exemplo 4.4): rode mais de uma vez.
• Mínimo risco concentra no Ativo 7 (o mais estável) e combina ativos que se compensam. O risco de 1,31% é menor que o do ativo menos arriscado sozinho (2,03%): é a diversificação trabalhando.
• Máximo retorno ÷ risco é o equilíbrio entre as duas: retorno 33% maior que o da carteira de mínimo risco (4,36% contra 3,27%) com só 0,19 ponto de risco a mais. Em aula, a primeira rodada chegou a 2,59 e, depois do SEERRO e de um novo ponto de partida, a 2,91, o mesmo valor da Tabela 4.4.