Изменить ссылки в Excel

Обновлено: 06.07.2024

Возможность связывать данные из одной книги Excel с другой — одна из лучших функций приложения для работы с электронными таблицами. Однако управление ссылками, в том числе редактирование и удаление ссылок, разочаровывает многих пользователей. В этой статье вы узнаете, как лучше всего управлять ссылками в книгах Excel.

Выявление внешних ссылок и управление ими

Самая распространенная потребность – определить и управлять входящими ссылками из одной или нескольких "исходных" книг в "целевую" книгу. Необходимость в этом типе управления возникает в нескольких ситуациях, включая устранение неполадок с вычислениями, а также аудит и проверку формул в рабочей книге назначения. К счастью, есть как минимум три простых способа определить входящие ссылки, и два из них облегчают редактирование связанных данных и управление ими.

Используйте функцию поиска и замены Excel для определения входящих ссылок

Возможно, самый прямой метод определения входящих ссылок – использовать функцию Excel "Найти и заменить" для поиска всех формул, содержащих "xlsx". Этот метод работает, потому что при связывании данных из другой книги текст ссылки включает «xlsx».

Чтобы использовать этот метод, сначала откройте диалоговое окно Excel «Найти и заменить», используя комбинацию клавиш CTRL + F. Затем введите «*.xlsx» в поле «Найти» и убедитесь, что вы включили Формулы в поле «Искать», как показано на рис. 1. Обратите внимание, что вы можете искать входящие ссылки только на текущем рабочем листе или всю рабочую тетрадь. Наконец, нажмите «Найти все», чтобы найти все ссылки на листе или в книге.

Рис. 1. Поиск ссылок с помощью функции поиска и замены Excel

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

Рисунок 2. Идентификация входящих ссылок с помощью функции поиска и замены

Управление входящими ссылками с помощью функции редактирования ссылок Excel

Еще один способ управления входящими ссылками в книге — использование функции редактирования ссылок Excel. Чтобы использовать эту функцию, откройте ее, нажав «Изменить ссылки» в группе «Запросы и подключения» на вкладке «Данные» на ленте. Кроме того, вы можете получить доступ к «Редактировать ссылки», щелкнув «Файл» , «Информация» и выбрав «Редактировать ссылки на файлы» в правом нижнем углу окна. Независимо от того, как вы решите получить к нему доступ, появится окно Edit Links, как показано на рисунке REF _Ref81232414 \h \* MERGEFORMAT.

Рисунок 3. Инструмент редактирования ссылок Excel

В окне «Редактировать ссылки» можно выбрать обновление значений, связанных с текущей книгой, изменить ссылки, изменив их исходную книгу или разорвав их, открыть исходную книгу и проверить состояние ссылки. Обратите внимание, что Excel не делает эти инструменты доступными, если вы выбираете метод «Найти и заменить» для определения ссылок, который обсуждался ранее.

Идентификация ссылок с надстройкой Excel Inquire

При активированной надстройке «Запрос» нажмите «Запрос», а затем «Связь рабочей книги», чтобы создать отчет «Связь рабочей книги». В этом отчете, образец которого показан на рис. 4, графически показаны ссылки из одной книги в другую. Точно так же вы можете создать отчет о взаимосвязях рабочих листов, чтобы увидеть, как данные передаются с одного рабочего листа на другой.

Рисунок 4. Отчет о взаимосвязях рабочей книги Inquire

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

Рисунок 5. Отчет о взаимосвязи ячеек, созданный с помощью надстройки Excel Inquire

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

Обзор

Связывание данных из одной книги с другой — обычная практика Excel. Однако иногда управление ссылками может стать громоздким. В некоторых случаях пользователи заканчивают поиск связанных данных, просматривая каждую ячейку одну за другой. Конечно, есть лучшие способы управления ссылками в книгах Excel.Например, как указано выше, вы можете использовать функцию Excel «Найти и заменить», инструмент Excel «Редактировать ссылки» или надстройку «Запрос», чтобы помочь вам идентифицировать связанные данные и управлять ими. Итак, если вам нужно управлять ссылками в книгах Excel, изучите обсуждаемые здесь методы, чтобы получить лучшие результаты за меньшее время.

Вы уже знаете, что Excel позволяет устанавливать связи между книгой и другими объектами, такими как графика, документы Word и другие элементы. (Здесь мы говорим об обычных ссылках, а не о гиперссылках.) Может наступить время, когда вы захотите изменить ссылки. Например, у вас может быть ссылка на изображение в вашей книге, и вы хотите изменить местоположение изображения (в другой каталог или на другой диск). Excel позволяет изменить ссылку, чтобы она указывала на нужный файл в качестве источника объекта в рабочей книге.

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

1. Отобразите вкладку "Данные" на ленте.
2. В группе «Подключения» (Excel 2007, Excel 2010 и Excel 2013) или в группе «Запросы и подключения» (Excel 2016) щелкните инструмент «Редактировать ссылки». Excel отображает диалоговое окно «Редактировать ссылки». (См. рис. 1.)

Рисунок 1. Диалоговое окно "Редактировать ссылки".

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

БОЛЬШЕ ОТ АЛЛЕНА

Восстановление пакета анализа

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

Последний рабочий день

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

Использование пользовательского формата даты в верхнем или нижнем колонтитуле

Нужно отображать пользовательскую дату в верхнем или нижнем колонтитуле распечатки? Вам нужно будет прибегнуть к использованию макроса, как описано в .

Решение реальных бизнес-проблем Освойте методы бизнес-моделирования и анализа с помощью Excel и преобразуйте данные в итоговые результаты. В этом практическом руководстве, ориентированном на сценарии, показано, как использовать новейшие инструменты Excel для интеграции данных из нескольких таблиц. Оцените Анализ данных и бизнес-моделирование Microsoft Excel 2013 сегодня!

Дополнительные советы по Excel (лента)

Создание отдельных книг

Рабочие книги могут содержать множество рабочих листов. Если вы хотите разделить книгу на части и создать целую серию книг на основе .

Открытие книги на определенном листе

При открытии книги Excel отображает лист, который был виден при последнем сохранении книги. Вы можете захотеть, .

Отображение миниатюры рабочего листа в Windows

При сохранении книги у вас есть возможность сохранить уменьшенное изображение, которое можно отобразить в Windows. Вот.

Подписаться

БЕСПЛАТНАЯ СЛУЖБА. Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по повышению эффективности. Введите свой адрес и нажмите "Подписаться".

Комментарии

Инструмент редактирования ссылок не отображается. Я использую версию 2016 года, как обычно, эти блоги — пустая трата времени!

Уолтер Ли Брамфилд

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

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

В (запоздалой) реакции на вопрос Ади обратите внимание, что это может произойти по самым разным причинам, и они могут быть довольно тонкими, запутанными и расстраивающими. Проблема заключается в том, что книга может быть подключена к другой книге несколькими способами — эта книга будет отображаться в списке «Редактировать ссылки», пока существует какое-либо из этих подключений, но кнопка «Изменить источник» обновляет только ссылки, найденные в формулах ячеек. Итак.

Все формулы вида =[other-wookbook.xlsx]tab!A5 будут заменены на =[new-workbook]tab!A5

Но если на ячейки в «другой книге» есть ссылки при проверке данных или условном форматировании, кнопка «Изменить источник» не обновит эти ссылки. Вам придется искать их самостоятельно и исправлять вручную. Я узнал об этих двух случаях специально для книг, которые использую, и исправил их, создав раздел ссылок. Для проверки данных в ячейке в Workbook-A установлено значение Allow: List, Source: =[Workbook-B]tab!U5:U45. Что я сделал, так это поместил ссылки в ячейки [Workbook-A]other-tab!B105:B145, содержащие =[Workbook-B]tab!U5, =[Workbook-B]tab!U6, . =[Workbook-B]вкладка!U45, а затем измените источник проверки данных, чтобы он указывал на этот набор ячеек. Содержимое раздела ссылок корректно обновляется с помощью кнопки «Изменить источник».

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

Спасибо. Мне пришлось переместить несколько файлов по сети, чтобы коллега меня прикрыл, и я сломал все свои книги. Чтобы исправить их, требуется время, но теперь я знаю, как это сделать.

