EX2-IE_Curso Completo@2014.02.04

Prévia do material em texto

CONFIDENCIAL E EXCLUSIVO 
É proibido usar este material sem autorização expressa da Excel Experts – Ex2 
Excel Experts 
Inteligência em Planilhas 
 
contato@exdois.com.br 
www.facebook.com/exdois 
www.exdois.com.br 
Adriano Carezzato 
adriano.carezzato@exdois.com.br 
 
Felipe Garcia 
felipe.garcia@exdois.com.br 
Excel Experts Curso - Intermediário 
... 
Excel Experts 2 
Disclaimer 
Este material foi utilizado como apoio gráfico a uma apresentação oral e, portanto, 
não representa registro completo do que foi abordado na referida apresentação. 
  
O emprego de todas logomarcas  possui caráter estritamente ilustrativo, não 
indicando parceria, promoção ou qualquer vínculo entre as organizações citadas e 
a Ex2. 
  
Nenhuma das partes deste documento pode ser veiculada, transcrita ou 
reproduzida sob qualquer forma ou por quaisquer meios — eletrônico, fotocópia, 
gravação ou outros —  sem prévio consentimento por escrito da Ex2. 
 
 
Filipe Póssa Ferreira 
11 9 7579 9564 
filipe.possa@gmail.com 
 
 
 
Felipe Garcia 
11 99191 9414 
felipe.garcia@exdois.com.br 
 
 
 
contato@exdois.com.br 
www.facebook.com/exdois 
www.exdois.com.br 
E x c e l E x p e r t s | I n t e l i g ê n c i a e m P l a n i l h a s 
Instrutores do 
Curso 
Excel Experts 5 
Pré-Requisitos 
Intermediário 
Excel Experts 
Estrutura do Curso 
Conhecimento básico do Excel e do Windows 
Excel 2007 ou superior (para Windows, independente da língua) 
Ter curiosidade, interagir e “botar a mão na massa” 
Demonstrar a utilização das 
fórmulas e funções mais úteis do 
Excel 
Apresentar as principais 
ferramentas utilizadas para 
destrinchar grandes volumes de 
dados 
Utilizar formatação avançada de 
dados e de exibição da planilha 
Montar uma caixa de ferramentas 
completa de conhecimento em 
funções e fórmulas 
Obter informações relevantes para 
tomada de decisões 
Criar planilhas mais profissionais e 
intuitivas, fáceis de se navegar e 
utilizar 
Conteúdo Motivos 
Excel Experts 6 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 7 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada? 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 8 
=SE(teste_lógico1;valor_se_verdadeiro1;SE(teste_lógico2;valor_se_verdadeiro2;SE(teste_lógico3
;valor_se_verdadeiro3; valor_se_falso3))) 
Funções lógicas 
SE e SEs aninhados 
Condicional 
aninhado 
=SE(teste_lógico;valor_se_verdadeiro;valor_se_falso) Condicional 
simples teste_lógico 
valor_se_verdadeiro 
valor_se_falso 
teste_lógico1 
valor_se_verdadeiro1 
teste_lógico2 
valor_se_verdadeiro2 
teste_lógico3 
valor_se_verdadeiro3 
valor_se_falso3 
Lembre-se que as funções lógicas são usualmente utilizadas em conjunto com funções de informação e/ou 
expressões envolvendo operadores de comparação. 
Por exemplo: SE(A1>10;”Valor alto!”;”Valor aceitável.”) 
Excel Experts 9 
=E(lógico1;lógico2;...;lógicoN) 
=OU(lógico1;lógico2;...;lógicoN) 
=NÃO(lógico) 
Funções lógicas 
E, OU e NÃO 
Lógicos 
=E(VERDADEIRO;VERDADEIRO;VERDADEIRO) 
Fórmula Resultado 
VERDADEIRO 
=OU(VERDADEIRO;FALSO;FALSO) 
=NÃO(FALSO) VERDADEIRO 
VERDADEIRO 
Lembre-se que as funções lógicas são usualmente utilizadas em conjunto com funções de informação e/ou 
expressões envolvendo operadores de comparação. 
Utiliza-se a função NÃO somente em alguns casos para facilitar a compreensão da fórmula. 
Excel Experts 10 
=CONT.VALORES(intervalo) 
=SE(teste lógico;verdadeiro;falso) 
=E(teste 1; teste 2;...;testeN) 
=OU(teste 1; teste 2;...;testeN) 
=CONTAR.VAZIO(intervalo) 
=CONT.NÚM(intervalo) 
Funções lógicas e de contagem 
Estrutura 
Condicional 
simples 
Operadores 
lógicos 
Contagem de 
valores 
Contagem de 
vazios 
Contagem de 
números 
1 
2 
3 
4 
5 
Aplicação 
1 e 2 
3 
4 
5 
Operadores de Comparação 
Operadores de comparação são utilizados nas fórmulas para 
comparar dois valores ou contas distintas e resultam no valor 
lógico VERDADEIRO ou FALSO. Ex: 
 
•  Igual = 
•  Maior que > 
•  Menor que < 
•  Maior ou igual a >= 
•  Menor ou igual a <= 
•  Diferente de <> 
Calcula número de células não vazias 
Conta o número de células vazias do intervalo 
Conta o número de células que contém números 
Excel Experts 11 
=SOMASE(intervalo;teste;[intervalo de soma]) 
 
=SOMASES([intervalo soma]; 
intervalo1; critério1; intervalo2; critério2;...) 
=MÉDIASE(intervalo;teste;intervalo de média) 
 
=MÉDIASES(intervalo_média; 
intervalo1; critério1; intervalo2;critério2;...) 
Funções condicionais 
Soma 
condicional 
Média 
condicional 
1 
2 
1 
3 
Estrutura 
=CONT.SE(intervalo;critério) 
 
=CONT.SES(intervalo_critério1;critério1; 
intervalo_critério2;critério2;...) 
Contador 
condicional 
3 
Calcula o número de células não vazias em um intervalo que corresponde a 
uma determinada condição 
Exemplo Aplicado 
2 
Fórmulas condicionais são particularmente úteis 
para contadores, resumos, subtotais, conferências 
rápidas. 
Caso queira usar um critério com operadores 
lógicos (>,>=, <,<=,<>), coloque a expressão do 
critério como texto entre aspas. Ex: 
=CONT.SE(A1:A10;”>3”) 
 
Planilha Ex2-IE-Exercícios > Exemplo-Funções 
Básicas 
 
Excel Experts 12 
=SOMARPRODUTO(Matriz1; Matriz2; ...) 
Função SOMARPRODUTO() 
SOMAR 
PRODUTO 
1 
Estrutura 
Retorna a soma dos produtos de intervalos ou matrizes correspondentes 
Exemplo Aplicado 
Todas as matrizes devem ter as mesmas dimensões. 
Trata as entradas não-numéricas da matriz como se 
fossem zeros 
Exercício-SOMARPRODUTO 
 
1 
Excel Experts 13 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada? 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 14 
Funções de pesquisa e referência de dados 
As funções de busca de informações ajudam a cruzar informações entre bases de dados. 
=PROCV(item procurado;matriz;índice;opção) 
=PROCH(item procurado;matriz;índice;opção) 
Busca 
vertical 
Busca 
horizontal 
1 n 
índice 
Matriz (ex. A2:C10) 
Coluna de busca 
(esquerda) 
1 
n 
índice 
Matriz (ex. A2:C10) Linha de busca 
(acima) 
Matriz (ex. A2:C10) 
Tipo de busca: Falso – 
exato, Verdadeiro - 
aproximado 
Excel Experts 15 
Funções de pesquisa e referência de dados 
As funções de busca de informações ajudam a cruzar informações entre bases de dados. 
=PROCV(valor_procurado;matriz_tabela;índice;opção) Busca 
vertical 
1 3 
índice Índice: 
1 = Nome 
2 = Nota 
3 = NUSP 
Matriz 
Coluna de busca 
(1ª esquerda) 
Valor_Procurado 
Tipo de busca: Falso – 
exato, Verdadeiro -aproximado 
Excel Experts 16 
Funções de pesquisa e referência de dados 
As funções de busca de informações ajudam a cruzar informações entre bases de dados. 
=PROCH(valor_procurado;matriz_tabela;índice;opção) Busca 
Horizontal 
Índice: 
1 = Dado1 
2 = Dado2 
3 = Dado3 
Matriz 
Linha de busca (1ª) 
Valor_Procurado 
Tipo de busca: Falso – 
exato, Verdadeiro - 
aproximado 
1 
n 
índice 
Excel Experts 17 
Tratamento de erros 
=ÉERROS(referência) 
=SEERRO(referência;verdadeiro) 
Identificando 
erro 
Tratamento 
de erro 
Tratamento de erros 
Exemplos de 
erros 
•  #DIV/0!: Divisão por zero 
•  #N/D: Dado não encontrado 
•  #REF!: Referência inválida 
•  #NOME?: Função não identificada 
•  #VALOR!: Operação com dados incorretos 
Deve-se tratar os erros pois eles 
impossibilitam operações quando 
estão em intervalos utilizados por 
outras operações 
Excel Experts 18 
=ORDEM(Posição; Matriz; [Ordem]) 
 
