Turite ataskaitą, kurioje parodyti 16 pardavimo atstovų pardavimai. Kiekvienas prekybos atstovas priklauso komandai. Kaip galite sukurti ataskaitą, kurioje būtų parodyti bendri kiekvienos komandos pardavimai?
Žiūrėti video
- Sukurkite pardavimo ataskaitą pagal regioną ir komandą
- Originalūs duomenys turi pardavimo atstovą ir regioną
- Antroji (blogos formos) lentelė organizuoja pardavimo atstovus komandoms
- 1 sąskaitos metodas: pertvarkykite komandos hierarchijos duomenis. Padarykite abu diapazonus į Ctrl + T lenteles
- Sukurkite suvestinę lentelę, pridėdami duomenis prie duomenų modelio. Ištraukite komandą iš antros lentelės.
- Sukurti santykius
- Mike'o metodas2: sukurkite SUMIFS, kur laukas Kriterijai2 yra masyvas!
- Perduokite SUMIFS į funkciją SUMPRODUCT
- 3 Billo metodas: pertvarkykite hierarchijos lentelę, kad pardavimo atstovas būtų kairėje.
- Pridėkite VLOOKUP prie pradinių duomenų
- Sukurkite sukamą lentelę
- Mike 4 metodas: naudokite juostos skirtuke Duomenys esančią santykio piktogramą
- Kai kuriate suvestinę lentelę, pasirinkite Naudoti šios darbaknygės duomenų modelį
- Bilo 5 metodas: galios užklausa. Paieškos lentelę pridėkite tik kaip ryšį
- Pridėkite originalią lentelę tik kaip paiešką
- Sujunkite šias dvi lenteles, sugrupuokite pagal galutinę ataskaitą
Vaizdo įrašo nuorašas
„ExcelPodcast“, 188 serijos dvikova: pardavimo grupės ataskaita pagal regioną.
Bilas: Ei. Sveikas sugrįžęs. Atėjo laikas kitam „Dueling Excel“ tinklui. Aš esu Billas iš. Prie manęs prisijungs Mike'as Girvinas iš „ExcelIsFun“. Tai yra mūsų 188 epizodas „Pardavimų grupės ataskaita pagal regioną“.
Gerai, štai, čia yra mūsų pateiktas klausimas, duomenų rinkinys su įvairiais pardavimo atstovais, kiek jų pardavimai buvo pagal regionus, o kai kurie žmonės parduoda abiejuose regionuose, o tada bendrovė suskirstė tuos 16 pardavimo atstovų į šiuos keturis pardavimus. komandos ir mes stengiamės kiekvienai pardavimų komandai išsiaiškinti, kiek jie turėjo pajamų.
Gerai. Taigi, mano požiūris į tai yra, jūs žinote, man nepatinka šis formatas čia. Aš ketinu pertvarkyti šį formatą į tam tikrą lentelę, šiek tiek čia hierarchijos, kuri kiekvienai komandai parodo, kas yra pardavimų atstovai, ir tada, jei numatyta, kad mes esame „Excel 2013“ arba „Excel 2016“ naudodami „Windows“, o ne „Mac“ , tada mes galime naudoti duomenų modelį ir, norėdami tai padaryti, turime atsižvelgti į kiekvieną iš šių lentelių ir FORMATUOTI KAIP LENTEL which, kuri yra CONTROL + T. Taigi yra pirmoji lentelė, kurią jie vadins 8 lentele, ir antroji lentelė, kurią jie vadins 9 lentele. Aš pervardysiu juos. Aš imsiuosi pirmojo ir pavadinsiu jį PARDAVIMŲ LENTELE, o antrą - vadinsiu Komandos HIERARCHIJA. Gerai.
Dabar patikrinkite tai. Pradedant „Excel 2013“ skirtuke INSERT, mes sukuriame PIVOT LENTELLE iš pirmojo duomenų rinkinio, tačiau sakome PRIDĖTI ŠIUS DUOMENIS Į DUOMENŲ MODELĮ, kuris yra nuobodžiausias būdas pranešti, kad iš tikrųjų „Power Pivot“ variklis sėdi už „Excel“ 2013. Net jei nemokate už „Power Pivot“, net jei turite tik pagrindinio lygio „Excel Office 365“ ar „Excel“, turite tai. Gerai, štai, čia yra mūsų nauja ataskaita ir ką aš darysiu, aš tikrai noriu pranešti pagal REGION, taigi yra REGIONAI, ir aš noriu pamatyti bendrą PARDAVIMĄ, bet noriu į tai žiūrėti pardavimų komanda. Pažiūrėk. Aš rinksiuosi VISAS ir tai suteiks man kitas šios grupės lenteles, įskaitant „TEAM HIERARCHY“. Paimsiu Komandą ir perkelsiu ją per kolonas.
Pirmas dalykas, kuris čia įvyks, yra neteisingi atsakymai. Tai labai labai normalu gauti neteisingus atsakymus. Taigi, ką mes padarysime, tai paspausime KURTI. Jei esate 16-os metų, galite AUTO-DETECT. Apsimeskime, kad jie yra „Excel 2013“, kur einame į mūsų PARDAVIMŲ LENTEL.. Ten yra laukas, pavadintas PARDAVIMŲ REP, ir jis susijęs su HIERARCHIJA, laukas PARDAVIMŲ REP, spustelėkite Gerai, ir mes turime teisingus atsakymus. Mike, pažiūrėkime, ką tu turi.
Mike: Ačiū. Taip, duomenų modelis yra puikus būdas su dviem skirtingomis lentelėmis sukurti vieną suvestinę lentelę, ir tai tikrai mano pageidaujamas metodas, tačiau jei tai turėtumėte padaryti naudodami formulę ir kiekvieno stulpelio viršuje turėtumėte turėti PARDAVIMŲ KOMANDĄ tai reiškia, kad pagal formulę turime pažodžiui pažvelgti į šį duomenų rinkinį ir kiekvienam įrašui turiu paklausti, ar PARDAVIMŲ REP = Gigi ar Chin, ar Sandy, ar Sheila, ir tada, jei tai yra grynasis pardavimas, turiu pasakyti, ir yra Šiaurės Amerikos regionas.
Na, mes galime tai padaryti. Funkcijoje SUMIFS galime atlikti AND loginį ir OR loginį testą. SUM_RANGE, tai visi skaičiai, todėl paspaudžiu viršutiniame langelyje „CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE“, paryškinsiu visą „SALESREP“ stulpelį „CONTROL + SHIFT + DOWNARROW + F4“. Dabar paprastai į kriterijus įtraukiame vieną elementą, pvz., BIRŽELIO PARDAVIMŲ REP. Tai liepia SUMIFS išpjauti vieną atsakymą birželio mėn., Tačiau, jei paryškinsiu 4 skirtingus langelius - po 1 kiekvienai pardavimų atstovybei, nurodome SUMSIFS atlikti SUMIF kiekvienam atskiram pardavimo atstovui.
Dabar, kai aš nukopijuoju šią formulę žemyn, man reikia, kad ji būtų užrakinta, bet aš ją nukopijuoju į šoną, ji turi judėti. Taigi, aš turiu paspausti F4 klavišą 1, 2 kartus, užrakinti eilutę, bet ne stulpelį. Dabar einu). Tai yra funkcijos argumento masyvo operacija. Tai funkcijos argumentas. Tai, kad turime kelis elementus, reiškia, kad tai yra masyvo operacija. Taigi, spustelėjus pabaigoje ir paspaudus F9, SUMIFS mums pakluso. Ji išspjovė bendrą birželio, Sioux, Poppi ir Tyrone sumą. (= SUMIFAI ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Dabar turime toliau riboti šias sumas, pridėdami sąlygą AND. Mums labai reikia, kad tai būtų birželis ir Šiaurės Amerika arba Sioux ir Šiaurės Amerika arba Poppi ir Šiaurės Amerika ir pan. CONTROL + Z. Paprasčiausiai pratęsiame „CRITERIA RANGE 2“. Dabar turime peržiūrėti stulpelį REGIONAS. CONTROL + SHIFT + DOWNARROW + F4, ir aš spustelėsiu vieną sąlygą F4 1, 2, 3 kartus, kad užfiksuočiau stulpelį, bet ne eilutę. Jei paspaudžiu pabaigoje ir F9, tai yra kiekvieno mūsų pardavimo atstovo Šiaurės Amerikoje bendra suma. Kai mes jį nukopijuosime, SUMIFS pateiks kiekvieno pardavimo atstovo Pietų Amerikoje bendrą sumą. (= SUMIFAI ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, E8))
Atkreipkite dėmesį, kad tiesiog SUMIFS pateikia kelis skaičius, kuriuos turime pridėti. CONTROL + Z. Taigi, galėčiau jį įtraukti į šią SUM funkciją, tačiau SUM funkcijos NUMERIS 1 argumentas neteiks šios masyvo operacijos teisingai, nenaudojant CONTROL + SHIFT + ENTER. Taigi, aš apgausiu ir naudosiu „SUMPRODUCT“. Dabar paprastai SUMPRODUCT ima kelis masyvus ir juos padaugina - tai yra „PRODUCT“ dalis - ir tada juos prideda, bet aš tiesiog naudosiu „ARRAY1“ ir tiesiog naudosiu „SUMPRODUCT“ SUM dalį,), „CONTROL + ENTER“, nukopijuosiu ją žemyn ir atgal į šoną, ir kadangi gavau daug beprotiškų langelių nuorodų, eisiu prie paskutinio F2 ir, tikrai, visi langeliai ir diapazonai yra teisingi. Gerai. Ketinu mesti atgal. (= SUMPRODUKTAS (SUMIFAI ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, E8)))
Bilas: Ką? Tai Beprotiska. Maikas. Parodykite į Mike'ą. O Dieve. Į SUMIFS įtraukdami reikšmių diapazoną, tada nusiųsdami jį į SUMPRODUCTS ir priverskite jį traktuoti kaip ARRAY. Ei, tai laukinė. Turėtume tiesiog sustoti čia pat. Parodykite į Mike'ą.
Gerai. Grįžkime prie mano metodo, bet apsimeskime, kad neturite „Excel 2013.“. Grįžote į „Excel 2010“ arba, dar blogiau, „Excel for Mac“. Noriu pasakyti, kad tai „Excel“. Nežinau. Tai mane tiesiog veda iš proto, ką „Mac“ gali ar negali. Taigi, mes čia perimsime mano HIERARCHY LENTEL and ir, kadangi „VLOOKUP“ negali žiūrėti į kairę, aš paimsiu informaciją „SALES REP“, „CONTROL + X“ ir įklijuosiu. Taip, aš žinau, kad moku indeksuoti ir lyginti. Šiandien nesu nusiteikęs daryti indekso ir rungtynių. Gerai, taigi, tai tikrai paprasta. Čia, = VLOOKUP, paimkite tą SALESREP vardą ir mes taip F4, 2, EXACTMATCHFALSE, dukart spustelėkite, jei norite nukopijuoti. (= 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.
Gerai. Na, ei. Noriu padėkoti, kad užsukote į šį labai ilgą „Dueling Excel Podcast“. Kitą kartą pamatysime kitą „ExcelIsFun“ epizodą.
Atsisiųsti failą
Failo pavyzdį atsisiųskite čia: „Duel188.xlsm“
