Предположим, у вас есть рабочий лист, содержащий ряд формул в ячейках A1: A3. Ячейка A1 содержит формулу = Sheet1! B4, ячейка A2 содержит = Sheet1! B18, а ячейка A3 содержит = Sheet1! B32. Возможно, вам придется продолжить этот шаблон вниз по столбцу, чтобы A4 содержал = Sheet1! B46 и т. Д.

Проблема в том, что если вы просто скопируете ячейки, шаблон не будет продолжен. Вместо этого формулы корректируются на основе отношения целевой ячейки к исходной ячейке. Таким образом, если вы вставите A1: A3 в A4: A6, тогда A4 будет содержать = Sheet1! B7, что вам не нужно. (Это происходит, если вы специально копируете и вставляете или заполняете ячейки, перетаскивая маркер заливки.)

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

Например, рассмотрим эту формулу:

=INDIRECT("Sheet1!B"&((ROW()-1)*14)+4)

Эта формула создает ссылку на основе положения ячейки, в которой находится формула. Если эта формула помещается в ячейку A1, функция СТРОКА возвращает 1 — строку, в которую помещена формула.

Таким образом, формула становится такой:

=INDIRECT("Sheet1!B"&((1-1)*14)+4)

=INDIRECT("Sheet1!B"&(0*14)+4)

=INDIRECT("Sheet1!B"&0+4)

=INDIRECT("Sheet1!B"&4)

=INDIRECT("Sheet1!B4")

Возвращается значение Sheet1! B4, как и требовалось изначально.

Однако когда вы копируете эту формулу вниз по столбцу, функция СТРОКА возвращает что-то другое в каждой строке. Фактически, функция СТРОКА становится способом увеличить смещение каждой формулы на 14 строк по сравнению с предыдущей — именно то, что вы хотели.

Вы также можете использовать немного другой подход, на этот раз используя функцию СМЕЩЕНИЕ:

=OFFSET(Sheet1!$B$4,(ROW()-1)*14,0)

Эта формула получает значение на основе строки, в которую помещена формула (опять же, с использованием функции СТРОКА) и смещения от ячейки Sheet1! B4.

Помещенная в первую строку столбца, а затем скопированная в этот столбец, формула возвращает значения в соответствии с желаемым шаблоном.

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

  1. Начните с нового пустого листа в книге, содержащей лист с именем «Лист1».

  2. В ячейку A1 введите формулу = Sheet1! B4.

  3. Поскольку в шаблоне формулы каждые 14 строк, выделите диапазон A1: A14. Формула должна быть только в первой ячейке; остальные 13 пустые.

  4. Перетащите маркер заливки (маленький квадратик в правом нижнем углу выделения вниз для хорошего количества строк — возможно, 1000 или около того.

Диапазон A1: A1000 все равно должен быть выделен.

  1. Выберите Edit | Go To | Special. Excel отобразит диалоговое окно Go To Special (см. Рис. 1.)

  2. Нажмите кнопку выбора «Пробелы», затем нажмите OK. || выделенные ячейки выбираются.

  3. Выберите Правка | Удалить | Сдвинуть ячейки вверх /

В результате вы получите только формулы с желаемым шаблоном.

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

Этот совет (3067) применим к Microsoft Excel 97, 2000, 2002 и 2003.