Anda mempunyai laporan yang menunjukkan penjualan untuk 16 wakil penjualan. Setiap wakil jualan adalah satu pasukan. Bagaimana anda dapat membuat laporan yang menunjukkan jumlah penjualan untuk setiap pasukan?
Tonton Video
- Bina Laporan Jualan mengikut Wilayah dan Pasukan
- Data asal mempunyai wakil penjualan dan wilayah
- Jadual kedua (berbentuk buruk) mengatur wakil jualan kepada pasukan
- Kaedah bil 1: Membentuk semula data hierarki pasukan. Jadikan kedua julat ke dalam jadual Ctrl + T
- Buat jadual pangsi, menambahkan data ke model data. Tarik Pasukan dari jadual kedua.
- Buat hubungan
- Kaedah Mike2: Bina SUMIFS di mana bidang Kriteria2 adalah array!
- Masukkan SUMIFS ke fungsi SUMPRODUCT
- Kaedah Bil 3: Susun semula jadual hierarki sehingga wakil penjualan berada di sebelah kiri.
- Tambahkan VLOOKUP ke data asal
- Bina jadual pangsi
- Kaedah Mike 4: Gunakan ikon Hubungan pada tab Data pada pita
- Semasa anda membuat jadual pangsi, pilih Gunakan Model Data Buku Kerja ini
- Kaedah Bil 5: Pertanyaan Kuasa. Tambahkan jadual carian sebagai Sambungan Sahaja
- Tambahkan jadual asal sebagai carian sahaja
- Gabungkan kedua-dua jadual tersebut, kumpulkan untuk menghasilkan laporan akhir
Transkrip Video
Dueling ExcelPodcast, Episode 188: Laporan Pasukan Jualan Mengikut Wilayah.
Bill: Hei. Selamat kembali. Sudah tiba masanya untuk Dueling Excel Podcast yang lain. Saya Bill Jelen dari. Saya akan disertai oleh Mike Girvin dari ExcelIsFun. Ini adalah episod 188 kami, Laporan Pasukan Jualan Mengikut Wilayah.
Baiklah, jadi, inilah pertanyaan yang kami miliki, kumpulan data di sini dengan pelbagai wakil penjualan, berapa penjualan mereka mengikut wilayah, dan beberapa orang mempunyai penjualan di kedua-dua wilayah tersebut, dan kemudian syarikat itu telah mengatur 16 wakil penjualan tersebut ke dalam empat penjualan ini pasukan, dan kami berusaha untuk mengetahui, untuk setiap pasukan penjualan, berapa banyak pendapatan yang mereka dapat.
Baiklah. Jadi, pendekatan saya untuk ini adalah, anda tahu, saya tidak menyukai format ini di sini. Saya akan menyusun semula format itu ke dalam beberapa jenis jadual, sedikit hierarki di sini, yang menunjukkan kepada setiap pasukan siapa wakil jualan dan kemudian, jika disediakan kita berada di Excel 2013 atau Excel 2016 menggunakan Windows dan bukan Mac , maka kita dapat memanfaatkan model data, dan, untuk melakukan ini, kita harus mengambil setiap tabel ini dan FORMAT SEBAGAI JADUAL yang CONTROL + T. Jadi, ada jadual pertama yang mereka panggil Jadual 8 dan jadual kedua yang akan mereka panggil Jadual 9. Saya akan menamakan semula ini. Saya akan mengambil yang pertama dan saya akan menyebutnya JUALAN JUALAN dan saya akan mengambil yang kedua dan saya akan menyebutnya TEAM HIERARCHY, seperti itu. Baiklah.
Sekarang, periksa ini. Bermula di Excel 2013, pada tab INSERT, kami membuat PIVOT TABLE dari set data pertama tetapi kami katakan TAMBAHKAN DATA INI KE MODEL DATA yang merupakan kaedah paling membosankan untuk memberitahu anda bahawa anda sebenarnya mempunyai mesin Power Pivot yang berada di belakang Excel 2013. Walaupun anda tidak membayar Power Pivot, walaupun hanya mempunyai Excel Office 365 atau Excel tahap asas, anda mempunyai itu. Baiklah, jadi, inilah laporan baru kami dan apa yang akan saya lakukan ialah saya pasti ingin melaporkan oleh REGION, jadi ada REGIONS, dan saya ingin melihat jumlah JUALAN tetapi saya ingin melihatnya oleh pasukan jualan. Lihat ini. Saya akan memilih SEMUA dan itu memberi saya jadual lain dalam kumpulan ini, termasuk TEAM HIERARCHY. Saya akan mengambil TEAM dan menggerakkannya melintasi COLUMNS
Sekarang, perkara pertama yang akan berlaku di sini adalah kita mendapat jawapan yang salah. Itu sangat biasa bagi mendapatkan jawapan yang salah. Jadi, apa yang akan kita lakukan ialah klik CREATE. Sekiranya anda berusia '16, anda boleh AUTO-DETECT. Mari berpura-pura mereka berada di Excel 2013 di mana kita pergi ke JUALAN JUALAN kita. Terdapat bidang yang disebut SALES REP dan ia berkaitan dengan HIERARCHY, bidang yang disebut SALES REP, klik OK, dan kami mempunyai jawapan yang betul. Mike, mari lihat apa yang anda ada.
Mike: Terima kasih,. Ya, model data adalah cara yang hebat untuk menggunakan dua jadual yang berbeza untuk membina satu jadual pangsi dan itu betul-betul kaedah pilihan saya, tetapi jika anda harus melakukannya dengan formula dan anda perlu mempunyai PASUKAN JUALAN di bahagian atas setiap lajur seperti ini, itu bermaksud, dengan rumus, kita benar-benar harus melihat set data ini dan, untuk setiap catatan, saya harus bertanya, adalah JUALAN REP = ke Gigi atau Chin atau Sandy atau Sheila, dan kemudian, jika itu adalah penjualan bersih, saya harus katakan, dan merupakan wilayah Amerika Utara.
Kita boleh melakukannya. Kita boleh melakukan ujian logik DAN dan ujian logik ATAU dalam fungsi SUMIFS. SUM_RANGE, itu semua nombor, jadi saya akan klik di sel atas, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, saya akan menyoroti keseluruhan lajur SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Sekarang, biasanya kita memasukkan satu item seperti JUNE SALES REP menjadi kriteria. Itu memberitahu SUMIFS untuk mengeluarkan satu jawapan untuk JUNE, tetapi, jika saya mengetengahkan 4 sel yang berbeza - 1 untuk setiap wakil penjualan - kami mengarahkan SUMSIFS untuk melakukan SUMIF untuk setiap wakil jualan individu.
Sekarang, apabila saya menyalin formula ini ke bawah, saya memerlukannya terkunci, tetapi saya menyalinnya ke sisi, ia perlu bergerak. Jadi, saya harus menekan kekunci F4 1, 2 kali, mengunci baris, tetapi bukan lajur. Sekarang saya akan). Ini adalah operasi array argumen fungsi. Itu hujah fungsi. Fakta bahawa kita mempunyai banyak item bermaksud ini adalah operasi array. Oleh itu, apabila saya mengklik pada akhir dan memukul F9, SUMIFS mematuhi kami. Ia mengeluarkan jumlah keseluruhan untuk bulan Jun, Sioux, Poppi, dan Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Sekarang, kita perlu mengehadkan jumlah tersebut dengan menambahkan syarat AND. Kita sangat memerlukannya pada bulan Jun dan Amerika Utara atau Sioux dan Amerika Utara atau Poppi dan Amerika Utara, dan seterusnya. KAWALAN + Z. Kita hanya memperluas, KRITERIA RANGE 2. Sekarang kita perlu melihat melalui ruangan REGION. CONTROL + SHIFT + DOWNARROW + F4, dan saya akan mengklik satu syarat, F4 1, 2, 3 kali untuk mengunci lajur tetapi bukan baris. Sekiranya saya mengklik pada akhir dan F9, itu adalah jumlah untuk setiap wakil jualan kami di Amerika Utara. Apabila kami menyalinnya, SUMIFS akan memberikan jumlah untuk setiap wakil jualan untuk Amerika Selatan. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Perhatikan bahawa hanya SUMIFS yang memberikan banyak nombor yang perlu kita tambah. KAWALAN + Z. Jadi, saya boleh memasukkannya ke dalam fungsi SUM ini tetapi argumen SUM fungsi NOMBOR 1 tidak akan mengira operasi array ini dengan betul tanpa menggunakan CONTROL + SHIFT + ENTER. Jadi, saya akan menipu dan menggunakan SUMPRODUCT. Sekarang, biasanya, SUMPRODUCT mengambil banyak tatasusunan dan menggandakannya - itulah bahagian PRODUK - dan kemudian menambahkannya, tetapi saya hanya akan menggunakan ARRAY1 dan hanya menggunakan bahagian SUM dari SUMPRODUCT,), CONTROL + ENTER, salin ke bawah dan ke sebelah, dan kerana saya mendapat banyak rujukan sel gila, saya akan datang ke yang terakhir di F2 dan, sudah tentu, semua sel dan julatnya betul. Baiklah. Saya akan membuang kembali ke. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bil: Apa? Itu gila. Mike. Tunjuk pada Mike. Ya ampun. Masukkan pelbagai nilai dalam SUMIFS dan kemudian kirimkan ke dalam SUMPRODUK dan jadikannya memperlakukannya seperti SALAH. Hei, itu liar. Kita mesti berhenti di sana. Tunjuk pada 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.
Baiklah. Baiklah, hei. Saya ingin mengucapkan terima kasih kerana mampir untuk Dueling Excel Podcast yang sangat lama ini. Kami akan berjumpa anda di lain masa untuk episod lain dari dan ExcelIsFun.
Muat turun fail
Muat turun fail sampel di sini: Duel188.xlsm
