Sie haben einen Bericht mit Verkäufen für 16 Vertriebsmitarbeiter. Jeder Vertriebsmitarbeiter gehört zu einem Team. Wie können Sie einen Bericht erstellen, in dem der Gesamtumsatz für jedes Team angezeigt wird?
Schau Video
- Erstellen Sie einen Verkaufsbericht nach Region und Team
- Originaldaten haben Vertriebsmitarbeiter und Region
- Ein zweiter (schlecht geformter) Tisch organisiert die Vertriebsmitarbeiter in Teams
- Rechnungsmethode 1: Formen Sie die Daten der Teamhierarchie neu. Machen Sie beide Bereiche zu Strg + T-Tabellen
- Erstellen Sie eine Pivot-Tabelle und fügen Sie die Daten dem Datenmodell hinzu. Team vom zweiten Tisch ziehen.
- Erstellen Sie eine Beziehung
- Mike Method2: Erstellen Sie ein SUMIFS, bei dem das Feld Criteria2 ein Array ist!
- Übergeben Sie die SUMIFS an die Funktion SUMPRODUCT
- Rechnungsmethode 3: Ordnen Sie die Hierarchietabelle neu an, sodass sich der Vertriebsmitarbeiter links befindet.
- Fügen Sie den Originaldaten einen VLOOKUP hinzu
- Erstellen Sie eine Pivot-Tabelle
- Mike Methode 4: Verwenden Sie das Beziehungssymbol auf der Registerkarte Daten des Menübands
- Wenn Sie die Pivot-Tabelle erstellen, wählen Sie Datenmodell dieser Arbeitsmappe verwenden
- Rechnungsmethode 5: Power Query. Fügen Sie die Nachschlagetabelle nur als Verbindung hinzu
- Fügen Sie die ursprüngliche Tabelle nur als Suche hinzu
- Führen Sie diese beiden Tabellen zusammen und gruppieren Sie sie, um den Abschlussbericht zu erstellen
Video-Transkript
Duell mit ExcelPodcast, Folge 188: Bericht des Verkaufsteams nach Regionen.
Bill: Hey. Willkommen zurück. Es ist Zeit für einen weiteren Duell-Excel-Podcast. Ich bin Bill Jelen von. Ich werde von Mike Girvin von ExcelIsFun begleitet. Dies ist unsere Folge 188, Verkaufsteambericht nach Region.
Also gut, hier ist die Frage, die wir haben, ein Datensatz hier mit verschiedenen Vertriebsmitarbeitern, wie hoch ihre Verkäufe nach Regionen waren, und einige Leute haben Verkäufe in beiden Regionen, und dann hat das Unternehmen diese 16 Vertriebsmitarbeiter in diese vier Verkäufe unterteilt Wir versuchen für jedes Verkaufsteam herauszufinden, wie viel Umsatz sie hatten.
In Ordung. Mein Ansatz dazu ist, dass ich dieses Format hier nicht mag. Ich werde dieses Format in eine Art Tabelle umordnen, eine kleine Hierarchie, die für jedes Team zeigt, wer die Vertriebsmitarbeiter sind, und dann, sofern wir in Excel 2013 oder Excel 2016 mit Windows und nicht mit einem Mac arbeiten Dann können wir das Datenmodell verwenden, und um dies zu tun, müssen wir jede dieser Tabellen und FORMAT AS TABLE, die CONTROL + T ist, nehmen. Es gibt also die erste Tabelle, die sie Tabelle 8 nennen, und die zweite Tabelle, die sie Tabelle 9 nennen. Ich werde diese umbenennen. Ich werde den ersten nehmen und ich werde es VERKAUFTABELLE nennen und ich werde den zweiten nehmen und ich werde es TEAM HIERARCHIE nennen, so. In Ordung.
Überprüfen Sie dies jetzt. Ab Excel 2013 erstellen wir auf der Registerkarte EINFÜGEN eine PIVOT-TABELLE aus dem ersten Datensatz. Wir sagen jedoch, DIESE DATEN ZUM DATENMODELL HINZUFÜGEN. Dies ist die langweiligste Methode, um Sie darüber zu informieren, dass sich die Power Pivot-Engine tatsächlich hinter Excel befindet 2013. Auch wenn Sie nicht für Power Pivot bezahlen, auch wenn Sie nur über Excel Office 365 oder Excel auf Basisebene verfügen, haben Sie dies. Also gut, hier ist unser neuer Bericht und ich werde definitiv von REGION berichten, also gibt es die REGIONEN, und ich möchte den Gesamtverkauf sehen, aber ich möchte dies vom Verkaufsteam betrachten. Überprüfen Sie dies heraus. Ich werde ALLES auswählen und das gibt mir die anderen Tabellen in dieser Gruppe, einschließlich TEAM HIERARCHY. Ich nehme das TEAM und bewege es über die SPALTEN.
Das erste, was hier passieren wird, ist, dass wir die falschen Antworten bekommen. Das ist sehr, sehr normal, um die falschen Antworten zu bekommen. Also werden wir auf CREATE klicken. Wenn Sie in '16 sind, können Sie AUTO-DETECT. Stellen wir uns vor, sie befinden sich in Excel 2013, wo wir zu unserer VERKAUFTABELLE gehen. Dort gibt es ein Feld namens SALES REP, das sich auf die HIERARCHIE bezieht. Das Feld heißt SALES REP. Klicken Sie auf OK, und wir haben die richtigen Antworten. Mike, mal sehen, was du hast.
Mike: Danke. Ja, das Datenmodell ist eine großartige Möglichkeit, mit zwei verschiedenen Tabellen eine Pivot-Tabelle zu erstellen, und das ist wirklich meine bevorzugte Methode. Wenn Sie dies jedoch mit einer Formel tun müssten und SALES TEAM oben in jeder Spalte haben müssten Das bedeutet, dass wir mit der Formel diesen Datensatz buchstäblich durchsehen müssen und für jeden Datensatz, den ich fragen muss, der SALES REP = für Gigi oder Chin oder Sandy oder Sheila ist, und dann, wenn es ein ist Nettoverkauf, muss ich sagen, und ist die Region Nordamerika.
Nun, das können wir tun. In der Funktion SUMIFS können wir einen logischen UND-Test und einen logischen ODER-Test durchführen. SUM_RANGE, das sind alle Zahlen, also klicke ich in die obere Zelle, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, ich werde die gesamte Spalte SALESREP hervorheben, CONTROL + SHIFT + DOWNARROW + F4 ,. Normalerweise setzen wir jetzt einen einzelnen Artikel wie JUNI SALES REP in Kriterien ein. Das weist SUMIFS an, eine Antwort für JUNI auszuspucken. Wenn ich jedoch 4 verschiedene Zellen hervorhole - 1 für jeden Vertriebsmitarbeiter -, weisen wir SUMSIFS an, für jeden einzelnen Vertriebsmitarbeiter einen SUMIF zu erstellen.
Wenn ich diese Formel nach unten kopiere, muss sie gesperrt sein, aber ich kopiere sie zur Seite, sie muss verschoben werden. Also muss ich 1, 2 mal die F4-Taste drücken, die Zeile sperren, aber nicht die Spalte. Jetzt gehe ich). Dies ist eine Funktionsargument-Array-Operation. Das ist das Funktionsargument. Die Tatsache, dass wir mehrere Elemente haben, bedeutet, dass es sich um eine Array-Operation handelt. Als ich am Ende klickte und F9 drückte, gehorchte SUMIFS uns. Es spuckte den Gesamtbetrag für June, Sioux, Poppi und Tyrone aus. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Jetzt müssen wir diese Beträge durch Hinzufügen einer UND-Bedingung weiter begrenzen. Wir brauchen es wirklich Juni und Nordamerika oder Sioux und Nordamerika oder Poppi und Nordamerika und so weiter. STEUERUNG + Z. Wir erweitern einfach, KRITERIENBEREICH 2. Jetzt müssen wir die Spalte REGION durchsehen. CONTROL + SHIFT + DOWNARROW + F4, und ich werde 1 Mal auf die einzelne Bedingung F4 1, 2, 3 klicken, um die Spalte, aber nicht die Zeile zu sperren. Wenn ich auf das Ende und F9 klicke, sind dies die Summen für jeden unserer Vertriebsmitarbeiter in Nordamerika. Wenn wir es nach unten kopieren, liefert SUMIFS die Gesamtsumme für jeden Vertriebsmitarbeiter für Südamerika. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Beachten Sie, dass es sich nur um SUMIFS handelt, die mehrere Zahlen liefern, die wir hinzufügen müssen. STEUERUNG + Z. Ich könnte es also in diese SUM-Funktion einfügen, aber das Argument SUM-Funktion NUMBER 1 berechnet diese Array-Operation nicht korrekt, ohne CONTROL + SHIFT + ENTER zu verwenden. Also werde ich betrügen und SUMPRODUCT verwenden. Normalerweise nimmt SUMPRODUCT mehrere Arrays und multipliziert sie - das ist der PRODUCT-Teil - und fügt sie dann hinzu, aber ich verwende nur ARRAY1 und verwende nur den SUMROD-Teil von SUMPRODUCT,), CONTROL + ENTER, kopiere es runter und rüber zur Seite, und da ich viele verrückte Zellreferenzen habe, werde ich zum letzten in F2 kommen und sicher genug, dass alle Zellen und Bereiche korrekt sind. In Ordung. Ich werde zurückwerfen. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Bill: Was? Das ist verrückt. Mike. Zeigen Sie auf Mike. Oh mein Gott. Fügen Sie einen Wertebereich in SUMIFS ein und senden Sie ihn dann an SUMPRODUCTS, damit er wie ein ARRAY behandelt wird. Hey, das ist wild. Wir sollten einfach genau dort anhalten. Zeigen Sie auf Mike.
In Ordung. Kehren wir zu meiner Methode zurück, tun aber so, als hätten Sie kein Excel 2013. Sie befinden sich wieder in Excel 2010 oder, schlimmer noch, in Excel für den Mac. Ich meine, es heißt Excel. Ich weiß es nicht. Es macht mich einfach verrückt, was der Mac kann oder nicht kann. Also nehmen wir meine HIERARCHIE-TABELLE hierher und da VLOOKUP nicht nach links schauen kann, nehme ich die SALES REP-Informationen CONTROL + X und füge sie ein. Ja, ich weiß, dass ich Index und Match machen kann. Ich bin nicht in der Stimmung, heute Index und Match zu machen. Also gut, es ist wirklich einfach. Hier, = VLOOKUP, nimm den SALESREP-Namen dort drüben, und wir werden F4, 2, EXACTMATCHFALSE so doppelklicken, um ihn nach unten zu kopieren. (= VLOOKUP (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.
In Ordung. Nun, hey. Ich möchte mich bei Ihnen für Ihren Besuch bei diesem sehr langen Duell-Excel-Podcast bedanken. Wir sehen uns beim nächsten Mal für eine weitere Episode von und ExcelIsFun.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Duel188.xlsm
