Сравнение и оценка на разходите за реклама с рекламния бюджет при използване на Power Query
Задача: Рекламният отдел на фирмата Х предлага на своите дистрибутори рекламни материали -
флайери, диплянки, шапки, календари.
Всеки рекламен материал има цена и се разпространява в ограничени количества.
За раздадените рекламни материали, служителите от рекламния отдел съставят документи,
които съдържат вид, тип и стойност за конкретния рекламен материал и също месецът, през който
ще бъде използван.
Рекламният отдел работи с бюджет по месеци и рекламни материали.
Бюджетът се използва за проверка на това:
- Дали раздадените материали не надвишават по стойност определените за месеца;
- На кой дистрибутор какво е раздадено и как това се отразява на резултатите.
Решение: Необходимо е да се съберат и обединят всички данни за раздадени материалии
и да се сравнят с бюджета.
Прилагането на MS Excel Power Query има следните предимства при решаването на тази задача:
- Избягване на грешки при обединяването на данните свързани с дублиране или пропускане на файлове;
- Възможност за актуализиране на крайния резултат, когато са настъпили промени в изходните документи.
Стъпка първа: Всеки документ трябва да се форматира с "Format as Table"
Стъпка втора: Създаване на заявка по първия документ
Power Query -> From File -> From Excel –> Select File -> Edit
Редактираме документ 100 с име ”Distribution 100.xls” като заявка, query.
Стъпка трета: премахване на ненужните колони
Стъпка четвърта: изберете "Close & Load" за създаване на работен лист от данните за документа
Повтаряме тези стъпки, за да добавим всички документи.
Всички заявки трябва да имат "говорящи" имена, за по-добър контрол.
Заявка с име Table110 за документ 110.
След “Close & Load”:
За да се върнем към Power Query можем да използваме "Edit Query", след позициониране в таблицата на работния лист.
Стъпка пета: след извикване на всички документи, можем да създадем една обща заявка за тях
Home -> Append Query as New
От диалога "Append" избираме две таблици (или варианта с три и повече, в зависимост от броя на документите).
Ето как изглежда резултатът от обединяване на всички данни:
Можем да сменим името на месеца с номер - така ще можем да сортираме по месец. Дали искаме това зависи от оформлението на бюджета!
Select Column for Month -> Transform ->Replace Values
Можем да превърнем тези стойности в цял тип, макар че това не е задължително:
For Month as Data Type -> Whole Number
Ако решим, че сме сбъркали, вместо Undo можем да използваме:
Query Settings – Right mouse button – Delete.
Можем да задаваме изчисления със стойностите на някоя колона.
Ще умножим Value с минус 1, защото тези стойности вече са "използвани" по смисъла на бюджета.
Можем да променим името на новата колона.
Можем да изтрием колоната Value без да променим получените вече стойности в Budget value.
Формулите, по които става трансформацията на зявките се виждат в реда на формулите.
Новосъздадената заявка, Query, можем да бъде прехвърлена в работния лист по обичайния начин.
Стъпка шеста: добавяне на данните за бюджета
Форматът на бюджета във вид Crosstab, със заглавия както в редовете, така и в колоните е неподхоящ за обработка като таблица в база данни.
Първо ще променим името на първата колона:
След това избираме първата колона и кликваме на Transform -> Unpivot Other Columns
Ще сменим имената на колоните на For Month и Budget Value.
Стъпка пета: ще добавим бюджета към останалите данни (Append1)
Отиваме на Append -> Append Queries As New
Сега името на новата заявка е AllData.
Използваме Close & Load за да създадем работен лист от обединените данни.
Работната книга съдържа всички заявки, които се появвяат като списък Queries.
Виждат се евентуалните проблеми, но тук всички данни от Table110 са вече добавени в заявката Append1.
Стъпка единадесет: създаване Pivot Table за анализ на всички данни
Ако сравним с бюджета, ще видим че остатъчните стойности са намалени, но са все още положителни.
Отрицателните стойности в резултативната Pivot Table говорят за това, че
сме нарушили бюджета и евентуално трябва да компенсираме с други месеци, ако е възможно.
Ще опитаме да тестваме подобна ситуация, като заедно с това изпробваме и възможностите за актуализиране при промяна на данните.
Съхраняваме файла като BudgetCalculation и го затваряме.
Нека да направим промени във файла за Document 100.
Добавяме материал “Item 9” със стойност 200.
Стъпка девета: актуализиране на заявките
Отваряме файла отново и кликваме на Enable Content.
Добавяме в Document 100 стойност 200 за Item 9.
Кликваме на Reresh, за да обновим данните в работната книга.
Така сега заявката AllData също се оказва актуализирана.
За да актуализираме работната книга за заявката AllData кликваме на Refresh. Ще трябва да сменим July със ”7”.
След това можем да проверим изходните данни на Pivot и да ги актуализираме с Refresh.
Заключение:
Служителите на рекламния отдел просто трябва да попълват файловете, в които отразяват раздаването на материали.
След това този, който наблюдава бюджета, трябва само да обнови файла с данните, без да дописва и нанася каквото и да било друго.
Може да се помисли само за начина, по който се отбелязва месецът, защото простото написване на името може да създаде проблеми.
Съвет: Ако ни интересуват данни за самите дистрибутори, за това какво те са взели като рекламни материали
и как това се е отразило на продажбите, просто трябва да оставим съответните колони в заявките.
Съвет: Можем да създаваме тези заявки като "теглим" данни от базата на фирмата, защото Power Query разполага с
такава възможност.
