Vandaag een interessant Excel-probleem over stuklijsten. Je hebt veel grondstoffen. Elk item kan worden geassembleerd tot verschillende assemblages op het hoogste niveau. Heeft u, op basis van de beschikbare grondstof, genoeg om een bestelling voor een bepaald artikel uit te voeren?
Bekijk video
- Tim vraagt: hoeveel van elk item zijn er beschikbaar om te verkopen
- Complicerende factor: een artikel bestaat uit meerdere dozen
- Factuurmethode # 1: een hulpkolom toevoegen met INT (aantal nodig / bij de hand)
- Voeg subtotalen toe voor de Min van Helper bij elke wijziging in Product
- Vouw subtotalen samen tot de # 2-weergave
- Selecteer alle gegevens. Gebruik alt = "" +; voor Selecteer zichtbare cellen
- Plakken in een nieuw bereik
- Ctrl + H om Space Min in niets te veranderen
- Mike-methode # 2
- Kopieer de Productkolom naar rechts en gebruik Gegevens, Duplicaten verwijderen
- Gebruik MINIFS naast de unieke lijst met producten
- Merk op dat MINIFS alleen beschikbaar is in Office 365
- Factuurmethode # 3: een gewone draaitabel mislukt omdat berekende velden in dit geval niet werken.
- Selecteer een cel in uw gegevens en druk op Ctrl + T om naar een tabel te converteren.
- Kies in plaats daarvan het vak voor Toevoegen aan gegevensmodel terwijl u de draaitabel maakt
- Maak een nieuwe meetwaarde voor Beschikbaar voor verkoop met INT
- Maak een nieuwe meting voor Kit beschikbaar voor verkoop met MINX
- Die draaitabel werkt!
- Mike Methode # 4 Gebruik de AGGREGATE-functie.
- Het lijkt erop dat je het MIN-argument zou willen gebruiken, maar gebruik SMALL omdat het arrays afhandelt
- Gebruik
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE is een van de vijf functies die een array als argument kunnen accepteren zonder Ctrl + Shift + Enter
- Factuurmethode # 5
- Converteer de gegevens naar een tabel en gebruik Power Query, ook wel Get & Transform genoemd
- Bereken in Power Query OH / Needed
- Gebruik de functie Number.RoundDown om naar integer te converteren
- Gebruik groeperen op onderdeelnummer en minimale beschikbaarheid
- Sluiten en laden
- Bonus: het is vernieuwbaar!
Videotranscriptie
MrExcel: Hey, welkom terug, het is tijd voor nog een Dueling Excel Podcast. Ik ben Bill Jelen van, ik zal worden vergezeld door Mike Girvin van Excel Is Fun. Dit is onze aflevering 190: hoeveel kits zijn er beschikbaar om te verkopen?
Oké, de vraag van vandaag ingezonden door Tim. Bekijkt onze Dueling Excel-video's, hij werkt voor een winkel en vroeg om een spreadsheet te maken om ons verkoopteam te laten zien wat we bezitten en wat we kunnen verkopen. Klinkt simpel toch? Maar hier is de vangst: het item dat ze verkopen bevat meerdere dozen en wordt per doos geïnventariseerd. Hier is een voorbeeld van wat hij ziet. Dus hier is dit item, P12345, heeft 3 verschillende dingen die ze moeten verzenden. En in de kit heb je 4 van doos 1, 1 van doos 2 en 1 van doos 3 nodig. En dit is hoeveel ze op voorraad hebben. Oké, dus reken maar gewoon hier, ze hebben 2 complete sets van doos 1, 4 complete sets van doos 2 en 3 complete sets van doos 3. Maar dat betekent dat ze het minimum van die 3 nummers kunnen verkopen. kan er slechts 2 verkopen. En hier hebben ze 4 complete sets van doos 4,4 van doos 5, 2 van doos 3, slechts 1 van doos 7 - dat is het beperkende item. Dus in dit geval kunnen ze er maar één verkopen. Oké. Nu, een vraag voor een latere dag, zei ik: "Is er een kans dat doos 3 op meer dan één plaats wordt gebruikt?" En hij zegt: "Ja, maar daar gaan we ons later zorgen over maken." Oké.
Dus hier is hoe ik dit ga aanvallen. Ik kan eigenlijk verschillende manieren bedenken om dit aan te vallen, dus dit kan interessant zijn - dit kan een heen en weer soort duel zijn. Wat ik ga doen is, ik wil hier een Helper-kolom hebben, en de Helper-kolom gaat per artikel bekijken hoeveel we er kunnen verkopen. Dus = 8 gedeeld 4, zoals dat, en we dubbelklikken om het naar beneden te kopiëren. Maar laten we zeggen dat we er 4 nodig hadden, en we hadden er 6. Oké, dus nu gaat het zeggen 1.5. Nou, je kunt niet verkopen, weet je, een halve bank, oké? Dus het moet het hele getal zijn. Dus wat ik hier ga doen, is de = INT-- INT, het gehele getal - dat ding gebruiken dat de decimalen zal verwijderen en ons gewoon het hele bedrag achterlaat. Oké. Dus dan hebben we 8 - terug naar het oorspronkelijke nummer.
En we moeten uitzoeken, voor elk item hier, wat het kleinste getal in kolom E is? Zorg ervoor dat de gegevens op Product zijn gesorteerd, ga naar het tabblad Gegevens, kies Subtotalen, gebruik bij elke wijziging in Product de functie Min. Weet je, ik geef de hele tijd subtotalen tijdens mijn Power Excel-seminars, en ik wijs erop dat er hier 11 functies zijn, maar ik heb nooit iets anders gebruikt dan Som en Count. Dus hoewel Subtotaal misschien niet de snelste manier is om dit te doen, wil ik kunnen zeggen dat er eigenlijk een keer was dat ik iets anders kon gebruiken dan Som en Count. Oké, klik op OK. En wat we zullen krijgen, is dat elke keer dat het gordijnnummer - het productnummer - verandert, we de Min. En dat Min is het antwoord dat we willen. Dus ik stortte in tot de weergave nummer 2, ik selecteer al deze gegevens en Alt +;om alleen de zichtbare cellen te selecteren, Ctrl + C, en dan komen we hier naar beneden en plakken - laten we gewoon in dit gebied plakken - Ctrl + V. Oké. Verwijder de extra kolommen en dan moeten we het woord Min verwijderen. En niet alleen het woord Min, maar de spatie Min. Oké. Dus ik ga Ctrl + H gebruiken en de herhaling van spatie Min veranderen in niets, Alles vervangen, klik op OK, klik op Sluiten, en daar is onze tabel met wat we beschikbaar hebben om te verkopen. Oké, Mike, ik gooi het naar je toe.en daar is onze tafel met wat we te koop hebben. Oké, Mike, ik gooi het naar je toe.en daar is onze tafel met wat we te koop hebben. Oké, Mike, ik gooi het naar je toe.
Mike: Wauw! MrExcel, ik vind het geweldig. De Min-functie in subtotalen. Hoe cool is dat? Oké, ik ga hier naar dit blad, ik ga dezelfde Helper-kolom doen. = INT we nemen alle "Voorhanden" gedeeld door "Vereiste hoeveelheid", haakjes sluiten. Ctrl + Enter, dubbelklik en verzend het naar beneden. Nu moet ik alleen de Min vinden die beschikbaar is voor een bepaalde conditie of criteria. Ik ga Product selecteren, Ctrl + Shift + Arroe omlaag, Ctrl + C om te kopiëren, dan ga ik naar de pijl naar rechts, Ctrl + V, en dan ga ik naar boven en zeg Duplicaten verwijderen. Daar is het.
Ik gebruikte altijd Advanced Filter, Unique Records Only, maar het lijkt erop dat deze methode sneller is. Daar is mijn unieke lijst. Nu ga ik hierheen komen. Hoeveel? En ik ga de nieuwe functie MINIFS gebruiken. Nu zit MINIFS in Office 365; voor Excel 2016 of hoger is de MINRANGE. Welnu, ik moet de minimumwaarde in deze kolom vinden, Ctrl + Shift + Pijl-omlaag, F4, komma en het criteriabereik - dat wordt dit hele product. Ctrl + Shift + Pijl-omlaag, F4, komma, Pijl-links, en daar gaan we. Dat krijgt de min-waarde van hoeveel, op basis van de voorwaarde of criteria, haakjes sluiten, Ctrl + Enter, dubbelklikken en naar beneden verzenden. Oké. Dus er zijn MINIFS en Subtotaal. Ik gooi het terug naar jou.
MrExcel: Ja, Mike, erg aardig. Verwijder duplicaten, haal de unieke lijst met producten op en vervolgens de MINIFS-functie. Ik vroeg hem welke versie van Excel hij gebruikt, hij zei Excel 2016. Ik hoop dat het Office 365-versie van 2016 is, dus daar heeft hij toegang toe. Nou, wat dacht je van een draaitabel? Oké, dus ik heb een draaitabel gemaakt met Product en vereist, Som van vereiste hoeveelheden en Som van voorhanden. Vervolgens vanaf hier "Analyseren", "Velden, items en sets", "Berekend veld", en een nieuw berekend veld gemaakt met de naam "Beschikbaar", dat voorhanden is gedeeld door vereiste hoeveelheid - op die manier heb ik niet nodig de Helper Column hier. En in het begin leek het alsof het zou werken, want we hadden 2, 3 en 4 en de melding dat het minimum 2 is - Ik heb deze berekening natuurlijk gewijzigd in Min,en dat leek me goed.
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.
Nou, hey, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor nog een Dueling Excel Podcast van MrExcel en Excel is leuk.
Download bestand
Download het voorbeeldbestand hier: Duel190.xlsx
