Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Формула подсчета для нескольких критерий - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула подсчета для нескольких критерий (Формулы/Formulas)
Формула подсчета для нескольких критерий
serewka Дата: Вторник, 03.09.2019, 12:10 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день, есть сложная задачка Прошу помощи создании формулы.

Задача: Из массива данных автоматически вычесть сколько нужно выдать сертификатов (подарков) клиенту за весь период участия в проекте. Массив данных выгружается с базы в формате ексель, где есть код клиента и все даты, когда клиент был в проекте.
Критерии выдачи сертификата клиенту: Сертификат выдается каждому клиенту за каждый полный месяц пребывания в проекте и тем, кто вступил в проект в начале/середине или в конце месяца (но преиод пребвания должен быть не меньше 10 дней в этом месяце). Например, клиент вошел в проект в середине мая месяца и пробыл в мае не меньше 10 дней - значит ему нужно выдать 1 сертификат, в июне этот клиент пробыл 30 дней + еще 1 сертификат, в июле пробыл 20 дней, то плюс еще 1 сертификат и того этому клиенту нужно выдать 3 сертификата за проект. Подскажите, пожалуйста, формулу как верно рассчитать.

Огромное спасибо за помощь. Всю голову сломал себе.
К сообщению приложен файл: 123456.xlsx(85.3 Kb)


Сообщение отредактировал serewka - Вторник, 03.09.2019, 12:48
 
Ответить
СообщениеДобрый день, есть сложная задачка Прошу помощи создании формулы.

Задача: Из массива данных автоматически вычесть сколько нужно выдать сертификатов (подарков) клиенту за весь период участия в проекте. Массив данных выгружается с базы в формате ексель, где есть код клиента и все даты, когда клиент был в проекте.
Критерии выдачи сертификата клиенту: Сертификат выдается каждому клиенту за каждый полный месяц пребывания в проекте и тем, кто вступил в проект в начале/середине или в конце месяца (но преиод пребвания должен быть не меньше 10 дней в этом месяце). Например, клиент вошел в проект в середине мая месяца и пробыл в мае не меньше 10 дней - значит ему нужно выдать 1 сертификат, в июне этот клиент пробыл 30 дней + еще 1 сертификат, в июле пробыл 20 дней, то плюс еще 1 сертификат и того этому клиенту нужно выдать 3 сертификата за проект. Подскажите, пожалуйста, формулу как верно рассчитать.

Огромное спасибо за помощь. Всю голову сломал себе.

Автор - serewka
Дата добавления - 03.09.2019 в 12:10
Nic70y Дата: Вторник, 03.09.2019, 12:45 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 5607
Репутация: 1227 ±
Замечаний: 0% ±

ru13;10;03
в июле этот клиент пробыл 30 дней + еще 1 сертификат, в июле пробыл 20 дней, то плюс еще 1
или я чего-то не понял или за июль 2 сертификата...


ЯД(poison) 41001841029809
 
Ответить
Сообщение
в июле этот клиент пробыл 30 дней + еще 1 сертификат, в июле пробыл 20 дней, то плюс еще 1
или я чего-то не понял или за июль 2 сертификата...

Автор - Nic70y
Дата добавления - 03.09.2019 в 12:45
serewka Дата: Вторник, 03.09.2019, 12:47 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Nic70y, спасибо, это опечатка, первый июль - это июнь


Сообщение отредактировал serewka - Вторник, 03.09.2019, 12:49
 
Ответить
СообщениеNic70y, спасибо, это опечатка, первый июль - это июнь

Автор - serewka
Дата добавления - 03.09.2019 в 12:47
Nic70y Дата: Вторник, 03.09.2019, 13:44 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 5607
Репутация: 1227 ±
Замечаний: 0% ±

ru13;10;03
Код
=(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=1))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=2))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=3))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=4))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=5))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=6))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=7))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=8))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=9))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=10))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=11))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=12))>9)


ЯД(poison) 41001841029809

Сообщение отредактировал Nic70y - Вторник, 03.09.2019, 13:52
 
