Перейти к основному содержанию

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

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

Как суммировать каждую 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)

Заметки: Чтобы суммировать каждый 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:
> СТРОКА (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}.
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 и т. Д.
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};
> СУММПРОИЗВ ({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 MOD
Функция Excel MOD возвращает остаток от деления числа на делитель.

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

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

Родственные формулы

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

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

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

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

Лучшие инструменты для работы в офисе

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

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

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

Описание

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations