Cap 1.2 Excel 2010 Avançado

Prévia do material em texto

Excel 2010 – Avançado 
 
 
 
Senac São Paulo I 
Sumário 
 
 
Capítulo 1 – Funções matemáticas, estatísticas, lógicas e manipulação de datas ........................ 1 
FUNÇÕES MATEMÁTICAS ..................................................................................................... 3 
Atividade 1 – Utilizando funções matemáticas ......................................................................... 3 
 Função SOMA ...................................................................................................................... 3 
 Função PRODUTO ............................................................................................................. 4 
 Função SOMARPRODUTO ................................................................................................ 4 
 Função SOMASE .................................................................................................................. 5 
 Função SOMASES ................................................................................................................ 6 
 
Atividade 2 – Utilizando Funções Estatísticas ........................................................................... 9 
 Função MÁXIMO .............................................................................................................. 10 
 Função MÍN ....................................................................................................................... 11 
 Função MÉDIA................................................................................................................... 11 
 Função ORDEM.EQ .......................................................................................................... 12 
 Função CONT.VALORES .................................................................................................. 12 
 Função CONTAR.VAZIO.................................................................................................. 13 
 Função CONT.NÚM ......................................................................................................... 13 
 Função CONT.SE .............................................................................................................. 14 
 Função CONT.SES ............................................................................................................ 14 
 Função MÉDIASE............................................................................................................... 15 
 Função MÉDIASES ............................................................................................................. 15 
 
Atividade 3 – Utilizando Funções Lógicas ............................................................................... 19 
 Função SE .......................................................................................................................... 19 
 Função E ............................................................................................................................ 20 
 Função OU ........................................................................................................................ 21 
 Função SEERRO ................................................................................................................ 23 
 
Atividade 4 – Manipulando Datas e Horas .............................................................................. 25 
 Formatação de DATAS ...................................................................................................... 25 
 FUNÇÕES DE DATA ....................................................................................................... 29 
 Função HOJE ................................................................................................................ 29 
 Função ANO ................................................................................................................ 29 
 Função MÊS .................................................................................................................. 29 
 Função DIA ................................................................................................................... 29 
 Função DIA.DA.SEMANA ............................................................................................ 29 
 Função DIATRABALHO.INTL .......................................................................................... 31 
 Função DIATRABALHOTOTAL.INTL .............................................................................. 33 
Excel 2010 – Avançado 
 
 
 
 
II Senac São Paulo 
 Função HORA ................................................................................................................... 37 
 Função MINUTO .............................................................................................................. 37 
 Formatação CONDICIONAL ........................................................................................... 38 
 
Exercício Proposto ................................................................................................................. 40 
 
 
Capítulo 2 – Funções de Pesquisa e Auditoria de Fórmulas ..................................................... 45 
Funções de PESQUISA ........................................................................................................... 47 
Atividade 1 – Utilizando as funções PROCV e PROCH ......................................................... 47 
 Função PROCV ................................................................................................................. 47 
 Função PROCH ................................................................................................................. 49 
 
Atividade 2 – Utilizando as funções CORRESP e ÍNDICE ..................................................... 51 
 Função CORRESP ............................................................................................................. 51 
 Função ÍNDICE ................................................................................................................. 52 
 
AUDITORIA DE FÓRMULAS ................................................................................................ 53 
Atividade 3 – Rastreando os precedentes de uma célula ....................................................... 53 
 
Atividade 4 – Rastreando os dependentes de uma célula ...................................................... 55 
 
Atividade 5 – Rastreando erros em fórmulas ......................................................................... 57 
 
Atividade 6 – Avaliando fórmulas ........................................................................................... 59 
 
Atividade 7 – Mostrando fórmulas ......................................................................................... 61 
 
Exercício Proposto ................................................................................................................. 63 
 
 
Capítulo 3 – Funções de Texto ............................................................................................... 65 
Funções de PESQUISA ........................................................................................................... 67 
Atividade 1 – Utilizando a função ARRUMAR ........................................................................ 67 
 Função ARRUMAR ............................................................................................................ 67 
 
Atividade 2 – Utilizando as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA ......... 69 
 Função MAIÚSCULA ........................................................................................................ 69 
 Função MINÚSCULA ........................................................................................................ 69 
 Função PRI.MAIÚSCULA ..................................................................................................69 
 
Atividade 3 – Utilizando as funções ESQUERDA, DIREITA e EXT.TEXTO ........................ 71 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo III 
 Função ESQUERDA .......................................................................................................... 71 
 Função DIREITA ................................................................................................................ 71 
 Função EXT.TEXTO ......................................................................................................... 71 
 
