Aujourd'hui, un problème Excel intéressant concernant les nomenclatures. Vous avez beaucoup de matières premières. Chaque élément peut être assemblé en plusieurs assemblages de niveau supérieur différents. Sur la base de la matière première disponible, en avez-vous assez pour traiter une commande pour un article donné?
Regarder la vidéo
- Tim demande: combien de chaque article est disponible à la vente
- Facteur de complication: un article est composé de plusieurs cartons
- Méthode de facturation n ° 1: Ajouter une colonne d'aide avec INT (Qté nécessaire / en main)
- Ajouter des sous-totaux pour le minimum d'aide à chaque changement de produit
- Réduire les sous-totaux dans la vue n ° 2
- Sélectionnez toutes les données. Utilisez alt = "" +; pour sélectionner les cellules visibles
- Coller dans une nouvelle gamme
- Ctrl + H pour changer Space Min en rien
- Méthode Mike # 2
- Copiez la colonne Produit à droite et utilisez Données, Supprimer les doublons
- À côté de la liste unique de produits, utilisez MINIFS
- Notez que MINIFS n'est disponible que dans Office 365
- Méthode de facturation n ° 3: un tableau croisé dynamique classique échoue car les champs calculés ne fonctionneront pas dans ce cas.
- Sélectionnez une cellule dans vos données et appuyez sur Ctrl + T pour convertir en tableau.
- Au lieu de cela, lorsque vous créez le tableau croisé dynamique, cochez la case Ajouter au modèle de données
- Créer une nouvelle mesure pour Disponible à la vente en utilisant INT
- Créer une nouvelle mesure pour le kit disponible à la vente à l'aide de MINX
- Ce tableau croisé dynamique fonctionne!
- Mike Méthode # 4 Utilisez la fonction AGGREGATE.
- Il semble que vous souhaitiez utiliser l'argument MIN, mais utilisez SMALL car il gère les tableaux
- Utilisation
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE est l'une des cinq fonctions qui peuvent accepter un tableau comme argument sans Ctrl + Maj + Entrée
- Méthode de facturation n ° 5
- Convertissez les données en table et utilisez Power Query - aka Get & Transform
- Dans Power Query, calculez OH / nécessaire
- Utilisez la fonction Number.RoundDown pour convertir en entier
- Utiliser le regroupement par numéro de pièce et disponibilité minimale
- Fermer et charger
- Bonus: c'est rafraîchissant!
Transcription vidéo
MrExcel: Salut, bon retour, il est temps pour un autre podcast Dueling Excel. Je suis Bill Jelen, je serai accompagné de Mike Girvin d'Excel Is Fun. Ceci est notre épisode 190: Combien de kits sont disponibles à la vente?
Très bien, la question d'aujourd'hui envoyée par Tim. Regarde nos vidéos Dueling Excel, il travaille pour un détaillant et a demandé de créer une feuille de calcul pour montrer à notre équipe de vente ce que nous possédons et ce que nous pouvons vendre. Cela semble simple, non? Mais voici le hic: l'article qu'ils vendent contient plusieurs cartons et sont inventoriés par carton. Voici un exemple de ce qu'il voit. Voici donc cet article, P12345, qui a 3 éléments différents à expédier. Et dans le kit, il faut 4 du carton 1, 1 du carton 2 et 1 du carton 3. Et c'est combien ils en ont en stock. D'accord, alors faites simplement le calcul ici, ils ont 2 jeux complets de Carton 1, 4 jeux complets de Carton 2 et 3 jeux complets de Carton 3. Mais cela signifie que ce qu'ils peuvent vendre est le minimum de ces 3 nombres - ils ne peut vendre que 2. Et ici, ils ont 4 ensembles complets de Carton 4,4 du carton 5, 2 du carton 3, seulement 1 du carton 7 - c'est l'élément limitant. Donc, dans ce cas, ils ne peuvent en vendre qu'un seul. Bien. Maintenant, une question pour un jour plus tard, j'ai dit: "Eh bien, y a-t-il une chance que le Carton 3 soit utilisé à plus d'un endroit?" Et il dit: "Oui, mais nous allons nous en préoccuper plus tard." Bien.
Alors, voici comment je vais attaquer cela. Je peux en fait penser à plusieurs façons différentes d'attaquer cela, donc cela pourrait être intéressant - cela pourrait être un type de duel aller-retour. Ce que je vais faire, c'est que je veux avoir une colonne d'aide ici, et la colonne d'aide va chercher article par article combien nous pouvons vendre. Donc = 8 divisé 4, comme ça, et nous allons double-cliquer pour le copier. Mais, disons que nous avions besoin de 4, et nous en avions 6. D'accord, alors maintenant ça va dire 1,5. Eh bien, vous ne pouvez pas vendre, vous savez, un demi-canapé, d'accord? Donc, il va falloir que ce soit le nombre entier. Donc ce que je vais faire ici, c'est utiliser le = INT - INT, l'entier - ce truc qui enlèvera les décimales et nous laissera juste le montant entier. Bien. Donc, nous avons 8 - retour au nombre d'origine.
Et nous devons déterminer, pour chaque élément ici, quel est le plus petit nombre dans la colonne E? Assurez-vous que les données sont triées par produit, allez dans l'onglet Données, choisissez Sous-totaux, à chaque changement de produit, utilisez la fonction Min. Vous savez, j'enseigne les sous-totaux tout le temps lors de mes séminaires Power Excel, et je souligne qu'il y a 11 fonctions ici, mais je n'ai jamais utilisé autre chose que Sum et Count. Donc, bien que le sous-total ne soit peut-être pas le moyen le plus rapide de le faire, je veux pouvoir dire qu'il y a eu en fait une fois où j'ai pu utiliser autre chose que Sum et Count. Très bien, cliquez sur OK. Et ce que nous allons obtenir, c'est à chaque fois que le numéro de rideau - le numéro de produit - change, nous pouvons voir le Min. Et ce Min est la réponse que nous voulons. Je me suis donc réduit à la vue numéro 2, je vais sélectionner toutes ces données et Alt +;pour sélectionner uniquement les cellules visibles, Ctrl + C, puis nous allons descendre ici et coller - collons simplement dans cette zone - Ctrl + V. Bien. Supprimez les colonnes supplémentaires et ensuite nous devons nous débarrasser du mot Min. Et pas seulement le mot Min, mais l'espace Min. Bien. Je vais donc utiliser Ctrl + H et changer la récurrence de l'espace Min en rien, Remplacer tout, cliquez sur OK, cliquez sur Fermer, et voici notre tableau de ce que nous avons à vendre. Très bien, Mike, je vous le jette.et voici notre tableau de ce que nous avons à vendre. Très bien, Mike, je vous le jette.et voici notre tableau de ce que nous avons à vendre. Très bien, Mike, je vous le jette.
Mike: Wow! MrExcel, j'adore ça. La fonction Min dans les sous-totaux. À quel point cela est cool? Très bien, je vais passer à cette feuille ici, je vais faire la même colonne Helper. = INT, nous prendrons tout "En main" divisé par "Quantité requise", fermez les parenthèses. Ctrl + Entrée, double-cliquez et envoyez-le. Maintenant, j'ai juste besoin de trouver le Min disponible pour une condition ou des critères donnés. Je vais sélectionner Produit, Ctrl + Maj + Bas Arroe, Ctrl + C pour copier, puis je vais à Flèche droite, Ctrl + V, puis je vais monter et dire Supprimer les doublons. Le voilà.
J'avais l'habitude d'utiliser le filtre avancé, les enregistrements uniques uniquement tout le temps, mais il semble que cette méthode soit plus rapide. Voilà ma liste unique. Maintenant, je vais venir ici. Combien? Et je vais utiliser la nouvelle fonction, MINIFS. Désormais, MINIFS est dans Office 365; pour Excel 2016 ou version ultérieure, le MINRANGE. Eh bien, j'ai besoin de trouver la valeur minimale dans cette colonne, Ctrl + Maj + Flèche bas, F4, virgule et la plage de critères - ce sera tout ce produit. Ctrl + Maj + Flèche bas, F4, virgule, Flèche gauche, et c'est parti. Cela obtiendra la valeur minimale à partir de combien, en fonction de la condition ou des critères, fermez les parenthèses, Ctrl + Entrée, double-cliquez et envoyez-la. Bien. Il y a donc MINIFS et Sous-total. Je vais vous le renvoyer.
MrExcel: Oui, Mike, très gentil. Supprimez les doublons, obtenez la liste unique des produits, puis la fonction MINIFS. Je lui ai demandé quelle version d'Excel il était, il a répondu Excel 2016. J'espère que c'est la version Office 365 de 2016, donc il y a accès. Eh bien, que diriez-vous d'un tableau croisé dynamique? Très bien, j'ai donc créé un tableau croisé dynamique avec le produit, et requiert, la somme des quantités requises et la somme des stocks disponibles. Ensuite, à partir de là, "Analyser", "Champs, articles et ensembles", "Champ calculé", et créé un nouveau champ calculé appelé "Disponible", qui est disponible divisé par la quantité requise - de cette façon, je n'ai pas besoin la colonne d'aide ici. Et au début, il semblait que cela allait marcher parce que nous avions 2, 3 et 4 et le rapport que le minimum est 2 - j'ai changé ce calcul, bien sûr, en Min,et cela semblait bon.
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.
Eh bien, je tiens à vous remercier d'être passé, nous vous verrons la prochaine fois pour un autre podcast Dueling Excel de MrExcel et Excel is Fun.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Duel190.xlsx
