Превключване на изчисления в обобщена таблица със слайсери

Слайсерите в обобщените таблици могат да се използват не само по класическия начин – за филтриране на изходните данни, но и за превключване между различни типове изчисления в областта на стойността:

Прилагането на това е много лесно – всичко, от което се нуждаете, е няколко формули и помощна таблица. Е, ние ще направим всичко това не в обичайното резюме, а в резюмето, изградено според Power Pivot Data Model.

Стъпка 1. Свързване на добавката Power Pivot

Ако разделите на добавката Power Pivot не се виждат във вашия Excel, първо ще трябва да я активирате. Има два варианта за това:

• Етикет предприемач - бутон COM добавки (Разработчик — COM добавки)
• Файл – Опции – Добавки – COM добавки – Отиди (Файл — Опции — Добавки — COM-добавки — Отиди на)

Ако това не помогне, опитайте да рестартирате Microsoft Excel.

Стъпка 2: Заредете данни в модела на данни на Power Pivot

Ще имаме две таблици като първоначални данни:

Първата е таблица с продажби, според която по-късно ще изградим резюме. Втората е помощна таблица, в която се въвеждат имената на бутоните на бъдещия срез.

И двете таблици трябва да бъдат преобразувани в „интелигентни“ (динамични) с клавишна комбинация Ctrl+T или екип Начало – Форматиране като таблица (Начало — Форматиране като таблица) и е желателно да им дадете разумни имена в раздела конструктор (Дизайн). Нека бъде напр. Продажби и Услуги.

След това всяка таблица на свой ред трябва да бъде заредена в Data Model – за това използваме раздела powerpivot бутон Добавяне към модела на данни (Добавяне към модела на данни).

Стъпка 3. Създайте мярка, за да определите бутона, натиснат върху среза

Изчисляваните полета в обобщена таблица по модел на данни се извикват мерки. Нека създадем мярка, която ще показва името на натиснатия бутон на бъдещия срез. За да направите това, в някоя от нашите таблици изберете която и да е празна клетка в долния панел за изчисления и въведете следната конструкция в лентата с формули:

Тук името на мярката е на първо място (Натиснат бутон), а след това след двоеточие и знак за равенство, формула за изчисляване с помощта на функцията СТОЙНОСТИ DAX, вграден в Power Pivot.

Ако повторите това не в Power Pivot, а в Power BI, тогава двоеточието не е необходимо и вместо това СТОЙНОСТИ можете да използвате нейния по-модерен аналог – функцията ИЗБРАНА СТОЙНОСТ.

Не обръщаме внимание на грешките в долната част на прозореца, които се появяват след въвеждане на формулата – те възникват, защото все още нямаме резюме и срез, в който нещо се кликва.

Стъпка 4. Създайте мярка за изчислението на натиснатия бутон

Следващата стъпка е да създадете мярка за различни опции за изчисление в зависимост от стойността на предишната мярка Натиснат бутон. Тук формулата е малко по-сложна:

Нека го разделим част по част:

1. функция SWITCH – аналог на вложен IF – проверява изпълнението на зададените условия и връща различни стойности в зависимост от изпълнението на някои от тях.
2. функция ВЯРНО() – дава логическо „истина“, така че условията, проверени по-късно от функцията SWITCH, да работят само ако са изпълнени, т.е. истина.
3. След това проверяваме стойността на мярката за натиснат бутон и изчисляваме крайния резултат за три различни опции – като сбор от разходите, средния чек и броя на уникалните потребители. За да преброите уникалните стойности, използвайте функцията DISTINCTCOUNT, а за закръгляване – КРЪГЛА.
4. Ако нито едно от горните три условия не е изпълнено, тогава се показва последният аргумент на функцията SWITCH – задаваме го като фиктивен с помощта на функцията ПРАЗНО().

Стъпка 5. Изграждане на резюме и добавяне на срез

Остава да се върнем от Power Pivot към Excel и там да изградим обобщена таблица за всички наши данни и мерки. За да направите това, в прозореца на Power Pivot на Основната команда за избор на раздел обобщена таблица (Начало — осева таблица).

Тогава:

1. Ние хвърляме полето Продукт от масата Продажби към района Редове (редове).
2. Хвърляне на поле там Резултат от масата Услуги.
3. Щракнете с десния бутон върху полето Резултати изберете екип Добавете като парче (Добавяне като слайсер).
4. Хвърляне на втори такт Заключение от масата Услуги към района Ценности (Стойности).

Тук всъщност са всички трикове. Сега можете да щракнете върху бутоните за разделяне – и сумите в обобщената таблица ще се превключат към функцията, от която се нуждаете.

Красота 🙂

• Предимства на Pivot by Data Model
• План-факт анализ в обобщена таблица на Power Pivot
• Създайте база данни в Excel с помощта на добавката Power Pivot