Salg etter region og team - Excel-tips

Du har en rapport som viser salg for 16 selgere. Hver selger tilhører et team. Hvordan kan du lage en rapport som viser totalt salg for hvert lag?

Se på video

Bygg en salgsrapport etter region og team
Originaldata har selger og region
En annen (dårlig formet) tabell organiserer salgsrepresentanter i team
Regningsmetode 1: Gjør om laghierarkidataene. Lag begge områdene til Ctrl + T-tabeller
Lag en pivottabell, og legg til dataene i datamodellen. Trekk Team fra andre bord.
Lag et forhold
Mike Method2: Bygg et SUMIFS der Criteria2-feltet er en matrise!
Gi SUMIFS til SUMPRODUCT-funksjonen
Regningsmetode 3: Omorganiser hierarkitabellen slik at selgeren er til venstre.
Legg til en VLOOKUP i de opprinnelige dataene
Bygg et pivottabell
Mike Method 4: Bruk Relationship-ikonet i Data-fanen på båndet
Når du oppretter pivottabellen, velger du Bruk denne arbeidsbokens datamodell
Bill Method 5: Power Query. Legg til oppslagstabellen som kun tilkobling
Legg til den originale tabellen bare som et oppslag
Slå sammen de to tabellene, gruppere etter for å lage den endelige rapporten

Videoutskrift

Dueling ExcelPodcast, episode 188: Salgsteamrapport etter region.

Bill: Hei. Velkommen tilbake. Det er på tide med nok en Dueling Excel Podcast. Jeg er Bill Jelen fra. Jeg får følge av Mike Girvin fra ExcelIsFun. Dette er vår episode 188, Salgsteamrapport etter region.

OK, så her er spørsmålet vi har, et datasett her med forskjellige selgere, hvor mye deres salg var etter region, og noen mennesker har salg i begge regioner, og så har selskapet organisert de 16 selgerne i disse fire salgene team, og vi prøver å finne ut hvor store inntekter de hadde for hvert salgsteam.

Ok. Så, min tilnærming til dette er, vet du, jeg liker ikke dette formatet her. Jeg skal omorganisere det formatet til en slags tabell, et lite hierarki her, som viser for hvert team hvem selgerne er, og så hvis vi er i Excel 2013 eller Excel 2016 ved hjelp av Windows og ikke en Mac , så kan vi bruke datamodellen, og for å gjøre dette, må vi ta hver av disse tabellene og FORMATERE SOM TABELL som er CONTROL + T. Så det er den første tabellen som de kaller tabell 8 og den andre tabellen som de vil kalle tabell 9. Jeg skal gi dem nytt navn. Jeg skal ta den første, og jeg vil kalle den SALGSBORD, og jeg skal ta den andre, og jeg skal kalle den TEAM HIERARCHY, sånn. Ok.

Nå, sjekk dette ut. Fra og med Excel 2013, på INSERT-fanen, lager vi en PIVOT-TABELL fra det første datasettet, men vi sier LEGG TIL DATA I DATAMODELLEN som er den kjedeligste måten å fortelle deg at du faktisk har Power Pivot-motoren som sitter bak Excel 2013. Selv om du ikke betaler for Power Pivot, selv om du bare har grunnnivået Excel Office 365 eller Excel, har du det. Ok, så her er vår nye rapport, og det jeg skal gjøre er at jeg definitivt vil rapportere av REGION, så det er REGIONENE, og jeg vil se den totale SALGEN, men jeg vil se på dette av salgsteamet. Sjekk ut dette. Jeg skal velge ALT og det gir meg de andre bordene i denne gruppen, inkludert TEAM HIERARCHY. Jeg tar teamet og flytter det over KOLONNENE.

Nå, det første som kommer til å skje her er at vi får feil svar. Det er veldig, veldig normalt å få feil svar. Så det vi skal gjøre er at vi klikker på OPPRETT. Hvis du er i '16, kan du AUTO-DETECT. La oss late som de er i Excel 2013, hvor vi går til SALGSBORDEN. Det er et felt der som heter SALES REP og det er relatert til HIERARCHY, feltet som heter SALES REP, klikk OK, og vi har de riktige svarene. Mike, la oss se hva du har.

Mike: Takk. Ja, datamodellen er en fantastisk måte å gå med to forskjellige tabeller for å bygge en pivottabell, og det er virkelig min foretrukne metode, men hvis du måtte gjøre det med en formel og du trengte å ha SALGSTeam øverst i hver kolonne som dette, betyr det at med formelen må vi bokstavelig talt se gjennom dette datasettet, og for hver post, må jeg spørre, er SALGSREP = til Gigi eller Chin eller Sandy eller Sheila, og hvis det er en nettosalg, må jeg si, og er regionen Nord-Amerika.

