Dnes je zajímavý problém aplikace Excel o kusovnících. Máte spoustu surovin. Každá položka může být sestavena do několika různých sestav nejvyšší úrovně. Máte na základě dostupné suroviny dostatek na to, abyste splnili objednávku na určitou položku?
Sledovat video
- Tim se ptá: Kolik z každé položky je k dispozici k prodeji
- Komplikující faktor: Položka se skládá z více kartonů
- Fakturační metoda č. 1: Přidání pomocného sloupce s INT (potřebné množství / po ruce)
- Přidejte mezisoučty pro min. Pomocníka při každé změně produktu
- Sbalte mezisoučty do zobrazení # 2
- Vyberte všechna data. Použijte alt = "" +; pro Vyberte viditelné buňky
- Vložit do nové řady
- Ctrl + H změní mezerník na nic
- Mike Method # 2
- Zkopírujte sloupec Produkt doprava a použijte Data, Odebrat duplikáty
- Vedle jedinečného seznamu produktů použijte MINIFY
- Upozorňujeme, že MINIFS je k dispozici pouze v Office 365
- Metoda vyúčtování č. 3: běžná kontingenční tabulka selže, protože vypočítané pole v tomto případě nebude fungovat.
- Vyberte jednu buňku ve svých datech a stiskněte Ctrl + T pro převod do tabulky.
- Místo toho při vytváření kontingenční tabulky zaškrtněte políčko Přidat do datového modelu
- Vytvořte nové měřítko pro Dostupné k prodeji pomocí INT
- Vytvořte nové měřítko pro Kit Available to Sell pomocí MINX
- Tato kontingenční tabulka funguje!
- Mike Metoda # 4 Použijte funkci AGGREGATE.
- Zdá se, že byste chtěli použít argument MIN, ale použijte SMALL, protože zpracovává pole
- Použití
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE je jednou z pěti funkcí, které mohou přijmout pole jako argument bez Ctrl + Shift + Enter
- Bill metoda # 5
- Převeďte data na tabulku a použijte Power Query - aka Získat a transformovat
- V Power Query vypočítejte OH / Needed
- Použijte funkci Number.RoundDown k převodu na celé číslo
- Použijte seskupení podle čísla dílu a minimálního využití
- Zavřít a načíst
- Bonus: Je obnovitelný!
Přepis videa
MrExcel: Ahoj, vítej zpět, je čas na další podcast Dueling Excel. Jmenuji se Bill Jelen a přidá se ke mně Mike Girvin z Excel Is Fun. Toto je naše epizoda 190: Kolik sad je k dispozici k prodeji?
Dobře, dnešní otázku zaslal Tim. Sleduje naše videa Dueling Excel, pracuje pro maloobchodníka a požádal o vytvoření tabulky, která našemu prodejnímu týmu ukáže, co vlastníme a co můžeme prodat. Zní to jednoduše, že? Ale tady je háček: Položka, kterou prodávají, obsahuje více kartonů a jsou inventarizovány na základě kartonu. Zde je příklad toho, co vidí. Tady je tato položka P12345, která má 3 různé věci, které musí odeslat. A v sadě vyžaduje 4 z kartonu 1, 1 z kartonu 2 a 1 z kartonu 3. A tolik mají na skladě. Dobře, takže když tu matematiku uděláme, mají 2 kompletní sady kartonu 1, 4 kompletní sady kartonu 2 a 3 kompletní sady kartonu 3. Ale to znamená, že to, co mohou prodat, je minimum z těchto 3 čísel - oni mohou prodávat pouze 2. A tady mají 4 kompletní sady kartonu 4,4 z kartonu 5, 2 z kartonu 3, pouze 1 z kartonu 7 - to je omezující položka. V tomto případě tedy mohou prodat pouze jednu z nich. V pořádku. Nyní, otázka na pozdější den, jsem řekl: „No, je nějaká šance, že se karton 3 použije na více než jednom místě?“ A on říká: „Ano, ale budeme si s tím dělat starosti později.“ V pořádku.
Tak na tohle zaútočím. Vlastně mě napadá několik různých způsobů, jak na to zaútočit, takže by to mohlo být zajímavé - mohlo by to být duel tam a zpět. To, co udělám, je, že chci mít sloupec Pomocník tady, A sloupec Pomocník se bude dívat po jednotlivých položkách na to, kolik jich můžeme prodat. Takže = 8 děleno 4, takhle, a my to dvakrát zkopírujeme dolů. Ale řekněme, že jsme potřebovali 4 a měli jsme 6. Dobře, takže teď to bude 1,5. No nemůžete prodat, víš, půl gauče, dobře? Takže to bude muset být celé číslo. Takže to, co zde udělám, je použít = INT-- INT, celé číslo-- ta věc, která sundá desetinná místa a ponechá nám jen celou částku. V pořádku. Takže máme 8-- zpět na původní číslo.
A musíme zjistit, jaké je u každé položky nejmenší číslo ve sloupci E? Ujistěte se, že jsou data tříděna podle Produktu, přejděte na kartu Data, vyberte Mezisoučty, při každé změně v Produktu použijte funkci Min. Víte, já na svých seminářích Power Excel neustále učím mezisoučty a poukazuji na to, že je tu 11 funkcí, ale nikdy jsem nepoužíval nic jiného než Sum a Count. Zatímco Mezisoučet nemusí být nejrychlejší způsob, jak toho dosáhnout, chci mít možnost říci, že ve skutečnosti bylo jednou možné, že jsem mohl použít něco jiného než Sum and Count. Dobře, klikněte na OK. A to, co dostaneme, je pokaždé, když se změní číslo opony - číslo produktu -, uvidíme min. A že Min je odpověď, kterou chceme. Takže jsem se zhroutil na pohled číslo 2, vyberu všechna tato data a Alt +;abychom vybrali pouze viditelné buňky, Ctrl + C, a pak přijdeme sem a vložíme - vložíme je do této oblasti - Ctrl + V V pořádku. Smažte další sloupce a pak se musíme zbavit slova Min. A nejen slovo Min, ale i prostor Min. V pořádku. Takže použiji Ctrl + H a změním opakování mezery Min na nic, Nahradit vše, kliknout na OK, kliknout na Zavřít a je tu naše tabulka toho, co máme k dispozici k prodeji. Dobře, Miku, hodím ti to.a je tu naše tabulka toho, co máme k dispozici k prodeji. Dobře, Miku, hodím ti to.a je tu naše tabulka toho, co máme k dispozici k prodeji. Dobře, Miku, hodím ti to.
Mike: Páni! Pane Excel, miluji to. Funkce Min v mezisoučtech. Jak je to skvělé? Dobře, přejdu k tomuto listu právě tady, udělám stejný sloupec Pomocník. = INT vezmeme vše „Po ruce“ dělené „Požadovaným množstvím“, uzavřené závorky. Ctrl + Enter, poklepejte a odešlete jej. Teď už jen potřebuji najít Min k dispozici pro danou podmínku nebo kritéria. Vyberu Produkt, Ctrl + Shift + Dolů Arroe, Ctrl + C pro kopírování, pak jdu do Šipky doprava, Ctrl + V, pak přijdu a řeknu Odstranit duplikáty. Je to tady.
Používal jsem po celou dobu Advanced Filter, Unique Records Only, ale zdá se, že tato metoda je rychlejší. Je tu můj jedinečný seznam. Teď půjdu sem. Kolik? A budu používat novou funkci MINIFS. Nyní je MINIFS v Office 365; pro Excel 2016 nebo novější MINRANGE. Musím v tomto sloupci najít minimální hodnotu, Ctrl + Shift + šipka dolů, F4, čárka a rozsah kritérií - to bude celý tento produkt. Ctrl + Shift + šipka dolů, F4, čárka, šipka doleva a je to. To získá minimální hodnotu z toho, kolik, na základě podmínky nebo kritérií, zavřete závorky, Ctrl + Enter, poklepejte a odešlete ji. V pořádku. Takže existují MINIFY a Mezisoučet. Hodím ti to zpět.
MrExcel: Ano, Mike, velmi milý. Odeberte duplikáty, získejte jedinečný seznam produktů a poté funkci MINIFS. Zeptal jsem se ho, na jaké verzi aplikace Excel je, řekl Excel 2016. Doufám, že je to verze Office 365 z roku 2016, takže k ní má přístup. A co kontingenční tabulka? Dobře, tak jsem vytvořil kontingenční tabulku s produktem a vyžaduje, součet požadovaných množství a součet po ruce. Odtud potom „Analyzovat“, „Pole, položky a sady“, „Vypočítané pole“ a vytvořit nové vypočítané pole s názvem „Dostupné“, které je po ruce vyděleno požadovaným množstvím - tak nepotřebuji pomocný sloup tady. A zpočátku se zdálo, že to bude fungovat, protože jsme měli 2, 3 a 4 a hlášení, že minimum je 2 - Změnil jsem tento výpočet, samozřejmě, na Min,a to se zdálo dobré.
Ale pak na tomto, kde máme 2,4,4,1,2, hlásí 3. A co se děje, dělá výpočet na tomto řádku. Máme 25 po ruce, děleno 8, to je 3 a zlomek, a tak hlásí 3, a tak, ne. Běžná položka výpočtu kontingenční tabulky nebude fungovat. Ale místo toho převeďte tato data na tabulku a poté Vložit, kontingenční tabulka, Přidat tato data do datového modelu, klikněte na OK. A budeme mít dole na levé straně Produkt a co to vyžaduje. Chystám se zde vytvořit dvě implicitní míry s požadovaným množstvím a některými položkami po ruce a pak vytvořím novou míru. PowerPivot, Measure, a New Measure, a toto nové měřítko se bude jmenovat Available to Sell (AvailToSell) a tento vzorec bude,kolik jich máme po ruce děleno počtem požadovaných pro každou položku a klikněte na OK. Dobře, takže 8 děleno 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, chci vám poděkovat, že jste se zastavili, uvidíme se příště pro další podcast Dueling Excel od MrExcel a Excel je zábava.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Duel190.xlsx
