Сводная таблица как изменить область данных - Исправление ошибок и поиск оптимальных решений проблем

Изменение исходных данных сводной таблицы

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше

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

Вы можете изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек или другой внешний источник данных.

Щелкните Отчет сводной таблицы.
На вкладке « Анализ» в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».

Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
Выполните одно из следующих действий:

чтобы использовать другое подключение
1. Щелкните » Использовать внешний источник данных«, а затем выберите «Выбрать подключение».
  
  Отобразится диалоговое окно «Существующие подключения».
2. В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
3. Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть». Что делать, если подключение отсутствует в списке?
  
  Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу в качестве нового подключения к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.
  
  Дополнительные сведения см. в статье «Управление подключениями к данным в книге».
4. Нажмите кнопку ОК.
Изменение источника данных сводной таблицы на другую таблицу Excel или диапазон ячеек
- Щелкните «Выбрать таблицу или диапазон«, а затем введите первую ячейку в текстовом поле «Таблица/диапазон» и нажмите кнопку «ОК«.

Если подключение отсутствует в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных сведений» и найдите источник данных, к которому нужно подключиться, в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.

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

Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.

Щелкните Отчет сводной таблицы.
На вкладке « Параметры » в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».

Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
Выполните одно из указанных ниже действий.
- Чтобы использовать другую таблицу или диапазон ячеек Excel, щелкните «Выбрать таблицу или диапазон «, а затем введите первую ячейку в текстовом поле «Таблица / диапазон».
  
  Кроме того, нажмите кнопку «Свернуть диалоговое окно чтобы временно скрыть диалоговое окно, выделите начальную ячейку на листе, а затем нажмите кнопку «Развернуть диалоговое окно» .
Чтобы использовать другое подключение, выберите «Использовать внешний источник данных», а затем нажмите кнопку «Выбрать подключение».

Отобразится диалоговое окно «Существующие подключения».
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть».

Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу в качестве нового подключения к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.

Дополнительные сведения см. в статье «Управление подключениями к данным в книге».
Нажмите кнопку ОК.

Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.

Дополнительные сведения о поддерживаемых источниках данных см. в разделе «Импорт и формирование данных в Excel для Mac (Power Query).

Щелкните Отчет сводной таблицы.
На вкладке « Анализ» в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных».

Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
Выполните одно из указанных ниже действий.
- Чтобы изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек, щелкните «Выбрать таблицу или диапазон«, а затем введите первую ячейку в текстовом поле «Таблица/диапазон» и нажмите кнопку «ОК».
- Чтобы использовать другое подключение, выполните следующие действия.
  1. Щелкните » Использовать внешний источник данных«, а затем выберите «Выбрать подключение».
    
    Отобразится диалоговое окно «Существующие подключения».
  2. В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
  3. Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть». Что делать, если подключение отсутствует в списке?
    
    Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу в качестве нового подключения к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.
    
    Дополнительные сведения см. в статье «Управление подключениями к данным в книге».
  4. Нажмите кнопку ОК.

Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Создание сводной таблицы с внешним источником данных

Создание сводной таблицы, подключенной к наборам данных Power BI

Управление подключениями к данным в книге

Нужна дополнительная помощь?

Источник

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]

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

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 42,472 times.

Is this article up to date?

Источник

Download Article

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]

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Thanks for submitting a tip for review!

References

About This Article

Article SummaryX

Did this summary help you?

Thanks to all authors for creating a page that has been read 42,472 times.

Is this article up to date?

Источник

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

Обновить диапазон сводной таблицы в Excel

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу …

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния: Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты: Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий …
Более 300 мощных функций; Работает с Office 2007-2021 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Обновить диапазон сводной таблицы в Excel

Выполните следующие действия, чтобы обновить диапазон сводной таблицы.

1. После изменения диапазона данных щелкните соответствующую сводную таблицу и щелкните Опция (в Excel 2013 щелкните АНАЛИЗ )> Изменить источник данных. Смотрите скриншот:

2. Затем во всплывающем диалоговом окне выберите новый диапазон данных, который необходимо обновить. Смотрите скриншот:

3. Нажмите OK. Теперь сводная таблица обновлена.

