Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
общая формула суммы по структуре
 
#1
29.07.2012 13:25:33
имеется: список-структура.  
в примере для наглядности строки сгруппированы, в реале - не будут  
структура описывается в доп.столбце - ручками ставится № уровня.  
нужна общая формула суммы для любого уровня - как суммы нижележащих строк с уровнем на 1 больше текущего, но с ограничением - суммировать строки до следующего уровня, равного текущему.  
мне кажется, что-то подобное уже было. возможно - не раз.  
но то ли поиск меня не понял, то ли у него настроения нет - короче, ничего путного за приемлемое время я не нашёл.  
 
со своими формульными "талантами" я нагородил бог весть что :(  
правда - рабочее, но уж больно некузявое.  
   
прошу уважаемых планетян-формулистов помочь мне либо подкорректировать мой вариант, либо предложить своё чудо-решение.  
чтобы не было скучно - бзики от начальства:  
- больше никаких доп.столбцов (хоть вручную. хоть формулами);  
- никаких "трёх пальцев"  
:)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#2
29.07.2012 13:27:40
пс. excel-2000, 2002, 2003
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#3
29.07.2012 13:42:13
А чего там корректировать? Мне нравится.  
Саша, в чем по твоему некузявость?
 
 
 
#4
29.07.2012 13:45:47
Разве что СУММПРОИЗВ() заменить на СУММЕСЛИ() - будет чуть быстрее.  
А с заданием диапазонов лучше не придумаешь...
 
 
 
#5
29.07.2012 13:51:00
ннууу...  
как-то длинно получилось.  
и эти вынужденные ЕНД() с двойнвм расчетом ПОИСКПОЗ()...  
увы, до ЕОШИБКА() майкрософт додумался лишь в 2007-м.  
 
Игорь, из спортивного интереса (плюс есть шанс убедить начальство):  
формулой массива было бы намного короче?  
тормоза не страшны - объемы не слишком большие, хотя...  
строк - около 200, ну и столбцы - по рабочим дням месяца плюс по декадам плюс "итого" - около 25.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#6
29.07.2012 14:06:02
{=СУММ((МИН(ЕСЛИ(A9:A$38=A8;СТРОКА(A9:A$38)))>=СТРОКА(A9:A$38))*(A9:A$38=A8+1)*B9:B$38)}  
Но я думаю что тут еще проверку на отсутствующие нужно делать...
 
 
 
#7
29.07.2012 14:06:57
вот вполне "кузявый", на мой взгляд, вариант с UDF  
но, видимо, некоторых людей макросами ещё в яслях напугали до икоты :(
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#8
29.07.2012 14:08:26
Формулу редактировал в ячейке B8
 
 
 
#9
29.07.2012 14:11:34
{quote}{login=kim}{date=29.07.2012 02:06}{thema=}{post}{=СУММ((МИН(ЕСЛИ(A9:A$38=A8;СТРОКА(A9:A$38)))>=СТРОКА(A9:A$38))*(A9:A$38=A8+1)*B9:B$38)}{/post}{/quote}  
а вот это уже красиво. спасибо.  
у меня что-то гораздо запутаннее получалось, да и то местами неверно считало :)  
файлик к предыдущему посту не прикрепился. но тогда уж - все три варианта:
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#10
29.07.2012 14:17:03
Для того чтобы не делать проверку, можно так.
 
 
 
#11
29.07.2012 14:39:17
вариант:  
=СУММЕСЛИ(A3:ИНДЕКС(A3:A$42;МАКС(МИН(ЕСЛИ(A3:A$42=A2;СТРОКА(A3:A$42)-СТРОКА()));1));A2+1;B3)
 
 
 
#12
29.07.2012 14:40:39
ага. распутался я со своим вариантом массива :)  
все варианты в одном флаконе (внимание - есть одна неверная сумма:)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#13
29.07.2012 14:41:04
прикольно, формулы совпали
 
 
 
#14
29.07.2012 14:42:53
Михаил :)  
а диапазон до 42-й строки откуда и зачем?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#15
29.07.2012 14:45:59
"а диапазон до 42-й строки откуда и зачем?"  
достаточно 39  
пока щелкал файлы, увидел что заполнено до 41, поэтому попровил формулу  
потом пригляделся, а у игоря аналогичная оказалась, так что зря выкладывал
 
 
 
#16
29.07.2012 14:48:18
МСН (коварным голосом), а не-массивно? будет чудо? :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#17
29.07.2012 14:57:45
Не массивно, но летуче :)  
=СУММЕСЛИ(A3:ИНДЕКС(A3:A$41;ПОИСКПОЗ(A2;A3:A$41;));A2+1;B3)
 
 
 
#18
29.07.2012 15:04:01
=СУММЕСЛИ(A3:ИНДЕКС(A3:A$42;СУММПРОИЗВ(ПОИСКПОЗ(A2;A3:A$42+(A3:A$42="")*A2;)));A2+1;B3)  
 
не нужно указывать пустые уровни в конце списка, достаточно указать на одну пустую ячейку ниже
 
 
 
#19
29.07.2012 15:06:16
немассивно, нелетуче:  
=СУММПРОИЗВ((A3:ИНДЕКС(A3:A$42;СУММПРОИЗВ(ПОИСКПОЗ(A2;A3:A$42+(A3:A$42="")*A2;)))=A2+1)*B3:ИНДЕКС(B3:B$42;СУММПРОИЗВ(ПОИСКПОЗ(A2;A3:A$42+(A3:A$42="")*A2;))))  
=СУММЕСЛИ(A3:ИНДЕКС(A3:A$42;СУММПРОИЗВ(ПОИСКПОЗ(A2;A3:A$42+(A3:A$42="")*A2;)));A2+1;B3:ИНДЕКС(B3:B$42;СУММПРОИЗВ(ПОИСКПОЗ(A2;A3:A$42+(A3:A$42="")*A2;))))
 
 
 
#20
29.07.2012 15:15:12
тэээк-с...  
чувствую одним местом - переутомился я :(  
пока не догоняю последние варианты.  
 
в любом случае - всем огромное спасибо.  
после перерыва продолжу насиловать мозг :)))
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#21
29.07.2012 15:36:18
Ладно, последний от меня вариант, без указания пустых и вспомагательных:  
=СУММЕСЛИ(A3:ИНДЕКС(A3:A$38;ПОИСКПОЗ(A2;A3:A$38;-1));A2+1;B3)
 
 
 
#22
29.07.2012 16:01:28
"ура! случилоссь чудо! друг спас друга!!!" (с)  
не зря я надеялся на простое, короткое и элегантное решение.  
правда, для этого пришлось помучить наших гуру, устроив небольшое соревнование :)  
 
СПАСИБО огромное!  
 
пс. в аттаче - вся история болезни.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
Страницы: 1
Наверх