Imate poročilo o prodaji 16 prodajnih predstavnikov. Vsak prodajni zastopnik pripada ekipi. Kako lahko ustvarite poročilo, ki prikazuje skupno prodajo za vsako ekipo?
Oglejte si video
- Sestavite poročilo o prodaji po regijah in skupini
- Izvirni podatki imajo prodajnega predstavnika in regijo
- Druga (slabo oblikovana) miza organizira prodajna predstavništva v ekipe
- Metoda obračuna 1: Preoblikujte podatke o hierarhiji ekipe. Naredite oba obsega v tabelah Ctrl + T
- Ustvarite vrtilno tabelo in dodajte podatke v podatkovni model. Povlecite ekipo z druge mize.
- Ustvari odnos
- Mike Method2: Zgradite SUMIFS, kjer je polje Criteria2 matrika!
- Prenesite SUMIFS v funkcijo SUMPRODUCT
- Metoda računa 3: Preuredite hierarhično tabelo, tako da bo prodajni zastopnik na levi.
- Izvirnim podatkom dodajte VLOOKUP
- Sestavite vrtilno tabelo
- Mike Method 4: Uporabite ikono Relationship na zavihku Data na traku
- Ko ustvarite vrtilno tabelo, izberite Uporabi podatkovni model tega delovnega zvezka
- Metoda računa 5: Power Query. Iskalno tabelo dodajte kot samo povezavo
- Izvirno tabelo dodajte samo kot iskanje
- Združi ti dve tabeli, razvrsti po, da se pripravi končno poročilo
Video zapis
Dvoboj ExcelPodcast, epizoda 188: Poročilo prodajne ekipe po regijah.
Bill: Hej. Dobrodošel nazaj. Čas je za še en dvobojni podcast Excel. Sem Bill Jelen iz. Pridružil se mi bo Mike Girvin iz ExcelIsFun. To je naša epizoda 188, Poročilo prodajne ekipe po regijah.
Torej, tukaj je vprašanje, ki ga imamo tukaj, nabor podatkov tukaj z različnimi prodajnimi zastopniki, kolikšna je bila njihova prodaja po regijah, in nekateri ljudje prodajajo v obeh regijah, nato pa je podjetje organiziralo teh 16 prodajnih predstavnikov v te štiri prodaje ekip in za vsako prodajno ekipo poskušamo ugotoviti, koliko prihodkov so imeli.
Vredu. Torej, moj pristop k temu je, veste, tukaj mi ta format ni všeč. Ta format bom preuredil v nekakšno tabelo, tu je malo hierarhije, ki za vsako ekipo pokaže, kdo so prodajni zastopniki, in potem, če je v programu Excel 2013 ali Excel 2016, uporabljamo Windows in ne Mac , potem lahko uporabimo podatkovni model in, da to naredimo, moramo vzeti vsako od teh tabel in OBLIKOVATI KOT TABELO, ki je CONTROL + T. Torej, tu je prva tabela, ki jo imenujejo tabela 8, in druga tabela, ki jo bodo imenovali tabela 9. Preimenovala jih bom. Vzel bom prvo in jo poimenoval PRODAJNA MIZA, vzel pa drugo in jo imenoval HIERARHIJA EKIPE, tako. Vredu.
Zdaj pa poglejte to. Z začetkom v Excelu 2013 na zavihku INSERT ustvarimo PIVOTNO TABELO iz prvega nabora podatkov, vendar pravimo DODAJTE TE PODATKE MODELU PODATKOV, kar je najdolgočasnejši način, da vas obvestimo, da za Excelom dejansko sedi motor Power Pivot 2013. Tudi če ne plačujete za Power Pivot, tudi če imate samo osnovni nivo Excel Office 365 ali Excel, ga imate. Torej, tukaj je naše novo poročilo in kar bom naredil, zagotovo želim poročati po REGION, torej obstajajo REGIJE in želim si ogledati celotne PRODAJE, vendar to želim pogledati s strani prodajne ekipe. Poglej to. Izbral bom VSE in to mi da druge tabele v tej skupini, vključno z HIERARHIJO TIMOV. Vzel bom TEAM in ga premaknil čez KOLONE.
Zdaj se bo prva stvar, ki se bo tukaj zgodila, dobila napačne odgovore. To je zelo, zelo normalno, če dobiš napačne odgovore. Torej, kar bomo storili, bomo kliknili USTVARI. Če ste leta '16, lahko AUTO-DETECT. Pretvarjajmo se, da so v Excelu 2013, kjer gremo do naše PRODAJNE MIZE. Tam je polje z imenom SALES REP in je povezano z HIERARHIJO, polje z imenom SALES REP, kliknite OK in dobili bomo pravilne odgovore. Mike, poglejmo, kaj imaš.
Mike: Hvala. Da, podatkovni model je odličen način, da z dvema različnima tabelama sestavite eno vrtilno tabelo in to je res moja najprimernejša metoda, toda če bi morali to narediti s formulo in bi morali na vrhu vsakega stolpca imeti PRODAJNI TIM takole to pomeni, da moramo s formulo dobesedno preučiti ta nabor podatkov in za vsak zapis moram vprašati, ali je REPRODAJA PRODAJE = Gigi ali Chin ali Sandy ali Sheila, nato pa, če gre za moram reči, neto prodaja je regija Severna Amerika.
No, to lahko storimo. V funkciji SUMIFS lahko naredimo logični test AND in logični test OR. SUM_RANGE, to so vse številke, zato bom kliknil v zgornjo celico, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, poudaril bom celoten stolpec SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Zdaj običajno v merila damo en izdelek, kot je JUNIJSKA PRODAJA. To pomeni, da SUMIFS izpusti en odgovor za JUNIJ, če pa izpostavim 4 različne celice - 1 za vsakega prodajnega predstavnika - SUMSIFS-u naročimo, naj naredi SUMIF za vsakega posameznega prodajnega predstavnika.
Zdaj, ko kopiram to formulo navzdol, jo moram zakleniti, vendar jo kopiram na stran, premakniti se mora. Torej, tipko F4 moram pritisniti dvakrat dvakrat, zakleniti vrstico, ne pa stolpca. Zdaj grem). To je funkcija argumentirane matrike. To je argument funkcije. Dejstvo, da imamo več elementov, pomeni, da gre za matrično operacijo. Ko sem kliknil na koncu in pritisnil F9, nas je SUMIFS ubogal. Izpljunil je skupni znesek za junij, Sioux, Poppi in Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Zdaj moramo te zneske še omejiti z dodajanjem pogoja AND. Resnično moramo biti junij in Severna Amerika ali Sioux in Severna Amerika ali Poppi in Severna Amerika itd. CONTROL + Z. Preprosto razširimo, KRITERIJI OBMOČJE 2. Zdaj moramo pogledati stolpec REGIJA. CONTROL + SHIFT + DOWNROW + F4 in kliknil bom en sam pogoj, F4 1, 2, 3-krat, da zaklenem stolpec, ne pa tudi vrstice. Če kliknem na koncu in F9, so to skupni zneski za vsakega našega prodajnega predstavnika v Severni Ameriki. Ko ga kopiramo, bo SUMIFS dostavil vsoto za vsa prodajnega predstavnika v Južni Ameriki. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Upoštevajte, da moramo samo SUMIFS dostaviti več številk, ki jih moramo dodati. CONTROL + Z. Torej, lahko bi ga dal v to funkcijo SUM, vendar argument ŠTEVILA 1 funkcije SUM ne bo pravilno izračunal te operacije polja, ne da bi uporabil CONTROL + SHIFT + ENTER. Torej, varal bom in uporabil SUMPRODUCT. Zdaj običajno SUMPRODUCT vzame več nizov in jih pomnoži - to je del PROIZVODA - in jih nato doda, vendar bom samo uporabil ARRAY1 in uporabil samo SUM del SUMPRODUCT,), CONTROL + ENTER, kopirajte navzdol in na stran, in ker imam veliko norih referenc na celice, bom prišel do zadnjega v F2 in, seveda, vse celice in obsegi so pravilni. Vredu. Vrnil se bom nazaj. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Kaj? To je noro. Mike. Pokažite na Mikea. O moj bog. Če v SUMIFS vnesete obseg vrednosti in ga nato pošljete v SUMPRODUCTS, naj ga obravnava kot ARRAY. Hej, to je divje. Kar tam bi se morali ustaviti. Pokažite na Mikea.
Vredu. Vrnimo se k moji metodi, vendar se pretvarjajmo, da nimate Excela 2013. Vrnili ste se v Excel 2010 ali, še huje, Excel za Mac. Mislim, piše, da je Excel. Nevem. Prav noro me sprašuje, kaj lahko Mac naredi ali ne. Torej, odnesli bomo mojo HIERARHIJSKO TABELO sem in ker, ker VLOOKUP ne more gledati v levo, bom vzel informacije o PRODAJNI REP, CONTROL + X in prilepil. Ja, vem, da znam kazati in ujemati. Danes nisem razpoložen za indeksiranje in ujemanje. V redu, res je preprosto. Tukaj = VLOOKUP, vzemite to ime SALESREP tja, mi pa bomo F4, 2, EXACTMATCHFALSE tako, dvakrat kliknite, da to kopirate. (= OGLED (A4, $ F $ 4: $ G $ 19,2, LAŽNO))
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.
Vredu. No, hej. Zahvaljujem se vam, da ste se ustavili pri tem zelo dolgem podcastu za dvoboje Excel. Naslednjič se vidimo v naslednji epizodi iz programa ExcelIsFun.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Duel188.xlsm
