16 satış temsilcisi için satışları gösteren bir raporunuz var. Her satış temsilcisi bir ekibe aittir. Her takım için toplam satışları gösteren bir raporu nasıl oluşturabilirsiniz?
Video izle
- Bölgeye ve Ekibe Göre Satış Raporu Oluşturun
- Orijinal verilerin satış temsilcisi ve bölgesi var
- İkinci bir (kötü şekilli) masa, satış temsilcilerini ekipler halinde organize eder
- Fatura yöntemi 1: Ekip hiyerarşisi verilerini yeniden şekillendirin. Her iki aralığı da Ctrl + T tablolarına dönüştür
- Verileri veri modeline ekleyerek bir pivot tablo oluşturun. Takımı ikinci masadan çekin.
- Bir ilişki oluşturun
- Mike Method2: Criteria2 alanının bir dizi olduğu bir SUMIFS oluşturun!
- SUMIFS'i SUMPRODUCT işlevine geçirin
- Fatura Yöntemi 3: Hiyerarşi tablosunu, satış temsilcisi solda olacak şekilde yeniden düzenleyin.
- Orijinal verilere bir DÜŞEYARA ekleyin
- Bir pivot tablo oluşturun
- Mike Yöntem 4: Şeridin Veri sekmesindeki İlişki simgesini kullanın
- Pivot tabloyu oluşturduğunuzda, Bu Çalışma Kitabının Veri Modelini Kullan'ı seçin.
- Fatura Yöntemi 5: Power Query. Arama tablosunu Yalnızca Bağlantı olarak ekleyin
- Orijinal tabloyu yalnızca arama olarak ekleyin
- Bu iki tabloyu birleştirin, nihai raporu oluşturmak için gruplayın
Video Metni
Düello ExcelPodcast, Bölüm 188: Bölgelere Göre Satış Ekibi Raporu.
Bill: Merhaba. Tekrar hoşgeldiniz. Başka bir Düello Excel Podcast zamanı. Ben Bill Jelen'liyim. ExcelIsFun'dan Mike Girvin'e katılacağım. Bu, bölüm 188, Bölgeye Göre Satış Ekibi Raporu.
Pekala, işte karşımızdaki soru, burada çeşitli satış temsilcilerinin bulunduğu bir veri kümesi, bölgelere göre satışlarının ne kadar olduğu ve bazı kişilerin her iki bölgede de satışları var ve sonra şirket bu 16 satış temsilcisini bu dört satışta organize etti ekipler ve her bir satış ekibi için ne kadar gelir elde ettiklerini anlamaya çalışıyoruz.
Peki. Yani, benim buna yaklaşımım, biliyorsunuz, buradaki bu formatı sevmiyorum. Bu biçimi bir tür tabloya, burada küçük bir hiyerarşi olarak yeniden düzenleyeceğim, bu, satış temsilcilerinin kim olduğunu ve daha sonra, Mac değil Windows kullanan Excel 2013 veya Excel 2016'da sağlandıysa veri modelini kullanabiliriz ve bunu yapmak için bu tabloların her birini ve CONTROL + T olan TABLE OLARAK FORMATLA almalıyız. Yani, Tablo 8 adını verdikleri ilk tablo ve Tablo 9 olarak adlandıracakları ikinci tablo var. Bunları yeniden adlandıracağım. Birincisini alacağım ve ona SATIŞ TABLOSU adını vereceğim ve ikincisini alacağım ve ona TAKIM HIERARCHY adını vereceğim. Peki.
Şimdi, şuna bir bakın. Excel 2013'ten başlayarak, INSERT sekmesinde, ilk veri kümesinden bir PIVOT TABLE oluşturuyoruz, ancak bu VERİ MODELİNE BU VERİLERİ EKLE diyoruz ki bu, aslında Excel'in arkasında Power Pivot motorunuz olduğunu bilmenizi sağlamanın en sıkıcı yoludur. 2013. Power Pivot için ödeme yapmasanız bile, yalnızca temel düzey Excel Office 365 veya Excel'e sahip olsanız bile, buna sahipsiniz. Pekala, işte yeni raporumuz ve yapacağım şey kesinlikle BÖLGELERE göre rapor vermek istiyorum, bu yüzden BÖLGELER var ve toplam SATIŞI görmek istiyorum ama buna satış ekibiyle bakmak istiyorum. Şuna bak. TÜMÜNÜ seçeceğim ve bu bana TEAM HIERARCHY dahil olmak üzere bu gruptaki diğer tabloları veriyor. TAKIMI alıp SÜTUNLAR arasında hareket ettireceğim.
Şimdi, burada olacak ilk şey yanlış cevapları almamız olacak. Yanlış cevaplar almak çok, çok normal. Yani, yapacağımız şey OLUŞTUR'a tıklayacağız. 16 yaşındaysanız, OTOMATİK ALGILAMA yapabilirsiniz. SATIŞ TABLOSUMUZA gittiğimiz Excel 2013'teymiş gibi düşünelim. Orada SATIŞ TEMSİLCİSİ adında bir alan var ve bu HİERARŞİ ile ilgili, SATIŞ TEMSİLCİSİ adlı alan, Tamam'ı tıklayın ve doğru cevapları aldık. Mike, bakalım neyin var.
Mike: Teşekkürler. Evet, veri modeli, bir pivot tablo oluşturmak için iki farklı tabloyla gitmek için harika bir yoldur ve bu gerçekten benim tercih ettiğim yöntemdir, ancak bunu bir formülle yapmanız gerekiyorsa ve her sütunun en üstünde SATIŞ EKİBİNİN olması gerekiyorsa bunun gibi, bu formülle, kelimenin tam anlamıyla bu veri setine bakmamız gerektiği anlamına gelir ve her kayıt için SATIŞ REP = Gigi veya Chin veya Sandy veya Sheila'ya ve sonra eğer bir net satış, söylemeliyim ve Kuzey Amerika bölgesi.
Bunu yapabiliriz. SUMIFS işlevinde AND mantıksal bir test ve OR mantıksal bir test yapabiliriz. SUM_RANGE, bunların hepsi sayılar, bu yüzden en üstteki hücreyi tıklayacağım, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, tüm SATIŞ SUNUMU sütununu, CONTROL + SHIFT + DOWNARROW + F4, vurgulayacağım. Şimdi normalde HAZİRAN SATIŞ RAPORU gibi tek bir kalemi kriterlere koyuyoruz. Bu, SUMIFS'e HAZİRAN için bir yanıt vermesini söyler, ancak 4 farklı hücreyi (her satış temsilcisi için 1) vurgularsam, SUMSIFS'e her bir satış temsilcisi için bir SUMIF yapmasını söyleriz.
Şimdi, bu formülü aşağıya kopyaladığımda, onun kilitlenmesine ihtiyacım var ama onu yana kopyalıyorum, hareket etmesi gerekiyor. Yani, F4 tuşuna 1, 2 kez basmalıyım, satırı kilitlemeliyim ama sütunu değil. Şimdi gidiyorum). Bu bir işlev bağımsız değişken dizisi işlemidir. Fonksiyon argümanı budur. Birden çok öğeye sahip olmamız, bunun bir dizi işlemi olduğu anlamına gelir. Yani, sonuna tıklayıp F9'a bastığımda, SUMIFS bize itaat etti. Haziran, Sioux, Poppi ve Tyrone için toplam miktarı tükürdü. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Şimdi, bir AND koşulu ekleyerek bu miktarları daha da sınırlamamız gerekiyor. Haziran ve Kuzey Amerika, Sioux ve Kuzey Amerika veya Poppi ve Kuzey Amerika vb. Olmasına gerçekten ihtiyacımız var. CONTROL + Z. KRITER ARALIĞI 2'yi basitçe genişletiyoruz. Şimdi BÖLGE sütununa bakmamız gerekiyor. CONTROL + SHIFT + DOWNARROW + F4, ve tek koşulu, F4 1, 2, 3 kez tıklayıp sütunu kilitleyip satırı kilitlemeyeceğim. Sonunu ve F9'u tıklarsam, bunlar Kuzey Amerika'daki satış temsilcilerimizin her birinin toplamlarıdır. Kopyaladığımızda, SUMIFS, Güney Amerika için her satış temsilcisi için toplamı sunacaktır. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Yalnızca SUMIFS'in eklememiz gereken birden çok sayı sağladığına dikkat edin. CONTROL + Z. Bu nedenle, onu bu TOPLA işlevine koyabilirim, ancak TOPLA işlevi NUMARA 1 bağımsız değişkeni, KONTROL + ÜST KRKT + ENTER kullanmadan bu dizi işlemini doğru hesaplamaz. Yani, SUMPRODUCT'ü hile yapacağım ve kullanacağım. Şimdi, normal olarak, SUMPRODUCT birden fazla dizi alır ve onları çoğaltır - bu ÜRÜN kısmıdır - ve sonra onları ekler, ancak sadece ARRAY1'i kullanacağım ve SUMPRODUCT'ün SUM kısmını kullanacağım,), CONTROL + ENTER, kopyalayın Aşağı ve yan yana ve çok sayıda çılgın hücre referansım olduğu için, F2'deki sonuncusuna geleceğim ve elbette, tüm hücreleri ve aralıkları doğru. Peki. Geri atacağım. (= SUMPRODUCT (TOPLA ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: Ne? Çılgınca. Mike. Mike'ı göster. Aman Tanrım. SUMIFS'e bir dizi değer koymak ve sonra bunu SUMPRODUCTS'e göndermek ve bir DİZİ gibi davranmasını sağlamak. Hey, bu vahşi. Orada durmalıyız. Mike'ı göster.
Peki. Yöntemime geri dönelim ama Excel 2013'e sahip olmadığınızı varsayalım. Excel 2010'a veya daha kötüsü, Mac için Excel'e geri döndünüz. Demek istediğim, Excel olduğunu söylüyor. Bilmiyorum. Mac'in yapıp yapamayacağı şeyler beni çıldırtıyor. Öyleyse, HIERARCHY TABLE'ımı buraya götüreceğiz ve DÜŞEYARA sola bakamadığından, SATIŞ REP bilgilerini, CONTROL + X'i alıp yapıştıracağım. Evet, indeks yapıp eşleştirebileceğimi biliyorum. Bugün endeksleme ve eşleştirme havasında değilim. Pekala, bu gerçekten çok basit. Burada = DÜŞEYARA, bu SATIŞ RAPORU adını oraya götürün ve F4, 2, EXACTMATCHFALSE, bunu kopyalamak için çift tıklayın. (= DÜŞEYARA (A4, $ F $ 4: $ G $ 19,2, YANLIŞ))
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.
Peki. Peki, merhaba. Bu çok uzun Düello Excel Podcast'i için uğradığınız için teşekkür etmek istiyorum. Bir dahaki sefere ExcelIsFun'dan başka bir bölüm için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Duel188.xlsm
