Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
-5
15
30
'5
VERDADEIRO
Fórmula
=SOMA(3; 2)
=SOMA("5"; 15; VERDADEIRO)
=SOMA(A2:A4)
Descrição
Soma 3 e 2.
Soma 5, 15 e 1. O valor de texto "5" é traduzido primeiro em um número e o valor lógico VERDADEIRO é traduzido primeiro no número 1.
Soma os valores nas células A2 a A4.
Soma os valores nas células A2 a A4 e, em seguida, acrescenta 15 a esse resultado.
=SOMA(A2:A4; 15)
Resultado
5
21
40
55
Soma os valores nas células A5 a A6 e, em seguida, acrescenta 15 a esse resultado. Como os valores não numéricos em referências não são
traduzidos — o valor na célula A5 ('5) e o valor na célula A6 (VERDADEIRO) são tratados como texto — os valores nessas células são ignorados.
=SOMA(A5;A6; 2)
2
Soma
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
10
7
9
27
2
Fórmula
15
32
Descrição
Resultado
Média dos números nas células de A2 a A6.
=MÉDIA(A2:A6)
11
Média dos números nas células de A2 a A6 e o número 5.
=MÉDIA(A2:A6; 5)
10
Média dos números nas células de A2 a C2.
=MÉDIA(A2:C2)
19
Média
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
A
Dados
10
7
9
27
2
Fórmula
=MÁXIMO(A2:A6)
=MÁXIMO(A2:A6; 30)
B
Descrição (resultado)
O maior entre os números acima (27)
O maior entre os números acima e 30 (30)
Máximo
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
10
7
9
27
2
Fórmula
Descrição (resultado)
O menor entre os números acima (2)
=MÍNIMO(A2:A6)
O menor entre os números acima e 0 (0)
=MÍNIMOA(A2:A6;0)
Mínimo
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
3
5
3
5
4
Fórmula
Dados
4
2
4
6
7
Descrição (resultado)
O terceiro maior entre os números acima (5)
=MAIOR(A2:B6;3)
O sétimo maior entre os números acima (4)
=MAIOR(A2:B6;7)
Maior
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
3
4
5
2
3
4
6
4
7
Fórmula
Dados
1
4
8
3
7
12
54
8
23
Descrição (Resultado)
O quarto menor número na primeira coluna (4)
=MENOR(A2:A10;4)
O segundo menor número na segunda coluna (3)
=MENOR(B2:B10;2)
Menor
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
Vendas
39790
19
22,24
VERDADEIRO
#DIV/0!
Fórmula
Descrição
Resultado
Conta o número de células não vazias nas células A2 a A8.
=CONT.VALORES(A2:A8)
6
Cont.Valores
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
Vendas
41772
19
22,24
VERDADEIRO
#DIV/0!
Fórmula
Descrição
Conta o número de células que contêm números nas células A2 a A8.
=CONT.NÚM(A2:A8)
Resultado
3
Conta o número de células que contêm números nas células A5 a A8.
=CONT.NÚM(A5:A8)
2
Conta o número de células que contêm números nas células A2 a A8 e o valor lógico VERDADEIRO
=CONT.NÚM(A1:A8;VERDADEIRO)
4
Conta o número de células que contêm números nas células A2 a A8 e o valor 2
=CONT.NÚM(A2:A8;2)
4
Cont.núm
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
120
10
150
23
Fórmula
Descrição (Resultado)
O subtotal da coluna acima usando a função SOMA (303)
=SUBTOTAL(9;A2:A5)
O subtotal da coluna acima usando a função MÉDIA (75,75)
=SUBTOTAL(1;A2:A5)
Subtotal
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
5
15
30
Fórmula
=MULT(A2:A4)
Descrição
Multiplica os números nas células A2 a A4.
Resultado
2250
=MULT(A2:A4; 2)
Multiplica os números nas células A2 a A4 e multiplica esse resultado por 2.
4500
=A2*A3*A4
Multiplica os números nas células A2 a A4 usando operadores matemáticos no lugar da função
PRODUTO.
2250
CUIDADO se algum argumento for "0" o resultado será "0".
Produto
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Fórmula
Descrição
Resultado
=ARRED(2,15; 1)
Arredonda 2,15 para uma casa decimal
2,2
=ARRED(2,149; 1)
Arredonda 2,149 para uma casa decimal
2,1
=ARRED(-1,475; 2)
Arredonda -1,475 para duas casas decimais
-1,48
=ARRED(21,5; -1)
Arredonda 21,5 para uma casa à esquerda da vírgula decimal
20
Pode ser utilizado em matriz de referência também, veja:
1,78
8,98754
7,4553
8,2
=ARRED(A10;2)
=ARRED(A11;0)
=ARRED(A12;-1)
=ARRED(A13;3)
Arred
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Fórmula
Descrição (Resultado)
=TRUNCAR(8,9)
=TRUNCAR(-8,9)
=TRUNCAR(PI())
A parte inteira de 8,9 (8)
A parte inteira de -8,9 (-8)
A parte inteira de pi (3)
Pode funcionar com =trunc(argumentos) também
Número
2,631238
7,848224
9,889
Truncar
ARRED
=ARRED(C9;2)
=ARRED(C10;2)
=ARRED(C11;2)
TRUNCAR
=TRUNCAR(C9;2)
=TRUNCAR(C10;2)
=TRUNCAR(C11;2)
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Fórmula
Descrição (resultado)
Arredonda 1,5 para cima para o número inteiro par mais próximo (2)
=PAR(1,5)
Arredonda 3 para cima para o número inteiro par mais próximo (4)
=PAR(3)
Arredonda 2 para cima para o número inteiro par mais próximo (2)
=PAR(2)
Arredonda -1 para cima para o número inteiro par mais próximo (-2)
=PAR(-1)
Pode ser utilizado em matriz de referência também, veja:
1,78
8,98754
7,4553
8,2
=PAR(A9)
=PAR(A10)
=PAR(A11)
=PAR(A12)
Par
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Fórmula
Descrição (Resultado)
Arredonda 1,5 para cima até o número inteiro ímpar mais próximo (3)
=ÍMPAR(1,5)
Arredonda 3 para cima até o número inteiro ímpar mais próximo (3)
=ÍMPAR(3)
Arredonda 2 para cima até o número inteiro ímpar mais próximo (3)
=ÍMPAR(2)
Arredonda -1 para cima até o número inteiro ímpar mais próximo (-1)
=ÍMPAR(-1)
Arredonda -2 para cima até o número inteiro ímpar mais próximo (-3)
=ÍMPAR(-2)
Pode ser utilizado em matriz de referência também, veja:
1,78
8,98754
7,4553
8,2
=ÍMPAR(A10)
=ÍMPAR(A11)
=ÍMPAR(A12)
=ÍMPAR(A13)
Impar
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Fórmula
Descrição (resultado)
=MOD(3; 2)
O resto de 3/2 (1)
O resto de -3/2. O sinal é igual ao do divisor (1)
=MOD(-3; 2)
O resto de 3/-2. O sinal é igual ao do divisor (-1)
=MOD(3; -2)
O resto de -3/-2. O sinal é igual ao do divisor (-1)
=MOD(-3; -2)
Usando com matriz de referência
Dividendo
Divisor
Usando Mod
9
5
7
8
2
3
2
1
=MOD(A9;B9)
=MOD(A10;B10)
=MOD(A11;B11)
=MOD(A12;B12)
Mod
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
5,6
4
4
3
2
4
Fórmula
Descrição (resultado)
O modo ou o número que ocorre com mais frequência acima (4)
=MODO(A2:A7)
Usando texto
Fabio
André
Fabio
João
Paulo
=MODO(A12:A16)
2
8
4
1
9
=MODO(B12:B16)
Usando sequência sem repetição
Texto e número
Fabio
1
2
1
André
João
=MODO(A21:A26)
VERDADEIRO
VERDADEIRO
VERDADEIRO
FALSO
FALSO
VERDADEIRO
=MODO(B21:B26)
Valores lógicos
Sequência com repetição "repetidas"
2
2
1
1
3
4
=MODO(B30:B35)
Traz o primeiro a repetir
Modo
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
50
Fórmula
23
Descrição
Resultado
=SE(A2<=100;"Dentro do orçamento";"Acima do orçamento")
Se o número na célula A2 for menor ou igual a 100, a fórmula retornará "Dentro do orçamento". Caso contrário, a
função exibirá "Acima do orçamento".
Dentro do orçamento
=SE(A2=100;A2+B2;"")
Se o número na célula A2 for igual a 100, A2 + B2 será calculado e retornado. Caso contrário, o texto vazio ("") será
retornado.
Texto vazio ("")
Despesas reais
1500
500
500
Fórmula
Despesas previstas
900
900
925
Descrição
Resultado
=SE(A9>B9;"Acima do orçamento";"OK")
Verifica se as despesas na linha 2 estão acima do orçamento.
Acima do orçamento
=SE(A10>B10;"Acima do orçamento";"OK")
Verifica se as despesas na linha 3 estão acima do orçamento.
OK
CUIDADO COM OS OPERADORES DE COMPARAÇÃO
> 7 (não inclui o sete) 7,1
>=7 (inclui o sete) 7 7,1
7,2 7,3 ......
7,2 7,3 .....
SE
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Resultado
45
90
78
Fórmula
=SE(A2>89;"A";SE(A2>79;"B"; SE(A2>69;"C";SE(A2>59;"D";"F"))))
=SE(A3>89;"A";SE(A3>79;"B"; SE(A3>69;"C";SE(A3>59;"D";"F"))))
=SE(A4>89;"A";SE(A4>79;"B"; SE(A4>69;"C";SE(A4>59;"D";"F"))))
Descrição
Atribui uma letra ao resultado na célula A2.
Atribui uma letra ao resultado na célula A3.
Atribui uma letra ao resultado na célula A4.
Se aninhado
Resultado
F
A
C
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
maçãs
laranjas
pêssegos
maçãs
Fórmula
Dados
32
54
75
86
Descrição
Resultado
=CONT.SE(A2:A5;"=maçãs")
=CONT.SE(A2:A5;A4)
Número de células com maçãs nas células de A2 a A5.
Número de células com maçãs nas células A2 a A5.
2
1
=CONT.SE(A2:A5;A3)+CONT.SE(A2:A5;A2)
Número de células com laranjas e maçãs nas células de A2 a A5.
3
=CONT.SE(B2:B5;">55")
Número de células com um valor maior do que 55 nas células de B2 a B5.
2
=CONT.SE(B2:B5;"<>"&B4)
Número de células com um valor não igual a 75 nas células de B2 a B5.
3
=CONT.SE(B2:B5;">=32")-CONT.SE(B2:B5;">85")
Número de células com um valor maior do que ou igual a 32 e menor do que ou igual a 85 nas células de B2 a B5.
3
Cont.se
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Valor de propriedade
Comissão
Dados
100000
200000
300000
400000
Fórmula
7000
14000
21000
28000
Descrição
250000
Resultado
Soma das comissões para valores de propriedade acima de 160.000.
=SOMASE(A2:A5;">160000";B2:B5)
=SOMASE(A2:A5;">160000")
Soma dos valores de propriedade acima de 160.000.
Soma das comissões para valores de propriedade iguais a 300.000.
=SOMASE(A2:A5;300000;B2:B5)
63000
900000
21000
Soma das comissões para valores de propriedade maiores do que o valor em C2.
=SOMASE(A2:A5;">" & C2;B2:B5)
49000
Somase
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Hora
12
16
Fórmula
=TEMPO(A2;B2;C2)
=TEMPO(A3;B3;C3)
Minuto
0
48
Descrição (Resultado)
A parte decimal de um dia, para a primeira hora acima (0,5)
A parte decimal de um dia, para a segunda hora acima (0,700115741)
Tempo
Segundo
0
10
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
4
-200
8000
Fórmula
Descrição
Os anos do empréstimo
O pagamento mensal
A quantia do empréstimo
Descrição (Resultado)
A taxa mensal do empréstimo com os termos acima (1%)
=TAXA(A2*12; A3; A4)
A taxa anual do empréstimo com os termos acima (0,09241767 ou 9,24%)
=TAXA(A2*12; A3; A4)*12
Taxa
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
39493
39583
1000000
1014420
2
Fórmula
Descrição
Data de liquidação
Data de vencimento
Investimento
Valor de resgate
Base real/360 (veja acima)
Descrição (resultado)
Taxa de desconto para os termos do título acima (0,05768 ou 5,77%)
=TAXAJUROS(A2;A3;A4;A5;A6)
Taxajuros
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
0,08
10
10000
Fórmula
=PGTO(A2/12;A3;A4)
=PGTO(A2/12;A3;A4;0;1)
Descrição
A taxa de juros anual
Número de meses de pagamentos
A quantia do empréstimo
Descrição (Resultado)
Pagamento mensal para um empréstimo nos termos acima (-1.037,03)
Pagamento mensal para um empréstimo nos termos acima, com exceção de vencimentos no início do período (-1.030,16)
Dados
0,06
18
50000
Fórmula
Descrição
A taxa de juros anual
Anos em que você pretende economizar
Quantia a ser economizada em 18 anos
Descrição (Resultado)
Quantia a ser economizada a cada mês para obter 50.000 no final de 18 meses (-129.08)
=PGTO(A12/12;A13*12;0;A14)
A taxa de juros é dividida por 12 para se obter uma taxa mensal. O número de anos durante os quais ocorre pagamento é multiplicado por 12 para se obter o número
de pagamentos.
Pgto
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Dados
1
Fórmula
Dados
2
Descrição
Dados
3
Resultado
=TRANSPOR($A$2:$C$2)
Valor a partir da primeira coluna
Valor a partir da segunda coluna
Valor a partir da terceira coluna
1
2
3
Transpor
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Produto
Bananas
Laranjas
Maçãs
Peras
Contagem
25
38
40
41
Fórmula
Descrição
Resultado
=CORRESP(39;B2:B5;1)
=CORRESP(41;B2:B5;0)
Como não existe uma correspondência exata, é retornada a posição do próximo valor mais baixo (38) no intervalo B2:B5.
A posição do valor 41 no intervalo B2:B5.
2
4
=CORRESP(40;B2:B5;-1)
Retorna um erro porque os valores no intervalo B2:B5 não estão em ordem decrescente.
#N/D
Corresp
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
ID
1
2
3
4
5
6
Fórmula
=PROCV(4;A2:E7;3;FALSO)
Sobrenome
Nome
Sousa
Gloria
Freitas
Victor
Oliveira
Susana
Neves
Paulo
Machado
Manuel
Ruivo
Pedro
Descrição
Nome do funcionário com ID=4
Cargo
Representante de vendas
Diretor de vendas
Representante de vendas
Representante de vendas
Gerente de vendas
Representante de vendas
Data de nascimento
25180
19043
23253
21447
20152
23194
Resultado
Paulo
Cargo da funcionária Gloria
Representante de Vendas
Nome e sobrenome do funcionário com ID=3
Susana Oliveira
=PROCV("Gloria";C2:E7;2;FALSO)
=PROCV(3;A2:E7;3;FALSO)&" "&PROCV(3;A2:E7;2;FALSO)
PROCV
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Eixos
4
5
6
Fórmula
Rolamentos
4
7
8
Descrição (resultado)
Parafusos
9
10
11
=PROCH("Eixos";A1:C4; 2; VERDADEIRO)
Pesquisa Eixos na linha 1 e retorna o valor que está na linha 2 da mesma coluna (4)
=PROCH("Rolamentos"; A1:C4; 3; FALSO)
Pesquisa Rolamentos na linha 1 e retorna o valor que está na linha 3 da mesma coluna (7)
=PROCH("B"; A1:C4; 3;VERDADEIRO)
Pesquisa B na linha 1 e retorna o valor que está na linha 3 da mesma coluna. Como B não é uma
coincidência exata, será usado o maior valor que seja inferior a B: Eixos (5)
=PROCH("Parafusos"; A1:C4; 4)
Pesquisa Parafusos na linha 1 e retorna o valor que está na linha 4 da mesma coluna (11)
PROCH
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Valor do Empréstimo
Prazo/meses
Taxa Juros
Pagamento
100000
180
0,0702095459302577
=PGTO(B3/12;B2;B1)
Atingir Meta
<-- Atingir meta
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Fórmula
=E(VERDADEIRO; VERDADEIRO)
=E(VERDADEIRO; FALSO)
Descrição
Todos os argumentos são VERDADEIRO
Um argumento é FALSO
Todos os argumentos são avaliados como VERDADEIRO
=E(2+2=4; 2+3=5)
Resultado
VERDADEIRO
FALSO
VERDADEIRO
Dados
50
104
Fórmula
Descrição
Resultado
=E(1<A2; A2<100)
Exibirá VERDADEIRO se o número na célula A2 estiver entre 1 e 100. Caso contrário, exibirá FALSO.
VERDADEIRO
=SE(E(1<A3;A3<100); A3; "O valor está fora do intervalo.")
Exibirá o número na célula A3, se estiver entre 1 e 100. Caso contrário, exibirá a mensagem "O valor está fora
do intervalo".
O valor está fora do
intervalo.
=SE(E(1<A2; A2<100); A2; "O valor está fora do intervalo.")
Exibirá o número na célula A2, se estiver entre 1 e 100. Caso contrário, exibirá uma mensagem.
50
E
Planilha de Funções Excel
Professor Fabio Rosar
[email protected]
Fórmula
=OU(VERDADEIRO)
=OU(1+1=1;2+2=5)
=OU(VERDADEIRO;FALSO;VERDADEIRO)
Descrição (Resultado)
Um argumento é VERDADEIRO (VERDADEIRO)
Todos os argumentos são avaliados como FALSO (FALSO)
Pelo menos um argumento é VERDADEIRO (VERDADEIRO)
OU