Связать ячейки в excel стрелками
Если в формуле используются влиятельные или зависимые ячейки, может быть сложно проверить формулы на точность или найти источник ошибки.
Ячейки- ячейки, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5,ячейка B5 является влияемой на ячейку D10.
Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на другие ячейки. Например, если ячейка D10 содержит формулу =B5, ячейка D10 является зависимой от ячейки B5.
Для проверки формул можно использовать команды "Ячейки трассировки" и "Зависимые ячейки" для графического отображения и трассировки связей между этими ячейками и формулами с помощью стрелки трассировки, как показано на рисунке.
Чтобы отобразить связи между ячейками, выполните указанные здесь действия.
Щелкните Файл > параметры > Дополнительные параметры.
Примечание: Если вы используете Excel 2007; нажмите кнопку Microsoft Office , Excel параметры, а затем выберите категорию Дополнительные параметры.
В разделе Показать параметры для этой книги выберите книгу и убедитесь, что выбрано все в разделе Для объектов, показать.
Чтобы указать ссылки на ячейки в другой книге, эта книга должна быть открыта. Microsoft Office Excel не может перейти к ячейке книги, если она не открыта.
Выполните одно из указанных ниже действий.
Трассировка ячеек, обеспечивающих формулу данными (влияющих ячеек)Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.
Чтобы отобразить стрелку трассировки для каждой ячейки, которая непосредственно предоставляет данные активной ячейке, на вкладке Формулы в группе Зависимости формул нажмите кнопку Ячейки трассировки .
Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку имеется ссылка из другого рабочего листа или книги, путь от выбранной ячейки к значку рабочего листа будет обозначен черной стрелкой . Другую книгу необходимо открыть до того, как Excel начнет отслеживать эти зависимости.
Чтобы определить следующий уровень ячеек, которые предоставляют данные для активной ячейки, снова нажмите кнопку ячейки.
Чтобы удалить стрелки трассировки по одному уровню, начните с влияемой ячейки, которая дальше от активной ячейки. Затем на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.
Трассировка формул, ссылающихся на конкретную ячейку (зависимых ячеек)Укажите ячейку, для которой следует найти зависимые ячейки.
Чтобы отобразить стрелку трассировки для каждой ячейки, зависимой от активной ячейки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Зависимые ячейки .
Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку ссылается ячейка на другом месте или в другой книге, при наконечнике выбранной ячейки на значок . Другую книгу необходимо открыть до того, как Excel начнет отслеживать эти зависимости.
Чтобы определить следующий уровень ячеек, зависящих от активной ячейки, снова нажмите кнопку Зависимые ячейки .
Чтобы удалить стрелки зависимости по одному уровню, начиная с зависимой ячейки, наиболее далекой от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с кнопкой Удалить стрелки ивыберите удалить зависимые стрелки . Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.
В пустой ячейке введите = (знак равно).
Вы выберите ячейку, а затем на вкладке Формулы в группе Зависимости формул дважды нажмите кнопку трассировки.
Чтобы удалить все стрелки трассировки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Удалить стрелки .
Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.
Если Excel при нажатии кнопки Зависимые зависимые или Зависимые зависимые , Excel отследил все уровни формулы или пытается отследить элемент, который невозможно найти. Следующие элементы на этих таблицах, на которые могут ссылаться формулы, невозможно отследить с помощью средств аудита:
Ссылки на текстовые поля, внедренные диаграммы или рисунки на таблицах.
Отчеты для отчетов в отчетах.
Ссылки на именуемые константы.
Формулы, расположенные в другой книге, которые ссылаются на активную ячейку, если другая книга закрыта.
Чтобы увидеть выделение цветом влияющих ячеек для аргументов формулы, выделите ячейку и нажмите клавишу F2.
Для выбора ячейки на другом конце стрелки дважды щелкните эту стрелку. Если ячейка находится на другом листе или в другой книге, дважды щелкните черную стрелку для отображения диалогового окна Переход, а затем дважды щелкните требуемую ссылку в списке Переход.
Все стрелки трассировки исчезают, если изменить формулу, на которую указывают стрелки, вставить или удалить столбцы или строки либо удалить или переместить ячейки. Для восстановления стрелок зависимости после сделанных изменений, необходимо снова использовать на этом листе команды аудита. Для отслеживания первоначальных стрелок зависимости следует до внесения изменений распечатать лист с отображенными стрелками зависимости.
Вы можете создать несколько формул на листе, и иногда вам нужно найти связь между этими формулами и ячейками, на которые они ссылаются. Как быстро этого добиться? В этой статье вы получите два метода.
Показывать стрелки между ячейками с помощью функции трассировки прецедентов и трассировки зависимых
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Вы можете использовать Прецеденты трассировки и Следить за зависимыми функции, чтобы показать взаимосвязь между формулами и выделенными ячейками.
1. Чтобы определить, какие ячейки влияют на значение текущей выбранной ячейки, выберите ячейку с формулой и нажмите Формулы > Прецеденты трассировки. Смотрите скриншот:
2. Затем между ячейками, на которые указывает ссылка, и ячейками формулы отображается стрелка.
Заметки:
1. Если вы хотите узнать, на какие ячейки формулы влияет значение текущей выбранной ячейки, нажмите Формулы > Следить за зависимыми. Смотрите скриншот:
Массовое отображение стрелок между ячейками с помощью Kutools for Excel
Отображение всех стрелок нескольких ячеек по очереди занимает много времени. Если вы хотите, чтобы стрелки между ячейками отображались автоматически, попробуйте Мониторинг прецедентов и иждивенцев полезности Kutools for Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Включите служебную программу «Отслеживание прецедентов и зависимостей», нажав Kutools > Еще > Мониторинг прецедентов и иждивенцев.
Затем включаются как прецеденты трассировки, так и зависимые трассировки.
Когда вы выбираете ячейку или диапазон, стрелки будут отображаться немедленно, если есть отношения между текущими выбранными ячейками и другими ячейками.
Заметки:
1. Нажмите Kutools > Еще > Отслеживание прецедентов и иждивенцев снова отключить эти две утилиты. 2. Если вы хотите только включить Прецеденты трассировки or Следить за зависимыми, Пожалуйста, нажмите Kutools > Еще > Отслеживайте прецеденты диапазонов or Отслеживайте прецеденты диапазонов.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Этот простой прием пригодится всем, кто хоть раз делал в своей жизни отчет, где нужно наглядно показать изменение какого-либо параметра (цен, прибыли, расходов) по сравнению с предыдущими периодами. Суть его в том, что прямо внутрь ячеек можно добавить зеленые и красные стрелки, чтобы показать куда сдвинулось исходное значение:
Реализовать подобное можно несколькими способами.
Способ 1. Пользовательский формат со спецсимволами
Предположим, что у нас есть вот такая таблица с исходными данными, которые нам надо визуализировать:
Для расчета процентов динамики в ячейке D4 используется простая формула, вычисляющая разницу между ценами этого и прошлого года и процентный формат ячеек. Чтобы добавить к ячейкам нарядные стрелочки, делаем следующее:
Выделите любую пустую ячейку и введите в нее символы "треугольник вверх" и "треугольник вниз", используя команду Вставить - Символ (Insert - Symbol) :
Лучше использовать стандартные шрифты (Arial, Tahoma, Verdana), которые точно есть на любом компьютере. Закройте окно вставки, выделите оба введенных символа (в строке формул, а не ячейку с ними!) и скопируйте в Буфер (Ctrl+C).
Выделите ячейки с процентами (D4:D10) и откройте окно Формат ячейки (можно использовать Ctrl+1). На вкладке Число (Number) выберите в списке формат Все форматы (Custom) и вставьте скопированные символы в строку Тип, а потом допишите к ним вручную с клавиатуры нули и проценты, чтобы получилось следующее:
Принцип прост: если в ячейке будет положительное число, то к ней будет применяться первый пользовательский формат, если отрицательное - второй. Между форматами стоит обязательный разделитель - точка с запятой. И ни в коем случае не вставляйте никаких пробелов "для красоты". После нажатия на ОК наша таблица будет выглядеть почти как задумано:
Добавить красный и зеленый цвет к ячейкам можно двумя способами. Первый - вернуться в окно Формат ячеек и дописать нужные цвета в квадратных скобках прямо в нашем пользовательском формате:
Лично мне не очень нравится получающийся в этом случае ядовитый зеленый, поэтому я предпочитаю второй вариант - добавить цвет с помощью условного форматирования. Для этого выделите ячейки с процентами и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Больше (Home - Conditional formatting - Highlight Cell Rules - Greater Then) , введите в качестве порогового значения 0 и задайте желаемый цвет:
Потом повторите эти действия для отрицательных значений, выбрав Меньше (Less Then) , чтобы отформатировать их зеленым.
Если немного поэкспериментировать с символами, то можно найти много аналогичных вариантов реализации подобного трюка с другими символами:
Способ 2. Условное форматирование
Выделите ячейки с процентами и откройте на вкладке Главная - Условное форматирование - Наборы значков - Другие правила (Home - Conditional formatting - Icon Sets - More) . В открывшемся окне выберите нужные значки из выпадающих списков и задайте ограничения для подстановки каждого из них как на рисунке:
После нажатия на ОК получим результат:
Плюсы этого способа в его относительной простоте и приличном наборе различных встроенных значков, которые можно использовать:
Минус же в том, что в этом наборе нет, например, красной стрелки вверх и зеленой вниз, т.е. для роста прибыли эти значки использовать будет логично, а вот для роста цен - уже не очень. Но, в любом случае, этот способ тоже заслуживает, чтобы вы его знали ;)
Этот простой прием пригодится всем, кто хоть раз делал в своей жизни отчет, где нужно наглядно показать изменение какого-либо параметра (цен, прибыли, расходов) по сравнению с предыдущими периодами. Суть его в том, что прямо внутрь ячеек можно добавить зеленые и красные стрелки, чтобы показать куда сдвинулось исходное значение:
Реализовать подобное можно несколькими способами.
Способ 1. Пользовательский формат со спецсимволами
Предположим, что у нас есть вот такая таблица с исходными данными, которые нам надо визуализировать:
Для расчета процентов динамики в ячейке D4 используется простая формула, вычисляющая разницу между ценами этого и прошлого года и процентный формат ячеек. Чтобы добавить к ячейкам нарядные стрелочки, делаем следующее:
Выделите любую пустую ячейку и введите в нее символы "треугольник вверх" и "треугольник вниз", используя команду Вставить - Символ (Insert - Symbol) :
Лучше использовать стандартные шрифты (Arial, Tahoma, Verdana), которые точно есть на любом компьютере. Закройте окно вставки, выделите оба введенных символа (в строке формул, а не ячейку с ними!) и скопируйте в Буфер (Ctrl+C).
Выделите ячейки с процентами (D4:D10) и откройте окно Формат ячейки (можно использовать Ctrl+1). На вкладке Число (Number) выберите в списке формат Все форматы (Custom) и вставьте скопированные символы в строку Тип, а потом допишите к ним вручную с клавиатуры нули и проценты, чтобы получилось следующее:
Принцип прост: если в ячейке будет положительное число, то к ней будет применяться первый пользовательский формат, если отрицательное - второй. Между форматами стоит обязательный разделитель - точка с запятой. И ни в коем случае не вставляйте никаких пробелов "для красоты". После нажатия на ОК наша таблица будет выглядеть почти как задумано:
Добавить красный и зеленый цвет к ячейкам можно двумя способами. Первый - вернуться в окно Формат ячеек и дописать нужные цвета в квадратных скобках прямо в нашем пользовательском формате:
Лично мне не очень нравится получающийся в этом случае ядовитый зеленый, поэтому я предпочитаю второй вариант - добавить цвет с помощью условного форматирования. Для этого выделите ячейки с процентами и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Больше (Home - Conditional formatting - Highlight Cell Rules - Greater Then) , введите в качестве порогового значения 0 и задайте желаемый цвет:
Потом повторите эти действия для отрицательных значений, выбрав Меньше (Less Then) , чтобы отформатировать их зеленым.
Если немного поэкспериментировать с символами, то можно найти много аналогичных вариантов реализации подобного трюка с другими символами:
Способ 2. Условное форматирование
Выделите ячейки с процентами и откройте на вкладке Главная - Условное форматирование - Наборы значков - Другие правила (Home - Conditional formatting - Icon Sets - More) . В открывшемся окне выберите нужные значки из выпадающих списков и задайте ограничения для подстановки каждого из них как на рисунке:
После нажатия на ОК получим результат:
Плюсы этого способа в его относительной простоте и приличном наборе различных встроенных значков, которые можно использовать:
Минус же в том, что в этом наборе нет, например, красной стрелки вверх и зеленой вниз, т.е. для роста прибыли эти значки использовать будет логично, а вот для роста цен - уже не очень. Но, в любом случае, этот способ тоже заслуживает, чтобы вы его знали ;)
Читайте также: