Müük piirkonna ja meeskonna järgi - Exceli näpunäited

Teil on aruanne, mis näitab müüki 16 müügiesindaja jaoks. Iga müügiesindaja kuulub meeskonda. Kuidas saate luua aruande, mis näitab iga meeskonna kogumüüki?

Vaata videot

  • Koostage müügiaruanne piirkonna ja meeskonna järgi
  • Algandmetel on müügiesindaja ja piirkond
  • Teine (halvasti vormitud) tabel korraldab meeskondade müügiesindajad
  • Arve meetod 1: kujundage meeskonna hierarhia andmed ümber. Tehke mõlemad vahemikud tabeliteks Ctrl + T
  • Looge pöördtabel, lisades andmed andmemudelisse. Tõmmake meeskond teisest lauast.
  • Loo suhe
  • Mike Method2: Koostage SUMIFS, kus väli Criteria2 on massiiv!
  • Andke funktsioon SUMIFS sisse funktsioonile SUMPRODUCT
  • Arve meetod 3: korraldage hierarhiatabel ümber, nii et müügiesindaja oleks vasakul.
  • Lisage algandmetele VLOOKUP
  • Ehitage pöördtabel
  • Mike 4. meetod: kasutage lindi vahekaardi Andmed ikooni Seos
  • Pöördtabeli loomisel valige Kasuta selle töövihiku andmemudelit
  • Billi meetod 5: voolupäring. Lisage otsingutabel ainult ühenduseks
  • Lisage algne tabel ainult otsinguna
  • Lõpparuande koostamiseks ühendage need kaks tabelit rühmade kaupa

Video ärakiri

Dueling ExcelPodcast, Episode 188: müügimeeskonna aruanne piirkonna järgi.

Bill: Hei. Tere tulemast tagasi. Aeg on veel üks Dueling Exceli podcast. Olen Bill Jelen pärit. Minuga liitub Mike Girvin ExcelIsFunist. See on meie osa 188, müügimeeskonna aruanne piirkonna järgi.

Hästi, nii, siin on meil küsimus, siin olev andmekogum koos erinevate müügiesindajatega, kui palju oli nende müüki piirkondade kaupa, ja mõnel inimesel on müüki mõlemas piirkonnas, ja siis on ettevõte need 16 müügiesindust korraldanud nendeks neljaks müügiks meeskonnad ja püüame iga müügimeeskonna jaoks välja mõelda, kui palju neil tulusid oli.

Hästi. Nii et minu lähenemine sellele on, teate, mulle see formaat siin ei meeldi. Korraldan selle vormingu ümber mingisuguseks tabeliks, siin on väike hierarhia, mis näitab iga meeskonna jaoks, kes on müügiesindajad ja siis, kui eeldame, et oleme Excelis 2013 või Excel 2016 Windowsi, mitte Maci abil , siis saame kasutada andmemudeli ja selleks peame võtma kõik need tabelid ja Vormindama kui tabel, mis on CONTROL + T. Seal on esimene tabel, mida nad nimetavad tabeliks 8, ja teine ​​tabel, mida nad nimetavad tabeliks 9. Nimetan need ümber. Ma võtan esimese ja nimetan seda MÜÜGILAUDAKS ja võtan teise ja nimetan seda TEAM HIERARCHY, niimoodi. Hästi.

Nüüd vaadake seda. Alates Excel 2013-st loome vahekaardil INSERT esimesest andmekogumist PIVOT TABEL, kuid ütleme, et LISA KÄESOLEVAD ANDMED ANDMEMUDELILE, mis on igavam viis teada anda, et teil on Power Pivoti mootor tegelikult Exceli taga 2013. Isegi kui te ei maksa Power Pivoti eest, on teil see olemas isegi siis, kui teil on lihtsalt baastaseme Excel Office 365 või Excel. Hästi, nii, siin on meie uus aruanne ja mida ma kavatsen teha, on see, et ma tahan kindlasti teatada REGIONi kaupa, nii et seal on REGIONS ja ma tahan näha kogu MÜÜGI, kuid ma tahan seda vaadata müügimeeskonna poolt. Vaata seda. Valin KÕIK ja see annab mulle selle grupi teised tabelid, sealhulgas TEAM HIERARCHY. Võtan meeskonna ja liigutan selle üle veergude.

Esimene asi, mis siin juhtuma hakkab, on see, et saame valed vastused. See on väga-väga normaalne, et saada valesid vastuseid. Niisiis, klõpsame nuppu LOE. Kui olete 16. eluaastal, saate AUTO-DETECT. Oletame, et nad on Excelis 2013, kus me läheme oma MÜÜGITABELI. Seal on väli nimega SALES REP ja see on seotud HIERARCHY'ga, väljal nimega SALES REP, klõpsake nuppu OK ja meil on õiged vastused. Mike, vaatame, mis sul on.

Mike: Aitäh ,. Jah, andmemudel on vinge viis kahe erineva tabeliga ühe pöördtabeli koostamiseks ja see on tõesti minu eelistatud meetod, kuid kui pidite seda tegema valemiga ja teil oli vaja iga veeru ülaosas SALES TEAM niimoodi, see tähendab, et valemiga peame sõna otseses mõttes selle andmekogumi läbi vaatama ja iga rekordi puhul pean küsima, kas MÜÜGIVÕLG = Gigile või Chinile või Sandyle või Sheilale ja siis, kui see on netomüük, pean ütlema, ja see on Põhja-Ameerika piirkond.

Noh, me saame seda teha. Funktsioonis SUMIFS saame teha JA loogilise testi ja VÕI loogilise testi. SUM_RANGE, need on kõik numbrid, nii et klõpsan ülemisse lahtrisse CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, tõstan esile kogu veeru SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Nüüd paneme tavaliselt kriteeriumidesse ühe elemendi nagu JUUNI MÜÜGIVAHEND. See käsib SUMIFSil välja lasta JUUNI jaoks üks vastus, kuid kui ma toon välja neli erinevat lahtrit - 1 iga müügiesindaja jaoks -, siis käskime SUMSIFSil teha SUMIF iga üksiku müügiesindaja jaoks.

Nüüd, kui kopeerin selle valemi alla, vajan seda lukustatuna, kuid kopeerin küljele, see peab liikuma. Niisiis, ma pean klahvi F4 vajutama 1, 2 korda, lukustama rea, kuid mitte veeru. Nüüd lähen). See on funktsiooni argumendi massiivi operatsioon. See on funktsiooni argument. Asjaolu, et meil on mitu üksust, tähendab, et see on massiivioperatsioon. Niisiis, kui ma klõpsan lõpus ja vajutan F9, allus SUMIFS meile. See sülitas välja juuni, Siouxi, Poppi ja Tyrone'i kogusumma. (= SUMIFID ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))

Nüüd peame neid summasid veelgi piirama, lisades tingimuse JA. Meil on tõesti vaja, et see oleks juuni ja Põhja-Ameerika või Sioux ja Põhja-Ameerika või Poppi ja Põhja-Ameerika jne. CONTROL + Z. Me lihtsalt laiendame, KRITEERIUMID RANGE 2. Nüüd peame läbi vaatama veeru REGION. CONTROL + SHIFT + DOWNROROW + F4 ja ma klõpsan veeru lukustamiseks üksikul tingimusel F4 1, 2, 3, kuid mitte rida. Kui klõpsan lõpus ja F9, on need meie Põhja-Ameerika müügiesindajate koguarvud. Kui me selle alla kopeerime, edastab SUMIFS iga Lõuna-Ameerika müügiesindaja koguarvu. (= SUMIFID ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, E8))

Pange tähele, et see on lihtsalt SUMIFS, mis edastab mitu numbrit, mille peame lisama. CONTROL + Z. Nii et ma võiksin selle lisada sellesse funktsiooni SUM, kuid funktsiooni NUMBER 1 argument SUM ei arvuta seda massiivi toimingut õigesti, ilma et kasutaksite klahve CONTROL + SHIFT + ENTER. Nii et ma hakkan petma ja kasutan SUMPRODUCTi. Nüüd võtab SUMPRODUCT tavaliselt mitu massiivi ja korrutab need - see on toote PRODUCT - ja lisab need, kuid ma lihtsalt kavatsen kasutada ARRAY1 ja lihtsalt kasutada SUMPRODUCTi osa SUM,), CONTROL + ENTER, kopeerida alla ja küljele ning kuna mul on palju hullumeelseid lahtriviiteid, tulen F2-s viimase juurde ja kindlasti on sellel kõik lahtrid ja vahemikud õiged. Hästi. Ma viskan tagasi. (= SUMPRODUCT (SUMIFID ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))

Bill: Mida? See on hull. Mike. Osutage Mike'ile. Oh jumal küll. Kui panete SUMIFS-i väärtuste vahemiku ja saadate selle seejärel SUMPRODUCTS-i ning panete selle kohtlema nagu ARRAY. Hei, see on metsik. Peaksime lihtsalt seal peatuma. Osutage Mike'ile.

Hästi. Läheme tagasi minu meetodi juurde, kuid teeskleme, et teil pole Exceli 2013. Olete tagasi rakenduses Excel 2010 või, mis veel hullem, Excelis Macile. See tähendab, et see on Excel. Ma ei tea. See ajab mind lihtsalt hulluks, mida Mac saab või ei saa. Niisiis, võtame mu HIERARCHY TABELi siia ja kuna VLOOKUP ei saa vasakule vaadata, võtan SALES REPi teabe, CONTROL + X ja kleepin. Jah, ma tean, et oskan indekseerida ja sobitada. Mul pole täna tuju indeksit ja matši teha. Hästi, nii, see on tõesti lihtne. Siin = VLOOKUP, võtke see SALESREP nimi sinna ja me teeme F4, 2, EXACTMATCHFALSE niimoodi, topeltklõpsake selle kopeerimiseks. (= VLOOKUP (A4, $ 4 $: $ G $ 19,2, VÄÄR))

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.

Hästi. Noh, hei. Tahan teid tänada selle eest, et peatusite selle väga pika Dueling Exceli Podcasti eest. Järgmisel korral näeme veel ühe episoodi saitilt ja ExcelIsFun.

Laadige fail alla

Laadige näidisfail alla siit: Duel188.xlsm

Huvitavad Artiklid...