Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
СУММЕСЛИ с 2я диапазонами, множеством критериев в одну формулу
 
#1
02.03.2010 02:21:45
Есть две формулы  
СУММЕСЛИ($D$13:$AI$13;ИСТИНА;D18:AI18) и СУММЕСЛИ($D$14:$AI$14;ИСТИНА;D18:AI18)с разными диапазонами, но одними критериями и дапазонами суммирования.  
 
Нужно как то вывести одну формулу, чтобы при поиске ИСТИНА в $D$13:$AI$13, найденые значения 7 и 15(еще 2а критерия) в D18:AI18 просто суммировались, если не найдены, то ничего.  
А при поиске ИСТИНА в $D$14:$AI$14, всё в томже диапазоне суммирования(D18:AI18) находил только цифру 11(еще один критерий), и если найдена, то суммировать нужно 5(а не 11), если не найдена, то ничего.
 
 
 
#2
02.03.2010 07:13:01
Формула не макрос, не надо быть программистом - неужели никаких мыслей. Знатоки, Хелпас Ми!
 
 
 
#3
02.03.2010 09:29:37
{quote}{login=АлексейМ.}{date=02.03.2010 07:13}{thema=}{post}Формула не макрос, не надо быть программистом{/post}{/quote}  
Естественно, но чтобы построить формулу, нужно разобраться в задаче, которую Вы выложили, как головоломку. И еще. Вам с работы звонят среди ночи? Вы думаете, что тут с 2 до 7 утра только и ждут, когда задачу подкинут? :)  
Формулы массива, вводятся Ctrl+Shift+Enter до появления фигурных скобок. Если не понятно - поиск по форуму "формулы массива":  
=СУММ((D13:AI13)*((D18:AI18=7)+(D18:AI18=15))*D18:AI18)  
=ЕСЛИ(СЧЁТЕСЛИ(D18:AI18;11);СУММ((D14:AI14)*(D18:AI18=5)*D18:AI18);0)  
Мог не так понять задание. Проверяйте.
 
 
 
#4
02.03.2010 10:12:21
{quote}{login=АлексейМ.}{date=02.03.2010 07:13}{thema=}{post} неужели никаких мыслей. {/post}{/quote}  
АлексейМ, извините, что мы ночью спали. Впредь будем караулить Ваши вопросы.
 
 
 
#5
02.03.2010 10:56:02
{quote}{login=vikttur}{date=02.03.2010 09:29}{thema=Re: }{post}{quote}Как головоломку. И еще. Вам с работы звонят среди ночи? Вы думаете, что тут с 2 до 7 утра только и ждут, когда задачу подкинут? :)  
{/post}{/quote}  
 
На счет "среди ночи" - звонят, блин!:(  
На счет с 2 до 7 - эт я правда перегнул, простите меня дуру грешную:D  
 
А на счет головоломки, Вы правы - та ещё "математика"! Постораюсь по другому.  
 
Если б Вы скачали файл, то из него было б видно, что смысл формул СУММЕСЛИ($D$13:$AI$13;ИСТИНА;D18:AI18) и СУММЕСЛИ($D$14:$AI$14;ИСТИНА;D18:AI18) в поиске ИСТИНЫ в Х$X$ диапазонах. И после нахождения токовой в любой ячейке, формула опускается вниз по столбцу(по столбцу, в ячейке которого наидена ИСТИНА) и просматривает ячейку нижнего диапазона D18:AI18. Если в ней цифра, то она(формула) суммирует её.  
И вот тут у меня проблема, а именно, с назначением критериев для обоих формул и слитием их в одну!!  
А конкретнее: формума с $D$13:$AI$13 должна понимать в нижнем диапозоне только цифры 7 и 15(и наёдя их, суммировать), а формула с $D$14:$AI$14 всё в том же диапазоне, должна понимать только 11, но найдя суммировать к 7 или 15 не 11, а 5 (т.е. находит 11 и превращая её в 5, суммирует).  
УФ! кажется, усё:о...!  
 
Я не думаю, что нужны формулы диапазонов, ведь в качестве диапазонов выступают только строки.
 
 
 
#6
02.03.2010 11:05:40
Ваш файл качал еще утром, без него по одному объяснению решить невозможно :)  
Вставил предложенную формулу в В11 (выделено красным). В АО17(18) объединенные ячейки, в таких формулы массива не работают. И вообще - убирайте из файла эти объединения - Вам красивее, программе хуже.  
Ваши проверки на ИСТИНА/ЛОЖЬ.  
Домножьте на единицу, удобнее будет работать, в глазах меньше рябит :)  
Например:  
=НЕ(ЕНД(ВПР(E10;ГОСПраздн;1;0)))*1
 
 
 
