Сводные таблицы
Сводные таблицы — это специальные таблицы, обобщающие и анализирующие данные из одной или нескольких таблиц.
Исходные данные для сводной таблицы могут находиться в списке на одном листе рабочей книги, на нескольких листах, во внешней базе данных или в другой сводной таблице. Меняя структуру таблицы, можно получать различные сводные ведомости одних и тех же исходных таблиц.
Сводные таблицы тесно связаны с исходными данными. При изменении исходных данных сводная таблица пересчитывается, но не автоматически.
Перед построением сводной таблицы на основе списка следует убрать из него промежуточные итоги и наложенные фильтры
Сводные таблицы сами обеспечивают подведение итогов и фильтрацию данных, но построить сводную таблицу по списку с уже имеющимися промежуточными итогами невозможно.
Сводная таблица по одному диапазону значений
Пусть у нас имеется следующая исходная таблица:
Выделяем любую ячейку в этой таблице и далее на вкладке Вставка в группе Таблицы щелкаем по верхней части кнопки Сводная таблица:
Откроется диалоговое окно Создание сводной таблицы. Параметр Выбрать таблицу или диапазон будет выбран автоматически. В поле Таблица или диапазон отображается выбранный диапазон данных (если мы предварительно выделили ячейку). Кроме того, выбран параметр места размещения отчета На новый лист:
Поскольку изменять ничего не надо, то сразу нажимаем кнопку OK.
На новом листе появятся две области — слева область макета, справа Список полей сводной таблицы:
В области макета будет создан отчет сводной таблицы. В Списке полей сводной таблицы показаны заголовки столбцов исходных данных.
Отчет сводной таблицы создается путем перемещения одного из полей в область макета отчета сводной таблицы.
Если щелкнуть мышью вне области макета (отчета сводной таблицы), список полей сводной таблицы исчезнет. Чтобы снова вывести список полей на экран, щелкните область макета сводной таблицы или отчет.
Страница — обеспечивает фильтрацию данных сводной таблицы по значениям выбранных полей. Строка, Столбец — обеспечивают группирование строк и столбцов списка для вычисления итогов. Данные — содержит произвольное число полей, не включенных в другие области. Одно и тоже поле может быть многократно размещено в области Данные, если для него нужны разные виды итогов (сумма, среднее значение, и т. Порядок следования полей в областях Страница, Строка, Столбец определяет иерархию группирования данных и формируемых итогов.
Пусть, например, мы хотим узнать
по полю На руки. Для этого перетаскиваем (зажав левую кнопку мыши) пункт На руки в область Данные:
В результате сразу получаем:
В открывшемся окне Параметры поля значений можно изменить текст (Пользовательское имя) и выбрать другую арифметическую операцию:
Например, изменим текст:
Добавим к сводной таблице еще четыре параметра: Максимальная зарплата, Минимальная зарплата, Средняя зарплата и Количество работников. Для этого еще четыре раза перетащим На руки в ячейку с суммой:
В результате получим:
Сводная таблица по нескольким диапазонам значений
Пусть у нас имеются три списка, отражающие выплаты сотрудникам за три месяца:
Прежде всего надо добавить на панель быстрого доступа кнопку Мастера сводных таблиц и диаграмм. Для этого щелкните стрелку рядом с панелью быстрого доступа:
В группе Выбрать команды из выберите Все команды:
В списке выберите пункт Мастер сводных таблиц и диаграмм, нажмите кнопку Добавить, а затем – кнопку ОК:
Теперь у нас на панели быстрого доступа есть кнопка Мастера сводных таблиц и диаграмм:
Таким же образом можно добавлять любые часто используемые команды. Для удаления ненужных кнопок используйте правую кнопку мыши (пункт Удалить с панели быстрого доступа).
Теперь вызываем Мастера сводных таблиц и диаграмм щелкнув по новой кнопке в Панели быстрого доступа.
Выбираем пункт в нескольких диапазонах консолидации и щелкаем по кнопке Далее:
Оставляем как есть и щелкаем по кнопке Далее:
При помощи кнопки и кнопки Добавить добавляем три диапазона с исходными таблицами (добавляем только список без начальных условий!):
Затем кнопка Далее. В следующем окне оставляем все как есть и нажимаем кнопку Готово:
Вот что в итоге получилось:
Чтобы придать сводной таблице окончательный вид, надо скрыть столбец Общий итог (щелкаем правой кнопкой мыши по имени столбца и выбираем пункт Скрыть) и изменить положение столбцов На руки и Оклад. Для перемещения столбца влево или вправо щелкаем правой кнопкой мыши по ячейке с заголовком столбца и выбираем пункт Переместить:
Окончательный вид сводной таблицы:
Перейти к исходным данным
Если источник данных(исходная таблица) недоступен по какой-либо причине(например, сводная построена на основании таблицы другой книги и книга закрыта), то программа сообщит об этом.
Расскажи друзьям, если статья оказалась полезной:
Видеоинструкции по использованию надстройки MulTEx
Get expert help now
Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.
Для того чтобы обновить данные в сводной таблице необходимо проделать следующие операции:
Необходимо выделить ячейку в сводной таблице, содержимое которой необходимо обновить.
После этого нажмите кнопку «Обновить» данные на панели инструментов Сводные таблицы или же в пункте меню «Данные» «обновить данные». После этого произойдет автоматическое обновление ячейки.
Рисунок 4 Значок «Обновить»
Чтобы сводная таблица обновлялась при открытии книги, выберите команду Параметры в меню «Сводная таблица» на панели инструментов «Сводные таблицы». Установите флажок «обновить» при открытии в группе «Источник».
Если сводная таблица создана на основе данных, находящихся в списке Microsoft Excel, и происходит добавление новых полей в исходный диапазон, добавить эти поля после обновления таблицы можно с помощью мастера сводных таблиц. Для этого выделите ячейку сводной таблицы, нажмите кнопку «Мастер сводных таблиц» на панели инструментов «Сводные таблицы» и перетащите новые поля в область сведения.
Чтобы обновить все сводные таблицы в книге, на панели инструментов «Сводные таблицы» выберите команду » Обновить данные» в меню «Сводная таблица».
Для отключения обновления сводной таблицы при открытии файла выделите ячейку сводной таблицы, для которой обновление не будет происходить при открытии. Далее на панели инструментов «Сводные таблицы» выберите команду «Параметры» в меню «Сводная таблица».
После этого в появившемся окне снимите флажок «обновить при открытии».
Рисунок 5 Параметры данных сводной таблицы
Выяснив что обновления нам нужны из за того что данные в таблице динамические, и научившись использовать обновления, переходим к следующему не менее важному разделу «Сортировки».
Дополнительные сведения
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Как перейти к редактированию исходных данных прямо из сводной таблицы?
Несомненно, основная задача работы со сводными таблица — анализ данных. А раз мы что-то анализируем, значит может потребоваться и что-то изменить в случае нахождения каких-то расхождений. И самое печальное здесь то, что нельзя изменять значения непосредственно внутри сводной таблицы(OLAP не в счет :)). Зато мы можем посмотреть из каких строк исходной таблицы состоит конкретное значение. Например, у нас есть таблица реализации следующего вида:
На основе её мы построили примерно такую сводную таблицу(как создать сводную можно посмотреть и прочитать в этой статье: Общие сведения о сводных таблицах):
В итогах у нас значения по прибыли, а красным выделены отрицательные значения, т. именно к таким нам следует присмотреться в первую очередь. Чтобы понять из каких строк исходной таблицы получилась сумма -1155 мы можем выделить эту ячейку внутри сводной таблицы -правая кнопка мыши -Показать детали(Show Details):
В итоге будет создан новый лист с таблицей, содержащей только те строки исходных данных, на основании которых сформировано выделенное нами значение:
Да, мы теперь можем целенаправленно и точечно посмотреть, изучить только нужные данные и принять решение. Но тут другая проблема: если нам надо что-то изменить, то это ни на что не повлияет. показ деталей из сводной никак не связан уже ни с исходными данными, ни с самой сводной таблицей. Как же быть? Можно попробовать вернуться в лист с исходными данными и отфильтровать последовательно каждый столбец до нужных значений. Но это явно не самый быстрый и точный путь. Поэтому его даже не рассматриваем. Я хочу предложить путь быстрее и эффективнее. После того как отобразили детали — ничего с этим листом пока не делать. Переходим на лист с исходными данными -вкладка Данные(Data) -группа Сортировка и фильтр(Sort & Filter) -Дополнительно(Advanced). В появившейся форме указываем следующие данные:
Краткое видео процесса:
И одна большая ложка дегтя, которую никак не объехать: данный прием работает не со всеми сводными. Если сводная создана из базы данных или иных внешних источников это может не сработать, т. хоть детали и отобразятся, сами исходные данные содержатся вне файла. Так же отображение деталей может быть недоступно, если кэш сводной таблицы не сохранен в самом файле
Но даже при всем этом: как-то это все долго и не очень удобно. Поэтому я решил пойти дальше и сделать все необходимое при помощи макросов(Visual Basic for Applications). Придется в них чуть-чуть вникнуть, но оно того стоит, т. для полного удобства мы сделаем вот что:
- по двойному клину на ячейке сводной таблицы автоматически отфильтруем данные в исходной таблице и перейдем в неё
- после изменений в исходной таблице и возврата в сводную — автоматически обновим эту сводную таблицу
можно сказать полностью заменим стандартный пункт «Показать детали».
Для этого создаем стандартный модуль (переходим в редактор VBA(Alt+F11) -Insert -Module) и вставляем в него код:
Это основной код фильтрации данных в источнике данных на основании выделенной в сводной таблице ячейке. Далее все в том же редакторе VBA переходим в модуль ЭтаКнига(ThisWorkbook) и вставляем туда следующий код:
‘ Author : Щербаков Дмитрий(The_Prist)
‘ Профессиональная разработка приложений для MS Office любой сложности
‘ Проведение тренингов по MS Excel
‘ Purpose: Обработка двойного клика мыши в сводной таблице
‘ и переход к сводной после редактирования источника данных
‘ Так же при открытии книги создается пункт в меню правой кнопки мыши сводной — Edit Source
‘ и удаляется перед закрытием этой книги
‘при активации листа со сводной таблицей — обновляем все сводные
Workbook_SheetActivate( Sh )
pt PivotTable
‘обновляем все сводные таблицы на листе, на который перешли
pt Sh. PivotTables
pt. PivotCache. Refresh
‘обрабатываем двойной клик мыши внутри сводной таблицы
Workbook_SheetBeforeDoubleClick( Sh , Target Range, Cancel )
rcPT PivotTable
‘проверяем, является ли ячейка,
‘на которой дважды щелкнули мышью
‘ячейкой внутри сводной таблицы
rcPT = Target. PivotTable
0
‘если это ячейка сводной
rcPT
‘вызываем процедуру фильтрации источника данных
EditPivotSource
Cancel =
‘ СОЗДАНИЕ И УДАЛЕНИЕ ПУНКТА МЕНЮ В СВОДНОЙ
‘добавляем в меню сводных таблиц пункт «Edit Source»,
‘который будет отбирать данные непосредственно в источнике данных
Workbook_Open()
bt CommandBarControl, indx
‘ищем пункт меню «Показать детали»
bt = Application. CommandBars(«PivotTable Context Menu»). FindControl(ID:=462)
‘если нашли — добавим после него новый пункт «Edit source»
‘ при нажатии которого будет вызываться наш код перехода к источнику
‘если не нашли — ставим вторым пунктом
bt
indx = bt. Index
indx = 1
‘пробуем удалить пункт «Edit source», если он ранее был создан
‘чтобы не было задвоения
Application. CommandBars(«PivotTable Context Menu»). Controls(). Delete
‘добавляем новый пункт
Application. CommandBars(«PivotTable Context Menu»). Controls. Add(before:=indx + 1). Caption =. OnAction = & ThisWorkbook. Name &
‘перед закрытием книги удаляем созданный нами пункт меню
Workbook_BeforeClose(Cancel )
Application. CommandBars(«PivotTable Context Menu»). Controls(). Delete
Все, теперь останется только сохранить книгу в формате «Книга Excel с поддержкой макросов(. xlsm)» и открыть заново. Хотя это нужно лишь для того, чтобы создался новый пункт меню в сводной таблицы, весь остальной функционал будет работать и без перезапуска. Надеюсь данный трюк будет полезен всем, кто работает со сводными.
Ну а если совсем лень делать что-то своими руками, то можно воспользоваться данной возможностью, уже встроенной в мою надстройку MulTex. Там же есть вариант отображения деталей всех выделенных ячеек, а не только одной, как это реализовано в самом Excel.
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика
Автообновляемая сводная таблица
Многие, кто работал со сводными таблицами наверняка знают, что при изменении значений в исходных данных(данные, на основании которых создана сводная) сама сводная таблица при этом не обновляется. Чтобы обновить надо проделать еще пару манипуляций:
Однако, если в конец исходных данных добавить строку(или несколько), то с большой долей вероятности даже обновление сводной таблицы не поможет — добавленная строка не появится в сводной. И чтобы её увидеть необходимо будет изменить источник данных для сводной таблицы, включив новую строку в диапазон. Не очень удобно, не правда ли? Чтобы добиться расширения диапазона исходных данных автоматически вместе с добавлением туда данных, лучше позаботиться об этом до создания сводной таблицы.
Недоавтообновление
Почему «недо» — жать кнопку Обновить все же придется. Но не отчаивайтесь — читайте до конца и мы научимся обновлять все автоматом.
Для счастливых обладателей Excel 2007 и старше есть простой способ без лишних телодвижений. Это встроенный инструмент Таблица(Table). Его еще иначе называют «умная таблица» и я тоже буду применять этот термин, чтобы не было путаницы.
— это специальный объект, который представляет собой правильную таблицу с заголовками, которая расширяется по мере добавления в неё данных. В ней много еще чего полезного, но нас интересует сейчас именно то, что она расширяется сама по мере добавления данных и что на её основе можно создать сводную таблицу. В нашем случае она будет играть роль динамического именованного диапазона(стандартный именованный диапазон не может быть источником данных для сводной таблицы, поэтому и приходится идти другими путями). Чтобы создать такую таблицу необходимо:
К выбранному диапазону автоматически будет применено форматирование как таблицы, используемое по умолчанию. На это можно не обращать внимания, т. для наших целей это по большому счету не важно.
А дальше все как привыкли:
- Выделить любую ячейку исходной таблицы(теперь уже «умной»)
- Вкладка Вставка(Insert) -группа Таблица(Table) -Сводная таблица(PivotTable)
- В диалоговом окне Создание сводной таблицы(Create PivotTable) в пункте Выбрать таблицу или диапазон(Select a table or range) в поле Таблица или диапазон(Range/Table) будет автоматически указан не адрес какого-то диапазона, а имя созданной умной таблицы:
Далее надо определить место размещения Сводной таблицы:
На новый лист (New Worksheet)На существующий лист (Existing Worksheet) - На новый лист (New Worksheet)
- На существующий лист (Existing Worksheet)
- нажать OK
Готово. Теперь при добавлении строк в эту таблицу для их отображения в сводной достаточно будет лишь обновить сводную таблицу как привыкли. Правда, тут тоже есть нюанс — добавлять строки надо правильно. Можно вбить данные в любую ячейку первой пустой строки таблицы — таблица автоматом расшириться, добавив еще одну строку. Теперь туда можно скопировать нужные данные или добить вручную. Если надо вставить сразу несколько строк — в правом нижнем углу последней строки умной таблицы есть слегка выделяющийся уголочек, который надо ухватить мышью и растянуть на нужное кол-во строк/столбцов. Если мы просто скопируем строки ниже таблицы, то она не расширится. Это надо учитывать.
настроить на авторасширение уже созданную сводную
, то порядок почти такой же, только сводную таблицу создавать не надо. Преобразуем исходные данные в умную таблицу, переходим на лист со сводной таблицей. Выделяем любую ячейку в сводной таблице, переходим на динамическую вкладку Работа со сводными таблицами(PivotTable Tools) -Параметры(Options) -группа кнопок Даныне(Data) -Источник данных(Change data Source). В появившемся окне в поле Таблица или диапазон(Table/Range) указываем либо ссылку на всю умную таблицу, либо имя нашей умной таблицы(если знаете где его подсмотреть).
На что здесь следует обратить внимание:
если указывался диапазон, то если он указан верно — в поле вместо адреса ячеек будет отображено имя умной таблицы:
Если же после указания видите именно диапазон — значит что-то указано неверно или таблица не является умной(возможно, форматирование от умной таблицы, но сама умная таблица была удалена).
Для полного счастья можно подключить работу макросов. Что я хочу? Я хочу, чтобы как только я изменил/добавил данные в исходные данные — сводная тут же обновилась. Для этого надо сделать следующее:
- убеждаемся, что макросы разрешены(Почему не работает макрос?, Что такое макрос и где его искать?)
- перейти на лист исходных данных(в моем случае лист так и называется — Исходные данные)
- жмем на ярлычке этого листа правой кнопкой мыши -Исходный текст(View code):
- вставляем туда следующий код:
Worksheet_Change( Target Range)
‘проверяем — изменения внутри умной таблицы или нет
Intersect(Target, Target.Parent.ListObjects(1).Range)
‘если внутри таблицы, то обновляем сводную таблицу на листе «Автообновляемая сводная»
Sheets().PivotTables(1).RefreshTable
‘для всех сводных на листе
‘ Dim pt As PivotTable
‘ For Each pt In Sheets(«Автообновляемая сводная»).PivotTables
‘ pt.RefreshTable
‘ Next - Сохраняем файл(это опционально :))
Все, теперь при любом изменении внутри исходных данных(будь это добавление/удаление строк или просто изменение значений внутри таблицы) сводная таблица обновиться без занудных действий вроде выделения сводной и жмахания кнопки Обновить. Пара важных комментариев к коду:
Так же можно использовать и иной подход — вставить в модуль листа Автообновляемая сводная такой код:
Worksheet_Activate()
Me. PivotTables(1). RefreshTable
тогда сводная на листе будет обновляться лишь тогда, когда будет активирован лист со сводной. Плюсы подобного подхода очевидны в случаях, если часто приходится менять исходные данные. В первом коде сводная будет обновляться при каждом ручном изменении в исходных данных, даже если после этого мы не переходили на лист сводной, а дальше стали делать изменения в исходных данных.
Tips_PT_AutoRefreshPT. xlsm (46,5 KiB, 2 848 скачиваний)