Departamento de Informática

INTRODUÇÃO ÀS FOLHAS DE CÁLCULO

Prof. Carlos Pampulim Caldeira

Évora, Versão Primavera 2006

 ÍNDICE

1. EXEMPLO DE UMA APLICAÇÃO DE FOLHA DE CÁLCULO .................................................1

1.1 INTRODUÇÃO..................................................................................................................................1
1.2 COMO ACEDER AO EXCEL ..............................................................................................................1
1.3 A JANELA DO EXCEL ......................................................................................................................2
1.4 DESLOCAÇÃO DO CURSOR NA FOLHA .............................................................................................2
Numa só janela .......................................................................................................................................2
Entre janelas ...........................................................................................................................................3
1.5 CRIAÇÃO DE UM NOVO LIVRO (WORKBOOK)...................................................................................3
1.6 COMO GRAVAR...............................................................................................................................3
1.6.1 Gravar pela primeira vez .........................................................................................................4
1.6.2 Regravar ...................................................................................................................................4
1.6.3 Gravar com um nome diferente e/ou para outra pasta ............................................................5
1.7 ABRIR UM LIVRO A PARTIR DO EXCEL ............................................................................................6
1.8 COMO FECHAR O EXCEL .................................................................................................................6
1.8.1 Através do comando Exit..........................................................................................................6
1.8.2 Usando as teclas.......................................................................................................................7
1.9 O CONCEITO DE OBJECTOS NAS FOLHAS DE CÁLCULO .....................................................................8
1.10 AS CÉLULAS DO EXCEL ..................................................................................................................8
1.10.1 A área de trabalho e a célula activa....................................................................................8
1.10.2 Qual é o tamanho máximo de uma folha de cálculo?........................................................10
1.10.3 A selecção de células .........................................................................................................10
[Link] Selecção de células contíguas ......................................................................................................11
[Link] Selecção de células não contíguas................................................................................................11
[Link] Como referenciar blocos de células .............................................................................................11
1.11 FORMATAÇÃO DE CÉLULAS ..........................................................................................................12
1.12 O ESSENCIAL SOBRE FÓRMULAS...................................................................................................14
1.12.1 A constituição de uma fórmula ..........................................................................................14
1.13 PREENCHIMENTO AUTOMÁTICO E CRIAÇÃO DE SÉRIES .................................................................14
1.14 REFERÊNCIAS ...............................................................................................................................17
1.14.1 Relativas ............................................................................................................................17
1.14.2 Absolutas............................................................................................................................17
1.14.3 Mistas.................................................................................................................................17
1.14.4 Referências a outras folhas................................................................................................18
1.14.5 Aplicação das referências..................................................................................................18
1.14.6 Exemplo de aplicação das referências ..............................................................................19
1.15 ATRIBUIÇÃO DE NOMES A CÉLULAS E BLOCOS ..............................................................................21
1.16 OPERADORES ...............................................................................................................................23
1.16.1 Aritméticos .........................................................................................................................24
1.16.2 Comparação ......................................................................................................................24
1.16.3 Texto ..................................................................................................................................24
1.16.4 Referência ..........................................................................................................................25
1.16.5 Precedência dos operadores..............................................................................................25
1.17 FILTRO AUTOMÁTICO...................................................................................................................26
1.18 ORDENAÇÃO DE DADOS ...............................................................................................................29
1.19 FUNÇÕES ......................................................................................................................................31
1.19.1 O assistente para a construção de funções........................................................................32
1.19.2 AVERAGE (MÉDIA)..........................................................................................................33
1.19.3 SUM (SOMA).....................................................................................................................34
1.19.4 MAX (MÁXIMO) e MIN (MÍNIMO) ..................................................................................35
1.19.5 COUNT (CONTAR) ...........................................................................................................35
1.19.6 SUMIF ([Link])............................................................................................................35
1.19.7 PRODUCT (PRODUTO)...................................................................................................36
1.19.8 SUMPRODUCT (SOMARPRODUTO) .............................................................................36
1.19.9 Funções de Consulta..........................................................................................................37
[Link] VLOOKUP (PROCV)..................................................................................................................37
[Link] HLOOKUP (PROCH)..................................................................................................................39
1.19.10 Funções Lógicas ................................................................................................................40
[Link] A função OR (OU).......................................................................................................................40
[Link] A função AND (E) .......................................................................................................................40
[Link] A função NOT (NÃO) .................................................................................................................41
[Link] A função IF (SE) ..........................................................................................................................41
1.19.11 Funções Estatísticas ..........................................................................................................44
[Link] COUNTIF ([Link])...........................................................................................................44
[Link] CORREL (CORREL) ..................................................................................................................45
[Link] STDEV (DESVPAD)...................................................................................................................46
1.19.12 Funções financeiras...........................................................................................................46
[Link] Cálculo do pagamento de um empréstimo ...................................................................................47
1.19.13 Funções sobre listas de dados ...........................................................................................49
[Link] Regras para a criação de listas no Excel ......................................................................................50
[Link] Funções sobre listas .....................................................................................................................50
Universidade de Évora

1. Exemplo de uma aplicação de folha de cálculo

1.1 Introdução

O Microsoft Excel ou o OpenOffice são programas que pertencem à categoria

designada por Folhas de Cálculo que permitem inserir, organizar e analisar
informação. O utilizador pode guardar, manipular, calcular e analisar dados tais
como números, texto e fórmulas. Pode colocar um gráfico directamente na folha
de cálculo e ainda outros elementos gráficos tais como linhas, rectângulos,
caixas de texto entre outros.. Pode também ordenar procurar e organizar dados
numéricos e/ou alfanuméricos. As Folhas de Cálculo permitem ainda automatizar
todo o trabalho de modo a efectuar cálculos e tarefas específicas.

1.2 Como aceder ao Excel

1. Clicar no botão Start (Iniciar) situado na parte inferior esquerda do écran.

2. Posicionar o mouse em Programs (Programas).

3. Clicar em Microsoft Excel.

Departamento de Informática - C. Caldeira -1-

Universidade de Évora

1.3 A janela do Excel

Quando se começa a trabalhar no Excel está-se perante um workbook (livro) que

contém diversas janelas chamadas worksheets (folhas) que se denominam
Sheet1 (Folha1), Sheet2 (Folha2) e assim sucessivamente. O Excel tem o
seguinte écran inicial:

1.4 Deslocação do cursor na folha

Existem diversos modos de movimentação do cursor numa folha de cálculo quer

numa só janela quer janela-a-janela de que se dão seguidamente alguns
exemplos.

Numa só janela

1. Para seleccionar uma célula basta clicar nela com o ponteiro do rato. Para
tornar activa, por exemplo, a célula A1 clica-se nela.

2. Para mover o cursor uma célula para a direita preme-se a tecla TAB, ou se
for para a esquerda SHIFT+TAB.

3. As teclas com setas podem ser utilizadas para deslocamentos verticais ou

horizontais.

Departamento de Informática - C. Caldeira -2-

Universidade de Évora

4. A tecla HOME desloca o cursor para a primeira coluna da folha de cálculo.

Entre janelas

1. Para deslocar o cursor uma janela para baixo preme-se PAGE DOWN.

2. A tecla PAGE UP provoca o deslocamento de uma janela para cima.

3. Para colocar o cursor na primeira célula duma folha de cálculo (a célula A1)
carrega-se nas teclas Ctrl+HOME.

4. A opção Go To presente no menu Edit possibilita a deslocação para uma

célula à escolha do utilizador.

1.5 Criação de um novo livro (workbook)

Para criar um novo livro de Excel podem-se seguir os passos deste exemplo:

1. No menu File clica-se em New.

2. Na opção General faz-se um duplo clique sobre o ícone Workbook para abrir
um novo.

