При создании формул Microsoft Excel вы можете генерировать вычисления, используя определенные значения — также известные как жесткое кодирование — или ссылаясь на другие ячейки в рабочей книге. В этом руководстве я объясню, почему жесткое кодирование значений может вызывать проблемы, и как использование ссылок на ячейки часто является лучшим вариантом.
Проблемы, вызванные жестким кодированием значений в формулах Excel
Допустим, вы вычисляете себестоимость продукции в вашем магазине, когда добавляется налог 20%. Для этого в столбце C вы умножили значения в столбце B на 1.2, как показано в строке формул в верхней части окна Excel:
= B2 * 1.2
Однако теперь представьте, что в следующем году налог на все товары снижается до 15%. В результате вам придется вернуться в ячейки и изменить все формулы в столбце C так, чтобы значения в столбце B умножались на 1.15.
Да, вы можете изменить формулу в ячейке C2 и автоматически дублировать ее в остальных ячейках, дважды щелкнув или щелкнув и перетащив маркер заполнения в правом нижнем углу ячейки. Однако этот подход занимает больше времени, чем необходимо, и может легко привести к ошибкам копирования или автозаполнения.
Более того, жесткое кодирование значений в формулах Excel затрудняет обнаружение ошибок, поскольку вам приходится выбирать ячейку, чтобы увидеть состав расчета в строке формул. В качестве альтернативы вы можете нажать Ctrl+` (гравитационный акцент), чтобы просмотреть все формулы в таблице, но — опять же — это ненужный шаг, который может добавить путаницы.
Наконец, если вы делитесь своей книгой Excel с другими, жестко заданные значения в формулах не имеют никакого контекста, объясняющего, как вы пришли к этому числу.
Почему ссылка на переменные ячейки — лучший вариант
Чтобы преодолеть недостатки жесткого кодирования значений в формулах Microsoft Excel, можно использовать ссылки на ячейки.
Используя приведенный выше пример, вместо того, чтобы умножать каждое значение в столбце B на 1.2 в вашей формуле, вы можете сослаться на ячейку, содержащую переменную, — в данном случае на ячейку E2:
=B2*$E$2
Важно отметить, что ссылка на ячейку E2 является абсолютной и представлена символами доллара ($) перед столбцом и строкой. Если этого не сделать, то при дублировании формулы в оставшиеся ячейки в столбце C ссылка на ячейку будет скорректирована относительно положения активной ячейки. Например, формула в ячейке C3 будет ссылаться на ячейку E3, формула в ячейке C4 будет ссылаться на ячейку E4 и т. д.
При создании формулы нажмите клавишу F4 после ввода ссылки на ячейку, чтобы превратить ее из относительной в абсолютную.
Теперь, когда налог на товары изменится до 15%, вы можете просто изменить значение в ячейке E2 на 1.15, и все формулы, ссылающиеся на эту ячейку, мгновенно обновятся.
Более того, поскольку ячейка, содержащая переменную, всегда отображается, ее легко проверить на точность. Кроме того, поскольку ячейка E1 содержит слово «Tax», любой, кто впервые обращается к таблице, может увидеть полный контекст того, как рассчитываются общие значения в столбце C.
Отказ от ответственности: есть случаи, когда жесткое кодирование значений в формулах может быть более уместным. Например, вы можете жестко закодировать недельный итог, который нужно разделить на семь, чтобы получить ежедневный итог, поскольку количество дней в неделе никогда не меняется. Кроме того, формулы со ссылками на ячейки используют немного больше памяти, чем формулы с жестко закодированными значениями, поэтому, если вы делаете это часто, вы можете заметить снижение — хотя и незначительное — производительности вашей рабочей книги.
Использование ссылок на ячейки в формулах вместо жестко закодированных значений особенно полезно, если параметры расчета, вероятно, изменятся в будущем. В этом примере функция IFS используется в столбце C для определения того, сдал ли каждый студент экзамен в конце семестра или нет, в зависимости от того, достигли ли они переменной проходного балла в ячейке E2:
=ЕСЛИ([@Оценка]<$E$2,»НЕСДАЧА»,[@Оценка]>=$E$2,»СДАЧА»)
В следующем семестре вы можете просто продублировать таблицу и изменить переменную проходного балла, не беспокоясь о корректировке формулы для получения правильного результата для этих данных.
Совет профессионала: дайте переменной имя
До сих пор я говорил о ссылках на ячейки в формулах Excel вместо жесткого кодирования значений. Один из способов еще больше обезопасить ваши формулы — дать ячейке, содержащей переменную, имя, что особенно полезно, если вы планируете использовать ее в своей рабочей книге.
Предположим, у вас есть несколько рабочих листов в рабочей книге, каждый из которых содержит различные товары из разных магазинов, и вам нужно отобразить общую стоимость каждого товара, включая налог.
На снимке экрана выше вы заметите, что также есть рабочий лист под названием «Налог». Этот лист содержит текущую налоговую ставку и значение, на которое необходимо умножить затраты, чтобы рассчитать общую стоимость.
Вместо того, чтобы каждый раз запоминать расположение переменной в рабочей книге, когда вы хотите рассчитать общую стоимость продукта, вы можете дать этой ячейке имя. Для этого выберите соответствующую ячейку и в поле имени в верхнем левом углу экрана введите запоминающуюся метку ячейки. В моем случае я выбрал «TAX». Затем нажмите Enter, чтобы завершить процесс именования.
Теперь, независимо от того, где вы находитесь в своей рабочей книге, вы можете легко ссылаться на эту переменную в своей формуле:
=B2*НАЛОГ
Всякий раз, когда меняется налоговая ставка, вам нужно только один раз скорректировать ее в вашем налоговом листе, чтобы обновить все ваши расходы. Более того, поскольку ссылки на именованные диапазоны являются абсолютными по своей природе, вам не нужно помнить о добавлении знаков доллара в ваши формулы!
Ссылки на ячейки вместо жесткого кодирования значений в формулах — не единственный способ сэкономить время в Microsoft Excel. Например, вы можете добавить наиболее часто используемые команды на панель быстрого доступа или изучить некоторые из самых полезных сочетаний клавиш Excel, чтобы не переключаться между клавиатурой и мышью.
