Как изменить область сводной таблицы excel - Word и Excel

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

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

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

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

Источник

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

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

Обновить диапазон сводной таблицы в 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)

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

Источник

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 50,578 times.

Is this article up to date?

Источник

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

В окне открытого листа выделите любую ячейку сводной таблицы.
Перейдите к вкладке «Параметры» и в группе «Данные» раскройте меню кнопки «Изменить источник данных».
В списке команд выберите пункт «Изменить источник данных» (рис. 5.69).
В окне «Изменить источник данных сводной таблицы» в графе «Таблица или диапазон» введите адрес размещения новых данных.
Закройте окно кнопкой «ОК».

Рис. 5.69. Вкладка «Параметры». Меню кнопки «Изменить источник данных»

Источник

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

SergeyKorotun

Дата: Понедельник, 19.08.2013, 15:52 |
Сообщение № 1

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

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

Ответить

SergeyKorotun

Дата: Понедельник, 19.08.2013, 16:04 |
Сообщение № 2

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

Нашел:
— Работа со сводными таблицами
— Параметры
— Изменить источник данных

Ответить

Serge_007

Дата: Понедельник, 19.08.2013, 16:13 |
Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 15894

Репутация:
2623
±

Замечаний:
±

Excel 2016

Используйте таблицы Ctrl+T в версиях выше 2003 или списки в версиях ниже 2007 или динамически именованые диапазоны во всех версиях

ЮMoney:41001419691823 | WMR:126292472390

Ответить

_Boroda_

Дата: Вторник, 20.08.2013, 01:13 |
Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Или можно использовать динамический диапазон (см. Вставка — Имя). Для 2007 и выше — Формулы — Диспетчер имен
А через Параметры Вы
1. замучаетесь каждый раз менять
2. когда-нибудь поменять возможно вообще забудете (я так один раз лет 10 назад людям зарплату посчитал — во крику-то было!)

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

SergeyKorotun

Дата: Среда, 21.08.2013, 01:29 |
Сообщение № 5

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

замучаетесь каждый раз менять

Одной замены достаточно, указав в качестве диапазона все столбцы, например $A:$Z
На скорость выполнения не повлияло. Но есть и недостаток, в сводной таблице появляется подгруппа «пусто».

Ответить

SergeyKorotun

Дата: Среда, 21.08.2013, 01:35 |
Сообщение № 6

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

Или можно использовать динамический диапазон (см. Вставка — Имя).

не нашел Вставка — Имя
Excel 2007

Ответить

SergeyKorotun

Дата: Среда, 21.08.2013, 10:17 |
Сообщение № 7

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

Начиная с 1 января за каждый рабочий день добавляется примерно 500 строк.

Ответить

Serge_007

Дата: Среда, 21.08.2013, 10:20 |
Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 15894

Репутация:
2623
±

Замечаний:
±

Excel 2016

указав в качестве диапазона все столбцы, например $A:$Z. На скорость выполнения не повлияло

Чем больше данных в указанном диапазоне, тем больше будут видны «тормоза»

Для 2007 и выше — Формулы — Диспетчер имен

Добавлю: Для всех версий Ctrl+F3

ЮMoney:41001419691823 | WMR:126292472390

Ответить

SergeyKorotun

Дата: Среда, 21.08.2013, 11:22 |
Сообщение № 9

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

Добавлю: Для всех версий Ctrl+F3

Это я знаю, а как создать динамический диапазон, про который писал _Boroda_

Ответить

Serge_007

Дата: Среда, 21.08.2013, 11:25 |
Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 15894

Репутация:
2623
±

Замечаний:
±

Excel 2016

Что значит «Как создать»? Саша же выложил файл, жмите Ctrl+F3 и смотрите формулу динамического диапазона…

ЮMoney:41001419691823 | WMR:126292472390

Ответить

SergeyKorotun

Дата: Среда, 21.08.2013, 12:02 |
Сообщение № 11

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

В случае незаполненных некоторых ячеек в столбце диапазон выделяется неверно.
Можно СЧЁТЗ заменить чем нибудь другим. Например:
Sheets(1).UsedRange.Rows.Count
Sheets(1).UsedRange.Columns.Count
И как создать динамический диапазон макросом?

К сообщению приложен файл:

9102280.xls
(20.5 Kb)

Ответить

Michael_S

Дата: Среда, 21.08.2013, 12:20 |
Сообщение № 12

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация:
373
±

