Советы для продвинутых пользователей Excel: как создать динамический отчет в стиле сводной таблиц...

Пользователи Excel, желающие узнать, как оптимизировать свою работу, могут перейти по ссылке 👉 https://affiliate.excel-university.co...

Советы по Excel для продвинутых пользователей: как создать динамический отчёт в стиле сводной таблицы с помощью одной формулы | Excel University
Исторически существовало два основных способа создания отчётов в Excel. Мы могли использовать формулы для вычисления значений отчёта или сводную таблицу. У обоих вариантов были свои плюсы и минусы, и нам приходилось выбирать тип отчёта в зависимости от контекста нашей книги. В этом видео я покажу вам новый вариант, который объединяет плюсы и минусы обоих вариантов. Он использует новые функции VSTACK и HSTACK для создания динамического отчёта в стиле сводной таблицы с одной формулой, которая автоматически обновляет значения и расширяется для добавления новых элементов без участия пользователя (без ручного обновления).

Давайте на секунду вернёмся и вспомним плюсы и минусы традиционных отчётов на основе формул и сводных таблиц. Нам необходимо разобраться в этих деталях, чтобы в полной мере оценить преимущества нового решения, которое предоставляют VSTACK/HSTACK.

Отчёты на основе формул

Один из подходов к построению отчётов заключается в том, чтобы ввести заголовки отчётов в ячейки, а затем написать формулы для расчёта значений отчёта. Поскольку значения отчёта вычисляются с помощью формул, они автоматически пересчитываются при изменении значений зависимых ячеек. Однако проблема заключается в том, что они не расширяются автоматически для включения новых элементов, найденных в обновлённом источнике данных. Это означает, что если в базовых данных появляется новый заголовок отчёта, вам придётся вручную вставить новую строку листа в отчёт, ввести новый заголовок, а затем написать или заполнить формулу для расчёта значения. Отсутствие возможности автоматического расширения для включения новых элементов было серьёзным недостатком. Именно поэтому сводные таблицы стали такой замечательной альтернативой отчётам на основе формул.

Отчёты сводных таблиц

Отчёты сводных таблиц автоматически корректируют свой размер в соответствии с любыми новыми найденными элементами (т.е. автоматически создают новую строку отчёта для любых новых элементов, найденных в исходных данных). Это замечательно и одна из многих причин, по которой пользователи Excel так любят сводные таблицы. Единственная небольшая проблема заключается в том, что пользователям нужно помнить о необходимости вручную нажимать кнопку «Обновить» после изменения исходных данных в рабочей книге.

Параметры

И это были наши два основных варианта буквально десятилетиями. Нам пришлось бы выбирать между этими двумя вариантами, исходя из особенностей нашей рабочей книги. С одной стороны, мы могли бы создать отчёт на основе формул, который автоматически пересчитывал бы значения, но нам пришлось бы вручную вставлять новые строки отчёта и заполнять формулы. С другой стороны, мы могли бы создать сводную таблицу, которая автоматически включает новые элементы, но для этого требуется ручное обновление.

Годами я мечтал о лучшем из обоих миров. Отчёт на основе формул, который не только автоматически пересчитывал бы значения, но и динамически расширялся, добавляя новые метки отчёта (элементы).

Друзья, это желание наконец-то сбылось!

С появлением VSTACK и HSTACK мы можем создать такой отчёт. Пожалуй, самый красивый из всех.

Примечание: VSTACK/HSTACK доступны не во всех версиях Excel. Подробнее о доступности:
https://techcommunity.microsoft.com/t...