Tarefa Orientada 17
Listas de dados
Estudo prático da folha de cálculo Excel - Gestão de listas como base de dados.
Conceitos teóricos
Lista – Série de linhas da folha de cálculo que contém dados relacionados,
como por exemplo, uma base de dados (BD).
O Excel permite a criação de pequenas bases de dados que consistem
essencialmente numa zona de trabalho (lista de dados) com o seguinte
formato:
− Colunas ou campos –
células
que
contêm
sempre o mesmo tipo de
dados
− Linhas ou registos – uma
ocorrência de todos os
campos.
Código
P1
P2
P3
Designação
papel
caneta
lápis
Preço
100
50
30
As operações sobre uma BD em Excel são as seguintes:
−
−
−
−
Inserção de registos;
Alteração de registos;
Eliminação de registos;
Consulta/pesquisa (rudimentar) de dados – Filtragem com, ou
sem, critérios de pesquisa;
− Análises estatísticas.
Para a criação de uma BD em Excel é preciso ter em atenção o seguinte:
− Inserir o nome dos campos na 1ª linha e os registos nas
restantes;
− Deixar pelo menos uma linha e uma coluna em branco entre a
lista que constituirá a base de dados e outra qualquer
informação;
− A lista é seleccionada automaticamente, desde que o cursor
esteja posicionado em qualquer célula da BD.
Microsoft Excel
1
1. Criação/Edição de uma lista
Para gerir um stock de produtos utiliza-se uma folha de cálculo, denominada
Stock, com a seguinte informação:
Código
P1
P2
P3
P4
P5
Designação
Arame
Rede
Alicate
Canivete
Parafuso
Preço
100
150
130
200
140
Armazém
A1
A2
A3
A1
A3
Existências
Q_alerta
1000
1500
3210
2000
4240
3000
2341
1000
900
1500
A “Existências” indica a quantidade em stock e o “Q_alerta” indica as
quantidades mínimas que devem existir para não haver rotura de stock.
a) Introduza os dados da tabela começando na célula “A1” (ou utilize o
ficheiro fornecido).
b) Seleccione uma célula que contenha dados e no menu “Dados” escolha a
opção “Formulário…”. Aparece a seguinte janela de formulário:
c) Navegue nos registos e altere o preço do produto “P2” para 300.
d) Introduza os dois novos registos apresentados na tabela seguinte:
P6
P7
Prego
Argola
170 A2
110 A1
1856
1749
2000
1500
e) Digite o texto “Fim de base de dados” na célula “A10”.
f) Utilizando novamente o formulário proporcionado pelo Excel tente
introduzir 2 novos registos. Analise o sucedido e feche o formulário.
g) Utilize novamente o formulário para introduzir um novo registo e analise
o sucedido.
h) Elimine todos os registos posteriores a “P7”.
Microsoft Excel
2
2. Consulta/Pesquisa de dados numa lista – Formulário de dados
a) Abra novamente o formulário de dados e carregue no botão “Critérios”.
b) Escreva “a” na “Designação” e carregue no botão “Localizar seguinte”.
Navegue no formulário. Quais os produtos que consegue observar? No fim
da análise limpe o critério (procedimento igual para as restantes alíneas).
c) Visualize apenas os produtos que comecem pela letra “a” e tenham stock
no armazém “A1”.
d) Pode utilizar os operadores “<” e “>” para procurar por letras ou números
que sejam menores ou maiores que o especificado. Por exemplo, com
“<c” na “Designação” e “>1500” nas “Existências” o que visualiza?
e) Pode ainda utilizar os caracteres especiais “?” (para designar um qualquer
carácter) e “*” (para designar uma série de quaisquer caracteres). Por
exemplo, “?r” na “Designação” para encontrar produtos cuja segunda
letra seja “r” e “a*go” para encontrar produtos que comecem por “a” e
contenha “go” em qualquer lugar da designação.
NOTA: Os critérios que podem utilizar-se no formulário são muito limitados.
Por exemplo, não permitem visualizar os produtos que pertencem aos
armazéns 1 ou 2.
Microsoft Excel
3
3. Consulta/Pesquisa de dados numa lista – Filtro Automático
a) No menu “Dados” escolha a opção “Filtro” e seleccione “Filtro
Automático”. Pode-se aceder às opções de filtragem através dos botões
de filtragem
que forma colocados junto aos nomes dos campos:
b) Clique no botão de filtragem do “Armazém” e seleccione “A1”. O Excel
não apagou os outros registos, apenas os escondeu. Para voltar a ver
todos os registo escolha a opção “(Tudo)” do botão de filtragem.
c) Seleccione “A1” no “Armazém” e depois 1500 no “Q_alerta”, o que
visualiza? Agora escolha “(Tudo)” no “Armazém”, são mostrados todos os
registos? O que conclui? Retire todos os filtros.
d) Consegue visualizar apenas os produtos dos armazéns 1 ou 2? Não? Escolha
a opção “(Personalizar…)” e preencha com os valores da figura seguinte:
e) Sem retirar o filtro anterior, visualize apenas os produtos cujo preço é
diferente de 100. Observe o resultado e retire todos os filtros.
f) Aplique os filtros necessários para visualizar apenas:
ƒ
Os produtos cujo stock existe no armazém A2 ou A3 e com preço
maior do que 190;
ƒ
Os produtos com preço pertencente ao intervalo [110 .. 190];
ƒ
Os produtos com preço pertencente ao intervalo ]110 .. 190].
NOTA: Os filtros automáticos são poderosos mas ainda não respondem a todo
o tipo de filtragem. Por exemplo, não permitem visualizar os produtos cujo
stock existe no armazém A2 ou A3 ou com preço maior do que 190.
Microsoft Excel
4
4. Consulta/Pesquisa de dados numa lista – Filtro Avançado
O Filtro Avançado permite a utilização de células normais da folha de
cálculo para definir critérios de filtragem. Para uma melhor compreensão
dos critérios, convém fazer uma tabela com o nome de todos os campos e
com várias linhas para os critérios.
a) Escreva “a1” na célula “D18” (critério). No menu “Dados” escolha a
opção “Filtro” e seleccione “Filtro Avançado”.
b) Coloque no intervalo de critérios “D17:D18” e clique em “Ok”. Para
retirar a filtragem, no menu “Dados” escolha a opção “Filtro” e
seleccione “Mostrar tudo”.
c) Escreva “>=4000” na célula “E18”. Repita a filtragem com o intervalo
“D17:E18”. Foi realizada uma conjunção (“E” lógico) ou uma disjunção
(“OU” lógico)?
d) Mova o conteúdo da célula “E18” para “E19” e repita a filtragem com o
intervalo “D17:E19”. Foi realizada uma conjunção (“E” lógico) ou uma
disjunção (“OU” lógico)?
e) Escolha conjunção ou disjunção para os seguintes casos:
ƒ
Se os critérios estiverem na mesma linha ocorre uma …
ƒ
Se os critérios estiverem em linhas diferentes ocorre uma …
f) Para visualizar os produtos cujo stock existe no armazém A1 ou A2 ou com
as existências maior ou igual a 4000, escreva “a2” em “D20”. Filtre com
base no intervalo “D17:E20”.
g) Pode também usar fórmulas para comparar valores entre campos. Por
exemplo, para visualizar os produtos que necessitam de reabastecimento,
escreva “Reabastecer” em “G17” e a fórmula “=Existências–Q_alerta<0”
Microsoft Excel
5
em “G18”. Apesar do Excel mostrar um erro (“#NOME?”), ignore e filtre
com base no intervalo “G17:G18”.
h) Pode ainda utilizar referências absolutas a células que contêm fórmulas.
Por exemplo, para visualizar apenas os produtos cujo preço seja maior
que a média, escreva a fórmula “=MÉDIA(C2:C8)” em “A24”,
“Preço>Média” em “H17” e a fórmula “=Preço > $A$24” em “H18”. Filtre
com base no intervalo “H17:H18”.
i) Aplique os filtros necessários para visualizar apenas:
ƒ
Os produtos cujo stock existe no armazém A2 ou A3 ou com preço
maior do que 190;
ƒ
Os produtos com preço pertencente ao intervalo [110 .. 190] ou
igual a 200;
ƒ
os produtos cujo stock é mantido no armazém A1 ou A2 e com
preço diferente de 130.
5. Consulta/Pesquisa de dados numa lista – Filtro Avançado e Extracção
a) Escreva “a1” na célula “D18”. No menu “Dados” escolha a opção “Filtro”
e seleccione “Filtro Avançado”.
b) Preencha o intervalo de critérios, seleccione o botão “Copiar para outro
local” e preencha a caixa “Copiar para”. Os registos que obedecem aos
critérios são copiados para o novo local.
c) Aplique os filtros necessários para extrair apenas:
ƒ
a informação disponível relativa aos produtos com preço
pertencente ao intervalo [130 .. 140] ou igual a 200;
ƒ
a informação disponível dos produtos que necessitam de ser
reabastecidos.
Microsoft Excel
6
6. Consulta/Pesquisa de dados numa lista – Ordenação
a) Seleccione uma célula que contenha dados e no menu “Dados” escolha a
opção “Ordenar…”. Escolha ordenar por armazém tal como a figura:
b) Ordene os registos da lista em função do armazém e do preço dos
produtos, supondo que pretende colocar em destaque os produtos mais
valiosos.
7. Análise estatísticas – Sub Totais
a) Certifique-se que a folha actual tem o nome “Stock”, adicione uma nova
folha, de nome “Vendas”, com a seguinte informação de vendas:
NºVenda
2375
2375
2376
2376
2376
2377
2377
2378
2379
Data
04-01-2001
04-01-2001
04-01-2001
04-01-2001
04-01-2001
05-01-2001
05-01-2001
05-01-2001
05-01-2001
Produto Quantidade Valor
P1
200
P2
100
P1
150
P3
225
P5
50
P2
200
P4
50
P1
100
P2
150
b) Para obter o valor a pagar pelo primeiro item insira a fórmula
“=ÍNDICE(Stock!$A$2:$F$8; CORRESP(C2; Stock!$A$2:$A$8; 1); 3) * D2” na
célula “E2” (Preço x Quantidade). Copie para os restantes itens.
c) Qual o valor a pagar por cada venda? O quadro mostra o valor a pagar por
item, mas não mostra o total de cada venda. Solução: sub totais.
d) No menu “Dados” escolha a opção “Subtotais…”. Preencha os valores tal
como na figura d).
Microsoft Excel
7
d)
e) Pode utilizar os botões
e)
/
para esconder/expandir os pormenores.
f) Remova os sub totais e ordene os valores por produto. Qual o resultado do
sub total ilustrado na figure f)?
f)
g)
g) Ordene os dados por data e analise o resultado do sub total g).
h) Para criar o sub total das unidades vendidas por produto, mas somente
para vendas de quantidade iguais ou superiores a 100 aplique o seguinte
filtro automático e depois o sub total (ordenação prévia por?):
Microsoft Excel
8