Bạn có một báo cáo hiển thị doanh số bán hàng cho 16 đại diện bán hàng. Mỗi đại diện bán hàng thuộc về một nhóm. Làm cách nào để bạn có thể tạo báo cáo hiển thị tổng doanh số cho từng nhóm?
Xem video
- Xây dựng Báo cáo Bán hàng theo Khu vực và Nhóm
- Dữ liệu gốc có đại diện bán hàng và khu vực
- Một bảng thứ hai (có hình dạng xấu) sắp xếp các đại diện bán hàng cho các đội
- Phương pháp hóa đơn 1: Định hình lại dữ liệu phân cấp nhóm. Đặt cả hai phạm vi thành bảng Ctrl + T
- Tạo bảng tổng hợp, thêm dữ liệu vào mô hình dữ liệu. Kéo Đội từ bàn thứ hai.
- Tạo mối quan hệ
- Mike Method2: Xây dựng một SUMIFS trong đó trường Criteria2 là một mảng!
- Chuyển SUMIFS vào hàm SUMPRODUCT
- Bill Phương pháp 3: Sắp xếp lại bảng phân cấp sao cho đại diện bán hàng ở bên trái.
- Thêm một VLOOKUP vào dữ liệu gốc
- Tạo bảng tổng hợp
- Mike Phương pháp 4: Sử dụng biểu tượng Mối quan hệ trên tab Dữ liệu của dải băng
- Khi bạn tạo bảng tổng hợp, hãy chọn Sử dụng Mô hình Dữ liệu của Sổ làm việc này
- Phương pháp Bill 5: Power Query. Thêm bảng tra cứu dưới dạng Chỉ kết nối
- Thêm bảng gốc dưới dạng chỉ tra cứu
- Hợp nhất hai bảng đó, nhóm lại để tạo báo cáo cuối cùng
Bản ghi video
Dueling ExcelPodcast, Tập 188: Báo cáo của nhóm bán hàng theo khu vực.
Bill: Này. Chào mừng trở lại. Đã đến lúc cho một Dueling Excel Podcast khác. Tôi là Bill Jelen đến từ. Tôi sẽ được tham gia bởi Mike Girvin từ ExcelIsFun. Đây là tập 188 của chúng tôi, Báo cáo của nhóm bán hàng theo khu vực.
Được rồi, vậy, đây là câu hỏi mà chúng tôi có, một tập dữ liệu ở đây với các đại diện bán hàng khác nhau, doanh số của họ là bao nhiêu theo khu vực và một số người có doanh số bán hàng ở cả hai khu vực và sau đó công ty đã tổ chức 16 đại diện bán hàng đó thành bốn lần bán hàng này và chúng tôi đang cố gắng tìm hiểu xem đối với mỗi nhóm bán hàng, họ có bao nhiêu doanh thu.
Ổn thỏa. Vì vậy, cách tiếp cận của tôi là, bạn biết đấy, tôi không thích định dạng này ở đây. Tôi sẽ sắp xếp lại định dạng đó thành một số loại bảng, một hệ thống phân cấp nhỏ ở đây, hiển thị cho từng nhóm ai là đại diện bán hàng và sau đó, nếu được cung cấp, nếu chúng tôi đang sử dụng Excel 2013 hoặc Excel 2016 sử dụng Windows chứ không phải Mac , sau đó chúng ta có thể sử dụng mô hình dữ liệu, và để làm được điều này, chúng ta phải lấy từng bảng này và ĐỊNH DẠNG NHƯ BẢNG là CONTROL + T. Vì vậy, có bảng đầu tiên mà họ gọi là Bảng 8 và bảng thứ hai mà họ sẽ gọi là Bảng 9. Tôi sẽ đổi tên chúng. Tôi sẽ lấy cái đầu tiên và tôi sẽ gọi nó là BẢNG BÁN HÀNG và tôi sẽ lấy cái thứ hai và tôi sẽ gọi nó là TEAM HIERARCHY, như vậy. Ổn thỏa.
Bây giờ, hãy kiểm tra điều này. Bắt đầu từ Excel 2013, trên tab INSERT, chúng tôi tạo BẢNG PIVOT từ tập dữ liệu đầu tiên nhưng chúng tôi nói THÊM DỮ LIỆU NÀY VÀO MÔ HÌNH DỮ LIỆU, đây là cách nhàm chán nhất để cho bạn biết rằng bạn thực sự có công cụ Power Pivot phía sau Excel 2013. Ngay cả khi bạn không trả tiền cho Power Pivot, ngay cả khi chỉ có Excel Office 365 hoặc Excel cấp cơ sở, bạn vẫn có điều đó. Được rồi, vì vậy, đây là báo cáo mới của chúng tôi và những gì tôi sẽ làm là tôi chắc chắn muốn báo cáo theo REGION, vì vậy, có REGIONS và tôi muốn xem tổng SỐ BÁN HÀNG nhưng tôi muốn xem điều này theo nhóm bán hàng. Kiểm tra điều này. Tôi sẽ chọn TẤT CẢ và điều đó mang lại cho tôi các bảng khác trong nhóm này, bao gồm cả TEAM HIERARCHY. Tôi sẽ lấy ĐỘI và di chuyển nó qua CỘT.
Bây giờ, điều đầu tiên sẽ xảy ra ở đây là chúng ta nhận được câu trả lời sai. Đó là rất, rất bình thường để nhận được câu trả lời sai. Vì vậy, những gì chúng tôi sẽ làm là chúng tôi sẽ nhấp vào TẠO. Nếu bạn đang ở '16, bạn có thể TỰ ĐỘNG PHÁT HIỆN. Hãy giả sử họ đang ở trong Excel 2013, nơi chúng ta chuyển đến BẢNG BÁN HÀNG. Ở đó có một trường được gọi là SALES REP và nó liên quan đến HIERARCHY, trường có tên là SALES REP, nhấp vào OK, và chúng tôi có câu trả lời chính xác. Mike, hãy xem bạn có gì.
Mike: Cảm ơn ,. Đúng, mô hình dữ liệu là một cách tuyệt vời để sử dụng hai bảng khác nhau để tạo một bảng tổng hợp và đó thực sự là phương pháp ưa thích của tôi, nhưng nếu bạn phải làm điều đó với một công thức và bạn cần có ĐỘI NGŨ BÁN HÀNG ở đầu mỗi cột như thế này, nghĩa là, với công thức, chúng ta phải xem qua tập dữ liệu này và đối với mỗi bản ghi, tôi phải hỏi, SALES REP có phải là Gigi hoặc Chin hoặc Sandy hoặc Sheila, và sau đó, nếu đó là Tôi phải nói là bán ròng, và là khu vực Bắc Mỹ.
Chà, chúng ta có thể làm được điều đó. Chúng ta có thể thực hiện kiểm tra logic AND và kiểm tra logic OR trong hàm SUMIFS. SUM_RANGE, đó là tất cả các số, vì vậy tôi sẽ nhấp vào ô trên cùng, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, tôi sẽ đánh dấu toàn bộ cột SALESREP, CONTROL + SHIFT + DOWNARROW + F4 ,. Bây giờ, thông thường chúng tôi đưa một mục như JUNE SALES REP vào tiêu chí. Điều đó yêu cầu SUMIFS đưa ra một câu trả lời cho JUNE, nhưng nếu tôi đánh dấu 4 ô khác nhau - 1 ô cho mỗi đại diện bán hàng - thì chúng tôi sẽ hướng dẫn SUMSIFS thực hiện một SUMIF cho mỗi đại diện bán hàng riêng lẻ.
Bây giờ, khi tôi sao chép công thức này xuống, tôi cần nó bị khóa, nhưng tôi sao chép nó sang một bên, nó cần phải di chuyển. Vì vậy, tôi phải nhấn phím F4 1, 2 lần, khóa hàng, nhưng không phải cột. Bây giờ tôi sẽ). Đây là một hoạt động mảng đối số hàm. Đó là đối số của hàm. Thực tế là chúng ta có nhiều mục có nghĩa đó là một hoạt động mảng. Vì vậy, khi tôi nhấp vào cuối và nhấn F9, SUMIFS đã tuân theo chúng tôi. Nó tính ra tổng số tiền cho June, Sioux, Poppi và Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Bây giờ, chúng ta cần giới hạn thêm những số tiền đó bằng cách thêm điều kiện AND. Chúng tôi thực sự cần nó là tháng Sáu và Bắc Mỹ hoặc Sioux và Bắc Mỹ hoặc Poppi và Bắc Mỹ, v.v. ĐIỀU KHIỂN + Z. Chúng tôi chỉ đơn giản là mở rộng, CRITERIA RANGE 2. Bây giờ chúng ta cần xem qua cột VÙNG. CONTROL + SHIFT + DOWNARROW + F4 và tôi sẽ nhấp vào điều kiện duy nhất, F4 1, 2, 3 lần để khóa cột nhưng không khóa hàng. Nếu tôi nhấp vào cuối và F9, đó là tổng số cho từng đại diện bán hàng của chúng tôi ở Bắc Mỹ. Khi chúng tôi sao chép nó xuống, SUMIFS sẽ cung cấp tổng số tiền cho mỗi đại diện bán hàng ở Nam Mỹ. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Lưu ý rằng nó chỉ là SUMIFS cung cấp nhiều số mà chúng ta cần thêm. ĐIỀU KHIỂN + Z. Vì vậy, tôi có thể đặt nó vào hàm SUM này nhưng đối số của hàm SUM SỐ 1 sẽ không tính toán chính xác hoạt động mảng này nếu không sử dụng CONTROL + SHIFT + ENTER. Vì vậy, tôi sẽ gian lận và sử dụng SUMPRODUCT. Bây giờ, thông thường, SUMPRODUCT lấy nhiều mảng và nhân chúng - đó là phần PRODUCT - và sau đó thêm chúng, nhưng tôi sẽ sử dụng ARRAY1 và chỉ sử dụng phần SUM của SUMPRODUCT,), CONTROL + ENTER, sao chép nó xuống và qua một bên, và vì tôi có rất nhiều tham chiếu ô điên rồ, tôi sẽ đi đến cái cuối cùng trong F2 và chắc chắn, nó có tất cả các ô và phạm vi chính xác. Ổn thỏa. Tôi sẽ ném trở lại. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Cái gì? Thật điên rồ. Mike. Chỉ vào Mike. Ôi trời ơi. Đặt một phạm vi giá trị trong SUMIFS và sau đó gửi nó vào SUMPRODUCTS và làm cho nó coi nó như một ARRAY. Này, thật là hoang đường. Chúng ta chỉ nên dừng lại ở đó. Chỉ vào Mike.
Ổn thỏa. Hãy quay lại phương pháp của tôi nhưng giả vờ rằng bạn không có Excel 2013. Bạn đã quay lại Excel 2010 hoặc tệ hơn là Excel cho Mac. Ý tôi là, nó nói đó là Excel. Tôi không biết. Nó chỉ khiến tôi phát điên vì những gì Mac có thể hoặc không thể làm. Vì vậy, chúng tôi sẽ lấy BẢNG HIERARCHY của tôi ở đây, và vì VLOOKUP không thể nhìn sang bên trái, tôi sẽ lấy thông tin SALES REP, CONTROL + X và dán. Vâng, tôi biết tôi có thể lập chỉ mục và đối sánh. Tôi không có tâm trạng để làm chỉ mục và khớp ngày hôm nay. Được rồi, nó thực sự đơn giản. Ở đây, = VLOOKUP, lấy tên SALESREP đó, và chúng ta sẽ F4, 2, EXACTMATCHFALSE như vậy, nhấp đúp để sao chép tên đó xuống. (= 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.
Ổn thỏa. Chà, này. Tôi muốn cảm ơn bạn đã ghé qua Dueling Excel Podcast rất dài này. Chúng tôi sẽ gặp lại bạn vào lần tới với một tập khác từ và ExcelIsFun.
Tải tập tin
Tải xuống tệp mẫu tại đây: Duel188.xlsm
