Pular para o conteúdo
Todo o material de apoio

25 min de leitura

Atualizado em

Como criar e formatar planilhas no Microsoft Excel?

Guia Completo e Didático de Microsoft Excel para 2025: Crie, Formate e Domine Planilhas para Gabaritar em Concursos Públicos!

O Microsoft Excel é uma ferramenta indispensável no ambiente profissional e pessoal, sendo um assunto muito cobrado nas questões de informática em concursos públicos. Dominar suas funcionalidades deixou de ser um diferencial e se tornou um item obrigatório para a contratação. Por isso, apresentamos um roteiro que vai do mais fácil ao mais complexo, preparando você para qualquer desafio, seja no dia a dia ou em provas altamente competitivas.

Vamos juntos nessa jornada rumo à proficiência no Excel!

Módulo 1: Fundamentos Essenciais do Excel (Nível Básico)

Este módulo aborda os conceitos mais básicos do Excel, essenciais para qualquer iniciante e ponto de partida para funcionalidades mais avançadas.

1. O que é o Excel e Por Que Ele é Crucial?

O Microsoft Office Excel é o programa de folha de cálculo mais popular do mercado e uma ferramenta incrivelmente poderosa para tornar significativa uma vasta quantidade de dados. Ele permite ao usuário desde a realização de simples operações algébricas até a criação de planilhas inteligentes e automatizadas que realizam atualizações em tempo real. Suas ferramentas podem ser utilizadas por qualquer pessoa, em diversas áreas como administração, contabilidade, engenharia, marketing e comércio. A linguagem do Excel é universal, sendo impossível imaginar a atividade de determinadas profissões sem essa ferramenta.

2. Estrutura Básica: Células, Linhas e Colunas

O software é estruturado em três elementos fundamentais: células, linhas e colunas.

  • As linhas são identificadas verticalmente por números.

  • As colunas são identificadas horizontalmente por letras.

  • A célula é o ponto de encontro entre uma linha e uma coluna. É justamente no interior dessas células que as fórmulas e funções do Excel serão inseridas.

3. Criando e Organizando Sua Primeira Planilha

No contexto do Excel, o arquivo como um todo é chamado de Pasta de Trabalho. Esta engloba todos os seus objetos, como planilhas (também conhecidas como guias ou abas), gráficos, planilhas de macro, tabelas dinâmicas, gráficos dinâmicos e projetos VBA. O termo "planilha" é tecnicamente correto para se referir a cada uma dessas abas.

Como Inserir Dados: Para começar a preencher sua planilha, basta inserir texto ou números na célula desejada. Em seguida, pressione Enter ou Tab para se mover para a célula seguinte.

Módulo 2: Formatando e Personalizando Planilhas (Nível Intermediário)

A formatação é um passo crucial para tornar suas planilhas mais legíveis, organizadas e eficientes.

4. Definição de Nomes para Otimização

A prática de definir nomes pode facilitar a manipulação de dados em uma planilha, permitindo que você os atualize, audite e gerencie de forma intuitiva e fácil.

Regras na Criação de Nomes: Ao criar nomes, atente-se a algumas regras essenciais:

  • O nome pode ter até 255 caracteres.

  • O primeiro caractere do nome deve ser letra ou underline _.

  • Não deve conter espaços ou caracteres especiais.

  • Não deve possuir o mesmo nome de algum existente na Pasta de Trabalho.

Como Definir um Nome:

  1. Na sua planilha, vá ao menu superior em Fórmulas.

  2. Selecione a célula ou o intervalo que deseja nomear (por exemplo, a célula F2 contendo a cotação do dólar).

  3. Clique em "Definir Nome" no menu Fórmulas.

  4. Digite o nome desejado (por exemplo, "Cot.Dolar").

  5. O campo "Refere-se a" já estará preenchido com a referência da célula selecionada. Clique em OK.

Como Utilizar um Nome em uma Fórmula: Após definir um nome, você pode usá-lo em suas fórmulas, tornando-as mais compreensíveis e fáceis de auditar. Por exemplo, em vez de =B2*F2, você pode usar =B2*Cot.Dolar.

Editando ou Excluindo Nomes: Para gerenciar os nomes definidos (editar, excluir), você pode acessar o Gerenciador de Nomes na aba Fórmulas.

5. Formatos de Número Personalizados

A formatação de número personalizada permite apresentar dados de maneira específica, como moedas, telefones ou CPFs.

Criação de Número Personalizado de Telefone: Para criar um formato personalizado para uma lista de telefones (por exemplo, (27) 99956-2145):

  1. Selecione a coluna que contém os números de telefone.

  2. Vá em "Formato de Número" > "Mais Formatos de Número" > "Personalizado".

  3. No campo "Tipo", digite a formatação desejada, por exemplo, (00) 00000-0000 ou (00) 0000-0000 dependendo do padrão de telefone que você quer.

    • Dica: Tudo que é texto no Excel, usualmente, é colocado entre aspas. Os zeros representam os valores numéricos nos formatos personalizados. O espaço, embora invisível, deve ser inserido entre aspas se você quiser incluí-lo no formato.

Criação de Número Personalizado de CPF: A criação de número personalizado de CPF segue uma lógica similar, adaptando o formato para a máscara de CPF (000.000.000-00).

6. Validação de Dados: Garantindo a Qualidade da Informação

A validação de dados é um conceito que permite limitar o tipo de dado que pode ser inserido em uma célula. É uma ferramenta crucial para garantir a integridade e consistência dos seus dados.

Tipos de Validação de Dados:

  • Validação de Dados Decimal: Permite definir um intervalo de valores numéricos que podem ser inseridos. Por exemplo, você pode permitir apenas notas entre 0 e 10. Se um valor fora do intervalo for digitado, o Excel exibirá uma mensagem de erro.

  • Validação de Dados – Comprimento do Texto: Define o tamanho mínimo e máximo para um texto a ser inserido, como permitir apenas o primeiro nome de uma pessoa.

Mensagens de Apoio na Validação:

  • Mensagem de Entrada: Uma mensagem que aparece quando o usuário seleciona a célula, informando o que precisa ser feito. Por exemplo, "Instrução: Digite notas de 0 a 10".

  • Mensagem de Erro: Uma mensagem personalizada exibida quando o usuário insere dados inválidos. Por exemplo, "Nota Inválida: O valor informado para a nota não é válido. Digite uma nota entre 0 e 10".

Como Aplicar:

  1. Selecione a célula ou o intervalo onde deseja aplicar a validação.

  2. Vá na guia Dados > Validação de Dados.

  3. Na aba Configurações, escolha o critério de validação (ex: Decimal, Comprimento do Texto).

  4. Nas abas Mensagem de Entrada e Alerta de Erro, customize as mensagens.

7. Formatação Condicional Avançada

A Formatação Condicional permite que você formate células automaticamente com base em regras específicas, tornando padrões e anomalias visíveis.

Como Aplicar uma Regra Baseada em Fórmula:

  1. Selecione a base de dados ou o intervalo onde deseja aplicar a formatação.

  2. Na guia Página Inicial, vá em Formatação Condicional > Nova Regra > "Usar uma fórmula para determinar quais células devem ser formatadas".

  3. No campo da fórmula, digite a sua lógica. Por exemplo, =$A2=$K$1 pode ser usada para formatar linhas onde o valor da coluna A corresponde ao valor em K1, travando a coluna A ($A2) para analisar as linhas individualmente e a célula K1 ($K$1) de forma absoluta.

  4. Defina uma cor ou estilo de formatação e clique em OK.

Módulo 3: Segurança e Proteção de Dados (Crucial para o Ambiente Profissional)

Proteger suas planilhas é fundamental, especialmente em ambientes colaborativos ou para dados sensíveis.

8. Protegendo Suas Planilhas e Pastas de Trabalho

O Excel possui funcionalidades robustas para proteger sua planilha ou pasta de trabalho, muito utilizadas em ambientes onde diversas pessoas modificam o mesmo arquivo. A proteção é necessária para evitar alterações indevidas e preservar a integridade dos dados.

a) Proteção de Planilha: Impede que outras pessoas façam alterações indesejadas, limitando a capacidade de edição.

  1. Vá na guia Revisão > Proteger Planilha.

  2. Você pode selecionar ou desmarcar os critérios permitidos ao usuário.

  3. Defina uma senha (por exemplo, excel2024) e clique em OK. Confirme a senha novamente.

    • Atenção: Se você perder a senha, não será possível recuperar a planilha. Quando a planilha está protegida, tentar alterar uma célula bloqueada resultará em uma mensagem de erro.

b) Desproteção de Planilha: Para desproteger, siga os mesmos passos: Revisão > Desproteger Planilha > informe a senha > OK.

c) Proteção da Pasta de Trabalho: A proteção da Pasta de Trabalho lida com questões estruturais, como impedir que o usuário edite, oculte ou exclua as planilhas (guias/abas). No entanto, o usuário conseguirá editar as células, mesmo as que possuem funções ou fórmulas.

  1. Vá na guia Revisão > Proteger Pasta de Trabalho.

  2. Defina uma senha (por exemplo, Excel) e clique em OK.

    • Observação: A opção "Windows" para proteção geralmente está desabilitada em versões recentes do Excel. A opção "Estrutura" impede que outros usuários visualizem planilhas ocultas, adicionem, movam, excluam ou ocultem planilhas e renomeiem planilhas.

