Capítulo 07: Agregações Condicionais (SOMASE, SOMASES, CONT.SE, CONT.SES)

🎯 Objetivo da Aula

Em grandes bancos de dados de logística e administração, raramente precisamos do total geral isolado de todas as coisas misturadas. O valor real para os tomadores de decisão está na agregação segmentada: “Quanto faturamos apenas na Região Sul para fretes já Entregues?” ou “Quantos motoristas terceirizados estão em trânsito com cargas de alto valor?”.

Nesta aula, você aprenderá a aplicar os agregadores matemáticos do Excel:

1. CONT.SE e CONT.SES para contagem de registros por critério único ou múltiplos critérios.
2. SOMASE e SOMASES para soma financeira e de volume com filtros combinados.
3. A diferença crítica na ordem dos argumentos entre as funções de critério único e critérios múltiplos.
4. O paralelo computacional direto com consultas agregadas em banco de dados SQL (GROUP BY, WHERE, SUM, COUNT).

🏢 O Cenário Prático (Seu Desafio)

Situação: A diretoria da FastLog convocou uma reunião de emergência para analisar a rentabilidade das rotas de transporte. Eles receberam um relatório contendo mais de 500 fretes realizados no mês, com informações misturadas de região (Sul, Sudeste, Nordeste) e status da entrega (Entregue, Em Trânsito, Cancelado).

Missão: Você deve construir uma matriz de consolidação executiva que calcule automaticamente:

1. O volume de entregas e o faturamento total por região isolada (CONT.SE / SOMASE).
2. O faturamento de alta precisão cruzando múltiplos critérios: fretes da região Sudeste que já estejam formalmente com status Entregue (SOMASES e CONT.SES).

🧠 Fundamentos: Teoria da Agregação de Dados

1. O Conceito de Agregação e Filtro em Memória

Na computação, Agregação é o processo de transformar um conjunto de várias linhas em um único valor resumido (Soma, Contagem, Média, Máximo ou Mínimo).

As funções da família SE e SES aplicam um filtro lógico em memória antes de processar os números:

graph TD
    Data[Base de Dados: 500 Fretes] --> Filter1{Critério 1: Região == 'Sudeste'?}
    Filter1 -- Sim --> Filter2{Critério 2: Status == 'Entregue'?}
    Filter1 -- Não --> Ignore[Descarta Registro]
    Filter2 -- Sim --> Acc["Acumulador Matemático<br/>(Soma Valor ou Incrementa Contador)"]
    Filter2 -- Não --> Ignore
    Acc --> Output["Resultado Consolidado na Tabela"]
    
    style Filter1 fill:#8e44ad,stroke:#fff,stroke-width:2px,color:#fff
    style Filter2 fill:#2980b9,stroke:#fff,stroke-width:2px,color:#fff
    style Acc fill:#217346,stroke:#fff,stroke-width:2px,color:#fff

2. Sintaxe das Funções Simples vs Múltiplas

A. Contagem com Critérios

• Critério Único:
  =CONT.SE(intervalo_criterio; criterio)
  Exemplo: =CONT.SE(B2:B50; "Sudeste")
• Múltiplos Critérios (Porta AND em Lote):
  =CONT.SES(intervalo1; crit1; intervalo2; crit2; ...)
  Exemplo: =CONT.SES(B2:B50; "Sudeste"; D2:D50; "Entregue")

B. Soma com Critérios (Atenção à Ordem dos Parâmetros!)

Existe uma pegadinha clássica de sintaxe no Excel:

3. Operadores em Critérios de Texto e Número

Você pode embutir operadores de comparação diretamente entre aspas no argumento de critério:

• =CONT.SE(C2:C50; ">2000") (Conta fretes acima de R$ 2.000,00)
• =CONT.SE(C2:C50; "<>") (Conta células não vazias)
• =SOMASE(A2:A50; "SP*"; C2:C50) (O curinga * soma tudo que começa com “SP”, como SP-Capital, SP-Interior)

4. 🌐 Quadro Bilíngue (PT-BR vs EN)

Se estiver usando o Excel em inglês ou na versão Web/Microsoft 365 internacional:

5. 💡 Visão de Programador: A Lógica SQL

Se estivéssemos programando um sistema conectado a um banco de dados relacional (MySQL, PostgreSQL ou Oracle), a fórmula =SOMASES(C2:C10; B2:B10; "Sudeste"; D2:D10; "Entregue") seria escrita exatamente assim:

1
2
3
4
5

SELECT SUM(Valor_Frete) AS Total_Faturado,
       COUNT(*) AS Qtd_Entregas
FROM Tabela_Fretes
WHERE Regiao = 'Sudeste' 
  AND Status_Entrega = 'Entregue';

Ao usar SOMASES e CONT.SES, você está implementando consultas de banco de dados diretamente na grade de cálculo do Excel!

📖 Exemplo Guiado: Contagem e Soma Simples

Vamos consolidar uma contagem de pedidos de lanche para a equipe de turno da madrugada.

Passo a Passo

