Excel - Formulas e Funções , Notas de estudo de Contabilidade

Baixe Excel - Formulas e Funções e outras Notas de estudo em PDF para Contabilidade, somente na Docsity!

MICROSOFT EXCEL

APOSTILA DE FÓRMULAS E FUNÇÕES

Índice

• Conceitos básicos
  • Operadores.....................................................................................................
  • FÓRMULA DA SUBTRAÇÃO
  • FÓRMULA DA MULTIPLICAÇÃO
  • FÓRMULA DA DIVISÃO.................................................................................
  • PRECEDÊNCIA DE OPERADORES..............................................................
  • ARREDONDAMENTO
  • USANDO O AUTOPREENCHIMENTO PARA COPIAR FÓRMULAS.
  • ENDEREÇOS ABSOLUTOS E RELATIVOS..................................................
• ENDEREÇOS ABSOLUTOS
• FÓRMULA DA PORCENTAGEM.......................................................................
• FUNÇÕES..........................................................................................................
  • FUNÇÃO SOMA
  • Usando o assistente de função.....................................................................
  • FUNÇÃO MÁXIMO
  • FUNÇÃO MÍNIMO
  • FUNÇÃO MÉDIA
  • FUNÇÃO DATA DE HOJE............................................................................
  • ALGUMAS CONSIDERAÇÕES SOBRE DATAS:
  • COMO DESCOBRIR QUANTOS DIAS EXISTEM ENTRE DUAS DATAS...
  • FUNÇÃO PARA DESCOBRIR O DIA DA SEMANA DE UMA DATA
• USANDO A FUNÇÃO SE.................................................................................
  • CRIANDO UMA FUNÇÃO SE COMPOSTA.
• USANDO A FUNÇÃO SE ATRAVÉS DO ASSISTENTE DE FUNÇÃO
  • USANDO O ASSISTENTE PARA CRIAR UMA FUNÇÃO SE COMPOSTA.
  • OUTRO EXEMPLO PARA FUNÇÃO SE
  • FÓRMULA DA CONDIÇÃO SE e E..............................................................
  • FÓRMULA DA CONDIÇÃO SE e OU...........................................................
  • FÓRMULA DO CONT.SE
  • FÓRMULA DO CONTAR.VAZIO
  • FÓRMULA DO SOMASE..............................................................................
  • FÓRMULA DO PROCV
  • FÓRMULA DO SE VAZIO
• FUNÇÕES FINANCEIRAS...............................................................................
  • FUNÇÃO IPGTO
  • FUNÇÃO PGTO
  • FUNÇÃO TAXA
  • FUNÇÃO VF
• OUTRAS FUNÇÕES........................................................................................
  • RAIZ
  • POTÊNCIA
  • ARRED
  • ARREDONDAR.PARA.BAIXO......................................................................
  • ARREDONDAR.PARA.CIMA........................................................................

FÓRMULA DA DIVISÃO

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

A B C

1 RENDA MEMBROS VALOR

2 25000 15 =A2/B

PRECEDÊNCIA DE OPERADORES

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:

1º RAIZ E POTENCIA 2º MULTIPLICAÇÃO E DIVISÃO 3º ADIÇÃO E SUBTRAÇÃO

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

=10+5* 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)* 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.

1º divida 10 por 2 e some 40. resultado = 45

2º some 10 ao 45. resultado = 55

3º: faça a subtração. Resultado=

4º multiplique 78 por 4. depois subtria de 55 que é o resultado do outro conjunto de parênteses.

Veja mais um exemplo:

ARREDONDAMENTO

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, 5,236
removendo duas casas decimais: 5,

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.

USANDO O AUTOPREENCHIMENTO PARA COPIAR FÓRMULAS.

É 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.

1º: resolva a multiplicação.

2º Faça a multiplicação e em seguida a divisão. Neste caso resolve-se a operação na ordem da esquerda pra direita, pois as duas operações tem a mesma precedência. O resultado subtraia do parênteses.

Clique aqui e arraste para copiar a fórmula.

Usando o auto preenchimento e copiando esta fórmula para

outras células veja como fica o cálculo da linha de baixo:

Veja aqui que o endereço não mudou. Continua como $B$2. Mudou apenas a célula do preço em Real

FÓRMULA DA PORCENTAGEM

Primeiro entenda que: 100%
1 50%
0, 10%
0, 8%
0,

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:

A B C D

1 Produto Preço custo Acréscimo. Preço final

2 Mouse R$ 10,00 30% =B2*C2+B 3

Obs: Multiplicando-se B2*C2 obtém-se quanto é 30% de 10, ou seja, 3. somando-se em seguida o valor do mouse –> 10 tem-se o preço final

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.

A B C D 1 CLIENTE TCOMPRA DESCT. VL A PAGAR 2 Maria 1000 8% =B2-B2*C 3

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.

Usando o assistente de função

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:

FUNÇÃO MÁXIMO

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:

A B C

1 IDADE

6 MAIOR IDADE: =MÁXIMO(A2:A5)

Onde: (A2:A5) – refere-se ao endereço dos valores onde você deseja ver qual é o maior valor. No caso a resposta seria 30. Faça como mostra o exemplo trocando apenas o endereço das células.

FUNÇÃO MÍNIMO

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:

A B C

1 PESO

6 MENOR IDADE: =MÍNIMO(A2:A5)

FUNÇÃO MÉDIA

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:

A B C

1 IDADE

6 MÉDIA IDADE =MÉDIA(A2:A5)

USANDO A FUNÇÃO 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 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 B C D E

1 Aluno 1º Bim. 2º Bim Média Resultado

2 Maria 4 6 5 Reprovado

3 João 6 9 7,5 Aprovado

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:

= SE (D2 > 7 ; “APROVADO” ; “REPROVADO”)

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.

MÉDIA

Critério

Resultado se a

comparação

retornar

Resultado se a

comparação retornar

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 como o palavra aprovado e reprovado. Quando usar numeros ou

endereços de células (p. ex: D2 ) não há a necessidade do aspas.

CRIANDO UMA FUNÇÃO SE COMPOSTA.

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 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” ))

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.

Comparação Resposta se a comparação for verdade

nova função SE caso

a 1ª retorne falso.

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

Clique em OK para confirmar.

OUTRO EXEMPLO PARA FUNÇÃO SE

A B C

1 Funcionário Salário INSS

2 José R$ 850,

3 Fernanda R$ 1210,

4 Ana R$ 1550,

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 ; B28% ; SE (B2 > 1300 ; B29% ; 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, desconta- se 9%, sendo falsa entende-se que o Salário está entre 901 e 1300, descontando 10%

FÓRMULA DA CONDIÇÃO SE e E

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, 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:

A B C D

1 ALUNO IDADE ALTURA SITUAÇÃO

2 Márcio 22 1,72 =SE(E(B2>15;C2>=1,70);”Competirá”;”Não Competirá”) 3 João 14 1, Onde: B2 – refere-se ao endereço da idade

15 – refere-se a condição, ou seja, se a idade for maior que 15 C2 – refere-se ao endereço da altura =1,70 – refere-se a condição, ou seja, se a altura for maior ou igual a 1, “Competirá” – resposta se as duas condições forem verdadeiras. “Não Competirá”- resposta se caso as duas respostas não forem verdadeiras.

FÓRMULA DO SOMASE

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

Exemplo:

Você gostaria de soma 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:

A B C

1 CLIENTE VALOR SITUAÇÃO

2 Bemol 150 PG 3 TV Lar 250 4 MS Casa 350 PG 5 Ótica Avenida 180 6 Marta 250 PG 7 Andréa 190 PG Valor Recebido =SOMASE(C2:C7;”PG”;B2:B7) Onde: =SOMASE- é o nome da fórmula (C2:C7 – refere-se ao endereço inicial e final de células onde você digita a palavra PG, especificando se está paga ou não. “PG” – é o critério para somar, ou seja, só somará se neste intervalo de células de C2 até C7, conter alguma palavra PG. O critério deverá sempre ser colocado entre aspas. B2:B7 – refere-se ao intervalo de células onde será somado, mediante a condição, ou seja, ele somará somente aqueles valores que na coluna C você digitou PG.

FÓRMULA DO PROCV

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, Saida, 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:

A B C D TABELA DE CLASSES

1 CLASSE VALOR

2 ALTA 55,

3 MÉDIA 45,

4 BAIXA 25,

5 PRESIDENTE 180,

6 COMUM 22,

Esta é a Tabela Matriz

8 Hóspede Tipo Classe Valor-Diária 9 JOAO ALTA =PROCV(B9;$A$2:$B$6;2) 10 KARLA BAIXA 11 MÁRCIO MÉDIA 12 13 14

Onde: =PROCV – é o nome da fórmula

(B9 – refere-se ao endereço do valor procurado, ou seja, o tipo da classe que você digitou

$A$2:$B$6 – refere-se ao endereço absoluto da Matriz – Tabela, ou seja, o endereço da tabela onde contem os dados que você procura, no caso, o valor das classes. O endereço da tabela matriz sempre deve ser absoluto, para que permaneça para as demais células(ou seja os clientes)

2 – refere-se ao número do índice de coluna, ou seja, o número da coluna onde está os dados que você deseja que apareça, no caso, o valor da classe.

Observação: Os dados da Tabela Matriz devem está em ordem crescente. Para fazer isso, selecione os dados e clique no botão abaixo para ordenar os dados.