Общая цель ссылок — предотвратить появление одних и тех же формул или данных в разных книгах и листах. Если рабочая книга содержит необходимую информацию, вы можете использовать ссылки для связывания данных без необходимости воссоздавать их в другой рабочей книге.
Для успешного выполнения этих действий данные в клиентской книге всегда должны соответствовать данным в исходной книге. Чтобы убедиться в этом, обратите внимание:
- Если исходная и клиентская книги открыты, Excel автоматически обновляет ссылки при изменении данных в исходной книге.
- Если исходная книга открыта при открытии клиентской книги, Excel также автоматически обновляется.
- Закрывается ли исходная рабочая книга при открытии рабочей книги, в которой используются ее данные. Excel отображает строку предупреждения, указывающую, что автоматическое обновление ссылок отключено. В этом случае нажмите кнопку «Параметры», установите переключатель в положение «Включить это содержимое», а затем нажмите кнопку «ОК». Вы также можете включить автоматическое обновление ссылок для всех книг. Для этого нажмите кнопку «Файл», затем «Параметры», «Центр управления безопасностью», затем нажмите кнопку «Параметры центра безопасности», затем на вкладке «Внешнее содержимое» установите переключатель, чтобы включить автоматическое обновление для всех ссылок в книге.
- Если вы не обновили ссылки при открытии зависимого документа, вы можете обновить их в любое время позже. Для этого перейдите на вкладку «Данные» на ленте, затем нажмите кнопку «Редактировать ссылки». Вы увидите диалоговое окно «Редактировать ссылки» на экране. В этом окне выберите нужное подключение и нажмите кнопку Обновить.
Если имя документа, содержащего загруженные данные, изменилось, вам нужно будет изменить ссылку, чтобы сохранить данные в актуальном состоянии. Вы можете изменить все ссылки напрямую, выполнив следующие действия:
- Откройте книжный клиент и активируйте его. Перейдите на вкладку «Данные» на ленте, затем нажмите кнопку «Изменить ссылки». Вы увидите диалоговое окно «Редактировать ссылки» на экране.
- Нажмите на ссылку (ссылку), с которой хотите работать.
- Нажмите кнопку «Изменить». Excel отобразит окно Find/File Explorer, где вы можете указать путь к нужному документу.
- Нажмите Закрыть, чтобы вернуться к книге.
Курс для школьников, которые боятся математики или потеряли к ней интерес. Занятия по подготовке к ЕГЭ по математике ведут два учителя: опытный психолог помогает восстановить интерес к учебе; пробелы в знаниях восполняет опытный учитель-математик по специальной программе. Студенты не только изучают математику, но и получают устойчивый психологический результат: уверенность в себе, возможность дальнейшего самостоятельного обучения, интерес к предмету.
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Создание связи между таблицами Excel
Связь электронной таблицы Excel — это формула, которая возвращает данные из ячейки в другой книге. Когда вы открываете книгу, содержащую ссылки, Excel считывает последнюю информацию из исходной книги (обновляет ссылки)
Отношения между таблицами в Excel используются для извлечения данных как из других листов книги, так и из других книг Excel. Например, у вас есть таблица с расчетом общей суммы продаж. В расчете используются цены на продукцию и объем продаж. В этом случае имеет смысл создать отдельную таблицу с данными о ценах, которые будут извлекаться по ссылкам из первой таблицы.
Когда вы создаете связь между таблицами, Excel создает формулу, которая включает имя исходной книги в квадратных скобках , имя листа с восклицательным знаком в конце и ссылку на ячейку.
Создание связей между рабочими книгами
- Откройте обе книги в Excel
- В исходной книге выберите ячейку для связи и скопируйте ее (сочетание клавиш Ctrl + C)
- Перейдите в рабочую книгу назначения, щелкните правой кнопкой мыши ячейку, в которую мы хотим поместить ссылку. В раскрывающемся меню выберите «Специальная вставка
- В появившемся диалоговом окне «Специальная вставка» выберите «Вставить ссылку.
Есть еще один, более простой вариант создания связи между таблицами. В ячейке, куда мы хотим вставить ссылку, ставим знак равенства (как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, нажимаем Enter.
Вы можете использовать инструменты копирования и автозаполнения, чтобы связать формулы так же, как и обычные формулы.
Прежде чем создавать связи между таблицами
Прежде чем вы начнете рассказывать о своих больших идеях, вот несколько советов по работе со связями в Excel:
Создавайте удобные связи. Нет автоматического поиска всех ячеек, содержащих ссылки. Поэтому используйте определенный формат для быстрого определения ссылок на другие таблицы, иначе связанный документ может разрастись до такой степени, что его будет сложно поддерживать.
Автоматические расчеты. Исходная рабочая книга должна работать в режиме автоматического расчета (установлено по умолчанию). Чтобы изменить вариант расчета, перейдите на вкладку «Формулы» в группе «Расчет». Выберите «Параметры расчета» -> «Автоматически.
Избегайте циклических ссылок. Циклические ссылки, когда две книги содержат ссылки друг на друга, могут привести к тому, что файл будет открываться и работать медленнее.
Читайте также: Где хранятся резервные копии Excel
Обновление связей
Чтобы вручную обновить связь между таблицами, перейдите на вкладку Данные в группе Соединения. Нажмите кнопку «Редактировать ссылки.
В появившемся диалоговом окне «Редактировать ссылки» выберите интересующую вас ссылку и нажмите кнопку «Обновить.
Разорвать связи в книгах Excel
Разрыв ссылки на источник заменит существующие формулы ссылки значениями, которые они возвращают. Например, ссылка =[Source.xlsx]Prices!$B$4 будет заменена на 16. Отменить ссылку нельзя, поэтому перед выполнением операции рекомендуется сохранить книгу.
Перейдите на вкладку «Данные» и перейдите в группу «Подключения». Нажмите кнопку «Редактировать ссылки». В появившемся диалоговом окне «Редактировать ссылки» выберите интересующую вас ссылку и нажмите кнопку «Разорвать ссылку.
Обобщаем с умом: внешние ссылки и консолидация в Excel
Здравствуйте дорогие читатели. Как часто вы сталкиваетесь с задачей консолидации или суммирования данных с разных листов и книг в единую таблицу? Я думаю, что часто. Консолидация в Excel — трудоемкая, кропотливая и утомительная работа, но в этой статье я покажу вам, как ее упростить. Существует два подхода к сбору информации из разных источников: внешние ссылки на ячейки или инструмент консолидации».
Внешние ссылки в Excel
Думаю, каждый из вас знает, что внешняя ссылка в Excel — это ссылка на ячейку, расположенную в другой книге. И самый распространенный способ собрать данные из разных источников в один — это внешние ссылки.
Если результирующая таблица достаточно проста, можно использовать этот метод; В противном случае легко запутаться и утомительно вручную вводить несколько сотен ссылок на ячейки, и велика вероятность ошибки. Однако для небольших таблиц это решение.
Существует много способов сослаться на ячейку на другом листе или в книге. Расскажу самое простое:
- Открыть все файлы: резюме и исходники
- Щелкните ячейку, в которую вы хотите вставить ссылку, и введите знак равенства (как при создании формул).
- Выберите файл с исходным значением и нажмите на это значение. Ссылка на это значение появится в строке формул.
- Если вам нужно добавить к этому значению другие из других источников, добавьте «+» и повторите шаги 3-4, пока оно не будет полностью заполнено.
- Когда все ссылки на ячейки будут сделаны, нажмите Enter, и будет рассчитан итоговый результат.
Внешняя ссылка имеет специфический вид: имя файла в квадратных скобках, имя листа, восклицательный знак, координаты ячейки:
Когда вы закроете исходные файлы, адрес исходного файла также будет добавлен к ссылке слева:
По умолчанию все внешние ссылки являются абсолютными, то есть не изменяются при копировании. При необходимости сделайте их относительными, нажимая F4, пока не получите нужный тип ссылки.
Не перемещайте исходные файлы, так как это сломает ссылки, а внешние ссылки больше не будут работать.
Если и итоговый документ, и исходный документ открыты на одном компьютере, изменения в исходной таблице будут немедленно «вставлены» в сводку. Если хотя бы одна из этих книг закрыта, потребуется принудительное обновление. Также при повторном открытии сводного файла Excel предложит обновить данные. Сделайте это, если вам нужно обновить информацию.
Управление внешними ссылками
Чтобы управлять ссылками на источники в других книгах, щелкните Лента данных — Подключения — Редактировать ссылки. В открывшемся окне отобразится полный список подключенных файлов и командных кнопок:
- Обновить: принудительное обновление всех ссылок
- Изменить: укажите новую книгу Excel, в которой находятся подключенные данные
- Открыть: открыть исходный документ
- Break Link — Удаляет внешние ссылки из этой ссылки, заменяет их значением
- Статус: обновляет статус доступности файлов данных
- Запрос на обновление ссылок: сообщите программе, должна ли она обновлять данные при открытии файла
Если у вас есть большой файл с несколькими листами, поиск внешних ссылок может занять много времени. Так как же быстро найти внешние ссылки в документе Excel? Я дам вам рецепт. Все эти ссылки имеют общий элемент: открывающие и закрывающие скобки, содержащие имя исходного файла. В остальных случаях такие скобки используются редко. Нажмите Ctrl+F, чтобы открыть окно поиска, введите квадратную скобку в поле поиска и нажмите «Искать все». Результаты поиска обязательно будут включать все внешние ссылки.
Использование внешних ссылок — самый простой и очевидный способ собрать данные из нескольких таблиц в одну. Но если данных много, этот способ неприменим. Затем мы используем инструмент консолидации».
Консолидация данных в одну таблицу
Этот инструмент упрощает консолидацию информации в таблице данных, это хороший способ автоматизировать процесс.
Рассмотрим его работу на примере. У нас есть 3 файла с данными о продажах по 3 подразделениям компании. Необходимо собрать общие показатели по организации, причем сделать это быстро, ведь информация нужна «на вчера».
Давайте действовать. Откройте все исходные файлы и хранилище. Активируйте «Сводка». Смотрим ленту Данные — Работа с данными — Консолидация. Откроется окно подключения к внешним данным.
Теперь давайте пошагово:
- В поле «Функция» выберите «Сумма» (или что-то другое, если необходимо)
- В поле «Ссылка» укажите ссылку на данные в первом исходном файле (вместе с заголовком). Столбцы лучше указывать целиком, например A:D. Тогда не нужно будет переопределять диапазон при добавлении новых данных.
- Нажмите «Добавить», ссылка появится в поле «Список диапазонов»
- Повторяйте шаги 2 и 3, пока не добавите ссылки на все исходные файлы
- Установите флажки «Заголовки верхней строки» и «Значения левого столбца», чтобы Excel сам подбирал заголовки и заголовки категорий в строках
- Установите флажок «Создавать ссылки на исходные данные», тогда консолидация будет производиться с использованием ссылок на ячейки. В противном случае будут просто вставлены итоговые значения.
- Нажмите OK, чтобы завершить настройку консолидации.
В результате в файле «Сводка» появится сводный список таблиц всех подключенных файлов. Если поставить галочку «Создавать ссылки с исходными данными», то итоговая таблица будет структурирована, вы сможете развернуть каждый элемент и увидеть детали каждого отдела отдельно
Если исходные таблицы изменились, можно перестроить сводку. Для этого снова откройте окно «Консолидация» и нажмите «ОК.
Такой простой инструмент, решающий одну из самых надоедливых задач в Excel. Я предпочитаю использовать его, ручная работа по созданию ссылок не для меня. Я рекомендую вам использовать его тоже. Хотя лучшим решением будет использование сводной таблицы (когда это возможно) или создание собственной программы VBA с подробными динамическими настройками.
Это оставляет вам возможность поэкспериментировать с консолидацией и начать писать сообщение о защите данных в Excel. Читайте, эта информация сейчас как никогда актуальна. До встречи!
Как в excel обновить связи
На этом шаге мы закончим рассмотрение принципов работы со связанными объектами.
Редактирование связанного объекта
Редактирование связанной информации выполняется в исходном приложении. Самый простой способ открыть исходное приложение — дважды щелкнуть соответствующую информацию в документе Word :
Рис. 1. Откройте Excel для редактирования связанной таблицы
Вы также можете использовать кнопку Open Source в диалоговом окне Links (команда Links в меню Edit), чтобы открыть исходное приложение):
Рисунок 2. Диалоговое окно «Ссылки
Обновление связи
По умолчанию (рис. 2) установлено автоматическое обновление ссылок (переключатель включается автоматически в диалоговом окне «Ссылки» в области «Метод обновления ссылок»), то есть при изменении информации в исходном документе (таблице). Расчет в Excel), обновляется в документе Word, если он открыт. Ссылки обновляются автоматически при открытии документа Word. Автоматическое обновление данных можно отменить в диалоге Connections, который открывается при вызове одноименной команды меню Edit Word Editor. Диалоговое окно содержит список всех ссылок в активном документе Word. Чтобы обновление происходило только при нажатии кнопки «Обновить .
Примечание. Чтобы выбрать несколько ссылок, удерживайте нажатой клавишу Ctrl при их выборе. После включения переключателя по требованию ссылки обновляются только при нажатии кнопки «Обновить» в диалоговом окне «Ссылки .
Команда «Ссылки» в меню «Правка» недоступна, если документ не содержит связанной информации.
Чтобы предотвратить обновление связанной информации, вы должны заблокировать ссылку. Для этого в диалоговом окне «Ссылки» выберите ссылку или ссылки, установите параметр «Не обновлять ссылки» и нажмите «ОК». Блокировка соединения не означает его разрыв. Чтобы отменить его, просто отключите параметр «Не обновлять ссылку» в окне «Ссылки» и нажмите кнопку «ОК .
Разрыв связи
Чтобы разорвать связь, используйте кнопку «Разорвать связь» в диалоговом окне «Ссылки». После нажатия этой кнопки необходимо подтвердить отключение в открывшемся окне запроса. После разрыва связи соответствующая информация остается в документе Word, но ее больше нельзя обновить. Вы не можете сбросить соединение!
Изменение связи
Ссылку также можно разорвать, переименовав или удалив исходный документ. В этом случае вы должны указать ссылку на исходный документ или ссылку на другой документ. Чтобы изменить или восстановить ссылку, необходимо нажать кнопку «Изменить источник» в диалоговом окне «Ссылки». В результате откроется диалоговое окно «Изменить шрифт» — стандартное окно открытия системных документов Windows, где необходимо выбрать документ, с которым восстанавливается соединение:
Рис. 3. Диалоговое окно «Изменить шрифт
Если нужный документ не отображается в списке, необходимо выбрать другой диск или папку.
На следующем шаге мы рассмотрим пример внедрения объекта в документ Word .