Как в экселе сделать зависимые ячейки. Поиск и исправление ошибок в формулах MS Excel

Вызов команды:
-группа Ячейки/Диапазоны -Ячейки

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

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

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

Для чего вообще нужна подобная команда? К примеру, выделенная ячейка содержит формулу:
=СУММ(C6 * E19 ;СУММ(СУММ("Статьи затрат.xls"!C2;"Статьи затрат.xls"!C4;"Статьи затрат.xls"!C6;"Статьи затрат.xls"!C7;"Статьи затрат.xls"!C8);СУММ("Производственная себестоимость"!B4;"Производственная себестоимость"!B5)))
при этом от значения этой ячейки зависят значения еще нескольких ячеек(т.к. изменение значения этой ячейки изменит значение других, т.к. в них формулы ссылаются на эту ячейку), в данном случае это несколько ячеек листа Расчет .
Команда построит наглядную карту зависимостей такой ячейки:

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

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

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

Выберите диапазон, который содержит города страны.
Назовите этот диапазон именем страны.

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

Примечание: если среди влияющих и зависимых ячеек присутствую диапазоны, содержащие более 20-ти ячеек, то для таких диапазонов будут отражены значения и формулы первых 20-ти ячеек. Это сделано для более быстрой работы функции, к тому же в большинстве случаев видеть большее количество значений совершенно нет необходимости, да и просматривать в этом окне их будет неудобно. Проще в этом случае перейти прямо из окна к нужному диапазону и посмотреть всю необходимую информацию "на месте".
Создание зависимых выпадающих списков
Если вы допустили ошибку в названии диапазона или выбрали неправильную группу ячеек, Менеджер имен позволит вам внести какие-либо изменения, нажав кнопку «Изменить». Теперь, когда у нас есть готовые имена, мы можем создать раскрывающиеся списки.
Очистить выбранный выбор списка
У зависимых списков, которые мы только что создали в предыдущем разделе, есть небольшой недостаток, и это означает, что после создания первого выбора страны и города при создании нового выбора страны ячейка, показывающая города, останется с предыдущим выбором.

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

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

Добавление данных в зависимые раскрывающиеся списки

Если вы хотите добавить новые данные в раскрывающиеся списки, вы должны быть осторожны, чтобы сохранить соответствующие ссылки в каждом из названных диапазонов. Теперь в стране Мексика есть 4 города вместо 3, поэтому необходимо будет изменить названный рейтинг для ваших городов. Чтобы внести это изменение, мы должны перейти на вкладку «Формулы» и нажать кнопку «Диспетчер имен». Когда вы откроете диалоговое окно, вы заметите две вещи.

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

Если для ячейки нет влияющих или зависимых ячеек - поле карты зависимостей остается пустым.

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

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

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

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

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

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

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

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

Нажмите на ячейку, в которой вы хотите работать. Нажмите вкладку «формулы», а затем нажмите «Прецеденты трассировки» или «Искатели трассировки», чтобы увидеть прецеденты или иждивенцы этой ячейки. Можно видеть все отношения на одном листе за раз, набрав = в пустой ячейке, а затем нажав на кнопку выше 1 и слева от А, которая выбирает все электронные таблицы.

Отображение предыдущих ячеек

Затем нажмите «Ввод», чтобы завершить формулу, нажмите на ячейку, в которой вы написали формулу, и дважды нажмите кнопку «Трек-прецеденты». Если вы отслеживаете предыдущие ячейки, между двумя ячейками, которые идут от маленькой точки в предыдущей ячейке, будет отображаться стрелка в голову стрелки в зависимой ячейке. Дважды щелкните по любой точке стрелки, чтобы сразу перейти к предыдущей ячейке, где бы вы ни находились.

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

Проблемы с предыдущими ячейками

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

Можно, конечно, для отслеживания зависимостей воспользоваться и стандартным средством - вкладка Формулы -группа Зависимости формул -Влияющие ячейки /Зависимые ячейки . Но в данном случае результат будет выглядеть следующим образом:

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

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

