Ссылки на другие книги Excel
| Категория: Приемы и советы, Формулы и функции | Опубликовано 17-02-2014
41
Исходные данные для расчета могут быть не только в одной рабочей книге, но и в разных книгах. Чтобы связать книги Excel, необходимо предварительно открыть все связываемые книги, а затем при создании формулы перейти в нужную книгу Excel, открыть нужный лист, выделить ячейку или диапазон ячеек, ввести знак следующей операции или завершить формулу клавишей Enter.
Изначально в формуле будет прописан адрес ячейки следующего вида:
[Курсы валют.xlsx]ЦБ’!$B$3,
где в квадратных скобках указывается название файла – в приведенном примере ссылки – Курсы валют.xlsx, затем название листа ЦБ и адрес ячейки – $B$3. По умолчанию, ссылка на ячейку другого файла абсолютная, однако при необходимости ее можно сделать и относительной и смешанной.
При открытии файла (если другие книги-источники будут закрыты) на экране появляется ПРЕДУПРЕЖДЕНИЕ СИСТЕМЫ БЕЗОПАСНОСТИ [SECURITY WARNING], которое сообщает, что автоматическое обновление ссылок отключено. Если нажать Включить содержимое [Enable Content], то результаты расчетов будут обновлены.
Вид ссылки немного изменяется – в него добавляется полный путь расположения файла, например:
‘D:\Материалы EXCEL Level 2\[Курсы валют.xlsx]ЦБ’!$B$3.
Для редактирования связи с внешним источником на вкладке Данные [Data], в группе Подключения [Connections], выбрать Изменить связи [Edit Links] 
В окне Изменение связей [Edit Links] выбрать нужное действие:
- Обновить [Update Values] – обновление выделенного источника. Состояние Неизвестно свидетельствует о том, что в данном окне обновление данных из источников не производилось. После выполнения команды Обновить состояние изменяется на OK.
- Изменить [Change source] – изменение внешнего источника в случае смены имени файла или перемещения источника в другую папку. Следует помнить, что структура книги изменяться не должна, т.к. внутри выбираемого файла название листа и адрес нужной ячейки должны совпадать с изначально заданными в формуле.
- Открыть [Open Source] – открытие файла-источника.
- Разорвать связь [Break Link] – разорвать связь с внешним источником. Формулы в ячейках заменяются значениями, которые получены в ячейках на данный момент. Команду отменить нельзя, поэтому следует создать копию файла, чтобы при необходимости остался исходный файл с формулами для последующих обновлений.
- Запрос на обновление связей [Startup Prompt] – возможность настроить вариант обновления при открытии файла:
– Пользователь указывает, нужно ли задавать вопрос [Let users choose to display the alert or not]]
– Не задавать вопрос и не обновлять связи [Don’t display the alert and don’t update automatic links]
– Не задавать вопрос и обновлять связи [Don’t display the alert and update links]
30.05.2014 (12:25)
У меня, обычно, возникает проблема как увидеть сами ячейки, в которых в формулах ссылки на другие файлы. Можно же это как-то сделать?
16.07.2014 (20:49)
присоединяюсь к вопросу
30.07.2014 (19:19)
Занятно-занятно, нигде раньше на такое не натыкался.
Тема довольно интересная для новичка
30.07.2014 (22:37)
А вот и ответ на Ваши вопросы:
“Как увидеть есть ли ссылки на другие файлы, и где они находятся в книге Excel?” http://mirexcel.ru/?p=250
31.07.2014 (11:35)
спасибо, я нашел ответы на свои вопросы
01.08.2014 (09:49)
Спасибо, кратно и информативно
03.08.2014 (16:50)
открыл источники, пишу формулу, но при переходе к другому файлу, формула завершается 🙁 Как быть?
03.08.2014 (22:31)
Лёня, вероятнее всего, у вас открыта программа excel несколько раз и в каждой из них уже открыты файлы. Закройте файлы и откройте в одном окне программы
05.08.2014 (16:08)
Я правильно понимаю, что использование внешних файлов в любых командах оставляет след в окне “Изменить связи”?
06.08.2014 (14:34)
может ли быть так. что у меня есть ссылки на другие файлы, но данная команда недоступна?
13.08.2014 (14:15)
Здорово, прочитал 2 статьи про внешние источники и нашел ответы на свои вопросы!
20.08.2014 (10:38)
Если у меня файл находится на общем диске, то мне подойдет такой алгоритм действий?
25.08.2014 (11:12)
Жанна, алгоритм будет такой же
27.10.2014 (20:42)
А я считаю, что все это верно и очень точно подмечено! И таких мелочей можно накопать тысячу!
17.11.2014 (08:19)
Все просто, когда знаешь. Я вечно ищу все по всем ячейкам. Хорошо быть умной! Я тоже хочу. Спасибо вам за сайт, очень полезный ресурс, мне ВСЁ-ВСЁ-ВСЁ здесь очень нравится…
24.01.2015 (14:38)
Есть ли подобная команда, при использовании данных с разных листов, но внутри одной книги?
04.02.2015 (21:45)
Пётр, как вариант через http://mirexcel.ru/?p=120
10.03.2015 (12:22)
Может быть такое, что ссылки на другие файлы вроде как есть, но в окне Изменить связи я их не вижу?
11.03.2015 (23:32)
Arn, возможно, используется в качестве источника в окне Данные – Проверка данных. Посмотрите.
18.05.2015 (13:04)
Прошу прощения, что вопрос немного не по Excel, но где подобная команда в PowerPoint?
22.05.2015 (18:21)
Robert, если 2013 офис, то открываете Файл – Сведения и в правой части найдете “изменить связи”
09.06.2015 (12:12)
Здравствуйте.
вопрос такой: при указании ссылки с целым числом на ячейку в другом документе все в порядке, оно и отображается, а если в этой ячейке не число а формула, даже простая, показывает либо 0,0, либо #ЗНАЧ. В зависимости от формата ячеек. Как получить значение?
11.06.2015 (00:57)
Евгений, поиграла с разными своими файлами, такой эффект не обнаружила. Если это не секретная информация, пришлите mirexcel@ya.ru – попробую разобраться
01.12.2016 (19:28)
ДД!
У меня 4 файла в одной папке ссылаются друг на друга, но при открытии этих файлов на другом компьютере все формулы ломаются. Что делать?
09.12.2016 (00:14)
ДД, Алёна!
Если Вы на другом компьютере и у Вас только один файл, то это правильно. Если Вы все файлы храните в одной папке и её перемещаете (копируете) на другой компьютер, то Excel сам ссылки корректируют. Но если расположение иное, то через команду “Изменить источник” необходимо “рассказать” об их новом расположении – простым открытием.
11.02.2017 (22:15)
Я внимательно вчитываюсь в каждую статью и радуюсь новым знаниям
28.02.2017 (22:52)
Boris, ничего нового не встречала
28.02.2017 (09:40)
Здравствуйте! как по умолчанию установить относительную ссылку на другую книгу? Именно относительную, что бы каждый раз не убирать значки доллара. подскажите. пжлст, очень нужно.
28.02.2017 (22:50)
Татьяна, здравствуйте!
Стандартными средствами такое не встречалось. Придется всё-таки 3 раза нажимать клавишу F4.
25.03.2017 (10:51)
Если я разорвал связь с источником. Есть ли способ быстренько вернуть?
26.03.2017 (22:08)
Увы, если Вы не создали копию файла, то только создавать формулы заново.
30.03.2017 (10:30)
Можно сделать так, чтобы при открытии файла сразу обновлялись все связи?
23.04.2017 (15:52)
Когда знаешь – всё просто, когда не знаешь – мучения. Спасибо Вам огромное за Ваш труд!
29.06.2017 (15:23)
Добрый день! как открыть файлы с перекрестными ссылками на другом компьютере без ломки ссылок и без изменения расположения каждый раз при открытии файлов на другом компьютере?
06.07.2017 (21:51)
Открыть без обновления
22.09.2017 (00:24)
Почему когда я открываю файл на своем компьютере, у меня никогда не предлагается выбрать ОБновление?
25.10.2017 (20:44)
Варианта, обычно два:
1) Может,открыть файл-источник
2) настройка в программе на автоматическое обновление связей без вопроса к пользователю
05.02.2018 (16:47)
Есть возможность уменьшить размер файла, если в нем есть линки на другие файлы?
16.03.2018 (23:27)
Mila, можно попробовать через VBA
17.02.2019 (21:51)
У меня есть 5 файлов, 1-й ссылается на 2-й, 2-й на 3-й и т.д. Когда открываю 1-й и обновляю, то ничего не происходит. Что я делаю не так?
24.02.2019 (13:45)
Tamara, чтобы у Вас обновилось, следует открыть все 5 файлов