I dag et interessant Excel-problem om styklister. Du har mange råvarer. Hver vare kan samles i flere forskellige samlinger på øverste niveau. Baseret på råmaterialet ved hånden, har du nok til at udføre en ordre på en bestemt vare?
Se video
- Tim spørger: Hvor mange af hver vare er tilgængelige at sælge
- Komplicerende faktor: En vare består af flere kartoner
- Regningsmetode nr. 1: Tilføj en hjælpekolonne med INT (antal behov / til rådighed)
- Tilføj subtotaler for min. Hjælper ved hver ændring i produkt
- Skjul subtotaler til visning nr. 2
- Vælg alle data. Brug alt = "" +; for Vælg synlige celler
- Indsæt i et nyt sortiment
- Ctrl + H for at ændre mellemrum Min til intet
- Mike Metode # 2
- Kopier produktkolonnen til højre og brug Data, Fjern duplikater
- Brug MINIFS ud for den unikke produktliste
- Bemærk, at MINIFS kun er tilgængelig i Office 365
- Regningsmetode nr. 3: En almindelig pivottabel mislykkes, fordi Beregnede felter ikke fungerer i dette tilfælde.
- Vælg en celle i dine data, og tryk på Ctrl + T for at konvertere til en tabel.
- I stedet, mens du opretter drejetabellen, skal du vælge feltet Tilføj til datamodel
- Opret et nyt mål for tilgængelig til salg ved hjælp af INT
- Opret et nyt mål for Kit tilgængelig til salg med MINX
- Det drejebord fungerer!
- Mike Method # 4 Brug AGGREGATE-funktionen.
- Det ser ud til, at du vil bruge MIN-argumentet, men brug SMALL, fordi det håndterer arrays
- Brug
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE er en af fem funktioner, der kan acceptere et array som et argument uden Ctrl + Shift + Enter
- Regningsmetode nr. 5
- Konverter dataene til en tabel, og brug Power Query - aka Get & Transform
- I Power Query beregner du OH / Needed
- Brug funktionen Number.RoundDown til at konvertere til heltal
- Brug gruppering efter varenummer og min
- Luk og indlæs
- Bonus: Det er forfriskende!
Videoudskrift
MrExcel: Hej, velkommen tilbage, det er tid til endnu en Dueling Excel Podcast. Jeg er Bill Jelen fra, jeg får følgeskab af Mike Girvin fra Excel Is Fun. Dette er vores afsnit 190: Hvor mange sæt er der til rådighed til at sælge?
Okay, dagens spørgsmål sendt af Tim. Ser vores Dueling Excel-videoer, han arbejder for en forhandler og bad om at oprette et regneark for at vise vores salgsteam, hvad vi ejer, og hvad vi kan sælge. Det lyder simpelt, ikke? Men her er fangsten: Varen, de sælger, indeholder flere kartoner og er opgjort på kartonbasis. Her er et eksempel på, hvad han ser. Så her er denne vare, P12345, der har 3 forskellige ting, som de skal sende. Og i sættet kræves 4 af karton 1, 1 af karton 2 og 1 af karton 3. Og det er hvor mange de har på lager. Okay, så bare gør matematikken her, de har 2 komplette sæt karton 1, 4 komplette sæt karton 2 og 3 komplette sæt karton 3. Men det betyder, at hvad de kan sælge, er minimumet af disse 3 numre - de kan kun sælge 2. Og her har de 4 komplette sæt karton 4,4 i karton 5, 2 i karton 3, kun 1 i karton 7-- det er den begrænsende vare. Så i dette tilfælde kan de kun sælge en af disse. I orden. Nu, et spørgsmål til en senere dag, sagde jeg: "Nå, er der nogen chance for, at karton 3 bruges mere end et sted?" Og han siger, "Ja, men vi kommer til at bekymre os om det senere." I orden.
Så her skal jeg angribe dette. Jeg kan faktisk tænke på flere forskellige måder at angribe dette på, så dette kan være interessant - dette kan være en frem og tilbage type duel. Hvad jeg skal gøre er, at jeg vil have en hjælper-kolonne herude, og hjælper-kolonnen vil se på en vare-for-vare-basis af, hvor mange vi kan sælge. Så = 8 delt 4, sådan, og vi dobbeltklikker for at kopiere det ned. Men lad os sige, at vi havde brug for 4, og vi havde 6. Okay, så nu siger det 1.5. Du kan ikke sælge, ved du, en halv sofa, okay? Så det bliver hele tallet. Så hvad jeg skal gøre her, er at bruge = INT-- INT, heltal-- den ting, der fjerner decimalerne og efterlader os bare hele beløbet. I orden. Så har vi 8-- tilbage til det oprindelige nummer.
Og vi skal finde ud af, for hvert element her, hvad er det mindste antal i kolonne E? Sørg for, at dataene er sorteret efter produkt, gå til fanen Data, vælg subtotaler, ved hver ændring i produkt skal du bruge funktionen Min. Du ved, jeg underviser subtotaler hele tiden på mine Power Excel-seminarer, og jeg påpeger, at der er 11 funktioner her, men jeg har aldrig brugt andet end Sum og Count. Så selvom Subtotal måske ikke er den hurtigste måde at gøre dette på, vil jeg være i stand til at sige, at der faktisk var en gang, hvor jeg var i stand til at bruge noget andet end Sum og Count. OK, klik på OK. Og hvad vi får, er, hver gang gardinnummeret - produktnummeret - ændres, får vi se Min. Og det Min er det svar, vi ønsker. Så jeg faldt sammen til visning nummer 2, jeg vælger alle disse data og Alt +;for kun at vælge de synlige celler, Ctrl + C, og så kommer vi herned og indsætter - lad os bare indsætte til dette område - Ctrl + V. I orden. Slet de ekstra kolonner, og så skal vi slippe af med ordet Min. Og ikke kun ordet Min, men også plads Min. I orden. Så jeg skal bruge Ctrl + H og ændre gentagelsen af plads Min til intet, Udskift alt, klik på OK, klik på Luk, og der er vores tabel over, hvad vi har til rådighed til at sælge. Okay, Mike, jeg smider det over til dig.og der er vores tabel over, hvad vi har til rådighed til at sælge. Okay, Mike, jeg smider det over til dig.og der er vores tabel over, hvad vi har til rådighed til at sælge. Okay, Mike, jeg smider det over til dig.
Mike: Wow! MrExcel, jeg elsker det. Funktionen Min i subtotaler. Hvor sejt er det? Okay, jeg vil gå over til dette ark lige her, jeg vil gøre den samme hjælper-kolonne. = INT vi tager alt "Til hånden" divideret med "Nødvendigt antal", tætte parenteser. Ctrl + Enter, dobbeltklik og send det ned. Nu skal jeg bare finde Min tilgængelig for en given tilstand eller kriterier. Jeg vælger Produkt, Ctrl + Skift + Ned Arroe, Ctrl + C for at kopiere, så går jeg til højre pil, Ctrl + V, så kommer jeg op og siger Fjern duplikater. Der er det.
Jeg plejede at bruge avanceret filter, unikke poster kun hele tiden, men det ser ud til, at denne metode er hurtigere. Der er min unikke liste. Nu kommer jeg herover. Hvor mange? Og jeg vil bruge den nye funktion, MINIFS. Nu er MINIFS i Office 365; til Excel 2016 eller nyere, MINRANGE. Nå, jeg er nødt til at finde minimumsværdien i denne kolonne, Ctrl + Skift + Pil ned, F4, komma og kriterieområdet - det bliver hele dette produkt. Ctrl + Skift + Pil ned, F4, komma, venstre pil, og så går vi. Det får den mindste værdi fra hvor mange, baseret på betingelsen eller kriterierne, lukker parenteser, Ctrl + Enter, dobbeltklik og send den ned. I orden. Så der er MINIFS og Subtotal. Jeg vil smide det tilbage til dig.
MrExcel: Ja, Mike, meget flot. Fjern duplikater, få den unikke liste over produkter og derefter MINIFS-funktionen. Jeg spurgte ham, hvilken version af Excel han er på, sagde han Excel 2016. Jeg håber, det er Office 365-versionen af 2016, så han har adgang til det. Nå, hvad med et pivottabel? Okay, så jeg oprettede en pivottabel med produkt og kræver summen af krævede mængder og summen af hånden. Herefter, "Analyser", "Felter, emner og sæt", "Beregnet felt" og oprettet et nyt beregnet felt kaldet "Tilgængelig", som er Til rådighed divideret med påkrævet antal - på den måde har jeg ikke brug Hjælpesøjlen herovre. Og først virkede det som om det skulle fungere, fordi vi havde 2, 3 og 4, og rapporteringen om, at minimumet er 2 - Jeg ændrede selvfølgelig denne beregning til Min,og det syntes godt.
Men så, på denne ene, hvor vi har 2,4,4,1,2, rapporterer den 3. Og hvad der sker er, at den laver beregningen på denne række. Vi har 25 på hånden, divideret med 8, det er 3 og en brøkdel, og så rapporterer det 3, og så, nej. En almindelig pivottabelberegningsartikel fungerer ikke. Men konverter i stedet disse data til en tabel, og indsæt derefter Pivottabel, tilføj disse data til datamodellen, klik på OK. Og vi har et produkt ned ad venstre side og hvad det kræver. Jeg vil oprette to implicitte tiltag her med en krævet mængde og noget af On Hand, og så skal jeg oprette et nyt mål. Så PowerPivot, Mål, et nyt mål, og dette nye mål kaldes Tilgængelig til salg (Tilgængelig til salg), og denne formel vil være,hvor mange vi har til rådighed divideret med hvor mange der kræves for hver vare, og klik på OK. Okay, så 8 divideret med 4 er 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.
Nå, hej, jeg vil gerne takke dig for at komme forbi, vi ses næste gang til endnu en Dueling Excel Podcast fra MrExcel, og Excel er sjovt.
Download fil
Download eksempelfilen her: Duel190.xlsx