Exercício Proposto ................................................................................................................. 73 
 
 
Capítulo 4 – Funções de Banco de Dados ................................................................................ 75 
Funções de BANCO DE DADOS .......................................................................................... 77 
Atividade 1 – Utilizando as funções BDSOMA e BDMÉDIA .................................................. 77 
 Função BDSOMA .............................................................................................................. 77 
 Função BDMÉDIA ............................................................................................................. 79 
 
Atividade 2 – Utilizando as funções BDMÁX e BDMÍN ......................................................... 81 
 Função BDMÁX................................................................................................................. 81 
 Função BDMÍN .................................................................................................................. 81 
 
Atividade 3 – Utilizando a função BDEXTRAIR ...................................................................... 83 
 Função BDEXTRAIR .......................................................................................................... 83 
 
Atividade 4 – Utilizando a função BDCONTARA .................................................................. 87 
 Função BDCONTARA ...................................................................................................... 87 
 
Exercício Proposto ................................................................................................................. 88 
 
 
Capítulo 5 – Importação de Dados para o Excel ...................................................................... 89 
Importação de Dados para o EXCEL ..................................................................................... 91 
Atividade 1 – Importando arquivos texto para o Excel .......................................................... 91 
 Importando um arquivo texto .......................................................................................... 91 
 
Atividade 2 – Importando arquivos de dados para o Excel .................................................... 97 
 IMPORTANDO UM ARQUIVO DE DADOS ................................................................... 97 
 
Atividade 3 – Importando base de dados como consulta para o Excel .................................. 99 
 Importando uma Tabela como Consulta ........................................................................... 99 
 
Atividade 4 – Vinculando dados do Excel no Access ............................................................ 103 
 Vinculando Dados do Excel no Access ............................................................................ 103 
 
Exercício Proposto ............................................................................................................... 106 
 
Excel 2010 – Avançado 
 
 
 
 
IV Senac São Paulo 
 
Capítulo 6 – Filtro, Classificação e Filtro Avançado ................................................................ 107 
Filtrando e classificando no Excel ......................................................................................... 109 
Atividade 1 – Aplicar o AutoFiltro ........................................................................................ 109 
 
Atividade 2 – Aplicar o filtro a mais de um campo ............................................................... 111 
 
Atividade 3 – Aplicar filtro alternativo .................................................................................. 113 
 
Atividade 4 – Classificar com o AutoFiltro ........................................................................... 115 
 
Atividade 5 – Classificação Avançada ................................................................................... 117 
 
Atividade 6 – Classificação por lista personalizada ............................................................... 119 
 
Atividade 7 – Classificação por cores ................................................................................... 121 
 
Atividade 8 – Filtro avançado ............................................................................................... 123 
 
Atividade 9 – Filtro avançado com critérios múltiplos ......................................................... 127 
 
Exercício Proposto ............................................................................................................... 128 
 
 
Capítulo 7 – Visualização de Dados ....................................................................................... 129 
Atividade 1 – Criação de Minigráficos .................................................................................. 131 
 
Atividade 2 – Alteração e formatação de Minigráficos ......................................................... 133 
 
Atividade 3 – Capturar telas ................................................................................................. 135 
 
Exercício Proposto ............................................................................................................... 136 
 
 
Capítulo 8 – Tabela Dinâmica ............................................................................................... 137 
Atividade 1 – Criação da Tabela Dinâmica ........................................................................... 139 
 
Atividade 2 – Atualização e Inclusão de Dados .................................................................... 141 
 
Atividade 3 – Criação de outros níveis ................................................................................. 143 
 
Atividade 4 – Formatar a Tabela Dinâmica .......................................................................... 145 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo V 
 
Atividade 5 – Agrupar Itens .................................................................................................. 147 
 
Atividade 6 – Aplicar Filtro de Relatório ............................................................................... 149 
 
Atividade 7 – Agrupar campos por períodos ........................................................................ 151 
 
Atividade 8 – Segmentação de Dados .................................................................................. 153 
 
Atividade 9 – Gráfico Dinâmico ............................................................................................ 155 
 
Atividade 10 – Mostrar Páginas de Relatório ........................................................................ 157 
 
Exercício Proposto ............................................................................................................... 158 
 
 
Capítulo 9 – Resumindo Dados ............................................................................................. 159 
Atividade 1 – Consolidação de Dados .................................................................................. 161 
 
Atividade 2 – Consolidação de Dados com Vínculo ............................................................. 165 
 
Atividade 3 – Estrutura de Tópicos ......................................................................................167 
 
Atividade 4 – Subtotais ......................................................................................................... 169 
 
Atividade 5 – Cenários .......................................................................................................... 173 
 
