Σήμερα, ένα ενδιαφέρον πρόβλημα του Excel σχετικά με τους λογαριασμούς υλικού. Έχετε πολλές πρώτες ύλες. Κάθε στοιχείο μπορεί να συναρμολογηθεί σε διάφορα διαφορετικά συγκροτήματα ανώτατου επιπέδου. Με βάση την πρώτη ύλη που διαθέτετε, έχετε αρκετό για να εκπληρώσετε μια παραγγελία για ένα συγκεκριμένο είδος;
Δες το βίντεο
- Ο Tim ρωτά: Πόσα από τα προϊόντα είναι διαθέσιμα προς πώληση
- Περίπλοκος παράγοντας: Ένα αντικείμενο αποτελείται από πολλά χαρτοκιβώτια
- Μέθοδος λογαριασμού # 1: Προσθέστε μια βοηθητική στήλη με INT (Qty Needed / On Hand)
- Προσθέστε υποσύνολα για το ελάχιστο βοηθό σε κάθε αλλαγή στο προϊόν
- Σύμπτυξη υποσυνόλων στην προβολή # 2
- Επιλέξτε όλα τα δεδομένα. Χρησιμοποιήστε alt = "" +; για Επιλογή ορατών κελιών
- Επικόλληση σε μια νέα σειρά
- Ctrl + H για αλλαγή του Space Min σε τίποτα
- Μέθοδος Mike # 2
- Αντιγράψτε τη στήλη Προϊόν στα δεξιά και χρησιμοποιήστε Δεδομένα, Κατάργηση διπλότυπων
- Δίπλα στη μοναδική λίστα προϊόντων, χρησιμοποιήστε το MINIFS
- Σημειώστε ότι το MINIFS είναι διαθέσιμο μόνο στο Office 365
- Μέθοδος χρέωσης # 3: ένας κανονικός συγκεντρωτικός πίνακας αποτυγχάνει επειδή τα υπολογισμένα πεδία δεν θα λειτουργούν σε αυτήν την περίπτωση.
- Επιλέξτε ένα κελί στα δεδομένα σας και πατήστε Ctrl + T για μετατροπή σε πίνακα.
- Αντ 'αυτού, καθώς δημιουργείτε τον συγκεντρωτικό πίνακα, επιλέξτε το πλαίσιο για Προσθήκη σε μοντέλο δεδομένων
- Δημιουργήστε ένα νέο μέτρο για Διαθέσιμο προς Πώληση χρησιμοποιώντας INT
- Δημιουργήστε ένα νέο μέτρο για το κιτ Διαθέσιμο προς πώληση χρησιμοποιώντας το MINX
- Αυτός ο περιστρεφόμενος πίνακας λειτουργεί!
- Μέθοδος Mike # 4 Χρησιμοποιήστε τη συνάρτηση AGGREGATE.
- Φαίνεται ότι θα θέλατε να χρησιμοποιήσετε το όρισμα ΜΙΝ, αλλά χρησιμοποιήστε το ΜΙΚΡΟ επειδή χειρίζεται πίνακες
- Χρήση
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - Το AGGREGATE είναι μία από τις πέντε συναρτήσεις που μπορούν να δεχτούν έναν πίνακα ως όρισμα χωρίς Ctrl + Shift + Enter
- Μέθοδος λογαριασμού # 5
- Μετατρέψτε τα δεδομένα σε πίνακα και χρησιμοποιήστε το Power Query - γνωστός και ως Get & Transform
- Στο Power Query, υπολογίστε OH / Needed
- Χρησιμοποιήστε τη συνάρτηση Number.RoundDown για μετατροπή σε ακέραιο
- Χρησιμοποιήστε την ομαδοποίηση κατά αριθμό μέρους και ελάχιστο διαθέσιμο
- Κλείσιμο & φόρτωση
- Μπόνους: Είναι ανανεώσιμο!
Μεταγραφή βίντεο
MrExcel: Γεια σας, καλώς ήρθατε, ήρθε η ώρα για ένα άλλο Dueling Excel Podcast. Είμαι ο Bill Jelen από, θα έρθω μαζί μου ο Mike Girvin από το Excel Is Fun. Αυτό είναι το επεισόδιο 190: Πόσα κιτ είναι διαθέσιμα για πώληση;
Εντάξει, η σημερινή ερώτηση που έστειλε ο Tim. Παρακολουθεί τα βίντεο Dueling Excel, εργάζεται για έναν λιανοπωλητή και ζήτησε να δημιουργήσει ένα υπολογιστικό φύλλο για να δείξει στην ομάδα πωλήσεών μας τι έχουμε και τι μπορούμε να πουλήσουμε. Ακούγεται απλό, έτσι; Αλλά εδώ είναι το αλίευμα: Το είδος που πωλούν περιέχει πολλά χαρτοκιβώτια και απογραφεί ανά χαρτοκιβώτιο. Εδώ είναι ένα παράδειγμα αυτού που βλέπει. Εδώ λοιπόν, αυτό το είδος, το P12345, έχει 3 διαφορετικά πράγματα που πρέπει να στείλουν. Και στο κιτ, απαιτούνται 4 από το Χαρτοκιβώτιο 1, 1 του Χαρτοκιβωτίου 2 και 1 του Χαρτοκιβωτίου 3. Και αυτό είναι πόσα έχουν σε απόθεμα. Εντάξει, οπότε απλά κάνουν τα μαθηματικά εδώ, έχουν 2 πλήρη σετ χαρτοκιβωτίων 1, 4 πλήρη σετ χαρτοκιβωτίων 2 και 3 πλήρη σετ χαρτοκιβωτίου 3. Αλλά αυτό σημαίνει ότι αυτό που μπορούν να πουλήσουν είναι το ελάχιστο από αυτούς τους 3 αριθμούς - αυτοί μπορεί να πουλήσει μόνο 2. Και εδώ, έχουν 4 πλήρη σετ χαρτοκιβωτίων 4,4 του χαρτοκιβωτίου 5, 2 του χαρτοκιβωτίου 3, μόνο 1 του χαρτοκιβωτίου 7 - αυτό είναι το περιοριστικό στοιχείο. Έτσι, σε αυτήν την περίπτωση, μπορούν να πουλήσουν μόνο ένα από αυτά. Καλώς. Τώρα, μια ερώτηση για μια μετέπειτα μέρα, είπα, "Λοιπόν, υπάρχει πιθανότητα το Carton 3 να χρησιμοποιηθεί σε περισσότερα από ένα μέρη;" Και λέει, "Ναι, αλλά θα το ανησυχούμε αργότερα." Καλώς.
Λοιπόν, πώς θα το επιτεθώ. Μπορώ πραγματικά να σκεφτώ πολλούς διαφορετικούς τρόπους για να επιτεθώ σε αυτό, οπότε αυτό μπορεί να είναι ενδιαφέρον - αυτό μπορεί να είναι ένα είδος μονομαχίας. Αυτό που πρόκειται να κάνω είναι, θέλω να έχω μια στήλη Βοηθός εδώ, και η στήλη Βοηθός θα εξετάσει κατά είδος πόσα μπορούμε να πουλήσουμε. Έτσι = 8 διαιρείται 4, έτσι, και θα κάνουμε διπλό κλικ για να το αντιγράψουμε. Αλλά, ας πούμε ότι χρειαζόμασταν 4 και είχαμε 6. Εντάξει, οπότε τώρα θα πούμε 1.5. Λοιπόν δεν μπορείτε να πουλήσετε, ξέρετε, μισό καναπέ, εντάξει; Λοιπόν, θα πρέπει να είναι ο ακέραιος αριθμός. Αυτό που πρόκειται να κάνω εδώ είναι να χρησιμοποιήσω το = INT-- INT, τον ακέραιο - αυτό που θα βγάλει τα δεκαδικά και θα μας αφήσει μόνο το συνολικό ποσό. Καλώς. Έχουμε λοιπόν 8 - πίσω στον αρχικό αριθμό.
Και πρέπει να καταλάβουμε, για κάθε στοιχείο εδώ, ποιος είναι ο μικρότερος αριθμός στη στήλη Ε; Βεβαιωθείτε ότι τα δεδομένα ταξινομούνται κατά Προϊόν, μεταβείτε στην καρτέλα Δεδομένα, επιλέξτε Υποσύνολα, σε κάθε αλλαγή στο Προϊόν, χρησιμοποιήστε τη συνάρτηση Min. Ξέρετε, διδάσκω Subtotals όλη την ώρα στα σεμινάρια Power Excel και επισημαίνω ότι υπάρχουν 11 συναρτήσεις εδώ, αλλά δεν έχω χρησιμοποιήσει ποτέ τίποτα άλλο εκτός από το άθροισμα και το πλήθος. Έτσι, ενώ το Subtotal μπορεί να μην είναι ο γρηγορότερος τρόπος για να το κάνω αυτό, θέλω να είμαι σε θέση να πω ότι υπήρχε πράγματι μια στιγμή όπου μπορούσα να χρησιμοποιήσω κάτι άλλο εκτός από το άθροισμα και τον αριθμό. Εντάξει, κάντε κλικ στο OK. Και αυτό που πρόκειται να λάβουμε, είναι κάθε φορά που αλλάζει ο αριθμός κουρτίνας - ο αριθμός προϊόντος - αλλάζει, βλέπουμε το Ελάχ. Και ότι η Min είναι η απάντηση που θέλουμε. Έτσι κατέρρευσα στην προβολή νούμερο 2, θα επιλέξω όλα αυτά τα δεδομένα και Alt +;για να επιλέξετε μόνο τα ορατά κελιά, Ctrl + C, και στη συνέχεια θα κατεβούμε εδώ και θα επικολλήσουμε - ας απλώς επικολλήσουμε αυτήν την περιοχή - Ctrl + V. Καλώς. Διαγράψτε τις επιπλέον στήλες και στη συνέχεια πρέπει να απαλλαγούμε από τη λέξη Ελάχ. Και όχι μόνο η λέξη Min, αλλά το διάστημα Min. Καλώς. Θα χρησιμοποιήσω λοιπόν το Ctrl + H και θα αλλάξω την επανάληψη του χώρου Ελάχιστη σε τίποτα, Αντικατάσταση όλων, κάντε κλικ στο OK, κάντε κλικ στο Κλείσιμο, και υπάρχει ο πίνακας μας για ό, τι έχουμε διαθέσιμο για πώληση. Εντάξει, Μάικ, θα σου το ρίξω.και υπάρχει ο πίνακας μας για το τι έχουμε διαθέσιμο για πώληση. Εντάξει, Μάικ, θα σου το ρίξω.και υπάρχει ο πίνακας μας για το τι έχουμε διαθέσιμο για πώληση. Εντάξει, Μάικ, θα σου το ρίξω.
Μάικ: Ουάου! MrExcel, μου αρέσει. Η συνάρτηση Min σε αθροίσματα. Πόσο δροσερό είναι αυτό; Εντάξει, θα πάω σε αυτό το φύλλο εδώ, θα κάνω την ίδια στήλη Βοηθός. = INT θα πάρουμε όλα "On Hand" διαιρούμενο με "Απαιτούμενη ποσότητα", κοντά σε παρενθέσεις. Ctrl + Enter, κάντε διπλό κλικ και στείλτε το. Τώρα, πρέπει απλώς να βρω το ελάχιστο διαθέσιμο για μια δεδομένη κατάσταση ή κριτήρια. Θα επιλέξω Product, Ctrl + Shift + Down Arroe, Ctrl + C για αντιγραφή, μετά θα πάω στο Right Arrow, Ctrl + V, και μετά θα έρθω να πω Αφαίρεση διπλότυπων. Εκεί είναι.
Συνήθιζα να χρησιμοποιώ το Advanced Filter, Unique Records Μόνο όλη την ώρα, αλλά φαίνεται ότι αυτή η μέθοδος είναι ταχύτερη. Υπάρχει η μοναδική μου λίστα. Τώρα θα έρθω εδώ. Πόσα? Και θα χρησιμοποιήσω τη νέα λειτουργία, MINIFS. Τώρα, το MINIFS είναι στο Office 365. για το Excel 2016 ή μεταγενέστερη έκδοση, το MINRANGE. Λοιπόν, πρέπει να βρω την ελάχιστη τιμή σε αυτήν τη στήλη, Ctrl + Shift + Down Arrow, F4, κόμμα και το εύρος κριτηρίων - αυτό θα είναι όλο αυτό το προϊόν. Ctrl + Shift + Κάτω βέλος, F4, κόμμα, Αριστερό βέλος και εκεί πηγαίνουμε. Αυτό θα πάρει την ελάχιστη τιμή από πόσες, με βάση την κατάσταση ή τα κριτήρια, κλείστε παρενθέσεις, Ctrl + Enter, κάντε διπλό κλικ και στείλτε την. Καλώς. Υπάρχει λοιπόν MINIFS και Subtotal. Θα σας το ρίξω πίσω σε εσάς.
MrExcel: Ναι, Mike, πολύ ωραία. Καταργήστε τα διπλότυπα, λάβετε τη μοναδική λίστα προϊόντων και, στη συνέχεια, τη λειτουργία MINIFS. Τον ρώτησα σε ποια έκδοση του Excel είναι, είπε το Excel 2016. Ελπίζω ότι είναι η έκδοση του Office 365 του 2016, οπότε έχει πρόσβαση σε αυτήν. Λοιπόν, τι γίνεται με έναν συγκεντρωτικό πίνακα; Εντάξει, οπότε δημιούργησα έναν Συγκεντρωτικό Πίνακα με Προϊόν και Απαιτήσεις, Άθροισμα απαιτούμενων ποσοτήτων και Άθροισμα Ετοιμότητας. Στη συνέχεια, από εδώ, "Ανάλυση", "Πεδία, αντικείμενα και σύνολα", "Υπολογισμένο πεδίο" και δημιούργησα ένα νέο υπολογισμένο πεδίο με την ονομασία "Διαθέσιμο", το οποίο διατίθεται διαμέσου με την απαιτούμενη ποσότητα - έτσι δεν χρειάζομαι η στήλη βοηθού εδώ. Και στην αρχή φαινόταν ότι θα λειτουργούσε επειδή είχαμε 2, 3 και 4 και η αναφορά ότι το ελάχιστο είναι 2-- Αλλάξα αυτόν τον υπολογισμό, φυσικά, σε Min,και αυτό φαινόταν καλό.
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.
Λοιπόν, hei, θέλω να σας ευχαριστήσω που σταματήσατε, θα σας δούμε την επόμενη φορά για ένα άλλο Dueling Excel Podcast από το MrExcel και το Excel is Fun
Λήψη αρχείου
Κάντε λήψη του δείγματος αρχείου εδώ: Duel190.xlsx
