Baixe o app para aproveitar ainda mais
Prévia do material em texto
Reposição de produtos faltantes para uma casa de vinhos: Parte 2 Terceiro Exercício-Programa FEA-Administração Noturna - 1o. Semestre de 2014 1. Este EP: o Este EP3 é uma extensão do EP2. o Assuntos tratados: decomposição de problema trabalho com várias planilhas e macro com parâmetros. 2. Na mesma loja de vinhos no final de 2014: o Este EP3 acontece supostamente no final do ano 2014. 3. 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. o Novidade: Durante o ano de 2014, o (a) funcionário (a) C teve excelente desempenho e foi promovido (a). Agora ele (a) opina sobre funcionamento de loja e ele (a) sugeriu a seguinte mudança sobre planilha de pedido de vinhos. Sua brilhante ideia foi aceita e adotada pela direção. A mudança é seguinte. o Definida os produtos e os fornecedores, a loja monta 3 planilhas que será usada durante o ano todo seguinte. 4. As 3 Planilhas: 1. As 3 planilhas mudam 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 das planilhas que não mudam. A seguir descrevo estas características inalteradas. 2. A Planilha-A: A linha 1 é cabeçalho. As células A1, B1 e C1 contem origem, fornec (fornecedor) e quant (quantidade) respectivamente. 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. 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 o bloco de fornecedores de vinhos de um pais é separado dos blocos vizinhos de modo bem visível. A coluna C é vazia exceto a célula C1. A coluna D é vazia. Basicamente a Planilha-A é primeiras 4 colunas da a Planilha 1 do EP2. 3. A Planilha-B: Ela tem Npais colunas. A linha 1 é cabeçalho. Ela tem Npais nomes dos pais de origem de vinho. Basicamente a Planilha-B é Npais colunas a partir da célula E1 da A Planilha 1 do EP2. No máximo ela vai ocupar (Nfornec + 1) linhas 4. A Planilha-C: Ela tem (Npais + 1) linhas e (Nfornec + 2) colunas. A célula A1 é vazia. Na coluna A a partir da célula A2 tem Npais nomes de pais de origem de vinho. Na linha 1 a partir da célula B1 tem Nfornec nomes dos fornecedores de vinho e depois tem string faltante. 5. A teimosia da direção da loja??: o Baseada na experiência do ano 2014, o (a) funcionário (a) C sugeriu que a ordem de países de origem de vinho na coluna A da Planilha-A:, a ordem de países de origem de vinho na linha 1 da Planilha-B: e a ordem de países de origem de vinho na coluna A da Planilha-C: sejam idênticas. Ele (a) acredita que programa para planilha fica bem mais simples. Mas por uma razão misteriosa, a direção da loja não aceitou a ideia. Assim foi mantido que as 3 ordens podem ser diferentes. 6. As 3 Planilhas 2015: 0. As decisões no final do 2014: Trabalhar com vinhos de 5 países: França, Itália, Alemanha, Portugal e Espanha. Trabalhar com 7 fornecedores: Loja A, Casa B, deposito C, Loja o D, Armazém E, Lojinha F e Casinha G. logo Npais = 5 e Nfornec = 7. A loja montou seguintes 3 planilhas abaixo. A Planilha-A-2015: abaixa mostra quais são os fornecedores de cada pais de origem de vinho após a negociação no final de 2014. A ordem de países de origem de vinho na coluna A é decrescente em quantidade total de vinhos vendidos de casa pais no ano 2014. a ordem de países de origem de vinho na linha 1 da A Planilha-B-2015: é decrescente em número de fornecedores. a ordem de países de origem de vinho na coluna A da A Planilha-C-2015: é alfabética. O (A) funcionário (a) C viu que, de fato, as ordens de cada planilhas são diferentes mas ainda não sabe por que a direção da loja insiste nisso. Mas como ele (a) é apenas um (a) funcionário, resolveu não discutir mais sobre este assunto. 1. A Planilha-A-2015: A B C D 1 origem fornec quantd 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 Loginha F 29 Casinha G 30 2. A Planilha-B-2015: A B C D E 1 espanhol português alemão francês italiano 2 3 4 5 6 7 8 3. A Planilha-C-2015: A B C D E F G H I 1 Loja A Casa B Deposito C Lojao D Armazém E Lojinha F Casinha G faltante 2 alemão 3 espanhol 4 francês 5 italiano 6 português 7. 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 garrafas) a ser reposto para semana seguinte. Este valor é colocado na célula da linha do nome de origem na coluna C da Planilha-A:. Por exemplo, a quantidade a ser reposto de vinho francês fica na célula C2 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 garrafas) 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-2015-A preenchida resultante de trabalho dos funcionários A e B de um certo fim de semana x. 3. A Planilha-A-2015 (preenchida): A B C D 1 origem fornec quantd 2 francês 200 3 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 Loginha F 35 29 Casinha G 25 30 8. A grande revelação: o Vocênão é o (a) funcionário (a) C!! o Afinal o (a) funcionário (a) C foi promovido (a) e agora conta com um (a) subordinado (a): o (a) funcionário (a) D o Agora você vai ser o (a) funcionário (a) D. 9. Atividades nas planilhas do (a) funcionário (a) D no começo de semana 0. Toda segunda-feira de manhã, o (a) funcionário (a) D abre a Planilha-A. Ele (a) determina quais são os fornecedores necessários para vinhos de cada pais de origem. Ele (a) vai preencher a Planilha-B assim como o (a) funcionário (a) C preenchia as colunas E em diante da planilha no ano 2014 no EP2. 1. Critério de escolha de fornecedores (idem ao EP2): Para repor estoque de vinhos de um pais de origem, é possível ter várias combinações de fornecedores. Observemos a planilha-A-2015 (preenchida) 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 decrescente de 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) D escreve na coluna D da planilha-B-2015 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) D deve fazer é apenas a lista 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ãos. No caso como este, o (a) funcionário (a) D 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 é a Planilha-B-2015 após o trabalho do (a) funcionário (a) D. A Planilha-B-2015 (preenchida) A B C D E 1 espanhol português alemão francês italiano 2 Armazém E Armazém E Armazém E Lojao D Loja A 3 Lojao D Casinha G Casinha G Deposito C 4 Lojinha F Deposito C Lojinha F Casa B 5 Lojinha F Deposito C 6 $$$ 7 8 2. Construção da Planilha C: Feita a Planilha B, o (a) funcionário (a) D passa a construir a Planilha C. Ele (a) vai construir Planilha C baseada nas Planilha A e Planilha B. Nesta planilha, a célula na posição (i, j) contem a quantidade de vinho do pais i do fornecedor j para 2 <= j <= (Nfornec + 1). E a célula na posição (i, Nfornec + 2) contem $$$ se a soma de quantias oferecidos pelos fornecedores não é suficiente. A seguinte planilha é Planilha-2015-C preenchida baseada nas Planilha-2015- A (preenchida) e Planilha-2015-B (preenchida). A Planilha-C-2015 (preenchida): A B C D E F G H I 1 Loj a A Cas a B Deposit o C Loja o D Armazé m E Lojinh a F Casinh a G faltant e 2 alemão 25 40 30 35 $$$ 3 espanhol 40 45 15 4 francês 30 70 100 5 italiano 250 6 portuguê s 40 50 10 45 Usando a Planilha-2015-C (preenchida) a loja sabe qual vai ser pedido para cada fornecedor de modo bem fácil. Por exemplo, para Deposito C deve pedir 25 vinhos alemãos, 70 vinhos francês e 40 vinhos português. Notes que para o último fornecedor (na ordem decrescente) a loja pode pedir menos que a quantia disponível deste fornecedor. Os números em negrito nesta planilha mostra estes casos. 10. Brilhante ideia do (a) funcionário (a) D o Durante o dia o (a) funcionário (a) D trabalha nesta loja de vinhos e a noite ele (a) estuda na FEA/USP e um dos cursos dele (a) é mac113!! como foi o (a) funcionário (a) C. o Assim, enquanto fazia este trabalho semanal de segundas-feiras, ele (a) percebeu também rapidamente que é possível transformar este trabalho num programa em BVA. o Ao começar a pensar em um programa em VBA para planilhas do ano 2015, 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 2015. o Uma conversa entre C e D: C: Acho que basta ter Planilha A e Planilha C. Não precisa de Planilha B para funcionamento da loja. D: Eu concordo com você. Mas o pessoal da loja tem amor pelo este formato e não vou discutir com eles. C: OK 11. Exercício-Programa o A planilha (0,5 ponto): Faça 3 planilhas: Planilha-201x-A, Planilha-201x-B e Planilha-201x-C como Planilha-2015-A, Planilha-2015-B e Planilha-2015-C acimas. Deve ter, no mínimo, 6 países de origem de vinhos e total de 10 fornecedores. Preenche a coluna C da Planilha-201x-A. 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 de vinho nas 3 planilhas devem ser diferentes. Quem quer ser criativo, pode escolher um produto diferente de vinho. o Os programas (9,5 ponto): A partir de Planilha-201x-A (preenchida), você tem 2 tarefas básicas: Preencher a Planilha-201x-B. Preencher a Planilha-201x-C. As constantes: 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 dada. Use obrigatoriamente Npais (número de países de origem de vinhos) e Nfornec é (número total de fornecedores de vinhos). As funções (idênticas ao EP2): 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 da Planilha-201x-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 da Planilha- 201x-A. 2. valor de retorno (inteiro): o número de células não vazias até a próxima célula vazia na coluna B da Planilha-201x-A. 3. exemplos: Se linha = 2, NumFornec (linha) = 4 Se linha = 16, NumFornec (linha) = 6 você pode criar mais funções, se isto torna sua macro melhor. Macro PreenchePlaB (3 pontos): Esta macro preenche a Planilha-201x-B transformando-a em Planilha- 201x-B(preenchida). Basta modificar EP2 no novo contexto! Macro (com parâmetro) PreencheLinPlaC (6 pontos): Parâmetro (inteiro): lin que é uma linha da Planilha-201x-C O que ela faz: Preenche a linha lin da Planilha-201x-C Atenção: É possível fazer esta macro (com parâmetro) usando apenas a Planilha-201x-A (preenchida). Mas isto significa jogar fora todo trabalho da Macro PreenchePlaB acima. Você deve usar obrigatoriamente as informações numéricas da Planilha-201x-A e lista de fornecedores já selecionados e ordenados de vinhos de um determinado pais de origem na Planilha-201x-B. Macro PreenchePlaC (0,5 ponto): Preenche a Planilha-201x-C Ela usa obrigatoriamente a macro (com parâmetro) PreencheLinPlaC acima. Macro Preen2Pla (0,5 ponto): Preenche as Planilha-201x-B e Planilha-201x-C Ela usa obrigatoriamente as macros PreenchePlaB e PreenchePlaC acimas. o nomenclatura: Você deve respeitar as nomenclaturas usadas no enunciado deste EP3. o Data limite de Entrega: 23hs55min/25/06/2014 o Bom Trabalho e boas férias!!
Compartilhar