KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

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

АвторSiluviaДата изменения

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

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

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

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

Универсальные формулы

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

Аргументы

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

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

1. Выделите пустую ячейку.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Sum values by month (with or without year)
В этом руководстве подробно разбираются две формулы, которые позволяют суммировать значения за конкретный месяц и год — или только за определённый месяц, игнорируя год, в Excel.

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

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

Суммирование каждых n строк или столбцов в диапазоне
В этом руководстве описаны две формулы на основе функций СУММ и СМЕЩ, которые позволяют суммировать каждые n строк или столбцов в ограниченном диапазоне Excel.

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

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

🤖KUTOOLS AI Помощник: Революционизируйте Анализ данных на основе:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и создания диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты  |  Удалить пустые строки  |  Объединить столбцы или ячеек без потери данных  |  Округление без использования формул…
Расширенный 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.