Vel, vi kan gjøre det. Vi kan gjøre en OG-logisk test og en ELLER-logisk test i SUMIFS-funksjonen. SUM_RANGE, det er alle tallene, så jeg skal klikke i den øverste cellen, KONTROLL + SKIFT + NED + F4, CRITERIA_RANGE, jeg skal fremheve hele SALESREP-kolonnen, KONTROLL + SKIFT + NEDRING + F4,. Normalt setter vi en enkelt vare som JUNE SALES REP i kriterier. Det forteller SUMIFS å spytte ut ett svar for JUNI, men hvis jeg markerer 4 forskjellige celler - 1 for hver salgsrepresentant - instruerer vi SUMSIFS om å gjøre en SUMIF for hver enkelt salgsrepresentant.

Nå, når jeg kopierer denne formelen, trenger jeg den låst, men jeg kopierer den til siden, den må bevege seg. Så jeg må trykke F4-tasten 1, 2 ganger, låse raden, men ikke kolonnen. Nå skal jeg). Dette er en funksjonsargument array operasjon. Det er funksjonsargumentet. Det faktum at vi har flere elementer betyr at det er en array-operasjon. Så når jeg klikker på slutten og slår F9, fulgte SUMIFS oss. Det spyttet ut det totale beløpet for juni, Sioux, Poppi og Tyrone. (= SUMMER ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))

Nå må vi begrense disse beløpene ytterligere ved å legge til en AND-betingelse. Vi trenger virkelig at det skal være juni og Nord-Amerika eller Sioux og Nord-Amerika eller Poppi og Nord-Amerika, og så videre. CONTROL + Z. Vi utvider ganske enkelt, CRITERIA RANGE 2. Nå må vi se gjennom REGION-kolonnen. CONTROL + SHIFT + DOWNARROW + F4, og jeg skal klikke på enkeltbetingelsen, F4 1, 2, 3 ganger for å låse kolonnen, men ikke raden. Hvis jeg klikker på slutten og F9, er det totalene for hver av våre salgsrepresentanter i Nord-Amerika. Når vi kopierer den ned, vil SUMIFS levere summen for hver selger for Sør-Amerika. (= SUMMER ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))

Legg merke til at det bare er SUMIFS som leverer flere tall vi trenger å legge til. CONTROL + Z. Så jeg kan sette den inn i denne SUM-funksjonen, men SUM-funksjonen NUMMER 1-argumentet vil ikke beregne denne array-operasjonen riktig uten å bruke CONTROL + SHIFT + ENTER. Så jeg skal jukse og bruke SUMPRODUCT. Normalt tar SUMPRODUCT flere matriser og multipliserer dem - det er PRODUKT-delen - og legger dem til, men jeg skal bare bruke ARRAY1 og bare bruke SUM-delen av SUMPRODUCT,), CONTROL + ENTER, kopiere den ned og over til siden, og siden jeg fikk mange sprø cellehenvisninger, kommer jeg til den siste i F2, og sikkert, det har alle cellene og områdene riktig. Ok. Jeg skal kaste tilbake til. (= SUMPRODUKT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))

Bill: Hva? Det er sinnsykt. Mike. Pek på Mike. Herregud. Å sette en rekke verdier i SUMIFS og deretter sende den til SUMPRODUCTS og få den til å behandle den som en ARRAY. Hei, det er vilt. Vi burde bare stoppe akkurat der. Pek på Mike.

Ok. La oss gå tilbake til metoden min, men later som om du ikke har Excel 2013. Du er tilbake i Excel 2010 eller, verre, Excel for Mac. Jeg mener, det står at det er Excel. Jeg vet ikke. Det gjør meg bare gal hva Mac kan eller ikke kan. Så vi skal ta HIERARCHY-TABELLEN min hit, og fordi VLOOKUP ikke kan se til venstre, skal jeg ta informasjon om SALGSREP, CONTROL + X og lime inn. Ja, jeg vet at jeg kan gjøre indeks og matche. Jeg er ikke i humør til å gjøre indeks og matche i dag. Ok, så det er veldig enkelt. Her, = VLOOKUP, ta det SALESREP-navnet der borte, og vi vil F4, 2, EXACTMATCHFALSE slik, dobbeltklikk for å kopiere det ned. (= VLOOKUP (A4, $ F $ 4: $ G $ 19,2, FALSE))

Nå som vi har alle disse dataene tilbake i en tabell, enkel liten INSERT, PIVOT TABLE. Selv om du ikke har avkrysningsruten på dette stadiet av datamodellen, kan vi bygge vår rapport med SALES TEAM som går over, REGION går ned og SALES slik. Du kan til og med her, la oss reversere disse, REGION på tvers og legge til SALGSREPPEN slik, i tilfelle du vil se hvem selgerne var, og hvis du som standard ikke vil ha det, kan vi bare kollaps hele gruppen. Så herfra går jeg til ANALYSE-fanen og kollapser. Ok. Så det er salgsteamene våre etter region, og hvis noen vil si hvem som var SALGSTeam 2, kan vi åpne det individuelt, noe sånt. Mike, har du en til?

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. Vel hei. Jeg vil takke deg for at du kom innom for denne veldig lange Dueling Excel Podcast. Vi sees neste gang for en ny episode fra og ExcelIsFun.

Last ned fil

Last ned eksempelfilen her: Duel188.xlsm