Astăzi, o problemă Excel interesantă despre facturile de material. Aveți o mulțime de materii prime. Fiecare articol poate fi asamblat în mai multe ansambluri diferite de nivel superior. Pe baza materiei prime la dispoziție, aveți suficient pentru a îndeplini o comandă pentru un anumit articol?
Urmăriți videoclipul
- Tim întreabă: Câte din fiecare articol sunt disponibile pentru vânzare
- Factor complicat: un articol este format din mai multe cutii
- Metoda facturii nr. 1: adăugați o coloană de asistență cu INT (Cantitate necesară / la îndemână)
- Adăugați subtotale pentru Min de ajutor la fiecare modificare a produsului
- Reduceți subtotalurile la vizualizarea # 2
- Selectați toate datele. Folosiți alt = "" +; pentru Selectați celule vizibile
- Inserați într-o gamă nouă
- Ctrl + H pentru a schimba Spațiul Min în nimic
- Metoda Mike # 2
- Copiați coloana Produs în dreapta și utilizați Date, Eliminare duplicate
- Lângă lista unică de produse, utilizați MINIFS
- Rețineți că MINIFS este disponibil numai în Office 365
- Metoda facturii nr. 3: un tabel pivot obișnuit eșuează, deoarece câmpurile calculate nu vor funcționa în acest caz.
- Selectați o celulă din datele dvs. și apăsați Ctrl + T pentru a converti într-un tabel.
- În schimb, pe măsură ce creați tabelul pivot, alegeți caseta pentru Adăugați la modelul de date
- Creați o nouă măsură pentru Disponibil pentru a vinde folosind INT
- Creați o nouă măsură pentru Kit disponibil pentru vânzare folosind MINX
- Acel tabel pivot funcționează!
- Mike Method # 4 Utilizați funcția AGGREGATE.
- Se pare că ai vrea să folosești argumentul MIN, dar folosește SMALL deoarece gestionează matricele
- Utilizare
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE este una dintre cele cinci funcții care pot accepta o matrice ca argument fără Ctrl + Shift + Enter
- Metoda facturii nr. 5
- Convertiți datele într-un tabel și utilizați Power Query - aka Get & Transform
- În Power Query, calculați OH / Needed
- Utilizați funcția Number.RoundDown pentru a converti în număr întreg
- Utilizați gruparea după numărul piesei și disponibilitatea minimă
- Închideți și încărcați
- Bonus: Se reîmprospătează!
Transcriere video
MrExcel: Hei, bine ați revenit, este timpul pentru un alt Dueling Excel Podcast. Sunt Bill Jelen din, mi se va alătura Mike Girvin din Excel Is Fun. Acesta este episodul nostru 190: Câte kituri sunt disponibile pentru a vinde?
Bine, întrebarea de astăzi trimisă de Tim. Urmărește videoclipurile noastre Dueling Excel, lucrează pentru un retailer și a cerut să creeze o foaie de calcul pentru a arăta echipei noastre de vânzări ce deținem și ce putem vinde. Sună simplu, nu? Dar iată captura: articolul pe care îl vând conține mai multe cutii și sunt inventariate pe bază de cutie. Iată un exemplu despre ceea ce vede. Deci, iată acest articol, P12345, are 3 lucruri diferite pe care trebuie să le livreze. Și în kit, necesită 4 din cutia 1, 1 din cutia 2 și 1 din cutia 3. Și iată câte au în stoc. Bine, așa că fac doar calculele aici, au 2 seturi complete de cutie 1, 4 seturi complete de cutie 2 și 3 seturi complete de cutie 3. Dar asta înseamnă că ceea ce pot vinde este minimul celor 3 numere - ei pot vinde doar 2. Și aici, ei au 4 seturi complete de cutie 4,4 din cutia 5, 2 din cutia 3, doar 1 din cutia 7 - acesta este elementul limitativ. Deci, în acest caz, ei pot vinde doar unul dintre acestea. Bine. Acum, o întrebare pentru o zi ulterioară, am spus, "Ei bine, există vreo șansă ca Carton 3 să fie utilizat în mai multe locuri?" Și el spune: „Da, dar ne vom îngrijora mai târziu”. Bine.
Deci, iată cum voi ataca acest lucru. De fapt, mă pot gândi la mai multe moduri diferite de a ataca acest lucru, deci acest lucru ar putea fi interesant - acesta ar putea fi un tip de duel înainte și înapoi. Ceea ce voi face este că vreau să am o coloană Helper aici, iar coloana Helper va analiza în funcție de câte articole putem vinde. Deci = 8 împărțit 4, așa, și vom face dublu clic pentru al copia. Dar, să spunem că aveam nevoie de 4 și că aveam 6. Bine, așa că acum va spune 1.5. Ei bine, nu poți vinde, știi, o jumătate de canapea, bine? Deci, va trebui să fie numărul întreg. Deci, ceea ce voi face aici, este să folosesc = INT-- INT, întregul - acel lucru care va scoate zecimalele și ne va lăsa doar întreaga sumă. Bine. Deci avem 8 - înapoi la numărul original.
Și trebuie să ne dăm seama, pentru fiecare articol de aici, care este cel mai mic număr din coloana E? Asigurați-vă că datele sunt sortate după produs, accesați fila Date, alegeți Subtotale, la fiecare modificare a produsului, utilizați funcția Min. Știi, eu predau subtotaluri tot timpul la seminariile mele Power Excel și subliniez că sunt 11 funcții aici, dar nu am folosit niciodată altceva decât Sum and Count. Deci, deși Subtotalul nu este cel mai rapid mod de a face acest lucru, vreau să pot spune că a existat de fapt o singură dată când am putut folosi altceva decât Sum and Count. Bine, faceți clic pe OK. Și ceea ce vom obține, este de fiecare dată când numărul perdelei - numărul produsului - se schimbă, vom vedea Min. Și acel Min este răspunsul pe care îl dorim. Așa că m-am prăbușit până la vizualizarea numărul 2, voi selecta toate aceste date și Alt +;pentru a selecta doar celulele vizibile, Ctrl + C, apoi vom coborî aici și vom lipi - să lipim doar în această zonă - Ctrl + V. Bine. Ștergeți coloanele suplimentare și apoi trebuie să scăpăm de cuvântul Min. Și nu doar cuvântul Min, ci spațiul Min. Bine. Deci, voi folosi Ctrl + H și voi schimba recurența spațiului Min în nimic, Înlocuiți toate, faceți clic pe OK, faceți clic pe Închidere și aici este tabelul nostru cu ceea ce avem la dispoziție pentru a vinde. Bine, Mike, ți-l voi arunca.și aici este tabelul nostru cu ceea ce avem la dispoziție pentru a vinde. Bine, Mike, ți-l arunc.și aici este tabelul nostru cu ceea ce avem la dispoziție pentru a vinde. Bine, Mike, ți-l arunc.
Mike: Uau! MrExcel, îmi place. Funcția Min din subtotale. Cat de tare e asta? Bine, voi trece la această foaie chiar aici, voi face aceeași coloană Helper. = INT vom lua tot „La îndemână” împărțit la „Cantitate necesară”, închideți parantezele. Ctrl + Enter, faceți dublu clic și trimiteți-l în jos. Acum, trebuie doar să găsesc Min disponibil pentru o anumită condiție sau criterii. Voi selecta Produs, Ctrl + Shift + Down Arroe, Ctrl + C pentru a copia, apoi voi merge la Săgeată dreapta, Ctrl + V, apoi voi veni și voi spune Remove Duplicates. Iata.
Foloseam tot timpul Advanced Filter, Unique Records, dar se pare că această metodă este mai rapidă. Există lista mea unică. Acum am să vin aici. Cat de mult? Și voi folosi noua funcție, MINIFS. Acum, MINIFS este în Office 365; pentru Excel 2016 sau o versiune ulterioară, MINRANGE. Ei bine, trebuie să găsesc valoarea minimă în această coloană, Ctrl + Shift + Săgeată în jos, F4, virgulă și gama de criterii - acesta va fi întregul produs. Ctrl + Shift + Săgeată în jos, F4, virgulă, Săgeată la stânga și acolo mergem. Aceasta va obține valoarea minimă din câte, în funcție de condiție sau criterii, închide paranteze, Ctrl + Enter, faceți dublu clic și trimiteți-o în jos. Bine. Deci, există MINIFS și Subtotal. Am să-l arunc înapoi către tine.
MrExcel: Da, Mike, foarte drăguț. Eliminați duplicatele, obțineți lista unică de produse și apoi funcția MINIFS. L-am întrebat ce versiune de Excel este, a spus că Excel 2016. Sper că este versiunea Office 365 a 2016, așa că are acces la asta. Ei bine, ce zici de un tabel pivot? În regulă, așa că am creat un tabel pivot cu produsul și cerințele, suma cantităților necesare și suma disponibilă. Apoi, chiar de aici, „Analizați”, „Câmpuri, articole și seturi”, „Câmp calculat” și a creat un nou câmp calculat numit „Disponibil”, care este la îndemână împărțit la Cantitatea necesară - în acest fel nu am nevoie Coloana Ajutorului de aici. Și la început se părea că va funcționa pentru că aveam 2, 3 și 4 și raportarea că minimul este 2 - Am schimbat acest calcul, desigur, în Min,și asta părea bine.
Dar apoi, pe acesta, unde avem 2,4,4,1,2, raportează 3. Și ceea ce se întâmplă este să facă calculul pe acest rând. Avem 25 la îndemână, împărțit la 8, adică 3 și o fracție, deci raportează 3 și deci nu. Un element regulat de calcul al tabelului pivot nu va funcționa. În schimb, convertiți aceste date într-un tabel și apoi Inserați, PivotTable, Adăugați aceste date la Modelul de date, faceți clic pe OK. Și vom avea, în partea stângă, produsul și ceea ce necesită. Voi crea aici două măsuri implicite cu o Cantitate Necesară și o parte din On Hand, și apoi voi crea o nouă măsură. Deci, PowerPivot, Măsură, o nouă măsură și această nouă măsură se va numi Disponibil pentru a vinde (Dispune de vânzare) și formula va fi,câte avem la îndemână împărțite la câte sunt necesare pentru fiecare articol și faceți clic pe OK. Bine, deci 8 împărțit la 4 este 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.
Ei bine, hei, vreau să vă mulțumesc că ați trecut pe aici, ne vedem data viitoare pentru un alt Dueling Excel Podcast de la MrExcel și Excel is Fun.
Descărcare fișier
Descărcați exemplarul de fișier aici: Duel190.xlsx