#7
02.03.2010 11:34:35
"убирайте из файла эти объединения" - я бы с радостью, да только и нам не красивее, и форма дока утверждена.  
 
"Домножьте на единицу" - согласен симпатично-удобнее:)  
 
Пока логики формулы никак не пойму, но это дело времени. Между тем ответ формула дала не правильный - 15. Должно получиться 20.  
 
ИСТИН, в диапазонах D13 и D14, две. 7го и 8го числа. Т.о. 15+11(а 11 должно пониматься как 5) = 20.
 
 
 
#8
02.03.2010 11:37:46
"находил только цифру 11(еще один критерий), и если найдена, то суммировать нужно 5(а не 11)" - а это я писал? Как можно догадаться, что 11 - это 5?
 
 
 
#9
02.03.2010 11:44:48
"Как можно догадаться, что 11 - это 5?"- всмысле кому догадаться?, если Ексель, он может догадаться через ЕСЛИ.  
В моём файле, он догадывается, но беда в том, что за 5 он принимает любую цифру.
 
 
 
#10
02.03.2010 11:50:37
Я вот сейчас это писал и, вдруг, подумал: а ведь достаточно добавить критерий с 11 во вторую часть моей формулы. Только как?  
=СУММЕСЛИ($D$13:$AI$13;ИСТИНА;D18:AI18)+[2Я ЧАСТЬ]ЕСЛИ(СУММЕСЛИ($D$14:$AI$14;ИСТИНА;D18:AI18);5;0)
 
 
 
#11
02.03.2010 11:56:10
Да не Excel'ю, а нам на форуме :)  
А что делать с "истинной" пятеркой? Или ее не может быть в строке? И вообще - что значат эти числа?  
 
Если без объединения не обойтись, то это проблема. Формула массива там работать не будет. Excel хоть и мощная штуковина, но тоже имеет свои ограничения. Объясните это начальству, иначе вручную считать будете :)  
Или придется использовать дополнительные ячейки для упрощения формулы.
 
 
 
#12
02.03.2010 12:07:00
"Или ее не может быть в строке?" - НЕ МОЖЕТ! В строках присутствуют только 7, 15, 11. Всё это часы работы, зависящие от места работы и празднчных дней.  
5 - это 5 ночных предпраздничных часов, от 11 суточных часов. Дурь короче!:D  
 
Можно ли хотябы во 2ю часть моего балета:) вставить критерий 11 (+[2Я ЧАСТЬ]ЕСЛИ(СУММЕСЛИ($D$14:$AI$14;ИСТИНА;D18:AI18);5;0)), т.е. чтобы понимал только 11 и принимал её за 5.
 
 
 
#13
02.03.2010 12:12:30
Подправьте мою исходную формулу:  
=СУММ((D13:AI13)*((D18:AI18=7)+(D18:AI18=15))*D18:AI18)+СУММ((D14:AI14)*ЕСЛИ(D18:AI18=11;5;0))
 
 
 
#14
02.03.2010 12:22:41
#знач! - неважно, подставить как втурую часть, или полностью формулу.  
 
