Vous disposez d'un rapport indiquant les ventes de 16 commerciaux. Chaque commercial appartient à une équipe. Comment créer un rapport indiquant les ventes totales de chaque équipe?
Regarder la vidéo
- Créer un rapport de ventes par région et par équipe
- Les données d'origine ont un représentant commercial et une région
- Une deuxième table (mal formée) organise les commerciaux en équipes
- Méthode de facturation 1: remodeler les données de la hiérarchie de l'équipe. Transformez les deux plages en tables Ctrl + T
- Créez un tableau croisé dynamique en ajoutant les données au modèle de données. Tirez l'équipe de la deuxième table.
- Créer une relation
- Mike Method2: Construisez un SUMIFS où le champ Criteria2 est un tableau!
- Passez le SUMIFS à la fonction SUMPRODUCT
- Méthode de facturation 3: Réorganisez la table hiérarchique de sorte que le représentant commercial soit sur la gauche.
- Ajouter un RECHERCHEV aux données d'origine
- Créer un tableau croisé dynamique
- Mike Méthode 4: utilisez l'icône Relation dans l'onglet Données du ruban
- Lorsque vous créez le tableau croisé dynamique, choisissez Utiliser le modèle de données de ce classeur
- Méthode de facturation 5: Power Query. Ajouter la table de recherche en tant que connexion uniquement
- Ajouter la table d'origine à titre de recherche uniquement
- Fusionnez ces deux tableaux, regroupez-les pour produire le rapport final
Transcription vidéo
Dueling ExcelPodcast, Episode 188: Rapport de l'équipe de vente par région.
Bill: Salut. Nous saluons le retour. Il est temps pour un autre podcast Dueling Excel. Je suis Bill Jelen. Je serai accompagné de Mike Girvin d'ExcelIsFun. Ceci est notre épisode 188, Rapport de l'équipe commerciale par région.
D'accord, alors, voici la question que nous nous posons, un ensemble de données ici avec divers représentants commerciaux, le montant de leurs ventes par région, et certaines personnes ont des ventes dans les deux régions, puis la société a organisé ces 16 représentants commerciaux dans ces quatre ventes. équipes, et nous essayons de déterminer, pour chaque équipe de vente, combien de revenus ils ont eu.
Bien. Donc, mon approche à ce sujet est, vous savez, je n'aime pas ce format ici. Je vais réorganiser ce format en une sorte de tableau, une petite hiérarchie ici, qui montre pour chaque équipe qui sont les commerciaux et ensuite, si à condition que nous soyons dans Excel 2013 ou Excel 2016 en utilisant Windows et non un Mac , alors nous pouvons utiliser le modèle de données et, pour ce faire, nous devons prendre chacune de ces tables et FORMAT COMME TABLE qui est CONTROL + T. Donc, il y a la première table qu'ils appellent Table 8 et la deuxième table qu'ils appelleront Table 9. Je vais les renommer. Je vais prendre le premier et je vais l'appeler TABLE DES VENTES et je vais prendre le second et je vais l'appeler TEAM HIERARCHY, comme ça. Bien.
Maintenant, vérifiez ceci. À partir d'Excel 2013, sur l'onglet INSÉRER, nous créons une TABLE PIVOT à partir du premier ensemble de données, mais nous disons AJOUTER CES DONNÉES AU MODÈLE DE DONNÉES, ce qui est le moyen le plus ennuyeux de vous faire savoir que vous avez réellement le moteur Power Pivot derrière Excel 2013. Même si vous ne payez pas pour Power Pivot, même si vous n'avez que le niveau de base Excel Office 365 ou Excel, vous l'avez. Très bien, voici notre nouveau rapport et ce que je vais faire, c'est que je veux vraiment faire un rapport par RÉGION, donc il y a les RÉGIONS, et je veux voir le total des VENTES, mais je veux regarder cela par l'équipe de vente. Regarde ça. Je vais choisir TOUS et cela me donne les autres tables de ce groupe, y compris TEAM HIERARCHY. Je vais prendre l'ÉQUIPE et la déplacer à travers les COLONNES.
Maintenant, la première chose qui va arriver ici, c'est que nous obtenons les mauvaises réponses. C'est très, très normal d'obtenir les mauvaises réponses. Donc, ce que nous allons faire est de cliquer sur CRÉER. Si vous êtes en '16, vous pouvez AUTO-DETECT. Imaginons qu'ils soient dans Excel 2013 où nous allons à notre TABLE DES VENTES. Il y a un champ appelé SALES REP et il est lié à la HIÉRARCHIE, champ appelé SALES REP, cliquez sur OK et nous avons les bonnes réponses. Mike, voyons ce que tu as.
Mike: Merci. Oui, le modèle de données est un excellent moyen de créer deux tableaux différents pour créer un tableau croisé dynamique et c'est vraiment ma méthode préférée, mais si vous deviez le faire avec une formule et que vous deviez avoir une équipe de vente en haut de chaque colonne comme ça, cela signifie, avec la formule, nous devons littéralement regarder à travers cet ensemble de données et, pour chaque enregistrement, je dois demander, est le VENTES REP = à Gigi ou Chin ou Sandy ou Sheila, et ensuite, si c'est un vente nette, je dois dire, et c'est la région Amérique du Nord.
Eh bien, nous pouvons le faire. Nous pouvons faire un test logique AND et un test logique OR dans la fonction SUMIFS. SUM_RANGE, ce sont tous les nombres, donc je vais cliquer dans la cellule du haut, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, je vais mettre en évidence toute la colonne SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Maintenant, normalement, nous mettons un seul élément comme JUNE SALES REP dans les critères. Cela indique à SUMIFS de cracher une réponse pour JUIN, mais si je surligne 4 cellules différentes - 1 pour chaque représentant commercial - nous demandons à SUMSIFS de faire un SUMIF pour chaque représentant commercial individuel.
Maintenant, quand je copie cette formule vers le bas, j'ai besoin qu'elle soit verrouillée, mais je la copie sur le côté, elle doit bouger. Donc, je dois appuyer sur la touche F4 1, 2 fois, verrouiller la ligne, mais pas la colonne. Maintenant, je vais). Il s'agit d'une opération de tableau d'arguments de fonction. C'est l'argument de la fonction. Le fait que nous ayons plusieurs éléments signifie que c'est une opération de tableau. Donc, quand je clique à la fin et frappe F9, SUMIFS nous a obéi. Il a craché le montant total pour June, Sioux, Poppi et Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Maintenant, nous devons limiter davantage ces montants en ajoutant une condition AND. Nous avons vraiment besoin que ce soit juin et l'Amérique du Nord ou Sioux et l'Amérique du Nord ou Poppi et l'Amérique du Nord, et ainsi de suite. CONTRÔLE + Z. Nous étendons simplement, CRITERIA RANGE 2. Nous devons maintenant parcourir la colonne REGION. CONTROL + SHIFT + DOWNARROW + F4, et je vais cliquer sur la seule condition, F4 1, 2, 3 fois pour verrouiller la colonne mais pas la ligne. Si je clique à la fin et sur F9, ce sont les totaux pour chacun de nos représentants commerciaux en Amérique du Nord. Lorsque nous le copierons, SUMIFS fournira le total pour chaque représentant commercial pour l'Amérique du Sud. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Notez que c'est juste SUMIFS qui fournit plusieurs numéros que nous devons ajouter. CONTROL + Z. Donc, je pourrais le mettre dans cette fonction SOMME mais l'argument NUMÉRO 1 de la fonction SOMME ne calculera pas correctement cette opération de tableau sans utiliser CONTROL + SHIFT + ENTER. Donc, je vais tricher et utiliser SUMPRODUCT. Maintenant, normalement, SUMPRODUCT prend plusieurs tableaux et les multiplie - c'est la partie PRODUCT - puis les ajoute, mais je vais simplement utiliser ARRAY1 et utiliser simplement la partie SUM de SUMPRODUCT,), CONTROL + ENTER, copiez-le De bas en haut sur le côté, et comme j'ai beaucoup de références de cellules folles, je vais en venir à la dernière en F2 et, bien sûr, toutes les cellules et plages sont correctes. Bien. Je vais y revenir. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Quoi? C'est fou. Mike. Montrez Mike. Oh mon Dieu. Mettre une plage de valeurs dans SUMIFS, puis l'envoyer dans SUMPRODUCTS et le faire traiter comme un ARRAY. Hé, c'est sauvage. Nous devrions simplement nous arrêter là. Montrez Mike.
Bien. Revenons à ma méthode mais prétendons que vous n'avez pas Excel 2013. Vous êtes de retour dans Excel 2010 ou, pire, Excel pour Mac. Je veux dire, ça dit que c'est Excel. Je ne sais pas. Cela me rend fou de ce que le Mac peut ou ne peut pas faire. Donc, nous allons prendre mon TABLEAU DE HIÉRARCHIE ici, et, parce que RECHERCHEV ne peut pas regarder à gauche, je vais prendre les informations du représentant des ventes, CONTROL + X, et coller. Ouais, je sais que je peux faire des index et des correspondances. Je ne suis pas d'humeur à faire des index et des correspondances aujourd'hui. D'accord, c'est vraiment simple. Ici, = RECHERCHEV, prenez ce nom SALESREP là-bas, et nous ferons F4, 2, EXACTMATCHFALSE comme ça, double-cliquez pour le copier. (= RECHERCHEV (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.
Bien. Et bien Salut. Je tiens à vous remercier d'être passé pour ce très long podcast Dueling Excel. Nous vous verrons la prochaine fois pour un autre épisode de et ExcelIsFun.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Duel188.xlsm