2 строки создаются, когда я пытаюсь изменить источник. Это происходит прекрасно для моего коллеги. в чем может быть причина?

Этот сайт

У вас есть версия Excel, использующая ленточный интерфейс (Excel 2007 или более поздней версии)? Этот сайт для Вас! Если вы используете более раннюю версию Excel, посетите наш сайт ExcelTips, посвященный интерфейсу меню.

При работе с большой базой данных иногда может потребоваться связать другие рабочие книги с базой данных для получения данных. Но во многих случаях ссылка может быть нарушена или расположение рабочей книги может быть изменено. Чтобы справиться с такими проблемами, мы можем редактировать ссылки, чтобы выполнить свою работу. Сегодня в этой статье мы обсудим некоторые способы редактирования ссылок в Excel

Скачать рабочую тетрадь

Загрузите этот практический лист, чтобы практиковаться во время чтения этой статьи.

Редактировать ссылки в Excel (3 метода)

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

1. Обновление значений ссылок в Excel

Шаг 1:

Рассмотрите ситуацию, когда у нас есть информация о продажах работника. Мы должны ввести эти данные в новую книгу. Для этого скопируйте данные воркера.

Теперь перейдите к новой книге и вместо обычной вставки выполните команду "Вставить ссылку", чтобы создать связь между этими двумя книгами.

Нажмите на значок, и ссылка будет создана.

Шаг 2:

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

Теперь мы должны убедиться, что основная рабочая книга также обновляется автоматически. Для этого перейдите в раздел «Данные», в группе «Соединение» нажмите «Изменить ссылки».

Шаг 3:

Появится новое окно с другими критериями редактирования. Здесь нажмите «Обновить значения», чтобы обновить основную книгу. Нажмите «Закрыть», чтобы продолжить.

Данные→ Редактировать ссылки→ Обновить значения

Шаг 4.

Теперь мы видим, что наши данные в основной книге автоматически обновляются.

Похожие чтения:

• Как удалить внешние ссылки в Excel
• Найти неработающие ссылки в Excel (4 быстрых метода)
• Поиск внешних ссылок в Excel (6 быстрых способов)

2. Изменение источника ссылок в Excel

Шаг 1:

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

Шаг 2:

Чтобы решить эту проблему, перейдите в раздел "Данные" и нажмите "Изменить ссылки". Когда появится новое окно, нажмите "Изменить источник".

Данные→Редактировать ссылки→Изменить источник

Шаг 3:

Теперь перейдите в новое место, где находится исходная книга.

Выберите книгу и нажмите "ОК", чтобы обновить источник.

Шаг 4.

Теперь мы видим, что наш набор данных обновляется в соответствии с исходной книгой.

3. Разрыв ссылок ссылок в Excel

Шаг 1:

В следующем примере у нас есть новая исходная книга, в которой приведены некоторые данные о продавце. Скопируйте эти данные в основную книгу, создав связь между двумя книгами.

Шаг 2:

Теперь в этой процедуре наша задача — разорвать связи между книгами. Для этого перейдите в раздел «Данные», нажмите «Редактировать ссылки», чтобы открыть окно «Редактировать ссылку».

Шаг 3:

Теперь выберите источник, к которому вы хотите применить разрыв связи. После выбора нажмите кнопку «Разорвать ссылку».

Появляется окно с предупреждением о том, что после разрыва ссылки это действие нельзя будет отменить. Нажмите «Разорвать ссылки», чтобы продолжить.

Теперь мы видим, что ссылка не работает, и нет информации о ссылке в окне редактирования ссылки.

Шаг 4.

Чтобы проверить, работает ли ссылка разрыва, измените какое-либо значение в исходной книге.

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

Что нужно помнить

➤ Ссылка на редактирование активируется только при создании ссылки между книгой и другой книгой.

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

Заключение

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

