У вас есть отчет о продажах для 16 торговых представителей. Каждый торговый представитель принадлежит к команде. Как вы можете создать отчет, показывающий общий объем продаж для каждой команды?
Смотреть видео
- Составьте отчет о продажах по регионам и командам
- Исходные данные с указанием торгового представителя и региона
- Второй (неправильной формы) стол распределяет торговых представителей по командам.
- Метод Билла 1. Измените структуру данных иерархии команды. Сделайте оба диапазона в таблицах Ctrl + T
- Создайте сводную таблицу, добавив данные в модель данных. Вытащите команду из второго стола.
- Создать отношения
- Майк Метод 2: создайте СУММЕСЛИМН, где поле Criteria2 является массивом!
- Передайте СУММЕСЛИМН в функцию СУММПРОИЗВ
- Билл. Метод 3. Измените иерархию таблицы так, чтобы торговый представитель был слева.
- Добавить ВПР к исходным данным
- Создайте сводную таблицу
- Майк Метод 4. Используйте значок "Связь" на вкладке "Данные" на ленте.
- При создании сводной таблицы выберите Использовать модель данных этой книги.
- Билл Метод 5: Power Query. Добавить таблицу поиска только как соединение
- Добавить исходную таблицу только для поиска
- Объедините эти две таблицы, сгруппируйте их, чтобы получить окончательный отчет.
Стенограмма видео
Подкаст Dueling Excel, серия 188: Отчет группы продаж по регионам.
Билл: Привет. С возвращением. Пришло время для еще одного подкаста Dueling Excel. Я Билл Джелен из. Ко мне присоединится Майк Гирвин из ExcelIsFun. Это наш выпуск 188, Отчет группы продаж по регионам.
Хорошо, вот вопрос, который у нас есть, набор данных здесь с различными торговыми представителями, сколько их продаж было по регионам, и у некоторых людей есть продажи в обоих регионах, а затем компания организовала этих 16 торговых представителей в эти четыре продажи. команд, и мы пытаемся выяснить для каждой группы продаж, какой у них доход.
Хорошо. Итак, мой подход к этому, вы знаете, мне здесь не нравится этот формат. Я собираюсь преобразовать этот формат в какую-то таблицу, небольшую иерархию здесь, которая показывает для каждой команды, кто есть торговые представители, а затем, если это предусмотрено, мы в Excel 2013 или Excel 2016 с использованием Windows, а не Mac , то мы можем использовать модель данных, и для этого мы должны взять каждую из этих таблиц и FORMAT AS TABLE, что является CONTROL + T. Итак, есть первая таблица, которую они называют Таблицей 8, и вторая таблица, которую они назовут Таблицей 9. Я собираюсь переименовать их. Я возьму первый и назову его ТАБЛИЦА ПРОДАЖ, а второй возьму и назову его ИЕРАРХИЯ КОМАНД, вот так. Хорошо.
Теперь посмотри на это. Начиная с Excel 2013, на вкладке ВСТАВИТЬ мы создаем Сводную таблицу из первого набора данных, но мы говорим ДОБАВИТЬ ЭТИ ДАННЫЕ В МОДЕЛЬ ДАННЫХ, что является самым утомительным способом сообщить вам, что у вас действительно есть движок Power Pivot, стоящий за Excel. 2013. Даже если вы не платите за Power Pivot, даже если у вас есть только базовый уровень Excel Office 365 или Excel, он у вас есть. Хорошо, вот наш новый отчет, и что я собираюсь сделать, так это то, что я определенно хочу отчитаться по РЕГИОНАм, так что есть РЕГИОНЫ, и я хочу увидеть общие ПРОДАЖИ, но я хочу посмотреть на это по отделам продаж. Проверь это. Я собираюсь выбрать ВСЕ, и это дает мне другие таблицы в этой группе, включая ИЕРАРХИЮ КОМАНДЫ. Я возьму КОМАНДУ и перенесу ее по КОЛОННАМ.
Итак, первое, что здесь произойдет, это то, что мы получим неправильные ответы. Получать неправильные ответы очень и очень нормально. Итак, что мы собираемся сделать, это щелкнуть СОЗДАТЬ. Если вам 16, вы можете АВТО-ОБНАРУЖЕНИЕ. Давайте представим, что они находятся в Excel 2013, и мы перейдем в нашу ТАБЛИЦУ ПРОДАЖ. Там есть поле SALES REP, связанное с ИЕРАРХИЕЙ, поле SALES REP, нажмите OK, и у нас есть правильные ответы. Майк, давай посмотрим, что у тебя есть.
Майк: Спасибо. Да, модель данных - это отличный способ построить одну сводную таблицу с двумя разными таблицами, и это действительно мой предпочтительный метод, но если вам нужно было сделать это с помощью формулы, и вам нужно было, чтобы КОМАНДА ПРОДАЖ была вверху каждого столбца Таким образом, это означает, что с помощью формулы мы буквально должны просмотреть этот набор данных, и для каждой записи я должен спросить, является ли ОТВЕТ ПРОДАЖ = Гиги, Чину, Сэнди или Шейле, а затем, если это чистая продажа, я должен сказать, и это регион Северной Америки.
Что ж, мы можем это сделать. Мы можем выполнить логический тест И и логический тест ИЛИ в функции СУММЕСЛИМН. SUM_RANGE, это все числа, поэтому я собираюсь щелкнуть в верхней ячейке, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, я собираюсь выделить весь столбец SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Теперь обычно мы помещаем в критерии один элемент, например JUNE SALES REP. Это говорит СУММЕСЛИМН выплюнуть один ответ для ИЮНЯ, но, если я выделю 4 разные ячейки - по одной для каждого торгового представителя, - мы даем СУММЕСЛИМН запрос на выполнение СУММЕСЛИ для каждого отдельного торгового представителя.
Теперь, когда я копирую эту формулу, мне нужно, чтобы она была заблокирована, но я копирую ее в сторону, ее нужно переместить. Итак, я должен нажать клавишу F4 1, 2 раза, заблокировать строку, но не столбец. Сейчас собираюсь). Это операция с массивом аргументов функции. Это аргумент функции. Тот факт, что у нас есть несколько элементов, означает, что это операция с массивом. Итак, когда я нажимаю в конце и нажимаю F9, СУММЕСЛИМН нам подчиняется. Он показал общую сумму за Джун, Сиу, Поппи и Тайрон. (= СУММЕСЛИМН ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Теперь нам нужно еще больше ограничить эти суммы, добавив условие И. Нам действительно нужно, чтобы это был июнь и Северная Америка, или сиу и Северная Америка, или Поппи, и Северная Америка, и так далее. КОНТРОЛЬ + Z. Мы просто расширяем CRITERIA RANGE 2. Теперь нам нужно просмотреть столбец REGION. CONTROL + SHIFT + DOWNARROW + F4, и я собираюсь щелкнуть по единственному условию, F4 1, 2, 3 раза, чтобы заблокировать столбец, но не строку. Если я нажму в конце и F9, это будут итоговые суммы для каждого нашего торгового представителя в Северной Америке. Когда мы его копируем, СУММЕСЛИМН возвращает итоговую сумму для каждого торгового представителя в Южной Америке. (= СУММЕСЛИМН ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Обратите внимание, что это просто СУММЕСЛИМН, доставляющая несколько чисел, которые нам нужно добавить. КОНТРОЛЬ + Z. Итак, я мог бы поместить его в эту функцию SUM, но аргумент функции SUM NUMBER 1 не будет правильно вычислять эту операцию с массивом без использования CONTROL + SHIFT + ENTER. Итак, я собираюсь схитрить и использовать SUMPRODUCT. Теперь, как правило, СУММПРОИЗВ берет несколько массивов и умножает их - это часть ПРОДУКТА - а затем добавляет их, но я просто собираюсь использовать МАССИВ1 и просто использовать часть СУММ в СУММПРОИЗВ,), CONTROL + ВВОД, скопируйте ее. вниз и в сторону, и поскольку у меня много сумасшедших ссылок на ячейки, я собираюсь перейти к последней в F2, и, конечно же, все ячейки и диапазоны указаны правильно. Хорошо. Собираюсь кинуть обратно. (= СУММПРОИЗВ (СУММЕСЛИ ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Билл: Что? Это безумие. Майк. Укажите на Майка. Боже мой. Помещение диапазона значений в СУММЕСЛИМН, а затем отправка его в СУММПРОДУКТЫ и обработка его как МАССИВ. Эй, это дико. Мы должны просто остановиться здесь. Укажите на Майка.
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.
Хорошо. Ну привет. Я хочу поблагодарить вас за то, что вы заглянули на этот очень длинный подкаст Dueling Excel. Увидимся в следующий раз для другого выпуска от ExcelIsFun.
Скачать файл
Загрузите образец файла здесь: Duel188.xlsm