=MAIOR(Matriz; k) 
 
 
=MENOR(Matriz; k) 
 
 
=ORDEM.PORCENTUAL(Matriz; X; Significância) 
 
Funções MAIOR, MENOR, ORDEM e ORDEM.PORCENTUAL 
Estrutura 
Maior 
Menor 
Ordem 
Ordem 
Porcentual 
1 
2 
3 
4 
Exemplo 
Retorna a posição de um número em uma lista de números: 
o seu tamanho em relação a outros valores da lista 
Retorna a ordem percentual de um valor em um conjunto de 
dados como um percentual do conjunto de dados. 
Retorna o k-ésimo maior valor de um conjunto de dados 
Retorna o k-ésimo menor valor de um conjunto de dados 
1 
2 
3 4 
Use as funções de ORDEM para discriminar um valor numérico à posição de um número em relação a uma lista de 
valores. Cuidado com valores repetidos, que podem ocupar a mesma posição. 
Excel Experts 19 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada? 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 20 
Funções de Data e Hora 
Estrutura Estrutura 
=AGORA() 
 
 Agora 
Retorna a data e hora atuais, formatada como Data 
e Hora 
As funções AGORA() e HOJE() não possuem argumentos e são voláteis, ou seja, a cada atualização retornam um valor. 
No Excel para Windows, 1/1/1900 é o número de série 1 e 1/1/2008 é o número de série 39448 porque está a 
39.448 dias após o 1/1/1900. O Excel armazena horas como frações decimais, pois a hora é considerada parte 
de um dia. 
=HORA(núm_série) 
 
Hora 
Retorna a hora como um número inteiro de 0 
(12:00 AM) a 23 (11:00 PM) 
=MINUTO(núm_série) 
 
Minuto 
Retorna o minuto, um número inteiro entre 0 e 59 
=SEGUNDO(núm_série) 
 
Segundo 
Retorna o segundo, um número inteiro entre 0 e 59 
=HOJE() 
 
 Hoje 
Retorna a data de hoje formatada como Data 
=DIA(núm_série) 
 
Dia 
Retorna o dia do mês, um número inteiro de 1 a 31 
=MÊS(núm_série) 
 
Mês 
Retorna o mês, um número inteiro de 1(Janeiro) a 
31(Dezembro) 
=ANO(núm_série) 
 
Ano 
Retorna o ano de uma data, um número inteiro de 
1900 a 9999 
Excel Experts 21 
Funções de Data e Hora 
Estrutura 
=DATA(ano; mês; dia) 
 
 Data 
Retorna o número que representa a Data no código 
data-hora do Microsoft Office Excel 
A função DATA() é especialmente útil para criar datas em função de outras variáveis. 
 
A função DIA.DA.SEMANA() é útil para resolver problemas que é necessário criar uma função que interaja com um dia 
específico da semana. Ex: Quantos Sábados temos em determinado período? Liste os dados que acontecem às terças. 
=DIA.DA.SEMANA(núm_série;[retornar_tipo]) 
 Dia da 
Semana Retorna um número entre 1 e 7 identificando o dia 
da semana 
Excel Experts 22 
Funções de Data e Hora 
Estrutura 
=DIATRABALHO(data_inicial; dias; [feriados]) 
 
 Dia Trabalho 
Retorna o número de série de data antes ou depois 
de um número especificado de dias úteis 
Utilize DIATRABALHO() para avançar ou regredir um número determinado de dias úteis a partir de uma data_inicial; 
DIATRABALHOTOTAL() contagem de dias úteis dentro do intervalo, incluindo a data_inicial e data_final. 
O Terceiro argumento da função - [feriados] - exclui a contagem do dia caso o feriado ocorra em um dia útil. 
=DIATRABALHOTOTAL(data_inicial; data_final; 
[feriados]) 
 
