Финансы в Excel

Главная Статьи Сводные таблицы Редактирование сводной таблицы

Редактирование сводной таблицы

Подключение макросов

Для работы примера требуется подключение макросов VBA. В Excel 2002-2003 может потребоваться предварительно изменить безопасность макросов до среднего или низкого уровня (Сервис \ Макросы \ Безопасность). В Excel 2007 щелкните на строку сообщения под лентой, а затем подтвердите операцию.

Без подключенных макросов пример будет работать в стандартном режиме отображения деталей (drill-down) при двойном клике в области данных сводной таблицы.

Функциональность

Программный код перехватывает двойной клик в области данных сводной таблицы и отображает исходные данные в виде отфильтрованных строк в отдельном окне. После редактирования и возврата в окно сводной таблицы, вторичное окно исходных данных автоматически закрывается, а данные в отчете обновляются.

В примере исходные данные размещены на листе «Data», а сводная таблица на листе «Отчет». В сводной таблице имеются 2 поля данных: «Объем», «Выручка» и вычисляемое поле «Средняя цена». Двойной клик доступен на обычных полях, при выборе вычисляемого поля никаких действий не производится, так как в этом случае, очевидно, нечего редактировать.

В новом окне редактирования выделяется ячейки столбца по выбранным в сводной таблице данным:

Обратите внимание, что в двух окнах находится одна и та же рабочая книга. Это видно по ярлыкам листов. Там же при первом открытии примера вы заметите появление нового листа «Filter». Этот лист используется для организации фильтра на листе исходных данных.

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

Программный код примера работает с любыми сводными таблицами, основанными на простых данных Excel. Для практического применения на реальном примере сводной таблицы несложно организовать редактирование в диалоговом окне вместо клонирования окна Excel.

Общий принцип поиска ключевой информации сводной таблицы может помочь создать более сложный интерфейс для выборки и редактирования информации из базы данных.

Алгоритм работы

1. Устанавливается свойство сводной таблицы, отменяющее стандартное поведение на двойной клик.
2. На уровне листа, на котором располагается сводная таблица, перехватывается событие двойного клика в области данных.
3. Проверяется, не является ли поле вычисляемым. Создается пустой лист для фильтрации исходных данных.
4. Формируется значения фильтра через проверку диапазонов областей строк, столбцов и страниц сводной таблицы. Эти значения записываются на служебный лист.
5. С помощью операции «Расширенный фильтр» фильтруется исходный диапазон данных.
6. Создается новое окно, в которое выводится отфильтрованный диапазон исходных данных.
7. Включается событие на активизацию окна Excel. При возврате в окно со сводной таблицей, второе окно с исходными данными закрывается.

Программный код

Небольшие комментарии к наиболее интересным частям программного кода VBA.

События рабочей книги (ThisWorkbook):

Автоматическое закрытие второго окна рабочей книги происходит при условии, что имя текущего окна заканчивается на «:2» (Right(Wn.Caption, 2) = «:2»). Этот суффикс (двоеточие плюс номер) Excel присваивает новому окну автоматически.

Процедура поиска и вывода данных для редактирования:

Исправление ошибки работы с датами в Excel 2007:

Комментарии

Добрый день.
Подскажите пож: «как убрать фильтр чтобы на листе Data отразилась вся база с изменениями»
Заране благодарен

Доброго времени суток у меня возникает ошибка У меня не закрывается второе «новое» окно после возврата к сводной таблице. Данные в сводной таблице обновляются только при нажатие обновить данные и второе окно приходится вручную закрывать после изменение данных

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

Спасибо!
Хотел сам писать что-то подобное, а тут раз и есть решение.
Отмечу одно — если даты сгруппировать, например по месяцам работать перестает. Почему — понятно, переписывать долго, точнее некогда. А сам макрос — здоровский. С именованными диапазонами работает легко, если имя присваивать листу, на не книге.

Всё верно. Если источник «умная таблица», то работает. Не работает если источником Сводной таблицы служит именованный диапазон, т.е. если источник сводной таблицы указан не ‘Лист1’!$A:$B, а Таблица1 (которая является диапазоном ‘Лист1’!$A:$B, но с присвоенным именем Таблица1. Но это мелочи, имхо.

Марат, простите. Обманул. И с именами массивов работает. Ошибка была моя в том, что лист Filter надо удалять, если в макросе меняешь название листа с отчётом и с данными. В общем, макрос — конфетка.

Прошу подсказать, как прописать, что б было автоматическое определение листа «отчета» и листа «данных», а то в случае если в одной книге несколько сводных таблиц с разных источников данных. надо прописывать ручками.

Николас, спасибо за проявленный интерес. Вообще, это в чистом виде демонстрационный пример. Для практического применения я бы формы VBA использовал. Уж больно ненадежно это управление окнами рабочей книги.

«Умная» — .это имеется в виду то, что через ленту «Вставка\Таблица» добавляется? По идее нет разницы в источнике. Попробуйте.

Всё верно. Если источник «умная таблица», то работает. Не работает если источником Сводной таблицы служит именованный диапазон, т.е. если источник сводной таблицы указан не ‘Лист1’!$A:$B, а Таблица1 (которая является диапазоном ‘Лист1’!$A:$B, но с присвоенным именем Таблица1. Но это мелочи, имхо.

Прошу подсказать, как прописать, что б было автоматическое определение листа «отчета» и листа «данных», а то в случае если в одной книге несколько сводных таблиц с разных источников данных. надо прописывать ручками.

Николас, спасибо за проявленный интерес. Вообще, это в чистом виде демонстрационный пример. Для практического применения я бы формы VBA использовал. Уж больно ненадежно это управление окнами рабочей книги.

«Умная» — .это имеется в виду то, что через ленту «Вставка\Таблица» добавляется? По идее нет разницы в источнике. Попробуйте.

Николас, спасибо за замечание.
Как оказалось, в 2010м очередное чудо. почему-то перестало отрабатывать свойство PivotItem.Visible. Исправил файл.

[expert_bq id=»1570″]Постройте сводную таблицу, где в области строк Города и Товары, а в области сумм Доходы если вы не знаете, как создать сводную таблицу, посмотрите статью Как построить сводную таблицу в Excel. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Давайте рассмотрим ряд инструментов, без которых мощнейший Excel становится просто мощным калькулятором с возможностью быстро рисовать таблицы. Речь идёт о Фильтре и Сводных таблицах. Лучше всего рассмотреть конкретный пример.

Обучение Excel 2010: Фильтр и Сводная таблица

1. Устанавливается свойство сводной таблицы, отменяющее стандартное поведение на двойной клик.
2. На уровне листа, на котором располагается сводная таблица, перехватывается событие двойного клика в области данных.
3. Проверяется, не является ли поле вычисляемым. Создается пустой лист для фильтрации исходных данных.
4. Формируется значения фильтра через проверку диапазонов областей строк, столбцов и страниц сводной таблицы. Эти значения записываются на служебный лист.
5. С помощью операции «Расширенный фильтр» фильтруется исходный диапазон данных.
6. Создается новое окно, в которое выводится отфильтрованный диапазон исходных данных.
7. Включается событие на активизацию окна Excel. При возврате в окно со сводной таблицей, второе окно с исходными данными закрывается.

Шаг 3. Все, конечно замечательно, НО хотелось бы рядом с процентами видеть суммы. И это тоже не проблема! Открою маленький секрет: в область значений сводной таблицы мы можем несколько раз перетащить один и тот столбец. Для этого просто захватываем мышкой нужное поле и несколько раз перетаскиваем его в область сумм.