Quantos kits disponíveis - dicas do Excel

Hoje, um problema interessante do Excel sobre listas de materiais. Você tem muitas matérias-primas. Cada item pode ser montado em várias montagens de nível superior diferentes. Com base na matéria-prima disponível, você tem o suficiente para atender a um pedido de determinado item?

Assistir vídeo

  • Tim pergunta: quantos de cada item estão disponíveis para venda
  • Fator de complicação: um item é composto de várias caixas
  • Método de cobrança nº 1: adicione uma coluna auxiliar com INT (quantidade necessária / disponível)
  • Adicionar subtotais para o mínimo de auxiliar em cada mudança no produto
  • Recolher subtotais para a visão # 2
  • Selecione todos os dados. Use alt = "" +; para selecionar células visíveis
  • Cole para um novo intervalo
  • Ctrl + H para alterar o espaço mínimo para nada
  • Método Mike # 2
  • Copie a coluna Produto à direita e use Dados, Remova Duplicados
  • Ao lado da lista exclusiva de produtos, use MINIFS
  • Observe que MINIFS está disponível apenas no Office 365
  • Método Bill # 3: uma tabela dinâmica regular falha porque os Campos calculados não funcionam neste caso.
  • Selecione uma célula em seus dados e pressione Ctrl + T para converter em uma tabela.
  • Em vez disso, ao criar a tabela dinâmica, escolha a caixa Adicionar ao modelo de dados
  • Crie uma nova medida para Disponível para Venda usando INT
  • Crie uma nova medida para o Kit Disponível para Venda usando MINX
  • Essa tabela dinâmica funciona!
  • Método Mike # 4 Use a função AGGREGATE.
  • Parece que você gostaria de usar o argumento MIN, mas use SMALL porque ele lida com matrizes
  • Usar =AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1)
  • AGGREGATE é uma das cinco funções que podem aceitar uma matriz como um argumento sem Ctrl + Shift + Enter
  • Método Bill # 5
  • Converta os dados em uma tabela e use o Power Query - também conhecido como Get & Transform
  • No Power Query, calcule OH / Necessário
  • Use a função Number.RoundDown para converter para inteiro
  • Use o agrupamento por número de peça e disponibilidade mínima
  • Fechar e carregar
  • Bônus: é renovável!

Transcrição de vídeo

MrExcel: Ei, bem-vindo de volta, é hora de outro Podcast de Duelo do Excel. Chamo-me Bill Jelen, e junto com ele Mike Girvin, do Excel Is Fun. Este é o nosso episódio 190: Quantos kits estão disponíveis para venda?

Tudo bem, a pergunta de hoje enviada por Tim. Assiste aos nossos vídeos Duelando no Excel, ele trabalha para um varejista e pediu para criar uma planilha para mostrar à nossa equipe de vendas o que possuímos e o que podemos vender. Parece simples, certo? Mas aqui está o problema: o item que vendem contém várias caixas e são inventariados por caixa. Aqui está um exemplo do que ele está vendo. Portanto, aqui está este item, P12345, tem 3 itens diferentes que devem ser enviados. E no kit, requer 4 da Caixa 1, 1 da Caixa 2 e 1 da Caixa 3. E é assim que eles têm em estoque. Tudo bem, apenas fazendo as contas aqui, eles têm 2 conjuntos completos da Caixa 1, 4 conjuntos completos da Caixa 2 e 3 conjuntos completos da Caixa 3. Mas isso significa que o que eles podem vender é o mínimo desses 3 números - eles só podem vender 2. E aqui, eles têm 4 conjuntos completos da Caixa 4,4 da Caixa 5, 2 da Caixa 3, apenas 1 da Caixa 7 - esse é o item limitante. Então, neste caso, eles podem vender apenas um desses. Tudo bem. Agora, uma pergunta para um dia posterior, eu disse: "Bem, há alguma chance de que a Caixa 3 seja usada em mais de um lugar?" E ele diz: "Sim, mas vamos nos preocupar com isso mais tarde." Tudo bem.

Então é assim que vou atacar isso. Na verdade, posso pensar em várias maneiras diferentes de atacar isso, então isso pode ser interessante - pode ser um tipo de duelo de ida e volta. O que farei é, quero ter uma coluna do Helper aqui, e a coluna do Helper examinará, item por item, quantos podemos vender. Então = 8 dividido por 4, assim, e clicaremos duas vezes para copiá-lo. Mas, digamos que precisássemos de 4 e tivéssemos 6. Tudo bem, agora vamos dizer 1,5. Bem, você não pode vender, sabe, meio sofá, certo? Então, terá que ser o número inteiro. Então, o que vou fazer aqui é usar = INT-- INT, o inteiro - aquela coisa que tira os decimais e nos deixa apenas o valor total. Tudo bem. Então temos 8 - de volta ao número original.

E precisamos descobrir, para cada item aqui, qual é o menor número na coluna E? Certifique-se de que os dados estão classificados por Produto, vá para a aba Dados, escolha Subtotais, a cada mudança no Produto, use a função Mín. Você sabe, eu ensino Subtotais o tempo todo em meus seminários do Power Excel e aponto que há 11 funções aqui, mas nunca usei nada além de Soma e Contagem. Portanto, embora o Subtotal possa não ser a maneira mais rápida de fazer isso, quero poder dizer que, na verdade, houve um momento em que consegui usar algo diferente de Soma e Contagem. Tudo bem, clique em OK. E o que vamos obter, é que cada vez que o número da cortina - o número do produto - muda, podemos ver o valor mínimo E esse Min é a resposta que queremos. Então eu reduzi para a visualização número 2, selecionarei todos esses dados e Alt +;para selecionar apenas as células visíveis, Ctrl + C, e então desceremos aqui e colar - vamos apenas colar nesta área - Ctrl + V. Tudo bem. Exclua as colunas extras e então temos que nos livrar da palavra Min. E não apenas a palavra Min, mas o espaço Min. Tudo bem. Vou usar Ctrl + H e mudar a recorrência do espaço Min para nada, Substituir tudo, clicar em OK, clicar em Fechar e aqui está nossa tabela do que temos disponível para vender. Tudo bem, Mike, vou passar para você.e aí está a nossa tabela do que temos disponível para vender. Tudo bem, Mike, vou passar para você.e aí está a nossa tabela do que temos disponível para vender. Tudo bem, Mike, vou passar para você.

Mike: Uau! MrExcel, eu adoro isso. A função Min em Subtotais. Quão legal é isso? Tudo bem, vou passar para esta folha bem aqui, vou fazer a mesma coluna Helper. = INT pegaremos todos os itens "Disponíveis" divididos pela "Quantidade necessária", feche parênteses. Ctrl + Enter, clique duas vezes e envie para baixo. Agora, eu só preciso encontrar o Mín disponível para uma determinada condição ou critério. Vou selecionar Produto, Ctrl + Shift + Arroe para baixo, Ctrl + C para copiar, em seguida, vou para a seta para a direita, Ctrl + V, vou subir e dizer Remover duplicatas. Aí está.

Eu costumava usar Filtro Avançado, Somente Registros Únicos o tempo todo, mas parece que esse método é mais rápido. Essa é minha lista exclusiva. Agora vou vir aqui. Quantos? E vou usar a nova função, MINIFS. Agora, o MINIFS está no Office 365; para Excel 2016 ou posterior, o MINRANGE. Bem, eu preciso encontrar o valor mínimo nesta coluna, Ctrl + Shift + Seta para baixo, F4, vírgula e o intervalo de critérios - esse será todo o produto. Ctrl + Shift + Seta para baixo, F4, vírgula, Seta para a esquerda e pronto. Isso obterá o valor mínimo de quantos, com base na condição ou nos critérios, feche os parênteses, Ctrl + Enter, clique duas vezes e envie para baixo. Tudo bem. Portanto, há MINIFS e Subtotal. Vou jogar de volta para você.

MrExcel: Sim, Mike, muito bom. Remova as duplicatas, obtenha a lista exclusiva de produtos e, em seguida, a função MINIFS. Eu perguntei a ele em qual versão do Excel ele está, ele disse Excel 2016. Espero que seja a versão do Office 365 de 2016, então ele tem acesso a ela. Bem, que tal uma Tabela Dinâmica? Tudo bem, então criei uma Tabela Dinâmica com Produto e Requer, Soma das Quantidades Requeridas e Soma do Disponível. Então, a partir daqui, "Analisar", "Campos, Itens e Conjuntos", "Campo Calculado", e criar um novo campo calculado chamado "Disponível", que está Disponível dividido pela Quantidade Requerida - dessa forma, eu não preciso a coluna do ajudante aqui. E no início parecia que ia funcionar porque tínhamos 2, 3 e 4 e o relatório de que o mínimo é 2-- Eu mudei este cálculo, é claro, para Min,e isso parecia bom.

