Je hebt een rapport met verkopen voor 16 verkopers. Elke verkoper behoort tot een team. Hoe kunt u een rapport maken met de totale verkoop voor elk team?
Bekijk video
- Stel een verkooprapport op per regio en team
- Originele gegevens hebben een verkoopvertegenwoordiger en regio
- Een tweede (slecht gevormde) tafel organiseert de verkopers in teams
- Factuurmethode 1: de gegevens van de teamhiërarchie opnieuw vormgeven. Maak van beide bereiken Ctrl + T-tabellen
- Maak een draaitabel en voeg de gegevens toe aan het gegevensmodel. Trek Team van de tweede tafel.
- Creëer een relatie
- Mike Method2: Bouw een SUMIFS waarbij het Criteria2-veld een array is!
- Geef de SUMIFS door aan de SUMPRODUCT-functie
- Factuurmethode 3: Herschik de hiërarchietabel zodat de verkoper zich aan de linkerkant bevindt.
- Voeg een VERT.ZOEKEN toe aan de originele gegevens
- Bouw een draaitabel
- Mike Methode 4: Gebruik het pictogram Relatie op het tabblad Gegevens van het lint
- Kies Gebruik het gegevensmodel van deze werkmap wanneer u de draaitabel maakt
- Factuurmethode 5: Power Query. Voeg de opzoektabel toe als Alleen verbinding
- Voeg de originele tabel alleen als zoekopdracht toe
- Voeg deze twee tabellen samen, groepeer ze om het eindrapport te produceren
Videotranscriptie
Duelleren met ExcelPodcast, aflevering 188: rapport van het verkoopteam per regio.
Bill: Hé. Welkom terug. Het is tijd voor nog een Dueling Excel Podcast. Ik ben Bill Jelen van. Ik zal worden vergezeld door Mike Girvin van ExcelIsFun. Dit is onze aflevering 188, Verkoopteamrapport per regio.
Oké, dus hier is de vraag die we hebben, een dataset hier met verschillende verkopers, hoeveel hun verkopen waren per regio, en sommige mensen hebben verkopen in beide regio's, en dan heeft het bedrijf die 16 verkopers georganiseerd in deze vier verkopen. teams, en we proberen voor elk verkoopteam uit te zoeken hoeveel inkomsten ze hadden.
Oké. Dus mijn benadering hiervan is, weet je, ik hou hier niet van dit formaat. Ik ga dat formaat herschikken in een soort van tabel, een kleine hiërarchie hier, die voor elk team laat zien wie de verkoopvertegenwoordigers zijn en dan, indien voorzien, we in Excel 2013 of Excel 2016 zijn met Windows en niet met een Mac , dan kunnen we gebruik maken van het datamodel, en om dit te doen, moeten we elk van deze tabellen en FORMAT AS TABLE nemen, wat CONTROL + T is. Dus er is de eerste tafel die ze Tafel 8 noemen en de tweede tafel die ze Tabel 9 noemen. Ik ga deze hernoemen. Ik ga de eerste nemen en ik ga het VERKOOPTAFEL noemen en ik ga de tweede nemen en ik ga het TEAM HIËRARCHIE noemen, zo. Oké.
Kijk dit nu eens. Beginnend in Excel 2013, maken we op het tabblad INSERT een DRAAITABEL van de eerste gegevensset, maar we zeggen: VOEG DEZE GEGEVENS TOE AAN HET GEGEVENSMODEL, wat de saaiste manier is om u te laten weten dat de Power Pivot-engine daadwerkelijk achter Excel zit 2013. Zelfs als u niet betaalt voor Power Pivot, zelfs als u alleen het basisniveau Excel Office 365 of Excel hebt, heeft u dat. Oké, dus hier is ons nieuwe rapport en wat ik ga doen is dat ik zeker wil rapporteren op REGIO, dus er zijn de REGIO'S, en ik wil de totale VERKOOP zien, maar ik wil dit bekijken per verkoopteam. Kijk hier eens naar. Ik ga ALLE kiezen en dat geeft me de andere tafels in deze groep, inclusief TEAM HIËRARCHIE. Ik neem het TEAM en verplaats het over de KOLOMMEN.
Nu, het eerste dat hier gaat gebeuren, is dat we de verkeerde antwoorden krijgen. Dat is heel normaal om de verkeerde antwoorden te krijgen. Dus wat we gaan doen, is klikken op MAKEN. Als je in '16 bent, kun je AUTOMATISCH DETECTEREN. Laten we doen alsof ze in Excel 2013 zijn, waar we naar onze VERKOOPTAFEL gaan. Er is daar een veld met de naam SALES REP en het is gerelateerd aan de HIËRARCHIE, een veld met de naam SALES REP, klik op OK en we hebben de juiste antwoorden. Mike, laten we eens kijken wat je hebt.
Mike: Bedankt. Ja, het datamodel is een geweldige manier om met twee verschillende tabellen een draaitabel te bouwen en dat is echt mijn voorkeursmethode, maar als je het met een formule moest doen en je VERKOOPTEAM bovenaan elke kolom moest hebben op deze manier, dat betekent dat we met de formule letterlijk door deze dataset moeten kijken en, voor elk record, moet ik vragen, is de SALES REP = naar Gigi of Chin of Sandy of Sheila, en dan, als het een netto verkoop, moet ik zeggen, en is de regio Noord-Amerika.
Nou, dat kunnen we doen. We kunnen een logische AND-test en een logische OR-test doen in de SUMIFS-functie. SUM_RANGE, dat zijn alle cijfers, dus ik ga in de bovenste cel klikken, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, ik ga de hele SALESREP-kolom markeren, CONTROL + SHIFT + DOWNARROW + F4,. Nu plaatsen we normaal gesproken een enkel item zoals JUNI SALES REP in criteria. Dat vertelt SUMIFS om één antwoord uit te spugen voor JUNI, maar als ik 4 verschillende cellen markeer - 1 voor elke verkoper - geven we SUMSIFS de opdracht om een SUMIF te maken voor elke individuele verkoper.
Als ik deze formule nu kopieer, moet ik hem vergrendelen, maar ik kopieer hem naar de zijkant, hij moet worden verplaatst. Dus ik moet de F4-toets 1, 2 keer indrukken, de rij vergrendelen, maar niet de kolom. Nu ga ik). Dit is een array-bewerking van een functieargument. Dat is het functieargument. Het feit dat we meerdere items hebben, betekent dat het een array-bewerking is. Dus toen ik aan het einde klik en op F9 druk, gehoorzaamde SUMIFS ons. Het spuugde het totale bedrag uit voor juni, Sioux, Poppi en Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Nu moeten we die bedragen verder beperken door een EN-voorwaarde toe te voegen. We hebben het echt nodig als juni en Noord-Amerika of Sioux en Noord-Amerika of Poppi en Noord-Amerika, enzovoort. CONTROL + Z. We breiden gewoon CRITERIABEREIK 2 uit. Nu moeten we door de kolom REGION kijken. CONTROL + SHIFT + PIJL-OMLAAG + F4, en ik klik 1, 2, 3 keer op de enkele voorwaarde, F4, om de kolom te vergrendelen, maar niet de rij. Als ik aan het einde en F9 klik, zijn dat de totalen voor elk van onze verkopers in Noord-Amerika. Wanneer we het kopiëren, levert SUMIFS het totaal voor elke verkoper voor Zuid-Amerika. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Merk op dat het alleen SUMIFS zijn die meerdere nummers leveren die we moeten toevoegen. CONTROL + Z. Dus ik zou het in deze SOM-functie kunnen plaatsen, maar het SOM-functie NUMBER 1-argument zal deze array-bewerking niet correct berekenen zonder CONTROL + SHIFT + ENTER te gebruiken. Dus ik ga vals spelen en SUMPRODUCT gebruiken. Normaal gesproken neemt SUMPRODUCT meerdere arrays en vermenigvuldigt ze - dat is het PRODUCT-gedeelte - en voegt ze vervolgens toe, maar ik ga gewoon ARRAY1 gebruiken en gewoon het SUM-gedeelte van SUMPRODUCT gebruiken,), CONTROL + ENTER, kopieer het naar beneden en naar de zijkant, en aangezien ik veel gekke celverwijzingen heb, ga ik naar de laatste in F2 en, ja hoor, alle cellen en bereiken zijn correct. Oké. Ik ga terug naar. (= SOMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Wat? Dat is gek. Mike. Wijs naar Mike. Oh mijn god. Een reeks waarden in SUMIFS plaatsen en deze vervolgens naar SUMPRODUCTS verzenden en het als een ARRAY laten behandelen. Hé, dat is wild. We moeten daar gewoon stoppen. Wijs naar Mike.
Oké. Laten we teruggaan naar mijn methode, maar net doen alsof je geen Excel 2013 hebt. Je bent terug in Excel 2010 of, erger nog, Excel voor de Mac. Ik bedoel, er staat dat het Excel is. Ik weet het niet. Het maakt me gewoon gek wat de Mac wel of niet kan. Dus we gaan mijn HIËRARCHIE TABEL hier nemen, en omdat VERT.ZOEKEN niet naar links kan kijken, ga ik de VERKOOPVERSLAG informatie, CONTROL + X, nemen en plakken. Ja, ik weet dat ik index en match kan doen. Ik ben niet in de stemming om vandaag index en match te doen. Oké, dus het is heel simpel. Hier, = VERT.ZOEKEN, neem die SALESREP-naam daar, en we zullen F4, 2, EXACTMATCHFALSE zo, dubbelklik om dat naar beneden te kopiëren. (= VERT.ZOEKEN (A4, $ F $ 4: $ G $ 19,2, FALSE))
Now that we have all this data back in one table, simple little INSERT, PIVOT TABLE. Even if you don't have the check box at this stage of the data model, we can build our report with SALES TEAM going across, REGION going down, and SALES like that. You can even, here, let's reverse these, REGION across and add the SALES REP in like that, in case you want to see who the sales reps were, and if, by default, if you don't want that, we could just collapse the whole group. So, from here, I go to the ANALYZE tab and collapse. Alright. So, there's our sales teams by region, and then, if someone wants to say, well, who was SALES TEAM 2, we can open that up individually, something like that. Mike, you got another one?
Mike: Still got to love VLOOKUP. It does so many amazing things and, yes, I agree with you. Excel for the Mac, that's not even Excel, is it? Alright. Okay. I have another method but I'm going to have to jump over to a different workbook. So, I just have the same two data sets and I've converted them to Excel tables and named them. There's the SALES table, there's the TEAM table, and I like your Power Pivot option so much I'm going to steal that but do it a slightly different way because, as you say, if you have Excel 2013 or later, you have the Power Pivot data model there, but it gets even better. On the DATA ribbon tab -- and I have Excel 2016 -- if you have the RELATIONSHIPS button, you can just build the relationship as if it was a VLOOKUP between these two tables and it will automatically send it to the data model.
So, here's the MANAGE RELATIONSHIPS. I'm going to click NEW. I'm going to select SALES table, SALES REP. This, in essence, is our lookup value, right, and then I'm going to select the lookup table dTEAM, and the SALES REP. This is the lookup table so it can look up SALES REP and return the SALES TEAM, but there's no VLOOKUP column. It simply is two tables in our pivot table field list. Yeah. Look at that, the relationships, when I click OK, it’s sending it to the data model.
Now I'm going to click in a cell off to the side ALT+N+V to open up CREATE PIVOTTABLE dialog box and -- look at that -- it already assumes I want the data model because there's stuff in the data model. Now I click OK and I have my two tables right there. I'm going to click the drop-down, SALES TEAM to ROW, SALES REP down below ROWS, and then SALES from the SALES TABLE down to VALUES. ROW LABELS. I don't like that so I'm going to go up to SHOW IN TABULAR, right click, NUMBER FORMATTING, something like CURRENCY, click OK.
Now, just as said, we can collapse this if we do not want to see the SALES REP, and then drag REGION down to COLUMNS, and, just like that, we have all of our sales teams’ totals for each region. I could even open this up. Whether you access the data model either through the check box in CREATE PIVOTTABLES dialog box or simply DATA, RELATIONSHIPS, that is the way to go. So fast and easy, and we can pull fields from two different tables. Alright, I'm going to throw it back to.
Bill: Whoa, Mike, the RELATIONSHIPS out here on the DATA tab, I'm sure I've never noticed that and I guess, in my defense, in the smaller version of Excel here, it doesn't have a word on it. Just looks like a tiny little icon and I realize it was new. That is super, super cool.
Alright. Let’s just do one more here. I'm going to use power query. So, on the DATA tab, GET & TRANSFORM DATA. FROM A TABLE, I select the first table, and I want to take this REGION field and I'm going to pivot it, so I'm going to create a pivot table right here in power query. I'd be careful here. The values are in the SALES area. Click OK. So, now, for each SALES REP, we have their sales to NORTH AMERICA and SOUTH AMERICA, and I'm going to call this ByRep. BYREP. I'll call it BYREP, and then HOME, CLOSE & LOAD, but I'm not going to CLOSE & LOAD to the workbook. I'm going to say ONLY CREATE CONNECTION, like that.
Alright. Then, I'll come to the second one and say that I'm going to create a query FROM A TABLE, alright, and this is just going to stay exactly the way it is. We'll call this TEAMS, and CLOSE & LOAD, CLOSE & LOAD to ONLY CREATE CONNECTION, like that.
Alright. So, now we have two different reports here and I'm going to say that I want to create a COMBINE QUERY, a MERGE QUERY, and my first query is going to be called BYREP, and then I'm going to look up into the TEAMS query. Now, this part is the part that is not intuitive at all. Click on SALES REP here, click on SALES REP here, and we want ALL FROM FIRST, MATCHING FROM SECOND. Click OK. Alright. So, now, here's all of our SALES REP information, what they sold in North America, what they sold in South America, and use the expand icon here, and all we want to get is the TEAM information. I just want to call it TEAM. I don't want to call it TEAMS.TEAM. That would be crazy.
Alright. At this point, we no longer need the SALES REP information. I'll remove that column. I'll take the TEAM and move it over to the left, and then -- check this out -- GROUP BY. We're going to GROUP BY the TEAM and the NEW COLUMN NAME is going to be called NORTH AMERICA, the OPERATION is going to be SUM, the NORTH AMERICA COLUMN, and then we'll add a second one called SOUTH AMERICA, SUM, the SOUTH AMERICA COLULMN. There we go. GROUP BY TEAM, two columns, and we have our information here.
Let's order this. So, on the HOME tab, we want to SORT A to Z. SALES TEAM 1, 2, 3, 4. There's our NORTH AMERICA. There's our SOUTH AMERICA. Now, finally, we'll CLOSE & LOAD and we have our results, and -- check this out -- that's even cooler than that. So, if I go back to BILLPQ and we take POPPI and we move POPPI to SALES TEAM 2 and then come back to our results out here, alright, so, SALES TEAM 2, we should see these numbers increase. Come here and click the refresh icon, and those numbers changed, right? How cool? How cool is that?
Alright, so, wrap up. The goal today, we're going to build a sales report by region and team. The original data has sales rep in region and then there's a lookup table -- in my opinion, badly shaped -- that organizes sales reps into teams. So, my method reshaped that data into a team hierarchy data, make both ranges into Control+T tables, create a pivot table, adding the data to the data model, and then create a relationship. Mike’s method: use SUMIFS where the Criteria2 to field is an array -- didn’t know you could do that -- and then the SUMPRODUCT function. My third method: rearrange the hierarchy table so sales rep is on the left and then do a VLOOKUP building a pivot table. Mike's method: use the RELATIONSHIP icon to build a relationship first, and then a pivot table from the workbook data model. And then, the fifth version -- the no VLOOKUP-no pivot table version in case you're afraid of both of those -- power query. Add the lookup table as a connection only, add the original table as a lookup only, doing the pivot right there to get North America and South America, merge those two tables, group by, and then group by within power query, and you can refresh.
Oké. Hallo daar. Ik wil je bedanken voor het langskomen voor deze zeer lange Dueling Excel Podcast. We zien je de volgende keer voor een nieuwe aflevering van en ExcelIsFun.
Download bestand
Download het voorbeeldbestand hier: Duel188.xlsm
