Baixe o app para aproveitar ainda mais
Prévia do material em texto
CURSO DE EXTENSÃO MS-Excel 2016 AVANÇADO com VBA Prof. André Luiz Duarte https://www.instagram.com/unibratec https://www.facebook.com/unibratec APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 3 SUMÁRIO 1 INTRODUÇÃO: Recursos & Benefícios ................................................................................................................. 11 1.1 O MS-Excel 2016 ....................................................................................................................................... 11 2 Criando uma “Pasta de Trabalho” ....................................................................................................................... 12 2.1 Criando um “NOVO” Arquivo .................................................................................................................... 12 2.2 Arquivo de Modelo: .................................................................................................................................. 13 2.2.1 Inserindo “DADOS” em uma Célula: ............................................................................................. 13 2.2.2 A Célula “ATIVA” da Seleção: ........................................................................................................ 14 2.2.3 Auto Formatação: ......................................................................................................................... 14 2.2.4 Alça de Preenchimento: ................................................................................................................ 15 2.2.5 Lista Personalizada: ...................................................................................................................... 15 3 Manipulação de “DADOS” ................................................................................................................................... 16 3.1 Formatação de “DADOS” na Célula ........................................................................................................... 16 3.1.1 Formatar Células: .......................................................................................................................... 17 3.1.2 Proteger Células:........................................................................................................................... 18 3.2 Manipulação de “DADOS” Maior .............................................................................................................. 19 3.2.1 Células Mescladas: ........................................................................................................................ 19 3.2.2 Largura e Altura da Célula:............................................................................................................ 19 4 Compatilhando “Pasta de Trabalho” ................................................................................................................... 20 4.1 Criando “Pasta de Trabalho” Compartilhada ............................................................................................ 20 4.2 Protegendo “Pasta de Trabalho” Compartilhada ...................................................................................... 21 4.3 Editar “Pasta de Trabalho” Compartilhada ............................................................................................... 21 4.4 Resolver Conflitos ..................................................................................................................................... 23 4.5 Encerrar Pasta de Trabalho Compartilhada .............................................................................................. 24 5 Fórmulas, Funções, Formatações ........................................................................................................................ 25 5.1 Fórmulas ................................................................................................................................................... 25 5.2 Fórmulas & Valores Constantes ................................................................................................................ 25 5.3 Funções ..................................................................................................................................................... 26 5.4 Funções Aninhadas ................................................................................................................................... 27 5.5 Referências de Células .............................................................................................................................. 28 5.6 Manipulando Funções ............................................................................................................................... 29 5.6.1 Função HOJE, Função MÊS, Função ANO: ..................................................................................... 30 5.6.2 Função CONT.VALORES, Função CONT.NÚM, Função CONTAR.VAZIO: ........................................ 31 5.6.3 Função LIN & Função COL: ............................................................................................................ 32 5.6.4 Função TEXTO: .............................................................................................................................. 32 5.6.5 Função MÉDIA & Função MÉDIAA: ............................................................................................... 33 5.6.6 Função MÁXIMO & Função MÍNIMO: ........................................................................................... 34 5.6.7 Função MAIOR & Função MENOR: ............................................................................................... 35 5.6.8 Função SE: .................................................................................................................................... 36 5.6.9 Função SE Aninhada: .................................................................................................................... 37 5.7 Formatação Condicional ........................................................................................................................... 39 5.7.1 Gerenciar Regras: ......................................................................................................................... 39 5.7.2 Função E: ...................................................................................................................................... 42 5.7.3 Função OU: ................................................................................................................................... 43 5.7.4 Função CONCAT (CONCATENAR): ................................................................................................. 44 5.7.5 Função CONT.SE: .......................................................................................................................... 44 5.7.6 Função CONT.SES: ........................................................................................................................ 45 5.7.7 Função SOMA: .............................................................................................................................. 47 4 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 5.7.8 Função SOMASE: .......................................................................................................................... 48 5.7.9 Função MÉDIASE: ......................................................................................................................... 49 5.8 Classificar & Filtrar; ................................................................................................................................... 50 5.8.1 FILTRAR: .......................................................................................................................................50 5.8.2 CLASSIFICAR: ................................................................................................................................ 51 5.9 Subtotais ................................................................................................................................................... 52 5.9.1 Conceitos & Funções .................................................................................................................... 52 5.9.2 Função SUBTOTAL: ....................................................................................................................... 53 6 Mais Funções ...................................................................................................................................................... 54 6.1 Opções de Controle .................................................................................................................................. 54 6.2 Nomear Intervalo de Células .................................................................................................................... 54 6.2.1 Gerenciador de Nomes: ............................................................................................................... 54 6.2.2 Função PROCV: ............................................................................................................................. 55 6.2.3 Função PROCH: ............................................................................................................................ 56 6.2.4 Função PROC: ............................................................................................................................... 57 6.2.5 Função ESQUERDA & Função DIREITA: ........................................................................................ 58 6.3 Funções de Banco de Dados ..................................................................................................................... 59 6.3.1 Diretrizes: ..................................................................................................................................... 59 6.4 Funções É.................................................................................................................................................. 60 6.4.1 Diretrizes: ..................................................................................................................................... 60 6.5 Opções de Dígitos e Textos ....................................................................................................................... 61 6.5.1 Função NÃO: ................................................................................................................................ 61 6.5.2 Função MAIÚSCULA & Função MINÚSCULA: ............................................................................... 62 6.5.3 Função PRI.MAIÚSCULA: .............................................................................................................. 63 6.5.4 Função ARREDONDAR.PARA.CIMA & Função ARREDONDAR.PARA.BAIXO: ................................. 63 6.6 Opções de Tempo & Conversão ............................................................................................................... 64 6.6.1 Diretrizes: ..................................................................................................................................... 64 6.6.2 Função DATA: ............................................................................................................................... 64 6.6.3 Função FIMMÊS: .......................................................................................................................... 65 6.6.4 Função DATADIF: .......................................................................................................................... 66 6.6.5 Função AGORA: ............................................................................................................................ 67 6.6.6 Função DIA.DA.SEMANA: ............................................................................................................. 68 6.6.7 Função ESCOLHER: ....................................................................................................................... 68 6.6.8 Função DIATRABALHOTOTAL: ...................................................................................................... 69 6.6.9 Função HORA: .............................................................................................................................. 70 6.6.10 Função CONVERTER: .................................................................................................................... 71 6.7 COMENTÁRIOS ......................................................................................................................................... 72 6.7.1 Definição: ..................................................................................................................................... 72 6.8 Congelar Painéis ....................................................................................................................................... 74 6.8.1 Dividir Painéis: .............................................................................................................................. 74 6.9 Validação de DADOS ................................................................................................................................. 75 6.9.1 Regras de Validação: .................................................................................................................... 75 6.9.2 Critérios de Validação: ................................................................................................................. 76 6.9.3 Alerta de ERRO: ............................................................................................................................ 77 6.9.4 Circundar Dados Inválidos: ........................................................................................................... 78 7 Mais & Mais Funções .......................................................................................................................................... 80 7.1 Opções de Busca, de Referência e Financeiras ......................................................................................... 80 7.1.1 Função ÍNDICE: ............................................................................................................................. 80 7.1.2 Função PROCURAR: ...................................................................................................................... 82 7.1.3 Função REPT: ................................................................................................................................ 83 7.1.4 Função PGTO (Pagamento): ......................................................................................................... 84 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 5 7.1.5 Função VP: .................................................................................................................................... 85 7.1.6 Função VF: .................................................................................................................................... 86 7.1.7 Função NPER: ................................................................................................................................ 87 7.2 Mais Funções Financeiras ......................................................................................................................... 88 7.2.1 Função VPL: .................................................................................................................................. 88 7.2.2 Função TIR: ...................................................................................................................................89 7.2.3 Função TAXA: ................................................................................................................................ 90 7.2.4 Função HIPERLINK: ....................................................................................................................... 91 7.3 Funções de Matemática & Trigonometria ................................................................................................ 92 7.3.1 Função ABS: .................................................................................................................................. 92 7.3.2 Função ALEATÓRIO: ...................................................................................................................... 92 8 GRÁFICOS ............................................................................................................................................................ 93 8.1 Representação Visual dos Dados .............................................................................................................. 93 8.2 Elementos do Gráfico ................................................................................................................................ 94 8.2.1 Guia Ferramentas de Gráfico: ....................................................................................................... 94 8.3 Minigráficos .............................................................................................................................................. 96 8.3.1 Linhas, Colunas, Perdas e Ganhos: ............................................................................................... 96 9 Gerenciando Informações ................................................................................................................................... 97 9.1 Introdução ................................................................................................................................................ 97 9.2 Tabelas ...................................................................................................................................................... 97 9.2.1 Criando uma Tabela: ..................................................................................................................... 97 9.2.2 Excluindo uma Tabela: .................................................................................................................. 98 9.3 Tabelas Dinâmicas ..................................................................................................................................... 99 9.3.1 Intordução: ................................................................................................................................... 99 9.3.2 Criando uma Tabela Dinâmica: ..................................................................................................... 99 9.3.3 Imprimindo uma Tabela Dinâmica: ............................................................................................. 102 9.4 Atingir Meta ............................................................................................................................................ 103 9.4.1 Recurso de Teste de Hipótese: ................................................................................................... 103 9.5 Gerenciador de Cenários ........................................................................................................................ 105 9.5.1 Recurso de Teste de Hipótese: ................................................................................................... 105 9.6 Proteção de Dados .................................................................................................................................. 107 9.6.1 Recursos de Bloqueios e Senhas: ................................................................................................ 107 9.6.2 SENHA para Abrir a Pasta de Trabalho: ...................................................................................... 107 9.6.3 Proteger Planilha / Proteger Pasta de Trabalho: ......................................................................... 108 9.6.4 Proteger Intervalo de Células: .................................................................................................... 109 9.6.5 Senha de Proteção / Senha de Gravação: ................................................................................... 110 9.7 Consolidar ............................................................................................................................................... 111 9.7.1 Resumir Informações de Dados: ................................................................................................. 111 9.7.2 Dados Atualizados Automaticamente: ........................................................................................ 112 9.8 SOLVER .................................................................................................................................................... 113 9.8.1 Suplementos do MS-Excel 2016: ................................................................................................ 113 9.8.2 Utilizando o Recurso de SOLVER: ................................................................................................ 115 9.9 Layout da Página ..................................................................................................................................... 116 9.9.1 Opções de Impressão: ................................................................................................................ 116 9.9.2 Configurar Página: ...................................................................................................................... 117 9.9.3 Opções de Planilha: .................................................................................................................... 118 10 Comandos de MACRO ....................................................................................................................................... 119 10.1 Automação de Tarefas ............................................................................................................................ 119 10.2 A Guia de Desenvolvedor ........................................................................................................................ 119 10.3 Segurança de MACRO: ............................................................................................................................ 120 6 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 10.3.1 Níveis de Segurança: .................................................................................................................. 120 10.4 Gravação de MACRO .............................................................................................................................. 121 10.4.1 Processo de Gravação: ............................................................................................................... 121 10.4.2 Personalizar MACRO: ................................................................................................................. 122 10.4.3 Editar & Excluir MACRO: ............................................................................................................ 122 10.5 Salvar Pasta de Trabalho com MACRO ................................................................................................... 124 10.5.1 Pasta de Trabalho Habilitada para MACRO: ............................................................................... 124 10.6 Editor do Visual Basic .............................................................................................................................. 124 10.6.1 Editor do VBA: ............................................................................................................................124 10.6.2 eXEMPLOS Do MS-Excel 2016: ................................................................................................... 125 11 Programação em VBA ....................................................................................................................................... 126 11.1 VBA - Visual Basic for Applications.......................................................................................................... 126 11.2 As Variáveis do VBA ................................................................................................................................ 126 11.2.1 Variáveis: .................................................................................................................................... 126 11.2.2 Tipos de Variáveis: ...................................................................................................................... 126 11.2.3 A Declaração de Variáveis: ......................................................................................................... 127 11.2.4 A Abrangência de uma Variável: ................................................................................................ 129 11.3 Os Operadores ........................................................................................................................................ 130 11.3.1 Operadores Aritméticos: ............................................................................................................ 130 11.3.2 Operadores de Comparação: ..................................................................................................... 130 11.3.3 Operadores de Concatenação: ................................................................................................... 130 11.3.4 Operadores Lógicos: ................................................................................................................... 131 11.3.5 Precedências entre Operadores: ................................................................................................ 131 11.4 As Estruturas Condicionais ..................................................................................................................... 132 11.4.1 If... Then... Else... ........................................................................................................................ 132 11.5 Select Case .............................................................................................................................................. 133 11.6 As Estrutura de Repetição ...................................................................................................................... 134 11.6.1 Do... Loop: .................................................................................................................................. 134 11.6.2 Do While... Loop: ........................................................................................................................ 134 11.6.3 Do... Loop While: ........................................................................................................................ 135 11.6.4 Do Until... Loop:.......................................................................................................................... 136 11.6.5 Do... Loop Until:.......................................................................................................................... 136 11.6.6 For... Next: .................................................................................................................................. 137 11.6.7 For Each... Next: ......................................................................................................................... 138 12 Caixas de Diálogo .............................................................................................................................................. 139 12.1 Montagem & Tipos de Caixas de Diálogo ............................................................................................... 139 12.1.1 Função InputBox: ....................................................................................................................... 139 12.1.2 Mensagem de Informação: ........................................................................................................ 139 12.1.3 Mensagem de Tomada de Decisão: ........................................................................................... 141 12.2 Formatação............................................................................................................................................. 142 12.2.1 Concatenar: ................................................................................................................................ 142 12.2.2 Quebrando Linhas: ..................................................................................................................... 143 12.2.3 Formato da Informação: ............................................................................................................ 144 13 Formulários ....................................................................................................................................................... 145 13.1 Criando um Formulário ........................................................................................................................... 145 13.2 Formulário na Planilha ............................................................................................................................ 145 13.2.1 Inserindo os Controles de Formulário: ....................................................................................... 145 13.2.2 Editando os Controles de Formulários: ...................................................................................... 146 13.2.3 Editando o Texto para o Controle: ............................................................................................. 146 13.2.4 Formatando os Controles de Formulários: ................................................................................. 147 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 7 13.2.5 Adicionando MACRO aos Controles: ........................................................................................... 147 13.3 Montando um Formulário....................................................................................................................... 148 13.3.1 Montando & Configurando: ........................................................................................................ 148 13.3.2 Utilizando a Função ÍNDICE: ....................................................................................................... 149 13.3.3 Proteção do Formulário: ............................................................................................................. 150 13.4 Formulário no Visual Basic ...................................................................................................................... 151 13.4.1 As Aplicações em VBA: ................................................................................................................ 151 13.4.2 As Etapas de uma Aplicação em VBA: ......................................................................................... 151 13.4.3 A Criação de uma Interface: ....................................................................................................... 152 13.4.4 Configurando os Controles: ........................................................................................................ 154 13.4.5 Inserindo o Código: ..................................................................................................................... 156 13.5 Executando o Formulário ........................................................................................................................161 13.5.1 Atribuindo um Comando de MACRO: ......................................................................................... 161 13.5.2 Finalizando o Formulário: ........................................................................................................... 162 8 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 9 WEB-PAGES DO MS-OFFICE 2016 Figura 1 – MS-Excel 2016: Site Oficial do MS-Office 2016. 10 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática APRESENTAÇÃO: É com imenso prazer que mostro para vocês, através do conteúdo programado desta Apostila, todo o poder que há neste Aplicativo de Planilha Eletrônica e de Banco de Dados, o MS-Excel 2016. Fazendo parte do Pacote da Suíte para escritório da Microsoft, o Office 356, o Excel 2016 é uma excelente opção para quem deseja controlar gastos, planejar investimentos futuros e, até mesmo, implementar pequenos projetos com o armazenamento de dados para acesso e a edição. Particularmente, eu espero que vocês gostem bastante deste material, e que essa Apostila sirva como um grande auxílio de ajuda e aprendizagem em relação a esta magnifica ferramenta, chamada de MS-Excel 2016. Obrigado! O Professor André Luiz Duarte é graduado em Ciência da Computação, pela FG - Faculdade dos Guararapes, e especialista na área de Design Web-Apps com Tecnologia Front-End, pela UNIBRATEC - União Brasileira de Tecnologia. O Prof. trabalha com marketing digital e planejamento de impressos, além de ministrar aulas junto ao ensino técnico e profissionalizante há cerca de 14 anos, tendo já assumido o cargo de coordenador para a confecção de material didático para o Ensino à Distância (EAD), da UNIBRATEC, por cerca de 02 anos. O Prof. já prestou serviços ligados às áreas de ensino, publicação de impressos e fechamento de arquivos, para o Grupo Dom Bosco de Artes e Ofícios (pré-impressão), e trabalhos de marketing digital (propaganda), planejamento visual e de ensino, para o Grupo SER Educacional (UNINASSAU e Faculdade Joaquim Nabuco). Atualmente, oferece serviços de aprendizagem e treinamento para pequenas, médias e grandes empresas, como, por exemplo, SENAC - Serviço Nacional de Aprendizagem Comercial, INFRAERO - Empresa Brasileira de Infraestrutura Aeroportuária, e com a ALEPE - Escola da Assembleia Legislativa do Estado de Pernambuco. André Luiz Duarte Prof. Nível Técnico e Profissionalizante de Informática APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 11 1 INTRODUÇÃO: RECURSOS & BENEFÍCIOS 1.1 O MS-EXCEL 2016 O MS-Excel 2016 trata-se de uma excelente e extraordinária planilha eletrônica, e de um ótimo banco de dados. Ele é bem prático em suas operações. Através dele, podemos realizar tipos de balancetes e de custos financeiros, estimativas de vendas e muitos outros recursos disponíveis. No MS-Excel 2016 há algumas novidades, como: ▪ Slicer, que permite analisar os dados em uma Tabela Dinâmica; ▪ Sparkline (Minigráficos), que podem ser introduzidos em uma célula; ▪ Preenchimento Relâmpago, que permite por dados automaticamente nas células; ▪ Análise Rápida, que permite após a seleção dos dados, por Gráficos, Subtotais e/ou Tabelas; ▪ Gráficos Recomendados, que permite impor Gráficos de acordo com os dados selecionados; ▪ “Diga-me”, que permite ver as novidades do Aplicativo em treinamentos gratuitos; O MS-Excel 2016 processa planilhas com até no máximo 16 mil colunas, da Letra A até as Letras XFD (onde visto que o alfabeto só tem 26 Letras), e com cerca de 01 milhão de linhas (1.048.576). Além disso, o MS-Excel 2016 pode definir pastas com até no máximo cerca de 255 planilhas. O MS-Excel 2016 faz parte do Pacote Office 365, onde se permite o uso de vários aplicativos voltados a escritório. Figura 2 – MS-Excel 2016: Área de Trabalho. Entre os vários cenários mais comuns para usar o MS-Excel 2016 podemos destacar: ▪ CONTABILIDADE: Você pode usar os recursos de cálculos do MS-Excel 2016 em demonstrações financeiras, como demonstração de fluxo de caixa, demonstração de resultados ou demonstração de lucros e de perdas; ▪ ELABORAÇÃO ORÇAMENTÁRIA: Não importa se as suas necessidades são pessoais ou comerciais, você pode criar qualquer tipo de orçamento no MS-Excel 2016, como um plano de orçamento de marketing, ou orçamento de evento ou orçamento de aposentadoria; Planilha. Linhas e Colunas. 12 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática ▪ COBRANÇA E VENDAS: O MS-Excel 2016 também é útil para gerenciar dados de cobrança e vendas, e você pode criar facilmente os formulários necessários, como notas fiscais de venda, guias de remessa ou ordens de compra, em modelos gerais; ▪ GERAÇÃO DE RELATÓRIOS: Você pode criar vários tipos de relatórios no MS-Excel 2016 que refletem sua análise dos dados ou resumo dos dados, como relatórios que medem o desempenho do projeto, mostram a variação entre os resultados estimados e os reais ou relatórios que preveem dados; ▪ PLANEJAMENTO E CONTROLE: Você pode usar o MS-Excel 2016 para gerar os planejamentos futuros e controle de dados de um quadro de informações, como um plano de aulas semanal ou um quadro de horários ou ainda uma lista de estoques de controle de equipamentos; 2 CRIANDO UMA “PASTA DE TRABALHO” 2.1 CRIANDO UM “NOVO” ARQUIVO A Pasta de Trabalho em Branco é o princípio para começar a colocar os seus dados, no MS-Excel 2016. E, para iniciar a implementar as suas informações na sua planilha. A partir dela, você pode adicionar Fórmulas e Funções, acrescentar Filtros e Regras de Validação de Dados, além de fazer Cenários, Consolidação, e muitas outras operações. Para criar uma “NOVA” Pasta de Trabalho em Branco, no MS-Excel 2016, basta fazer o seguinte: 1 – Clique na Guia Arquivo; 2 – Depois, clique em “NOVO”; 3 – Em Modelos Disponíveis, clique em Pasta de Trabalho em Branco; 4 – Clique em Criar; 5 – OU, clique sobre o Botão Novo, disponível na Barra de Acesso Rápido, na parte superior do Aplicativo; Figura 3 – MS-Excel 2016: Criando uma “NOVA” Pasta de Trabalho. Os Arquivos de Modelo do Office (Template) são uma ótima pedida para quem deseja começar um trabalho e não tem ideia de como inicia-lo. Daí a importância de começar através de um modelo já pronto e disponível. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 13 2.2 ARQUIVO DE MODELO: Como dito antes, os Arquivos de Modelo do Office (Template) são uma ótima pedida, para iniciar algum projeto, partindo de um modelo pronto já pré-existente, um para cada tipo de situação. Existem vários modelos disponíveis que o usuário poderá utilizar, de acordo com as suas necessidades. Caso o usuário deseja, ele poderá construir o seu próprio Arquivo de Modelo. Basta, para isso, escolher a opção para “Salvar como Modelo de Documento”, presente nas opções para Salvar o arquivo. Todos os Arquivo de Modelo atuais ficam disposto na Aba Em Destaque. E depois, os arquivos ficam separados pela opção Pesquisa Sugerida. Já os Arquivos de Modelo “criados pelo usuário” ficam dispostos na Aba Pessoal. 2.2.1 INSERINDO “DADOS” EM UMA CÉLULA: A Planilha é seu principal arquivo utilizado no MS-Excel 2016 para incorporar e trabalhar com DADOS. Uma Planilha consiste de Células organizadas em Linhas e Colunas, armazenadas na mesma Pasta. O número máximo de Planilhas que se pode adicionar em uma mesmaPasta é de 255. O padrão já pré-existente para o MS-Excel 2016 é de apenas 01 planilha por pasta, mas esse padrão pode ser alterado. As Células são a interseção existente entre as Linhas e as Colunas, na planilha. O limite máximo para a planilha é de 16.384 Colunas (da Letra A às Letras XFD, visto que o alfabeto só possui 26 Letras), e de 1.048.576 Linhas, e com o seu limite de 33.554 caracteres no máximo, para serem inclusos em cada célula disposta. 1 – Clique na Célula onde você deseja pôr as suas informações, e digite os seus DADOS; 2 – Pressione ENTER, ou a Tecla TAB, ou use uma das Setas de Direção do Teclado para ir à próxima Célula, e para confirmar os DADOS digitados. O ENTER insere as informações e seleciona a Célula debaixo; 3 – Se desejar, clique no Botão Inserir, na Barra de Fórmulas e Cálculos, para adicionar as informações na Célula; 4 – Se clicar no Botão Cancelar, ou se pressionar a Tecla ESC, no Teclado, você “CANCELA” as informações; 5 – Com “ISSO”, as informações serão adicionadas e incorporadas à Célula; Figura 4 – MS-Excel 2016: Inserindo “DADOS” na Célula. É possível inserir a mesma informação em várias células sequenciais ou adjacentes (aleatórias), ao mesmo tempo. Para isso, basta usar a Tecla SHIFT (sequencial) ou CTRL (aleatória) enquanto seleciona as células de uma planilha. Planilha. Célula. 14 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 2.2.2 A CÉLULA “ATIVA” DA SELEÇÃO: Para selecionar várias células de uma planilha sequencialmente, basta clicar nelas com o Botão Esquerdo do Mouse, ou pressionar as Setas de Direção do Teclado, enquanto estiver pressionando a Tecla SHIFT. Para selecionar várias células de uma planilha aleatoriamente, basta clicar sobre as células com o Botão Esquerdo do Mouse, enquanto estiver pressionando a Tecla CTRL. OBS.: Durante o processo da seleção, uma célula em particular fica com a sua seleção em destaque de “branco”, diferente das demais células selecionadas do grupo. Trata-se da “Célula Ativa”, aquela célula no Grupo de Seleção onde você irá digitar as suas informações de DADOS. Esta “Célula Ativa” pode ser alterada de posição, modificada, simplesmente pressionando a Tecla TAB. Depois, para inserir a mesma informação em todas as células selecionadas, basta pressionar as Teclas CTRL+ENTER. Figura 5 – MS-Excel 2016: Inserindo “DADOS” na Célula. 2.2.3 AUTO FORMATAÇÃO: Algumas informações quando inseridas, postas, em uma célula permitem realizar uma Auto Formatação na mesma, alterando a forma como elas leem essas mesmas informações, como se você mesmo quisesse essa opção disposta. Por exemplo, ao escrever em uma célula uma informação de data qualquer, ou algum tipo de valor monetário, logo automaticamente, a célula assume a formatação adequada para receber aquele dado. OBS.: Mas, é importante ressaltar que, determinada auto formatação definida na célula irá permanecer na mesma, mesmo que você venha a DELETAR as informações contidas na célula. Para remover alguma “Auto Formatação” já definida na célula, faça o seguinte: 1 – Clique na Célula com a Auto Formatação já definida; 2 – Na Guia de Página Inicial, clique no Botão Limpar (botão em forma de borracha); 3 – Ao clicar na Seta de Escolha, ao lado, você terá algumas opções de limpeza: Limpar Formato: limpa a formatação, Limpar Conteúdo: limpa os dados “digitados” contidos na célula, Limpar Comentários: elimina os comentários (se houver), Limpar Hiperlinks: limpa os LINKS se houver, ou escolhe a opção Limpar Tudo; Grupo de Células selecionadas. “DADOS” na Célula. Barra de Fórmulas e Cálculos. Botão Cancelar. Botão Inserir. Botão Inserir Função. Endereço da Célula. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 15 2.2.4 ALÇA DE PREENCHIMENTO: A Alça de Preenchimento é um pequeno quadrado preto, que fica no canto inferior direito da célula. Quando você aponta para a Alça, o cursor do Mouse se transforma em um “Ponto Cruz” (Sinal de MAIS) pequeno. Quando você clica e arrasta, você insere uma série de informações (DADOS), como uma “LISTA” já Pré-definida, como Dias da Semana, Meses do Ano, Números Progressivos, Números com Acréscimos, e/ou, apenas Números que são repetitivos. Para utilizar a Alça de Preenchimento, faça o seguinte: 1 – Digite o valor inicial na Célula; 2 – Em seguida, na próxima Célula, digite um outro valor para estabelecer um padrão; 3 – Selecione as duas Células, clique na Alça de Preenchimento e “ARRASTE”; Graças a Alça de Preenchimento, podemos ter LISTAS Pré-Definidas, em acordo com os dados que você digita. Figura 6 – MS-Excel 2016: Alça de Preenchimento. 2.2.5 LISTA PERSONALIZADA: As LISTAS Pré-definidas, são uma opção muito útil para você inserir opções de textos prontos, direto na sua planilha. Para Editar a Lista Personalizada, da Alça de Preenchimento, basta fazer o seguinte: 1 – Clique na Guia Arquivo, e peça Opções; 2 – Na Caixa de Diálogo de Opções, clique em Avançado, Geral, e clique no Botão Editar Listas Personalizadas; 3 – Na Caixa de Listas Personalizadas, você adiciona uma “NOVA” Lista, digitando os DADOS na Entradas da Lista, separando as informações por vírgula e espaço ou pressionando a Tecla ENTER entre as informações; 4 – Você pode “Importar os DADOS” existentes de células selecionadas; 5 – Você pode ELIMINAR uma Lista já existente, selecionando a Lista disponível entre as opções, e clicar no Botão Excluir; Uma das opções quanto ao uso de uma LISTA Pré-Definida está para o cadastro e uso dos nomes dos funcionários de uma empresa, ou para o registro e uso dos nomes dos alunos de uma sala de aula, em uma escola. Sempre que houver a necessidade, você pode alterar/editar os dados em uma LISTA Pré-Definida. Basta clicar sobre as informações contidas de dados, digitar as alterações, e clicar em Adicionar para alterá-los. Preenchimento de Números Consecutivos. “LISTA” Pré-Definida. Alça de Preenchimento. 16 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 3 MANIPULAÇÃO DE “DADOS” 3.1 FORMATAÇÃO DE “DADOS” NA CÉLULA Para aplicar uma Formatação aos “DADOS” inseridos na célula, faça o seguinte: 1 – Clique na Célula que contém os “DADOS” que você deseja Formatar; 2 – Na Guia Página Inicial, no Grupo Células, clique no Botão Formatar e peça a opção Formatar Células; 3 – Na Caixa de Diálogo de Formatar, defina Número, Alinhamento, Fonte, Borda, Cores e a Proteção da Célula; 4 – Você também pode Formatar a Célula, clicando sobre a mesma, e usando os Botões da Guia Página Inicial; 5 – Você pode também Formatar a Célula, clicando com o Botão Direito do Mouse sobre a mesma, e solicitando a opção no Menu de Atalho para Formatar Células; Figura 7 – MS-Excel 2016: Formatando “DADOS” na Célula. Outra forma para definir formatação de “DADOS” na célula é fazendo as seguintes ações e comandos: 1 – Clique na Célula que contém os “DADOS” que você deseja Formatar; 2 – Na Guia Página Inicial, entre os Grupos de Fonte e Alinhamento, você pode definir recursos para Fonte, Estilo, Cor, Alinhamento, Sombreamento, Inclinação, Quebra e Mesclagem de Células; Outra maneira ainda: 1 – Clique na Célula que contém os “DADOS” que você deseja Formatar; 2 – Na Guia Página Inicial, no Grupo Número, clique na seta ao lado de Geral, e escolha o Formato. Você pode escolher entre Geral, Moeda, Contábil, Data, Hora, Porcentagem, Fração e Científico; Outra maneira ainda: 1 – Clique na Célula que contém os “DADOS” que você deseja Formatar; 2 – Na Guia Página Inicial, no Grupo Estilo,clique no Botão Estilos de Célula, e defina o Formato do seu Estilo; 3 – Se desejar, você pode aplicar Formatos de Estilos, um sobre o outro, melhorando ainda mais a sua Formatação, na Célula; Estilos de Célula. Números. Alinhamento. Fonte. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 17 3.1.1 FORMATAR CÉLULAS: Na Janela de Encaixe Formatar Células, você tem as Guias Número, Alinhamento, Fonte, Borda, Preenchimento e para a Proteção de Dados na Célula. Cada uma delas com suas opções de edição e formatação. Na Guia Número, você pode definir se você deseja trabalhar com Número Geral, em Formato de Moeda e Contábil (definindo as casas decimais), em Formato de Data e Hora, em Porcentagem e/ou Fração, no Formato em Especial (para lidar com opções como CEP, CIC e Número de Telefone) e o Personalizado (onde você define o seu formato). Na Guia Alinhamento, você define o Alinhamento na Horizontal e na Vertical para os dados, a Orientação dos dados em Ângulos com Graus, uma Quebra Automática o Texto (dentro da célula), Reduzir o tamanho do conteúdo para se ajustar ao tamanho da célula, e Mesclar Células, além de definir a Direção do Texto. Figura 8 – MS-Excel 2016: Formatar Células: Número e Alinhamento. Formatar Células: Alinhamento. Quebra de Texto na Célula. Orientação do Texto. Formatar Células: Número. Personalizado. Formatos de Números. 18 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 3.1.2 PROTEGER CÉLULAS: O recurso para Proteger Células, na Guia Proteger, na Janela de Encaixe Formatar Células, permite definir quais vão ser as células que serão protegidas ou não, no momento em que você por uma Senha de Proteção à sua Planilha, definindo assim restrições de formatação e edição. Para trabalhar com o recurso de Proteger Células, faça o seguinte: 1 – Selecione o intervalo de células, na sua planilha, que você deseja tornar disponível “editável”; 2 – Clique sobre as Células com o Botão Direito do Mouse. No Menu de Atalho, peça Formatar Células; 3 – Na Janela de Encaixe Formatar Células, na Guia Proteção, desmarque a opção Bloqueadas. Com isso, ao Bloquear a sua Planilha, esse intervalo de células ficará disponível para edição; Figura 9 – MS-Excel 2016: Formatar Células: Proteção. O recurso para poder “Proteger a sua Planilha”, determina em quais células os outros usuários poderão mexer e até mesmo editar. Para poder “Proteger a sua Planilha”, faça o seguinte: 1 – Clique na Guia Revisão, e depois, clique no Botão Proteger Planilha; 2 – Na Janela de Encaixe Proteger Planilha, marque as opções que você deseja que o usuário possa fazer; 3 – Digite a sua Senha, para Proteger/Desproteger a sua Planilha; 4 – Deixe a opção “Proteger a Planilha e o Conteúdo de Células Bloqueadas” marcada; Figura 10 – MS-Excel 2016: Proteger a Planilha. Formatar Células: Proteção. Formatar Células. Botão Direito do Mouse. Células Bloqueadas. Proteger Planilha. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 19 3.2 MANIPULAÇÃO DE “DADOS” MAIOR 3.2.1 CÉLULAS MESCLADAS: A Célula Mesclada é uma célula criada pela combinação de duas ou mais células selecionadas adjacentes. Quando você “MESCLA” as células, na horizontal e/ou na vertical, o seu conteúdo é exibido no intervalo desta Mesclagem. 1 – Selecione duas ou mais Células em sequência, na horizontal ou na vertical; 2 – Na Guia Página Inicial, no Grupo Alinhamento, clique no Botão de Mesclar e Centralizar; As Células serão “Mescladas” em Linhas e/ou Colunas, e o seu conteúdo será centralizado no intervalo; OBS.: A Célula Mesclada, às vezes, fica muito mais difícil de se movimentar, quando você clica na borda da célula com a ponta da Seta do Mouse, e arrasta para outra posição. 3.2.2 LARGURA E ALTURA DA CÉLULA: Uma célula tem, como o seu padrão, 8,43 cm de Largura por 15 cm de Altura. Quando você insere uma informação muito grande na célula, ela não exibe corretamente os “DADOS”, mostrando o símbolo do sustenido (#) no lugar. Então, para ser feito o “Auto - Ajuste” da célula para o seu conteúdo interno, clique duas vezes na linha divisória que há entre as Letras das Colunas ou entre os Números das Linhas, dando o duplo clique (clicando duas vezes) com o Botão Esquerdo do Mouse. Se você quiser, clique com o Botão Direito do Mouse sobre a Linha ou sobre a Coluna, e, no Menu de Atalho, peça a opção de Altura da Linha ou de Largura da Coluna, para definir os ajustes que você quer para a célula, digitando o valor. Figura 11 – MS-Excel 2016: Largura e Altura de Célula. OBS.: Às vezes, quando você insere uma Figura ou uma Imagem, você pode querer “redimensionar” os objetos, querendo ajustá-los diretamente às células, em largura e altura. Para que isso aconteça, mantenha pressionada junto a Tecla ALT, quando você redimensiona o objeto selecionado. Quando você redimensiona a altura e a largura das células, você também altera o tamanho do objeto ajustado nelas. Altura da Linha. Largura da Coluna. Célula com conteúdo muito grande. Auto-Ajuste. 20 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 4 COMPATILHANDO “PASTA DE TRABALHO” 4.1 CRIANDO “PASTA DE TRABALHO” COMPARTILHADA Geralmente, quando temos uma Pasta de Trabalho que será manipulada por mais de uma pessoa, nós podemos torna-la uma “Pasta de Trabalho Compartilhada”. Desta forma, várias pessoas poderão editá-lo, simultaneamente. Aquele usuário que tornar a sua Pasta de Trabalho do tipo Compartilhada, tornar-se-á também seu Administrador, que poderá gerenciar o acesso dos demais participantes do arquivo. 1 – Abra o seu Arquivo de Pasta de Trabalho, ou crie uma “NOVA” Pasta de Trabalho; 2 – Clique na Guia Revisão, e no Grupo Alterações, clique no Botão Compartilhar Pasta de Trabalho; 3 – Na Caixa de Diálogo Compartilhar Pasta de Trabalho, clique na Guia Editar. Marque a Caixa de Seleção “Permitir Alterações por mais de um Usuário ao mesmo tempo”; 4 – Na Guia Avançado, marque as opções que você desejar melhor; Figura 12 – MS-Excel 2016: Compartilhando uma “Pasta de Trabalho”. Quando você salva a sua Pasta de Trabalho Compartilhada, você define o local onde ela será salva. E, depois, copie e cole a sua Pasta de Trabalho Compartilhada em uma Pasta Compartilhada na “REDE”. Qualquer pessoa que tenha acesso a essa Pasta Compartilhada na “REDE”, terá acesso ao arquivo de Pasta de Trabalho Compartilhada. Como Administrador de uma Pasta de Trabalho Compartilhada, você pode determinar restringir acesso aos demais usuários do grupo apenas como pessoas autorizadas, através do uso de uma Senha de Acesso, permitindo assim Bloquear Células e Proteger Planilha e Pasta de Trabalho. OBS.: Ao Compartilhar uma Pasta de Trabalho, alguns comandos e recursos disponíveis do MS-Excel 2016 irão ficar totalmente indisponíveis, como Excluir Planilhas, Mesclar Células, Dividir Células Mescladas, Usar Ferramentas para Fazer Desenhos, Criar Minigráficos, Modificar Minigráficos, entre outras opções. Importante ressaltar que, algumas condições de ajuste e controle perdem a sua liberdade quando teremos então uma Pasta Compartilhada, justamente devido a sua falta de necessidade. E, ficam dispostos apenas os recursos que são mais importantes para o compartilhamento. Compartilhar Pasta de Trabalho. Compartilhar Pasta de Trabalho. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 21 4.2 PROTEGENDO “PASTA DE TRABALHO” COMPARTILHADA Para Protegeruma Pasta de Trabalho Compartilhada é necessário Bloquear Células e Proteger a Planilha, além de adicionar uma Senha de Acesso. Para fazê-lo, faça a seguinte operação: 1 – Clique na Guia Revisão, e no Grupo Alterações, clique no Botão Proteger e Compartilhar a Pasta de Trabalho; 2 – Na Caixa de Diálogo Proteger Pasta de Trabalho Compartilhada, marque a opção “Compartilhar com Alterações de Controle”; 3 – No Campo Senha (opcional), digite a sua Senha de Acesso. Clique no Botão OK; 4 – Na Caixa de Diálogo Confirmar Senha, digite novamente a sua Senha de Acesso; 5 – Salve o seu Arquivo; OBS.: A Senha de Acesso definida deve ser a mesma utilizada por todos os membros do grupo para ter o acesso direto ao arquivo da pasta compartilhada. Figura 13 – MS-Excel 2016: Proteger uma “Pasta de Trabalho” Compartilhada. 4.3 EDITAR “PASTA DE TRABALHO” COMPARTILHADA É possível determinar que a Pasta de Trabalho Compartilhada receba e aceite as alterações de edição feitas pelos usuários membros do grupo de acesso à mesma. Mas para isso, além dos usuários terem a mesma Senha de Acesso, é necessário determinar algo importante. 1 – Com a Planilha da Pasta de Trabalho Compartilhada aberta, clique na Guia Arquivo, e peça Opções; 2 – Na Caixa de Diálogo Opções do Excel, clique na Categoria Geral; 3 – E, na Opção “Personalizar a Cópia do Microsoft Excel” digite o seu nome na Caixa “Nome do Usuário”, para identificar você como membro do grupo; 4 – Depois, clique no Botão OK; Ao fazer isso, automaticamente, todos os usuários passarão a fazer parte do mesmo grupo de acesso e controle para a edição do arquivo de pasta compartilhada. É possível também ter controle total sobre os membros do grupo, saber quem está editando o arquivo e por onde, e excluir membros do grupo. Proteger Pasta de Trabalho Compartilhada. Opção de Senha. Proteger e Compartilhar Pasta de Trabalho. 22 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática Para saber qual usuário membro do grupo está trabalhando com a Pasta de Trabalho Compartilha nesse instante, e para REMOVER um usuário do grupo, faça os seguintes passos: 1 – Com a Planilha da Pasta de Trabalho Compartilhada aberta, clique na Guia Revisão; 2 – No Grupo Alterações, clique no Botão Compartilha Pasta de Trabalho; 3 – Na Caixa de Diálogo Compartilhar Pasta de Trabalho, clique na Guia Editar; 4 – Os nomes dos usuários que estão com a Pasta de Trabalho aberta aparecerão na opção “Quem está com está Pasta de Trabalho aberta”; 5 – Para REMOVER um usuário do grupo, clique no nome do usuário, e clique no Botão Remover Usuário; Figura 14 – MS-Excel 2016: Opções do Excel: Personalizar Cópia do MS-Office: Nome do Usuário. Figura 15 – MS-Excel 2016: Realçar e Controlar Alterações dos usuários. Realçar Alterações Todas as Alterações. Controlar Alterações: Realçar Alterações. Compartilhar Pasta de Trabalho. Remover Usuário. Nome do Usuário. Usuário Selecionado. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 23 OBS.: A ação para “REMOVER” um usuário do grupo, da Pasta de Trabalho Compartilhada, não impedirá o usuário de poder editar novamente na Pasta Compartilhada, visto que, o mesmo, ainda possuirá a Senha de Acesso. 4.4 RESOLVER CONFLITOS Quando for necessário, deveremos Resolver os Conflitos que ocorrem quando usuários diferentes no grupo tentam salvar alterações feitas no mesmo endereço de célula. Geralmente, o MS-Excel 2016 permite manter apenas uma das alterações feitas, por padrão, apenas a primeira alteração. Quando algum outro usuário do grupo for tentar fazer alguma alteração na mesma célula, e tentar salvá-la junto com a Pasta de Trabalho Compartilhada, o MS-Excel 2016 mostrará a Caixa de Diálogo Resolver Conflito. 1 – Na Caixa de Diálogo Resolver Conflitos é mostrada as alterações do usuário atual, e de todos os outros; 2 – Clique no Botão Aceitar as Minhas, para manter as alterações do usuário atual; 3 – Clique no Botão Aceitar as Outras, para manter as alterações de outros usuários do grupo; Figura 16 – MS-Excel 2016: Aceitar e/ou Rejeitar as Alterações dos Usuários. Se houver mais alguma alteração feita por outros usuários do grupo, do tipo “conflitante”, elas irão todas aparecer em sequência, na Caixa de Diálogo Resolver Conflitos. É possível configurar o MS-Excel 2016 para que as alterações feitas pelo usuário atual acabem por substituir todas as outras mudanças feitas pelos outros usuários, sem ser necessário reexibir a Caixa de Diálogo Resolver Conflitos, além de ver todas as outras alterações já feitas. Para isso, basta fazer o seguinte: 1 – Na Guia Revisão, Grupo Alterações, clique no Botão Compartilha Pasta de Trabalho; 2 – Na Caixa de Diálogo Proteger Pasta de Trabalho Compartilhada, clique na Guia Avançadas; 3 – Na Seção Alterações Conflitantes entre Usuários, marque a opção “As Alterações Salvas Prevalecem”. Dê OK; 4 – Depois, ainda na Guia Revisão, Grupo Alterações, clique no Botão Realçar Alterações. 5 – Na Caixa de Diálogo Realçar Alterações, na opção Quando, marque “Todas”. Depois, marque a opção para “Listar as Alterações em uma Nova Planilha”; 6 – Clique no Botão OK; As Alterações Salvas Prevalecem. Realçar Alterações. Todas as Alterações. Controlar Alterações: Aceitar/Rejeitar Alterações. Realçar na Tela. 24 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática Com “ISSO”, na sua Pasta de Trabalho, teremos uma “NOVA” Planilha, chamada Histórico. E, além disso, ao mover o Cursor da Barra de Rolagem na Horizontal para a direita, iremos ver as Colunas “Tipo da Ação” e “Ação Perdida”. As alterações conflitantes que serão mantidas possuirão o valor de “Alteração Aceita”, na Coluna de Tipo de Ação. E, o número que será exibido na Coluna Ação Perdida identifica a Linha que informa as alterações conflitantes que não foram mantidas, incluindo os DADOS excluídos. 4.5 ENCERRAR PASTA DE TRABALHO COMPARTILHADA Para “ENCERRAR” uma Pasta de Trabalho Compartilhada, você deve verificar se todos os usuários que fazem parte do grupo de compartilhamento já fizeram as suas devidas alterações, pois as devidas alterações “não salvas” serão perdidas totalmente. Inclusive, o Histórico de Alterações será perdido após encerrar o compartilhamento. 1 – Abra a Pasta de Trabalho Compartilhada; 2 – Clique na Guia Revisão, no Grupo Alterações, e clique no Botão Compartilhar Pasta de Trabalho; 3 – Na Caixa de Diálogo Compartilhar Pasta de Trabalho, clique na Guia Editar, e veja se você é o único usuário na lista “Quem está com a Pasta de Trabalho aberta”. Se existir, REMOVA esses usuários; 4 – Desmarque a opção de “Permitir Alterações por mais de um Usuário ao mesmo tempo”; 5 – Clique no Botão OK; Figura 17 – MS-Excel 2016: ENCERRAR Pasta de Trabalho Compartilhada. Uma Caixa de Diálogo irá aparecer, informando dos “EFEITOS” que serão causados junto aos outros usuários que ainda formam o grupo de compartilhamento, e sobre a desativação do Histórico de Alterações. É muito importante que a sua Pasta de Trabalho esteja desprotegida, para que você possa ter justamente a opção para “Permitir Alterações por mais de um Usuário ao mesmo tempo” disponível para ser desmarcada. O recurso para o Compartilhamento de Pasta de Trabalho é muito útil, principalmente quando nós temos grupos de trabalho formados por vários usuários, e quando nós temos uma mesma Pasta de Trabalho, no MS-Excel 2016, disponível para realizar cálculos e cumprir metas de negócios. Antes de tudo, um aprendizado para sesaber e trabalhar em grupo, em uma Pasta de Trabalho Compartilhada. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 25 5 FÓRMULAS, FUNÇÕES, FORMATAÇÕES 5.1 FÓRMULAS As Fórmulas são as EQUAÇÕES que podem conter cálculos, retornar dados de resultados, testar condições, e muito e muito mais. Uma Fórmula sempre irá começar com o Sinal de Igual (=). Para executar uma Fórmula faça o seguinte: 1 - Digite na Célula o Sinal de Igual (=); 2 - Digite os números, junto com os seus Operadores Matemáticos. Exemplo: =3+5; 3 - Você também pode usar os endereços (as Referências das Células) nas suas conotações de Fórmulas. Exemplo: =B6+C6; 4 - Com os Operadores Matemáticos, você executa a operação na Fórmula, obtendo os resultados desejados; 5 - Você pode acrescentar outros Símbolos, para implementar as suas Fórmulas. Exemplo: =(B6+C6)*D6; 6 - Você deve se preocupar com a “ORDEM” de Execução dos Operadores Matemáticos (Aritméticos). Exemplo: 1º a Multiplicação, 2º a Divisão, 3º a Adição e em 4º a Subtração; Figura 18 – MS-Excel 2016: Operadores Aritméticos. 5.2 FÓRMULAS & VALORES CONSTANTES É importante ressaltar que uma Célula recebe só dois tipos de informação como seu conteúdo direto e irrestrito. Seriam as Fórmulas e os Valores Constantes (de Texto e de Número). Para adicionar uma Fórmula em uma Célula, basta começar a digitar as informações de dados iniciando pelo Sinal de Igual (=) e “SEM” pôr as Barras de Espaço. Já todo e qualquer Valor Constante posto em uma Célula será toda e qualquer informação de dado que você digitar de texto e/ou de número, diretamente sobre a célula. “ATENÇÃO”! Se você adicionar Barras de Espaço enquanto você digita alguma informação de Fórmula, o MS-Excel 2016 poderá interpretar aquela informação de dado como um Valor Constante de Texto. Fórmula com Referência de Célula. Fórmula com Valores Constantes. Operadores Matemáticos (Aritméticos), Caracteres e Símbolos. 26 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática Por “PADRÃO”, no MS-Excel 2016, todo Valor Constante de Número ficará alinhado à direita, dentro da célula. E, por “PADRÃO”, no MS-Excel 2016, todo Valor Constante de Texto ficará alinhando à esquerda, dentro da célula. Independente disso, você poderá clicar sobre a célula e escolher o alinhamento que você desejar para o conteúdo. 5.3 FUNÇÕES As Funções, no MS-Excel 2016, são as Fórmulas, mas em um critério “mais específico”. Tratam-se de operações já pré-definidas, prontas para serem utilizadas, com o subterfúgio do uso de um ou mais argumentos, que permitem retornar o resultado desejado. As Funções, no MS-Excel 2016, são divididas em Categorias: ▪ Funções de Banco de Dados; ▪ Funções de Contabilidade; ▪ Funções de Data & Hora; ▪ Funções de Pesquisa e de Referência; ▪ Funções de Texto; ▪ Funções de Estatísticas; ▪ Funções de Lógica; ▪ Funções Financeiras; ▪ Funções de Engenharia; ▪ Funções de Informação; ▪ Funções de Matemática e de Trigonometria; ▪ Funções de Cubo; Figura 19 – MS-Excel 2016: Fórmulas & Funções. Para executar uma Função, faça o seguinte: 1 - Digite na Célula o Sinal de Igual (=); 2 - Digite a Letra inicial do Nome da Função, para ver a Lista de Funções disponíveis; 3 - “TODA” Função possui um Nome, e uma Dica de Tela (uma descrição) sobre o seu Conjunto de Argumento; 4 - Cada Função tem definido, dentro dos seus Parênteses, o seu Conjunto de Argumentos; 5 - O MS-Excel 2016 exibe “DICAS”, enquanto você digita os argumentos da Função; 6 - No geral, além dos Parênteses, no Conjunto de Argumentos dentro de uma Função, podem ter: Ponto e Vírgula (separar argumentos), Dois Pontos (separar intervalos), Aspas (textos), entre outros; Lista de Funções. Função SOMA, e o seu Conjunto de Argumentos. AutoSoma. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 27 Outra forma de adicionar Funções seria através da Guia Fórmulas, nos Grupos de Biblioteca de Funções, e através do Botão Inserir Função. Outra forma, também, seria através do próprio Botão Inserir Função, disponível lá na Barra de Fórmulas e Cálculos. Tanto pela Caixa de Inserir Função, quanto pela Guia de Fórmulas, você pode acrescentar as suas Funções, dentro de suas necessidades. E, “AINDA”, colocar Funções dentro de outras Funções. A Sintaxe de uma Função geralmente possui: 1 - Nome da Função, você tem a “LISTA” pressionando SHIFT + F3; 2 - Os Conjuntos de Argumentos da Função, são separados por Ponto e Vírgula, Dois Pontos, Aspas, “TUDO” dentro dos Parênteses; 3 - Como são muitas Funções, geralmente você precisa “praticar” para “lembrar” dos Conjuntos de Argumentos de cada uma delas; Cada Categoria possui muitas e muitas Funções, o que chega a ser difícil saber e conhecer de todas elas, devido ao seu nível de complexidade e pelo o seu controle. Certamente, o melhor, será de você estudar e em conhecer apenas daquelas Funções que você irá mais utilizar no seu dia-a-dia. Mas, apesar disso, caso haja a necessidade de usar alguma Função que você não conheça e/ou não tenha o domínio sobre a mesma, você poderá usar facilmente dos Arquivos de AJUDA relacionados, diretamente no MS-Excel 2016, através da Tecla de Atalho F1. Figura 20 – MS-Excel 2016: Fórmulas & Funções. 5.4 FUNÇÕES ANINHADAS As Funções Aninhadas são quando se dá da necessidade de usar, uma Função, como argumento para outra Função. Quando uma Função é utilizada como argumento de outra Função, ela deve retornar o mesmo tipo de valor utilizado no argumento dela, como VERDADEIRO ou FALSO. Se “ISSO” não acontecer, o MS-Excel 2016 retorna Valor de ERRO, “#VALOR!” Uma Fórmula pode conter até cerca de Sete Níveis de Funções Aninhadas, uma dentro da outra. Por exemplo: =SE(MÉDIA(F2:F6)>50;SOMA(G2:G6);0) aonde as Funções MÉDIA e SOMA são ambas de segundo nível, usadas como Argumento da Função SE. Inserir Função. Função SOMA. Inserir Função. 28 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 5.5 REFERÊNCIAS DE CÉLULAS As Referências de Células são as suas identificações, ligadas aos seus endereços das Células, Planilhas e das Pastas, retornando os Valores e os DADOS neles contidos, a serem utilizados dentro de Fórmulas e Funções. Existem três tipos de Referências de Células: Relativas, Mistas e Absolutas; ▪ Referência Relativa é baseada na posição relativa da Célula utilizada na Fórmula. Ao se mover a Fórmula, a Referência será alterada; ▪ Referência Absoluta é baseada na posição “FIXA” da Célula usada na Fórmula. Ao se mover a Fórmula, a Referência permanece a mesma. Para tornar uma Célula, Planilha ou Pasta, algo “FIXO”, basta pressionar a Tecla de Função F4; ▪ Referência MISTA é baseada na junção, em termos de posição, dos dois outros exemplos de Referências dispostos pelo MS-Excel 2016, podendo ser uma “mistura” entre todos os Modelos de Referência; Figura 21 – MS-Excel 2016: Referências de Células. “TODA” Função possui o seu Conjunto de Argumentos, dentro dos Parênteses. Algumas Funções não possuem Argumentos NENHUM, como temos a Função HOJE, exemplo: =HOJE(), que retorna apenas a Data Atual. Outras Funções, como a Função SE, são Funções de Lógica, que retornam valores VERDADEIROS ou FALSOS, de acordo com os Argumentos que você determinar: =SE(Teste_Lógico;VERDADEIRO;FALSO). Você pode utilizar até 64 Funções SE, ANINHADAS, uma dentro da outra, além de usar outras Funções de Lógica e de Busca e Referência, como PROCV, PROCH, CONT.SE, SOMASE, E, OU. “TUDO”, dentro de outras Funções, permitindocriar NOVAS Fórmulas. Algumas Funções são colocadas apenas em uma única Célula, para construir suas Fórmulas, usando as Referências. A Função CONT.SE só contará o Número de Células dentro de um certo “intervalo” que atenda a um critério: =CONT.SE(Intervalo;Critérios). A Função SOMASE “SOMA”, por exemplo, os valores em um intervalo que atenda a alguns determinados critérios: =SOMASE(Intervalo;Critérios;Soma). Referência “Absoluta”, quando se move a Fórmula, a referência permanece “FIXA”. Referência Relativa. A referência se move junto com a Fórmula. Referência “MISTA”, pressionando a Tecla F4 na Célula B2 ($B$2 ou $B2 ou B$2), quando se move a Fórmula, a referência permanece “FIXA”, apenas onde está $. APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 29 5.6 MANIPULANDO FUNÇÕES Vamos agora trabalhar o conceito para várias Funções que são usadas no MS-Excel 2016, dentro dos seus Conjuntos de Argumentos utilizados em suas operações. Lembrando que nós não iremos trabalhar com todas as Funções que estão dispostas no Aplicativo. Iremos ver apenas as mais usadas no nosso dia-a-dia. Vamos tomar como exemplo uma escola para jovens, em uma sala de aula, “a Escola EDUCAR”. A partir daí, vamos trabalhar muitas das Funções usadas no MS-Excel 2016, dentre os seus muitos recursos, como se diariamente trabalhando com o processo de planilhas. Figura 22 – MS-Excel 2016: Escola EDUCAR. Figura 23 – EXEMPLO: Escola EDUCAR. Muitas dessas Funções serão postas uma dentro da outra, para desenvolvermos no conceito de Função Aninhada, melhorando ainda mais o poder de atuação do Aplicativo. Devemos, com esse aprendizado, procurar memorizar sobre o Conjunto de Argumentos que cada uma das Funções no MS-Excel 2016 possui. Infelizmente, muitos destes Conjuntos de Argumentos precisam ser mesmo memorizados visto que possuem a Lógica de Construção do Excel. Dentro desta lógica, alguns desses argumentos são bem mais simples de serem feitos, e outros são mais complexos e exigem um maior conhecimento. 30 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 5.6.1 Função HOJE, Função MÊS, Função ANO: A Função HOJE serve para retornar a data atual em uma célula. Se o Formato do Número utilizado antes na célula for Geral, então quando usamos a Função HOJE na célula, o MS-Excel 2016 irá transformar o Formato do Número em Data. Então, basta apenas digitar: =HOJE(). A Função HOJE não possui argumentos, dentro dos seus Parênteses. A Função HOJE é bastante útil para quando você precisa usar da data atual na sua planilha, independentemente de quando a sua Pasta de Trabalho estiver aberta. Ou seja, a data atual sempre será atualizada, na Pasta de Trabalho. A Função HOJE também será bastante útil quando você vir precisar saber, por exemplo, qual a idade de uma pessoa, a partir do ano de nascimento de alguém. Para isso, precisamos utilizar do recurso de Funções Aninhadas, que seria por uma Função dentro da outra. Para isso, vamos usar a Função ANO, que retorna o ano de uma certa data, com a Função HOJE dentro dela, menos o ano de nascimento da pessoa: =ANO(HOJE())-1980. Figura 24 – MS-Excel 2016: Função HOJE. Ao adicionar a Função HOJE em uma célula, e inserir a data atual, o usuário pode determinar qual a melhor opção de “formato” para a sua data. Para isso, basta clicar sobre a célula com a data atual, o Botão Direito do Mouse, e peça a opção Formatar Célula. Na Caixa de Diálogo Formatar Células, Categoria: Data, escolha seu formato de data. Figura 25 – MS-Excel 2016: Formatar Células. Inserir Função. Formato do Número. Data Abreviada. Função HOJE APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 31 A Função HOJE possui as suas variantes, que podem ser utilizadas para recuperar certas informações, de acordo com as necessidades que o usuário deseja ter, como pegar apenas a informação do ano, ou do mês. Para isso, faça: ▪ Função MÊS: Retorna o mês atual de uma data qualquer digitada na célula. Ex.: =MÊS(20/10/2016) = 10; ▪ Função ANO: Retorna o ano atual de uma data qualquer digitada na célula. Ex.: =ANO(20/10/2016) = 2016; 5.6.2 Função CONT.VALORES, Função CONT.NÚM, Função CONTAR.VAZIO: A Função CONT.VALORES permite contar o número de células que não estão vazias, em um determinado intervalo de células existentes. Ou seja, indo de uma célula qualquer, e chegando até uma outra célula, em uma “seleção”, quantas células naquele intervalo possuem algum tipo de conteúdo de informação. Figura 26 – MS-Excel 2016: Função CONT.VALORES. É importante salientar que, um intervalo de células consistirá de um endereço de célula inicial, até um endereço de célula final, dividido por dois pontos, e incluindo todas as células disponíveis existentes dentro daquele intervalo. Ex.: (A6:A10), ou seja, o intervalo de células será da Célula A6 até a Célula A10. A Função CONT.VALORES é uma Função de Estatística, e além dela temos outros exemplos de Funções que fazem contagens semelhantes, mudando apenas o propósito pelo qual é feita essa contagem em um intervalo específico. ▪ Função CONT.NÚM: Permite contar o número de células existentes em um determinado intervalo que possuam exclusivamente “números”, como conteúdo das suas células; ▪ Função CONTAR.VAZIO: Permite contar o número de células existentes em um determinado intervalo que estejam “vazias”, sem conteúdo nenhum; Temos também dois outros bons exemplos de Funções Variantes para tratarmos aqui, que fazem parte da Categoria das Funções de Estatísticas, como as Funções CONT.SE e CONT.SES, que são Funções que fazem uma “contagem” de acordo com uma certa condição, determinada pelo o usuário, quando da elaboração da sua Fórmula. Devido ao seu grau de complexidade, nós iremos tratar dessas duas Funções pessoalmente em seus próprios exemplos, futuramente, nesta Apostila. Argumentos da Função. Função CONT.VALORES. Função CONT.VALORES. 32 APOSTILA de MS-Excel 2016 Avançado com VBA | UNIBRATEC - expert em informática 5.6.3 Função LIN & Função COL: A Função LIN permite retornar o número da linha na qual você faz referência, na sua Planilha, no MS-Excel 2016. A Função COL permite retornar o número da coluna, onde você faz referência, na sua Planilha, no MS-Excel 2016. As Funções de LIN & COL são Funções de Pesquisa e Referência. Ex.: =LIN(A1) = 1 | =COL(D10) = 4 (D é a 4ª Coluna). Os Números das Linhas poderão ser “atualizados automaticamente” à medida em que você adiciona novas linhas, à sua Planilha. Para “ISSO”, basta que você selecione o intervalo de dados digitado e transforme-o em uma Tabela. Todas as Tabelas do MS-Excel 2016 possuem uma Condição de Formatação “ÚNICA”, e podem ser de muito úteis e de bastante ajuda na construção de suas Pastas de Trabalho. 5.6.4 Função TEXTO: A Função TEXTO permite que você possa unir/juntar informações de texto e de número na mesma célula, utilizando para isso o Recurso de Concatenação, “&” (Letra E Comercial). Nem sempre o recurso será obrigatoriamente usado. A Função TEXTO é uma das Funções de TEXTO existentes no MS-Excel 2016. Ex.: =TEXTO(HOJE();“DD/MM/AA”) Retorna a data de hoje no formato dia, mês, ano = 12/12/16; Ex.: =“Hoje é: ”&TEXTO(HOJE();“DD/MM/AA”) Retorna o texto “Hoje é: ” com mais a data em dia, mês, ano; OBS.: Veja que a informações de dados de texto devem vir contidas dentro de Aspas Duplas (“”), obrigatoriamente; OBS.: Uma Função também bastante semelhante à Função TEXTO, e que usa do mesmo Recurso de Concatenação, “&”, é a Função CONCATENAR, que também permite juntar informações de textos e de números na mesma célula, mas com a diferença
Compartilhar