Baixe o app para aproveitar ainda mais
Prévia do material em texto
Conteúdo Planilha Eletrônica – Professor Paulo Lima – Curso Maciel Planilha Eletrônica Funções Todas as funções do Excel utilizam uma sintaxe em comum, para digitar qualquer cálculo é necessário iniciar com o símbolo de igual “=”. Este símbolo define que tudo o que será digitado ali deverá ser buscado na biblioteca de funções. O Excel além de trabalhar com fórmulas prontas utiliza também as operações matemáticas básicas através dos operadores: Função Operador Exemplo Resultado Potenciação ^ =5^2 25 Multiplicação * =5*2 10 Divisão / =5/2 2,5 Soma + =5+4 9 Subtração - =5-2 3 Para montar uma fórmula deve-se observar a regra matemática para as prioridades das operações. Se na fórmula não colocarmos parênteses ( ) o Excel irá aplicar a regra matemática. Primeiro será considerado a potenciação, depois a multiplicação, divisão, soma e subtração. Observe no exemplo a seguir que usaremos as mesmas operações, mas podem-se obter resultados diferentes. = 2 + 2 * 5 - o resultado será 12. = (2+2) * 5 - o resultado será 20. Quando usamos parênteses, este passa a ter prioridade sobre as demais operações. Copiar fórmulas absolutas ou relativas Quando copiamos uma fórmula =A1*B1 para as linhas abaixo o Excel entende que as fórmulas devem ser relativas, ou seja, mudam de acordo com as linhas. No exemplo usado =A1*B1 sendo relativas, mudam para =A2*B2, =A3*B3 e assim por diante. Mas quando um determinado endereço deve permanecer absoluto, antes de copiar deve-se usar o símbolo $ na fórmula que queremos copiar. Ex.: Na fórmula =A1*B1 queremos que quando copiar para as linhas abaixo o endereço B1 permaneça absoluto, ou fixo. =A1*B$1 Notem que o símbolo $ está antes do número 1 que identifica a linha. Depois de copiado, as fórmulas ficam assim: =A2*B$1 =A3*B$1 =A4*B$1 =A5*B$1 Quando copiamos uma fórmula para outras colunas, a regra é a mesma, ou seja devemos colocar o símbolo $ na fórmula na frente da letra que identifica a coluna que deve permanecer fixa. Usando o exemplo =A1*A2, quando copiamos para as outras colunas, ficam assim: =B1*B2 =C1*C2 =D1*D2 Colocando o símbolo $ antes do A1, =$A1*A2, depois de copiado, fica assim: =$A1*B2 =$A1*C2 =$A1*D2 Para fixar a coluna e a linha usa-se =$A$1*A2, pois assim, quando copiamos esta fórmula para baixo ou para o lado na planilha, o endereço $A$1 sempre ficará fixo. Função condicional SE A função SE do Excel é sempre utilizada quando o valor de uma célula pode variar. A tarefa da função SE é comparar o valor da célula com um critério estabelecido e retornar dois resultados. Um se a comparação for verdadeira e outro se a comparação for falsa. Veja estas comparações: 5+2 > 8 – comparação falsa 2*3 = 6 – comparação verdadeira (2+8) * 2 > 5+6 – comparação verdadeira. Estas comparações são bem simples e conhecidas. No Excel funciona da mesma forma, mas comparamos uma célula (ou seja, seu conteúdo) com um determinado valor. No exemplo a seguir o aluno é reprovado caso sua média seja menor que 7. Sendo assim 7 é o critério para aprovação. Então se compara a média do aluno com 7. Se a comparação retornar verdadeiro, este estará aprovado, caso contrário estará reprovado. Nossa comparação ficaria assim para o 1º aluno: Média da Maria > 7 Comparação falsa. Então REPROVADA No momento a Maria tem média menor que 7, mas o valor da média pode variar no decorrer dos bimestres, fazendo com que a aluna seja aprovada. A função SE trata então as duas situações. Quando a comparação for falsa e quando for verdadeira. A forma de montar a Função é a seguinte: =SE(Teste Lógico;Valor se verdadeiro;Valor se falso) A função SE para este exemplo ficaria assim: = SE (D2 > 7; “APROVADO”; “REPROVADO”) Teste lógico Valor se falso Valor se verdadeiro No exemplo acima o resultado da média da aluna Maria, é REPROVADO, pois sua média é menor do que 7 o que faz com que a comparação retorne falso. Para o aluno João, o resultado é APROVADO, já que sua média é maior que 7, fazendo com que a comparação seja verdadeira. Obs: o sinal de ponto e vírgula ( ; ) é obrigatório na função e serve para separaras 3 partes da mesma. Toda vez que usar um texto na em qualquer função o mesmo deve vir dentro de aspas como o palavra aprovado e reprovado. Quando usar números ou referências não há a necessidade do aspas. Criando uma função se composta (aninhada). Quando o valor da célula pode conter mais de dois resultados é necessário criarmos a função SE composta. No exemplo a seguir o aluno que tiver média menor que 4 está reprovado, o aluno que tiver média maior que 7 estará aprovado, e o aluno que tiver um média entre 4 e 7 ficará de exame. Note, portanto que podemos ter 3 situações para o resultado das notas do aluno. É necessário então criarmos duas funções SE, para realizar duas comparações, sobrando um ultima alternativa para o retorno da resposta. Usando o exemplo da tabela de notas acima Teríamos: =SE (D2>7;“APROVADO”;SE(D2<4;“REPROVADO”; “EXAME”)) Valor se falso Valor se verdadeiro 2º teste lógico 2º teste lógico Valor se verdadeiro 1º teste lógico 1º teste lógico No exemplo acima a primeira comparação, sendo verdadeira, retorna a resposta APROVADO para o aluno, caso seja falsa, será realizada uma nova função para comparar novamente a o valor da célula, pois ainda não podemos afirmar que o aluno está REPROVADO ou de EXAME. A segunda comparação retorna APROVADO caso a comparação de D2 < 4 seja verdadeira. E retorna EXAME caso a comparação seja falsa, pois se D2 não é nem maior que 7 (1ª comparação) e nem menor que 4 só pode estar entre 4 e 7, fazendo com que o aluno fique de exame. Os operadores lógicos são: 1. > maior 2. < menor 3. >= maior ou igual 4. <= menor ou igual 5. = igual 6. <> diferente Fórmulas Digitando Data e Hora: Digitar a data separada por barra (/) ou hífen (-); Digitar a hora usando o sinal de (:); Digitar CTRL + ; a entrada é a data do micro; Digitar CTRL + SHIFT + : a entrada é a hora do micro; Digitar =HOJE() – insere a data automática; Digitar =AGORA() – insere a data e a hora. Função do Máximo Mostra o valor máximo de uma faixa de células. Exemplo: Suponhamos que se desejasse saber qual a maior idade de crianças em uma tabela de dados. Veja a fórmula no exemplo abaixo: Função do Mínimo Mostra o valor mínimo de uma faixa de células. Exemplo: Suponhamos que desejasse saber qual o menor peso de crianças em uma tabela de dados. Veja a fórmula no exemplo abaixo: Função da Média Calcula a média de uma faixa de valores. Exemplo: Suponhamos que desejasse saber qual a média de idade numa tabela de dados abaixo: Função de Raiz Quadrada Calcula a raiz quadrada de um número ou referencia. Função de Potência A função da potência eleva um número a potência especificada e assume esta forma: Função Cont.SE Agora você possui uma planilha onde tem o nome dos alunos e as suas médias. E você desejasse agora saber quantos alunos tiraram médias maior e igual a 9. Veja o exemplo: Função Contar.Vazio Contar as células que estão vazias. Exemplo: você gostaria de saber quantos alunos estão sem a média. Função do SomaSE Soma um intervalo de células mediante uma condição estabelecida. Exemplo: você gostaria de somar somente as faturas que foram pagas. Função MAIOR Identifica em um intervalo o maior valor dentre os dados, por exemplo, localizar o terceiro maior valor de uma lista: Números repetidos também são contados para determinar o valor. Função MENOR Identifica em um intervalo o menor valor dentre os dados, por exemplo localizar o terceiro menor valor de uma lista: Números repetidos também são contados para determinar o valor Função Arred Arredonda um número para um número especificado de dígitos. Função INT Arredonda um número para baixo para o inteiro mais próximo. =INT(8,9) Arredonda 8,9 para baixo (8) =INT(-8,9)Arredonda -8,9 para baixo (-9) Função E Retornará VERDADEIRO se todos os seus argumentos forem avaliados como VERDADEIRO e retornará FALSO se um ou mais argumentos forem avaliados como FALSO. Função OU Retornará verdadeiro se pelo menos um dos argumentos for avaliado como VERDADEIRO e retornará falso se todos os argumentos forem falsos. Função MOD Retorna o resto depois da divisão de um número por um divisor. O resultado possui o mesmo sinal que divisor. A função MOD tem os seguintes argumentos: MOD(número; divisor) Número – É o número para o qual você deseja encontrar o resto. Divisor – É o número pelo qual você deseja dividir o número. Função PROCV Você pode usar a função PROCV para pesquisar a primeira coluna de um intervalo (intervalo: duas ou mais células em uma planilha. As células de um intervalo podem ser adjacentes ou não adjacentes.) de células e, em seguida, retornar um valor de qualquer célula na mesma linha do intervalo. Por exemplo, suponhamos que você tenha uma lista de funcionários contida no intervalo A2:C10. Os números de identificação dos funcionários são armazenados na primeira coluna do intervalo, como mostrado na ilustração a seguir. Se souber o número de identificação do funcionário, você poderá usar a função PROCV para retornar o departamento ou o nome desse funcionário. Para obter o nome do funcionário número 38, você pode usar a fórmula =PROCV(38, A2:C10, 3, FALSO). Essa fórmula procura o valor 38 na primeira coluna do intervalo A2:C10 e, em seguida, retorna o valor contido na terceira coluna do intervalo e na mesma linha do valor procurado ("Nuno Farinha"). O V em PROCV significa vertical. Use PROCV em vez de PROCH quando os valores da comparação estiverem posicionados em uma coluna à esquerda ou à direita dos dados que você deseja procurar. SINTAXE =PROCV(valor_procurado;matriz_tabela;núm_índice_ coluna;procurar_intervalo) A sintaxe da função PROCV tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento.): valor_procurado Obrigatório. O valor a ser procurado na primeira coluna da tabela ou intervalo. O argumento valor_procurado pode ser um valor ou uma referência. Se o valor que você fornecer para o argumento valor_procurado for menor que o menor valor da primeira coluna do argumento matriz_tabela, PROCV retornará retornará o valor de erro #N/D. matriz_tabela Obrigatório. O intervalo de células que contém os dados. Você pode usar uma referência a um intervalo (por exemplo, A2:D8) ou um nome de intervalo. Os valores na primeira coluna de matriz_tabela são os valores procurados por valor_procurado. Os valores podem ser texto, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes. núm_índice_coluna Obrigatório. O número da coluna no argumento matriz_tabela do qual o valor correspondente deve ser retornado. Um argumento núm_índice_coluna de 1 retornará o valor na primeira coluna em matriz_tabela; um argumento núm_índice_coluna de 2 retornará o valor na segunda coluna em matriz_tabela e assim por diante. Se o argumento núm_índice_coluna for: Menor que 1, PROCV retornará o valor de erro #VALOR!. Maior do que o número de colunas em matriz tabela, PROCV retornará o valor de erro #REF!. procurar_intervalo Opcional. Um valor lógico que especifica se você quer que PROCV localize uma correspondência exata ou aproximada. Se procurar intervalo for VERDADEIRO, ou for omitido, uma correspondência exata ou aproximada será retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor procurado será retornado. Exemplo Este exemplo procura a coluna Densidade da tabela de propriedades atmosféricas para localizar os valores correspondentes nas colunas Viscosidade e Temperatura. (Os valores são para o ar de 0 grau Celsius no nível do mar ou 1 para atmosfera.) Talvez seja mais fácil entender o exemplo se você copiá-lo em uma planilha em branco. Função Hoje Retorna a data do dia atual =Hoje() Função Dia Retorna o dia de uma data =Dia(hoje()) Função Mês Retorna o mês de uma data =Mês(hoje()) Função Ano Retorna o ano de uma data =Ano(hoje()) Função Agora Retorna a data e a hora atual =Agora() Função segundo Retorna os segundos da hora atual =segundo(agora()) Função Minuto Retorna os minutos da hora atual =Minuto(agora()) Função Hora Retorna a hora da hora atual =hora(agora()) Exercícios A planilha acima possui fórmulas que levaram a cada valor das células. Observando a planilha, responda as perguntas: 1. A fórmula que retornou o valor da célula A1, foi: =HOJE() =DATA() =AGORA() =DIA() =HORA() 2. A fórmula que retornou o valor da célula A2, foi: =HORA() =HOJE() =AGORA() =DIA() =MOMENTO() 3. A fórmula que retornou o valor da célula A3, foi: =PI() =PI(3,14) =CÁLCULO(PI) =RETORNAR(VALOR.PI) =SOMA(PI) 4. A fórmula que retornou o valor da célula A4, foi: =2x3 =MULT(2;3) =SOMA(2-3) =2^3 =CONCATENAR(2;3) 5. A fórmula que retornou o valor da célula A5, foi: =3^2 =2^3 =4^2 =2^4 =8^0 6. A fórmula que retornou o valor da célula B1, foi: =10-2/2 =10-(-2) =9-(2+2) =10-(-1) =(10+(-2/-2)) 7. A fórmula que retornou o valor da célula B2, foi: =((2+3)*2)-3 =2+3*2-3 =(2+3*2)-3 =2+(3*2)-3 =2+3*(2-3) 8. A fórmula que retornou o valor da célula B3, foi: =-3+3 =-3-3 =3+3 =3-3 =-(3-3) 9. A fórmula que retornou o valor da célula B4, foi: =3+3 =+3+3 =-3<>3 =3^3 =-(-3-3) 10. A fórmula que retornou o valor da célula B5, foi: =1^3 =-3^1 =-3<>3 =3^2 =-3-(-6) Observe a figura acima e responda as questões de 1 a 3: 1. Para acessar a última célula de uma planilha do Excel, devemos digitar o seguinte endereço de célula na "Caixa de Nome" (realçada em cor vermelha): Z65536 ZZ65536 A65536 IV65536 AZ65536 2. Observando a figura acima, podemos afirmar que, se digitarmos a palavra CURSO e pressionarmos a tecla "Enter": A planilha será nomeada como CURSO. A célula A1 será nomeada como CURSO. A linha 1 será nomeada como CURSO. A coluna A será nomeada como CURSO. Não é possível digitar palavras na "Caixa de Nome", apenas endereços de células. 3. É correto afirmar sobre a "Caixa de Nome", exceto: Permite navegar/acessar qualquer célula da planilha. Permite nomear qualquer célula ou grupo de células da planilha. Exibe o endereço ou nome da célula. Exibe a relação de todos os nomes de células criado pelo usuário. Permite selecionar colunas ou linhas de uma planilha 4. Observe, através da "Linha de Entrada" (na Barra de Fórmulas), a fórmula digitada na célula C1. Ao utilizar a "alça de preenchimento" para arrastar a fórmula da célula C1 até a célula C3, qual será o valor encontrado para a célula C3. 30 3 33 111 133 5. Ao digitar a fórmula da célula B5, e pressionar a tecla Enter, o valor retornado será igual a: 0 10 20 100 Erro na fórmula 6. A fórmula digitada em B5 retornará o resultado: 0 1 3 5 8 7. A fórmula exibida na célula B5 retornará o seguinte resultado: Erro na fórmula 1 2 3 6 8. A fórmula constante na célula A3 retornará o valor: 10321,4 0,0103124 10,3124000 10,312 10,333 9. Marque a única opção que exibe um valor possível, retornado pela fórmula acima: 0,138 1,523 2,653 10,234 5,999 10. Ao selecionar uma célula contendo o valor R$10,00 e pressionar o botão exibido acima, o valor retornado será igual a: R$10,00 10% 0,1 10 R$10,00% 1. Clicar com o botão direito sobre a guia "Planilha1" permitirá, exceto: Inserir planilha Ocultar planilha Excluir planilha Renomear planilha Mover/Copiar planilha 2. Para selecionarmos "Planilha1" e "Planilha3", devemos: Clicar sobre a guia da "Planilha1", pressionar e manter pressionada a tecla "Shift", e clicarna guia da "Planilha3". Clicar no menu Formatar > Planilha e escolher a opção "Selecionar planilhas", e clicar em "Planilha1" e "Planilha3". Clicar sobre a guia da "Planilha3", pressionar e manter pressionada a tecla "Ctrl", e clicar na guia da "Planilha1". Clicar com o botão direito do mouse sobre qualquer guia de planilha, escolher a opção "Selecionar planilhas" e clicar na guia "Planilha1" e na guia "Planilha3". Clicar na guia "Planilha2" com o botão direito do mouse e escolher "Selecionar > Inverter seleção". 3. Para excluir a guia "Planilha1", podemos clicar com o botão direito do mouse sobre a guia e escolher a opção "Excluir planilha", ou utilizar o menu...: Arquivo > Excluir planilha Ferramentas > Excluir > Excluir planilha Editar > Planilha > Excluir Formatar > Planilha > Excluir Inserir > Planilha > Excluir 4. Ao clicar na guia "Planilha1", pressionar e manter pressionada a tecla ou combinação de teclas __________, e arrastar a guia "Planinlha1" até esteja posicionada entre as guias "Planilha2" e "Planilha3" irá criar uma cópia da "Planilha1". A tecla ou combinação de teclas que devemos pressionar e manter pressionada para esta ação, é: Ctrl Shift Alt Ctrl+Shift Ctrl+Alt 5. Ao selecionar o intervalo A1:A10, pressionar a tecla [Delete], marcar a opção "Sequência de caracteres" na caixa de diálogo “Excluir Conteúdo" e clicar no botão [Ok], irá excluir o seguinte intervalo de células: A1:A10 A1:A5 A6;A7 A8;A9 A6:A10 6. Selecionar o intervalo de células A1:A4, clicar na "alça de preenchimento" exibida no canto inferior direito da seleção e arrastar até a célula A9, irá retornar o seguinte valor para a célula A9: 0 1 -1 4 9 7. Digitar a fórmula acima e pressionar a "Enter" irá retornar o seguinte valor para a célula A1: Erro 5 15 Falso Verdadeiro 8. Marque a opção abaixo que não corresponde a uma célula inválida de uma planilha do Calc, levando-se em consideração que nenhuma célula ou intervalo de células foi "nomeado": IU20 HZ65536 AA3000 LB254 Z12987 9. Marque a opção que retornará um erro na fórmula, caso seja utilizada: =123^1 =31>4 =(-3+3) =3^-3 =(-3+)2 10. Qual o resultado, com 2 casas decimais, possível de ser obtido através da fórmula =ALEATÓRIO ()+2: 0,02 2,32 3,20 4,22 12,42 1. Observe, através da "linha de entrada" (Barra de Fórmulas) a fórmula digita na célula B1. Ao utilizar a "alça de preenchimento" conforme mostrado pelas "setas", qual será o valor retornado na célula B3: 0 11 12 30 31 2. Para travar a célula A11 na fórmula digitada em A1 (tanto a coluna A quanto a linha 11), podemos utilizar o "$" conforme exibido na figura, ou pressionar: F2 F3 F4 Shift+F4 Ctrl+F4 3. Observe a fórmula digita da célula A5 (veja "linha de entrada", na "Barra de Fórmulas"). Ao utilizar a "alça de preenchimento" para arrastar a fórmula até a célula D5, qual será o valor da célula D5. 6 60 600 6000 0 4. A fórmula digitada na célula A9 irá retornar o valor: 0 2 3 5 7 5. Selecionar o intervalo de células acima e clicar no botão indicado ao lado, irá classificar os valores (de A1 para A5, nesta ordem) conforme alternativa: -98,00; -1,00; 0,00; 1,00; 2,01 -1,00; -98,00; 2,01; 1,00; 0,00 2,01; 1,00; 0,00; -1,00; -98,00 2,01; 1,00; -98,00; -1,00; 0,00 -98,00; 2,01; 1,00; -1,00; 0,00 6. Na célula A2, é possível conferir o recurso: Editar > Inserir > Nota Inserir > Nota Editar > Comentário Formatar > Observação Inserir > Comentário 7. Na fórmula =A4+$B$4 foi utilizado o recurso "travar célula", como pode ser conferido através dos "$" antes do B e do 4. A tecla ou combinação de teclas de atalho que pode ser utilizada para "travar células" em uma fórmula, é: Ctrl+F3 F3 F6 Shift+F4 F4 8. Na fórmula =A1&A2, está explicito - através do "&" -, o uso de um recurso equivalente à função: Concatenar Arredondar Multiplicar Bloquear Pagamento 9. Observando a célula A1, podemos afirmar que ocorreu um erro chamado: Referência circular Célula bloqueada Formato inválido Célula truncada Valor indefinido 10. A fórmula digitada na célula "A4" irá retornar o resultado: -2 -8 -12 10 8 Com base na figura acima, responda as questões 1, 2 e 3: 1. A figura acima exibe algumas "setas azuis", características do recurso: Detetive Rastreador Ancorar Atingir Meta Validação 2. É possível afirmar que a opção escolhida para a exibição das setas, foi: Remover precedentes Rastrear precedentes Remover dependentes Rastrear dependentes Exibir todas as setas 3. Também é correto afirmar que: A célula A5 possui uma fórmula. A célula A5 foi utilizada na elaboração de 3 fórmulas. O conteúdo da célula A5, caso deletado, não irá interferir no resultado de outras células. As células "indicadas" pelas setas não utilizaram a célula A5 em suas fórmulas. As células B1, B2 e B3 não possuem fórmulas. 4. A fórmula digitada em A4, se efetuada, irá retornar um erro chamado: Nome incorreto Valor inexistente Violação de validação Quebra de validação Referência circular 5. O erro retornado pela fórmula indicada na figura acima, é: Erro:0 Erro:12 Erro:324 Erro:522 Erro:731 6. Para que, ao arrastar a fórmula digitada na célula C1 (através da alça de preenchimento), até a célula C7, o resultado seja, na célula C7, o somatório de A7 com B7, multiplicado por E1, a fórmula digitada em C1 deverá ser: =(A1+$B1)*E1 =%(A1+B1)*$E1 =(A7+B7)*E&1 =(A7+$B$7)*"E1" =(A1+B1)*E&1 7. Ainda sobre a figura da questão anterior, se deixarmos a fórmula como está e utilizarmos a "alça de preenchimento" até a célula C7, o resultado obtido em C7 seria igual a: 0 27 127 300 2700 8. Sabendo-se que os valores da coluna C foram obtidos através da soma dos valores correspondentes da mesma linha, das colunas A e B, podemos afirmar que, ao pressionar a tecla [Delete] e escolher a opção "Fórmulas" (conforme pode ser visto na figura), serão excluídos as seguintes células: nenhuma célula será excluída apenas o intervalo de células A1:B7 apenas o intervalo de células A1;B7 apenas o intervalo de células C1;C7 apenas o intervalo de células C1:C7 9. Selecionar a célula A1 e clicar 2x seguidas no botão exibido ao lado, irá formatar o conteúdo da célula A1 conforme alternativa: 2 2,0 2,00 2,000 2,0000 10. Digitar A1:A20 na caixa de nome e pressionar a tecla [Enter], irá: Nada acontecerá Selecionar a célula A1 Executar o somatório do intervalo A1:A20 Ocorrerá um erro O intervalo A1:A20 será selecionado
Compartilhar