Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
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 ... 1 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. Excel Experts ‹#› 2 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 Excel Experts | Inteligência em Planilhas Instrutores do Curso Adriano Carezzato 11 97144 2884 adriano.carezzato@exdois.com.br Felipe Garcia 11 99191 9414 felipe.garcia@exdois.com.br contato@exdois.com.br www.facebook.com/exdois www.exdois.com.br Excel Experts | Inteligência em Planilhas Instrutores do Curso 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 ‹#› 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 ‹#› 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 ‹#› =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 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 ‹#› =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 ‹#› =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 ‹#› =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 ‹#› =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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› =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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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. Excel Experts ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 o usuá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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 ‹#› 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 Para qual faixa de resultados, a droga apresentou melhor incremento dos Resultados? Para qual sexo a droga é recomendada? Excel Experts ‹#› 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 ‹#› 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 ‹#› 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. Para qual faixa de resultados, a droga apresentou melhor incremento dos Resultados? Para qual sexo a droga é recomendada? Tabela Dinâmica + Formatação Condicional Gráfico Dinâmico Excel Experts ‹#› Selecione a Tabela Inserir > Tabela Dinâmica > Gráfico Dinâmico Escolha o Local (da Tabela e Gráfico Dinâmico) Arraste os campos desejados para as áreas de Filtro, Linhas, Colunas e Valores A partir de uma Tabela Simples Selecione a Tabela Dinâmica Tabela Dinâmica > Opções > Gráfico Dinâmico Selecione o tipo de gráfico 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 ‹#› 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 ‹#› 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 Excel Experts ‹#› 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 Excel Experts | Inteligência em Planilhas Instrutores do Curso Mantenha Contato Dúvidas? www.TalentSN.ning.com Excel Experts | Inteligência em Planilhas Mantenha Contato Adriano Carezzato 11 97144 2884 adriano.carezzato@exdois.com.br Felipe Garcia 11 99191 9414 felipe.garcia@exdois.com.br contato@exdois.com.br www.facebook.com/exdois www.exdois.com.br Dúvidas? www.TalentSN.ning.com