Суммирование каждой 2-й, 3-й... N-й ячейки

110881 13.09.2014 Скачать пример

Постановка задачи

Предположим, что у нас имеется вот такая таблица с данными по выручке и выполнению плана для нескольких городов:

sum-every-N-row1.png

Необходимо просуммировать выручку по всем городам или, что примерно то же самое, подсчитать средний процент выполнения плана по всем городам.

Если решать проблему "в лоб", то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:

sum-every-N-row2.png

Если количество городов в таблице больше пары десятков, то такой способ начинает нагонять тоску, да и ошибиться при вводе формулы можно запросто. Как же поступить? Есть способы сделать это изящнее.

Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию

Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF):

sum-every-N-row3.png

Первый аргумент этой функции - диапазон проверяемых ячеек, второй - критерий отбора (слово "Выручка"), третий - диапазон ячеек с числами для суммирования.

Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF), которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.

Способ 2. Формула массива для суммирования каждой 2-й, 3-й ... N-й строки

Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то "Выручка", а где-то "Revenue" и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:

sum-every-N-row4.png

Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:

  • Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
  • Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
  • Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
  • И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
  • Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.

Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:

sum-every-N-row5.png

А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:

sum-every-N-row6.png

И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:

sum-every-N-row7.png

Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.

Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.

Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:

sum-every-N-row8.png

Способ 3. Функция БДСУММ и таблица с условием

Формулы массива из предыдущего способа - штука красивая, но имеют слабое место - быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой - функцией БДСУММ (DSUM). Перед использованием эта функция требует небольшой доработки, а именно - создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) - не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM):

sum-every-N-row10.png

Способ 4. Суммирование каждой 2-й, 3-й... N-й строки

Если нужно просуммировать не отдельные ячейки, а целые строки, то можно это легко реализовать формулой массива, аналогичной способу 2:

sum-every-N-row11.png

Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.

Ссылки по теме



15.09.2014 16:22:39
а как через БДСумм просуммировать аналогично значения по столбцам? Например если Москву, Питер, ... расположить в колонках, а не строках.

+ в описании надо поправить формулу массива Ctrl+Alt+Enter на Ctrl+Shift+Enter
16.09.2014 13:17:28
Николай, там на скриншотах ошибку исправьте пожалуйста. Я все понять не мог, почему в формуле условия для БДСУММ идет ссылка на пустую ячейку, на столбец C, а ведь должно на столбец данных - B.
19.09.2014 13:00:10
Пример выбран не очень удачно, т. к. можно было обойтись всего лишь такими формулами:

=СУММЕСЛИ(C:J;">2")
и
=СРЗНАЧЕСЛИ(C:J;"<2")

«2» выбрано по той причине, что продаж менее чем на 2 рубля с вероятностью 99,(9) % не будет, а план, вероятнее всего, не будет выполнен на 200 %. Если всё же план и будет перевыполнен более чем в два раза, то в этом случае нужно увольнять специалиста по планированию. :D

Другое дело, если в таблице данные о выручке не в руб., а в тыс. руб. (млн руб. и т. д.), или какие-либо показатели, которые принимают значения менее 2. В этом случае уже нужно применять что-то «посложнее».
19.09.2014 13:22:51
Не придирайтесь, Владимир! :)
Замените мысленно проценты на даты или количество товара - и простых решений уже не будет.
Цель статьи - не решить эту конкретную задачу, а научить читателя решать подобные задачи в будущем самостоятельно, т.е. продемонстрировать универсальный подход ;)
22.09.2014 07:01:34
Добрый день, Николай
Прежде всего, огромное спасибо за Ваш сайт. Невероятно полезный ресурс.
И хочу присоединиться к вопросу Gibbet. Подобное суммирование по столбцам возможно?

Спасибо
24.09.2014 18:33:47
Есть функция СТОЛБЕЦ(), аналог функции СТРОКА(). Поэкспериментируйте :)
25.09.2014 22:31:35
Давно.
выводит ошибку, потому и вопрос

в любом случае Николай вопросы видел.
Видимо со столбцами не так всё просто.
26.09.2014 07:09:14
Формула массива и СУММЕСЛИ по столбцам работают отлично, а вот БДСУММ умеет только со строками. Можно попробовать сначала транспонировать вашу таблицу с помощью функции ТРАНСП, но, боюсь, это убьет все выгоды по скорости по сравнению с другими способами.
28.10.2014 14:28:16
Добрый день, Николай.
Подскажите пожалуйста каким образом настроить суммирование каждой n-й ячейки начиная с определенного места в таблице и изменение результата вычисления формулы при добавлении данных в каждую n-ую ячейку?
Заранее спасибо.
17.05.2015 10:59:58
Николай, есть ещё матричная формула:
=СУММ(МУМНОЖ((СТРОКА(СМЕЩ($A$1;;;ЧСТРОК(C2:J15)/2))*2-1<ТРАНСП(СТРОКА(C2:J15)-0))*(СТРОКА(СМЕЩ($A$1;;;ЧСТРОК(C2:J15)/2))*2-1>=ТРАНСП(СТРОКА(C2:J15)-0)-1);C2:J15))
Вы скажете очень длинная формула, да для этого примера согласен.Но они не заменимы в построении графиков формулами из диспетчера формул.
Если в выделенный диапазон поставите формулу:
=(СТРОКА(СМЕЩ($A$1;;;ЧСТРОК(C2:J15)/2))*2-1<ТРАНСП(СТРОКА(C2:J15)-0))*(СТРОКА(СМЕЩ($A$1;;;ЧСТРОК(C2:J15)/2))*2-1>=ТРАНСП(СТРОКА(C2:J15)-0)-1)
то вы увидите матрицу. В этом случае она нечетная, т.е. выберет каждый нечетный ряд. Обратите внимание в формуле на -0 в двух местах,
если вы замените на 0 на 1, то получите четную матрицу. Можете ставить 2 и 3 и т.д. Диапазон лучше указывать динамический.
Снова выделяем диапазон под таблицей и вставляем  формулу:
=МУМНОЖ((СТРОКА(СМЕЩ($A$1;;;ЧСТРОК(C2:J15)/2))*2-1<ТРАНСП(СТРОКА(C2:J15)-0))*(СТРОКА(СМЕЩ($A$1;;;ЧСТРОК(C2:J15)/2))*2-1>=ТРАНСП(СТРОКА(C2:J15)-0)-1);C2:J15)
Получаем таблицу с нечетными строками.
Кто строит графики формулами, без построения дополнительных столбцов и таблиц , матрицы незаменимая вещь.
Николай у вас один из лучших сайтов по Excel, но про матрицы ничего нет, думаю вы исправите это упущение.
Удачи вам.
21.06.2015 14:16:38
Фёдор Романюк оставил комментарий на странице Как сложить каждую 2-ю, 3-ю ... n-ю я..

Когда мне в таблице где много сток и много колонок, а просчитать каждую вторую строчку и каждую колонку, я делаю так: Перед таблицей я ввожу ещё одну колонку где каждую первую строку обозначаю буквой А, каждую вторую В (в английской раскладке),выделяю ячейку в той строке куда надо вставить данные суммирования вводим = суммесли ,открывается окно в первой строке вводим $A10:$A100 во второй критерий - это будет буква A или B в зависимости какую строчку надо просчитать, а в третьей строке B10:B100 нажимаем ок получаем результат, далее выделяем эту ячейку и за крестик протягиваем по всей строке получаем результат во всех ячейках. Николай Павлов правильно говорит "Когда знаешь - всё просто"
30.03.2016 11:37:35
Отличный совет! Всё работает. Но у вас здесь маленькая опечатка: формула массива вводится сочетанием Ctrl + Shift + Enter, а не Ctrl+Alt+Enter.
Наверх