UNIVESP - Unidade de aprendizagem 14 - Fórmulas e funções no Excel - Informática aplicada à gestão

Prévia do material em texto

Unidade de
Aprendizagem 14
01
Ao término da aula o aluno será capaz de criar fórmulas 
com uso da biblioteca de funções e que relacionem mais de 
uma planilha.
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.
Elaborar, formatar, editar, planejar e imprimir planilhas 
utilizando o Excel 2010.
Fórmulas e 
funções no Excel
Informática Aplicada à Gestão
Apresentação
Informática Aplicada à Gestão
02
 Estamos em nossa terceira unidade do Excel 2010, com os recursos aprendidos 
suas planilhas estão muito sofisticas 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 planilhas dinâmicas e mais funcionais. Ao final desta aula você 
conhecerá recursos mais poderosos do Excel.
 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 resposta. Neste capítulo nós vamos 
explorar o potencial do Excel em trabalhar com fórmulas.
 O Excel apresenta grande número de funções, que permitem a manipulação dos 
dados e a obtenção de resultados muito interessantes.
 Figura 1: Guia – Formulas 
 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ê conhecer e souber usar melhores serão as fórmulas criadas
 1 Formatação: mais recursos
 Vamos retomar o assunto da formatação da planilha para observar mais alguns 
recursos.
Informática Aplicada à Gestão
 Figura 2: Planilha de orçamento 
 Abra a pasta de trabalho Orçamento Doméstico.xlsx, em seguida, no Menu 
arquivo clique na 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 planilha ficará sem 
as linhas que separavam linhas e colunas.
 Agora vamos à formatações:
 Selecione a região: A1:N1 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 alinhamento 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 e 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.
 Na linha saldo observamos que os valores negativos estão em um retângulo com 
bordas vermelhas.
03
Informática Aplicada à Gestão Informática Aplicada à Gestão
 
 
 
 ).
04
 Neste caso foi usada a formatação condicional, 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, 
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 o Excel nós vimos alguns conceitos sobre elementos 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 – AAH linha e 234 linha.
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órmula aparece na célula onde a fórmula está colocada. Exemplo: 
=SOMA(B11:B18) – SOMA é a função matemática que soma os valores 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 Informática Aplicada à Gestão
Escolha a guia , 
 . 
 Note que as fórmulas nas células C20 e D20 têm as mesmas características da 
fórmula da célula B20. Elas calculam a soma das respectivas colunas 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, movimente o ponteiro do mouse para o canto 
inferior esquerdo 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 ela ser 
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 coluna.
 No caso da célula M20 o resultado é =SOMA(M12:M19) indicando um movimento 
de onze colunas.
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.
Na figura ao lado 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.
Figura 4: Cópia relativa
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
05
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.
Informática Aplicada à Gestão Informática Aplicada à Gestão
Note que as fórmulas nas células C20 e D20 têm as mesmas características da 
 
06
 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
 Figura 5: Ênfase na coluna B de taxa de Juros
 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 que a fórmula em D8: 
 Figura 5: Ênfase na coluna D – mês 1
 Resultado da cópia da fórmula da célula D8 para as células D9, E8 e E9.
 O endereço da coluna dos juros não se alterou nas cópias realizadas, $B é o 
endereço absoluto de coluna.
Informática Aplicada à Gestão Informática Aplicada à Gestão
O símbolo $ antes de uma parte do endereço de 
célula indica 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.
Figura 5: Ênfase na coluna D – mês 1
 Vamos agora conhecer algumas funções do Excel.
 No exemplo do Orçamento doméstico nos 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 linhas20, 21 e 22 no menu Página 
Inicial clique em Inserir. O Excel vai inseri 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 ao lado.
 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 que o Excel vai selecionar toda a região 
acima da célula. Incluindo o Total das despesas. 
Selecione a região que contenha apenas os valores 
das despesas do mês de janeiro (B11:B18).
Figura 6: Acesso ao menu AutoSoma
 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).
 Agora você pode copiar as fórmulas de Janeiro para os outros meses formatar os 
resultados.
07
Informática Aplicada à Gestão Informática Aplicada à Gestão
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).
08
 Figura 7: Planilha Orçamento doméstico – formatação 
 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.
 Figura 8: Planilha de Orçamento – código do produto
Informática Aplicada à Gestão Informática Aplicada à Gestão
 Na figura abaixo os produtos e serviços existentes na Empresa Limpa tudo em 
Geral.
 Figura 9: Planilha de produtos e serviços
 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ódigo 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 exibido 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á sabemos como saber se a célula está vazia, vamos aprender a 
decidir com base nesta informação.
09
Informática Aplicada à Gestão Informática Aplicada à Gestão
confirmar com luiz sobre quadro
10
 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.
 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 endereç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 contrá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 resultado 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 Informática Aplicada à Gestão
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 Produtos 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.
 O Excel apresenta um conjunto de recursos para construção de fórmulas que 
tornam as planilhas um instrumento de tomada de decisão muito importante.
 Profissionais de várias áreas podem encontrar no Excel 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 problemas do dia-a-dia.
 Algumas funções não dependem de conceitos sofisticados, são ligadas a 
conceitos simples que podem ajuda-lo a solucionar problemas mais complexos.
11
Informática Aplicada à Gestão Informática Aplicada à Gestã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.”
 , para verificar cada parte 
12
Função Lógica – retorna um valor lógico: VERDADEIRO ou FALSO.
Erro de Fórmula – indica que a fórmula não pode ser calculada.
 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 ferramentas de verificação de fórmulas, 
com elas você pode verificar 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 planilha ou podemos importar 
os dados diretamente do Banco de Dados da empresa. Na disciplina de Sistemas de 
Informação (3º semestre) você vai conhecer como a tecnologia 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 aula vamos conhecer a solução para importar dados e analisa-los.
 Então bons estudos e até a próxima aula.
ALMEIDA, J. T. S. Cálculos Financeiros Com Excel 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ásicas. SP: Erica, 2007.
Informática Aplicada à Gestão Informática Aplicada à Gestão
 Nessa unidade de aprendizagem, vamos prosseguir com a construção de uma 
planilha, fazendo uso de funções mais sofisticadas. 
13
Informática Aplicada à Gestão Informática Aplicada à Gestão