Замечаний:
0% ±

Excel2016

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

Ответить

RAN

Дата: Среда, 21.08.2013, 12:22 |
Сообщение № 13

Группа: Друзья

Ранг: Экселист

Сообщений: 5645

И как создать динамический диапазон макросом?

Элементарно.
Включаем макрорекордер и пишем
[vba]

Код

Sub Макрос2()
‘
‘ Макрос2 Макрос
‘

‘
     ActiveWorkbook.Names.Add Name:=»табл_», RefersToR1C1:= _
         «=OFFSET(Лист1!R1C1,,,COUNTA(Лист1!C1),COUNTA(Лист1!R1))»
     ActiveWorkbook.Names(«табл_»).Comment = «»
End Sub

[/vba]

Быть или не быть, вот в чем загвоздка!

Ответить

Michael_S

Дата: Среда, 21.08.2013, 12:37 |
Сообщение № 14

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация:
373
±

Замечаний:
0% ±

Excel2016

Если макросом, то как-то так.
[vba]

Код

Sub NameAdd()
ActiveWorkbook.Names(«Табл_»).RefersToR1C1 = «=» & Sheets(1).Name & «!» & _
Sheets(1).Range(«A1»).CurrentRegion.Address(ReferenceStyle:=xlR1C1)
End Sub

[/vba]

Ответить

SergeyKorotun

Дата: Среда, 21.08.2013, 12:51 |
Сообщение № 15

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

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

То я специально удалил, чтобы продемонстрировать ошибку выделения.
Michael_S, ваш формула

Код

=СМЕЩ(Лист1!$A$1;;;ПРОСМОТР(«яяя»;Лист1!$A:$A;СТРОКА(Лист1!$A:$A));СЧЁТЗ(Лист1!$1:$1))

не всегда срабатывает, смотрите приложенный файл.
Макросы еще не тестировал.

К сообщению приложен файл:

5782406.xls
(27.5 Kb)

Ответить

Michael_S

Дата: Среда, 21.08.2013, 13:04 |
Сообщение № 16

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация:
373
±

Замечаний:
0% ±

Excel2016

SergeyKorotun, ваши примеры выходят из пределов разумной организации данных.

Ответить

Serge_007

Дата: Среда, 21.08.2013, 13:09 |
Сообщение № 17

Группа: Админы

Ранг: Местный житель

Сообщений: 15894

Репутация:
2623
±

Замечаний:
±

Excel 2016

Сводные таблицы предназначены для работы с данными, оформленными в виде списка (таблицы), т.е. фактически — базой данных

ЮMoney:41001419691823 | WMR:126292472390

Ответить

SergeyKorotun

Дата: Среда, 21.08.2013, 13:17 |
Сообщение № 18

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

RAN, добавьте в ваш макрос Range(«табл_»).Select и на присоединенном файле в 16 сообщении увидите, что диапазон выделяется неверно.

SergeyKorotun, ваши примеры выходят из пределов разумной организации данных

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

Ответить

_Boroda_

Дата: Среда, 21.08.2013, 13:29 |
Сообщение № 19

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Понятно. То есть я за Вас Ваш пример нарисовал и я же виноват и оказался. Извините, больше так не буду. По крайней мере, с Вашими вопросами.

Таблицы разные бывают. И правильнее будет использовать в качестве ранга все столбцы.

Правильнее будет делать нормальные таблицы, коли уж на то пошло. Существует 3-я нормальная форма базы данных, для тех, кто не в курсе.

Ответить

SergeyKorotun

Дата: Среда, 21.08.2013, 18:42 |
Сообщение № 20

Группа: Проверенные

Ранг: Обитатель

Сообщений: 301

Репутация:
15
±

Замечаний:
0% ±

Excel 2007

Мы то в курсе. Но «нормальные таблицы» хранят не в екселе и юзер прямого доступа к базам не имеет.
От удалит он случайно одно значение в ключевом поле (в нашем случае в столбце А) и если помечать диапазон, а не столбцы для сводной таблицы, он за каждый новый день в сводной таблице будет терять данные из последней строки. В случае выбора столбцов в качестве диапазона он получит неверные данные в сводной таблице только за 1 день.

Ответить

Источник

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

См. также

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

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

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

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

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

Steps

References

About This Article

Is this article up to date?

А вот еще интересные статьи: