Суммирование каждой 2-й, 3-й... N-й ячейки
Постановка задачи
Предположим, что у нас имеется вот такая таблица с данными по выручке и выполнению плана для нескольких городов:
Необходимо просуммировать выручку по всем городам или, что примерно то же самое, подсчитать средний процент выполнения плана по всем городам.
Если решать проблему "в лоб", то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:
Если количество городов в таблице больше пары десятков, то такой способ начинает нагонять тоску, да и ошибиться при вводе формулы можно запросто. Как же поступить? Есть способы сделать это изящнее.
Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию
Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF):
Первый аргумент этой функции - диапазон проверяемых ячеек, второй - критерий отбора (слово "Выручка"), третий - диапазон ячеек с числами для суммирования.
Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF), которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.
Способ 2. Формула массива для суммирования каждой 2-й, 3-й ... N-й строки
Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то "Выручка", а где-то "Revenue" и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:
Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:
- Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
- Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
- Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
- И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
- Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.
Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:
А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:
И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:
Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.
Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.
Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:
Способ 3. Функция БДСУММ и таблица с условием
Формулы массива из предыдущего способа - штука красивая, но имеют слабое место - быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой - функцией БДСУММ (DSUM). Перед использованием эта функция требует небольшой доработки, а именно - создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) - не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM):
Способ 4. Суммирование каждой 2-й, 3-й... N-й строки
Если нужно просуммировать не отдельные ячейки, а целые строки, то можно это легко реализовать формулой массива, аналогичной способу 2:
Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.
+ в описании надо поправить формулу массива Ctrl+Alt+Enter на Ctrl+Shift+Enter
«2» выбрано по той причине, что продаж менее чем на 2 рубля с вероятностью 99,(9) % не будет, а план, вероятнее всего, не будет выполнен на 200 %. Если всё же план и будет перевыполнен более чем в два раза, то в этом случае нужно увольнять специалиста по планированию.
Другое дело, если в таблице данные о выручке не в руб., а в тыс. руб. (млн руб. и т. д.), или какие-либо показатели, которые принимают значения менее 2. В этом случае уже нужно применять что-то «посложнее».
Замените мысленно проценты на даты или количество товара - и простых решений уже не будет.
Цель статьи - не решить эту конкретную задачу, а научить читателя решать подобные задачи в будущем самостоятельно, т.е. продемонстрировать универсальный подход
Прежде всего, огромное спасибо за Ваш сайт. Невероятно полезный ресурс.
И хочу присоединиться к вопросу Gibbet. Подобное суммирование по столбцам возможно?
Спасибо
выводит ошибку, потому и вопрос
в любом случае Николай вопросы видел.
Видимо со столбцами не так всё просто.
Подскажите пожалуйста каким образом настроить суммирование каждой n-й ячейки начиная с определенного места в таблице и изменение результата вычисления формулы при добавлении данных в каждую n-ую ячейку?
Заранее спасибо.
=СУММ(МУМНОЖ((СТРОКА(СМЕЩ($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, но про матрицы ничего нет, думаю вы исправите это упущение.
Удачи вам.
Когда мне в таблице где много сток и много колонок, а просчитать каждую вторую строчку и каждую колонку, я делаю так: Перед таблицей я ввожу ещё одну колонку где каждую первую строку обозначаю буквой А, каждую вторую В (в английской раскладке),выделяю ячейку в той строке куда надо вставить данные суммирования вводим = суммесли ,открывается окно в первой строке вводим $A10:$A100 во второй критерий - это будет буква A или B в зависимости какую строчку надо просчитать, а в третьей строке B10:B100 нажимаем ок получаем результат, далее выделяем эту ячейку и за крестик протягиваем по всей строке получаем результат во всех ячейках. Николай Павлов правильно говорит "Когда знаешь - всё просто"
=СУММ(F3:ИНДЕКС($F$3:$I$3;;$D$3))
где F3 начала строки суммирования, ИНДЕКС($F$3:$I$3;;$D$3) определяет размер суммирования, а $D$3 количество ячеек.