Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Установить статус если расчет попадает в период действия договора
 
#1
28.12.2020 15:44:05
Добрый день.
Попал в ступор.....вот как до работы с датами доходит, вообще тупняк......и формулами мне кажется тут не справиться. мб не прав :(
На листе "Расчет УК" есть столбцы Улица/дом/квартира/ПЕРИОД
На листе "База" есть столбцы Улица/дом/квартира/дата начала/дата конца.

Надо на листе "расчет УК" в столбце S поставить статус:
"Договора нет" - если по данному адресу + период, НЕ попадает в период действия договора на листе "База"
"Договор есть" - если по данному адресу + период, попадает в период действия договора на листе "База"

Если при статусе "Договор есть" период на листе "Расчет УК" выпадает на "дату начала" или "дату окончания" на листе "БАЗА" поставить такие даты в соседние ячейки рядом со статусом. (пример в файле 18 и 23 строка).
Вот обзац выше про даты, возможно выходит за рамки правил (одна тема один вопрос) если надо создам отдельно, но тут информация больше для понимания общей задачи, вдруг, будет макрос, и при добавлении условия что нужны ещё даты, придётся переписывать вообще с 0. Так что в случае чего Пнуть ногой в бочину, будем создавать отдельно)
UPD: создал под эту часть отдельную тему ТЫЦ

Файл в реальной структуре, всё лишнее удалено.
P.S. кол-во строк на каждом листе более 10000 по факту.
Сортировал Улица/дом/кв/период - но в реальных файлах сортировки может не быть. Но если без сортировки никуда, будем делать)

UPD: Дополнение в посте #18 ТЫЦ
Изменено: Wild.Godlike - 29.12.2020 13:17:23
 
 
 
#2
28.12.2020 15:52:20
Wild.Godlike, приветствую!

Ну по сути, задача сводится к:
  1. Подтянуть по СЦЕПКЕ "Улица-Дом-Квартира" из базы в расчёт МИНИМАЛЬНУЮ дату из столбца "начало договора" и МАКСИМАЛЬНУЮ дату из столбца "окончание договора" — то есть аналоги МИНЕСЛИ и МАКСЕСЛИ (экстремум по условию - обсуждалось много раз)
  2. Сравнить период на предмет попадания (>= начала И <=окончанию)
Изменено: Jack Famous - 28.12.2020 16:10:24 (Добавил файл)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#3
28.12.2020 16:26:13
Jack Famous, Очередное колдунство, премного благодарен, за уделённое время :) но результат не совсем верный.

Но есть Нюансы:

1)Строка 16 в вашем файле, результат ЛОЖЬ, а должна быть истина.
P.S.S. подумал тут, я же могу при проверке, отбрасывать число месяца и смотреть только на Месяц/ГОД. тогда по идее результат будет верный.
2)воткнул данные формулы в рабочий файл.(почти по 50к строк на каждом листе) при этом так же ограничив диапазоны(а не просто выбрав столбцы).
И ПК умер, проц нагрузка 100%, эксель не отвечает....

P.S. я вот тоже игрался всякими формулами, в итоге у меня был либо результат не тот, либо ПК Умерал.
Изменено: Wild.Godlike - 28.12.2020 16:49:22
 
 
 
#4
28.12.2020 16:53:50
Wild.Godlike, я думаю пришло время вам сменить комп, на своем компе я, для примера, установил 1048576 кол-во строк на обоих листах, есть незаметные тормоза, но работать можно. При том комп у меня среднечковый.
 
 
 
#5
28.12.2020 16:55:31
Цитата
Wild.Godlike: ПК умер
агрегат — тяжёлая функция в этом виде. Можно связать таблицы через PQ, например или макросом тянуть

Цитата
Adamm: на своем компе я, для примера, установил 1048576 кол-во строк на обоих листах, есть незаметные тормоза
что-то я сомневаюсь, что =АГРЕГАТ() на миллионе строк вообще у вас отработает — вы бы столбцы F:G проверили всё-таки на предмет наличия АГРЕГАТа…
Изменено: Jack Famous - 28.12.2020 17:01:11
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#6
28.12.2020 16:56:05
Adamm,  :D Да рад бы сменить этот пенек, но рабочий ПК дело такое....а всё время домой файлы не потаскаешь.
 
 
 
#7
28.12.2020 17:11:53
Jack Famous, Благодарствуем ещё раз. :)
Переделал формулу проверки на вот такого монстра)
=И(ДАТА(ГОД(I4);МЕСЯЦ(I4);ДЕНЬ(1))>=ДАТА(ГОД(T4);МЕСЯЦ(T4);ДЕНЬ(1));ДАТА(ГОД(I4);МЕСЯЦ(I4);ДЕНЬ(1))<=ДАТА(ГОД(U4);МЕСЯЦ(U4);ДЕНЬ(1)))
Файл утащил на домашний ПК, во славу тимвивера) полёт нормальный.
Пойду подумаю как решить вторую часть задачи)
 
 
 
#8
28.12.2020 17:21:12
Wild.Godlike, та на здоровье  :D
думаю, что скоро придут формулисты и намного интереснее накидают  ;)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#9
28.12.2020 17:39:43
Цитата
Jack Famous написал:
что-то я сомневаюсь
Да прошу прощения область не заменил, но протестил на 100000 строк, работает без тормозов
 
 
 
#10
28.12.2020 18:02:56
Adamm, при неизменном периоде и сцепке такое сравнение также некорректно, полагаю  :D
В любом случае, это не влияет на задачу напрямую - тут не до тестов пока что  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#11
28.12.2020 19:50:06
Wild.Godlike, Может в базе надо не текстом держать даты? Тогда простой COUNTIFS поможет
Изменено: БМВ - 29.12.2020 08:48:53
По вопросам из тем форума, личку не читаю.
 
 
 
#12
29.12.2020 08:20:09
БМВ, Доброго:)
В базе даты как дата) косяк при подготовке примера :(
Ну счетесли в моём 2016 нету :С но можно и UDF по юзать, но пока не пойму как её применить.
Кофе надо пить по утрам, чтоб такого не писать.
Изменено: Wild.Godlike - 29.12.2020 08:59:10
 
 
 
#13
29.12.2020 08:47:00
Цитата
Wild.Godlike написал:
счетесли в моём 2016 нету :С
чегооо???

Пример нормальный в студию. а то скоро 2+2 будем через UDF или PowerQuery считать. Вдруг у кого-то + на клавиатуре отсутствует.
Изменено: БМВ - 29.12.2020 08:48:33
По вопросам из тем форума, личку не читаю.
 
 
 
#14
29.12.2020 08:58:41
БМВ,  :D Максимально осуждаю себя.
Не проснулся написал, попутал Мин/максесли.
 
 
 
#15
29.12.2020 09:07:22
Цитата
БМВ: простой COUNTIF поможет
вместо МИНЕСЛИ и МАКСЕСЛИ? Как? Я поправил даты в #2…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#16
29.12.2020 09:37:09
Цитата
Jack Famous написал:
Как?
статус COUNTIFS. Даты, конечно при условии договоров, которые не пересекаются, SUMIFS
По вопросам из тем форума, личку не читаю.
 
 
 
#17
29.12.2020 09:39:11
Цитата
БМВ: статус COUNTIFS
так неинтересно  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#18
29.12.2020 10:05:19
Jack Famous, Доброго утра. начал работать плотно с файлом, перепроверяя результаты глазами и руками.
Ну и как обычно бывает на этом форуме, в исключения я не попал к сожалению.
Обнаружилась вот такая штука чего в изначальном файле не было........(для примера чучуть обновил файл пример):
строка 35 в приложеном файле. Период 01.10.2017 (т.е. Октябрь 2017) отображается как истина, а должно быть ложь, т.к. в период Октябрь 2017 нет действующего договора.

Получается что с подходом МИН/МАКС, не верно.

P.S. чучуть оправданий  :D случайно глаз зацепился когда перепроверял рабочий файл, уже на 30тыщ+ строке..... когда составлял пример даже не думал что могут быть промежутки.......
Изменено: Wild.Godlike - 29.12.2020 10:11:09
 
 
 
#19
29.12.2020 10:37:43
Wild.Godlike, приветствую. "Период" находится между началом и концом - всё верно, что не так?
Скрин

Цитата
Wild.Godlike: с подходом МИН/МАКС, не верно … не думал что могут быть промежутки
вникать не интересно, простите…
Изменено: Jack Famous - 29.12.2020 10:39:57
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#20
29.12.2020 10:46:21
Перекрыт е в один день можно обыграть убрав равенство
По вопросам из тем форума, личку не читаю.
 
 
 
#21
29.12.2020 10:47:30
Цитата
Jack Famous написал:
находится между началом и концом
По вашему решению период находится между минимальной датой начала действия одного договора и Максимальной другого по данному адресу.
Хотя по факту в договорах имеется разрыв.
Один закончился СЕНТЯБРЕМ 2017 другой начался НОЯБРЕМ 2017.
Октябрря 2017 = НЕТ
скрин


P.S.
Цитата
Jack Famous написал:
вникать не интересно,
Я сам в этом виноват, когда готовил тему и пример, не учёл/не знал что такое может быть..... :(

UDP:

БМВ, Извиняюсь не увидел сразу ответ ваш.

1)Вопрос а почему на 18,32 и 36 строках ЛОЖЬ когда должно быть ИСТИНА
2)Даты проставляются везде где истина, а должны только если выпадает на период начала или период конца в ином случае пусто, прям как в примере оранжевым. Ну это собстно вроде как нарушает правила форума, по этому я тут вторую тему создал под этот вопрос. ТЫЦ
Изменено: Wild.Godlike - 29.12.2020 13:16:26
 
 
 
