Предположим в ячейках A1:A14 у нас находятся некоторые числа и мы хотим просуммировать, например, только нечетные строки. Тогда проще всего воспользоваться следующей формулой:
=СУММПРОИЗВ(A1:A14;ОСТАТ(СТРОКА(A1:A14);2))
а для суммирования только четных такой:
=СУММПРОИЗВ(A1:A14;ОСТАТ(СТРОКА(A1:A14)+1;2))
Но чтобы просуммировать, например, каждую пятую строку нашу формулу придется усложнить.
В нашем случае, используя формулу ОСТАТ(СТРОКА(A1:A14);5) получим такой массив:
1, 2, 3, 4, 0, 1, 2, 3, 4, 0, 1, 2, 3, 4
Использование формулы НЕ(ОСТАТ(СТРОКА(A1:A14);5) )*1 поможет получить такой массив:
0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0
В итоге окончательная формула для подсчета каждого пятого элемента выглядит так:
=СУММПРОИЗВ( A1:A14;НЕ(ОСТАТ(СТРОКА(A1:A14);5) )*1)
Эта формула посчитает сумму строк, кратных 5.
Но формула зависит от номера строки листа, т.е. если мы вставим строку перед нашим массивом, результат изменится. Чтобы этого избежать воспользуемся такой формулой:
=СУММПРОИЗВ(A1:A14;НЕ(ОСТАТ(СТРОКА(ДВССЫЛ("1:" & ЧСТРОК(A1:A14)));5))*1)
Эта формула уже не зависит от номеров строк листа Excel, в которых расположены элементы.
=СУММПРОИЗВ(A1:A14;ОСТАТ(СТРОКА(A1:A14);2))
а для суммирования только четных такой:
=СУММПРОИЗВ(A1:A14;ОСТАТ(СТРОКА(A1:A14)+1;2))
Но чтобы просуммировать, например, каждую пятую строку нашу формулу придется усложнить.
В нашем случае, используя формулу ОСТАТ(СТРОКА(A1:A14);5) получим такой массив:
1, 2, 3, 4, 0, 1, 2, 3, 4, 0, 1, 2, 3, 4
Использование формулы НЕ(ОСТАТ(СТРОКА(A1:A14);5) )*1 поможет получить такой массив:
0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0
В итоге окончательная формула для подсчета каждого пятого элемента выглядит так:
=СУММПРОИЗВ( A1:A14;НЕ(ОСТАТ(СТРОКА(A1:A14);5) )*1)
Эта формула посчитает сумму строк, кратных 5.
Но формула зависит от номера строки листа, т.е. если мы вставим строку перед нашим массивом, результат изменится. Чтобы этого избежать воспользуемся такой формулой:
=СУММПРОИЗВ(A1:A14;НЕ(ОСТАТ(СТРОКА(ДВССЫЛ("1:" & ЧСТРОК(A1:A14)));5))*1)
Эта формула уже не зависит от номеров строк листа Excel, в которых расположены элементы.
Спасибо, полезно!
ОтветитьУдалитьА если строк не 14, а неизвестное количество (они могут удаляться и добавляться в таблицу со временем, как это отразить в формуле?
Alex, замени диапазон А1:А14 на свой.
ОтветитьУдалитьНиколай, классная штука. Спасибо!
Спасибо за материал!
ОтветитьУдалитьА как подсчитать количество строк, кратных 5, в которых значение равно "x" ?
Использовать массивы. Введите формулу
Удалить=СУММ((A1:A14="x")*НЕ(ОСТАТ(СТРОКА(A1:A14);5) ))
По завершении нажать CTRL + SHIFT + ENTER
У меня здесь "икс", т.е. англ расладка