Зависимые ячейки Excel: как проверить связи в таблице курса

Разделите два направления проверки

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

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

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

Соберите правильную и ошибочную цепочку

Введите исходные числа: B2 — 4 модуля, B3 — 3 задания на модуль, B4 — 2 итоговых задания. Подпишите ячейки рядом. В C2 запишите =B2*B3, в C3 — =C2+B4, в D2 — =C3*2. Получатся 12 обычных заданий, 14 заданий всего и 28 возможных попыток.

Теперь сохраните копию для учебного расследования. В ней намеренно замените формулу C3 на =C2+B3. Результаты станут равны 15 и 30. Excel получил корректные числа из допустимых ссылок, поэтому сама арифметика не обязана сигнализировать об ошибке. Нарушено наше предметное правило: вместо двух итоговых заданий добавлено количество заданий на модуль.

Не исправляйте итог D2 вручную на 28. Такая подмена скроет причину и разорвёт следующую зависимость. Нужно найти неправильную ссылку в цепочке, затем проверить пересчёт всех результатов. Именно для этого полезно заранее знать ожидаемые 12, 14 и 28.

Найдите источник неверного результата

Выделите C3 и на вкладке «Формулы» в группе проверки формул выберите «Влияющие ячейки», или Trace Precedents. Порядок действий и различия направлений описаны в документации Microsoft по связям формул. Повторное нажатие показывает следующий уровень влияния.

В ошибочной версии непосредственными источниками C3 должны оказаться C2 и B3. Сопоставьте это с правилом: к обычным заданиям нужно прибавить итоговые из B4. Затем откройте формулу и замените неверный адрес. После исправления получите 14 в C3 и 28 в D2.

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

Проследите последствия изменения входа

Теперь выберите B4 и вызовите «Зависимые ячейки», или Trace Dependents. Первый уровень ведёт к C3; следующий позволяет увидеть дальнейшую зависимость D2. Это маршрут воздействия изменения числа итоговых заданий. C2 при этом не должен меняться: обычные задания в нашей модели считаются отдельно.

В копии замените B4 с 2 на 4. Правильные результаты: C2 остаётся 12, C3 становится 16, D2 — 32. Если после изменения итоговые числа остались 15 и 30, вероятно, сохранилась первоначальная ошибочная ссылка. Если меняется C2, расследуйте уже его формулу и исходные данные.

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

Учитывайте границы стрелок

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

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

Не скрывайте проблему функцией ЕСЛИОШИБКА. В нашей ошибочной цепочке она не обнаружит нарушения: 15 и 30 являются допустимыми результатами вычисления. Сначала исправляется источник по смыслу, затем проверяется способ представления числа ученику.

Передайте проверенную модель вместе с условиями

Перед выдачей рабочей тетради повторите исходный набор 4, 3, 2 и контрольное изменение последнего числа на 4. Убедитесь, что обе цепочки дают ожидаемые результаты. Откройте именно сохранённый файл, чтобы проверить ту версию, которая будет размещена в уроке.

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

Ученику можно предложить ошибочную копию как задание: найти подменённый источник, объяснить последствия и показать контроль после исправления. Для Umhub подготовьте условие и правильную версию раздельно. Результатом станет обоснованная проверка модели, а не просто лист со множеством стрелок.