Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Нарастающим итогом уникальных больше нуля + навороты
 
#1
02.02.2011 14:02:02
В файле таблица и две формулы с решениями, которые считают следующее: нарастающим итогом уникальных "поставщиков", для которых отгрузка больше нуля.  
 
Хочется "наворотить" формулу.  
Чтобы она считала то же самое, но + два параметра: "регион" и "дата с по"  
 
Табличка в диапазоне C43:E46. Заполняются ячейки: D45:E46.  
 
На пересечении должно быть к-во уникальных поставщиков по выбранному региону, которые отгрузились больше нуля за период указанный выше (например с 13.01.2011 по 17.01.2011).  
 
Реально, вообще, такое формулами?...  
:)
 
 
 
#2
02.02.2011 15:03:59
с ПОИСКПОЗ по началу и окончанию периода:  
ИНДЕКС(верх строка раб части таблицы;номер поз начала):ИНДЕКС(нижн строка раб части таблицы;номер поз окончания)  
и доп условие столбца поставщиков на соответствие эталону
 
 
 
#3
02.02.2011 15:15:08
как то так:  
 
=СУММПРОИЗВ(($E$4:$W$4>=D$43)*($E$4:$W$4<=D$44)*($C$5:$C$39=$C45)*($E$5:$W$39>0))
Редко но метко ...
 
 
 
#4
02.02.2011 15:25:24
{quote}{login=GIG_ant}{date=02.02.2011 03:15}{thema=}{post}как то так:  
 
=СУММПРОИЗВ(($E$4:$W$4>=D$43)*($E$4:$W$4<=D$44)*($C$5:$C$39=$C45)*($E$5:$W$39>0)){/post}{/quote}  
 
если именно так, как написано, то тут не учитывается момент "нарастающим итогом" уникальных поставщиков...
 
 
 
#5
02.02.2011 15:30:29
укажите в примере где неуникальные поставщики, и какой результат должен получится.
Редко но метко ...
 
 
 
#6
02.02.2011 15:52:30
в файле специально оставлены формулы в первых двух строках. Они считают одно и то же.  
Суть простая. Для первой даты это обычная СЧЕТЕСЛИ(диапазон;">0"), для второй даты - то же самое, но в чет не берутся те, кто кто уже попал в первую дату, для третьей даты те, кто брал в третью дату (к-во поставщиков) и накопленный результат первой и второй даты, ну и т.д....
 
 
 
#7
02.02.2011 16:54:34
пока формулами ничего толкового придумать не могу, может макрос ?
Редко но метко ...
 
 
 
#8
03.02.2011 01:00:17
Определение начальной строки и ее длины (D45):  
СМЕЩ(D4;ПОИСКПОЗ(C45;C5:C39;0)-1;ПОИСКПОЗ(D43;E4:W4;0);1;D44-D43+1)  
Немного изменить готовую формулу:  
=СУММПРОИЗВ(Ч(СЧЁТЕСЛИ(СМЕЩ(СМЕЩ(...);СТРОКА(ДВССЫЛ("1:"&СЧЁТЕСЛИ(C5:C39;C45))););">0")>0))
 
 
 
#9
03.02.2011 01:11:11
Выбросить первую СМЕЩ:  
=СУММПРОИЗВ(Ч(СЧЁТЕСЛИ(СМЕЩ($D$4;ПОИСКПОЗ($C45;$C$5:$C$39;0)+СТРОКА(ДВССЫЛ("1:"&СЧЁТЕСЛИ($C$5:$C$39;$C45)))-2;ПОИСКПОЗ(D$43;$E$4:$W$4;0);1;D$44-D$43+1);">0")>0))
 
 
 
#10
04.02.2011 10:14:05
Спасибо большое за участие в теме. Прекрасно понимаю, что тут такие вопросы задаются не часто (скажем так: не очень легкие)...  
 
Вот небольшой комментарий.  
 
Последняя из предложенных формул очень похожа "на правду"..  
но для    
13.01.2011  
17.01.2011  
и  
регион1  
 
она возвращает 5, что неверно...  
 
