Máte prehľad zobrazujúci predaj pre 16 obchodných zástupcov. Každý obchodný zástupca patrí k tímu. Ako môžete vytvoriť prehľad s celkovým predajom každého tímu?
Pozeraj video
- Vytvorte správu o predaji podľa regiónu a tímu
- Pôvodné údaje majú obchodného zástupcu a región
- Druhý (zle tvarovaný) stôl organizuje obchodných zástupcov tímov
- Fakturačná metóda 1: Reformácia údajov tímovej hierarchie. Z oboch rozsahov urobte tabuľky Ctrl + T.
- Vytvorte kontingenčnú tabuľku a pridajte údaje do dátového modelu. Vytiahnite tím z druhého stola.
- Vytvorte vzťah
- Mike Method2: Vytvorte SUMIFS, kde je pole Criteria2 pole!
- Prejdite SUMIFY do funkcie SUMPRODUCT
- Fakturačná metóda 3: Usporiadajte hierarchickú tabuľku tak, aby bol obchodný zástupca vľavo.
- Pridajte K pôvodným údajom SVETROHLED
- Zostavte kontingenčnú tabuľku
- Mike Metóda 4: Použite ikonu Vzťah na karte Údaje na páse s nástrojmi
- Pri vytváraní kontingenčnej tabuľky vyberte možnosť Použiť údajový model tohto zošita
- 5. metóda fakturácie: Power Query. Pridajte vyhľadávaciu tabuľku iba ako pripojenie
- Pridajte pôvodnú tabuľku iba na vyhľadanie
- Zlúčte tieto dva stoly a zoskupte ich, aby ste vytvorili záverečnú správu
Prepis videa
Dueling ExcelPodcast, epizóda 188: Správa predajného tímu podľa regiónu.
Bill: Hej. Vitaj späť. Je čas na ďalší podcast Dueling Excel. Som Bill Jelen z. Doplní ma Mike Girvin z ExcelIsFun. Toto je naša epizóda 188, Správa predajného tímu podľa regiónov.
Dobre, takže tu máme otázku, súbor dát s rôznymi obchodnými zástupcami, koľko ich predajov bolo podľa regiónov a niektorí ľudia majú tržby v oboch regiónoch. Potom spoločnosť zorganizovala týchto 16 obchodných zástupcov do týchto štyroch predajov. tímy a snažíme sa pre každý predajný tím zistiť, aké vysoké tržby mali.
Dobre. Takže môj prístup k tomu je, viete, tento formát sa mi tu nepáči. Chystám sa usporiadať tento formát do nejakej tabuľky, trochu hierarchie tu, ktorá ukazuje pre každý tím, kto sú obchodní zástupcovia, a potom, ak je k dispozícii, sme v programe Excel 2013 alebo Excel 2016 pomocou Windows a nie Mac , potom môžeme využiť dátový model, a aby sme to mohli urobiť, musíme zobrať každú z týchto tabuliek a FORMÁTOVAŤ AKO TABUĽKU, čo je CONTROL + T. Takže je tu prvá tabuľka, ktorú nazývajú Tabuľka 8, a druhá tabuľka, ktorú budú nazývať Tabuľka 9. Tieto premenujem. Ja si vezmem prvý a budem to volať PREDAJNÁ TABUĽKA a budem brať ten druhý a budem to volať TEAM HIERARCHY, asi tak. Dobre.
Teraz to skontrolujte. Počnúc programom Excel 2013 na karte VLOŽIŤ vytvoríme PIVOTNÚ TABUĽKU z prvej množiny údajov, ale povieme PRIDAŤ TOTO ÚDAJE DO DATOVÉHO MODELU, čo je najnudnejší spôsob, ako vám oznámiť, že v skutočnosti máte motor Power Pivot sediaci za programom Excel 2013. Aj keď za Power Pivot neplatíte, aj keď máte iba základnú úroveň Excel Office 365 alebo Excel, máte ju. Dobre, takže, tu je naša nová správa a to, čo urobím, je, že určite chcem podať správu podľa REGIONU, takže sú tu REGIÓNY, a chcem vidieť celkový PREDAJ, ale chcem sa na to pozrieť predajným tímom. Pozri na toto. Vyberiem VŠETKO a to mi dá ďalšie stoly v tejto skupine, vrátane TEAM HIERARCHY. Vezmem TÍM a presuniem ho cez STĹPCE.
Prvá vec, ktorá sa tu stane, je, že dostaneme nesprávne odpovede. Je veľmi, veľmi normálne, že dostanete nesprávne odpovede. Čo teda urobíme, je kliknutie na VYTVORIŤ. Ak ste v 16-tke, môžete sa automaticky ZISTIŤ. Predstierajme, že sú v programe Excel 2013, kde prejdeme k našej TABUĽKE PREDAJA. Je tu pole s názvom SALES REP a súvisí s HIERARCHY, s názvom SALES REP, kliknite na OK a máme správne odpovede. Mike, pozrime sa, čo máš.
Mike: Vďaka ,. Áno, dátový model je úžasný spôsob, ako vytvoriť dve kontingenčné tabuľky s dvoma rôznymi tabuľkami, a to je naozaj moja preferovaná metóda, ale ak ste to museli urobiť pomocou vzorca a potrebujete mať v hornej časti každého stĺpca SALES TEAM to znamená, že so vzorcom musíme doslova prezerať tento súbor údajov a pre každý záznam, ktorý sa musím opýtať, je SALES REP = Gigi alebo Chin alebo Sandy alebo Sheila, a potom, ak je to čistý predaj, musím povedať, a je to región Severná Amerika.
To dokážeme. Môžeme urobiť logický test AND a logický test OR vo funkcii SUMIFS. SUM_RANGE, to sú všetky čísla, takže kliknem do hornej bunky, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, zvýrazním celý stĺpec SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Teraz normálne dáme do kritérií jednu položku ako JUNE SALES REP. To hovorí, že SUMIFS má vypľuť jednu odpoveď na JÚN, ale ak zvýrazním 4 rôzne bunky - 1 pre každého obchodného zástupcu - dávame SUMSIFS pokyn, aby urobil SUMIF pre každého jednotlivého obchodného zástupcu.
Keď teraz kopírujem tento vzorec, potrebujem ho uzamknutý, ale kopírujem ho nabok, musí sa pohnúť. Takže musím stlačiť kláves F4 1, 2 krát, uzamknúť riadok, ale nie stĺpec. Teraz sa chystám). Toto je operácia s poľom argumentov funkcie. To je argument funkcie. Skutočnosť, že máme viac položiek, znamená, že ide o operáciu poľa. Takže keď kliknem na koniec a trafím F9, SUMIFS nás poslúchli. Vyplivol celkovú sumu za jún, Sioux, Poppi a Tyrone. (= SUMIFY ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7)))
Teraz musíme tieto sumy ďalej obmedziť pridaním podmienky AND. Skutočne to potrebujeme, aby to bol jún a Severná Amerika alebo Sioux a Severná Amerika alebo Poppi a Severná Amerika atď. CONTROL + Z. Jednoducho rozšírime, ROZSAH KRITÉRIÍ 2. Teraz sa musíme pozrieť do stĺpca REGIÓN. CONTROL + SHIFT + DOWNARROW + F4 a kliknem na jednu podmienku, F4 1, 2, 3 krát, aby som uzamkol stĺpec, ale nie riadok. Ak kliknem na koniec a na F9, sú to súčty pre každého z našich obchodných zástupcov v Severnej Amerike. Keď to skopírujeme, SUMIFS dodá celkovú sumu za každého obchodného zástupcu pre Južnú Ameriku. (= SUMIFY ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45 $ E8))
Všimnite si, že je to iba SUMIFS, ktorý dodáva viac čísel, ktoré musíme pridať. CONTROL + Z. Takže by som to mohol vložiť do tejto funkcie SUM, ale argument SUMA ČÍSLO 1 argument nevypočíta túto operáciu poľa správne bez použitia klávesov CONTROL + SHIFT + ENTER. Takže idem podvádzať a používať SUMPRODUCT. Teraz normálne SUMPRODUCT vezme viac polí a znásobí ich - to je časť PRODUKTU - a potom ich pridá, ale ja len použijem ARRAY1 a použijem iba SUM časť SUMPRODUCT,), CONTROL + ENTER, skopíruj to dole a znova nabok, a keďže som dostal veľa šialených odkazov na bunky, prídem k poslednému v F2 a, samozrejme, má všetky bunky a rozsahy správne. Dobre. Vraciam sa späť k. (= SUMPRODUCT (SUMIFY ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Čože? To je šialené. Mike. Ukážte na Mika. O môj Bože. Vložením rozsahu hodnôt do SUMIFS a následným odoslaním do SUMPRODUCTS sa s nimi zaobchádza ako s POLOM. Hej, to je divoké. Mali by sme sa zastaviť práve tam. Ukážte na Mika.
Dobre. Vráťme sa k mojej metóde, ale tvárme sa, že nemáte program Excel 2013. Ste späť v programe Excel 2010 alebo, čo je horšie, v Exceli pre Mac. Myslím tým, hovorí sa, že je to Excel. Neviem. Jednoducho ma privádza do šialenstva, čo Mac dokáže alebo nemôže. Takže tu prevezmeme moju HIERARCHY TABUĽKU, a pretože VLOOKUP sa nemôže pozerať doľava, vezmem informácie SALES REP, CONTROL + X a prilepím ich. Áno, viem, že môžem indexovať a porovnávať. Dnes nemám náladu robiť indexy a porovnávať ich. Dobre, takže je to naozaj jednoduché. Tu = VLOOKUP, vezmite tam meno SALESREP a my budeme F4, 2, EXACTMATCHFALSE takhle, dvojitým kliknutím to skopírujte. (= 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.
Dobre. No hej. Chcem vám poďakovať, že ste sa zastavili pri tomto veľmi dlhom podcastu Dueling Excel. Uvidíme sa nabudúce pri ďalšej epizóde od a ExcelIsFun.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Duel188.xlsm