Внимание: Только строки добавляются в нижнюю часть исходных данных таблицы или столбцы добавляются в самый правый угол, диапазон сводной таблицы обновляется при нажатии Option (или Analyze)> Change Data Source.

Относительные статьи:

Обновить сводную таблицу при открытии файла в Excel
Обновить сводную таблицу без повторного открытия в Excel

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (1)

Оценок пока нет. Оцените первым!

Источник

Изменить источник данных для сводной таблицы

Сводные таблицы — это мощные инструменты, которые помогают нам много времени при анализе данных. Перед созданием сводной таблицы нам нужно выбрать диапазон данных, который мы охватываем при создании отчета сводной таблицы. Любое удаление данных повлияет на нажатие кнопки обновления, и любое изменение значений также повлияет на кнопку обновления.

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

Найдите источник данных сводной таблицы

Одна из вещей, когда мы получаем книгу 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 737

Источник

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

Создание новой сводной таблицы

Для создания сводной таблицы:

Подготовьте исходный набор данных, который требуется использовать для создания сводной таблицы. Он должен включать заголовки столбцов. Набор данных не должен содержать пустых строк или столбцов.
Выделите любую ячейку в исходном диапазоне данных.
Перейдите на вкладку Сводная таблица верхней панели инструментов и нажмите на кнопку Вставить таблицу .Если вы хотите создать сводную таблицу на базе форматированной таблицы, также можно использовать опцию Вставить сводную таблицу на вкладке Параметры таблицы правой боковой панели.
Откроется окно Создать сводную таблицу.
- Диапазон исходных данных уже указан. В этом случае будут использоваться все данные из исходного диапазона. Если вы хотите изменить диапазон данных (например, включить только часть исходных данных), нажмите на кнопку . В окне Выбор диапазона данных введите нужный диапазон данных в формате Лист1!$A$1:$E$10. Вы также можете выделить нужный диапазон данных на листе с помощью мыши. Когда все будет готово, нажмите кнопку OK.
- Укажите, где требуется разместить сводную таблицу.
- Когда местоположение таблицы будет выбрано, нажмите кнопку OK в окне Создать таблицу.

Пустая сводная таблица будет вставлена в выбранном местоположении.

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

ВЫБОР ПОЛЕЙ ДЛЯ ОТОБРАЖЕНИЯ

Раздел Выбрать поля содержит названия полей, соответствующие заголовкам столбцов в исходном наборе данных. Каждое поле содержит значения из соответствующего столбца исходной таблицы. Ниже доступны следующие четыре поля: Фильтры, Столбцы, Строки и Значения.

Отметьте галочками поля, которые требуется отобразить в сводной таблице. Когда вы отметите поле, оно будет добавлено в один из доступных разделов на правой боковой панели в зависимости от типа данных и будет отображено в сводной таблице. Поля, содержащие текстовые значения, будут добавлены в раздел Строки; поля, содержащие числовые значения, будут добавлены в раздел Значения.

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

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

Ниже приводятся примеры использования разделов Фильтры, Столбцы, Строки и Значения.

При добавлении поля в раздел Фильтры над сводной таблицей будет добавлен отдельный фильтр. Он будет применен ко всей сводной таблице. Если нажать на кнопку со стрелкой в добавленном фильтре, вы увидите значения из выбранного поля. Если снять галочки с некоторых значений в окне фильтра и нажать кнопку OK, значения, с которых снято выделение, не будут отображаться в сводной таблице.
При добавлении поля в раздел Столбцы, сводная таблица будет содержать столько же столбцов, сколько значений содержится в выбранном поле. Также будет добавлен столбец Общий итог.
При добавлении поля в раздел Строки, сводная таблица будет содержать столько же строк, сколько значений содержится в выбранном поле. Также будет добавлена строка Общий итог.
При добавлении поля в раздел Значения в сводной таблице будет отображаться суммирующее значение для всех числовых значений из выбранных полей. Если поле содержит текстовые значения, будет отображаться количество значений. Функцию, которая используется для вычисления суммирующего значения, можно изменить в настройках поля.

УПОРЯДОЧИВАНИЕ ПОЛЕЙ И ИЗМЕНЕНИЕ ИХ СВОЙСТВ
Когда поля будут добавлены в нужные разделы, ими можно управлять, чтобы изменить макет и формат сводной таблицы. Нажмите на черную стрелку справа от поля в разделе Фильтры, Столбцы, Строки или Значения, чтобы открыть контекстное меню поля.

С его помощью можно:

Переместить выбранное поле Вверх, Вниз, В начало или В конец текущего раздела, если в текущий раздел добавлено несколько полей.
Переместить выбранное поле в другой раздел — в Фильтры, Столбцы, Строки или Значения. Опция, соответствующая текущему разделу, будет неактивна.
Удалить выбранное поле из текущего раздела.
Изменить параметры выбранного поля.

Параметры полей из раздела Фильтры, Столбцы и Строки выглядят одинаково:

На вкладке Макет содержатся следующие опции:

Опция Имя источника позволяет посмотреть имя поля, соответствующее заголовку столбца из исходного набора данных.
Опция Пользовательское имя позволяет изменить имя выбранного поля, отображаемое в сводной таблице.
В разделе Форма отчета можно изменить способ отображения выбранного поля в сводной таблице:
- Выберите нужный макет для выбранного поля в сводной таблице:
  - В форме В виде таблицы отображается один столбец для каждого поля и выделяется место для заголовков полей.
  - В форме Структуры отображается один столбец для каждого поля и выделяется место для заголовков полей. В ней также можно отображать промежуточные итоги над каждой группой.
  - В Компактной форме элементы из разных полей раздела строк отображаются в одном столбце.
- Опция Повторять метки элементов в каждой строке позволяет визуально группировать строки или столбцы при наличии нескольких полей в табличной форме.
- Опция Добавлять пустую строку после каждой записи позволяет добавлять пустые строки после элементов выбранного поля.
- Опция Показывать промежуточные итоги позволяет выбрать, надо ли отображать промежуточные итоги для выбранного поля. Можно выбрать одну из опций: Показывать в заголовке группы или Показывать в нижней части группы.
- Опция Показывать элементы без данных позволяет показать или скрыть пустые элементы в выбранном поле.

На вкладке Промежуточные итоги можно выбрать Функции для промежуточных итогов. Отметьте галочкой нужную функцию в списке: Сумма, Количество, Среднее, Макс, Мин, Произведение, Количество чисел, Стандотклон, Стандотклонп, Дисп, Диспр.

Параметры поля значений

Опция Имя источника позволяет посмотреть имя поля, соответствующее заголовку столбца из исходного набора данных.
Опция Пользовательское имя позволяет изменить имя выбранного поля, отображаемое в сводной таблице.
В списке Операция можно выбрать функцию, используемую для вычисления суммирующего значения всех значений из этого поля. По умолчанию для числовых значений используется функция Сумма, а для текстовых значений — функция Количество. Доступны следующие функции: Сумма, Количество, Среднее, Макс, Мин, Произведение, Количество чисел, Стандотклон, Стандотклонп, Дисп, Диспр.

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

Опции, доступные на верхней панели инструментов, позволяют изменить способ отображения сводной таблицы. Эти параметры применяются ко всей сводной таблице.

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

В выпадающем списке Макет отчета можно выбрать нужный макет для сводной таблицы:
В выпадающем списке Пустые строки можно выбрать, надо ли отображать пустые строки после элементов:
- Вставлять пустую строку после каждого элемента — позволяет добавить пустые строки после элементов.
- Удалить пустую строку после каждого элемента — позволяет убрать добавленные пустые строки.
В выпадающем списке Промежуточные итоги можно выбрать, надо ли отображать промежуточные итоги в сводной таблице:
- Не показывать промежуточные итоги — позволяет скрыть промежуточные итоги для всех элементов.
- Показывать все промежуточные итоги в нижней части группы — позволяет отобразить промежуточные итоги под строками, для которых производится промежуточное суммирование.
- Показывать все промежуточные итоги в верхней части группы — позволяет отобразить промежуточные итоги над строками, для которых производится промежуточное суммирование.
В выпадающем списке Общие итоги можно выбрать, надо ли отображать общие итоги в сводной таблице:
- Отключить для строк и столбцов — позволяет скрыть общие итоги как для строк, так и для столбцов.
- Включить для строк и столбцов — позволяет отобразить общие итоги как для строк, так и для столбцов.
- Включить только для строк — позволяет отобразить общие итоги только для строк.
- Включить только для столбцов — позволяет отобразить общие итоги только для столбцов.
Примечание: аналогичные настройки также доступны в окне дополнительных параметров сводной таблицы в разделе Общие итоги вкладки Название и макет.

