Как добавить данные в таблицу в excel из другой таблицы

Как добавить данные в таблицу в excel из другой таблицы

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

Пользователь
Сообщений: 15 Регистрация: 04.05.2017
21.07.2017 13:25:45

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

Нужно (хотелось бы) сделать это автоматически для большой таблицы.

С функцией СМЕЩ и ПОВТОР по-отдельности все кое-как работает, а в результате получается какая-то мутотень, потому что приходится вносить правку в адреса каждой строки таблицы «Сроки» вручную.

Прикрепленные файлы

• Таблица.xlsx (14.8 КБ)

Изменено: Лыцарь Конный — 21.07.2017 14:07:31
Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
21.07.2017 13:53:47

Для названия так можно:
=ИНДЕКС(Список!$B$2:$B$26;НАИМЕНЬШИЙ(ЕСЛИ(Список!$B$2:$B$26<>«»;СТРОКА(Список!$B$2:$B$26)-1);СТРОКА()-1))
А вот с датами даже не знаю.
В ячёйке С3две даты. Как их выводить на лист сроки?

Изменено: Bema — 21.07.2017 13:55:30

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

Использование нескольких таблиц для создания сводной таблицы

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

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

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

Ниже приведены три основных шага для добавления нескольких таблиц в список полей сводной таблицы.

Шаг 1. Импорт связанных таблиц из базы данных

Импортируйте их из реляционной базы данных, например Microsoft SQL Server, Oracle или Access. Вы можете импортировать несколько таблиц одновременно:

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

Шаг 2. Добавление полей в сводную таблицу

Обратите внимание: список полей содержит несколько таблиц.

Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ. Вы можете:

• Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».
• Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.

Шаг 3. Создание связей при необходимости

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

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

Примечание Модели данных не поддерживаются в Excel для Mac.

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

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

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

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

Нажмите кнопку Открыть, а затем — ОК, чтобы отобразить список полей, содержащий все таблицы в модели.

См. также

• Создание модели данных в Excel
• Получение данных с помощью надстройки Power Pivot
• Упорядочение полей сводной таблицы с помощью списка полей
• Создание сводной таблицы для анализа данных на листе
• Создание сводной таблицы для анализа внешних данных
• Создание сводной таблицы, подключенной к наборам данных Power BI
• Изменение диапазона исходных данных для сводной таблицы
• Обновление данных в сводной таблице
• Удаление сводной таблицы

Как добавить данные в таблицу в excel из другой таблицы

Добрый день!
прошу помочь с такой задачей: в таблицах несколько десятков строчек.
Необходимо дополнить таб.1 данными из таб.2
Строчки в таблицах не все, оставил немного, лишь для примера.
Условие:
Необходимо найти совпадение в массиве данных и присвоить значения нескольких ячеек совпавшей строки от одной таблицы к другой.

условие: если B5 из таб.1 = знач. из массива D6:D9 (таб.2) то вставить строчку (все 32 ячейки таб.2) в соответствующую строчку столбца L таб.1.
Похоже на пример с этого же сайта про функцию «ВПР» с ценами и яблоками но у меня не заработало.
http://www.planetaexcel.ru/techniques/2/106/

Прикрепленные файлы

• таб.2.xlsx (13.27 КБ)
• таб.1.xlsx (23.15 КБ)

Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
21.11.2016 15:18:09

Не уверен, что правильно понял вопрос, но если вы пытаетесь решить задачу при помощи ВПР и скомое значение у Вас, например АС01001, то в таблице 1 и таблице 2, AC01001<>АС01001. Возможно именно по этой причине и не работает ВПР. Правильнее сказать- это одна из причин по которой не будет работать ВПР.

Изменено: Bema — 21.11.2016 15:21:32

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

Пользователь
Сообщений: 5 Регистрация: 21.11.2016
21.11.2016 15:39:24

а в чем я не прав?
я ищу совпадения значений ячейки в столбце «В» (таб.1) со значением ячейки «D» (таб.2)
чтобы потом присвоить ячейке столбца «P» (таб.1) значение из ячейки столбца «G» (таб.2).
как и в примере с «яблоком» — в обеих таблицах ищем значение «яблоко».
разве нет?

Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
21.11.2016 15:53:56

Все верно, но в таблице 1 в столбце В значение AC01001<>значению АС01001 в столбце D таблицы 2. Хотя визуально значения равны. Они у Вас написаны английскими и русскими буквами. В этом случае ВПР не найдет совпадение и выдаст ошибку.

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

Пользователь
Сообщений: 5 Регистрация: 21.11.2016
21.11.2016 16:13:12

растиражировал из одного источника, чтобы исключить «раскладки», ошибка изменилась с «Н/Д» на «ССЫЛКА»
.
причем сейчас уже пробую не из разных книг, а перенес обе таблицы на один Лист одного документа.

Изменено: atorgov — 21.11.2016 16:16:50
Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
21.11.2016 16:37:44
atorgov, залейте новый файл, пожалуйста.

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

Пользователь
Сообщений: 5 Регистрация: 21.11.2016
21.11.2016 16:48:51

обе таблицы разместил на одном листе в док. (таб.1).
сама таблица (таб.2) начинается на Листе1 со столбцев «АХ»
желтым выделил участвующие ячейки
как было указано в примере с «яблоком» присвоил собственное имя массиву с данными в таб.2 по адресу (=Лист1!$AY$5:$BB$8) под названием «номлин».
. что еще можно добавить?

Прикрепленные файлы

• таб.1.xlsx (26.27 КБ)

Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
21.11.2016 16:55:37

А так?
=ВПР(B5;номлин;4;0)
В качестве третьего аргумента в функии ВПР номер столбца просматриваемой таблицы из которого нужно взять данные. У вас указано 54.

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

Пользователь
Сообщений: 5 Регистрация: 21.11.2016
21.11.2016 17:39:02

ох. кАк! ШАЙТАН ТРА-ТА-ТА!

«. а ларчик просто открывался. «
© И. А Крылов

Ни за что бы не догадался сам! Расцеловать готов! Вот бы так наши врачи всегда помогали!
Сердечно благодарю!

Пользователь
Сообщений: 7650 Регистрация: 15.02.2016
21.11.2016 19:25:03
atorgov, рад, что помог. Пожалуйста.

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

Создание связи между двумя таблицами в Excel

Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.

Все таблицы в книге указываются в списках полей сводной таблицы и Power View.

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

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

Если команда Отношения недоступна, значит книга содержит только одну таблицу.

1. В окне Управление связями нажмите кнопку Создать.
2. В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи «один ко многим» эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
3. Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
4. В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
5. В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
6. Нажмите кнопку ОК.

Дополнительные сведения о связях между таблицами в Excel

• Примечания о связях
• Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
• «Могут потребоваться связи между таблицами»
  • Шаг 1. Определите, какие таблицы указать в связи
  • Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблицы к другой

  Примечания о связях

  • Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
  • Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
  • В модели данных связи таблиц могут быть типа «один к одному» (у каждого пассажира есть один посадочный талон) или «один ко многим» (в каждом рейсе много пассажиров), но не «многие ко многим». Связи «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью «многие ко многим» или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением «один ко многим», но между первой и последней образуется отношение «многие ко многим»). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
  • Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
  • Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.

  Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

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

  1. Запустите надстройку Power Pivot в Microsoft Excel и откройте окно Power Pivot.
  2. Нажмите Получение внешних данных >Из службы данных >Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
  3. В разделе Price (Цена) нажмите Free (Бесплатно).
  4. В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
  5. Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
  6. Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.
  7. Прокрутите вниз и нажмите Select Query (Запрос на выборку).
  8. Нажмите кнопку Далее.
  9. Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
  10. Чтобы импортировать второй набор данных, нажмите Получение внешних данных >Из службы данных >Из Microsoft Azure Marketplace.
  11. В разделе Type (Тип) нажмите Data Данные).
  12. В разделе Price (Цена) нажмите Free (Бесплатно).
  13. Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
  14. Прокрутите вниз и нажмите Select Query (Запрос на выборку).
  15. Нажмите кнопку Далее.
  16. Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
  17. Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
  18. В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
  19. В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
  20. Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
  21. Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
  22. В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.
  23. В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.
  24. В поле «Таблица» выберитеBasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.
  25. Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
  26. В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.

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

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

  1. Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
  2. В главной таблице нажмите Сортировка по столбцу.
  3. В поле «Сортировать» выберите MonthInCalendar.
  4. В поле «По» выберите MonthOfYear.

  Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.

  «Могут потребоваться связи между таблицами»

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

  

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

  Шаг 1. Определите, какие таблицы указать в связи

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

  

  Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.

  Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой

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

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

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

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

  Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.