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

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

Возможность анализировать все данные может помочь вам принимать более лучшие бизнес-решения. Но иногда трудно понять, с чем начать, особенно при наличии большого объема данных, хранящихся за пределами Excel, например в базе данных Microsoft Access или Microsoft SQL Server или в файле куба OLAP. В этом случае вы подключитесь к внешнему источнику данных, а затем создадите сводную таблицу для суммирования, анализа, изучения и представления данных.

Вот как создать сводную таблицу с помощью существующего подключения к внешним данным:

1. Щелкните любую ячейку на листе.
2. Выберите Вставка >Сводная таблица.
3. В диалоговом окне Создание сводной таблицы щелкните Из внешнего источника данных.
4. Выберите вариант Выбрать подключение.
5. На вкладке «Подключения» в окне «Показать» оставьте флажок «Все подключения» или выберите категорию подключения с источником данных, к которому вы хотите подключиться.

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

1. В списке подключений выберите нужное подключение и нажмите кнопку » Открыть».
2. В разделе «Выбор места размещения отчета сводной таблицы» выберите расположение.
   • Чтобы поместить сводную таблицу на новый лист, начиная с ячейки A1, выберите » Создать лист».
   • Чтобы поместить сводную таблицу на активный лист, выберите «Существующий лист«, а затем в поле «Расположение» введите ячейку, в которой будет запущена сводная таблица.
3. Нажмите кнопку ОК. Excel добавляет пустую сводную таблицу и отображает список полей, чтобы можно было отобразить нужные поля и изменить их порядок для создания собственного макета.
4. В разделе списка полей установите флажок рядом с именем поля, чтобы поместить поле в область по умолчанию в разделе областей списка полей. Как правило, в область «Строки» добавляются нечисловые поля, в область «Значения» добавляются числовые поля, а в область «Столбцы» — поля даты и времени. При необходимости поля можно переместить в другую область.

Совет: Можно также щелкнуть правой кнопкой мыши имя поля, а затем выбрать «Добавить в фильтр отчета«, «Добавить в метки столбцов «, «Добавить в метки строк» или «Добавить в значения«, чтобы поместить поле в эту область раздела областей, или перетащить поле из раздела поля в область в разделе областей.

Подключение к новому внешнему источнику данных

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

1. Щелкните «>из других источников».
2. Щелкните нужное подключение.
   • Щелкните «SQL Server«, чтобы создать подключение к SQL Server таблице.
   • Щелкните «Из служб Analysis Services«, чтобы создать подключение к SQL Server analysis.
3. В мастере подключения к данным выполните действия по установке подключения.
   • На странице 1 введите сервер базы данных и укажите способ входа на сервер.
   • На странице 2 введите базу данных, таблицу или запрос, содержащий нужные данные.
   • На странице 3 введите файл подключения, который вы хотите создать.

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

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

В диалоговом окне «Выбор таблицы» выберите таблицу и нажмите кнопку «ОК «.

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

В диалоговом окне «Импорт данных» выберите способ просмотра данных в книге и место их размещения, а затем нажмите кнопку «ОК «.

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

Дополнительные сведения о сводных таблицах

• Создание сводной таблицы для анализа данных на листе
• Создание сводной таблицы для анализа данных в нескольких таблицах
• Упорядочение полей сводной таблицы с помощью списка полей
• Изменение диапазона исходных данных для сводной таблицы
• Обновление данных в сводной таблице
• Удаление сводной таблицы

Добавление данных с листа в модель данных с помощью связанной таблицы

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

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

Чтобы связать данные с таблицей, выполните указанные ниже действия.

1. Вы можете выбрать диапазон строк и столбцов, которые вы хотите использовать в связанной таблице.
2. Отформатирование строк и столбцов в виде таблицы
   • На ленте >в формате таблицы, а затем выберите стиль таблицы. Вы можете выбрать любой стиль, но не забудьте выбрать таблицу с заглавами. Если в таблице нет заглавных таблиц, можно создать их прямо сейчас. В противном Excel будут использовать произвольные имена (столбец1, столбец2 и так далее), которые не передают осмысленные сведения о содержимом столбца.
   • Присвойте таблице имя. В окне Excel нажмите кнопку Конструктор средств работы с таблицами. В группе Свойства введите имя таблицы.
3. Поместите курсор на любую ячейку таблицы.
4. Щелкните Power Pivot >добавить в модель данных, чтобы создать связанную таблицу. В окне Power Pivot вы увидите таблицу со значком ссылки, указывающим на то, что таблица связана с таблицей-источником в Excel:
5. Если модель уже содержит таблицы, то нужно сделать еще один шаг. Необходимо создать связь между новой таблицей, которую вы только что добавили, и другими таблицами в модели. Инструкции см. в таблицах Создание связи между двумя таблицами и Создание связей в представлении диаграммы.

Если книга ранее не содержала ее, теперь она содержит модель данных. Модель создается автоматически при создании связи между двумя таблицами или нажатии кнопки Добавить в модель данных в Power Pivot. Дополнительные сведения см. в этойExcel.

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

Имена таблиц являются исключением. Если переименовать таблицу в Excel, потребуется обновить ее вручную в Power Pivot.

Совет: Дополнительные сведения о связанных таблицах см. в учебнике Импорт данных в Excel и Создание модели данных.

Синхронизация изменений между таблицей и моделью

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

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

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

1. Убедитесь, что книга со связанной таблицей открыта в Excel.
2. Откройте окно Power Pivot.
3. На вкладке внизу щелкните связанную таблицу. Любая связанная таблица обозначена маленьким значком ссылки рядом с ее именем.
4. В верхней части ленты щелкните Связанная таблица.
5. В режиме обновления выберите Вручную или Автоматически. По умолчанию этот режим является автоматическим. Если переключиться на ручное, обновления будут происходить только при использовании команд Обновить все или Обновить выбранные на ленте Связанной таблицы в окне Power Pivot или команды Обновить все на ленте Power Pivot в Excel.

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

При работе со сводной таблицей в программе эксель, довольно часто требуется в неё добавлять новые данные. Данная процедура несложная с одной стороны, но с другой стороны бывают по ней и вопросы. Поэтому давайте рассмотрим пошаговую инструкцию, как это можно сделать.

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

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

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

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

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

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

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

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

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

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

При этом создается сводная таблица на основе существующей таблицы или диапазона.

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

Сводные таблицы из других источников

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

Примечание: В зависимости от ит-параметров вашей организации в списке может отображаться имя вашей организации. Например, «Из Power BI (Майкрософт)».

Получение из внешнего источника данных

Получение из модели данных

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

Получение из Power BI

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

Настройка сводной таблицы

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

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

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

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

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

Работа с значениями сводной таблицы

По умолчанию поля сводной таблицы, размещенные в области Значения , отображаются в виде СУММ. Если Excel интерпретирует данные как текст, данные отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выберите параметр Параметры поля значения .

Затем измените функцию в разделе Операция. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например «Sum of FieldName», но вы можете изменить его. Если выбран параметр Числовой формат, можно изменить числовой формат для всего поля.

Совет: Так как при изменении вычисления в разделе Суммирование значений по изменяется имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы до завершения настройки сводной таблицы. Один из способов заключается в использовании функции Поиска & Замены (CTRL+H) >Найти то, что > «Сумма«, а затем заменить на > оставить пустым, чтобы заменить все сразу, а не вручную повторно.

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

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

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

Отображение значения как результата вычисления и как процента

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

Вставка сводной таблицы

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

• В карта Создание собственной сводной таблицы выберите Новый лист или Существующий лист, чтобы выбрать назначение сводной таблицы.
• В рекомендуемой сводной таблице выберите Новый лист или Существующий лист, чтобы выбрать назначение сводной таблицы.

Примечание: Рекомендуемые сводные таблицы доступны только подписчикам Microsoft 365.

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

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

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

Получение из Power BI

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

Работа с областью «Поля сводной таблицы»

В области Поля сводной таблицы выберите поле проверка для любого поля, которое вы хотите добавить в сводную таблицу.

По умолчанию нечисловые поля добавляются в область Строк , поля даты и времени добавляются в область Столбцы , а числовые — в область Значения .

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

Работа с значениями сводной таблицы

По умолчанию поля сводной таблицы в области Значения отображаются в виде СУММ. Если Excel интерпретирует данные как текст, они отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений.

Измените вычисление по умолчанию, щелкнув правой кнопкой мыши любое значение в строке и выбрав параметр Суммировать значения по .

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

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

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

Сделайте выбор из списка.

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

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

При добавлении новых данных в источник данных сводной таблицы необходимо обновить все сводные таблицы, созданные на основе этого источника данных. Щелкните правой кнопкой мыши в любом месте диапазона сводной таблицы и выберите обновить.

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

Если вы создали сводную таблицу и решили, что она больше не нужна, выберите весь диапазон сводной таблицы и нажмите кнопку Удалить. Это не повлияет на другие данные, сводные таблицы или диаграммы вокруг них. Если сводная таблица находится на отдельном листе, где нет других данных, которые вы хотите сохранить, удаление листа — это быстрый способ удаления сводной таблицы.

Перед началом работы

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

Создание с помощью рекомендуемой сводной таблицы

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

1. Выберите ячейку в диапазоне исходных данных или таблицы.
2. На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.

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

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

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

1. Выберите ячейку в диапазоне исходных данных или таблицы.
2. На вкладке Вставка нажмите кнопку Сводная таблица.

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

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

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

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

Значения в сводной таблице

• Суммировать по По умолчанию поля сводной таблицы, размещенные в области Значения , отображаются в виде СУММ. Если Excel интерпретирует данные как текст, данные отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выбрав параметр Параметры поля . Затем измените функцию в разделе Суммировать по. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например «Sum of FieldName», но вы можете изменить его. При выборе параметра Число. можно изменить формат чисел для всего поля.

Совет: Так как изменение вычисления в разделе Суммирование по изменяет имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы, пока не завершите настройку сводной таблицы. Один из способов заключается в том, чтобы выбрать Заменить (в меню Правка) >Найти, что > «Сумма«, а затем заменить на > оставить пустым, чтобы заменить все сразу, а не вручную повторно.

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

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

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

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

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

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

Сводная таблица на iPad постепенно развертывается для пользователей, использующих Excel на iPad версии 2.80.1203.0 и выше.

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

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

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

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

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

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

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

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

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

Советы и рекомендации по форматированию данных

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

Отформатируйте данные как таблицу Excel (выберите в любом месте данных, а затем на ленте выберите Вставить > таблицу ).

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

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

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

Microsoft Privacy Policy

Рекомендации сводной таблицы являются частью работы с подключением в Microsoft 365 и анализируют данные с помощью служб искусственного интеллекта. Если вы решите отказаться от работы с подключением в Microsoft 365, данные не будут отправляться в службу искусственного интеллекта, и вы не сможете использовать рекомендации сводной таблицы. Дополнительные сведения см. в заявлении о конфиденциальности Майкрософт.