Как добавить данные в сводную таблицу

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

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

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

Изменение исходных данных

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

Диалоговое окно

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

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

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

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

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

Что делать, если подключение отсутствует в списке?

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

Диалоговое окно

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

Вкладка

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

Изменение исходных данных

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

Что делать, если подключение отсутствует в списке?

Диалоговое окно

Вкладка

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

Изменение исходных данных

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

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

Что делать, если подключение отсутствует в списке?

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как добавить данные в сводную таблицу MS Excel

В создании этой статьи участвовала наша опытная команда редакторов и исследователей, которые проверили ее на точность и полноту.

Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.

Количество просмотров этой статьи: 23 816.

В этой статье:

В этой статье мы расскажем вам, как добавить данные в существующую сводную таблицу Excel. Это можно сделать на компьютерах под управлением Windows и Mac OS X.

Откройте сводную таблицу Excel. Дважды щелкните по файлу Excel со сводной таблицей, чтобы открыть ее.

Перейдите на лист с нужными данными. Щелкните по листу (например, по «Лист2») внизу окна Excel.
- Например, если данные находятся в ячейках с A1 по E10, введите дополнительные данные в столбце «F» или в строке «11».
- Если вы просто хотите изменить существующие данные, сделайте это.
Вернитесь на лист со сводной таблицей. Щелкните по листу (например, по «Лист1») внизу окна Excel.

Выберите сводную таблицу. Для этого нажмите на нее.
- На компьютере Mac перейдите на вкладку «Анализ сводной таблицы».
Нажмите Изменить источник данных . Эта опция находится в разделе «Данные» панели инструментов «Анализ». Раскроется меню.

Нажмите Изменить источник данных . Эта опция находится в меню. Откроется окно.

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

Щелкните по OK . Эта кнопка находится внизу окна.
- Если вы добавили новый столбец в сводную таблицу, отметьте его в правой части окна Excel, чтобы отобразить его.
- Сохраните сводную таблицу, прежде чем вносить в нее изменения, чтобы вернуться к сохраненной версии, если что-то пойдет не так.
Дополнительные статьи

преобразовать текстовый файл (TXT) в файл Excel (XLSX)

добавить вторую ось Y на график в Microsoft Excel

открыть защищенный паролем файл Excel

обрезать текст в Excel

снять фильтр в Excel

добавить столбец в сводную таблицу (Pivot Table)

вычислить стандартную ошибку среднего в Excel

связать листы в Excel

сортировать ячейки по алфавиту в Excel
изменить регистр букв в Excel

создать конвертер валют в Excel

создать круговую диаграмму в Excel

обновить Excel

вычислить стандартное отклонение в Excell

Об этой статье

Штатный автор wikiHow

В создании этой статьи участвовала наша опытная команда редакторов и исследователей, которые проверили ее на точность и полноту.

Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества. Количество просмотров этой статьи: 23 816.

Как сделать сводную таблицу, сгруппировать временной ряд?

Сводная таблица в Excel — это мощнейший инструмент для анализа данных, который поможет вам быстро:
- Подготовить данные для отчетов;
- Рассчитать различные показатели;
- Сгруппировать данные;
- Отфильтровать и проанализировать интересующие показатели.
А также сэкономить вам кучу времени.

Из данной статьи вы узнаете:
- Как сделать сводную таблицу;
- Как с помощью сводной таблицы сгруппировать временные ряды и оценить данные в динамике по годам, кварталам, месяцам, дням.
- Как рассчитать прогноз с помощью сводной таблицы и Forecast4AC PRO;
Для начала научимся делать сводные таблицы.

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

И в каждой строке 3-м параметра связаны между собой, т.е. например, 01.02.2010 года Товар 1 продали на 422 656 руб.

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

