Microsoft Excel: Fórmulas e Funções

Para construir as fórmulas do Excel, primeiro precisa-se conhecer os operadores matemáticos e de igualdade. Veja-os a seguir:

Veja o exemplo a seguir: 

Para calcular o total do exemplo acima devemos criar uma fórmula de adição.

Posicione o cursor na célula D2 e digite: =B2+C2 Tecle ENTER ao terminar. 

• Toda fórmula sempre começa com sinal de igual para que o Excel entenda que é um cálculo e não um texto qualquer que está digitando.

• Não se coloca o valor na soma como, por exemplo, =300+500 porque estes valores podem sofrer alterações e o resultado não seria atualizado, portanto você teria que corrigir a fórmula depois.

• Use sempre os endereços de célula (B2 e C2 como no exemplo). Assim alterando-se os valores da célula o resultado será atualizado automaticamente.

No exemplo abaixo você deseja saber qual o saldo líquido do José. Então é simples: Basta que você digite o endereço do Salário Bruto – o endereço do Desconto.

Veja:

Agora a maneira como você subtraiu é a mesma para multiplicar, será preciso apenas trocar o sinal de subtração pelo o sinal de multiplicação (*).

Veja o exemplo:

A fórmula ocorre da mesma maneira que as duas anteriores. Você só precisa trocar colocar o sinal para dividir (/).

Ao criar um cálculo que possua mais de uma operação matemática, deve-se levar em consideração a precedência de operadores, ou seja, qual operação o Excel fará primeiro. Veja a precedência abaixo na tabela:

O Excel faz os cálculos nesta ordem e não na ordem digitada.

Por exemplo: =10+5*2 O resultado deste cálculo é 20, pois primeiro o Excel multiplica 5 por 2 e depois adiciona o 10.

Caso queira que a adição seja feita primeiro, use os parênteses: =(10+5)*2 O resultado será 30.

Primeiro ocorre a soma, 10+5, resultado 15 e depois a multiplicação por 2.

Veja mais um exemplo: =(80-2)*4-(10+(40+10/2))

Qual o resultado desta fórmula ?

Vamos resolver os parênteses.

O mais interno primeiro.

Para fixar a fórmula, veja mais um exemplo:

Por padrão o Excel arredonda os decimais terminados em 1,2,3 e 4 para baixo e os decimais terminados 5,6,7,8 e 9 para cima.

Por exemplo:

5,236 = removendo uma casa decimal : 5,24

5,236 = removendo duas casas decimais: 5,2

Caso queira fazer um arredondamento diferente do padrão, use as funções ARREDONDAR.PARA.BAIXO e ARREDONDAR.PARA.CIMA que são explicadas nesta apostila.

É possível usar o auto preenchimento para copiar qualquer fórmula criada. Este processo economiza muito tempo e é um dos principais recursos do Excel.

Para isso:

Basta criar a primeira fórmula na célula desejada (normalmente a primeira da sua lista de valores);

Depois clique sobre ela e leve o ponteiro do mouse até o canto inferior direito;

Observe que o cursor muda para uma cruz preta e fina; 

Clique e arraste para copiar para outros itens da lista.

Os endereços de células podem ser divididos em 2 tipos: Relativos e Absolutos.

Os endereços relativos como o nome diz são referenciados pela posição da célula dentro da planilha. Quando copiados para outras células estes endereços mudam para manter relação com sua posição original.

Veja o exemplo:

Este tipo de endereço não muda quando a fórmula é copiada para outra célula. Chamamos este procedimento de travar células. Para isso:

Coloca-se um cifrão ($) antes da letra e um antes do numero da célula.

Por exemplo: $A$1. Use este endereço em valores de índice, que serão usados para cálculos em toda a tabela de dados e que normalmente ficam em separado na sua planilha.

Usando o auto preenchimento e copiando esta fórmula para outras células veja como fica o cálculo da linha de baixo:

100% = 1

50% = 0,5

10% = 0,1

8% = 0,08

Ou seja, toda porcentagem equivale a um numero decimal.

O excel não trabalha com porcentagens da mesma forma que a calculadora.

Para somar uma porcentagem a um valor você deve multiplicar o valor pela porcentagem e somar o valor em seguida.

Veja o exemplo:

Veja outro exemplo: Um cliente de sua loja, fez uma compra no valor de R$ 1.000,00 e você deseja dar a ele um desconto de 8% em cima do valor da compra.

Em breves palavras, uma função é um comando que recebe uma informação, realiza um cálculo e devolve uma resposta ao usuário.

Ex: =SOMA(A1:A8) .

