Baixe o app para aproveitar ainda mais
Prévia do material em texto
Microsoft Excel 2010 Essencial Aplicado às Unidades Fazendárias EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 2 Índice Revisão de conceitos ..................................................................................................................... 4 Subtotal ..................................................................................................................................... 4 Nomeando um intervalo ........................................................................................................... 6 Referência Relativa.................................................................................................................... 7 Referência Absoluta .................................................................................................................. 8 Referência Mista ....................................................................................................................... 8 Função SE() ................................................................................................................................ 9 Removendo registros duplicados ................................................................................................ 10 Tratamento de erros ................................................................................................................... 11 Validação de dados ..................................................................................................................... 12 Lista de dados .......................................................................................................................... 13 Funções de pesquisa ................................................................................................................... 15 Utilizando a função PROCV() ................................................................................................... 15 Função CORRESP() ................................................................................................................... 17 Banco de Dados ........................................................................................................................... 18 Função BDSOMA() ................................................................................................................... 19 Função BDMÉDIA() .................................................................................................................. 21 Proteção de células e pastas de trabalho ................................................................................... 22 Bloqueando células ................................................................................................................. 22 Configurar para abrir somente leitura .................................................................................... 25 Inserir senha para abrir e/ou editar o arquivo ........................................................................ 27 Teste de hipóteses ...................................................................................................................... 28 Atingindo meta ........................................................................................................................ 28 Tabela de dados ...................................................................................................................... 29 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 3 Cenários ................................................................................................................................... 32 Solver ....................................................................................................................................... 35 Consolidação de planilhas ........................................................................................................... 37 Tabela Dinâmica e Gráfico Dinâmico .......................................................................................... 39 Tabela dinâmica ...................................................................................................................... 39 Gráfico dinâmico ..................................................................................................................... 46 Hiperlinks..................................................................................................................................... 52 Funções de texto ......................................................................................................................... 60 Exportando dados do Excel ......................................................................................................... 61 Importando dados externos ........................................................................................................ 62 De Arquivo Texto ..................................................................................................................... 62 Do access ................................................................................................................................. 65 Introdução às macros .................................................................................................................. 66 Gravando uma macro .............................................................................................................. 68 Atribuindo a macro a um objeto ............................................................................................. 72 Alterando uma macro ............................................................................................................. 73 Excluindo uma macro .............................................................................................................. 75 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 4 Revisão de conceitos Subtotal Utilizado para calcular os subtotais por categorias agrupadas, seguidas, no final, pelo total geral. Prática 1: Calcular os subtotais da planilha. 1. Classifique as informações em ordem crescente do nome do credor. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 5 2. Em seguida deveremos utilizar a função subtotal, localizada no menu “Dados”, grupo “Estrutura de Tópicos”. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 6 Nomeando um intervalo Prática 2: Nomear o intervalo A2:A12 - “Funcionários” e o intervalo C2:D12 – “Cargos. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 7 Referência Relativa Por padrão, o Excel trabalha com referências relativas, ou seja, esta referencia é alterada quando copiamos ou movemos a célula que contem uma fórmula, ajustando a linha e ou a coluna para onde é copiada ou movida. Exemplo: EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 8 Referência Absoluta O endereçamento da célula não é modificado, mantendo-se como original sua linha e sua coluna. Para isso, usamos o símbolo $ antes da linha e $ antes da coluna. Exemplo: $A$5, para onde essa célula for copiada ou movida a referencia absoluta permanecerá, ou seja, A5. Referência Mista Nesse caso é possível tornar absoluto ou a linha ou a coluna. Com relação às referências mistas, estas são utilizadas quando se pretende fixar apenas a coluna ou a linha de uma determinada célula, como por exemplo, em C$3, em que a coluna é relativa e a linha é absoluta. Exemplo:EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 9 Função SE() Esta função verifica se uma condição foi satisfeita e retorna um valor se for verdadeiro ou outro valor, se for falso. É uma função condicional, ou seja, se o teste lógico for verdadeiro, o primeiro valor é assumido, caso contrário (falso) o segundo é assumido. Prática 3: Utilizar a função SE() para retornar a situação de cada aluno, de modo que se saiba se ele foi aprovado ou reprovado. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 10 Removendo registros duplicados Em um conjunto de registros, é possível remover aqueles que são duplicados. Prática 4: Remover os registros duplicados na tabela. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 11 Tratamento de erros Os tipos de erros mais comuns no Excel são os seguintes: • #DIV/0 a. Ocorre quando efetuamos uma divisão por zero, o que caracteriza uma operação inválida. Qualquer fórmula que efetue uma divisão por zero irá retornar o erro #DIV/0. • #N/D a. Erro ao tentar utilizar uma função de pesquisa e referência, cujo valor não foi localizado, ou não indisponível. As funções que podem resultar neste erro são PROCV, PROCH, PROC e CORRESP. • #NOME? a. Ocorre ao fazer referência a uma fórmula, intervalo, célula ou função inexistente. • #NÚM! a. Pode ocorrer quando se insere um argumento inválido para uma função, quando é informado um argumento não-numérico numa quando era esperado um valor numérico, e quando a fórmula EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 12 retorna um valor demasiadamente grande ou pequeno, fora dos limites do Excel • #REF! a. Em geral, ocorre devido a alguma manipulação de exclusão de uma Planilha ou Coluna, alterando a referência anterior. Esse erro também pode ocorrer quando a fórmula resultar uma referência inválida (ex. PROCV). • #VALOR! a. Exibido quando a fórmula incluir células que contêm tipos diferentes de dados. Exemplo: uma ou mais células incluídas em uma fórmula contêm texto e a fórmula executa o cálculo matemático, como por exemplo, SOMA. • ###### a. Ocorre em caso de valor muito grande – com vários dígitos – tiver de ser mostrado em uma coluna que seja larga o suficiente para exibi-lo em sua totalidade ou quando você estiver tratando de horas negativas Função SEERRO() Esta função retorna uma expressão ou símbolo quando, em uma célula, resulta um erro. Argumentos: Valor: corresponde ao cálculo que será realizado ou a função que será aplicada. Valor_se_erro: expressão, número ou símbolo que deverá ser retornado caso o cálculo resultar em erro. Validação de dados Use a validação de dados para controlar o tipo de dados que os usuários inserem em uma célula. Por exemplo, é possível restringir a entrada de dados a certo intervalo de datas, limitar opções usando uma lista ou garantir que EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 13 apenas números inteiros positivos sejam inseridos. Como padrão, o Excel permite que o usuário insira qualquer tipo de informação na célula. Lista de dados Prática 5: Inserir, na célula B2, uma lista suspensa com o “nome” dos funcionários. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 14 Prática 6: Inserir, na célula B3, uma lista suspensa com a “matrícula” dos funcionários. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 15 Funções de pesquisa Utilizando a função PROCV() A função PROCV busca a ocorrência de um texto e retorna outras colunas da mesma linha. Permite que você digite o valor de uma célula para procurar o valor em uma célula correspondente. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 16 Prática 7: Localizar o “cargo” do funcionário “Pedro”. Prática 8: Retornar o “vencimento base” do funcionário “Pedro”. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 17 Função CORRESP() A função CORRESP procura um item especificado em um intervalo de células e retorna a posição relativa desse item no intervalo. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 18 Prática 9: Retornar a posição do registro ‘Pedro’ na tabela. Banco de Dados Coleção de dados armazenados eletronicamente, no Excel, uma base de dados é uma tabela. As colunas da tabela consideramos campos e as linhas, os registros. Cada campo deve ser representado por um nome e não por um valor. Campos não podem ter o mesmo nome. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 19 Função BDSOMA() Adiciona os números em um campo (coluna) de registros em uma lista ou banco de dados que coincidem com as condições especificadas. Sintaxe BDSOMA(banco_dados;campo;critérios) Banco de dados: é o intervalo de células da lista ou do banco de dados. Um banco de dados é uma lista de dados relacionados em que as linhas de informações relacionadas são os registros e as colunas de dados são os campos. A primeira linha da lista contém os rótulos de cada coluna. Campo: indica a coluna que será usada na função. O campo pode ser dado como texto com o rótulo da coluna entre aspas, como "Idade" ou "Rendimento", ou como um número (sem aspas) que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante. Critérios: são os intervalos de células que contém as condições especificadas. Você pode usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos um rótulo de coluna e pelo menos EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 20 uma célula abaixo do rótulo de coluna para especificar uma condição para a coluna. Utilizaremos o arquivo abaixo para utilizar a função Bdsoma(). Note que utilizaremos a planilha “Bdsoma” para inserir a função. Prática 10: Calcular o montante do “valor transferido total” utilizando-se como critério o beneficiário “Instituto Vital Brasil” e o concedente “Fundo Nacional de Saúde/MS”. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 21 Função BDMÉDIA() Prática 11: Utilizando-se o mesmo beneficiário e o mesmo concedente da prática anterior, calcular a média do “valor transferido total”. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 22 Outras funções utilizando Banco de Dados e a similaridade com as demais funções do Excel: Função Função B...() cont.núm() Bdcontar() cont.valores() Bdcontara() máximo() Bdmáx() mín() Bdmín() Proteção de células e pastas de trabalho Bloqueando células Ao bloquear células, evitamos que fórmulas ou dados sejam alterados indevidamente. O procedimento garante segurança para informações importantes. Prática 12: Desbloquear toda a planilha, com exceção do intervalo C3:C5, campos estes que retornam o valor do INSS, Base de Cálculo do IR e IRRF. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 23 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 24 Prática 13: Após bloquear o intervalo solicitado, proteger a planilha, de modo a evitar que o usuário altere suas informações.EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 25 Configurar para abrir somente leitura Prática 14: Configurar a planilha para abrir no modo “recomendável somente leitura”. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 26 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 27 Inserir senha para abrir e/ou editar o arquivo Prática 15: Configurar para abrir a planilha somente com senha. Configurar também que o usuário digite senha caso ele queira editar o arquivo, caso contrário ele abrirá no modo somente leitura. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 28 Teste de hipóteses O teste de hipóteses é o processo de alteração dos valores nas células para ver como essas alterações afetarão o resultado das fórmulas na planilha. Existem três tipos de ferramentas de teste de hipóteses no Excel: cenários, tabelas de dados e Atingir Meta. Cenários e tabelas de dados usam conjuntos de valores de entrada e fazem uma projeção progressiva para determinar os possíveis resultados. O recurso Atingir Meta é diferente de cenários e tabelas de dados, pois utiliza um resultado e faz uma projeção regressiva para determinar os possíveis valores de entrada que produziram esse resultado. Atingindo meta Encontra o resultado desejado ajustando um valor de entrada. Prática 16: Calcule o valor do salário bruto, de modo que o salário líquido seja de R$ 8.000,00. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 29 Tabela de dados Uma tabela de dados é um intervalo de células que mostra como a alteração de uma ou duas variáveis nas suas fórmulas irá afetar os resultados dessas fórmulas. Tabelas de dados fornecem um atalho para o cálculo de vários resultados em uma operação e uma maneira de exibir e comparar os resultados de todas as diferentes variações na sua planilha. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 30 Prática 17: Retorne o custo total de acordo com as variáveis “preço unitário” e “quantidade” apresentadas na planilha. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 31 Prática 18: Retorne o valor da prestação mensal, conforme as variáveis “taxa de juros” e “quantidade de meses”. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 32 Cenários Cenários servem para simularmos variadas situações baseados em valores. Prática 19: Inserir um resumo dos valores de INSS, IRRF e salário líquido variando conforme os salários líquidos. Cenário 1: R$ 1.200,00 – Cenário 2: R$ 2.500,00 – Cenário 3: R$ 5.000,00 – Cenário 4: R$ 8.000,00. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 33 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 34 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 35 Solver A função do solver é um teste de hipóteses. Com ela, é possível encontrar o valor ideal para uma situação. Ele ajusta automaticamente os valores de determinadas células as quais são consideradas variáveis. É um dos suplementos do Excel e geralmente vem desabilitado. Logo, é necessário habilitá-lo para a sua utilização. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 36 Prática 20: Definir o objetivo de R$ 40.000,00 para a célula E14, utilizando-se como restrições que o valor unitário do Desktop seja menor ou igual a R$ 1.900,00 e o valor unitário do notebook seja menor ou igual a R$ 1.600,00. Após aberta a janela conforme a figura abaixo, basta clicar sobre o comando que se deseja incluir. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 37 Consolidação de planilhas Para resumir e relatar os resultados de planilhas separadas, você pode consolidar dados de cada planilha separada em uma planilha principal. As planilhas podem estar na mesma pasta de trabalho que a planilha principal ou em outras pastas de trabalho. Prática 21: Consolidar as informações dos meses de janeiro, fevereiro e março/2014. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 38 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 39 Tabela Dinâmica e Gráfico Dinâmico Tabela dinâmica A tabela dinâmica é uma ferramenta interativa e extremamente útil para análise de dados e tomada de decisões, cruzando informações de maneira rápida e flexível. Com ela, você pode resumir centenas de informações de forma clara e objetiva e assim facilitar a análise dessas informações, gerando rapidamente novas tabelas onde serão analisadas de várias formas diferentes. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 40 Prática 22: Inserir uma tabela dinâmica de modo a retornar o repasse total (janeiro a dezembro, sem juros e mora) por região. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 41 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 42 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 43 Prática 23: Inserir uma tabela dinâmica de modo a retornar o total geral por tipo de tributo. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 44 Prática 24: Inserir uma tabela dinâmica de modo a retornar o montante do repasse do principal e o do montante de juros e mora, por município. Prática 25: Inserir uma tabela dinâmica de modo a retornar o montante do repasse do principal nos meses de janeiro, fevereiro e março para os municípios de Macaé, Niterói, Rio de Janeiro e São Gonçalo. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 45 Prática 26: Inserir uma tabela dinâmica de modo a retornar o montante do repasse do principal nos meses de janeiro, fevereiro e março, por tipo de tributo. Prática 27: Inserir uma tabela dinâmica de modo a retornar o montante do repasse do principal, a média, o repasse máximo e o repasse mínimo, por tipo de tributo, considerando-se o período de janeiro a dezembro. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 46 Gráfico dinâmico Utilizando esta ferramenta, podemos aplicar filtros e mover campos de posição de tal maneira que o gráfico é dinamicamente alterado para refletir os filtros e/ou alterações efetuadas. Prática 28: Inserir um gráfico dinâmico, de modo a retornar o montante do repasse, em janeiro, por região (excluindo-se a região metropolitana). EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 47 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 48 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 49 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 50 Prática 29: Inserir um gráfico dinâmico, de modo a visualizar o montante repassado, por tributo, nos meses de janeiro, fevereiro e março.EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 51 Prática 30: Inserir um gráfico dinâmico, de modo a visualizar o montante repassado aos municípios de Belfort Roxo, Niterói, Nova Iguaçu, São João de Meriti e Saquarema, nos meses de janeiro, fevereiro e março. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 52 Hiperlinks Para obter acesso rápido às informações relacionadas em outro arquivo ou em uma página da Web, você pode inserir um hiperlink em uma célula da planilha. Prática 31: Inserir, na planilha “índice” hiperlinks para as demais planilhas da pasta de trabalho. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 53 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 54 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 55 Prática 32: Inserir um hiperlink para o site da “Educação Fiscal”, localizado no portal da Secretaria de Estado de Fazenda. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 56 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 57 Prática 33: Inserir um hiperlink com o intuito de enviar um e-mail para o Grupo de Educação Fiscal. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 58 Prática 34: Inserir um hiperlink para o usuário inserir informações em um novo arquivo. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 59 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 60 Funções de texto Prática 35: Utilizar as funções de texto discriminadas na coluna “B” da planilha. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 61 Exportando dados do Excel Para exportar dados do Excel para um arquivo de texto, use o comando Salvar como. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 62 Geralmente os gerenciadores de Banco de dados leem arquivos csv e texto. Importando dados externos De Arquivo Texto EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 63 Prática 36: Importar o arquivo de texto com as informações acerca dos pagamentos efetuados a fornecedores. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 64 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 65 Do access Prática 37: Importar o arquivo gerado no programa Access. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 66 Introdução às macros Primeiramente, deveremos habilitar as funcionalidades de macro na guia desenvolvedor: EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 67 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 68 Gravando uma macro Uma macro é uma sequência de comandos e funções armazenados em um módulo do Visual Basic for Applications - VBA e pode ser executada sempre que você precisar executar uma tarefa. Quando você grava uma macro, o Excel armazena informações sobre cada etapa realizada à medida que você executa uma sequência de comandos. Existem duas maneiras distintas para a criação de uma macro: • Podemos usar o gravador de Macros: Nesse caso o Excel grava cada uma das ações que farão parte da Macro e transforma essas ações nos comandos VBA equivalentes. Quando a macro for executada, os comandos VBA é que serão efetivamente executados. Cada comando VBA corresponde a uma ação efetiva da macro. • Criar a Macro usando VBA: A partir do momento em que você domina a linguagem VBA, poderá criar a macro digitando os comandos VBA necessários. Isso é feito usando o Editor de VBA. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 69 Prática 38: Gravar uma macro para formatar a célula com preenchimento em amarelo, cor da fonte vermelho e negrito. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 70 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 71 Utilizando-se a tecla de atalho, CTRL+J: EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 72 Atribuindo a macro a um objeto Prática 39: Atribuir a macro “Sorteio” a um objeto. EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 73 Alterando uma macro Depois de gravar uma macro, você poderá exibir o código da macro com o Editor do VBA para corrigir erros ou alterar a função da macro. O Editor do VBA é um programa criado para facilitar a escrita e a edição de código de macro para principiantes e fornece bastante Ajuda on-line. Você não precisa aprender a programar ou a usar a linguagem do Visual Basic para fazer alterações simples nas suas macros. Na Figura a seguir temos um exemplo de código associado com uma Macro. Esse é um exemplo de código VBA: EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 74 EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 75 Excluindo uma macro EFAZ – Curso de Excel Essencial Aplicado Às Unidades Fazendárias 76
Compartilhar