1.19 Funções
A criação de fórmulas para a execução de determinados cálculos pode ser uma tarefa complicada e demorada. Muitos dos tipos mais comuns e utilizadas de fórmulas já estão pré-definidas no Excel e têm a designação geral de FUNÇÕES. As funções são fórmulas pré-definidas que fazem cálculos utilizando valores específicos, através da chamada de argumentos, numa forma particular denominada sintaxe. O Excel contém mais de trezentas funções, as quais se encontram agrupadas em diversas categorias: estatística, matemática e trigonometria, financeira, base de dados (Dfunctions), com datas e horas, lógica, procura de dados e texto. Uma função típica em Excel pode-se dividir em três partes:
=Nome_da_Função (Argumentos)
Na figura seguinte identificam-se as partes fundamentais duma função em Excel:
1.19.1 O assistente para a construção de funções
A construção de funções pode ser relativamente difícil em diversas situações, nomeadamente no caso daquelas que trabalham com vários argumentos. Para utilizar o assistente das funções devem seguir-se estes passos:
1. Seleccionar a célula onde se quer colocar a função;
2. Premir o botão 
Insert Function presente na barra de ferramentas standard;
3. Na janela Paste Function que se abre escolhe-se o tipo de função pretendido e o respectivo nome:
4. Depois de escolher a função abre-se uma nova caixa de diálogo onde se deve em primeiro lugar ler a respectiva descrição. Após isso introduzem-se os valores em todos os argumentos que compõem a função escolhida:
5. Depois de se fechar a janela do assistente das funções dá-se como concluído o processo de escrita da função.
1.19.2 AVERAGE (MÉDIA)
A função para o cálculo da média designa-se por AVERAGE e tem a sintaxe: AVERAGE(bloco_de_células) Exemplo:
1.19.3 SUM (SOMA)
Esta função faz a soma de uma série de valores.
Sintaxe:
SUM(bloco_de_células)
Exemplo 1:
SUM(2;3;5) devolve 10
Exemplo 2:
Suponha-se que B3:B6 contêm 32, 54, 75, 86:
SUM(B3:B6) devolve 247
1.19.4 MAX (MÁXIMO) e MIN (MÍNIMO)
A função MAX devolve o valor mais alto de um dado conjunto e o MIN diz qual é o valor mais baixo.
Sintaxe:
MAX(bloco_de_células)
MIN(bloco_de_células)
1.19.5 COUNT (CONTAR)
Esta função realiza a contagem dos elementos que constituem uma determinada lista de dados.
Sintaxe:
COUNT(bloco_de_células)
Exemplo:
COUNT(2;6;8;9;13;16) = 6
1.19.6 SUMIF (SOMA.SE)
Soma as células de acordo com uma determinada condição.
Sintaxe:
SUMIF(bloco;condição;bloco_a_somar)
BLOCO - células a testar
CONDIÇÃO - define as células a somar. Exemplos: “32”, “>55”, “maçãs”.
BLOCO_A_SOMAR - são as células que vão ser somadas se as correspondentes no bloco satisfizerem a condição.
Exemplo:
As células A1:A4 contém os seguintes valores de ordenados: 100000, 200000, 300000, 400000, respectivamente. B1:B4 contém as comissões de vendas correspondentes: 7000, 14000, 21000, 28000.
SUMIF(A1:A4,">160000",B1:B4) iguala 63000
1.19.7 PRODUCT (PRODUTO)
Multiplica todos os argumentos e devolve o produto.
Sintaxe:
PRODUCT(número1; número2;...)
Exemplo 1:
Se as células A2:C2 contiverem 5,15 e 30 então:
PRODUCT(A2:C2) é 2250
Exemplo 2:
PRODUCT(A2:C2;2) devolve 4500
1.19.8 SUMPRODUCT (SOMARPRODUTO)
Multiplica os elementos correspondentes nos arrays (argumentos) e devolve a soma desses produtos.
Sintaxe:
SUMPRODUCT(array1; array2;...)
Exemplo:
Tomando em consideração os dois arrays presentes na figura acima a função SUMPRODUCT realiza a seguinte operação:
3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3 = 156
ou seja,
SUMPRODUCT(A1:B3; D1:E3) devolve 156
1.19.9 Funções de Consulta
1.19.9.1 VLOOKUP (PROCV)
A função VLOOKUP compara o valor pesquisado com a primeira coluna de uma lista de dados e devolve o valor associado na mesma linha e na coluna indicada.
Sintaxe:
VLOOKUP(valor_a_pesquisar; tabela; coluna_a_ler; método_c)
Valor_a_pesquisar - valor a encontrar (número, string ou uma referência);
Tabela - Lista de dados;
Coluna_a_ler - coluna de onde é retirado a valor;
Método_c - Se FALSE VLOOKUP faz uma procura exacta. Se este parâmetro for igual a TRUE ou se for omitido a pesquisa será aproximada.
Exemplo 1:
VLOOKUP(1;A2:C10;1;TRUE) devolve 0.946
VLOOKUP(0.746;A2:C10;3;FALSE) iguala 200
VLOOKUP(0.1;A2:C10;2;TRUE) devolve #N/A
Exemplo 2:
Considere a folha de cálculo da Figura 1-17 em que o objectivo é preencher as colunas I e J com os valores das colunas M e N de acordo com a pontuação “Final” de cada aluno.
Figura 1-17: Exemplo de aplicação da função VLOOKUP.
Passos a seguir:
Figura 1-18: Utilização do Assistente para o caso da função VLOOKUP.
1. Clicar na célula I2;
2. Clicar no botão Insert Function;
3. Na janela da Insert Function que aparece escolher Lookup & Reference na categoria de função e, finalmente, seleccionar a função VLOOKUP;
4. Clicar OK;
5. Escrever H2, como se vê na Figura 1-19, na caixa do Lookup_Value;
6. Clicar no botão de contracção da linha Table_array;
Figura 1-19: Escola dos parâmetros para a função VLOOKUP.
7. Marcar L2 até N5 e clicar no botão de expansão;
8. Tornar absolutas as referências do ponto anterior.
9. Escrever 2 na linha do Col_index_num;
10. Clicar OK
11. Copiar o conteúdo da célula I2 para a célula J2 alterando o valor do Lookup_Value para H2. Modificar a Col_index_num para 3;
12. Seleccionar simultaneamente as colunas I2 e J2 e arrastar até à linha 5. O resultado final mostra-se na Figura 1-20.
Figura 1-20: Resultado final da aplicação da função VLOOKUP.
1.19.9.2 HLOOKUP (PROCH)
Compara o valor pesquisado na primeira linha da lista e devolve o valor associado na mesma coluna mas na linha especificada.
Sintaxe:
HLOOKUP(valor_a_pesquisar; tabela; linha_a_ler; método_c)
Valor_a_pesquisar - valor a encontrar (número, string ou uma referência);
Tabela - Lista de dados;
Linha_a_ler - linha de onde é retirado a valor;
Método_c - Se FALSE HLOOKUP faz uma procura exacta. Se este parâmetro for igual a TRUE ou se for omitido a pesquisa será aproximada.
Exemplo (os dados são os mesmos da função Vlookup):
HLOOKUP(1;A2:C10;1;TRUE) devolve 0.457
HLOOKUP(3.55;A2:C10;3;FALSE) iguala 2.93
HLOOKUP(3.57;A2:C10;6;FALSE) devolve #N/A
1.19.10 Funções Lógicas
1.19.10.1 A função OR (OU)
Esta função devolve VERDADE se pelo menos um dos argumentos for verdadeiro; devolve FALSO se todos os argumentos forem falsos.
Sintaxe:
OR(valor_lógico1; valor_lógico2;...)
Exemplos:
• OR(1+1=2;2+2=5) iguala VERDADE
• OR(1+1=1;2+2=5) devolve FALSO
1.19.10.2 A função AND (E)
A função AND devolve VERDADE se todos os argumentos forem verdadeiros; devolve FALSO se pelo menos um dos argumentos for falso.
Sintaxe:
AND(valor_lógico1; valor_lógico2;...)
Exemplos:
• AND(1+1=2;2+2=4) iguala VERDADE
• AND(1+1=1;2+2=4) devolve FALSO
1.19.10.3 A função NOT (NÃO)
Troca o valor lógico da expressão. Se é FALSO NOT devolve VERDADE e vice-versa.
Sintaxe:
NOT(valor_lógico)
Exemplos:
• NOT(1+1=2) iguala FALSO
• NOT(1+1=3) devolve VERDADE
1.19.10.4 A função IF (SE)
Devolve um valor se a condição testada for VERDADE e outro valor se o teste der NEGATIVO. O IF utiliza-se para fazer testes condicionais a valores e fórmulas.
Sintaxe:
IF(teste_lógico,valor_se_verdade,valor_se_falso)
Exemplo 1:
Se o valor na célula A10 for 100, então o teste lógico dá VERDADEIRO e é calculada a soma do bloco B5:B15. Pelo contrário, se o teste der FALSO é devolvida uma string em branco.
IF(A10=100,SUM(B5:B15),””)
Exemplo 2:
Suponha-se uma folha que tem em B2:B4 os dados “Despesas Mensais” para Janeiro, Fevereiro e Março: 1500, 500,500. E C2:C4 contém as “Despesas Orçamentadas” para o mesmo período: 900,900,925.
IF(B2>C2,”Deficite”,”OK”) devolve “Deficite”
IF(B3>C3,”Deficite”,”OK”) iguala “OK”
Exemplo 3:
Neste caso vai-se usar o caso de estudo utilizado num dos exemplos de aplicação da função VLOOKUP no capítulo 1.19.9.1, para obter o mesmo resultado mas agora utilizando a função IF.
1. Clicar na célula I2;
2. Clicar no ícone Insert Function;
4. Na janela da Insert Function que aparece escolher Logical na categoria de função e, finalmente, seleccionar a função IF como se vê na .
5. Clicar OK;
6. Escrever H2>=18 na caixa Logical_test
7. Clicar na caixa Value_if_true
8. Escrever “Muito Bom”
9. Clicar na caixa Value_if_false
10. Escrever: IF(H2>=15,”Bom”,IF(H2>=10,”Suficiente”,”Insuficiente”))
11. Clicar OK, e a fórmula fica assim: =IF(H2>=18;"Muito Bom";IF(H2>=15;"Bom";IF(H2>=10;"Suficiente";"Insuficiente")))
12. Copiar a fórmula até à célula I5 como se vê na Figura 1-21.
Figura 1-21: A função IF() em substituição da função VLOOKUP(). 1ª Parte.
13. Copiar a fórmula que está em I2 e copiá-la para J2
14. Em seguida alterar na fórmula o seguinte: “Muito Bom” para “Aprov. Distinção”; “Bom”, “Suficiente” to “Aprovado” e “Insuficiente” para “Reprovado”.
15. Premir A fórmula em J2 fica com esta forma: =IF(H2>=18;"Aprov. Distinção";IF(H2>=15;"Aprovado";IF(H2>=10;"Aprovado";"Reprovado")))&nb sp;
16. Copiar a formula até à célula J5 como se vê na Figura 1-22.
Figura 1-22: A função IF() em substituição da função VLOOKUP(). 2ª Parte.
1.19.11 Funções Estatísticas
1.19.11.1 COUNTIF (CONTAR.SE)
Conta o número de células num bloco que não estejam em branco, como a função COUNT, mas neste caso só aquelas que obedecem a uma determinada condição.
Sintaxe:
COUNTIF(bloco;condição)
Exemplo 1:
Supunha-se que A3:A6 contém ”maças", ”laranjas", ”pêssegos", ”maçãs":
COUNTIF(A3:A6;”maçãs") devolve 2
Exemplo 2:
Suponha-se que B3:B6 contém 32, 54, 75, 86:
COUNTIF(B3:B6,">55") devolve 2
Exemplo 3:
Considere a tabela seguinte e suponha que quer saber quantos pomares tiveram uma produção superior a 700 Kg h-1:
COUNTIF(B2:B7,">700") devolve 3
1.19.11.2 CORREL (CORREL)
Esta função serve para determinar o coeficiente de correlação entre os valores de dois conjuntos de dados.
Sintaxe:
CORREL(bloco_1;bloco_2)
Exemplo 1:
No quadro seguinte apresentam-se os dados referentes a vários anos que relacionam a produção de milho-forragem com a adubação azotada e pretende-se determinar a correlação entre os dois factores.
=CORREL(B2:B12;C2:C12)
O resultado desta operação é: 0,970156 e a conclusão é de existe uma forte relação linear entre a produção e a adubação, a um incremento na fertilização corresponde um acréscimo na produção. A correspondência entre as duas variáveis pode ser visualizada na Figura 1-23:
Figura 1-23: Relação entre a produção e a adubação.
1.19.11.3 STDEV (DESVPAD)
Esta função calcula o desvio padrão que é uma medida aproximada da diferença dos dados relativamente à média.
Sintaxe:
STDEV(bloco)
Exemplo 1:
No quadro seguinte apresentam-se os dados referentes a vários anos que relacionam a produção de milho-forragem com a adubação azotada e pretende-se determinar o desvio padrão relativamente à produção.
=STDEV(B2:B12)
Assim, o desvio padrão corresponde a 274,1336.
1.19.12 Funções financeiras
As aplicações de folha de cálculo, como é o caso do Excel ou do OpenOffice, contêm, prontas a serem utilizadas, muitas dezenas de funções na área da gestão financeira. Estas funções permitem calcular, por exemplo, o pagamento mensal de um empréstimo, o valor futuro de uma anuidade ou a taxa de retorno de um investimento.
Os factores taxa de juro e risco estão no cerne da maior parte das fórmulas financeiras. Ou seja, aqueles factores permitem a comparação entre o “gastar” de um euro agora versus “gastar” esse mesmo euro no futuro acrescido de uma recompensa pelo “passar” do tempo, o chamado “prémio”. Isso consegue-se fazer comparando o “valor actual” de uma importância contra o esperado “valor futuro” dessa mesma quantia. A relação entre estes dois valores é a seguinte:
a) Valor Futuro = Valor Actual + Taxa de Juro
b) Valor Actual = Valor Futuro – Taxa de Desconto
A maioria das fórmulas desta classe incluem aqueles três factores (Valor Actual, Valor Futuro, Juro - ou de Desconto -), a que se juntam outros dois: o número de períodos, o número de pagamentos ou depósitos realizados durante um empréstimo ou investimento; e o pagamento, que é a quantidade de dinheiro pago ou investido em cada período.
1.19.12.1 Cálculo do pagamento de um empréstimo
Quando alguém negoceia um empréstimo para adquirir um equipamento, ou uma habitação, a principal preocupação é, quase sempre, aquilo que vai pagar em cada período, por forma a que o seu orçamento não fique desequilibrado.
Para fazer o cálculo desse pagamento periódico usa-se a função:
PMT(rate, nper, pv, [fv], [type])
em que:
rate: taxa, fixa, de juro durante o empréstimo;
nper: o número de pagamentos ao longo do empréstimo;
pv: a quantia emprestada;
fv: o valor futuro do empréstimo
type: o tipo de pagamento. Por defeito é ‘0’ para pagamento no fim do período. Para pagamento no início do período o valor é ‘1’.
Exemplo 1:
Neste exemplo vai-se calcular o pagamento mensal de um empréstimo de 65.000,00 € por 15 anos a 3,9% de juro anual.
=PMT(0,039/12;15*12;65000)
Neste tipo de funções é muito vantajoso que em vez de se escrever uma fórmula com os valores e faça antes uma pequena folha de cálculo em que dispõem esses valores em células, como é o caso da Figura 1-24. Para obter várias simulações basta ir alterando os valores nas células pertinentes.
Figura 1-24: Exemplo de utilização da função PMT().
Duas notas sobre a função PMT():
• Como a taxa de juro é anual e os períodos são expressos em anos para obter um pagamento mensal é necessário converter esses valores para os seus equivalentes mensais. Ou seja, multiplicar o período por 12 e dividir por 12 a taxa de juro;
• A função devolve um valor negativo dado que do ponto de vista da pessoa que pede o empréstimo os pagamentos são fluxos negativos no seu orçamento.
Exemplo 2:
Neste exemplo vai-se analisar um empréstimo com um valor residual. Em muitos empréstimos os pagamentos são feitos apenas com base numa parte da quantia emprestada, sendo o restante devido no fim da duração do empréstimo. O valor residual é considerado o valor futuro do empréstimo pelo que vai ocupar o lugar do parâmetro fv na função PMT(). No caso de empréstimos com valor residual o valor emprestado é sempre a totalidade do dinheiro dado que são sempre devidos juros pelo valor residual.
Figura 1-25: Utilização da função PMT() com valor residual.
1.19.13 Funções sobre listas de dados
No Excel existem uma série de funções que permitem analisar os dados armazenados numa lista. Estas funções têm o nome genérico de Dfunctions e usam três argumentos: lista, campo e condição.
Sintaxe:
DFUNCTION(lista; campo; condição)
Lista - é o bloco de células que compõem os dados a analisar;
Campo - indica qual é a coluna utilizada na função;
Condição - é a referência ao bloco de células que contêm as condições para aplicação da função.
1.19.13.1 Regras para a criação de listas no Excel
1- Localização da lista:
• Não colocar mais de uma lista por folha;
• Colocar pelo menos uma linha e uma coluna em branco entre a lista e outros dados que se encontrem na mesma folha;
• Não colocar linhas ou colunas em branco no interior da lista.
2. Cabeçalhos das colunas
• Criar cabeçalhos na primeira linha da lista;
• Utilizar nos cabeçalhos uma fonte, cor, formatação ou esquadria diferentes dos da lista em si própria.
1.19.13.2 Funções sobre listas
Para a análise de listas de dados vão ser estudadas as seguintes funções: DMAX (Bdmáx), DMIN (Bdmín), DAVERAGE (Bdmédia), DGET, DCOUNT (Bdcontar), DSUM (Bdsoma) e DSTDEV (Bddesvpad) todas elas têm a mesma finalidade das funções normais (i.e., DCOUNT faz contagens como a sua congénere COUNT) excepto a função DGET que será analisada em detalhe.
1.19.13.2.1 Função DGET (Bdobter)
Extrai um único valor da coluna duma lista que obedeça a uma determinada condição.
Sintaxe:
DGET(lista; campo; condição)
Notas:
Se não existir nenhum valor que obedeça à condição, DGET devolve a mensagem de erro #VALUE! .
Se houver mais do que um valor que obedeça à condição, DGET devolve a mensagem de erro #NUM! .
Exemplo 1:
DCOUNT(Lista;”Altura”;A1:F2) iguala 1
DMAX(A6:E12;5;A1:A3) devolve 105
DMIN(Lista;”Lucro”;A1:B2) iguala 75
DSUM(Lista;5;A1:A2) devolve 225
DSUM(Lista;”Lucro”;A1:F2) iguala 75
DAVERAGE(Lista;”Produção”;A1:B2) devolve 12
DAVERAGE(Lista;3;Lista) iguala 13
DCOUNT(Lista;”Altura”;A1:F3) iguala 3
DGET(Lista;”Árvore”;A1:F3) = #NUM!
DGET(A6:E12;1;A1:F2) devolve Macieira
DGET(Lista;”Árvore”;A1:F2) iguala #VALUE! ( 4 ) Exemplo 2:
Sobre o grupo de dados apresentado na figura anterior vão-se formular estas questões:
1. Nº de Alunos com notas > 14 valores na 2ª Frequência;
2. Nº de Alunos com notas > 10 valores na 2ª Frequência de Informática I;
3. Nota média do Aluno nº100;
4. Melhor nota em Informática I na 1ª Frequência. Sendo a resposta a cada uma dessas questões as fórmulas seguintes:
Carlos P. Caldeira
Universidade de Évora
