Folha de Cálculo 2
Pedro Barahona
DI/FCT/UNL
Introdução aos Computadores e à Programação
2º Semestre 2006/2007
25 de Maio de 2007
Folha de Cálculo
1
Folha de Cálculo
• As possibilidades de acesso e tratamento avançado de
informação numa folha de cálculo serão ilustradas através de
alguns exemplos.
– Ordenação de Registos
– Agregações (médias, médias ponderadas, extremos)
– Contagens e contagens condicionais
– Determinação da ordem
– Descoberta de um valor numa tabela
– Selecção de valor numa tabela através da sua linha e coluna
25 de Maio de 2007
Folha de Cálculo
2
Ordenação de Registos
• Começamos por considerar a apresentação ordenada de um
conjunto de registos, dispostos em várias linhas.
• No EXCEL tal pode ser facilmente obtido através da
operação “sort” na entrada “data” do menu.
• Nesta ordenação podem ser seleccionados
– os “campos” por onde se faz a ordenação,
– A sua prioriades (um campo só é utilizado em caso de
empate nos campos mais prioritários),
– a ordem (crescente ou decrescente) utilizada, e
25 de Maio de 2007
Folha de Cálculo
3
Ordenação de Registos
• Dada a pauta da turma abaixo
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
ela pode, por exemplo, ser ordenada
– Por ordem crescente dos números ou nomes dos alunos;
– Por ordem decrescente das suas notas (1, 2 ou 3)
25 de Maio de 2007
Folha de Cálculo
4
Ordenação de Registos
• Por ordem crescente dos números dos alunos
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
• Seleccionar a tabela
• Menu: Data - Sort
• Opções: num, ascending
25 de Maio de 2007
num
18849
18937
19686
19874
20179
20447
20685
20744
20765
Folha de Cálculo
nome
marta
ricardo
joão
vera
mário
madalena
maria
luís
pedro
nota 1
9.2
17.3
10.7
11.1
8.3
19.8
16.1
9.9
10.7
nota 2
7.3
17.6
18.2
10.2
15.7
13.6
18.8
8.6
15.4
nota 3
18.3
15.9
12.9
8.9
15.6
19.7
9.3
12.2
16.8
5
Ordenação de Registos
• Por ordem decrescente dos nomes dos alunos (notar o
correcto tratamento dos acentos)
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
• Seleccionar a tabela
• Menu: Data - Sort
• Opções: nome, descending
25 de Maio de 2007
num
19874
18937
20765
18849
20179
20685
20447
20744
19686
Folha de Cálculo
nome
vera
ricardo
pedro
marta
mário
maria
madalena
luís
joão
nota 1
11.1
17.3
10.7
9.2
8.3
16.1
19.8
9.9
10.7
nota 2
10.2
17.6
15.4
7.3
15.7
18.8
13.6
8.6
18.2
nota 3
8.9
15.9
16.8
18.3
15.6
9.3
19.7
12.2
12.9
6
Ordenação de Registos
• Por ordem decrescente da nota 3
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
• Seleccionar a tabela
• Menu: Data - Sort (menu)
• Opções: nota 3, descending
25 de Maio de 2007
num
20447
18849
20765
18937
20179
19686
20744
20685
19874
Folha de Cálculo
nome
madalena
marta
pedro
ricardo
mário
joão
luís
maria
vera
nota 1
19.8
9.2
10.7
17.3
8.3
10.7
9.9
16.1
11.1
nota 2
13.6
7.3
15.4
17.6
15.7
18.2
8.6
18.8
10.2
nota 3
19.7
18.3
16.8
15.9
15.6
12.9
12.2
9.3
8.9
7
Agregações (Médias, Somas)
• A determinação de agregações de dados é feita através da
utilização das operações apropriadas em células adicionais.
• Por exemplo para determinar a médias das notas de uma
aluno, pode utilizar-se a sua soma dividida por 3.
• Mais genericamente podem utilizar-se as funções predefinidas
sum e count, ou mais directamente a função average.
1
2
3
4
B
num
20685
18937
C
nome
maria
ricardo
D
nota 1
16.1
17.3
E
nota 2
18.8
17.6
F
nota 3
9.3
15.9
= sum(D3:D5) / count(D3:D5)
25 de Maio de 2007
Folha de Cálculo
G
média
14.73
16.93
= (D3+D4+D5)/3
= average(D3:D5)
8
Agregações (Médias, Somas)
• As várias médias (por disciplinas e por alunos) podem ser
acrescentadas à folha inicial
1
2
3
4
5
6
7
8
9
10
11
B
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
C
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
média
D
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
12.6
E
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
13.9
F
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
14.4
G
média
14.73
16.93
10.23
13.20
17.70
10.07
11.60
14.30
13.93
o que permite eventualmente a posterior apresentação
ordenada por médias dos alunos
25 de Maio de 2007
Folha de Cálculo
9
Agregações (Extremos)
• Outras funções de agregação podem ser usadas para
determinar por exemplo os máximos e mínimos
1
2
3
4
5
6
7
8
9
10
11
B
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
C
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
máximo
D
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
19.8
E
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
18.8
F
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
19.7
G
mínimo
9.30
15.90
8.60
8.30
13.60
8.90
7.30
10.70
10.70
= min(D6:F6)
= max(D3:D11)
25 de Maio de 2007
Folha de Cálculo
10
Agregações (Médias Ponderadas)
• A média ponderada de n valores vi,
cada um com peso pi, é obtida através
da fórmula
1
2
3
4
5
6
7
B
pesos
valor
C
D
E
1
3
8
15
2
2
1
10
3
1
1
5
n
mp 
p
i
i 1
 vi
