Sinulla on raportti, joka näyttää 16 myyntiedustajan myynnin. Jokainen myyntiedustaja kuuluu tiimiin. Kuinka voit luoda raportin, joka näyttää jokaisen tiimin kokonaismyynnin?
Katso video
- Luo myyntiraportti alueittain ja tiimittäin
- Alkuperäisillä tiedoilla on myyntiedustaja ja alue
- Toinen (huonosti muotoiltu) pöytä järjestää myyntiedustajat joukkueille
- Laskutustapa 1: Muuta joukkueen hierarkiatiedot uudelleen. Tee molemmat alueet Ctrl + T-taulukoiksi
- Luo pivot-taulukko lisäämällä tiedot tietomalliin. Vedä joukkue toisesta pöydästä.
- Luo suhde
- Mike Method2: Rakenna SUMIFS, jossa Criteria2-kenttä on taulukko!
- Siirrä SUMIFS SUMPRODUCT-toimintoon
- Laskutustapa 3: Järjestä hierarkiataulukko uudelleen niin, että myyntiedustaja on vasemmalla.
- Lisää VLOOKUP alkuperäisiin tietoihin
- Rakenna pivot-taulukko
- Mike-menetelmä 4: Käytä nauhan Data-välilehden Relationship-kuvaketta
- Kun luot pivot-taulukon, valitse Käytä tämän työkirjan tietomallia
- Bill-menetelmä 5: Power Query. Lisää hakutaulukko vain yhteydeksi
- Lisää alkuperäinen taulukko vain hakuna
- Yhdistä nämä kaksi taulukkoa ryhmittelemällä lopullisen raportin tuottamiseksi
Videon transkriptio
Dueling ExcelPodcast, Episode 188: Myyntitiimiraportti alueittain.
Bill: Hei. Tervetuloa takaisin. On aika uudelle Dueling Excel Podcastille. Olen Bill Jelen. Minuun liittyy Mike Girvin ExcelIsFunista. Tämä on jakso 188, Myyntitiimin raportti alueittain.
Selvä, tässä on kysymys, täällä oleva tietojoukko, jossa on erilaisia myyntiedustajia, kuinka paljon heidän myyntiään oli alueittain, ja joillakin ihmisillä on myyntiä molemmilla alueilla, ja sitten yritys on järjestänyt nämä 16 myyntiedustajaa näihin neljään myyntiin ja yritämme selvittää kullekin myyntitiimille, kuinka paljon heillä oli tuloja.
Hyvä on. Joten lähestymistapani tähän on, tiedätkö, en pidä tästä muodosta täällä. Aion järjestää tämän muodon uudelleen jonkinlaiseksi taulukoksi, tässä pieneksi hierarkiaksi, joka näyttää jokaiselle tiimille kuka on myyntiedustaja, ja sitten, jos oletamme, että olemme Excel 2013: ssa tai Excel 2016: ssa Windowsia eikä Macia , niin voimme hyödyntää tietomallia, ja tätä varten meidän on otettava jokainen näistä taulukoista ja Muotoile taulukoksi, joka on CONTROL + T. Joten siellä on ensimmäinen taulukko, jota he kutsuvat taulukoksi 8, ja toinen taulukko, jota he kutsuvat taulukoksi 9. Aion nimetä nämä uudelleen. Otan ensimmäisen ja kutsun sitä myyntitaulukoksi ja otan toisen ja kutsun sitä TEAM HIERARCHYksi. Hyvä on.
Tarkista nyt tämä. Excel 2013: stä alkaen INSERT-välilehdellä luodaan KÄYTTÖTAULUKKO ensimmäisestä tietojoukosta, mutta sanomme LISÄÄ TÄTÄ TIETOJA TIETOMALLIIN, mikä on tylsin tapa ilmoittaa, että Power Pivot -moottori istuu todella Excelin takana 2013. Vaikka et maksaisi Power Pivotista, sinulla on se, vaikka sinulla olisi vain perustason Excel Office 365 tai Excel. Selvä, tässä on siis uusi raporttimme ja mitä aion tehdä, haluan ehdottomasti raportoida REGION: n mukaan, joten siellä on REGIONS, ja haluan nähdä koko MYYNNIN, mutta haluan tarkastella tätä myyntitiimin kautta. Tsekkaa tämä. Aion valita KAIKKI ja se antaa minulle tämän ryhmän muut taulukot, mukaan lukien TEAM HIERARCHY. Otan TEAMin ja siirrän sen sarakkeiden yli.
Ensimmäinen asia, joka täällä tapahtuu, on väärät vastaukset. Se on hyvin, hyvin normaalia saada väärät vastaukset. Joten, mitä aiomme tehdä, napsautamme Luo. Jos olet vuonna 16, voit AUTO-DETECT. Oletetaan, että he ovat Excel 2013: ssa, jossa siirrymme MYYNTIPÖYTÖLLE. Siellä on kenttä nimeltä SALES REP ja se liittyy HIERARCHY-kenttään nimeltä SALES REP, napsauta OK, ja meillä on oikeat vastaukset. Mike, katsotaan mitä sinulla on.
Mike: Kiitos. Kyllä, tietomalli on mahtava tapa edetä kahdella eri taulukolla yhden pivot-taulukon rakentamiseksi, ja se on oikeastaan ensisijainen menetelmäni, mutta jos joudut tekemään sen kaavalla ja jokaisen sarakkeen yläosassa on oltava SALES TEAM näin tämä tarkoittaa, että kaavan avulla meidän on kirjaimellisesti tarkasteltava tätä tietojoukkoa, ja minun on kysyttävä jokaisesta tietueesta, onko MYYNTITIEDOT Gigille tai Chinille tai Sandylle tai Sheilalle, ja sitten, jos se on nettomyynti, minun on sanottava, ja se on Pohjois-Amerikan alue.
No, voimme tehdä sen. Voimme tehdä JA-loogisen testin ja TAI-loogisen testin SUMIFS-funktiossa. SUM_RANGE, nämä ovat kaikki numerot, joten aion napsauttaa yläsolua, CONTROL + VAIHTO + ALAS + F4, CRITERIA_RANGE, korostan koko SALESREP-sarakkeen, CONTROL + VAIHTO + ALAS + F4,. Normaalisti nyt asetamme kriteereihin yhden kohteen, kuten JUNE SALES REP. Se käskee SUMIFSia purkamaan yhden vastauksen kesäkuuhun, mutta jos korostan 4 erilaista solua - 1 kutakin myyntiedustajaa varten - käskemme SUMSIFSia tekemään SUMIF jokaiselle yksittäiselle myyntiedustajalle.
Kun kopioin tämän kaavan alas, tarvitsen sen lukittuna, mutta kopioin sen sivulle, sen on siirryttävä. Joten minun on painettava F4-näppäintä 1, 2 kertaa, lukittava rivi, mutta ei saraketta. Nyt aion). Tämä on funktion argumenttiryhmäoperaatio. Se on funktion argumentti. Se, että meillä on useita kohteita, tarkoittaa, että se on matriisitoiminto. Joten kun napsautan lopussa ja osun F9, SUMIFS totteli meitä. Se sylki kesäkuun, Siouxin, Popin ja Tyronen kokonaissumman. (= SUMMA ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Nyt meidän on rajoitettava näitä määriä edelleen lisäämällä AND-ehto. Tarvitsemme sen olevan kesäkuu ja Pohjois-Amerikka tai Sioux ja Pohjois-Amerikka tai Poppi ja Pohjois-Amerikka jne. CONTROL + Z. Laajennamme yksinkertaisesti CRITERIA RANGE 2. Nyt meidän on tarkasteltava ALUE-saraketta. CONTROL + VAIHTO + ALAS + F4, ja napsautan yksittäistä ehtoa F4 1, 2, 3 kertaa lukitaksesi sarakkeen, mutta en riviä. Jos napsautan lopussa ja F9, nämä ovat kunkin Pohjois-Amerikan myyntiedustajamme kokonaissummat. Kun kopioimme sen alas, SUMIFS toimittaa jokaisen myyntiedustajan kokonaissumman Etelä-Amerikkaan. (= SUMMA ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Huomaa, että vain SUMIFS tuottaa useita numeroita, jotka meidän on lisättävä. CONTROL + Z. Joten voisin laittaa sen tähän SUM-toimintoon, mutta SUM-funktion NUMERO 1 argumentti ei laske tätä taulukko-operaatiota oikein ilman CONTROL + SHIFT + ENTER. Joten aion huijata ja käyttää SUMPRODUCTia. Normaalisti nyt SUMPRODUCT vie useita taulukoita ja kertoo ne - se on PRODUCT-osa - ja lisää ne sitten, mutta aion vain käyttää ARRAY1: tä ja käyttää vain SUMPRODUCT: n SUM-osaa,), CONTROL + ENTER, kopioida se alas ja sivulle, ja koska minulla on paljon hulluja soluviittauksia, aion tulla viimeiseen F2: een, ja tosiaan, kaikki solut ja alueet ovat oikein. Hyvä on. Aion heittää takaisin. (= YHTEENVETO (YHTEENVETO ($ B $ 4: $ B $ 45, $ A $ 4: $ 45 $, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Mitä? Tuo on hullua. Mike. Osoita Mike. Voi jumala. Laittamalla arvoalue SUMIFS: iin ja lähettämällä se sitten SUMPRODUCTS: iin ja saamalla se käsittelemään sitä kuin ARRAY. Hei, se on villiä. Meidän pitäisi vain pysähtyä siellä. Osoita Mike.
Hyvä on. Palataan takaisin menetelmään, mutta teeskennellään, että sinulla ei ole Excel 2013. Olet taas Excel 2010: ssä tai, mikä vielä pahempaa, Excel for Mac. Tarkoitan, se sanoo olevan Excel. Minä en tiedä. Se vain ajaa minut hulluksi, mitä Mac voi tai ei voi tehdä. Joten vietämme HIERARCHY-TAULUKONI tänne, ja koska VLOOKUP ei voi katsoa vasemmalle, aion viedä MYYNTITIEDOT, CONTROL + X ja liittää. Joo, tiedän, että osaan indeksoida ja yhdistää. Minulla ei ole mielialaa indeksoida ja ottella tänään. Selvä, niin, se on todella yksinkertaista. Täällä = VLOOKUP, vie tuo SALESREP-nimi sinne, niin me F4, 2, EXACTMATCHFALSE tuollainen, kaksoisnapsauttamalla kopioit sen alas. (= VLOOKUP (A4, $ 4 $: $ G $ 19,2, EPÄTOSI))
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.
Hyvä on. No hei. Haluan kiittää sinua siitä, että pysähdyit tähän erittäin pitkään Dueling Excel Podcastiin. Nähdään ensi kerralla toisen jakson lähettäjältä ja ExcelIsFun.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Duel188.xlsm
