Эксель ссылка на другую книгу

Эксель ссылка на другую книгу

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

К примеру, можно разместить данные о ценах на товары на одном листе, а во всех расчетных таблицах прописать ссылки на ячейки листа с ценами. При изменении цены, необходимо будет изменить данные только на листе цен, на всех других листах, где имеются ссылки, изменение пройдет автоматически.

I. Чтобы создать ссылку на ячейку из другого листа той же книги необходимо:

1. Выделить ячейку, в которую необходимо вставить ссылку.

2. Вставить знак «=»

3. Перейти, с помощью вкладок листов, на лист, из которого будем брать данные.

4. Выделить ячейку с необходимым значением.

Таким образом, мы получим ссылку на ячейку из Листа 2, и в исходной ячейке отобразится нужное значение.
То же самое можно было получить, если вручную ввести в исходную ячейку формулу «=Лист2!В2».

Для заполнения остальных ячеек таблицы, можно протянуть формулу с помощью маркера заполнения,

Теперь при изменении цен на Листе 2 , автоматически будут меняться и значения цен в таблице «Объем продаж».

Если необходимо вставить ссылку на ячейку, находящуюся в текущей книге, но на другом листе, нужно в строке формул ввести ссылку в виде:

= Имя листа ! Адрес ячейки

II. Аналогично можно создать ссылку на ячейку из другой книги.

Первый способ — в ячейку первой книги вносим знак «=», переходим ко второй книге, где выбираем нужную ячейку. Далее нажимаем Enter .

Второй способ — вручную прописать ссылку в ячейку.

Пример ссылки, если книги находятся в одном каталоге: =[Книга1.xlsx]Лист1!A1

=[ Имя книги ] Имя листа! Адрес ячейки

Если книга, на которую вы ссылаетесь, расположена в другом каталоге, то необходимо указать полный путь доступа к ней непосредственно в формуле.

Пример ссылки на ячейку в книге, находящейся в каталоге C:Мои документы:

=C: Мои документы [Книга1.xlsx]Лист1!A1

Понравилось? Поделись с друзьями

Семинары. Вебинары. Конференции

Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

Исходные данные для расчета могут быть не только в одной рабочей книге, но и в разных книгах. Чтобы связать книги 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]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Подключение к беспроводной сети вручную windows 10

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

У меня есть 5 файлов, 1-й ссылается на 2-й, 2-й на 3-й и т.д. Когда открываю 1-й и обновляю, то ничего не происходит. Что я делаю не так?

Tamara, чтобы у Вас обновилось, следует открыть все 5 файлов

Я создаю лист для расчета цен. Лист должен иметь ссылку на несколько других книг, чтобы получить цены на различные компоненты. Это отлично работает на моем компьютере, но когда я перемещаю их на сервер или другой компьютер, он не может найти ссылки.

моя папка структурирована следующим образом:

prices.xlsx — основной лист, который ссылается на другие листы. На моем компьютере он строит ссылки с абсолютным путем, поэтому, когда я копирую файлы путь остается фиксированным к моей машине, вместо того, чтобы ссылаться на файлы на другом компьютере.

Читайте также:  Канал страйка на ютубе

есть ли способ сделать ссылки относительными, чтобы я мог поместить в основной лист что-то вроде =’Variable Components[ComponentsC.xlsx]Sheet1′!A1 . Я бы не хотел использовать VBA для этого, поскольку люди не склонны доверять макросам, а затем жаловаться, что функции не работают.

5 ответов

единственные решения, которые я видел для организации внешних файлов в подпапки, потребовали использования VBA для разрешения полного пути к внешнему файлу в формулах. Вот ссылка на сайт с несколькими примерами, которые использовали другие:

кроме того, если вы можете поместить все файлы в одну папку вместо разделения их на подпапки, то Excel разрешит внешние ссылки без необходимости использования VBA даже при перемещении файлов в сетевое расположение. Ваши формулы тогда становятся просто ='[ComponentsC.xlsx]Sheet1′!A1 без имен папок для перемещения.

у меня была аналогичная проблема, которую я решил, используя следующую последовательность:

использовать CELL("filename") функция для получения полного пути к текущему листу текущего файла.

использовать SEARCH() функция для поиска начала строки [FileName]SheetName текущего файла excel и листа.

использовать LEFT функция для извлечения полного пути к каталогу, содержащему текущий файл.

добавить имя каталога нашел в шаге #3 имя файла, имя листа, а ссылка на ячейку, которую вы хотите открыть.

легче и короче через косвенные: INDIRECT("’……..SupplySUSU.ods’#$Data.$A:$AC0")

однако косвенные() имеет недостатки производительности, если много ссылок в книге

Я скучаю по конструкции, как: [‘../Data.ods’]#Sheet1.A1 в LibreOffice. Намерение здесь: Если я создаю кучу мастер-книг и в зависимости от отчетов книги в ограниченном поддереве каталогов в исходной файловой системе, я могу zip весь поддерево каталога с полным пакетом книг и отправить его другому сотрудничающему лицу по электронной почте или около того. Он будет сохранен в некотором другом абсолютном pazth на целевой системе, но связь снова работает в новом абсолютном пути, потому что он был закодирован относительно корня поддерева.

использование функции =worksheetname() и =Indirect() и присвоение имен листам в Родительском файле Excel имени файла Excel с внешней ссылкой. Каждый файл excel с внешней ссылкой был в своих собственных папках с тем же именем. Эти подпапки были только для того, чтобы создать больше ясности.

Я сделал следующее: —

R2) родительская папка ———> "C:TEMPExcel"

R3) имя вложенной папки — — — > =worksheetname ()

R5) полный путь —————> ="’"&C2&C3 & " ["&C3&".xlsx]Sheet1′!$A$1"

Р7) двссыл——> =косвенные(С5,правда)

в главном файле, я сказал, 5 листов, помеченных как Ext-1, Ext-2, Ext-3, Ext-4, Ext-5. Копия вставила вышеуказанные формулы во все пять листов. Открыл все соответственно названные файлы Excel в фоновом режиме. Почему-то результатов не было автоматически вычисляя, следовательно, пришлось форсировать изменение, редактируя любую ячейку. Volla, значение в ячейке A1 каждого файла Excel с внешней ссылкой было в основном файле.

Предположим, вы связываете с общим диском, например, диск S? Если это так, другие люди, возможно, сопоставили диск по-другому. Вероятно, вам нужно использовать "официальное" имя диска //euhkj002/forecasts / bla bla. Вместо S / / в вашей ссылке

Ссылка на основную публикацию
Что такое windows 10 pro
Кто бы мог подумать, но до с даты релиза Windows 10 прошел почти год. Сотни миллионов пользователей уже обзавелись новой...
Что делать после заправки картриджа canon
Многие пользователи принтеров Canon после очередной дозаправки картриджей сталкиваются с различными неполадками в работе устройств, в том числе и с...
Что делать после скачивания драйвера для принтера
Часто задаваемый вопрос пользователей принтеров – как установить драйвер на принтер или МФУ. Вставьте флешку с драйвером в компьютер, в...
Что такое winmail dat
Получив очередное письмо по почте, пользователь может столкнуться с ситуацией, что часть информации в нем отсутствует и в списке файлов...
Adblock detector