Загальна формула
=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 робить свою справу, спочатку множуючи, а потім підсумовуючи добутки масивів.
Єдиними значеннями, які «переживають» множення, є ті, де масив1 містить 1. Таким чином, ви можете думати про логіку масиву1, що «фільтрує» значення в масиві2.
Підсумуйте всі інші стовпці
Якщо ви хочете підсумувати всі інші стовпці, просто адаптуйте цю формулу за потреби, маючи на увазі, що формула автоматично присвоює 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)
