Сохранять значения внешних связей в excel что это значит
Обобщаем с умом: внешние ссылки и консолидация в Excel
Внешние ссылки в Excel
Думаю, каждый из Вас знает, что внешняя ссылка в Эксель – это ссылка на ячейку, находящуюся в другой книге. И самый распространенный способ собрать данные из разных источников в одну – как раз внешние ссылки.
Если итоговая таблица достаточно простая, этот способ можно использовать, иначе – легко запутаться, да и утомительно вручную внести несколько сотен ссылок на ячейки, высока вероятность ошибки. Тем не менее, для маленьких таблиц это действительно решение.
Существует много способов сослаться на ячейку с другого листа, или книги. Я расскажу Вам самый простой:
- Откройте все файлы – свод и исходники
- Кликните по ячейке, в которую нужно вставить ссылку, и введите знак равно (как вы это делаете, когда создаете формулы).
- Выберите файл с исходным значением и кликните на этом значении. В строке формул появится ссылка на это значение.
- Если к этому значению нужно прибавить другие из остальных источников – допишите «+» и повторяйте п.3-4 до полного заполнения.
- Когда все ссылки на ячейки сделаны, нажмите Enter , будет просчитан сводный результат.
Внешняя ссылка имеет специфический внешний вид: имя файла в квадратных скобках, имя листа, восклицательный знак, координаты ячейки:
Когда Вы закроете исходные файлы, к ссылке слева добавится еще и адрес файла-исходника:
По умолчанию, все внешние ссылки абсолютные, т.е. при копировании не изменяются. Если нужно, сделайте их относительными, нажимая F4 до получения необходимого типа ссылки.
Не перемещайте файлы-исходники, т.к. это разорвет связи, и внешние ссылки работать больше не будут.
Если на одном компьютере открыт и сводный документ и исходник, изменения в исходной таблице сразу же «подтянутся» и в свод. Если хотя бы одна из этих книг закрыта – понадобится принудительное обновление. Так же, при повторном открытии сводного файла, Эксель предложит обновить данные. Сделайте это, если нужно актуализировать информацию.
Управление внешними ссылками
Чтобы управлять ссылками на источники в других книгах, нажмите на ленте Данные – Подключения – Изменить связи . В открывшемся окне отобразится весь список подключенных файлов и кнопки-команды:
- Обновить – принудительное обновление всех ссылок
- Изменить – указать новую книгу Excel, в которой располагаются подключенные данные
- Открыть – открывает документ-источник
- Разорвать связь – удаляет внешние ссылки из этой связи, заменяет их на значение
- Состояние – обновляет статус доступности файлов с данными
- Запрос на обновление связей – укажите программе, нужно ли обновлять данные при открытии файла
Если у Вас большой файл с несколькими листами, может понадобиться много времени на поиски внешних ссылок. Так как быстро найти внешние ссылки в документе Excel? Предложу рецепт. Все подобные ссылки имеют общий элемент – открывающая и закрывающая квадратные скобки, в которые записано имя файла-исходника. В других случаях такие скобки используются редко. Нажмите Ctrl+F для открытия окна поиска, в поле «Найти» введите квадратную скобку и нажмите «Найти все». В результатах поиска точно будут все внешние ссылки.
Применение внешних ссылок – самый простой и очевидный способ собрать данные из нескольких таблиц в одну. Но если данных достаточно много, этот метод неприменим. Тогда пользуемся инструментом «Консолидация».
Консолидация данных в одну таблицу
Этот инструмент упрощает сведение информации в одну таблицу данных, это удачный способ автоматизации процесса.
Рассмотрим его работу на примере. У нас есть 3 файла с данными о продажах трех подразделений компании. Нужно собрать общие показатели по организации, и сделать это быстро, ведь информация нужна «на вчера».
Давайте выполнять. Открываем все файлы-исходники и свод. Активируем «Свод». Ищем на ленте Данные – Работа с данными – Консолидация . Откроется окно подключения внешних данных.
Теперь пошагово выполняем:
- В поле «Функция» выбираем «Сумма» (или что-то другое, если нужно)
- В поле «Ссылка» указываем ссылку на данные в первом файле-источнике (вместе с шапкой). Лучше всего, указать столбцы полностью, например A:D . Тогда не нужно будет переопределять диапазон при добавлении новых данных.
- Нажимаем «Добавить», ссылка появляется в поле «Список диапазонов»
- Повторяйте п. 2-3, пока не добавите ссылки на все файлы-исходники
- Установите галочки «Подписи верхней строки» и «Значения левого столбца», чтобы Эксель сам подобрал заголовки шапки и категории в строках
- Установите галку «Создавать связи с исходными данными», тогда консолидация будет сделана с помощью ссылок на ячейки. Иначе – будут просто вставлены итоговые значения.
- Нажмите Ок для завершения настройки консолидации.
Если исходные таблицы изменились, можно переформировать свод. Для этого повторно откройте окно «Консолидация» и нажмите Ок.
Такой вот простой инструмент, решающий одну из самых противных задач в Эксель. Я предпочитаю применять именно его, ручная работа по внесению ссылок – не для меня. Рекомендую и Вам пользоваться. Хотя, более удачным решением будет применение сводной таблицы (когда это возможно), или создание собственной VBA-программы с детальными настройками сведения.
На этом оставлю Вас экспериментировать с Консолидацией, и приступаю к написанию поста о защите данных в Эксель. Читайте, эта информация сейчас, как никогда актуальна. До встречи!
Как найти ссылки на внешние источники excel
Связывание с другими книгами — очень распространенная задача в Excel, но иногда вы можете найти книгу, которая ссылок не может найти, хотя Excel сообщает о том, что она существует. Нет способа автоматического поиска всех внешних ссылок, используемых в книге, однако существует несколько способов, используемых вручную. Вам нужно искать формулы, определенные имена, объекты (например, текстовые поля или фигуры), названия диаграмм и ряды данных диаграммы.
Имя любой Excel книги, с помощью ссылки на которую вы ссылались, будет связана с расширением XL* (например, .xls, .xlsx, XLSM), поэтому рекомендуемый способ — найти все ссылки на частичное расширение XL. Если вы ссылались на другой источник, необходимо определить оптимальный поисковый запрос.
Поиск ссылок, используемых в формулах
Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.
В поле Найти введите.xl.
В поле В пределах выберите книга.
В поле Искать в выберите формулы.
В отображемом списке наймем в столбце Формула формул, содержащих XL. В этом случае Excel найдено несколько экземпляров функции бюджетного Master.xlsx.
Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.
Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
Проверьте каждую запись в списке и проверьте, нет ли в столбце Ссылка внешних ссылок. Внешние ссылки содержат ссылку на другую книгу, например [Budget.xlsx].
Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
Если вы хотите удалить сразу несколько элементов, можно сгруппнуть несколько элементов, нажав клавишу SHIFT или CTRL и щелкнув левой кнопкой мыши.
Нажмите клавиши CTRL+G, нажмите клавиши CTRL+G, чтобы перейти в диалоговое окно Перейти, а затем выберите специальные > объекты > ОК. При этом будут выбраны все объекты на активном сайте.
Нажимая клавишу TAB, переходить между выбранными объектами, а затем искать в строка формул ссылку на другую книгу, например [Budget.xlsx].
Щелкните название диаграммы, которую вы хотите проверить.
В строка формул наймем ссылку на другую книгу, например [Budget.xls].
Выберите диаграмму, которую нужно проверить.
На вкладке Макет в группе Текущий выделение щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных, которые нужно проверить.
На строка формул , наймем ссылку на другую книгу, например [Budget.xls] в функции РЯД.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Поиск "битых" ссылок на внешние файлы Excel
Очень часто в сложных (многолистовых) файлах Excel возникает такая проблема: когда хочешь "разорвать" ссылки на внешние файлы (заменить их значениями), то некоторые ссылки на старинские файлы не убиваются и найти эти старинские файлы не представляется возможным. Я задался вопросом обнаружить ячейки или любые объекты книги, которые ссылаются на внешние файлы. Ниже представлен код, который репортит на отдельный лист адреса ячеек, которые ссылаются на внешние файлы:
Однако прогнав, данный код по файлу, который содержит битую ссылку я не смог идентифицировать ячейку, которая ссылается на несуществующий файл.
Отсюда делаю вывод, что кроме ячеек ссылаться могут и другие объекты Excel.
II. Постановка задачи
Помогите пожалуйста доработать код, которые идентифицирует не только ячейки (и в отдельном листе прописывает их путь), но и все другие объекты, которые могут ссылаться на внешние файлы?
III. Решение №1 (с помощью стандартного ф-ла Excel)
Проблема решилась следующим образом:
Получаем скрин
Получаем скрин
В Microsoft Excel вы можете связать ячейку книги с другой книгой, используя формулу, которая ссылается на внешнюю книгу. При создании этой ссылки может использоваться относительный путь. С помощью относительных ссылок можно перемещать книги, не нарушая связь. В этой статье рассказывается о том, как с помощью Excel хранить ссылки на связанные книги в разных обстоятельствах.
Дополнительная информация
Как обрабатываются пути ссылок при открытии файла
При открытии в Excel файла, содержащего ссылки (связанная книга), она объединяет части ссылок, хранящиеся в файле, с необходимыми фрагментами текущего пути связанной книги.
Как сохраняются пути ссылок
Если путь к связанному файлу сохраняется в Excel, для определения того, что нужно сохранить, используются следующие правила:Примечание. Перемещение вверх по пути указывает на то, что вы ссылаетесь на папки, расположенные вне корневого диска или общего доступа. Если переместиться вниз по пути, это означает, что вы переходите ближе к корневому диску или к общему доступу.
Если связанный файл и исходный файл данных находятся на разных дисках, буква диска сохраняется с путем к файлу и имени файла.
Если связанный файл и исходный файл данных находятся в одной папке, сохраняется только имя файла.
Если файл исходных данных находится в папке, вложенной в ту же корневую папку, что и связанный файл, свойство хранится для указания корневой папки. Все части пути, к которым предоставлен общий доступ, не сохраняются. Например, если связанный файл C:\Mydir\Linked.xls зависит от C:\Mydir\Files\Source.xls, то единственной сохраненной частью пути является \Files\Source.xls.
Если исходный файл данных находится в папке, расположенной ниже связанного файла, свойство сохраняется, чтобы указать это. Например, связанный файл — C:\Mydir\Files\Myfile\Linked.xls, а исходный файл данных — C:\Mydir\Files\Source.xls. Excel хранит только \MyDir\Files\.. \Source.xls.Примечание. Это позволяет поддерживать ссылки при копировании связанного файла в дополнительную вложенную папку в папке, в которой находится исходный файл. Например, связанный файл — C:\Mydir\Files\Myfiles1\Linked.xls, а файл исходных данных — C:\Mydir\Files\Source.xls, связанный файл с именем Link. xls копируется из папки C:\Mydir\Files\Myfiles1 в папку C:\Mydir\Files\Myfiles2, а ссылка на C:\Mydir\Files\Source.xls сохраняется.
Если исходный файл данных находится в папке XLStart, в разделе " Каталог автозагрузки" или " Библиотека ", свойству записывается свойство, которое указывает на одну из этих папок, и сохраняется только имя файла.Примечание. В Excel распознаются две стандартные папки XLStart, из которых автоматически открываются файлы при запуске. Ниже указаны две папки.
Папка XLStart в папке установки Office, например C:\Program Files\Microsoft Office \Office\XLStart
Папка XLStart в профиле пользователя (например, C:\Documents and Settings \имя_пользователя\Application Data\Microsoft\Excel\XLStart
Папка XLStart в профиле пользователя — это Папка XLStart, которая будет храниться в качестве свойства ссылки. Если вы используете папку XLStart, которая находится в папке установки Office, эта папка XLStart будет обрабатываться так же, как и любая другая папка на жестком диске. Имя папки Office меняется между версиями Office. Например, имя папки Office — Office, Office10, Office11 или Office12 в зависимости от используемой версии Office. Изменение имени папки приводит к разрыву связей при переходе на компьютер, на котором установлена другая версия Excel, чем версия, в которой была установлена ссылка.
Важно отметить, что содержимое строки формул не обязательно должно быть сохранено. Например, если файл исходных данных закрыт, вы увидите полный путь к файлу, но может быть сохранено только имя файла.
Относительные и абсолютные ссылки
Ссылки на внешние книги создаются в определенном порядке, если это возможно. Это означает, что полный путь к исходному файлу данных не записывается, а только часть пути, связанная с связанной книгой. С помощью этого метода вы можете перемещать книги, не нарушая связи между ними. Однако ссылки остаются неизменными, только если книги остаются в одном месте относительно друг друга. Например, если связанный файл — C:\Mydir\Linked.xls, а файл источника данных — C:\Mydir\Files\Source.xls, вы можете переместить файлы на диск D, пока исходный файл по-прежнему находится во вложенной папке "файлы". Относительные ссылки могут приводить к возникновению проблем при перемещении связанного файла на другой компьютер, когда источник находится в одном месте.
Сопоставленные диски и UNC-имя
При связывании книги с исходными данными связь устанавливается на основе способа открытия книги. Если книга была открыта на подключенном диске, она будет создана с помощью подключенного диска. Ссылка останется в том случае, если книга с исходными данными будет открыта в будущем. Если файл исходного файла данных открыт с помощью пути в формате UNC, ссылка не будет возвращена на подключенный диск, даже если соответствующий диск доступен. Если в одном и том же файле есть ссылки UNC и сопоставленные диски, а исходные файлы открыты одновременно с конечным файлом, только те ссылки, которые соответствуют тому, как был открыт этот файл, будут реагировать на гиперссылку. В частности, если вы откроете файл на подключенном диске и измените значения в исходном файле, только ссылки, созданные для подключенного диска, будут немедленно обновлены. Ссылка, отображаемая в Excel, может выглядеть по-разному в зависимости от того, как была открыта книга. Возможно, эта ссылка соответствует корневому общему ресурсу UNC или букве корневого диска, которая использовалась для открытия файла.
Сценарии, которые могут привести к неправильной работе ссылок
Существует несколько причин, по которым связь между файлами может быть непреднамеренно облагаться, чтобы они указывали на ошибочные места. Ниже приведены два наиболее распространенных сценария. Сценарий 1:
Вы можете сопоставить диск с корневым каталогом общего доступа. Например, вы можете подключить диск Z к \ \сервер\поделиться\Folder1.
Вы создаете ссылки на книгу, которая хранится в сопоставленном расположении, после того как вы откроете файл через этот подключенный диск.
Вы открываете файл по UNC-пути.
Вследствие этого ссылка будет разорвана.
Если вы закрыли файл, не сохраняя его, ссылки не будут изменены. Тем не менее, если сохранить файл перед его закрытием, вы сохранит ссылки с текущим разорванным расположением. Папки между корнем общего доступа и сопоставленной папкой будут оставлены вне пути. В приведенном выше примере ссылка изменится на \ \сервер\Folder1. Другими словами, имя общего доступа исключается из пути. Сценарий 2.
Вы можете сопоставить диск с корневым каталогом общего доступа. Например, вы можете подключить диск Z к \ \сервер\поделиться\Folder1.
Вы открываете файл по UNC-пути или сопоставленному диску, подключенному к другой папке в общем доступе, например \ \сервер\поделиться\Folder2.
Как следствие, ссылка будет разорвана.
Если вы закрыли файл, не сохраняя его, ссылки не будут изменены. Тем не менее, если сохранить файл перед его закрытием, вы сохранит ссылки с текущим разорванным расположением. Папки между корнем общего доступа и сопоставленной папкой будут оставлены вне пути. В приведенном выше примере ссылка изменится на \ \сервер\folder1.
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Дополнительные сведения о связях между таблицами в Excel
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа "один к одному" (у каждого пассажира есть один посадочный талон) или "один ко многим" (в каждом рейсе много пассажиров), но не "многие ко многим". Связи "многие ко многим" приводят к ошибкам циклической зависимости, таким как "Обнаружена циклическая зависимость". Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью "многие ко многим" или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением "один ко многим", но между первой и последней образуется отношение "многие ко многим"). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на 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. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
При создании внешних ссылок на другие книги (также называемые связями) вы можете управлять тем, обновляются ли они. Это важно, так как если кто-то другой изменяет значение связанной ячейки, Excel не обновляет ссылку и не отображает новое значение, если вы не сообщаете об этом.
Книга, которая содержит связи, называется книгой назначения, а книга, с которую она связана, называется книгой-источником. Вы можете связать несколько исходных книг с одной книгой.
1. Destination workbook contains the external reference (link).
2. Внешняя ссылка (или ссылка) — это ссылка на ячейку или диапазон в книге-источнике.
3. Книга-источник содержит связанную ячейку или диапазон и фактическое значение, возвращаемого в 9-й книге.
Примечание: Количество книг, на которые можно ссылаться, не ограничивается, но обычно лучше использовать как можно меньше ссылок. В противном случае могут возникнуть проблемы с производительностью при пересчете формул. Кроме того, можно перекрестно связывать книги, где они выступают в качестве источника и назначения, но это может запутать и затруднить управление.
Когда исходные и назначения книг открываются на одном компьютере, связи обновляются автоматически. Когда конечная книга открыта, а книга-источник не открыта, в области сведений о документе возможно появление запроса на обновление связей. Можно задать режим вывода этого запроса, а также режим обновления всех связей без вывода запроса. Вы также можете обновить только определенные ссылки, если книга содержит несколько ссылок.
В следующих разделах рассматриваются наиболее распространенные варианты изменения связей.
Закроем все исходные книги. Если одна книга-источник будет открыта, а остальные закрыты, обновления не будут одинаковыми.
Откройте конечную книгу.
Чтобы обновить связи, на панели доверия нажмите кнопку Обновить. Закройте планку доверия, если вы не хотите обновлять связи (на правом — X).
Откройте книгу, содержащую связи.
Перейдите в >запросы & подключения >ссылки.
Из списка Источник выберите связанный объект, который необходимо изменить.
Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Найдите книгу, которую вы хотите использовать в качестве нового источника для внешней ссылки, и обратите внимание на ее расположение.
В книге назначения выберите ячейку с внешней ссылкой, которую вы хотите изменить.
В строка формул наймем ссылку на другую книгу, например C:\Reports\[Budget.xlsx], и замените ее расположением новой книги-источника.
Вы можете решить, обновляются ли ссылки в этой книге при открытии файла в соответствии с настройками пользователя, не обновляются при открытии файла или обновляются автоматически без запроса пользователя.
Предупреждение: Этот режим влияет на всех пользователей книги. Если отключить обновление связей и выдачу запроса, пользователи этой книги не будут знать, что данные устарели.
Перейдите в >запросы & подключения >ссылки.
Выберите один из следующих трех вариантов:
Разрешим пользователям отображать оповещение или нет
Не показывать оповещение и не обновлять автоматические ссылки
Не отображайте ссылки на оповещения и обновления.
Параметры автоматического и ручного обновления. Для ссылок на формулы всегда установлено автоматическое обновление.
Даже если указать, что Excel не должен спрашивать, следует ли обновлять ссылки, вы по-прежнему будете уведомлены об их неработаете.
Открыв диалоговое окно Изменение связей (Запросы > данных & connections > Изменениесвязей), вы сможете работать с существующими ссылками несколькими вариантами. Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Обновление значений
При этом будут обновлены все выбранные книги.
Изменить источник
Этот параметр удобен, если нужно наказать существующие ссылки на другой источник. Например, у вас может быть книга за предыдущий год, и вам нужно будет навести указатель на новую книгу, когда начинается новый год. Если выбрать изменить источник, запустится диалоговое окно проводника, в котором можно найти новую книгу. Если книга состоит из двух листов, вам будет предложено указать, на какой из них должна указывать ссылка. Просто щелкните нужный лист и нажмите кнопку ОК.
Примечание: Вы можете наказать книгу на себя, выбрав ее в диалоговом оке Изменение источника. В этом случае любая формула будет отсоединяться от исходной исходной книги.
Open Source
Разрыв связи
Важно: При разрыве связей с источником все формулы, использующие источник, заменяются на их текущее значение. Например, ссылка =СУММ([Budget.xlsx]Годовой! C10:C25) будет преобразовано в сумму значений в книге-источнике. Так как это действие нельзя отменить, может потребоваться сначала сохранить версию файла.
На вкладке Данные в группе Запросы & Connections нажмитекнопку Изменить связи.
В списке Источник выберите связь, которую требуется разорвать.
Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Щелкните элемент Разорвать.
Если ссылка использовала определенное имя, оно не удаляется автоматически, поэтому вы также можете удалить его.
Чтобы удалить имя:
Если используется диапазон внешних данных, параметр запроса может также использовать данные из другой книги. Может потребоваться проверить и удалить эти типы связей.
На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
В столбце Имя выберите имя, которое следует удалить, и нажмите кнопку Удалить.
Проверка состояния
В области Изменение связей просто отображается уведомление о том, является ли связанная книга допустимым источником. Должно отображаться "ОК",но если этого не происходит, необходимо проверить исходные книги. Во многих случаях при вырезании ссылки может быть перемещена или удалена книга-источник. Если книга по-прежнему существует, вы можете использовать параметр Изменить источник, чтобы перессылка книги.
Можно ли заменить одну формулу ее вычисляемой величиной?
Да. Формулы в Excel, вместо которых вводится их значение, удаляются без возможности восстановления. Если вы случайно заменяете формулу значением и хотите ее восстановить, нажмите кнопку Отменить сразу после ввода или вжатия значения.
Нажмите CTRL+C, чтобы скопировать формулу.
Нажмите ALT+E+S+V, чтобы вировать формулу в качестве ее значения, или перейдите на главная> буфер обмена> в > Вэтом > в буфер обмена .
Что делать, если я не подключен к источнику?
Я не хочу заменять текущие данные новыми
Я пытался обновить его в прошлый раз, но это занимает слишком много времени
Кто-то другой создал книгу, и я не знаю, почему я вижу этот запрос
Щелкните Не обновлятьи обратитесь к владельцу книги. Вы также можете выяснить, какие связи находятся в книге. На вкладке Данные в группе Запросы & Connections нажмитекнопку Изменить связи.
Я отвечаю на запрос одинаково каждый раз и не хочу видеть его снова
Можно ответить на запрос и запретить его вывод для этой книги в будущем.
Не отображать запрос и обновлять связи автоматически
Этот параметр влияет на все книги, которые открываются на компьютере. Пользователи, которые откроют книгу на другом компьютере, не будут затронуты.
Откройте меню Файл, выберите раздел Параметры, а затем — Дополнительно.
В разделе Общие снимите флажок Запрашивать об обновлении автоматических связей. Если этот флажок снят, связи будут обновляться автоматически (без выдачи запроса).
Одинаковый запрос для всех пользователей этой книги
Предупреждение: Этот режим влияет на всех пользователей книги. Если отключить обновление связей и выдачу запроса, пользователи этой книги не будут знать, что данные устарели.
Перейдите в > запросы & подключения > Изменить ссылки > запросана запуск и выберите нужный параметр.)
Примечание: При наличии разорванных связей будет появляться оповещение об этом.
Что делать, если я использую запрос с параметрами?
Закройте конечную книгу.
Откройте конечную книгу.
Связь с параметрическим запросом нельзя обновить без открытия книги-источника.
Почему я не могу выбрать параметр "Вручную" в качестве параметра обновления для определенной внешней ссылки?
Для ссылок на формулы всегда установлено автоматическое.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Когда в формуле вы указываете ссылку на другую книгу, то Excel образует с ней связь. Эта связь будет прекрасно работать и обновляться автоматически в том случае, когда открыты обе книги.
К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.
Как разорвать связь
Один из способов решения данной проблемы — разрыв связи. Если в файле только одна связь, то сделать это довольно просто:
- Перейдите на вкладку Данные.
- Выберите команду Изменить связи в разделе Подключения.
- Нажмите Разорвать связь.
ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!
Как разорвать связь со всеми книгами
Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:
Код на VBA
Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.
Как разорваться связи только в выделенном диапазоне
Иногда в книге имеется много связей и есть опасения, что при удалении связи можно удалить лишнюю. Чтобы этого избежать с помощью надстройки можно удалить связи только в выделенном диапазоне. Для этого:
При разрыве связи с исходной книгой (внешним источником) все формулы, использующие значения в этой книге, преобразуются в свои текущие значения. Например, если разорвать связь с внешней ссылкой =СУММ([Budget.xls]Годовой! C10:C25), формула СУММ заменяется вычисляемой величиной, каким бы ни была формула. Кроме того, так как это действие нельзяотменить, вы можете сохранить версию конечной книги в качестве резервной копии.
Если используется диапазон внешних данных, параметр в запросе может использовать данные из другой книги. Может потребоваться проверить и удалить эти типы связей.
Разрыв связи
На вкладке Данные в группе Подключения нажмите кнопку Изменить связи.
Примечание: Отсутствие команды Изменить связи означает, что файл не содержит связанных данных.
В списке Источник выберите связь, которую требуется разорвать.
Чтобы выбрать несколько связанных объектов, щелкните каждый из них, удерживая нажатой клавишу CTRL.
Чтобы выделить все связи, нажмите клавиши CTRL+A.
Щелкните элемент Разорвать.
Удаление имени определенной ссылки
Если для ссылки использовался определенный имя, имя не удаляется автоматически. Чтобы удалить имя, с помощью следующих действий:
На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.
Щелкните имя, чтобы выделить его.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Как удалить связи в Excel
При выполнении определенных задач в Excel иногда приходится иметь дело с несколькими таблицами, которые к тому же связаны между собой. То есть, данные из одной таблицы подтягиваются в другие и при их изменении пересчитываются значения во всех связанных табличных диапазонах.
Связанные таблицы очень удобно использовать для обработки большого объема информации. Располагать всю информацию в одной таблице, к тому же, если она не однородная, не очень удобно. С подобными объектами трудно работать и производить по ним поиск. Указанную проблему как раз призваны устранить связанные таблицы, информация между которыми распределена, но в то же время является взаимосвязанной. Связанные табличные диапазоны могут находиться не только в пределах одного листа или одной книги, но и располагаться в отдельных книгах (файлах). Последние два варианта на практике используют чаще всего, так как целью указанной технологии является как раз уйти от скопления данных, а нагромождение их на одной странице принципиально проблему не решает. Давайте узнаем, как создавать и как работать с таким видом управления данными.
Создание связанных таблиц
Прежде всего, давайте остановимся на вопросе, какими способами существует возможность создать связь между различными табличными диапазонами.
Способ 1: прямое связывание таблиц формулой
Самый простой способ связывания данных – это использование формул, в которых имеются ссылки на другие табличные диапазоны. Он называется прямым связыванием. Этот способ интуитивно понятен, так как при нем связывание выполняется практически точно так же, как создание ссылок на данные в одном табличном массиве.
Посмотрим, как на примере можно образовать связь путем прямого связывания. Имеем две таблицы на двух листах. На одной таблице производится расчет заработной платы с помощью формулы путем умножения ставки работников на единый для всех коэффициент.
На втором листе расположен табличный диапазон, в котором находится перечень сотрудников с их окладами. Список сотрудников в обоих случаях представлен в одном порядке.
Нужно сделать так, чтобы данные о ставках из второго листа подтягивались в соответствующие ячейки первого.
-
На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.
Итак, ставим курсор в нижнюю правую область элемента с формулой. После этого курсор должен преобразоваться в маркер заполнения в виде черного крестика. Выполняем зажим левой кнопки мыши и тянем курсор до самого низа столбца.
Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ
Но что делать, если перечень сотрудников в табличных массивах расположен не в одинаковом порядке? В этом случае, как говорилось ранее, одним из вариантов является установка связи между каждой из тех ячеек, которые следует связать, вручную. Но это подойдет разве что для небольших таблиц. Для массивных диапазонов подобный вариант в лучшем случае отнимет очень много времени на реализацию, а в худшем – на практике вообще будет неосуществим. Но решить данную проблему можно при помощи связки операторов ИНДЕКС – ПОИСКПОЗ. Посмотрим, как это можно осуществить, связав данные в табличных диапазонах, о которых шел разговор в предыдущем способе.
-
Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».
«Массив» — аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.
«Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.
«Номер столбца» — аргумент, носящий необязательный характер. Для решения конкретно нашей задачи мы его использовать не будем, а поэтому описывать его суть отдельно не нужно.
Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».
«Искомое значение» — аргумент, содержащий наименование или адрес ячейки стороннего диапазона, в которой оно находится. Именно позицию данного наименования в целевом диапазоне и следует вычислить. В нашем случае в роли первого аргумента будут выступать ссылки на ячейки на Листе 1, в которых расположены имена сотрудников.
«Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.
«Тип сопоставления» — аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.
Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение», кликаем по первой ячейке столбца «Имя» на Листе 1.
Способ 3: выполнение математических операций со связанными данными
Прямое связывание данных хорошо ещё тем, что позволяет не только выводить в одну из таблиц значения, которые отображаются в других табличных диапазонах, но и производить с ними различные математические операции (сложение, деление, вычитание, умножение и т.д.).
Посмотрим, как это осуществляется на практике. Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться (при помощи функции СУММ) и умножаться на коэффициент с помощью формулы.
-
Выделяем ячейку, где будет выводиться итог расчета заработной платы на Листе 3. Производим клик по кнопке «Вставить функцию».
Поля в окне соответствуют аргументам указанной функции. Хотя их число может достигать 255 штук, но для нашей цели достаточно будет всего одного. Ставим курсор в поле «Число1». Кликаем по ярлыку «Лист 2» над строкой состояния.
Способ 4: специальная вставка
Связать табличные массивы в Excel можно также при помощи специальной вставки.
-
Выделяем значения, которые нужно будет «затянуть» в другую таблицу. В нашем случае это диапазон столбца «Ставка» на Листе 2. Кликаем по выделенному фрагменту правой кнопкой мыши. В открывшемся списке выбираем пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl+C. После этого перемещаемся на Лист 1.
Существует также альтернативный вариант. Он, кстати, является единственным для более старых версий Excel. В контекстном меню наводим курсор на пункт «Специальная вставка». В открывшемся дополнительном меню выбираем позицию с одноименным названием.
Урок: Специальная вставка в Экселе
Способ 5: связь между таблицами в нескольких книгах
Кроме того, можно организовать связь между табличными областями в разных книгах. При этом используется инструмент специальной вставки. Действия будут абсолютно аналогичными тем, которые мы рассматривали в предыдущем способе, за исключением того, что производить навигацию во время внесений формул придется не между областями одной книги, а между файлами. Естественно, что все связанные книги при этом должны быть открыты.
-
Выделяем диапазон данных, который нужно перенести в другую книгу. Щелкаем по нему правой кнопкой мыши и выбираем в открывшемся меню позицию «Копировать».
Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.
Разрыв связи между таблицами
Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.
Способ 1: разрыв связи между книгами
Разорвать связь между книгами во всех ячейках можно, выполнив фактически одну операцию. При этом данные в ячейках останутся, но они уже будут представлять собой статические не обновляемые значения, которые никак не зависят от других документов.
-
В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные». Щелкаем по значку «Изменить связи», который расположен на ленте в блоке инструментов «Подключения». Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
Способ 2: вставка значений
Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.
-
Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать». Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C.
Как видим, в Excel имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.
Читайте также: