Heute ein interessantes Excel-Problem über Stücklisten. Sie haben viele Rohstoffe. Jedes Element kann zu mehreren verschiedenen Baugruppen der obersten Ebene zusammengesetzt werden. Haben Sie aufgrund des vorhandenen Rohmaterials genug, um eine Bestellung für einen bestimmten Artikel zu erfüllen?
Schau Video
- Tim fragt: Wie viele von jedem Artikel können verkauft werden?
- Komplikationsfaktor: Ein Artikel besteht aus mehreren Kartons
- Rechnungsmethode Nr. 1: Fügen Sie eine Hilfsspalte mit INT (benötigte Menge / verfügbar) hinzu.
- Fügen Sie bei jeder Änderung des Produkts Zwischensummen für das Minimum des Helfers hinzu
- Reduzieren Sie die Zwischensummen auf die Ansicht Nr. 2
- Wählen Sie alle Daten aus. Verwenden Sie alt = "" +; für Sichtbare Zellen auswählen
- In einen neuen Bereich einfügen
- Strg + H, um Space Min in nichts zu ändern
- Mike Methode # 2
- Kopieren Sie die Spalte Produkt nach rechts und verwenden Sie Daten, Duplikate entfernen
- Verwenden Sie neben der eindeutigen Produktliste MINIFS
- Beachten Sie, dass MINIFS nur in Office 365 verfügbar ist
- Abrechnungsmethode 3: Eine reguläre Pivot-Tabelle schlägt fehl, da berechnete Felder in diesem Fall nicht funktionieren.
- Wählen Sie eine Zelle in Ihren Daten aus und drücken Sie Strg + T, um sie in eine Tabelle zu konvertieren.
- Wählen Sie stattdessen beim Erstellen der Pivot-Tabelle das Kontrollkästchen Zum Datenmodell hinzufügen
- Erstellen Sie mit INT eine neue Kennzahl für "Verfügbar zum Verkauf"
- Erstellen Sie eine neue Kennzahl für das mit MINX zum Verkauf verfügbare Kit
- Dieser Pivot-Tisch funktioniert!
- Mike-Methode Nr. 4 Verwenden Sie die AGGREGATE-Funktion.
- Es scheint, als würden Sie das MIN-Argument verwenden wollen, aber SMALL verwenden, weil es Arrays verarbeitet
- Verwenden
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE ist eine von fünf Funktionen, die ein Array ohne Strg + Umschalt + Eingabetaste als Argument akzeptieren können
- Rechnungsmethode Nr. 5
- Konvertieren Sie die Daten in eine Tabelle und verwenden Sie Power Query - auch bekannt als Get & Transform
- Berechnen Sie in Power Query OH / Needed
- Verwenden Sie die Number.RoundDown-Funktion, um in eine Ganzzahl zu konvertieren
- Verwenden Sie die Gruppierung nach Teilenummer und Mindestverfügbarkeit
- Schließen & Laden
- Bonus: Es ist erfrischbar!
Video-Transkript
MrExcel: Hey, willkommen zurück, es ist Zeit für einen weiteren Duell-Excel-Podcast. Ich bin Bill Jelen von, ich werde von Mike Girvin von Excel Is Fun begleitet. Dies ist unsere Episode 190: Wie viele Kits können verkauft werden?
Okay, die heutige Frage von Tim. Er sieht sich unsere Duell-Excel-Videos an, arbeitet für einen Einzelhändler und bittet darum, eine Tabelle zu erstellen, um unserem Verkaufsteam zu zeigen, was wir besitzen und was wir verkaufen können. Klingt einfach, oder? Aber hier ist der Haken: Der Artikel, den sie verkaufen, enthält mehrere Kartons und wird pro Karton inventarisiert. Hier ist ein Beispiel für das, was er sieht. Also hier ist dieser Artikel, P12345, hat 3 verschiedene Dinge, die sie versenden müssen. Und im Kit sind 4 von Karton 1, 1 von Karton 2 und 1 von Karton 3 erforderlich. Und so viele haben sie auf Lager. Okay, wenn Sie hier nur rechnen, haben sie 2 komplette Sätze von Karton 1, 4 komplette Sätze von Karton 2 und 3 komplette Sätze von Karton 3. Aber das bedeutet, dass sie das Minimum dieser 3 Zahlen verkaufen können - sie kann nur 2 verkaufen. Und hier haben sie 4 komplette Sätze von Karton 4,4 von Karton 5, 2 von Karton 3, nur 1 von Karton 7 - das ist der begrenzende Gegenstand. In diesem Fall können sie also nur eine davon verkaufen. In Ordung. Nun, eine Frage für einen späteren Tag, sagte ich: "Nun, gibt es eine Chance, dass Karton 3 an mehr als einem Ort verwendet wird?" Und er sagt: "Ja, aber darüber werden wir uns später Sorgen machen." In Ordung.
Also hier ist, wie ich das angreifen werde. Ich kann mir tatsächlich verschiedene Möglichkeiten vorstellen, dies anzugreifen, daher könnte dies interessant sein - dies könnte eine Art Hin- und Her-Duell sein. Was ich tun werde, ist, dass ich hier draußen eine Hilfsspalte haben möchte, und die Hilfsspalte wird Artikel für Artikel prüfen, wie viele wir verkaufen können. Also = 8 geteilt 4, so und wir doppelklicken, um es nach unten zu kopieren. Aber nehmen wir an, wir brauchten 4 und wir hatten 6. Okay, jetzt heißt es 1,5. Du kannst doch keine halbe Couch verkaufen, okay? Es muss also die ganze Zahl sein. Also, was ich hier tun werde, ist das = INT-- INT, die ganze Zahl - das Ding, das die Dezimalstellen entfernt und uns nur den ganzen Betrag lässt. In Ordung. Dann haben wir also 8 - zurück zur ursprünglichen Nummer.
Und wir müssen für jeden Punkt hier herausfinden, was die kleinste Zahl in Spalte E ist. Stellen Sie sicher, dass die Daten nach Produkt sortiert sind, wechseln Sie zur Registerkarte Daten, wählen Sie Zwischensummen, und verwenden Sie bei jeder Änderung des Produkts die Min-Funktion. Wissen Sie, ich unterrichte die ganze Zeit Zwischensummen in meinen Power Excel-Seminaren und ich weise darauf hin, dass es hier 11 Funktionen gibt, aber ich habe nie etwas anderes als Summe und Anzahl verwendet. Obwohl Zwischensumme möglicherweise nicht der schnellste Weg ist, möchte ich sagen können, dass ich tatsächlich einmal etwas anderes als Summe und Anzahl verwenden konnte. Okay, klicken Sie auf OK. Und was wir bekommen werden, ist jedes Mal, wenn sich die Vorhangnummer - die Produktnummer - ändert, sehen wir die min. Und dieses Min ist die Antwort, die wir wollen. Also bin ich auf die Ansicht Nummer 2 zusammengebrochen, ich werde alle diese Daten auswählen und Alt +;Um nur die sichtbaren Zellen auszuwählen, Strg + C, und dann kommen wir hierher und fügen ein - lassen Sie uns einfach in diesen Bereich einfügen - Strg + V. In Ordung. Löschen Sie die zusätzlichen Spalten und dann müssen wir das Wort Min. Und nicht nur das Wort Min, sondern Raum Min. In Ordung. Also werde ich Strg + H verwenden und die Wiederholung des Leerzeichens Min in nichts ändern, Alle ersetzen, auf OK klicken, auf Schließen klicken und es gibt unsere Tabelle mit dem, was wir verkaufen können. Okay, Mike, ich werfe es dir zu.und da ist unser Tisch mit dem, was wir verkaufen können. Okay, Mike, ich werfe es dir zu.und da ist unser Tisch mit dem, was wir verkaufen können. Okay, Mike, ich werfe es dir zu.
Mike: Wow! MrExcel, ich liebe es. Die Min-Funktion in Zwischensummen. Wie cool ist das? Okay, ich werde gleich hier zu diesem Blatt gehen, ich werde die gleiche Hilfsspalte machen. = INT nehmen wir alle "On Hand" geteilt durch "Required Quantity", schließen Sie Klammern. Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten. Jetzt muss ich nur noch die Min für eine bestimmte Bedingung oder ein bestimmtes Kriterium finden. Ich wähle Produkt, Strg + Umschalt + Abwärtspfeil, Strg + C zum Kopieren, dann gehe ich zu Rechtspfeil, Strg + V, dann komme ich und sage Duplikate entfernen. Da ist es.
Früher habe ich immer Advanced Filter, Unique Records Only verwendet, aber diese Methode scheint schneller zu sein. Da ist meine einzigartige Liste. Jetzt komme ich hierher. Wie viele? Und ich werde die neue Funktion MINIFS verwenden. Jetzt befindet sich MINIFS in Office 365. für Excel 2016 oder höher das MINRANGE. Nun, ich muss den Mindestwert in dieser Spalte finden, Strg + Umschalt + Abwärtspfeil, F4, Komma und den Kriterienbereich - das wird das ganze Produkt sein. Strg + Umschalt + Abwärtspfeil, F4, Komma, Linkspfeil und los geht's. Dadurch wird der Mindestwert ermittelt, wie viele, basierend auf der Bedingung oder den Kriterien, Klammern schließen, Strg + Eingabetaste drücken, doppelklicken und nach unten senden. In Ordung. Es gibt also MINIFS und Zwischensumme. Ich werde es dir zurückwerfen.
MrExcel: Ja, Mike, sehr nett. Entfernen Sie Duplikate, rufen Sie die eindeutige Produktliste ab und rufen Sie dann die Funktion MINIFS auf. Ich habe ihn gefragt, auf welcher Excel-Version er sich befindet. Er sagte Excel 2016. Ich hoffe, es ist die Office 365-Version von 2016, also hat er Zugriff darauf. Wie wäre es mit einem Pivot-Tisch? Okay, also habe ich eine Pivot-Tabelle mit Produkt und Anforderungen, Summe der erforderlichen Mengen und Summe der verfügbaren Mengen erstellt. Dann von hier aus "Analysieren", "Felder, Elemente & Mengen", "Berechnetes Feld" und ein neues berechnetes Feld mit dem Namen "Verfügbar" erstellt, das durch die erforderliche Menge geteilt wird - auf diese Weise brauche ich es nicht die Hilfssäule hier. Und zuerst schien es, als würde es funktionieren, weil wir 2, 3 und 4 hatten und die Meldung, dass das Minimum 2 ist - ich habe diese Berechnung natürlich in Min geändert,und das schien gut zu sein.
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.
Nun, hey, ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Duell-Excel-Podcast von MrExcel und Excel macht Spaß.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Duel190.xlsx