3. Depois, por exemplo, clica-se na célula B2 e escreve-se Lista de Filmes.

4. Finalmente, no menu File escolhe-se a opção Save, dá-se o nome Filmes 1 e

clica-se em OK.

1.6 Como gravar

Depois de se criar um livro, independentemente do método utilizado, é

necessário guardá-lo numa pasta localizada num disco rígido ou numa disquete.
É preciso ter algum cuidado neste processo convém atribuir-lhe um nome
adequado e colocá-lo na pasta certa, sob pena de se tornar difícil a sua
localização quando for necessário voltar a trabalhar nesse mesmo livro.

Departamento de Informática - C. Caldeira -3-

Universidade de Évora

1.6.1 Gravar pela primeira vez

Para gravar um livro pela primeira vez devem-se seguir estes passos:

1. No menu File clica-se em Save.

2. Clicar na seta-de-opção Save in e escolher o local de destino.

3. Na caixa File name escreve-se o nome que se quer dar ao novo livro, por
exemplo Lista de Filmes.

4. Grava-se o ficheiro carregando no botão Save.

1.6.2 Regravar

Para gravar um livro já existente com o mesmo nome e na mesma pasta:

1. No menu File clica-se em Save.

2. Em seguida em OK.

3. Fecha-se a janela respectiva.

Departamento de Informática - C. Caldeira -4-

Universidade de Évora

1.6.3 Gravar com um nome diferente e/ou para outra pasta

Desde que não se especifique o contrário o Excel grava sempre o livro com o
mesmo nome e para a mesma pasta. Para gravar para outra pasta ou para
mudar o nome ao ficheiro devem-se observar os seguintes pontos:

1. Abrir, por exemplo, o livro “Lista de Filmes”.

2. No menu File clicar em Save As.

3. Na janela Save As carregar no botão Create New Folder .

4. Na linha do nome escrever “Lista de Filmes de 75 mm”.

5. Clicar em OK e depois em Save.

Departamento de Informática - C. Caldeira -5-

Universidade de Évora

1.7 Abrir um livro a partir do Excel

Quando se tem o Excel em funcionamento usa-se o seguinte procedimento para

abrir outro livro:

1. Carrega-se na opção Open presente no menu File.

2. Na janela de dialogo que aparece escolhe-se o ficheiro que se quer abrir

premindo-se seguidamente em Open.

1.8 Como fechar o Excel

Para fechar o Excel deve sempre seguir-se um procedimento adequado, caso

contrário o trabalho feito pode ser perdido. Existem diversas formas de fechar
esta aplicação:

1.8.1 Através do comando Exit

1. No menu File clicar em Exit.

Departamento de Informática - C. Caldeira -6-

Universidade de Évora

2. Na caixa de diálogo que aparece escolher Yes para gravar o livro e fechar o
Excel;

ou, clicar No para fechar o Excel sem gravar nada;

ou, escolher Cancel se afinal não se quiser fechar o livro.

1.8.2 Usando as teclas

1. Premir simultaneamente as teclas Alt+F+X;

ou, Alt+F4

ou, fazer duplo clic no canto superior esquerdo da janela sobre o ícone do Excel
97;

ou, clicar no botão colocado no canto superior direito da janela.

Departamento de Informática - C. Caldeira -7-

Universidade de Évora

1.9 O conceito de objectos nas folhas de cálculo

A designação programação por objectos é uma referência comum no mundo da

informática. Um objecto representa um elemento de software que pode ser
manipulado. Numa folha de cálculo este conceito pode ajudar os seus
utilizadores a perspectivarem os diversos constituintes deste tipo de ferramentas.
No Excel existem os seguintes objectos:

• O próprio Excel;

• Um livro;

• A folha de um livro;

• Um bloco (range) numa folha;

• Um botão numa folha;

• Uma “caixa de listagem” num formulário;

• Uma folha-gráfico;

• Um gráfico numa folha-gráfico;

• Os valores num gráfico.