Ответить
Сообщение
Код
=(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=1))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=2))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=3))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=4))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=5))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=6))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=7))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=8))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=9))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=10))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=11))>9)+(СУММПРОИЗВ(($C$5:$C$5054=H5)*(МЕСЯЦ($E$5:$E$5054)=12))>9)

Автор - Nic70y
Дата добавления - 03.09.2019 в 13:44
Kostya_Ye Дата: Вторник, 03.09.2019, 14:16 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 68
Репутация: 16 ±
Замечаний: 0% ±

Excel 2016
serewka, добрый день,
Я понял, что период участия клиента в проекте определяется минимальной и максимальной датами получения услуги. С учетом этого у меня вот так получилось.
Код
=((EOMONTH(MINIFS($E$4:$E$5054;$C$4:$C$5054;H5);0)-MINIFS($E$4:$E$5054;$C$4:$C$5054;H5)+1)>10)+((EOMONTH(MAXIFS($E$4:$E$5054;$C$4:$C$5054;H5);0)-MAXIFS($E$4:$E$5054;$C$4:$C$5054;H5))<=10)+(MONTH(MAXIFS($E$4:$E$5054;$C$4:$C$5054;H5))-MONTH(MINIFS($E$4:$E$5054;$C$4:$C$5054;H5))-1)

можно существенно сократить формулу вынеся расчет минимальной и максимальной даты в два дополнительных столбца.


Сообщение отредактировал Kostya_Ye - Вторник, 03.09.2019, 14:18
 
Ответить
Сообщениеserewka, добрый день,
Я понял, что период участия клиента в проекте определяется минимальной и максимальной датами получения услуги. С учетом этого у меня вот так получилось.
Код
=((EOMONTH(MINIFS($E$4:$E$5054;$C$4:$C$5054;H5);0)-MINIFS($E$4:$E$5054;$C$4:$C$5054;H5)+1)>10)+((EOMONTH(MAXIFS($E$4:$E$5054;$C$4:$C$5054;H5);0)-MAXIFS($E$4:$E$5054;$C$4:$C$5054;H5))<=10)+(MONTH(MAXIFS($E$4:$E$5054;$C$4:$C$5054;H5))-MONTH(MINIFS($E$4:$E$5054;$C$4:$C$5054;H5))-1)

можно существенно сократить формулу вынеся расчет минимальной и максимальной даты в два дополнительных столбца.

Автор - Kostya_Ye
Дата добавления - 03.09.2019 в 14:16
serewka Дата: Вторник, 03.09.2019, 14:26 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 34
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Kostya_Ye, клиент может пропустить например один месяц получения услуг (не приходить), был в марте, апреле, а в маю - нет и снова вернулся в июле, поэтому даты МИН и МАКС здесь не очень корректны. Тоже изначально так планировал расчет.
 
Ответить
СообщениеKostya_Ye, клиент может пропустить например один месяц получения услуг (не приходить), был в марте, апреле, а в маю - нет и снова вернулся в июле, поэтому даты МИН и МАКС здесь не очень корректны. Тоже изначально так планировал расчет.

Автор - serewka
Дата добавления - 03.09.2019 в 14:26
Kostya_Ye Дата: Вторник, 03.09.2019, 14:33 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 68
Репутация: 16 ±
Замечаний: 0% ±

Excel 2016
serewka, в таком случае только суммарного количества дней получения услуги в месяце достаточно ?
Если да, то решение предоставлено формулой Nic70y.
 
Ответить
Сообщениеserewka, в таком случае только суммарного количества дней получения услуги в месяце достаточно ?
Если да, то решение предоставлено формулой Nic70y.

Автор - Kostya_Ye
Дата добавления - 03.09.2019 в 14:33
bmv98rus Дата: Вторник, 03.09.2019, 15:03 | Сообщение № 8
Группа: Проверенные
Ранг: Участник клуба
Сообщений: 2528
Репутация: 437 ±
Замечаний: 0% ±

Excel 2013/2016
Код
=SUM(--(MMULT(TRANSPOSE(($C$5:$C$5054=H5)*
(--$E$5:$E$5054>EOMONTH(MIN(--$E$5:$E$5054);TRANSPOSE(ROW($A$1:INDEX(A:A;DATEDIF(MIN(--$E$5:$E$5054);MAX(--$E$5:$E$5054);"m")+1))-2)))*
(--$E$5:$E$5054<=EOMONTH(MIN(--$E$5:$E$5054);TRANSPOSE(ROW($A$1:INDEX(A:A;DATEDIF(MIN(--$E$5:$E$5054);MAX(--$E$5:$E$5054);"m")+1))-1))));
ROW($C$5:$C$5054)^0)>9))
Это если произвольные даты и не только в пределах одного года. Естевенно массивная. Если вытащить всякие преобразования в отделные ячейки, то будет очень компактно.
Код
=SUM(--(MMULT(TRANSPOSE(($C$5:$C$5054=H5)*
(--$E$5:$E$5054>EOMONTH($K$4;TRANSPOSE(ROW($A$1:INDEX($A:$A;$L$4))-2)))*
(--$E$5:$E$5054<=EOMONTH($K$4;TRANSPOSE(ROW($A$1:INDEX($A:$A;$L$4))-1))));
ROW($C$5:$C$5054)^0)>9))

а еще проще и шустрее
Код
=SUM(--(COUNTIFS($C$5:$C$5054;H5;$E$5:$E$5054;"*"&TEXT(EOMONTH($K$4;ROW($A$1:INDEX($A:$A;$L$4))-1);".ММ.ГГГГ"))>9))
и даже в одну формулу без допов
Код
=SUM(--(COUNTIFS($C$5:$C$5054;H5;$E$5:$E$5054;"*"&TEXT(EOMONTH(MIN(--$E$5:$E$5054);ROW($A$1:INDEX($A:$A;DATEDIF(MIN(--$E$5:$E$5054);MAX(--$E$5:$E$5054);"m")+1))-1);".ММ.ГГГГ"))>9))
К сообщению приложен файл: Copy_of_1125.xlsx(93.0 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 03.09.2019, 15:58
 
Ответить
Сообщение
Код
=SUM(--(MMULT(TRANSPOSE(($C$5:$C$5054=H5)*
(--$E$5:$E$5054>EOMONTH(MIN(--$E$5:$E$5054);TRANSPOSE(ROW($A$1:INDEX(A:A;DATEDIF(MIN(--$E$5:$E$5054);MAX(--$E$5:$E$5054);"m")+1))-2)))*
(--$E$5:$E$5054<=EOMONTH(MIN(--$E$5:$E$5054);TRANSPOSE(ROW($A$1:INDEX(A:A;DATEDIF(MIN(--$E$5:$E$5054);MAX(--$E$5:$E$5054);"m")+1))-1))));
ROW($C$5:$C$5054)^0)>9))
Это если произвольные даты и не только в пределах одного года. Естевенно массивная. Если вытащить всякие преобразования в отделные ячейки, то будет очень компактно.
Код
=SUM(--(MMULT(TRANSPOSE(($C$5:$C$5054=H5)*
(--$E$5:$E$5054>EOMONTH($K$4;TRANSPOSE(ROW($A$1:INDEX($A:$A;$L$4))-2)))*
(--$E$5:$E$5054<=EOMONTH($K$4;TRANSPOSE(ROW($A$1:INDEX($A:$A;$L$4))-1))));
ROW($C$5:$C$5054)^0)>9))

а еще проще и шустрее
Код
=SUM(--(COUNTIFS($C$5:$C$5054;H5;$E$5:$E$5054;"*"&TEXT(EOMONTH($K$4;ROW($A$1:INDEX($A:$A;$L$4))-1);".ММ.ГГГГ"))>9))
и даже в одну формулу без допов
Код
=SUM(--(COUNTIFS($C$5:$C$5054;H5;$E$5:$E$5054;"*"&TEXT(EOMONTH(MIN(--$E$5:$E$5054);ROW($A$1:INDEX($A:$A;DATEDIF(MIN(--$E$5:$E$5054);MAX(--$E$5:$E$5054);"m")+1))-1);".ММ.ГГГГ"))>9))

Автор - bmv98rus
Дата добавления - 03.09.2019 в 15:03
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула подсчета для нескольких критерий (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!