EXERCICIO FINAL DE EXCEL AVANÇADO
-
Upload
cicero-maia -
Category
Documents
-
view
223 -
download
0
Transcript of EXERCICIO FINAL DE EXCEL AVANÇADO
-
7/28/2019 EXERCICIO FINAL DE EXCEL AVANADO
1/7
EXERCICIO FINAL DE EXCEL AVANADO
1 Criar as tabelas de apoio
2-Renomear esta planilha com o nome de Tabelas de Apoio
3- Inserir a quantidade de estoque igual ao da figura acima.
4- na prxima planilha faa a seguinte planilha conforme a figura, abaixo:
5- Esta planilha segue at alinha 28, e siga conforme a figura logo abaixo.
6-Agora vamos a parte mais legal da historia, vamos aos campos calculados.
-
7/28/2019 EXERCICIO FINAL DE EXCEL AVANADO
2/7
7-No campo Vendedor, se o cdigo do vendedor estiver na tabela de funcionrios deve exibir o
nome, seno deve mostrar que o cdigo digitado no existe, detalhe esta tabela esta na
planilha tabelas de apoio.
8 No campo Departamento, se o cdigo do funcionrio estiver na tabela de funcionrios,
deve mostrar o nome do departamento do funcionrio, seno exibir que o cdigo no existe,
detalhe o departamento esta na mesma tabela de funcionrios.9- No campo Data, inserir a data da venda.
10 No campo Produto a mesma coisa, sem mistrios se o cdigo do produto existir, exiba o
nome do produto seno informe que o cdigo no existe.
11- No campo Estoque Atual, a mesma coisa, se o cdigo do produto existir na tabela de
produtos, deve mostra a quantidade de estoque que tambm esta na tabela de produtos.
12- no campo quantidade a quantidade vendida do produto.
13- No campo Preo, verificar se o cdigo do produto existe, se existir deve mostra o preo de
acordo com o produto.
14- No campo Subtotal o produto da quantidade vendida pelo preo de venda.
15- No campo comisso, verificar se o cdigo do funcionrio existe, se existir deve-se calcular a
comisso da venda, que definida pelo percentual da comisso pelo valor do subtotal.16- Na Celula K2 entra o ms. 17 Na clula K29 entra o total geral do subtotal. 18 Na clula
L29 entra o total geral das comisses.
19 Na linha 31 temos o campo funcionrio e na C31 temos o campo Comissao, logo abaixo
temos a seguinte situao, quando digitarmos o cdigo do funcionrio na clula A32 a clula
B32 deve mostrar o nome do funcionrio, se o cdigo no existir deve mostra que no existe,
j na clula C32 devemos somar somente as comisses referente aquele vendedor.
20 Vamos agora a parte da automao, clique na guia Desenvolvedor, se estiver vendo ,
clique no boto do Office , opes e clique em mostrar a guia de desenvolvedor na faixa de
opcpes. Feito isso vamos usar um controle chamado Caixa de seleo conforme mostra a
prxima figura.
21 quando escolher o controle Caixa de Selecao, voc deve desenha este controle ao lado do
campo Comissao, aps desenhar s clicar com o boto direito e quando aparecer o menu
popup, escolher formatar controle e deixe conforme a imagem a baixo.
-
7/28/2019 EXERCICIO FINAL DE EXCEL AVANADO
3/7
22- Na guia Web no campo Texto alternativo digite Update
23- Na guia Controle , no campo Valor marque conforme a figura abaixo, o vinculo da clula
tambm, e o sombreamento 3D.
24- Vamos criar a macro que vai fazer fazer a baixa do estoque de acordo com a quantidade
vendida, para isto vamos abrir o ambiente de desenvolvimento VBA, faa como na imagem.
25 Para criar uma macro, devemos escrever os cdigos VBA que iram manipular os valores
das clulas dentro das planilhas, segue agora o cdigo comentado da manipulao destes
dados.
-
7/28/2019 EXERCICIO FINAL DE EXCEL AVANADO
4/7
26- vamos agora criar um modulo onde vai conter o cdigo, segue conforme a imagem abaixo.
27 vamos criar um Sub procedimento da seguinte forma.--------------------------
Sub baixaNoestoque()
If (Worksheets("Mapa de Vendas-01").Range("N4") = 1) Then
Worksheets("Mapa de Vendas-01").Range("H4") = (Worksheets("Mapa de
Vendas-01").Range("G4")) - (Worksheets("Mapa de Vendas-01").Range("I4"))
Worksheets("Mapa de Vendas-01").Range("M4") = FALSO 'limpa o checkbox
'copia o estoque atual para a celula k2
Worksheets("Tabelas de Apoio").Range("K1") = Worksheets("Mapa de
Vendas-01").Range("H4")
'copia o codigo do produto para a celula k3
Worksheets("Tabelas de Apoio").Range("L1") = Worksheets("Mapa de
Vendas-01").Range("E4")
Call upDate
ElseIf (Worksheets("Mapa de Vendas-01").Range("N5") = 1) Then
Worksheets("Mapa de Vendas-01").Range("H5") = (Worksheets("Mapa de
Vendas-01").Range("G5")) - (Worksheets("Mapa de Vendas-01").Range("I5"))
Worksheets("Mapa de Vendas-01").Range("M5") = FALSO 'limpa o checkbox
'copia o estoque atual para a celula k2
Worksheets("Tabelas de Apoio").Range("K1") = Worksheets("Mapa de
Vendas-01").Range("H5")
'copia o codigo do produto para a celula k3
Worksheets("Tabelas de Apoio").Range("L1") = Worksheets("Mapa deVendas-01").Range("E5")
Call upDate
ElseIf (Worksheets("Mapa de Vendas-01").Range("N6") = 1) Then
Worksheets("Mapa de Vendas-01").Range("H6") = (Worksheets("Mapa de
Vendas-01").Range("G6")) - (Worksheets("Mapa de Vendas-01").Range("I6"))
Worksheets("Mapa de Vendas-01").Range("M6") = FALSO 'limpa o checkbox
'copia o estoque atual para a celula k2
Worksheets("Tabelas de Apoio").Range("K1") = Worksheets("Mapa de
Vendas-01").Range("H6")
'copia o codigo do produto para a celula k3
Worksheets("Tabelas de Apoio").Range("L1") = Worksheets("Mapa de
Vendas-01").Range("E6")
-
7/28/2019 EXERCICIO FINAL DE EXCEL AVANADO
5/7
Call upDate
ElseIf (Worksheets("Mapa de Vendas-01").Range("N7") = 1) Then
Worksheets("Mapa de Vendas-01").Range("H7") = (Worksheets("Mapa de
Vendas-01").Range("G7")) - (Worksheets("Mapa de Vendas-01").Range("I7"))
Worksheets("Mapa de Vendas-01").Range("M7") = FALSO 'limpa o checkbox
'copia o estoque atual para a celula k2
Worksheets("Tabelas de Apoio").Range("K1") = Worksheets("Mapa de
Vendas-01").Range("H7")
'copia o codigo do produto para a celula k3
Worksheets("Tabelas de Apoio").Range("L1") = Worksheets("Mapa de
Vendas-01").Range("E7")
Call upDate
ElseIf (Worksheets("Mapa de Vendas-01").Range("N8") = 1) Then
Worksheets("Mapa de Vendas-01").Range("H8") = (Worksheets("Mapa de
Vendas-01").Range("G8")) - (Worksheets("Mapa de Vendas-01").Range("I8"))
Worksheets("Mapa de Vendas-01").Range("M8") = FALSO 'limpa o checkbox
'copia o estoque atual para a celula k2
Worksheets("Tabelas de Apoio").Range("K1") = Worksheets("Mapa de
Vendas-01").Range("H8")
'copia o codigo do produto para a celula k3
Worksheets("Tabelas de Apoio").Range("L1") = Worksheets("Mapa de
Vendas-01").Range("E8")
Call upDate
ElseIf (Worksheets("Mapa de Vendas-01").Range("N9") = 1) Then
Worksheets("Mapa de Vendas-01").Range("H9") = (Worksheets("Mapa de
Vendas-01").Range("G9")) - (Worksheets("Mapa de Vendas-01").Range("I9"))
Worksheets("Mapa de Vendas-01").Range("M9") = FALSO 'limpa o checkbox
'copia o estoque atual para a celula k2
Worksheets("Tabelas de Apoio").Range("K1") = Worksheets("Mapa de
Vendas-01").Range("H9")
'copia o codigo do produto para a celula k3
Worksheets("Tabelas de Apoio").Range("L1") = Worksheets("Mapa de
Vendas-01").Range("E9")
Call upDate
ElseIf (Worksheets("Mapa de Vendas-01").Range("N10") = 1) Then
Worksheets("Mapa de Vendas-01").Range("H10") = (Worksheets("Mapa de
Vendas-01").Range("G10")) - (Worksheets("Mapa de Vendas-01").Range("I10"))
Worksheets("Mapa de Vendas-01").Range("M10") = FALSO 'limpa o checkbox
'copia o estoque atual para a celula k2
Worksheets("Tabelas de Apoio").Range("K1") = Worksheets("Mapa de
Vendas-01").Range("H10")
'copia o codigo do produto para a celula k3
Worksheets("Tabelas de Apoio").Range("L1") = Worksheets("Mapa de
Vendas-01").Range("E10")
Call upDate
ElseIf (Worksheets("Mapa de Vendas-01").Range("N11") = 1) Then
Worksheets("Mapa de Vendas-01").Range("H11") = (Worksheets("Mapa de
Vendas-01").Range("G11")) - (Worksheets("Mapa de Vendas-01").Range("I11"))
Worksheets("Mapa de Vendas-01").Range("M11") = FALSO 'limpa o checkbox
-
7/28/2019 EXERCICIO FINAL DE EXCEL AVANADO
6/7
'copia o estoque atual para a celula k2
Worksheets("Tabelas de Apoio").Range("K1") = Worksheets("Mapa de
Vendas-01").Range("H11")
'copia o codigo do produto para a celula k3
Worksheets("Tabelas de Apoio").Range("L1") = Worksheets("Mapa de
Vendas-01").Range("E11")
Call upDate
End If
End Sub
------------------------
28 Agora vamos criar o boto de aao que vai afetuar a baixa no estoque, crie da mesma
forma como foi criado o controle deCaixa de Selecao que foi desenhado anteriormente,
quando desenhar o boto, logo aparece uma caixa de propriedade do boto, nele vamos
escolher a macro que criamos, o nome dela j esta l, chamado de baixaNoEstoque e clique
em OK.
29 Vamos agora criar a atualizao do estoque para que possa ser visto na planilha principal
do nosso sistema, segue agora o cdigo da funo Updade.
Function upDate()
'se o codigo do produto for igual a o codigo na celula L1
If (Worksheets("Tabelas de Apoio").Range("F3") = Worksheets("Tabelas de
Apoio").Range("L1")) Then
'o campo estoque celula I3 recebe o valor da celula K1 com o valor
atualizado
Worksheets("Tabelas de Apoio").Range("I3") = Worksheets("Tabelas de
Apoio").Range("K1")
ElseIf (Worksheets("Tabelas de Apoio").Range("F4") = Worksheets("Tabelas de
Apoio").Range("L1")) Then
Worksheets("Tabelas de Apoio").Range("I4") = Worksheets("Tabelas de
Apoio").Range("K1")
ElseIf (Worksheets("Tabelas de Apoio").Range("F5") = Worksheets("Tabelas de
Apoio").Range("L1")) Then
Worksheets("Tabelas de Apoio").Range("I5") = Worksheets("Tabelas de
Apoio").Range("K1")
ElseIf (Worksheets("Tabelas de Apoio").Range("F6") = Worksheets("Tabelas de
Apoio").Range("L1")) Then
Worksheets("Tabelas de Apoio").Range("I6") = Worksheets("Tabelas de
Apoio").Range("K1")
ElseIf (Worksheets("Tabelas de Apoio").Range("F7") = Worksheets("Tabelas de
Apoio").Range("L1")) Then
Worksheets("Tabelas de Apoio").Range("I7") = Worksheets("Tabelas de
Apoio").Range("K1")
ElseIf (Worksheets("Tabelas de Apoio").Range("F8") = Worksheets("Tabelas de
Apoio").Range("L1")) Then
Worksheets("Tabelas de Apoio").Range("I8") = Worksheets("Tabelas de
Apoio").Range("K1")
ElseIf (Worksheets("Tabelas de Apoio").Range("F9") = Worksheets("Tabelas de
Apoio").Range("L1")) Then
Worksheets("Tabelas de Apoio").Range("I9") = Worksheets("Tabelas deApoio").Range("K1")
-
7/28/2019 EXERCICIO FINAL DE EXCEL AVANADO
7/7
ElseIf (Worksheets("Tabelas de Apoio").Range("F10") = Worksheets("Tabelas de
Apoio").Range("L1")) Then
Worksheets("Tabelas de Apoio").Range("I10") = Worksheets("Tabelas de
Apoio").Range("K1")
End If
MsgBox "Estoque atualizado", vbInformation, "Estoque"
End Function
30 Agora podemos salvar tudo e executar.
31 Protegendo o sistema.