A função irá somar todos os valores que se encontram no endereço A1 até o endereço A8. Os dois pontos indicam até, ou seja, some de A1 até A8. A fórmula será sempre a mesma, só mudará os devidos endereços dos valores que você deseja somar.

Veja o outro exemplo:

Neste exemplo estamos somando todos os valores do endereço A1 até o endereço D1. A fórmula seria digitada como no exemplo, e ao teclar enter o valor apareceria. No caso a resposta seria 60.

Outra maneira de você somar é utilizando o Botão da Autosoma.

Veja o exemplo: Este é o botão da AutoSoma. Para trabalhar com o botão da Autosoma você deve fazer o seguinte:

1. Selecionar os valores que desejar somar;

2. Depois clique no Botão da Autosoma e ele mostrará o resultado.

Veja mais um exemplo de Soma Agora você deseja somar todos os valores dispostos nesta planilha usando uma única fórmula, desta vez você terá que digitar a fórmula.

Para fazer isto, só basta que você digite o endereço inicial (em destaque) e também o endereço final(em destaque). Desta forma, você está somando todos os valores numa única fórmula, é o que chamamos de Somar Matrizes.

Acompanhe mais um exemplo de Soma. Desta vez você deseja somar números dispostos de maneira alternada, ou seja, em endereços diferentes.

Veja o exemplo:

Você desejar somar somente os valores de água, então, basta digitar o endereço de cada valor, ou seja, o endereço do primeiro valor + o endereço do 2º valor e assim sucessivamente. Lembre-se que sempre devo iniciar o cálculo usando o sinal de igualdade.

O assistente de função auxilia na montagem de uma função, colocando o nome da função corretamente, abrindo e fechando o parênteses, além de mostrar explicações sobre a montagem e o resultado prévio do cálculo.

Para usar o assistente, clique no botão colar função.

Surgirá então o seguinte quadro:

Escolha a categoria e a função desejada e depois clique no OK.

Caso queira uma explicação sobre o uso da função, clique no botão ajuda ( ? ) que está no canto inferior esquerdo do quadro.

Como exemplo vamos escolher a função SOMA.

Veja o quadro que aparece em seguida:

No quadro acima ele sugere somar o intervalo que vai do A1 até o A5 na caixa Num1. na frente aparecem os valores deste intervalo (1,26,59,89,63).

A caixa Num2 é possível colocar mais um intervalo de células, ou seja podemos somar vários intervalos de células intercalados.

Veja também que o canto inferior esquerdo temos o resultado da fórmula (238).

Clique no OK para confirmar a soma.

Neste exemplo portanto, bastou selecionar a área que se deseja somar. Para outras funções basta também apenas selecionar a faixa de células. O processo é o mesmo, apenas a função que fornece outro tipo de resultado. São elas: MÍNIMO, MÁXIMO, e MÉDIA.

Veja a seguir uma explicação destas funções:

Mostra o valor máximo de uma faixa de células. Exemplo: Suponha que desejasse saber qual a maior idade de crianças em uma tabela de dados.

Veja a fórmula no exemplo abaixo:

Mostra o valor mínimo de uma faixa de células. Exemplo: Suponha que desejasse saber qual o menor peso de crianças em uma tabela de dados.

Veja a fórmula no exemplo abaixo:

Calcula a média de uma faixa de valores.

Exemplo: Suponha que desejasse saber qual a média de idade numa tabela de dados abaixo:

Esta fórmula insere a data automática em uma planilha.

Veja o exemplo:

Esta fórmula é digitada precisamente como esta’. Você só precisa colocar o cursor no local onde deseja que fique a data e digitar =HOJE() e ela colocará automaticamente a data do sistema.

ALGUMAS CONSIDERAÇÕES SOBRE DATAS:

Datas são na verdade números decimais. O Excel assume como número 1 a data 01/01/1900.

Para ver qual número corresponde a uma determinada data:

- digite a mesma em uma célula;

- depois clique no botão Separador de milhares na barra de botões ou use o menu Formatar -> Células e transforme a data em número.

Por exemplo, 15/05/2003 corresponde ao número 37.756. Usando os número, o Excel pode resolver cálculos envolvendo datas.

É simples, basta digitar as datas nas células desejadas e depois subtrair a mais recente da mais velha.

Veja o exemplo:

Caso o resultado seja apresentado em forma de data, basta converte-lo para número.

Através da função texto podemos descobrir o dia da semana em que caiu uma determinada data.

Veja o exemplo:

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 críté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 compara-se a média do aluno com 7. Se a comparação retornar verdadeiro, este estará aprovado, caso contrário estará reprovado.

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 críté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 compara-se a média do aluno com 7. Se a comparação retornar verdadeiro, este estará aprovado, caso contrário estará reprovado.

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 críté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, compara-se 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 ( Comparação ; Resultado caso Verdadeiro ; Resultado caso Falso)

A função SE para este exemplo ficaria assim:

No exemplo acima, o resultado da média da aluno 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 separar as 3 partes da mesma; 

- Toda vez que usar um texto na em qualquer função, o mesmo deve vir dentro de aspas com a palavra aprovado e reprovado;

- Quando usar números ou endereços de células (p. ex: D2 ) não há a necessidade das aspas.

Quando o valor da célula pode conter mais de dois resultados, é necessário criarmos a função SE composta de mais outra.

No exemplo, a seguir, o aluno que tiver média menor que 4 estará 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 uma última alternativa para o retorno da resposta. Usando o exemplo da tabela de notas acima, teríamos:

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.

É possível utilizarmos o assistente de função SE através do assistente de função.

Para isso, devemos clicar no seguinte botão :

Surgirá então, uma janela com duas listas.

Na lista da esquerda, escolha a categoria: “Lógica”.

Na lista da direita, escolha: a função SE e clique em OK. Surgirá então o seguinte quadro, dividindo a função SE em 3 caixas, que correspondem às 3 partes da função.

Montando o exemplo anterior para aluna Maria no Assistente, este ficaria assim:

O assistente cria facilidades, pois reduz a possibilidade de erros de digitação na função.

Usaremos a seguir, o mesmo exemplo para criar a função SE.

Acione a função SE através do botão colar função.

Passe os seguintes dados para a função:

Para iniciar a nova função, clique em seguida neste botão que fica na parte superior esquerda da janela, acima das letras das colunas.

 Será exibida uma nova janela da função, monte da seguinte forma:

Clique em OK para confirmar.

Neste exemplo, precisamos criar uma função se, para determinar o desconto do INSS do funcionário.

Imagine uma tabela de desconto desta forma:

Salário até 900 -> desconto de 8% do salário ;

Salário entre 901 e 1300 -> Desconto de 9% do salário; 

Salário acima de 1300 -> Desconto de 10% do salário;

A função neste caso, para o primeiro funcionário ficaria assim:

=SE (B2 < 900 ; B2*8% ; SE (B2 > 1300 ; B2*9% ; B2*10%))

A primeira função SE, compara se o salário (B2) é menor que 900. Caso seja verdadeira, a comparação o calculo realizado é salário * 8%. Caso a comparação seja falsa, devemos realizar um outro SE para determinar se o salário está entre 901 e 1300 ou acima de 1300.

Neste caso, a segunda função SE compara se o Salário é maior do que 1300, sendo verdadeira, descontasse 9%, sendo falsa entende-se que o Salário está entre 901 e 1300, descontando 10%.

Agora você tem uma planilha, onde tem a idade e altura de seus alunos. Haverá uma competição e somente aqueles que tem Idade Maior que 15 e Altura maior ou igual que 1,70 participarão da competição.

Neste caso, você utilizará a condição SE e a condição E. Porque? É simples, porque para o aluno participar ele dever possuir a idade maior que 15 e altura maior ou igual 1,70. As duas condições devem ser verdadeiras, caso uma seja falsa, ele não participará.

Veja o exemplo:

Neste exemplo, basta que uma condição seja verdadeira para que o aluno participe da condição.

Veja o exemplo:

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:

Contar as células que estão vazias.

Exemplo:

Você gostaria de saber quantos alunos estão sem a média:

Soma um intervalo de células mediante a condição estabelecida.

Exemplo:

Você gostaria de somar as faturas que foram pagas. Então, você tem uma planilha onde na coluna A você coloca o nome do cliente, na coluna B o valor da fatura e na coluna C, a situação se foi paga ou não. Você gostaria de somar somente as faturas que estivessem pagas, assim você saberia o quanto já recebeu.

Logo a fórmula seria a seguinte:

Procura um determinado valor, numa Tabela Matriz.

Suponhamos que você tivesse uma planilha, onde controla-se a entrada e a saída de clientes do seu hotel. Nela você deseja colocar o: Nome, Entrada, Saída, Classe e o Valor da Classe. Você deseja que: ao digitar o nome da Classe, automaticamente apareça o valor da Classe.

Na verdade, você terá que criar 2(duas) planilhas:

-A 1ª Primeira planilha chamaremos de Matriz Tabela, porque nela você colocará o nome das Classe e o seu Valor;

A 2ª Segunda planilha, você controlará a entrada e a saída do cliente.

Veja o Exemplo:

Imagine agora, que você só deseja que apareça a resposta se caso, você digita a classe, enquanto isso o campo classe deverá ficar em branco. Neste caso, você juntará a fórmula do SE com a do PROCV.

