четверг, 28 марта 2013 г.

Суммирование каждой n-й строки

Предположим в ячейках 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, в которых расположены элементы.



Похожие по тематике посты - еще почитать:

4 комментария:

  1. Спасибо, полезно!
    А если строк не 14, а неизвестное количество (они могут удаляться и добавляться в таблицу со временем, как это отразить в формуле?

    ОтветитьУдалить
  2. Alex, замени диапазон А1:А14 на свой.
    Николай, классная штука. Спасибо!

    ОтветитьУдалить
  3. Спасибо за материал!
    А как подсчитать количество строк, кратных 5, в которых значение равно "x" ?

    ОтветитьУдалить
    Ответы
    1. Использовать массивы. Введите формулу
      =СУММ((A1:A14="x")*НЕ(ОСТАТ(СТРОКА(A1:A14);5) ))
      По завершении нажать CTRL + SHIFT + ENTER
      У меня здесь "икс", т.е. англ расладка

      Удалить