Έχετε μια αναφορά που δείχνει τις πωλήσεις για 16 πωλήσεις. Κάθε αντιπρόσωπος πωλήσεων ανήκει σε μια ομάδα. Πώς μπορείτε να δημιουργήσετε μια αναφορά που να δείχνει τις συνολικές πωλήσεις για κάθε ομάδα;
Δες το βίντεο
- Δημιουργήστε μια αναφορά πωλήσεων ανά περιοχή και ομάδα
- Τα αρχικά δεδομένα έχουν πωλήσεις και περιοχή πωλήσεων
- Ένας δεύτερος (άσχημα διαμορφωμένος) πίνακας οργανώνει τους αντιπροσώπους πωλήσεων σε ομάδες
- Μέθοδος χρέωσης 1: Αναδιαμορφώστε τα δεδομένα ιεραρχίας ομάδας. Κάντε και τα δύο εύρη σε πίνακες Ctrl + T
- Δημιουργήστε έναν συγκεντρωτικό πίνακα, προσθέτοντας τα δεδομένα στο μοντέλο δεδομένων. Τραβήξτε την ομάδα από το δεύτερο τραπέζι.
- Δημιουργήστε μια σχέση
- Μέθοδος Mike2: Δημιουργήστε ένα SUMIFS όπου το πεδίο Κριτήρια2 είναι ένας πίνακας!
- Περάστε το SUMIFS στη συνάρτηση SUMPRODUCT
- Μέθοδος λογαριασμού 3: Αναδιάταξη του πίνακα ιεραρχίας, ώστε ο αντιπρόσωπος πωλήσεων να βρίσκεται στα αριστερά.
- Προσθέστε ένα VLOOKUP στα αρχικά δεδομένα
- Δημιουργήστε έναν συγκεντρωτικό πίνακα
- Μέθοδος Mike 4: Χρησιμοποιήστε το εικονίδιο Σχέση στην καρτέλα Δεδομένα της κορδέλας
- Όταν δημιουργείτε τον συγκεντρωτικό πίνακα, επιλέξτε Χρήση του μοντέλου δεδομένων αυτού του βιβλίου εργασίας
- Μέθοδος λογαριασμού 5: Power Query. Προσθέστε τον πίνακα αναζήτησης ως σύνδεση μόνο
- Προσθέστε τον αρχικό πίνακα μόνο ως αναζήτηση
- Συγχωνεύστε αυτούς τους δύο πίνακες, ομαδοποιήστε για να δημιουργήσετε την τελική αναφορά
Μεταγραφή βίντεο
Dueling ExcelPodcast, Episode 188: Αναφορά ομάδας πωλήσεων ανά περιοχή.
Μπιλ: Γεια. Καλως ορισες πισω. Ήρθε η ώρα για ένα άλλο Dueling Excel Podcast. Είμαι ο Μπιλ Τζέλεν από. Θα ενωθώ με τον Mike Girvin από το ExcelIsFun. Αυτό είναι το επεισόδιο 188, Αναφορά ομάδας πωλήσεων ανά περιοχή.
Εντάξει, λοιπόν, εδώ είναι η ερώτηση που έχουμε, ένα σύνολο δεδομένων εδώ με διάφορους αντιπροσώπους πωλήσεων, πόσο οι πωλήσεις τους ήταν ανά περιοχή και μερικοί άνθρωποι έχουν πωλήσεις και στις δύο περιοχές και στη συνέχεια η εταιρεία έχει οργανώσει αυτές τις 16 εκπρόσωποι πωλήσεων σε αυτές τις τέσσερις πωλήσεις ομάδες και προσπαθούμε να υπολογίσουμε, για κάθε ομάδα πωλήσεων, πόσα έσοδα είχαν.
Καλώς. Έτσι, η προσέγγισή μου σε αυτό είναι, ξέρετε, δεν μου αρέσει αυτή η μορφή εδώ. Θα αναδιατάξω αυτήν τη μορφή σε κάποιο είδος πίνακα, μια μικρή ιεραρχία εδώ, που δείχνει για κάθε ομάδα ποιοι είναι οι αντιπρόσωποι πωλήσεων και, στη συνέχεια, εάν παρέχεται στο Excel 2013 ή στο Excel 2016 χρησιμοποιώντας Windows και όχι σε Mac , τότε μπορούμε να κάνουμε χρήση του μοντέλου δεδομένων και, για να το κάνουμε αυτό, πρέπει να πάρουμε καθένα από αυτούς τους πίνακες και FORMAT AS TABLE που είναι CONTROL + T. Λοιπόν, υπάρχει ο πρώτος πίνακας που ονομάζουν Πίνακας 8 και ο δεύτερος πίνακας που θα ονομάσουν Πίνακας 9. Θα τα μετονομάσω. Θα πάρω το πρώτο και θα το ονομάσω ΠΙΝΑΚΑΣ ΠΩΛΗΣΕΩΝ και θα πάρω το δεύτερο και θα το ονομάσω ΟΜΑΔΑ ΙΕΡΑΡΧΙΑ, έτσι. Καλώς.
Τώρα, δείτε αυτό. Ξεκινώντας από το Excel 2013, στην καρτέλα INSERT, δημιουργούμε έναν ΠΙΝΑΚΑ PIVOT από το πρώτο σύνολο δεδομένων, αλλά λέμε ΠΡΟΣΘΗΚΗ ΑΥΤΩΝ ΤΩΝ ΔΕΔΟΜΕΝΩΝ ΣΤΟ ΜΟΝΤΕΛΟ ΔΕΔΟΜΕΝΩΝ, ο οποίος είναι ο πιο βαρετός τρόπος για να σας ενημερώσουμε ότι στην πραγματικότητα έχετε τον κινητήρα Power Pivot πίσω από το Excel 2013. Ακόμα κι αν δεν πληρώνετε για Power Pivot, ακόμα και αν έχετε το βασικό επίπεδο Excel Office 365 ή Excel, έχετε αυτό. Εντάξει, λοιπόν, εδώ είναι η νέα μας αναφορά και αυτό που πρόκειται να κάνω είναι σίγουρα θέλω να αναφέρω από την REGION, οπότε υπάρχουν οι REGIONS και θέλω να δω τις συνολικές ΠΩΛΗΣΕΙΣ αλλά θέλω να το εξετάσω από την ομάδα πωλήσεων. Κοίτα αυτό. Θα επιλέξω ΟΛΑ και αυτό μου δίνει τα άλλα τραπέζια σε αυτήν την ομάδα, συμπεριλαμβανομένης της ΟΜΑΔΑΣ ΙΕΡΑΡΧΙΑ. Θα πάρω την ΟΜΑΔΑ και θα τη μετακινήσω στις ΣΤΗΛΕΣ.
Τώρα, το πρώτο πράγμα που θα συμβεί εδώ είναι να έχουμε λάθος απαντήσεις. Είναι πολύ, πολύ φυσιολογικό να παίρνεις λάθος απαντήσεις. Έτσι, αυτό που πρόκειται να κάνουμε είναι να κάνουμε κλικ στη ΔΗΜΙΟΥΡΓΙΑ. Εάν βρίσκεστε στο '16, μπορείτε να ΑΥΤΟΜΑΤΟ-ΑΝΙΧΝΕΥΣΤΕ. Ας προσποιηθούμε ότι είναι στο Excel 2013 όπου πηγαίνουμε στον ΠΙΝΑΚΑ ΠΩΛΗΣΕΩΝ. Υπάρχει ένα πεδίο που ονομάζεται SALES REP και σχετίζεται με το HIERARCHY, το πεδίο που ονομάζεται SALES REP, κάντε κλικ στο OK και έχουμε τις σωστές απαντήσεις. Μάικ, ας δούμε τι έχετε.
Mike: Ευχαριστώ ,. Ναι, το μοντέλο δεδομένων είναι ένας καταπληκτικός τρόπος να πάει με δύο διαφορετικούς πίνακες για να δημιουργήσετε έναν συγκεντρωτικό πίνακα και αυτή είναι πραγματικά η προτιμώμενη μέθοδος μου, αλλά αν έπρεπε να το κάνετε με έναν τύπο και έπρεπε να έχετε SALES TEAM στην κορυφή κάθε στήλης έτσι, αυτό σημαίνει, με τον τύπο, κυριολεκτικά πρέπει να κοιτάξουμε αυτό το σύνολο δεδομένων και, για κάθε εγγραφή, πρέπει να ρωτήσω, είναι το SALES REP = σε Gigi ή Chin ή Sandy ή Sheila, και τότε, εάν είναι καθαρή πώληση, πρέπει να πω, και είναι η περιοχή της Βόρειας Αμερικής.
Λοιπόν, μπορούμε να το κάνουμε αυτό. Μπορούμε να κάνουμε μια λογική δοκιμή AND και μια λογική δοκιμή Ή στη συνάρτηση SUMIFS. SUM_RANGE, αυτοί είναι όλοι οι αριθμοί, οπότε θα κάνω κλικ στο πάνω κελί, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, θα επισημάνω ολόκληρη τη στήλη SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Τώρα, συνήθως βάζουμε ένα μεμονωμένο στοιχείο όπως το JUNE SALES REP σε κριτήρια. Αυτό λέει στο SUMIFS να δώσει μια απάντηση για τον Ιούνιο, αλλά, αν επισημάνω 4 διαφορετικά κελιά - 1 για κάθε εκπρόσωπο πωλήσεων - δίνουμε οδηγίες στη SUMSIFS να κάνει SUMIF για κάθε μεμονωμένο αντιπρόσωπο πωλήσεων.
Τώρα, όταν αντιγράφω αυτόν τον τύπο προς τα κάτω, το χρειάζομαι κλειδωμένο, αλλά το αντιγράφω στο πλάι, πρέπει να μετακινηθεί. Έτσι, πρέπει να πατήσω το πλήκτρο F4 1, 2 φορές, να κλειδώσω τη σειρά, αλλά όχι τη στήλη. Τώρα θα πάω). Αυτή είναι μια λειτουργία συστοιχίας ορίσματος συνάρτησης. Αυτό είναι το επιχείρημα συνάρτησης. Το γεγονός ότι έχουμε πολλά στοιχεία σημαίνει ότι είναι μια λειτουργία πίνακα. Έτσι, όταν κάνω κλικ στο τέλος και πατήσω το F9, το SUMIFS μας υπακούει. Έβγαλε το συνολικό ποσό για τον Ιούνιο, το Sioux, το Poppi και το Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Τώρα, πρέπει να περιορίσουμε περαιτέρω αυτά τα ποσά προσθέτοντας μια συνθήκη AND. Χρειαζόμαστε πραγματικά τον Ιούνιο και τη Βόρεια Αμερική ή το Sioux και τη Βόρεια Αμερική ή το Poppi και τη Βόρεια Αμερική και ούτω καθεξής. CONTROL + Z. Απλώς επεκτείνουμε, CRITERIA RANGE 2. Τώρα πρέπει να κοιτάξουμε τη στήλη REGION. CONTROL + SHIFT + DOWNARROW + F4 και θα κάνω κλικ στη μεμονωμένη συνθήκη, F4 1, 2, 3 φορές για να κλειδώσω τη στήλη αλλά όχι τη σειρά. Αν κάνω κλικ στο τέλος και στο F9, αυτά είναι τα σύνολα για κάθε έναν από τους αντιπροσώπους πωλήσεών μας στη Βόρεια Αμερική. Όταν το αντιγράψουμε, το SUMIFS θα παραδώσει το σύνολο για κάθε αντιπρόσωπο πωλήσεων για τη Νότια Αμερική. (= SUMIFS ($ 4 $: $ B $ 45, $ 4 $: 45 $ $, 4 $: 7 $, 4 $ 4: $ 45 $, $ 8))
Παρατηρήστε ότι το SUMIFS παρέχει πολλούς αριθμούς που πρέπει να προσθέσουμε. CONTROL + Z. Έτσι, θα μπορούσα να το βάλω σε αυτήν τη συνάρτηση SUM, αλλά το όρισμα ΑΡΙΘΜΟΣ 1 της συνάρτησης SUM δεν θα υπολογίσει σωστά αυτήν τη λειτουργία πίνακα χωρίς να χρησιμοποιήσετε το CONTROL + SHIFT + ENTER. Λοιπόν, θα εξαπατήσω και θα χρησιμοποιήσω το SUMPRODUCT. Τώρα, κανονικά, το SUMPRODUCT παίρνει πολλές συστοιχίες και πολλαπλασιάζει - αυτό είναι το τμήμα PRODUCT - και στη συνέχεια τα προσθέτει, αλλά απλώς πρόκειται να χρησιμοποιήσω το ARRAY1 και απλά να χρησιμοποιήσω το μέρος SUM του SUMPRODUCT κάτω και πάλι στο πλάι, και δεδομένου ότι έχω πολλές τρελές αναφορές κυττάρων, θα έρθω στο τελευταίο στο F2 και, σίγουρα, έχει όλα τα κελιά και τα εύρη σωστά. Καλώς. Θα το ρίξω πίσω. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, A $ 4: $ A $ 45, F $ 4: F $ 7, C $ 4: $ C $ 45, E8 $))))
Μπιλ: Τι; Αυτό είναι τρελό. Μικρόφωνο. Δείξτε στον Mike. Ω Θεέ μου. Βάζοντας ένα εύρος τιμών στο SUMIFS και μετά το στέλνουμε σε SUMPRODUCTS και κάνε το να το αντιμετωπίζει σαν ARRAY. Γεια, αυτό είναι άγριο. Πρέπει ακριβώς να σταματήσουμε εκεί. Δείξτε στον Mike.
Alright. Let's go back to my method but pretend that you don't have Excel 2013. You're back in Excel 2010 or, worse, Excel for the Mac. I mean, it says it's Excel. I don't know. It just drives me crazy what the Mac can or can't do. So, we’re going to take my HIERARCHY TABLE over here, and, because VLOOKUP can't look to the left, I'm going to take the SALES REP information, CONTROL+X, and paste. Yeah, I know I can do index and match. I'm not in the mood to do index and match today. Alright, so, it's really simple. Here, =VLOOKUP, take that SALESREP name over there, and we will F4 , 2 , EXACTMATCHFALSE like that, double click to copy that down. (=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.
Καλώς. Λοιπόν, γεια. Θέλω να σας ευχαριστήσω που σταματήσατε για αυτό το πολύ καιρό Dueling Excel Podcast. Θα σας δούμε την επόμενη φορά για ένα άλλο επεισόδιο από και το ExcelIsFun.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Duel188.xlsm
