excel_mod2_u3

Prévia do material em texto

Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
1
MÓDULO 2 | UNIDADE 3 - EXCEL RECURSOS AVANÇADOS
RECURSOS AVANÇADOS (PARTE 2)
Nesta unidade vamos estudar alguns recursos avançados do Excel relacionados à:
• Formatação, validação e consolidação de dados.
• Criação de tabelas, gráficos e formulários.
• Funcionalidades que agilizam a obtenção de resultados em planilhas e análise de dados.
Bons estudos!
Aplicar formatação condicional
A formatação condicional permite definir critérios para formatar células, incluindo formatos para fon-
te, borda e padrões de sombreamento. Esse recurso é muito útil quando se tem diversos dados na 
planilha e é preciso diferenciá-los por meio de um formato específico.
O comando Formatação Condicional pode ser encontrado na guia Página Inicial, grupo Estilo.
Quer um exemplo de uso?
Podemos definir que as células fiquem na cor vermelha quando houver valores entre 0 e 4,99, 
para indicar reprovação em uma disciplina.
Colocar, em azul, os valores entre 5 e 6,99, para indicar que o aluno passará por avaliação.
Colocar, em verde, os valores acima de 7, para indicar aprovação.
Personalizar formato de números
Para personalizar um formato de números, acesse a guia Página Inicial, grupo Células, opção Forma-
tar > Formatar células.
Na guia Número, clique na categoria Personalizado.
A formatação personalizada de números é aplicável somente em valor positivo, negativo, nulo e texto, 
sendo possível definir cor e máscaras de formatação.
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
2
A máscara ou código de formatação é um recurso 
utilizado para estabelecer a forma como se preten-
de mostrar dados específicos na planilha, facilitan-
do a organização e visualização de dados.
No exemplo, para o dado de entrada CPF, um usuá-
rio poderá digitar o número sem pontos e sem hífen, 
deste modo: 47638497891. Outro usuário poderá di-
gitar o CPF sem pontos e com hífen: 634784778-41. 
E um terceiro usuário poderá digitar nesta forma: 
974.284.518-64. Essa variação na entrada de dados 
causa uma desordem na planilha.
Conheça a estrutura da máscara de formatação para número:
No Excel, um formato de número pode ser composto de até quatro seções de códigos, separa-
das por ponto e vírgula. Cada código define um tipo de formato: números positivos, números 
negativos, zeros e texto, sempre nessa ordem.
Não é preciso especificar todas as seções de código no formato. Se for criada apenas uma seção 
de código, esta será usada para todos os números. Caso uma das seções não seja usada, deve-se 
incluir o ponto e vírgula final relativo a essa seção.
Personalizar formato de números
Existem diversas possibilidades de uso da funcionalidade de personalização de números. 
Texto e espaçamento
Para exibir um dado específico em formato de texto, os caracteres (texto ou números) devem estar 
entre aspas (“ “). Esses sinais devem ser usados na seção específica dos códigos de formato. Por exem-
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
3
plo, se for definido o código 0,00” Saldo Credor”;-0,00” Saldo Devedor”, o dado será mostrado do 
seguinte modo:
• Para valor positivo: “172,53 Saldo Credor”.
• Para valor negativo: “-172,53 Saldo Devedor”.
Seção para entrada de texto
Ao usar a seção de texto, a última do formato de número, inclua um sinal de arroba (@) na formatação 
para exibir um texto qualquer na célula com um texto pré-existente na máscara (digitado entre aspas).
Observação: Se o formato não incluir uma seção de texto, o texto inserido não será afetado pelos for-
matos numéricos, ou seja, ele será mantido.
Adicionar espaço
Em um formato de número é possível criar um espaço da largura de um caractere. Para isso, basta 
incluir no código de formatação um sublinhado _ seguido de um caractere.
Repetir caracteres
Existe um formato utilizado para preencher a largura de uma coluna repetindo-se determinado ca-
ractere. Basta incluir um asterisco (*) no formato do número, por exemplo, digite 0*- para incluir traços 
após um número o suficiente para preencher a célula.
Formatos de data
Conheça as formatações possíveis para data. Vamos utilizar como exemplo a data 25/05/2018.
Formatos de hora
O Excel possui diversas possibilidades de formatos de horas:
• h = Exibe a hora como um número sem um zero à esquerda.
• [h] = Tempo decorrido, em horas. Quando estiver trabalhando com uma fórmula que retorna um 
tempo em que o número de horas exceder 24, utilize um formato número que se assemelha: 
mm:ss [h].
• hh = Exibe a hora como um número com um zero à esquerda quando apropriado. Se o formato 
contiver AM ou PM, a hora será baseada no relógio 12-Hour. Caso contrário, a hora será baseada 
no relógio 24-Hour.
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
4
• m = Exibe o minuto como um número sem um zero à esquerda. O m deve aparecer imediatamen-
te após o símbolo h ou hh.
• [m] = Tempo decorrido, em minutos. Quando estiver trabalhando com uma fórmula que retorna 
um tempo em que o número de minutos exceder 60, utilize um formato número que se asseme-
lha [mm]: SS.
• mm = Exibe o minuto como um número com um zero à esquerda quando apropriado. O mm deve 
aparecer imediatamente após o símbolo h ou hh.
• s = Exibe o segundo como um número sem um zero à esquerda.
• [s] = Tempo decorrido, em segundos. Quando estiver trabalhando com uma fórmula que retorna 
um tempo em que o número de segundos exceder 60, utilize um formato número que se asse-
melha: [ss].
• ss = Exibe o segundo como um número com um zero à esquerda quando apropriado. Quando 
quiser exibir frações de segundo, utilize um formato de número que se assemelha: h:mm:ss.00.
Aplicar validação de dados
O recurso de validação de dados permite restringir a entrada de informações específicas em deter-
minada célula. Além da limitação de dados, o comando permite exibir uma mensagem que informa 
quais dados podem ser cadastrados e, ainda, criar listas contendo, por exemplo, siglas de estados, 
cidades, estado civil, entre outras, agilizando a entrada de dados na planilha.
Para acessar a o recurso, vá na guia Dados, no grupo Ferramentas de Dados, clique em Validação 
de Dados.
Conheça exemplos do uso da validação de dados:
Situação 1
Uma empresa tem como norma a não contratação de funcionários que nasceram antes de 1970. Nes-
se caso, podemos utilizar a Validação de Dados para exibir um aviso sobre não aceitar o cadastro para 
a vaga quando o ano de nascimento do candidato não corresponder ao critério.
Situação 2
No processo seletivo para uma vaga, podemos criar uma restrição relacionada ao tempo de experiên-
cia do candidato. Se esse tempo for menor do que o estipulado, poderá ser exibida uma mensagem 
alertando que não corresponde ao tempo mínimo exigido.
Auditar fórmulas
O recurso de auditoria de fórmulas tem a função de localizar a entrada de dados incorretos, verificar 
erros em fórmulas, entre outras. Para verificar as opções de auditoria disponíveis no Excel, acesse a 
guia Fórmulas, grupo Auditoria de Fórmulas.
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
5
Conheça alguns erros comuns em células sinalizadas pelo Excel:
##### - Uma coluna não tem largura suficiente para o dado de entrada. Para corrigir esse erro, po-
de-se: aumentar a largura da coluna; reduzir o conteúdo para caber na coluna; aplicar um formato de 
número diferente. Foi usada uma data ou uma hora negativa. Se estiver sendo usado o sistema de 
data 1900, as datas e horas no Excel deverão ter valores positivos. Ao subtrair datas e horas, certifique-
-se de que a fórmula foi criada corretamente. Se a fórmula estiver correta, embora o resultado seja um 
valor negativo, o valor poderá ser exibido, estabelecendo um formato que não o de uma data ou hora.
#VALOR! - Um tipo errado de argumento ou operando foi utilizado.
Possíveis causas e soluções:
• Inserir texto quando a fórmula requer um número ou valor lógico, como Verdadeiro ouFalso.
• Inserir ou editar uma fórmula de matriz e, em seguida, pressionar Enter.
• Inserir uma referência de célula, uma fórmula ou uma função como uma constante de matriz.
• Fornecer um intervalo a um operador ou a uma função que requer um valor único, não um intervalo.
• Usar uma matriz que não é válida em uma das funções de matriz da planilha.
• Executar uma macro que insere uma função que retorna #VALOR!
#DIV/0! - Número é dividido por zero (0).
#NOME? - O Excel não reconhece o texto em uma fórmula.
#N/D - Um valor não está disponível para uma função ou fórmula.
#REF! - Uma referência de célula não é válida.
#NÚM! - Valores numéricos inválidos em uma fórmula ou função.
#NULO! - A interseção de duas áreas não se interceptam. O operador de interseção é um espaço 
entre referências.
Consolidar dados
O comando para consolidar dados permite criar uma nova planilha resultante de outras planilhas 
já existentes.
Para utilizar o comando Consolidar, acesse a guia Dados e no grupo Ferramentas de Dados, clique 
em Consolidar.
As planilhas que serão utilizadas no processo de consolidação devem ser iguais, isto é, com os mes-
mos dados (layout e rótulos).
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
6
Janela de configuração do comando Consolidar
Confira as funções presentes na janela de configuração do comando Consolidar:
• Função: escolha da função que será utilizada na conso-
lidação dos dados.
• Referência: seleção da planilha e das células que irão 
compor a planilha consolidada.
• Procurar: busca de arquivo externo.
• Adicionar: após cada seleção no campo referência, de-
ve-se clicar no botão Adicionar.
• Excluir: retira determinada referência.
• Linha superior: mantém os itens da primeira linha da seleção.
• Coluna esquerda: mantém os itens da coluna esquerda da seleção.
• Criar vínculos com dados de origem: em caso de alteração nas planilhas de origem, ocorrerá 
atualização na planilha consolidada.
Criar tabela de dados
O comando Tabela de Dados é uma ferramenta de análise hipotética que possibilita obter resultados 
a partir da alteração de valores em células pré-definidas.
Para acessar o comando Tabela de Dados, na guia Dados, no grupo Ferramentas de Dados e clique 
em Teste de Hipóteses e selecione o comando Tabela de dados.
Uma das vantagens de se utilizar as tabelas de dados é que elas permitem calcular várias ver-
sões de resultados em uma única operação, permitindo compará-los em todas as variações. 
Um exemplo de uso do comando é variar a taxa de juros em uma tabela de amortização para 
determinar o valor dos pagamentos.
Atingir meta
O comando Atingir meta realiza cálculos para alcançar uma determinada meta. O resultado é obtido 
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
7
alterando-se o valor de uma célula para que uma fórmula atinja determinado valor.
O comando está na guia Dados, grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em 
seguida, no ícone 6 para acessar o Atingir meta.
Confira uma planilha que apresenta uma lista de itens 
de despesas e respectivos valores. Você e um grupo de 
amigos estão organizando uma viagem e precisam to-
mar uma decisão: quantas pessoas devem participar da 
viagem sendo que há 10 candidatos interessados, mas há 
apenas R$2.000,00 de verba para realizá-la.
O problema é que as despesas calculadas resultaram em 
R$2.600,00. Nesse caso, o comando Atingir meta pode 
ser usado para verificar quantas pessoas poderão viajar.
Adicionar cenários
O Excel possui um Gerenciador de Cenários utilizado para simular possibilidades de resultados a partir 
de valores pré-determinados em uma planilha. A partir dos novos resultados, é possível gerar relató-
rios que demonstram os novos valores.
Confira o acesso: na guia Dados, grupo Ferramentas de Dados, clique em Teste de Hipóteses e, de-
pois em Gerenciador de Cenários...
Habilitar suplementos
Suplementos são programas que adicionam comandos ou 
funcionalidades personalizadas ao Microsoft Office. Nor-
malmente, eles ficam armazenados na pasta Biblioteca ou 
na pasta Suplementos, ou ainda em uma das respectivas 
subpastas da pasta Microsoft Office\Office.
Se determinado suplemento não for encontrado na cai-
xa de diálogo Suplementos, ele poderá ser instalado a 
partir do website da Microsoft Office. Se você trabalha 
em uma empresa com administrador de rede, ele pode 
ter designado outros locais para armazenar os suple-
mentos. Assim, consulte o administrador para obter 
mais informações.
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
8
Funcionalidades dos suplementos
Conheça as funcionalidades dos suplementos disponíveis:
Ferramentas de Análise
Adiciona ferramentas e funções de análise financeira, estatística e de engenharia.
Ferramentas de Análise – VBA
Permite que desenvolvedores publiquem funções e ferramentas de análise de finanças, estatística e 
engenharia.
Ferramentas para o Euro
Formata valores, como euros, e fornece a função de planilha EUROCONVERT para converter moedas.
Solver
Calcula soluções para cenários hipotéticos, baseados em células ajustáveis e com limitações.
Aplicar o Solver
O recurso Solver é utilizado para resolver problemas 
com uma ou mais variáveis, além disso, permite res-
tringir valores determinados.
Por exemplo, confira uma planilha de controle de 
notas e status de avaliação dos servidores, em que a 
média deve ser igual a 95 e o desvio padrão deve ser 
superior a 5.
Nesse caso, o comando Solver poderá ser utilizado 
para alcançar esses critérios.
Inserir o Solver
Como o botão Solver não foi incluído na faixa de opções, é preciso ativá-lo na Barra de Ferramentas 
de Acesso Rápido.
Confira como inserir o comando Solver:
Passo 1.
No Excel, clique na guia Arquivo e, depois, em Opções para abrir a janela de configuração.
Passo 2.
Na janela, clique em Suplementos e no item Gerenciar, selecione Suplementos do Excel.
Passo 3.
Clique em Ir para aparecer a janela Suplementos.
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
9
Passo 4.
Na janela Suplementos, no item Suplementos disponíveis, clique na opção Solver. Depois, clique em OK.
Passo 5.
Após isso, a opção Solver será incluída na guia Dados, grupo Análise. O comando permanecerá na 
faixa de opções do Excel enquanto não for desmarcado na janela Suplementos.
Parâmetros do Solver
No botão Opções do comando Solver, existem outros parâmetros que podem ser usados conforme a 
necessidade do cálculo.
Após clicar no botão Opções, é mostrada a seguinte janela:
• Precisão da Restrição: Varia de 0 a 1 e limita a margem 
de erro.
• Tempo Máx.: O limite de tempo para resolver em segundos.
• Iterações: Número de vezes que os valores são alterados. 
Também é um limitador de tempo.
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
10
Criar tabela dinâmica
A Tabela Dinâmica é um tipo especial de tabela utilizada para resumir dados de determinados cam-
pos de uma lista. Após utilizar o comando, os dados podem ser reorganizados de diferentes modos 
facilitando a análise das informações.
Para se criar uma Tabela Dinâmica, acesse o comando disponível na guia Inserir, grupo Tabelas.
Criar gráfico dinâmico
Um gráfico dinâmico, assim como um gráfico padrão, mostra a série de dados, as categorias e os eixos 
de gráfico, mas com a diferença de que há controles de filtragem interativos para que os dados pos-
sam ser resumidos e explorados rapidamente.
Criar formulário de dados
Um Formulário de dados é um recurso prático usado para facilitar a manipulação de dados, envol-
vendo ações como: inclusão, exclusão, alteração e consulta em banco de dados, seguindo critérios 
definidos pelo usuário.
Confira como inserir o botão Formulário na faixa de opções
Passo 1.
No Excel, clique na guia Arquivo e, depois, em Opções para abrir a janela de configuração.
Passo 2.
Na janela, clique em Barra de Ferramentas de Acesso Rápido e no item Escolher comandos 
em: selecione Todos osComandos.
Passo 3.
Na caixa de listagem, selecione o botão Formulário. Clique em Adicionar e, depois, em OK. 
Assim, o botão Formulário aparecerá na Barra de Ferramentas de Acesso Rápido, ao lado do 
botão Refazer.
Senac 2018. Todos os direitos reservados.
Excel Recursos Avançados
11
Inserir Subtotal
O comando Subtotal serve para calcular automatica-
mente os subtotais e totais de um agrupamento de 
dados relacionados.
Por exemplo, em uma planilha de folha de paga-
mento, utilizamos o Subtotal para incluir subtotais 
em cada função na coluna Salário.
Importante!
Para utilizar o Subtotal é necessário classificar a coluna 
que servirá de critério.
Nesta unidade você estudou:
• Formatação, validação e consolidação de dados
Na próxima unidade você estudará sobre as funções avançadas. Até lá!