rss
  •  
Обучение Microsoft Excel: от основ до PowerBI

Расчет доли в модели Power Pivot

| Категория: Надстройки Power, Приемы и советы |

3

Всё, что может быть подсчитано, должно быть рассчитано! Работая в Excel, можно вспомнить о дополнительных вычислений в сводных таблицах. Но! В каждом отчете возникает необходимость в добавлении поля, выбор вычисления, изменении имени поля. А при создании вычисляемых полей – мер – в Power Pivot, значительно сокращается количество действий. Ведь подобную настройку мы делаем при создании меры, т.е. один раз!

Формула выглядит так:
Доля продажи, %:=SUMX(‘БытоваяТехника’;
‘БытоваяТехника'[Цена за шт, р]*‘БытоваяТехника'[Количество, шт])/
SUMX(ALL(‘БытоваяТехника’);
‘БытоваяТехника'[Цена за шт, р]*’БытоваяТехника'[Количество, шт])

  • Функция SUMX(Table;Expression) – позволяет без создания вычисляемых столбцов, сразу задать расчетное выражение по исходным столбцам. Когда мера добавляется в отчет, то значения рассчитываются по значениям категорий. Например, наименование или производитель.
  • Функция ALL игнорирует все возможные фильтры и вычисляет итоговое значение по всем данным.

Затем следует применить к созданной мере процентный формат. Остается добавить вычисляемое поле в нужный отчёт. Готово!

Расчет доли продажи по наименованиям:

Расчет доли продажи по производителям:

Диаграмма с квартальной “раскраской”

| Категория: Диграммы, Приемы и советы |

12

Диаграммы – прекрасное средство для визуализации и анализа данных. В программе Excel есть много разных вариантов, но всё равно чего-то не хватает или хочется сделать лучше!

1. Подготовка таблицы с исходными данными:

2. Построение комбинированной диаграммы:
ряд Показатель – график с маркерами и по основной оси, а ряд Кв – гистограмма с группировкой по вспомогательной оси:

3. Настройка диаграммы:

а) Параметры для вспомогательной оси:
– максимальное значение задать равное 1.
положение подписинет.
б) Боковой зазор в формате ряда данных для ряда Показатель задать минимальным – 0.
в) Отформатировать точки ряда Кв разными цветами в соответствии с кварталами.
г) Выделить ряд Показатель и установить Линии проекции (вкладка Конструктор, Добавить элемент диаграммы, Линии, Линии проекции)


DIVIDE в Power Pivot

| Категория: Надстройки Power, Приемы и советы |

2

При создании в модели Power Pivot вычисляемого столбца, часто используют обычный оператор деления – /. Однако, при отсутствии части данных или если значения равны нулю, то результат вычисления – бесконечность:

Практичнее использовать функцию DIVIDE, которая не будет выдавать ошибку от деления на ноль.

DIVIDE(<numerator>, <denominator> [,<alternateresult>]), где
<numerator> – числитель ,
<denominator> – знаменатель
<alternateresult> – альтернативный вариант, если не указан, то по умолчанию используется пустая строка BLANK()