Bugün, malzeme faturalarıyla ilgili ilginç bir Excel sorunu. Çok fazla ham maddenin var. Her öğe, birkaç farklı üst düzey derlemede birleştirilebilir. Eldeki hammaddeye bağlı olarak, belirli bir ürün için bir siparişi yerine getirmek için yeterli var mı?
Video izle
- Tim sorar: Her bir öğeden kaç tane satılabilir?
- Karmaşıklaştırıcı faktör: Bir öğe birden fazla kartondan oluşur
- Fatura Yöntemi # 1: INT (Gerekli Miktar / Eldeki) ile bir yardımcı sütun ekleyin
- Üründeki her değişiklikte Minimum Yardımcı için Alt Toplamlar Ekleyin
- Alt Toplamları # 2 Görünüme Daralt
- Tüm verileri seçin. Alt = "" +; Görünür Hücreleri Seçin
- Yeni bir aralığa yapıştır
- Space Min'i sıfıra çevirmek için Ctrl + H
- Mike Yöntemi 2
- Ürün sütununu sağa kopyalayın ve Verileri kullanın, Kopyaları Kaldır
- Benzersiz ürün listesinin yanında MINIFS kullanın
- MINIFS'in yalnızca Office 365'te kullanılabildiğini unutmayın
- Fatura Yöntemi # 3: Hesaplanan Alanlar bu durumda çalışmayacağı için normal bir pivot tablo başarısız olur.
- Verilerinizde bir hücre seçin ve bir tabloya dönüştürmek için Ctrl + T tuşlarına basın.
- Bunun yerine, pivot tabloyu oluştururken Veri Modeline Ekle kutusunu seçin.
- INT kullanarak Satılmaya Uygun için yeni bir ölçü oluşturun
- MINX kullanarak Satılabilen Kit için yeni bir ölçü oluşturun
- Bu pivot tablo çalışıyor!
- Mike Method # 4 AGGREGATE işlevini kullanın.
- Görünüşe göre MIN bağımsız değişkeni kullanmak istiyorsunuz, ancak KÜÇÜK kullanın çünkü dizileri işliyor
- Kullanım
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE, Ctrl + Shift + Enter olmadan bir diziyi bağımsız değişken olarak kabul edebilen beş işlevden biridir.
- Fatura Yöntemi # 5
- Verileri bir tabloya dönüştürün ve Power Query'yi kullanın - diğer adıyla Get & Transform
- Power Query'de OH / Gerekli'yi hesaplayın
- Tam sayıya dönüştürmek için Number.RoundDown işlevini kullanın
- Parça Numarası ve Min Kullanıma Göre Gruplamayı Kullanın
- Kapat ve Yükle
- Bonus: Yenilenebilir!
Video Metni
MrExcel: Hey, tekrar hoş geldiniz, başka bir Düello Excel Podcast zamanı. Ben Bill Jelen'den, Excel Is Fun'dan Mike Girvin'e katılacağım. Bu bizim Bölüm 190: Satılabilecek Kaç Kit Var?
Pekala, bugünün sorusu Tim tarafından gönderildi. Düello Excel videolarımızı izliyor, bir perakendeci için çalışıyor ve satış ekibimize neye sahip olduğumuzu ve neyi satabileceğimizi göstermek için bir elektronik tablo oluşturmasını istedi. Basit, değil mi? Ancak şudur: Sattıkları ürün birden fazla karton içerir ve her karton için envantere alınır. İşte gördüklerinin bir örneği. İşte bu öğe, P12345, göndermeleri gereken 3 farklı şeye sahip. Ve sette, 4 Karton 1, 1 Karton 2 ve 1 Karton 3 gerektirir. Ve bu, stoklarında kaç tane var. Pekala, burada sadece matematiği yapıyorum, 2 tam set Karton 1, 4 tam Set Karton 2 ve 3 tam Karton 3 setine sahipler. sadece 2 satabilir. Ve burada 4 tam set Karton 4 var,Karton 5'in 4'ü, Karton 3'ün 2'si, Karton 7'nin sadece 1'i - sınırlayıcı öğe budur. Yani bu durumda bunlardan sadece birini satabilirler. Peki. Şimdi, daha sonraki bir gün için bir soru, "Karton 3'ün birden fazla yerde kullanılması ihtimali var mı?" Dedim. Ve "Evet, ama bunun için daha sonra endişeleneceğiz" diyor. Peki.
İşte buna nasıl saldıracağım. Aslında buna saldırmanın birkaç farklı yolunu düşünebilirim, bu yüzden bu ilginç olabilir - bu ileri geri bir düello olabilir. Yapacağım şey, burada bir Yardımcı sütun istiyorum, Ve Yardımcı sütunu, kaç tane satabileceğimize göre öğe bazında bakacak. Yani = 8 bölü 4, bunun gibi, ve kopyalamak için çift tıklayacağız. Ama diyelim ki 4'e ihtiyacımız vardı ve 6'ya sahiptik. Pekala, şimdi 1.5 diyecek. Şey, satamazsın, yarım koltuğun yarısı, tamam mı? Yani, tam sayı olmak zorunda kalacak. Yani burada yapacağım şey, = INT - INT, tamsayı - ondalık sayıları çıkaracak ve bize sadece tüm miktarı bırakacak olan şeyi kullanmak. Peki. Öyleyse, orijinal numaraya geri dönüyoruz.
Ve buradaki her öğe için E Sütunundaki en küçük sayının ne olduğunu bulmalıyız. Verilerin Ürüne göre sıralandığından emin olun, Veri sekmesine gidin, Alt Toplamlar'ı seçin, Ürün'deki her değişiklikte Min işlevini kullanın. Biliyorsunuz, Power Excel seminerlerimde her zaman Alt Toplamlar öğretiyorum ve burada 11 işlev olduğunu söylüyorum ama Topla ve Say dışında hiçbir şey kullanmadım. Bu yüzden Alt Toplam bunu yapmanın en hızlı yolu olmasa da, aslında bir kez Toplam ve Say dışında bir şey kullanabildiğimi söylemek istiyorum. Pekala, Tamam'ı tıklayın. Ve elde edeceğimiz şey, perde numarası - ürün numarası - her değiştiğinde, Min'i görmemizdir. Ve bu Min, istediğimiz cevap. Bu yüzden 2 numaralı görünüme daraldım, tüm bu verileri seçeceğim ve Alt +;sadece görünen hücreleri seçmek için, Ctrl + C, ve sonra buraya gelip yapıştıracağız - sadece bu alana yapıştıralım - Ctrl + V. Peki. Fazladan sütunları silin ve ardından Min kelimesinden kurtulmalıyız. Ve sadece Min kelimesi değil, boşluk Min. Peki. Bu yüzden Ctrl + H'yi kullanacağım ve Min alanının yinelemesini sıfır olarak değiştireceğim, Tümünü Değiştir, Tamam'a tıklayın, Kapat'a tıklayın ve burada satabileceğimiz şeylerin bulunduğu tablomuz var. Pekala Mike, onu sana atacağım.ve satmaya hazır olduğumuz şeylerin tablosu var. Pekala Mike, onu sana atacağım.ve satmaya hazır olduğumuz şeylerin tablosu var. Pekala Mike, onu sana atacağım.
Mike: Vay canına! MrExcel, bayıldım. Alt Toplamlarda Min işlevi. Ne kadar serin? Pekala, tam burada bu sayfaya gideceğim, aynı Yardımcı sütununu yapacağım. = INT tüm "Eldeki" kısımları "Gerekli Miktar" a bölerek parantezleri kapatacağız. Ctrl + Enter, çift tıklayın ve aşağıya gönderin. Şimdi, sadece belirli bir koşul veya kriter için kullanılabilir Min'i bulmam gerekiyor. Kopyalamak için Ürün, Ctrl + Shift + Aşağı Arroe, Ctrl + C'yi seçeceğim, ardından Sağ Ok, Ctrl + V'ye gidiyorum, sonra gelip Yinelemeleri Kaldır diyeceğim. İşte burada.
Eskiden Advanced Filter, Unique Records'u her zaman kullanırdım, ancak bu yöntem daha hızlı gibi görünüyor. Benim eşsiz listem var. Şimdi buraya geleceğim. Kaç? Ve yeni işlevi MINIFS'i kullanacağım. Artık MINIFS Office 365'te; Excel 2016 veya sonraki sürümler için MINRANGE. Pekala, bu sütunda minimum değeri bulmam gerekiyor, Ctrl + Shift + Aşağı Ok, F4, virgül ve kriter aralığı - bu tüm bu ürün olacak. Ctrl + Shift + Aşağı Ok, F4, virgül, Sol Ok ve işte başlıyoruz. Bu, koşula veya kriterlere bağlı olarak kaç taneden minimum değeri alacak, parantezleri kapatınız, Ctrl + Enter, çift tıklayıp aşağı gönderiniz. Peki. Demek MINIFS ve Ara Toplam var. Onu sana geri atacağım.
MrExcel: Evet, Mike, çok güzel. Yinelenenleri Kaldır, benzersiz ürün listesini ve ardından MINIFS işlevini alın. Ona Excel'in hangi sürümünü kullandığını sordum, Excel 2016 dedi. Umarım 2016'nın Office 365 sürümüdür, yani ona erişimi vardır. Peki pivot tabloya ne dersiniz? Pekala, bu yüzden Ürün ve Gereksinimler, Gerekli Miktarların Toplamı ve Eldeki Toplamın bulunduğu bir Pivot Tablo oluşturdum. Daha sonra buradan, "Analiz Et", "Alanlar, Öğeler ve Kümeler", "Hesaplanan Alan" ve Eldeki Gerekli Miktar'a bölünen "Kullanılabilir" adlı yeni bir hesaplanan alan oluşturdu - böylece ihtiyacım yok Yardımcı Sütun burada. Ve ilk başta işe yarayacak gibi görünüyordu çünkü 2, 3 ve 4 vardı ve minimumun 2 olduğunu rapor ediyoruz - Bu hesaplamayı elbette Min olarak değiştirdim.ve bu iyi görünüyordu.
Ama sonra, 2,4,4,1,2'ye sahip olduğumuz bunda, rapor veriyor 3. Ve olan şey, hesaplamayı bu satırda yapıyor. Elimizde 25 var, 8'e bölünmüş, bu 3 ve bir kesir, yani 3'ü rapor ediyor, yani hayır. Normal bir Pivot Tablo hesaplama öğesi çalışmayacak. Ancak bunun yerine, bu verileri bir tabloya dönüştürün ve ardından Ekle, PivotTable, Bu verileri Veri Modeline ekle, Tamam'a tıklayın. Ve sol tarafta, Ürün ve Gereksinimine sahip olacağız. Burada bir Gerekli Miktar ve bir miktar Elde olan iki örtük ölçü oluşturacağım ve sonra yeni bir ölçü oluşturacağım. Dolayısıyla, PowerPivot, Ölçü, Yeni Ölçü ve bu yeni ölçüme Satılmaya Uygun (AvailToSell) adı verilecek ve bu formül şu şekilde olacak:Elimizde kaç tane var, her öğe için kaç tane gerekli olduğuna bölün ve Tamam'ı tıklayın. Pekala, 8 bölü 4 eşittir 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.
Pekala, hey, uğradığınız için teşekkür etmek istiyorum, bir dahaki sefere MrExcel'den başka bir Düello Excel Podcast'i için görüşürüz ve Excel Eğlencelidir.
Dosyayı indir
Örnek dosyayı buradan indirin: Duel190.xlsx
