Imate izvješće o prodaji 16 prodajnih predstavnika. Svaki prodajni predstavnik pripada timu. Kako možete stvoriti izvještaj koji prikazuje ukupnu prodaju za svaki tim?
Gledaj video
- Izradite izvješće o prodaji po regijama i timu
- Izvorni podaci imaju predstavnika prodaje i regiju
- Drugi (loše oblikovan) stol organizira prodajna predstavništva po timovima
- Metoda računa 1: Preoblikujte podatke o hijerarhiji tima. Unesite oba raspona u tablice Ctrl + T
- Stvorite zaokretnu tablicu dodavanjem podataka u podatkovni model. Povucite momčad s drugog stola.
- Stvorite odnos
- Mike Method2: Izradite SUMIFS gdje je polje Criteria2 niz!
- Prenesite SUMIFS u funkciju SUMPRODUCT
- Metoda računa 3: Preuredite hijerarhijsku tablicu tako da je prodajni predstavnik na lijevoj strani.
- Izvornim podacima dodajte VLOOKUP
- Izgradite zaokretnu tablicu
- Mike metoda 4: Upotrijebite ikonu Odnos na kartici Podaci na vrpci
- Kada kreirate zaokretnu tablicu, odaberite Koristi model podataka ove radne knjige
- Metoda računa 5: Power Query. Dodajte tablicu pretraživanja kao samo vezu
- Izvornu tablicu dodajte samo kao pretragu
- Spojite te dvije tablice, grupirajte prema dobivanju konačnog izvješća
Prijepis videozapisa
Dvoboj ExcelPodcast, epizoda 188: Izvještaj prodajnog tima po regijama.
Bill: Hej. Dobrodošao natrag. Vrijeme je za još jedan dvobojni Excel Podcast. Ja sam Bill Jelen iz. Pridružit će mi se Mike Girvin iz ExcelIsFuna. Ovo je naša epizoda 188, Izvještaj prodajnog tima po regijama.
U redu, pa, evo pitanja koje imamo, skupa podataka ovdje s raznim prodajnim predstavnicima, kolika je bila njihova prodaja po regijama, a neki ljudi prodaju u obje regije, a onda je tvrtka organizirala tih 16 prodajnih predstavnika u ove četiri prodaje timova, a mi pokušavamo doznati za svaki prodajni tim koliki su prihod imali.
U redu. Dakle, moj pristup ovome je, znate, ne sviđa mi se ovaj format ovdje. Preuredit ću taj format u nekakvu tablicu, ovdje malo hijerarhije, koja svakom timu pokazuje tko su prodajni zastupnici, a zatim, ako je predviđeno da smo u programu Excel 2013 ili Excel 2016 koristeći Windows, a ne Mac , tada možemo iskoristiti podatkovni model i, da bismo to učinili, moramo uzeti svaku od ovih tablica i OBLIKOVATI KAO TABLICU koja je CONTROL + T. Dakle, postoji prva tablica koju nazivaju tablicom 8 i druga tablica koju će nazvati tablicom 9. Preimenovat ću ih. Uzeti ću prvu i nazvat ću je PRODAJNI STOL, a uzeti drugu i nazvat ću je HIJERARHIJA TIMA, tako. U redu.
Sad, pogledajte ovo. Počevši od programa Excel 2013, na kartici INSERT kreiramo PIVOTNU TABLICU od prvog skupa podataka, ali kažemo DODAJTE OVE PODATKE MODELU PODATAKA što je najdosadniji način da vas obavijestimo da Power Pivot zapravo stoji iza Excela 2013. Čak i ako ne plaćate Power Pivot, čak i ako imate samo osnovnu razinu Excel Office 365 ili Excel, to imate. U redu, evo, evo našeg novog izvještaja i ono što ću učiniti je da definitivno želim izvještavati po REGION-u, dakle postoje REGIONI i želim vidjeti ukupnu PRODAJU, ali želim to pogledati od prodajnog tima. Pogledaj ovo. Odabrat ću SVE, a to mi daje ostale tablice u ovoj grupi, uključujući HIJERARHIJU TIMA. Uzet ću TIM i premjestiti ga preko KOLONA.
Prvo što će se ovdje dogoditi jest da dobijemo pogrešne odgovore. To je vrlo, vrlo normalno da se dobiju pogrešni odgovori. Dakle, ono što ćemo učiniti je da kliknemo STVORI. Ako ste u '16, možete AUTO-DETEKT. Pretvarajmo se da su u programu Excel 2013 gdje idemo na naš PRODAJNI TABELU. Tamo postoji polje pod nazivom PRODAJA REP, a ono je povezano s HIJERARHIJOM, polje pod nazivom PRODAJA REP, kliknite U redu i imamo točne odgovore. Mike, da vidimo što imaš.
Mike: Hvala ,. Da, podatkovni model je sjajan način da se s dvije različite tablice napravi jedna zaokretna tablica i to je stvarno moja preferirana metoda, ali ako biste to morali raditi s formulom, a na vrhu svakog stupca trebao je biti TIM PRODAJE ovako, to znači da s formulom doslovno moramo pregledati ovaj niz podataka i, za svaki zapis, moram pitati, je REPUBLIKA PRODAJE = Gigi ili Chin ili Sandy ili Sheila, a onda, ako je to moram reći da je neto prodaja i to je regija Sjeverna Amerika.
Pa, mi to možemo. U funkciji SUMIFS možemo napraviti logički test AND i logički test OR. SUM_RANGE, to su svi brojevi, pa ću kliknuti u gornjoj ćeliji, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, istaknut ću cijeli stupac SALESREP, CONTROL + SHIFT + DOWNROW + F4,. Sada obično u kriterije stavljamo jednu stavku poput PRODAJE U LIPNJU. To govori SUMIFS-u da ispljune jedan odgovor za LIPANJ, ali ako istaknem 4 različite ćelije - 1 za svakog prodajnog predstavnika - nalažemo SUMSIFS-u da napravi SUMIF za svakog pojedinog prodajnog predstavnika.
Sad, kad kopiram ovu formulu prema dolje, trebam je zaključanu, ali kopiram je sa strane, treba se pomaknuti. Dakle, moram pritisnuti tipku F4 1, 2 puta, zaključati red, ali ne i stupac. Sad idem). Ovo je funkcija argumentacije polja. To je argument funkcije. Činjenica da imamo više stavki znači da je riječ o operaciji niza. Dakle, kad kliknem na kraju i pritisnem F9, SUMIFS nas je poslušao. Ispljunuo je ukupan iznos za June, Sioux, Poppi i Tyrone. (= SUMIFI ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Sada moramo dodatno ograničiti te iznose dodavanjem uvjeta I. Zaista nam treba da to bude lipanj i Sjeverna Amerika ili Sioux i Sjeverna Amerika ili Poppi i Sjeverna Amerika, i tako dalje. CONTROL + Z. Jednostavno proširujemo, KRITERIJUM RANGE 2. Sada moramo pogledati stupac REGION. CONTROL + SHIFT + DOWNROW + F4, a ja ću kliknuti na jedan uvjet, F4 1, 2, 3 puta da zaključam stupac, ali ne i redak. Ako kliknem na kraju i F9, to su ukupni iznosi za svakog našeg prodajnog predstavnika u Sjevernoj Americi. Kad ga kopiramo, SUMIFS će isporučiti ukupan iznos za svakog prodajnog predstavnika za Južnu Ameriku. (= SUMIFI ($ 4 $: $ 45 $, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Primijetite da samo SUMIFS isporučuje više brojeva koje trebamo dodati. CONTROL + Z. Dakle, mogao bih je staviti u ovu funkciju SUM, ali argument SUM funkcije BROJ 1 neće pravilno izračunati ovu operaciju niza bez korištenja CONTROL + SHIFT + ENTER. Dakle, varat ću i koristiti SUMPRODUCT. Sada, normalno, SUMPRODUCT uzima više nizova i množi ih - to je dio PROIZVODA - i zatim ih dodaje, ali ja ću samo koristiti ARRAY1 i samo SUM dio SUMPRODUCT,), CONTROL + ENTER, kopirati prema dolje i na stranu, a budući da imam puno ludih referenci na ćelije, doći ću do posljednje u F2 i, sasvim sigurno, sve stanice i dometi su ispravni. U redu. Vratit ću se na. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Što? To je ludo. Mikrofon. Pokaži na Mikea. O moj Bože. Stavljanje raspona vrijednosti u SUMIFS, a zatim slanje u SUMPRODUCTS i neka se prema njemu ponaša kao prema NIZU. Hej, to je divlje. Trebali bismo se zaustaviti ovdje. Pokaži na Mikea.
U redu. Vratimo se mojoj metodi, ali pretvarajte se da nemate Excel 2013. Vratili ste se u Excel 2010 ili, još gore, Excel za Mac. Mislim, kaže da je to Excel. Ne znam. Jednostavno me izluđuje što Mac može, a što ne može. Dakle, odnijet ćemo ovdje moj HIJERARHIJSKI STOL i, jer VLOOKUP ne može gledati ulijevo, uzet ću informacije o PRODAJI, CONTROL + X i zalijepiti. Da, znam da znam indeksirati i podudarati se. Danas nisam raspoložen za indeksiranje i podudaranje. U redu, pa, stvarno je jednostavno. Evo, = VLOOKUP, uzmi ono ime SALESREP tamo, a mi ćemo F4, 2, EXACTMATCHFALSE tako, dvaput kliknuti da to kopiramo. (= PREGLED (A4, $ F $ 4: $ G $ 19,2, NETOČ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.
U redu. Pa, hej. Želim vam zahvaliti što ste navratili do ovog vrlo dugog dvoboja Excel Podcast. Vidimo se sljedeći put za još jednu epizodu s programa ExcelIsFun.
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Duel188.xlsm
