Внешние ссылки
Связи между книгами. Управление внешними ссылками.





Составление формул без переноса данных из одной книги Excel в другую возможно! Для этого нужно научиться создавать внешние ссылки (связи) и управлять ими.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Связи между книгами. Управление внешними ссылками.

Составление формул без переноса данных из одной книги Excel в другую возможно! Для этого нужно научиться создавать внешние ссылки (связи) и управлять ими. Можно ли составлять формулы, используя данные из другого файла Excel? Как поменять такие связи, если связанных файл будет переименован или перемещён в другую папку? А что делать с такими ссылками перед отправкой файла по почте? Обо всём об этом читайте в этом уроке.

Создание связей между книгами
Откройте все прикрепленные к теме файлы (их должно быть три) – на этот раз будем разбираться с вот такой задачей – есть два файла ( «Прайс» и «Остатки» ), из которых мы регулярно берем информацию для своего файла «Отчет» : из файла «Остатки» получаем количество для одноименного столбца книги «Отчет» , а из файла «Прайс» - цену. Сейчас будем учиться, как настроить ссылки между книгами один только раз, а в дальнейшем просто обновлять их. План действий следующий:
1. Откройте книгу «Отчет» и в ячейке B5 пропишите ссылку: ставим знак равно, после чего переходим в книгу «Остатки» и щелкаем по ячейке с таким же адресом, т.е. тоже B5 . В итоге ссылка должна выглядеть следующим образом:

2. Потом избавляемся от знаков доллара – это мы уже умеем, но напомню – находясь в режиме редактирования содержимого ячейки, помещаем курсор в любое место адреса и жмем клавишу F4 до тех пор, пока все знаки долларов не пропадут, после чего подтверждаем сделанные изменения нажатием на клавишу Enter . И только теперь копируем формулу до конца таблицы ( Double-клик по маркеру заполнения, например)

Что означают и зачем нужны один или два знака доллара в адресе ячейки, а также как их быстро расставить – можно узнать из Урока "Типы ссылок"

3. Аналогичным образом организуйте ссылки для столбца «Цена» , должно получиться вот так:

Кстати, как облегчить себе жизнь и зафиксировать адреса ячеек или целых диапазонов без знаков доллара, рассказывает Урок "Имена ячеек"

4. Попробуйте прямо сейчас изменить любые данные по количеству и/или цене в одноименных файлах и увидите, что данные в файле с отчетом так же изменяются. Но давайте продолжим моделировать реальную ситуацию – сохраните и закройте файл «Отчет» (Только один этот файл(!) Другие два «Остатки» и «Прайс» должны остаться открытыми)

Управление внешними ссылками
5. Внесите любые изменения в исходные данные в оба файла – увеличьте количество, уменьшите цены – поступите с ними произвольным образом
6. Файл с остатками сохраните и закройте – это одна ситуация, когда исходные данные в уже использованном файле изменились
7. Файл с ценой сохраните под другим именем (обычно это дата, на которую составлен документ, но для учебных целей можно просто добавить к имени файла цифру 2) – это другая реальная ситуация, когда файл переложили в другую папку или нужно собрать аналогичный отчет по образу и подобию – не прописывать же ссылки заново. Мы и не будем :-) Закройте этот файл тоже и проверьте – ни одного открытого файла Excel не должно остаться
Откройте книгу «Отчет» - в таблице содержатся старые данные, но это дело легко поправить. Для управления внешними связями выполняем команду Данные – Подключения – Изменить связи [Data – Connections – Edit Links]. Появится диалоговое окно Изменение связей [Edit Links], в центральной части которого виден полный список всех книг, с которыми имеются связи. Чтобы их актуализировать, можно пойти одним из двух путей в зависимости от ситуации:
1. Чтобы просто обновить связи с книгой-источником, выделяем ее в списке и жмем кнопку Обновить [Update Values], что в правой части окна
2. Если книга-источник была сохранена под новым именем – выделяем в списке файл со старым названием и жмем кнопку Изменить [Change Source], а далее открывается стандартное окно проводника, находим наш файл и нажимаем Открыть [Open] – в этот же момент обновятся все ссылки

Разрыв связей между книгами с сохранением текущих значений
Есть еще пара полезных кнопок в этом окне, а именно:
с помощью кнопки Открыть [Open Source] можно открыть файл, который выбран в списке связей (то есть не нужно искать его в проводнике или каким-то иным способом)
а при помощи кнопки Разорвать связь [Break Link] можно избавиться от связей с выбранной книгой, а вместо них зафиксировать в ячейках текущие значения. Этот инструмент удобен при отправке файла по почте, и даже желателен. Если связи в такой ситуации не разорвать, то получатель увидит в ячейках примерно следующее (путь до ячейки будет теперь включать и название жесткого диска и вложенную структуру папок и еще много всего):

Или же эти ссылки вообще «слетят» и в ячейках вместо значений поселится ошибка вида #ССЫЛКА!
Ну и напоследок скажу, что в данном примере мы разобрали только основы управления внешними связями, причем на примере таблиц одинакового размера. Если же они разные – подстановку нужно значения можно выполнять при помощи функции ВПР, например.

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора.

Похожие материалы

Примеры использования функции ЕСЛИ
ЕСЛИ логическая функция
Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

Внесение числовых и текстовых данных, создание списков
Создание списков
Урок рассказывает, как быстро и без ошибок создавать числовые и текстовые списки в программе Excel, используя встроенные "знания" программы о последовательности чисел и текста.

Создание и настройка выпадающих списков.
Выпадающие списки.
Как ограничить ввод данных в ячейке списком допустимых значений (справочником)? Где расположить этот справочник. Команда Проверка данных содержит такую настройку.

Как разделить текст ячейки Excel по разным столбцам, используя числовую закономерность расположения знаков или символ-разделитель
Текст по столбцам
Как разделить текст по разным столбцам (колонкам), если в исходном документе разнородные фрагменты текста записаны в одной ячейке?

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



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