матрица условий для сумирования (формулы) как использовать знак "*" ?

[kievlyanin]

есть определенная матрица из 4-х условий при соблюдении которых формула выдает тот или иной результат:

=СУММПРОИЗВ(($C$1:$C$15=$K3)*($D$1: $D$15=$L3)*($E$1:$E$15=$M3)*($F$1:$ F$15=$N3)*$H$1:$H$15)


достаточно просто )

но например есть условие строка С и D не важно какое значение - я ставлю в этих строках "*" - формула не работает .. т.к. сравнение значения в к3 с каким то значением и столбце С со значением "*" дает ЛОЖЬ а надо ИСТИНА ...

как затавить сумпроизв воспринимать "*" ??

[Казанский]

Попробуйте этот фрагмент ($C$1:$C$15=$K3) заменить на

ЕСЛИ($C$1:$C$15=$K3;1;$C$1:$C$15="*")

Без файла-примера оптимизировать неохота.

[IgorGO]

оператор сравнения = сравнивает значения
оператор не использует подстановочные символы т.е. "все" = "*" - получите ЛОЖЬ

используйте функции, которые работают с подстановочными символами
например поиск("*";"что угодно") = 1
в Вашем случае

не(еош(поиск($K3;$C$1:$C$15)))

[kievlyanin]

ПОИСК() не работает т.к. сравнение надо обратное а не прямое .. т.е. в аргументе будет массив значений а в массиве - одно значение поскольку массив будет содержать "*" а аргумент всегда будет полным со всеми 4-мя условиями.


витиевато выразился )) т.е. если мы будем искать 1 _ 2_ 3 в базе где * _ 2 _ * то не найдет т.к. в условии четко определены 3 значения а если наоброт - то естественно найдем.

пример в файле:

[Казанский]

kievlyanin, вот ваши 3 и 10

[IgorGO]

в общем-то первая подсказка была правильной:

используйте функции, которые работают с подстановочными символами

=СУММПРОИЗВ(СЧЁТЕСЛИ(RC3;R3C3:R14C3)*СЧЁТЕСЛИ(RC4;R3C4:R14C4)*СЧЁТЕСЛИ(RC5;R3C5:R14C5)*СЧЁТЕСЛИ(RC6;R3C6:R14C6)*R3C7:R14C7)

[kievlyanin]

итого сделал так:

=СУММПРОИЗВ(МИН(ЕСЛИ((ЕСЛИ($C$3:$C$14=C27;1;$C$3:$C$14="*")*ЕСЛИ($D$3:$D$14=D27;1;$D$3:$D$14="*")*ЕСЛИ($E$3:$E$14=E27;1;$E$3:$E$14="*")*ЕСЛИ($F$3:$F$14=F27;1;$F$3:$F$14="*")
*$G$3:$G$14)>0;(ЕСЛИ($C$3:$C$14=C27;1;$C$3:$C$14="*")*ЕСЛИ($D$3:$D$14=D27;1;$D$3:$D$14="*")*ЕСЛИ($E$3:$E$14=E27;1;$E$3:$E$14="*")*ЕСЛИ($F$3:$F$14=F27;1;$F$3:$F$14="*")*$G$3:$G$14))))

на основе формулы Казанский поскольку получается что условие прямое и условвие с "*" будут идентичны для суммирования, то я сделал сумирование по минимальному результату - зная эту особенность я составлю матрицу условий суммирования по убыванию точности условия.

[IgorGO]

а то, что я написал тоже выдает 3 и 10
(переведите ексель в режим ссылок R1C1 и скопируйте в любую из ячеек, где она должна быть. формула раза в 3 короче от той, что решили использовать Вы)

[kievlyanin]

у вас тоже надо будет уходить от проблемы задвоения условий четкого и общего (в примере по четкому условию где дает результата 3 формула даст результата 7 т.к. суппроизв сложит совпадения четкого условия и совпадения условия с "*" а это 3 и 4)

уходя от этгог через МИН() то тоже длинно получится:

=СУММПРОИЗВ(МИН(ЕСЛИ((СЧЁТЕСЛИ($C35;$C$3:$C$14)*СЧЁТЕСЛИ($D35;$D$3:$D$14)*СЧЁТЕСЛИ($E35;$E$3:$E$14)*СЧЁТЕСЛИ($F35;$F$3:$F$14)*$G$3:$G$14)>0;
(СЧЁТЕСЛИ($C35;$C$3:$C$14)*СЧЁТЕСЛИ($D35;$D$3:$D$14)*СЧЁТЕСЛИ($E35;$E$3:$E$14)*СЧЁТЕСЛИ($F35;$F$3:$F$14)*$G$3:$G$14))))

хотя на пару десяток симовол короче.. на реальной базе просмотрю что будет быстрее работать.