Hai un rapporto che mostra le vendite di 16 rappresentanti di vendita. Ogni rappresentante di vendita appartiene a una squadra. Come puoi creare un rapporto che mostri le vendite totali per ogni squadra?
Guarda un video
- Crea un rapporto sulle vendite per regione e team
- I dati originali hanno un rappresentante di vendita e una regione
- Un secondo tavolo (mal formato) organizza i rappresentanti di vendita in squadre
- Metodo di fatturazione 1: ridisegna i dati della gerarchia del team. Trasforma entrambi gli intervalli in tabelle Ctrl + T
- Crea una tabella pivot, aggiungendo i dati al modello di dati. Tira la squadra dal secondo tavolo.
- Crea una relazione
- Mike Method2: crea un SUMIFS dove il campo Criteria2 è un array!
- Passare SUMIFS alla funzione SUMPRODUCT
- Metodo di fatturazione 3: riorganizza la tabella gerarchica in modo che il rappresentante di vendita si trovi a sinistra.
- Aggiungi un CERCA.VERT ai dati originali
- Costruisci una tabella pivot
- Mike Metodo 4: utilizzare l'icona Relazione nella scheda Dati della barra multifunzione
- Quando crei la tabella pivot, scegli Usa il modello di dati di questa cartella di lavoro
- Metodo di fatturazione 5: Power Query. Aggiungi la tabella di ricerca come Solo connessione
- Aggiungi la tabella originale solo come ricerca
- Unisci queste due tabelle, raggruppale per produrre il rapporto finale
Trascrizione del video
Dueling ExcelPodcast, episodio 188: Report del team di vendita per regione.
Bill: Hey. Ben tornato. È il momento per un altro Podcast Dueling Excel. Sono Bill Jelen di. Sarò raggiunto da Mike Girvin di ExcelIsFun. Questo è il nostro episodio 188, Report del team di vendita per regione.
Va bene, quindi, ecco la domanda che abbiamo, un set di dati qui con vari rappresentanti di vendita, quanto sono state le loro vendite per regione e alcune persone hanno vendite in entrambe le regioni, e poi la società ha organizzato quei 16 rappresentanti di vendita in queste quattro vendite e stiamo cercando di capire, per ogni team di vendita, l'ammontare delle entrate che hanno ottenuto.
Tutto a posto. Quindi, il mio approccio a questo è, sai, non mi piace questo formato qui. Riorganizzerò quel formato in una sorta di tabella, una piccola gerarchia qui, che mostra per ogni squadra chi sono i rappresentanti di vendita e quindi, se previsto, siamo in Excel 2013 o Excel 2016 utilizzando Windows e non un Mac , quindi possiamo utilizzare il modello dati e, per fare ciò, dobbiamo prendere ciascuna di queste tabelle e FORMAT AS TABLE che è CONTROL + T. Quindi, c'è la prima tabella che chiamano Tabella 8 e la seconda tabella che chiameranno Tabella 9. Le rinominerò. Prenderò il primo e lo chiamerò TABELLA DELLE VENDITE e prenderò il secondo e lo chiamerò GERARCHIA DI SQUADRA, così. Tutto a posto.
Ora, controlla questo. A partire da Excel 2013, nella scheda INSERT, creiamo una TABELLA PIVOT dal primo set di dati ma diciamo AGGIUNGI QUESTI DATI AL MODELLO DI DATI che è il modo più noioso per farti sapere che in realtà hai il motore Power Pivot dietro Excel 2013. Anche se non stai pagando per Power Pivot, anche se hai solo il livello base di Excel, Office 365 o Excel, ce l'hai. Va bene, quindi, ecco il nostro nuovo rapporto e quello che farò è che desidero sicuramente riportare per REGIONE, quindi ci sono le REGIONI, e voglio vedere le VENDITE totali, ma voglio guardarlo dal team di vendita. Controllalo. Scelgo TUTTO e questo mi dà gli altri tavoli in questo gruppo, incluso GERARCHIA DI SQUADRA. Prenderò il TEAM e lo sposterò sulle COLONNE.
Ora, la prima cosa che succederà qui è che otteniamo le risposte sbagliate. È molto, molto normale ottenere risposte sbagliate. Quindi, quello che faremo è fare clic su CREA. Se sei nel '16, puoi AUTO-RILEVARE. Facciamo finta che siano in Excel 2013 dove andiamo alla nostra TABELLA DELLE VENDITE. C'è un campo chiamato RAPPRESENTANTE DI VENDITA ed è correlato alla GERARCHIA, campo chiamato RAPPRESENTANTE DI VENDITA, fare clic su OK e abbiamo le risposte corrette. Mike, vediamo cosa hai.
Mike: Grazie ,. Sì, il modello di dati è un modo fantastico per utilizzare due tabelle diverse per creare una tabella pivot e questo è davvero il mio metodo preferito, ma se dovevi farlo con una formula e dovevi avere TEAM DI VENDITA nella parte superiore di ogni colonna in questo modo, significa che, con la formula, dobbiamo letteralmente guardare attraverso questo set di dati e, per ogni record, devo chiedere, è il COMMERCIALE = a Gigi o Chin o Sandy o Sheila, e poi, se è un vendita netta, devo dire, ed è la regione del Nord America.
Bene, possiamo farlo. Possiamo eseguire un test logico AND e un test logico OR nella funzione SUMIFS. SUM_RANGE, quelli sono tutti i numeri, quindi farò clic nella cella in alto, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, evidenzierò l'intera colonna SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Ora, normalmente inseriamo un singolo elemento come RAPPORTO VENDITE DI GIUGNO nei criteri. Questo dice a SUMSIFS di sputare una risposta per GIUGNO, ma, se evidenzi 4 celle diverse, 1 per ogni rappresentante di vendita, stiamo istruendo SUMSIFS a fare un SUMIF per ogni singolo rappresentante di vendita.
Ora, quando copio questa formula, ho bisogno che sia bloccata, ma la copio di lato, deve spostarsi. Quindi, devo premere il tasto F4 1, 2 volte, bloccare la riga, ma non la colonna. Ora vado a). Questa è un'operazione su array di argomenti di una funzione. Questo è l'argomento della funzione. Il fatto che abbiamo più elementi significa che è un'operazione di matrice. Quindi, quando clicco alla fine e premo F9, SUMIFS ci ha obbedito. Sputa l'importo totale per June, Sioux, Poppi e Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Ora, dobbiamo limitare ulteriormente tali importi aggiungendo una condizione AND. Abbiamo davvero bisogno che sia giugno e il Nord America o Sioux e il Nord America o Poppi e il Nord America e così via. CTRL + Z. Estendiamo semplicemente, CRITERIA RANGE 2. Ora dobbiamo guardare attraverso la colonna REGION. CTRL + MAIUSC + FRECCIA GIÙ + F4 e farò clic sulla singola condizione, F4 1, 2, 3 volte per bloccare la colonna ma non la riga. Se clicco alla fine e F9, questi sono i totali per ciascuno dei nostri rappresentanti di vendita in Nord America. Quando lo copiamo, SUMIFS fornirà il totale per ogni rappresentante di vendita per il Sud America. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Si noti che è solo SUMIFS che fornisce più numeri che dobbiamo aggiungere. CTRL + Z. Quindi, potrei metterlo in questa funzione SOMMA ma l'argomento NUMERO 1 della funzione SOMMA non calcolerà correttamente questa operazione di matrice senza usare CONTROL + MAIUSC + INVIO. Quindi, ho intenzione di imbrogliare e utilizzare SUMPRODUCT. Ora, normalmente, SUMPRODUCT prende più array e li moltiplica - questa è la parte PRODOTTO - e poi li aggiunge, ma userò semplicemente ARRAY1 e userò solo la parte SUM di SUMPRODUCT,), CONTROL + INVIO, copialo di lato, e dato che ho molti riferimenti di celle pazzi, arriverò all'ultimo in F2 e, abbastanza sicuro, ha tutte le celle e gli intervalli corretti. Tutto a posto. Tornerò a. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: cosa? Questo è pazzesco. Mike. Indica Mike. Oh mio Dio. Mettere un intervallo di valori in SUMIFS e quindi inviarlo in SUMPRODUCTS e farlo trattare come un ARRAY. Ehi, è selvaggio. Dovremmo semplicemente fermarci qui. Indica Mike.
Tutto a posto. Torniamo al mio metodo ma fingi di non avere Excel 2013. Sei tornato in Excel 2010 o, peggio, in Excel per Mac. Voglio dire, dice che è Excel. Non lo so. Mi fa impazzire quello che il Mac può o non può fare. Quindi, porteremo la mia TABELLA DELLA GERARCHIA qui e, poiché CERCA.VERT non può guardare a sinistra, prenderò le informazioni RAPPRESENTANTE VENDITE, CONTROL + X e incollerò. Sì, so di poter eseguire l'indice e la corrispondenza. Non sono dell'umore giusto per indicizzare e abbinare oggi. Va bene, quindi, è davvero semplice. Qui, = VLOOKUP, prendi quel nome COMMESREP laggiù, e faremo F4, 2, EXACTMATCHFALSE in questo modo, fai doppio clic per copiarlo. (= CERCA.VERT (A4, $ F $ 4: $ G $ 19,2, FALSO))
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.
Tutto a posto. Beh ciao. Voglio ringraziarti per esserti fermato per questo lunghissimo Dueling Excel Podcast. Ci vediamo la prossima volta per un altro episodio di ed ExcelIsFun.
Download file
Scarica il file di esempio qui: Duel188.xlsm