Появится диалоговое окно, в котором:
- вы можете сразу нажать кнопку «ОК», и сводная таблица выведется в отдельный лист.
- а можете настроить параметры вывода данных сводной таблицы:
1. Диапазон с данными, которые будут выведены в сводную таблицу;
2. Куда вывести сводную (в новый лист или на существующий (если выберите на существующий, то необходимо будет указать ячейку, в которую вы хотите поместить сводную таблицу)).
Нажимаем «ОК», сводная таблица готова и выведена в новый лист. Назовем лист «Сводная».
- В правой части листа вы увидите поля и области, с которыми вы сможете работать. Поля вы можете перетащить в области и они выведутся в сводную таблицу на лист.
- В левой части листа сводная таблица.
Теперь, зажимаем левой кнопкой мыши поле «Товар» — перетаскиваем его в «Название строк», поле «Продажи в руб.» — в «Значения» в сводной таблице. Таким образом мы получили сумму продаж по товарам за весь период:

Группировка и фильтрация временных рядов в сводной таблице

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

Для этого переходим в лист «Данные», и после даты вставляем 3 пустых столбца. Выделяем столбец «Товар» и нажимаем «Вставить».

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

Вставленные столбцы называем «Год», «Месяц», «Год-Месяц».

Теперь в каждый из этих столбцов добавляем соответствующую формулу для получения интересующего параметра времени:
- В столбец «Год» добавляем формулу =ГОД(со ссылкой на дату);
- В столбец «Месяц» добавляем формулу =МЕСЯЦ(со ссылкой на дату);
- В столбец «Год — Месяц» добавляем формулу =СЦЕПИТЬ(ссылка на год;» «;ссылка на месяц).
Получаем 3 столбца с годом, месяцем и годом и месяцем:

Теперь переходим в лист «Сводная», устанавливаем курсор на сводную таблицу, вызываем правой кнопкой мыши меню и нажимаем кнопку «Обновить». После обновления в списке полей у нас появляются новые поля сводной таблицы «Год», «Месяц», «Год — месяц», которые мы добавили в простую таблицу с данными:

Теперь давайте проанализируем продажи по годам.

Для этого поле «Год» мы перетаскиваем в «название столбцов» сводной таблицы. Получаем таблицу с продажами по товарам по годам:

Теперь мы хотим еще более глубже «опуститься» на уровень месяцев и проанализировать продажи по годам и по месяцам. Для этого в «название столбцов» перетаскиваем поле «месяц» под год:

Для анализа динамики месяцев по годам, можем месяцы переместить в область сводной «Название строк» и получить следующий вид сводной таблицы:

В данном представлении сводной таблицы мы видим:
- продажи по каждому товару в сумме за целый год (строка с названием товара);
- более подробно продажи по каждому товару в каждом месяце в динамике за 4 года.
Следующая задача, мы хотим убрать из анализа продажи за какой-то месяц (например, октябрь 2012 года), т.к. данные о продажах у нас еще не за полный месяц.
Для этого в область сводной «Фильтр отчета» перетащим «Год — месяц»

Нажимаем на появившейся над сводной фильтр и ставим галочку «Выделить несколько элементов». Затем в списке с годами и номерами месяцев снимаем галочку с 2012 10 и нажимаем ОК.

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

Расчет проноза с помощью сводной таблицы и Forecast4AC PRO

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

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

Для расчета прогноза с помощью Forecast4AC PRO устанавливаем курсор в 1 января 2009 года

и нажимаем кнопку «График Модель прогноза» в меню Forecast4AC PRO

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

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite — автоматический расчет прогноза в Excel .
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Настроить сводную таблицу

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

• Конструктор – с помощью данной вкладки определяется структура (внешний вид) сводной таблицы.

• Параметры – содержит параметры сводной таблицы. В текущей версии приложения в данной вкладке доступно только редактирование источника данных сводной таблицы.

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

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

Панель настроек можно также открыть и закрыть вручную.

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

Чтобы закрыть панель вручную, выполните одно из следующих действий:

• В верхней части панели нажмите кнопку .

• На боковой панели нажмите кнопку ( Сводная таблица ).

Чтобы открыть панель вручную, выполните следующие действия:

1. Выделите весь диапазон или одну/несколько ячеек из диапазона сводной таблицы.