But then, on this one, where we have 2,4,4,1,2, it's reporting 3. And what's happening is it's doing the calculation on this row. We have 25 on hand, divided by 8, that's 3 and a fraction, and so it's reporting 3, and so, no. A regular Pivot Table calculate item is not going to work. But instead, convert this data to a table and then Insert, PivotTable, Add this data to the Data Model, click OK. And we're going to have, down the left-hand side, Product and what it Requires. I'm going to create two implicit measures here with a Required Quantity and some of On Hand, and then I'm going to create a new measure. So, PowerPivot, Measure, a New Measure, and this new measure will be called Available to Sell (AvailToSell) and that formula is going to be, how many we have on hand divided by how many are required for each item, and click OK. Alright, so 8 divided by 4 is 2.

Alright. Now, that's still not our right answer, and we probably need to run this through the Integer function. So, Measures, Manage Measures, edit this and wrap the whole thing inside the INT function like this, click OK, and click Close. Now we're getting a fractional number-- still the wrong answer here.

But we're going to use a great new function that's only available in DAX. New Measure, and this is going to be called KitAvailable, and the function is not MIN, but MINX-- MINX. The MINX function. And the table that we're going to use is Table 1, and then expression is going to be that Available to Sell that we just calculated, and what this does-- the MINX function evaluates on a row by row basis and finds the minimum error. And so, we'll click KitAvailable, OK. Well, check this out: So here, where we have 2, 4, 4, 1, and 2, it's reporting 1. Alright, now in a perfect world all we have is Product and KitAvailable-- we don't need any of this other stuff in the middle. Alright. So we're just going to check this here, 2, 1, 3, 2, are our answers. I'll take the Requires out, 2, 1, 3, 2, yes. It's going to work. We actually take all the intermediate calculations out, just have a KitAvailable, like that. Mike, do you have another one?

Mike: How cool is that,? You use the MINX function in DAX; well, I'm going to go back over here, I'm going to use a formula. But I'm going to pretend like I don't even have this Helper column. I used MINIFS. Well, before MINIFS, in Excel 2016 there was the AGGREGATE function in Excel 2010. Now I want to use MIN, but of course, functions 1 to 13 do not let you do array formulas. So I'm going to have to use SMALL 1 as a substitute for the MIN function. And SMALL is one of the functions, 14 and above, that can handle array operations. That argument right there, array. So function number 15, comma, I want to ignore divided by zero error, so I'm going to type a 6 to ignore errors, comma, and I need to simulate that whole Helper column in the array argument-- INT. And instead of simply saying On Hand divided by Require, we do the whole column, Ctrl+Shift+Down Arrow, F4, divided by the Required column-- Ctrl+Shift+Down Arrow, F4-- now close parenthesis. That INT right there, if I highlight this and hit F9, it simulates that entire How Many Helper column. Ctrl+Z, now I simply divide it by, in parentheses, I need to get an array of TRUES and FALSEs, so I click on Product, Ctrl+Shift+Down Arrow, F4, and I ask the question are any of you equal to that Product ID, close parentheses. That will give me a bunch of TRUES and FALSEs. F9 TRUES and FALSEs in the denominator, TRUE will become a 1, FALSE will become a 0, which will give us divide by zero error. Ctrl+Z.

In essence, if I click the whole array in here, F9, the divide by zero is going to be our filter, so we only see the numbers for a particular Product. Ctrl+Z, and then, of course, AGGREGATE will pick the min out from that array of errors and numbers, close parenthesis. And AGGREGATE's amazing-- one of five functions that has an argument that can handle array operations without Ctrl+Shift+Enter. So I simply Ctrl+Enter and F2. What did I forget? Backspace. Array, then I type a comma and the K is 1 because I always want SMALL 1, which is the min, close parentheses. Ctrl+Enter, double-click, and send it down, F2. Alright. Aggregate with that whole Helper column right there to get how many for each Product. Alright? I'm going to throw it back over to.

