Aveți un raport care arată vânzările pentru 16 reprezentanți de vânzări. Fiecare reprezentant de vânzări aparține unei echipe. Cum puteți crea un raport care să arate vânzările totale pentru fiecare echipă?
Urmăriți videoclipul
- Construiți un raport de vânzări în funcție de regiune și de echipă
- Datele originale au reprezentant de vânzări și regiune
- Un al doilea tabel (prost format) organizează reprezentanții vânzărilor către echipe
- Metoda de facturare 1: reformați datele ierarhiei echipei. Faceți ambele intervale în tabele Ctrl + T
- Creați un tabel pivot, adăugând datele la modelul de date. Trageți echipa de la a doua masă.
- Creați o relație
- Mike Method2: Construiți un SUMIFS unde câmpul Criteria2 este o matrice!
- Treceți sumele la funcția SUMPRODUCT
- Metoda facturii 3: rearanjați tabelul ierarhic, astfel încât reprezentantul vânzărilor să fie în stânga.
- Adăugați un VLOOKUP la datele originale
- Construiți un tabel pivot
- Mike Metoda 4: Utilizați pictograma Relație de pe fila Date a panglicii
- Când creați tabelul pivot, alegeți Folosiți modelul de date al acestui registru de lucru
- Metoda facturii 5: interogare de putere. Adăugați tabelul de căutare numai ca conexiune
- Adăugați tabelul original doar ca căutare
- Combinați aceste două tabele, grupați-le pentru a produce raportul final
Transcriere video
Dueling ExcelPodcast, episodul 188: Raportul echipei de vânzări pe regiuni.
Bill: Hei. Bine ai revenit. Este timpul pentru un alt podcast Dueling Excel. Sunt Bill Jelen din. Mi se va alătura Mike Girvin de la ExcelIsFun. Acesta este episodul nostru 188, Raportul echipei de vânzări pe regiuni.
Bine, deci, iată întrebarea pe care o avem, un set de date aici cu diferiți reprezentanți de vânzări, cât au fost vânzările lor pe regiuni și unii oameni au vânzări în ambele regiuni, iar apoi compania a organizat aceste 16 reprezentanți de vânzări în aceste patru vânzări echipe și încercăm să ne dăm seama, pentru fiecare echipă de vânzări, cât de mult au venit.
Bine. Deci, abordarea mea în acest sens este, știi, nu îmi place acest format aici. Voi rearanja formatul respectiv într-un fel de tabel, o mică ierarhie aici, care arată pentru fiecare echipă cine sunt reprezentanții vânzărilor și apoi, dacă sunt furnizați, suntem în Excel 2013 sau Excel 2016 folosind Windows și nu un Mac , atunci putem folosi modelul de date și, pentru a face acest lucru, trebuie să luăm fiecare dintre aceste tabele și FORMATAȚI CA TABEL care este CONTROL + T. Deci, există primul tabel pe care îl numesc Tabelul 8 și al doilea tabel pe care îl vor numi Tabelul 9. Voi redenumi acestea. O voi lua pe prima și o voi numi TABEL DE VÂNZĂRI și o voi lua pe a doua și o voi numi IERARHIE DE ECHIPĂ, așa. Bine.
Acum, verificați acest lucru. Începând din Excel 2013, în fila INSERT, creăm un TABEL PIVOT din primul set de date, dar spunem ADĂUGAȚI ACESTE DATE LA MODELUL DE DATE, care este cel mai plictisitor mod de a vă informa că aveți de fapt motorul Power Pivot așezat în spatele Excel 2013. Chiar dacă nu plătiți pentru Power Pivot, chiar dacă aveți doar nivelul de bază Excel Office 365 sau Excel, aveți acest lucru. Bine, deci, iată noul nostru raport și ceea ce voi face este cu siguranță că vreau să raportez în funcție de REGIUNE, deci există REGIUNI și vreau să văd VÂNZĂRILE totale, dar vreau să analizez acest lucru de către echipa de vânzări. Verificați acest lucru. Voi alege TOATE și asta îmi oferă celelalte tabele din acest grup, inclusiv IERARHIA ECHIPEI. Voi lua ECHIPA și o voi muta peste COLOANE.
Acum, primul lucru care se va întâmpla aici este că primim răspunsuri greșite. Este foarte, foarte normal să obții răspunsuri greșite. Deci, ceea ce vom face este să facem clic pe CREARE. Dacă sunteți în '16, puteți AUTO-DETECTA. Să ne prefacem că sunt în Excel 2013, unde mergem la TABELUL DE VÂNZĂRI. Există un câmp numit SALES REP și este legat de IERARHIE, câmp numit SALES REP, faceți clic pe OK și avem răspunsurile corecte. Mike, hai să vedem ce ai.
Mike: Mulțumesc. Da, modelul de date este o modalitate minunată de a merge cu două tabele diferite pentru a construi un tabel pivot și aceasta este cu adevărat metoda mea preferată, dar dacă ar fi trebuit să o faci cu o formulă și ar fi trebuit să ai SALES TEAM în partea de sus a fiecărei coloane așa, asta înseamnă, cu formula, trebuie literalmente să căutăm prin acest set de date și, pentru fiecare înregistrare, trebuie să întreb, este REPREZENTUL DE VÂNZĂRI = către Gigi sau Chin sau Sandy sau Sheila, și apoi, dacă este un vânzare netă, trebuie să spun, și este regiunea Americii de Nord.
Ei bine, putem face asta. Putem face un test logic ȘI un test logic SAU în funcția SUMIFS. SUM_RANGE, acestea sunt toate numerele, așa că voi face clic în celula de sus, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, voi evidenția întreaga coloană SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Acum, în mod normal, punem un singur element precum REP. VÂNZĂRI IUNIE în criterii. Asta îi spune SUMIFS să scuipe un răspuns pentru IUNIE, dar, dacă evidențiez 4 celule diferite - 1 pentru fiecare reprezentant de vânzări - îi instruim SUMSIFS să facă un SUMIF pentru fiecare reprezentant de vânzări individual.
Acum, când copiez această formulă, am nevoie de ea blocată, dar o copiez lateral, trebuie să se miște. Deci, trebuie să apăs tasta F4 de 1, 2 ori, să blochez rândul, dar nu și coloana. Acum am de gând să). Aceasta este o operație de matrice de argumente funcționale. Acesta este argumentul funcției. Faptul că avem mai multe articole înseamnă că este o operațiune matrice. Deci, când dau clic la final și dau clic pe F9, SUMIFS ne-au ascultat. A scuipat suma totală pentru iunie, Sioux, Poppi și Tyrone. (= SUME ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Acum, trebuie să limităm în continuare aceste sume prin adăugarea unei condiții ȘI. Chiar avem nevoie să fie iunie și America de Nord sau Sioux și America de Nord sau Poppi și America de Nord și așa mai departe. CONTROL + Z. Pur și simplu extindem, CRITERIA GAMA 2. Acum trebuie să ne uităm prin coloana REGION. CONTROL + SHIFT + JOS + F4 și voi face clic pe singura condiție, F4 1, 2, 3 ori pentru a bloca coloana, dar nu rândul. Dacă dau clic la sfârșit și F9, acestea sunt totalurile pentru fiecare dintre reprezentanții noștri de vânzări din America de Nord. Când îl copiem, SUMIFS va livra totalul pentru fiecare reprezentant de vânzări pentru America de Sud. (= SUME ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Observați că este doar SUMIFS care furnizează mai multe numere pe care trebuie să le adăugăm. CONTROL + Z. Așadar, aș putea să o pun în această funcție SUM, dar argumentul funcției SUM NUMĂRUL 1 nu va calcula corect această operațiune matrice fără a utiliza CONTROL + SHIFT + ENTER. Deci, voi înșela și voi folosi SUMPRODUCT. Acum, în mod normal, SUMPRODUCT ia mai multe matrice și le înmulțește - aceasta este partea PRODUCT - și apoi le adaugă, dar voi folosi doar ARRAY1 și voi folosi doar partea SUM a SUMPRODUCT,), CONTROL + ENTER, copiați-o în jos și peste lateral și, din moment ce am o mulțime de referințe nebune de celule, voi ajunge la ultima din F2 și, destul de sigur, are toate celulele și intervalele corecte. Bine. Am să mă arunc înapoi la. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Ce? Asta e o nebunie. Mike. Arată spre Mike. Oh Doamne. Punând o gamă de valori în SUMIFS și apoi trimițându-l în SUMPRODUCTS și faceți-l să o trateze ca pe o matrice. Hei, este sălbatic. Ar trebui să ne oprim chiar acolo. Arată spre Mike.
Bine. Să ne întoarcem la metoda mea, dar să ne prefacem că nu ai Excel 2013. Te-ai întors în Excel 2010 sau, mai rău, Excel pentru Mac. Adică spune că este Excel. Nu știu. Mă înnebunește doar ce poate sau nu Mac-ul să facă. Deci, vom lua TABELUL IERARHICI aici și, pentru că VLOOKUP nu se poate uita în stânga, voi lua informațiile REP. VÂNZĂRI, CONTROL + X și le voi lipi. Da, știu că pot face index și potrivi. Nu am chef să fac index și să potrivesc astăzi. Bine, deci, este foarte simplu. Aici, = VLOOKUP, duceți numele SALESREP acolo și vom face F4, 2, EXACTMATCHFALSE așa, faceți dublu clic pentru a copia acest lucru. (= VLOOKUP (A4, $ F $ 4: $ G $ 19,2, FALS))
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.
Bine. Ei bine, hei. Vreau să vă mulțumesc că ați trecut pe aici pentru acest foarte lung Dueling Excel Podcast. Ne vedem data viitoare pentru un alt episod din și ExcelIsFun.
Descărcare fișier
Descărcați exemplarul de fișier aici: Duel188.xlsm