#22
29.12.2020 14:22:10
на примере 18. почему истина? или я не корректно понял задачу?Как это
01.11.2017
Попадает сюда?
10.11.201723.01.2019Воргашорская        6 29
По вопросам из тем форума, личку не читаю.
 
 
 
#23
29.12.2020 14:31:06
БМВ, Период 01.11.2017. Если тип представления данных поменять будет написано Ноябрь 2017 т.е. 01.11.17-30.11.2017 т.е. именно месяц.
в картинке


Вот как раз из за этого и вытекает вторая тема про которую выше написал, когда дата начала договора выпадает на ПЕРИОД, но не на весь а на его часть. нужна рядом информация с какого именно числа, и тоже самое с датой закрытия.

:D постарался со всех сторон объяснить.
Изменено: Wild.Godlike - 29.12.2020 14:36:34
 
 
 
#24
29.12.2020 15:09:35
Цитата
Wild.Godlike написал:
т.е. 01.11.17-30.11.2017 т.е
делов то
=COUNTIFS(База!$F$1:$F$100000;"<="&EOMONTH(I4;0);База!$G$1:$G$100000;">="&I4;База!$H$1:$H$100000;'Рачет УК'!F4;База!$I$1:$I$100000;'Рачет УК'!G4;База!$J$1:$J$100000;'Рачет УК'!H4)>0
А разговоров то было
=IF(Y4;SUMIFS(База!F:F;База!F:F;"<="&EOMONTH(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);"")
=IF(Y4;SUMIFS(База!G:G;База!F:F;"<="&EOMONTH(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);"")
Изменено: БМВ - 29.12.2020 15:10:25
По вопросам из тем форума, личку не читаю.
 
 
 
#25
29.12.2020 17:00:41
БМВ,Очень благодарен. :oops:  :)
1) По статусам теперь ошибок нет.
2)по датам то что вы предложили оно по прежнему проставляло дату в каждую ячейку если истина.
Паразитируя на вашей формуле, слепил франкенштейна :D
=ЕСЛИ(И(Y4;МЕСЯЦ(I4)&ГОД(I4)=МЕСЯЦ(ЕСЛИ(Y4;СУММЕСЛИМН(База!F:F;База!F:F;"<="&КОНМЕСЯЦА(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);"0"))&ГОД(ЕСЛИ(Y4;СУММЕСЛИМН(База!F:F;База!F:F;"<="&КОНМЕСЯЦА(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);"0")));СУММЕСЛИМН(База!F:F;База!F:F;"<="&КОНМЕСЯЦА(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);"")

В итоге добился того что хотел.
Картинка


Беру из "Период" только МЕСЯЦ+ГОД и результат ФОРМУЛЫ МЕСЯЦ+ГОД, если они равны и статус ИСТИНА, то Возвращаю результат формулы, иначе пусто.
Что-то мне подсказывает что можно сделать по другому) но вроде работает. :D  
Изменено: Wild.Godlike - 29.12.2020 17:02:08
 
 
 
#26
29.12.2020 18:01:11
=IF(EOMONTH(I4;-1)+1=IF(Y4;EOMONTH(SUMIFS(База!F:F;База!F:F;"<="&EOMONTH(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);-1)+1;"");SUMIFS(База!F:F;База!F:F;"<="&EOMONTH(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);"")
и
=IF(EOMONTH(I4;0)=IF(Y4;EOMONTH(SUMIFS(База!G:G;База!F:F;"<="&EOMONTH(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);0);"");SUMIFS(База!G:G;База!F:F;"<="&EOMONTH(I4;0);База!G:G;">="&I4;База!H:H;'Рачет УК'!F4;База!I:I;'Рачет УК'!G4;База!J:J;'Рачет УК'!H4);"")
Изменено: БМВ - 29.12.2020 18:01:30
По вопросам из тем форума, личку не читаю.
 
 
 
#27
30.12.2020 14:36:24
БМВ, Очередное колдунство. Спасибо большое.
 
 
 
Страницы: 1
Наверх