Exercício Proposto ............................................................................................................... 179 
 
 
Capítulo 10 – Validação de Dados ......................................................................................... 181 
Atividade 1 – Criar regras de validação ................................................................................ 183 
 
Exercício Proposto ............................................................................................................... 188 
 
 
Capítulo 11 – Utilizando pasta de trabalho em equipe ........................................................... 189 
Trabalho em Equipe.............................................................................................................. 191 
Atividade 1 – Proteção de planilha ....................................................................................... 191 
 
Atividade 2 – Compartilhar a pasta de trabalho ................................................................... 197 
 
Excel 2010 – Avançado 
 
 
 
 
VI Senac São Paulo 
Exercício Proposto ............................................................................................................... 200 
 
 
Capítulo 12 – Simulações ...................................................................................................... 201 
Atividade 1 – Atingir Meta .................................................................................................... 203 
 
Atividade 2 – Solver ............................................................................................................. 205 
 
Atividade 3 – Tabela de Dados ............................................................................................ 209 
 
Atividade 4 – Inserir Tabela ................................................................................................. 213 
 
Exercício Proposto ............................................................................................................... 216 
 
 
Capítulo 13 – Macros Intrativas ............................................................................................. 219 
Trabalhando com Macros ..................................................................................................... 221 
Atividade 1 – Criar macro .................................................................................................... 221 
 
Atividade 2 – Criando macro com deslocamentos relativos ................................................ 225 
 
Atividade 3 – Execução e exclusão de macros ..................................................................... 229 
 
Exercício Proposto ............................................................................................................... 235 
 
 
Capítulo 14 – Automação de Planilhas ................................................................................... 237 
Automação de Planilhas ........................................................................................................ 239 
Atividade 1 – Criar controles para escolha de dados ........................................................... 239 
 
Atividade 2 – Criação da Macro Modelos ............................................................................ 243 
 
Atividade 3 – Criar controles complementares à pesquisa .................................................. 245 
 
Atividade 4 – Criar Critérios para a pesquisa ....................................................................... 249 
 
Atividade 5 – Criar macro Veículos ...................................................................................... 251 
 
Atividade 6 – Controles para cálculos .................................................................................. 253 
 
Exercício Proposto ............................................................................................................... 256 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo VII 
A estrutura da apostila 
Esta apostila está dividida em capítulos. Cada capítulo contém atividades práticas e informações teóricas 
sobre o software. 
Para obter o melhor rendimento possível em seu estudo, evitando dúvidas ou erros, é importante que 
você: 
• leia com atenção todos os itens da apostila, pois sempre encontrará informações úteis para a 
execução das atividades; 
• conheça e respeite o significado dos símbolos colocados na margem esquerda de determinados 
parágrafos do texto, pois eles servem para orientar seu trabalho; 
• faça apenas o que estiver indicado no item e só execute uma seqüência após ter lido a instrução do 
respectivo item. 
 
Significado dos símbolos 
 
 
Início 
Este símbolo sinaliza o início de uma atividade. Apesar de poder interromper seu 
estudo em qualquer etapa, é recomendável que você inicie e finalize uma atividade 
sem interrupção. 
 
 
 
Dica 
Quando este símbolo aparecer, você terá informações adicionais sobre o assunto, 
como dicas, atalhos e sugestões para facilitar o trabalho com o software. Sua leitura 
não é obrigatória para o desenvolvimento da atividade; mas lembre-se: quanto mais 
informações você tiver, melhor será o seu aproveitamento. 
 
 
 
Observação 
Este símbolo marca um texto com observações sobre o assunto tratado. Embora 
importante, sua leitura não é obrigatória, nem interfere diretamente na atividade 
em execução. 
 
 
 
Atenção 
Toda vez que se deparar com este símbolo, leia com muita atenção o texto que o 
acompanha, pois as informações que ele contém afetarão os passos seguintes da 
atividade. Isso evitará dúvidas posteriores. 
 
 
Utilizando o CD 
Para a execução das atividades dos capítulos, disponibilizamos os arquivos num CD que acompanha a 
apostila. 
Para facilitar seu trabalho, faça uma cópia da pasta “PLANILHAS - ATIVIDADES” para sua Área de 
Trabalho. Esta pasta contém todos os arquivos necessários para a execução de suas atividades e dos 
exercícios propostos. 
Além dela, há uma pasta chamada “PLANILHAS RESOLVIDAS”, que contém os arquivos finais das 
atividades e exercícios propostos para você possa comparar com as atividades executadas por você. 
 
 
Excel 2010 – Avançado 
 
 
 
 
VIII Senac São Paulo