Dia Trabalho 
Total 
Retorna o número de dias úteis entre duas datas 
Excel Experts 23 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada? 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 24 
Validação de Dados 
Pode-se usar “Circular Dados Inválidos” para identificar entrada de dados que não atendam as condições propostas. 
DICA: Use validação de dados para buscas com PROCV()/PROCH() ou funções que precisam de entradas exatas. 
Limitação de entrada de dados para Números Inteiros, DATA e HORA, também são bem comuns para limitar inputs em 
planilhas e simuladores. 
A validação de dados pode ser usada pra limitar as entradas de dados em células de determinado intervalo. 
Os usos mais comuns de validação de dados são: limitar listas em menus Dropdown, limitar entrada de dados para números ou 
textos apenas, entre outras aplicações. 
1 
2 
3 
1 Faixa de Opções > Dados 
2 Ferramentas de Dados > Validação de Dados 
3 Escolha o Critério de Validação 
Com a(s) célula(s) selecionada(s), clique em: 
Excel Experts 25 
Validação de Dados 
1 
2 
3 
1 Selecione o Critério de Validação (Lista) 
2 Selecione Menu suspenso na célula 
3 Selecione a fonte de referência 
No menu de Validação de Dados: 
Lista em Menu Dropdown 
Mensagem de 
Entrada 
Alerta de Erro 
Excel Experts 26 
Gerenciamento de Nomes 
O uso de nomes para uma planilha pode ser útil quando se está trabalhando com Listas para Validação de Dados, 
Premissas fixas, etc – de maneira que facilite a referência a uma determinada constante, lista ou tabela. 
Um nome é uma abreviação significativa que facilita o entendimento da finalidade de uma referência de célula, constante, 
fórmula, lista ou tabela. Os nomes podem ser aplicáveis à Pasta de Trabalho inteira, ou a uma planilha específica – para isso 
define-se o “Escopo” do nome criado. 
Excel Experts 27 
Remover Duplicatas 
A ferramenta “Remover Duplicatas” apaga os valores repetidos. Ela é particularmente útil para realizar tabelas 
estatísticas de resumo e subtotais. Por isso recomenda-se copiar os valores em outro local, que terão seus valores 
repetidos removidos. 
Caso deseje apenas realizar uma consulta, use o Filtro. 
 
Um valor duplicado é aquele em que todos os valores da linha são um equivalente exato de todos os valores em outra linha. 
Valores duplicados são determinados pelo valor exibido na célula e não necessariamente pelo valor armazenado na célula. Por 
exemplo, se você tem o mesmo valor de data em células diferentes, um formatado como “8/3/2013” e outro como “8/Mar/13”, 
os valores são únicos. 
1 
2 
3 
3 
Excel Experts 28 
Texto para Colunas 
 
Esta ferramenta é muito usada quando há cópia de textos externos, ou arquivos CSV (Comma-Separated Values) 
Use a ferramenta Texto para Colunas para separar conteúdo de célula simples, como nomes e sobrenomes, em colunas 
diferentes. 
ExcelExperts 29 
Texto para Colunas 
Use a ferramenta Texto para Colunas para separar conteúdo de célula simples, como nomes e sobrenomes, em colunas 
diferentes. 
Caracteres como vírgulas ou tabulações separam cada campo 
 
 Delimitado 
Use Texto para Colunas Delimitado quando o que separa os campos é um caractere específico 
Escolha o tipo de Delimitador 
(Tabulação, Ponto e Vírgula, Vírgula, Espaço, Outros) 
Escolha o destino da primeira referência com o Texto 
separado em Colunas 
Escolha a formatação das colunas 
Excel Experts 30 
Texto para Colunas 
Use a ferramenta Texto para Colunas para separar conteúdo de célula simples, como nomes e sobrenomes, em colunas 
diferentes. 
Campos são alinhados em colunas e com espaços entre cada campo 
 
 Largura Fixa 
