Anda memiliki laporan yang menunjukkan penjualan untuk 16 perwakilan penjualan. Setiap perwakilan penjualan termasuk dalam sebuah tim. Bagaimana Anda bisa membuat laporan yang menunjukkan total penjualan untuk setiap tim?
Menonton video
- Buat Laporan Penjualan berdasarkan Wilayah dan Tim
- Data asli memiliki perwakilan penjualan dan wilayah
- Meja kedua (berbentuk buruk) mengatur perwakilan penjualan ke dalam tim
- Metode tagihan 1: Menyusun ulang data hierarki tim. Buat kedua rentang menjadi tabel Ctrl + T.
- Buat tabel pivot, tambahkan data ke model data. Tarik Tim dari meja kedua.
- Ciptakan hubungan
- Mike Metode2: Buat SUMIFS dengan bidang Criteria2 adalah larik!
- Teruskan SUMIFS ke fungsi SUMPRODUCT
- Metode Tagihan 3: Atur ulang tabel hierarki sehingga perwakilan penjualan ada di sebelah kiri.
- Tambahkan VLOOKUP ke data asli
- Buat tabel pivot
- Metode 4 Mike: Gunakan ikon Hubungan pada tab Data di pita
- Saat Anda membuat tabel pivot, pilih Gunakan Model Data Buku Kerja ini
- Metode Tagihan 5: Power Query. Tambahkan tabel pencarian sebagai Koneksi Saja
- Tambahkan tabel asli sebagai pencarian saja
- Gabungkan kedua tabel tersebut, kelompokkan menurut untuk menghasilkan laporan akhir
Transkrip Video
Dueling ExcelPodcast, Episode 188: Laporan Tim Penjualan Berdasarkan Wilayah.
Bill: Hei. Selamat datang kembali. Saatnya untuk Podcast Excel Duel lainnya. Saya Bill Jelen dari. Saya akan bergabung dengan Mike Girvin dari ExcelIsFun. Ini adalah episode 188 kami, Laporan Tim Penjualan Berdasarkan Wilayah.
Baiklah, jadi, inilah pertanyaan yang kami miliki, kumpulan data di sini dengan berbagai perwakilan penjualan, berapa banyak penjualan mereka menurut wilayah, dan beberapa orang memiliki penjualan di kedua wilayah tersebut, lalu perusahaan telah mengatur 16 perwakilan penjualan tersebut ke dalam empat penjualan ini. tim, dan kami mencoba mencari tahu, untuk setiap tim penjualan, berapa banyak pendapatan yang mereka miliki.
Baik. Jadi, pendekatan saya untuk ini adalah, Anda tahu, saya tidak suka format ini di sini. Saya akan mengatur ulang format itu menjadi semacam tabel, sedikit hierarki di sini, yang menunjukkan untuk setiap tim siapa perwakilan penjualannya dan kemudian, jika asalkan kami menggunakan Excel 2013 atau Excel 2016 menggunakan Windows dan bukan Mac , kemudian kita dapat menggunakan model data, dan, untuk melakukan ini, kita harus mengambil masing-masing tabel dan FORMAT SEBAGAI TABEL, yaitu CONTROL + T. Jadi, ada tabel pertama yang mereka sebut Tabel 8 dan tabel kedua yang mereka sebut Tabel 9. Saya akan mengganti namanya. Saya akan mengambil yang pertama dan saya akan menyebutnya TABEL PENJUALAN dan saya akan mengambil yang kedua dan saya akan menyebutnya TEAM HIERARCHY, seperti itu. Baik.
Sekarang, lihat ini. Mulai di Excel 2013, pada tab SISIPKAN, kami membuat TABEL PIVOT dari kumpulan data pertama tetapi kami mengatakan TAMBAHKAN DATA INI KE MODEL DATA yang merupakan cara paling membosankan untuk memberi tahu Anda bahwa Anda benar-benar memiliki mesin Power Pivot di belakang Excel 2013. Bahkan jika Anda tidak membayar untuk Power Pivot, meskipun hanya memiliki tingkat dasar Excel Office 365 atau Excel, Anda memilikinya. Baiklah, ini laporan baru kita dan yang akan saya lakukan adalah saya pasti ingin melaporkan menurut REGION, jadi ada REGIONS, dan saya ingin melihat total PENJUALAN tetapi saya ingin melihat ini dari tim penjualan. Lihat ini. Saya akan memilih SEMUA dan itu memberi saya tabel lain dalam grup ini, termasuk TEAM HIERARCHY. Saya akan mengambil TIM dan memindahkannya melintasi KOLOM.
Sekarang, hal pertama yang akan terjadi di sini adalah kita mendapatkan jawaban yang salah. Sangat, sangat normal untuk mendapatkan jawaban yang salah. Jadi, yang akan kita lakukan adalah mengklik BUAT. Jika Anda berada di tahun '16, Anda dapat MENDETEKSI OTOMATIS. Anggap saja mereka berada di Excel 2013 di mana kita pergi ke TABEL PENJUALAN. Ada bidang di sana yang disebut REP PENJUALAN dan itu terkait dengan HIERARCHY, bidang yang disebut REP PENJUALAN, klik OK, dan kami memiliki jawaban yang benar. Mike, mari kita lihat apa yang kamu punya.
Mike: Terima kasih ,. Ya, model data adalah cara yang luar biasa untuk menggunakan dua tabel berbeda untuk membuat satu tabel pivot dan itu benar-benar metode pilihan saya, tetapi jika Anda harus melakukannya dengan rumus dan Anda perlu memiliki TIM PENJUALAN di bagian atas setiap kolom seperti ini, artinya, dengan rumusnya, kita benar-benar harus melihat kumpulan data ini dan, untuk setiap catatan, saya harus bertanya, apakah REP PENJUALAN = untuk Gigi atau Chin atau Sandy atau Sheila, dan kemudian, jika itu a penjualan bersih, saya harus mengatakan, dan merupakan wilayah Amerika Utara.
Ya, kita bisa melakukannya. Kita dapat melakukan tes logika AND dan tes logika OR dalam fungsi SUMIFS. SUM_RANGE, itu semua angka, jadi saya akan mengklik di sel atas, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, saya akan menyorot seluruh kolom SALESREP, CONTROL + SHIFT + DOWNARROW + F4 ,. Sekarang, biasanya kami memasukkan satu item seperti REP PENJUALAN JUNI ke dalam kriteria. Itu memberitahu SUMIFS untuk mengeluarkan satu jawaban untuk JUNI, tetapi, jika saya menyorot 4 sel berbeda - 1 untuk setiap perwakilan penjualan - kami menginstruksikan SUMSIFS untuk melakukan SUMIF untuk setiap perwakilan penjualan individu.
Sekarang, ketika saya menyalin rumus ini ke bawah, saya perlu mengunci, tapi saya menyalinnya ke samping, itu perlu dipindahkan. Jadi, saya harus menekan tombol F4 1, 2 kali, mengunci baris, tetapi bukan kolomnya. Sekarang saya akan). Ini adalah operasi array argumen fungsi. Itulah argumen fungsi. Fakta bahwa kita memiliki banyak item berarti ini adalah operasi array. Jadi, ketika saya mengklik di akhir dan menekan F9, SUMIFS mematuhi kami. Ini memuntahkan jumlah total untuk June, Sioux, Poppi, dan Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Sekarang, kita perlu membatasi jumlah tersebut lebih lanjut dengan menambahkan kondisi DAN. Kami benar-benar membutuhkannya pada bulan Juni dan Amerika Utara atau Sioux dan Amerika Utara atau Poppi dan Amerika Utara, dan seterusnya. CONTROL + Z. Kita hanya perlu memperpanjang, CRITERIA RANGE 2. Sekarang kita perlu melihat melalui kolom REGION. CONTROL + SHIFT + DOWNARROW + F4, dan saya akan mengklik satu kondisi, F4 1, 2, 3 kali untuk mengunci kolom tetapi bukan barisnya. Jika saya mengklik di akhir dan F9, itu adalah total untuk setiap perwakilan penjualan kami di Amerika Utara. Saat kami menyalinnya, SUMIFS akan mengirimkan total untuk setiap perwakilan penjualan untuk Amerika Selatan. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Perhatikan bahwa SUMIFS hanya memberikan beberapa nomor yang perlu kita tambahkan. CONTROL + Z. Jadi, saya bisa memasukkannya ke dalam fungsi SUM ini tetapi argumen fungsi SUM NOMOR 1 tidak akan menghitung operasi array ini dengan benar tanpa menggunakan CONTROL + SHIFT + ENTER. Jadi, saya akan menipu dan menggunakan SUMPRODUCT. Sekarang, biasanya, SUMPRODUCT mengambil beberapa larik dan menggandakannya - itu bagian PRODUCT - lalu menambahkannya, tapi saya hanya akan menggunakan ARRAY1 dan cukup gunakan bagian SUM dari SUMPRODUCT,), CONTROL + ENTER, salin ke bawah dan ke samping, dan karena saya mendapat banyak referensi sel yang gila, saya akan menuju ke yang terakhir di F2 dan, tentu saja, semua sel dan rentangnya benar. Baik. Saya akan melempar kembali ke. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Apa? Itu gila. Mike. Arahkan ke Mike. Ya ampun. Menempatkan rentang nilai di SUMIFS lalu mengirimkannya ke SUMPRODUCTS dan membuatnya memperlakukannya seperti ARRAY. Hei, itu liar. Kita harus berhenti di situ. Arahkan ke Mike.
Baik. Mari kembali ke metode saya, tetapi anggaplah Anda tidak memiliki Excel 2013. Anda kembali menggunakan Excel 2010 atau, lebih buruk, Excel untuk Mac. Maksud saya, dikatakan itu Excel. Saya tidak tahu. Itu hanya membuat saya gila apa yang bisa atau tidak bisa dilakukan Mac. Jadi, kita akan mengambil TABEL HIERARCHY saya di sini, dan, karena VLOOKUP tidak dapat melihat ke kiri, saya akan mengambil informasi REP PENJUALAN, CONTROL + X, dan menempelkannya. Ya, saya tahu saya bisa melakukan indeks dan mencocokkan. Saya tidak berminat untuk mengindeks dan mencocokkan hari ini. Baiklah, jadi, ini sangat sederhana. Di sini, = VLOOKUP, ambil nama SALESREP itu di sana, dan kita akan F4, 2, EXACTMATCHFALSE seperti itu, klik dua kali untuk menyalinnya. (= 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.
Baik. Baiklah. Saya ingin mengucapkan terima kasih telah mampir untuk Dueling Excel Podcast yang sangat lama ini. Sampai jumpa di lain waktu untuk episode lain dari dan ExcelIsFun.
Unduh berkas
Unduh file sampel di sini: Duel188.xlsm
