Banco de dados, funções de texto e Avaliação 7
O que você vai ver aqui
Duas ferramentas que resolvem o problema mais comum do mundo real: a base veio suja. E a Avaliação 7, que é exatamente isso — receber base suja e entregar base tratada.
Funções de banco de dados
Sintaxe comum: =BDSOMA(banco_de_dados; campo; criterios)
- banco_de_dados — a base inteira, incluindo a linha de cabeçalho.
- campo — o nome da coluna entre aspas, ou o número da posição.
- criterios — um intervalo separado, com os mesmos cabeçalhos da base e as condições abaixo deles.
| Função | O que faz |
|---|---|
| BDSOMA | Soma o campo nos registros que atendem aos critérios |
| BDMÉDIA | Média do campo |
| BDCONTAR | Conta registros com valor numérico |
| BDCONTARA | Conta registros não vazios |
| BDMÁX e BDMÍN | Maior e menor valor |
| BDEXTRAIR | Devolve o único registro que atende ao critério |
Como montar o intervalo de critérios
- Condições na mesma linha funcionam como E: categoria Bebidas e filial Norte.
- Condições em linhas diferentes funcionam como OU: filial Norte ou filial Sul.
- Colunas repetidas permitem faixas: valor maior que 500 e valor menor que 2000.
Funções de texto
| Função | Uso |
|---|---|
| ARRUMAR | Remove espaços extras, inclusive duplos no meio |
| MAIÚSCULA, MINÚSCULA, PRI.MAIÚSCULA | Padroniza a caixa |
| ESQUERDA, DIREITA, EXT.TEXTO | Extrai pedaços |
| NÚM.CARACT | Conta caracteres |
| LOCALIZAR e PROCURAR | Acham a posição de um trecho; LOCALIZAR diferencia maiúsculas |
| SUBSTITUIR e MUDAR | Trocam conteúdo por conteúdo ou por posição |
| CONCAT e UNIRTEXTO | Juntam textos; o segundo aceita separador e ignora vazios |
| TEXTO | Converte número em texto com formato definido |
| VALOR | Converte texto em número |
| REPT | Repete um caractere, útil para gráficos em célula |
| TIRAR | Limpa caracteres não imprimíveis |
| EXATO | Compara dois textos considerando a caixa |
Receitas úteis
| Objetivo | Fórmula |
|---|---|
| Primeiro nome | =ESQUERDA(A2;LOCALIZAR(" ";A2)-1) |
| Usuário do e-mail | =ESQUERDA(A2;LOCALIZAR("@";A2)-1) |
| Domínio do e-mail | =EXT.TEXTO(A2;LOCALIZAR("@";A2)+1;100) |
| Nome completo padronizado | =PRI.MAIÚSCULA(ARRUMAR(A2)) |
| Código com zeros à esquerda | =TEXTO(A2;"00000") |
| Juntar com separador | =UNIRTEXTO(", ";VERDADEIRO;A2:D2) |
Ferramentas sem fórmula: Texto para Colunas (Dados) separa por delimitador ou largura fixa; Preenchimento Relâmpago (Ctrl + E) detecta o padrão a partir de exemplos e preenche a coluna.
Limpeza de base importada
Roteiro padrão, nesta ordem:
- ARRUMAR em tudo.
- Padronizar a caixa.
- Converter texto que deveria ser número, com VALOR ou multiplicando por 1.
- Corrigir datas em texto.
- Remover duplicatas (Dados, Remover Duplicatas).
- Conferir com CONT.VALORES se nada sumiu.
- Colar valores por cima, para congelar o resultado.
Avaliação 7
Receber uma base suja de no mínimo 300 registros e entregar a base tratada mais um painel de consultas.
Requisitos obrigatórios
- Base tratada: espaços removidos, caixa padronizada, números e datas convertidos, duplicatas eliminadas, com registro do que foi corrigido.
- Colunas derivadas por fórmula de texto: primeiro nome, domínio de e-mail e código formatado.
- Um intervalo de critérios funcional com pelo menos uma condição E e uma condição OU.
- Quatro indicadores usando funções de banco de dados.
- Indicadores equivalentes calculados com SOMASES e CONT.SES, para comparação.
- Três indicadores estatísticos das aulas 31 e 32.
- Duas regras de formatação condicional com fórmula.
- Três frases de interpretação dos resultados.
| Critério de correção | Pontos |
|---|---|
| Qualidade do tratamento da base | 20 |
| Funções de texto aplicadas corretamente | 15 |
| Intervalo de critérios com E e OU | 15 |
| Funções de banco de dados | 20 |
| Comparação com SOMASES e CONT.SES | 10 |
| Indicadores estatísticos | 10 |
| Formatação condicional com fórmula | 5 |
| Interpretação escrita | 5 |
Na prática
- Monte o intervalo de critérios com cabeçalhos idênticos aos da base.
- Calcule BDSOMA, BDMÉDIA, BDCONTAR e BDMÁX sobre a mesma base.
- Altere o critério nas células e confira os indicadores mudarem sozinhos.
- Extraia primeiro nome e domínio de e-mail com funções de texto.
- Separe cidade e estado de uma coluna única com
Ctrl + E. - Execute o roteiro de limpeza completo numa base fornecida pelo docente.
Exercícios
- Monte um intervalo de critérios que traga bebidas da filial Norte ou laticínios de qualquer filial.
- Extraia o primeiro nome de uma coluna com nomes completos.
- Qual a diferença entre LOCALIZAR e PROCURAR?
- Converta uma coluna de valores em texto para número e comprove pela soma.
- Use
Ctrl + Epara separar cidade e estado de uma coluna única.
Erros comuns
- Esquecer o cabeçalho no intervalo do banco de dados.
- Escrever o cabeçalho do critério diferente do cabeçalho da base.
- Confundir E com OU na montagem das linhas de critério.
Checklist de saída
Você limpa bases reais e consulta dados com critérios complexos e visíveis.
Referências
- REZENDE, Edson Roberto; FRANÇÓIA, Jorge Alberto. Microsoft Excel: apostila de fórmulas e funções.
- MARTINS, Denise; ALMEIDA, Flávio. Excel Avançado para Negócios. Rio de Janeiro: Alta Books, 2021.
- Microsoft Support. Funções de texto. Disponível em: https://support.microsoft.com/pt-br/excel
Testa o que você leu
4 perguntas sobre esta aula. Errar aqui não custa nada - a explicação vem junto da correção.