Berapa Banyak Kit Yang Ada - Petua Excel

Hari ini, masalah Excel yang menarik mengenai bil bahan. Anda mempunyai banyak bahan mentah. Setiap item mungkin dipasang menjadi beberapa perhimpunan tingkat atas yang berbeza. Berdasarkan bahan mentah yang ada, adakah anda cukup memenuhi pesanan untuk item tertentu?

Tonton Video

  • Tim bertanya: Berapakah jumlah barang yang boleh dijual
  • Faktor rumit: Item terdiri daripada pelbagai kadbod
  • Kaedah Bil # 1: Tambahkan lajur pembantu dengan INT (Kuantiti Diperlukan / Di Tangan)
  • Tambahkan Subtotal untuk Min Pembantu pada setiap perubahan Produk
  • Runtuhkan Subtotal ke Paparan # 2
  • Pilih semua data. Gunakan alt = "" +; untuk Sel Sel Terlihat
  • Tampal ke rangkaian baru
  • Ctrl + H untuk menukar Space Min menjadi sia-sia
  • Kaedah Mike # 2
  • Salin lajur Produk di sebelah kanan dan gunakan Data, Buang Pendua
  • Di sebelah senarai produk yang unik, gunakan MINIFS
  • Perhatikan bahawa MINIFS hanya tersedia di Office 365
  • Kaedah Bil # 3: jadual pangsi biasa gagal kerana Medan Terhitung tidak akan berfungsi dalam kes ini.
  • Pilih satu sel dalam data anda dan tekan Ctrl + T untuk menukar ke jadual.
  • Sebaliknya, semasa anda membuat jadual pangsi, pilih kotak untuk Tambah ke Model Data
  • Buat ukuran baru untuk Tersedia untuk Jual menggunakan INT
  • Buat ukuran baru untuk Kit Tersedia untuk Jual menggunakan MINX
  • Jadual pangsi itu berfungsi!
  • Kaedah Mike # 4 Gunakan fungsi AGGREGATE.
  • Nampaknya anda ingin menggunakan argumen MIN, tetapi gunakan KECIL kerana mengendalikan array
  • Gunakan =AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1)
  • AGGREGATE adalah salah satu daripada lima fungsi yang dapat menerima array sebagai argumen tanpa Ctrl + Shift + Enter
  • Kaedah Bil # 5
  • Tukarkan data ke jadual dan gunakan Power Query - aka Get & Transform
  • Dalam Kuasa Kuasa, hitung OH / Diperlukan
  • Gunakan fungsi Number.RoundDown untuk menukar ke integer
  • Gunakan Pengelompokan mengikut Nombor Bahagian dan Min Min
  • Tutup & Muatkan
  • Bonus: Ia boleh disegarkan!

Transkrip Video

MrExcel: Hei, selamat datang kembali, sudah tiba masanya untuk Dueling Excel Podcast yang lain. Saya Bill Jelen dari, saya akan disertai oleh Mike Girvin dari Excel Is Fun. Ini adalah Episod 190 kami: Berapa Banyak Kit Yang Ada untuk Dijual?

Baiklah, soalan hari ini dihantar oleh Tim. Menonton video Dueling Excel kami, dia bekerja untuk peruncit dan meminta untuk membuat spreadsheet untuk menunjukkan kepada pasukan penjualan kami apa yang kami miliki dan apa yang dapat kami jual. Kedengarannya mudah, bukan? Tetapi inilah hasilnya: Item yang mereka jual mengandungi banyak kadbod dan dicipta berdasarkan per kadbod. Inilah contoh apa yang dilihatnya. Jadi inilah item ini, P12345, mempunyai 3 perkara berbeza yang harus dihantar. Dan dalam kit itu, memerlukan 4 daripada Karton 1, 1 dari Karton 2, dan 1 dari Karton 3. Dan jumlahnya ada dalam stok. Baiklah, jadi dengan membuat matematik di sini, mereka mempunyai 2 set lengkap Karton 1, 4 set lengkap Karton 2, dan 3 set lengkap Karton 3. Tetapi itu bermakna apa yang dapat mereka jual adalah minimum 3 nombor itu-- hanya boleh menjual 2. Dan di sini, mereka mempunyai 4 set Karton 4 yang lengkap,4 dari Karton 5, 2 dari Karton 3, hanya 1 dari Karton 7-- itu adalah item yang membatasi. Jadi, dalam kes ini, mereka hanya boleh menjual salah satu daripadanya. Baiklah. Sekarang, soalan untuk kemudian hari, saya berkata, "Baiklah, adakah kemungkinan Karton 3 digunakan di lebih dari satu tempat?" Dan dia berkata, "Ya, tetapi kami akan bimbang tentang hal itu nanti." Baiklah.

Jadi inilah cara saya menyerang ini. Saya sebenarnya dapat memikirkan beberapa cara yang berbeza untuk menyerang ini, jadi ini mungkin menarik - ini mungkin jenis duel bolak-balik. Apa yang akan saya lakukan ialah, saya mahu mempunyai ruangan Helper di sini, dan ruangan Helper akan melihat berdasarkan item demi item berapa banyak yang dapat kita jual. Jadi = 8 dibahagi 4, seperti itu, dan kami akan klik dua kali untuk menyalinnya. Tetapi, katakan bahawa kita memerlukan 4, dan kita mempunyai 6. Baiklah, jadi sekarang ia akan mengatakan 1.5. Anda tidak boleh menjual, anda tahu, setengah sofa, baiklah? Jadi, ia mesti menjadi nombor bulat. Jadi apa yang akan saya lakukan di sini, adalah menggunakan = INT-- INT, bilangan bulat - perkara yang akan melepaskan perpuluhan dan meninggalkan kita jumlah keseluruhan. Baiklah. Oleh itu, kita mempunyai 8-- kembali ke nombor asal.

Dan kita perlu mengetahui, untuk setiap item di sini, berapakah bilangan terkecil di Lajur E? Pastikan data diurutkan berdasarkan Produk, pergi ke tab Data, pilih Subtotal, pada setiap perubahan pada Produk, gunakan fungsi Min. Anda tahu, saya mengajar Subtotal sepanjang masa di seminar Power Excel saya, dan saya menunjukkan ada 11 fungsi di sini tetapi saya tidak pernah menggunakan perkara lain selain Jumlah dan Kiraan. Oleh itu, walaupun Subtotal mungkin bukan cara terpantas untuk melakukan ini, saya ingin mengatakan bahawa sebenarnya ada satu masa di mana saya dapat menggunakan sesuatu selain daripada Jumlah dan Kiraan. Baiklah, klik OK. Dan apa yang akan kita perolehi adalah setiap kali nombor tirai - nombor produk - berubah, kita dapat melihat Min. Dan Min adalah jawapan yang kita mahukan. Oleh itu, saya jatuh ke paparan nombor 2, saya akan memilih semua data ini, dan Alt +;untuk memilih hanya sel yang kelihatan, Ctrl + C, dan kemudian kita akan turun ke sini dan tampal-- mari kita tampal ke kawasan ini-- Ctrl + V. Baiklah. Padamkan lajur tambahan dan kemudian kita harus menyingkirkan kata Min. Dan bukan hanya perkataan Min, tetapi ruang Min. Baiklah. Oleh itu, saya akan menggunakan Ctrl + H dan mengubah pengulangan ruang Min kepada apa-apa, Ganti Semua, klik OK, klik Tutup, dan ada jadual kami tentang apa yang ada untuk kami jual. Baiklah, Mike, saya akan menyerahkannya kepada anda.dan ada jadual kami tentang apa yang ada untuk dijual. Baiklah, Mike, saya akan menyerahkannya kepada anda.dan ada jadual kami tentang apa yang ada untuk dijual. Baiklah, Mike, saya akan menyerahkannya kepada anda.

Mike: Wah! MrExcel, saya suka. Fungsi Min dalam Subtotal. Sejauh mana sejuknya? Baiklah, saya akan pergi ke helaian ini di sini, saya akan membuat ruangan Helper yang sama. = INT kami akan mengambil semua "On Hand" dibahagi dengan "Kuantiti yang Diperlukan", tanda kurung dekat. Ctrl + Enter, klik dua kali, dan hantar ke bawah. Sekarang, saya hanya perlu mencari Min yang ada untuk syarat atau kriteria tertentu. Saya akan memilih Product, Ctrl + Shift + Down Arroe, Ctrl + C untuk disalin, kemudian saya akan ke Arrow Kanan, Ctrl + V, kemudian saya akan muncul dan mengatakan Remove Duplicates. Itu dia.

Saya selalu menggunakan Penapis Lanjutan, Rekod Unik Hanya sepanjang masa, tetapi nampaknya kaedah ini lebih cepat. Ada senarai unik saya. Sekarang saya akan datang ke sini. Berapa banyak? Dan saya akan menggunakan fungsi baru, MINIFS. Kini, MINIFS ada di Office 365; untuk Excel 2016 atau lebih baru, MINRANGE. Baiklah, saya perlu mencari nilai minimum di ruangan ini, Ctrl + Shift + Down Arrow, F4, koma dan kriteria berkisar - itulah keseluruhan produk ini. Ctrl + Shift + Panah Bawah, F4, koma, Panah Kiri, dan di sana kita pergi. Itu akan mendapat nilai min dari berapa, berdasarkan keadaan atau kriteria, tanda kurung tutup, Ctrl + Enter, klik dua kali dan hantarkan. Baiklah. Jadi ada MINIF dan Subtotal. Saya akan mengembalikannya kepada anda.

MrExcel: Ya, Mike, sangat bagus. Buang Pendua, dapatkan senarai produk yang unik, dan kemudian fungsi MINIFS. Saya bertanya kepadanya versi Excel apa yang dia gunakan, katanya Excel 2016. Saya harap itu versi Office 365 2016, jadi dia boleh mengaksesnya. Nah, bagaimana dengan Jadual Pangsi? Baiklah, jadi saya membuat Jadual Pangsi dengan Produk, dan Memerlukan, Jumlah Kuantiti yang Diperlukan, dan Jumlah Sedia Ada. Kemudian dari sini, "Analisis", "Medan, Item & Set", "Medan Dikira", dan buat medan dikira baru yang disebut "Tersedia", yang Di Tangan dibahagi dengan Kuantiti yang Diperlukan - dengan cara itu saya tidak perlu Tiang Penolong di sini. Dan pada mulanya nampaknya ia akan berjaya kerana kami mempunyai 2, 3, dan 4 dan melaporkan bahawa minimum adalah 2-- Saya mengubah pengiraan ini, tentu saja, kepada Min,dan itu kelihatan baik.

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.

Baiklah, hei, saya ingin mengucapkan terima kasih kerana mampir, kami akan berjumpa dengan anda untuk Dueling Excel Podcast lain dari MrExcel dan Excel is Fun.

Muat turun fail

Muat turun fail sampel di sini: Duel190.xlsx

Artikel menarik...