Как отредактировать сводную таблицу в excel

Пользователи создают сводные таблицы для анализа, суммирования и представления большого объема данных. Такой инструмент Excel позволяет произвести фильтрацию и группировку информации, изобразить ее в различных разрезах (подготовить отчет).

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

Но два заголовка в этих таблицах идентичны. Поэтому мы можем объединить данные, а потом создать сводный отчет.

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.



2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.



3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

Открывается заготовка Сводного отчета со Списком полей, которые можно отобразить.

Покажем, к примеру, количество проданного товара.

Можно выводить для анализа разные параметры, перемещать поля. Но на этом работа со сводными таблицами в Excel не заканчивается: возможности инструмента многообразны.



Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

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

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

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

1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.



2. Параметры.



3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.



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

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

Иногда пользователю недостаточно данных, содержащихся в сводной таблице. Менять исходную информацию не имеет смысла. В таких ситуациях лучше добавить вычисляемое (пользовательское) поле.

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

Инструкция по добавлению пользовательского поля:

1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.



2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.



3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.



4. Жмем ОК. Появились Остатки.

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

Скачать пример работы

По такой же схеме можно группировать данные в сводной таблице по другим параметрам.

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Хитрости »

19 Июнь 2020              13367 просмотров

Как перейти к редактированию исходных данных прямо из сводной таблицы?

Несомненно, основная задача работы со сводными таблица — анализ данных. А раз мы что-то анализируем, значит может потребоваться и что-то изменить в случае нахождения каких-то расхождений. И самое печальное здесь то, что нельзя изменять значения непосредственно внутри сводной таблицы(OLAP не в счет :)). Зато мы можем посмотреть из каких строк исходной таблицы состоит конкретное значение.
Например, у нас есть таблица реализации следующего вида:

На основе её мы построили примерно такую сводную таблицу(как создать сводную можно посмотреть и прочитать в этой статье: Общие сведения о сводных таблицах):

В итогах у нас значения по прибыли, а красным выделены отрицательные значения, т.к. именно к таким нам следует присмотреться в первую очередь. Чтобы понять из каких строк исходной таблицы получилась сумма -1155 мы можем выделить эту ячейку внутри сводной таблицы -правая кнопка мыши —Показать детали(Show Details):

В итоге будет создан новый лист с таблицей, содержащей только те строки исходных данных, на основании которых сформировано выделенное нами значение:

Да, мы теперь можем целенаправленно и точечно посмотреть, изучить только нужные данные и принять решение. Но тут другая проблема: если нам надо что-то изменить, то это ни на что не повлияет. Т.к. показ деталей из сводной никак не связан уже ни с исходными данными, ни с самой сводной таблицей. Как же быть? Можно попробовать вернуться в лист с исходными данными и отфильтровать последовательно каждый столбец до нужных значений. Но это явно не самый быстрый и точный путь. Поэтому его даже не рассматриваем. Я хочу предложить путь быстрее и эффективнее. После того как отобразили детали — ничего с этим листом пока не делать. Переходим на лист с исходными данными -вкладка Данные(Data) -группа Сортировка и фильтр(Sort & Filter) —Дополнительно(Advanced). В появившейся форме указываем следующие данные:

Исходные диапазон: $A$1:$H$4777 (у меня эти ячейки на листе Data. Указываем обязательно с заголовками)
Диапазон условий: Таблица2[#Все] (это как раз наша таблица деталей, которую мы отобразили из сводной таблицы. Обращаю особое внимание на то, что должно быть именно Таблица2[#Все], т.е. с заголовками)
Обязательно оставляем отмеченным пункт Фильтровать список на месте. Нажимаем Ок.
В итоге у нас в исходной таблице отфильтруются ровно те строки, которые были отображены в деталях:

Краткое видео процесса:

И теперь мы спокойно можем их анализировать и при необходимости изменять.
Только следует помнить, что после любого изменения надо будет обновить сводную(правая кнопка мыши на любой ячейке сводной таблицы —Обновить(Refresh).

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

Но даже при всем этом: как-то это все долго и не очень удобно. Поэтому я решил пойти дальше и сделать все необходимое при помощи макросов(Visual Basic for Applications). Придется в них чуть-чуть вникнуть, но оно того стоит, т.к. для полного удобства мы сделаем вот что:

• по двойному клику на ячейке сводной таблицы автоматически отфильтруем данные в исходной таблице и перейдем в неё
• после изменений в исходной таблице и возврата в сводную — автоматически обновим эту сводную таблицу
• для большего удобства мы еще создадим в меню правого клика сводной таблицы свой собственный пункт меню «Edit Source», который будет делать то же самое, что и двойной клик
  

Т.е. можно сказать полностью заменим стандартный пункт «Показать детали».

Для этого создаем стандартный модуль (переходим в редактор VBA(Alt+F11) —Insert —Module) и вставляем в него код:

'---------------------------------------------------------------------------------------
' 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 KiB, 526 скачиваний)

Ну а если совсем лень делать что-то своими руками, то можно воспользоваться данной возможностью, уже встроенной в мою надстройку MulTex. Там же есть вариант отображения деталей всех выделенных ячеек, а не только одной, как это реализовано в самом Excel.

Так же см.:
Показать все детали
Перейти к исходным данным
Связать сводные
Использование вычисляемых полей и объектов в сводных таблицах

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика