Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Суммирование каждого n-го ряда или столбца в диапазоне в Excel

Author Siluvia Last modified

В этом руководстве показано, как создать формулу на основе функций СУММПРОИЗВ, ОСТАТ и СТОЛБЕЦ для суммирования каждого n-го ряда или столбца в диапазоне в Excel.

doc-sum-every-nth-row-column-1

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

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

Общие формулы

=СУММПРОИЗВ(--(ОСТАТ(СТРОКА(диапазон_значений)-СТРОКА(первая_ячейка)+1,n)=0),диапазон_значений)

Аргументы

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

Как использовать эту формулу?

1. Выберите пустую ячейку.

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

=СУММПРОИЗВ(--(ОСТАТ(СТРОКА(E5:E14)-СТРОКА(E5)+1,3)=0),E5:E14)

doc-sum-every-nth-row-column-2

Примечания: Чтобы суммировать каждый n-й столбец в определённом диапазоне строк, нужно просто заменить все СТРОКИ в формуле на СТОЛБЦЫ, как показано ниже.

=СУММПРОИЗВ(--(ОСТАТ(СТОЛБЕЦ(C6:H6)-СТОЛБЕЦ(C6)+1,3)=0),C6:H6)

doc-sum-every-nth-row-column-3

Объяснение формулы

=СУММПРОИЗВ(--(ОСТАТ(СТРОКА(E5:E14)-СТРОКА(E5)+1,3)=0),E5:E14)

СТРОКА(E5:E14)-СТРОКА(E5)+1:
> СТРОКА(E5:E14): Функция СТРОКА получает относительные номера строк для диапазона E5:E14 и возвращает массив следующего вида: {5;6;7;8;9;10;11;12;13;14};
> СТРОКА(E5): Здесь функция СТРОКА возвращает номер строки 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}.
ОСТАТ(({1;2;3;4;5;6;7;8;9;10},3): Функция ОСТАТ возвращает остаток от деления каждого числа в массиве на 3 (здесь число 3 — это значение для N): {1;2;0;1;2;0;1;2;0;1}; В этом массиве нули представляют строки 3, 6, 9 и т.д.
СУММПРОИЗВ(--({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, чтобы получить массив ИСТИНА или ЛОЖЬ следующего вида: {ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}; Затем два знака минус преобразуют «ИСТИНА» и «ЛОЖЬ» в 1 и 0: {0;0;1;0;0;1;0;0;1;0};
> СУММПРОИЗВ({0;0;1;0;0;1;0;0;1;0},E5:E14)=СУММПРОИЗВ({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}, а затем функция СУММПРОИЗВ складывает все числа в массиве и возвращает окончательный результат как 5039.

Связанные функции

Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.

Функция ОСТАТ в Excel
Функция ОСТАТ в Excel возвращает остаток после деления числа на делитель.

Функция СТРОКА в Excel
Функция СТРОКА в Excel возвращает номер строки ссылки.

Функция СТОЛБЕЦ в Excel
Функция СТОЛБЕЦ в Excel возвращает номер столбца, в котором появляется формула, или возвращает номер столбца указанной ссылки.

Связанные формулы

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

Суммирование значений по неделе заданной даты
Это руководство рассказывает о том, как суммировать значения по неделе, к которой относится заданная дата.

Суммирование значений по номеру недели или дню недели
Это руководство пошагово демонстрирует формулы, которые помогут вам суммировать значения в определенном диапазоне на основе указанного номера недели или дня недели в Excel.

Суммирование каждых n строк или столбцов в диапазоне
Это руководство объясняет две формулы на основе функций СУММ и СМЕЩ для суммирования каждых n строк или столбцов в указанном диапазоне в Excel.

Лучшие инструменты для повышения производительности Office

Kutools для Excel - Помогает вам выделиться из толпы

🤖 KUTOOLS AI Помощник: Революционизируйте анализ данных на основе: Интеллектуальное выполнение   |  Генерация кода  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools…
Популярные функции: Поиск, Выделение или Отметка дубликатов  |  Удалить пустые строки  |  Объединить столбцы или ячейки без потери данных  |  Округлить без формулы ...
Супер VLookup: Множественные критерии  |  Множественные значения  |  На нескольких листах  |  Распознавание нечетких соответствий...
Расширенный раскрывающийся список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Раскрывающийся список с множественным выбором...
Управление столбцами: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить столбцы для выбора одинаковых и разных ячеек ...
Основные функции: Сетка фокусировки  |  Дизайн листа  |  Улучшенная строка формул  |  Управление книгами и листами | Библиотека автотекста (Авто текст)  |  Выбор даты  |  Объединить данные  |  Шифрование/Расшифровка ячеек  |  Отправка писем по списку  |  Супер фильтр  |  Специальный фильтр (фильтр жирного/курсивного/зачеркнутого текста...) ...
Топ-15 наборов инструментов:  12 инструментов для работы с текстом (Добавить текст, Удалить определенные символы ...)  |  50+ типов диаграмм (Диаграмма Ганта ...)  |  40+ практических формул (Расчет возраста на основе даты рождения ...)  |  19 инструментов вставки (Вставить QR-код, Вставить изображение по пути ...)  |  12 инструментов преобразования (Преобразовать в слова, Конвертация валюты ...)  |  7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки Excel ...)  |  ... и многое другое
Используйте Kutools на вашем предпочитаемом языке – поддерживает английский, испанский, немецкий, французский, китайский и более 40 других языков!

Kutools для Excel имеет более 300 функций, гарантируя, что то, что вам нужно, находится всего в одном клике...

Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)

  • Один щелчок, чтобы переключаться между десятками открытых документов!
  • Сократите сотни кликов мышью каждый день, попрощайтесь с болью в руке от мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.