Сложение в MS EXCEL каждого второго (третьего и т.п.) значения в списке

Все помнят, как утомительно писать в ручную формулу сложения каждого второго значения в списке: A1+A3+A5+… К счастью есть функция СУММПРОИЗВ().

Пусть в диапазоне B5:B13 имеется список значений, а в ячейке B4 - заголовок списка (см. файд примера).

Решение задачи сложения каждого второго значения в диапазоне основано на использовании функций ЕЧЕТН() и ЕНЕЧЕТ().

Для суммирования 1-го, 3, 5, 7 и 9 значения в диапазоне (списке) используйте формулу:
=СУММПРОИЗВ((B5:B13)*ЕНЕЧЁТ(СТРОКА(B5:B13)-СТРОКА($B$4)))

Разберем формулу:

  • часть формулы СТРОКА(B5:B13)-СТРОКА($B$4) формирует числовую последовательность {1:2:3:4:5:6:7:8:9}, в этом можно убедиться, выделив в Строке формул СТРОКА(B5:B13)-СТРОКА($B$4), и нажав клавишу F9;
  • функция ЕНЕЧЁТ() возвращает массив {ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА}, где значение ИСТИНА соответствует нечетному числу;
  • Функция СУММПРОИЗВ() избавляет от необходимости использовать формулу массива и складывает попарные произведения элементов двух массивов: значений из диапазона B5:B13 и полученного на предыдущем шаге массива {ИСТИНА: ЛОЖЬ: ИСТИНА:ЛОЖЬ: ИСТИНА:ЛОЖЬ: ИСТИНА:ЛОЖЬ:ИСТИНА};
  • при перемножении значений из ячеек диапазона B5:B13 на значения ИСТИНА и ЛОЖЬ, учитывается, что ИСТИНА соответствует 1, а ЛОЖЬ - 0, поэтому значения, находящиеся в четных позициях списка (2, 4, 6 и 8), при суммировании не учитываются.
  • в результате получим сумму значений, находящихся в позициях списка 1, 3, 5, 7 и 9, что и требовалось.

Для суммирования 2-го, 4, 6 и 8 значения в диапазоне (списке) используйте формулу:
=СУММПРОИЗВ((B5:B13)*ЕЧЁТН(СТРОКА(B5:B13)-СТРОКА($B$4)))

Решение задачи в более общем виде - сложение каждого третьего (четвертого, пятого, ...) значения основано на использовании функции ОСТАТ(). Используем тот факт, что для позиций значений, которые нацело делятся на 3 (каждый третий), остаток от деления равен 0.

Для суммирования 3, 6 и 9 значения (каждого третьего, начиная с третьего) используйте формулу:
=СУММПРОИЗВ((B5:B13)*НЕ(ОСТАТ(СТРОКА(B5:B13)-СТРОКА($B$4);3)))

Для суммирования 1, 4 и 7 значения (каждого третьего, начиная с первого) используйте формулу:
=СУММПРОИЗВ((B5:B13)*НЕ(ОСТАТ(СТРОКА(B5:B13)-СТРОКА($B$4)+2;3)))

Для суммирования каждого четвертого начиная с первого, т.е.1, 5 и 9 значения используйте формулу:
=СУММПРОИЗВ((B5:B13)*НЕ(ОСТАТ(СТРОКА(B5:B13)-СТРОКА($B$4)+3;4)))

СОВЕТ:
Частный случай - сложение значений в каждой третьей строке листа, приведен в соответствующей статье.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)

Комментарии

AleksSid28

Точно воспроизвел формулу, но результат =450

Creator

Перепроверил, все правильно, пишите на creator@excel2.ru Пришлю файл.

37Alexandra

Думаю, что для не профессиональных экслеистов следовало бы указать, что в ячейку D5 вставляем указанную ниже формулу.

Статья полезная. Такими формулами пользуюсь давно и успешно.

Яндекс.Метрика