Поиск ссылок (внешних ссылок) в книге
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
Управление обновлением внешних ссылок (связей)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
При проверке расчетов и корректировке формул зачастую необходимо работать с ссылками в выражениях на другие листы.
Если мы хотим проверить значение в ячейке на другом листе, на которую ссылается наша формула, то приходится тратить много времени на поиск ячейки по ее адресу вручную.
Для того, чтобы автоматически перейти на требуемую ячейку, необходимо:
- Выбрать ячейку для проверки и перейти в меню ФОРМУЛЫ.
- Нажать на кнопку «Влияющие ячейки»
- При наличии ссылок на другие листы, мы увидим стрелку с указанием на другой лист.
- Необходимо кликнуть два раза по стрелке и вызвать окно перехода.
- В открывшемся окне, выбираем ссылку на необходимый лист и нажимаем ОК.
- Программа выделит целевую ячейку, влияющую на наши расчеты.
Данный способ также позволяет увидеть все внешние ссылки и проверить все исходные данные по очереди.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Всем привет.
При обработке некоторой таблички в MS Excel возникла простенькая задача — надо было найти все формулы в которых участовала определенная ячейка. Ммм, попробуйте далее не читать и решить ее самостоятельно.
Ну а кому не втерпеж, слушаем далее. Итак для примера мне надо найти кто использует ячейку B4.
Первое решение — используем поиск. Указываем ячейку в поиске и нажимаем «Найти все». В результате имеем список всех ячеек где наша B4 участвует в расчетах. Для чего не подходит такой способ? Это не подходит в случае, если наша ячейка участвует в диапазоне типа «=СУММ(B3:B7)» или ссылка абсолютная «=B$4».
Решение второе — используем «Зависимости формул». Ух ты, какая фишка есть в MS Excel. Закладка «Формулы -> Зависимость формул -> Зависимые ячейки» и MS Excel сам показывает где участвует наша ячейка. Красиво правда? Согласен.
Однако есть небольшое ограничение. Если ваша ячейка на другом листе, то MS Excel покажет только наличие зависимости, но не скажет на каком именно листе книги находится искомая формула. Пишите макрос.
Такие дела. Успехов.
Как мы можем узнать, есть ли ссылка на ячейку в каких-либо формулах или нет? Фактически, в Excel есть встроенная функция, называемая «Отслеживание зависимостей», которая может помочь вам легко узнать, есть ли ссылка на ячейку в каких-либо формулах.
Узнайте, есть ли ссылка на ячейку в Excel
Узнайте, есть ли ссылка на ячейку в Excel
Включить функцию отслеживания зависимостей в Excel довольно просто, сделайте следующее.
1. Выделите ячейку, в которой вы хотите узнать, ссылаются ли на нее какие-либо формулы.
2. Затем нажмите Формулы > Следить за зависимыми. Смотрите скриншот:
3. После активации Следить за зависимыми функции, вы можете видеть, что на листе отображаются стрелки.
Как показано на снимке экрана ниже, эти две стрелки указывают на то, что на активную ячейку A2 есть ссылки в двух формулах, которые находятся в ячейке B2 и ячейке B5.
Заметки:
1. Если формулы, относящиеся к активной ячейке, отсутствуют, появится диалоговое окно с запросом, как показано ниже.
2. Вы можете удалить стрелки, нажав Формулы > Удалить стрелки > Удалить зависимые стрелки.
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (3)
Оценок пока нет. Оцените первым!
Прежде, чем мы приступим к поиску циклических ссылок в таблицах Эксель, давайте разберемся, что это вообще такое.
Циклические ссылки встречаются в формулах, в которых ячейка ссылается на другие ячейки, которые в свою очередь могут ссылаются на другие и т.д., и в итоге, получается так, что начальная ячейка (в которой формула) через цепочку ссылок на другие ячейки, ссылается на саму себя.
Иногда пользователи целенаправленно используют подобную схему для выполнения определенных вычислений. Однако, чаще всего, подобные ситуации – результат ошибки в формуле, вероятнее всего, сделанной пользователей случайно или по незнанию специфики работы в программе.
Потому очень важно уметь находить и устранять циклические ссылки, особенно, в документе, полученном от другого пользователя, который могу допустить такие ошибки.
Содержание
- Нахождение циклических ссылок
- Метод 1. Визуальный поиск циклической ссылки
- Метод 2. Использование инструментов на Ленте
- Заключение
Нахождение циклических ссылок
Когда в документе есть циклическая ссылка, при его открытии Excel проинформирует нас об этом в соответствующем окошке.
Следовательно, ломать голову над тем, если ли в книге циклическая ссылка (ссылки) или нет, не нужно, так как это понятно в момент его открытия. Остается только определить, где именно она находится.
Метод 1. Визуальный поиск циклической ссылки
Данный способ самый простой, однако, удобен лишь при работе с небольшими таблицами.
- Для начала нам нужно закрыть всплывшее диалоговое окно, предупреждающее нас о том, что в книге присутствует циклическая ссылка.
- После этого в таблице отобразится так называемая стрелка трассировки, наглядно демонстрирующая связь между ячейками, которая вызывает ошибку.
- Переходим в ту ячейку, куда указывает стрелка и исправляем формулу, чтобы избавиться от цикличности. В нашем случае лишним элементом является ячейка F13, поэтому убираем ее и формулы.
- Все готово. Теперь в нашей таблице нет циклических ссылок.
Метод 2. Использование инструментов на Ленте
Иногда бывает так, что стрелки, помогающие определить циклическую связь между ячейкам не отображаются в книге. В этом случае поступаем следующим образом:
- Начнем с того, что закроем информационное окно о наличии циклической ссылки.
- Теперь переключаемся во вкладку “Формулы”. Обращаем внимание на раздел “Зависимости формул”. Здесь нас интересует кнопка “Проверка ошибок” (в некоторых случаях, когда размеры окна сжаты по горизонтали, отображается только значок кнопки в виде восклицательного знака). Щелкаем по небольшому треугольнику, направленному вниз, справа от кнопки. Откроется перечень команд, среди которых выбираем пункт “Циклические ссылки”, после чего откроется список всех ячеек, содержащих эти самые ссылки.
- Если мы щелкнем на адрес ячейки, программа сразу же выделит ее, независимо от того, в какой ячейке мы находились до того, как решили воспользоваться данной функцией.
- Нам остается только разобраться с формулой и исправить допущенные в ней ошибки. В нашем случае в диапазон суммируемых ячеек была включена и ячейка, куда записана сама формула, что конечно же, неверно.
- Корректируем координаты диапазона в формуле, чтобы избавиться от цикличности.
- Чтобы удостовериться в том, что теперь все в порядке, снова раскрываем перечень команд рядом с кнопкой “Проверка ошибок”. На этот раз пункт “Циклические ссылки” неактивен, что свидетельствует о том, что ошибки устранены.
Заключение
Итак, как вы могли убедиться, в нахождении и удалении циклических ссылок в Excel нет никаких сложностей. При работе с небольшими таблицами достаточно визуального осмотра на наличие стрелок трассировки, указывающих на ссылки, которые мы ищем. Если документ достаточно объемный, либо не хочется терять время на поиск “проблемных” мест таблицы вручную, нас выручит инструмент под названием “Поиск ошибок”.