И я вот что не пойму: D13:AI13)*((D18..... или ...AI14)*ЕСЛИ(D18..., а где критерий отбора по ИСТИНЕ, т.е. как формула узнает по какому столбику ей спускаться до диапазона суммирования.
 
 
 
#15
02.03.2010 12:30:53
...+ЕСЛИ(СУММ(($D$14:$AI$14=1)*(D18:AI18=11));5;0) - #ЗНАЧ! Но ведь по идее правильно, должно же работать. Формат ячейкит "общий".
 
 
 
#16
02.03.2010 12:31:57
Так короче:  
=СУММ((D13:AI13)*((D18:AI18=7)+(D18:AI18=15))*D18:AI18;(D14:AI14)*ЕСЛИ(D18:AI18=11;5;0))  
Критерии истинности - (D13:AI13) или (D14:AI14) - условия умножаются на "0" или"1" в зависимости от ЛОЖЬ/ИСТИНА в D13:AI14
 
 
 
#17
02.03.2010 12:33:18
{quote}{login=}{date=02.03.2010 12:30}{thema=}{post}...+ЕСЛИ(СУММ(($D$14:$AI$14=1)*(D18:AI18=11));5;0) - #ЗНАЧ! Но ведь по идее правильно, должно же работать. Формат ячейкит "общий".{/post}{/quote}  
А то, что Вам писали по поводу формул массива с вводом "тремя пальцами" - не читали?
 
 
 
#18
02.03.2010 12:44:06
"поводу формул массива" - ну как же Вы их любитие. Искал, читал, но в основном то, что я уже знаю. В своё время сайт Майки, до дыр зоглядел:)  
 
До "ТРЁХПАЛЫХ" не добрался. А чёйта?:) Ужасно сложно?
 
 
 
#19
02.03.2010 12:47:50
ЕСЛИ(СУММ(($D$14:$AI$14=1)*(D18:AI18=11));5;0)    
И запись у Вас неправильна - СУММ и ЕСЛИ поменять местами.  
СУММ(($D$14:$AI$14=1)*(D18:AI18=11)) - покажет количество совпадений ИСТИНА и "11".    
При СУММ=0 ЕСЛИ покажет ноль, при СУММ>0 результат ЕСЛИ - "5".
 
 
 
#20
02.03.2010 12:54:51
{quote}{login=АлексейМ.}{date=02.03.2010 12:44}{thema=Re: vikttur}{post}Искал, читал, но в основном то, что я уже знаю. До "ТРЁХПАЛЫХ" не добрался.{/post}{/quote}  
Если Вы не знаете о "ТРЁХПАЛЫХ", то вы  о формулах массива ничего не знаете :)  
Когда формула написана, стать указателем в строку формул и одновременно нажать три упомянутые выше клавиши - формула должна заключиться в фигурные скобки. Вот тогда она станет полноправной и рабочей :)  
По поводу любви к формулам массива - без применения дополнительных расчетов одной формулой Вашу "Дурь короче!:D" не сосчитать. А Вы ведь сами просили все в одной формуле.
 
 
 
#21
02.03.2010 12:58:49
"И запись у Вас неправильна - СУММ и ЕСЛИ поменять местами" - ошибка в формуле!  
У Вас это получилось? ЕСЛИ да прикрепите?
 
 
 
#22
02.03.2010 13:03:44
Еще раз - эта формула, введенная как формула массива, дает результат "20":  
=СУММ((D13:AI13)*((D18:AI18=7)+(D18:AI18=15))*D18:AI18)+СУММ((D14:AI14)*ЕСЛИ(D18:AI18=11;5;0))  
КАК ФОРМУЛА МАССИВА - ТРИ КЛАВИШИ.  
Больше ничем помочь не могу.
 
 
 
#23
02.03.2010 13:04:33
"...формула должна заключиться в фигурные скобки" - ну я же скзал, "в основном то, что я уже знаю".  
 
"просили все в одной формуле" - да, да, просил, в одной пожалуйста. И чё возбуждаться - надо, так надо, хоть 5ю пальцами:)
 
 
 
#24
02.03.2010 13:08:04
Большое Вам человеческое "от всей души", за проявленную стойкость, понимание, и работу конечно же!!!!
 
 
 
#25
02.03.2010 13:20:03
Все работает. Круто. Но, мне повезло еще больше:  
В объединённых ячейках формулы массивов не работают, но ведь их можно объединить после ввода формулы, а потом сделать автозаполнение. Что я и сделал!  
 
Круто:|  
Ище раз "от всей души"!
 
 
 
Страницы: 1
Читают тему
Наверх