Download Article
Download Article
Microsoft’s Excel spreadsheet application is designed to allow users to organize and interpret their data using advanced features such as pivot tables, formulas and macros. Occasionally, users of these spreadsheets may need to edit or alter the data in order to answer questions about the results. Changing the source of a pivot table can be confusing, because the source material is typically on a separate sheet, but it is possible to change the source data without losing the formatting of your table.
-
1
Launch Microsoft Excel.
- You can use either the desktop icon, the Programs listed in the Start menu or the Quick Launch taskbar, depending on your desktop arrangement.
-
2
Open the file containing the pivot table and data.
Advertisement
-
3
Make any necessary adjustments to the source data.
- You may need to insert or delete columns and rows.
- Ensure that all inserted columns have a descriptive heading.
-
4
Select the workbook sheet that contains the pivot table by clicking the appropriate tab.
-
5
Click inside the pivot table to force the pivot table tools menu to launch.
- In Excel 2007 and 2010, you will see the Pivot Table Tools menu appear, highlighted in red, above the Options and Design tabs in the ribbon.
- In Excel 2003, choose «Pivot Table and Pivot Chart Reports» from the Data menu.
-
6
Edit the source data range for your pivot table.
- In Excel 2007 and 2010, choose «Change Data Source» from the Data group of options.
- In Excel 2003, launch the Wizard utility by right-clicking inside the pivot table and choosing «Wizard» from the pop-up menu. Click the «Next» button until you see the screen with the source data range.
- In all versions of Excel, with the source data range highlighted, click and drag to highlight the new range for your data.
- You can also the range description to include more columns and rows.
-
7
Refresh the pivot table by clicking the «Refresh» button.
- This button may have a red exclamation point icon, a green «recycle» icon or simply the word «Refresh,» depending upon your version and degree of personalization of Excel.
Advertisement
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
-
Be sure to refresh your pivot table each time you make changes to the source data. Otherwise, the changes will not be reflected in your pivot table.
-
You cannot make changes to data by manipulating the pivot table. All changes must be made to the source data and then refreshed in the pivot table.
-
The process for changing the source data in a pivot chart is the same. Remember to alter the source and refresh your chart if you have also created a pivot chart from your source data.
Thanks for submitting a tip for review!
Advertisement
About This Article
Thanks to all authors for creating a page that has been read 161,928 times.
Is this article up to date?
Download Article
Download Article
Microsoft’s Excel spreadsheet application is designed to allow users to organize and interpret their data using advanced features such as pivot tables, formulas and macros. Occasionally, users of these spreadsheets may need to edit or alter the data in order to answer questions about the results. Changing the source of a pivot table can be confusing, because the source material is typically on a separate sheet, but it is possible to change the source data without losing the formatting of your table.
-
1
Launch Microsoft Excel.
- You can use either the desktop icon, the Programs listed in the Start menu or the Quick Launch taskbar, depending on your desktop arrangement.
-
2
Open the file containing the pivot table and data.
Advertisement
-
3
Make any necessary adjustments to the source data.
- You may need to insert or delete columns and rows.
- Ensure that all inserted columns have a descriptive heading.
-
4
Select the workbook sheet that contains the pivot table by clicking the appropriate tab.
-
5
Click inside the pivot table to force the pivot table tools menu to launch.
- In Excel 2007 and 2010, you will see the Pivot Table Tools menu appear, highlighted in red, above the Options and Design tabs in the ribbon.
- In Excel 2003, choose «Pivot Table and Pivot Chart Reports» from the Data menu.
-
6
Edit the source data range for your pivot table.
- In Excel 2007 and 2010, choose «Change Data Source» from the Data group of options.
- In Excel 2003, launch the Wizard utility by right-clicking inside the pivot table and choosing «Wizard» from the pop-up menu. Click the «Next» button until you see the screen with the source data range.
- In all versions of Excel, with the source data range highlighted, click and drag to highlight the new range for your data.
- You can also the range description to include more columns and rows.
-
7
Refresh the pivot table by clicking the «Refresh» button.
- This button may have a red exclamation point icon, a green «recycle» icon or simply the word «Refresh,» depending upon your version and degree of personalization of Excel.
Advertisement
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
-
Be sure to refresh your pivot table each time you make changes to the source data. Otherwise, the changes will not be reflected in your pivot table.
-
You cannot make changes to data by manipulating the pivot table. All changes must be made to the source data and then refreshed in the pivot table.
-
The process for changing the source data in a pivot chart is the same. Remember to alter the source and refresh your chart if you have also created a pivot chart from your source data.
Thanks for submitting a tip for review!
Advertisement
About This Article
Thanks to all authors for creating a page that has been read 161,928 times.
Is this article up to date?
Изменить источник данных для сводной таблицы
Сводные таблицы — это мощные инструменты, которые помогают нам много времени при анализе данных. Перед созданием сводной таблицы нам нужно выбрать диапазон данных, который мы охватываем при создании отчета сводной таблицы. Любое удаление данных повлияет на нажатие кнопки обновления, и любое изменение значений также повлияет на кнопку обновления.
Однако одна из жалоб пользователей сводной таблицы (не экспертов) заключается в том, что любые дополнительные данные за пределами выбранного диапазона данных не будут влиять на отчет сводной таблицы; для меня это тоже была одна из проблем, потому что каждый раз, когда появляются дополнительные данные, нам нужно время от времени изменять диапазон данных. Однако в этой статье мы покажем вам, как изменить диапазон данных для сводной таблицы, а также покажем вам автоматический выбор диапазона данных в конце этой статьи.
Найдите источник данных сводной таблицы
Одна из вещей, когда мы получаем книгу Excel, кто-то, мы можем сводную таблицу, и мы не знаем точно, где находится источник данных. Например, посмотрите на приведенную ниже сводную таблицу.
Чтобы идентифицировать источник данных, поместите курсор внутри сводной таблицы в любую из ячеек, и на ленте откроются еще две вкладки: «Анализировать» и «Дизайн».
Перейдите в «Анализировать» и нажмите «Изменить источник данных».
Это откроет окно ниже, а также перенесет вас на рабочий лист данных с выбором диапазона данных.
Как видите, источник данных как «Технические данные»! $ A $ 1: $ D $ 11, в этом «Листе данных» — имя рабочего листа, а «$ A $ 1: $ D $ 11» — адрес ячейки, таким образом мы можем найти диапазон источника данных в Excel.
Вы можете скачать этот шаблон Excel для изменения источника данных сводной таблицы здесь — Шаблон Excel для изменения источника данных сводной таблицы
Как изменить источник данных в сводной таблице Excel?
Ниже приведены несколько примеров изменения источника данных.
# 1 — Изменить источник данных сводной таблицы
На данный момент сводная таблица создана для диапазона ячеек от A1 до D11, поэтому все, что происходит в этом диапазоне, будет отражено в сводной таблице с помощью кнопки «Обновить». Например, теперь мы изменим номер продажи для категории «Мебель».
Перейдя на лист сводной таблицы, щелкните правой кнопкой мыши и выберите параметр «Обновить», чтобы обновить отчет сводной таблицы.
Это изменит номер продаж категории «Мебель» с 69525 на 40432.
Это хорошо!!!
Однако здесь возникает проблема: диапазон данных, выбранный для сводной таблицы, равен A1: D11; в настоящее время, мы добавим еще 3 строки данных в строках с 12 по 14.
Теперь «Обновить» сводной таблицы не будет, потому что диапазон ячеек, указанных в сводной таблице, ограничен A1: D11, поэтому нам нужно изменить диапазон дат вручную.
Перейдите в «Анализировать» и нажмите «Изменить источник данных».
Вы перейдете к актуальной таблице данных с выделением уже выбранного диапазона данных из A1: D11.
Теперь удалите существующий диапазон данных и выберите новый диапазон данных.
Нажмите «ОК», и сводная таблица покажет результат обновленного диапазона данных.
Выглядит нормально, но представьте, что сценарий диапазона данных увеличивается каждый день, и если у вас есть от 10 до 20 сводных таблиц, мы не можем перейти к каждой сводной таблице и изменить диапазон источника данных, поэтому у нас есть метод для этого.
# 2 — Источник автоматического диапазона данных для сводной таблицы
С обычным диапазоном данных мы не можем заставить сводную таблицу выбирать какой-либо дополнительный источник данных, поэтому для этого нам нужно сделать диапазон данных для преобразования в Таблицы Excel.
Поместите курсор в ячейку данных и нажмите Ctrl + T , чтобы открыть окно «Создать таблицу».
Убедитесь, что в поле «Моя таблица есть заголовки» установлен флажок, и нажмите «ОК», чтобы преобразовать данные в формат «Таблица Excel».
Обычно мы выбираем данные и затем вставляем сводную таблицу, но с таблицей Excel нам нужно просто выбрать хотя бы одну ячейку в диапазоне таблиц Excel и вставить сводную таблицу.
Теперь добавьте три строки данных чуть ниже существующей таблицы данных.
Теперь вернитесь к сводной таблице и обновите отчет, чтобы обновить изменения.
В этом прелесть использования таблиц Excel в качестве источника данных для сводной таблицы, так что это сделает выбор диапазона данных автоматическим, и нам просто нужно обновить сводную таблицу.
То, что нужно запомнить
- Таблицы Excel в качестве сводной таблицы позволяют автоматически выбирать диапазон данных, а с помощью сочетания клавиш Excel ALT + A + R + A, мы можем обновить все сводные таблицы одним нажатием клавиши.
- Чтобы преобразовать данные в таблицу Excel, нажмите сочетание клавиш Ctrl + T.
УЗНАТЬ БОЛЬШЕ >>
Post Views: 1 670
Загрузить PDF
Загрузить PDF
Приложение для работы с электронными таблицами Microsoft Excel позволяет пользователям организовывать и интерпретировать их данные, используя расширенные функции, такие как сводные таблицы, формулы и макросы. Иногда пользователям этих таблиц может потребоваться отредактировать или изменить данные для того, чтобы получить результаты. Изменение источника сводной таблицы может быть запутанным делом, потому что исходный материал обычно находится на отдельном листе, но существует способ изменить источник данных без потери форматирования таблицы.
Шаги
-
1
Запустите Microsoft Excel.
- Вы можете использовать иконку на рабочем столе, в Программах, в меню Пуск или на Панели быстрого запуска.
-
2
Откройте файл, содержащий сводную таблицу и данные.
-
3
Внесите необходимые изменения в исходные данные.
- Может потребоваться вставить или удалить строки и столбцы.
- Убедитесь, что все вставленные столбцы имеют описательный заголовок.
-
4
Выберите лист книги, содержащий сводную таблицу, нажав на соответствующую вкладку.
-
5
Щелкните внутри сводной таблицы чтобы открыть меню «Инструменты сводной таблицы».
- В Excel 2007 и 2010, вы увидите инструменты сводной таблицы в появившемся меню, выделенном красным цветом, над вкладками Параметры и Дизайн на ленте.
- В Excel 2003, выберите «Сводная таблица и отчеты сводной диаграммы » из меню Данные.
-
6
Измените исходный диапазон данных для сводной таблицы.
- В Excel 2007 и 2010, выберите » Изменение источника данных» из опции группы данных.
- В Excel 2003, Запустите мастер, щелкните внутри сводной таблицы правой кнопкой мыши и выберите «Мастер» во всплывающем меню. Нажмите на кнопку «Далее», пока не увидите экран с источником диапазона данных.
- В любой версии Microsoft Excel, выделив источник данных нажмите и перетащите рамку, чтобы выделить новый диапазон для ваших данных.
- Вы можете также включить больше строк и столбцов в диапазон.
-
7
Обновите сводную таблицу, нажав кнопку «Обновить».
- Эта кнопка может иметь красный треугольник с восклицательным знаком, картинку в виде зеленых «стрелок переработки» или просто слово «Обновить», в зависимости от версии и степени персонализации Excel.
Реклама
Советы
- Нельзя внести изменения в данные, манипулируя сводной таблицей. Все изменения необходимо внести в источник данных и затем обновить сводную таблицу.
- Не забудьте обновлять вашу сводную таблицу каждый раз, когда вы вносите изменения в источник данных. В противном случае изменения не будут отражены в сводной таблице.
- Процесс изменения источника данных в сводной диаграмме такой же. Не забывайте изменять источник и обновлять диаграмму, если вы также создали сводную диаграмму используя источник данных.
Реклама
Об этой статье
Эту страницу просматривали 36 277 раз.
Была ли эта статья полезной?
|
Добрый день! Есть книга с несколькими сводными таблицами, источником данных для которых служит таблица на отдельном листе этой же книги. Необходимо добавить новые данные, но из за ограничения количества строк на листе в Excel решил воспользоваться Power Query. Создал запрос, настроил формат данных (точно такой же как на существующем листе), но не могу изменить источник данных сводной таблицы на созданный запрос в PQ. Я так понимаю нужно выбрать «использовать внешний источник данных», но пункт не активен. Подскажите пожалуйста как решить проблему? Прикрепленные файлы
Изменено: Murderface_ — 09.08.2018 12:41:28 |
|
|
ADimov  Пользователь Сообщений: 132 |
Добрый день! Вставка->сводная таблица->использовать внешний источник данных->выбрать подключение->нужное подключение->открыть |
|
ADimov, получается все сводные нужно пересоздавать заново? Просто поменять источник нельзя? |
|
|
Андрей VG  Пользователь Сообщений: 11878 Excel 2016, 365 |
#4 09.08.2018 14:14:22
Увы, нет. Только в пределах одного типа источника данных возможны изменения. Неявно — типы PivotCache различаются. Изменено: Андрей VG — 09.08.2018 14:15:42 |
||
|
Stranded  Пользователь Сообщений: 33 |
Можно попробовать перейти на PowerPivot, там можно и миллионами строк оперировать и обновлять источник данных. |
|
Спасибо всем, кто откликнулся! |
|
|
Андрей VG Пользователь Сообщений: 11878 Excel 2016, 365 |
#7 09.08.2018 14:47:59
То есть, неявно вы утверждаете, что обычной сводной при внешнем источнике данных нельзя оперировать миллионами строк и обновлять сводную? |
||
|
Stranded Пользователь Сообщений: 33 |
Если стоит вопрос в хранении данных в Эксель, то насколько я знаю модель данных PowerPivot лучше справится с задачей с хранением миллионов строк нежели просто хранение данных на листе Эксель. А если именно внешний источник,то конечно пожалуйста. Но поправьте меня если я неправ)))) |
|
Андрей VG Пользователь Сообщений: 11878 Excel 2016, 365 |
#9 09.08.2018 15:27:28
Вы не правы в построении фразы. И Power Pivot и обычная сводная работают с миллионами строк внешних по подключению данных и позволяют выполнять обновление. Тогда, стоит ли подчёркивать для Power Pivot именно эти свойства? Это как в разговоре про сахар и шоколад подчёркивать для шоколада свойство, что он сладкий Изменено: Андрей VG — 09.08.2018 15:28:36 |
||
|
Stranded Пользователь Сообщений: 33 |
Спасибо за пояснение.Учту))) |
|
ZVI  Пользователь Сообщений: 4328 |
#11 10.08.2018 01:52:51
Добрый вечер, Андрей. Шоколад же бывает не только сладким, но и горьким, Изменено: ZVI — 10.08.2018 01:53:27 |
||
|
Андрей VG Пользователь Сообщений: 11878 Excel 2016, 365 |
#12 10.08.2018 07:31:11
Спасибо. Но это уже особенности реализации. Изменено: Андрей VG — 10.08.2018 07:32:50 |
||
Вы узнали, как суммировать данные с помощью сводной таблицы. Данные, на которых основана сводная таблица, могут обновляться либо периодически, либо при возникновении события. Кроме того, вам также может потребоваться изменить макет сводной таблицы для разных отчетов.
В этой главе вы узнаете о различных способах обновления макета и / или обновления данных в сводной таблице.
Обновление макета сводной таблицы
Вы можете решить, будет ли обновляться сводная таблица всякий раз, когда вы вносите изменения в макет, или она должна обновляться с помощью отдельного триггера.
Как вы узнали ранее, в области задач Поля сводных таблиц в нижней части вы найдете флажок для отложенного обновления макета. По умолчанию этот флажок снят, что означает, что макет сводной таблицы обновляется, как только вы вносите изменения в области сводной таблицы.
Установите флажок « Отложить обновление макета» .
Кнопка UPDATE рядом с ней будет включена. Если вы внесете какие-либо изменения в области сводной таблицы, эти изменения будут отражены только после нажатия кнопки ОБНОВЛЕНИЕ.
Обновление данных сводной таблицы
Когда данные сводной таблицы изменяются в ее источнике, то же самое можно отразить в сводной таблице, обновив ее.
- Нажмите на сводную таблицу.
- Нажмите вкладку ANALYZE на ленте.
- Нажмите Обновить в группе данных.
Существуют различные варианты обновления данных в раскрывающемся списке.
-
Обновить – Получить последние данные из источника, подключенного к активной ячейке.
-
Обновить все – для получения последних данных путем обновления всех источников в рабочей книге.
-
Свойства соединения – установка свойств обновления для соединений книги.
Обновить – Получить последние данные из источника, подключенного к активной ячейке.
Обновить все – для получения последних данных путем обновления всех источников в рабочей книге.
Свойства соединения – установка свойств обновления для соединений книги.
Изменение исходных данных сводной таблицы
Вы можете изменить диапазон исходных данных сводной таблицы. Например, вы можете расширить исходные данные, включив в них большее количество строк данных.
Однако, если исходные данные были существенно изменены, например, имеется больше или меньше столбцов, рассмотрите возможность создания новой сводной таблицы.
-
Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .
-
Нажмите на вкладку АНАЛИЗ.
-
Нажмите Изменить источник данных в группе данных.
Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .
Нажмите на вкладку АНАЛИЗ.
Нажмите Изменить источник данных в группе данных.
Выберите Изменить источник данных в раскрывающемся списке.
Откроется диалоговое окно «Изменить источник данных сводной таблицы», и текущий источник данных будет выделен.
Выберите таблицу или диапазон, который хотите включить в поле «Таблица / диапазон» в разделе «Выбор таблицы или диапазона». Нажмите ОК.
Источник данных для сводной таблицы будет изменен на выбранную таблицу / диапазон данных.
Переход на внешний источник данных
Если вы хотите изменить внешний источник данных для сводной таблицы, лучше всего создать новую сводную таблицу. Однако, если расположение внешнего источника данных изменяется, например, имя базы данных SQL Server такое же, но оно было перемещено на другой сервер, или ваша база данных Access была перемещена в другой сетевой ресурс, вы можете изменить ваше текущее подключение к данным, чтобы отражать то же самое.
-
Нажмите на сводную таблицу.
-
Нажмите вкладку ANALYZE на ленте.
-
Нажмите Изменить источник данных в группе данных. Откроется диалоговое окно « Изменить источник данных сводной таблицы ».
-
Нажмите кнопку « Выбрать соединение» .
Нажмите на сводную таблицу.
Нажмите вкладку ANALYZE на ленте.
Нажмите Изменить источник данных в группе данных. Откроется диалоговое окно « Изменить источник данных сводной таблицы ».
Нажмите кнопку « Выбрать соединение» .
Откроется диалоговое окно « Существующие подключения ».
-
Выберите Все соединения в поле Показать. Все Соединения в вашей Рабочей книге будут отображены.
-
Нажмите кнопку Обзор для более .
Выберите Все соединения в поле Показать. Все Соединения в вашей Рабочей книге будут отображены.
Нажмите кнопку Обзор для более .
Откроется окно выбора источника данных .
- Нажмите на кнопку «Новый источник».
- Пройдите шаги мастера подключения к данным.
Если ваш источник данных находится в другой книге Excel, выполните следующие действия:
- Нажмите на поле Имя файла.
- Выберите имя файла книги.
Удаление сводной таблицы
Вы можете удалить сводную таблицу следующим образом:
- Нажмите на сводную таблицу.
- Нажмите вкладку ANALYZE на ленте.
- Нажмите «Выбрать» в группе «Действия».
В раскрывающемся списке выберите « Вся сводная таблица». Вся сводная таблица будет выбрана.
Нажмите клавишу удаления. Сводная таблица будет удалена.
Если сводная таблица находится на отдельной рабочей таблице, вы также можете удалить сводную таблицу, удалив всю рабочую таблицу.
Щелкните правой кнопкой мыши вкладку листа и выберите « Удалить» в раскрывающемся списке.
Весь рабочий лист вместе со сводной таблицей будет удален.
Сводные таблицы используются как основной инструмент отчетности, без их понимания нельзя сказать, что вы знаете Excel. В этой статье узнаем, как построить сводную таблицу в Excel.
- Что такое сводная таблица
- Подготавливаем данные для создания сводной таблицы
- Как создать сводную таблицу из обычной таблицы
- Где найти панель редактирования сводной таблицы
- Как изменить структуру сводной таблицы
- Как удалить поле сводной таблицы
- Для чего нужно поле Фильтр в сводной таблице
- Как обновить сводную таблицу
- Как изменить внешний вид сводной таблицы
- Значение (пусто) в сводной таблице — как убрать
- Где изменить источник сводной таблицы
- Как скопировать сводную таблицу
- Удаляем сводную таблицу
Что такое сводная таблица
Сводная таблица в Excel (или pivit table, или “пивотная таблица”) — это особым образом организованная и структурированная таблица, которая выводит данные в нужном пользователю разрезе и производит необходимые вычисления без использования формул.
Например, у нас есть база данных с продажами магазина для садоводов. В ней собрана информация обо всех продажах, детализированная до каждого кассового чека. Это огромная таблица с большим количеством строк, из которой невозможно взять какую-то ценную аналитическую информацию.
Предположим, нужна информация о сумме выручки в разрезе категории товара. Или по месяцам. Конечно, можно взять все категории товара и функцией СУММЕСЛИМН вытянуть нужные данные, но это займет намного больше времени. К тому же, если у вас завтра появится новая категория, ее нужно будет вручную добавить в таблицу (не забыть об этом, как минимум). Если же построить сводную таблицу в excel, они это сделает это за вас.
Подготавливаем данные для построения сводной таблицы
Давайте разберемся, из какой исходной таблицы можно построить сводную таблицу в excel (спойлер: не каждая таблица годится).
Основные требования к таблице, из которой будет создаваться сводная таблица:
- Все столбцы в исходной таблице должны иметь уникальные (т.е. не повторяющиеся) заголовки.
- Все строки в пределах одного столбца должны иметь однородные данные. Например, если в столбце указан месяц в формате “янв”, то не должно быть данных в формате “Январь” — эксель сочтет их как два разных месяца. Также использование русских и похожих латинских букв (например С и C на глаз не отличишь, но одна из них в латинской раскладке, и эксель также посчитает их разными).
- Числовые данные, которым вы будете оперировать, должны быть именно числовыми. Если есть зеленый треугольник в верхнем левом углу ячейки — такое число является текстом, и должно быть преобразовано в число, иначе с ним нельзя делать вычисления.
- Исходная таблица не должна содержать промежуточных итогов.
- Нежелательно иметь пустые строки
- Под исходной таблицей нежелательно иметь какие-то другие данные, оставьте это пространство пустым, это облегчит дальнейшее обновление сводной таблицы.
Лучшим вариантом организации исходных данных является преобразование их в “умную” таблицу. Но это не является обязательным требованием, к тому же “умные” таблицы имеют некоторые ограничения и могут быть использованы не всегда. Но в целом их использование очень упрощает работу со сводными таблицами.
Итак, имеем исходную таблицу с продажами садового инвентаря со следующими столбцами. Данные в серых столбцах взяты из учетной системы, столбец Месяц сделали расчетный для удобства организации данных.
Рассматривать будем на примере обычной таблицы.
Если мы не предполагаем в дальнейшем добавлять в исходную таблицу данные, то можно выделить только диапазон этой таблицы, например, А1:Н202. Однако, если в исходную таблицу будут добавляться данные, лучше выделить полностью столбцы, входящие в таблицу. Иначе есть риск, что при обновлении сводной таблицы новые данные в нее не попадут.
Выделяем полностью столбцы, зажав левую кнопку мыши.
Далее вкладка Вставка — Сводная таблица.
Появится окно создания сводной таблицы. Разберем, что в нем:
- Выберите данные для анализа. Здесь уже указан диапазон, который вы выделили, не меняйте его.
- Укажите, куда следует поместить отчет сводной таблицы. Новичкам (и не только) советую всегда выбирать Новый лист. Если выбираете “на существующий лист”, то нужно указать ячейку на уже созданном листе, с которой будет начинаться сводная таблица. Только учтите, что пространство нужно выбрать с запасом, чтобы потом сводная таблица не “съела” данные на листе.
Выберем На новый лист и нажмем ОК.
Появится заготовка для будущей сводной таблицы и возможность выбрать поля в правой части экрана.
Как можно добавлять поля из исходной таблицы в сводную.
- нажимая на “галочку”. При этом нужно контролировать, в какую область (строки или столбцы) excel занесет отмеченное поле. При необходимости перенести.
- щелкнув правой кнопкой мыши на названии поля и выбрав, куда его поместить
- перетаскивая мышкой поля в нужный раздел. Такой способ рекомендуется.
Перетащим поле Категория в блок СТРОКИ, поле Месяц — в блок СТОЛБЦЫ, а поле Стоимость — в блок ЗНАЧЕНИЯ.
Обратите внимание!
Возле слова Количество не случайно указан знак вопроса. В большинстве версий excel по умолчанию считает в значениях именно количество элементов. По невнимательности можно оставить так и получить кривой результат.
Нам же нужна именно сумма по полю Стоимость. Нажмем на “треугольник” и выберем Параметры полей значений, и далее — Сумма.
В итоге получим такую сводную таблицу. В строках — Категории товара, в столбцах — месяцы, а в ячейках — суммы выручки от продаж товаров данной категории. И это без единой формулы!
Где найти панель редактирования сводной таблицы
Вы создали сводную таблицу и закрыли списки полей (так обычно и делают, чтобы не мешали просмотру), но возникла необходимость внести корректировки в макет сводной таблицы.
Необходимо правой кнопкой мыши щелкнуть в любом месте сводной таблицы и выбрать пункт Показать список полей.
Как изменить структуру сводной таблицы
Предположим, требования изменились, и теперь в строках нужно видеть разбивку по месяцам и наименованию товара, а в столбцах — категорию товара. В случае с обычной таблицей формул на этом месте мы бы начали плакать и переписывать формулы *смайлик* Здесь же просто нужно перетащить Категорию товара — в столбцы, Месяц в строки, и добавить в строки поле Наименование товара.
Получаем результат.
Как удалить поле сводной таблицы
Теперь нам больше не нужно видеть развертку по наименованию товара в строках.
Для того, чтобы удалить поле из сводной таблицы, есть 3 основных способа:
- снять галочку с названия поля, оно автоматически исчезнет
- в блоке, где располагается поле, щелкнуть на “треугольник” и выбрать Удалить поле.
- в самой сводной таблице щелкнуть правой кнопкой мыши на любом значении поля и выбрать Удалить “Название поля”
Для чего нужно поле Фильтр в сводной таблице
Итак, мы разобрались со строками, столбцами и значениями в сводной таблице, научились их добавлять и удалять. Но зачем же нужно поле Фильтр в сводной таблице?
Перестроим таблицу таким образом: в строки добавим Наименование товаров, в столбцы — месяцы.
Предположим, нужно видеть не все названия товаров, а только по категории Инструмент.
Для этого нужно перетащить поле Категория товара в блок ФИЛЬТРЫ.
После этого над сводной таблицей появляется дополнительная строка с фильтрами.
По умолчанию выбраны все значения. Отфильтруем нужную нам категорию Инструмент. Для этого щелкнем на выпадающий список в фильтре и выделим нужную категорию. Сводная таблица уменьшилась, отобразив данные только по категории Инструмент.
Фильтровать значения в сводной таблице можно не только по одному параметру, но и по нескольким. Для этого нужно активировать “галочку” Выделить несколько элементов и установить “галочки” возле нужных.
К томе же, полей, по которым фильтруем данные, может быть несколько. Чтобы отфильтровать данные в сводной таблице не только по категории товара, но и по другим полям, их также нужно перетянуть в блок ФИЛЬТРЫ.
Как обновить сводную таблицу
Если исходная таблица с данными периодически пополняется новыми строками, то потребуется обновлять сводную таблицу.
Чтобы обновить сводную таблицу, нужно щелкнуть на ней правой кнопкой мыши и выбрать пункт Обновить.
Возможно также настроить автоматическое обновление сводной таблицы при открытии файла. Для этого щелкните правой кнопкой мыши на сводной таблице и выберите пункт Параметры сводной таблицы, далее на вкладке Данные активируйте “галочку” Обновить при открытии файла.
Как изменить внешний вид (формат) сводной таблицы
Про настройку форматирования сводной таблицы есть видеоролик:
В статье кратко остановимся на возможностях.
Изменить внешний вид сводной таблицы можно на вкладке Конструктор.
Выпадающий список Промежуточные итоги позволяет управлять выводом промежуточных итогов по строкам. Промежуточные итоги можно отключить совсем, или выводить в верхней или нижней части группы.
Общий итоги — позволяет управлять общими итогами для строк или столбцов, выводить их или отключать.
Макет отчета — позволяет изменять структуру таблицы. По умолчанию сводная таблица создается в сжатой форме. Но можно перестроить ее в форме структуры или в табличной форме.
Пустые строки — этот выпадающий список редко используется, позволяет добавлять пустую строку между группами, чтобы визуально отделить их.
Значение (пусто) в сводной таблице — как убрать
По умолчанию в сводную таблицу всегда включается значение Пусто. Если в исходной таблице есть строки или столбцы с пустыми ячейками, их значения попадут в эту категорию.
Но Пусто выводится и в том случае, если пустых ячеек нет, и портит внешний вид сводной таблицы. Часто его убирают.
Чтобы убрать Пусто в сводной таблице, нужно щелкнуть на выпадающем списке строки или столбца и снять “галочку”.
В этом случае Пусто исчезнет и для строк, и для столбцов одновременно.
Обратите внимание! Если вы убираете значение Пусто, то есть следующая опасность: при добавлении новых значений в исходную таблицу (например, появится новая категория товара) они не появятся автоматически при обновлении сводной таблицы. Их нужно будет добавить вручную, установив “галочку” (там же, где снимали “галочку” с Пусто).
Где изменить источник сводной таблицы
Иногда может возникнуть ситуация, когда в исходную таблицу добавились новые столбцы, которые не попали в сводную таблицу. Сразу оговорюсь, что если вы использовали умную таблицу в качестве источника, то такой проблемы не будет, новые столбцы будут добавляться в сводную таблицу автоматически при обновлении.
Предположим, в нашей исходной таблице добавился столбец Продавец. Этот столбец нужно добавить в сводную таблицу, чтобы иметь возможность выводить данные по продавцам.
Для этого щелкнем в любом месте сводной таблицы и далее идем на вкладку Анализ — Источник данных.
Откроется лист с исходной таблицей, в котором бегущим выделением будут выделены данные, которые попали в сводную таблицу, а в окне — источник данных.
Далее необходимо в открывшемся окне в поле Таблица или диапазон указать новый диапазон для сводной таблицы: просто выделить необходимый диапазон левой кнопкой мыши (полностью диапазон, включая новый столбец, а не только новый столбец). Нажимаете Ок.
Вернувшись на лист сводной таблицы, вы увидите, что новое поле Продавец появилось в списке полей. Теперь его можно перетаскивать в блоки СТРОКИ, СТОЛБЦЫ или ФИЛЬТРЫ.
Как скопировать сводную таблицу
Чтобы скопировать сводную таблицу, необходимо выделить ее полностью, вместе со строками фильтров. Далее копировать как обычно (Ctrl+C или кнопка Копировать) и вставить в нужное место (Ctrl + V или кнопка Вставить).
Удаляем сводную таблицу
Если сводная таблица расположена на отдельном листе, то чтобы удалить сводную таблицу, можно удалить целиком весь лист (правой кнопкой мыши на ярлычке листа и выбрать Удалить).
Если сводная таблица расположена на листе, где есть другие данные, самый простой способ ее удалить — выделить полностью сводную таблицу вместе с фильтрами (если они есть) и нажать кнопку Delete.
Итак, мы узнали, как построить сводную таблицу в Excel из обычной таблицы с данными. Также рассмотрели некоторые ее базовые возможности. Но если вы хотите освоить такой инструмент, как сводные таблицы с нуля и до профессионального уровня, ознакомьтесь с другими статьями в разделе. Поверьте, без знания сводных таблиц вы не станете профессионалом в Excel!
Сообщество Excel Analytics | обучение Excel
Канал на Яндекс.Дзен
Вам может быть интересно:
Мы не можем изменить выделенные ячейки так как это повлияет на сводную таблицу
После создания сводной таблицы можно изменить диапазон исходных данных. Например, расширить его и включить дополнительные строки данных. Однако если исходные данные существенно изменены, например содержат больше или меньше столбцов, рекомендуется создать новую сводную таблицу.
Вы можете изменить источник данных сводная таблица на другой Excel или диапазон ячеек или другой внешний источник данных.
Щелкните Отчет сводной таблицы.
На вкладке « Анализ» в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».
Отобразится диалоговое сводная таблица «Источник данных».
Выполните одно из следующих действий:
чтобы использовать другое подключение
Щелкните » Использовать внешний источник данных«, а затем выберите «Выбрать подключение».
Отобразится диалоговое окно «Существующие подключения».
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть». Что делать, если подключение отсутствует в списке?
Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу как новое подключение к книге, а затем используется в качестве нового подключения для сводная таблица отчета.
Нажмите кнопку ОК.
Изменение источника данных сводная таблица другой Excel или диапазона ячеек
Щелкните «Выбрать таблицу или диапазон«, а затем введите первую ячейку в текстовом поле «Таблица/диапазон» и нажмите кнопку «ОК«.
Если подключение отсутствует в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных сведений» и найдите источник данных, к которому нужно подключиться, в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Щелкните Отчет сводной таблицы.
На вкладке « Параметры » в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».
Отобразится диалоговое окно сводная таблица источника данных.
Выполните одно из указанных ниже действий.
Чтобы использовать другой Excel таблицы или диапазона ячеек, щелкните «Выбрать таблицу или диапазон«, а затем введите первую ячейку в текстовом поле «Таблица/диапазон».
Кроме того, нажмите кнопку «Свернуть диалоговое окно чтобы временно скрыть диалоговое окно, выделите начальную ячейку на листе, а затем нажмите кнопку «Развернуть диалоговое окно» .
Чтобы использовать другое подключение, выберите «Использовать внешний источник данных», а затем нажмите кнопку «Выбрать подключение».
Отобразится диалоговое окно «Существующие подключения».
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть».
Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу как новое подключение к книге, а затем используется в качестве нового подключения для сводная таблица отчета.
Нажмите кнопку ОК.
Если подключение отсутствует в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных сведений» и найдите источник данных, к которому нужно подключиться, в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Дополнительные сведения о поддерживаемых источниках данных см. в разделе «Импорт и формирование данных в Excel для Mac (Power Query).
Щелкните Отчет сводной таблицы.
На вкладке « Анализ» в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».
Отобразится диалоговое сводная таблица «Источник данных».
Выполните одно из указанных ниже действий.
Чтобы изменить источник данных сводная таблица на другую таблицу Excel или диапазон ячеек, щелкните «Выбрать таблицу или диапазон«, а затем введите первую ячейку в текстовом поле «Таблица или диапазон» и нажмите кнопку «ОК».
Чтобы использовать другое подключение, выполните следующие действия.
Щелкните » Использовать внешний источник данных«, а затем выберите «Выбрать подключение».
Отобразится диалоговое окно «Существующие подключения».
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть». Что делать, если подключение отсутствует в списке?
Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу как новое подключение к книге, а затем используется в качестве нового подключения для сводная таблица отчета.
Нажмите кнопку ОК.
Если подключение отсутствует в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных сведений» и найдите источник данных, к которому нужно подключиться, в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Сводные таблицы в Excel
Сводные таблицы ( Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:
- имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
- необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
- этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра ( CTRL+SHIFT+L ), формул , Расширенного фильтра ;
- исходная таблица удовлетворяет определенным требованиям (см. ниже).
Пользователи часто избегают использования Сводных таблиц , т.к. уверены, что они слишком сложны. Действительно, для того чтобы освоить любой новый инструмент или метод, требуется приложить усилия и потратить время. Но, в результате эффект от освоения нового должен превзойти вложенные усилия. В этой статье разберемся, как создавать и применять Сводные таблицы .
Подготовка исходной таблицы
Начнем с требований к исходной таблице.
- каждый столбец должен иметь заголовок;
- в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата ; столбец «Поставщик» — названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
- в таблице должны отсутствовать полностью незаполненные строки и столбцы;
- в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
- избегайте таблиц с «неправильной» структурой (см. рисунок ниже).
Вместо того, чтобы плодить повторяющиеся столбцы ( регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).
Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц .
Несколько облегчит процесс построения Сводной таблицы , тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 ( Вставка/ Таблицы/ Таблица ). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица . Все поля окна будут автоматически заполнены, нажмите ОК.
Создание таблицы в формате EXCEL 2007 добавляет новые возможности:
- при добавлении в таблицу новых значений новые строки автоматически добавляются к таблице;
- при создании таблицы к ней применяется форматирование, к заголовкам – фильтр, появляется возможность автоматически создать строку итогов, сортировать данные и пр.;
- таблице автоматически присваивается Имя .
В качестве исходной будем использовать таблицу в формате EXCEL 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте.
В таблице имеются столбцы:
- Товар – наименование партии товара, например, « Апельсины »;
- Группа – группа товара, например, « Апельсины » входят в группу « Фрукты »;
- Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
- Дата поставки – Дата поставки Товара Поставщиком;
- Регион продажи – Регион, в котором была реализована партия Товара;
- Продажи – Стоимость, по которой удалось реализовать партию Товара;
- Сбыт – срок фактической реализации Товара в Регионе (в днях);
- Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.
Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».
Создание Сводной таблицы
Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
Имея исходную таблицу в формате EXCEL 2007 , для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица .
В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.
На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы).
Структура Сводной таблицы в общем виде может быть представлена так:
Заполним сначала раздел Названия строк . Т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец — синонимы).
Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже ).
Теперь поставим галочку в Списке полей у поля Продажи.
Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.
Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью Отбор уникальных значений с суммированием по соседнему столбцу ). Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.
Детализация данных Сводной таблицы
Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы , то достаточно двойного клика мышкой на конкретном значении в Сводной таблице , чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».
Обновление Сводной таблицы
Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице . Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/ Данные/ Обновить . Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы , вызовите правой клавишей мыши контекстное меню и выберите пункт Обновить .
Удаление Сводной таблицы
Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ — удалить только саму Сводную таблицу : выделите любую ячейку Сводной таблицы , нажмите CTRL + A (будет выделена вся Сводная таблица ), нажмите клавишу Delete .
Изменение функции итогов
При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы. Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
Изменение порядка сортировки
Теперь немного модифицируем наш Сводный отчет . Сначала изменим порядок сортировки названий Товаров: отсортируем их в обратном порядке от Я до А. Для этого через выпадающий список у заголовка столбца, содержащего наименования Товаров, войдем в меню и выберем Сортировка от Я до А .
Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).
Затем, нажав левую клавишу мыши, перетащите ячейку на самую верхнюю позицию в списке прямо под заголовок столбца.
После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.
Изменение формата числовых значений
Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат …
В появившемся окне выберите числовой формат и поставьте галочку флажка Разделитель групп разрядов .
Добавление новых полей
Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.
Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть Сводную таблицу , чтобы отобразить только продажи по Регионам.
Добавление столбцов
Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.
Сводная таблица примет следующий вид.
Меняем столбцы местами
Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место.
Удаление полей
Любое поле можно удалить из Сводной таблицы. Для этого нужно навести на него курсор мыши в Списке полей (в областях Фильтр отчета, Названия отчета, Названия столбцов, Значения), нажать левую клавишу мыши и перетащить удаляемое поле за границу Списка полей.
Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).
Добавление фильтра
Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:
- Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
- Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
- Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;
Вид получившейся Сводной таблицы должен быть таким:
Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.
После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.
Обратите внимание, что в Списке полей Сводной таблицы напротив поля Прибыль появился значок фильтра. Удалить фильтр можно сняв галочку в Списке полей.
Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры .
Также стандартный механизм фильтрации данных доступен через выпадающий список в заголовках строк и столбцов Сводной таблицы.
Несколько итогов для одного поля
Предположим, что требуется подсчитать количество проданных партий и суммарные продажи каждого Товара. Для этого:
- Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
- Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
- Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;
- в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество . Задача решена.
Отключаем строки итогов
Строку итогов можно отключить через меню: Работа со сводными таблицами/ Конструктор/ Макет/ Общие итоги . Не забудьте предварительно выделить любую ячейку Сводной таблицы .
Группируем числа и Даты
Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:
- Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
- Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
- выделите единственное значение поля Сбыт в Сводной таблице , вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
- Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;
Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:
- Выделите одно значение Сводной таблицы в столбце Названия строк;
- В меню Работа со сводными таблицами/ Параметры/ Группировать выберите пункт Группировка по полю ;
- Появившееся окно заполните, как показано на рисунке ниже;
- Нажмите ОК.
Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.
Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать .
Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:
Теперь Сводная таблица показывает, сколько партий Товара поставлялось каждый месяц.
Условное форматирование ячеек Сводной таблицы
К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:
Как перейти к редактированию исходных данных прямо из сводной таблицы?
Несомненно, основная задача работы со сводными таблица — анализ данных. А раз мы что-то анализируем, значит может потребоваться и что-то изменить в случае нахождения каких-то расхождений. И самое печальное здесь то, что нельзя изменять значения непосредственно внутри сводной таблицы(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)» и открыть заново. Хотя это нужно лишь для того, чтобы создался новый пункт меню в сводной таблицы, весь остальной функционал будет работать и без перезапуска.
Надеюсь данный трюк будет полезен всем, кто работает со сводными.
Ну а если совсем лень делать что-то своими руками, то можно воспользоваться данной возможностью, уже встроенной в мою надстройку MulTex. Там же есть вариант отображения деталей всех выделенных ячеек, а не только одной, как это реализовано в самом Excel.