Excel Retornar soma de valores exportados apenas para certos items de uma tabela excel

[CaueMS]

Bom dia, primeiramente peço desculpas caso esteja postando na área errada do fórum, peço ao administrador que mova este tópico para a respectiva área para que eu possa ter essa dúvida solucionada.

Vou descrever brevemente o que é a planilha que estou montando e aonde exatamente eu tenho dúvidas.

 

Eu estou montando uma planilha no Excel 2016 for Mac, onde eu possa selecionar as REFEIÇÕES que vou comer durante a semana em forma de calendário. Eu tenho uma tabela com o preço de cada ingrediente no mercado e a quantidade. A partir dessa planilha eu fui capaz de montar uma outa tabela, onde eu informo a quantidade de cada ingrediente utilizada para montar a REFEIÇÃO. Fazendo uma regra de 3 simples entre duas tabelas eu fui capaz de descobrir o quanto cada REFEIÇÃO CUSTA (pois já tenho o quanto uso de cada ingrediente, e já tenho o valor de cada ingrediente). 

 

A minha primeira questão é: Como eu faço para abater o valor das refeições selecionadas na primeira tabela (calendário semanal) de um valor de Budget que eu informo em outra célula? Lembrando que as refeições podem repetir durante a semana. No calendário eu tenho 84 células, porém algumas vão eventualmente ficar em branco.

 

A planilha tem as seguintes abas:

 

* Plan of the Week - Um calendário semanal onde seleciono as refeições através de um Drop Down menu.

* Meals - Lista de refeições, e ao lado eu já adicionei a fórmula que calcula o valor de cada refeição (Utilizei SUMIF)

* Meals Ingredients - Onde eu informo quanto preciso de cada ingrediente para cada refeição. No campo da refeição utilizo o menu drop down novamente, e no segundo campo, outro drop down menu, porém com os ingredientes (que eu puxo da outra tabela com os preços dos ingredientes). Tive que fazer em forma de lista para poder fazer o SUMIF funcionar. Isso está funcionando perfeitamente.

* Price - Simples tabela com o nome de cada ingrediente, preço, por quantidade, e unidade de medida (gramas, ml, units, pack, etc). Também adicionei uma Coluna Section que me informará qual é a sessão do mercado que encontro esse alimento (Vou explicar melhor mais pra frente)

* Shopping List - Essa aba tem 3 tabelas. A terceira é a mais importante.

A primeira é apenas um campo para eu checar rapidamente o preço de cada refeição.

A segunda é onde eu preciso de ajuda. Eu quero fazer uma tabela que me informe baseado nas refeições selecionadas, a quantidade de cada ingrediente que eu vou precisar para a semana. Lembrando que refeições diferentes podem utilizar o mesmo ingrediente (Por ex: Chicken pode ser utilizado no Strogonoff quanto no Chicken Parmegiana), então ele vai ter que me informar a quantidade que preciso de cada alimento corretamente sem repetir. 

Após a quantidade, a próxima coluna será a de preço, onde ele vai me informar o quanto aquele produto custa no mercado (porém, nesse momento ele deve utilizar a quantidade através da lista da ABA PRICE, caso contrário ele me mandaria comprar 0.2 litros de leite).

Ainda nessa tabela eu gostaria de adicionar uma coluna onde eu possa selecionar algo do tipo True/False. Para caso eu já tenha algum ingrediente em casa então quero ocultar esse item, e ele não aparecerá na terceira tabela. 

A terceira tabela é nada mais nada menos do que a lista de mercado com os itens e as quantidades do que eu preciso comprar, depois de passar pelo filtro da coluna anterior e as quantidades arredondadas para a quantidade que custa no mercado.

OBS muito importante, eu não posso simplesmente copiar o valor através da lista da ABA Price caso a quantidade seja maior que a mínima do mercado.

Por ex: Eu não posso comprar 0.2L de leite, e na lista ele me diz que o mínimo é 2 dólares para 2 Litros, nesse caso ele deve me mandar o mesmo valor da lista, que é 2 dolares para 2 litros. PORÉM, caso eu precise comprar 3 litros de leite, ele não pode me mandar comprar 2 litros por 2 dólares.

OBS2: Essa tabela deverá ter a opção de "Sort By" para que eu possa agrupar os itens da mesma seção na hora de ir ao mercado

 

Eu pensei em utilizar Pivot Tables, mas também não consegui configurar.

 

Estou anexando foto das abas da planilha e alguns campos para vocês olharem os códigos, se precisarem de alguma informação ou se ficou muito confuso por favor me perguntem, porque quero terminar isso e acho que não deve ser tão complicado, apesar da complexidade de detalhes.

 

Muito obrigado pela atenção

quizv1.zip

[CaueMS]

Eu consigo retornar o valor da célula específica utilizando o código

=INDEX(Meals!A2:B37,MATCH('Plan of the week'!B5,Items,0),2) mas não consigo fazer a soma de todos os valores de B5 até H16. Como faço essa soma? 

Se precisar posso postar a planilha aqui, caso vocês queiram fazer testes. Muito obrigado.

[deciog]

@CaueMS , Bom Dia.

 

Sim poste um modelo para que possamos fazer a formula correta

 

Decio

 

[CaueMS]

@deciog Segue o anexo

Food Chart.xlsx

[deciog]

@CaueMS

 

Tenho uma duvida

 

Pergunto na coluna onde tem o Needed (Necessário)  o que devo informar

 

Gostaria que você preencher, digitando, o resultado esperado da 3º tabela, essa muita duvida, coloca por exempro com base da Lasagne  são 4 itens

 

Já montei alguma coisa, aguardo resposta

 

Decio

 

 

 

[CaueMS]

@deciog muito obrigado pela resposta. Na coluna needed eu gostaria de um campo em branco onde eu pudesse selecionar uma lista com as opções "SIM" e "NÃO". Isso é pra caso eu já tenha esse alimento em casa e não precise dela incluída na lista do mercado. 

Um exemplo seria, suponhamos que no calendário eu deixe tudo em branco, e selecione apenas uma refeição Burgers (escolhi burgers porque é mais fácil de explicar porque tem duas Lasagne).

Na lista ele deve me retornar o que preciso para montar burgers (você encontra na aba meals ingredients)

No caso se eu só escolher burger 1x na lista ele deve me retornar na lista:

Mince - 250 - GRAMS - SIM
Burger Bun - 1 - units - SIM

Tomatoes - 1 - units - SIM

Cheese - 50 - grams - SIM

 

Suponhamos que eu veja que já tenho carne, mas não tenho Burger Bun, Tomatoes, nem Cheese.

Eu marcaria NÃO na linha da carne, e na coluna ao lado ele me informará a lista do mercado.

 

No caso seria a seguinte:

BAKERY - Burger Bun - 6 - units (6 na quantidade pois um pacote tem 6 unidades, portanto só posso comprar o mínimo de 6 pães, ou 12, 18 e assim por diante). - $3.00

VEGETABLES - Tomatoes - 1 - unit - 0.90

DAIRY - Cheese - 500 - grams - $4.50

 

 

Esqueci de adicionar uma coluna para o preço, mas ja está sendo aprimorada nesse momento.

Em baixo deve me informar o total, mas isso eu posso fazer.

adicionado 4 minutos depois

Segue planilha atualizada @deciog

Food Chart.xlsx

[deciog]

@CaueMS

 

Fiz alterações confere se esta conforme pedido.

 

Nota que fiz formatação Condicional colocando bordas caso tenha dados

 

Se foi útil clica no curtir é uma forma de agradecimento pela ajuda

Se você Visitante Também gostou e foi útil pode clicar no Curtir
 

Decio

 

Food Chart Decio.xlsx

[CaueMS]

Fala @deciog beleza, obrigado pelo envio. Porém vi que você utilizou como referência a célula B3, quando no caso ele deveria escanear na planilha da aba "Plan of the Week" todas as refeições selecionadas e somar os ingredientes nessa lista. É possível? Obrigado

[deciog]

@CaueMS

 

Posso mudar mas onde você informa a semana?? e também onde informar tipo de refeição Café almoço jantar?/

 

Decio

 

[CaueMS]

@deciog

Na primeira aba (Plan of the week) tem o calendário com o drop down menu. Eu quero que ele puxe os valores com referência no que foi inserido nessa tabela, então se eu trocar as refeições no calendário, a lista deve se atualizar automaticamente, entendeu? O tipo da refeição é irrelevante para a lista.

[deciog]

ok, porém vejo somente na domingo ou todas a semana??

 

coloco uma lista debaixo da outra de todas a semana

 

Porque usou o Drop Down na aba Shopping list 

 

Lembra eu sei montar formulas mas não entendo de seu projeto, por isso as duvidas, porque então tem 

 

Decio

 

[CaueMS]

@deciog

 

Não, deve ver todos os 84 campos de drop down list. De B5 até H16 na aba Plan of the Week.

A ideia é que toda semana eu faça uma simulação através do calendário da aba Plan of the Week.

Em seguida eu iria para a aba Shopping List e ver a lista de tudo que preciso comprar para esta semana.

Com essa lista eu vou marcar o que preciso e o que não preciso para cada item da lista. E automaticamente ele atualiza na tabela ao lado da forma que você fez, e aí eu levo essa lista no mercado e compro apenas o que vou precisar para me alimentar durante a semana.

O porém é que se eu tiver selecionado por ex: Burgers e Lasagne, ele deve agrupar as quantidades de Mince (ou qualquer outro ingrediente que se repete) na hora de me mostrar na lista e aí eu conseguiria ver a lista resumida.

 

Eu utilizei o drop down menu no calendário porque será a forma que vou escolher as refeições que quero comer no decorrer da semana. 

Utilizei o drop down menu na aba Shopping List apenas para poder checar rapidamente o preço de cada Refeição, era apenas um teste para o código e eu já posso deletar esse campo, pois já tenho essa informação na Aba Meals

 

Qualquer dúvida pode me perguntar.

[deciog]

@CaueMS

 

Ok entendi, acho melhor fazer mais uma aba com os ingrediente acumulados, e uma que já esta pronta, podemos fazer desta forma?,  mas agora tenho que ir dar um suporte ao meu cliente devo voltar depois do almoço, então vejo mais tarde, ou se for o caso amanha, sem problema.

 

Decio

 

[CaueMS]

@deciog Estou completamente aberto à sugestões, podemos sim fazer desta forma. Acredito que essa seja uma planilha muito útil para todos. Sinta-se à vontade para utilizá-la depois. Muito obrigado pela ajuda, não tenha pressa! Eu moro na Austrália, aqui são 9:30 da noite então faça tudo no seu tempo, sem pressa. Muito obrigado Decio

[deciog]

@CaueMS

 

 

Esse pais é um que gostaria de conhecer é lindo, mas frio.

 

Outra ideia, fazer uma aba com o estoque de alimentos que tenha, assim não precisa colocar sim ou não, ficaria automático, você pode elaborar?

 

Decio fui

 

[CaueMS]

@deciog Na verdade é MUITO calor na Austrália hahaha. Gostei muito da ideia, vou desenvolver o estoque dos alimentos, mas pode dar um pouco errado caso eu utilize um pouco mais de determinado alimento, então talvez seja melhor se eu mantiver a coluna do "Sim" e "Não". Vou fazer as alterações e posto em breve.

adicionado 53 minutos depois

Segue Anexo: Food Chart.xlsx @deciog

Temos agora uma nova aba chamada Stock Control.

 

Nela temos duas tabelas. A primeira tabela mostra o meu estoque atual para cada item:

 

Na primeira coluna ele puxa os valores através da planilha de preços, então se eu adicionar um item novo na tabela de preços, ele automaticamente vai criar uma linha no estoque também.