1.10 As células do Excel

1.10.1 A área de trabalho e a célula activa

A área de trabalho do Excel é composta por um conjunto de rectângulos a que se

dá o nome de células:

Departamento de Informática - C. Caldeira -8-

Universidade de Évora

Uma célula pode conter caracteres alfanuméricos, numéricos, tipo data/hora ou

fórmulas. Cada célula só pode conter um desses itens de cada vez. Cada célula
é univocamente identificada por um par ordenado constituído por uma letra e um
número, em que a letra (maiúscula) representa a coluna e o número identifica a
linha. A célula localizada na intercepção
da coluna A com a linha 1 tem a
referência A1. Apesar de uma folha poder
conter um grande número de células só
existe uma única célula activa em cada
momento. A célula activa de uma folha de
cálculo é aquela que está pronta a
receber o input do utilizador.

Para tornar activa uma célula basta seleccioná-la, i.e., “ira para lá”, o que pode
ser feito, por exemplo, do seguinte modo:

1. Clicar com o rato sobre a célula.

2. Deslocar o cursor para a célula escolhida usando as teclas de setas.

Departamento de Informática - C. Caldeira -9-

Universidade de Évora

1.10.2 Qual é o tamanho máximo de uma folha de cálculo?

O tipo de objecto mais comum no Excel, e em todas as outras aplicações

semelhantes como, por exemplo, o OpenOffice, é a folha de cálculo. Cada uma
dessas folhas tem um tamanho fixo, e inalterável, de 256 colunas por 65 536
linhas.

Para se determinar o tamanho de uma folha basta multiplicar 256 por 65 536.
Assim, cada folha, mas um livro pode conter mais de uma folha, tem uma
capacidade total de 16 777 216 células. Um utilizador que fosse rápido a digitar
um carácter por célula demoraria 194 dias a preencher uma folha. Para imprimir
esse trabalho seriam necessárias 72 resmas de papel.

1.10.3 A selecção de células

A selecção de células pode ser feita com o rato quando este apresenta o sinal

Existem três tipos de selecção:

• Célula simples

• Conjunto de células

• rectângulo contíguo de células

• uma linha
• uma coluna
• toda a folha de cálculo

• Selecção múltipla

Departamento de Informática - C. Caldeira - 10 -

Universidade de Évora

[Link] Selecção de células contíguas

[Link] Selecção de células não contíguas

[Link] Como referenciar blocos de células

Um bloco de células contíguas pode ser referenciado colocando o símbolo :

entre a primeira e a última célula do bloco, por exemplo, para referenciar o bloco
entre as células B2 e E15 escreve-se B2:D10.

Departamento de Informática - C. Caldeira - 11 -

Universidade de Évora

Os blocos de células não contíguas identificam-se similarmente ao mencionando

atrás com a excepção de que se somam/unem os diferentes blocos, por
exemplo, para referenciar os blocos B2 até C10 e F5 até G16 escreve-se o
seguinte: B2:C10;F5:G16.

1.11 Formatação de células

Numa folha de cálculo existem dois tipos possíveis de formatação para as

células:

1) Numérica:

É a forma como um valor aparece numa célula. O Excel, com base no input,
aplica automaticamente alguns tipos de formatação, mas um outro formato
qualquer pode ser escolhido através do menu respectivo como se vê na Figura
1-1.

Figura 1-1: Janela de alteração da formatação das células.

Departamento de Informática - C. Caldeira - 12 -

Universidade de Évora

O tipo de formatação não afecta o valor armazenado na célula. Por exemplo,

uma célula que contenha o número “7,18678” e que seja formatada com apenas
duas casas decimais, vai mostrar o valor “7,19”, mas quando essa célula for
utilizada numa fórmula o valor utilizado vai ser o realmente armazenado
independentemente da formatação.

2) Estilo

Com a formatação de estilo pode-se modificar a aparência de uma célula e/ou