Logo, a fórmula ficaria assim:

As funções financeiras facilitam em muito o cálculo de juros, valor de parcelas e retornos de investimentos. São muitas as funções financeiras aqui, veremos algumas das mais comuns.

Observações: os valores das funções podem retornar um valor negativo, caso queira evitar isso, coloque o argumento PGTO da função em número negativo.

Esta função retorna o pagamento de juros para um determinado período de investimento, de acordo com pagamentos periódicos e constantes e com uma taxa de juros constante.

Vamos a um exemplo:

-Digite o titulo na célula A1 : Exercicio n. 1 - Cálculo de juros simples.

-Digite nas células correspondentes:

A3: Valor do empréstimo;

A4: juros ao mês; 

A5: meses; 

A7: Resultado; 

B3: 20.000,00;

B4: 3% ;

B5: 4 .

Sua planilha deve ficar assim:

Digite os seguintes valores nas caixas:

Taxa: B4;

Período: 1;

Nper: B5 VP: -B3;

Veja o que significa cada item:

Taxa é a taxa de juros por período.

Período é o período cujos juros se deseja saber e deve estar no intervalo entre 1 e nper.

Nper é o número total de períodos de pagamento em uma anuidade.

Vp é o valor presente, ou a quantia total atual correspondente a uma série de pagamentos futuros.

Repare que logo abaixo, no quadro será apresentado o resultado. Clique em OK.

O resultado será de R$ 600,00.

Este resultado é o valor dos juros do primeiro mês. Para calcular o juros dos 4 meses, você pode multiplicar este valor por 4 editando a célula que contém a função (B7). Clique na referida célula. Repare a função na barra de fórmulas. Clique no fim da mesma, e digite: *4. Tecle Enter para confirmar.

Veja a figura a seguir:

Pronto, o valor da célula B7 agora é de R$ 2.400.

OBS: Para os exemplos a seguir, também pode-se usar o Assistente de função.

Retorna o pagamento periódico de uma anuidade, de acordo com pagamentos constantes e com uma taxa de juros constante.

Sintaxe da função:

PGTO(taxa;nper;vp;vf;tipo);

Taxa é a taxa de juros por período.

Nper é o número total de pagamentos pelo empréstimo.

Vp é o valor presente—o valor total presente de uma série de pagamentos futuros.

Vf é o valor futuro, ou o saldo de caixa, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0).

Tipo é o número 0 ou 1 e indica as datas de vencimento. Indique 1 se o pagamento for efetuado no inicio do período e 0 se for efetuado no final do período.

A fórmula a seguir, retorna o pagamento mensal por um empréstimo de R$1.000, a uma taxa anual de 20% que você deve pagar em 12 meses:

PGTO(20%/12; 12; -1000);

Os valores de VF foram omitidos, pois não temos o valor final do total de pagamento.

Veja o quadro a seguir:

Retorna a taxa de juros por período de uma anuidade.

Sintaxe:

TAXA(nper;pgto;vp;vf;tipo);

Exemplo:

Para calcular a taxa de um empréstimo de quatro anos de $8.000, com pagamentos mensais de $200:

TAXA(48; -200; 8000)

Retorna o valor futuro de um investimento, de acordo com os pagamentos periódicos e constantes e com uma taxa de juros constante.

Sintaxe:

VF(taxa;nper;pgto;vp;tipo)

Exemplo:

Suponha que deseja aplicar um dinheiro na poupança, e que esta rende juros fixos todo o mês. Você acaba de abrir uma conta no banco e depositou de inicio R$ 1000,00. Todo inicio de mês irá depositar R$ 120,00 durante 18 meses com rendimento mensal de 1%. Quanto obterá no final do período ?

Veja a fórmula a seguir:

=VF(1%;18;-120;-1000;1)

O resultado será R$ 3.573,45.

Esta função retorna a raiz quadrada de um número.

Fornece o resultado de um número elevado a uma potência.

POTÊNCIA(núm;potência)

Arredonda um número até uma quantidade especificada de dígitos.

Sintaxe:

ARRED(núm;núm_dígitos)

Esta função arredonda um número sempre para baixo.

Exemplo:

Suponha que queira arredondar o número 15,236 para baixo com duas casas decimais:

=ARREDONDAR.PARA.BAIXO(15,236;2)

O resultado será: 15,23.

Esta função arredonda um número sempre para cima.

Exemplo:

Suponha que queira arredondar o numero 15,234 para cima com duas casas decimais:

=ARREDONDAR.PARA.CIMA(15,234;2)

O resultado será: 15,24.