Use Texto para Colunas Largura Fixa quando o que será separado possui um número fixo de caracteres ou alinhamento específico 
Para converter Texto em Colunas quando não há 
caractere que separe os valores, use Largura Fixa. 
Ex: separar último dígito das placas de carro 
Escolha como deseja que seja feita a separação de Texto 
para Colunas inserindo linhas de quebra entre os 
caracteres 
Excel Experts 31 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 32 
Classificar Avançado 
Ao manipular uma base de dados grande, o Excel disponibiliza funções simples para tornar a busca de dados mais rápida e 
intuitiva. A classificação é útil para colocar uma lista em ordem alfabética, compilar lista por valores do mais alto para o mais 
baixo, ou até organizar linhas por cores ou ícones. 
As opções de classificar 
ordenam os dados a partir 
de uma determinada 
variável da base de dados. 
Pode-se classificar por 
valores e cores 
DICAS: 1) Antes de classificar, certifique-se que a ordem inicial não é importante. Caso contrário, crie um 
contador auxiliar para poder reordenar seus dados. 
2) Ao classificar e filtrar certifique-se que está selecionando toda a tabela, cuidado com os dados ocultos e 
tabelas ou dados adjacentes. 
Adicione vários níveis de 
classificação e selecione 
como e que ordem deverá 
ser classificada a lista 
Excel Experts 33 
Classificar Avançado 
Evite classificar o banco de dados original. Use a ferramenta de Filtro para consultas específicas, ou trabalhe com uma 
cópia de seu banco de dados em uma planilha à parte. 
Saiba mais sobre problemas gerais de classificação. 
Verifique se os valores retornados pelas fórmulas foram 
alterados. Se os dados classificados contiverem uma ou 
mais fórmulas, os valores retornados dessas fórmulas 
poderão ser alterados. 
 
Fórmulas 
As linhas e colunas ocultas não serão movidas quando a 
tabela for classificada. Isso pode embaralhar os dados. 
 
Linhas/
Colunas 
Ocultas 
Insira títulos de colunas em apenas uma linha. Se desejar 
rótulos de várias linhas, quebre a linha dentro da célula 
(Alt-Enter). Certifique-se que os títulos não estão inclusos 
na classificação (“Meus dados têm cabeçalhos”) 
 
Títulos 
Certifique-se que toda sua tabela está selecionada antes da 
classificação. Classificar colunas específicas pode 
embaralhar seus dados. 
 
