Note: The other languages of the website are Google-translated. Back to English
English English

Суммируйте каждую 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 for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места