Что значит залинковать в экселе
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Mobile Еще. Меньше
При создании Excel таблицыExcel присваивает имя таблице и каждому ее заглавию. Можно сделать так, чтобы при добавлении формул эти имена отображались автоматически и ссылки на ячейки в таблице можно было выбрать вместо ввода вручную. Вот пример того, что происходит в Excel:
Прямая ссылка на ячейки
Имена таблицы и столбцов в Excel
Это сочетание имен таблицы и столбца называется структурированной ссылкой. Имена в структурированных ссылках корректируются при добавлении данных в таблицу или их удалении.
Структурированные ссылки также появляются, когда вы создаете формулу вне таблицы Excel, которая ссылается на данные таблицы. Ссылки могут упростить поиск таблиц в крупной книге.
Чтобы добавить структурированные ссылки в формулу, можно щелкнуть ячейки таблицы, на которые нужно сослаться, а не вводить ссылку непосредственно в формуле. В примере ниже введите формулу, которая автоматически использует структурированные ссылки для расчета комиссионных за продажу.
Sales Amount
ПроцентКомиссии
ОбъемКомиссии
Скопируйте образец данных из таблицы выше, включая заголовки столбцов, и вкопируйте его в ячейку A1 нового Excel таблицы.
Чтобы создать таблицу, вы выберите любую ячейку в диапазоне данных и нажмите CTRL+T.
Убедитесь, что в поле Таблица с headers (Таблица с заглавами) есть проверка, и нажмите кнопку ОК.
В ячейке E2 введите знак равно(=)и щелкните ячейку C2.
В строке формул после знака равенства появится структурированная ссылка [@[ОбъемПродаж]].
Введите звездку(*) сразу после закрываемой скобки и щелкните ячейку D2.
В строке формул после звездочки появится структурированная ссылка [@[ПроцентКомиссии]].
Нажмите клавишу ВВОД.
Excel автоматически создает вычисляемый столбец и копирует формулу вниз по нему, корректируя ее для каждой строки.
Методы работы со структурированными ссылками
При работе со структурированными ссылками учитывайте следующее:
Автозаполнение формул может оказаться очень полезным при вводе структурированных ссылок для соблюдения правил синтаксиса. Дополнительные сведения см. в этой теме.
Выбор того, следует ли создавать структурированные ссылки для таблиц в semi-selections По умолчанию при создании формулы при щелчке диапазона ячеек в таблице происходит полувыберение ячеек и автоматическое ввод структурированной ссылки вместо диапазона ячеек в формуле. Псевдовыбор облегчает ввод структурированной ссылки. Вы можете включить или отключить эту функцию, с помощью которых в диалоговом окне Параметры файла > параметры > Формулы > Работа с формулами.
Преобразование диапазона в таблицу и таблицы в диапазон. При преобразовании таблицы в диапазон все ссылки на ячейки преобразуются в эквивалентные абсолютные ссылки стилей A1. При преобразовании диапазона в таблицу Excel не меняет автоматически ссылки на ячейки этого диапазона на соответствующие структурированные ссылки.
Добавление и удаление столбцов и строк в таблице. Так как диапазоны данных таблицы часто меняются, ссылки на ячейки для структурированных ссылок корректируются автоматически. Например, если вы используете имя таблицы для подсчета всех ячеек в ней, и добавляете строку данных, ссылка на ячейки автоматически меняется.
Переименование таблицы или столбца. Если переименовать столбец или таблицу, в приложении Excel автоматически изменится название этой таблицы или заголовок столбца, используемые во всех структурированных ссылках книги.
Перемещение, копирование и заполнение структурированных ссылок При копировании или перемещение формулы, использующей структурированную ссылку, все структурированные ссылки остаются одинаковыми.
Примечание: Копирование структурированной ссылки и заполнение структурированной ссылки — это не одно и то же. При копировании все структурированные ссылки остаются одинаковыми, а при заполнении формулы полностью структурированные ссылки настраивают задатчики столбцов, как ряд, как указано в таблице ниже.
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Указатели специальных элементов
Чтобы сослаться на определенную часть таблицы, например на строку итогов, в структурированных ссылках можно использовать перечисленные ниже указатели специальных элементов.
Этот указатель специального элемента:
Ссылается на:
Вся таблица, включая заголовки столбцов, данные и итоги (если они есть).
Только строки данных.
Только строка заголовка.
Только строка итога. Если ее нет, будет возвращено значение null.
Только ячейки в той же строке, где располагается формула. Эти указатели нельзя сочетать с другими указателями специальных элементов. Используйте их для установки неявного пересечения в ссылке или для переопределения неявного пересечения и ссылки на отдельные значения из столбца.
Заключение
В статье мы научились создавать связанные таблицы в Excel. Мы разобрали два способа, которые наиболее эффективны для новичков. Конечно, есть и другие варианты. Например, вставить таблицу в Эксель, через кнопки «Файл» и «Открыть» или с помощью формул. Эти практические советы Вам должны помочь в соединении двух таблиц Excel. Спасибо за внимание, удачи Вам!
С уважением, Иван Кунпан.
P.S. Практические статьи по работе с Excel-таблицами:
Как известно, по умолчанию в одной ячейке листа Excel располагается одна строка с числами, текстом или другими данными. Но, что делать, если нужно перенести текст в пределах одной ячейки на другую строку? Данную задачу можно выполнить, воспользовавшись некоторыми возможностями программы. Давайте разберемся, как сделать перевод строки в ячейке в Excel.
Примеры использования структурированных ссылок
Ниже приведены примеры использования структурированных ссылок.
Эта структурированная ссылка:
Ссылается на:
Диапазон ячеек:
Все ячейки в столбце "ОбъемПродаж".
Заголовок столбца "ПроцентКомиссии".
Итог столбца "Регион". Если нет строки итогов, будет возвращено значение ноль.
Все ячейки в столбцах "ОбъемПродаж" и "ПроцентКомиссии".
Только данные в столбцах "ПроцентКомиссии" и "ОбъемКомиссии".
Только заголовки столбцов от "Регион" до "ОбъемКомиссии".
Итоги столбцов от "ОбъемПродаж" до "ОбъемКомиссии". Если нет строки итогов, будет возвращено значение null.
Только заголовок и данные столбца "ПроцентКомиссии".
E5 (если текущая строка — 5)
"Могут потребоваться связи между таблицами"
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Дополнительные сведения о связях между таблицами в Excel
Способы переноса текста
Некоторые пользователи пытаются перенести текст внутри ячейки нажатием на клавиатуре кнопки Enter. Но этим они добиваются только того, что курсор перемещается на следующую строку листа. Мы же рассмотрим варианты переноса именно внутри ячейки, как очень простые, так и более сложные.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Связанные таблицы в Excel, что это такое и зачем с ними работать
Здравствуйте друзья! Во время работы с таблицами Excel приходится их связывать. Что такое связанные таблицы в Эксель? Их называют Эксель таблицами, с помощью которых переносится информация с одной таблицы на другую таблицу.
С помощью связанных таблиц, можно легко работать с большими таблицами. Например, быстро проводить по ним поиск нужных Вам данных, менять различные значения, добавлять в них символы, текст и так далее. А слишком большая таблица, занимает много времени в работе, когда в ней пишешь или ищешь какую-нибудь информацию.
Связанные таблицы могут разделяться на несколько листов и работа с ними будет проходить гораздо быстрее. Далее, будем разбирать инструкцию, которая поможет в создании связанных таблиц.
Дополнительные сведения о связях между таблицами в Excel
Что произойдет, если я буду использовать прямые ссылки на ячейки?
Если вы введете в вычисляемый столбец прямые ссылки на ячейки, может быть сложнее понять, что вычисляет формула.
На примере таблицы щелкните ячейку E2
Введите в формулу =C2*D2 инажмитеввод .
Обратите внимание на то, что хотя Excel копирует формулу вниз по столбцу, структурированные ссылки не используются. Если, например, вы добавите столбец между столбцами C и D, вам придется исправлять формулу.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 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. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле "Сортировать" выберите MonthInCalendar.
В поле "По" выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию "месяц и год" (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
"Могут потребоваться связи между таблицами"
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Способ 1: использование клавиатуры
Самый простой вариант переноса на другую строку, это установить курсор перед тем отрезком, который нужно перенести, а затем набрать на клавиатуре сочетание клавиш Alt (левый) + Enter.
В отличие от использования только одной кнопки Enter, с помощью этого способа будет достигнут именно такой результат, который ставится.
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа "один к одному" (у каждого пассажира есть один посадочный талон) или "один ко многим" (в каждом рейсе много пассажиров), но не "многие ко многим". Связи "многие ко многим" приводят к ошибкам циклической зависимости, таким как "Обнаружена циклическая зависимость". Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью "многие ко многим" или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением "один ко многим", но между первой и последней образуется отношение "многие ко многим"). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Как связать ячейки из разных файлов Excel
Процесс связывания между различными файлами Excel (или рабочими книгами) практически такой же, как указано выше. За исключением случаев, когда вы вставляете ячейки, вставьте их в другую электронную таблицу вместо другой вкладки. Вот как это сделать в 4 простых шага.
- Откройте оба документа Excel.
- Во втором файле (Мастер службы поддержки), выберите ячейку и введите символ равенства знак равно
- Переключиться на оригинальный файл (Интернет Tech Советы) и нажмите на ячейку, на которую хотите сослаться.
- Нажмите Войти завершить формулу.
Теперь формула для связанной ячейки также имеет другое имя книги в квадратных скобках.
Если вы закроете исходный файл Excel и снова посмотрите на формулу, вы увидите, что теперь он также имеет местоположение всего документа. Это означает, что если вы переместите исходный файл, который вы указали в другом месте, или переименуете его, ссылки перестанут работать. Вот почему надежнее хранить все важные данные в одном файле Excel.
Правила синтаксиса структурированных ссылок
Структурированные ссылки также можно вводить и изменять вручную, но для этого необходимо разобраться в синтаксисе структурированных ссылок. Рассмотрим такую формулу:
В этой формуле используются указанные ниже компоненты структурированной ссылки.
Имя таблицы: ОтделSales — это пользовательское имя таблицы. Она ссылается на данные таблицы без строк с заглавными и итогами. Вы можете использовать имя таблицы по умолчанию, например "Таблица1", или изменить его на пользовательское.
Обозреватель столбцов: [Sales Amount] и [Commission Amount] — это оговарители столбцов, которые используют имена столбцов, которые они представляют. Они ссылались на данные столбца без заглавного колонок и строки итогов. Всегда заключены в квадратные скобки, как показано ниже.
При создании или изменении структурированных ссылок вручную учитывайте перечисленные ниже правила синтаксиса.
Заключайте указатели в квадратные скобки. Все указатели таблиц, столбцов и специальных элементов должны быть заключены в парные скобки ([ ]). Указатель, содержащий другие указатели, требует наличия таких же внешних скобок, в которые будут заключены внутренние скобки других указателей. Например: =ОтделSales[[Продавец]:[Регион]]
Все заголовки столбцов — это текстовые строки. Но они не требуют кавычка, если они используются в структурированной ссылке. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Нельзя использовать выражения с заголовками столбцов. Например, выражение ОтделПродажСводкаФГ[[2014]:[2012]] недопустимо.
Заключайте в квадратные скобки заголовки столбцов, содержащие специальные знаки. Если присутствуют специальные знаки, весь заголовок столбца должен быть заключен в скобки, а это означает, что для указателя столбца потребуются двойные скобки. Пример: =ОтделПродажСводкаФГ[[Итого $]]
Дополнительные скобки в формуле нужны при наличии таких специальных знаков:
Escape-символ (') в формуле необходим при наличии таких специальных знаков:
Рекомендуется использовать один пробел:
После первой левой скобки ([)
Перед последней правой скобками (]).
Как связать две таблицы в Excel, варианты
Разберём два эффективных варианта создания связанных Эксель таблиц. В начале работы, создайте на своём компьютере две таблицы. Например, первая таблица будет с таким названием, как «Даты», а вторая таблица пустая.
Вместе с тем, запускаете на компьютере ещё одну таблицу – «Даты» и записываете в неё значения. Например, «0.1.11.2019» Январь и так далее. Выделяете столбцы в таблице с любой информацией и нажимаете правой кнопкой мыши – «Копировать» (Скрин 1).
Запускаем второй лист таблицы Excel. Затем, нужно кликнуть в любую ячейку таблицы компьютерной мышкой и кликните кнопки – «Вставить» и «Вставить связь» (Скрин 2).
После их нажатия, в Вашей таблице появятся данные из другой таблицы (Скрин 3).
Таким образом она приобретёт связь с другой таблицей. Это способ одиночный. Вам придётся несколько раз нажимать на рассмотренные выше кнопки. Поэтому, рассмотрим ещё один метод, как связать две таблицы в Excel по пошаговой инструкции.
Запустите на своём компьютере пустую Excel таблицу. Далее, в ней нажмите кнопку – «Вставка» затем, «Объект» (Скрин 4).
Выбираете из появившегося окна раздел – «Из файла». Далее, нажимаете кнопку «Обзор» и загружаете другую таблицу Эксель нажатием кнопки «Вставить». После этого, Вы сможете соединить сразу две таблицы.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 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. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле "Сортировать" выберите MonthInCalendar.
В поле "По" выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию "месяц и год" (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
Связанные таблицы в Excel, как их сделать? Простые советы
Как сделать связанные таблицы в Excel? В статье будет раскрыт ответ на вопрос. С помощью простой инструкции мы соединим таблицы.
Способ 2: форматирование
Если перед пользователем не ставится задачи перенести на новую строку строго определенные слова, а нужно только уместить их в пределах одной ячейки, не выходя за её границы, то можно воспользоваться инструментом форматирования.
-
Выделяем ячейку, в которой текст выходит за пределы границ. Кликаем по ней правой кнопкой мыши. В открывшемся списке выбираем пункт «Формат ячеек…».
После этого, если данные будут выступать за границы ячейки, то она автоматически расширится в высоту, а слова станут переноситься. Иногда приходится расширять границы вручную.
Чтобы подобным образом не форматировать каждый отдельный элемент, можно сразу выделить целую область. Недостаток данного варианта заключается в том, что перенос выполняется только в том случае, если слова не будут вмещаться в границы, к тому же разбитие осуществляется автоматически без учета желания пользователя.
Станьте профессиональным пользователем Microsoft Excel
Связывание ячеек между листами — это только один из примеров того, как вы можете фильтровать данные в Excel и организовывать свои электронные таблицы. Проверьте некоторые другие Советы и рекомендации по Excel что мы собрали вместе, чтобы помочь вам стать продвинутым пользователем.
Какие еще изящные лайфхаки Excel вы знаете и используете? Знаете ли вы какие-либо другие творческие способы связать ячейки в Excel? Поделитесь ими с нами в разделе комментариев ниже.
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа "один к одному" (у каждого пассажира есть один посадочный талон) или "один ко многим" (в каждом рейсе много пассажиров), но не "многие ко многим". Связи "многие ко многим" приводят к ошибкам циклической зависимости, таким как "Обнаружена циклическая зависимость". Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью "многие ко многим" или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением "один ко многим", но между первой и последней образуется отношение "многие ко многим"). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Способ 3: использование формулы
Также можно осуществить перенос внутри ячейки при помощи формул. Этот вариант особенно актуален в том случае, если содержимое выводится с помощью функций, но его можно применять и в обычных случаях.
- Отформатируйте ячейку, как указано в предыдущем варианте.
- Выделите ячейку и введите в неё или в строку формул следующее выражение:
Главным недостатком данного способа является тот факт, что он сложнее в выполнении, чем предыдущие варианты.
В целом пользователь должен сам решить, каким из предложенных способов оптимальнее воспользоваться в конкретном случае. Если вы хотите только, чтобы все символы вмещались в границы ячейки, то просто отформатируйте её нужным образом, а лучше всего отформатировать весь диапазон. Если вы хотите устроить перенос конкретных слов, то наберите соответствующее сочетание клавиш, как рассказано в описании первого способа. Третий вариант рекомендуется использовать только тогда, когда данные подтягиваются из других диапазонов с помощью формулы. В остальных случаях использование данного способа является нерациональным, так как имеются гораздо более простые варианты решения поставленной задачи.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Еще статьи по данной теме:
Доброго дня!
А как перенести на новую строку (внутри ячейки) части одной формулы?
У меня есть формула — весьма длинная, со множеством вложенных функций. Когда она записана сплошняком без разрывов и переходов на новую строку работать с ней тяжело и неудобно.
Разделить формулу на несколько строк и сделать в них (строках) отступы для удобства работы можно, но как?
Microsoft Excel — очень мощный многоцелевой инструмент, который может использовать каждый. Но если вы работаете с электронными таблицами каждый день, вам может потребоваться знать больше, чем просто основы использования Excel. Знание нескольких простых трюков может иметь большое значение с Excel. Хорошим примером является знание того, как связать ячейки в Excel между листами и книгами.
Изучение этого сэкономит много времени и путаницы в долгосрочной перспективе.
Зачем связывать данные ячейки в Excel
Возможность ссылаться на данные на разных листах является ценным навыком по нескольким причинам.
Во-первых, это упростит организацию ваших электронных таблиц. Например, вы можете использовать один лист или рабочую книгу для сбора необработанных данных, а затем создать новую вкладку или новую рабочую книгу для отчетов и / или суммирования.
После того, как вы свяжете ячейки между ними, вам нужно всего лишь изменить или ввести новые данные в одну из них, а результаты автоматически изменятся в другой. Все без необходимости перемещаться между различными таблицами.
Во-вторых, этот прием позволит избежать дублирования одних и тех же чисел в нескольких таблицах. Это уменьшит ваше рабочее время и возможность совершать ошибки в расчетах.
В следующей статье вы узнаете, как связать отдельные ячейки в других листах, связать диапазон ячеек и как связать ячейки из разных документов Excel.
Операторы ссылок
Перечисленные ниже операторы ссылок служат для составления комбинаций из указателей столбцов, что позволяет более гибко задавать диапазоны ячеек.
Эта структурированная ссылка:
Ссылается на:
Диапазон ячеек:
Все ячейки в двух или более смежных столбцах
: (двоеточие) — оператор ссылки
Сочетание двух или более столбцов
, (запятая) — оператор объединения
Пересечение двух или более столбцов
(пробел) — оператор пересечения
Как связать две отдельные клетки
Давайте начнем со связывания двух ячеек, расположенных на разных листах (или вкладках), но в одном файле Excel. Для этого выполните следующие действия.
Теперь, если вы нажмете на ячейку в Sheet2вы увидите, что Excel записывает для вас путь в строке формул.
Например, = Лист1! C3, где Лист1 это имя листа, C3 является ли ячейка, на которую вы ссылаетесь, и восклицательный знак (!) используется как разделитель между ними.
Используя этот подход, вы можете связать вручную, не выходя из исходного листа вообще. Просто введите формулу ссылки прямо в ячейку.
Примечание. Если имя листа содержит пробелы (например, Лист 1), то при вводе ссылки в ячейку необходимо указать имя в одинарных кавычках. подобно = ’Лист 1 ′! C3, Поэтому иногда проще и надежнее позволить Excel написать для вас справочную формулу.
Определение структурированных ссылок в вычисляемых столбцах
Когда вы создаете вычисляемый столбец, для формулы часто используется структурированная ссылка. Она может быть неопределенной или полностью определенной. Например, для создания вычисляемого столбца "ОбъемКомиссии", который вычисляет комиссионные в рублях, можно использовать следующие формулы:
Тип структурированной ссылки
Перемножает соответствующие значения из текущей строки.
Перемножает соответствующие значения из каждой строки обоих столбцов.
Общее правило таково: если структурированная ссылка используется внутри таблицы, например, при создании вычисляемого столбца, то она может быть неопределенной, но вне таблицы нужно использовать полностью определенную структурированную ссылку.
Как связать диапазон ячеек
Другой способ связать ячейки в Excel — связать целый ряд ячеек из разных вкладок Excel. Это полезно, когда вам нужно хранить одни и те же данные на разных листах, не редактируя оба листа.
Чтобы связать несколько ячеек в Excel, выполните следующие действия.
- В исходной вкладке с данными (Лист1), выделите ячейки, на которые вы хотите сослаться.
- Скопируйте ячейки (Ctrl/команда + С, или щелкните правой кнопкой мыши и выберите копия).
- Перейти на другую вкладку (Sheet2) и нажмите на ячейку (или ячейки), где вы хотите разместить ссылки.
При нажатии на новые связанные ячейки в Sheet2 вы можете увидеть ссылки на ячейки из Sheet1 на вкладке формулы. Теперь, когда вы меняете данные в выбранных ячейках в Sheet1, они автоматически изменяют данные в связанных ячейках в Sheet2.
Как связать ячейку с функцией
Связывание с кластером ячеек может быть полезно, когда вы выполняете суммирование и хотите хранить их на листе отдельно от исходных необработанных данных.
Допустим, вам нужно написать функцию SUM в Sheet2, которая будет связывать несколько ячеек из Sheet1. Для этого перейдите на Sheet2 и нажмите на ячейку, где вы хотите разместить функцию. Напишите функцию как обычно, но когда дело доходит до выбора диапазона ячеек, перейдите на другой лист и выделите их, как описано выше.
У вас будет = SUM (Лист1 C3: C7)где функция SUM суммирует содержимое ячеек C3: C7 в Sheet1. Нажмите Войти завершить формулу.
Как изменить имя таблицы?
При создании таблицы Excel ей назначается имя по умолчанию ("Таблица1", "Таблица2" и т. д.), но его можно изменить, чтобы сделать более осмысленным.
Выберите любую ячейку таблицы, чтобы отобрать на ленте вкладку > Конструктор.
Введите нужное имя в поле Имя таблицы и нажмите ввод.
В этом примере мы используем имя ОтделПродаж.
При выборе имени таблицы соблюдайте такие правила:
Используйте допустимые символы. Имя всегда должно начинаться с буквы, символа подчеркивания (_) или обратной косой черты (\). Остальная часть имени может включать в себя буквы, цифры, точки и символы подчеркивания. В имени нельзя использовать латинские буквы C, c, R и r, так как они служат для быстрого выделения столбца или строки с активной ячейкой при вводе их в поле Имя или Перейти.
Не используйте ссылки на ячейки. Имена не могут иметь такой же вид, как ссылки на ячейки, например Z$100 или R1C1.
Не используйте пробелы для разделения слов. В имени нельзя использовать пробелы. Вы можете использовать символ подчеркиваия(_)и точка (.). Примеры допустимых имен: ОтделПродаж, Налог_на_продажи, Первый.квартал.
Используйте не более 255 знаков. Имя таблицы может содержать не более 255 знаков.
Использование уникальных имен таблиц Повторяющиеся имена запрещены. Excel не различает верхний и нижний регистр символов в именах, поэтому если в книге ввести "Продажи", но уже есть другое имя "ПРОДАЖИ", вам будет предложено выбрать уникальное имя.
Использование идентификатора объекта Если вы планируете иметь сочетание таблиц, таблиц и диаграмм, ведите префикс имен с типом объекта. Например, tbl_Sales для таблицы продаж, pt_Sales для таблицы продаж и chrt_Sales для диаграммы продаж или ptchrt_Sales для сводная диаграмма. При этом все ваши имена будут упорядочены в диспетчере имен.
Читайте также: