Šodien interesanta Excel problēma par materiālu rēķiniem. Jums ir daudz izejvielu. Katru priekšmetu var salikt vairākos dažādos augstākā līmeņa mezglos. Vai jums ir pietiekami daudz, lai izpildītu konkrētas preces pasūtījumu, pamatojoties uz izejvielām?
Skatīties video
- Tims jautā: Cik daudz no katra priekšmeta ir pieejams pārdošanai
- Komplicējošais faktors: prece sastāv no vairākām kastītēm
- Rēķina metode Nr. 1: pievienojiet palīga kolonnu ar INT (nepieciešamais daudzums / pa rokai)
- Katrā Produkta izmaiņā pievienojiet starpnieka kopsummu par Palīdzības minimumu
- Sakļaut starpsummas skatā Nr. 2
- Atlasiet visus datus. Izmantojiet alt = "" +; priekš Atlasiet redzamās šūnas
- Ielīmēt jaunā diapazonā
- Ctrl + H, lai Space Min mainītu uz neko
- Maika metode # 2
- Nokopējiet kolonnu Produkts pa labi un izmantojiet Dati, Noņemt dublikātus
- Blakus unikālajam produktu sarakstam izmantojiet MINIFS
- Ņemiet vērā, ka MINIFS ir pieejams tikai Office 365
- Rēķina metode Nr. 3: parastā pagrieziena tabula neizdodas, jo aprēķinātie lauki šajā gadījumā nedarbosies.
- Atlasiet vienu šūnu savos datos un nospiediet Ctrl + T, lai pārveidotu par tabulu.
- Tā vietā, veidojot rakurstabulu, atzīmējiet izvēles rūtiņu Pievienot datu modelim
- Izveidojiet jaunu iespēju Pārdot, izmantojot INT
- Izmantojot MINX, izveidojiet jaunu komplektu, kas pieejams pārdošanai
- Tas pagrieziena galds darbojas!
- Maika metode Nr. 4 Izmantojiet funkciju AGGREGATE.
- Šķiet, ka jūs vēlaties izmantot MIN argumentu, bet izmantojiet SMALL, jo tas apstrādā masīvus
- Izmantot
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE ir viena no piecām funkcijām, kas var pieņemt masīvu kā argumentu bez Ctrl + Shift + Enter
- Bila metode # 5
- Konvertējiet datus tabulā un izmantojiet Power Query - aka Saņemt un pārveidot
- Programmā Power Query aprēķiniet OH / Needed
- Izmantojiet funkciju Number.RoundDown, lai pārveidotu par veselu skaitli
- Izmantojiet grupēšanu pēc detaļu numura un minimālās pieejamības
- Aizvērt un ielādēt
- Bonuss: Tas ir atsvaidzināms!
Video atšifrējums
MrExcel: Sveiki, sveicināti, ir pienācis laiks kārtējam Dueling Excel Podcast. Es esmu Bils Jelen no, man pievienosies Maiks Girvins no Excel Is Fun. Šī ir mūsu 190. sērija: Cik daudz komplektus ir iespējams pārdot?
Labi, šodienas jautājumu iesūtīja Tims. Skatās mūsu Dueling Excel videoklipus, viņš strādā pie mazumtirgotāja un lūdza izveidot izklājlapu, lai parādītu mūsu pārdošanas komandai to, kas mums pieder un ko mēs varam pārdot. Izklausās vienkārši, vai ne? Bet šeit ir nozveja: precē, ko viņi pārdod, ir vairākas kastes, un tās ir uzskaitītas katrā kartonā. Šeit ir piemērs tam, ko viņš redz. Tātad, šim priekšmetam P12345 ir 3 dažādas lietas, kas viņiem jānosūta. Komplektā ir nepieciešamas 4 1. kartona kastes, 1 2. kartona kastes un 1 3. kartona kastes. Un tas ir, cik daudz viņiem ir noliktavā. Labi, tāpēc, vienkārši veicot matemātiku šeit, viņiem ir 2 pilni 1. kartona komplekti, 4 pilni 2. kartona komplekti un 3 pilni 3. kartona komplekti. Bet tas nozīmē, ka tas, ko viņi var pārdot, ir minimālais no šiem 3 skaitļiem - viņi var pārdot tikai 2. Un šeit viņiem ir 4 komplekti 4. kartona komplektu,4 no 5, 2 no 3, tikai 1 no 7 - tas ir ierobežojošais elements. Tātad šajā gadījumā viņi var pārdot tikai vienu no šiem. Labi. Tagad, jautājot vēlākai dienai, es teicu: "Nu, vai ir kāda iespēja, ka 3. kartona kārba tiek izmantota vairāk nekā vienā vietā?" Un viņš saka: "Jā, bet mēs par to vēlāk uztraucamies." Labi.
Tātad, lūk, kā es tam uzbrukšu. Es faktiski varu iedomāties vairākus dažādus veidus, kā tam uzbrukt, tāpēc tas varētu būt interesanti - tas varētu būt turp un atpakaļ duelis. Ko es darīšu, es vēlos, lai šeit būtu Palīdzības sleja, un Palīga slejā tiks aplūkots katrs produkts atsevišķi, cik mēs varam pārdot. Tātad = 8 dalīts 4, tāpat kā mēs, un mēs divreiz noklikšķināsim, lai to nokopētu. Pieņemsim, ka mums vajadzēja 4, un mums bija 6. Labi, tāpēc tagad būs 1,5. Nu, jūs nevarat pārdot, jūs zināt, puse dīvāna, labi? Tātad tam būs jābūt veselam skaitlim. Tātad, ko es šeit darīšu, ir izmantot = INT - INT, veselu skaitli - šo lietu, kas noņems aiz komata un atstās mums tikai visu summu. Labi. Tad mums ir 8 - atpakaļ uz sākotnējo numuru.
Un mums ir jānoskaidro, kas katram kolonnam ir mazākais skaitlis E slejā? Pārliecinieties, ka dati ir sakārtoti pēc Produkta, dodieties uz cilni Dati, izvēlieties Starpsummas, pie katras Produkta izmaiņas izmantojiet funkciju Min. Ziniet, es savos Power Excel semināros visu laiku mācu Starpsummas, un es norādīju, ka šeit ir 11 funkcijas, bet es nekad neesmu lietojis neko citu kā Sum un Count. Tā kā starpsumma, iespējams, nav ātrākais veids, kā to izdarīt, es vēlos pateikt, ka patiesībā bija viena reize, kad es varēju izmantot kaut ko citu, nevis Sum un Count. Labi, noklikšķiniet uz Labi. Un tas, ko mēs iegūsim, ir tas, ka katru reizi, kad mainās priekškara numurs - produkta numurs, mēs redzam Min. Un tas Mins ir atbilde, ko mēs vēlamies. Tāpēc es sabruku skatā ar numuru 2, es atlasīšu visus šos datus un Alt +;lai atlasītu tikai redzamās šūnas, Ctrl + C, un tad mēs nāksim šeit un ielīmēsim - vienkārši ielīmēsim šo apgabalu - Ctrl + V. Labi. Izdzēsiet papildu kolonnas, un tad mums ir jāatbrīvojas no vārda Min. Un ne tikai vārdu Min, bet arī kosmosa Min. Labi. Tāpēc es izmantošu Ctrl + H un mainīšu vietas Min atkārtošanos uz neko, Aizstāt visu, noklikšķiniet uz Labi, noklikšķiniet uz Aizvērt, un tur ir mūsu tabula par to, kas mums ir pieejams pārdošanai. Labi, Maik, es to iemetu tev.un tur ir mūsu tabula par to, kas mums ir pieejams pārdošanai. Labi, Maik, es to iemetu tev.un tur ir mūsu tabula par to, kas mums ir pieejams pārdošanai. Labi, Maik, es to iemetu tev.
Maiks: Oho! MrExcel, man tas patīk. Min funkcija starpsummā. Cik tas ir forši? Labi, es šeit pārietu uz šo lapu, es darīšu to pašu Palīga kolonnu. = INT, mēs ņemsim visu “uz rokas” dalītu ar “nepieciešamo daudzumu”, aizverot iekavas. Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju. Man vienkārši jāatrod konkrētajam nosacījumam vai kritērijiem pieejamais minimums. Es izvēlos Produkts, Ctrl + Shift + Down Arroe, Ctrl + C, lai kopētu, pēc tam es dodos uz Labo bultiņu, Ctrl + V, pēc tam es nākšu klajā un teikšu: Noņemt dublikātus. Tur tas ir.
Es visu laiku izmantoju Advanced Filter, Unique Records Only, taču šķiet, ka šī metode ir ātrāka. Tur ir mans unikālais saraksts. Tagad es nākšu šeit. Cik daudz? Es izmantošu jauno funkciju MINIFS. Tagad MINIFS atrodas Office 365; Excel 2016 vai jaunākai versijai MINRANGE. Man šajā kolonnā jāatrod minimālā vērtība: Ctrl + Shift + bultiņa uz leju, F4, komats un kritēriju diapazons - tas būs viss šis produkts. Ctrl + Shift + bulta uz leju, F4, komats, bulta pa kreisi, un tur mēs ejam. Tas iegūs minimālo vērtību no tā, cik daudz, pamatojoties uz nosacījumu vai kritērijiem, aizver iekavas, Ctrl + Enter, veiciet dubultklikšķi un nosūtiet to uz leju. Labi. Tātad tur ir MINIFS un starpsumma. Es to atmetīšu jums atpakaļ.
MrExcel: Jā, Mike, ļoti jauks. Noņemiet dublikātus, iegūstiet unikālo produktu sarakstu un pēc tam funkciju MINIFS. Es viņam jautāju, kādā Excel versijā viņš ir, viņš teica, ka Excel 2016. Es ceru, ka tā ir 2016. gada Office 365 versija, tāpēc viņam ir piekļuve tai. Nu, kā būtu ar rakurstabulu? Labi, tāpēc es izveidoju rakurstabulu ar produktu un vajadzību, nepieciešamo daudzumu summu un rokas naudas summu. Tad no šejienes: "Analizēt", "Lauki, vienumi un kopas", "Aprēķinātais lauks" un izveidoja jaunu aprēķinātu lauku ar nosaukumu "Pieejams", kas tiek dalīts ar nepieciešamo daudzumu - tādā veidā man tas nav vajadzīgs. Palīgkolonna šeit. Sākumā šķita, ka tas darbosies, jo mums bija 2, 3 un 4 un ziņojums, ka minimums ir 2 - es, protams, mainīju šo aprēķinu uz Min,un tas likās labi.
Bet tad uz šī, kur mums ir 2,4,4,1,2, tas ziņo par 3. Un kas notiek, tas veic šīs rindas aprēķinu. Mums ir 25 uz rokas, dalīti ar 8, tas ir 3 un daļa, un tāpēc tas ziņo par 3, un tā, nē. Parastais rakurstabulas aprēķināšanas elements nedarbosies. Bet tā vietā pārveidojiet šos datus tabulā un pēc tam ievietojiet, PivotTable, Pievienojiet šos datus datu modelim, noklikšķiniet uz Labi. Mums kreisajā pusē būs Produkts un tam nepieciešamais. Es šeit izveidošu divus netiešus pasākumus ar nepieciešamo daudzumu un dažus no “Hand”, un tad es izveidošu jaunu mēru. Tātad, PowerPivot, Measure, New Measure un šis jaunais pasākums tiks saukts Pieejams pārdošanai (AvailableToSell), un šī formula būs,cik daudz mums ir pa rokai, dalot ar to, cik daudz katram priekšmetam nepieciešams, un noklikšķiniet uz Labi. Labi, tāpēc 8 dalīts ar 4 ir 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.
Nu, hei, es gribu pateikties, ka apstājāties, mēs tiksimies nākamreiz uz citu Dueling Excel Podcast no MrExcel un Excel ir jautri.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Duel190.xlsx
