Изменение исходных данных сводной таблицы
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше
После создания сводной таблицы можно изменить диапазон исходных данных. Например, расширить его и включить дополнительные строки данных. Однако если исходные данные существенно изменены, например содержат больше или меньше столбцов, рекомендуется создать новую сводную таблицу.
Вы можете изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек или другой внешний источник данных.
-
Щелкните Отчет сводной таблицы.
-
На вкладке « Анализ» в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».
Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
-
Выполните одно из следующих действий:
чтобы использовать другое подключение
-
Щелкните » Использовать внешний источник данных«, а затем выберите «Выбрать подключение».
Отобразится диалоговое окно «Существующие подключения».
-
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
-
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть». Что делать, если подключение отсутствует в списке?
Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу в качестве нового подключения к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.
Дополнительные сведения см. в статье «Управление подключениями к данным в книге».
-
Нажмите кнопку ОК.
Изменение источника данных сводной таблицы на другую таблицу Excel или диапазон ячеек
-
Щелкните «Выбрать таблицу или диапазон«, а затем введите первую ячейку в текстовом поле «Таблица/диапазон» и нажмите кнопку «ОК«.
-
Если подключение отсутствует в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных сведений» и найдите источник данных, к которому нужно подключиться, в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
-
Выберите нужное подключение и нажмите кнопку Открыть.
-
Выберите вариант Только создать подключение.
-
Щелкните пункт Свойства и выберите вкладку Определение.
-
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
-
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
-
Щелкните Отчет сводной таблицы.
-
На вкладке « Параметры » в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».
Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
-
Выполните одно из указанных ниже действий.
-
Чтобы использовать другую таблицу или диапазон ячеек Excel, щелкните «Выбрать таблицу или диапазон «, а затем введите первую ячейку в текстовом поле «Таблица / диапазон».
Кроме того, нажмите кнопку «Свернуть диалоговое окно
чтобы временно скрыть диалоговое окно, выделите начальную ячейку на листе, а затем нажмите кнопку «Развернуть диалоговое окно»
.
-
-
Чтобы использовать другое подключение, выберите «Использовать внешний источник данных», а затем нажмите кнопку «Выбрать подключение».
Отобразится диалоговое окно «Существующие подключения».
-
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
-
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть».
Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу в качестве нового подключения к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.
Дополнительные сведения см. в статье «Управление подключениями к данным в книге».
-
Нажмите кнопку ОК.
Если подключение отсутствует в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных сведений» и найдите источник данных, к которому нужно подключиться, в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
-
Выберите нужное подключение и нажмите кнопку Открыть.
-
Выберите вариант Только создать подключение.
-
Щелкните пункт Свойства и выберите вкладку Определение.
-
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
-
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Дополнительные сведения о поддерживаемых источниках данных см. в разделе «Импорт и формирование данных в Excel для Mac (Power Query).
-
Щелкните Отчет сводной таблицы.
-
На вкладке « Анализ» в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».
Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
-
Выполните одно из указанных ниже действий.
-
Чтобы изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек, щелкните «Выбрать таблицу или диапазон«, а затем введите первую ячейку в текстовом поле «Таблица/диапазон» и нажмите кнопку «ОК».
-
Чтобы использовать другое подключение, выполните следующие действия.
-
Щелкните » Использовать внешний источник данных«, а затем выберите «Выбрать подключение».
Отобразится диалоговое окно «Существующие подключения».
-
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
-
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть». Что делать, если подключение отсутствует в списке?
Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу в качестве нового подключения к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.
Дополнительные сведения см. в статье «Управление подключениями к данным в книге».
-
Нажмите кнопку ОК.
-
-
Если подключение отсутствует в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных сведений» и найдите источник данных, к которому нужно подключиться, в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
-
Выберите нужное подключение и нажмите кнопку Открыть.
-
Выберите вариант Только создать подключение.
-
Щелкните пункт Свойства и выберите вкладку Определение.
-
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
-
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Создание сводной таблицы с внешним источником данных
Создание сводной таблицы, подключенной к наборам данных Power BI
Управление подключениями к данным в книге
Нужна дополнительная помощь?
Download Article
Download Article
After you create a pivot table, you might need to edit it later. This wikiHow will show you how to edit a pivot table in Excel on your computer by adding or changing the source data. After you make any changes to the data for your Pivot Table, you will need to refresh it to see any changes.
Steps
-
1
Open your project in Excel. To do this, double-click the Excel document that contains your pivot table in Finder (Macs) or File Explorer (Windows). Alternatively, if you already have Excel open, click File > Open and select the file that has your pivot table.
-
2
Go to the spreadsheet page that contains the data for the pivot table. Click the tab that contains your data (e.g., Sheet 2) at the bottom of the Excel window.
Advertisement
-
3
Add or change your data. Enter the data that you want to add to your pivot table directly next to or below the current data.
- For example, if you have data in cells A1 through E10, you would add another column in the F column or another row in the 11 row.
- If you simply want to change the data in your pivot table, edit the data here. It won’t be reflected in the pivot table until you refresh the data, though.
-
4
Go back to the pivot table tab. Click the tab on which your pivot table is listed.
-
5
Select your pivot table. Click the pivot table to select it.
-
6
Click the Analyze tab. It’s in the middle of the editing ribbon that’s at the top of the Excel window. Doing so will open a toolbar just below the editing ribbon.
- On a Mac, click the PivotTable Analyze tab here instead.
-
7
Click Change Data Source. This option is in the «Data» section of the Analyze toolbar. A drop-down menu will appear.
-
8
Click Change Data Source…. It’s in the drop-down menu. Doing so opens a window.
-
9
Select your data. Click and drag from the top-left cell in your data group down to the bottom-left cell in the group. This will include the column(s) or row(s) that you added.
-
10
Click OK. It’s at the bottom of the window.
-
11
Click Refresh. It’s in the «Data» section of the toolbar.
- If you added a new column to your pivot table, check its box on the right side of the Excel window to display it.[1]
- If you added a new column to your pivot table, check its box on the right side of the Excel window to display it.[1]
Advertisement
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Thanks for submitting a tip for review!
References
About This Article
Article SummaryX
1. Open your project in Excel.
2. Go to the spreadsheet that contains the data for the pivot table
3. Add or change your data.
4. Go back to the pivot table tab.
5. Select your pivot table.
6. Click Analyze tab (Windows) or PivotTable Analyze (Mac).
7. Click Change Data Source.
8. Click Change Data Source.
9. Select your data.
10. Click Ok.
11. Click Refresh.
Did this summary help you?
Thanks to all authors for creating a page that has been read 50,578 times.
Is this article up to date?
Хитрости »
19 Июнь 2020 13363 просмотров
Как перейти к редактированию исходных данных прямо из сводной таблицы?
Несомненно, основная задача работы со сводными таблица — анализ данных. А раз мы что-то анализируем, значит может потребоваться и что-то изменить в случае нахождения каких-то расхождений. И самое печальное здесь то, что нельзя изменять значения непосредственно внутри сводной таблицы(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
ссылки
статистика
Когда в сводной таблице есть расчетные поля, может возникнуть необходимость внести исправления в сам расчет, либо удалить данное поле.
Для того, чтобы это сделать, необходимо воспользоваться модулем настройки вычисляемых полей в EXCEL.
В примере вычисляемым поле является поле с валовым доходом с НДС.
Предположим, нам необходимо изменить расчет в поле и перевести в валовый доход БЕЗ НДС.
Для упрощения, определим ставку НДС=18% для всех групп товаров в таблице.
Для этого:
1. Кликнем на любом элементе сводной таблицы и в группе меню «Параметры» на ленте.
2. Кликнем на «Формулы» (или «Поля, элементы, наборы») и выберем «Вычисляемое поле».
3. В открывшемся окне, необходимо нажать на выпадающий список справа от поля «Имя». По умолчанию, имена вычисляемых полей сводной таблицы в данном модуле не отображаются.
4. Выберем поле, по которому необходимо изменить расчет и откорректируем формулу в поле «Формула».
В примере мы переведем Валовый доход в значения без НДС.
Вместо
=’Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’
укажем
=(‘Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’)/1,18
5. Переименуем поле и нажмем кнопку «Изменить». Закроем модуль.
Итогом будет пересчитанный, уже по новой формуле, столбец.
Для удаления поля, необходимо выбрать имя поля в модуле и нажать «Удалить».
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Сводная таблица – мощный инструмент Microsoft Excel. С ее помощью пользователь анализирует большие по объему диапазоны, подводит итоги всего в несколько кликов, выводит на экран только нужную в данный момент информацию.
Фильтр в сводной таблице Excel
В сводную таблицу можно преобразовать практически любой диапазон данных: итоги финансовых операций, сведения о поставщиках и покупателях, каталог домашней библиотеки и т.д.
Для примера возьмем следующую таблицу:
Создадим сводную таблицу: «Вставка» — «Сводная таблица». Поместим ее на новый лист.
Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.
Напомним, как выглядит диалоговое окно сводного отчета:
Перетаскивая заголовки, мы задаем программе инструкции для формирования сводного отчета. Если случайно допустим ошибку, из нижней области можно удалить заголовок, заменить его другим.
По данным, которые помещены в поле «Значения», подводятся итоги. В автоматическом режиме – сумма. Но можно задать «среднее», «максимум» и т.д. Если сделать это нужно для значений всего поля, то щелкаем по названию столбца и меняем способ представления итогов:
Например, среднее количество заказов по каждому поставщику:
Итоги можно менять не во всем столбце, а только в отдельной ячейке. Тогда щелкаем правой кнопкой мыши именно по этой ячейке.
Установим фильтр в сводном отчете:
- В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
- Перетащим это поле в область «Фильтр отчета».
- Таблица стала трехмерной – признак «Склад» оказался вверху.
Теперь мы можем отфильтровать значения в отчете по номеру склада. Нажимаем на стрелочку в правом углу ячейки и выбираем интересующие нас позиции:
Например, «1»:
В отчете отображается информация только по первому складу. Вверху видим значение и значок фильтра.
Отфильтровать отчет можно также по значениям в первом столбце.
Сортировка в сводной таблице Excel
Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».
Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:
После нажатия ОК сводная таблица приобретает следующий вид:
Отсортируем данные в отчете по значению столбца «Стоимость». Кликнем правой кнопкой мыши по любой ячейке или названию столбца. Выбираем «Сортировка» и способ сортировки.
Значения в сводном отчете поменяются в соответствии с отсортированными данными:
Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:
Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».
Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):
Формулы в сводных таблицах Excel
Сначала составим сводный отчет, где итоги будут представлены не только суммой. Начнем работу с нуля, с пустой таблицы. За одно узнаем как в сводной таблице добавить столбец.
- Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» — в сводную таблицу добавятся три одинаковых столбца.
- Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
- Поменяем местами значения столбцов и значения строк. «Поставщик» — в названия столбцов. «Σ значения» — в названия строк.
Сводный отчет стал более удобным для восприятия:
Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».
Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.
Получаем добавленный дополнительный столбец с результатом вычислений по формуле.
Скачать пример управления сводными таблицами
Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.