Baixe Excel - Apostilas - Ciência da Computação Parte1 e outras Notas de estudo em PDF para Informática, somente na Docsity! APOSTILA EXCEL INTERMEDIÁRIO E AVANÇADO SUMÁRIO Utilizando funções no Microsoft Excel ..........................................................................................11 Uma Introdução às Funções Básicas do Microsoft Excel ................................................................. 13 2 Efetuam operações matemáticas básicas como adição, subtração ou multiplicação, combinam números e produzem resultados numéricos. Na tabela a seguir, temos uma descrição desses operadores utilizados em fórmulas do Excel: Operador Descrição Exemplo + Adição =B2+B3+B4 - Subtração =C5-D5 * Multiplicação =C5*2 / Divisão =A20/B4 % Porcentagem. Utilizado para especificar porcentagens. Por exemplo, para inserir o valor de cinco por cento em uma célula, digite o seguinte: 5% ou 0,05. =A2*20% ou =A2*0,2 ^ Exponenciação. É utilizado para elevar o primeiro operando ao expoente definido pelo segundo operando. O seguinte exemplo eleva 2 no expoente 3: =2^3 =A2^B2 Operadores de comparação: Comparam dois valores e produzem o valor lógico VERDADEIRO ou FALSO. Por exemplo, se utilizarmos a seguinte fórmula: =F2<F5 Se o valor contido na célula F2 for menor do que o valor contido na célula F5, a fórmula irá retornar VERDADEIRO; caso contrário, irá retornar FALSO. A seguir, a descrição desses operadores disponíveis no Excel: Operador Descrição Exemplo = Igual Retorna verdadeiro quando os dois valores forem iguais. =F2=F5 > Maior do que Retorna verdadeiro quando o primeiro valor for maior do que o segundo. =F2>F5 < Menor do que Retorna menor quando o primeiro valor for menor do que o segundo. =F2<F5 SENAC/INFORMÁTICA 7 >= Maior ou igual a Retorna verdadeiro quando o primeiro valor for maior ou igual ao segundo. =F2>=F5 <= Menor ou igual a Retorna verdadeiro quando o primeiro valor for menor ou igual ao segundo. =F2<=F5 <> Diferente Retorna verdadeiro quando os dois valores comparados forem diferentes =F2<>F5 Operadores de texto: O operador de texto & é utilizado para concatenar (juntar) texto. Por exemplo, se tivermos o nome do contribuinte na coluna A e o sobrenome na coluna B, podemos utilizar o operador & para gerar o nome completo na coluna C. Para isso, utilizaríamos a seguinte fórmula: =A1 & " " & B1 Observe que utilizamos um espaço em branco entre aspas (" "). Esse espaço em branco é utilizado para que o sobrenome não fique "grudado" com o nome. Utilizamos o operador & para concatenar as diferentes partes que formam o nome completo: 1. O nome que está na coluna A. 2. Um espaço em branco (" "). 3. O sobrenome que está na coluna B. Operadores de referência: Combinam intervalos de células para cálculos. Operador: (dois pontos)->Operador de intervalo: produz uma referência a todas as células entre duas referências, incluindo as duas referências, como por exemplo: B5:B15. Utilizaremos esse operador quando aprendermos a utilizar fórmulas no Excel. Apenas a título de exemplo, considere a fórmula: =SOMA(A1:A30). Essa fórmula irá retornar a soma de todos os valores contidos na faixa de célula A1 até A30. Essa fórmula é equivalente à seguinte fórmula: • =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+A13+A14+A15 • +A16+A17+A18+A19+A20+A21+A22+A23 • +A24+A25+A26+A27+A28+A29+A30 Operador união ";" (ponto-e-vírgula): Esse operador é utilizado para "unir" vários intervalos de células, de tal forma que os mesmos sejam tratados como um único intervalo. Por exemplo, para somarmos os valores dos intervalos B5:B15, mais os valores do intervalo C32:C200 mais o valor da célula X45, utilizamos a seguinte fórmula: 8 MICROSOFT EXCEL2000 =SOMA(B5:B15;C32:C200;X45) ORDEM DE AVALIAÇÃO DOS OPERADORES NO MICROSOFT EXCEL: Vamos a alguns exemplos práticos para entender a ordem de avaliação. Ex. 1. Qual o resultado da seguinte fórmula: =5*6+3^2 R: 39. Primeiro, o 3 é elevado ao quadrado e nossa expressão fica assim: =5*6+9. Depois, é feita a multiplicação e nossa expressão fica assim: =30+9. Finalmente, é feita a soma, obtendo-se o resultado 39. Ex. 2. Vamos utilizar parênteses para modificar a ordem de avaliação da expressão anterior. Qual o resultado da seguinte fórmula: =5*(6+3)^2 R: 405. Primeiro, é feita a operação entre parênteses e a nossa expressão fica assim: =5*9^2. Entre a multiplicação e a exponenciação, primeiro é efetuada a exponenciação e a nossa expressão fica assim: =5*81. A multiplicação é calculada e o resultado 405 é obtido. Veja como um simples parêntese altera completamente o resultado de uma expressão. UM EXEMPLO DE UTILIZAÇÃO DE FÓRMULAS Apresentação: vamos trabalhar com um exemplo que ilustra a utilização de fórmulas no Excel. Abra o Excel e digite os dados indicados na figura a seguir: SENAC/INFORMÁTICA 9 Se uma função aparecer no início de uma fórmula, anteceda-a com um sinal de igual, como em qualquer fórmula. Os parênteses informam ao Excel onde os argumentos iniciam e terminam; lembre-se de que não pode haver espaço antes ou depois dos parênteses. Os argumentos podem ser números, textos, valores lógicos ou referências. Para usar uma função, basta incluí-la nas fórmulas da planilha. A seqüência de caracteres usada em uma função é chamada sintaxe. Todas as funções têm basicamente a mesma sintaxe. Se você não a seguir, o Microsoft Excel exibirá uma mensagem indicando que há um erro na fórmula. Seja qual for o tipo de fórmula, ao incluir uma função no início de uma fórmula, inclua um sinal de igual antes da função. Os argumentos são especificados sempre dentro dos parênteses. Os argumentos podem ser números, texto, valores lógicos, matrizes, valores de erro ou referências a uma célula ou faixa de células. Para que o argumento seja válido, é preciso que ele gere um valor válido. Algumas funções aceitam argumentos opcionais, não necessários para que a função execute os cálculos. Os argumentos também podem ser constantes ou fórmulas. As fórmulas podem conter outras funções. Uma função que tem como argumento uma outra função é chamada função aninhada. No Microsoft Excel, você pode aninhar até sete níveis de funções em uma fórmula. Veremos exemplos de funções aninhadas no decorrer deste Curso. As funções são fórmulas predefinidas que efetuam cálculos usando valores específicos, denominados argumentos, em uma determinada ordem, denominada sintaxe. Por exemplo, a função SOMA() adiciona valores ou intervalos de células, e a função PGTO() calcula os pagamentos de empréstimos com base em uma taxa de juros, na extensão do empréstimo e no valor principal do empréstimo. Os argumentos podem ser números, texto, valores lógicos como VERDADEIRO ou FALSO, matrizes, valores de erro como #N/D, ou referências de célula. O argumento atribuído deve produzir um valor válido para esse argumento. Os argumentos também podem ser constantes, fórmulas ou outras funções. A sintaxe de uma função começa com o nome da função, seguido de um parêntese de abertura, os argumentos da função separados por ponto-e-vírgula (;) e um parêntese de fechamento. Se a função iniciar uma fórmula, digite um sinal de igual (=) antes do nome da função. Essa sintaxe não possui exceções, ou seja: 1. Em primeiro lugar vem o nome da função e uma abertura de parênteses. Por Ex. =Soma( 2. Em seguida, vem uma lista de parâmetros separados por ponto-e-vírgula (;). O número de parâmetros varia de função para função. Algumas possuem um único parâmetro, outras possuem dois ou mais, e assim por diante. Por exemplo, a função soma pode conter, no mínimo, um parâmetro e, no máximo, trinta parâmetros. Por Ex. =Soma(A1;C3;F4). Essa fórmula retorna o valor da soma dos valores das células passadas como parâmetros, ou seja, essa fórmula é equivalente à: =A1+C3+F4. 3. Após a lista de parâmetros, fechamos os parênteses. Por Ex. =Soma(A1;C3;F4). Agora nossa fórmula está completa. Na tabela a seguir temos mais alguns exemplos de utilização da função SOMA(). 12 MICROSOFT EXCEL2000 Exemplo – função SOMA Descrição =SOMA(A1:A20) Soma dos valores no intervalo de células de A1 até A20. =SOMA(A1:A20;C23) Soma dos valores no intervalo de células de A1 até A20, mais o valor da célula C23. =SOMA(A1:A20;C23;235) Soma dos valores no intervalo de células de A1 até A20, mais o valor da célula C23, mais o valor 235, o qual foi passado diretamente como parâmetro. =SOMA(A1:A20;C10:C50) Soma dos valores no intervalo de células de A1 até A20 mais os valores do intervalo de C10 até C50. Na tabela a seguir temos mais alguns exemplos de utilização da função SOMA(). FUNÇÕES INTERMEDIÁRIAS DO EXCEL Uma Introdução às Funções Básicas do Microsoft Excel Apresentação: vamos aprender algumas funções básicas do Excel. Nesta lição aprenderemos a utilizar as seguintes funções: • SOMA() • MÉDIA() • MÁXIMO() • MÍNIMO() =SOMA( ) Essa função produz a soma de todos os números incluídos como argumentos, ou seja, que estiverem dentro do intervalo especificado. Sintaxe: =SOMA(núm1;núm2;intervalo 1;intervalo 2;...) São permitidos de 1 a 30 argumentos. Os argumentos devem ser números, matrizes ou referências que contenham números. Exemplo: Se A1, A2 e A3 contiverem respectivamente os números 5, 8 e 2, então: =SOMA(A1:A3) resultará 15 =SOMA(A1:A3;15;5) resultará 35 SENAC/INFORMÁTICA 13 =MÉDIA( ) Essa função produz a média (aritmética) dos argumentos. Ela aceita de 1 a 30 argumentos, e os argumentos devem ser números, matrizes ou referências que contenham números. Importante: o nome da função deve ser escrito com o acento; caso contrário será gerado um erro. Sintaxe: =MÉDIA(núm1;núm2;intervalo 1;intervalo 2;...) Por ex.: =MÉDIA(5;6;7) irá retornar o valor 6. =MÉDIA(A1:A20) irá retornar a média dos valores na faixa de A1 até A20. =MÁXIMO( ) Essa função retorna o maior número da lista de argumentos, ou seja, fornece o valor do maior número que estiver dentro do intervalo de células passado como parâmetro. A função MÁXIMO( ) aceita até 30 argumentos. Os argumentos devem ser números ou matrizes ou referências que contenham números. Importante: o nome da função deve ser escrito com o acento; caso contrário será gerado um erro. Sintaxe: =MÁXIMO(núm1;núm2;intervalo 1;intervalo 2;...) São usados argumentos que sejam números, células vazias, valores lógicos ou representações de números em forma de texto. Argumentos que sejam valores de erro ou texto que não possa ser traduzido em números causarão erros. Exemplo: Se o intervalo A1:A5 contiver os números 10, 7, 9, 27 e 2, então: =MÁXIMO(A1:A5) resultado 27 =MÁXIMO(A1:A5;30) resultado 30 14 MICROSOFT EXCEL2000 sobre o salário, então: =SOMASE(B2:B50;"CONTAB";F2:F50) Retorna a soma dos salários dos 10 funcionários da Contabilidade. Em resumo, procura na faixa de B2:B50 pela palavra CONTAB; ao encontrar, desloca-se para a coluna F (onde está o valor dos salários) e vai somando os valores dos salários para os funcionários do departamento de Contabilidade. FUNÇÕES INTERMEDIÁRIAS DO EXCEL – III Apresentação: neste lição aprenderemos a utilizar as seguintes funções: • ESQUERDA() • DIREITA() • OU() • E() • NÃO() =ESQUERDA() Essa função atua em valores do tipo texto. A função esquerda retorna um determinado número de caracteres a partir da esquerda (início) de uma String de Texto. Sintaxe: =ESQUERDA(String ou Endereço;Número de Caracteres) Exemplo: Se na célula B2 tivermos o texto "Curso Básico de Excel 97", então: =ESQUERDA(B2;7) --> Retorna Curso B =ESQUERDA("Todos devem Participar";4) Retorna Todo Observe que o espaço em branco também conta como um caractere. =DIREITA() Essa função atua em valores do tipo texto. A função direita retorna um determinado número de caracteres a partir da direita (final) de uma String de Texto. Sintaxe: =DIREITA(String ou Endereço;Número de Caracteres) Exemplo: Se na célula B2 tivermos o texto “Lições de Excel 2002”, então: SENAC/INFORMÁTICA 17 =DIREITA(B2;7) --> Retorna xcel 2002 =DIREITA("Todos Devem Participar";4) Retorna ipar Observe que o espaço em branco também conta como um caractere. =E() Todos os argumentos devem ser verdadeiros, para que a função retorne um valor verdadeiro. Sintaxe: =E(Argumentos) Exemplo: =E(2<3;7>5) --> Retorna Verdadeiro =E(2>3;5>4)) --> Retorna Falso Também podemos utilizar referência a Células. Por exemplo, se na Célula A5 tivermos o valor 10, teremos o seguinte: =E(A5<12;A5=10) --> Retorna Verdadeiro =E(A5<10;5>3) --> Retorna Falso, pois A5<10 é falso =OU() Pelo menos um dos argumentos testados devem ser verdadeiros, para que a função retorne um valor verdadeiro. A função somente retorna falso, quando todos os argumentos testados forem falsos. Sintaxe: =OU(Argumentos) Exemplo: =OU(2<3;7>5) --> Retorna Verdadeiro =OU(2>3;5>4)) --> Retorna Verdadeiro =OU(2>3;5<4) --> Retorna Falso Também posso utilizar referência a Células. Por exemplo, se na Célula A5 tivermos o valor 10, teremos o seguinte: =OU(A5<12;A5=9) --> Retorna Verdadeiro =OU(A5<10;5<3) --> Retorna Falso =não() Inverte o valor de uma expressão lógica, se a expressão for verdadeira, retorna falso, e, 18 MICROSOFT EXCEL2000 se a expressão falso, retorna verdadeiro. Sintaxe: =NÃO(Argumento) Exemplo: =NÃO(2>3) -> Retorna Verdadeiro =NÃO(3>2) -> Retorna Falso FUNÇÕES INTERMEDIÁRIAS DO EXCEL – EXEMPLOS PRÁTICOS Apresentação: a partir desta lição veremos uma série de exemplos práticos que utilizam as funções básicas do Excel, apresentadas nas lições anteriores. Aqui teremos um exemplo que utiliza as seguintes funções: • SOMA() • MÉDIA() • MÁXIMO() • MÍNIMO() Exemplo: Vamos criar uma planilha simples, que se chamará Módulo2.xls, na qual efetuaremos alguns cálculos, e será salva na pasta C:\Meus documentos. Para criar a planilha Módulo 2.xls, faça o seguinte: 1. Abra o Excel (Iniciar -> Programas -> Microsoft Excel). SENAC/INFORMÁTICA 19 3.Clique no botão Salvar ( ). NOTA: com base nos conceitos apresentados nas lições anteriores, tente resolver este exercício. Na próxima lição veremos a resolução comentada. FUNÇÕES INTERMEDIÁRIAS DO EXCEL – RESOLUÇÃO DE EXEMPLOS RESOLUÇÃO DO EXEMPLO 2 Exemplo 2 - Resolução Apresentação: nesta lição, apresentaremos a resolução do exemplo 2, enviado na lição anterior. Resolução: 1. Na coluna C, para calcular o valor do INSS, digite a seguinte fórmula: 22 MICROSOFT EXCEL2000 =B5*0,1 Estamos multiplicando o salário bruto (coluna B) por 10 por cento (0,1). Estenda essa fórmula para as demais células, até a linha 10. =SE(B5>=650;B5*15%;B5*10%) Observe que utilizamos a função SE para efetuar um desconto de 15% se o salário bruto for maior ou igual a 650, e 10%, caso contrário. Estenda essa fórmula para as demais células, até a linha 10. Para maiores informações sobre a função SE( ). Nota: para maiores informações sobre como estender uma fórmula para uma faixa de células 3. Na coluna E, para calcular o valor do salário líquido, digite a seguinte fórmula: =B5-C5-D5 Estenda essa fórmula para as demais células, até a linha 10. 4. Efetue os seguintes cálculos: 4.1) Na célula B12, determine o valor do maior salário líquido. Utilize a função Máximo. Utilize a seguinte fórmula: =MÁXIMO(E5:E10). 4.2) Na célula B13, determine o valor do menor salário líquido. Utilize a função Mínimo. Utilize a seguinte fórmula: =MÍNIMO(E5:E10). 4.3) Na célula B14, determine o valor da soma de todos os salários líquidos. Utilize a função Soma. Utilize a seguinte fórmula: =SOMA(E5:E10). 4.4) Na célula B12, determine o valor da média dos salários líquidos. Utilize a função Média. Utilize a seguinte fórmula: =MÉDIA(E5:E10). 5. Você deve obter os resultados indicados na figura a seguir: SENAC/INFORMÁTICA 23 6. Clique no botão Salvar ( ). NOTA: observe que a novidade neste exemplo é a utilização da função SE(). Com a utilização desta, foi possível aplicar diferentes percentuais ao desconto do plano de saúde, com base no valor do salário bruto. Nos exemplos das próximas lições utilizaremos bastante a função SE( ). FUNÇÕES INTERMEDIÁRIAS DO EXCEL – EXEMPLO DE ENDEREÇOS ABSOLUTOS Apresentação: nesta lição, veremos mais um exemplo prático, com a utilização de fórmulas. Também aprenderemos o conceito de endereço absoluto, o qual é de fundamental importância para a criação de planilhas no Excel. O exemplo proposto: vamos supor que você esteja preparando uma planilha para calcular o valor do salário bruto para os funcionários da Empresa ABC Ltda. O salário é calculado com base no número de horas trabalhadas. O valor para horas extras é diferente do valor para a hora normal. Nesta lição, criaremos a planilha Módulo 2.xls e a salvaremos na pasta C:\Meus documentos. Para criar a planilha Módulo 2.xls, faça o seguinte: 1. Abra o Excel (Iniciar -> Programas -> Microsoft Excel). 24 MICROSOFT EXCEL2000 Observe que, na medida em que vamos descendo uma linha, os números das linhas vão sendo incrementados. Esse é o comportamento padrão do Excel quando copiamos uma fórmula para uma faixa de células. Para o número de horas (colunas B e C), esse é o comportamento desejado, porém, para o valor da hora extra e da hora normal, esse não é o comportamento desejado. Uma vez que o valor da hora normal está fixo na célula B4, devemos sempre multiplicar o valor da coluna B (número de horas normais) pelo valor da célula B4. Uma vez que o valor da hora extra está fixo na célula B5, devemos sempre multiplicar o valor da coluna C (número de horas extras) pelo valor da célula B5. Para que os cálculos fossem feitos corretamente, deveríamos utilizar as fórmulas indicadas na tabela a seguir: Para a linha: A fórmula correta é: 8 =B8*B4+C8*B5 9 =B9*B4+C9*B5 10 =B10*B4+C10*B5 11 =B11*B4+C11*B5 12 =B12*B4+C12*B5 13 =B13*B4+C13*B5 ENTÃO, NESTE CASO, TEREI QUE DIGITAR AS FÓRMULAS UMA A UMA?????? De maneira alguma! Para evitar que isso aconteça utilizamos os endereços absolutos. Quando precisamos fixar um endereço, de tal forma que, ao copiar uma fórmula, o endereço da célula não seja adaptado, precisamos torná-lo um endereço absoluto. Esse é o caso com os endereços das células B4 e B5, os quais devem ficar fixos, isto é, não devem ser adaptados à medida que a fórmula é copiada para outras células. Para tornar um endereço absoluto, basta colocar um sinal $ antes da letra da coluna e antes do número da linha. Por exemplo, para tornar B4 e B5 endereços absolutos na fórmula da linha 8, é só utilizar a seguinte fórmula: =B8*$B$5+C8*$B$6 Feito isso, você pode estender a fórmula para as demais células, que os endereços absolutos não serão adaptados, conforme indicado na tabela a seguir: Para a linha: A fórmula com endereço absoluto fica: 8 =B8*$B$4+C8*$B$5 9 =B9*$B$4+C9*$B$5 10 =B10*$B$4+C10*$B$5 SENAC/INFORMÁTICA 27 11 =B11*$B$4+C11*$B$5 12 =B12*$B$4+C12*$B$5 13 =B13*$B$4+C13*$B$5 Observe que os endereços que não são absolutos vão sendo adaptados, já os endereços absolutos se mantêm inalterados à medida que a fórmula vai sendo copiada para as demais células. Por isso, para calcular o valor do salário bruto, digite a seguinte fórmula na célula D8: =B8*$B$4+C8*$B$5 Depois, é só estendê-la para as demais linhas. 1. Você deve obter os resultados indicados na figura a seguir: 2. Clique no botão Salvar ( ). 28 MICROSOFT EXCEL2000