MrExcel: Hey, that's beautiful. I knew there'd be a lot of different ways to solve this. I did not think of using AGGREGATE, which of course is better, because if someone has 2010, this will work. The 15 allows an array out here that is gorgeous. Alright, now, hey, when I set up the question, I just missed this and, you know, and Mike, you know this, when people send us questions, they try and minimize the situation to make it sound like it's easy, but the thing that's going to be a disaster here, is the fact that Carton 3 is used in multiple places, alright? And as soon as they sell something from, let's say, they sell, like, this item P12346, well then the number of Carton 3s on hand is going to change, right? And so that's going to impossibly impact what else we can sell.

Alright. So, thinking about how Tim is going to have to manage this process, he's going to have to have a way to regenerate this item quickly. And so, hopefully, he has an inventory table for every item. It'll show how many there are on hand and then, a VLOOKUP here, to pull the inventory over. Alright? That's what I'm hoping is going to happen, because then it might become somewhat manageable. And if this is something we have to reproduce again and again and again, then Power Query definitely has a use here.

So, Power Query in Excel 2010 or 2013, you're going to go download it, you'll have your own Power Query tab; but in Excel 2016, you're going to look for the Get and Transform. It's funny, in Excel 2016, it was the second group, but then in Office 365 they moved it to be the first group. Power Query has the ability to take something from a Table or Range, so I'm going to choose one cell in this table, Ctrl+T-- that will create a table for me. Table 3 is a fine name, I don't need to rename that. Now, this is the Table, we go to Data, From Table or Range, and we are going to Add a new Column-- this column is going to be a Custom Column, it's going to be called "Available", and that is going to be the On Hand divided by Required Quantity. Alright. Now, we need to send this into the INT function. Unfortunately, the function and Power Query are not the same. So, click here and then go to Formula Types, and you'll find this function is called Number.RoundDown, and this is case sensitive-- you have to make sure to use that exact same case. So =Number.RoundDown, open paren, and closed paren, and click OK. And so 11 divided by 4 is 2.75, rounds down to 2. Alright. That's the answer we need there, we don't need these columns anymore. So I can click on Requires, Shift+click on On Hand, and remove those columns. Alright. Now, choose Product, Transform, Group By, we're going to group by the Product, and the new function is going to be called KitsAvailable, and the operation is going to be the min of the available column. Click OK. Alright.

So now we have Product and KitsAvailable. Home, Close & Load, get a brand new sheet with our answers, but here's the beautiful thing. Alright, so, when we sell something-- let's make these columns less wide-- and we sell, let's say we sell enough so we have no Carton 3s left, I change that number there, the VLOOKUPS bring the results, and then come back here and choose this and Refresh all. And you see that now we have none of this, and this, and this, available to sell, because they all needed that Carton 3, and we have none of those left. Being able to Refresh in Power Query is going to help this in the end.

Well, this was a fun one for me because I knew there would be a lot of different ways to solve this problem. The Episode wrap up of this really long Episode: How many of each item is available to sell? And there's multiple cartons, alright? So, the first thing I did was add a Helper column; and then use Subtotals with the Min function; and then a whole bunch of really boring steps. Make had method number two, used MINIFS, which is great if you have Office 365. I went back to a Pivot Table, but a regular Pivot Table won't work, instead had to do a Data Model and then use the MINX function-- the MINX function-- and that calculated field or measure will actually work. Mike, using the AGGREGATE function, beautiful function, one of five functions that can accept an array as an argument without Ctrl+Shift+Enter. And then, method 5, convert the data to a table and use Power Query, also known as Get & Transform; and we're going to calculate On Hand divided by Needed (Required); and then the Number.RoundDown function to convert to an integer; group by part name, number, and calculate the minimum available; Close & Load; and the bonus, it's refreshable.

Bem, ei, quero agradecer a você por passar por aqui, nos vemos na próxima vez para outro Podcast de Duelo do Excel do MrExcel e o Excel é divertido.

⇬ Fazer download do arquivo

Baixe o arquivo de amostra aqui: Duel190.xlsx

Artigos interessantes...