Нямате Power Pivot? Няма значение. По-голямата част от Power Pivot е вградена в Excel 2013 и дори повече в Excel 2016. Днес нашият съвет от Ash се присъединява към таблици в обобщена таблица.
Всяка сряда в продължение на седем седмици представям един от любимите съвети от Ash Sharma. Ash е продуктов мениджър в екипа на Excel. Неговият екип ви носи пивот таблици и много други добри неща. Днес любимата функция на Аш е присъединяването към множество набори от данни, използвайки Relationships и модела на данни.
Кажете, че вашият ИТ отдел ви дава набора от данни, показан в колони A: D. Има полета за клиент и пазар. Трябва да комбинирате определени пазари в региони. Всеки клиент принадлежи към сектор. Регионът и секторът не са в оригиналните данни, но имате справочни таблици, за да предоставите тази информация.
Обикновено бихте изравнили данните, като използвате VLOOKUP, за да изтеглите данни от оранжевата и жълтата таблици в синята таблица. Но тъй като ключовото поле не е в лявата страна на всяка таблица, ще трябва или да превключите към INDEX и MATCH, или да пренаредите справочните таблици.
От Excel 2013 можете да оставите справочните таблици там, където са и да ги комбинирате в самия отчет на обобщената таблица.
За да работи тази техника, трите таблици трябва да бъдат форматирани като таблица. Изберете една клетка във всеки набор от данни и изберете Начало, Форматиране като таблица или натиснете Ctrl + T. Трите таблици първоначално ще се наричат Таблица1, Таблица2 и Таблица3. Използвам раздела Дизайн на инструменти за таблици на лентата и преименувам всяка таблица. Сменям и цвета на всяка таблица. В този пример синята таблица се нарича Data. Оранжевата таблица е RegionTable. Жълтата таблица е SectorTable.
Забележка
Някои ще ви кажат, че трябва да използвате отвратителни имена като Fact, TblSector и TblRegion. Ако някой ви притеснява по този начин, просто откраднете джобния му протектор и го уведомете, че предпочитате имена, звучащи на английски.
За да преименувате таблица, въведете ново име в полето отляво на раздела Дизайн на инструменти за таблици. Имената на таблиците не трябва да имат интервали.
След като трите таблици бъдат дефинирани, преминете към раздела Данни и кликнете върху Връзки.
В диалоговия прозорец Управление на връзките щракнете върху Ново. В диалоговия прозорец Създаване на връзка посочете, че полето Клиент на таблицата с данни е свързано с полето на клиента на SectorTable. Щракнете върху OK.
Дефинирайте друга нова връзка между полето Market в полетата Data и RegionTable. След като дефинирате и двете връзки, ще ги видите в диалоговия прозорец Управление на връзките.
Поздравления: току-що сте изградили модел на данни в работната си книга. Време е да се изгради обобщена таблица.
Изберете празната клетка, където искате да се появи вашата обобщена таблица. По подразбиране диалоговият прозорец Създаване на обобщена таблица ще избере Модел на данни на тази работна книга. Местоположението на обобщената таблица по подразбиране ще бъде избраната от вас клетка. Щракнете върху OK.
Списъкът с полета на обобщената таблица ще изброи и трите таблици. Използвайте триъгълника отляво на таблица, за да разширите името на таблицата, за да ви покаже полетата.
Разгънете таблицата с данни. Изберете полето Приходи. Той автоматично ще се премести в областта Стойности. Разгънете секторната таблица. Изберете полето Сектор. Той ще се премести в областта на редовете. Разгънете таблицата Region. Плъзнете полето Регион до областта Колони. Сега ще имате обобщена таблица, обобщаваща данни от трите таблици.
Забележка
Във всяка книга, която съм писал преди днес, използвам различна техника за изграждане на този отчет. След като дефинирам трите таблици, избирам клетка A1 и Insert, Pivot Table. Поставям отметка в квадратчето за Добавяне на тези данни към модела на данни. В списъка с полета на обобщената таблица изберете Всички от горната част на списъка. Изберете полета за отчета и след това определете връзките след факта. Описаната по-горе техника изглежда по-гладка и всъщност включва малко планиране напред. Хората, които използват Option Explicit в своя VBA код, определено биха харесали този метод.
Взаимоотношенията в модела на данни карат Excel да се чувства по-скоро като Access или SQL Server, но с цялата доброта на Excel.
Обичам да питам екипа на Excel за любимите им функции. Всяка сряда ще споделя по един от отговорите им. Благодаря на Аш Шарма, че предостави тази идея.
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Не търсете, ако имате връзка“
Джон Михалудис
