Masz raport pokazujący sprzedaż dla 16 przedstawicieli handlowych. Każdy przedstawiciel handlowy należy do zespołu. Jak możesz stworzyć raport pokazujący całkowitą sprzedaż dla każdego zespołu?
Obejrzyj wideo
- Utwórz raport sprzedaży według regionu i zespołu
- Oryginalne dane mają przedstawiciela handlowego i region
- Drugi (źle ukształtowany) stół organizuje przedstawicieli handlowych w zespoły
- Metoda rozliczeniowa 1: Zmień kształt danych hierarchii zespołu. Zrób oba zakresy w tabelach Ctrl + T.
- Utwórz tabelę przestawną, dodając dane do modelu danych. Wyciągnij drużynę z drugiego stołu.
- Stwórz relację
- Mike Method2: Zbuduj SUMIFS, w którym pole Criteria2 jest tablicą!
- Przekaż SUMA.WARUNKÓW do funkcji SUMA.RODUKTU
- Metoda rachunku 3: Zmień układ tabeli hierarchii, tak aby przedstawiciel handlowy był po lewej stronie.
- Dodaj WYSZUKAJ.PIONOWO do oryginalnych danych
- Utwórz tabelę przestawną
- Metoda Mike'a 4: Użyj ikony Relacja na karcie Dane na wstążce
- Podczas tworzenia tabeli przestawnej wybierz opcję Użyj modelu danych tego skoroszytu
- Metoda rachunku 5: Power Query. Dodaj tabelę przeglądową jako Tylko połączenie
- Dodaj oryginalną tabelę tylko do wyszukiwania
- Połącz te dwie tabele, pogrupuj według, aby sporządzić raport końcowy
Transkrypcja wideo
Pojedynek ExcelPodcast, odcinek 188: raport zespołu sprzedaży według regionu.
Bill: Hej. Witamy spowrotem. Czas na kolejny podcast Dueling Excel. Jestem Bill Jelen z. Dołączy do mnie Mike Girvin z ExcelIsFun. To jest nasz odcinek 188, raport zespołu sprzedaży według regionu.
W porządku, oto pytanie, które mamy, zestaw danych z różnymi przedstawicielami handlowymi, ile ich sprzedaż była według regionu, a niektórzy ludzie mają sprzedaż w obu regionach, a następnie firma zorganizowała tych 16 przedstawicieli handlowych w tych czterech sprzedażach zespoły i próbujemy dowiedzieć się, jakie przychody miały każdy zespół sprzedaży.
W porządku. Więc moje podejście do tego jest takie, że nie podoba mi się ten format. Zamierzam zmienić ten format w jakąś tabelę, tutaj małą hierarchię, która pokazuje dla każdego zespołu, kim są przedstawiciele handlowi, a następnie, jeśli pod warunkiem, że jesteśmy w programie Excel 2013 lub Excel 2016 na systemie Windows, a nie na komputerze Mac , wtedy możemy skorzystać z modelu danych i aby to zrobić, musimy wziąć każdą z tych tabel i FORMAT AS TABLE, czyli CONTROL + T. Jest więc pierwsza tabela, którą nazywają tabelą 8, i druga tabela, którą nazywają tabelą 9. Zamierzam zmienić ich nazwy. Mam zamiar wezwać pierwszą i nazwać ją TABELĄ SPRZEDAŻY, a drugą wezwać HIERARCHIĘ DRUŻYNOWĄ. W porządku.
Teraz spójrz na to. Począwszy od programu Excel 2013, na karcie WSTAW, tworzymy TABELĘ PRZESTAWNĄ z pierwszego zestawu danych, ale mówimy DODAJ TE DANE DO MODELU DANYCH, co jest najnudniejszym sposobem poinformowania Cię, że faktycznie masz silnik Power Pivot znajdujący się za programem Excel 2013. Nawet jeśli nie płacisz za Power Pivot, nawet jeśli masz tylko podstawowy poziom Excel Office 365 lub Excel, masz to. W porządku, oto nasz nowy raport. Zdecydowanie chcę zgłosić według REGIONU, więc są REGIONY i chcę zobaczyć całkowitą SPRZEDAŻ, ale chcę przyjrzeć się temu przez zespół sprzedaży. Spójrz na to. Mam zamiar wybrać WSZYSTKIE i to daje mi inne tabele w tej grupie, w tym HIERARCHIĘ DRUŻYNOWĄ. Wezmę TEAM i przeniosę ją przez KOLUMNY.
Otóż, pierwszą rzeczą, która się tutaj stanie, będzie otrzymanie złych odpowiedzi. Otrzymywanie złych odpowiedzi jest bardzo, bardzo normalne. Więc zamierzamy kliknąć UTWÓRZ. Jeśli jesteś w 16, możesz WYKRYWAĆ AUTOMATYCZNIE. Udawajmy, że są w programie Excel 2013, gdzie przechodzimy do naszej TABELI SPRZEDAŻY. Jest tam pole o nazwie REP SPRZEDAŻY i jest ono powiązane z HIERARCHIĄ, pole o nazwie REP SPRZEDAŻY, kliknij OK i mamy poprawne odpowiedzi. Mike, zobaczmy, co masz.
Mike: Dzięki. Tak, model danych to świetny sposób na zbudowanie jednej tabeli przestawnej z dwoma różnymi tabelami i to jest naprawdę moja preferowana metoda, ale gdybyś musiał to zrobić z formułą i musiałeś mieć ZESPÓŁ SPRZEDAŻY na górze każdej kolumny w ten sposób, to znaczy, że przy formule musimy dosłownie przejrzeć ten zestaw danych i dla każdego rekordu, muszę zapytać, czy REP SPRZEDAŻY = Gigi lub Chin, Sandy lub Sheila, a następnie, jeśli jest to sprzedaż netto, muszę powiedzieć, i jest to region Ameryki Północnej.
Cóż, możemy to zrobić. Możemy wykonać test logiczny AND i test logiczny OR w funkcji SUMA.WARUNKÓW. SUM_RANGE, to wszystkie liczby, więc kliknę w górną komórkę, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, podświetlę całą kolumnę SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Teraz normalnie umieszczamy w kryteriach pojedynczy element, taki jak CZERWIEC ZE SPRZEDAŻY. To mówi SUMIFS, aby wypluł jedną odpowiedź dla CZERWCA, ale jeśli zaznaczę 4 różne komórki - 1 dla każdego przedstawiciela handlowego - polecimy SUMIFS, aby zrobił SUMA.JEŻELI dla każdego indywidualnego przedstawiciela handlowego.
Teraz, kiedy kopiuję tę formułę, potrzebuję jej zablokowania, ale kopiuję ją na bok, musi się przesunąć. Muszę więc 1, 2 razy nacisnąć klawisz F4, zablokować wiersz, ale nie kolumnę. Teraz mam zamiar). To jest operacja na tablicy argumentów funkcji. To jest argument funkcji. Fakt, że mamy wiele elementów, oznacza, że jest to operacja tablicowa. Tak więc, kiedy kliknąłem na koniec i nacisnąłem F9, SUMIFS posłuchał nas. Wypluła całkowitą kwotę za czerwiec, Sioux, Poppi i Tyrone. (= SUMA.WARUNKÓW (4 B $: 45 $ B $, 4 $ A $: 45 $ A $, F $ 4: F $ 7))
Teraz musimy jeszcze bardziej ograniczyć te kwoty, dodając warunek AND. Naprawdę potrzebujemy czerwca i Ameryki Północnej lub Sioux i Ameryki Północnej lub Poppi i Ameryki Północnej i tak dalej. CONTROL + Z. Po prostu przedłużamy, KRYTERIA ZAKRES 2. Teraz musimy przejrzeć kolumnę REGION. CONTROL + SHIFT + STRZAŁKA W DÓŁ + F4, i kliknę pojedynczy warunek, F4 1, 2, 3 razy, aby zablokować kolumnę, ale nie wiersz. Jeśli kliknę na końcu i F9, to są sumy dla każdego z naszych przedstawicieli handlowych w Ameryce Północnej. Kiedy go skopiujemy, SUMIFS dostarczy sumę dla każdego przedstawiciela handlowego w Ameryce Południowej. (= SUMIFS (4 $ B $: 45 $ B $, 4 $ A $: 45 $ A $, F $ 4: F $ 7, 4 $ C $: 45 $ C $, E8 $))
Zauważ, że to po prostu SUMA.WARUNKÓW dostarczających wiele liczb, które musimy dodać. CONTROL + Z. Mogę więc umieścić to w tej funkcji SUMA, ale argument funkcji SUMA NUMER 1 nie obliczy poprawnie tej operacji na tablicy bez użycia CONTROL + SHIFT + ENTER. Więc zamierzam oszukiwać i używać SUMPRODUCT. Teraz normalnie SUMPRODUCT pobiera wiele tablic i mnoży je - to jest część PRODUKT - a następnie dodaje je, ale zamierzam po prostu użyć ARRAY1 i po prostu użyć części SUMA SUMPRODUCT), CONTROL + ENTER, skopiuj to w dół i na bok, a ponieważ mam wiele szalonych odwołań do komórek, przejdę do ostatniego w F2 i, na pewno, wszystkie komórki i zakresy są prawidłowe. W porządku. Wrócę do. (= SUMPRODUCT (SUMIFS (4 $ B $: 45 $ B $, 4 $ A $: 45 $ A $, F $ 4: F $ 7, 4 $ C $: 45 $ C $, E8 $)))
Bill: Co? To szalone. Mikrofon. Wskaż Mike'a. O mój Boże. Umieszczenie zakresu wartości w SUMIFS, a następnie wysłanie go do SUMPRODUCTS i sprawienie, aby traktował go jak tablicę. Hej, to jest dzikie. Powinniśmy się tam zatrzymać. Wskaż Mike'a.
W porządku. Wróćmy do mojej metody, ale udawajmy, że nie masz programu Excel 2013. Jesteś z powrotem w programie Excel 2010 lub, co gorsza, w programie Excel dla komputerów Mac. To znaczy, mówi, że to Excel. Nie wiem Po prostu doprowadza mnie do szału, co Mac może, a czego nie może zrobić. Więc weźmiemy tutaj moją TABELĘ HIERARCHII, a ponieważ WYSZUKAJ.PIONOWO nie może spojrzeć w lewo, wezmę informacje REP SPRZEDAŻY, CONTROL + X i wkleję. Tak, wiem, że mogę indeksować i dopasowywać. Nie mam dziś nastroju na indeksowanie i dopasowywanie. W porządku, więc to naprawdę proste. Tutaj = WYSZUKAJ.PIONOWO, weź tę nazwę SALESREP, a my F4, 2, EXACTMATCHFALSE w ten sposób, kliknij dwukrotnie, aby ją skopiować. (= WYSZUKAJ.PIONOWO (A4; F $ 4 $: $ G 19,2; FAŁSZ))
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.
W porządku. Więc hej. Chcę wam podziękować za zatrzymanie się przy tym bardzo długim podcastie Dueling Excel. Do zobaczenia następnym razem w kolejnym odcinku programu i ExcelIsFun.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Duel188.xlsm