Кнопка Выделить позволяет выделить всю сводную таблицу.

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

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

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

Параметры строк и столбцов позволяют выделить некоторые строки или столбцы при помощи особого форматирования, или выделить разные строки и столбцы с помощью разных цветов фона для их четкого разграничения. Доступны следующие опции:

Заголовки строк — позволяет выделить заголовки строк при помощи особого форматирования.
Заголовки столбцов — позволяет выделить заголовки столбцов при помощи особого форматирования.
Чередовать строки — включает чередование цвета фона для четных и нечетных строк.
Чередовать столбцы — включает чередование цвета фона для четных и нечетных столбцов.

Список шаблонов позволяет выбрать один из готовых стилей сводных таблиц. Каждый шаблон сочетает в себе определенные параметры форматирования, такие как цвет фона, стиль границ, чередование строк или столбцов и т.д. Набор шаблонов отображается по-разному в зависимости от параметров, выбранных для строк и столбцов. Например, если вы отметили опции Заголовки строк и Чередовать столбцы, отображаемый список шаблонов будет содержать только шаблоны с выделенными заголовками строк и включенным чередованием столбцов.

Фильтрация и сортировка сводных таблиц

Вы можете фильтровать сводные таблицы по подписям или значениям и использовать дополнительные параметры сортировки.

ФИЛЬТРАЦИЯ

Нажмите на кнопку со стрелкой в Названиях строк или Названиях столбцов сводной таблицы. Откроется список команд фильтра:

Настройте параметры фильтра. Можно действовать одним из следующих способов: выбрать данные, которые надо отображать, или отфильтровать данные по определенным критериям.

Выбор данных, которые надо отображатьСнимите флажки рядом с данными, которые требуется скрыть. Для удобства все данные в списке команд фильтра отсортированы в порядке возрастания.
Примечание: флажок (пусто) соответствует пустым ячейкам. Он доступен, если в выделенном диапазоне есть хотя бы одна пустая ячейка.

Чтобы облегчить этот процесс, используйте поле поиска. Введите в этом поле свой запрос полностью или частично — в списке ниже будут отображены значения, содержащие эти символы. Также будут доступны следующие две опции:
- Выделить все результаты поиска — выбрана по умолчанию. Позволяет выделить все значения в списке, соответствующие вашему запросу.
- Добавить выделенный фрагмент в фильтр — если установить этот флажок, выбранные значения не будут скрыты после применения фильтра.
После того как вы выберете все нужные данные, нажмите кнопку OK в списке команд фильтра, чтобы применить фильтр.
Фильтрация данных по определенным критериямВ правой части окна фильтра можно выбрать команду Фильтр подписей или Фильтр значений, а затем выбрать одну из опций в подменю:
- Для Фильтра подписей доступны следующие опции:
  - Для текстовых значений: Равно…, Не равно…, Начинается с…, Не начинается с…, Оканчивается на…, Не оканчивается на…, Содержит…, Не содержит….
  - Для числовых значений: Больше…, Больше или равно…, Меньше…, Меньше или равно…, Между, Не между.
- Для Фильтра значений доступны следующие опции: Равно…, Не равно…, Больше…, Больше или равно…, Меньше…, Меньше или равно…, Между, Не между, Первые 10.
После выбора одной из вышеуказанных опций (кроме опций Первые 10), откроется окно Фильтра подписей/Значений. В первом и втором выпадающих списках будут выбраны соответствующее поле и критерий. Введите нужное значение в поле справа.

Нажмите кнопку OK, чтобы применить фильтр.

При выборе опции Первые 10 из списка опций Фильтра значений откроется новое окно:

