Обща формула
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Обобщение
За да сумирате всяка n-та колона, можете да използвате формула, базирана на функциите SUMPRODUCT, MOD и COLUMN.
В показания пример формулата в 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 така:
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, за да принуди TRUE, когато остатъкът е нула, и FALSE, когато не, тогава използваме двойно отрицателно (-), за да принудим TRUE и FALSE на единици и нули. Това оставя масив като този:
(0,0,1,0,0,1,0,0,1)
Където 1s сега показват "n-ти стойности". Това влиза в SUMPRODUCT като масив1, заедно с B5: J5 като масив2. След това SUMPRODUCT прави своето, първо умножава, след това сумира произведенията на масивите.
Единствените стойности, които „преживяват“ умножението, са тези, при които array1 съдържа 1. По този начин можете да мислите за логиката на array1 „филтриране“ на стойностите в array2.
Сумирайте всяка друга колона
Ако искате да сумирате всяка друга колона, просто адаптирайте тази формула според необходимостта, като имате предвид, че формулата автоматично присвоява 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)