do seu conteúdo. No ecrã mostrado na Figura 1-2 pode-se, por exemplo,
modificar as cores, sombreado, forma dos caracteres ou bordaduras.

Figura 1-2: Janela da aplicação onde podem ser modificada a aparência de

células e/ou do seu conteúdo.

Departamento de Informática - C. Caldeira - 13 -

Universidade de Évora

1.12 O essencial sobre Fórmulas

Aqui vão ser referidos alguns princípios básicos sobre o funcionamento de

fórmulas nas folhas de cálculo.

1.12.1 A constituição de uma fórmula

Uma fórmula pode ser constituída por cinco elementos:

• Operadores (Ver o capítulo 1.16, página 23);

• Referências a células. Aqui estão incluídas as células, os blocos de

células e as células com nome atribuído (Ver os capítulos 1.14, página 17
e 1.15, página 21);

• Valores numéricos ou strings. Incluem-se, por exemplo, “34,56” ou

“Taxa de Juro”;

• Funções da folha de cálculo e os seus argumentos. Neste grupo cabe

um grande número de funções de que são exemplo a soma (SUM) ou a
média (AVERAGE) (Ver o capítulo 1.19, página 31);

• Parênteses. Como é habitual, no cálculo de expressões, permite controlar

a ordem de processamento numa fórmula.

1.13 Preenchimento automático e criação de séries

O conteúdo de células, incluindo fórmulas e formatações, pode ser copiado para

outras utilizando o controlo de preenchimento automático ou através da opção
Fill presente no menu Edit.

Departamento de Informática - C. Caldeira - 14 -

Universidade de Évora

Para estender o conteúdo de uma célula basta utilizar o botão de preenchimento

automático (Autofill, em inglês):

Uma série de valores pode ser criada a partir dos valores presentes num grupo
de células. Veja-se o seguinte exemplo:

Ou ainda:

Departamento de Informática - C. Caldeira - 15 -

Universidade de Évora

Departamento de Informática - C. Caldeira - 16 -

Universidade de Évora

1.14 Referências

A referência é a identificação/posição de uma célula numa folha de cálculo, como

por exemplo A1 (1ª célula duma folha). Com as referências podemos utilizar
dados localizados em áreas diferentes numa única fórmula ou o valor presente
numa célula em diferentes fórmulas.

Papel das referências:

• identificação de células ou blocos (ranges)

• quando utilizadas numa fórmula apontam para os valores a serem

utilizados

Existem três tipos de referências: relativas, absolutas e mistas.

1.14.1 Relativas

Uma referência relativa, como por exemplo A1, indica ao Excel como encontrar
outra célula a partir da célula que contém a fórmula.

1.14.2 Absolutas

Uma referência absoluta, como é o caso de $A$1, indica ao Excel como encontra
uma célula através da sua localização exacta na folha de cálculo

1.14.3 Mistas

Uma referência mista, como por exemplo A$1 ou $A1 diz ao Excel como
encontrar outra célula através da combinação da referência exacta a uma coluna
ou linha com a referência relativa a uma linha ou coluna.

Departamento de Informática - C. Caldeira - 17 -

Universidade de Évora

O símbolo $A1 indica que a referência à coluna é absoluta e a referência à linha

1 é relativa. Enquanto A$1 é ao contrário (a linha 1 é uma referência absoluta).

1.14.4 Referências a outras folhas

Tal como se pode usar uma referência para encontrar uma célula numa folha
também se podem procurar células noutras folhas do mesmo livro. A figura
seguinte dá um panorama geral sobre este tipo de referência.

1.14.5 Aplicação das referências

A cópia e a movimentação de células que contenham referências a outras

células ou blocos tem as seguintes consequências:

1. Mover células

Quando se movem células as referências relativas são ajustadas à sua nova

posição na folha e as absolutas mantêm-se. As referências que são feitas a
essas células (noutras células quaisquer) são automaticamente ajustadas por
forma a que se refiram às mesmas células na sua nova localização.

2. Cópia de células

A cópia de células influencia distintamente as referências absolutas e relativas.

As relativas são ajusta-as à área para onde foram copiadas e no caso das mistas

Departamento de Informática - C. Caldeira - 18 -

Universidade de Évora

apenas é alterada a parte relativa das referências. As referências absolutas não

são modificadas através da cópia.

1.14.6 Exemplo de aplicação das referências

Neste ponto vai ser abordado um exemplo sobre a utilização de referências

através do estudo de uma “Lista de Empregados”, em que para cada um deles
se sabe o número de dias efectivos de trabalho por mês e o objectivo é o de
calcular o ordenado a ser pago nesse mês.

Figura 1-3: Utilização da referência a uma célula.

1. O cálculo do ordenado para o funcionário João Manuel faz-se multiplicando a

célula D3 (nº de dias de trabalho no mês) pela F4 (valor da remuneração
diária), i.e., digita-se a seguinte fórmula na célula D3 “=D3*F4” o que dá o

resultado

2. Em seguida utiliza-se a facilidade de preenchimento automático (Autofill) para

copiar a fórmula para o bloco de células D4:D11 para se saber o ordenado de
todos os empregados. Mas o resultado não satisfaz porque ao copiar a
fórmula as referências relativas vão sendo ajustadas às novas posições o que

Departamento de Informática - C. Caldeira - 19 -

Universidade de Évora

conduz a operação do tipo: nº dias de trabalho * nada, o que dá um resultado

nulo.

Figura 1-4: Utilização errada de referências.

3. Para os resultados serem válidos tem-se que se forçar a multiplicação apenas

pela célula F4, o que se consegue utilizando uma referência absoluta.

Depois já é possível copiar essa fórmula, como se vê na Figura 1-5, para as

restantes células da coluna sem que se perca a informação relativa à
remuneração diária:

Departamento de Informática - C. Caldeira - 20 -

Universidade de Évora

Figura 1-5: Utilização das referências absolutas.

1.15 Atribuição de nomes a células e blocos

O Excel tem uma facilidade que permite a atribuição de nomes a células e a

blocos. Permite que, por exemplo, uma células seja identificada por uma
determinada denominação em substituição da sua referência. As vantagens de
atribuir nomes a células ou blocos são as seguintes:

• As fórmulas tornam-se mais compreensíveis e, portanto, mais

facilmente utilizáveis;

• Reduz as possibilidades de erro na construção de fórmulas;

• Dar nomes às constantes torna o seu propósito mais evidente;

A atribuição de denominações deve obedecer às seguintes regras:

• O primeiro caracter tem que ser uma letra ou um traço inferior (_);

• Não são permitidos espaços em branco;

• O tamanho máximo de um nome é de 255 caracteres;

• Podem ser utilizadas letras minúsculas ou maiúsculas. O Excel grava o

nome como o digitamos mas ao lê-lo não distingue entre elas.

O processo de atribuição de nomes é o seguinte:

Departamento de Informática - C. Caldeira - 21 -

Universidade de Évora

1. Seleccionar o bloco (ver a Figura 1-6) a que se quer dar nome e depois clicar
na área de referência.

Figura 1-6: Lista com preços de peças de roupa.

2. Escrever na janela o nome desejado e em seguida premir <ENTER>: Depois

selecciona-se, ou apaga-se, a referência ao bloco.

Figura 1-7: Como criar um Nome numa folha de cálculo.

3. Para inserir na fórmula o nome do bloco dão-se os passos mostrados na

Figura 1-8 após o que na janela Paste Name escolhe-se o nome
correspondente ao bloco.

Departamento de Informática - C. Caldeira - 22 -

Universidade de Évora

Figura 1-8: Inserir um Nome numa fórmula.

Figura 1-9: Uma função com um Nome a substituir as referências a um bloco.

1.16 Operadores

No Excel temos um conjunto de operadores formados pelos tipos seguintes:

• aritméticos

• comparação

• texto

