UC –
Introdução a Informática
Docente: André Luiz Silva de Moraes
1º sem – Análise e Desenvolvimento de Sistemas
Roteiro 10: (Planilhas
Eletrônicas) - Função procv / manipulação de
formulários
Objetivos
•
•
•
Explorar funções de procura e referência;
Explorar ferramentas para controle de formulários em planilhas;
Importar e exportar dados em planilhas;
Ferramentas Necessárias
Microsoft Excel, libreoffice Calc
Introdução
Neste roteiro serão exploradas as funcionalidades de funções para procura e referência, que são importantes para filtrar
resultados em pesquisas onde existam informações que possam ser consultadas como tabelas de cadastros ou dados que
forem importados de listas pré-existentes.
Função procv
Função
PROCV(critério de
pesquisa;matriz;índice;ordem de
classificação)
Descrição
Exemplo
Retorna os dados referentes ao critério de
pesquisa em um conjunto de células.
PROCV("senac";D16:F19;2;0)
Esta função é útil para desenvolver planilhas de controle de estoque onde é necessário pesquisar itens através de um código,
por exemplo, ou através de alguma referência que identifique os itens de forma exclusiva.
Função PROCV – Exemplo:
Seja a seguinte planilha, a região de pesquisa
Ainda na mesma planilha, a região de dados de estoque:
Para funcionar a pesquisa, inseriremos as seguintes fórmulas nas células a seguir:
A, ao inserir qualquer valor que conste nos códigos do estoque teremos a pesquisa sendo realizada:
P á g | 50
Faculdade de Tecnologia SENAC PELOTAS - Credenciado pela Portaria nº. 3.071, de 01 de outubro de 2004.
A função procv consiste em uma busca na vertical. Ela permite que seja possível pesquisar dados em tabelas dentro da
mesma planilha, em planilhas diferentes ou em arquivos diferentes.
UC –
Introdução a Informática
Docente: André Luiz Silva de Moraes
1º sem – Análise e Desenvolvimento de Sistemas
Para qualquer valor que for inserido como referência que não existir na tabela de estoque será exibido um valor de erro:
Este valor refere-se ao fato de estarmos lidando com um valor não disponível. Por isto é exibida tal mensagem. Existem outras
tantas mensagens de erro que podem ser inclusive utilizadas pelo programador da planilha ao elaborar alguns cálculos.
Criar formulários é uma maneira bastante interessante de controlar e padronizar as informações que podem ser inseridas em
planilhas pelo seus usuários. A entrada de dados pode ser feita através de campos de formulário. Estes campos permitem ao
usuário optar por um item específico dentro de um universo de informações com valores pré-definidos, ou selecionar diversos
itens de uma lista.
Para poder utilizar recursos de formulário no calc, é necessário primeiro ativar a barra de ferramentas Formulário, localizada no
menu Exibir / Barra de ferrramentas / Controles de formulários. No Microsoft Excel 2010 é necessário ativar a guia
desenvolvedor, acessando as opções do excel / Personalizar faixa de opções / guias principais / desenvolvedor e no
Excel 2007 deve-se acessar opções do excel / mais usados / mostrar guia desenvolvedor na faixa de opções. A Figura 30
ilustra a barra de formulários no calc e no Excel.
Figura 30 - barra de controle de formulários de calc e excel.
No calc, para desenhar um item de formulário, é utilizada a mesma maneira de ferramentas de desenho, bastando clicar no
objeto a ser desenhado, posicionar em qualquer região da planilha e arrastar o mouse com o botão pressionado para desenhar
o elemento.
Na primeira vez que um objeto é desenhado o calc ativa um assistente de criação de objetos, para auxiliar na construção do
formulário, mas faremos a desativação do mesmo clicando no último botão mostrado na figura anterior. Após, os elementos
são criados sem qualquer pergunta, passando a tarefa de configurar o controle criado de forma manual, demonstrada a seguir:
P á g | 51
Faculdade de Tecnologia SENAC PELOTAS - Credenciado pela Portaria nº. 3.071, de 01 de outubro de 2004.
Elaborando Formulários
UC –
Introdução a Informática
Docente: André Luiz Silva de Moraes
1º sem – Análise e Desenvolvimento de Sistemas
Ferramenta
Significado
Detalhe de implementação
Ativa os modos de “edição” ou “utilização”, permitindo
visualizar a funcionalidade dos controles utilizados.
Para calc e para excel o funcionamento é
semelhante. O detalhe é que nas últimas versões
do Excel foi criado um botão de design maior e
mais visível, mas o funcionamento segue o
mesmo.
Cria um botão de opção, onde é possível optar por
somente um.
No calc vincula-se uma célula par cada controle
retornando VERDADEIRO ou FALSO para
marcado ou não marcado. No excel retorna o
número do índice do controle criado vinculados
todos em uma única célula.
Cria uma caixa de listagem, com vários itens, onde é
possível selecionar apenas um deles.
Para calc e para excel o funcionamento é
semelhante.
Cria uma caixa de combinação, onde o item selecionado
aparece no topo da caixa;
Para calc e para excel o funcionamento é
semelhante.
Cria um rótulo na planilha, como por exemplo, um título;
Para calc e para excel o funcionamento é
semelhante.
Cria uma caixa de seleção, onde é possível optar por
vários itens;
No calc vincula-se uma célula par cada controle
retornando VERDADEIRO ou FALSO para
marcado ou não marcado. No excel retorna o
número do índice do controle criado vinculados
todos em uma única célula.
Cria uma caixa de texto, onde é possível solicitar ao
usuário que insira um texto qualquer;
Para calc e para excel o funcionamento é
semelhante.
Cria um botão de comando, utilizado quando é
necessário ativar alguma rotina ou programa por parte do
usuário;
Para calc e para excel o funcionamento é
semelhante.
Ativa a barra “mais controles”, possibilitando o uso de
mais ferramentas para formulários;
Para calc e para excel o funcionamento é
semelhante.
Tabela 5 - componentes da barra de formulário
Vinculando controles
Vincular controles significa que faremos com que o objeto criado comunique-se com a planilha, ou seja, para que sejam
listados itens em uma caixa de listagem, por exemplo, estes dados devem ser obtidos de algum local. E quando algum dado é
selecionado, também deve-se decidir o que fazer com a informação inserida, normalmente é definido que o valor seja inserido
em alguma célula existente na planilha. Faremos alguns exemplos a seguir que demonstram estas funcionalidades, o exemplo
escolhido para esta criação foi a caixa de combinação:
P á g | 52
Faculdade de Tecnologia SENAC PELOTAS - Credenciado pela Portaria nº. 3.071, de 01 de outubro de 2004.
Na criação de formulários, o universo de itens que podem ser criados é bastante grande. O mais comum é utilizar objetos que
permitam interagir com as informações de uma planilha. A tabela x ilustra alguns dos itens existentes:
UC –
Introdução a Informática
Docente: André Luiz Silva de Moraes
1º sem – Análise e Desenvolvimento de Sistemas
Passo 1: crie/prepare os dados de origem a serem listados
Toda a informação listada em qualquer caixa deve ser obtida de algum lugar, neste caso digitamos nas células de A1:A5 os
elementos que serão listados ao usuário.
Passo 3: Acesse as propriedades do objeto criado para realizar a sua configuração
Todo o objeto criado possui configurações. Para as tarefas que necessitamos deve-se procurar duas opções: como obter a
listagem de itens e como vincular o objeto a alguma célula da planilha. Clique com o botão da direita no objeto que foi criado e
vá na opção CONTROLE.
Passo 4: Alterne para Desativar o modo Design
O modo design é a maneira que temos de alterar as propriedades dos controles no calc, para poder utilizar as funcionalidades
ou testar o que foi configurado é necessário alternar neste modo.
P á g | 53
Faculdade de Tecnologia SENAC PELOTAS - Credenciado pela Portaria nº. 3.071, de 01 de outubro de 2004.
Passo 2: crie o objeto de caixa de combinação
Exiba a barra de formulários caso não esteja listada, e insira uma caixa de combinação em qualquer região da planilha.
UC –
Introdução a Informática
Docente: André Luiz Silva de Moraes
1º sem – Análise e Desenvolvimento de Sistemas
Feito isto, as configurações do controle estão prontas para serem utilizadas, bastando agora realizar o teste necessário para
verificar as configurações.
Note que a configuração CÉLULA VINCULADA será a célula onde será armazenado o valor selecionado pelo usuário, e como
este valor fica armazenado em célula pode ser utilizado posteriormente por qualquer função que seja elaborada
posteriormente, como um SE ou PROCV fazendo com que estes dados possam interagir com o que for selecionado pelo
usuário.
1)
Em plan1, elaborar a planilha de controle de estoque
a)
b)
c)
d)
Utilizando a função PROCV, faça com que seja possível inserir um código de um produto na coluna CÓDIGO e os
dados de QUANTIDADE, UNIDADE, DESCRIÇÃO, CUSTO sejam localizados automaticamente.
a. A célula de busca (referência) será a célula c2, para a primeira linha.
b. A área de busca dos dados será de A19:E26.
c. A coluna retornada irá variar de acordo com a informação escolhida.
Insira a fórmula para cálculo do subtotal, do desconto.
O frete deverá ser inserido manualmente.
Insira a fórmula para cálculo do total geral.
2)
Em plan2, elaborar uma planilha para o controle de notas de alunos.
P á g | 54
Faculdade de Tecnologia SENAC PELOTAS - Credenciado pela Portaria nº. 3.071, de 01 de outubro de 2004.
Tarefas (ATENÇÃO: executar o roteiro no calc e no excel, fazendo a entrega dos dois arquivos-fonte)
UC –
Introdução a Informática
Docente: André Luiz Silva de Moraes
1º sem – Análise e Desenvolvimento de Sistemas
e)
Insira os dados de notas de alunos
Calcule as médias por aluno na coluna G.
Ative a barra de formulários e insira uma caixa de combinação, posicione-a sobre a célula B9;
Na caixa de combinação inserida:
a. Defina como intervalo de origem os dados de B2:B7;
Calcule todas as funções de pesquisa e referência na seção de pesquisa de alunos utilizando a função PROCV
utilizando como referência a célula A9;
3)
Em plan3, elaborar uma planilha contendo um formulário para compra de equipamentos.
a)
b)
c)
Insira os elementos de formulário necessários;
Configure os elementos de formulário para as células respectivas
Para o somatório de valores, deve-se utilizar a função SE verificando os valores booleanos resultantes das células de
vínculo;
Para o somatório de opcionais, combine a função SOMA() com a função SE() verificando quando cada opcional foi
comprado no formato SOMA(SE()), utilize um SE para cada opcional.
Calcule o total geral, somando os valores das células anteriores.
d)
e)
Referências
Material disponível no Mussum.
“Livro - Aprenda em 24 horas Microsoft Office XP PERRY”, Greg, Rio de Janeiro Campus 2001;
P á g | 55
Faculdade de Tecnologia SENAC PELOTAS - Credenciado pela Portaria nº. 3.071, de 01 de outubro de 2004.
a)
b)
c)
d)