n
p
i 1
i
F
média
ponderada
8.33
11.67
13.50
10.00
• Em EXCEL tal pode ser implementado pelas fórmulas
= { SUM(C5:E5*C$7:E$7)/SUM(C5:E5) }
Nota: Array expression
CTRL-SHIFT-ENTER
= sumproduct(C5:E5*C$7:E$7)/SUM(C5:E5)
25 de Maio de 2007
Folha de Cálculo
11
Agregações (Médias Ponderadas)
• Eis o exemplo anterior com médias e médias ponderadas. De
notar o endereçamento absoluto na linha dos pesos.
1
2
3
4
5
6
7
8
9
10
11
12
B
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
C
peso
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
D
5
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
E
1
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
F
2
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
G
média
14.73
16.93
10.23
13.20
17.70
10.07
11.60
14.30
13.93
H
média
ponderada
14.74
16.99
10.31
11.05
19.00
10.44
11.24
12.81
12.19
= {sum(D9:F9*D$2:F$2)/sum(D$2:F$2)}
= sumproduct(D9:F9,D$2:F$2)/sum(D$2:F$2)
25 de Maio de 2007
Folha de Cálculo
12
Arredondamentos
• Tal como em OCTAVE, o Excel considera todos os dados numéricos
como reais. No entanto eles podem ser convertidos em inteiros (operações,
floor, ceiling e round), ou simplesmente mostrados com a precisão
desejada (não alterando o seu valor)...
1
2
3
4
5
6
7
8
9
10
11
12
B
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
C
peso
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
D
5
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
E
1
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
F
2
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
Format: cells: number: decimal places: 0
25 de Maio de 2007
Folha de Cálculo
G
média
14.73
16.93
10.23
13.20
17.70
10
12
14
14
H
média
arredondada
15.00
17.00
10.00
13.00
18.00
10
12
14
14
= round(B6)
13
Contagens
• Outras operações de agregação´muito utilizadas são operações
de contagem, especialmente as condicionais. Para tal poderão
ser utilizadas as funções prédefinidas sum / sumif ou, mais
directamente, count / countif.
1
2
3
4
5
6
7
8
9
10
11
12
H
média
arredondada
15
17
10
13
18
10
12
14
14
I
valor
10
11
12
13
14
15
16
17
18
= sumif(int(H$4:H$12= J11))
25 de Maio de 2007
J
K
L
frequência
2
0
1
1
2
1
0
1
1
= countif(H$4:H$12,J4)
Folha de Cálculo
14
Contagens
• As comparações podem ser feitas não apenas para valores
iguais, mas também para valores que satisfaçam uma certa
condição.
1
2
3
4
5
6
7
8
9
10
11
12
H
média
arredondada
15
17
10
13
18
10
12
14
14
Ou com a operação
sobre vectores
25 de Maio de 2007
I
J
K
valores maiores
do que 12
6
= countif(H$4:H$12,”>12”)
= { sum(int(H$4:H$12>12)) }
Folha de Cálculo
15
Parametrização com texto
• Para parametrizar os campos de texto, podem ser usadas
operações sobre strings, nomeadamente a concatenação.
1
2
3
4
5
6
7
8
9
10
11
12
H
média
arredondada
15
17
10
13
18
10
12
14
14
I
J
maiores
do que
10
11
12
13
14
15
16
17
18
K
L
>
7
7
6
5
3
2
2
1
0
= countif(H$4:H$12,concatenate(">",J4))
ou
= countif(H$4:H$12,concatenate(K$3,J4))
25 de Maio de 2007
Folha de Cálculo
16
Parametrização com texto
• Para expressar critérios de selecção mais complexos podem
utilizar-se expressões booleanas ou a sua conversão em
expressões numéricas (em Excel,as operações booleanas não
são muito “fiáveis”).
= { sum(int(H4:H12 >= J3) * int(H4:H12 <= L3)) }
1
2
3
4
5
6
7
8
9
10
11
12
H
média
arredondada
15
17
10
13
18
10
12
14
14
= { sum(int(H4:H12 <
25 de Maio de 2007
I
J
K
L
valores dentro do intervalo
12
...
15
5
valores fora do intervalo
12
...
15
4
J9) + int(H4:H12 >
Folha de Cálculo
L9)) }
17
Classificações
• A classificação (“ranking”) de valores num vector/matriz é
igualmente uma operação frequente e que pode ser obtida por
contagens condicionais, ou directamente pela função rank.
1
2
3
4
5
6
7
8
9
10
11
12
B
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
C
peso
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
D
5
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
E
1
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
= {sum(int(G$4:G$12 >= G6))}
25 de Maio de 2007
Folha de Cálculo
F
2
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
G
H
média
classificação
14.73
16.93
10.23
13.20
17.70
10.07
11.60
14.30
13.93
3
2
8
6
1
9
7
4
5
= rank(G8,G$4:G$12 )
18
Procura de valores em tabelas
• A instrução vlookup permite que dado um valor de referência,
se seleccione um valor numa tabela, na linha começada por
esse valor, e colocado na coluna especificada. Por exemplo,
dado o número de um aluno, pode obter-se o seu nome
1
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
nota 1
16.1
17.3
9.9
8.3
19.8
11.1
9.2
10.7
10.7
nota 2
18.8
17.6
8.6
15.7
13.6
10.2
7.3
15.4
18.2
nota 3
9.3
15.9
12.2
15.6
19.7
8.9
18.3
16.8
12.9
G
H
classificação
número
18937
nome
ricardo
= vlookup(H5,B4:F12,2,false)
25 de Maio de 2007
Folha de Cálculo
19
Procura de valores em tabelas
• De notar que a tabela tem de ter o valor de referência na
coluna mais à esquerda. Por exemplo, se pretendermos obter
os nomes dos primeiros 5 alunos, temos de colocar a coluna
de classificação à esquerda da tabela
1
2
3
4
5
6
7
8
9
10
11
12
B
rank
3
2
8
6
1
9
7
4
5
C
D
H
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
média
14.7
16.9
10.2
13.2
17.7
10.1
11.6
14.3
13.9
I
J
K
melhores alunos
1 madalena
2
ricardo
3
maria
4
pedro
5
joão
= vlookup(J6,B$4:D$12,3,false)
25 de Maio de 2007
Folha de Cálculo
20
Seleccão por linha e coluna
• Um valor pode ser seleccionado de uma tabela através da
função index, se for indicada a linha e coluna pretendida. Em
alguns casos, a posição (linha ou coluna) pretendida pode ser
obtida pela função auxiliar match.
1
2
3
4
5
6
7
8
9
10
11
12
B
rank
3
2
8
6
1
9
7
4
5
C
D
H
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
média
14.7
16.9
10.2
13.2
17.7
10.1
11.6
14.3
13.9
I
J
K
L
média
10.2
posição
3
= match(K5,H4:H12,0)
5ª posição
que também pode ser obtida através da utilização da função row
= {sum(int(K5=H$4:H$12)*(row(H$4:H$12)-row(H3)))}
25 de Maio de 2007
Folha de Cálculo
21
Seleccão por linha e coluna
• A utilização conjunta das funções index e match permite
determinar não só a posição do elemento máximo de um
vector mas outros campos relacionados registados noutras
colunas.
1
2
3
4
5
6
7
8
9
10
11
12
B
rank
3
2
8
6
1
9
7
4
5
C
D
H
num
20685
18937
20744
20179
20447
19874
18849
20765
19686
nome
maria
ricardo
luís
mário
madalena
vera
marta
pedro
joão
média
14.7
16.9
10.2
13.2
17.7
10.1
11.6
14.3
13.9
I
J
K
L
aluno com melhor média
num
nome
média
20447
madalena
17.7
5ª posição
= max(H4:H12)
= index(B4:D12, match(L6,H4:H12,0), 2)
= index(B4:D12,match(L6,H4:H12,0), 3)
25 de Maio de 2007
Folha de Cálculo
22
Seleccão por linha e coluna
• A determinação da posição de um valor máximo numa tabela
deve ser feita pelos “equivalentes” da função match.
1
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
1
2
3
4
5
6
7
8
9
1
3627
193
9259
5038
9290
253
5918
1460
2249
2
2290
1470
2671
445
9590
7813
5345
9379
3412
3
5011
6761
2379
3824
4931
1034
2998
3915
3324
4
6829
3604
7652
9498
4352
6926
3788
4152
2737
G
5
5700
1211
4735
8519
4428
5172
4384
9058
8152
H
I
J
maior valor
9590
linha
5
coluna
2
= max(C4:G12)
={sum(int(K5=H$4:H$12)*(row(H$4:H$12)-row(H3)))}
={sum(int(K5=H$4:H$12)*(column(H$4:H$12)-column(H3)))}
25 de Maio de 2007
Folha de Cálculo
23