Segundo Exercicio Programa 2014

Prévia do material em texto

Reposição de produtos faltantes para uma casa de 
vinhos 
Segundo Exercício-Programa 
FEA-Administração Noturna - 1o. Semestre de 2014 
 
 
1. Funcionamento da loja: 
o Esta loja se especializa em vinhos importados. 
o No fim de cada ano, a direção da loja se reúne para estabelecer estratégia do ano seguinte: 
1. Na primeira etapa, define quais são os países da origem de vinho a ser 
comercializado para o ano seguinte. 
2. Baseada na decisão da primeira etapa começa a segunda etapa. Nesta etapa a loja 
negocia com seus fornecedores e potenciais fornecedores para definir de quais 
fornecedores vão comprar os vinhos de cada pais de origem durante o ano seguinte. 
Dependendo da negociação, algum fornecedor tradicional pode deixar de negociar 
com a loja e também podem surgir novos fornecedores. 
o O conjunto de fornecedores de vinho de cada pais de origem varia de pais para pais. 
o Nomenclatura: 
 Npais é número de países de origem de vinhos a ser importados 
 Nfornec é número total de fornecedores de vinhos. (Possivelmente este número não 
vai ser usado no EP2). 
o Definida os produtos e os fornecedores, a loja monta uma planilha que será usada durante o 
ano todo seguinte. 
2. A Planilha: 
1. A planilha muda cada ano dependendo de conjunto de pais de origem de vinho e conjunto 
de fornecedores para vinho de cada pais de origem. Mas existem características da planilha 
que não mudam. A seguir descrevo estas características inalteradas. 
2. A linha 1 é cabeçalho. As células A1, B1 e C1 contem origem, fornec (fornecedor) e quant 
(quantidade) respectivamente. A célula D1 é vazia. As Npais células a partir da célula E1 são 
lista de nomes de países de origem. 
3. A coluna A é de origem de vinho. Cada origem é separada por células vazias. O número de 
células vazias para cada origem é igual ao número de fornecedores de vinhos desta origem. 
4. A coluna B é lista de fornecedores de vinhos de cada origem. Note que a célula na linha do 
nome de origem é sempre vazia. Assim o bloco de fornecedores de vinhos de um pais é 
separado dos blocos vizinhos de modo bem visível. 
3. A Planilha 2014: 
0. Para o ano de 2014 a loja vai importar os vinhos de 5 países: França, Itália, Alemanha, 
Portugal e Espanha e vai trabalhar com 7 fornecedores: Loja A, Casa B, deposito C, Lojão D, 
Armazém E, Lojinha F e Casinha G. Isto significa que Npais = 5 e Nfornec = 7. 
1. Este conjunto de países de origem de vinho é apenas deste ano. Por exemplo, para ano 2015, 
a loja decide não trabalhar mais com vinho alemão, mas começa trabalhar com vinho 
argentino e vinho chileno. 
2. A seguinte planilha (Planilha 1) é a planilha para 2014 definida no final de 2013. Nesta 
planilha a linha 1 e colunas A e B serão inalteradas durante todo o ano de 2014. 
3. Nesta planilha, as linhas a partir da linha 30 (inclusive) não são usadas e as colunas a partir 
da coluna J (inclusive) não são usadas. Logo a parte efetivamente usada da planilha é 
retângulo com 29 linhas X 9 colunas que fica no canto superior esquerda. A coluna D vai ser 
usada mais tarde. 
4. A coluna A é de origem de vinho. Para a planilha 2014 existem 5 origens. cada origem é 
separada por células vazias. O número de células vazias para cada origem é igual ao número 
de fornecedores de vinhos desta origem. 
5. A coluna B é lista de fornecedores de vinhos de cada origem. Note que a células na linha do 
nome de origem é sempre vazia. Assim um bloco de fornecedores de vinhos de um pais é 
separado do bloco de fornecedor de vinhos de outro pais. 
6. Para o ano 2014, enquanto os vinhos franceses são fornecidos por Loja A, Casa B, deposito C 
e Lojao D, os vinhos italianos são fornecidos por Loja A, Lojao D e Casinha D. 
7. Atenção: No ano de 2014 a ordem dos países da coluna 1 é idêntica da ordem dos países na 
linha 1 a partir da célula E1. Mas este é acidental! A princípio, as 2 ordens podem ser 
diferentes seguindo 2 critérios diferentes. Este fato é importante na hora de construir EP. 
< 
 A B C D E F G H I J 
1 origem fornec quantd francês italiano alemão português espanhol 
2 francês 
3 Loja A 
4 Casa B 
5 Deposito C 
6 Lojao D 
7 italiano 
8 Loja A 
9 Lojao D 
10 Casinha G 
11 alemão 
12 Deposito C 
13 Armazém E 
14 Lojinha F 
15 casinha G 
16 português 
17 Loja A 
18 Casa B 
19 Deposito C 
20 Armazém E 
21 Lojinha F 
22 Casinha G 
23 espanhol 
24 Loja A 
25 Casa B 
26 Lojao D 
27 Armazém E 
28 Lojinha F 
29 Casinha G 
30 
4. Duas atividades na planilha da loja no fim de semana 
0. Toda sexta-feira o funcionário A examina vários indicadores numéricos inclusive os 
estoques restantes de vinhos de cada origem e estabelece quantidade (de caixas) a ser 
reposto para semana seguinte. Este valor é colocado na célula da linha do nome de origem 
na coluna C. Por exemplo, a quantidade a ser reposto de vinho francês fica na célula C3 e a 
de vinho italiano na célula C7 e assim por diante. 
1. Ao mesmo tempo o funcionário B contacta cada fornecedor para saber quantidade (de 
caixas) de vinho de cada origem que cada fornecedor pode fornecer para a loja na semana 
que vem. Este valor é colocado na célula da coluna C na linha do nome do fornecedor. 
2. A seguinte planilha (Planilha 2) é planilha resultante de trabalho dos funcionários A e B de 
um certo fim de semana. 
 
< 
 A B C D E F G H I J 
1 origem fornec quantd francês italiano alemão português espanhol 
2 francês 200 
3 Loja A 50 
4 Casa B 60 
5 Deposito C 70 
6 Lojao D 100 
7 italiano 250 
8 Loja A 300 
9 Lojao D 300 
10 Casinha G 250 
11 alemão 180 
12 Deposito C 25 
13 Armazém E 40 
14 Lojinha F 30 
15 casinha G 35 
16 português 150 
17 Loja A 30 
18 Casa B 25 
19 Deposito C 40 
20 Armazém E 50 
21 Lojinha F 35 
22 Casinha G 45 
23 espanhol 100 
24 Loja A 20 
25 Casa B 30 
26 Lojao D 40 
27 Armazém E 45 
28 Lojinha F 35 
29 Casinha G 25 
30 
5. Uma Atividade na planilha da loja no começo de semana 
0. Toda segunda-feira de manhã, 0(a) funcionário (a) C abre a planilha. Ele (a) determina quais 
são os fornecedores necessários para vinhos de cada pais de origem. 
1. Critério de escolha de fornecedores: 
 Para repor estoque de vinhos de um pais de origem, é possível ter várias 
combinações de fornecedores. Observemos a planilha 2 e vamos pensar no caso de 
vinhos franceses. A quantidade necessária para repor estoque é 200. Temos 3 
possíveis combinações: Loja A + Casa B + Lojao D, Loja A + Deposito C + Lojao D e 
Casa B + Deposito C + Lojao D. 
 A loja adota seguinte critério simples: Na ordem decrescentede quantidade 
fornecida. Em outras palavras, sempre privilegia quem fornece maior quantidade. 
Logo a combinação vai ser Lojao D + Deposito C + Casa B nesta ordem. E 0(a) 
funcionário (a) C escreve na coluna E a lista de fornecedores seguindo a ordenação 
pelo critério acima. 
 Note que enquanto Lojao D e Deposito C fornecem o total de sua quantidade 
disponibilizada para a loja, a Casa B fornece apenas metade de sua quantidade 
disponível para a loja. Mas esta questão não é nenhum problema. O que o (a) 
funcionário (a) C deve fazer é apenas listar dos fornecedores para vinhos de cada 
pais de origem. 
 Vejamos o caso de vinhos italianos. Cada um dos 3 fornecedores sozinho já fornece 
quantia necessária, mas pelo critério acima deve ser Loja A ou Lojao D. Quando tem 
empate qualquer um serve. 
 Por outro lado, Pode acontecer que, mesmo juntando todas as quantidades de 
fornecedores, não atinge a quantidade necessária de reposição. É o caso de vinhos 
alemão. No caso como este, o (a) funcionário (a) C lista todos os fornecedores (na 
ordem decrescente) e numa célula imediatamente abaixo da última célula (a célula 
do última fornecedor) da coluna escreve "$$$". 
 A planilha seguinte planilha 3 é a planilha após o trabalho do (a) funcionário (a) C. 
< 
 A B C D E F G H I J 
1 origem fornec quantd francês italiano alemão português espanhol 
2 francês 200 Lojao D Loja A 
Armazém 
E 
Armazém 
E 
Armazém 
E 
 
3 Loja A 50 
Deposito 
C 
 Casinha G Casinha G Lojao D 
4 Casa B 60 Casa B Lojinha F 
Deposito 
C 
Lojinha F 
5 
Deposito 
C 
70 
Deposito 
C 
Lojinha F 
6 Lojao D 100 $$$ 
7 italiano 250 
8 Loja A 300 
9 Lojao D 300 
10 Casinha G 250 
11 alemão 180 
12 
Deposito 
C 
25 
13 
Armazém 
E 
40 
14 Lojinha F 30 
15 casinha G 35 
16 português 150 
17 Loja A 30 
18 Casa B 25 
19 
Deposito 
C 
40 
20 
Armazém 
E 
50 
21 Lojinha F 35 
22 Casinha G 45 
23 espanhol 100 
24 Loja A 20 
25 Casa B 30 
26 Lojao D 40 
27 
Armazém 
E 
45 
28 Lojinha F 35 
29 Casinha G 25 
30 
6. Brilhante ideia do (a) funcionário (a) C 
o Durante o dia, o (a) funcionário (a) C trabalha nesta loja de vinhos e a noite ele (a) estuda na 
FEA/USP e um dos curso que ele (a) está fazendo é mac113!. 
o Assim, enquanto fazia este trabalho semanal de segundas-feiras, ele (a) percebeu 
rapidamente que é possível transformar este trabalho num programa em VBA. 
o Ao começar a pensar em um programa em VBA para planilha do ano 2014, ele (a) percebeu 
o seguinte: A aparência de planilha pode mudar de um ano para outro mas sua estrutura 
geral não muda. Logo é possível construir um programa para ano genérico que também 
possa ser usado em 2014. 
7. A grande revelação: 
o O (a) funcionário (a) C é Você!! 
o O programa em VBA que ele (a) vai fazer é seu Exercício-programa 2. 
8. Exercício-Programa 
o A planilha (0,5 ponto): 
 Faça uma planilha como a A planilha 2 acima. 
 Deve ter, no mínimo, 6 países de origem de vinhos e total de 8 fornecedores. 
 Para vinhos de cada pais de origem, as quantidades oferecidas por fornecedores 
devem ser bastante variadas para ter na planilha diversos cenários possíveis. 
 A ordem dos países de origem na linha 1 a partir da célula E1 deve ser 
obrigatoriamente diferente da origem dos países de origem na coluna A 
 Quem quer ser criativo, pode escolher um produto diferente de vinho. 
o A macro (9,5 ponto): 
 As constantes (0,5 ponto): 
 Use obrigatoriamente as constantes. 
 Lembre-se que a macro deve ser usada em qualquer ano. Logo não faz 
sentido constantes que apenas serve para planilha 2014. 
 Use obrigatoriamente Npais (número de países de origem de vinhos) como 
uma das constantes. 
 As funções (1,0 ponto): 
 Use obrigatoriamente as 2 seguintes funções: 
 LinhaDoPais: 
1. parâmetro (string): pais que representa o nome do pais de origem do 
vinho. 
2. valor de retorno (inteiro): A linha em que este pais se encontra na 
coluna A. 
3. exemplos: 
 Se pais = "francês", LinhaDoPais (pais) = 2. 
 Se pais = "português", LinhaDoPais (pais) = 16. 
 NumFornec: 
1. parâmetro (inteiro): linha uma linha na coluna B. 
2. valor de retorno (inteiro): o número de células não vazias até a 
próxima célula vazia na coluna B. 
3. exemplos: 
 Se linha = 2, NumFornec (linha) = 4 
 Se linha = 16, NumFornec (linha) = 6 
 As 2 funções são necessárias pois a ordem dos países na linha 1 a partir da 
célula E1 nem sempre é igual à ordem dos países na coluna A. Logo para 
cada pais na linha 1, você deve localizar este pais na coluna A. 
 você pode criar mais funções, se isto torna sua macro melhor. 
 o algoritmo da loja para fazer a lista de fornecedores de vinhos de um determinado 
pais: 
1. Usaremos vinhos franceses como exemplo . Logo focaremos em apenas 6 
primeiras linha e 5 primeiras colunas da planilha 2. 
 
 A B C D E 
1 origem fornec quantd francês 
2 francês 200 
3 Loja A 50 
4 Casa B 60 
5 Deposito C 70 
6 Lojao D 100 
2. algoritmo 
É bastante simples: Escolha sempre o fornecedor que oferece maior 
quantidade e sem símbolo * na coluna D. 
3. No começo, nenhum fornecedor tem * na coluna D. Logo todos os 4 
fornecedores tem condição de serem escolhidos. Entre eles, Lojao D oferece a 
maior quantidade. Logo escolhemos primeiro Lojao D e marcamos com * na 
coluna D deste fornecedor. A planilha fica assim: 
 
 A B C D E 
1 origem fornec quantd francês 
2 francês 200 Lojao D 
3 Loja A 50 
4 Casa B 60 
5 Deposito C 70 
6 Lojao D 100 * 
4. Como precisa de 200 caixas mas Lojao D apenas oferece 100, o processo de 
escolha continua. Quem oferece mais? Continua sendo Lojao D. Mas ele já 
tem * na coluna D, logo ele é excluído da escolha. Então entre os 3 
fornecedores restantes sem * quem oferece mais? A resposta é Deposito C. A 
planilha vai ficar assim: 
 
 A B C D E 
1 origem fornec quantd francês 
2 francês 200 Lojao D 
3 Loja A 50 Deposito C 
4 Casa B 60 
5 Deposito C 70 * 
6 Lojao D 100 * 
5. Temos 170 caixas. Ainda deve completar 30 caixas. Vamos continuar o 
processo. Temos 2 fornecedores sem *. Obviamente escolhemos Casa B e a 
planilha fica assim: 
 
 A B C D E 
1 origem fornec quantd francês 
2 francês 200 Lojao D 
3 Loja A 50 Deposito C 
4 Casa B 60 * casa B 
5 Deposito C 70 * 
6 Lojao D 100 * 
Como já temos a quantidade necessária, o processo termina! 
6. O uso do algoritmo acima é obrigatório. 
 Depois de completar a lista de fornecedores de todos os países de origem, sua macro 
deve limpar a coluna D. 
 
o observação 1: Certamente qualquer expert em vinhos vai ficar horrorizado com o trecho 
como "200 caixas de vinho francês". Vai dizer algo como “A França tem várias regiões 
produtores começando por Bordeaux e depois Bourgogue....e cada região produtor tem 
famosos produtores tradicionais" etc, etc, etc. mas como o professor gosta de vinhos mas é 
um apreciador iniciante (por enquanto) e este é apenas um exercício-programa, não vamos 
complicar mais. 
o observação 2: Alémde ser trabalhoso, este EP2 é conceitualmente mais complexo que o EP1. 
Logo recomendo que comece trabalhar já! 
o dica: Primeiro faça uma macro para os vinhos de um pais de origem apenas e depois pense 
em como fazer para vinhos de todos os países. 
o proibição: Neste EP2, você não deve usar decomposição em pequenas macros. Deve 
construir apenas 1 macro! 
o continuidade para EP3: O EP3 vai ser uma extensão deste EP2, logo é muito importante que 
você faça este EP2 e entender o contexto do problema para evitar desgaste enorme no EP3 
que tem peso de 55% na média dos EPs. 
 
o Data limite de Entrega: 23hs55min/02/06/2014 
o Bom Trabalho!