Несомненно, основная задача работы со сводными таблица - анализ данных. А раз мы что-то анализируем, значит может потребоваться и что-то изменить в случае нахождения каких-то расхождений. И самое печальное здесь то, что нельзя изменять значения непосредственно внутри сводной таблицы(OLAP не в счет :)). Зато мы можем посмотреть из каких строк исходной таблицы состоит конкретное значение.
Например, у нас есть таблица реализации следующего вида:
На основе её мы построили примерно такую сводную таблицу(как создать сводную можно посмотреть и прочитать в этой статье: Общие сведения о сводных таблицах):
В итогах у нас значения по прибыли, а красным выделены отрицательные значения, т.к. именно к таким нам следует присмотреться в первую очередь. Чтобы понять из каких строк исходной таблицы получилась сумма
В итоге будет создан новый лист с таблицей, содержащей только те строки исходных данных, на основании которых сформировано выделенное нами значение:
Да, мы теперь можем целенаправленно и точечно посмотреть, изучить только нужные данные и принять решение. Но тут другая проблема: если нам надо что-то изменить, то это ни на что не повлияет. Т.к. показ деталей из сводной никак не связан уже ни с исходными данными, ни с самой сводной таблицей. Как же быть? Можно попробовать вернуться в лист с исходными данными и отфильтровать последовательно каждый столбец до нужных значений. Но это явно не самый быстрый и точный путь. Поэтому его даже не рассматриваем. Я хочу предложить путь быстрее и эффективнее. После того как отобразили детали - ничего с этим листом пока не делать. Переходим на лист с исходными данными -вкладка Данные
Обязательно оставляем отмеченным пункт Фильтровать список на месте. Нажимаем Ок.
В итоге у нас в исходной таблице отфильтруются ровно те строки, которые были отображены в деталях:
И теперь мы спокойно можем их анализировать и при необходимости изменять.
Только следует помнить, что после любого изменения надо будет обновить сводную(правая кнопка мыши на любой ячейке сводной таблицы -Обновить
И одна большая ложка дегтя, которую никак не объехать: данный прием работает не со всеми сводными. Если сводная создана из базы данных или иных внешних источников это может не сработать, т.к. хоть детали и отобразятся, сами исходные данные содержатся вне файла. Так же отображение деталей может быть недоступно, если кэш сводной таблицы не сохранен в самом файле
Но даже при всем этом: как-то это все долго и не очень удобно. Поэтому я решил пойти дальше и сделать все необходимое при помощи макросов(Visual Basic for Applications). Придется в них чуть-чуть вникнуть, но оно того стоит, т.к. для полного удобства мы сделаем вот что:
- по двойному клику на ячейке сводной таблицы автоматически отфильтруем данные в исходной таблице и перейдем в неё
- после изменений в исходной таблице и возврата в сводную - автоматически обновим эту сводную таблицу
- для большего удобства мы еще создадим в меню правого клика сводной таблицы свой собственный пункт меню "Edit Source", который будет делать то же самое, что и двойной клик
Т.е. можно сказать полностью заменим стандартный пункт "Показать детали".
Для этого создаем стандартный модуль (переходим в редактор VBA(
'--------------------------------------------------------------------------------------- ' Author : Щербаков Дмитрий(The_Prist) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' Purpose: '--------------------------------------------------------------------------------------- Option Explicit Sub EditPivotSource() Dim pt As PivotTable Dim wsDetails As Worksheet Dim rSource As Range, rDetails As Range Dim lAppCalc As Long Application.DisplayAlerts = False lAppCalc = Application.Calculation 'запоминаем установленный режим пересчета формул Application.Calculation = xlCalculationManual Application.ScreenUpdating = False On Error GoTo END_ 'определяем сводную таблицу и её исходные данные Set pt = ActiveCell.PivotTable Set rSource = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)) 'отображаем все данные в листе с исходными данными rSource.EntireRow.Hidden = False 'разрешаем отображение деталей, если запрещено настройками ' Параметры сводной таблицы -Данные -Разрешить отображение деталей If Not pt.EnableDrilldown Then pt.EnableDrilldown = True End If 'показываем лист с данными по выделенной области Selection.ShowDetail = True 'запоминаем лист с деталями - потом надо будет удалить Set wsDetails = ActiveSheet Set rDetails = ActiveSheet.UsedRange rSource.AdvancedFilter xlFilterInPlace, rDetails 'удаляем лист деталей - он больше не нужен wsDetails.Delete 'активируем лист с исходными данными - теперь там отображены только нужные строки rSource.Parent.Activate END_: If Err.Number <> 0 Then MsgBox "Выделите ячейку данных для редактирования", vbInformation, "www.excel-vba.ru" End If 'возвращаем измененные настройки приложения в прежние значения Application.DisplayAlerts = True Application.Calculation = lAppCalc Application.ScreenUpdating = True End Sub |
Это основной код фильтрации данных в источнике данных на основании выделенной в сводной таблице ячейке.
Далее все в том же редакторе VBA переходим в модуль ЭтаКнига(ThisWorkbook) и вставляем туда следующий код:
'--------------------------------------------------------------------------------------- ' Author : Щербаков Дмитрий(The_Prist) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' https://www.excel-vba.ru ' info@excel-vba.ru ' Purpose: Обработка двойного клика мыши в сводной таблице ' и переход к сводной после редактирования источника данных ' ' Так же при открытии книги создается пункт в меню правой кнопки мыши сводной - Edit Source ' и удаляется перед закрытием этой книги '--------------------------------------------------------------------------------------- Option Explicit 'при активации листа со сводной таблицей - обновляем все сводные Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim pt As PivotTable 'обновляем все сводные таблицы на листе, на который перешли For Each pt In Sh.PivotTables pt.PivotCache.Refresh Next End Sub 'обрабатываем двойной клик мыши внутри сводной таблицы Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim rcPT As PivotTable 'проверяем, является ли ячейка, 'на которой дважды щелкнули мышью 'ячейкой внутри сводной таблицы On Error Resume Next Set rcPT = Target.PivotTable On Error GoTo 0 'если это ячейка сводной If Not rcPT Is Nothing Then 'вызываем процедуру фильтрации источника данных EditPivotSource Cancel = True End If End Sub '================================================================================ ' СОЗДАНИЕ И УДАЛЕНИЕ ПУНКТА МЕНЮ В СВОДНОЙ ' 'добавляем в меню сводных таблиц пункт "Edit Source", 'который будет отбирать данные непосредственно в источнике данных Private Sub Workbook_Open() Dim bt As CommandBarControl, indx As Long On Error Resume Next 'ищем пункт меню "Показать детали" Set bt = Application.CommandBars("PivotTable Context Menu").FindControl(ID:=462) 'если нашли - добавим после него новый пункт "Edit source" ' при нажатии которого будет вызываться наш код перехода к источнику 'если не нашли - ставим вторым пунктом If Not bt Is Nothing Then indx = bt.Index Else indx = 1 End If 'пробуем удалить пункт "Edit source", если он ранее был создан 'чтобы не было задвоения Application.CommandBars("PivotTable Context Menu").Controls("Edit source").Delete 'добавляем новый пункт With Application.CommandBars("PivotTable Context Menu").Controls.Add(before:=indx + 1) .Caption = "Edit source" .OnAction = "'" & ThisWorkbook.Name & "'!EditPivotSource" End With End Sub 'перед закрытием книги удаляем созданный нами пункт меню Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("PivotTable Context Menu").Controls("Edit source").Delete End Sub '================================================================================ |
Все, теперь останется только сохранить книгу в формате "Книга Excel с поддержкой макросов(.xlsm)" и открыть заново. Хотя это нужно лишь для того, чтобы создался новый пункт меню в сводной таблицы, весь остальной функционал будет работать и без перезапуска.
Надеюсь данный трюк будет полезен всем, кто работает со сводными.
Перейти к исходным данным сводной таблицы.xlsm (612,2 КиБ, 703 скачиваний)
Так же см.:
Показать все детали
Перейти к исходным данным
Связать сводные
Использование вычисляемых полей и объектов в сводных таблицах