Na segunda coluna ele deve me informar a quantidade que tenho desse item, mas ele deve ser uma célula simples sem código porque eu posso querer alterar rapidamente o quanto tenho de estoque desse item, entende?

 

Na segunda tabela, eu coloquei um drop down com duas opções "Shopping List" e "Custom".

Caso selecionado shopping list, ele deve me retornar os valores iguais ao shopping list, considerando que eu tenha comprado exatamente as mesmas quantidades.

Caso selecionado custom, ele vai me deixar adicionar os itens manualmente.

 

Ao lado deixei um campo para o botão de adicionar. Ele deve ler a segunda tabela e somar os valores das quantidades na primeira tabela, substituindo seus valores. Você acha que a melhor forma de realizar isso é através de um macro? Sou iniciante no Microsoft Excel e estou fazendo essa planilha para ampliar meus conhecimentos e adquirir experiência.

 

Muito obrigado.

 

[deciog]

@CaueMS , Bom Dia, agora é 07:36 AM, em São Paulo-SP

 

Devo informar que eu não tenho pratica em VBA Macros, só em formulas, caso queira algo em VBA, devera pedir num novo tópico quando nesesario.

 

Hoje vou conferir a planilha e retorno mais trde

 

Decio

 

[CaueMS]

@deciog Sem problemas, não acho que seja necessário.

[deciog]

@CaueMS , Bom Dia.

 

Hoje esta mais calmo aqui, vou dar uma olhada

 

Decio

 

[CaueMS]

@deciog Muito obrigado

[deciog]

@CaueMS , Bom Dia.

 

Não estou conseguido pegar todas as semana, só um dia da semana, a formula esta dando erro, estou tentando, mas se alguém souber como faz pode também ajudar neste caso.

 

Decio

 

[deciog]

@CaueMS

 

Olhe na aba Shopping list coloque uma validação dependente da outra, assim pode selecionar somente o que quer

Isso são teste se não gostou avisa, colocar todos os tipos que tem assim posso acerta melhor a planilhas.

 

Gostaria de fazer também na aba Plan of the week, como café, almoço, lanche da tarde, jantar, outros, assim teria melhor opção para a semana, o que acha?

 

Ainda tenho problema para pegar todos da semana, mas estou mexendo

 

Se foi útil clica no curtir é uma forma de agradecimento pela ajuda

Se você Visitante Também gostou e foi útil pode clicar no Curtir
 
Decio

Food Chart Decio 3.xlsx

[CaueMS]

@deciog Não entendi direito o que quer dizer com uma validação dependente da outra. Acho que fica mais fácil depois que conseguir transformar todos os dados da planilha Plan of the Week em Week List e na talvez em cima coloque um botão de adicionar à Shopping List, assim posso escolher o item, a quantidade, ele me mostra o preço e depois que clicar no botão ele cai na tabela Shopping List, assim eu tenho um fluxo de ações, entende? 
E sobre separar os itens por café, almoço, jantar etc, eu já tinha feito antes, porém mudei de ideia porque posso querer colocar Drinks na seção de almoço, lanches na seção de café da manhã, etc, então preferi deixar em aberto mesmo! 

[deciog]

@CaueMS

 

A validação de lista é o Drop Down, nota que quando mudo para // MAINS // a o segundo Drop Down, seleciona somente os item do // MAINS //, olha outro anexos, faz testes.

 

O erro que tenho para colocar todos os ingredientes por exemplo da semanas, é que quando mudo Drop Down da semana fica tudo fora de ordem, precisa bolar outra forma de fazer a relação, talvez só por Macros.

 

Decio

 

Food Chart 3 Decio 4.xlsx

[CaueMS]

@deciog Ah sim, reparei nisso, legal a separação. Ainda estou aprendendo a mexer no Excel. Espero que alguém possa te ajudar com a formula. Achei que seria simples para montar a planilha, quando vi já estava uma loucura hahahaha