Поиск ссылок (внешних ссылок) в книге
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Связывание с другими книгами — очень распространенная задача в Excel, но иногда вы можете найти книгу, которая ссылок не может найти, хотя Excel сообщает о том, что она существует. Нет способа автоматического поиска всех внешних ссылок, используемых в книге, однако существует несколько способов, используемых вручную. Вам нужно искать формулы, определенные имена, объекты (например, текстовые поля или фигуры), названия диаграмм и ряды данных диаграммы.
Имя любой Excel книги, с помощью ссылки на которую вы ссылались, будет связана с расширением XL* (например, .xls, .xlsx, XLSM), поэтому рекомендуемый способ — найти все ссылки на частичное расширение XL. Если вы ссылались на другой источник, необходимо определить оптимальный поисковый запрос.
Поиск ссылок, используемых в формулах
-
Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.
-
Нажмите кнопку Параметры.
-
В поле Найти введите.xl.
-
В поле В пределах выберите книга.
-
В поле Искать в выберите формулы.
-
Нажмите кнопку Найти все.
-
В отображемом списке наймем в столбце Формула формул, содержащих XL. В этом случае Excel найдено несколько экземпляров функции бюджетного Master.xlsx.
-
Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.
Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
-
На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
-
Проверьте каждую запись в списке и проверьте, нет ли в столбце Ссылка внешних ссылок. Внешние ссылки содержат ссылку на другую книгу, например [Budget.xlsx].
Советы:
-
Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.
-
Если вы хотите удалить сразу несколько элементов, можно сгруппнуть несколько элементов, нажав клавишу SHIFT или CTRL и щелкнув левой кнопкой мыши.
-
-
Нажмите клавиши CTRL+G, нажмите клавиши CTRL+G, чтобы перейти в диалоговое окно Перейти, а затем выберите специальные > объекты > ОК. При этом будут выбраны все объекты на активном сайте.
-
Нажимая клавишу TAB, переходить между выбранными объектами, а затем искать в строка формул
ссылку на другую книгу, например [Budget.xlsx].
ссылку на другую книгу, например [Budget.xlsx].
-
Щелкните название диаграммы, которую вы хотите проверить.
-
В строка формул
наймем ссылку на другую книгу, например [Budget.xls].
-
Выберите диаграмму, которую нужно проверить.
-
На вкладке Макет в группе Текущий выделение щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных, которые нужно проверить.
-
На строка формул
, наймем ссылку на другую книгу, например [Budget.xls] в функции РЯД.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Исправление недействительных ссылок
Разрыв связи с внешним ресурсом в Excel
Управление обновлением внешних ссылок (связей)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Одна из величайших возможностей Microsoft Excel — возможность связываться с другими книгами. Поэтому, если придет время, когда вам нужно будет найти те ссылки на книги, которые вы включили, вам нужно будет знать, с чего начать.
Общий поиск ссылок на книги прост, если вы просматриваете только текст в ячейках. Но если у вас есть книги, связанные формулами, определенными именами, диаграммами или объектами, это не очевидный поиск. Вот как найти ссылки на другие книги в Microsoft Excel с помощью сочетания встроенных инструментов и ваших собственных глаз.
Поиск ссылок на книги в формулах
Помимо простой ссылки на перекрестную ссылку в тексте некоторых ячеек, формулы являются обычным местом для включения ссылок на книги. В конце концов, извлечение данных из другого листа, который рассчитывается с использованием текущего листа, — это мощный способ использовать Excel.
Начните с открытия функции поиска. Вы можете сделать это с помощью Ctrl + f или «Найти и выделить»> «Найти» на ленте на вкладке «Главная».
Когда откроется окно «Найти и заменить», вам нужно будет ввести только три части информации. Нажмите «Параметры» и введите следующее:
- Найдите что: введите «.xl»
- Внутри: выберите «Рабочая тетрадь»
- Загляните внутрь: выберите «Формулы»
Нажмите «Найти все», чтобы получить результаты.
Вы должны увидеть свои связанные книги в разделе «Книга». Вы можете щелкнуть заголовок этого столбца для сортировки в алфавитном порядке, если у вас есть ссылки на несколько книг.
Совет: Вы также можете использовать диалоговое окно «Найти», чтобы найти ссылки книги в значениях, примечаниях и комментариях. Просто выберите один из этих вариантов вместо формул в раскрывающемся списке «Искать».
Поиск ссылок на книгу в определенных именах
Еще одно распространенное место для внешних ссылок в Excel — это ячейки с определенными именами. Как вы знаете, присвоить ячейке или диапазону значимое имя, особенно если оно содержит ссылку на ссылку, удобно.
Хотя диалоговое окно поиска и выбора для поиска, как и для формул, в настоящее время недоступно, вы можете извлечь все определенные имена в своей книге. Затем просто поищите ссылки на книги.
Перейдите на вкладку «Формулы» и нажмите «Диспетчер имен».
Когда откроется окно диспетчера имен, вы можете найти книги в столбце «Ссылается на». Поскольку они имеют расширение XLS или XLSX, вы сможете легко их обнаружить. При необходимости вы также можете выбрать один, чтобы увидеть полное имя книги в поле «Ссылается на» в нижней части окна.
Поиск ссылок на книги в диаграммах
Если вы используете Microsoft Excel для размещения данных в удобной диаграмме и получаете больше данных из другой книги, эти ссылки довольно легко найти.
Выберите свою диаграмму и перейдите на вкладку «Формат», которая появится после того, как вы это сделаете. В крайнем левом углу ленты щелкните раскрывающийся список «Элементы диаграммы» в разделе «Текущий выбор».
Выберите серию данных из списка, в котором вы хотите найти ссылку на книгу.
Затем переместите взгляд на строку формул. Если у вас есть связанная книга, вы увидите ее здесь, обозначенную расширением Excel. И вы можете таким же образом проверить каждую серию данных в своей диаграмме.
Если вы считаете, что у вас есть книга, связанная в заголовке диаграммы, а не в серии данных, просто щелкните заголовок диаграммы. Затем взгляните на строку формул книги Microsoft Excel.
Найти ссылки книги в объектах
Точно так же, как вставку PDF-файла в лист Excel с помощью объекта, вы можете сделать то же самое для своих книг. К сожалению, когда дело доходит до поиска ссылок на другие книги, объекты являются самым утомительным элементом. Но с этим советом вы можете ускорить процесс.
Откройте диалоговое окно «Перейти к специальному». Вы можете сделать это с помощью Ctrl + g или «Найти и выделить»> «Перейти к специальному» на ленте на вкладке «Главная».
Выберите «Объекты» в поле и нажмите «ОК». Это выберет все объекты в вашей книге.
Для первого объекта найдите диаграммы в строке формул (как показано выше). Затем нажмите клавишу Tab, чтобы перейти к следующему объекту и сделать то же самое.
Вы можете продолжать нажимать Tab и смотреть на строку формул для каждого объекта в вашей книге. Когда вы снова приземляетесь на первый объект, который вы просмотрели, вы прошли их все.
Запомните эти полезные советы, когда в следующий раз вам понадобится найти ссылку на книгу в Microsoft Excel.
Коля nilem написал мне классный макрос котроый рвет все связи и при этом не трогает формулы листа..давно хотел прорекламировать -очень полезная штука может адаптируете себе для поиска таких ячеек.
Option Explicit
Option Compare Text
‘—————————————————————————————
‘ Module : Module1
‘ Author : Николай (nilem)
‘ Date : 05.09.2011
‘ Purpose : Mikki
‘—————————————————————————————
Sub CopyWithoutRef()
‘Dim tm!: tm = Timer
Dim r As Range, s As String, nm As Name
With Application
.ScreenUpdating = 0: .Calculation = xlCalculationManual
End With
ActiveSheet.Copy after:=ActiveSheet
With ActiveSheet
For Each r In .UsedRange.SpecialCells(xlCellTypeFormulas).Cells
s = r.Formula
If InStr(1, s, “!”, vbTextCompare) Then
r.Value = r.Value
Else
For Each nm In ThisWorkbook.Names
If InStr(1, s, nm.Name, vbTextCompare) Then r.Value = r.Value: Exit For
Next nm
End If
Next r
End With
With Application
.ScreenUpdating = 1: .Calculation = xlCalculationAutomatic
End With
‘MsgBox Timer – tm
End Sub
‘Private Sub rtyrty()
‘Dim msg As String, nm As Name
”For Each nm In ActiveSheet.Names
‘For Each nm In ThisWorkbook.Names
‘ If nm.Visible = False Then
‘ msg = msg & “имя: ” & nm.Name & vbCrLf
‘ End If
‘Next nm
‘MsgBox msg, 64
‘End Sub
‘—————————————————————————————
‘ Module : Module1
‘ Author : Николай (nilem)
‘ Date : 05.09.2011
‘ Purpose : Mikki
‘—————————————————————————————
Sub ertert()
Dim x, i&, ch As ChartObject, nm$
Application.ScreenUpdating = False
With Sheets(“Перевод”)
x = .Range(“B3:C” & .Cells(Rows.Count, 2).End(xlUp).Row).Value
End With: nm = ActiveSheet.Name
ActiveSheet.Copy after:=ActiveSheet
On Error Resume Next
With ActiveSheet
With .UsedRange.SpecialCells(2) ‘ константы
For i = 1 To UBound(x)
.Replace x(i, 1), x(i, 2)
Next i
End With
With .UsedRange.SpecialCells(-4123) ‘ формулы
For i = 1 To UBound(x)
.Replace x(i, 1) & ” “, x(i, 2) & ” “, xlPart
Next i
End With
For Each ch In .ChartObjects ‘ диаграммы
If ch.Chart.HasTitle = True Then
With ch.Chart.ChartTitle
For i = 1 To UBound(x)
If .Text = x(i, 1) Then .Text = x(i, 2): Exit For
Next i
End With
End If
Next ch
.Name = nm & “P” ‘P англ.
End With
Application.ScreenUpdating = True
End Sub
Содержание
- Найти внешние ссылки
- Поиск внешних ссылок с помощью функции поиска и замены
- Найдите внешние ссылки с помощью ссылок редактирования
Как найти ячейки, связанные с внешними источниками в Excel
В этой статье вы узнаете, как найти внешние ссылки в Excel.
Найти внешние ссылки
В Excel можно использовать данные из другой книги (файла). В этом случае Excel создает внешнюю ссылку на эту книгу. Рассмотрим набор данных на рисунке ниже, который содержит внешние ссылки на другую книгу.
Как вы можете видеть выше, значение в B2 связано с рабочим листом с именем Внешний файл .xlsx (Лист1, ячейка B2). Ячейки B5, B7 и B8 также содержат похожие ссылки. Теперь посмотрите на этот файл и значение в ячейке B2.
Выше видно, что значение ячейки B2 в файле Внешний файл .xlsx 55, и это значение связано с исходным файлом. Когда вы связываете ячейку с другой книгой, значения обновляются в обеих книгах при каждом изменении связанной ячейки. Таким образом, вы можете столкнуться с проблемой, если файл, на который вы ссылаетесь, будет удален.
При открытии исходный файл предупреждает вас о том, что некоторые ячейки в этой книге связаны с несуществующим файлом. Чтобы решить эту проблему, вы можете найти все ячейки с внешними ссылками и заменить их каким-либо другим значением (или просто удалить их).
Однако часто бывают случаи, когда у вас много связанных ячеек, и на просмотр всего файла, чтобы выбрать их одну за другой, потребуется много времени. Вы можете найти все внешние ссылки в файле сразу, используя Найти и заменить или Редактировать ссылки Особенности.
Поиск внешних ссылок с помощью функции поиска и замены
1. В Лента, перейти к Главная> Найти и выбрать> Заменить.
2. Во всплывающем окне (1) введите «* .xl *» для Найти то, что, (2) нажмите Найти все, и (3) нажмите CTRL + A на клавиатуре, чтобы выделить все найденные ячейки.
Связанные файлы должны быть в формате Excel (.xlsx, .xlsm, .xls), поэтому вы хотите найти ячейки, содержащие «.xl» в формуле (ссылка). Звездочки (*) перед и после «.xl» обозначают любой символ, поэтому поиск найдет любое из расширений файлов Excel.
3. В результате выбираются все ячейки, содержащие внешнюю ссылку (B2, B5, B7 и B8). Чтобы заменить их определенным значением, введите это значение в поле Заменить коробка и удар Заменить все. Если оставить поле пустым, удаляется содержимое всех связанных ячеек. Например, если ввести 55 в поле Заменить на, получится Ценности столбец на картинке ниже. В любом случае замененные ячейки больше не связаны с другой книгой.
Найдите внешние ссылки с помощью ссылок редактирования
Другой вариант – использовать функцию редактирования ссылок в Excel.
1. В Лента, перейти к Данные> Изменить ссылки.
2. В окне «Редактировать ссылки» вы можете увидеть все книги, связанные с текущим файлом. Чтобы удалить ссылку, вы можете выбрать внешний файл и нажать Разорвать ссылку. В результате все ссылки на этот файл удаляются, а ранее связанные ячейки будут содержать значения, которые они имели на момент разрыва.
Вы поможете развитию сайта, поделившись страницей с друзьями
Устаревшие или неработающие внешние ссылки могут вызвать проблемы с электронными таблицами Excel. Из этого руководства вы узнаете, как найти и удалить внешние ссылки в Excel.
Microsoft Excel — мощное приложение для работы с электронными таблицами с множеством полезных функций. Одной из таких полезных функций является возможность ссылаться на данные, находящиеся за пределами самой электронной таблицы. Excel позволяет добавлять ссылки на данные в другие электронные таблицы и использовать эти данные, как если бы они были частью вашей текущей электронной таблицы.
Однако иногда эти внешние ссылки могут вызывать проблемы. Они могут быть устаревшими, сломанными или иметь другие проблемы. Вы также можете не захотеть включать внешние ссылки, если вы отправляете файл кому-то еще, у кого нет доступа к файлам, на которые они ссылаются.
Избавиться от внешних ссылок из ваших электронных таблиц Excel обычно довольно просто, но вы можете обнаружить, что есть некоторые упрямые ссылки, от которых избавиться труднее. Если вы хотите узнать, как найти и удалить внешние ссылки в Excel, выполните следующие действия.
В Excel есть функция, которая помогает вам находить внешние ссылки и дает вам возможность разорвать эти ссылки, если они вам больше не нужны. Хотя это очень полезно, он не всегда находит все ссылки в вашем документе, особенно если ссылки находятся где-то еще, кроме ячейки.
Тем не менее, это определенно хорошее место для начала, если вы хотите найти и удалить внешние ссылки Excel.
Чтобы разорвать внешние ссылки в Excel:
- Откройте книгу Excel, содержащую ваши внешние ссылки.
- Щелкните вкладку Данные.
- В разделе «Запросы и подключения» на ленте нажмите «Изменить ссылки». Если эта кнопка неактивна, это означает, что Excel не может найти внешние ссылки.
- Вы увидите список всех документов, на которые ссылаются извне.
- Нажмите на ссылку, которую вы хотите сломать. Чтобы выбрать несколько ссылок, удерживайте нажатой клавишу Ctrl при выборе каждой ссылки или нажмите Ctrl+A, чтобы выбрать их все.
- Щелкните Разорвать ссылку. Это преобразует содержимое любых ячеек, которые ссылаются на внешние файлы, в их текущие значения.
- Если вы уверены, что хотите удалить ссылки, нажмите «Разорвать ссылки» для подтверждения.
- Выбранные вами ссылки теперь должны быть неработающими. Если вы решили разбить их все, кнопка «Редактировать ссылки» теперь должна быть неактивна, указывая на то, что Excel больше не может найти внешние ссылки в вашем документе.
Как найти и удалить внешние ссылки в объектах
К сожалению, описанный выше метод может не удалить все внешние ссылки в вашей электронной таблице. В других местах еще могут скрываться. Если это так, вы можете найти и удалить их вручную.
Одно место, которое вы можете найти, — это объекты Excel, такие как фигуры или текстовые поля.
Чтобы найти и удалить внешние ссылки в объектах Excel:
- На вкладке «Главная» щелкните значок «Найти и выбрать».
- Выберите «Перейти к специальному».
- В нижней части окна щелкните Объекты.
- Нажмите OK, и все объекты в вашей электронной таблице будут выбраны. Чтобы перемещаться по объектам, нажимайте Tab.
- Для каждого объекта найдите в строке формул любые ссылки на внешние рабочие листы. Если вы их найдете, вы можете удалить эти внешние ссылки прямо в строке формул.
- Наведите указатель мыши на объект, чтобы увидеть, связан ли он с каким-либо конкретным файлом.
- Если объект ссылается на файл, щелкните его правой кнопкой мыши и выберите «Удалить ссылку».
Как найти и удалить внешние ссылки в диаграммах
Внешние ссылки также можно найти в диаграммах Excel. Не существует простого способа поиска этих ссылок в диаграммах, поэтому вам придется работать с диаграммами вручную.
Чтобы найти и удалить ссылки в диаграммах Excel:
- Щелкайте по разным частям диаграммы. В ряду данных диаграммы или даже в заголовке диаграммы могут быть внешние ссылки.
- Когда вы нажимаете на каждую часть диаграммы, ищите внешние ссылки в строке формул. Если вы найдете какие-либо, вы можете удалить или заменить их.
- Если ваша диаграмма включает несколько рядов данных, вам нужно щелкнуть каждый из них по очереди, чтобы проверить его.
Как найти и удалить внешние ссылки в сводных таблицах
Сводные таблицы — еще одно место, где могут скрываться внешние ссылки. Еще раз вам нужно будет изучить каждую сводную таблицу в вашей электронной таблице; нет простого способа найти их все сразу.
Чтобы найти внешние ссылки в сводных таблицах Excel:
- Щелкните где-нибудь в сводной таблице.
- Перейдите на вкладку Анализ сводной таблицы.
- Выберите «Изменить источник данных».
- Проверьте поле «Таблица/диапазон» на наличие внешних ссылок, которые при необходимости можно удалить.
Как найти и удалить внешние ссылки в именованных диапазонах
Умный способ быстро обратиться к нескольким ячейкам в Excel — создать именованные диапазоны. На эти именованные диапазоны можно ссылаться в формулах или даже просто для быстрого перехода к нужным ячейкам. Иногда эти именованные диапазоны могут относиться к ячейкам за пределами вашей текущей электронной таблицы. Другими словами, они содержат внешние ссылки.
Чтобы найти внешние ссылки в именованных диапазонах в Excel:
- Откройте вкладку Формулы.
- В разделе «Определенные имена» нажмите «Диспетчер имен».
- Посмотрите в столбце Refers To любые внешние ссылки.
- Если вы хотите удалить именованный диапазон, содержащий внешние ссылки, щелкните Удалить.
Как найти и удалить внешние ссылки в ячейках
Вы также можете искать внешние ссылки в ячейках вручную, используя инструмент Excel «Найти и заменить». Это стоит попробовать, если вы перепробовали все вышеперечисленное, но в вашей электронной таблице все еще скрываются упрямые внешние ссылки.
Чтобы найти внешние ссылки в ячейках Excel:
- На вкладке «Главная» нажмите «Найти и выделить» > «Найти» или нажмите Ctrl+F.
- У вас есть несколько вариантов того, что ввести в Find What. Вы можете ввести квадратную скобку ([)таккаквнешниессылкивсегдадаютсявквадратныхскобкахОднакоэтонайдетлюбыедругиеквадратныескобкиввашейэлектроннойтаблице[)sinceexternallinksarealwaysgiveninsquarebracketsThiswillfindanyothersquarebracketsinyourspreadsheethowever
- Кроме того, вы можете искать .XL, так как любая связанная электронная таблица будет включать один из форматов файлов .XLS, .XLSX или .XLSM.
- Выберите поиск во всей книге или на текущем листе.
- Установите для параметра «Искать в» значение «Формулы».
- Нажмите «Найти все».
- Если возвращаются какие-либо результаты, вы можете выбрать один из них для переноса в эту ячейку.
- Чтобы выбрать несколько ячеек, удерживайте клавишу Ctrl при выборе каждой ссылки.
- Чтобы выделить все ячейки в результатах, нажмите Ctrl+A.
- Теперь вы можете удалить ячейки, содержащие внешние ссылки, если хотите.
Эксель в Экселе
Изучение того, как находить и удалять внешние ссылки в Excel, гарантирует, что вы сможете удалить все ненужные или мертвые ссылки из своей электронной таблицы. Это также означает, что если вы отправите электронную таблицу кому-то другому, у вас не возникнет проблем с отсутствующими ссылками.
Существует множество других навыков работы с Excel, которым вы можете научиться, чтобы вывести свои электронные таблицы на новый уровень. Например, вы можете узнать, как считать ячейки, содержащие текст, в Excel. Вы можете узнать, как включить или отключить макросы в Excel. Вы даже можете научиться создавать генератор случайных чисел.
