Имате отчет, показващ продажби за 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 и то е свързано с HIERARCHY, поле, наречено SALES REP, щракнете върху OK и имаме верните отговори. Майк, да видим какво имаш.
Майк: Благодаря ,. Да, моделът на данните е страхотен начин да се използват две различни таблици за изграждане на една обобщена таблица и това наистина е предпочитаният от мен метод, но ако трябва да го направите с формула и е необходимо да имате SALES TEAM в горната част на всяка колона по този начин, това означава, че с формулата буквално трябва да прегледаме този набор от данни и за всеки запис трябва да попитам, е РАЗПРОДАЖБАТА НА ПРОДАЖБИТЕ = на Джиджи или Чин или Санди или Шийла, а след това, ако е нетна продажба, трябва да кажа, и е регионът Северна Америка.
Е, можем да го направим. Можем да направим логически тест И и логически тест ИЛИ във функцията SUMIFS. SUM_RANGE, това са всички числа, така че ще щракна в горната клетка, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, ще подчертая цялата колона SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Сега обикновено поставяме в критерии единичен артикул като ПРОДАЖБА НА ЮНИ. Това казва на SUMIFS да изплюе един отговор за ЮНИ, но ако маркирам 4 различни клетки - 1 за всеки търговски представител - ние инструктираме SUMSIFS да направят SUMIF за всеки отделен търговски представител.
Сега, когато копирам тази формула надолу, трябва да е заключена, но я копирам отстрани, тя трябва да се премести. И така, трябва да натисна клавиша F4 1, 2 пъти, да заключа реда, но не и колоната. Сега отивам). Това е операция с масив на аргумент на функция. Това е аргументът на функцията. Фактът, че имаме множество елементи, означава, че това е операция с масив. И така, когато щракна в края и натисна F9, SUMIFS ни се подчини. Той изплюва общата сума за юни, Sioux, Poppi и Tyrone. (= СУММИФИ ($ 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 функция НОМЕР 1 няма да изчисли правилно тази операция с масив, без да използвам CONTROL + SHIFT + ENTER. И така, ще изневеря и ще използвам SUMPRODUCT. Сега обикновено SUMPRODUCT взема множество масиви и ги умножава - това е частта PRODUCT - и след това ги добавя, но просто ще използвам ARRAY1 и просто използвам SUM частта на SUMPRODUCT, надолу и встрани и тъй като имам много луди препратки към клетки, ще стигна до последната във 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 не може да гледа наляво, ще взема информацията за ПРОДАЖБИТЕ REP, CONTROL + X и ще поставя. Да, знам, че мога да правя индекс и мач. Не съм в настроение да правя индекс и мач днес. Добре, така че наистина е просто. Тук, = VLOOKUP, вземете това име SALESREP там и ние ще F4, 2, EXACTMATCHFALSE така, щракнете двукратно, за да копирате това надолу. (= VLOOKUP (A4, $ F $ 4: $ G $ 19,2, FALSE))
Сега, когато имаме всички тези данни обратно в една таблица, просто малко INSERT, PIVOT TABLE. Дори и да нямате отметка на този етап от модела на данните, ние можем да изградим нашия отчет с ПРОДАЖБЕН ЕКИП, който преминава през, РЕГИОН намалява и ПРОДАЖБИ така. Можете дори тук да обърнем тези, РЕГИОН и да добавим ПРОДАЖБАТА така, в случай че искате да видите кои са търговските представители и ако по подразбиране, ако не искате това, можем просто срути цялата група. И така, оттук отивам в раздела АНАЛИЗ и свивам. Добре. И така, има наши екипи по продажби по региони и тогава, ако някой иска да каже, добре, кой беше SALES TEAM 2, можем да го отворим индивидуално, нещо подобно. Майк, имаш ли още един?
Майк: Все още трябва да обичам VLOOKUP. Прави толкова много невероятни неща и, да, съгласен съм с вас. Excel за Mac, това дори не е Excel, нали? Добре. Добре. Имам друг метод, но ще трябва да премина към друга работна книга. И така, просто имам същите два набора от данни и ги преобразувах в таблици на Excel и ги наименувах. Има таблица ПРОДАЖБИ, има таблица TEAM и толкова ми харесва опцията ви Power Pivot, че ще я открадна, но направете го по малко по-различен начин, защото, както казвате, ако имате Excel 2013 или по-нова версия, имате Модел за данни на Power Pivot там, но става още по-добър. В раздела на лентата с данни - и аз имам Excel 2016 - ако имате бутона RELATIONSHIPS, можете просто да изградите връзката, сякаш е VLOOKUP между тези две таблици и тя автоматично ще я изпрати към модела на данни.
И така, тук е УПРАВЛЕНИЕТО НА ВРЪЗКИТЕ. Ще щракна НОВО. Отивам да избера таблица ПРОДАЖБИ, ПРОДАЖБА REP. По същество това е нашата справочна стойност, нали, и тогава ще избера справочната таблица dTEAM и ПРОДАЖБИТЕ. Това е таблицата за търсене, за да може да търси ПРОДАЖБА REP и да върне ЕКИПА НА ПРОДАЖБИТЕ, но няма колона VLOOKUP. Това са просто две таблици в нашия списък с полета на обобщена таблица. Да Вижте това, връзките, когато щракна OK, той го изпраща към модела на данни.
Сега ще щракна в клетка отстрани ALT + N + V, за да отворя диалоговия прозорец CREATE PIVOTTABLE и - вижте това - той вече предполага, че искам модела на данните, защото има нещо в модела на данни. Сега щраквам OK и имам двете си маси точно там. Ще щракна върху падащото меню, ЕКИП НА ПРОДАЖБИТЕ НА РЕД, РАЗПРОДАЖБИ НА РЕПУЛАТА надолу под РЯДОВЕ и след това ПРОДАЖБИ от МАСАТА ЗА ПРОДАЖБИ до СТОЙНОСТИ РЕДНИ ЕТИКЕТИ. Това не ми харесва, така че ще се кача на SHOW IN TABULAR, щракване с десния бутон, ФОРМИРАНЕ НА НОМЕР, нещо като ВАЛУТА, щракнете върху OK.
Сега, точно както казахме, можем да сгънем това, ако не искаме да виждаме ПРОДАЖБАТА, и след това плъзнете РЕГИОН надолу до КОЛОНИ и точно така имаме общите данни за всички наши екипи за продажби за всеки регион. Дори бих могъл да отворя това. Независимо дали влизате в модела на данни или чрез квадратчето за отметка в СЪЗДАВАНЕ НА ПИВОТАТИ, или просто ДАННИ, ВРЪЗКИ, това е пътят. Толкова бързо и лесно и можем да изтегляме полета от две различни таблици. Добре, ще го хвърля обратно.
Бил: Уау, Майк, ВЗАИМООТНОШЕНИЯТА тук в раздела ДАННИ, сигурен съм, че никога не съм забелязвал това и предполагам, в моя защита, в по-малката версия на Excel тук, няма и дума . Просто прилича на малка малка икона и осъзнавам, че е нова. Това е супер, супер готино.
Добре. Нека просто направим още едно тук. Ще използвам заявка за захранване. И така, в раздела ДАННИ, ВЗЕМЕТЕ И ПРЕОБРАЗЕТЕ ДАННИ. ОТ ТАБЛИЦА избирам първата таблица и искам да взема това поле РЕГИОН и ще го завъртя, така че ще създам обобщена таблица точно тук в заявка за захранване. Бих бил внимателен тук. Стойностите са в областта ПРОДАЖБИ. Щракнете върху OK. И така, сега, за всеки ПРОДАЖБЕН ПРЕДСТАВИТЕЛ, имаме техните продажби на СЕВЕРНА АМЕРИКА и ЮЖНА АМЕРИКА и ще нарека това ByRep. BYREP. Ще го нарека BYREP, а след това HOME, CLOSE & LOAD, но няма да CLOSE & LOAD в работната книга. Ще кажа САМО СЪЗДАВАНЕ НА ВРЪЗКА, така.
Добре. След това ще стигна до втория и ще кажа, че ще създам заявка ОТ МАСА, добре, и това просто ще остане точно така, както е. Ще наречем това TEAMS и CLOSE & LOAD, CLOSE & LOAD, САМО СЪЗДАВАНЕ НА ВРЪЗКА, така.
Добре. И така, сега имаме два различни отчета тук и ще кажа, че искам да създам КОМБИНИРАНО ЗАПИТВАНЕ, СЛЕДНО ЗАПИТВАНЕ и първата ми заявка ще се нарича BYREP, а след това ще разгледам заявката TEAMS. Сега тази част е тази, която изобщо не е интуитивна. Кликнете върху РАЗПРОДАЖБА тук, кликнете върху РАЗПРОДАЖБА тук и ние искаме ВСИЧКИ ОТ ПЪРВО, СЪОБЩЕНИЕ ОТ ВТОРО. Щракнете върху OK. Добре. И така, тук е цялата наша информация за ПРОДАЖБИ, какво са продали в Северна Америка, какво са продали в Южна Америка и използвайте иконата за разгъване тук и всичко, което искаме да получим, е информацията за ЕКИПА. Просто искам да го нарека ЕКИП. Не искам да го наричам TEAMS.TEAM. Това би било лудост.
Добре. На този етап вече не се нуждаем от информацията за ПРОДАЖБИТЕ. Ще премахна тази колона. Ще взема ЕКИПА и ще го преместя наляво, а след това - проверете това - GROUP BY. Ще ГРУПИРАМЕ ОТ ЕКИПА и НОВОТО ИМЕ НА КОЛОНАТА ще се нарича СЕВЕРНА АМЕРИКА, ОПЕРАЦИЯТА ще бъде СУММА, СЕВЕРНА АМЕРИКА КОЛОНА и след това ще добавим втора, наречена ЮЖНА АМЕРИКА, СУМА ЮЖНА АМЕРИКА КОЛОННА. Ето. ГРУПИРАНЕ ПО ЕКИП, две колони и ние разполагаме с нашата информация тук.
Нека поръчаме това. И така, в раздела НАЧАЛО искаме да СОРТИРАМЕ А до Я. ЕКИП ЗА ПРОДАЖБИ 1, 2, 3, 4. Там е нашата СЕВЕРНА АМЕРИКА. Там е нашата ЮЖНА АМЕРИКА. Сега най-накрая ще ЗАКРИВАМЕ И ТОВАРИМ и имаме своите резултати и - вижте това - това е дори по-готино от това. Така че, ако се върна към BILLPQ и вземем POPPI и преместим POPPI в SALES TEAM 2 и след това се върнем към нашите резултати тук, добре, така че, SALES TEAM 2, трябва да видим, че тези числа се увеличават. Елате тук и щракнете върху иконата за опресняване и тези цифри се промениха, нали? Колко яко? Колко готино е това?
Добре, приключи. Целта днес е да изготвим отчет за продажбите по регион и екип. Оригиналните данни имат търговски представител в региона и след това има справочна таблица - по мое мнение, с лоша форма - която организира търговските представители в екипи. И така, методът ми преработи тези данни в данни за йерархия на екип, направи двата диапазона в таблици Control + T, създаде обобщена таблица, добавяйки данните към модела на данни и след това създаде връзка. Методът на Майк: използвайте SUMIFS, където полето Criteria2 to е масив - не знаех, че можете да го направите - и след това функцията SUMPRODUCT. Третият ми метод: пренаредете йерархичната таблица, така че представителят по продажбите да е вляво и след това направете VLOOKUP, изграждайки обобщена таблица. Методът на Майк: използвайте иконата RELATIONSHIP, за да изградите първо връзка, а след това обобщена таблица от модела на данните на работната книга. И тогава,петата версия - версията без VLOOKUP-без обобщена таблица, в случай че се страхувате и от двете - заявка за захранване. Добавете справочната таблица само като връзка, добавете оригиналната таблица само като справочна, като направите въртене точно там, за да получите Северна Америка и Южна Америка, обединете тези две таблици, групирайте и след това групирайте в рамките на заявката за захранване и можете опресняване.
Добре. Е, хей. Искам да ви благодаря, че се отбихте за този много дълъг Ducast Excel Podcast. Ще се видим следващия път за друг епизод от и ExcelIsFun.
Свали файл
Изтеглете примерния файл тук: Duel188.xlsm