У меня есть несколько пользователей, которые обращаются к файлу Excel, содержащему около 300 ссылок на определенные PDF-файлы, соответствующие позициям. Все было хорошо, пока на днях. Внезапно путь ссылки изменился. Он перешел из "\\SERVER1\FOLDER1\EXAMPLE1.PDF" в "C:\Users\EXAMPLE USER1\AppData\Roaming\Microsoft\Excel\EXAMPLE1.pdf". После исследования выяснилось, что это могло быть вызвано тем, что файл был открыт, когда у нас был сбой питания или что-то в этом роде.Я ищу способ (желательно без использования стороннего программного обеспечения или сложного кода VB), чтобы изменить все 300 или около того путей ссылок обратно в расположение «\\SERVER1\FOLDER1\EXAMPLE.PDF» вместо локального пути, упомянутого ранее. Заранее миллион благодарностей.

Крис-8916

Еще один метод, если файл имеет формат xlsx:

Сделайте копию файла (на всякий случай никогда не работайте с оригиналом)

Переименовать в .zip

Откройте соответствующие XML-файлы

Найти и заменить

Переименуйте .zip обратно в .xlsx

7 ответов

Похоже на это:

Откройте целевую книгу.

На вкладке "Данные" в группе "Подключения" нажмите "Изменить ссылки".

В диалоговом окне "Редактировать ссылки" щелкните исходную книгу, которую вы хотите изменить.

Нажмите "Изменить источник".

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

Было бы намного проще ответить, если бы мы могли видеть лист. Для этого достаточно просто щелкнуть в любом месте листа, затем нажать Ctrl+A, чтобы выбрать все, а затем Ctrl+F, чтобы найти и заменить все.

Найти: "C:\Users\EXAMPLE USER1\AppData\Roaming\Microsoft\Excel\EXAMPLE1.pdf"

Заменить на: "\\SERVER1\FOLDER1\EXAMPLE.PDF"

Сначала сделайте копию листа.

Крис-8916

Еще один метод, если файл имеет формат xlsx:

Сделайте копию файла (на всякий случай никогда не работайте с оригиналом)

Переименовать в .zip

Откройте соответствующие XML-файлы

Найти и заменить

Переименуйте .zip обратно в .xlsx

К сожалению, Рич, кнопка редактирования ссылок неактивна, и изменить ее таким образом было невозможно. Благодарю за ваш ответ.

Michael939508 написал:

Было бы намного проще ответить, если бы мы могли видеть лист. Это исправление может быть таким же простым, как щелкнуть в любом месте листа, затем нажать Ctrl+A, чтобы выбрать все, а затем Ctrl+F, чтобы найти и заменить все.

Найти: "C:\Users\EXAMPLE USER1\ AppData\Roaming\Microsoft\Excel\EXAMPLE1.pdf"

Заменить на: "\\SERVER1\FOLDER1\EXAMPLE.PDF"

Создать сначала скопируйте лист.

Спасибо за ответ, Михаил. Я пробовал это уже до того, как опубликовал проблему здесь. Нет такой удачи. Еще раз спасибо.

Chris-8916 написал:

Еще один метод, если файл имеет формат xlsx;

Сделайте копию файла (на всякий случай никогда не работайте с оригиналом)

Переименовать в .zip

Откройте соответствующие XML-файлы

Найти и заменить

Переименуйте .zip обратно в .xlsx

Спасибо, Крис!! Это сэкономило мне столько времени. Я смог преобразовать его в заархивированную папку, найти соответствующий файл XML, выполнить «заменить все» на всех 4 листах, сохранить изменения, изменить его обратно на документ xlsx, а затем я успешно открыл и попробовал ссылки. очень хороший, короткий ответ, который творил чудеса. (Отказ от ответственности: мне пришлось сделать «извлечь все», прежде чем я открыл файлы в wordpad и сохранил там изменения. Затем мне пришлось скопировать их из извлеченной папки и вставить в еще заархивированную папку. Не жалуюсь, просто делаю примечание на случай, если кто-то прочитает эту ветку позже.)

Читайте также:

  
• Как написать формулу в word с дробью и степенью
  
• Сопоставьте вкладки Excel в соответствии с их назначением, соедините элементы парами
  
• Вы можете проверять почту на мобильном устройстве только через мобильный браузер
  
• Входной файл с ошибкой имеет текстовый формат, загрузите его с помощью psql
  
• Программа для телефонов samsung для работы с экраном при закрытом корпусе a80