I dag et interessant Excel-problem om papirregninger. Du har mange råvarer. Hvert element kan samles i flere forskjellige toppnivåsenheter. Basert på råvaren som er tilgjengelig, har du nok til å oppfylle en bestilling på en bestemt vare?
Se på video
- Tim spør: Hvor mange av hver vare er tilgjengelig å selge
- Kompliserende faktor: En vare består av flere kartonger
- Regningsmetode nr. 1: Legg til en hjelpekolonne med INT (Antall behov / Til hånd)
- Legg til delsummer for Min of Helper ved hver endring i produktet
- Skjul delsummene til nr. 2-visningen
- Velg alle data. Bruk alt = "" +; for Velg synlige celler
- Lim inn i et nytt utvalg
- Ctrl + H for å endre Space Min til ingenting
- Mike Method # 2
- Kopier produktkolonnen til høyre og bruk Data, Fjern duplikater
- Bruk MINIFS ved siden av den unike listen over produkter
- Merk at MINIFS bare er tilgjengelig i Office 365
- Regningsmetode nr. 3: En vanlig pivottabell mislykkes fordi Beregnede felt ikke fungerer i dette tilfellet.
- Velg en celle i dataene dine og trykk Ctrl + T for å konvertere til en tabell.
- I stedet, mens du oppretter pivottabellen, velger du boksen for Legg til datamodell
- Opprett et nytt mål for tilgjengelig for salg ved hjelp av INT
- Lag et nytt mål for Kit tilgjengelig for salg med MINX
- Det svingbordet fungerer!
- Mike Method # 4 Bruk AGGREGATE-funksjonen.
- Det virker som om du vil bruke MIN-argument, men bruk SMALL fordi det håndterer matriser
- Bruk
=AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1) - AGGREGATE er en av fem funksjoner som kan akseptere en matrise som et argument uten Ctrl + Shift + Enter
- Bill Method # 5
- Konverter dataene til en tabell og bruk Power Query - aka Get & Transform
- I Power Query beregner du OH / Needed
- Bruk Number.RoundDown-funksjonen til å konvertere til heltall
- Bruk gruppering etter varenummer og min
- Lukk og last
- Bonus: Det er forfriskende!
Videoutskrift
MrExcel: Hei, velkommen tilbake, det er på tide med en annen Dueling Excel Podcast. Jeg er Bill Jelen fra, jeg får med Mike Girvin fra Excel Is Fun. Dette er vår episode 190: Hvor mange sett er tilgjengelige for salg?
Greit, dagens spørsmål sendt av Tim. Ser på våre Dueling Excel-videoer, han jobber for en forhandler og ba om å lage et regneark for å vise salgsteamet vårt hva vi eier og hva vi kan selge. Høres enkelt ut, ikke sant? Men her er fangsten: Varen de selger inneholder flere kartonger og lagerføres per kartong. Her er et eksempel på hva han ser. Så her er denne varen, P12345, som har 3 forskjellige ting de må sende. Og i settet krever 4 av kartong 1, 1 av kartong 2 og 1 av kartong 3. Og dette er hvor mange de har på lager. Greit, så bare gjør matte her, de har 2 komplette sett med kartong 1, 4 komplette sett med kartong 2 og 3 komplette sett med kartong 3. Men det betyr at det de kan selge er minimum av disse 3 tallene - de kan bare selge 2. Og her har de 4 komplette sett med kartong 4,4 av kartong 5, 2 av kartong 3, bare 1 av kartong 7-- det er det begrensende elementet. Så i dette tilfellet kan de bare selge en av disse. Ok. Nå, et spørsmål for en senere dag, sa jeg: "Vel, er det noen sjanse for at kartong 3 brukes mer enn ett sted?" Og han sier, "Ja, men vi kommer til å bekymre oss for det senere." Ok.
Så her skal jeg angripe dette. Jeg kan faktisk tenke på flere forskjellige måter å angripe dette på, så dette kan være interessant - dette kan være en frem og tilbake type duell. Det jeg skal gjøre er at jeg vil ha en Helper-kolonne her ute, og Helper-kolonnen vil se på en vare-for-element-basis av hvor mange vi kan selge. Så = 8 delt 4, slik, og vi dobbeltklikker for å kopiere den ned. Men la oss si at vi trengte 4, og vi hadde 6. Greit, så nå kommer det til å si 1,5. Vel, du kan ikke selge, vet du, en halv sofa, ok? Så det må være hele tallet. Så det jeg skal gjøre her, er å bruke = INT-- INT, heltallet - den tingen som tar av desimalene og gir oss bare hele beløpet. Ok. Så da har vi 8-- tilbake til det opprinnelige nummeret.
Og vi må finne ut, for hvert element her, hva er det minste antallet i kolonne E? Forsikre deg om at dataene er sortert etter produkt, gå til Data-fanen, velg Delsummer, ved hver endring i Produkt, bruk Min-funksjonen. Du vet, jeg underviser delsummer hele tiden på Power Excel-seminarene mine, og jeg påpeker at det er 11 funksjoner her, men jeg har aldri brukt noe annet enn Sum og Count. Så selv om Subtotal kanskje ikke er den raskeste måten å gjøre dette på, vil jeg være i stand til å si at det faktisk var en gang jeg kunne bruke noe annet enn Sum og Count. OK, klikk OK. Og det vi skal få, er at hver gang gardinnummeret - produktnummeret - endres, får vi se Min. Og det Min er svaret vi ønsker. Så jeg kollapset ned til nummer 2-visningen, jeg velger alle disse dataene, og Alt +;for å velge bare de synlige cellene, Ctrl + C, og så kommer vi ned hit og lime inn - la oss bare lime ut til dette området - Ctrl + V. Ok. Slett de ekstra kolonnene, og så må vi bli kvitt ordet Min. Og ikke bare ordet Min, men også plass Min. Ok. Så jeg skal bruke Ctrl + H og endre tilbakefall av plass Min til ingenting, Erstatt alt, klikk OK, klikk Lukk, og det er vår tabell over hva vi har tilgjengelig å selge. OK, Mike, jeg vil kaste den over til deg.og det er vårt bord med hva vi har tilgjengelig å selge. OK, Mike, jeg vil kaste det over til deg.og det er vårt bord med hva vi har tilgjengelig å selge. OK, Mike, jeg vil kaste det over til deg.
Mike: Wow! MrExcel, jeg elsker det. Min-funksjonen i delsummer. Hvor kult er det? Greit, jeg skal gå over til dette arket akkurat her, jeg skal gjøre den samme hjelpekolonnen. = INT vi tar alle "Til hånden" delt på "Nødvendig mengde", nære parenteser. Ctrl + Enter, dobbeltklikk og send den ned. Nå trenger jeg bare å finne Min tilgjengelig for en gitt tilstand eller kriterier. Jeg skal velge Produkt, Ctrl + Skift + Ned Arroe, Ctrl + C for å kopiere, så skal jeg til høyre pil, Ctrl + V, så skal jeg komme opp og si Fjern duplikater. Der er det.
Jeg pleide å bruke avansert filter, unike poster bare hele tiden, men det virker som om denne metoden er raskere. Det er min unike liste. Nå skal jeg komme hit. Hvor mange? Og jeg skal bruke den nye funksjonen, MINIFS. Nå er MINIFS i Office 365; for Excel 2016 eller senere, MINRANGE. Vel, jeg må finne minimumsverdien i denne kolonnen, Ctrl + Shift + Pil ned, F4, komma og kriterieområdet - det kommer til å være hele dette produktet. Ctrl + Shift + Pil ned, F4, komma, Venstre pil, og der går vi. Det vil få minverdien fra hvor mange, basert på tilstanden eller kriteriene, lukker parentes, Ctrl + Enter, dobbeltklikk og send den ned. Ok. Så det er MINIFS og Subtotal. Jeg skal kaste den tilbake til deg.
MrExcel: Ja, Mike, veldig hyggelig. Fjern duplikater, få den unike listen over produkter, og deretter MINIFS-funksjonen. Jeg spurte ham hvilken versjon av Excel han er på, sa han Excel 2016. Jeg håper det er Office 365-versjonen av 2016, så han har tilgang til det. Vel, hva med et pivottabell? Ok, så jeg opprettet en pivottabell med produkt, og krever, sum av nødvendige mengder og sum av hånden. Deretter, "Analyser", "Felt, gjenstander og sett", "Beregnet felt", og opprettet et nytt beregnet felt kalt "Tilgjengelig", som er Tilgjengelig delt på påkrevd antall - på den måten trenger jeg ikke Helper Column her borte. Først virket det som om det kom til å fungere fordi vi hadde 2, 3 og 4 og rapporterte at minimum er 2 - Jeg endret selvfølgelig denne beregningen til Min,og det virket bra.
Men så, på denne, hvor vi har 2,4,4,1,2, rapporterer den 3. Og det som skjer er at den gjør beregningen på denne raden. Vi har 25 på hånden, delt på 8, det er 3 og en brøkdel, og så rapporterer det 3, og så, nei. En vanlig pivottabellberegningsvare kommer ikke til å fungere. Men konverter i stedet disse dataene til en tabell, og sett deretter inn, pivottabell, legg til disse dataene i datamodellen, klikk OK. Og vi vil ha, nede på venstre side, Produkt og hva det krever. Jeg skal lage to implisitte tiltak her med et påkrevd antall og noe av On Hand, og så skal jeg lage et nytt mål. Så, PowerPivot, Mål, et nytt mål, og dette nye tiltaket vil bli kalt tilgjengelig å selge (availToSell), og den formelen kommer til å være,hvor mange vi har til rådighet delt på hvor mange som kreves for hvert element, og klikk OK. OK, så 8 delt på 4 er 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.
Vel, hei, jeg vil takke deg for at du var innom, vi sees neste gang for en annen Dueling Excel Podcast fra MrExcel og Excel er morsomt.
Last ned fil
Last ned eksempelfilen her: Duel190.xlsx
