terça-feira, 20 de abril de 2010

Como criar Formulários no Excel 2007

Muitas pessoas que se interessam pelo Excel tem dúvidas quanto a criação de formulários no Excel 2007. Então nessa postagem um pouco do que é ensinado sobre formulários no curso de Excel Avançado da Qualità.

Podemos, claro, criar formulários avançados com programação VBA, mas este é bem simples usando apenas funções condicionais SE, SOMASE e outras funções básicas.

Um formulário permite a entrada de dados numa planilha, clicando nas opções ao invés de digitar tudo. É possível criar menus drop down, botões de opção, caixas de combinação e muito mais..tudo com comandos pré-definidos. Esse processo além de mais prático, pois evita a digitação, é também mais seguro, pelo fato de o usuário acrescentar o valor preestabelecido.

Vejam o exemplo do nosso formulário abaixo.

Todo o controle de compra de um computador pode ser feito em formulário e os itens são habilitados com um clique do mouse. Aqui nesse exemplo só constam alguns itens, é só pra começar a entender como estruturar um formulário.

Tá pronto? Então arregace as mangas e impressione os amigos e faça uma média com o patrão...

Vamos iniciar um formulário de uma página em branco e construí-lo passo a passo. Quem trabalhava com formulários no Excel XP ou 2003 deve estar se perguntando onde foi parar a barra de Formulários no Excel 2007. As opções de criar botões de controle de formulários estão na Guia Desenvolvedor do Excel 2007, mas por padrão essa aba não é exibida. Então vamos adicionar a Guia Desenvolvedor do Excel 2007.

Clique no botão do Office e depois clique em Opções do Excel:
Na janela de opções marque a caixa "Mostrar guia Desenvolvedor na faixa de opções" e depois clique em OK.

Veja que apareceu mais uma Guia na barra do Excel (nesse caso ela aparece agora no Word também) e ela é destinada a usuários avançados do Office que exploram recursos de Macros e códigos em VBA do Excel. Aparecem também os comandos para criar controles de formulários e ActiveX.

Criaremos um formulário como apresentado no topo da postagem usando os controles de Formulário da nova barra Desenvolvedor. Para inserção de botões de controle de formulários, é necessária a criação de Caixas de Grupo, pois esses controles de formulários só funcionam em grupos que se destinam a funções semelhantes. Então para cada grupo de botões de controle criaremos uma caixa de grupo própria.

Selecione de A1 até J22 para criar um fundo do formulário. Isso é opcional mas fica bem mais bonito.

Escolha uma cor para o fundo do formulário.

Escolhemos esse Azul... Clique em qualquer célula para remover a seleção.

Aumente a altura da linha...( posicione o cursor na linha divisória entre o 1 e o 2, quando o cursor se transformar numa seta dupla, clique, segure apertado e arraste até a altura desejada)

... selecione o intervalo de A1 até J1 e depois clique no botão Mesclar e Centralizar. Esse marcado em vermelho...

Agora você pode digitar um título (Rótulo de Colunas) para seu trabalho. Que tal "Venda de Computador"?

Vamos começar a dar forma com a Caixa de Grupo do Processador.

Clique na Guia Inserir e nos controles de formulário escolha Caixa de Grupo.

Clique no início (à esquerda) do fundo azul, segure apertado e arraste até definir o tamanho desejado para a caixa de grupo dependendo dos elementos que serão inseridos nela.

Clique sobre o nome Caixa de Grupo e renomeie a caixa para Processador

Nessa caixa de grupo, iremos inserir os botões de opções para escolha do Processador. Lembrando que esse botão de opção é de seleção única. Ou seja, usando o botão de opção só é possível marcar um item por vez. Até porque nesse caso, um computador não teria dois processadores então optamos por apenas um item assinalado nessa caixa de grupo.
Clique no Botão de Opção...
... e desenhe esse botão dentro da caixa de grupo (isso é muito importante porque faz com que a opção seja reconhecida como componente da caixa de grupo) o botão da primeira opção para escolha do Processador.

Digite um nome para a opção e posicione no inicio da lista...
Todo o processo deverá ser repetido em cada opção dessa caixa para os outros modelos de Processadores.
Você pode dimensionar a caixa de grupo clicando no contorno e depois usando as alças (bolinhas brancas) para aumentar e diminuir. Para movimentar e alinhar pelo teclado, clique com o botão direito do mouse na borda da caixa, clique depois botão esquerdo (também na borda) e use as setas de direção do teclado. Para remover a seleção clique fora da caixa.
Para dar um efeito de 3D, clique com o botão direito do mouse no contorno da caixa, escolha Formatar Controle..
e depois habilite a opção Sombreamento em 3D.

Agora vamos repetir todo o processo e criar a Caixa de Grupo das Memórias. Desenhe a caixa de grupo e depois insira novos botões de opção para que o usuário escolha a quantidade de memória desejada.

Na terceira caixa de Grupo, os Acessórios: Crie a caixa de Grupo, defina um tamanho para caber seis opções de Acessórios. Essas opções serão escolhidas com uma caixa de Seleção. Clique na Caixa de Seleção...

...clique dentro da caixa de grupo Acessórios e digite os nomes, faça os alinhamentos...

Importante: A caixa de seleção de uma opção, não deve sobresecrever outra, senão selecionará as duas opções simultaneamente na hora de utilizar o formulário.

No final deverá ficar assim:

Agora vamos à área de calculos...digite o texto conforme a figura abaixo...
As caixa em branco são apenas células com contorno preto e fundo branco. Observe que tem uma caixa de grupo em volta e dois botões de opção: À vista e À Prazo. No Local da Entrega tem uma Caixa de Combinação, essa por enquanto não precisa fazer...

Tá pronto? Então vamos à segunda parte (bem mais fácil e mais rápida) que é criar a base de dados. Abaixo está a base de dados que deve ser digitada para que o formulário, através de dígitos de controle, resgate os valores dos componentes. Digite a base e siga rigorosamente as linhas e colunas definidas na tabela.

Agora: Os cálculos!!!

Clique com o botão direito sobre o item AMD Athlon XP e escolha Formatar Controle. Na opção Valor está habilitado Não Selecionado que corresponde a atual situação botão do processador AMD Athlon XP.

Clique no botão Recolher ( o botão com setinha vermelha ) para associar a opção do botão com uma célula. Clique na célula O4 para inserção do digito de controle da opção. Clique no botão Recolher novamente para retornar à tela anterior e confirme OK.


Veja que assinalando a primeira opção dos processadores, na célula do digito de controle aparece o número 1.

Se for marcada a segunda opção da lista (Intel Core2Duo) aparecerá o número 2 nessa mesma célula indicando que agora a segunda opção da Caixa de Grupo foi escolhida pelo usuário.

O que são e para que servem os Dígitos de Controle:
Na caixa Processador, o primeiro item da lista é AMD Athlon XP. Se essa opção for escolhida com um clique do mouse, a célula do dígito de controle deve registrar o número 1, se for escolhido Intel Core2Duo, que é a segunda opção da caixa, a célula do dígito de controle deve registrar o número 2 e assim sucessivamente, não importando quantos itens existem dentro da caixa grupo.
Apenas as opções devem ser criadas na ordem em que estarão na caixa de grupo, pois esse dígito de controle resulta na ordem crescente de criação da opção.

Para a Caixa de Grupo Memórias, repita os procedimentos anteriores: Clique com o botão direito sobre o item 2 Gbe escolha Formatar Controle.

Na opção Valor está habilitado Não Selecionado que corresponde a atual situação botão da memória de 2Gb DDR2

Clique no botão Recolher (o botão com setinha vermelha ) para associar o opção do botão com uma célula. Clique na célula O8 para inserção do digito de controle da opção. Clique no botão Recolher novamente para retornar à tela anterior e confirme OK.
Se for marcada a segunda opção da lista (4Gb) aparecerá o número 2 nessa mesma célula indicando que agora a segunda opção da Caixa de Grupo foi escolhida pelo usuário.

Na caixa Acessórios, são Caixas de Seleção que oferecem as opções de escolha. Nesse caso, é necessário clicar botão direito e escolher cada célula de controle individualmente.



Caixas de Seleção não resultam em valores numéricos como 1, 2 ou 3, mas uma resposta lógica Verdadeiro para assinalado e Falso para não assinalado.

Ex: Ao clicar com o botão direito em Web Cam, escolher a célula ao lado de Web Cam na base de dados, como exemplo da figura acima, clicar botão direito em Pen-drive e escolher a célula ao lado de Pen-Drive na base para o controle e assim por diante.
No final teremos o valor lógico VERDADEIRO para os itens assinalados na lista e FALSO para os itens não assinalados.

Agora as fórmulas:

Clique na célula em branco ao lado da palavra processador e digite:


=SE(O4=1;N4;SE(O4=2;N5;N6))

Que diz o seguinte:
Se a célula O4 for igual a 1, então insira aqui o valor contido em N4, SE a célula O4 for igual a 2, então insira aqui o valor contido em N5, SENÃO, insira o valor de N6.

Aham!! Esse é o segredo! Se marcarmos a primeira opção do formulário, então a célula de controle vai registrar 1 e se for 1 o valor é de O4 ou seja R$190,00....Se for 2, então é R$ 258,00 senão R$ 289,00.

Clique na célula em branco ao lado da palavra Memórias e digite:
=SE(O8=1;N8;SE(O8=2;N9;N10))

Se a célula O8 for igual a 1, então insira aqui o valor contido em N8, SE a célula O8 for igual a 2, então insira aqui o valor contido em N9, SENÃO, insira o valor de N10.

Agora vamos às fórmulas para verificar a validação das Caixas de Seleção dos Acessórios, onde a resposta é Verdadeiro para os itens habilitados e Falso para os itens não habilitados.

Clique na célula ao lado dos Acessórios e digite a fórmula com a função SOMASE:

=SOMASE(O12:O17;VERDADEIRO;N12:N17)

O Excel busca os valores Verdadeiros (habilitados) de O12 até O17 e quando os encontrar, somará os valores correspondes nas mesmas linhas de N12 até N17.

Agora clique como botão direito do mouse no Botão de Opção A Vista e escolha formatar controle.

Escolha uma célula na base de dados que não esteja sendo usada e defina como célula de controle da forma de pagamento. Pode ser P16, abaixo da lista das cidades. Clique OK.

Clique na célula em branco ao lado do "À Vista" e digite a fórmula:

=SE(P16=1;SOMA(C11;F11;I11);"")

SE P16 for igual a 1 (À vista) então some Processador+Memórias+Acessórios, Senão deixe a célula VAZIA.

Clique na célula em branco ao lado do "À Prazo" e digite a fórmula:

=SE(P16=2;SOMA(C11;F11;I11);"")

SE P16 for igual a 2 (À Prazo) então some Processador+Memórias+Acessórios, Senão deixe a célula VAZIA.

- A célula da entrada deixe em branco, afinal a gente não sabe quanto ocliente quer pagar na entrada.
- Na célula Saldo, o cálculo do valor total da compra menos a entrada;
=SE(P16=2;F14-C16;0)

Se a célula P16 tiver o valor 2, então o cliente vai pagar em parcelas, então faça o cálculo do valor da compra menos a entrada, senão deixe 0 (Zero)

Na célula Número de Parcelas, deixe em branco mas vamos pôr uma validação para o máximo de 10 parcelas Clique na célula em branco ao lado de Número de Parcelas e clique na Guia Dados > Validação de Dados:

- Escolha um número inteiro...

- Mínimo 2 (porque se for em 1 parcela ele está pagando à vista)
- Máximo 10.

Clique em Alerta de Erro e escreva uma mensagem ao usuário desatento...

Na célula Valor Base da parcela, vamos apenas dividir o saldo pelo número de parcelas:
=F16/I16

Nos Juros vamos aplicar três alíquotas diferentes, dependendo do número de parcelas:
De 2 a 4 = 1,75%
De 4 a 7 = 2,75%
De 7 a 10 = 3,5%

Olha a fórmula aí:
=SE(I16<4;1,99%;se(i16>=7;3,5%;2,75%))

SE o Número de Parcelas (I16) for menor que 4, então 1,99%
SE o Número de Parcelas (I16) for maior ou igual a 7, então 3,5%%
SENÃO ( se não for nenhuma das opções anteriores) 2,75%

No valor final da Parcelas, multiplique o valor base das parcelas pelo juro, mais o próprio valor das parcelas. Resultará no valor base já adicionado o juro.

No Valor final da compra, use a fórmula:

=SE(P16=2;I18*I16+C16;C14)

SE a compra foi a prazo (P16 =2), então multiplique o I18 (Valor final das parcelas) pelo I16 (quantidade de parcelas) e some ainda o C16 (a entrada), SENÃO mostre aqui o valor do C14 (Valor à vista).

Calma que tá quase....

Para concluir o formulário precisamos inserir mais um controle chamado de Caixa de Combinação para selecionar o local da entrega da mercadoria comprada. Na Guia Desenvolvedor, clique em inserir e nos controles de formulário escolha Caixa de Combinação.


Desenhe a caixa de combinação ao lado da célula "Local da Entrega"..

Clique com o botão direito do mouse sobre a caixa de combinação e escolha Formatar Controle.

Configure as opções:

Intervalo de Entrada: Esse intervalo relaciona as células que contém a lista que fará parte das opções da Caixa de Combinação. No nosso caso as cidades que serão escolhidas com um clique do mouse.

Vínculo com a célula:
Devemos indicar a célula que deverá exibir o dígito verificador que indica o item selecionado na lista.
Exemplo: Se na caixa de combinação do formulário for selecionado "Jussara - GO", essa célula exibirá o número 1, pois começando pela primeira cidade, Jussara é a primeira entrada de lista.

Linhas Suspensas: Quantos itens serão exibidos sem usar a barra de rolagem. Se sua lista contém 9 entradas, mude para 9 o número de linhas suspensas, assim o Excel não exibe a barra de rolagem vertical. Negrito

Clique Ok para aplicar as configurações.

Agora vamos testar nossa lista no formulário. Clique na caixa de combinação e escolha um outro item da lista.

Veja que Belo Horizonte é o quarto item da lista, então a célula registra 4. Esse tipo de controle poderia ser usado aqui como um critério para cobrar frete de acordo com a distãncia do cliente ou meramente informativo. A intenção aqui foi demonstrar como funciona uma lista de formulário.

Após o trabalho concluído, desabilite a visualização das linhas de grade do Excel, elas servem como guias no momento de organizar e alinhas as caixas, mas depois podem ser desligadas. Clique na Guia Layout da Página, Opções de Planilha e desmarque a opção Exibir nas linhas de Grade.

Quer aprender tudo sobre Formulários, Validações, Auditoria em Planilhas, Macros, Controles ActiveX, Programação VBA e UserForms?

Faça um curso de Excel Avançado na Qualità - Escola de informática. As inscrições estão abertas.Aproveite e descubra o poder do Conhecimento!

23 comentários:

Rodrigo Cassab disse...

Muito bom seu tutorial, vc esta de parabens. Mas ocorreu um erro na minha planilha, quando digitei a formula =SOMASE(O12:O17;VERDADEIRO;N12:N17), esta dando resultado Verdadeiro, ao invez de numeros, com as caixas selecionadas ou não. Vc saberia me dizer o que esta havendo? Obrigado.

Qualita - Escola de Informática disse...

Olá, Rodrigo, obrigado pelo seu comentário no Blog da Qualità. Verifiquei a fórmula com a função somase e na planilha que gerou a postagem, está correta.

Talvez revendo a sintaxe da função você consiga encontrar o erro na sua planilha:
A função SOMASE soma intervalos intercalados mediante uma condição:

No caso dessa planilha, a função procura a resposta VERDADEIRO no intervalo O12 até O17. Quando encontrar, soma os valores equivalentes na mesma linha, mas na coluna N (de N12 a N17).
Talvez na sua planilha, esses dados estejam em outras colunas, daí o Excel não encontra os valores numéricos para a soma.Se você quiser posso enviar a amostra da planilha por e-mail.

Abraço

Solano
Equipe Qualita - Escola de Informática

Gonpeg disse...

Olá Amigo! Fiz conforme sua demonstração, exceto uma linha que ficou diferente no banco de dados...rs ae só tive que ir trocando o n° da célula e blz., saiu perfeito, fiz enquanto estava no trabalho...rs. Sou fã dessas coisas, excel, word, access etc. Um detalhe que me chamou a atenção: Caso o cliente n compre processador ou outro item da outra seção, onde deixaria sem marcar essa opção?
Teria que criar uma opção em branco para assinalar?
Vlw, Parabéns!
Gonçalo/gonpeg@hotmail.com

Anônimo disse...

Muito bom cara! aprendi muito com essa postagem.

Qualità Escola de Informatica disse...

Agradecemos todos os comentários e elogios á postagem e que bom que está ajudando a tantas pessoas.

Gonçalo, você está certo. Se a pessoa opta por não comprar um processador, coloque mais um botão de opção e escreva "Nenhum". Na fórmula da função SE, deve ter a opção 0 (zero) no final. Ficaria assim:
=SE(O4=1;N4;SE(O4=2;N5;se(O4=3;N6;0)))

Anônimo disse...

Seu tutorial é otimo. Gostaria de uma dica para fazer uma pequena eleição em minha igreja usando um formulario do excel. Pode me ajudar????

Anônimo disse...

Muito bom o tutorial.
Parabens!

Anônimo disse...

wau... gostei mesmo muito, explicações simples e boa.

Mas quero saber como posso registar todas vendas efectuadas?

obrigado.

Anônimo disse...

Seu tutorial é otimo, estão de parabens!

Mas quero que me ajude, como posso salvar (guardar) todas as vendas já efectuadas

obrigado

Anônimo disse...

Seu tutorial é otimo, estão de parabens!

Mas quero que me ajude, como posso salvar (guardar) todas as vendas já efectuadas?

obrigado

Anônimo disse...

Seu tutorial é otimo, estão de parabens!

Mas quero que me ajude, como posso salvar (guardar) todas as vendas já efectuadas?

obrigado.

Anônimo disse...

Tenho uma tabela de jogo lotofacil, “para jogar entre amigos”.
Gostaria que você me ajudasse a melhora um pouco mais esta planilha
Erros
1ª- toda vezes que eu excluo uma linha os cálculos muda no total de acertos;
2ª se acrescento uma linha ela muda também;
3ª Ela não muda a cor dos números sorteados na tabela de jogos dos amigos;
4ª Não avisa o números de ganhadores com 15 acertos ou 14 acertos, 13 acertos e 12 acertos e não divide o prêmio
Observação o maior numero de acerto leva o Prêmio e se houver vários ganhadores o Prêmio e dividido.
Gostaria de dividir as planinhas onde a primeira Le a segunda, terceira e assim por diante.
Espero o seu contato
Obrigado desta já.
PAULOROGERIO68@YAHOO.COM.BR

Anônimo disse...

Tenho uma tabela de jogo lotofacil, “para jogar entre amigos”.
Gostaria que você me ajudasse a melhora um pouco mais esta planilha
Erros
1ª- toda vezes que eu excluo uma linha os cálculos muda no total de acertos;
2ª se acrescento uma linha ela muda também;
3ª Ela não muda a cor dos números sorteados na tabela de jogos dos amigos;
4ª Não avisa o números de ganhadores com 15 acertos ou 14 acertos, 13 acertos e 12 acertos e não divide o prêmio
Observação o maior numero de acerto leva o Prêmio e se houver vários ganhadores o Prêmio e dividido.
Gostaria de dividir as planinhas onde a primeira Le a segunda, terceira e assim por diante.
Espero o seu contato
Obrigado desta já.
PAULOROGERIO68@YAHOO.COM.BR

Anônimo disse...

Muito bom!!! Facílima compreensão. Parabéns!

Camilla disse...

Muito bom :D Tenho uma dúvida, tem como ex: ao abrir a planilha aparecer só o layout de opções e não a tabela que alimentamos? tenho como proteger para que ninguém altere com facilidade a tabela que tem os preços? Muito obrigada, de fácil compreensão e útil.

José Maria da Fonseca disse...

Olá,Sou José Maria da Fonseca. Gostei muito do modelo apresentado, mas eu preciso do seguinte: tenho um produto com vários fornecedores, daí tenho necessidade de consultar não só o produto mas seus respectivos fornecedores, e com isso: endereço, telefone, site, contato e e-mail. É possível?
jmdafonseca@msn.com

Everton disse...

Bom Gostei Bastante tutorial, era o que eu tava procurando, mas infelizmente não ta danda certo quando marco as opções, no DIGITO DE CONTROLE, aparece os números na ordem crescente ou seja:
PROCESSADOR
AMD ATHLON XP = DIGITO 1
INTEL CORE2DUO = DIGITO 2
INTEL I5 = DIGITO 3

ATÉ AI TUDO BEM, MAS QUANDO EU CLICO NAS MÉMORIAS APARECE:
MÉMORIA
2GB = 4
4GB = 5
8GB = 6
Ta aparecendo desse jeito
nessa ordem.
O certo num era pra aparecer na mesma ordem da Caixa de Grupo dos Processadores não? Mim ajudem ai...

Everton disse...

Bom Gostei Bastante tutorial, era o que eu tava procurando, mas infelizmente não ta danda certo quando marco as opções, no DIGITO DE CONTROLE, aparece os números na ordem crescente ou seja:
PROCESSADOR
AMD ATHLON XP = DIGITO 1
INTEL CORE2DUO = DIGITO 2
INTEL I5 = DIGITO 3

ATÉ AI TUDO BEM, MAS QUANDO EU CLICO NAS MÉMORIAS APARECE:
MÉMORIA
2GB = 4
4GB = 5
8GB = 6
Ta aparecendo desse jeito
nessa ordem.
O certo num era pra aparecer na mesma ordem da Caixa de Grupo dos Processadores não? Mim ajudem ai...

Everton disse...

Oi Turma, Muito bom o tutoria. Mas no caso de compra acima de um Produto, Ou seja: se o Cliente comprar 3 MÉMORIAS 4Gb, como ficaria?
Desde Já Agradeço a ATENÇÃO...

juninhojatei disse...

Boa noite, por favor, se possível me mande a planilha pra que eu possa ver, pois gerou alguns erros, e queria ver onde estou errando. Meu Email é jr_jorge188@hotmail.com

Carlos André disse...

bROTHER MUITO FERA ESSE TUTORIAL, BEM EXPLICATIVO, GOSTEI MUITO, SOU UM AVENTUREIRO NA AREA DE PROGRAMAÇÃO MAS QUERO APRENDER E DOMINAR ESTA ARTE.
OBRIGADO POR SUA AJUDA.

Ana... disse...

Excelente! Excelente ajuda! Perfeito! Tem mais tutoriais seus que eu possa acompanhar e continuar aprendendo... Fico a aguardar a indicação. Brigada mesmo! Tudo de Bom! Ana

Acacio Julio Mugunhe disse...

Obrigado pela explicação. Mas como posso gravar mais de Uma venda