Сложение в 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)))

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