Van egy jelentése, amely 16 értékesítési képviselő eladását mutatja be. Minden értékesítési képviselő egy csapathoz tartozik. Hogyan készíthet jelentést, amely bemutatja az egyes csapatok összes eladását?
Videót nézni
- Készítsen értékesítési jelentést régiónként és csapatonként
- Az eredeti adatoknak van értékesítési képviselője és régiója
- Egy második (rosszul formázott) asztal szervezi a csapatok értékesítési képviselőit
- Bill módszer 1: Átalakítsa a csapat hierarchiájának adatait. Tegye mindkét tartományt Ctrl + T táblákká
- Hozzon létre egy forgatótáblát, hozzáadva az adatokat az adatmodellhez. Húzza a csapatot a második asztalról.
- Hozz létre kapcsolatot
- Mike Method2: Készítsen SUMIFS-ot, ahol a Criteria2 mező egy tömb!
- Adja át a SUMIFS-ot a SUMPRODUCT függvénybe
- Bill 3. módszer: Átrendezze a hierarchia táblázatot úgy, hogy az értékesítési képviselő a bal oldalon legyen.
- VLOOKUP hozzáadása az eredeti adatokhoz
- Készítsen forgóasztalt
- Mike 4. módszer: Használja a kapcsolat ikont a szalag Adatok lapján
- A forgatótábla létrehozásakor válassza a A munkafüzet adatmodelljének használata lehetőséget
- Bill 5. módszer: Teljesítmény lekérdezés. Adja hozzá a keresési táblázatot csak kapcsolatként
- Az eredeti táblázatot csak keresésként adja hozzá
- A végleges jelentés elkészítéséhez egyesítse ezt a két táblázatot
Videó átirat
Párbaj az ExcelPodcast, 188. rész: Értékesítési csapat jelentése régiónként.
Bill: Hé. Üdv újra. Itt az ideje egy újabb Dueling Excel Podcastnak. Bill Jelen vagyok. Mike Girvin lesz az ExcelIsFun munkatársa. Ez a 188. részünk, az értékesítési csapat jelentése régiónként.
Rendben, szóval itt van a kérdés, itt egy adatkészlet, különféle értékesítési képviselőkkel, mennyi volt az eladásuk régiónként, és vannak, akik mindkét régióban értékesítenek, és akkor a vállalat e négy értékesítésbe szervezte ezt a 16 értékesítést csapatokat, és megpróbáljuk minden egyes értékesítési csapat számára kitalálni, hogy mekkora bevételük volt.
Rendben. Szóval, ehhez a megközelítésemhez tudod, hogy nem szeretem ezt a formátumot itt. Átrendezem ezt a formátumot valamiféle táblává, itt egy kis hierarchiává, amely minden csapat számára megmutatja, hogy kik az értékesítési képviselők, majd ha feltesszük, hogy az Excel 2013-ban vagy az Excel 2016-ban Windows-ot használunk, nem pedig Mac-et , akkor felhasználhatjuk az adatmodellt, és ennek érdekében ezeket a táblázatokat és a FORMÁTUMOT TÁBLÁZATOKKAK kell vennünk, ami a CONTROL + T. Tehát ott van az első táblázat, amelyet ők 8. táblázatnak hívnak, és a második táblázat, amelyet a 9. táblázatnak hívnak. Ezeket át fogom nevezni. Az elsőt fogom venni és SALES TABLE-nak fogom nevezni, a másodikat pedig TEAM HIERARCHY-nak fogom nevezni, így. Rendben.
Most nézze meg ezt. Az Excel 2013-tól kezdődően az INSERT fülön létrehozunk egy PIVOT TÁBLÁZATOT az első adatsorból, de azt mondjuk, hogy HOZZÁADJA EZT AZ ADATOT AZ ADATMODELHEZ, ami a legunalmasabb módja annak, hogy tudassa veled, hogy valóban a Power Pivot motor ül az Excel mögött 2013. Még akkor is, ha nem fizet a Power Pivotért, még akkor is, ha csak az alapszintű Excel Office 365 vagy Excel rendelkezik. Rendben, íme, itt van az új jelentésünk, és azt fogom tenni, hogy mindenképpen REGION-onként szeretnék beszámolni, tehát ott vannak a REGION-ok, és szeretném megnézni a teljes ÉRTÉKESÍTÉST, de ezt az értékesítési csapat szeretné megnézni. Ezt nézd meg. Az ALL-t fogom választani, és ez adja a csoport többi tábláját, beleértve a TEAM HIERARCHY-t is. Fogom a CSAPATOT, és áthelyezem az Oszlopokra.
Az első dolog, ami itt fog történni, az, hogy rossz válaszokat kapunk. Nagyon-nagyon normális, ha rossz válaszokat kapunk. Tehát azt fogjuk tenni, hogy a CREATE gombra kattintunk. Ha 16 éves vagy, akkor AUTO-DETECT. Tegyük fel, hogy az Excel 2013-ban vannak, ahol az ÉRTESÍTÉSI TÁBLÁZATUNKhoz megyünk. Van egy SALES REP nevű mező, amely a HIERARCHY-hoz kapcsolódik, az SALES REP nevű mező, kattintson az OK gombra, és megkapjuk a helyes válaszokat. Mike, nézzük meg, mi van.
Mike: Köszönöm. Igen, az adatmodell fantasztikus módszer két különböző táblával haladni egy pivot tábla felépítéséhez, és ez az én előnyben részesített módszerem, de ha képlettel kellett csinálnod, és minden oszlop tetején SALES TEAM kellett volna mint ez, ez azt jelenti, hogy a képlettel szó szerint át kell néznünk ezt az adatsort, és minden egyes rekordnál fel kell tennem a kérdést, hogy az ÉRTÉKESÍTÉSI GÉP = Giginek vagy Chinnek, vagy Sandynek vagy Sheilának, majd ha ez a nettó eladás, azt kell mondanom, és Észak-Amerika régiója.
Nos, meg tudjuk csinálni. A SUMIFS függvényben AND logikai tesztet és OR logikai tesztet is elvégezhetünk. SUM_RANGE, ezek mind a számok, ezért rákattintok a felső cellára, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, a teljes SALESREP oszlopot emelem ki, CONTROL + SHIFT + DOWNARROW + F4,. Most általában egyetlen elemet, például JÚNIUS ÉRTÉKESÍTÉSI FELTÉTELT teszünk a kritériumok közé. Ez azt mondja a SUMIFS-nak, hogy kössön ki egy választ JÚNIUSRA, de ha 4 különböző cellát emelek ki - 1-t minden értékesítési képviselőnél -, akkor arra utasítjuk a SUMSIFS-t, hogy készítsen SUMIF-ot minden egyes értékesítési képviselő számára.
Amikor ezt a képletet lefelé másolom, zárva kell, de oldalra másolom, mozognia kell. Tehát, meg kell nyomnom az F4 billentyűt 1, 2x, be kell zárnom a sort, de nem az oszlopot. Most megyek). Ez egy függvény argumentum tömb művelet. Ez a függvény argumentuma. Az a tény, hogy több elemünk van, tömbműveletet jelent. Tehát, amikor a végén rákattintok és megnyomom az F9 billentyűt, a SUMIFS engedelmeskedett nekünk. Kiköpte a június, Sioux, Poppi és Tyrone teljes összegét. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Most tovább kell korlátoznunk ezeket az összegeket egy AND feltétel hozzáadásával. Nagyon szükségünk van rá, hogy június és Észak-Amerika vagy Sioux és Észak-Amerika vagy Poppi és Észak-Amerika stb. CONTROL + Z. Egyszerűen kibővítjük a 2. KRITÉRIUMKÖRT. Most át kell néznünk a REGION oszlopot. CONTROL + SHIFT + DOWNARROW + F4, és rákattintok az egyetlen feltételre, az F4-re 1, 2, 3 alkalommal, hogy lezárjam az oszlopot, de a sort ne. Ha a végén és az F9 gombra kattintok, akkor ezek az összes észak-amerikai értékesítési képviselőnk összesítései. Amikor lemásoljuk, a SUMIFS szállítja az összes értékesítési képviselő teljes összegét Dél-Amerikában. (= ÖSSZEFOGLALÁS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, E8))
Figyelje meg, hogy csak a SUMIFS szolgáltat több számot, amelyet hozzá kell adnunk. CONTROL + Z. Tehát be tudnám tenni ebbe a SUM függvénybe, de az 1. SZÁM függvény argumentum nem fogja megfelelően kiszámítani ezt a tömbműveletet a CONTROL + SHIFT + ENTER használata nélkül. Szóval meg fogok csalni és használni fogom a SUMPRODUCT-ot. Most általában a SUMPRODUCT több tömböt vesz fel, és megszorozza őket - ez a PRODUCT része -, majd összeadja őket, de én csak az ARRAY1-et fogom használni, és csak a SUMPRODUCT SUM részét használom, lefelé, és mivel sok őrült cellahivatkozást kaptam, az F2-nél az utolsóhoz fogok jutni, és bizony, minden cella és tartomány helyes. Rendben. Vissza fogok dobni. (= ÖSSZEFOGLALÁS (ÖSSZEFOGLALÓ ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, E8)))
Bill: Mi van? Ez őrültség. Mikrofon. Mutasson Mike-ra. A mindenit. Ha beír egy értéktartományt a SUMIFS-ba, majd elküldi a SUMPRODUCTS-be, és arra készteti, hogy ARRAY-ként kezelje. Hé, ez vad. Csak ott kellene megállnunk. Mutasson Mike-ra.
Rendben. Térjünk vissza a módszeremhez, de tegyünk úgy, mintha nem rendelkezne Excel 2013-mal. Visszatért az Excel 2010-be, vagy ami még rosszabb, az Excel for Mac-be. Mármint azt mondja, hogy ez az Excel. Nem tudom. Csak megőrjít, amit a Mac képes vagy nem. Tehát ide fogjuk vinni a HIERARCHY TÁBLÁMAT, és mivel a VLOOKUP nem tud balra nézni, átveszem a SALES REP információkat, a CONTROL + X-et és beillesztem. Igen, tudom, hogy tudok indexelni és egyeztetni. Ma nincs kedvem indexelni és mérkőzni. Rendben, szóval, nagyon egyszerű. Itt, = VLOOKUP, vigye oda a SALESREP nevet, és mi hasonlóan F4, 2, EXACTMATCHFALSE fogunk lenni, dupla kattintással másoljuk le. (= VLOOKUP (A4, $ 4 USD: $ G $ 19,2, HAMIS))
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.
Rendben. Hát hello. Szeretném megköszönni, hogy meglátogatta ezt a nagyon hosszú Dueling Excel Podcast-ot. Legközelebb találkozunk az ExcelIsFun újabb epizódjával.
Fájl letöltése
Töltse le a minta fájlt innen: Duel188.xlsm