Seleção da 
Tabela 
Excel Experts 34 
Auto-Filtro 
O uso do AutoFiltro para filtrar dados é um modo rápido e fácil de localizar e trabalhar com um subconjunto de dados em um 
intervalo de células ou coluna de tabela. Os dados filtrados exibem somente as linhas que atendem aos critérios especificados e 
oculta as linhas que não deseja exibir. Depois que filtrar os dados, é possível copiar, localizar, editar, formatar, criar gráfico e 
imprimir o subconjunto de dados filtrados sem reorganizá-los nem movê-los. 
DICA: Não misture formatos de armazenamento na mesma coluna (ex. Texto, Data e Número) – somente um 
tipo de comando de filtro fica disponível para cada coluna. 
Selecione a tabela de dados 
que deseja aplicar o 
AutoFiltro. A primeira linha 
será o cabeçalho e não será 
filtrada. 
A seta no cabeçalho indica 
que os dados abaixo serão 
filtrados. Clique na seta 
para observar os critérios 
de filtro. Filtre itens específicos, 
Texto, Cor, Números, 
Datas e Horas. 
Você pode classificar os 
itens também (observe 
os problemas gerais de 
classificação) 
Para limpar os critérios de 
Filtro use o “Limpar Filtro” 
Excel Experts 35 
Proteção de Planilhas e Arquivos 
CUIDADO: Sem a senha correta não é possível acessar o arquivo! Guarde sempre uma cópia de segurança em outro 
arquivo. 
O Excel permite diversas camadas de segurança e proteção que permite controlar quem pode acessar e alterar os dados do 
arquivo. 
Pode-se proteger todo o arquivo da pasta de trabalho com uma senha, o que permite limitar os usuários que 
podem exibir ou modificar os dados. 
Proteger 
Pasta de 
Trabalho 
1 Selecione Salvar Como... 
2 Selecione Ferramentas > Opções Gerais... 
3 Determine uma senha para Proteção e Gravação 
Para proteger a pasta de trabalho com senha: 
1 
2 
3 
Ao abrir o arquivo, o usuário 
terá que digitar uma senha para 
visualizar e outra para editar 
Excel Experts 36 
Proteção de Planilhas e Arquivos 
CUIDADO: Sem a senha correta não é possível acessar o arquivo! Guarde sempre uma cópia de segurança em outro 
arquivo. 
O Excel permite diversas camadas de segurança e proteção que permite controlar quem pode acessar e alterar os dados do 
arquivo. 
Ao proteger os elementos da planilha, todas as células são bloqueadas por padrão e os usuários não podem 
alterá-las. Por exemplo, eles não podem inserir, modificar, excluir ou formatar dados em uma célula 
bloqueada. Entretanto, pode-se especificar quais elementos os usuários poderão alterar quando uma 
planilha é protegida. 
Proteger 
Planilha 
1 Selecione Revisão > Proteger Planilha 
2 Crie uma senha (opcional) e escolha as permissões para a planilha 
3 Observe que a proteção será aplicada em todas as células que estiverem 
bloqueadas 
Para proteger a Planilha ativa: 
1 
2 
3 
Excel Experts 37 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 38 
Estilos 
Os Estilos de célula têm como base o Tema do Documento aplicado a toda a pasta de trabalho. Ao alternar para outro 
tema de documento, os estilos de célula serão atualizados para corresponder ao novo tema de documento. 
Um Estilo é um conjunto de características de formatação, como fontes, tamanhos de fontes, formatos de número, bordas, 
sombreamento, proteção, etc. Estilos rápidos são combinações de diferentes opções de formatação e são exibidos em uma 
miniatura nas diversas galerias de Estilos Rápidos. 
Estilos de Célula 
Estilos de Forma Estilos de WordArt 
Estilos de SmartArt Estilos de Layout 
Excel Experts 39 
Estilos 
DICA: se você clicar em uma célula já formatada antes de criar o Estilo, esse novo estilo já terá a Formatação da célula 
selecionada previamente. 
Crie estilos para padronizar sua planilha. Diferenciar células de INPUT, OUTPUT, CÁLCULO, PREMISSAS e CABEÇALHOS, é muito 
importante para orientar visualmente ousuário da planilha. 
Estilos de Célula 
1 Na guia Início, Grupo Estilo, clique em Estilos de Célula 
2 No menu, clique em Novo Estilo de Célula 
3 Defina um Nome para seu novo Estilo, e configure sua 
Formatação em Formatar... 
Para criar um novo estilo personalizado: 
1 
2 
3 
Excel Experts 40 
Formatação Condicional 
A Formatação Condicional ajuda a explorar visualmente e a analisar dados, detectar problemas críticos e identificar 
padrões e tendências. Assim, fica mais fácil destacar células ou intervalos, enfatizar valores não-usuais e visualizar 
dados usando barras de dados, escalas de cores e conjuntos de ícones. 
A Formatação Condicional altera a aparência de um intervalo de células com base em uma condição (ou critério). Se a condição 
for verdadeira, o intervalo de células será formatado com base nessa condição; se a condição for falsa, o intervalo de células 
não será formatado com base nessa condição. 
Regras de comparação 
absoluta 
Regras de comparação 
relativa 
Barras comparativas de 
valores 
Valores representados 
em Escalas de Cor 
Cada ícone representa 
um intervalo de valor 
Crie, Limpe e Gerencie 
as regras do intervalo 
selecionado 
Condição / 
Critério 
Formatação caso 
Verdadeira 
Mais 
Formatações 
Crie Novas 
Regras 
Excel Experts 41 
Formatação Condicional 
A Formatação Condicional ajuda a explorar visualmente e a analisar dados, detectar problemas críticos e identificar 
padrões e tendências. Assim, fica mais fácil destacar células ou intervalos, enfatizar valores não-usuais e visualizar 
dados usando barras de dados, escalas de cores e conjuntos de ícones. 
Pode-se aplicar várias Regras de Formatação Condicional em determinado intervalo. Visualize, edite, ordene e apague as regras 
na opção “Gerenciar Regras...” 
Alterar Critérios Intervalo de Aplicação Condições Limite 
Alterar ordem 
de prioridade 
Formatos 
aplicados 
Formatação 
Condicional 
Aplicada 
Excel Experts 42 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 43 
Tabela Dinâmica 
Use a tabela dinâmica para organizar seus dados e gerar rapidamente resumos e consultas. 
Observe que a tabela dinâmica é referenciada a uma Tabela – caso a tabela tenha valores alterados, clique em 
“Atualizar Tudo”, na guia de Dados, para recalcular a tabela dinâmica. 
A Tabela Dinâmica é uma das ferramentas de análise mais poderosas do Excel e deve ser usada para resumir, analisar, explorar 
e apresentar dados. Com ela é possível cruzar dados, identificar tendências e organizar uma grande quantidade de dados pois 
sua disposição personalizável automaticamente calcula subtotais e categoriza itens. 
Tabela simples – 
dados de origem 
Tabelas 
Dinâmicas 
#Pessoas / Ativ. Física / Sexo #Pessoas / Sexo / Idade #Pessoas / Sexo / Idade vs. Fumante 
1) Quantos homens e quantas mulheres participaram da pesquisa? 
2) Quem faz atividade física fuma menos? 
3) Quais são os hábitos dominantes da faixa etária 18-27 anos? 
4) O Experimento da Droga X teve melhoria significativa dos 
Resultados do exame? Para qual faixa etária? 
Excel Experts 44 
Tabela Dinâmica 
DICA: Teste várias ordens de agrupamento e cruzamento, para melhorar a visualização. Normalmente o agrupamento 
vertical é melhor para categorias que possuem muitos subitens. 
Como criar uma Tabela Dinâmica 
Layout Padrão 
1 
2 
Na guia Inserir, Grupo Tabelas, clique em Tabela Dinâmica 
3 
Para criar uma tabela dinâmica, selecione a tabela que deseja 
resumir (inclusive cabeçalhos): 
Arraste os campos para a posição desejada 
Configure os campos de acordo com o objetivo da tabela 
(Agrupamento, contagem, soma, subtotais, totais, etc) 
1 
2 
3 
Lista de Campos 
3 
Configurações do Campo de Valor 
Excel Experts 45 
Tabela Dinâmica 
DICA: Use Formatação Condicional para visualizar mais facilmente tendências e comportamentos dos dados 
analisados. Para isso, oculte os Subtotais para não prejudicar a escala de cores da formatação condicional. 
Use “Layout Clássico” (Opções da Tabela Dinâmica>Exibição) para copiar/colar valores da tabela dinâmica. 
Desabilite o InfoDadosTabelaDinâmica para realizar referências relativas na tabela. 
Configuração e Formatação da Tabela Dinâmica é importante para facilitar a visualização e interpretação dos dados 
Exibe/Oculta a 
Lista de Campos 
Mais Opções da 
Tabela Dinâmica Fonte de Dados 
Atualize a tabela quando 
houver alteração na fonte 
de dados 
Agrupe dados 
muito dispersos 
Use “Fórmulas” para inserir 
um “Campo Calculado” 
Configure os Estilos da 
Tabela Dinâmica 
Ocultar/Exibir Subtotais e 
Totais 
Habiiltar / Desabilitar 
InfoDadosTabelaDinâmica 
Excel Experts 46 
Tabela Dinâmica 
DICA: Use Formatação Condicional para visualizar mais facilmente tendências e comportamentos dos dados 
analisados. Para isso, oculte os Subtotais para não prejudicar a escala de cores da formatação condicional. 
Use “Layout Clássico” (Opções da Tabela Dinâmica>Exibição) para copiar/colar valores da tabela dinâmica. 
Desabilite o InfoDadosTabelaDinâmica para realizar referências relativas na tabela. 
Configuração e Formatação da Tabela Dinâmica é importante para facilitar a visualização e interpretação dos dados 
Grande 
quantidade de 
Dados 
Resultado por 
Experimento 
A Droga X teve maior 
resultado incremental 
no público Masc 
1)  Para qual faixa de resultados, a droga apresentou melhor 
incremento dos Resultados? 
2)  Para qual sexo a droga é recomendada? 
Excel Experts 47 
InfoDadosTabelaDinâmica (GetPivotData) 
Usa-se a função InfoDadosTabelaDinâmica() quando outra tabela usará os dados resumidos na Tabela Dinâmica como 
fonte de dados para os cálculos. 
A Tabela Dinâmica indexa os campos da tabela comum exibindo resumos de somas, contagens, médias, etc (Campo Valores). 
Pode-se usar a função InfoDadosTabelaDinâmica() para buscar dados específicos a partir de referências absolutas e coordenadas 
da tabela dinâmica. 
Valor Referenciado 
(Masc/Sim/Droga X) 
Campo 
Calculado 
Posição da 
Tabela Dinâmica 
Campo e Item 
do Campo 
Os Campos são constantes 
(característica da Tabela 
Dinâmica criada) 
Os Itens buscados 
são referenciados 
(variáveis) 
Excel Experts 48 
Agenda 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
Excel Experts 49 
Gráfico Dinâmico 
O Gráfico Dinâmico é interativo e permite fácil alteração de sua representação através dos campos da Tabela Dinâmica 
associada. Como ele gera os gráficos a partir da comparação de valores das categorias, pode-se alterar o tipo de gráfico 
sem a necessidade de alterar a tabela de dados de origem, como acontece com os gráficos convencionais. 
O relatório de Gráfico Dinâmico oferece uma representação gráfica dos dados em uma Tabela Dinâmica. Assim como a Tabela 
Dinâmica, o Gráfico Dinâmico serve para visualizar facilmente as comparações, padrões e tendências. É muito utilizado em 
relatórios e apresentações por ilustrar as dimensões das análises. 
1)  Para qual faixade resultados, a droga apresentou melhor 
incremento dos Resultados? 
2)  Para qual sexo a droga é recomendada? 
Tabela Dinâmica + Formatação Condicional Gráfico Dinâmico 
Excel Experts 50 
1)  Selecione a Tabela 
2)  Inserir > Tabela Dinâmica > Gráfico Dinâmico 
3)  Escolha o Local (da Tabela e Gráfico Dinâmico) 
4)  Arraste os campos desejados para as áreas de 
Filtro, Linhas, Colunas e Valores 
 
 
A partir 
de uma 
Tabela 
Simples 
1)  Selecione a Tabela Dinâmica 
2)  Tabela Dinâmica > Opções > Gráfico Dinâmico 
3)  Selecione o tipo de gráfico 
4)  Arraste os campos desejados para as áreas de 
Filtro, Linhas, Colunas e Valores 
 
