Страницы: 1
RSS
Подсчёт количества значений не учитывая повторы
 
Добрый день. Помогите с формулой.
нужно посчитать кол-во офисов Банка с продажами по условиям: 1) по банку; 2) по диапазону дат; 3) по статусу договора; 4) не считать повторы.
Мне подсказали формулу массива, но она почему-то не срабатывает в моём случае.
пример прилагаю.
 
dioniska169, так пойдет?
Код
=SUMPRODUCT(1/COUNTIF(D8:D22;D8:D22)*(A8:A22="Действующий"))
 
Код
=СУММПРОИЗВ((A8:A22=A8)/СЧЁТЕСЛИМН(A8:A22;A8:A22;D8:D22;D8:D22))
 
Stics,не совсем так. Нужно чтобы учитывался конкретный Банк. т.е. формула должна учитывать условие Банк. у меня задача посчитать офисы по Банкам.
 
iMrTidy,необходимо учитывать Банк и диапазон дат (в примере выделен жёлтым). в отчёте могут быть Банк1 и Банк 2 и Банк 3 и их офисы с продажами в течении года. Нужно понять сколько офисов (без дублей) в каждом банке и за определённый период имеют продажи.  
 
dioniska169, я же Вам дал пример, по котрому легко сделать то, что Вам нужно.
Код
=SUMPRODUCT(1/COUNTIF(D8:D22;D8:D22)*(A8:A22="Действующий")*(C8:C22=H8)*(B8:B22>=A5)*(B8:B22<=B5))
 
iMrTidy,не получается.. могут ли быть проблемы если тянуть данные из таблицы?
 
dioniska169, могут, например, таблица может менять свой размер. Дата может восприниматься не как дата и т.д.
 
iMrTidy, формула в принципе считает, но считает сильно не правильно, поэтому и спросил. Вообще я использую эту же таблицу для други целей (подсчёт суммы сборов с учётом статуса и по банкам), поэтому не могу понять что сейчас не так...это же массив, верно?  
 
dioniska169, похоже это я поторопился.
Код
=SUMPRODUCT(--(FREQUENCY(MATCH(D8:D22;D8:D22;0)*(A8:A22="Действующий")*(C8:C22=H8)*(B8:B22>=A5)*(B8:B22<=B5);ROW(D8:D23)-ROW(B8))>0))-1
 
iMrTidy,если поменять статус у офиса 3 на "действующий", то ничего не происходит.. показывает 4 офиса, а их на самом деле 5...  
 
dioniska169, если ни одно значение не будет отфильтровано, то такая ситуация возможна. Тогда можно так:
Код
=SUMPRODUCT(--(FREQUENCY(MATCH(D7:D22;D7:D22;0)*(A7:A22="Действующий")*(C7:C22=H8)*(B7:B22>=A5)*(B7:B22<=B5);ROW(D7:D22)-ROW(D8))>0))-1
Изменено: iMrTidy - 28.03.2019 17:45:55
 
iMrTidy, это гениально! всё получилось! маленький вопрос: пробовал поставить условие не равно (<>) и не получилось. как прописать вместо ="действующий", <>"расторгнут" и <>"расторгнут в по"?
 
dioniska169,
Код
=SUMPRODUCT(--(FREQUENCY(MATCH(D7:D22;D7:D22;0)*(A7:A22<>"расторгнут")*(A7:A22<>"расторгнут в по")*(C7:C22=H8)*(B7:B22>=A5)*(B7:B22<=B5);ROW(D7:D22)-ROW(D8))>0))-1
 
dioniska169, может возникнуть ситуация, когда придется осуществлять поиск по ключевым словам, тогда можно так:
Код
=SUMPRODUCT(--(FREQUENCY(MATCH(D7:D22;D7:D22;0)*(ISERROR(SEARCH("расторгнут";A7:A22)))*(C7:C22=H8)*(B7:B22>=A5)*(B7:B22<=B5);ROW(D7:D22)-ROW(D8))>0))-1
 
iMrTidy,спасибо огромное! возьму на вооружение.
 
dioniska169,
Предлагаю так
=СЧЁТ(1/ЧАСТОТА(СЧЁТЕСЛИ(D8:D22;">="&D8:D22)*(C8:C22=H8)*(A8:A22=A8)*(A5<=B8:B22)*(B5>=B8:B22);СЧЁТЕСЛИ(D8:D22;">="&D8:D22)))
 
Чуть сократим
=СЧЁТ(1/ЧАСТОТА(СЧЁТЕСЛИМН(D:D;">="&D8:D22;A:A;"*действ*";B:B;">="&A5;B:B;"<="&B5;C:C;H12);СТРОКА(A8:A22)-СТРОКА(A8)+1))

Можно и так ещё
=СУММПРОИЗВ(Ч(ЕСЛИ(ЧАСТОТА(СЧЁТЕСЛИМН(D:D;">="&D8:D22;A:A;"*действ*";B:B;">="&A5;B:B;"<="&B5;C:C;H12);СТРОКА(D8:D22)-СТРОКА(D8)+1);B8:B22)<>(1=2)))
Коварство ЧАСТОТЫ заключается в том, что  если все "договоры расторгнуты", то косячок в вычислениях появится.
Изменено: Akropochev - 29.03.2019 22:37:40
Страницы: 1
Наверх