Имате извештај који приказује продају 16 продајних представника. Сваки продајни представник припада тиму. Како можете да направите извештај који приказује укупну продају за сваки тим?
Погледајте видео
- Направите извештај о продаји по регионима и тиму
- Оригинални подаци имају представника продаје и регион
- Други (лоше обликован) сто организује продајна представништва по тимовима
- Метод рачуна 1: Преобликовање података о хијерархији тима. Унесите оба опсега у Цтрл + Т табеле
- Направите пивот табелу додавањем података у модел података. Повуците тим са другог стола.
- Створите однос
- Мике Метход2: Направите СУМИФС где је поље Цритериа2 низ!
- Пренесите СУМИФС у функцију СУМПРОДУЦТ
- Метод обрачуна 3: Преуредите табелу хијерархије тако да је представник продаје на левој страни.
- Додајте ВЛООКУП оригиналним подацима
- Направите пивот табелу
- Мике Метход 4: Користите икону Релатионсхип на картици Дата на траци
- Када креирате пивот табелу, изаберите Користи модел података ове радне свеске
- Метод рачуна 5: Повер Куери. Додајте табелу за претрагу као само везу
- Додајте оригиналну табелу само као претрагу
- Спојите те две табеле, групишите их да бисте добили коначни извештај
Видео транскрипт
Двобој ЕкцелПодцаст, епизода 188: Извештај продајног тима по регионима.
Билл: Хеј. Добродошли назад. Време је за још један Дуел Екцел подцаст. Ја сам Билл Јелен из. Придружиће ми се Мике Гирвин из ЕкцелИсФун-а. Ово је наша епизода 188, Извештај продајног тима по регионима.
Дакле, ево питања које имамо, скупа података са разним продајним представницима, колика је била њихова продаја по регионима, а неки људи продају у оба региона, а онда је компанија организовала тих 16 продајних представништава у ове четири продаје тимова и покушавамо да схватимо за сваки продајни тим колики су приходи имали.
У реду. Дакле, мој приступ овоме је, знате, не свиђа ми се овај формат овде. Преуредит ћу тај формат у неку врсту табеле, мала хијерархија овде, која сваком тиму показује ко су продајни представници, а затим, ако је предвиђено да смо у Екцел 2013 или Екцел 2016 користећи Виндовс, а не Мац , тада можемо искористити модел података и, да бисмо то урадили, морамо узети сваку од ових табела и ОБЛИКОВАТИ КАО ТАБЕЛУ која је ЦОНТРОЛ + Т. Дакле, постоји прва табела коју називају Табела 8 и друга табела коју ће назвати Табела 9. Преименоваћу их. Узећу прву и назваћу је ТАБЕЛА ПРОДАЈЕ, а узећу другу и назваћу је ХИЈЕРАРХИЈА ТИМА, тако. У реду.
Сад, погледајте ово. Почевши од програма Екцел 2013, на картици ИНСЕРТ креирамо ПИВОТ ТАБЕЛУ од првог скупа података, али кажемо ДОДАЈТЕ ОВЕ ПОДАТКЕ МОДЕЛУ ПОДАТАКА што је најсаднији начин да вас обавестимо да Повер Пивот заправо стоји иза Екцел-а 2013. Чак и ако не плаћате Повер Пивот, чак и ако имате само основни ниво Екцел Оффице 365 или Екцел, то имате. Дакле, ево нашег новог извештаја и оно што ћу урадити је да свакако желим да пријављујем по РЕГИОН-у, дакле постоје РЕГИОНИ и желим да видим укупне ПРОДАЈЕ, али желим да ово погледам од продајног тима. Види ово. Изабраћу СВЕ и то ми даје остале табеле у овој групи, укључујући ХИЈЕРАРХИЈУ ТИМА. Узећу ТИМ и пребацићу га преко КОЛОНЕ.
Прво што ће се овде догодити је да добијемо погрешне одговоре. То је врло, врло нормално да се добију погрешни одговори. Дакле, оно што ћемо урадити је да кликнемо СТВАРИ. Ако сте у '16, можете АУТО-ДЕТЕКТ. Претварајмо се да су у програму Екцел 2013 где идемо на наш ПРОДАЈНИ ТАБЕЛ. Тамо постоји поље под називом ПРОДАЈА РЕП и оно је повезано са ХИЈЕРАРХИЈОМ, поље под називом ПРОДАЈА РЕП, кликните ОК и имамо тачне одговоре. Мике, да видимо шта имаш.
Мике: Хвала ,. Да, модел података је сјајан начин да се са две различите табеле направи једна пивот табела и то је заиста мој омиљени метод, али ако је то требало урадити помоћу формуле, а на врху сваке колоне требало је да имате ТИМ ПРОДАЈЕ овако, то значи да са формулом буквално морамо прегледати овај низ података и за сваки запис морам да питам да ли је РЕПУБЛИКА ПРОДАЈЕ = Гиги или Цхин или Санди или Схеила, а онда, ако је морам рећи да је нето продаја и то је регион Северна Америка.
Па, ми то можемо. У функцији СУМИФС можемо да радимо логички тест И и логички тест ИЛИ. СУМ_РАНГЕ, то су сви бројеви, па ћу кликнути у горњу ћелију, ЦОНТРОЛ + СХИФТ + ДОВНАРРОВ + Ф4, ЦРИТЕРИА_РАНГЕ, истакћу целу колону САЛЕСРЕП, ЦОНТРОЛ + СХИФТ + ДОВНРОВ + Ф4,. Сада обично у критеријуме стављамо једну ставку као што је ПРОДАЈА У ЈУНУ. То говори СУМИФС-у да испљуне један одговор за ЈУН, али, ако истакнем 4 различите ћелије - 1 за сваког продајног представника - налажемо СУМСИФС-у да уради СУМИФ за сваког појединачног продајног представника.
Сада, када копирам ову формулу, треба да је закључана, али је копирам у страну, мора да се помери. Дакле, морам притиснути тастер Ф4 1, 2 пута, закључати ред, али не и колону. Сад ћу). Ово је функција низа функција аргумента. То је аргумент функције. Чињеница да имамо више ставки значи да је реч о операцији низа. Дакле, када кликнем на крају и притиснем Ф9, СУМИФС нас је послушао. Испљунуо је укупан износ за Јуне, Сиоук, Поппи и Тироне. (= СУМИФС ($ 4 УСД: $ Б $ 45, $ А $ 4: $ А $ 45, Ф $ 4: Ф $ 7))
Сада морамо да ограничимо те износе додавањем услова АНД. Заиста нам је потребно да то буду јуни и Северна Америка или Сиукси и Северна Америка или Поппи и Северна Америка итд. ЦОНТРОЛ + З. Једноставно проширујемо, КРИТЕРИЈУМИ РАНГЕ 2. Сада морамо погледати колону РЕГИОН. ЦОНТРОЛ + СХИФТ + ДОВНРОВ + Ф4, а ја ћу кликнути на један услов, Ф4 1, 2, 3 пута да закључам колону, али не и ред. Ако кликнем на крају и Ф9, то су укупни износи за сваког нашег продајног представника у Северној Америци. Када га копирамо, СУМИФС ће испоручити укупан износ за сваког продајног представника за Јужну Америку. (= СУМИФС ($ 4 УСД: $ Б $ 45, $ А $ 4: $ А $ 45, Ф $ 4: Ф $ 7, $ Ц $ 4: $ Ц $ 45, $ Е8))
Приметите да само СУМИФС испоручује више бројева које морамо додати. ЦОНТРОЛ + З. Дакле, могао бих да је ставим у ову функцију СУМ, али аргумент СУМ функције БРОЈ 1 неће правилно израчунати ову операцију низа без употребе ЦОНТРОЛ + СХИФТ + ЕНТЕР. Дакле, преварићу и користити СУМПРОДУЦТ. Сада, нормално, СУМПРОДУЦТ узима више низова и множи их - то је део ПРОИЗВОДА - а затим их додаје, али ја ћу само користити АРРАИ1 и само СУМ део СУМПРОДУЦТ,), ЦОНТРОЛ + ЕНТЕР, копирати надоле и на страну, а пошто имам пуно лудих референци на ћелије, доћи ћу до последње у Ф2 и, сасвим сигурно, све ћелије и домети су тачни. У реду. Вратићу се на. (= СУМПРОДУЦТ (СУМИФС ($ Б $ 4: $ Б $ 45, $ А $ 4: $ А $ 45, Ф $ 4: Ф $ 7, $ Ц $ 4: $ Ц $ 45, $ Е8)))
Билл: Шта? То је лудо. Мике. Покажи на Микеа. О мој Боже. Стављање опсега вредности у СУМИФС, а затим слање у СУМПРОДУЦТС и навођење да се према њему понаша као према НИЗУ. Хеј, то је дивље. Требали бисмо се зауставити управо тамо. Покажи на Микеа.
У реду. Вратимо се мојој методи, али претварамо се да немате Екцел 2013. Вратили сте се у Екцел 2010 или, још горе, Екцел за Мац. Мислим, каже да је то Екцел. Не знам. То ме излуђује шта Мац може, а шта не може. Дакле, узећемо овде моју ХИЈЕРАРХИЈСКУ ТАБЕЛУ и, пошто ВЛООКУП не може да гледа улево, узећу информације О ПРОДАЈИ РЕП, ЦОНТРОЛ + Кс и налепити. Да, знам да знам индексирати и поклапати. Данас нисам расположен за индексирање и мечеве. Па, стварно је једноставно. Ево, = ВЛООКУП, узми оно име САЛЕСРЕП тамо, а ми ћемо Ф4, 2, ЕКСАЦТМАТЦХФАЛСЕ тако, двапут кликнути да то копирамо. (= ВЛООКУП (А4, $ Ф $ 4: $ Г $ 19,2, ФАЛСЕ))
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.
У реду. Добро Хеј. Желим да вам захвалим што сте навратили у овај веома дуги Екцел Подцаст. Видимо се следећи пут за још једну епизоду са и ЕкцелИсФун.
Скини докуменат
Преузмите датотеку узорка овде: Дуел188.клсм
