Перейти до основного матеріалу

Досліджуйте нове, Безкоштовна версія Kutools для Outlook сьогодні!

 

Підсумуйте кожен n-й рядок або стовпець діапазону в Excel

Автор: Силувій Остання зміна: 2021-10-28

Цей підручник покаже вам, як створити формулу на основі функцій SUMPRODUCT, MOD і COLUMN для підсумовування кожного n-го рядка або стовпця в діапазоні в Excel.

Як підсумувати кожен n-й рядок або стовпець у заданому діапазоні в Excel?

Як показано вище, щоб підсумувати кожен третій рядок у стовпці «Сума», ви можете застосувати наведену нижче формулу.

Загальні формули

=SUMPRODUCT(--(MOD(ROW(value_range)-ROW(first_cell)+1,n)=0),value_range)

Аргументи

Діапазон_значення: діапазон значень для суми;
Перша_клітина: перша клітинка в діапазоні значень;
N: число, яке представляє кожен n-й рядок, використаний для знаходження суми.

Як користуватися цією формулою?

1. Виділіть порожню комірку.

2. Скопіюйте або введіть наведену нижче формулу та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.

=SUMPRODUCT(--(MOD(ROW(E5:E14)-ROW(E5)+1,3)=0),E5:E14)

примітки: Щоб підсумувати кожен n-й стовпець у певному діапазоні рядків, вам просто потрібно замінити всі РЯДКИ у формулі на СТОБЦІ, щоб зробити це таким чином.

=SUMPRODUCT(--(MOD(COLUMN(C6:H6)-COLUMN(C6)+1,3)=0),C6:H6)

Пояснення формули

=SUMPRODUCT(--(MOD(ROW(E5:E14)-ROW(E5)+1,3)=0),E5:E14)

РЯД(E5:E14)-РЯДК(E5)+1:
> ROW(E5:E14): функція ROW отримує відносні номери рядків для діапазону E5:E14 і повертає такий масив: {5;6;7;8;9;10;11;12;13;14} ;
> ROW(E5): функція ROW повертає номер рядка 5 для комірки E5;
> {5;6;7;8;9;10;11;12;13;14}-{5}+1: Потім кожне число в масиві віднімає число 5, а потім додає 1, щоб отримати новий масив: {1; 2;3;4;5;6;7;8;9;10}.
MOD(({1;2;3;4;5;6;7;8;9;10},3): Функція MOD повертає залишок кожного числа в масиві, поділеного на 3 (тут число 3 є значенням для N): {1;2;0;1;2;0;1;2;0;1}; У цьому масиві 0 представляють рядки 3, 6, 9 тощо.
SUMPRODUCT(--({1;2;0;1;2;0;1;2;0;1}=0),E5:E14):
> ---({1;2;0;1;2;0;1;2;0;1}=0): тут кожен залишок порівнюється з 0, щоб примусово створити масив TRUE або FALSE, наприклад {FALS;FALSE;TRUE; НЕПРАВДНЯ; НЕПРАВДА; ПРАВДА; НЕПРАВДА; НЕПРАВДА; Потім два знаки мінус перетворюють «TRUE» і «FALSE» в 1s і 0s: {0;0;1;0;0;1;0;0;1;0};
> SUMPRODUCT({0;0;1;0;0;1;0;0;1;0},E5:E14)=SUMPRODUCT({0;0;1;0;0;1;0;0;1; 0},{1428;2010;2288;1831;2069;1728;1325;1469;1023;1100}): кожна пара записів у масивах множиться разом і повертає {0;0;2288;0;0;1728 ;0;0;1023;0}, а потім функція SUMPRODUCT підсумовує всі числа в масиві і повертає кінцевий результат у вигляді 5039.

Супутні функції

Функція Excel SUMPRODUCT
Функцію Excel SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім для отримання суми продуктів.

Функція Excel MOD
Функція Excel MOD повертає залишок після поділу числа на дільник.

Функція Excel ROW
Функція Excel ROW повертає номер рядка посилання.

Функція Стовпець Excel
Функція COLUMNS Excel повертає номер стовпця, у якому з’являється формула, або повертає номер стовпця даного посилання.

Пов’язані формули

Сума значень за місяцями (з роком або без нього)
У цьому підручнику докладно демонструються дві формули, які допомагають підсумовувати значення за певним місяцем і роком або підсумовувати значення за певним місяцем ігнорувати рік у Excel.

Сума значень за тиждень певної дати
У цьому посібнику розповідається про те, як підсумувати значення за тижнем, на який припадає певна дата

Сума значень за тижнем або днем ​​тижня
У цьому підручнику покроково демонструються формули, які допоможуть вам підсумувати значення в певному діапазоні на основі вказаного номера тижня або дня тижня в Excel.

Підсумуйте кожні n рядків або стовпців діапазону
У цьому посібнику пояснюються дві формули на основі функцій SUM і OFFSET для підсумовування кожні n рядків або стовпців у визначеному діапазоні в Excel.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів:  12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...

Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.