Из этого несомненно можно понять большую часть, но если присмотреться к изображению выше, то видно, что стрелка к той же ячейке С8 , на которую влияет выделенная С3 перекрывается стрелками влияющих ячеек C13 , C14:C18 и практически не заметна. А сразу увидеть ячейки с других листов и книг вообще не получится - для этого надо будет сначала дважды щелкнуть мышью на стрелке(которая ведет к значку в виде таблицы ) и в появившемся окне выбрать одну(! ) ссылку. А если ссылок больше 10? Сколько раз надо будет щелкать туда-сюда? Пока будем щелкать уже забудем, что хотели узнать...Так же в этом окне никак не помечены ссылки на закрытые книги - они просто ничем не отличаются от ссылок на доступные источники(открытые книги). О том, что ссылка недоступна узнать можно будет только после того, как попробуем на неё перейти.
И еще нюанс: при отображенных зависимостях одной ячейки отобразить зависимости другой нельзя, пока не уберем стрелки от первой ячейки(нажав Убрать стрелки ). Т.е. для просмотра зависимостей второй ячейки, необходимо сначала убрать отображение зависимостей текущей. После чего заново отобразить стрелки через меню. Несколько затратно по времени, особенно если ячеек куча.

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

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

Группа Зависимости формул вкладки Формулы тоже существует для того, чтобы облегчить вам жизнь (рис. 3.21).

Команда Влияющие ячейки стрелками покажет вам, какие именно ячейки влияют на результат вычисления формулы в выделенной вами ячейке (рис. 3.22).

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

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

Команда Зависимые ячейки покажет вам, какие ячейки зависят от той, что вы выделили (рис. 3.23).

Эти стрелки нужны для наглядности и для проверки формул. Вдруг вы ошиблись и нечаянно ввели в формулу не тот адрес. А стрелки вам все покажут. Когда вы убедитесь, что все сделали правильно, стрелки можно убрать. Команда Убрать стрелки позволяет убрать все стрелки или отдельно стрелки к влияющим ячейкам и отдельно стрелки к зависимым ячейкам. Нужный вариант можно выбрать из списка кнопки.
Кнопка Показать формулы уберет из ячеек результат вычисления и покажет вам
формулы. Это тоже удобно для контроля над собой в процессе работы. Кнопка Проверка наличия ошибок запустит поиск самых распространенных ошибок во всех формулах открытого листа. Кнопка Вычислить формулу поможет вам следить за процессом вычисления в сложных формулах. Она дает возможность вычислять формулу по частям (рис. 3.24).

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

Назначение автозаполнения: = ячейки. Клетка, противоположная значению, должна содержать значение или формулу. Выберите диапазон ячеек. Нажмите кнопку «Ячейки». Проверьте тип (и) данных для удаления. Выбираются соответствующие ячейки. Затем вы можете нажать клавишу «Удалить» на клавиатуре, чтобы удалить содержимое ячеек, сохраняя формулы в первоначально выбранном диапазоне. Выберите ячейку (ы) для параметризации. Выберите вкладку «Параметры». В поле «Разрешить»: выберите строку «Длина текста». В поле «Данные»: выберите строку «ниже или равно».

На рис. 3.24а вы видите формулу = 1000-Сумма_покупок. Если вы нажмете кнопку Вычислить, то программа посчитает подчеркнутое действие, то есть вычислит то, что находится в ячейке Сумма_покупок. На рис. 3.24б Сумма_покупок уже посчитана, и теперь при нажатии кнопки Вычислить программа посчитает второе действие, в этом примере оно же и последнее. На рис. 3.24, в вы видите результат вычислений.

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

«Главная» вкладка «Редакция». Нажмите кнопку «Поиск и выбор». «Перейти». Выберите вариант «Формулы». И если вы хотите защитить лист. Этот пример отправляет количество строк на листе, где они: Если строки с 1 по 10 и с 17 по 19 пусты, но строки с 11 по 16 и с 20 по 24 заполняются, результат равен 11.

Такое пошаговое вычисление очень удобно для больших, громоздких формул, особенно в тот момент, когда вы ищете в них ошибку. Получили вы, например, отрицательную цену на товар и напряженно размышляете, как такое могло случиться. На каком этапе вычислений ошибка? Теперь вы знаете, что можете пошагово проверить все формулы, и быстро найдете, в чем причина. Кстати, если вы глянете на рис. 3.24, в, то увидите внизу две кнопки - Заново и Закрыть. Первую вы нажмете, если не разобрались сразу, в чем проблема, а вторую - если разобрались.

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

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

В группе Зависимости формул вкладки Формулы (см. рис. 3.21) у нас осталась одна нерассмотренная кнопка - Окно контрольного значения. В окно, которое появляется после нажатия данной кнопки, вы можете ввести значимые для вас результаты, то есть те, которые хотите иметь перед глазами и сравнивать (рис. 3.25).

В этом окне нужно нажать кнопку Добавить контрольное значение (рис. 3.26).

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

24.10.2012

Возможно, будет полезно почитать: