Máte přehled zobrazující prodej pro 16 obchodních zástupců. Každý obchodní zástupce patří týmu. Jak můžete vytvořit přehled s celkovým prodejem každého týmu?
Sledovat video
- Sestavte si zprávu o prodeji podle regionu a týmu
- Původní data mají obchodní zástupce a region
- Druhý (špatně tvarovaný) stůl organizuje obchodní zástupce do týmů
- Metoda vyúčtování 1: Reformujte data týmové hierarchie. Vytvořte oba rozsahy do tabulek Ctrl + T.
- Vytvořte kontingenční tabulku a přidejte data do datového modelu. Vytáhněte tým z druhého stolu.
- Vytvořte vztah
- Mike Method2: Vytvořte SUMIFS, kde pole Criteria2 je pole!
- Předejte SUMIFS do funkce SUMPRODUCT
- Metoda vyúčtování 3: Změna uspořádání tabulky hierarchie, aby byl obchodní zástupce nalevo.
- Přidejte SVYHLEDÁNÍ k původním datům
- Vytvořte kontingenční tabulku
- Mike Metoda 4: Použijte ikonu Vztah na kartě Data na pásu karet
- Když vytvoříte kontingenční tabulku, zvolte Použít datový model tohoto sešitu
- Bill Metoda 5: Power Query. Přidejte vyhledávací tabulku pouze jako připojení
- Přidejte původní tabulku pouze jako vyhledávání
- Sloučit tyto dvě tabulky, seskupit a vytvořit závěrečnou zprávu
Přepis videa
Souboj ExcelPodcast, epizoda 188: Zpráva prodejního týmu podle regionů.
Bill: Hej. Vítej zpět. Je čas na další podcast Dueling Excel. Jsem Bill Jelen. Přidá se ke mně Mike Girvin z ExcelIsFun. Toto je naše epizoda 188, Zpráva prodejního týmu podle regionů.
Dobře, takže tady máme otázku, datovou sadu s různými obchodními zástupci, kolik jejich prodejů bylo podle regionů a někteří lidé mají prodej v obou regionech, a pak společnost zorganizovala těchto 16 obchodních zástupců do těchto čtyř prodejů týmy a snažíme se pro každý prodejní tým zjistit, jaké tržby měly.
V pořádku. Můj přístup k tomu tedy je, víte, tento formát se mi zde nelíbí. Chystám se přeuspořádat tento formát do nějaké tabulky, trochu hierarchie zde, která ukazuje pro každý tým, kdo jsou obchodní zástupci, a pak, pokud je k dispozici, jsme v aplikaci Excel 2013 nebo Excel 2016 pomocí Windows a ne Mac , pak můžeme využít datový model a abychom to mohli udělat, musíme vzít každou z těchto tabulek a FORMÁTOVAT JAKO TABULKU, což je CONTROL + T. Takže je tu první tabulka, které říkají Tabulka 8, a druhá tabulka, které budou říkat Tabulka 9. Přejdu je přejmenovat. Vezmu si první a nazvu to PRODEJNÍ TABULKA a vezmu si druhou a nazvu to TEAM HIERARCHY, takhle. V pořádku.
Podívejte se na to. Počínaje Excelem 2013 na kartě VLOŽIT vytvoříme PIVOTNÍ TABULKU z první datové sady, ale říkáme PŘIDAT TATO DATA DO DATOVÉHO MODELU, což je nejnudnější způsob, jak vám sdělit, že ve skutečnosti máte motor Power Pivot, který sedí za Excelem 2013. I když za Power Pivot neplatíte, i když máte pouze základní úroveň Excel Office 365 nebo Excel, máte ji. Dobře, takže tady je naše nová zpráva a to, co udělám, je, že určitě chci hlásit podle REGIONU, takže tam jsou REGIONY, a chci vidět celkový PRODEJ, ale chci se na to podívat prodejním týmem. Koukej na tohle. Vyberu VŠE a to mi dá další tabulky v této skupině, včetně TEAM HIERARCHY. Vezmu TEAM a přesunu ho přes SLOUPCE.
První věc, která se zde stane, je, že dostaneme špatné odpovědi. Je velmi, velmi normální dostat špatné odpovědi. To, co uděláme, je kliknout na VYTVOŘIT. Pokud jste v '16, můžete AUTO-DETEKCE. Předstírejme, že jsou v aplikaci Excel 2013, kde přejdeme k naší PRODEJNÍ TABULCE. Existuje pole s názvem SALES REP a souvisí s HIERARCHY, pole s názvem SALES REP, klikněte na OK a máme správné odpovědi. Mike, podívejme se, co máš.
Mike: Díky ,. Ano, datový model je úžasný způsob, jak pomocí dvou různých tabulek vytvořit jednu kontingenční tabulku, a to je opravdu moje preferovaná metoda, ale pokud jste to museli udělat pomocí vzorce a potřebujete mít v horní části každého sloupce SALES TEAM to znamená, že se vzorcem musíme doslova prozkoumat tuto datovou sadu a pro každý záznam se musím zeptat, je SALES REP = Gigi nebo Chin nebo Sandy nebo Sheila, a pak, pokud je to čistý prodej, musím říct, a je to region Severní Amerika.
Můžeme to udělat. Můžeme provést logický test AND a logický test OR ve funkci SUMIFS. SUM_RANGE, to jsou všechna čísla, takže kliknu do horní buňky, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, zvýrazním celý sloupec SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Nyní normálně do kritérií vložíme jednu položku, jako je JUNE SALES REP. To říká SUMIFS, aby vyplivl jednu odpověď na ČERVEN, ale pokud zvýrazním 4 různé buňky - 1 pro každého obchodního zástupce - dáváme SUMSIFS pokyn, aby udělal SUMIF pro každého jednotlivého obchodního zástupce.
Když teď zkopíruji tento vzorec dolů, potřebuji ho uzamčený, ale zkopíruji ho na stranu, musí se pohnout. Takže musím stisknout klávesu F4 1, 2krát, uzamknout řádek, ale ne sloupec. Teď se chystám). Toto je operace s polem argumentu funkce. To je argument funkce. Skutečnost, že máme více položek, znamená, že jde o operaci pole. Takže když kliknu na konec a stisknu F9, SUMIFS nás poslechl. Vyplivl celkovou částku za červen, Sioux, Poppi a Tyrone. (= SUMIFY ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Nyní musíme tyto částky dále omezit přidáním podmínky AND. Opravdu potřebujeme, aby to byl červen a Severní Amerika nebo Sioux a Severní Amerika nebo Poppi a Severní Amerika atd. CONTROL + Z. Jednoduše rozšiřujeme, KRITÉRIA ROZSAH 2. Nyní musíme prohlédnout sloupec REGION. CONTROL + SHIFT + DOWNARROW + F4 a kliknu na jedinou podmínku, F4 1, 2, 3 krát, abych uzamkl sloupec, ale ne řádek. Pokud kliknu na konec a F9, jsou to součty pro každého našeho obchodního zástupce v Severní Americe. Když to zkopírujeme, SUMIFS dodá celkovou částku za každého obchodního zástupce pro Jižní Ameriku. (= SUMIFY ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Všimněte si, že jde pouze o SUMIFY, které dodávají více čísel, které musíme přidat. CONTROL + Z. Mohl bych to tedy dát do této funkce SUM, ale argument funkce SUM ČÍSLO 1 nebude správně počítat tuto operaci pole bez použití kláves Control + SHIFT + ENTER. Takže budu podvádět a používat SUMPRODUCT. Nyní normálně SUMPRODUCT vezme více polí a znásobí je - to je část PRODUKTU - a pak je přidá, ale já prostě použiji ARRAY1 a použiji SUM část SUMPRODUCT,), CONTROL + ENTER, zkopíruj to dolů a znovu na stranu, a protože jsem získal spoustu šílených odkazů na buňky, přijdu k poslednímu v F2 a určitě jsou všechny buňky a rozsahy správné. V pořádku. Hodím se zpět. (= SUMPRODUCT (SUMIFY ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Cože? To je šílené. Mike. Ukažte na Mika. Pane Bože. Vložení řady hodnot do SUMIFS a jejich odeslání do SUMPRODUCTS a zacházení s nimi jako s POLÍČKY. Hej, to je divoký. Měli bychom se zastavit právě tam. Ukažte na Mika.
V pořádku. Vraťme se k mé metodě, ale předstírejme, že nemáte Excel 2013. Jste zpět v Excelu 2010 nebo v horším případě v Excelu pro Mac. Myslím tím, že říká, že je to Excel. Nevím. Prostě mě přivádí k šílenství, co Mac může nebo nemůže dělat. Takže si vezmeme můj TABULKA HIERARCHIE sem a protože VLOOKUP se nemůže dívat doleva, vezmu informace SALES REP, CONTROL + X a vložím. Jo, vím, že můžu udělat index a shodu. Dnes nemám náladu dělat index a odpovídat. Dobře, takže je to opravdu jednoduché. Tady = VLOOKUP, vezměte tam jméno SALESREP a my budeme F4, 2, EXACTMATCHFALSE takhle, dvojitým kliknutím to zkopí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.
V pořádku. No, hej. Chtěl bych vám poděkovat, že jste se zastavili za tento velmi dlouhý podcast Dueling Excel. Uvidíme se příště u další epizody od a ExcelIsFun.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Duel188.xlsm