В первом выпадающем списке можно выбрать, надо ли отобразить Наибольшие или Наименьшие значения. Во втором поле можно указать, сколько записей из списка или какой процент от общего количества записей требуется отобразить (можно ввести число от 1 до 500). В третьем выпадающем списке можно задать единицы измерения: Элемент, Процент или Сумма. В четвертом выпадающем списке отображается имя выбранного поля. Когда нужные параметры будут заданы, нажмите кнопку OK, чтобы применить фильтр.

Кнопка Фильтр появится в Названиях строк или Названиях столбцов сводной таблицы. Это означает, что фильтр применен.

СОРТИРОВКА

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

Опция Дополнительные параметры сортировки… позволяет открыть окно Сортировать, в котором можно выбрать нужный порядок сортировки — По возрастанию (от А до Я) или По убыванию (от Я до А) — а затем выбрать определенное поле, которое требуется отсортировать.

Изменение дополнительных параметров сводной таблицы

Чтобы изменить дополнительные параметры сводной таблицы, нажмите ссылку Дополнительные параметры на правой боковой панели. Откроется окно ‘Сводная таблица — Дополнительные параметры’:

На вкладке Название и макет можно изменить общие свойства сводной таблицы.

С помощью опции Название можно изменить название сводной таблицы.
В разделе Общие итоги можно выбрать, надо ли отображать общие итоги в сводной таблице. Опции Показывать для строк и Показывать для столбцов отмечены по умолчанию. Вы можете снять галочку или с одной из них, или с них обеих, чтобы скрыть соответствующие общие итоги из сводной таблицы.
Примечание: аналогичные настройки также доступны на верхней панели инструментов в меню Общие итоги.
В разделе Отображать поля в области фильтра отчета можно настроить фильтры отчета, которые появляются при добавлении полей в раздел Фильтры:
- Опция Вниз, затем вправо используется для организации столбцов. Она позволяет отображать фильтры отчета по столбцам.
- Опция Вправо, затем вниз используется для организации строк. Она позволяет отображать фильтры отчета по строкам.
- Опция Число полей фильтра отчета в столбце позволяет выбрать количество фильтров для отображения в каждом столбце. По умолчанию задано значение 0. Вы можете выбрать нужное числовое значение.
В разделе Заголовки полей можно выбрать, надо ли отображать заголовки полей в сводной таблице. Опция Показывать заголовки полей для строк и столбцов выбрана по умолчанию. Снимите с нее галочку, если хотите скрыть заголовки полей из сводной таблицы.

На вкладке Источник данных можно изменить данные, которые требуется использовать для создания сводной таблицы.

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

В окне Выбор диапазона данных введите нужный диапазон данных в формате Лист1!$A$1:$E$10. Также можно выбрать нужный диапазон ячеек на рабочем листе с помощью мыши. Когда все будет готово, нажмите кнопку OK.

Вкладка Альтернативный текст позволяет задать Заголовок и Описание, которые будут зачитываться для людей с нарушениями зрения или когнитивными нарушениями, чтобы помочь им лучше понять, какую информацию содержит сводная таблица.

Удаление сводной таблицы

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

Выделите всю сводную таблицу с помощью кнопки Выделить на верхней панели инструментов.
Нажмите клавишу Delete.

Источник

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

В этой главе вы узнаете о различных способах обновления макета и / или обновления данных в сводной таблице.

Обновление макета сводной таблицы

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

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

Установите флажок « Отложить обновление макета» .

Кнопка UPDATE рядом с ней будет включена. Если вы внесете какие-либо изменения в области сводной таблицы, эти изменения будут отражены только после нажатия кнопки ОБНОВЛЕНИЕ.

Обновление данных сводной таблицы

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

Нажмите на сводную таблицу.
Нажмите вкладку ANALYZE на ленте.
Нажмите Обновить в группе данных.

Существуют различные варианты обновления данных в раскрывающемся списке.

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

Обновить – Получить последние данные из источника, подключенного к активной ячейке.

Обновить все – для получения последних данных путем обновления всех источников в рабочей книге.

Свойства соединения – установка свойств обновления для соединений книги.

