У вас є звіт, що показує продажі 16 торгових представників. Кожен торговий представник належить команді. Як можна створити звіт, що відображає загальний обсяг продажів для кожної команди?
Переглянути відео
- Складіть звіт про продажі за регіонами та командами
- Вихідні дані мають торговий представник та регіон
- Другий (погано сформований) стіл організовує торгових представників команд
- Метод рахунку 1: Переформуйте дані ієрархії команди. Внесіть обидва діапазони в таблиці Ctrl + T
- Створіть зведену таблицю, додавши дані до моделі даних. Витягніть команду з другого столу.
- Створіть стосунки
- Mike Method2: Створіть SUMIFS, де поле Criteria2 є масивом!
- Передайте SUMIFS у функцію SUMPRODUCT
- Метод рахунку 3: Переставити таблицю ієрархій так, щоб торговий представник був ліворуч.
- Додайте VLOOKUP до вихідних даних
- Побудуйте зведену таблицю
- Mike Method 4: Використовуйте піктограму Relationship на вкладці Data на стрічці
- Створюючи зведену таблицю, виберіть Використовувати модель даних цієї книги
- Метод рахунку 5: Запит на живлення. Додайте таблицю пошуку як лише підключення
- Додайте оригінальну таблицю лише як підстановку
- Об’єднайте ці дві таблиці, згрупуйте за результатами, щоб отримати остаточний звіт
Стенограма відео
Дуель ExcelPodcast, епізод 188: Звіт команди продажів за регіонами.
Білл: Ей. З поверненням. Настав час чергового дуельного підкасту Excel. Я Білл Джелен з. До мене приєднається Майк Гірвін з ExcelIsFun. Це наш епізод 188, Звіт команди продажів за регіонами.
Гаразд, отже, ось питання, яке ми маємо, набір даних з різними торговими представниками, скільки їх продажів склав регіон, і деякі люди мають продажі в обох регіонах, а потім компанія організувала ці 16 торгових представників у ці чотири продажі команд, і ми намагаємося з’ясувати, для кожної команди продажів, який дохід вони мали.
Гаразд Отже, мій підхід до цього такий, знаєте, мені тут не подобається такий формат. Я збираюся переставити цей формат у якусь таблицю, трохи ієрархії тут, яка показує кожній команді, хто є торговими представниками, а потім, якщо передбачено, що ми працюємо в Excel 2013 або Excel 2016, використовуючи Windows, а не Mac , тоді ми можемо використовувати модель даних, і для цього нам потрібно взяти кожну з цих таблиць та ФОРМАТУВАТИ ТАБЛИЦЮ, яка є CONTROL + T. Отже, є перша таблиця, яку вони називають таблицею 8, а друга таблиця, яку вони називатимуть таблицею 9. Я збираюся перейменувати їх. Я збираюся взяти перший, і я буду називати його ТАБЛИЦЯ ПРОДАЖІВ, а я збираюся взяти другий і буду називати його КОМАНДНА ІЄРАРХІЯ, так. Гаразд
А тепер перевірте це. Починаючи з Excel 2013, на вкладці ВСТАВКА ми створюємо ПІВОТУ ТАБЛИЦЮ з першого набору даних, але ми говоримо ДОДАТИ ЦІ ДАНІ ДО МОДЕЛІ ДАНИХ, що є найнуднішим способом повідомити вас, що двигун Power Pivot у вас сидить за Excel 2013. Навіть якщо ви не платите за Power Pivot, навіть якщо у вас є лише базовий рівень Excel Office 365 або Excel, ви це маєте. Гаразд, отже, ось наш новий звіт, і я збираюся зробити це - я точно хочу звітувати по REGION, отже, є REGIONS, і я хочу бачити загальний обсяг ПРОДАЖІВ, але я хочу розглянути це з боку команди продажів. Перевір це. Я збираюся вибрати ВСЕ, і це дає мені інші таблиці в цій групі, включаючи ІЄРАРХІЮ КОМАНД. Я візьму КОМАНДУ і переміщу її через КОЛОНИ.
Тепер перше, що тут має статися, - це отримання неправильних відповідей. Це дуже, дуже нормально, щоб отримати неправильні відповіді. Отже, ми збираємося натиснути СТВОРИТИ. Якщо ви перебуваєте в 16-му році, ви можете АВТОМАТИЧНО ВИЗНАЧИТИ. Давайте прикинемося, що вони в Excel 2013, де ми переходимо до нашої ТАБЛИЦІ ПРОДАЖІВ. Там є поле під назвою SALES REP і воно пов’язане з ІЄРАРХІЄЮ, поле під назвою SALES REP, натисніть OK, і ми отримаємо правильні відповіді. Майк, давайте подивимось, що у вас є.
Майк: Дякую ,. Так, модель даних - це чудовий спосіб поєднати дві різні таблиці для створення однієї зведеної таблиці, і це справді мій улюблений метод, але якщо вам потрібно було зробити це за допомогою формули, і вам потрібно було мати КОМАНДУ ПРОДАЖУ у верхній частині кожного стовпця ось так, це означає, що за допомогою формули ми буквально повинні переглянути цей набір даних, і для кожного запису, я повинен запитати, це РЕПЕРТАЦІЯ ПРОДАЖУ = Джиджи чи Чін чи Сенді чи Шейла, а потім, якщо це Чистий продаж, я повинен сказати, і це регіон Північної Америки.
Ну, ми можемо це зробити. Ми можемо виконати логічний тест І та логічний тест АБО у функції SUMIFS. SUM_RANGE, це всі цифри, тому я збираюся натиснути у верхній комірці, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, я виділю весь стовпець SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Зараз, як правило, ми вкладаємо в критерії один предмет, такий як ЧЕРВЕНЬСЬКИЙ ПРОДАЖ. Це говорить SUMIFS виплюнути одну відповідь за ЧЕРВЕНЬ, але, якщо я виділю 4 різні клітинки - по 1 для кожного торгового представника - ми доручаємо SUMSIFS зробити SUMIF для кожного окремого торгового представника.
Тепер, коли я копіюю цю формулу, мені потрібно, щоб вона була заблокована, але я копіюю її в бік, вона повинна рухатися. Отже, мені потрібно натиснути клавішу F4 1, 2 рази, зафіксувати рядок, але не стовпець. Зараз я збираюся). Це операція з масивом аргументів функції. Це аргумент функції. Той факт, що у нас кілька елементів, означає, що це операція з масивом. Отже, коли я натискаю в кінці і натискаю F9, SUMIFS нас слухалися. Він виплюнув загальну суму за червень, сіу, поппі та тайрон. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Тепер нам потрібно ще більше обмежити ці суми, додавши умову І. Нам справді потрібно, щоб це були червень і Північна Америка, або Сіу, і Північна Америка, або Поппі та Північна Америка тощо. CONTROL + Z. Ми просто розширюємо, КРИТЕРІЇ ДИАПАЗОН 2. Тепер нам потрібно переглянути стовпець РЕГІОН. CONTROL + SHIFT + ВНИЗ + F4, і я збираюся натиснути на одну умову, F4 1, 2, 3 рази, щоб заблокувати стовпець, але не рядок. Якщо натиснути на кінець і клавішу F9, це загальна сума для кожного нашого торгового представника в Північній Америці. Коли ми копіюємо його, SUMIFS доставить загальну суму для кожного торгового представника для Південної Америки. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Зверніть увагу, що це просто SUMIFS, які доставляють кілька номерів, які нам потрібно додати. CONTROL + Z. Отже, я міг би помістити його в цю функцію SUM, але аргумент функції SUM NUMBER 1 не буде правильно обчислювати цю операцію масиву без використання CONTROL + SHIFT + ENTER. Отже, я збираюся обдурити та використовувати SUMPRODUCT. Зараз, як правило, SUMPRODUCT приймає кілька масивів і множить їх - це частина PRODUCT - і потім додає їх, але я просто буду використовувати ARRAY1 і просто використовувати SUM-частину SUMPRODUCT,), CONTROL + ENTER, скопіювати вниз і в сторону, і оскільки у мене є багато божевільних посилань на клітинки, я збираюся дійти до останнього в F2, і, звичайно, у нього всі клітинки та діапазони правильні. Гаразд Я збираюся кинути назад до. (= SUMPRODUCT (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Білл: Що? Це божевільно. Майк. Вкажіть на Майка. О Боже. Помістіть діапазон значень у SUMIFS, а потім надішліть його в SUMPRODUCTS і зробіть так, щоб він обробляв це як ARRAY. Гей, це дико. Нам слід просто зупинитися на цьому. Вкажіть на Майка.
Гаразд Повернемося до мого методу, але зробимо вигляд, що у вас немає Excel 2013. Ви повернулися до Excel 2010 або, що ще гірше, Excel для Mac. Я маю на увазі, там написано, що це Excel. Не знаю. Це просто зводить мене з розуму, що Mac може чи не може робити. Отже, ми візьмемо сюди мій ІЄРАРХІЧНИЙ СТОЛ, і, оскільки VLOOKUP не може дивитись ліворуч, я візьму інформацію про ПРОДАЖ РЕПОРТАЖУ, CONTROL + X і вставлю. Так, я знаю, що вмію робити індекси та збіги. Я не в настрої сьогодні індексувати та збігатись. Гаразд, отже, це дуже просто. Тут, = VLOOKUP, візьміть це ім'я SALESREP там, і ми будемо F4, 2, EXACTMATCHFALSE так, двічі клацніть, щоб скопіювати це вниз. (= ПЕРЕГЛЯД (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.
Гаразд Ну, привіт. Я хочу подякувати вам за те, що ви завітали на цей дуже довгий дуельний підкаст Excel. Ми побачимо вас наступного разу для наступного епізоду від та ExcelIsFun.
Завантажити файл
Завантажте зразок файлу тут: Duel188.xlsm
