Du har en rapport som visar försäljning för 16 säljare. Varje säljare tillhör ett team. Hur kan du skapa en rapport som visar den totala försäljningen för varje lag?
Kolla på video
- Bygg en försäljningsrapport efter region och team
- Originaldata har säljare och region
- En andra (dåligt formad) tabell organiserar säljare i team
- Faktureringsmetod 1: Forma om laghierarkidata. Gör båda intervallen till Ctrl + T-tabeller
- Skapa en pivottabell och lägg till data i datamodellen. Dra Team från andra bordet.
- Skapa en relation
- Mike Method2: Bygg en SUMIFS där Criteria2-fältet är en matris!
- Skicka SUMIFS till SUMPRODUCT-funktionen
- Fakturametod 3: Ordna om hierarkitabellen så att säljare finns till vänster.
- Lägg till en VLOOKUP till originaldata
- Bygg ett pivottabell
- Mike Metod 4: Använd ikonen Förhållande på fliken Data i menyfliksområdet
- När du skapar pivottabellen väljer du Använd den här arbetsbokens datamodell
- Bill Method 5: Power Query. Lägg till uppslagstabellen som enbart anslutning
- Lägg till originaltabellen endast som en uppslagning
- Slå ihop dessa två tabeller, gruppera efter för att ta fram slutrapporten
Videoutskrift
Duelling ExcelPodcast, avsnitt 188: Säljteamsrapport efter region.
Bill: Hej. Välkommen tillbaka. Det är dags för ytterligare en Duelling Excel Podcast. Jag är Bill Jelen från. Jag får sällskap av Mike Girvin från ExcelIsFun. Detta är vårt avsnitt 188, Säljteamsrapport efter region.
Okej, så här är frågan vi har, en dataset här med olika säljare, hur mycket deras försäljning var per region, och vissa människor har försäljning i båda regionerna, och sedan har företaget organiserat de 16 säljare i dessa fyra försäljningar lag, och vi försöker ta reda på, för varje säljteam, hur mycket intäkter de hade.
OK. Så min inställning till detta är, du vet, jag gillar inte det här formatet här. Jag ska ordna om det formatet till någon form av en tabell, en liten hierarki här, som visar för varje team vem säljare är och sedan, om vi är i Excel 2013 eller Excel 2016 med Windows och inte en Mac , då kan vi använda datamodellen, och för att göra detta måste vi ta var och en av dessa tabeller och FORMATERA SOM TABELL som är CONTROL + T. Så det finns den första tabellen som de kallar tabell 8 och den andra tabellen som de kommer att kalla tabell 9. Jag ska byta namn på dessa. Jag ska ta den första och jag kommer att kalla den FÖRSÄLJNINGSTABELL och jag kommer att ta den andra och jag kommer att kalla den TEAM HIERARCHY, så. OK.
Kolla nu det här. Från och med Excel 2013, på INSERT-fliken, skapar vi en PIVOT-TABELL från den första datamängden men vi säger LÄGG TILL DENNA DATA TILL DATAMODELLEN vilket är det tråkigaste sättet att meddela att du faktiskt har Power Pivot-motorn som sitter bakom Excel 2013. Även om du inte betalar för Power Pivot, även om du bara har basnivån Excel Office 365 eller Excel, har du det. Okej, så här är vår nya rapport och vad jag ska göra är att jag definitivt vill rapportera av REGION, så det finns REGIONER, och jag vill se den totala FÖRSÄLJNINGEN men jag vill titta på detta av säljteamet. Kolla in det här. Jag ska välja ALLA och det ger mig de andra tabellerna i denna grupp, inklusive TEAM HIERARCHY. Jag tar teamet och flyttar det över KOLONNEN.
Det första som kommer att hända här är att vi får fel svar. Det är väldigt, mycket normalt att få fel svar. Så vad vi ska göra är att vi klickar på CREATE. Om du är i '16, kan du AUTO-DETECT. Låt oss låtsas att de är i Excel 2013 där vi går till vår FÖRSÄLJNINGSTABELL. Det finns ett fält som heter SALES REP och det är relaterat till HIERARCHY, fält som heter SALES REP, klicka på OK och vi har rätt svar. Mike, låt oss se vad du har.
Mike: Tack. Ja, datamodellen är ett fantastiskt sätt att gå med två olika tabeller för att bygga en pivottabell och det är verkligen min föredragna metod, men om du var tvungen att göra det med en formel och du måste ha SALES TEAM högst upp i varje kolumn så här betyder det, med formeln måste vi bokstavligen titta igenom den här datauppsättningen och för varje post, måste jag fråga, är FÖRSÄLJNINGSREP = till Gigi eller Chin eller Sandy eller Sheila, och sedan, om det är en nettoförsäljning, måste jag säga, och är regionen Nordamerika.
Vi kan göra det. Vi kan göra ett OCH-logiskt test och ett ELLER-logiskt test i SUMIFS-funktionen. SUM_RANGE, det är alla siffror, så jag klickar i den översta cellen, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, jag ska markera hela SALESREP-kolumnen, CONTROL + SHIFT + DOWNARROW + F4,. Normalt sätter vi en enda artikel som JUNE SALES REP i kriterier. Det säger till SUMIFS att spotta ut ett svar för JUNI, men om jag markerar fyra olika celler - 1 för varje säljare - instruerar vi SUMSIFS att göra en SUMIF för varje enskild säljare.
Nu när jag kopierar den här formeln behöver jag den låst, men jag kopierar den åt sidan, den måste flyttas. Så jag måste slå F4-tangenten 1, 2 gånger, låsa raden, men inte kolumnen. Nu ska jag). Detta är en funktionsargumentmatningsoperation. Det är funktionsargumentet. Det faktum att vi har flera objekt betyder att det är en array-operation. Så när jag klickar på slutet och slår F9, följde SUMIFS oss. Det spottade ut det totala beloppet för juni, Sioux, Poppi och Tyrone. (= SUMMER ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Nu måste vi ytterligare begränsa dessa belopp genom att lägga till ett AND-villkor. Vi behöver verkligen att det är juni och Nordamerika eller Sioux och Nordamerika eller Poppi och Nordamerika, och så vidare. CONTROL + Z. Vi utökar helt enkelt, CRITERIA RANGE 2. Nu måste vi titta igenom REGION-kolumnen. CONTROL + SHIFT + DOWNARROW + F4, och jag klickar på det enda villkoret, F4 1, 2, 3 gånger för att låsa kolumnen men inte raden. Om jag klickar på slutet och F9 är det summan för var och en av våra säljare i Nordamerika. När vi kopierar ner det kommer SUMIFS att leverera summan för varje säljare för Sydamerika. (= SUMMER ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Lägg märke till att det bara är SUMIFS som levererar flera nummer vi behöver lägga till. CONTROL + Z. Så jag skulle kunna lägga in den i den här SUM-funktionen men SUM-funktionen NUMMER 1 kommer inte att beräkna denna array-operation korrekt utan att använda CONTROL + SHIFT + ENTER. Så jag ska fuska och använda SUMPRODUCT. Normalt tar SUMPRODUCT normalt flera matriser och multiplicerar dem - det är PRODUCT-delen - och lägger sedan till dem, men jag ska bara använda ARRAY1 och bara använda SUM-delen av SUMPRODUCT,), CONTROL + ENTER, kopiera det ner och över till sidan, och eftersom jag har massor av galna cellreferenser, kommer jag att komma till den sista i F2 och, nog, det har alla celler och intervall rätt. OK. Jag ska kasta tillbaka till. (= SUMPRODUKT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Vad? Det är galet. Mikrofon. Peka på Mike. Herregud. Att sätta ett antal värden i SUMIFS och sedan skicka det till SUMPRODUCTS och få det att behandla det som en ARRAY. Hej, det är vildt. Vi borde bara sluta där. Peka på Mike.
OK. Låt oss gå tillbaka till min metod men låtsas att du inte har Excel 2013. Du är tillbaka i Excel 2010 eller, ännu värre, Excel för Mac. Jag menar, det står att det är Excel. jag vet inte. Det gör mig bara galen vad Mac kan eller inte kan göra. Så vi ska ta min HIERARCHY-BORD här, och eftersom VLOOKUP inte kan se till vänster kommer jag att ta information om REA REP, CONTROL + X och klistra in. Ja, jag vet att jag kan indexera och matcha. Jag är inte på humör att göra index och matcha idag. Okej, så det är väldigt enkelt. Här, = VLOOKUP, ta det SALESREP-namnet där borta, så kommer vi F4, 2, EXACTMATCHFALSE så, dubbelklicka för att kopiera det ner. (= 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.
OK. Men Hej. Jag vill tacka dig för att du kom in för denna mycket långa Duelling Excel Podcast. Vi ses nästa gång för ett nytt avsnitt från och ExcelIsFun.
Nedladdning fil
Ladda ner exempelfilen här: Duel188.xlsm