A partir 
de uma 
Tabela 
Dinâmica 
Gráfico Dinâmico 
O Gráfico Dinâmico é interativo e permite fácil alteração de sua representação através dos campos da Tabela Dinâmica 
associada. Como ele gera os gráficos a partir da comparação de valores das categorias, pode-se alterar o tipo de gráfico 
sem a necessidade de alterar a tabela de dados de origem, como acontece com os gráficos convencionais. 
Como criar um Gráfico Dinâmico 
Excel Experts 51 
Gráfico Dinâmico 
O Gráfico Dinâmico é interativo e permite fácil alteração de sua representação através dos campos da Tabela Dinâmica 
associada. Como ele gera os gráficos a partir da comparação de valores das categorias, pode-se alterar o tipo de gráfico 
sem a necessidade de alterar a tabela de dados de origem, como acontece com os gráficos convencionais. 
Alterando as formas de visualização do Gráfico Dinâmico 
O Gráfico Dinâmico é 
baseado na Tabela 
Dinâmica 
Arraste os Campos a 
serem representados 
no Gráfico 
Aplique filtro nos 
dados para melhorar a 
visualização 
Experimente diferentes 
Agrupamentos e Ordens 
Formate a tabela de 
forma adequada 
Destaque os itens em 
análise 
As séries são os 
Rótulos de Coluna 
O Eixo X são os 
Rótulos de Linha Agrupe os Dados 
muito dispersos 
Excel Experts 52 
Agenda FIM 
Aula 01 - Funções Lógicas e Condicionais 
Aula 02 - Funções de Busca e Tratamento de Erros 
Aula 03 - Funções de Operações com Data e Hora 
Aula 04 - Ferramentas de Validação de Dados e Gerenciador de Nomes 
Aula 05 - Ferramentas de Filtro e Classificação Avançada 
Aula 06 - Formatação Condicional e Estilos Personalizados 
Aula 07 - Tabela Dinâmica 
Aula 08 - Gráfico Dinâmico 
 
Intermediário 
 
Filipe Póssa Ferreira 
11 9 7579 9564 
filipe.possa@gmail.com 
 
 
 
Felipe Garcia 
11 99191 9414 
felipe.garcia@exdois.com.br 
 
 
 
contato@exdois.com.br 
www.facebook.com/exdois 
www.exdois.com.br 
E x c e l E x p e r t s | I n t e l i g ê n c i a e m P l a n i l h a s 
Instrutores do 
Curso 
Mantenha 
Contato 
Dúvidas? 
www.TalentSN.ning.com