Hôm nay, một bài toán Excel thú vị về hóa đơn nguyên vật liệu. Bạn có rất nhiều nguyên liệu thô. Mỗi mục có thể được lắp ráp thành một số cụm cấp cao nhất khác nhau. Dựa trên nguyên liệu thô có trong tay, bạn có đủ để thực hiện đơn đặt hàng cho một mặt hàng nào đó không?
Xem video
- Tim hỏi: Có bao nhiêu mặt hàng có sẵn để bán
- Yếu tố phức tạp: Một mặt hàng bao gồm nhiều thùng
- Phương pháp tính tiền # 1: Thêm cột người trợ giúp với INT (Số lượng cần thiết / Có sẵn)
- Thêm Tổng phụ cho Min of Helper ở mỗi thay đổi trong Sản phẩm
- Thu gọn Tổng phụ thành Chế độ xem số 2
- Chọn tất cả dữ liệu. Sử dụng alt = "" +; cho các ô có thể nhìn thấy được chọn
- Dán vào một phạm vi mới
- Ctrl + H để thay đổi Space Min thành không
- Phương pháp Mike # 2
- Sao chép cột Sản phẩm sang bên phải và sử dụng Dữ liệu, Xóa các Bản trùng lặp
- Bên cạnh danh sách sản phẩm duy nhất, hãy sử dụng MINIFS
- Lưu ý rằng MINIFS chỉ khả dụng trong Office 365
- Phương pháp hóa đơn # 3: bảng tổng hợp thông thường không thành công vì Trường được tính sẽ không hoạt động trong trường hợp này.
- Chọn một ô trong dữ liệu của bạn và nhấn Ctrl + T để chuyển đổi thành bảng.
- Thay vào đó, khi bạn đang tạo bảng tổng hợp, hãy chọn hộp Thêm vào Mô hình Dữ liệu
- Tạo một thước đo mới cho Sẵn có để Bán bằng INT
- Tạo thước đo mới cho Bộ công cụ có sẵn để bán bằng MINX
- Bảng xoay đó hoạt động!
- Mike Phương pháp # 4 Sử dụng hàm AGGREGATE.
- Có vẻ như bạn muốn sử dụng đối số MIN, nhưng sử dụng SMALL vì nó xử lý các mảng
- Sử dụng
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE là một trong năm hàm có thể chấp nhận một mảng làm đối số mà không cần Ctrl + Shift + Enter
- Phương thức thanh toán số 5
- Chuyển đổi dữ liệu thành bảng và sử dụng Power Query - hay còn gọi là Get & Transform
- Trong Power Query, hãy tính OH / Cần thiết
- Sử dụng hàm Number.RoundDown để chuyển đổi thành số nguyên
- Sử dụng Nhóm theo Số bộ phận và Thời hạn tối thiểu
- Đóng và tải
- Phần thưởng: Nó có thể làm mới!
Bản ghi video
MrExcel: Chào mừng bạn trở lại, đã đến lúc dành cho một Dueling Excel Podcast khác. Tôi là Bill Jelen đến từ, tôi sẽ tham gia cùng với Mike Girvin từ Excel Is Fun. Đây là Tập 190 của chúng ta: Có bao nhiêu bộ dụng cụ được bán?
Được rồi, câu hỏi hôm nay do Tim gửi. Xem video Dueling Excel của chúng tôi, anh ấy làm việc cho một nhà bán lẻ và yêu cầu tạo một bảng tính để cho nhóm bán hàng của chúng tôi biết chúng tôi sở hữu những gì và chúng tôi có thể bán những gì. Nghe có vẻ đơn giản đúng không? Nhưng đây là điểm nổi bật: Mặt hàng họ bán chứa nhiều thùng và được kiểm kê trên cơ sở mỗi thùng. Đây là một ví dụ về những gì anh ấy đang nhìn thấy. Vì vậy, đây là mặt hàng này, P12345, có 3 thứ khác nhau mà họ phải vận chuyển. Và trong bộ này, yêu cầu 4 trong số Thùng 1, 1 của Thùng 2 và 1 của Thùng 3. Và đây là số lượng họ có trong kho. Được rồi, chỉ cần làm phép toán ở đây, họ có 2 bộ Carton 1 hoàn chỉnh, 4 bộ Carton 2 hoàn chỉnh và 3 bộ hoàn chỉnh Carton 3. Nhưng điều đó có nghĩa là những gì họ có thể bán là tối thiểu trong 3 con số đó-- họ chỉ có thể bán 2. Và ở đây, họ có 4 bộ Carton 4 hoàn chỉnh,4 trong số Thùng 5, 2 trong số Thùng 3, chỉ 1 trong số Thùng 7-- đó là mục giới hạn. Vì vậy, trong trường hợp này, họ chỉ có thể bán một trong số này. Ổn thỏa. Bây giờ, một câu hỏi cho một ngày sau đó, tôi nói, "Chà, có khả năng nào mà Carton 3 được sử dụng ở nhiều nơi không?" Và anh ta nói, "Có, nhưng chúng ta sẽ lo lắng về điều đó sau." Ổn thỏa.
Vì vậy, đây là cách tôi sẽ tấn công điều này. Tôi thực sự có thể nghĩ ra một số cách khác nhau để tấn công nó, vì vậy điều này có thể thú vị - đây có thể là một kiểu đấu tay đôi. Những gì tôi sẽ làm là, tôi muốn có một cột Người trợ giúp ở đây, Và cột Người trợ giúp sẽ xem xét trên cơ sở từng mặt hàng về số lượng chúng tôi có thể bán. So = 8 chia 4, như vậy, và chúng tôi sẽ nhấp đúp để sao chép nó xuống. Nhưng, giả sử rằng chúng tôi cần 4 và chúng tôi có 6. Được rồi, bây giờ nó sẽ nói 1,5. Chà, bạn không thể bán, bạn biết đấy, một nửa đi văng, được chứ? Vì vậy, nó sẽ phải là số nguyên. Vì vậy, những gì tôi sẽ làm ở đây, là sử dụng = INT-- INT, số nguyên-- thứ sẽ loại bỏ các số thập phân và để lại cho chúng ta toàn bộ số tiền. Ổn thỏa. Vì vậy, sau đó chúng tôi có 8-- trở lại số ban đầu.
Và chúng ta cần tìm ra, đối với mỗi mục ở đây, số nhỏ nhất trong Cột E là bao nhiêu? Đảm bảo dữ liệu được sắp xếp theo Sản phẩm, chuyển đến tab Dữ liệu, chọn Tổng phụ, tại mỗi thay đổi trong Sản phẩm, hãy sử dụng hàm Min. Bạn biết đấy, tôi dạy Tổng số phụ mọi lúc trong các buổi hội thảo về Power Excel của mình và tôi chỉ ra rằng có 11 hàm ở đây nhưng tôi chưa bao giờ sử dụng bất cứ thứ gì khác ngoài Tổng và Đếm. Vì vậy, mặc dù Subtotal có thể không phải là cách nhanh nhất để làm điều này, nhưng tôi muốn có thể nói rằng thực sự đã có một lần tôi có thể sử dụng thứ gì đó ngoài Tổng và Đếm. Được rồi, bấm OK. Và những gì chúng ta sẽ nhận được, là mỗi khi số rèm - số sản phẩm - thay đổi, chúng ta sẽ thấy Min. Và Min đó chính là câu trả lời mà chúng tôi mong muốn. Vì vậy, tôi thu gọn xuống chế độ xem số 2, tôi sẽ chọn tất cả dữ liệu này và Alt +;để chỉ chọn các ô có thể nhìn thấy, Ctrl + C, sau đó chúng tôi sẽ xuống đây và dán-- hãy dán vào khu vực này-- Ctrl + V. Ổn thỏa. Xóa các cột thừa và sau đó chúng ta phải loại bỏ từ Min. Và không chỉ từ Min, mà là khoảng trắng Min. Ổn thỏa. Vì vậy, tôi sẽ sử dụng Ctrl + H và thay đổi khoảng cách lặp lại Min thành không, Thay thế Tất cả, nhấp vào OK, nhấp vào Đóng và có bảng của chúng tôi về những gì chúng tôi có sẵn để bán. Được rồi, Mike, tôi sẽ giao nó cho anh.và có bảng của chúng tôi về những gì chúng tôi có sẵn để bán. Được rồi, Mike, tôi sẽ giao nó cho anh.và có bảng của chúng tôi về những gì chúng tôi có sẵn để bán. Được rồi, Mike, tôi sẽ giao nó cho anh.
Mike: Chà! MrExcel, tôi thích nó. Hàm Min trong Tổng phụ. Làm thế nào là thú vị? Được rồi, tôi sẽ chuyển đến trang tính này ngay tại đây, tôi sẽ thực hiện cùng một cột Trình trợ giúp. = INT, chúng tôi sẽ lấy tất cả "Trên tay" chia cho "Số lượng Yêu cầu", đóng dấu ngoặc đơn. Ctrl + Enter, nhấp đúp và gửi nó xuống. Bây giờ, tôi chỉ cần tìm Min có sẵn cho một điều kiện hoặc tiêu chí nhất định. Tôi sẽ chọn Sản phẩm, Ctrl + Shift + Mũi tên Xuống, Ctrl + C để sao chép, sau đó tôi sẽ đến Mũi tên phải, Ctrl + V, sau đó tôi sẽ đến và nói Xóa bản sao. Nó đây.
Tôi đã từng sử dụng Bộ lọc nâng cao, Chỉ bản ghi duy nhất mọi lúc, nhưng có vẻ như phương pháp này nhanh hơn. Có danh sách duy nhất của tôi. Bây giờ tôi sẽ đến đây. Bao nhiêu? Và tôi sẽ sử dụng hàm mới, MINIFS. Bây giờ, MINIFS có trong Office 365; đối với Excel 2016 trở lên, MINRANGE. Chà, tôi cần tìm giá trị tối thiểu trong cột này, Ctrl + Shift + Mũi tên Xuống, F4, dấu phẩy và phạm vi tiêu chí-- đó sẽ là toàn bộ sản phẩm này. Ctrl + Shift + Mũi tên Xuống, F4, dấu phẩy, Mũi tên Trái và chúng ta bắt đầu. Điều đó sẽ nhận giá trị tối thiểu từ bao nhiêu, dựa trên điều kiện hoặc tiêu chí, đóng dấu ngoặc đơn, Ctrl + Enter, nhấp đúp và gửi nó xuống. Ổn thỏa. Vì vậy, có MINIFS và Subtotal. Tôi sẽ trả lại cho bạn.
MrExcel: Vâng, Mike, rất tuyệt. Loại bỏ các sản phẩm trùng lặp, lấy danh sách sản phẩm duy nhất và sau đó là hàm MINIFS. Tôi hỏi anh ấy đang sử dụng phiên bản Excel nào, anh ấy nói Excel 2016. Tôi hy vọng đó là phiên bản Office 365 2016, vì vậy anh ấy có quyền truy cập vào đó. Vậy còn Pivot Table thì sao? Được rồi, vì vậy tôi đã tạo Bảng tổng hợp có Sản phẩm và Yêu cầu, Tổng số lượng bắt buộc và Tổng hiện tay. Sau đó, từ ngay tại đây, "Phân tích", "Trường, Mục & Bộ", "Trường được Tính toán" và tạo một trường được tính toán mới có tên "Có sẵn", trường này được chia cho Số lượng bắt buộc - theo cách đó tôi không cần Cột Người trợ giúp ở đây. Và lúc đầu có vẻ như nó sẽ hoạt động vì chúng tôi có 2, 3 và 4 và báo cáo rằng mức tối thiểu là 2-- Tất nhiên, tôi đã thay đổi phép tính này thành Min,và điều đó có vẻ tốt.
But then, on this one, where we have 2,4,4,1,2, it's reporting 3. And what's happening is it's doing the calculation on this row. We have 25 on hand, divided by 8, that's 3 and a fraction, and so it's reporting 3, and so, no. A regular Pivot Table calculate item is not going to work. But instead, convert this data to a table and then Insert, PivotTable, Add this data to the Data Model, click OK. And we're going to have, down the left-hand side, Product and what it Requires. I'm going to create two implicit measures here with a Required Quantity and some of On Hand, and then I'm going to create a new measure. So, PowerPivot, Measure, a New Measure, and this new measure will be called Available to Sell (AvailToSell) and that formula is going to be, how many we have on hand divided by how many are required for each item, and click OK. Alright, so 8 divided by 4 is 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.
Chà, này, tôi muốn cảm ơn bạn đã ghé qua, lần sau chúng ta sẽ gặp lại bạn trong một Dueling Excel Podcast khác từ MrExcel và Excel is Fun.
Tải tập tin
Tải xuống tệp mẫu tại đây: Duel190.xlsx