2. Откройте панель одним из следующих способов:

• Выберите пункт командного меню Данные > Настроить сводную таблицу .

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

• На боковой панели нажмите кнопку ( Сводная таблица ) .

• Щелчком правой кнопки мыши откройте контекстное меню и выполните команду Настроить сводную таблицу .

Настроить сводную таблицу

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

Вкладка Конструктор содержит следующие области:

• Доступные поля – в этой области содержится список всех выбранных столбцов исходной таблицы (далее – полей ). Чтобы определить структуру сводной таблицы, добавьте поля из области Доступные поля в области Строки , Столбцы , Значения и Фильтры .

• Строки – добавьте в эту область поля, данные из которых будут являться заголовками строк сводной таблицы.

• Столбцы – добавьте в эту область поля, данные из которых будут являться заголовками столбцов сводной таблицы.

• Значения – добавьте в эту область поля, значения которых будут использоваться для расчетов.

• Фильтры – при необходимости добавьте в эту область поля, которые будут использоваться для фильтрации данных в сводной таблице. Фильтры помогают задать уровень детализации данных и меняют внешний вид таблицы.

Поля из области Доступные поля можно добавить в области Строки и Значения вручную или автоматически. Если поля добавляются автоматически, то они распределяются между областями Строки и Значения по типу данных:

• Если поле содержит текстовые значения, то оно перемещается в область Строки .

• Если поле содержит числовые значения, то оно перемещается в область Значения .

Чтобы автоматически распределить поля между областями Строки и Значения , последовательно выполните для каждого из данных полей следующие действия:

1. В области Доступные поля наведите курсор мыши на название поля, которое требуется переместить в область Строки или Значения .

2. Нажмите кнопку ( Добавить ).

Чтобы добавить поля в области Строки и Значения вручную, перетащите их в соответствующие области, удерживая левую кнопку мыши, или последовательно выполните для каждого поля следующие действия:

1. В области Доступные поля наведите курсор мыши на поле, которое требуется добавить в область Строки или Значения .

2. Нажмите кнопку ( Еще ).

3. В отобразившемся меню выберите область, в которую необходимо добавить поле.

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

Добавление полей из области Доступные поля в другие области выполняется вручную, аналогично добавлению полей в области Строки и Значения .

Переместить поле из одной области в другую

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

Изменить порядок расположения полей в области

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

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

1. Наведите курсор мыши на поле и нажмите кнопку ( Еще ).

2. В выпадающем меню выберите требуемую команду:

• Переместить в начало – переместить поле в начало списка;

• Переместить выше – поменять местами выбранное и предыдущее поле;

• Переместить ниже – поменять местами выбранное и последующее поле;

• Переместить в конец – переместить поле в конец списка.

Состав команд выпадающего меню определяется местоположением выбранного поля в списке.

Редактировать функцию в области «Значения»

Для поля, которое добавляется в область Значения , функция расчета данных выбирается автоматически:

• Если поле содержит числовые данные, выбирается функция Сумма .

• Если поле содержит текстовые данные, выбирается функция Количество .

Чтобы изменить способ расчета данных для поля, выполните следующие действия:

1. В области Значения разверните выпадающий список функций, соответствующий данному полю.

2. Выберите в выпадающем списке требуемую функцию.

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

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

Чтобы удалить поле из какой-либо области сводной таблицы, воспользуйтесь одним из следующих способов:

• Наведите курсор мыши на название данного поля и нажмите кнопку ( Удалить ).

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

Чтобы удалить поле из всех областей сводной таблицы, в которые оно добавлено, выполните следующие действия:

1. В области Доступные поля наведите курсор мыши на название данного поля.

2. Нажмите кнопку (Удалить из сводной таблицы ).

Редактировать источник данных

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

1. На панели настроек сводной таблицы выберите вкладку Параметры .

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

3. Редактируйте значение в поле Источник данных и нажмите кнопку или клавишу Enter на клавиатуре.
Похожие публикации: