Koliko kompletov na voljo - nasveti za Excel

Danes zanimiva težava v Excelu glede računov. Imate veliko surovin. Vsak element je lahko sestavljen v več različnih sklopov najvišje ravni. Ali imate na voljo dovolj surovin za izpolnitev naročila za določen izdelek?

Oglejte si video

  • Tim vpraša: Koliko posameznih predmetov je na voljo za prodajo
  • Zapleten dejavnik: Artikel je sestavljen iz več kartonov
  • Metoda računa # 1: Dodajte pomožni stolpec z INT (Količina je potrebna / na roki)
  • Pri vsaki spremembi izdelka dodajte vmesne seštevke za pomočnika
  • Strni vmesne seštevke pogleda # 2
  • Izberite vse podatke. Uporabite alt = "" +; za Izberite vidne celice
  • Prilepite v nov obseg
  • Ctrl + H, če želite minuto presledka spremeniti v nič
  • Mikeova metoda # 2
  • Kopirajte stolpec Product na desno in uporabite Data, Remove Duplicates
  • Poleg edinstvenega seznama izdelkov uporabite MINIFS
  • Upoštevajte, da je MINIFS na voljo samo v storitvi Office 365
  • Metoda računa # 3: običajna vrtilna tabela ne uspe, ker izračunana polja v tem primeru ne bodo delovala.
  • V podatkih izberite eno celico in pritisnite Ctrl + T za pretvorbo v tabelo.
  • Namesto tega, ko ustvarjate vrtilno tabelo, izberite polje Dodaj v podatkovni model
  • Ustvarite nov ukrep za Na voljo za prodajo s pomočjo INT
  • Ustvarite nov ukrep za komplet, ki je na voljo za prodajo z MINX
  • Ta vrtilna tabela deluje!
  • Mike Method # 4 Uporabite AGREGATE funkcijo.
  • Zdi se, da bi radi uporabili argument MIN, vendar uporabite SMALL, ker obravnava polja
  • Uporaba =AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1)
  • AGGREGATE je ena od petih funkcij, ki lahko sprejme matriko kot argument brez Ctrl + Shift + Enter
  • Metoda računa # 5
  • Pretvorite podatke v tabelo in uporabite Power Query - aka Get & Transform
  • V Power Query izračunajte OH / potrebno
  • Za pretvorbo v celo število uporabite funkcijo Number.RoundDown
  • Uporabite razvrščanje po številki dela in najmanjši razpoložljivosti
  • Zapri in naloži
  • Bonus: Osvežujoč je!

Video zapis

MrExcel: Hej, dobrodošli nazaj, čas je za še en poddaj Duel Excel. Sem Bill Jelen iz, pridružil se mi bo Mike Girvin iz Excel Is Fun. To je naša epizoda 190: Koliko kompletov je na voljo za prodajo?

V redu, današnje vprašanje je poslal Tim. Ogleduje si naše videoposnetke Dueling Excel, dela za prodajalca in prosi, da ustvari preglednico, ki bo naši prodajni ekipi pokazala, kaj imamo v lasti in kaj lahko prodamo. Sliši se preprosto, kajne? Toda tu je ulov: izdelek, ki ga prodajajo, vsebuje več kartonov in je popisan na osnovi kartona. Tu je primer tega, kar vidi. Torej, tukaj ima ta izdelek, P12345, 3 različne stvari, ki jih morajo poslati. In v kompletu potrebujete 4 škatle 1, 1 škatle 2 in 1 škatle 3. In toliko jih imajo na zalogi. V redu, tako da tukaj samo računamo, imajo 2 popolna kompleta kartona 1, 4 kompleta kartona 2 in 3 kompleta kartona 3. Ampak to pomeni, da lahko od teh treh številk prodajo najmanj - lahko prodajo samo 2. In tukaj imajo 4 celotne komplete škatle 4,4 kartona 5, 2 kartona 3, samo 1 kartona 7 - to je omejitveni element. V tem primeru lahko prodajo le eno od teh. Vredu. Zdaj pa vprašanje za kasnejši dan sem rekel: "No, ali obstaja možnost, da se karton 3 uporablja na več mestih?" In reče: "Ja, ampak kasneje bomo to skrbeli." Vredu.

Takole bom napadel to. Pravzaprav si lahko omislim več različnih načinov, kako to napasti, zato je to lahko zanimivo - to je lahko dvoboj naprej in nazaj. Kar bom storil, je, da želim imeti stolpec Helper tukaj, stolpec Helper pa bo po posameznih postavkah pregledal, koliko jih lahko prodamo. Torej = 8, razdeljeno na 4, tako, in dvakrat kliknemo, da ga kopiramo. Toda recimo, da smo potrebovali 4 in smo jih imeli 6. V redu, zdaj bomo rekli 1,5. No, ne moreš prodati, veš, pol kavča, v redu? Torej, to mora biti celo število. Torej, kar bom naredil tukaj, bom uporabil = INT-- INT, celo število-- tisto stvar, ki bo odstranila decimalne številke in nam pustila samo celoten znesek. Vredu. Torej imamo 8 - nazaj na prvotno številko.

In za vsak element tukaj moramo ugotoviti, kakšno je najmanjše število v stolpcu E? Prepričajte se, da so podatki razvrščeni po izdelku, pojdite na zavihek Podatki, izberite Vmesne seštevke, pri vsaki spremembi izdelka uporabite funkcijo Min. Veste, ves čas predavam vmesne seštevke na svojih seminarjih Power Excel in poudarjam, da je tu 11 funkcij, vendar nikoli nisem uporabil ničesar drugega kot vsoto in štetje. Torej, čeprav vmesni seštevek morda ni najhitrejši način za to, bi rad rekel, da sem nekoč dejansko lahko uporabil nekaj drugega kot vsota in štetje. V redu, kliknite V redu. In kar bomo dobili, je, da se vsakič, ko se spremeni številka zavese - številka izdelka, vidimo Min. In da je Min odgovor, ki ga želimo. Tako sem strmoglavil do pogleda številka 2, izbral bom vse te podatke in Alt +;da izberemo samo vidne celice, Ctrl + C, nato pa pridemo sem in prilepimo - le prilepiva na to območje - Ctrl + V. Vredu. Izbrišite odvečne stolpce in potem se moramo znebiti besede Min. Pa ne samo beseda Min, ampak presledek Min. Vredu. Torej bom uporabil Ctrl + H in spremenil ponovitev prostora Min na nič, Zamenjaj vse, kliknite V redu, kliknite Zapri in tam je naša tabela, kaj imamo na voljo za prodajo. V redu, Mike, vrgel ti ga bom.in tam je naša tabela, kaj imamo na voljo za prodajo. V redu, Mike, vrgel ti ga bom.in tam je naša tabela, kaj imamo na voljo za prodajo. V redu, Mike, vrgel ti ga bom.

Mike: Vau! MrExcel, všeč mi je. Funkcija Min v vmesnih seštevkih. Kako kul je to? V redu, grem na ta list tukaj, naredil bom isti stolpec Helper. = INT vzeli bomo vse "Na roko", deljeno s "Zahtevana količina", tesne oklepaje. Ctrl + Enter, dvokliknite in ga pošljite navzdol. Zdaj moram samo najti najnižjo vrednost, ki je na voljo za določen pogoj ali merila. Izbral bom izdelek, Ctrl + Shift + puščico navzdol, Ctrl + C za kopiranje, nato bom puščico desno, Ctrl + V, nato bom prišel gor in rekel Odstrani dvojnike. Tukaj je.

Včasih sem ves čas uporabljal napredni filter, edinstvene zapise, vendar se zdi, da je ta metoda hitrejša. Tu je moj edinstveni seznam. Zdaj grem sem. Koliko? In uporabil bom novo funkcijo MINIFS. Zdaj je MINIFS v storitvi Office 365; za Excel 2016 ali novejši MINRANGE. No, v tem stolpcu moram najti najmanjšo vrednost, Ctrl + Shift + puščica dol, F4, vejica in obseg meril - to bo celoten izdelek. Ctrl + Shift + puščica dol, F4, vejica, puščica levo in gremo. Tako bomo dobili najmanjšo vrednost od tega, koliko na podlagi pogoja ali meril zapre oklepaje, Ctrl + Enter, dvoklikne in pošlje navzdol. Vredu. Torej obstajajo MINIFI in vmesni seštevek. Vrnil vam ga bom nazaj.

MrExcel: Ja, Mike, zelo lepo. Odstranite dvojnike, dobite edinstven seznam izdelkov in nato funkcijo MINIFS. Vprašal sem ga, na kateri različici Excela je, rekel je Excel 2016. Upam, da gre za različico Office 365 iz leta 2016, zato ima dostop do tega. No, kaj pa vrtilna miza? V redu, zato sem ustvaril vrtilno tabelo z izdelkom in zahteva, vsoto zahtevanih količin in vsoto na roki. Nato od tukaj, "Analiziraj", "Polja, predmeti in kompleti", "Izračunano polje" in ustvari novo izračunano polje z imenom "Na voljo", ki je na roki deljeno s potrebno količino - na ta način stolpec pomočnikov tukaj. In sprva se je zdelo, da bo šlo, ker smo imeli 2, 3 in 4 in poročanje, da je najmanj 2 - ta izračun sem seveda spremenil v Min,in to se je zdelo dobro.

Potem pa na tej, kjer imamo 2,4,4,1,2, poroča 3. In kar se dogaja je, da v tej vrstici izvaja izračun. Pri roki imamo 25, deljeno z 8, to je 3 in ulomek, zato poročamo o 3 in tako, ne. Navaden element izračuna vrtilne tabele ne bo deloval. Namesto tega te podatke pretvorite v tabelo in nato Vstavi, vrtilna tabela, Dodaj te podatke v podatkovni model in kliknite V redu. Na levi strani bomo imeli izdelek in tisto, kar zahteva. Tukaj bom ustvaril dva implicitna merila z zahtevano količino in nekaj na roki, nato pa bom ustvaril nov ukrep. Torej, PowerPivot, Measure, New Measure in ta novi ukrep se bo imenoval Na voljo za prodajo (AvailToSell) in ta formula bo,koliko jih imamo pri roki, deljeno s številom, ki jih potrebujemo za posamezen izdelek, in kliknite V redu. V redu, torej 8, deljeno s 4, je 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.

No, hej, rad bi se vam zahvalil, da ste se ustavili, se vidimo naslednjič za še en dvoboj Excel Podcast od MrExcela in Excel je zabaven.

Prenesite datoteko

Prenesite vzorčno datoteko tukaj: Duel190.xlsx

Zanimive Članki...