У Тима есть лист, в котором ячейка 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» & ( СТРОКА () — 1) * 7 + 7))
Если вы поместите эту формулу в ячейку B1, она будет работать, потому что она проверяет номер строки (возвращенный функцией СТРОКА) строка, в которой содержится формула. Поскольку он находится в строке 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) ) = СУММ (КОСВЕННАЯ («A» & 0 * 7 + 1 & «: A» & 0 * 7 + 7)) = СУММ (КОСВЕННАЯ («A» & 0 + 1 & «: A» & 0 + 7) ) = СУММ (КОСВЕННАЯ («A» & 1 & «: A» & 7)) = СУММ (КОСВЕННАЯ («A1: A7»)) = СУММ (A1: A7)
Что вы в итоге в B1 будет желаемая сумма. (Функция ДВССЫЛ использует значение в строке, как если бы это был реальный диапазон, что и нужно вам.) Когда вы копируете формулу вниз по столбцу, по мере увеличения номера строки формула обеспечивает правильное приращение 7 на обоих концах. диапазона.
Есть и другие варианты этой техники, которые вы можете использовать. Единственное отличие состоит в том, что в вариантах используются разные функции рабочего листа для выполнения одной и той же задачи. Например, следующий вариант по-прежнему использует функцию СТРОКА, но затем в конечном итоге полагается на функцию СМЕЩЕНИЕ для вычисления желаемого диапазона:
= СУММ (СМЕЩЕНИЕ (A1, ((СТРОКА () — 1) * 6), 0): OFFSET (A7, ((ROW () — 1) * 6), 0))
Более короткий подход, использующий OFFSET, выглядит следующим образом:
= 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): СМЕЩЕНИЕ (A8, ((СТРОКА () — 2) * 6), 0)) = СУММ (СМЕЩЕНИЕ ($ A $ 2, (СТРОКА () — 1) * 7-7,0,7, 1))
Начните с другого места, и вам нужно будет внести дополнительные изменения в формулу, которую вы выберете для использования.
Источник: