Jums ir pārskats, kurā parādīti pārdošanas apjomi 16 tirdzniecības pārstāvjiem. Katrs tirdzniecības pārstāvis pieder komandai. Kā jūs varat izveidot pārskatu par katras komandas kopējo pārdošanas apjomu?
Skatīties video
- Veidojiet pārdošanas ziņojumu pēc reģiona un komandas
- Sākotnējiem datiem ir tirdzniecības pārstāvis un reģions
- Otra (slikti veidota) tabula organizē pārdošanas pārstāvjus komandām
- 1. rēķina metode: pārveidojiet komandas hierarhijas datus. Abus diapazonus izveidojiet tabulās Ctrl + T
- Izveidojiet rakurstabulu, pievienojot datus datu modelim. Izvelciet komandu no otrās tabulas.
- Izveidojiet attiecības
- Maiks 2. metode: izveidojiet SUMIFS, kur lauks Kritēriji2 ir masīvs!
- Nododiet SUMIFS funkcijai SUMPRODUCT
- Bila metode 3: Pārkārtojiet hierarhijas tabulu, lai tirdzniecības pārstāvis atrastos kreisajā pusē.
- Sākotnējiem datiem pievienojiet VLOOKUP
- Veidojiet pagrieziena tabulu
- Maika 4. metode: Izmantojiet saites ikonu lentes cilnē Dati
- Veidojot rakurstabulu, izvēlieties Izmantot šīs darbgrāmatas datu modeli
- Bila metode 5: barošanas vaicājums. Pievienojiet uzmeklēšanas tabulu tikai kā savienojumu
- Pievienojiet sākotnējo tabulu tikai uzmeklēšanai
- Apvienojiet šīs divas tabulas, grupējot pēc, lai izveidotu galīgo ziņojumu
Video atšifrējums
Duelis ar ExcelPodcast, 188. sērija: Pārdošanas komandas pārskats pēc reģiona.
Bils: Hei. Laipni lūdzam atpakaļ. Ir pienācis laiks kārtējam Dueling Excel Podcast. Es esmu Bils Jelen no. Man pievienosies Maiks Girvins no ExcelIsFun. Šī ir mūsu 188. sērija, Pārdošanas komandas pārskats pēc reģiona.
Labi, lūk, šeit ir mūsu jautājums, datu kopa šeit ar dažādiem pārdošanas pārstāvjiem, cik liela bija viņu pārdošana pēc reģiona, un dažiem cilvēkiem ir pārdošanas apjomi abos reģionos, un pēc tam uzņēmums ir organizējis šos 16 pārdošanas pārstāvjus šajos četros pārdošanas darījumos. komandas, un mēs cenšamies katrai pārdošanas komandai noskaidrot, cik daudz viņiem bija ieņēmumu.
Labi. Tātad, mana pieeja tam ir, jūs zināt, man šeit nepatīk šis formāts. Es pārkārtošu šo formātu kaut kādā tabulā, šeit ir neliela hierarhija, kas katrai komandai parāda, kas ir tirdzniecības pārstāvji, un pēc tam, ja ir paredzēts, ka programmā Excel 2013 vai Excel 2016 mēs izmantojam Windows, nevis Mac , tad mēs varam izmantot datu modeli, un, lai to izdarītu, mums ir jāņem katra no šīm tabulām un FORMATĒT KĀ TABULU, kas ir CONTROL + T. Tātad, tur ir pirmā tabula, ko viņi sauc par 8. tabulu, un otrā tabula, ko viņi sauks par 9. tabulu. Es tos pārdēvēšu. Es ņemšu pirmo un saukšu to PĀRDOŠANAS TABULA, un es ņemšu otro, un es to saucu tāpat kā TEAM HIERARCHY. Labi.
Tagad pārbaudiet to. Sākot ar programmu Excel 2013, cilnē INSERT mēs izveidojam PIVOT TABLE no pirmās datu kopas, bet mēs sakām PIEVIENOT ŠOS DATUS DATU MODELIM, kas ir garlaicīgākais veids, kā informēt, ka Power Pivot motors sēž aiz Excel 2013. Pat ja jūs nemaksājat par Power Pivot, pat ja jums ir tikai pamata līmeņa Excel Office 365 vai Excel, jums tas ir. Labi, lūk, šeit ir mūsu jaunais ziņojums, un es darīšu to, ka es noteikti vēlos ziņot pa REGION, tāpēc tur ir REGIONS, un es vēlos redzēt kopējo PĀRDOŠANU, bet es vēlos to apskatīt pārdošanas komanda. Pārbaudiet to. Es izvēlos VISUS, un tas dod man citas šīs grupas tabulas, tostarp TEAM HIERARCHY. Es paņemšu KOMANDU un pārvietošu to pa kolonnām.
Pirmais, kas šeit notiks, ir nepareizas atbildes. Tas ir ļoti, ļoti normāli, lai saņemtu nepareizas atbildes. Tātad, mēs darīsim to, ka noklikšķināsim uz Izveidot. Ja esat 16 gadu vecumā, varat AUTO-DETECT. Izliksimies, ka viņi ir programmā Excel 2013, kur dodamies uz mūsu PĀRDOŠANAS TABULU. Tur ir lauks ar nosaukumu SALES REP un tas ir saistīts ar HIERARCHY, lauks ar nosaukumu SALES REP, noklikšķiniet uz OK, un mums ir pareizās atbildes. Maik, paskatīsimies, kas tev ir.
Maiks: Paldies ,. Jā, datu modelis ir lielisks veids, kā iet ar divām dažādām tabulām, lai izveidotu vienu rakurstabulu, un tā patiešām ir mana vēlamā metode, bet, ja jums tas bija jādara ar formulu un katras kolonnas augšpusē vajadzēja būt SALES TEAM tas nozīmē, ka, izmantojot formulu, mums burtiski ir jāizskata šī datu kopa, un man par katru ierakstu ir jājautā, vai PĀRDOŠANAS REP = Gigi vai Zodam vai Sandijam vai Šeilai, un tad, ja tas ir neto pārdošana, man jāsaka, un tas ir Ziemeļamerikas reģions.
Nu, mēs to varam izdarīt. Funkcijā SUMIFS mēs varam veikt AND loģisko pārbaudi un OR loģisko pārbaudi. SUM_RANGE, tie ir visi skaitļi, tāpēc es noklikšķināšu augšējā šūnā CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, es izcelšu visu kolonnu SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Parasti kritērijos mēs ievietojam vienu vienumu, piemēram, JŪNIJA PĀRDOŠANAS REP. Tas liek SUMIFS izspļaut vienu atbildi JŪNIJAM, bet, ja es izceļu 4 dažādas šūnas - 1 katram tirdzniecības pārstāvim, mēs uzdodam SUMSIFS veikt SUMIF katram atsevišķam tirdzniecības pārstāvim.
Tagad, kad es nokopēju šo formulu uz leju, man tā ir bloķēta, bet es to nokopēju uz sāniem, tai ir jāpārvietojas. Tātad, man ir jānospiež F4 taustiņš 1, 2 reizes, jānoslēdz rinda, bet ne kolonna. Tagad es eju uz). Šī ir funkcija argumenta masīva darbība. Tas ir funkcijas arguments. Tas, ka mums ir vairāki vienumi, nozīmē, ka tā ir masīva darbība. Tātad, kad es noklikšķinu beigās un nospiežu F9, SUMIFS mums paklausīja. Tā izspļāva jūnija, Sioux, Poppi un Tyrone kopējo summu. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Tagad mums ir vēl vairāk jāierobežo šīs summas, pievienojot nosacījumu AND. Mums ļoti vajadzīgs, lai tas būtu jūnijs un Ziemeļamerika vai Sioux un Ziemeļamerika vai Poppi un Ziemeļamerika utt. CONTROL + Z. Mēs vienkārši paplašinām, CRITERIA RANGE 2. Tagad mums ir jāizskata sleja REGION. CONTROL + SHIFT + DOWNARROW + F4, un es noklikšķināšu uz viena nosacījuma F4 1, 2, 3 reizes, lai bloķētu kolonnu, bet ne rindu. Ja es noklikšķinu beigās un F9, tie ir kopsummas katram no mūsu tirdzniecības pārstāvjiem Ziemeļamerikā. Kad mēs to nokopēsim, SUMIFS piegādās kopējo summu par katru Dienvidamerikas tirdzniecības pārstāvi. (= SUMIFI ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, E8))
Ievērojiet, ka tikai SUMIFS piegādā vairākus numurus, kas mums jāpievieno. CONTROL + Z. Tātad, es varētu to ievietot šajā SUM funkcijā, bet SUM funkcijas NUMBER 1 arguments neaprēķinās šo masīva darbību pareizi, neizmantojot CONTROL + SHIFT + ENTER. Tātad, es apkrāpšu un izmantoju SUMPRODUCT. Tagad parasti SUMPRODUCT aizņem vairākus masīvus un tos reizina - tā ir PRODUCT daļa - un pēc tam tos pievieno, bet es tikai izmantoju ARRAY1 un vienkārši izmantoju SUMPRODUCT daļu SUM,), CONTROL + ENTER, kopēšu to uz leju un pāri sānam, un, tā kā man ir daudz traku šūnu atsauču, es nākšu pie pēdējā F2 un, protams, visas šūnas un diapazoni ir pareizi. Labi. Es došos atpakaļ. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bils: Ko? Tas ir traki. Maiks. Norādiet uz Maiku. Ak mans Dievs. Ievietojot vērtību diapazonu SUMIFS un pēc tam nosūtot to SUMPRODUCTS un liekot tam izturēties kā pret ARRAY. Hei, tas ir mežonīgi. Mums vienkārši vajadzētu apstāties turpat. Norādiet uz Maiku.
Labi. Atgriezīsimies pie manas metodes, bet izliksimies, ka jums nav programmas Excel 2013. Jūs esat atgriezies programmā Excel 2010 vai, vēl ļaunāk, programmā Excel Mac. Es domāju, tas saka, ka tas ir Excel. Es nezinu. Tas mani vienkārši padara traku to, ko Mac var vai nevar. Tātad, mēs šeit paņemsim manu HIERARCHY GALDA, un, tā kā VLOOKUP nevar skatīties pa kreisi, es ņemšu informāciju par SALES REP, CONTROL + X un ielīmēju. Jā, es zinu, ka varu indeksēt un saskaņot. Šodien neesmu noskaņots darīt indeksu un spēles. Labi, tāpēc tas ir patiešām vienkārši. Šeit = VLOOKUP, paņemiet to SALESREP vārdu, un mēs F4, 2, EXACTMATCHFALSE tāpat, dubultklikšķi, lai to nokopētu. (= 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.
Labi. Nu, hei. Es vēlos pateikties, ka apstājāties šajā ļoti ilgajā Dueling Excel Podcast. Tiksimies nākamreiz, lai skatītu vēl vienu epizodi no un ExcelIsFun.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Duel188.xlsm
