Indústria F & B - Como converter 1 Menu em seus componentes com o Excel?

Question

Eu trabalho na indústria de F & B e tenho um problema que o software da minha empresa não suportava, por isso tento gerenciá-lo no Excel.

Eu tenho um menu com muitos componentes. Então eu tento converter o menu para seus componentes ( imagem anexada )

Eu só tenho dados: Sopa de Frango 3 unidades

Meu banco de dados:

              Column A     | Column B       | Column C
              Chicken Soup | Water          | 100 ml
              Chicken Soup | Chicken Broth  |  10 ml
              Chicken Soup | Chicken Meat   |  50 gr

E eu quero que o resultado seja:

              Water         300 ml

              Chicken Broth  30 ml

              Chicken Meat   150 gr

O que tentei:

Eu coloco todo o meu banco de dados em uma folha e uso SUMIFS * (quantidade de componentes). O SUMIFS apenas para resumir todos os dados correspondidos com a coluna A, e eu multiplico com seus componentes

O problema com o meu trabalho:

1. Eu tenho tantas receitas (de cerca de 14k linhas) e a fórmula que eu tentei leva muito tempo para ser calculada (de cerca de 10s por dados digitados).

2. Se eu colocar meu banco de dados como minha planilha, haverá tantas linhas que resultarão em zero (porque basicamente os dados diários têm apenas 5-10 itens em média), ou seja, eu tenho que classificar o resultado novamente para ter os dados reais.

Eu também tentei com a Tabela Dinâmica, mas a Tabela Dinâmica mostrará todos os dados incluídos na quantidade ZERO (mas eu só preciso de dados que só tenham valor).

    

Answer 1

Primeiro, configure uma coluna que extraia os ingredientes da lista de receitas. Isso examina todo o menu e encontra todos os ingredientes associados a esse item.

=IFERROR(INDEX($B$1:$B$9,SMALL(IF($A$1:$A$9=F$1,ROW($A$1:$A$9),9E+99),ROW($B1)-ROW($B$1)+1)),"")

(certifique-se de pressionar ctr + Shift + Enter desde que isso esteja na fórmula de matriz

Em seguida, use uma correspondência de índice para encontrar a quantidade:

=INDEX($C$1:$C$6,MATCH(F2,$B$1:$B$6,0))*G$1&INDEX($B$1:$D$6,MATCH(F2,$B$1:$B$6,0),3)

Você pode ajustar a receita e / ou a quantidade e a lista deve se atualizar.