Relatórios Avançados com Excel 2013

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: novatec@novatec.com.br 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