Сегодня расскажу, как простое использование функции RowNo() с выражением IF() поможет вам удобно считать итоги в сводной таблице.
Анархия EXCEL
Конечно, сводные таблицы прекрасны и нужны, но с точки зрения визуализации данных – это не самый лучший вариант, т.к. в них сложно проследить тренды и отследить выбросы.
Часто в разработке можно столкнуться с просьбами: «Вот наш Excel, вот наши таблицы, переведите все в QlikView/ Qlik Sense, а вот визуализаций никаких нам не надо, наш формат – хардкорные сводные таблицы»! Ок, мы не станем спорить с тем, кто так просит, ведь есть случаи, где без таблиц невозможно обойтись, например, финансовому директору или актуариям в страховой компании.
Рано или поздно к вам обратятся за помощью в добавлении строки среднего по итогам. Это не проблема, если вы работаете с прямой таблицей, но в сводных таблицах все не так просто. Давайте посмотрим настройки диаграмм:
 Прямая таблица. Опции для итогов, смотрите. |
 Сводная таблица. Функция итогов неактивна. |
В большинстве случаев рекомендуют использовать функцию aggr(), но такой способ явно не самый простой. Давайте попробуем решить задачу иначе.
Сводные таблицы: маленькие хитрости
Давайте добавим в нашу сводную таблицу выражение RowNo(), у нас получится что-то такое:
RowNo() в последней колонке. Обратите внимание: в итогах RowNo() получается «0».
А давайте поработаем с If():
| 1 | If(RowNo()=0,’Yes!!!’,’Nope’) |
RowNo() позволяет нам управлять каждой ячейкой столбца. Давайте поправим наше условие If(), добавив среднее по годам:
| 1 | If(RowNo()=0 |
| 2 | ,RangeAvg(Top(count(OrderID),1,(NoOfRows()))) |
| 3 | ,Count(OrderID) |
| 4 | ) |
Да, так просто – таблица показывает среднее!
Вы можете спросить, почему мы используем не простое среднее, а RangeAvg(). Такой способ экономит память на вычислениях, т.к. он является внутренней функцией. Также мы заменили итоги на среднее в ячейке. Это мы изменили через представление (настройки объекта, ярлык для итогов):
Итоги
Такой подход работает не только для строк, но и для колонок. Так, вы получили средние итоги по каждой строке.
Что вам для этого понадобиться: ColumnNo() и функция First() вместо RownNo() и Top().
Коллеги, на этом сегодня все! Спасибо за внимание.
Василий Петренко
B3
Максим
Stepan
Павел Карасев
Стоит добавить, что для выбора внутри Expression определенной части формулы в зависимости от контекста лучше использовать специальные функции — Dimensionality() и SecondaryDimensionality(). На этом блоге уже были про них статьи.
RowNo() предназначена все таки для других задач.