rss
  •  

Ссылки на другие книги Excel

| Категория: Приемы и советы, Формулы и функции |

39

Исходные данные для расчета могут быть не только в одной рабочей книге, но и в разных книгах. Чтобы связать книги Excel, необходимо предварительно открыть все связываемые книги, а затем при создании формулы перейти в нужную книгу Excel, открыть нужный лист, выделить ячейку или диапазон ячеек, ввести знак следующей операции или завершить формулу клавишей Enter.

celllinks4.png

Изначально в формуле будет прописан адрес ячейки следующего вида:
[Курсы валют.xlsx]ЦБ’!$B$3,
где в квадратных скобках указывается название файла —  в приведенном примере ссылки — Курсы валют.xlsx, затем название листа ЦБ и адрес ячейки — $B$3. По умолчанию, ссылка на ячейку другого файла абсолютная, однако при необходимости ее можно сделать и относительной и смешанной.

При открытии файла (если другие книги-источники будут закрыты) на экране появляется ПРЕДУПРЕЖДЕНИЕ СИСТЕМЫ БЕЗОПАСНОСТИ [SECURITY WARNING], которое сообщает, что автоматическое обновление ссылок отключено. Если нажать Включить содержимое [Enable Content], то результаты расчетов будут обновлены.

celllinks.png

Вид ссылки немного изменяется — в него добавляется полный путь расположения файла, например:
‘D:\Материалы EXCEL Level 2\[Курсы валют.xlsx]ЦБ’!$B$3.

Для редактирования связи с внешним источником на вкладке Данные [Data], в группе Подключения [Connections], выбрать Изменить связи [Edit Links] celllinks3.png

celllinks2.png

В окне Изменение связей [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]



Оставьте комментарий!

На сообщение “Ссылки на другие книги Excel” комментариев 39

  1. Лана:

    У меня, обычно, возникает проблема как увидеть сами ячейки, в которых в формулах ссылки на другие файлы. Можно же это как-то сделать?

  2. Fedor:

    присоединяюсь к вопросу

  3. Шурик:

    Занятно-занятно, нигде раньше на такое не натыкался.
    Тема довольно интересная для новичка

  4. Ольга Кулешова:

    А вот и ответ на Ваши вопросы:
    «Как увидеть есть ли ссылки на другие файлы, и где они находятся в книге Excel?» http://mirexcel.ru/?p=250

  5. Semёn:

    спасибо, я нашел ответы на свои вопросы

  6. Kira:

    Спасибо, кратно и информативно

  7. Лёня:

    открыл источники, пишу формулу, но при переходе к другому файлу, формула завершается 🙁 Как быть?

  8. Ольга Кулешова:

    Лёня, вероятнее всего, у вас открыта программа excel несколько раз и в каждой из них уже открыты файлы. Закройте файлы и откройте в одном окне программы

  9. Люба:

    Я правильно понимаю, что использование внешних файлов в любых командах оставляет след в окне «Изменить связи»?

  10. Павел:

    может ли быть так. что у меня есть ссылки на другие файлы, но данная команда недоступна?

  11. Илья:

    Здорово, прочитал 2 статьи про внешние источники и нашел ответы на свои вопросы!

  12. Жанна:

    Если у меня файл находится на общем диске, то мне подойдет такой алгоритм действий?

  13. Ольга Кулешова:

    Жанна, алгоритм будет такой же

  14. Леонид:

    А я считаю, что все это верно и очень точно подмечено! И таких мелочей можно накопать тысячу!

  15. Татьяна:

    Все просто, когда знаешь. Я вечно ищу все по всем ячейкам. Хорошо быть умной! Я тоже хочу. Спасибо вам за сайт, очень полезный ресурс, мне ВСЁ-ВСЁ-ВСЁ здесь очень нравится…

  16. Пётр:

    Есть ли подобная команда, при использовании данных с разных листов, но внутри одной книги?

  17. Ольга Кулешова:

    Пётр, как вариант через http://mirexcel.ru/?p=120

  18. Arn:

    Может быть такое, что ссылки на другие файлы вроде как есть, но в окне Изменить связи я их не вижу?

  19. Ольга Кулешова:

    Arn, возможно, используется в качестве источника в окне Данные — Проверка данных. Посмотрите.

  20. Robert:

    Прошу прощения, что вопрос немного не по Excel, но где подобная команда в PowerPoint?

  21. Ольга Кулешова:

    Robert, если 2013 офис, то открываете Файл — Сведения и в правой части найдете «изменить связи»

  22. Евгений:

    Здравствуйте.
    вопрос такой: при указании ссылки с целым числом на ячейку в другом документе все в порядке, оно и отображается, а если в этой ячейке не число а формула, даже простая, показывает либо 0,0, либо #ЗНАЧ. В зависимости от формата ячеек. Как получить значение?

  23. Ольга Кулешова:

    Евгений, поиграла с разными своими файлами, такой эффект не обнаружила. Если это не секретная информация, пришлите mirexcel@ya.ru — попробую разобраться

  24. Алёна:

    ДД!
    У меня 4 файла в одной папке ссылаются друг на друга, но при открытии этих файлов на другом компьютере все формулы ломаются. Что делать?

  25. Ольга Кулешова:

    ДД, Алёна!
    Если Вы на другом компьютере и у Вас только один файл, то это правильно. Если Вы все файлы храните в одной папке и её перемещаете (копируете) на другой компьютер, то Excel сам ссылки корректируют. Но если расположение иное, то через команду «Изменить источник» необходимо «рассказать» об их новом расположении — простым открытием.

  26. Boris:

    Я внимательно вчитываюсь в каждую статью и радуюсь новым знаниям

  27. Ольга Кулешова:

    Boris, ничего нового не встречала

  28. Татьяна:

    Здравствуйте! как по умолчанию установить относительную ссылку на другую книгу? Именно относительную, что бы каждый раз не убирать значки доллара. подскажите. пжлст, очень нужно.

  29. Ольга Кулешова:

    Татьяна, здравствуйте!
    Стандартными средствами такое не встречалось. Придется всё-таки 3 раза нажимать клавишу F4.

  30. Альберт:

    Если я разорвал связь с источником. Есть ли способ быстренько вернуть?

  31. Ольга Кулешова:

    Увы, если Вы не создали копию файла, то только создавать формулы заново.

  32. Nиколаy:

    Можно сделать так, чтобы при открытии файла сразу обновлялись все связи?

  33. Tома:

    Когда знаешь — всё просто, когда не знаешь — мучения. Спасибо Вам огромное за Ваш труд!

  34. Марина:

    Добрый день! как открыть файлы с перекрестными ссылками на другом компьютере без ломки ссылок и без изменения расположения каждый раз при открытии файлов на другом компьютере?

  35. Ольга Кулешова:

    Открыть без обновления

  36. Армен:

    Почему когда я открываю файл на своем компьютере, у меня никогда не предлагается выбрать ОБновление?

  37. Ольга Кулешова:

    Варианта, обычно два:
    1) Может,открыть файл-источник
    2) настройка в программе на автоматическое обновление связей без вопроса к пользователю

  38. Mila:

    Есть возможность уменьшить размер файла, если в нем есть линки на другие файлы?

  39. Ольга Кулешова:

    Mila, можно попробовать через VBA