Fórmulas e funções no Excel

Prévia do material em texto

gestão empresarial
informática aplicada à gestão
Fórmulas e Funções 
no excel
11
ObjetivOs da Unidade de aprendizagem 
Apresentar ao aluno os recursos de criação e verificação 
de fórmulas. Discutir os recursos da biblioteca de fun-
ções e seu uso na construção de fórmulas.
COmpetênCias 
Ao término da unidade o aluno será capaz de criar fór-
mulas com uso da biblioteca de funções e que relacio-
nem mais de uma planilha.
Habilidades 
Elaborar, formatar, editar, planejar e imprimir planilhas 
utilizando o Excel 2010.
informática aplicada 
à gestão
Fórmulas e Funções 
no excel
APRESENTAÇÃO
Estamos em nossa terceira unidade do Excel 2010, com 
os recursos aprendidos suas planilhas estão muito sofis-
ticadas e com uma aparência profissional. Agora vamos 
sofisticar os conteúdos, nesta unidade vamos aprender a 
usar várias fórmulas do Excel. Você vai poder criar plani-
lhas dinâmicas e mais funcionais. Ao final desta unidade 
você conhecerá recursos mais poderosos do Excel.
PARA COMEÇAR
Nossa planilha já está formatada. Podemos então usar 
mais alguns recursos e torná-la mais agradável à leitura. 
Nossa primeira planilha está pronta. Algumas questões 
sobre o preenchimento de fórmulas ficaram sem respos-
ta. Neste capítulo nós vamos explorar o potencial do Ex-
cel em trabalhar com fórmulas.
O Excel apresenta grande número de funções, que per-
mitem a manipulação dos dados e a obtenção de resul-
tados muito interessantes.
O uso de algumas funções pode estar relacionado com 
conhecimentos adquiridos “fora” do Excel. Funções 
Matemáticas, Estatísticas, de Engenharia ou de Cubo 
entre outras.
A criação de fórmulas é sem dúvida um dos recursos 
mais poderosos do Excel, quanto mais funções você co-
nhecer e souber usar melhores serão as fórmulas criadas.
Figura 1. Guia 
– Fórmulas.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 4
FuNdAMENTOS
1. FOrmataçãO: mais reCUrsOs
Vamos retomar o assunto da formatação da planilha para observar mais 
alguns recursos.
Abra a pasta de trabalho Orçamento Doméstico.xlsx. Em seguida, no 
Menu Arquivo, clique no botão Salvar Como e altere o nome da pasta para 
Orçamento Doméstico Cores.xlsx.
Selecione toda a planilha clicando em  . No Menu Página Inicial, 
clique na opção de Todas as Bordas e escolha a opção Sem Bordas. A pla-
nilha ficará sem as linhas que separavam linhas e colunas.
Agora, vamos às formatações:
 → Selecione a região: A1:N1, e em seguida clique na opção 
. A região vai se transformar em uma única célula 
e o texto “Orçamento doméstico” fica centralizado. Altere o alinha-
mento para a esquerda;
 → Use a opção  para alterar a cor do preenchimento da célula 
para azul;
 → Para mudar a cor das letras para a cor branca é a opção Cor da Fonte 
( ).
As outras alterações de cores podem ser feitas da mesma forma, primeiro 
selecionando a região a ser formatada e em seguida alterando a formata-
ção de acordo com o exemplo.
Figura 2. Planilha de 
orçamento doméstico.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 5
Na linha saldo observamos que os valores negativos estão em um re-
tângulo com bordas vermelhas. Neste caso foi usada a formatação con-
dicional. Esta formatação permite que uma célula tenha uma formatação 
diferente dependo do resultado ou do valor de seu conteúdo.
 → Posicione o cursor no primeiro valor do saldo (C21), no Menu Página 
Inicial, e clique na opção  ;
 → Escolha a guia   e nela clique na opção  , 
digite zero no valor solicitado e clique em Ok;
 → Use o Pincel de Formatação para copiar a formatação desta célula 
para os outros valores da linha do saldo.
Pronto, a planilha está agora com uma nova formatação. Salve o trabalho 
e feche a pasta.
2. endereçOs, FórmUlas e FUnções nO exCel.
Na primeira unidade do Excel nós vimos alguns conceitos sobre elemen-
tos de planilhas do Excel:
 → Célula: encontro de linha e coluna, menor porção de uma planilha que 
possui endereço único formado pelo endereço da coluna seguido pelo 
endereço da linha. Exemplo: célula AAH234 — coluna AAH e linha 234.
 → Região: conjunto formado por uma ou mais células formando um 
retângulo. Exemplo: B4:J15 — B4 primeira célula; J15 última célula.
 → Fórmulas: começam com o sinal “=” e podem conter endereços de 
células, operadores matemáticos, funções etc. O resultado da fór-
mula aparece na célula onde a fórmula está colocada. Exemplo: 
=SOMA(B11:B18) — SOMA é a função matemática que soma os valo-
res numéricos na região B11:B18.
No exemplo do Orçamento Doméstico nós trabalhamos com algumas 
fórmulas. A sugestão de solução do exercício foi a de se digitar todas 
as fórmulas.
Com certeza esta não é a melhor solução para o preenchimento de 
fórmulas que têm o mesmo conteúdo relativo, isto é, efetuam os mesmos 
cálculos, porém alterando apenas os endereços usados.
Figura 3. 
Preenchimento 
de fórmula.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 6
Note que as fórmulas nas células C20 e D20 têm as mesmas característi-
cas da fórmula da célula B20. Elas calculam a soma das respectivas colu-
nas de despesas.
Nesta caso, podemos efetuar a cópia da fórmula da célula B20 para as 
células que formam a linha do Total de Despesas.
Posicione o cursor na célula B20 e movimente o ponteiro do mouse 
para o canto inferior direito da célula ( ). O ponteiro deve ficar 
com a seguinte forma: +. Neste momento você pode pressionar o botão 
esquerdo do mouse e arrastar até a última célula dos totais de despesas 
na coluna M ou dar um duplo clique que o Excel vai preencher a linha até 
a última coluna com valores.
A fórmula =SOMA(B12:B19) quando foi copiada para a célula C20 se 
transformou em =SOMA(C12:C19), ou seja, os endereços foram alterados 
em “uma” coluna: o que era B virou C no movimento lateral de uma colu-
na. No caso da célula M20 o resultado é =SOMA(M12:M19), indicando um 
movimento de onze colunas.
atenção
O resultado obtido neste caso foi correto, pois os endereços 
nas fórmulas guardaram o movimento feito na cópia.
Quando um endereço não pode mudar durante uma cópia 
de fórmula o Excel deve ser avisado.
Cópia relativa
Na figura abaixo podemos notar como funciona uma cópia relativa no 
Excel. Todos os endereços estão no formato normal.
Observe que a fórmula =C1+D2 foi copiada relativamente para as células 
D5, E4 e E5.
Os endereços foram alterados de acordo com o movimento.
 → Movimento de uma coluna a direita: C1 → D1 e D2 → E2;
 → Movimento de uma linha para baixo: C1 → C2 e D2 → D3;
 → Movimento de uma linha para baixo e uma coluna para esquerda: 
