Júlio César Scheiffer Saleh
Novatec
© Novatec Editora Ltda. 2013.
Todos os direitos reservados e protegidos pela Lei 9.610 de 19/02/1998. É proibida a
reprodução desta obra, mesmo parcial, por qualquer processo, sem prévia autorização,
por escrito, do autor e da Editora.
Editor: Rubens Prates
Revisão gramatical: Marta Almeida de Sá
Editoração eletrônica: Carolina Kuwabata
Capa: Carolina Kuwabata/ Júlio César Scheiffer Saleh
ISBN: 978-85-7522-353-6
Histórico de impressões:
Novembro/2013
Primeira edição
Novatec Editora Ltda.
Rua Luís Antônio dos Santos 110
02460-000 – São Paulo, SP – Brasil
Tel.: +55 11 2959-6529
Fax: +55 11 2950-8869
E-mail: [email protected]
Site: www.novatec.com.br
Twitter: twitter.com/novateceditora
Facebook: facebook.com/novatec
LinkedIn: linkedin.com/in/novatec
XX20131107
capítulo 1
Revisão de funções
1.1 Funções: Lógica e Financeira
Neste capítulo serão abordadas algumas funções e também soluções a
problemas facilmente encontrados no dia a dia de um usuário do Excel
para os quais nem sempre a solução é simples.
Iniciaremos com um exemplo prático da compra de um carro em que
precisamos descobrir o total gasto no financiamento. Para isso utilizamos
a função VF (Função Financeira), fornecendo os seguintes argumentos:
a.Taxa: taxa de juros ao mês (por exemplo, 1%).
b. Nper: número de parcelas (por exemplo, 48).
c. Pgto: valor de cada parcela (por exemplo, R$ 600,00).
Neste caso não utilizaremos o argumento VP, que seria equivalente à
entrada. Lembrando que os juros são considerados compostos e, neste
caso, temos como resultado a função mostrada na figura 1.1:
Figura 1.1
25
26
Relatórios Avançados com Excel 2013
A função SE (Função Lógica) nos ajuda a definir valores para possibilidades
diferentes de testes lógicos. Em expressões mais complexas, utilizamos
a função SE aninhada (mais de uma função ao mesmo tempo) ou em
conjunto com outras funções como E, OU, etc. A seguir será apresentado
um exemplo relacionado à aprovação de um aluno no final do ano letivo.
No exemplo da figura 1.2, utilizamos a seguinte regra: o aluno é obrigado
a ter média final maior que 7 e também frequência maior ou igual a 75%.
Na média abaixo de 5, o aluno é automaticamente reprovado. Para os
demais valores, temos os alunos que ficam em recuperação.
Figura 1.2
1.2 Funções: Texto e Data/Hora
Muitas vezes utilizamos dados provenientes de sistemas. Essas informações podem vir alteradas, com caracteres a mais (principalmente espaços),
atrapalhando funções que fazem comparativos. Para eliminá-los através
de fórmulas, podemos combinar algumas funções de texto:
a. ARRUMAR (texto): remove os espaços de uma sequência de caracteres
de texto, exceto os espaços simples entre palavras.
b. TIRAR (texto): remove todos os caracteres não imprimíveis.
c. SUBSTITUIR (texto, texto antigo, texto novo): substitui um texto antigo por
outro novo.
27
Capítulo 1 ■ Revisão de funções
Figura 1.3
A função substitui cada caractere de espaço incondicional (valor Unicode
160) por um caractere de espaço (valor ASCII 32) usando a função SUBSTITUIR
e, em seguida, remove os espaços à direita e os vários espaços incorporados
na sequência de caracteres “ BD 122” (BD 112).
Continuando com funções de texto, podemos destacar a função EXT.
TEXTO, que é responsável por extrair informação específica de um texto.
Nesta função precisamos apenas informar a posição e o comprimento
da informação a ser extraída. Na figura 1.4 recuperamos apenas o ano a
partir do sétimo caractere da coluna D num total de quatro caracteres.
Figura 1.4
Dentro da biblioteca de funções de data e hora podemos destacar a função
DIATRABALHOTOTAL, que calcula a quantidade de dias úteis entre duas datas
e pode opcionalmente adicionar feriados.
Segue o exemplo na figura 1.5:
Figura 1.5
28
Relatórios Avançados com Excel 2013
Em alguns casos, precisamos fazer outros tipos de operação como, por
exemplo, para saber a quantidade de dias que um serviço (ainda em execução) levará para o término da atividade, como é mostrado na figura 1.6.
Figura 1.6
O resultado é definido em dias. No caso da figura 1.6, seriam 16 dias e 6%
de um dia. A função AGORA traz a data/hora do sistema.
Para converter em data os valores de dia, mês e ano separados, basta usar
a função DATA como no exemplo da figura 1.7.
Figura 1.7
1.3 Funções: Pesquisa/Referência e Estatística
As funções PROCV e PROCH são muito utilizadas e servem para retornar um
determinado valor de uma tabela de acordo com um item a ser procurado.
Também podemos utilizar esta função para verificar a diferença de itens
(registros) entre tabelas.
Devemos levar em conta algumas regras importantes:
a. Se existir mais de uma referência encontrada na tabela matriz, a
função sempre retornará somente a primeira.
29
Capítulo 1 ■ Revisão de funções
b. A primeira coluna da tabela matriz deverá sempre ter relação com
o valor procurado (item em comum nas duas tabelas).
c. Verificar sempre a possibilidade de crescimento da tabela matriz,
fixando normalmente a referência da mesma por meio de colunas
(PROCV) ou linhas (PROCH), e não de intervalo (sendo A:B em
vez de A2:B50).
d. Nunca será possível inserir valores negativos no terceiro argumento:
num_índice_coluna (PROCV) e num_índice_lin (PROCH).
e. Verificar se o valor procurado não possui diferenças com o valor
da tabela matriz: acentuação, espaços, caracteres especiais etc.
Na figura 1.8 temos um exemplo simples de PROCV, em que o objetivo é
verificar a diferença entre as tabelas.
Figura 1.8
No caso da figura 1.8, verificamos que as matrículas 678, 890 e 789 não
estão na tabela matriz (tabela 2) e provavelmente devem ser inseridas
(atualizadas).
A célula B4 (valor 678) é o item a ser procurado no intervalo E:G (matriz
tabela) e o número 2 significa que será retornada a segunda coluna da
minha tabela matriz, neste caso, o valor correspondente em F ou o cargo.
O valor zero no último argumento refere-se à procura ter correspondência
exata, isto é, o valor deve corresponder exatamente ao que procuramos.
Se ocultarmos ou colocarmos o valor 1, teremos a correspondência aproximada. Neste caso, o Excel nos traz o primeiro valor aproximado.
30
Relatórios Avançados com Excel 2013
Este último argumento com o valor 1 só serve para definir faixa de valores numéricos, pois este sempre trará o valor próximo mais abaixo do
pesquisado na função PROCV, como mostra a figura 1.9.
Figura 1.9
A seguir, na figura 1.10, temos um exemplo do uso da função PROCH. Ela
tem a mesma estrutura do PROCV, porém a pesquisa do valor procurado
é pela linha (horizontal).
Figura 1.10
O primeiro argumento (valor procurado) é a célula P11 (item eixos). O
segundo argumento corresponde às linhas a serem pesquisadas (3 a 8).
O terceiro argumento refere-se à linha que gostaríamos que retornasse,
31
Capítulo 1 ■ Revisão de funções
neste caso, a linha do mês Abril (quinta linha a partir da linha 3). E por
último o argumento zero refere-se a uma correspondência exata, isto é, a
palavra “eixos” deve ser encontrada da mesma forma. Se houver alguma
acentuação diferente ou letra/espaço a mais, o resultado não aparecerá
(ficará como #N/D).
No exemplo da figura 1.11, temos uma junção de funções SE e MÁXIMO utilizando chaves envolvendo a fórmula ({}). Esta fórmula de matriz (array) é
obtida por meio do atalho CTRL + SHIFT + ENTER. Neste exemplo, o objetivo é
inserir o nome de um vendedor e ter como retorno o maior valor de venda.
Figura 1.11
No exemplo da figura 1.12 é elaborada a fórmula para retorno da formação
por meio da pesquisa pelo nome (José) utilizando a função ÍNDICE (retorna
o valor por meio da linha e da coluna fornecidas) e CORRESP (que retorna
o valor da linha).
Para exemplificar, a função que está na célula Z10 =ÍNDICE(Z1:AC5;2;3) traz
o valor da tabela na linha 2 e na coluna 3, equivalente a 31.
Já a função que está na célula Z15 =CORRESP(“Débora”;AA:AA;0) traz o valor
da linha que corresponde ao nome Débora na coluna A.
32
Relatórios Avançados com Excel 2013
Figura 1.12
1.4 Tipos de erros de funções
Quando efetuamos cálculos no Excel, podemos encontrar expressões que
significam um tipo de erro ocorrido. Quando uma fórmula não pode
ser processada corretamente, o Excel exibe um valor de erro. Veja alguns
desses valores de erro demonstrados na figura 1.13.
Tipo de erro
Descrição
####
Esse erro ocorre quando uma coluna não é larga o bastante
ou quando é usada uma data ou hora negativa.
#DIV/0!
Ocorre quando um número é dividido por zero (0).
#N/D
Ocorre quando um valor não está disponível para uma função
ou fórmula.
#NOME?
Ocorre quando o Excel não reconhece o nome de uma função.
#NULL!
Ocorre quando você especifica uma interseção de duas áreas
que não se interceptam. O operador de interseção é um espaço
entre referências.
#REF!
Ocorre quando uma referência de célula é inválida.
#VALOR!
O Excel não consegue converter o texto no tipo de dado correto.
Figura 1.13