Сумма первых n значений, удовлетворяющих условию, в MS Excel
В Интернете ничего не нашел по вопросу ниже, решил написать здесь.
Имеется таблица следующего вида:
Январь Февраль Март ...
План Факт План Факт План Факт ...
Показатель 1 10 23 14 15 20 25 ...
Показатель 2 15 24 14 17 21 17 ...
...
Необходимо в конце таблицы посчитать, к примеру, сумму показателей за первые шесть месяцев отдельно по факту и отельно по плану.
Если пользоваться формулой СУММЕСЛИ, то выйдет, что для каждой отдельной суммы в зависимости от количества месяцев, которые нужно учесть, необходимо задавать разные диапазоны условия и суммирования. Если же пользоваться СУММЕСЛИМН и учитывать месяцы, то формула выходит длинная и ее придется прописывать при добавлении каждого последующего месяца.
Есть ли возможность создать формулу без дополнительных ячеек, которая будет учитывать условие (план или факт), при этом принимая диапазоны значений и условия целого года, а также количество первых месяцев, необходимых для расчета?
В идеале на выходе иметь формулу типа =СУММАПЕРВЫХN(числ_строка(Янв-Дек);строка_условия(Янв-Дек);"План"(или "Факт");количество_мес_с_первого), но понимаю, что такого нет. Как это можно реализовать иначе?
Спасибо!
Ответы (2 шт):
Если я правильно понял, то такой вариант:
Если просто в конце таблицы 2 столбца, в которых считается отдельно план и факт, но при это можно вставлять или удалять месяцы ячейка
J3:=СУММЕСЛИ($B$2:ИНДЕКС($B$2:$BB$2;1;СТОЛБЕЦ()-1);"План";$B3:ИНДЕКС($B3:$BB3;1;СТОЛБЕЦ()-1))
K3 тоже самое только меняем План на Факт, ну и далее копируем вниз
Если надо именно считать за определенное количество месяцев
L3:=СУММЕСЛИ($B$2:ИНДЕКС($B$2:$BB$2;1;МИН(СТОЛБЕЦ()-1;2*$M$1));"План";$B3:ИНДЕКС($B3:$BB3;1;МИН(СТОЛБЕЦ()-1;2*$M$1)))
M3 так же меняем План на Факт
Небольшие пояснения:
если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес
т.е. для первого варианта $B$2:ИНДЕКС($B$2:$BB$2;1;СТОЛБЕЦ()-1) - вернет ссылку на диапазон от $B$2 до предыдущего столбца, где написана эта формула, т.е. $B2:I2
Во втором варианте функция МИН служит выбора минимального значения между количеством заполненных месяцев и требуемых в отчете, иначе будет циклическая ссылка, если заполнено 2 месяца а мы хотим сделать выборку за 3
В Z3, копировать на два столбца:
=СУММЕСЛИ($B$2:ИНДЕКС($B$2:$Y$2;$AA$1*2);Z$2;$B3:ИНДЕКС($B3:$Y3;$AA$1*2))
