Как поменять источник данных в сводной таблице 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

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

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

Источник

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.

Ask a Question

200 characters left

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

Submit

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!

About This Article

Thanks to all authors for creating a page that has been read 162,978 times.

Is this article up to date?

Источник

Загрузить 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 720 раз.

Была ли эта статья полезной?

Источник

Be a bit wary of any solution that doesn’t involve re-creating the PivotTable from scratch. It is possible for your pivot fields’ option names to get out of sync with the values they present to the database.

For example, in one workbook I’m dealing with involving demographic data, if you try to select the «20-24» age band option, Excel actually presents you with the figures for ages 25-29. It doesn’t tell you it’s doing this, of course.

See below for a programmatic (VBA) approach to the problem that solves this issue among others. I think it’s fairly complete/robust, but I don’t use PivotTables much so would appreciate feedback.

Sub SwapSources()

strOldSource = "2010 Data"
strNewSource = "2009 Data"

Dim tmpArrOut

For Each wsh In ThisWorkbook.Worksheets
    For Each pvt In wsh.PivotTables
        tmpArrIn = pvt.SourceData
        ' row 1 of SourceData is the connection string.
        ' rows 2+ are the SQL code broken down into 255-byte chunks.
        ' we need to concatenate rows 2+, replace, and then split them up again

        strSource1 = tmpArrIn(LBound(tmpArrIn))
        strSource2 = ""
        For ii = LBound(tmpArrIn) + 1 To UBound(tmpArrIn)
            strSource2 = strSource2 & tmpArrIn(ii)
        Next ii

        strSource1 = Replace(strSource1, strOldSource, strNewSource)
        strSource2 = Replace(strSource2, strOldSource, strNewSource)

        ReDim tmpArrOut(1 To Int(Len(strSource2) / 255) + 2)
        tmpArrOut(LBound(tmpArrOut)) = strSource1
        For ii = LBound(tmpArrOut) + 1 To UBound(tmpArrOut)
            tmpArrOut(ii) = Mid(strSource2, 255 * (ii - 2) + 1, 255)
        Next ii

        ' if the replacement SQL is invalid, the PivotTable object will throw an error
        Err.Clear
        On Error Resume Next
            pvt.SourceData = tmpArrOut
        On Error GoTo 0
        If Err.Number <> 0 Then
            MsgBox "Problems changing SQL for table " & wsh.Name & "!" & pvt.Name
            pvt.SourceData = tmpArrIn ' revert
        ElseIf pvt.RefreshTable <> True Then
            MsgBox "Problems refreshing table " & wsh.Name & "!" & pvt.Name
        Else
            ' table is now refreshed
            ' need to ensure that the "display name" for each pivot option matches
            ' the actual value that will be fed to the database.  It is possible for
            ' these to get out of sync.
            For Each pvf In pvt.PivotFields
                'pvf.Name = pvf.SourceName
                If Not IsError(pvf.SourceName) Then ' a broken field may have no SourceName
                    mismatches = 0
                    For Each pvi In pvf.PivotItems
                        If pvi.Name <> pvi.SourceName Then
                            mismatches = mismatches + 1
                            pvi.Name = "_mismatch" & CStr(mismatches)
                        End If
                    Next pvi
                    If mismatches > 0 Then
                        For Each pvi In pvf.PivotItems
                            If pvi.Name <> pvi.SourceName Then
                                pvi.Name = pvi.SourceName
                            End If
                        Next
                    End If
                End If
            Next pvf
        End If
    Next pvt
Next wsh

End Sub

Источник

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

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