У Тима есть рабочий лист, в котором ячейка B1 содержит формулу = СУММ (A1: A7).

Он хочет скопировать эту формулу вниз и увеличить диапазон на 7 строк, чтобы ячейка B2 содержала формулу = СУММ (A8: A14), ячейка B3 содержала бы = СУММ (A15: A21) и т. Д. Проблема в том, что когда он копирует его вниз, каждый «конец» диапазона увеличивается только на 1, тогда как он должен увеличиваться на 7, чтобы удовлетворить его потребность. Он задается вопросом, как заставить Excel делать правильное приращение.

Вы не можете заставить Excel выполнять правильное приращение с помощью копирования и вставки; он просто этого не сделает. Причина проста — бывают случаи, когда увеличение на 1 имеет смысл с теоретической точки зрения. Поскольку Excel не может читать ваши мысли (по крайней мере, до следующей версии:>)), он предполагает, что он должен увеличиваться только на 1.

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

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

Если вы поместите эту формулу в ячейку B1, она будет работать, потому что она проверяет номер строки (возвращенный функцией ROW) строки, в которой содержится формула. Поскольку он находится в строке 1, формула вычисляется в Excel следующим образом:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

=SUM(INDIRECT("A" & (1-1)7+1 & ":A" & (1-1)7+7))

=SUM(INDIRECT("A" & 07+1 & ":A" & 07+7))

=SUM(INDIRECT("A" & 0+1 & ":A" & 0+7))

=SUM(INDIRECT("A" & 1 & ":A" & 7))

=SUM(INDIRECT("A1:A7"))

=SUM(A1:A7)

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

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

=SUM(OFFSET(A1,((ROW()-1)6),0):OFFSET(A7,((ROW()-1)6),0))

Более короткий подход, использующий СМЕЩЕНИЕ, следующий:

=SUM(OFFSET($A$1,ROW()*7-7,0,7,1))

Независимо от подхода, вы, вероятно, можете сказать, что идея состоит в том, чтобы придумать формулу, которая использует строку, в которой появляется формула, для построения диапазона, который вам действительно нужен. Каждый из примеров до сих пор предполагает, что вы начинаете с ячейки B1. Если вы хотите начать с ячейки B2, вам необходимо изменить формулы для учета той строки, с которой вы начинаете. Чтобы дать вам общее представление о том, как это работает, если бы вы начинали с ячейки B2, три формулы, представленные в этом совете, были бы изменены следующим образом:

=SUM(INDIRECT("A" & (ROW()-2)7+2 & ":A" & (ROW()-2)7+8))

=SUM(OFFSET(A2,((ROW()-2)6),0):OFFSET(A8,((ROW()-2)6),0))

=SUM(OFFSET($A$2,(ROW()-1)*7-7,0,7,1))

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

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

Этот совет (8387) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Incrementing_References_by_Multiples_when_Copying_Formulas [Увеличение ссылок на кратные при копировании формул].