Итоги в сводной таблице Excel

Сегодня расскажу, как простое использование функции RowNo() с выражением IF() поможет вам удобно считать итоги в сводной таблице.

Анархия EXCEL

Конечно, сводные таблицы прекрасны и нужны, но с точки зрения визуализации данных — это не самый лучший вариант, т.к. в них сложно проследить тренды и отследить выбросы.

Часто в разработке можно столкнуться с просьбами: «Вот наш Excel, вот наши таблицы, переведите все в QlikView/ Qlik Sense, а вот визуализаций никаких нам не надо, наш формат — хардкорные сводные таблицы»! Ок, мы не станем спорить с тем, кто так просит, ведь есть случаи, где без таблиц невозможно обойтись, например, финансовому директору или актуариям в страховой компании.

Рано или поздно к вам обратятся за помощью в добавлении строки среднего по итогам. Это не проблема, если вы работаете с прямой таблицей, но в сводных таблицах все не так просто. Давайте посмотрим настройки диаграмм:

В большинстве случаев рекомендуют использовать функцию aggr(), но такой способ явно не самый простой. Давайте попробуем решить задачу иначе.

Сводные таблицы: маленькие хитрости

Давайте добавим в нашу сводную таблицу выражение RowNo(), у нас получится что-то такое:

RowNo() в последней колонке. Обратите внимание: в итогах RowNo() получается «0».

А давайте поработаем с If():

RowNo() позволяет нам управлять каждой ячейкой столбца. Давайте поправим наше условие If(), добавив среднее по годам:

Да, так просто — таблица показывает среднее!

Вы можете спросить, почему мы используем не простое среднее, а RangeAvg(). Такой способ экономит память на вычислениях, т.к. он является внутренней функцией. Также мы заменили итоги на среднее в ячейке. Это мы изменили через представление (настройки объекта, ярлык для итогов):

Итоги

Такой подход работает не только для строк, но и для колонок. Так, вы получили средние итоги по каждой строке.

Что вам для этого понадобиться: ColumnNo() и функция First() вместо RownNo() и Top().

Коллеги, на этом сегодня все! Спасибо за внимание.