- Основна ідея Припустимо, що ми з вами сидимо в приймально-екзаменаційної комісії і оцінюємо абітурієнтів,...
- Розрахунок середньозваженого в зведеній таблиці
- Розрахунок середньозваженого в зведеній таблиці за допомогою Power Pivot і мови DAX
Основна ідея
Припустимо, що ми з вами сидимо в приймально-екзаменаційної комісії і оцінюємо абітурієнтів, які хочуть вступити в наш ВНЗ. Оцінки з різних предметів у наших кандидатів наступні:
Вільне місце, припустимо, тільки одне, і наша задача - вибрати гідного.
Перше, що зазвичай приходить в голову - це розрахувати класичний середній бал за допомогою стандартної функції Excel СРЗНАЧ (AVERAGE).
На перший погляд здається, що краще за всіх підходить Іван, тому що у нього середній бал максимальний. Але тут ми вчасно згадуємо, що факультет-то наш називається "Програмування", а у Івана хороші оцінки тільки по малюванню, співу та іншої фізкультури, а з математики та інформатики як раз не дуже. Виникає питання: а як привласнити нашим предметів різну важливість (цінність), щоб враховувати її при розрахунку середнього? І ось тут на допомогу приходить середньозважене значення.
Середньозважена - це середнє з урахуванням різної цінності (ваги, важливості) кожного з елементів.
У бізнесі середньозважене часто використовується в таких завданнях, як:
- оцінка портфеля акцій, коли у кожної з них своя цінність / ризиковість
- оцінка прогресу за проектом, коли у завдань не рівну вагу і важливість
- оцінка персоналу по набору навичок (компетенцій) з різною значимістю для необхідної посади
- і т.д.
Розрахунок середньозваженого формулами
Додамо до нашої таблиці ще один стовпець, де вкажемо деякі безрозмірні бали важливості кожного предмета за шкалою, наприклад, від 0 до 9 при надходженні на наш факультет програмування. Потім розрахуємо середньозважений бал для кожного абітурієнт, тобто середнє з урахуванням ваги кожного предмета. Потрібна нам формула буде виглядати так:
Функція СУММПРОИЗВ (SUMPRODUCT) попарно перемножує один на одного осередку в двох зазначених діапазонах - оцінки абітурієнт і вага кожного предмета - а потім підсумовує всі отримані твори. Потім отримана сума ділиться на суму всіх балів важливості, щоб усереднити результат. Ось і вся премудрість.
Так що беремо Машу, а Іван хай вступає до інституту фізкультури;)
Розрахунок середньозваженого в зведеній таблиці
Піднімемо ставки і ускладнити завдання. Припустимо, що тепер нам потрібно підрахувати середньозважене, але не в звичайній, а в зведеній таблиці. Припустимо, що у нас є ось така таблиця з даними по продажам:
Зверніть увагу, що я перетворив її в "розумну" таблицю за допомогою команди Головна - Форматувати як таблицю (Home - Format as Table) і дав їй на вкладці Конструктор (Design) ім'я Data.
Зауважте, що ціна на один і той же товар може відрізнятися. Наше завдання: розрахувати середньозважені ціни для кожного товару. Слідуючи тій же логіці, що і в попередньому пункті, наприклад, для суниці, яка продавалася 3 рази, це повинно бути:
= (691 * 10 + 632 * 12 + 957 * 26) / (10 + 12 + 26) = 820,33
Тобто ми підсумовуємо вартості всіх угод (ціна кожної угоди множиться на кількість по угоді) і потім ділимо число, на загальну кількість цього товару.
Правда, з реалізацією цієї нехитрої логіки саме в зведеній таблиці нас чекає невеликий облом. Якщо ви працювали зі зведеними раніше, то, напевно, пам'ятаєте, що можна легко переключити поле значень зведеної в потрібну нам функцію, клацнувши по ньому правою кнопкою миші і вибравши команду Підсумки по (Summarize Values By):
У цьому списку є середнє, але немає середньозваженого :(
Можна частково вирішити проблему, якщо додати в вихідну таблицю допоміжний стовпець, де буде вважатися вартість кожної угоди:
Тепер можна поруч закинути в область значень вартість і кількість - і ми отримаємо майже те, що потрібно:
Чи залишиться поділити одне на інше, але зробити це, начебто, просте математичне дію всередині зведеної не так просто. Доведеться або додавати в зведену обчислюване поле (вкладка Аналіз - Поля, елементи, набори - Обчислюване поле), або вважати звичайною формулою в сусідніх осередках або залучати функцію ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ (GET.PIVOT.DATA), про яку я вже писав . А якщо завтра зміняться розміри зведеної (асортимент товарів), то всі ці формули доведеться вручну коригувати.
Загалом, як-то все незручно, занадто багато роботи і наганяє тугу. Та ще й додатковий стовпець у вихідних даних потрібно руками робити. Але гарне рішення є.
Розрахунок середньозваженого в зведеній таблиці за допомогою Power Pivot і мови DAX
Якщо у вас Excel 2013-2016, то в нього вбудований суперпотужний інструмент для аналізу даних - надбудова Power Pivot, в порівнянні з якою зведені таблиці з їх можливостями - як рахунки проти калькулятора. Якщо у вас Excel 2010, то цю надбудову можна абсолютно безкоштовно скачати з сайту Microsoft і теж собі встановити. За допомогою Power Pivot розрахунок середньозваженого (і інших неможливих в звичайних зведених штук) дуже сильно спрощується.
1. Для початку, завантажимо нашу таблицю в Power Pivot. Це можна зробити на вкладці Power Pivot кнопкою Додати в модель даних (Add to Data Model). Відкриється вікно Power Pivot і в ньому з'явиться наша таблиця.
2. Потім клацніть в рядок формул і введіть туди формулу для розрахунку середньозваженого:
Кілька нюансів по формулі:
- У Power Pivot є свій вбудовану мову з набором функцій, інструментів і певним синтаксисом, який називається DAX . Так що можна сказати, що ця формула - на мові DAX.
- Тут WA - це назва обчислюваного поля (в Power Pivot вони ще називаються заходи), яке ви придумуєте самі (я називав WA, маючи на увазі Weighted Average - "середньозважене" по-англійськи).
- Зверніть увагу, що після WA йде не дорівнює, як в звичайному Excel, а двокрапка і так само.
- При введенні формули будуть випадати підказки - використовуйте їх.
- Після завершення введення формули треба натиснути Enter, як і в звичайному Excel.
3. Тепер будуємо зведену. Для цього у вікні Power Pivot виберіть на вкладці Основне - Зведена таблиця (Home - Pivot Table). Ви автоматично повернетеся в вікно Excel і побачите звичний інтерфейс побудови зведеної таблиці і список полів на панелі праворуч. Залишилося закинути поле Найменування в область рядків, а нашу створену формулою міру WA в область значень - і завдання виконане:
Ось так - красиво і витончено.
Загальна мораль: якщо ви багато і часто працюєте зі зведеними таблицями і вам їх можливості "тісні" - копайте в сторону Power Pivot і DAX - і буде вам щастя!
<
