Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Суммирование по нескольким критериям прописанным на ячейке
 
#1
04.10.2021 13:05:31
Приветствую!

В файле схематично приложен пример. Суть вопроса следующая:
Использую сум + суммеслимн, чтобы посчитать сумму по каким-то критериям.
На текущий момент эти критерии забиты в формуле, можно ли каким-то образом переделать формулу, чтобы не нужно была забивать значения в саму формулу, а чтобы оно ссылалось на какую-то ячейку, где эти параметры уже забиты?
То есть если смотреть на пример, то вместо данных в фигурных скобках была бы ссылка на ячейку E3.

Заранее благодарю за помощь
Изменено: reactortyz - 04.10.2021 13:06:52
 
 
 
#2
04.10.2021 13:27:23
Можно, но каждое слово должно быть в отдельной ячейке столбца
Код
=СУММ(СУММЕСЛИМН($F$6:$F$9;$E$6:$E$9;ТРАНСП(D1:D4)))

или в отдельной ячейке строки

Код
=СУММ(СУММЕСЛИМН($F$6:$F$9;$E$6:$E$9;E1:I1))
 
 
 
#3
04.10.2021 16:28:02
mechanix 85, Спасибо!

Этот вариант я понимал, но он не подходит из-за структуры таблицы и отличающихся количеств критериев для разных строк.
Меня интересовала возможность реализовать именно так, как я спрашивал в тексте и примере
 
 
 
#4
04.10.2021 16:32:46
массивно
=SUM(IF(ISNUMBER(SEARCH(", "&E6:E9&",";", "&E3&","));F6:F9))
или просто
=SUMPRODUCT(ISNUMBER(SEARCH(", "&E6:E9&",";", "&E3&","))*F6:F9)
Изменено: БМВ - 04.10.2021 17:06:13
По вопросам из тем форума, личку не читаю.
 
 
 
#5
04.10.2021 16:46:50
Еще вариант, до 15 условий.
Код
=SUMPRODUCT(SUMIFS($F$6:$F$9;$E$6:$E$9;TRIM(MID(SUBSTITUTE(E$3;", ";REPT(" ";99));ROW($1:$15)*99-98;99))))
 
 
 
#6
04.10.2021 17:08:25
Казалось бы что SUMIFS должен был быть по скорости быстрее
но
=SUMPRODUCT(SUMIFS($F$6:$F$9,$E$6:$E$9,TRIM(MID(SUBSTITUTE(E$3,", ",REPT(" ",99)),ROW($1:$15)*99-98,99))))      484.375
=SUMPRODUCT(ISNUMBER(SEARCH(", "&E6:E9&",",", "&E3&","))*F6:F9)        402.3438
По вопросам из тем форума, личку не читаю.
 
 
 
#7
04.10.2021 18:07:12
Вроде бы то, что нужно)
БМВ, memo, спасибо большое за помощь!
 
 
 
#8
04.10.2021 18:43:27
По правде говоря, SUMIFS мне чисто по ощущениям никогда не казался быстрым на относительно больших таблицах от 1000 строк и выше. Завтра вроде релиз 2021-го офиса, интересно было бы проверить там.
Ну и напоследок еще вариант:
Код
=SUMPRODUCT(SUMIFS($F$6:$F$9;$E$6:$E$9;FILTERXML("<j><i>"&SUBSTITUTE($E$3;", ";"</i><i>")&"</i></j>";"//i")))
 
 
 
#9
04.10.2021 19:06:54
memo,  от 1000 даннвх или применений?
А последний вариант будет еще более медленным. Ибо тормзит xml  сильнее чем ...
По вопросам из тем форума, личку не читаю.
 
 
 
#10
04.10.2021 19:36:41
БМВ, Данных, с применениями я думаю тормозить будут все функции.
Последний вариант чисто для коллекции.
 
 
 
#11
04.10.2021 22:02:32
Цитата
memo написал:
Данных,
с десятками и сотнями тысяч SUMIF, COUNTIf.... справлябтся без особенных тормозов. но аот если десятки тысяч раз гонять их по сотням тысяч трок, то да, это тормоза.

ну тоже для коллекции замер
=SUMPRODUCT(SUMIFS($F$6:$F$9,$E$6:$E$9,FILTERXML("<j>"&SUBSTITUTE($E$3,", ","")&"</j>","//i")))    1882.813
=SUMPRODUCT(SUMIFS($F$6:$F$9,$E$6:$E$9,TRIM(MID(SUBSTITUTE(E$3,", ",REPT(" ",99)),ROW($1:$15)*99-98,99))))       515.625
=SUMPRODUCT(ISNUMBER(SEARCH(", "&E6:E9&",",", "&E3&","))*F6:F9)        421.875
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх