Baixe o app para aproveitar ainda mais
Prévia do material em texto
AN02FREV001/REV 4.0 1 PROGRAMA DE EDUCAÇÃO CONTINUADA A DISTÂNCIA Portal Educação CURSO DE MICROSOFT® OFFICE EXCEL 2010 AVANÇADO Aluno: EaD - Educação a Distância Portal Educação AN02FREV001/REV 4.0 2 CURSO DE MICROSOFT® OFFICE EXCEL 2010 AVANÇADO MÓDULO I Atenção: O material deste módulo está disponível apenas como parâmetro de estudos para este Programa de Educação Continuada. É proibida qualquer forma de comercialização ou distribuição do mesmo sem a autorização expressa do Portal Educação. Os créditos do conteúdo aqui contido são dados aos seus respectivos autores descritos nas Referências Bibliográficas. AN02FREV001/REV 4.0 3 SUMÁRIO MÓDULO I 1 INTRODUÇÃO 2 OBJETIVOS 3 O EXCEL 2010 E AS FUNÇÕES 4 CONSTRUINDO A PLANILHA FOLHA DE PAGAMENTO 4.1 OS AJUSTES DAS TABELAS DE PESQUISA E DE OUTROS CAMPOS 4.2 CÁLCULO DOS DEMAIS CAMPOS 4.2.1 Função Lógica De Conjunção – E( ) 4.2.2 Função Lógica De Conjunção – OU( ) 4.2.3 Função Lógica De Negação – Não ( ) 5 OUTRAS OPERAÇÕES COM FUNÇÕES 5.1 FUNÇÃO =ABS( ) 5.2 FUNÇÃO =AGORA( ) 5.3 FUNÇÃO =CONT.SE( ) 5.4 FUNÇÃO =FATORIAL( ) 5.5 FUNÇÃO =POTÊNCIA( ) 5.6 FUNÇÃO =SOMA( ) 6 ERROS OCORRIDOS NO USO DE FUNÇÕES 7 EXERCÍCIOS MÓDULO II 8 OBJETIVOS 9 AS CARACTERÍSTICAS DE UMA BASE DE DADOS 9.1 A CLASSIFICAÇÃO DE REGISTROS 9.2 AS OPERAÇÕES DE BUSCA E TROCA 9.3 OS RELATÓRIOS DE UMA BASE DE DADOS 9.4 OS EXTRATOS DE UMA BASE DE DADOS 9.5 COMO UTILIZAR A PESQUISA DO TIPO “E” AN02FREV001/REV 4.0 4 9.6 COMO UTILIZAR A PESQUISA DO TIPO “OU” 9.7 USO DE CAMPO CALCULADO NA EXTRAÇÃO DE DADOS 9.8 USO DE SUBTOTAIS 9.9 AS FUNÇÕES DE BASE DE DADOS 10 EXERCÍCIOS MÓDULO III 11 OBJETIVOS 12 TRABALHANDO COM TABELAS DE GRÁFICOS DINÂMICOS 12.1 COMO ATUALIZAR UMA TABELA DINÂMICA 12.2 A ALTERAÇÃO DE ELEMENTOS 12.2.1 Alteração Da Exibição Dos Valores 12.2.2 Os Relatórios Baseados Em Detalhes De Valores 12.3 A UTILIZAÇÃO DE GRÁFICO DINÂMICO 13 EXERCÍCIOS 14 ANÁLISE DE DADOS 14.1 UTILIZAÇÃO DE CENÁRIOS 14.2 ATINGIR METAS 14.3 AUDITOR DE FÓRMULAS 14.4 GERENCIAR EXIBIÇÕES 15 IMPORTAÇÃO DE DADOS DA INTERNET 16 SOLVER 17 EXERCÍCIOS MÓDULO IV 18 OBJETIVOS 19 APRESENTANDO AS MACROS E O AMBIENTE VBA 19.1 A ATIVAÇÃO DA GUIA DESENVOLVEDOR DO EXCEL 2010 19.2 OS ARQUIVOS DO EXCEL 2010 19.3 OS TIPOS DE MACRO 19.3.1 As Macros De Comando 19.3.2 As Macros De Funções Definidas Pelo Usuário AN02FREV001/REV 4.0 5 19.3.3 As Macros De Sub-Rorina 19.4 A NECESSIDADE DE PLANEJAR UMA MACRO 19.5 ESTUDANDO O AMBIENTE VISUAL BASIC 19.5.1 A Construção De Uma Macro 19.6 CARACTERÍSTICAS TÍPICAS DE MACRO EM VBA 19.6.1 Linhas De Comentários 19.6.2 Os Comandos Sub/End Sub 19.6.3 Nome Do Procedimento 19.6.4 Código-Fonte Do Procedimento 19.7 EXECUTAR UMA MACRO 19.7.1 Macros De Endereço Absoluto E Relativo 19.7.2 Macro De Endereço Absoluto 19.7.3 Macro De Endereço Relativo 19.8 O AMBIENTE DO VISUAL BASIC 19.8.1 Caixa Objeto 19.8.2 Caixa Procedimento/Evento 19.8.3 Barra De Divisão 19.8.4 Barra Do Indicador De Margem 19.8.5 Ícone Exibir Procedimento 19.8.6 Ícone Exibir Módulo Completo 20 EXERCÍCIOS 21 TÉCNICA DE PROGRAMAÇÃO NO EXCEL 2010 21.1 CONCEITOS BÁSICOS 21.1.1 Procedimentos 21.1.2 Instruções 21.1.3 Objetos 21.1.4 Propriedades 21.1.5 Métodos 21.1.6 Funções 21.1.7 Valores de Uma Propriedade 21.1.8 Múltiplos Métodos e Propriedades 21.1.9 Hierarquia das Propriedades 21.2 VARIÁVEIS, CONSTANTES E ARITIMÉTICAS AN02FREV001/REV 4.0 6 21.3 TIPOS DE DADOS 21.4 ENTRADA DE DADOS 21.5 DESVIOS CONDICIONAIS 21.6 LAÇOS 21.7 DEPURAR MACROS 22 EXERCÍCIOS MÓDULO V 23 OBJETIVOS 24 CARACTERÍSTICA FUNCIONAL 25 COMO DESENVOLVER FUNÇÕES COM O VB 25.1 A VERIFICAÇÃO DE FUNÇÕES 26 EXERCÍCIOS 27 AS MACROS COM O EXCEL 2010 27.1 DEFININDO A PLANILHA 27.2 OBSERVANDO ÁREA DE RECEPÇÃO DOS DADOS 27.3 A MACRO PARA RECEPÇÃO DOS DADOS 27.4 A MACRO PARA TRANSPORTE DOS DADOS 27.5 A MACRO PARA CADASTRO DOS DADOS 27.6 A MACRO PARA ORDENAR DADOS 28 AS PRÓXIMAS NOVIDADES DA NOVA VERSÃO DO EXCEL 2013 REFERÊNCIAS BIBLIOGRÁFICAS AN02FREV001/REV 4.0 7 Organização O Curso de Microsoft Office Excel 2010 Avançado está organizado em diversos níveis de aprendizagens, proporcionando ao participante um aproveitamento teórico/prático mais dinâmico. Ao longo do conteúdo, serão apresentados diversos ícones que orientará o participante ao cumprimento de diversas tarefas fundamentais. Ícone Descrição Aplicação de exercício. Observação ao que está sendo citado. Praticar a teoria. Atenção ao que está sendo citado. AN02FREV001/REV 4.0 8 Módulo 1 1 INTRODUÇÃO O principal foco deste treinamento é o estudo da parte avançada dos recursos da planilha eletrônica Excel em sua versão 2010. Não se pretende gastar tempo com noções básicas de Excel, mas sim ganhar tempo com os estudos de novos e avançados recursos. Neste módulo inicial, vamos tentar resgatar um pouco sobre a utilização de algumas funções de cálculo, o seu emprego e demais operações com funções. Tenha a certeza de que você aprenderá muito e que as suas atividades com planilhas de cálculos nunca mais serão as mesmas. 2 OBJETIVOS Revisar algumas funções do Excel 2010. Identificar a ajuda das funções do Excel 2010. Construir a planilha de folha de pagamento. AN02FREV001/REV 4.0 9 3 O EXCEL 2010 E AS FUNÇÕES Não há dúvida de que o Excel 2010 é uma poderosa ferramenta para a realização de cálculos, sejam eles simples e aqueles mais sofisticados. As funções do Excel 2010 são composições de fórmulas predefinidas para a realização de cálculos das mais variadas formas com extrema facilidade. As funções disponíveis no Excel 2010 são muito similares às funções embutidas e existentes nas calculadoras científicas para as mais diversas aplicações. Podemos dividir as funções do Excel 2010 em subcategorias e assim classificá-las: Funções de suplemento e automação; Funções de gerenciamento de banco de dados ou listas; Funções de engenharia; Funções de informações; Funções de pesquisa e referência; Funções estatísticas; Funções cubo; Funções de data e hora; Funções financeiras; Funções lógicas; Funções matemáticas e trigonométricas; Funções de textos e dados. Uma função tem um nome de identificação que pode ser digitado com ou sem argumentos (parâmetros). AN02FREV001/REV 4.0 10 As funções sem argumentos são representadas por palavras seguidas dos parênteses sem a menção de conteúdo como argumento dentro desses símbolos. Exemplo de uma função sem argumento: =FUNÇÃO( ) As funções com argumento são acompanhadas de algum conteúdo escrito dentro dos parênteses. Exemplo de uma função com argumento: =FUNÇÃO( argumento1; argumento2; ....; argumentoN) Os argumentos, assim como o nome de identificação de uma função, a serem fornecidos à planilha podem ser digitados tanto em caracteres maiúsculos como em caracteres minúsculos. Para a utilização de funções no Excel 2010, é necessário observar as seguintes regras: Sempre que uma função necessitar de argumentos, eles devem estar mencionados dentro dos parênteses. Só pode ser utilizada uma única função por célula, a qual não pode ultrapassar o limite de 256 caracteres. Não podem ser informados espaços em branco entre os argumentos. Para separar os argumentos, deve ser utilizada uma vírgula (,), um ponto(.) ou um ponto e vírgula (;), dependendo da configuração de região e idioma do sistema operacional. Normalmente se utiliza o ponto e vírgula (;) quando a configuração do sistema está para o idioma português (Brasil). AN02FREV001/REV 4.0 11 Os argumentos de uma função podem ser fornecidos como valores ou fórmulas. As fórmulas podem conter outras funções. Uma função que tem como argumento outra função é conhecida como função aninhada. Só podem ser aninhadas até sete níveis de funções em uma fórmula. Procedimentos práticos: Para que você possa obter maiores informações e exemplos de uso das funções do Excel 2010, pressione a tecla de função <F1> (“Ajuda”) para que o modo ajuda do Excel 2010 seja ativado. Abaixo da barra de ferramentas há um campo onde se pode digitar o que se deseja pesquisar. Assim sendo, digite nesse campo a palavra “funções” e em seguida dê um clique com o ponteiro do mouse sobre o botão Pesquisar. Observe o modelo: AN02FREV001/REV 4.0 12 FIGURA 01 - AJUDA DO EXCEL COM PESQUISA SOBRE FUNÇÕES FONTE: Arquivo pessoal do autor (Renato Nicodemos). A partir deste ponto basta acionar os links (pontos de ligação) apresentados na tela. Um link importante que pode ser inicialmente consultado e estudado, principalmente por usuários mais antigos de outras versões, é “O que há de novo: alterações feitas em funções do Excel”. AN02FREV001/REV 4.0 13 4 CONSTRUINDO A PLANILHA DE FOLHA DE PAGAMENTO A planilha de folha de pagamento que será desenvolvida é uma simulação que está muito longe de ser considerada para a implementação real em uma empresa de qualquer porte, mesmo pequenas, médias ou microempresas, pois não estão sendo levados em consideração diversos fatores e condições contábeis e legais. Assim sendo, para que essa planilha possa ser utilizada profissionalmente, deve ser apreciada por um profissional da área de contabilidade para as devidas orientações técnicas. Procedimentos práticos: Passo 01: Execute o Excel 2010. Passo 02: Posicione o cursor na célula A1 e digite o seguinte título: “Grupo Simulação Ltda. – Folha de Pagamento”. Altere a largura dessa coluna para o tamanho 23 e formate o texto em negrito e sublinhado. Passo 03: Posicione o cursor na célula A3 e digite o texto: “Salário mínimo”. Não efetue nenhuma formatação nessa célula. Passo 04: Posicione o cursor na célula B3 e digite o valor fictício de salário mínimo na casa de 724,00. Formate essa célula como número com duas casas decimais e com separadores de milhares. Passo 05: Coloque o cursor na célula A5 e digite o título: “Funcionário”. Aplique o efeito de formatação negrito. Passo 06: Posicione o cursor na célula B5 e digite o título: “Cód.” (código do cargo). Altere a largura dessa coluna para o tamanho 7 e aplique o efeito negrito. AN02FREV001/REV 4.0 14 Passo 07: Leve o cursor até a célula C5 e digite o título: “Cargo”. Depois altere a largura dessa coluna para o tamanho 21, deixando o formato do texto em negrito. Passo 08: Leve o cursor até a célula D5 e digite o título: “Nr. Sal.” (número de salários mínimos recebidos). Em seguida, altere a largura dessa coluna para o tamanho 7, deixando o formato do texto em negrito. Passo 09: Coloque o cursor na célula E5 e digite o título: “Sal. Bruto” (salário bruto). Altere a largura dessa coluna para o tamanho 10 deixando o formato de texto em negrito. Passo 10: Posicione o cursor na célula F5 e digite o título: “INSS”. A largura dessa coluna deve ser definida com o tamanho 9, deixando o formato de texto em negrito. Dê uma observada na montagem de sua planilha, ela deve estar similar ao modelo: FIGURA 02 - TÍTULOS DE COLUNAS APLICADOS PARCIALMENTE FONTE: Arquivo pessoal do autor (Renato Nicodemos). Passo 11: Posicione o cursor na célula G5 e digite o título: “Dep.” (número de dependentes). Altere a largura dessa coluna para o tamanho 5 deixando o formato de texto em negrito. Passo 12: Leve o cursor até a célula H5 e digite o título: “Sal. Fam.” (salário família). Altere a largura dessa coluna para o tamanho 10, deixando o formato do texto em negrito. AN02FREV001/REV 4.0 15 Passo 13: Posicione o cursor na célula I5 e digite o título: “IRRF” (Imposto de Renda Retido na Fonte). Altere a largura dessa coluna para o tamanho 9, deixando o formato do texto em negrito. Passo 14: Posicione o cursor na célula J5 e digite o título: “Tot. Desc.” (total de descontos). Altere a largura dessa coluna para o tamanho 10, deixando o formato do texto em negrito. Passo 15: Posicione o cursor na célula K5 e digite o título: “Sal. Líq.” (salário líquido). Altere a largura dessa coluna para o tamanho 10, deixando o formato do texto em negrito. Dê uma observada na montagem de sua planilha, ela deve estar similar ao modelo: FIGURA 03 - TÍTULOS DAS COLUNAS FINALIZADOS FONTE: Arquivo pessoal do autor (Renato Nicodemos). Passo 16: Posicione o cursor na célula A22 e digite o título: “Total de Salários Pagos”, deixando o formato do texto em negrito. Passo 17: Posicione o cursor na célula A23 e digite o título: “Média de Salários Pagos”, deixando o formato do texto em negrito. AN02FREV001/REV 4.0 16 Passo 18: Posicione o cursor na célula A24 e digite o título: “Maior Salário Pago”, deixando o formato do texto em negrito. Passo 19: Posicione o cursor na célula A25 e digite o título: “Menor Salário Pago”, deixando o formato do texto em negrito. Compare se os procedimentos anteriores correspondem ao mesmo formato do modelo: FIGURA 04 - MODELO DE PLANILHA DE FOLHA DE PAGAMENTO FONTE: Arquivo pessoal do autor (Renato Nicodemos). AN02FREV001/REV 4.0 17 4.1 OS AJUSTES DAS TABELAS DE PESQUISAS E OUTROS CAMPOS A partir daqui desenvolveremos as tabelas de pesquisas a serem utilizadas na planilha de folha de pagamento. Procedimentos práticos: Passo 01: Digite a listagem de funcionários e de seus códigos de cargos, comparando a sua entrada de dados com o modelo abaixo que mostra a disposição dessas informações na planilha de folha de pagamento a partir da célula A6. FIGURA 05 - DISPOSIÇÃO DAS INFORMAÇÕES NA PLANILHA FOLHA DE PAGAMENTO FONTE: Arquivo pessoal do autor (Renato Nicodemos). AN02FREV001/REV 4.0 18 Funcionário: é a lista de dezesseis funcionários que faz parte da folha de pagamento. Cód.: é o código de cada funcionário que deverá ser fornecido, o qual deve estar entre 1 e 16. Passo 02: Digite a Tabela de Cargos e Salários a partir da célula M5, pois ela é a base para diversos cálculos na planilha, observe o modelo: FIGURA 06 - TABELA DE CÓDIGOS, CARGOS E SALÁRIOS FONTE: Arquivo pessoal do autor (Renato Nicodemos). Passo 03: Digite a partir da célula Q5 a Tabela de INSS conforme o modelo: FIGURA 07 - TABELA DE INSS FONTE: Arquivo pessoal do autor (Renato Nicodemos). AN02FREV001/REV 4.0 19 Passo 04: Digite a partir da célula U5 a tabela para os devidos cálculos do IRRF, conforme o modelo: FIGURA 08 - TABELA IRRF FONTE: Arquivo pessoal do autor (Renato Nicodemos). Passo 05: A partir do momento em que as tabelas de auxílio estão prontas na planilha, é necessário continuar a montagem. Vá até a célula G6 e digite o número de dependentes de cada funcionário na coluna Dep.. Observe o modelo: FIGURA 09 - COLUNA DE DEPENDENTES FONTE: Arquivo pessoal do autor (Renato Nicodemos). AN02FREV001/REV 4.0 20 Faça uma comparação com os modelos abaixo: FIGURA 10 - PLANILHA DE FOLHA DE PAGAMENTO FIM DO MÓDULO IFONTE: Arquivo pessoal do autor (Renato Nicodemos). AN02FREV001/REV 4.0 21 FIGURA 11 - ÁREA RESERVADA ÀS TABELAS DE PESQUISA FONTE: Arquivo pessoal do autor (Renato Nicodemos). 4.2 CÁLCULOS DOS DEMAIS CAMPOS Os campos seguintes serão completados com as informações já dispostas e devidamente preparadas na planilha de folha de pagamento, de tal modo que para cada um deles há um tipo de cálculo diferente, obedecendo às características particulares. Procedimentos práticos: Passo 01: O campo Cargo tem por finalidade apresentar o nome do cargo a partir do código fornecido nas células da coluna “Cód.”. Assim sendo, será utilizada uma fórmula baseada na função =PROCV( ). Na célula C6 informe a função: AN02FREV001/REV 4.0 22 =PROCV(B6M$6;O$20;2) Onde: B6 Célula que possui o número referente ao Código. M$6:O$22 Faixa de células onde se encontra a tabela de Código, Cargo e Salário. 2 É a segunda coluna que deseja pesquisar dentro da tabela que está na faixa M$6:O$22, ou seja, pretende-se retornar o nome do Cargo correspondente ao Código localizado. Todavia, pode acontecer de ser fornecido um valor de código que não existe na tabela. Neste caso, a fórmula deve prever essa situação e alertar o usuário quanto ao erro. A fórmula da célula C6 dever ser efetivamente digitada como: =SE(OU(B6<1;B6>15);”ERRO!!!”;PROCV(B6;M$6:O$20;2)) Onde: OU(B6<=0;B6>=15) Verifica se o valor do código é menor que 1 ou maior que 15. “ERRO!!!” É a mensagem que se apresenta caso os números realmente sejam menores que 1 e maiores que 15. PROCV(...) É a resposta desejada e apresentada quando o valor fornecido for válido. Abrindo-se parênteses na apresentação do tema, cabe apresentar detalhes sobre o uso de funções lógicas no Excel 2010. Observe que a fórmula anterior usa-se a função OU( ) que é uma função lógica. Além da função lógica OU( ), encontram-se ainda as funções lógicas E( ) e NÃO( ). As funções lógicas E( ) e OU( ) são utilizadas quando há a necessidade de avaliar mais de uma condição para a tomada de uma decisão. É possível avaliar de 1 até 256 condições como argumentos dessas condições. AN02FREV001/REV 4.0 23 A função lógica E( ) é de conjunção e a função lógica OU( ) é de disjunção inclusiva. Para entender melhor estas questões, observe as tabelas verdade em seguida para cada uma dessas funções. 4.2.1 Função Lógica de Conjunção – E( ) Do ponto de vista filosófico, a lógica de conjunção é a relação entre duas ou mais proposições (entende-se como proposição como sendo condição) que geram um resultado lógico verdadeiro quando todas as proposições, sem exceção, forem verdadeiras. Observe a tabela seguinte: Tabela verdade do operador lógico de conjunção Condição 1 Condição 2 Resultado lógico Verdadeiro Verdadeiro Verdadeiro Verdadeiro Falso Falso Falso Verdadeiro Falso Falso Falso Falso 4.2.2 Função Lógica de Conjunção – OU( ) Do ponto de vista filosófico, a lógica de disjunção inclusiva é a relação lógica entre duas ou mais proposições de tal modo que seu resultado lógico será verdadeiro quando pelo menos uma das proposições for verdadeira. Observe a tabela seguinte: AN02FREV001/REV 4.0 24 Tabela verdade do operador lógico de disjunção inclusiva Condição 1 Condição 2 Resultado lógico Verdadeiro Verdadeiro Verdadeiro Verdadeiro Falso Verdadeiro Falso Verdadeiro Verdadeiro Falso Falso Falso A função lógica NÃO( ) opera apenas com um argumento e tem por finalidade inverter o resultado lógico da condição a ela atribuída. Assim sendo, se a condição for verdadeira, a função NÃO( ) fará com que essa condição seja considerada falsa, mas se o valor da condição for falso, a função NÃO( ) fará com que essa condição seja considerada verdadeira. 4.2.3 Função Lógica de Negação – NÃO( ) Do ponto de vista filosófico, a negação é uma rejeição ou a contradição do todo ou parte desse todo. Pode ser a relação entre uma proposição p e a sua negação não-p. Se p for verdadeira, não-p é falsa e se p for falsa, não-p é verdadeira. Observe atentamente a seguinte tabela: Tabela verdade do operador lógico de negação Condição 1 Resultado lógico Verdadeiro Falso Falso Verdadeiro De volta a montagem da planilha de folha de pagamento, defina a fórmula anterior para a célula C6 e copie esta fórmula para a faixa de células de C7 até C21. Após a cópia ajuste o tamanho da coluna de forma que todas as descrições de cargos caibam nas extensões de células da coluna C. Observe o modelo: AN02FREV001/REV 4.0 25 FIGURA 12 - CAMPO CARGO DEVIDAMENTE CALCULADO FONTE: Arquivo pessoal do autor (Renato Nicodemos). Na sequência chegou a vez de obter o número de salários mínimos pagos (coluna Nr. Sal.) para o cargo definido. Esse campo também será calculado a partir da função =PROCV( ), que faz a pesquisa no código e retorna o salário correspondente. Assim sendo, na célula D6 digite a seguinte fórmula: =SE(C6=”ERRO!!!”;0;PROCV(B6;M$6:O$20;3)) Onde: C6=”ERRO!!!” Verifica se há o texto “ERRO!!!”. 0 Caso haja o referido texto que indica o erro, é mostrado o valor 0. PROCV(...) Caso contrário, é efetuado o retorno do valor referente ao Nr. Sal. Correspondente ao Código localizado. Copie esta fórmula a qual é definida em D6 para a faixa de células de D7 até D21, em seguida salve a planilha. AN02FREV001/REV 4.0 26 Na sequência será definido o valor bruto de salário a ser recebido (coluna Sal. Bruto). Na célula E6 digite a fórmula: =D6*B$3 Copie-a para as células de E7 até E21. Formate a coluna de valores do salário com duas casas decimais com separadores de milhares. Para a coluna do INSS será feito o cálculo com base no número de salários mínimos recebidos e de acordo com a tabela de alíquotas. Assim sendo, na célula F6 digite a fórmula: =PROCV(D6;Q$7:S$10;3)*E6 Copie para as células de F7 até F21. Formate a coluna de valores do salário bruto com duas casas decimais com separadores de milhares. Note que a fórmula anterior, além de trazer o valor da alíquota correspondente, efetua a multiplicação desta pelo valor do salário bruto a fim de obter o valor a ser reduzido. O cálculo do salário família (coluna Sal. Fam.) deve verificar se o cargo desse funcionário existe, e em seguida, calcular o valor do salário mínimo vigente dividido por 8 e multiplicando pelo número de dependentes. Digite na célula H6 a seguinte fórmula: =SE(C6=”ERRO!!!”;0;B$3/8*G6) Em seguida copie a fórmula para as células de H7 até H21. Formata a coluna de valores do salário bruto com duas casas decimais com separadores de milhares. Observe o modelo: AN02FREV001/REV 4.0 27 FIGURA 13 - PLANILHA DE FOLHA DE PAGAMENTO PARCIALMENTE PREENCHIDA FONTE: Arquivo pessoal do autor (Renato Nicodemos). Agora será preparado o cálculo do Imposto de Renda Retido na Fonte (IRRF). Na célula I6 digite a fórmula de cálculo do IRRF: =PROCV(D6;U$7:W$14;3)*E6 Copie a fórmula para as células de I7 até I21. Formate a coluna de valores do salário bruto com duas casas decimais com separadores de milhares e atente para os seguintes dados: D6 Efetua a pesquisa a partir de Nr. Sal. U$7:W$14 Tabela que mostra a abrangência do desconto do IRRF. 3 É a terceira coluna dentro da faixa U$7:W$4. *E6 Após ter descoberto a Alíquota de desconto de IRRF, deve-se multiplicar pelo Sal. Bruto. AN02FREV001/REV 4.0 28 A partir do momento que já são realizados os cálculos anteriores, serão realizados o cálculo do total de descontos (coluna Tot. Desc.). Assim sendo, posicione o cursor na célula J6 e digite a fórmula:=F6+I6 Copie-a para as células de J7 até J21 e formate a coluna de valores do salário bruto com duas casas decimais e com separadores de milhares. Para finalizar a montagem das colunas da planilha de folha de pagamento, posicione o cursor na célula K6 e digite a fórmula: =E6+H6-J6 Depois copie para as células K7 até K21 e formate a coluna de valores do salário bruto com duas casas decimais com separadores de milhares. Observe o modelo: FIGURA 14 - PLANILHA DE FOLHA DE PAGAMENTO QUASE FINALIZADA FONTE: Arquivo pessoal do autor (Renato Nicodemos). AN02FREV001/REV 4.0 29 Na sequência é necessário criar a parte de extrato da folha de pagamento. Assim sendo, na célula E22 digite a fórmula: =SOMA(E6:E21) Na célula E23 digite a fórmula: =MÉDIA(E6:E21) Na célula E24 digite a fórmula: =MÁXIMO(E6:E21) Na célula E25 digite a fórmula: =MÍN(E6:E21) Em seguida, copie as quatro fórmulas anteriores para as colunas F, H, I, J e K. Salve a planilha de folha de pagamento e observe o modelo: FIGURA 15 - PLANILHA DE FOLHA DE PAGAMENTO FINALIZADA AN02FREV001/REV 4.0 30 FONTE: Arquivo pessoal do autor (Renato Nicodemos). 5 OUTRAS OPERAÇÕES COM FUNÇÕES O uso das funções no Excel é sem dúvida o seu ponto forte mais alto, pois o uso de funções poupa muito trabalho. Conhecer ao máximo as funções do programa é ter certeza de saber trabalhar melhor com o programa. Cabe apresentar algumas funções que podem ser úteis, em seu trabalho profissional ou mesmo escolar. São apresentadas informações básicas para usar as funções =ABS, =AGORA, =CONT.SE, =FATO, =POTÊNCIA, =SOMA e =SOMA.SE. Para maiores detalhes a respeito destas e outras funções, é fundamental consultar o recurso de ajuda do programa. 5.1 FUNÇÃO =ABS( ) AN02FREV001/REV 4.0 31 Função da categoria matemática e trigonométrica que tem por finalidade retornar o valor do módulo de um número, ou seja, seu valor absoluto, sendo sempre um valor positivo. A função ABS usa apenas um argumento. Exemplo 01: =ABS(-7) , retorna o valor 7. Exemplo 02: =ABS(9), retorna o valor 9. 5.2 FUNÇÃO =AGORA( ) Função da categoria data e hora que tem por finalidade retornar o valor de série da data e da hora atual do sistema. A função =AGORA não usa argumento. Exemplo 01: =AGORA( ) - retorna um valor numérico serial como 403312,62688 que corresponde a informação de data e hora 14/05/2010 15:02. AN02FREV001/REV 4.0 32 A informação como data e hora é apresenta numa célula formatada. Ao fazer uso da função, a formatação ocorre automaticamente. 5.3 FUNÇÃO =CONT.SE( ) Função da categoria estatística que conta em um intervalo de célula quantas vezes certa informação ocorre. Dessa forma, é possível contar quantas vezes certo nome aparece em uma lista. A função =CONT.SE faz uso de dois argumentos, sendo o primeiro caracterizado pelo intervalo de célula e o segundo pelo critério de pesquisa. Exemplo 01: =CONT.SE(D1:D10; “Abacate”) retorna o número de vezes que abacate aparece na faixa de células informada. AN02FREV001/REV 4.0 33 5.4 FUNÇÃO =FATORIAL( ) Função da categoria matemática e trigonométrica que retorna a fatorial de um valor numérico inteiro. A função FATORIAL faz uso de um argumento. Exemplo 01: =FATO(5) - retorna 120, pois seria o mesmo que multiplicar 1x2x3x4x5. 5.5 FUNÇÃO =POTÊNCIA( ) Função da categoria matemática e trigonométrica que retorna a potência de um valor numérico fornecido como base elevado a outro valor numérico fornecido como expoente. A função =POTÊNCIA( ) faz uso de dois argumentos, sendo o primeiro o valor da base e o segundo o valor do expoente. Exemplo 01: =POTÊCIA(2;3) - retorna 8. Exemplo 02: =POTÊNCIA (10,5;1,5) - retorna 34,02388867 O cálculo de potência também pode ser efetuado pelo operador aritmético (^). Assim sendo, =2^3 retorna o resultado 8. AN02FREV001/REV 4.0 34 5.6 FUNÇÃO =SOMA( ) Função da categoria matemática e trigonométrica que tem por finalidade retornar o valor da soma de todos os números da lista de argumentos. A função SOMA utiliza até 255 argumentos. Exemplo 01: =SOMA(A1:A5) - retorna a soma de todos os valores na faixa de células A1 até A5, ou seja, os valores da linha 1 até a linha 5 da coluna A. Exemplo 02: =SOMA(A1:E1) - retorna a soma de todos os valores na faixa de células A1 até E1, ou seja, os valores da coluna A até a coluna E da linha 1. Exemplo 03: =SOMA(A1:E5) - retorna a soma de todos os valores na faixa de células A1 até E5, ou seja, os valores da coluna A da linha 1 até a coluna E da linha 5. Exemplo 04: =SOMA(A1:A5;C1:C5) - retorna a soma de todos os valores na faixa de células de C1 até C5. AN02FREV001/REV 4.0 35 6 ERROS OCORRIDOS NO USO DE FUNÇÕES Na utilização das funções e cálculos no programa Excel, alguns erros podem surgir como resposta ao uso desses recursos, dependendo do que faça com eles. O erro é apresentado quando uma função não consegue realizar o que é pedido. No sentido de saber os erros retornados e como evitá-los, seguem suas descrições. ### Ocorre quando a largura de uma coluna é menor que a extensão do valor numérico a ser apresentado. Neste caso, basta aumentar o tamanho da coluna para visualizar o valor apresentado. Caso se realizem operações com data e hora, é necessário verificar se o valor resultante não é negativo. #DIV/0! Ocorre quando se divide um valor por zero. Uma solução para evitar esse tipo de erro é usar a função SE que pode permitir o cálculo quando valor do divisor for diferente de zero #N/A Acontece quando se utiliza certo valor que não se encontra disponível para a função em uso, normalmente quando se esquece de usar um argumento obrigatório em uma função. #NOME? Ocorre quando se usa o nome de função que não existe no programa Excel. #NULO Aparece quando se usa o intervalo de células de duas áreas que não se cruzam. O operador que estabelece o ponto de cruzamento das faixas é um caractere de espaço entre as referências usadas. Por exemplo, ao usar a função =CÉL(“endereço”;(B1:B5 A5:C6)), ter-se-á como ponto de cruzamento o retorno de endereço $B$5, mas se usar a função =CÉL(“endereço”;(B1:B5A6:C6)), ter-se-á como resultado de endereço e mensagem de erro #NULO!. #NUM! Ocorre quando são fornecidos valores inválidos para a ação de uma função ou fórmula #REF! Ocorre geralmente quando se utiliza um endereço de célula que existia na planilha, mas foi removido, por algum motivo. #VALOR! Acontece quando se usa certo tipo de valor incorreto para o argumento da função em uso. AN02FREV001/REV 4.0 36 7 EXERCÍCIOS Agora chegou o momento de fazermos alguns exercícios para fixarmos o conteúdo estudado neste primeiro módulo. Procure desenvolver todos os exercícios propostos e peça auxílio sempre que necessitar ao seu Tutor. Questão 01: O que é função para o Microsoft Office Excel? Questão 02: Explique a diferença entre função com argumento e função sem argumento. Questão 03: O que é argumento de função? Questão 04: Como se obtém ajuda a respeito do Microsoft Office Excel? Questão 05: Qual é a finalidade da função =SOMA( )? Questão 06: Cite a finalidade da função =MÉDIA( ). Questão 07: Qual é a finalidade da função =MÀXIMO( )? Questão 08: Qual é a finalidade da função =MÍNIMO( )? AN02FREV001/REV 4.0 37 Questão 09: Descreva a finalidade da função =SE( ). Questão 10: Qual é a finalidade da função =PROCV( )? Questão 11: Qualé a finalidade da função =OU( ). Questão 12: É possível ter mais de uma função associada à função =PROCV( )? Questão 13: O que faz a função =SE( ). Questão 14: É possível ter mais de uma função associada à função SE. Questão 15: Para que serve o caractere “$” colocado em um endereço de célula da planilha? Qual a diferença em usar o símbolo “$ “antes da letra e do número, antes do número ou da letra do endereço de uma célula? Questão 16: Crie e explique uma função =PROCV( ) com outra associada e o mecanismo de seu funcionamento. Questão 17: Crie e explique uma função =SE( ) com outra associada e o mecanismo de seu funcionamento. FIM DO MÓDULO I
Compartilhar