Фильтр исходных данных в сводной таблице
У любого уважающего себя тренера всегда есть запас "вау-фишек" - простых, но эффектных приемов, эдаких killing-features для быстрого очарования сложной аудитории. В сводных таблицах одной из таких фишек, безусловно, является двойной щелчок левой кнопкой мыши по любому числу в области значений:
Если это сделать, то вас вынесет на новый лист, куда Excel выгрузит детализацию по данной ячейке - всю "подноготную", объясняющую как получилось данное значение, из чего оно сложилось:
Официально, эта процедура называется drill-down, неофициально ее обычно называют "провалиться".
Ключевой нюанс в том, что полученная в результате такого проваливания двойным щелчком таблица - это копия исходных данных, а не они сами. Полученная таблица абсолютно автономна и никак не связана ни с исходными данными, ни со сводной. Иногда это нам на руку - мы можем использовать ее для своих целей, менять ее и т.д.
Но порой возникает другое желание: а можно увидеть не копию исходных данных, а сами данные? То есть отфильтровать те самые строки в исходной таблице, которые участвуют в расчете данной ячейки? Их, например, можно было бы затем изменить, подкорректировав тем самым результат в сводной таблице.
Стандартными средствами такое невозможно, но для макросов пределы возможного в Excel существенно шире :)
Откройте редактор Visual Basic:
- В Excel 2003 и старше для этого нужно выбрать в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor)
- В новых версиях Excel 2007-2013 перейти на вкладку Разработчик (Developer) и нажать кнопку Visual Basic. Если такой вкладки у вас не видно, то включите ее в настройках Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon)
В окне редактора вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот этих двух макросов:
Sub FilterPivot()
Dim pt As PivotTable
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Set pt = ActiveCell.PivotTable
Set rSource = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
rSource.EntireRow.Hidden = False
nCols = rSource.Columns.Count
Selection.ShowDetail = True
Set rDrill = ActiveSheet.UsedRange
Set shDrill = ActiveSheet
DrillLastRow = shDrill.Range("A1").End(xlDown).Row
For i = nCols To 1 Step -1
formulatxt = formulatxt & "RC[-" & i & "]&"
Next i
formulatxt = Left(formulatxt, Len(formulatxt) - 1)
shDrill.Cells(2, nCols + 1).Resize(DrillLastRow - 1, 1).FormulaR1C1 = "=" & formulatxt
For j = 2 To rSource.Rows.Count
contxt = ""
For i = 1 To nCols
contxt = contxt & rSource.Cells(j, i).Value
Next i
If WorksheetFunction.CountIf(shDrill.Cells(2, nCols + 1).Resize(DrillLastRow - 1, 1), contxt) = 0 Then
rSource.Cells(j, 1).EntireRow.Hidden = True
End If
Next j
shDrill.Delete
rSource.Parent.Activate
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Sub ShowAllData()
ActiveSheet.Rows.Hidden = False
End Sub
Теперь, если выделить одну любую ячейку с данными в сводной таблице и запустить наш первый макрос FilterPivot с помощью сочетания клавиш Alt+F8 или через меню Сервис - Макрос - Макросы (Tools - Macro - Macros), то мы перейдем на лист с исходными данными для сводной, где автоматически будут применены фильтры, отбирающие только те строки, которые участвовали в расчете текущей ячейки:
Теперь их можно, например, изменить, чтобы добиться в отчете сводной таблицы нужного результата. Только не забудьте обновить сводную после внесения изменений: правой кнопкой мыши - Обновить (Refresh).
Второй макрос ShowAllData нужен, чтобы вернуть прежний вид исходной таблицы - он делает все строки на текущем листе видимыми. Для пущего удобства можно повесить эти два макроса на удобные вам сочетания клавиш, используя кнопку Параметры (Options) в окне Макросы, которое отображается по Alt+F8.
Ссылки по теме
- Что такое сводные таблицы, как их строить
- Настройка вычислений в сводных таблицах
- Новые возможности сводных таблиц в Microsoft Excel 2013
"Run-time error 1004:
Невозможно получить свойство CountIf класса WorksheetFunction"
В режиме отладки желтой заливкой выделяется строка 27 кода
If WorksheetFunction.CountIf(shDrill.Cells(2, nCols + 1).Resize(DrillLastRow - 1, 1), contxt) = 0 Then
Таблица с исходными данными у меня большая (более 400 тыс.строк и 76 столбцов)
А макрос "ShowAllData" кричит "недостаточно ресурсов" (16 Гб оперативки, проц - i7 два ядра четыре потока)
For j = 2 To rSource.Rows.Count
на
For j = 1 To rSource.Rows.Count
"Run-time error 1004:
Невозможно получить свойство CountIf класса WorksheetFunction". В чем причина?
По двойному клику на сводной, сразу фильтрует исходную таблицу.
Работает с длинными текстовыми данными.
Обновляется автоматом при возврате на лист со сводной.
Скорость можно еще увеличить при работе с громадными таблицами:
1. не использовать Union
2. Сравнивать количество найденных строк и если все найдены, то все оставшиеся скрывать.
3. Так как количество отобранных строк, в большинстве случаев меньше скрываемых (Можно проверить программно), то использовать сначала скрытие всех строк, а затем отображение найденных.(При использовании Union)
---------------------
Sub FilterPivot() Dim pt As PivotTable Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Application.ScreenUpdating = False On Error GoTo ext Set pt = ActiveCell.PivotTable Set rSource = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)) rSource.EntireRow.Hidden = False ncols = rSource.Columns.Count Selection.ShowDetail = True Set rdrill = ActiveSheet.UsedRange Set shDrill = ActiveSheet DrillLastRow = shDrill.Range("A1".End(xlDown).Row a = rdrill.Value b = rSource.Value For j = 1 To rSource.Rows.Count Nbl = True: bl = False For i = 1 To ncols For ii = 2 To UBound(a) If b(j, i) = a(ii, i) Then bl = True: Exit For Next ii If Not bl Then Nbl = True: bl = False: Exit For Else bl = False: Nbl = False Next i If Nbl Then If Not IsEmpty(r) Then Set r = Union(r, rSource.Rows(j)) Else Set r = rSource.Rows(j) End If End If Next j If Not IsEmpty(r) Then r.EntireRow.Hidden = True shDrill.Delete rSource.Parent.Activate ext: If Err.Number <> 0 Then MsgBox "Выделите диапазон редактирования" Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Sub ShowAllData() ActiveSheet.Rows.Hidden = False End SubНиколай, в твоем коде это тоже не помешало бы добавить
К сожалению, в интернете не удалось найти ясного описания того, как это можно сделать, поэтому и задаю данный вопрос.
Коллеги, как сделать чтобы фильтр исходных данных шел по ячейкам первого столбца сводной таблицы, и еще вопрос: можно как-то выделить ячейки в сводной таблице по которым можно производить фильтр исходной, например как в гиперссылках.
Заранее спасибо.
"Run-time error 1004:" не забываем что запускать его нужно из вкладки "Лист1"
там где соответственно и есть сводная, а не данные.