Как добавить данные в сводную таблицу Excel из другой таблицы
Обновлено: 03.07.2024
После создания сводной таблицы вы можете изменить диапазон ее исходных данных. Например, вы можете расширить исходные данные, чтобы включить больше строк данных. Тем не менее, если исходные данные были существенно изменены (например, количество столбцов увеличилось или уменьшилось), рассмотрите возможность создания новой сводной таблицы.
Вы можете изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек или перейти на другой внешний источник данных.
Нажмите на отчет сводной таблицы.
На вкладке "Анализ" в группе "Данные" нажмите "Изменить источник данных", а затем нажмите "Изменить источник данных".
Откроется диалоговое окно "Изменить источник данных сводной таблицы".
Выполните одно из следующих действий:
Чтобы изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек, нажмите «Выбрать таблицу или диапазон», затем введите первую ячейку в текстовое поле «Таблица/диапазон» и нажмите «ОК».
Чтобы использовать другое подключение, выполните следующие действия:
Нажмите «Использовать внешний источник данных», а затем нажмите «Выбрать подключение».
Отображается диалоговое окно «Существующие подключения».
В раскрывающемся списке Показать в верхней части диалогового окна выберите категорию подключений, для которых вы хотите выбрать подключение, или выберите Все существующие подключения (по умолчанию).
Выберите соединение в списке Выберите соединение, а затем нажмите Открыть. Что делать, если вашего соединения нет в списке?
Примечание. Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или совместно использовать его. Если вы выбираете подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения копируется в книгу как новое подключение к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.
Что делать, если вашего соединения нет в списке?
Если вашего подключения нет в списке в диалоговом окне «Существующие подключения», нажмите «Найти больше», а затем найдите источник данных, к которому вы хотите подключиться, в диалоговом окне «Выбор источника данных». Нажмите «Новый источник», если это необходимо, и следуйте инструкциям мастера подключения к данным, а затем вернитесь в диалоговое окно «Выбор источника данных».
Если ваша сводная таблица основана на соединении с диапазоном или таблицей в модели данных, вы можете выбрать другую таблицу или соединение модели данных на вкладке "Таблицы". Однако, если ваша сводная таблица основана на модели данных рабочей книги, вы не можете изменить источник данных.
Выберите нужное подключение и нажмите «Открыть».
Нажмите «Только создать подключение».
Нажмите "Свойства" и перейдите на вкладку "Определение".
Если ваш файл подключения .odc был перемещен, перейдите к его новому местоположению в поле "Файл подключения".
Если вам нужно изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Нажмите на отчет сводной таблицы.
На вкладке "Параметры" в группе "Данные" нажмите "Изменить источник данных", а затем нажмите "Изменить источник данных".
Откроется диалоговое окно "Изменить источник данных сводной таблицы".
Выполните одно из следующих действий:
Чтобы использовать другую таблицу или диапазон ячеек Excel, нажмите «Выбрать таблицу или диапазон», а затем введите первую ячейку в текстовое поле «Таблица/диапазон».
Можно также нажать "Свернуть диалоговое окно", чтобы временно скрыть диалоговое окно, выбрать начальную ячейку на листе и нажать "Развернуть диалоговое окно" .
Чтобы использовать другое подключение, нажмите «Использовать внешний источник данных», а затем нажмите «Выбрать подключение».
Отображается диалоговое окно «Существующие подключения».
В раскрывающемся списке Показать в верхней части диалогового окна выберите категорию подключений, для которых вы хотите выбрать подключение, или выберите Все существующие подключения (по умолчанию).
Выберите подключение в списке Выберите подключение и нажмите кнопку Открыть.
Примечание. Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или совместно использовать его. Если вы выбираете подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения копируется в книгу как новое подключение к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.
Что делать, если вашего соединения нет в списке?
Если вашего подключения нет в списке в диалоговом окне «Существующие подключения», нажмите «Найти больше», а затем найдите источник данных, к которому вы хотите подключиться, в диалоговом окне «Выбор источника данных». Нажмите «Новый источник», если это необходимо, и следуйте инструкциям мастера подключения к данным, а затем вернитесь в диалоговое окно «Выбор источника данных».
Если ваша сводная таблица основана на соединении с диапазоном или таблицей в модели данных, вы можете выбрать другую таблицу или соединение модели данных на вкладке "Таблицы". Однако, если ваша сводная таблица основана на модели данных рабочей книги, вы не можете изменить источник данных.
Выберите нужное подключение и нажмите «Открыть».
Нажмите «Только создать подключение».
Нажмите "Свойства" и перейдите на вкладку "Определение".
Если ваш файл подключения .odc был перемещен, перейдите к его новому местоположению в поле "Файл подключения".
Если вам нужно изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Вы не можете изменить исходные данные для сводной таблицы в Excel для Интернета. Для этого вам необходимо использовать настольную версию Excel.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
В этом уроке показано, как обновить существующие данные и добавить новые данные в существующую сводную таблицу Excel. Когда вы создаете новую сводную таблицу, Excel либо использует выбранные вами исходные данные, либо автоматически выбирает данные для вас. Но данные часто меняются, а это значит, что вы также должны иметь возможность обновлять свои сводные таблицы, чтобы отражать новые или измененные данные.
Сценарий: у вас есть сводная таблица, содержащая данные о продажах, которые необходимо обновить новыми данными
Чтобы продемонстрировать, как обновлять данные в сводной таблице, давайте рассмотрим пример, который мы использовали в нашем уроке "Как создать сводную таблицу" (ссылка открывается в новом окне), где мы суммировали продажи за несколько месяцев. данные разных продавцов в нашей команде.
Ситуация сейчас такова, что мы получили некоторые дополнительные данные и должны включить их в наш отчет. В частности, нас попросили включить данные о продажах дополнительной линейки товаров (телевизоров) за тот же период времени, что и исходный отчет.
Вот пример данных о продажах, которые мы использовали (обратите внимание на количество строк — очевидно, что в нашем отчете гораздо больше данных о продажах, чем показано здесь):
И вот результирующая сводная таблица:
Измените исходные данные для сводной таблицы
Чтобы изменить исходные данные для сводной таблицы, выполните следующие действия:
Некоторые моменты, которые следует помнить об обновлении данных в сводных таблицах:
- Вам не нужно сортировать данные при обновлении сводной таблицы. В нашем примере мы добавили данные о телевидении в конец существующих данных и не сортировали по дате продажи. Сводная таблица обновилась нормально.
- Вы можете выбрать любой диапазон данных при обновлении сводной таблицы. Мы добавили новые данные в существующую таблицу. С тем же успехом мы могли бы создать новую таблицу данных со всеми нашими данными на другом листе и изменить сводную таблицу, чтобы она указывала на новые данные.
- Обратите внимание: если вы укажете сводную таблицу на новую таблицу, дизайн сводной таблицы может измениться, если в новой таблице данных не будут те же столбцы, что и в исходной таблице данных. Здесь пригодится CTRL+Z, чтобы отменить изменение.
- Если вы используете функцию таблицы Excel, большая часть этого урока не нужна, поскольку Excel использует таблицу в качестве источника данных и автоматически отражает любые изменения таблицы в сводной таблице. Однако вам все равно потребуется обновить сводную таблицу, чтобы включить в нее новые или измененные данные.
- Наконец, вы, возможно, заметили параметр «Использовать внешний источник данных». Это позволяет использовать внешнюю базу данных. Это достаточно сложно и выходит за рамки данного урока. Достаточно сказать, что этот метод обычно гарантирует, что ваша сводная таблица содержит последние данные из вашей базы данных, но, опять же, вам все равно нужно использовать кнопку «Обновить», чтобы обновить сводную таблицу.
Если у вас есть какие-либо комментарии к этому уроку или вопросы о том, как обновить данные в сводных таблицах, не стесняйтесь публиковать их в разделе комментариев ниже.
Вы когда-нибудь использовали функцию ВПР для переноса столбца из одной таблицы в другую? Теперь, когда в Excel есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами данных на основе сопоставления данных в каждой таблице. Затем вы можете создавать листы Power View и создавать сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы взяты из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете импортировать и связать данные аналитики времени, чтобы анализировать модели продаж по годам и месяцам.
Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.
Когда вы импортируете связанные таблицы из реляционной базы данных, Excel часто может создавать эти связи в модели данных, которую он создает в фоновом режиме. Во всех остальных случаях вам потребуется создавать отношения вручную.
Убедитесь, что книга содержит как минимум две таблицы и что в каждой таблице есть столбец, который можно сопоставить со столбцом в другой таблице.
Выполните одно из следующих действий: отформатируйте данные в виде таблицы или импортируйте внешние данные в виде таблицы на новый лист.
Дайте каждой таблице значимое имя. В разделе «Инструменты для таблиц» нажмите «Дизайн» > «Имя таблицы» > введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения данных без дубликатов. Excel может создать связь, только если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиентов с аналитикой времени, обе таблицы должны содержать даты в одном и том же формате (например, 01.01.2012), и по крайней мере в одной таблице (аналитика времени) каждая дата указана только один раз в пределах столбец.
Нажмите Данные > Отношения.
Если Отношения неактивны, ваша книга содержит только одну таблицу.
В поле "Управление отношениями" нажмите "Создать".
В поле "Создать связь" нажмите стрелку рядом с таблицей и выберите таблицу из списка. В отношении «один ко многим» эта таблица должна быть на стороне многих. Используя наш пример с данными о клиентах и времени, вы должны сначала выбрать таблицу продаж клиентов, потому что в любой день может произойти много продаж.
В поле "Столбец (внешний)" выберите столбец, содержащий данные, связанные со связанным столбцом (основным). Например, если бы у вас был столбец даты в обеих таблицах, вы бы выбрали этот столбец сейчас.
В поле "Связанная таблица" выберите таблицу, в которой есть хотя бы один столбец данных, связанный с таблицей, которую вы только что выбрали для таблицы.
Для параметра "Связанный столбец (основной)" выберите столбец с уникальными значениями, совпадающими со значениями в столбце, который вы выбрали для параметра "Столбец".
Подробнее о связях между таблицами в Excel
Примечания о взаимоотношениях
Вы узнаете, существует ли связь, перетащив поля из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, в Excel уже есть информация о связи, необходимая для связи данных.
Создание взаимосвязей похоже на использование ВПР: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel мог сопоставлять строки в одной таблице со строками в другой таблице. В примере с логикой операций со временем таблица Customer должна иметь значения даты, которые также существуют в таблице аналитики операций со временем.
В модели данных отношения между таблицами могут быть "один к одному" (каждый пассажир имеет один посадочный талон) или "один ко многим" (на каждом рейсе много пассажиров), но не "многие ко многим". Отношения «многие ко многим» приводят к ошибкам циклической зависимости, например «Обнаружена циклическая зависимость». Эта ошибка возникает, если вы устанавливаете прямое соединение между двумя таблицами, которые являются многими ко многим, или косвенные соединения (цепочка отношений таблиц, которые являются один ко многим внутри каждого отношения, но многие ко многим при просмотре в конце). до конца. Узнайте больше о связях между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Дополнительные сведения см. в разделе Типы данных в моделях данных Excel.
Другие способы создания взаимосвязей могут быть более интуитивно понятными, особенно если вы не уверены, какие столбцы использовать. См. раздел Создание связи в представлении схемы в Power Pivot.
Пример: сопоставление данных аналитики времени с данными о рейсах авиакомпаний
Вы можете узнать как о связях между таблицами, так и об аналитике времени, используя бесплатные данные в Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики и требуют быстрого подключения к Интернету, чтобы завершить загрузку данных за разумный период времени.
Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace.Домашняя страница Microsoft Azure Marketplace откроется в мастере импорта таблиц.
В разделе "Цена" нажмите "Бесплатно".
В разделе "Категория" нажмите "Наука и статистика".
Найдите DateStream и нажмите «Подписаться».
Войдите в свою учетную запись Microsoft и нажмите «Войти». В окне должен появиться предварительный просмотр данных.
Прокрутите вниз и нажмите "Выбрать запрос".
Нажмите "Далее".
Выберите BasicCalendarUS, а затем нажмите Готово, чтобы импортировать данные. При быстром интернет-соединении импорт займет около минуты. Когда закончите, вы должны увидеть отчет о состоянии 73 414 переданных строк. Нажмите Закрыть.
Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace, чтобы импортировать второй набор данных.
В разделе "Тип" нажмите "Данные".
В разделе "Цена" нажмите "Бесплатно".
Найдите задержки рейсов авиаперевозчика США и нажмите «Выбрать».
Прокрутите вниз и нажмите "Выбрать запрос".
Нажмите "Далее".
Нажмите «Готово», чтобы импортировать данные. При быстром подключении к Интернету импорт может занять 15 минут. По завершении вы должны увидеть отчет о состоянии 2 427 284 переданных строк. Щелкните Закрыть. Теперь у вас должно быть две таблицы в модели данных. Чтобы связать их, нам потребуются совместимые столбцы в каждой таблице.
Обратите внимание, что DateKey в BasicCalendarUS имеет формат 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени, FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одного типа, и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих нескольких шагах вы будете использовать эти столбцы для связи таблиц.
В окне Power Pivot нажмите "Сводная таблица", чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните On_Time_Performance и щелкните ArrDelayMinutes, чтобы добавить его в область значений. В сводной таблице вы должны увидеть общее время задержки рейсов, выраженное в минутах.
Разверните BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
Обратите внимание, что в сводной таблице теперь перечислены месяцы, но общее количество минут одинаково для каждого месяца. Повторяющиеся одинаковые значения указывают на необходимость связи.
В списке полей в разделе «Могут потребоваться связи между таблицами» нажмите «Создать».
В связанной таблице выберите On_Time_Performance, а в связанном столбце (основной) выберите FlightDate.
В таблице выберите BasicCalendarUS, а в столбце (иностранный) выберите DateKey. Нажмите OK, чтобы создать связь.
Обратите внимание, что сумма задержек в минутах теперь меняется для каждого месяца.
В BasicCalendarUS перетащите YearKey в область строк над MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам или другим значениям в календаре.
Советы. По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot можно изменить сортировку, чтобы месяцы отображались в хронологическом порядке.
Убедитесь, что таблица BasicCalendarUS открыта в окне Power Pivot.
В таблице "Главная" нажмите "Сортировать по столбцу".
В разделе «Сортировка» выберите «Месяц в календаре».
В поле "По" выберите "МесяцГода".
Сводная таблица теперь сортирует каждую комбинацию месяц-год (октябрь 2011 г., ноябрь 2011 г.) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что фид DateStream предоставляет все необходимые столбцы, чтобы этот сценарий работал. Если вы используете другую таблицу операций со временем, ваш шаг будет другим.
"Могут потребоваться связи между таблицами"
При добавлении полей в сводную таблицу вы будете проинформированы о том, требуется ли связь между таблицами для понимания полей, выбранных вами в сводной таблице.
Хотя Excel может сообщить вам, когда требуется связь, он не может сказать, какие таблицы и столбцы использовать, и возможна ли вообще связь между таблицами. Попробуйте выполнить следующие действия, чтобы получить ответы, которые вам нужны.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего несколько таблиц, может быть сразу понятно, какие из них вам нужно использовать. Но для более крупных моделей вам, вероятно, может понадобиться помощь. Один из подходов — использовать представление схемы в надстройке Power Pivot. Представление диаграммы обеспечивает визуальное представление всех таблиц в модели данных. Используя представление диаграммы, вы можете быстро определить, какие таблицы отделены от остальной части модели.
Примечание. Можно создать неоднозначные отношения, которые недопустимы при использовании в сводной таблице или отчете Power View.Предположим, что все ваши таблицы каким-то образом связаны с другими таблицами в модели, но при попытке объединить поля из разных таблиц вы получаете сообщение «Могут потребоваться связи между таблицами». Наиболее вероятная причина заключается в том, что вы столкнулись с отношениями «многие ко многим». Если вы проследите цепочку взаимосвязей таблиц, которые соединяются с таблицами, которые вы хотите использовать, вы, вероятно, обнаружите, что у вас есть две или более связи таблиц «один ко многим». Не существует простого обходного пути, подходящего для любой ситуации, но вы можете попробовать создать вычисляемые столбцы, чтобы объединить нужные столбцы в одну таблицу.
Шаг 2. Найдите столбцы, которые можно использовать для создания пути от одной таблицы к другой
После того как вы определили, какая таблица не связана с остальной частью модели, просмотрите ее столбцы, чтобы определить, содержит ли другой столбец в другом месте модели совпадающие значения.
Например, предположим, что у вас есть модель, которая содержит данные о продажах продуктов по территории, и вы впоследствии импортируете демографические данные, чтобы выяснить, существует ли корреляция между продажами и демографическими тенденциями в каждой территории. Поскольку демографические данные поступают из другого источника данных, его таблицы изначально изолированы от остальной части модели. Чтобы интегрировать демографические данные с остальной частью вашей модели, вам нужно найти столбец в одной из демографических таблиц, соответствующий столбцу, который вы уже используете. Например, если демографические данные организованы по регионам, а ваши данные о продажах указывают, в каком регионе произошла продажа, вы можете связать два набора данных, найдя общий столбец, такой как «Штат», «Почтовый индекс» или «Регион», для обеспечения поиска.
Помимо сопоставления значений, существует несколько дополнительных требований для создания связи:
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликатов. В модели данных нули и пустые строки эквивалентны пробелу, который является отдельным значением данных. Это означает, что в столбце поиска не может быть нескольких пустых значений.
Типы данных исходного столбца и столбца подстановки должны быть совместимы. Дополнительные сведения о типах данных см. в разделе Типы данных в моделях данных.
Чтобы узнать больше о связях между таблицами, см. раздел Отношения между таблицами в модели данных.
Мы можем добавить данные в сводную таблицу в Excel с помощью параметра "Изменить источник данных". «Изменить источник данных» находится в «Параметры» или «Анализ» в зависимости от нашей версии Excel. В приведенных ниже шагах описан процесс добавления данных в сводную таблицу в Excel.
Рисунок 1. Как добавить данные в сводную таблицу в Excel
Настройка данных
- Мы создадим сводную таблицу с данными, показанными на рис. 2.
Рисунок 2. Настройка данных
Создание таблицы данных
- Перед созданием таблицы мы поместим данные в таблицу
- Мы нажмем на любую часть данных
- Мы нажимаем на вкладку «Вставка» и нажимаем «Таблица».
Рисунок 3. Щелчок по таблице
Рисунок 4. Диалоговое окно "Создать таблицу"
- Мы нажмем OK, чтобы создать таблицу.
< бр />
Рисунок 5. Созданная таблица
- Мы щелкнем поле «Имя таблицы» под файлом и назовем таблицу Sales_Data, как показано на рисунке 5.
Создание сводной таблицы
- Мы нажмем на любую часть нашей таблицы
- Мы нажмем на сводную таблицу, как показано на рисунке 3.
Рис. 6. Диалоговое окно "Создать сводную таблицу"
- Мы хотим, чтобы наша сводная таблица находилась на существующем рабочем листе. Мы выберем этот вариант. Мы также укажем расположение сводной таблицы на текущем листе как E3
- Мы нажмем "ОК".
Рис. 7. Созданная сводная таблица с указанными полями
Добавление данных в сводную таблицу
Мы скопируем и вставим данные некоторых дополнительных филиалов и их продаж в наши данные в таблице
Если мы щелкнем правой кнопкой мыши сводную таблицу и нажмем «Обновить», чтобы обновить данные, ничего не произойдет. Мы можем обновить сводную таблицу новыми данными, нажав «Анализ», а затем «Изменить источник данных».
Рисунок 9. Изменение источника данных
- Мы выберем диапазон, который хотим проанализировать, и нажмем ОК.
Рис. 10. Диалоговое окно "Изменить источник данных сводной таблицы"
- Мы получим приглашение. Мы нажмем Да
Рис. 11. Подсказка
Рис. 12. Добавление данных в сводную таблицу
Мгновенное подключение к эксперту через нашу службу Excelchat
В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.
Читайте также: