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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула подсчета для нескольких критерий (Формулы/Formulas)
Формула подсчета для нескольких критерий
serewka Дата: Вторник, 03.09.2019, 12:10 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 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
Группа: Друзья
Ранг: Экселист
Сообщений: 8760
Репутация: 2273 ±
Замечаний: 0% ±

Excel 2010
Цитата serewka, 03.09.2019 в 12:10, в сообщении № 1 ()
в июле этот клиент пробыл 30 дней + еще 1 сертификат, в июле пробыл 20 дней, то плюс еще 1
или я чего-то не понял или за июль 2 сертификата...

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата serewka, 03.09.2019 в 12:10, в сообщении № 1 ()
в июле этот клиент пробыл 30 дней + еще 1 сертификат, в июле пробыл 20 дней, то плюс еще 1
или я чего-то не понял или за июль 2 сертификата...
Автор - Nic70y
Дата добавления - 03.09.2019 в 12:45
serewka Дата: Вторник, 03.09.2019, 12:47 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 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
Группа: Друзья
Ранг: Экселист
Сообщений: 8760
Репутация: 2273 ±
Замечаний: 0% ±

Excel 2010
Код
=(СУММПРОИЗВ(($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)

ЮMoney 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 271
Репутация: 228 ±
Замечаний: 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
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Kostya_Ye, клиент может пропустить например один месяц получения услуг (не приходить), был в марте, апреле, а в маю - нет и снова вернулся в июле, поэтому даты МИН и МАКС здесь не очень корректны. Тоже изначально так планировал расчет.
 
Ответить
СообщениеKostya_Ye, клиент может пропустить например один месяц получения услуг (не приходить), был в марте, апреле, а в маю - нет и снова вернулся в июле, поэтому даты МИН и МАКС здесь не очень корректны. Тоже изначально так планировал расчет.
Автор - serewka
Дата добавления - 03.09.2019 в 14:26
Kostya_Ye Дата: Вторник, 03.09.2019, 14:33 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 271
Репутация: 228 ±
Замечаний: 0% ±

Excel 2016
serewka, в таком случае только суммарного количества дней получения услуги в месяце достаточно ?
Если да, то решение предоставлено формулой Nic70y.
 
Ответить
Сообщениеserewka, в таком случае только суммарного количества дней получения услуги в месяце достаточно ?
Если да, то решение предоставлено формулой Nic70y.
Автор - Kostya_Ye
Дата добавления - 03.09.2019 в 14:33
bmv98rus Дата: Вторник, 03.09.2019, 15:03 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 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-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!