Изменение исходных данных сводной таблицы

Вы можете изменить диапазон исходных данных сводной таблицы. Например, вы можете расширить исходные данные, включив в них большее количество строк данных.

Однако, если исходные данные были существенно изменены, например, имеется больше или меньше столбцов, рассмотрите возможность создания новой сводной таблицы.

Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .
Нажмите на вкладку АНАЛИЗ.
Нажмите Изменить источник данных в группе данных.

Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .

Нажмите на вкладку АНАЛИЗ.

Нажмите Изменить источник данных в группе данных.

Выберите Изменить источник данных в раскрывающемся списке.

Откроется диалоговое окно «Изменить источник данных сводной таблицы», и текущий источник данных будет выделен.

Выберите таблицу или диапазон, который хотите включить в поле «Таблица / диапазон» в разделе «Выбор таблицы или диапазона». Нажмите ОК.

Источник данных для сводной таблицы будет изменен на выбранную таблицу / диапазон данных.

Переход на внешний источник данных

Если вы хотите изменить внешний источник данных для сводной таблицы, лучше всего создать новую сводную таблицу. Однако, если расположение внешнего источника данных изменяется, например, имя базы данных SQL Server такое же, но оно было перемещено на другой сервер, или ваша база данных Access была перемещена в другой сетевой ресурс, вы можете изменить ваше текущее подключение к данным, чтобы отражать то же самое.

Нажмите на сводную таблицу.
Нажмите вкладку ANALYZE на ленте.
Нажмите Изменить источник данных в группе данных. Откроется диалоговое окно « Изменить источник данных сводной таблицы ».
Нажмите кнопку « Выбрать соединение» .

Нажмите на сводную таблицу.

Нажмите вкладку ANALYZE на ленте.

Нажмите Изменить источник данных в группе данных. Откроется диалоговое окно « Изменить источник данных сводной таблицы ».

Нажмите кнопку « Выбрать соединение» .

Откроется диалоговое окно « Существующие подключения ».

Выберите Все соединения в поле Показать. Все Соединения в вашей Рабочей книге будут отображены.
Нажмите кнопку Обзор для более .

Выберите Все соединения в поле Показать. Все Соединения в вашей Рабочей книге будут отображены.

Нажмите кнопку Обзор для более .

Откроется окно выбора источника данных .

Нажмите на кнопку «Новый источник».
Пройдите шаги мастера подключения к данным.

Если ваш источник данных находится в другой книге Excel, выполните следующие действия:

Нажмите на поле Имя файла.
Выберите имя файла книги.

Удаление сводной таблицы

Вы можете удалить сводную таблицу следующим образом:

Нажмите на сводную таблицу.
Нажмите вкладку ANALYZE на ленте.
Нажмите «Выбрать» в группе «Действия».

В раскрывающемся списке выберите « Вся сводная таблица». Вся сводная таблица будет выбрана.

Нажмите клавишу удаления. Сводная таблица будет удалена.

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

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

Весь рабочий лист вместе со сводной таблицей будет удален.

Источник

Дополнительные сведения

См. также

Нужна дополнительная помощь?

Steps

References

About This Article

Is this article up to date?

Steps

References

About This Article

Is this article up to date?

Относительные статьи:

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Изменить источник данных для сводной таблицы

Найдите источник данных сводной таблицы

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

# 1 — Изменить источник данных сводной таблицы

# 2 — Источник автоматического диапазона данных для сводной таблицы

То, что нужно запомнить

УЗНАТЬ БОЛЬШЕ >> Post Views: 1 737

Создание новой сводной таблицы

ВЫБОР ПОЛЕЙ ДЛЯ ОТОБРАЖЕНИЯ

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

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

Фильтрация и сортировка сводных таблиц

ФИЛЬТРАЦИЯ

СОРТИРОВКА

Изменение дополнительных параметров сводной таблицы

Удаление сводной таблицы

Обновление макета сводной таблицы

Обновление данных сводной таблицы

Изменение исходных данных сводной таблицы

Переход на внешний источник данных

Удаление сводной таблицы

УЗНАТЬ БОЛЬШЕ >>

Post Views: 1 737