У Адама две формулы в ячейках A1 и A2. В ячейке A1 у него есть формула = B150, а в ячейке A2 — формула = B149. Он попытался скопировать эту последовательность вниз, используя маркер заполнения, но последовательность не сработала.

(Например, после заполнения ячейки A3 содержала = B152, хотя она должна была содержать = B148.) Адам задается вопросом, есть ли способ заставить такое заполнение работать должным образом.

Адам прав; заливка в этом случае работать не будет. Причина в том, что функция заполнения в Excel работает со значениями, а не с формулами.

Итак, есть несколько способов решить эту проблему.

Первый — поместить какие-то текстовые значения в ячейки A1 и A2. Например, вы можете поместить текст «x = B150» (без кавычек) в ячейку A1 и «x = B149» (опять же, без кавычек) в ячейку A2. Excel правильно анализирует эти ячейки как текст. Затем вы можете выбрать A1: A2 и перетащить вниз, насколько вам нужно. Excel следует шаблону в тексте (помните, это не формула) и уменьшает числа. (Не перетаскивайте за пределы ячейки A150. Когда вы нажмете A151, текст будет «x = B0», а затем Excel снова начнет отсчет вверх в ячейке A152.)

Теперь выберите все ячейки, которые вы только что заполнили, и используйте команду «Найти и заменить», чтобы найти символ предисловия (x) и заменить его ничем. В итоге вы получаете знак равенства на лидирующей позиции, и теперь Excel анализирует ячейки как формулы, как вы хотите.

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

=INDIRECT("B"&151-ROW())

Вставьте это в ячейку A1, а затем скопируйте вниз через ячейку A150. Это работает, потому что вы вычитаете текущий номер строки из 151, добавляете букву «B» перед ним, а затем используете INDIRECT для ссылки на значение в этом вычисленном адресе ячейки. Очевидно, что если вы хотите сослаться на другую ячейку (кроме начинающейся с B150), вам необходимо настроить формулу так, чтобы вычислялся правильный адрес ячейки.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (8443) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.