C1 → D2 e D2 → E3.
Figura 4. Cópia 
relativa.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 7
Endereço absoluto
conceito
O símbolo $ antes de uma parte do endereço de célula indi-
ca que se a fórmula for copiada esta parte do endereço não 
será alterado.
Neste caso o endereço é dito absoluto. Por exemplo: $A1, 
C$3 e $H$7.
O endereço absoluto não altera o cálculo da fórmula.
O uso de endereços absolutos deve ser planejado com cuidado. Existe 
uma tendência em usar o endereço absoluto tanto na coluna quanto na 
linha. Nem sempre dá certo.
Observe o exemplo abaixo a taxa de juros ocupa uma coluna.
Na célula D8 foi colocada a fórmula para calcular o valor do empréstimo 
no primeiro mês para o Banco A.
Na próxima figura observe a fórmula em D8:
D E
8 =(1+$B8)*C8 =(1+$B8)*D8
9 =(1+$B9)*C9 =(1+$B9)*D9
O endereço da coluna dos juros não se alterou nas cópias realizadas, $B é 
o endereço absoluto de coluna.
Figura 5. Ênfase 
na coluna B de 
taxa de Juros.
Figura 6. Ênfase na 
coluna D – mês 1.
Figura 7. Resultado 
da cópia da fórmula 
da célula D8 para as 
células D9, E8 e E9.
Informática Aplicada à Gestão / UA 11 Fórmulase Funções no Excel 8
dica
Pense assim: se os dados fixos estão em uma coluna, então 
fixe a coluna ($B9), se os dados fixos estão em uma linha 
então fixe a linha (B$9) agora se o dado fixo está em uma 
única célula fixe linha e coluna ($B$9).
Vamos agora conhecer algumas funções do Excel.
No exemplo do Orçamento doméstico nós usamos a função SOMA para 
obter a soma das receitas e a soma das despesas. Agora gostaríamos de 
saber qual é o valor da maior ou da menor despesa de cada mês e qual é 
a média das despesas no mês.
Selecione as linhas 20, 21 e 22 no menu Página Inicial clique em Inserir. 
O Excel vai inserir três linhas entre as despesas e linha de Saldo. Preencha 
as células A20, A21 e A22 com os conteúdos sugeridos na figura abaixo.
Agora vamos inserir as fórmulas para cada uma das linhas.
No menu Página Inicial você pode clicar na seta de opções da AutoSoma.
A lista de opções inclui a soma, a média, a contagem de números, o valor 
máximo e o valor mínimo.
Na célula B20 vamos inserir a função de Máximo para obter a maior 
despesa. Clique na opção Máx (cuidado, pois o Excel vai selecionar toda 
a região acima da célula, incluindo o Total das despesas). Selecione a re-
gião que contenha apenas os valores das despesas do mês de janeiro 
(B11:B18).
Na célula B21 insira a fórmula para obter o valor da menor despesa 
(Min) e na célula B22 a fórmula para obter a média das despesas (Média). 
Não se esqueça de alterar a sugestão do Excel para a região das fórmulas 
(B11:B18).
Figura 8. Células 
inseridas.
Figura 9. Acesso ao 
menu AutoSoma.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 9
Agora você pode copiar as fórmulas de Janeiro para os outros meses 
formatarem os resultados.
Vamos agora explorar mais funções do Excel.
O exemplo abaixo apresenta uma planilha de orçamento em que os 
dados de Descrição e Valor Unit são obtidos com a digitação do código 
do produto.
Na figura abaixo estão os produtos e serviços existentes na Empresa Lim-
pa Tudo em Geral.
Figura 10. Planilha 
Orçamento doméstico 
– formatação.
Figura 11. Planilha 
de Orçamento – 
código do produto.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 10
Vamos começar com a criação da Célula do Valor Total.
Na célula I11 o valor é obtido pela fórmula =G11*H11. Porém quando 
copiarmos esta fórmula para as outras linhas da planilha o resultado será 
a mensagem de erro #VALOR!. A origem deste erro está na falta do códi-
go do produto na coluna Código.
Para solucionar este problema vamos usar duas funções:
 → Uma para testar se a célula A11 está vazia;
 → A outra função será usada para decidir qual resultado deve ser exi-
bido em função da verificação anterior.
A primeira função é a ÉCÉL.VAZIA(A11) que retorna VERDADEIRO se a 
célula estiver vazia e FALSO caso contrário.
Agora que já aprendemos como saber se a célula está vazia, vamos 
aprender a decidir com base nesta informação.
Usaremos a função:
SE(“Teste Lógico”;”Resultado para Verdadeiro”;”Resultado para Falso”)
No nosso caso:
 → Teste lógico é a verificação se a célula A11 está vazia (VERDADEIRO 
ou FALSO);
 → Resultado para Verdadeiro é deixar a célula em branco (“ “);
 → Resultado para Falso é calcular o resultado da fórmula G11*H11.
Figura 12. Planilha de 
produtos e serviços.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 11
Vamos agora escrever a fórmula:
=SE(ÉCÉL.VAZIA(A11);” “;G11*H11)
Na célula I20 devemos inserir uma fórmula para somar a coluna do 
valor total.
Vamos agora à fórmula que vai permitir que você busque a Descrição 
e o Preço Unit usando apenas o Código. Observe que os produtos estão 
na região A2:C12 — é importante, pois é nesta região que vamos localizar 
o código digitado.
A função PROCV efetua a busca na primeira coluna da região indicada 
na fórmula para um padrão digitado na célula a ser pesquisada.
Os parâmetros da função PROCV são:
 → Célula com o valor a ser pesquisado: $A11;
 → Região a ser pesquisada: ‘Serviços e Produtos’!$A$2:$C$12:
 → ‘Serviços e Produtos’! é o nome da planilha de origem;
 → $A$2:$C$12 é região onde estão os dados, está na forma de ende-
reço absoluto para permitir a cópia da fórmula;
 → Qual é a ordem (número) da coluna que deve ser retornada:
 → 2 para a Descrição;
 → 3 para o Valor unitário.
A fórmula completa fica desta forma:
PROCV($A11;'Serviços e Produtos'!$A$2:$C$12;2) para a Descrição;
PROCV($A11;'Serviços e Produtos'!$A$2:$C$12;3) para o Valor Unit;
Estas fórmulas atentem a situação de preenchimento do código, caso con-
trário o resultado será de erro: #N/D indicando que o resultado não está 
disponível na região pesquisada.
Para solucionar podemos usar dos mesmos tipos de funções usadas no 
cálculo do valor total: SE para decidir se devemos exibir ou não o resulta-
do e a função ÉCÉL.VAZIA($A11).
A fórmula completa para retornar a Descrição fica assim:
=SE(ÉCÉL.VAZIA($A11);””;PROCV(A11;’Serviços e Produtos’!$A$2:$C$12;2))
O Valor Unit é Obtido com a troca do número da coluna de 2 para 3.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 12
atenção
O Excel verifica se uma fórmula está escrita corretamente, 
em alguns casos corrigindo de forma automática.
Quando uma fórmula é muito complexa convém o uso 
do recurso de , para verificar cada parte 
da fórmula.
O Excel tem o recurso de nomear células ou regiões. O uso deste recurso 
torna as fórmulas muito mais fáceis de serem entendidas.
Por exemplo, vamos chamar a região A2:C12 da planilha Serviços e Pro-
dutos de Itens. Basta selecionar a região, clicar na opção Definir Nome e 
digitar o nome Itens.
Para a região A11:A19 da planilha Orçamento vamos definir o nome 
Código.
Podemos agora reescrever a fórmula anterior usando os nomes criados:
=SE(ÉCÉL.VAZIA(Código);””;PROCV(Código;Itens;2))
Ficou bem mais fácil de ler.
Figura 13. 
Gerenciador 
de Nomes.
antena 
PARAbóliCA
O Excel apresenta um conjunto de recursos para cons-
trução de fórmulas que tornam as planilhas um instru-
mento de tomada de decisão muito importante.
Profissionais de várias áreas podem encontrar no Ex-
cel funções que facilitam a solução de problemas em 
suas áreas. As planilhas criadas no Excel podem ser 
simples ou extremamente complexas, onde conceitos 
e teorias são aplicados no uso de funções e na criação 
de fórmulas.
Você pode explorar as funções do Excel e descobrir 
que muitas delas podem ser úteis na solução de proble-
mas do dia a dia.
Algumas funções não dependem de conceitos sofisti-
cados, são ligadas a conceitos simples que podem ajudá-
-lo a solucionar problemas mais complexos.
E AgORA, José?
Nesta Unidade exploramos os recursos de construção 
de fórmulas do Microsoft Excel 2010.
Usamos funções que tornaram nossas planilhas mais 
funcionais e complexas.
A Auditoria de Fórmulas é um conjunto de ferramen-
tas de verificação de fórmulas, com elas você pode veri-
ficar se sua fórmula está correta, se as células envolvidas 
estão certas etc.
O Excel é uma poderosa ferramenta para análise de 
dados. De onde vêm esses dados?
Os dados podem ser digitados diretamente na plani-
lha ou podemos importar os dados diretamente do Ban-
co de Dados da empresa. Na disciplina de Sistemas de 
Informação (3o semestre) você vai conhecer como a tec-
nologia da informação é usada nas organizações. O livro 
Sistemas de Informações Gerenciais de Keneth Laudon 
é uma boa fonte para saber sobre o uso de Bancos de 
Dados nas empresas.
Na próxima unidade vamos conhecer a solução para 
importar dados e analisá-los.
Então bons estudos e até a próxima unidade.
Informática Aplicada à Gestão / UA 11 Fórmulas e Funções no Excel 15
glOSSáRiO
Função Lógica: retorna um valor lógico: VER-
DADEIRO ou FALSO.Erro de Fórmula: indica que a fórmula não 
pode ser calculada.
REFERÊNCiAS
ALMEIDA, J. T. S. Cálculos Financeiros Com Ex-
cel e HP-12c. Visual Books, 2008.
CORREIA NETO, J. Excel Para Profissionais de 
Finanças. Campus, 2006.
PEREIRA, M S A. Excel Para Contadores. 
IOB, 2009.
SILVA, M. G. Informática: terminologias bási-
cas. SP: Erica, 2007.