1. Em A1 digite Item e em B1 Valor.
2. Em A2:A5 digite: Sanduíche, Refrigerante, Sanduíche, Café.
3. Em B2:B5 digite: 15, 6, 18, 4.
4. Em D1 digite Resumo Sanduíches:.
5. Na célula E1, conte quantos sanduíches foram pedidos: =CONT.SE(A2:A5; "Sanduíche"). Retornará 2.
6. Na célula E2, some o valor total gasto com sanduíches: =SOMASE(A2:A5; "Sanduíche"; B2:B5). Retornará 33.

✅ Resultado Esperado (Exemplo)

🛠️ Prática Obrigatória 1: Relatório de Faturamento por Região (SOMASE / CONT.SE)

Passo 1: Inserindo a Base Operacional Bruta

Na Planilha 1, construa a tabela de expedição nas células A1 até D8:

Passo 2: Montando a Tabela de Resumo Unicritério

A partir da célula F1, estruture a matriz de resumo:

• F1: Região | G1: Qtd de Fretes | H1: Total Faturado (R$)
• F2: Sudeste
• F3: Sul
• F4: Norte

Passo 3: Aplicando as Fórmulas com Referências Absolutas ($)

1. Na célula G2, conte os fretes da região Sudeste travando o intervalo de busca: =CONT.SE($B$2:$B$8; F2)
2. Na célula H2, some os valores travando tanto a coluna de região quanto a de valores: =SOMASE($B$2:$B$8; F2; $C$2:$C$8)
3. Selecione G2:H2 e arraste a alça de preenchimento até a linha 4.

✅ Resultado Esperado (Prática 1)

🛠️ Prática Obrigatória 2: Agregações Multicritérios de Alta Precisão (SOMASES / CONT.SES)

A gerência agora exige saber apenas o que já foi efetivamente Entregue em cada região, ignorando cargas canceladas ou ainda em trânsito.

Passo 1: Construindo a Matriz de Critérios Cruzados

Ainda na Planilha 1, monte a segunda tabela analítica a partir da célula F7:

• F7: Região | G7: Status Alvo | H7: Entregas Concluídas | I7: Faturamento Realizado
• F8: Sudeste | G8: Entregue
• F9: Sul | G9: Entregue
• F10: Sudeste | G10: Em Trânsito

Passo 2: Codificando com CONT.SES e SOMASES

1. Na célula H8, conte combinando Região e Status: =CONT.SES($B$2:$B$8; F8; $D$2:$D$8; G8)
2. Na célula I8, realize a soma condicional composta (Lembre-se: a coluna da soma $C$ vem primeiro!): =SOMASES($C$2:$C$8; $B$2:$B$8; F8; $D$2:$D$8; G8)
3. Arraste para as linhas 9 e 10.

✅ Resultado Esperado (Prática 2)

📤 Instruções de Entrega (Microsoft Teams)

Após finalizar as duas práticas obrigatórias no mesmo arquivo:

1. Salve seu arquivo como: Atividade_07_SeuNome_SeuSobrenome.xlsx
2. No Microsoft Teams, vá na equipe da turma > guia Tarefas.
3. Envie o arquivo na tarefa “Capítulo 07 - Agregações Condicionais”.
4. Clique em Entregar (Turn In).

💡 Checkpoint de Lógica

Parabéns! Você dominou o conceito de Agrupamento e Redução de Conjuntos (Data Aggregation & Reduction).

Essas operações são a base de qualquer ferramenta moderna de Business Intelligence (como Power BI e Tableau) e bibliotecas de Ciência de Dados (como pandas.DataFrame.groupby() em Python).

🔥 Desafio de Fixação: Agregação com Operadores Numéricos

Calcule na célula I12 o valor total faturado considerando apenas fretes da região Sudeste cujo valor unitário seja estritamente maior que R$ 2.000,00:

1

=SOMASES(C2:C8; B2:B8; "Sudeste"; C2:C8; ">2000")

✅ Resultado Esperado (Desafio)

O Excel somará os valores das linhas 4 (2200) e 8 (3100), retornando exatamente R$ 5.300,00.

📝 Atividade Extra: Questionário de Fixação (Caderno)

Instruções: Responda no caderno, de próprio punho, as 10 perguntas abaixo com base no que foi estudado neste capítulo. Ao concluir, leve o caderno até o professor para correção e visto.

1. O que significa “Agregação de Dados” na computação?
2. Qual a diferença entre CONT.SE e CONT.SES?
3. Na função SOMASE, em qual posição fica o intervalo de soma? E na SOMASES?
4. Escreva um exemplo de fórmula usando SOMASE para somar valores de uma região específica.
5. O que o curinga “” faz dentro de um critério de texto, como em “SP”?
6. Qual comando SQL é equivalente à função SOMASES combinada com múltiplos critérios?
7. Cite as versões em inglês das funções CONT.SE, CONT.SES, SOMASE e SOMASES.
8. Por que é importante travar os intervalos com $ ao usar SOMASE/CONT.SE?
9. Dê um exemplo de uma pergunta de negócio que só pode ser respondida com múltiplos critérios (SOMASES/CONT.SES).
10. Explique a diferença entre calcular o “total geral” e a “agregação segmentada” de uma base de dados.