Du har en rapport, der viser salg for 16 sælgere. Hver salgsrepræsentant tilhører et team. Hvordan kan du oprette en rapport, der viser det samlede salg for hvert hold?
Se video
- Opret en salgsrapport efter region og team
- Oprindelige data har salgsrepræsentant og region
- Et andet (dårligt formet) bord organiserer salgsrepræsentanterne i teams
- Regningsmetode 1: Omform teamhierarkidataene. Lav begge områder til Ctrl + T-tabeller
- Opret en pivottabel, tilføj dataene til datamodellen. Træk Team fra andet bord.
- Opret et forhold
- Mike Method2: Byg et SUMIFS, hvor Criteria2-feltet er en matrix!
- Giv SUMIFS til SUMPRODUCT-funktionen
- Regningsmetode 3: Omarranger hierarkitabellen, så sælgere er til venstre.
- Føj en VLOOKUP til de originale data
- Byg et pivottabel
- Mike Metode 4: Brug forholdsikonet på fanen Data på båndet
- Når du opretter pivottabellen, skal du vælge Brug denne projektmappes datamodel
- Regningsmetode 5: Power Query. Tilføj opslagstabellen som kun en forbindelse
- Tilføj kun den originale tabel som et opslag
- Flet disse to tabeller, gruppér efter for at producere den endelige rapport
Videoudskrift
Duelling ExcelPodcast, afsnit 188: Salgsteamsrapport efter region.
Bill: Hej. Velkommen tilbage. Det er tid til endnu en Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg får følgeskab af Mike Girvin fra ExcelIsFun. Dette er vores episode 188, Salgsteamrapport efter region.
Okay, så her er spørgsmålet, vi har, et datasæt herover med forskellige salgsrepræsentanter, hvor meget deres salg var efter region, og nogle mennesker har salg i begge regioner, og så har virksomheden organiseret disse 16 salgsrepræsentanter i disse fire salg hold, og vi forsøger at finde ud af for hvert salgsteam, hvor meget indtægter de havde.
I orden. Så min tilgang til dette er, du ved, jeg kan ikke lide dette format her. Jeg vil omarrangere dette format til en slags tabel, et lille hierarki her, der viser for hvert hold, hvem salgsrepræsentanterne er, og så hvis vi er i Excel 2013 eller Excel 2016 ved hjælp af Windows og ikke en Mac , så kan vi gøre brug af datamodellen, og for at gøre dette er vi nødt til at tage hver af disse tabeller og FORMATERE SOM TABEL, som er CONTROL + T. Så der er den første tabel, som de kalder tabel 8, og den anden tabel, som de vil kalde tabel 9. Jeg vil omdøbe disse. Jeg tager den første, og jeg kalder den SALGSBORD, og jeg tager den anden, og jeg kalder det TEAM HIERARCHY, sådan. I orden.
Tjek det nu. Fra og med Excel 2013 opretter vi på fanen INSERT en PIVOT-TABEL fra det første datasæt, men vi siger FØJ DENNE DATA TIL DATAMODELLEN, som er den kedeligste måde at fortælle dig, at du faktisk har Power Pivot-motoren, der sidder bag Excel 2013. Selvom du ikke betaler for Power Pivot, selvom du bare har basisniveauet Excel Office 365 eller Excel, har du det. Okay, så her er vores nye rapport, og hvad jeg vil gøre er, at jeg bestemt vil rapportere af REGION, så der er REGIONERNE, og jeg vil se den samlede SALG, men jeg vil se på dette af salgsteamet. Se lige det her. Jeg vælger ALLE, og det giver mig de andre tabeller i denne gruppe, inklusive TEAM HIERARCHY. Jeg tager TEAMET og flytter det over KOLONNERNE.
Den første ting, der skal ske her, er, at vi får de forkerte svar. Det er meget, meget normalt at få de forkerte svar. Så hvad vi skal gøre er, at vi skal klikke på Opret. Hvis du er i '16, kan du AUTO-DETECT. Lad os lade som om de er i Excel 2013, hvor vi går til vores SALGSBORD. Der er et felt der hedder SALES REP, og det er relateret til HIERARCHY, feltet kaldet SALES REP. Klik på OK, og vi har de rigtige svar. Mike, lad os se, hvad du har.
Mike: Tak. Ja, datamodellen er en fantastisk måde at gå med to forskellige tabeller for at oprette en drejetabel, og det er virkelig min foretrukne metode, men hvis du skulle gøre det med en formel, og du havde brug for at have SALES TEAM øverst i hver kolonne sådan betyder det med formlen, at vi bogstaveligt talt skal kigge igennem dette datasæt, og for hver rekord er jeg nødt til at spørge, er SALES REP = til Gigi eller Chin eller Sandy eller Sheila, og så hvis det er en nettosalg, må jeg sige, og er regionen Nordamerika.
Det kan vi godt. Vi kan lave en OG-logisk test og en ELLER-logisk test i SUMIFS-funktionen. SUM_RANGE, det er alle numrene, så jeg klikker i den øverste celle, KONTROL + SKIFT + NEDSPIL + F4, CRITERIA_RANGE, jeg vil fremhæve hele SALESREP-kolonnen, KONTROL + SKIFT + NEDRING + F4,. Normalt sætter vi normalt en enkelt vare som JUNE SALES REP i kriterier. Det fortæller SUMIFS at spytte et svar til JUNI, men hvis jeg fremhæver 4 forskellige celler - 1 for hver salgsrepræsentant - instruerer vi SUMSIFS om at lave en SUMIF for hver enkelt salgsrepræsentant.
Når jeg nu kopierer denne formel, har jeg brug for den låst, men jeg kopierer den til siden, den skal bevæge sig. Så jeg er nødt til at trykke på F4-tasten 1, 2 gange, låse rækken, men ikke kolonnen. Nu skal jeg). Dette er en funktionsargument array-operation. Det er funktionsargumentet. Det faktum, at vi har flere emner, betyder, at det er en array-operation. Så når jeg klikker i slutningen og rammer F9, adlød SUMIFS os. Det spyttede det samlede beløb for juni, Sioux, Poppi og Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Nu skal vi yderligere begrænse disse beløb ved at tilføje en AND-betingelse. Vi har virkelig brug for, at det skal være juni og Nordamerika eller Sioux og Nordamerika eller Poppi og Nordamerika osv. CONTROL + Z. Vi udvider simpelthen, KRITERIER UDVALG 2. Nu skal vi gennemse REGION-kolonnen. CONTROL + SHIFT + DOWNARROW + F4, og jeg skal klikke på den enkelte betingelse, F4 1, 2, 3 gange for at låse kolonnen, men ikke rækken. Hvis jeg klikker i slutningen og F9, er det totalerne for hver af vores salgsrepræsentanter i Nordamerika. Når vi kopierer det ned, leverer SUMIFS det samlede beløb for hver salgsrepræsentant for Sydamerika. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Bemærk, det er bare SUMIFS, der leverer flere numre, vi skal tilføje. CONTROL + Z. Så jeg kunne sætte det i denne SUM-funktion, men SUM-funktionen NUMMER 1-argumentet beregner ikke denne array-operation korrekt uden at bruge CONTROL + SHIFT + ENTER. Så jeg skal snyde og bruge SUMPRODUCT. Normalt tager SUMPRODUCT normalt flere arrays og multiplicerer dem - det er PRODUKT-delen - og tilføjer dem derefter, men jeg skal bare bruge ARRAY1 og bare bruge SUM-delen af SUMPRODUCT,), CONTROL + ENTER, kopiere det ned og over til siden, og da jeg fik masser af skøre cellehenvisninger, kommer jeg til den sidste i F2, og det er helt sikkert, at alle celler og områder er korrekte. I orden. Jeg kaster tilbage til. (= SUMPRODUKT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Hvad? Det er skørt. Mike. Peg på Mike. IH, du godeste. At sætte en række værdier i SUMIFS og derefter sende det til SUMPRODUCTS og få det til at behandle det som en ARRAY. Hej, det er vildt. Vi skal bare stoppe lige der. Peg på Mike.
I orden. Lad os gå tilbage til min metode, men lad som om du ikke har Excel 2013. Du er tilbage i Excel 2010 eller, værre, Excel til Mac. Jeg mener, det siger, det er Excel. Jeg ved ikke. Det gør mig bare vild, hvad Mac kan eller ikke kan. Så vi skal tage min HIERARCHY TABEL herovre, og fordi VLOOKUP ikke kan se til venstre, vil jeg tage SALES REP informationen, CONTROL + X og indsætte. Ja, jeg ved, at jeg kan indeksere og matche. Jeg er ikke i humør til at lave indeks og matche i dag. Okay, så det er virkelig simpelt. Her, = VLOOKUP, tag det SALESREP navn derovre, og vi vil F4, 2, EXACTMATCHFALSE sådan, dobbeltklik for at kopiere det ned. (= VLOOKUP (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.
I orden. Nå, hej. Jeg vil gerne takke dig for at komme forbi for denne meget lange Dueling Excel Podcast. Vi ses næste gang til endnu en episode fra og ExcelIsFun.
Download fil
Download eksempelfilen her: Duel188.xlsm