Departamento de Informática - C. Caldeira - 23 -

Universidade de Évora

• referência

1.16.1 Aritméticos

Esta classe de operadores é formada por:

• adição (+)

• subtracção (-)

• multiplicação (*)

• divisão (/)

• exponenciação (^)

1.16.2 Comparação

Os operadores de comparação são os abaixo referidos:

• igual (=)

• menor que (<)

• maior que (>)

• menor ou igual (<=)

• maior ou igual (>=)

• diferente (< >)

1.16.3 Texto

O operador de texto & junta uma, duas ou mais cadeias alfanuméricas numa
única.

Departamento de Informática - C. Caldeira - 24 -

Universidade de Évora

1.16.4 Referência

São estes os operadores que permitem que se façam associações entre as

células constituintes de uma folha de cálculo:

• Conjunto/bloco de células/range (:) - A1:C10

• união (;) - B2,C3

• intersecção (“espaço em branco”): B1:C3 C2:C4

1.16.5 Precedência dos operadores

A utilização de parênteses nas fórmulas permite controlar a ordem pela qual as

operações são realizadas. Considere, por exemplo, a seguinte fórmula que utiliza
referências a nomes de células:

=Vendas-Despesas*Taxa_de_IRS

O objectivo desta fórmula é a de subtrair as despesas às vendas e multiplicar

esse resultado pela taxa de imposto. No entanto, se aquela fórmula fosse
utilizada o resultado não seria o esperado, dado que a folha de cálculo
multiplicará primeiro a taxa de imposto pelas despesas e em seguida subtrairá o
resultado às vendas, pelo que a forma correcta de escrever a fórmula será:

=(Vendas-Despesas)*Taxa_de_IRS

Para compreender esta forma de funcionamento é necessário ter em

consideração um conjunto de regras denominadas procedência dos
operadores utilizadas pela folha de cálculo para efectuar cálculos. No Quadro
1-1 pode-se ver a ordem de precedência das operações, a qual pode ser
modificada com a utilização de parênteses.

Departamento de Informática - C. Caldeira - 25 -

Universidade de Évora

Quadro 1-1: Tabela de precedência dos operadores em fórmulas.

Símbolo Operador
- Negação, como em -1
% Percentagem
^ Exponenciação
*e/ Multiplicação e divisão
+e- Adição e subtracção
& Concatenação de texto
=, <, >, <=, >=, e
<> Comparação

Nas formulas também se pode recorrer à utilização de parênteses aninhados,

i.e., parênteses dentro de parênteses, e neste caso as expressões aritméticas a
terem prioridade são as que se localizam nos parênteses mais interiores.

1.17 Filtro Automático

Filtrar é uma forma fácil para encontrar dados numa lista. O procedimento geral
para utilização do Filtro Automático é o seguinte(ver a Figura 1-10):

1. Tornando activa uma célula no interior da lista escolher o menu Data e

depois as opções Filter e AutoFilter:

Departamento de Informática - C. Caldeira - 26 -

Universidade de Évora

Figura 1-10: Utilização do filtro automático.

2. Depois do Filtro Automático estar ligado o cabeçalho da lista passa a ter este
aspecto:

Figura 1-11: Resultado da utilização do filtro automático.

3. Em cada uma das colunas podem ser escolhidas estas opções: todos os
elementos da lista (All), só os dez primeiros (Top 10…), um membro
qualquer da lista ou escolhe-se a pesquisa parametrizável (Custom…).

Departamento de Informática - C. Caldeira - 27 -

Universidade de Évora

Figura 1-12: Exemplo da utilização de uma condição pré-definida no filtro

automático.

4. Na janela da pesquisa podem-se configurar as pesquisas

Figura 1-13: Exemplo da utilização de uma condição configurável no filtro

automático (I).

5. A pesquisa apresentada na janela seguinte obedeceu aos seguintes critérios:

encontrar todos os empregados que contenham Ana no seu nome.

