Общая формула
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Резюме
Для суммирования каждого n-го столбца можно использовать формулу, основанную на функциях СУММПРОИЗВ, МОД и СТОЛБЕЦ.
В показанном примере формула L5 имеет следующий вид:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Объяснение
По сути, SUMPRODUCT используется для суммирования значений в строке, которые были «отфильтрованы» с использованием логики, основанной на MOD. Ключ в следующем:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
Этот фрагмент формулы использует функцию COLUMN для получения набора «относительных» номеров столбцов для диапазона (подробно объясненного здесь), который выглядит следующим образом:
(1,2,3,4,5,6,7,8,9)
Это входит в МОД так:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
где K5 - значение N в каждой строке. Функция MOD возвращает остаток для каждого номера столбца, деленного на N. Так, например, когда N = 3, MOD вернет что-то вроде этого:
(1,2,0,1,2,0,1,2,0)
Обратите внимание, что нули появляются в столбцах 3, 6, 9 и т. Д. В формуле используется = 0, чтобы получить ИСТИНА, когда остаток равен нулю, и ЛОЖЬ, когда нет, затем мы используем дважды отрицательный (-), чтобы привести ИСТИНА и ЛОЖЬ до единиц и нулей. В результате получается такой массив:
(0,0,1,0,0,1,0,0,1)
Где 1 теперь обозначают «n-е значения». Это входит в SUMPRODUCT как array1 вместе с B5: J5 как array2. Затем СУММПРОИЗВ выполняет свое дело, сначала умножая, а затем суммируя произведения массивов.
Единственные значения, которые «выживают» при умножении, - это те, в которых array1 содержит 1. Таким образом, вы можете думать о логике «фильтрации» значений array2 в array1.
Суммируйте каждый второй столбец
Если вы хотите суммировать все остальные столбцы, просто измените эту формулу по мере необходимости, помня, что формула автоматически присваивает 1 первому столбцу в диапазоне. Чтобы суммировать ЧЕТНЫЕ столбцы, используйте:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
Чтобы суммировать столбцы ODD, используйте:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