d) Proteção do Arquivo com Senha ao Salvar: Esta é uma opção externa ao Excel para proteger o arquivo com senha na hora de salvar, controlando quem pode abri-lo e/ou modificá-lo.

  1. Com a planilha aberta, vá em Arquivo > Salvar como.

  2. Escolha o local do arquivo e, na caixa de diálogo "Salvar como", clique em Ferramentas (geralmente ao lado do botão "Salvar") > Opções Gerais.

  3. Você pode definir uma "Senha de proteção" (para abrir o arquivo) e uma "Senha de gravação" (para poder editar). Recomenda-se também marcar a opção "Recomendável somente leitura".

  4. Ao abrir um arquivo protegido, o Excel solicitará as senhas para acessar e/ou editar.

    • Dica: Essa funcionalidade evita que pessoas não autorizadas acessem ou criem cópias sem permissão. Lembre-se que nem sempre criptografa os dados, a menos que uma senha criptografada seja criada no modo de exibição Backstage.

Módulo 4: Funções Essenciais para Concursos e o Dia a Dia (Nível Essencial/Intermediário)

As fórmulas e funções são os assuntos mais cobrados nas provas de concursos públicos. Dominá-las é fundamental para sua aprovação. É necessário inserir o sinal "=" antes de qualquer fórmula ou função para que o Excel a reconheça.

9. As Funções Básicas Mais Cobradas em Concursos

a) Função SOMA: Como o próprio nome sugere, a função SOMA calcula o total de valores especificados.

  • Sintaxe: =SOMA(número1; [número2]; ...) ou =SOMA(intervalo).

  • Exemplos:

    • =SOMA(B4;B5;B7): Somará os números contidos nas células B4, B5 e B7 individualmente. O ponto e vírgula separa células ou valores individuais.

    • =SOMA(B4:B8): Somará todos os números contidos no intervalo de células que vai de B4 a B8. Os dois pontos indicam um intervalo contínuo.

  • Exceção Importante: A função SOMA ignora valores de texto e retorna a soma dos valores numéricos, ao contrário de operações como =A1+B1, que retornariam #VALUE! se uma das células contivesse texto.

b) Função MÉDIA: Calcula a média aritmética dos números em um intervalo.

  • Dica de Concurso: O nome da função é =MÉDIA, com acento. Se estiver escrita sem acento em uma questão, a alternativa está errada.

  • Cuidado: Não confunda com a função MED (MEDIANA), que tem usabilidade muito diferente.

c) Função SE (IF): Tomando Decisões na Planilha A função SE é uma ferramenta essencial para realizar comparações lógicas e tomar decisões com base em condições específicas.

  • Sintaxe: =SE(teste_lógico; valor_se_verdadeiro; valor_se_falso).

    • teste_lógico: A condição a ser avaliada (por exemplo, B3>6).

    • valor_se_verdadeiro: O que fazer se a condição for verdadeira.

    • valor_se_falso: O que fazer se a condição for falsa.

  • Pensamento Lógico: Ao usar o SE, pense em "o que aconteceria se essa condição fosse atendida vs. o que aconteceria se não fosse?".

Tipos de Referências para a Função SE:

  • Referência Relativa: O Excel ajusta automaticamente as referências de célula ao arrastar a fórmula para outras células. Por exemplo, de B3 para B4, B5, etc.

  • Referência Absoluta: As referências de célula ficam fixas e não mudam ao arrastar a fórmula. Para fixar uma referência, use o caractere $ antes da letra da coluna e/ou do número da linha (ex: $I$3). A tecla F4 pode ser usada para alternar entre referências relativas, absolutas e mistas. Isso torna a fórmula mais flexível, permitindo alterar critérios sem modificar a fórmula original.

  • Referência Mista: Fixa apenas a coluna ou apenas a linha (ex: $N3 fixa a coluna N, O$3 fixa a linha 3). Útil quando os critérios variam para cada linha ou coluna.

d) Função SE com Caixa de Seleção: Permite que o usuário interaja com a planilha marcando uma caixa, e o Excel reage a essa seleção.

  1. Ative a guia "Desenvolvedor": Vá em Arquivo > Opções > Personalizar Faixa de Opções > marque a opção Desenvolvedor.

  2. Na guia Desenvolvedor > Inserir > selecione Caixa de Seleção (Controle de Formulário).

  3. Desenhe a caixa e, opcionalmente, apague o texto padrão. Arraste para as demais linhas.

  4. Clique com o botão direito na caixa > Formatar Controle. Na aba Controle, vincule a caixa a uma célula auxiliar (por exemplo, F2). Quando a caixa é marcada, a célula vinculada exibe VERDADEIRO; se desmarcada, exibe FALSO.

  5. Use a função SE com base no valor VERDADEIRO ou FALSO da célula vinculada (ex: =SE(F2=VERDADEIRO;"QUITADO";"PENDENTE")).

10. Funções de Contagem e Média com Múltiplos Critérios

a) Função CONTSES (COUNTIFS): Conta o número de células que satisfazem múltiplos critérios. A função CONT.SE é para um único critério.

  • Sintaxe: =CONT.SES(intervalo_critério1; critério1; intervalo_critério2; critério2; ...).

    • intervalo_critério1: O primeiro intervalo a ser avaliado.

    • critério1: O critério correspondente para o primeiro intervalo.

  • Exemplo: Contar pessoas do cargo "Assistente ADM" no estado do "ES".

  • Importante: Qualquer critério de texto ou que inclua símbolos lógicos/matemáticos deve estar entre aspas duplas (""). Critérios numéricos não exigem aspas.

b) Função MÉDIASES (AVERAGEIFS): Similar ao CONTSES, mas calcula a média dos valores que satisfazem múltiplos critérios.

11. Operadores Lógicos (E, OU) em Conjunto com a Função SE

Para condições mais complexas, você pode aninhar operadores lógicos E (AND) e OU (OR) dentro da função SE.

a) Função SE com E (AND): A condição E exige que TODOS os critérios sejam verdadeiros para que a função retorne VERDADEIRO.

  • Exemplo: Para aprovar um aluno, a nota deve ser maior ou igual a 6 E a frequência deve ser acima de 80%.

  • Sintaxe: =SE(E(condição1; condição2; ...); valor_se_verdadeiro; valor_se_falso).

b) Função SE com OU (OR): A condição OU exige que PELO MENOS UM dos critérios seja verdadeiro para que a função retorne VERDADEIRO.

  • Exemplo: Um aluno pode ser aprovado se a nota for maior ou igual a 6 OU se a frequência for de 80%.

  • Sintaxe: =SE(OU(condição1; condição2; ...); valor_se_verdadeiro; valor_se_falso).

c) Funções SE Aninhadas (Nested IFs): Para lógicas ainda mais complexas, onde há múltiplas condições e resultados diferentes, você pode usar uma função SE dentro de outra. Isso ocorre quando o valor_se_falso (ou valor_se_verdadeiro) de um SE é outra função SE.

  • Exemplo: Converter o número do mês para o nome do mês (=SE(MÊS(Data)=1;"janeiro";SE(MÊS(Data)=2;"fevereiro";"Não existe"))).

12. Manipulação Avançada de Textos

Embora o Excel seja focado em dados numéricos, ele oferece diversas funções poderosas para trabalhar com textos.

a) Função CONCATENAR (CONCATENATE): Combina ou une várias cadeias de texto em uma única cadeia.

  • Sintaxe: =CONCATENAR(texto1; [texto2]; ...).

  • Exemplo: Juntar "Nome" e "Sobrenome" de pessoas.

  • Dica Importante: Para separar os textos com espaços (ou qualquer outro caractere), você deve incluí-los explicitamente entre aspas duplas na função (ex: CONCATENAR(A2;" ";B2) para adicionar um espaço entre A2 e B2).

b) Função EXT.TEXTO (MID): Extraindo Partes do Texto Extrai um número específico de caracteres de uma cadeia de texto, começando de uma posição que você especificar.

  • Sintaxe: =EXT.TEXTO(texto_a_extrair; núm_inicial; núm_caract).

    • texto_a_extrair: A célula ou a cadeia de texto de onde você quer extrair.

    • núm_inicial: A posição do primeiro caractere que você quer extrair, contando da esquerda para a direita (o primeiro caractere é 1).

    • núm_caract: O número de caracteres que você deseja que EXT.TEXTO retorne.

c) Função LOCALIZAR (FIND): Encontrando Posições no Texto Retorna a posição inicial de uma cadeia de texto dentro de outra cadeia de texto.

  • Sintaxe: =LOCALIZAR(texto_procurado; no_texto; [núm_inicial]).

    • texto_procurado: O texto que você deseja encontrar (ex: " " para um espaço).

    • no_texto: O texto onde você deseja procurar.

    • [núm_inicial]: Opcional, a posição do caractere no no_texto onde você deseja começar a procurar. Se omitido, começa em 1.

d) Combinando EXT.TEXTO e LOCALIZAR (Avançado): Essa combinação é poderosa para extrair partes de textos que possuem tamanhos diferentes. Por exemplo, para extrair apenas o primeiro nome de uma lista:

  • Você pode usar LOCALIZAR para encontrar a posição do primeiro espaço (que indica o fim do primeiro nome) e usar esse número como núm_caract para EXT.TEXTO.

  • Exemplo: =EXT.TEXTO(B2;1;LOCALIZAR(" ";B2)) extrai o texto da célula B2, começando da posição 1, até a posição do primeiro espaço.

  • Dica: Em cenários complexos, colunas auxiliares podem ser usadas para armazenar a localização de caracteres, simplificando a função principal.

Módulo 5: Funções de Procura e Análise de Dados (Nível Avançado e Essencial para Concursos)

Este módulo aprofunda em ferramentas de busca e análise de dados, cruciais para relatórios e tomada de decisões.

13. Funções de Procura Vertical e Horizontal (PROCV e PROCH)

As funções de procura são fundamentais para buscar informações em grandes bases de dados. A lógica dessas funções é sempre ter um dado exclusivo para realizar a procura, como um CPF, código, ou matrícula.

a) Função PROCV (VLOOKUP): Procura Vertical Procura um valor na primeira coluna de um intervalo de tabela e retorna um valor da mesma linha em uma coluna especificada.

  • Sintaxe: =PROCV(valor_procurado; matriz_tabela; núm_índice_coluna; [procurar_intervalo]).

    • valor_procurado: O valor que você deseja procurar.

    • matriz_tabela: O intervalo de células onde estão os dados da tabela (toda a base de dados).

    • núm_índice_coluna: O número da coluna (a partir da primeira coluna da matriz_tabela) de onde o valor correspondente deve ser retornado.

    • [procurar_intervalo]: Define se a correspondência deve ser exata (FALSO ou 0) ou aproximada (VERDADEIRO ou 1). É altamente recomendável usar FALSO ou 0 para correspondência exata para evitar erros nas análises, especialmente com dados textuais.

b) Função PROCH (HLOOKUP): Procura Horizontal Similar ao PROCV, mas procura um valor na primeira linha de um intervalo de tabela e retorna um valor da mesma coluna em uma linha especificada.

  • Sintaxe: =PROCH(valor_procurado; matriz_tabela; núm_índice_linha; [procurar_intervalo]).

14. PROCX (XLOOKUP): A Nova Geração de Procura no Excel

O PROCX é uma função de procura mais moderna e eficaz, superando as funcionalidades de funções mais antigas como CORRESP e ÍNDICE.

  • Sintaxe: =PROCX(pesquisa_valor; pesquisa_matriz; matriz_retorno; [se_não_encontrada]; [modo_correspondência]; [modo_pesquisa]).

    • pesquisa_valor: O valor que você deseja buscar.

    • pesquisa_matriz: A matriz ou intervalo onde o valor será pesquisado.

    • matriz_retorno: A matriz ou intervalo de onde o Excel buscará o valor correspondente para retornar.

    • [se_não_encontrada]: Opcional. Funciona como um SEERRO, exibindo uma mensagem caso o valor não seja encontrado.

    • [modo_correspondência]: Define o tipo de correspondência:

      • 0: Correspondência exata (padrão, recomendado).

      • -1: Correspondência exata ou próximo item menor.

      • 1: Correspondência exata ou próximo item maior.

      • 2: Correspondência de caractere curinga (para buscas baseadas em padrões).

    • [modo_pesquisa]: Opcional. Define a direção da pesquisa:

      • 1: Primeiro para o último (padrão).

      • -1: Último para o primeiro.

      • 2: Pesquisa binária crescente (para dados classificados em ordem crescente para maior velocidade).

      • -2: Pesquisa binária decrescente (para dados classificados em ordem decrescente para maior velocidade).

Caracteres Curinga no PROCX: São utilizados para buscas não exatas, baseadas em padrões.

  • * (Asterisco): Busca qualquer sequência de caracteres. Ex: PROCX("Produto*"; A:A; B:B; "Não Encontrado") busca qualquer produto que comece com "Produto".

  • ? (Interrogação): Busca exatamente um caractere. Ex: PROCX("Camiseta ?"; A:A; B:B; "Não Encontrado") busca "Camiseta" seguido por um único caractere.

  • ~ (Til): Busca o caractere curinga de forma literal. Ex: PROCX("Caminho~*"; A:A; B:B; "Não Encontrado") busca "Caminho*" (literalmente com o asterisco).

PROCX Avançada com Múltiplos Critérios: O PROCX pode ser usado para buscar valores com base em vários critérios, concatenando os pesquisa_valor e pesquisa_matriz.

  • Exemplo: Buscar o produto "Macbook" no estado "Espírito Santo" para uma análise de "Receita" com indicador "Realizado".

    • O pesquisa_valor seria a concatenação das células de Estado e Produto (L4&L5).

    • A pesquisa_matriz seria a concatenação dos intervalos das colunas de Estado e Produto na base de dados (A4:A12&B4:B12).

    • A matriz_retorno poderia ser definida por um segundo PROCX para buscar as colunas de "Receita Previsto", "Receita Realizado", etc., com base na análise e indicador desejados.

15. Tabelas Dinâmicas: Resumo e Análise de Grandes Volumes de Dados

As Tabelas Dinâmicas são ferramentas poderosas para resumir, analisar, comparar, explorar e apresentar dados, permitindo a visualização fácil de padrões e tendências. A alta gestão as utiliza para tomar decisões críticas.

Regras para a Criação de Tabelas Dinâmicas: Para evitar erros e garantir uma análise precisa, siga as seguintes regras:

  • Não possuir Linhas ou Colunas Vazias: Podem causar erros na análise.

  • Formatação Inconsistente: Dados com formatação errada podem ocasionar erros em cálculos (ex: número formatado como texto).

  • Múltiplas Linhas de Títulos: As tabelas devem possuir apenas uma única linha de cabeçalho para identificar os campos.

  • Dados Duplicados sem Chave Primária: Podem causar ambiguidades e imprecisão.

  • Intervalos Nomeados Não Associados: Um nome criado a partir de um intervalo é desconsiderado se não for referenciado.

  • Dados Ausentes ou Incorretos: Os dados devem estar completos e sem erros de cálculos.

Como Criar uma Tabela Dinâmica:

  1. Selecione o intervalo de dados que você deseja analisar.

  2. Opcional, mas recomendado: Crie um nome para o intervalo da sua tabela. Vá na guia Fórmulas > Definir Nome (ex: "Analise" para o intervalo A1:D236).

  3. Vá no menu Inserir e clique em Tabela Dinâmica.

  4. Na caixa de diálogo "Criar Tabela Dinâmica":

    • "Selecionar uma tabela ou intervalo": Insira o nome do intervalo que você criou (ex: "Analise") ou selecione o intervalo diretamente.

    • "Escolha onde deseja que o relatório de tabela dinâmica seja colocado": Selecione "Planilha Existente" e defina o local inicial (ex: célula I1).

    • Clique em OK.

    • Observação: As opções "Usar uma fonte de dados externa" ou "Adicionar esses dados ao Modelo de Dados" são para análises mais complexas e fontes de dados fora da pasta de trabalho atual.

Itens do Relatório da Tabela Dinâmica (Campos da Tabela Dinâmica): Após criar a tabela, a lista de campos aparecerá. Você pode arrastar os campos para as seguintes áreas:

  • Filtros: Para filtrar os dados exibidos.

  • Colunas: Para exibir os dados nas colunas da tabela dinâmica.

  • Linhas: Para exibir os dados nas linhas da tabela dinâmica.

  • Valores: Para realizar cálculos (soma, contagem, média, etc.) sobre os dados.

Exibindo Valores Como Porcentagens: Para visualizar a proporção de cada item em relação ao total:

  1. Na sua Tabela Dinâmica, clique com o botão direito do mouse nos valores.

  2. Vá em "Mostrar Valores Como" > "% do Total Geral".

Excluindo Tabelas Dinâmicas:

  1. Selecione a Tabela Dinâmica que deseja excluir.

  2. Vá na faixa de opções "Análise de Tabela Dinâmica" > "Selecionar" > "Tabela Dinâmica Inteira" > Pressione Delete.

16. Gráficos Dinâmicos

Os Gráficos Dinâmicos são representações visuais interativas dos dados de uma Tabela Dinâmica. Eles permitem explorar e visualizar tendências e padrões de forma ainda mais eficaz. A criação de gráficos dinâmicos segue um processo semelhante à criação de tabelas dinâmicas, conectando-se à mesma fonte de dados.

17. Gerenciador de Cenários: Análise "E Se"

O Gerenciador de Cenários é uma ferramenta de "Teste de Hipóteses" que permite criar e gerenciar diferentes conjuntos de valores (cenários) em uma planilha, para ver como diferentes condições afetam os resultados.

a) Criando Cenários (Pessimista, Realista, Otimista):

  1. Vá na guia Dados > Previsão > Teste de Hipóteses > Gerenciador de Cenários.

  2. Clique em "Adicionar".

  3. Nome do cenário: Dê um nome (ex: "Pessimista").

  4. Células variáveis: Selecione as células cujos valores serão alterados em cada cenário (ex: B2:G5).

  5. Comentário: Adicione uma breve descrição.

  6. Clique em OK e insira os valores para as células variáveis para este cenário. Repita o processo para outros cenários (ex: "Realista", "Otimista").

    • Dicas de Proteção: O Excel seleciona por padrão a opção "Evitar alterações" para impedir que usuários da planilha alterem os dados do cenário. A opção "Ocultar" oculta o cenário caso a planilha esteja protegida com senha, evitando erros.

b) Mesclagem dos Cenários: Para integrar cenários de diferentes planilhas ou pastas de trabalho:

  1. Com a planilha desejada selecionada, vá em Dados > Teste de Hipóteses > Gerenciador de Cenários.

  2. Clique em "Mesclar".

  3. Selecione a Pasta e as Planilhas de onde deseja mesclar os cenários.

c) Exibindo e Resumindo os Cenários: Para visualizar e gerar um relatório dos cenários:

  1. No Gerenciador de Cenários, selecione um cenário e clique em "Mostrar" para aplicá-lo à planilha.

  2. Para um relatório completo, clique em "Resumir".

  3. Escolha o "Tipo de relatório": "Resumo do cenário" (padrão, recomendado para análises mais amplas, criado em uma nova planilha) ou "Relatório de tabela dinâmica do cenário".

  4. Defina as "Células de resultado" (ex: uma célula com uma função SOMA que resume os lucros dos cenários).

18. Solver: Otimização Matemática no Excel

O Solver é uma ferramenta poderosa integrada ao Microsoft Excel, projetada para resolver problemas de otimização matemática. Ele permite maximizar ou minimizar uma função objetivo, considerando um conjunto de restrições.

Como Ativar o Solver: Por padrão, o Solver não vem instalado na Faixa de Opções do Excel.

  1. Vá em Arquivo > Opções.

  2. Na janela de opções, selecione "Suplementos".

  3. Na parte inferior, em "Gerenciar", selecione "Suplementos do Excel" e clique em "Ir...".

  4. Marque a caixa de seleção "Solver" e clique em OK. Após a ativação, o Solver aparecerá na guia Dados, no grupo Análise.

19. Macros e VBA: Automatizando Tarefas Repetitivas

Macros são sequências de comandos e ações que podem ser gravadas e executadas automaticamente, automatizando tarefas repetitivas e otimizando o tempo. A linguagem de programação por trás das macros no Excel é o VBA (Visual Basic for Applications).

a) Ativando o Menu Desenvolvedor: Para acessar as ferramentas de macro, primeiro ative a guia Desenvolvedor:

  1. Vá em Arquivo > Opções.

  2. Na janela de opções, selecione "Personalizar Faixa de Opções".

  3. No lado direito, marque a caixa de seleção "Desenvolvedor" e clique em OK.

b) Gravando uma Macro:

  1. Na guia Desenvolvedor, clique em "Gravar Macro".

  2. Na caixa de diálogo "Gravar macro":

    • Nome da macro: Dê um nome significativo (ex: "Cadastro").

    • Tecla de atalho: Opcional. Defina uma combinação de teclas (ex: Ctrl+Shift+R). Certifique-se de que não seja um comando já existente.

    • Armazenar macro em: Escolha "Esta pasta de trabalho" para que a macro funcione apenas nela.

    • Descrição: Adicione uma descrição para que o usuário saiba o que a macro faz.

  3. Clique em OK. A partir deste momento, o Excel gravará todos os seus passos na planilha.

  4. Execute as ações que deseja automatizar (ex: selecionar um intervalo, copiar, colar em outra planilha, limpar o intervalo original).

  5. Quando terminar, na guia Desenvolvedor, clique em "Parar Gravação".

c) Entendendo e Alterando o Código VBA da Macro: Após gravar uma macro, você pode ver e editar o código VBA gerado, o que permite funcionalidades mais avançadas. O código VBA da macro "Cadastro" define variáveis para as planilhas de cadastro e base de dados, encontra a última linha preenchida, copia o intervalo de dados da planilha de cadastro, cola na próxima linha vazia da base de dados e limpa o intervalo de cadastro.

d) Executando uma Macro:

  1. Vá na guia Desenvolvedor > "Macros".

  2. Selecione a macro desejada e clique em "Executar".

    • Você também pode usar a tecla de atalho definida durante a gravação.

e) Salvando uma Macro no Computador: Para que as macros funcionem, a pasta de trabalho precisa ser salva em um formato específico:

  1. Vá em Arquivo > Salvar Como.

  2. Em "Tipo", selecione "Pasta de Trabalho Habilitada para Macro do Excel (.xlsm)".

f) Atribuindo a Macro a um Botão: Você pode criar um botão ou forma na sua planilha e atribuir a macro a ele para uma execução mais fácil e intuitiva.

  1. Insira uma forma ou imagem na sua planilha.

  2. Clique com o botão direito do mouse na forma/imagem > "Atribuir Macro...".

  3. Selecione a macro desejada e clique em OK.

20. Formulários Personalizados: Interação Intuitiva com Seus Dados

Os formulários personalizados são ferramentas que facilitam a coleta e o preenchimento de dados em planilhas, permitindo que os usuários insiram informações de forma mais organizada e intuitiva. No Excel, existem vários controles de formulário, como caixas de combinação, botões de opção e caixas de seleção.

a) Ativando a Guia Desenvolvedor: Assim como para macros, ative a guia Desenvolvedor (ver passo 19a).

b) Criando um Formulário de Consulta de Produtos (Exemplo):

  1. Layout: Crie o layout do seu formulário com campos como "Produto", "Categoria", "Unidade", "Preço", "Quantidade" e "TOTAL". Remova as linhas de grade para um visual mais limpo.

  2. Inserindo uma Caixa de Combinação (Controle de Formulário):

    • Vá na guia Desenvolvedor > Inserir > selecione Caixa de Combinação (Controle de Formulário).

    • Desenhe e ajuste o tamanho da caixa na frente do campo "Produto".

    • Clique com o botão direito na caixa > Formatar Controle.

      • Intervalo de entrada: Selecione a lista de produtos (ex: BD!$A$2:$A$14 de uma planilha de dados mestre).

      • Vínculo da célula: Escolha uma célula auxiliar (ex: M1) que armazenará o índice (número da linha) do item selecionado na caixa de combinação. Esta célula será usada em fórmulas.

c) Utilizando a Função ÍNDICE para Preencher o Formulário: Para que os campos do formulário (Categoria, Unidade, Preço) sejam preenchidos automaticamente com base na seleção do produto na Caixa de Combinação, use a função ÍNDICE.

  • Sintaxe da Função ÍNDICE: =ÍNDICE(matriz; núm_linha; [núm_coluna]).

    • matriz: O intervalo completo da sua base de dados (ex: BD!$A$2:$D$14).

    • núm_linha: Será a célula vinculada da caixa de combinação (ex: M1) + 1 (para pular o cabeçalho da base de dados).

    • [núm_coluna]: O número da coluna de onde você quer extrair o dado (ex: 2 para categoria, 3 para unidade, 4 para preço).

  • Para o TOTAL: Faça uma multiplicação simples da "Quantidade" (célula de entrada do usuário) pelo "Preço" obtido pela função ÍNDICE.

  • Formatação Final: Formate seu formulário com cores e destaque a célula de "Quantidade", que é a única que o usuário irá inserir dados. Oculte as células auxiliares (como M1).

Módulo Final: Dicas de Estudo e Recursos Adicionais para Concursos

Para gabaritar as questões de Excel em concursos públicos, a prática é, sem dúvida, a melhor forma de estudar.

21. Como Gabaritar Excel em Concursos Públicos

  • Pratique Constantemente: Tenha em mãos as bases das fórmulas e funções e crie seus próprios exercícios. É assim que se aprende e se internaliza os comandos, ao invés de apenas decorá-los.

  • Atenção aos Detalhes: O Excel exige muita atenção, pois uma vírgula fora do lugar pode tornar uma alternativa falsa. Fique atento(a) a acentos (como na MÉDIA) e à sintaxe (ponto e vírgula vs. dois pontos).

  • Atalhos do Teclado: Conhecer os atalhos do Excel mais cobrados em provas de concurso pode economizar tempo precioso. Alguns dos mais recorrentes incluem:

    • F1: Exibe o painel de Ajuda do Excel.

    • F2: Ativa a célula e coloca o cursor no final do conteúdo.

    • F3: Exibe a caixa de diálogo "Colar nome" (se nomes foram definidos).

    • F4: Repete o último comando ou ação.

    • F5: Exibe a caixa de diálogo "Ir para".

    • F6: Alterna entre a planilha, a Faixa de Opções, o painel de tarefas e os controles de zoom.

    • CTRL+SHIFT+Mais (+): Exibe a caixa de diálogo Inserir para inserir células em branco.

  • Priorize Conteúdos Essenciais: Funções como SOMA, SE, PROCV, e os conceitos de Tabelas Dinâmicas e proteção de dados são recorrentes em provas.

22. Recursos de Estudo Complementares

Para aprofundar seus conhecimentos e garantir a aprovação, a Nova Concursos oferece:

  • Apostilas e Cursos: Material elaborado por especialistas.

  • Mapas Mentais: Para revisão e fixação.

  • Cursos Grátis: Conteúdos para ajudar a sair do zero e conquistar a aprovação.

  • Aulas Gratuitas no YouTube: Dicas de professores de informática para gabaritar questões de Excel.

Lembre-se: a constância e a qualidade do seu estudo farão toda a diferença. Continue acompanhando os blogs especializados e pratique sempre!