Departamento de Informática - C. Caldeira - 28 -

Universidade de Évora

Figura 1-14: Exemplo da utilização de uma condição configurável no filtro

automático (II).

1.18 Ordenação de Dados

A ordenação serve para organizar um conjunto de dados alfabética, numérica ou

cronologicamente. O procedimento geral para utilização da Ordenação de Dados
é o seguinte:

1. No menu Data escolhe-se a opção Sort.

Departamento de Informática - C. Caldeira - 29 -

Universidade de Évora

2. Após isso abre-se uma janela que permite escolher a coluna a ordenar
ascendente ou descendentemente.

Figura 1-15: Ordenação de dados numa lista.

3. O resultado final será, neste exemplo, a lista ordenada alfabeticamente pelo

campo Nome.

Figura 1-16: Resultado da ordenação de dados.

Departamento de Informática - C. Caldeira - 30 -

Universidade de Évora

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:

Departamento de Informática - C. Caldeira - 31 -

Universidade de Évora

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:

Departamento de Informática - C. Caldeira - 32 -

Universidade de Évora

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:

Departamento de Informática - C. Caldeira - 33 -

Universidade de Évora

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

Departamento de Informática - C. Caldeira - 34 -

Universidade de Évora

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 ([Link])

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:

Departamento de Informática - C. Caldeira - 35 -

Universidade de Évora

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:

Departamento de Informática - C. Caldeira - 36 -

Universidade de Évora

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

[Link] 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

Departamento de Informática - C. Caldeira - 37 -

Universidade de Évora

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:

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
Figura 1-18: Utilização do Assistente para o caso da
categoria de
função VLOOKUP.
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;

Departamento de Informática - C. Caldeira - 38 -

Universidade de Évora

6. Clicar no botão de
contracção da
linha Table_array;

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
Figura 1-19: Escola dos parâmetros para a função
linha do
VLOOKUP.
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.

[Link] 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);

Departamento de Informática - C. Caldeira - 39 -

Universidade de Évora

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

[Link] 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

[Link] 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;...)

Departamento de Informática - C. Caldeira - 40 -

Universidade de Évora

Exemplos:

• AND(1+1=2;2+2=4) iguala VERDADE

• AND(1+1=1;2+2=4) devolve FALSO

[Link] 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

[Link] 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:

Departamento de Informática - C. Caldeira - 41 -

Universidade de Évora

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 [Link], página 37, 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.

Departamento de Informática - C. Caldeira - 42 -

Universidade de Évora

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 <Enter>

A fórmula em J2 fica com esta forma:

=IF(H2>=18;"Aprov.
Distinção";IF(H2>=15;"Aprovado";IF(H2>=10;"Aprovado";"Reprovado")))

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.

Departamento de Informática - C. Caldeira - 43 -

Universidade de Évora

1.19.11 Funções Estatísticas

[Link] COUNTIF ([Link])

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

Departamento de Informática - C. Caldeira - 44 -

Universidade de Évora

[Link] 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:

Departamento de Informática - C. Caldeira - 45 -

Universidade de Évora

12

Aduba çã o (t/ha )
10
8
6
4
2
0
0 200 400 600 800 1000
P roduçã o (t/ha )

Figura 1-23: Relação entre a produção e a adubação.

[Link] 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.

Erro! Ligação inválida.

=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

Departamento de Informática - C. Caldeira - 46 -

Universidade de Évora

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.

[Link] 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;

Departamento de Informática - C. Caldeira - 47 -

Universidade de Évora

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:

Departamento de Informática - C. Caldeira - 48 -

Universidade de Évora

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.

Departamento de Informática - C. Caldeira - 49 -

Universidade de Évora

[Link] 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.

[Link] 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.

[Link].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:

Departamento de Informática - C. Caldeira - 50 -

Universidade de Évora

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!

Departamento de Informática - C. Caldeira - 51 -

Universidade de Évora

(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:

Departamento de Informática - C. Caldeira - 52 -