Проверяется так: ставится фильтр на столбец "РЕГИОН" - "регион1" после этого смотрится глазами. В подсчет согласно всем условиям должны были пойти ячейки: F6, F7, F9 и J8, т.е. формула должна была вернуть 4, а не 5...
 
 
 
#11
04.02.2011 13:44:07
Косячок. Замените "-2" на "-1", иначе в расчет берется строка 4.
 
 
 
#12
04.02.2011 15:08:56
Огромное спасибо!!!  
Только можно пару комментов, ну хотя бы почему -1 правильно, -2 неправильно, что это вообще за параметр и в каких случаях его нужно будет измени(я)ть?
 
 
 
#13
04.02.2011 15:11:01
Можно так (формула массива):  
=СУММ(--(МУМНОЖ(($C$5:$C$39=$C45)*(ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$43;$E$4:$W$4;0)):ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$44;$E$4:$W$4;0)));СТРОКА($1:$5)^0)>0))
 
 
 
#14
04.02.2011 15:13:34
{quote}{login=}{date=04.02.2011 03:11}{thema=}{post}Можно так (формула массива):  
=СУММ(--(МУМНОЖ(($C$5:$C$39=$C45)*(ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$43;$E$4:$W$4;0)):ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$44;$E$4:$W$4;0)));СТРОКА($1:$5)^0)>0)){/post}{/quote}  
Вернее так:  
=СУММ(--(МУМНОЖ(($C$5:$C$39=$C45)*(ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$43;$E$4:$W$4;0)):ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$44;$E$4:$W$4;0)));СТРОКА(A$1:ИНДЕКС(A:A;D$44-D$43+1))^0)>0))
 
 
 
#15
04.02.2011 15:36:21
Так это нужно препарировать всю формулу :)  
 
Работа функции СМЕЩ().  
Задаем начало, ширину и длину диапазона:  
СМЕЩ($D$4;...;ПОИСКПОЗ(D$43;$E$4:$W$4;0);1;D$44-D$43+1)  
Начало - смещение D4 по столбцам на ПОИСКПОЗ(нач_дата;$E$4:$W$4;0), ширина - одна строка, длина - (разница_дат+1).  
 
Количество строк смещения:  
СМЕЩ($D$4;ПОИСКПОЗ(...)+СТРОКА(1:количество_нужных_строк)-1;...;...;...)  
ПОИСКПОЗ() задает смещение по строкам до начала диапазона.  
 
Без функции СТРОКА() функция СМЕЩ() формирует статическую ссылку на диапазон ячеек выбранных столбцов одной строки - первой, где встречается искомый регион.  
СТРОКА() делает ссылку динамической, смещая найденную строку (вернее, ссылку на строку) поочередно вниз до конца диапазона, в котором встречается искомый регион.    
Чтобы не было начального смещения найденной строки, отнимается единица:  
СТРОКА(1:1)-1=0
 
 
 
#16
04.02.2011 17:58:19
{quote}{login=DV}{date=04.02.2011 03:13}{thema=Re: }{post}{quote}{login=}{date=04.02.2011 03:11}{thema=}{post}Можно так (формула массива):  
=СУММ(--(МУМНОЖ(($C$5:$C$39=$C45)*(ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$43;$E$4:$W$4;0)):ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$44;$E$4:$W$4;0)));СТРОКА($1:$5)^0)>0)){/post}{/quote}  
Вернее так:  
=СУММ(--(МУМНОЖ(($C$5:$C$39=$C45)*(ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$43;$E$4:$W$4;0)):ИНДЕКС($E$5:$W$39;0;ПОИСКПОЗ(D$44;$E$4:$W$4;0)));СТРОКА(A$1:ИНДЕКС(A:A;D$44-D$43+1))^0)>0)){/post}{/quote}  
 
ну и ну, спасибо! буду думать! )
 
 
 
#17
04.02.2011 17:59:45
{quote}{login=vikttur}{date=04.02.2011 03:36}{thema=}{post}Так это нужно препарировать всю формулу :)  
...  
 
{/post}{/quote}  
 
Спасибо!  
Позволю себе процитировать небезывестную молодежную "группировку"; автор пиши еще!    
:)
 
 
 
Страницы: 1
Читают тему
Наверх