Idag ett intressant Excel-problem om räkningar. Du har mycket råvaror. Varje artikel kan samlas i flera olika toppnivåenheter. Baserat på råvaran till hands, har du tillräckligt för att fullfölja en beställning för en viss artikel?
Kolla på video
- Tim frågar: Hur många av varje artikel är tillgängliga att sälja
- Komplicerande faktor: En artikel består av flera kartonger
- Bill Method # 1: Lägg till en hjälpkolumn med INT (antal behövs / till hands)
- Lägg till delsummor för Min of Helper vid varje ändring av produkten
- Dölj delsummorna till vyn nr 2
- Välj all data. Använd alt = "" +; för Välj synliga celler
- Klistra in i ett nytt sortiment
- Ctrl + H för att ändra Space Min till ingenting
- Mike Method # 2
- Kopiera produktkolumnen till höger och använd Data, Ta bort dubbletter
- Använd MINIFS bredvid den unika produktlistan
- Observera att MINIFS endast är tillgängligt i Office 365
- Fakturametod 3: en vanlig pivottabell misslyckas eftersom beräknade fält inte fungerar i det här fallet.
- Välj en cell i dina data och tryck på Ctrl + T för att konvertera till en tabell.
- Istället väljer du rutan för Lägg till datamodell när du skapar pivottabellen
- Skapa ett nytt mått för tillgängligt för sälj med INT
- Skapa ett nytt mått för Kit tillgängligt för säljning med MINX
- Det svängbordet fungerar!
- Mike Method # 4 Använd AGGREGATE-funktionen.
- Det verkar som om du vill använda MIN-argumentet, men använd SMALL eftersom det hanterar matriser
- Använda sig av
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE är en av fem funktioner som kan acceptera en array som ett argument utan Ctrl + Shift + Enter
- Bill Method # 5
- Konvertera data till en tabell och använd Power Query - aka Get & Transform
- I Power Query beräknar du OH / Needed
- Använd funktionen Number.RoundDown för att konvertera till heltal
- Använd gruppering efter artikelnummer och min
- Stäng och ladda
- Bonus: Det är uppfriskande!
Videoutskrift
MrExcel: Hej, välkommen tillbaka, det är dags för ytterligare en Duelling Excel Podcast. Jag är Bill Jelen från, jag får sällskap av Mike Girvin från Excel Is Fun. Detta är vårt avsnitt 190: Hur många kit finns att sälja?
Okej, dagens fråga skickad in av Tim. Tittar på våra Duelling Excel-videor, han arbetar för en återförsäljare och bad om att skapa ett kalkylark för att visa vårt säljteam vad vi äger och vad vi kan sälja. Låter enkelt, eller hur? Men här är fångsten: Föremålet de säljer innehåller flera kartonger och lagerförs per kartong. Här är ett exempel på vad han ser. Så här är den här artikeln, P12345, som har 3 olika saker som de måste skicka. Och i satsen krävs 4 av kartong 1, 1 av kartong 2 och 1 av kartong 3. Och så många har de i lager. Okej, så bara gör matte här, de har 2 kompletta uppsättningar kartong 1, 4 kompletta uppsättningar kartong 2 och 3 kompletta uppsättningar kartong 3. Men det betyder att vad de kan sälja är minsta av dessa 3 siffror - de kan bara sälja 2. Och här har de fyra kompletta uppsättningar kartong 4,4 i kartong 5, 2 i kartong 3, endast 1 i kartong 7-- det är det begränsande föremålet. Så i det här fallet kan de bara sälja en av dessa. OK. Nu, en fråga för en senare dag, sa jag, "Tja, finns det någon chans att kartong 3 används på mer än ett ställe?" Och han säger, "Ja, men vi kommer att oroa oss för det senare." OK.
Så här ska jag attackera detta. Jag kan faktiskt tänka på flera olika sätt att attackera detta, så det här kan vara intressant - det här kan vara en fram och tillbaka typ av duell. Vad jag ska göra är att jag vill ha en hjälpar-kolumn här ute, och hjälpar-kolumnen kommer att titta för varje artikel på hur många vi kan sälja. Så = 8 delad 4, så, och vi dubbelklickar för att kopiera den. Men, låt oss säga att vi behövde 4, och vi hade 6. Okej, så nu kommer det att sägas 1,5. Du kan inte sälja, du vet, en halv soffa, okej? Så det måste vara hela numret. Så vad jag ska göra här är att använda = INT-- INT, heltalet - den saken som tar bort decimalerna och lämnar oss bara hela beloppet. OK. Så då har vi 8-- tillbaka till det ursprungliga numret.
Och vi måste räkna ut, för varje artikel här, vad är det minsta antalet i kolumn E? Se till att data sorteras efter produkt, gå till fliken Data, välj Delsummor, använd Min-funktionen vid varje ändring i Produkt. Du vet, jag undervisar delsummor hela tiden vid min Power Excel-seminarier, och jag påpekar att det finns 11 funktioner här men jag har aldrig använt något annat än Sum and Count. Så även om Subtotal kanske inte är det snabbaste sättet att göra detta, vill jag kunna säga att det faktiskt var en gång där jag kunde använda något annat än Sum and Count. Okej, klicka på OK. Och vad vi ska få är att varje gång gardinnumret - produktnumret - ändras får vi se min. Och det Min är det svar vi vill ha. Så jag kollapsade ner till nummer 2-vyn, jag väljer all denna information och Alt +;för att bara välja de synliga cellerna, Ctrl + C, och sedan kommer vi ner hit och klistra in - låt oss bara klistra ut till det här området - Ctrl + V. OK. Radera de extra kolumnerna och sedan måste vi bli av med ordet Min. Och inte bara ordet Min, utan rymden Min. OK. Så jag ska använda Ctrl + H och ändra återkomst av utrymme Min till ingenting, Byt ut alla, klicka på OK, klicka på Stäng, och det finns vår tabell över vad vi har tillgängligt att sälja. Okej, Mike, jag slänger det till dig.och det finns vårt bord med vad vi har att sälja. Okej, Mike, jag slänger det till dig.och det finns vårt bord med vad vi har att sälja. Okej, Mike, jag slänger det till dig.
Mike: Wow! MrExcel, jag älskar det. Funktionen Min i delsummor. Hur coolt är inte det? Okej, jag ska gå över till det här bladet här, jag kommer att göra samma hjälpar-kolumn. = INT tar vi alla "Till hands" dividerat med "Nödvändigt antal", nära parenteser. Ctrl + Enter, dubbelklicka och skicka ner den. Nu behöver jag bara hitta Min tillgängliga för ett visst tillstånd eller kriterier. Jag ska välja Produkt, Ctrl + Skift + Nedåt Arroe, Ctrl + C för att kopiera, sedan går jag till högerpil, Ctrl + V, då ska jag komma upp och säga Ta bort dubbletter. Där är det.
Jag använde avancerat filter, unika poster bara hela tiden, men det verkar som om den här metoden är snabbare. Det är min unika lista. Nu ska jag komma hit. Hur många? Och jag ska använda den nya funktionen, MINIFS. Nu finns MINIFS i Office 365; för Excel 2016 eller senare, MINRANGE. Tja, jag måste hitta minimivärdet i den här kolumnen, Ctrl + Skift + Pil ner, F4, komma och kriterieområdet - det kommer att bli hela denna produkt. Ctrl + Skift + nedåtpil, F4, komma, vänsterpil, och där går vi. Det kommer att få minvärdet från hur många, baserat på villkoret eller kriterierna, stänger parenteser, Ctrl + Enter, dubbelklickar och skickar ner det. OK. Så det finns MINIFS och delsumma. Jag ska kasta tillbaka det till dig.
MrExcel: Ja, Mike, mycket trevlig. Ta bort dubbletter, få den unika produktlistan och sedan MINIFS-funktionen. Jag frågade honom vilken version av Excel han har, han sa Excel 2016. Jag hoppas att det är Office 365-versionen av 2016, så han har tillgång till det. Tja, vad sägs om ett pivottabell? Okej, så jag skapade en pivottabell med produkt och kräver summan av erforderliga kvantiteter och summan av till hands. Därifrån, "Analysera", "Fält, artiklar och uppsättningar", "Beräknat fält", och skapade ett nytt beräknat fält som heter "Tillgängligt", vilket är Till hands dividerat med erforderligt antal - på det sättet behöver jag inte hjälparpelaren här. Och först verkade det som om det skulle fungera för att vi hade 2, 3 och 4 och rapporterade att minimum är 2 - Jag ändrade naturligtvis denna beräkning till Min,och det verkade bra.
Men då, på den här, där vi har 2,4,4,1,2, rapporterar den 3. Och vad som händer är att det gör beräkningen på den här raden. Vi har 25 till hands, dividerat med 8, det är 3 och en bråkdel, och så rapporterar det 3, och så, nej. En vanlig beräkning av pivottabellen fungerar inte. Men istället konvertera dessa data till en tabell och sedan Infoga, pivottabell, lägg till dessa data i datamodellen, klicka på OK. Och vi kommer att ha, nere till vänster, Produkt och vad den kräver. Jag ska skapa två implicita åtgärder här med en nödvändig mängd och en del av Till hands, och sedan ska jag skapa en ny åtgärd. Så, PowerPivot, Mät, ett nytt mått, och det här nya måttet kommer att kallas Tillgängligt att sälja (availToSell) och den formeln kommer att bli,hur många vi har till hands dividerat med hur många som krävs för varje artikel och klicka på OK. Okej, så 8 dividerat med 4 är 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.
Tja, hej, jag vill tacka dig för att du stannade, vi ses nästa gång för en annan Duelling Excel Podcast från MrExcel och Excel är kul.
Nedladdning fil
Ladda ner exempelfilen här: Duel190.xlsx
