как узнать какие формулы ссылаются на ячейку excel

Отображение связей между формулами и ячейками

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

Ячейки- ячейки, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5,ячейка B5 является влияемой на ячейку D10.

Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на другие ячейки. Например, если ячейка D10 содержит формулу =B5, ячейка D10 является зависимой от ячейки B5.

Для проверки формул можно использовать команды «Ячейки трассировки» и «Зависимые ячейки» для графического отображения и трассировки связей между этими ячейками и формулами с помощью стрелки трассировки, как показано на рисунке.

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

Щелкните Файл > параметры > Дополнительные параметры.

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

Чтобы указать ссылки на ячейки в другой книге, эта книга должна быть открыта. Microsoft Office Excel не может перейти к ячейке книги, если она не открыта.

Выполните одно из указанных ниже действий.

Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.

Чтобы отобразить стрелку трассировки для каждой ячейки, которая непосредственно предоставляет данные активной ячейке, на вкладке Формулы в группе Зависимости формул нажмите кнопку Ячейки трассировки .

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

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

Чтобы удалить стрелки трассировки по одному уровню, начните с влияемой ячейки, которая дальше от активной ячейки. Затем на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.

Укажите ячейку, для которой следует найти зависимые ячейки.

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

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

Чтобы удалить стрелки зависимости по одному уровню, начиная с зависимой ячейки, наиболее далекой от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить зависимые стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.

В пустой ячейке введите = (знак равно).

Нажмите кнопку Выделить все.

Вы выберите ячейку, а затем на вкладке Формулы в группе Зависимости формул дважды нажмите кнопку трассировки.

Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.

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

Ссылки на текстовые поля, внедренные диаграммы или рисунки на таблицах.

Отчеты для отчетов в отчетах.

Ссылки на именуемые константы.

Формулы, расположенные в другой книге, которые ссылаются на активную ячейку, если другая книга закрыта.

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

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

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

Источник

Поиск ссылок (внешних ссылок) в книге

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

Поиск ссылок, используемых в формулах

Нажмите CTRL+F, чтобы запустить диалоговое окно Найти и заменить.

Нажмите кнопку Параметры.

В поле В пределах выберите книга.

В поле Искать в выберите формулы.

Нажмите кнопку Найти все.

В отображемом списке наймем в столбце Формула формул, содержащих XL. В этом случае Excel найдено несколько экземпляров функции бюджетного Master.xlsx.

Чтобы выбрать ячейку с внешней ссылкой, щелкните ссылку на эту строку в списке.

Совет: Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.

На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.

Проверьте каждую запись в списке и проверьте, нет ли в столбце Ссылка внешних ссылок. Внешние ссылки содержат ссылку на другую книгу, например [Budget.xlsx].

Щелкните любой за колонок, чтобы отсортировать столбец и сгруппировать все внешние ссылки.

Если вы хотите удалить сразу несколько элементов, можно сгруппнуть несколько элементов, нажав клавишу SHIFT или CTRL и щелкнув левой кнопкой мыши.

Нажмите клавиши CTRL+G, нажмите клавиши CTRL+G, чтобы перейти в диалоговое окно Перейти, а затем выберите специальные > объекты > ОК. При этом будут выбраны все объекты на активном сайте.

«Специальная»» loading=»lazy»>

Нажимая клавишу TAB, переходить между выбранными объектами, а затем искать в строка формул ссылку на другую книгу, например [Budget.xlsx].

Щелкните название диаграммы, которую вы хотите проверить.

В строка формул наймем ссылку на другую книгу, например [Budget.xls].

Выберите диаграмму, которую нужно проверить.

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

формат > текущий выделение» loading=»lazy»>

На строка формул , наймем ссылку на другую книгу, например [Budget.xls] в функции РЯД.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Как узнать какие формулы ссылаются на ячейку excel

Как узнать, есть ли ссылка на ячейку в другой ячейке Excel?

Как мы можем узнать, есть ли ссылка на ячейку в каких-либо формулах или нет? Фактически, в Excel есть встроенная функция, называемая «Отслеживание зависимостей», которая может помочь вам легко узнать, есть ли ссылка на ячейку в каких-либо формулах.

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

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

2. Затем нажмите Формулы > Следить за зависимыми. Смотрите скриншот:

3. После активации Следить за зависимыми функции, вы можете видеть, что на листе отображаются стрелки.

Как показано на снимке экрана ниже, эти две стрелки указывают на то, что на активную ячейку A2 есть ссылки в двух формулах, которые находятся в ячейке B2 и ячейке B5.

Заметки:

1. Если формулы, относящиеся к активной ячейке, отсутствуют, появится диалоговое окно с запросом, как показано ниже.

2. Вы можете удалить стрелки, нажав Формулы > Удалить стрелки > Удалить зависимые стрелки.

Источник

Поиск циклической ссылки в Excel

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

Выявление циклических связей

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

Способ 1: кнопка на ленте

Способ 2: стрелка трассировки

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

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

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

Помимо этой статьи, на сайте еще 12226 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Источник

Работа в Excel с формулами и таблицами для чайников

Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.

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

При изменении значений в ячейках формула автоматически пересчитывает результат.

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

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

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

Как в формуле Excel обозначить постоянную ячейку

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

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

Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

При создании формул используются следующие форматы абсолютных ссылок:

Как составить таблицу в Excel с формулами

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

Простейшие формулы заполнения таблиц в Excel:

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

Источник