Добрый день. Попал в ступор.....вот как до работы с датами доходит, вообще тупняк......и формулами мне кажется тут не справиться. мб не прав На листе "Расчет УК" есть столбцы Улица/дом/квартира/ПЕРИОД На листе "База" есть столбцы Улица/дом/квартира/дата начала/дата конца.
Надо на листе "расчет УК" в столбце S поставить статус: "Договора нет" - если по данному адресу + период, НЕ попадает в период действия договора на листе "База" "Договор есть" - если по данному адресу + период, попадает в период действия договора на листе "База"
Если при статусе "Договор есть" период на листе "Расчет УК" выпадает на "дату начала" или "дату окончания" на листе "БАЗА" поставить такие даты в соседние ячейки рядом со статусом. (пример в файле 18 и 23 строка). Вот обзац выше про даты, возможно выходит за рамки правил (одна тема один вопрос) если надо создам отдельно, но тут информация больше для понимания общей задачи, вдруг, будет макрос, и при добавлении условия что нужны ещё даты, придётся переписывать вообще с 0. Так что в случае чего Пнуть ногой в бочину, будем создавать отдельно) UPD: создал под эту часть отдельную тему ТЫЦ
Файл в реальной структуре, всё лишнее удалено. P.S. кол-во строк на каждом листе более 10000 по факту. Сортировал Улица/дом/кв/период - но в реальных файлах сортировки может не быть. Но если без сортировки никуда, будем делать)
Ну по сути, задача сводится к: 1. Подтянуть по СЦЕПКЕ "Улица-Дом-Квартира" из базы в расчёт МИНИМАЛЬНУЮ дату из столбца "начало договора" и МАКСИМАЛЬНУЮ дату из столбца "окончание договора" — то есть аналоги МИНЕСЛИ и МАКСЕСЛИ (экстремум по условию - обсуждалось много раз) 2. Сравнить период на предмет попадания (>= начала И <=окончанию)
Изменено: Jack Famous - 28.12.2020 16:10:24(Добавил файл)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, Очередное колдунство, премного благодарен, за уделённое время но результат не совсем верный.
Но есть Нюансы:
1)Строка 16 в вашем файле, результат ЛОЖЬ, а должна быть истина. P.S.S. подумал тут, я же могу при проверке, отбрасывать число месяца и смотреть только на Месяц/ГОД. тогда по идее результат будет верный. 2)воткнул данные формулы в рабочий файл.(почти по 50к строк на каждом листе) при этом так же ограничив диапазоны(а не просто выбрав столбцы). И ПК умер, проц нагрузка 100%, эксель не отвечает....
P.S. я вот тоже игрался всякими формулами, в итоге у меня был либо результат не тот, либо ПК Умерал.
Wild.Godlike, я думаю пришло время вам сменить комп, на своем компе я, для примера, установил 1048576 кол-во строк на обоих листах, есть незаметные тормоза, но работать можно. При том комп у меня среднечковый.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, Благодарствуем ещё раз. Переделал формулу проверки на вот такого монстра) =И(ДАТА(ГОД(I4);МЕСЯЦ(I4);ДЕНЬ(1))>=ДАТА(ГОД(T4);МЕСЯЦ(T4);ДЕНЬ(1));ДАТА(ГОД(I4);МЕСЯЦ(I4);ДЕНЬ(1))<=ДАТА(ГОД(U4);МЕСЯЦ(U4);ДЕНЬ(1))) Файл утащил на домашний ПК, во славу тимвивера) полёт нормальный. Пойду подумаю как решить вторую часть задачи)
Wild.Godlike, та на здоровье думаю, что скоро придут формулисты и намного интереснее накидают
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Adamm, при неизменном периоде и сцепке такое сравнение также некорректно, полагаю В любом случае, это не влияет на задачу напрямую - тут не до тестов пока что
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
БМВ, Доброго:) В базе даты как дата) косяк при подготовке примера Ну счетесли в моём 2016 нету :С но можно и UDF по юзать, но пока не пойму как её применить. Кофе надо пить по утрам, чтоб такого не писать.
вместо МИНЕСЛИ и МАКСЕСЛИ? Как? Я поправил даты в #2…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, Доброго утра. начал работать плотно с файлом, перепроверяя результаты глазами и руками. Ну и как обычно бывает на этом форуме, в исключения я не попал к сожалению. Обнаружилась вот такая штука чего в изначальном файле не было........(для примера чучуть обновил файл пример): строка 35 в приложеном файле. Период 01.10.2017 (т.е. Октябрь 2017) отображается как истина, а должно быть ложь, т.к. в период Октябрь 2017 нет действующего договора.
Получается что с подходом МИН/МАКС, не верно.
P.S. чучуть оправданий случайно глаз зацепился когда перепроверял рабочий файл, уже на 30тыщ+ строке..... когда составлял пример даже не думал что могут быть промежутки.......
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: находится между началом и концом
По вашему решению период находится между минимальной датой начала действия одного договора и Максимальной другого по данному адресу. Хотя по факту в договорах имеется разрыв. Один закончился СЕНТЯБРЕМ 2017 другой начался НОЯБРЕМ 2017. Октябрря 2017 = НЕТ
1)Вопрос а почему на 18,32 и 36 строках ЛОЖЬ когда должно быть ИСТИНА 2)Даты проставляются везде где истина, а должны только если выпадает на период начала или период конца в ином случае пусто, прям как в примере оранжевым. Ну это собстно вроде как нарушает правила форума, по этому я тут вторую тему создал под этот вопрос. ТЫЦ
БМВ, Период 01.11.2017. Если тип представления данных поменять будет написано Ноябрь 2017 т.е. 01.11.17-30.11.2017 т.е. именно месяц.
в картинке
Вот как раз из за этого и вытекает вторая тема про которую выше написал, когда дата начала договора выпадает на ПЕРИОД, но не на весь а на его часть. нужна рядом информация с какого именно числа, и тоже самое с датой закрытия.
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);"")
БМВ,Очень благодарен. 1) По статусам теперь ошибок нет. 2)по датам то что вы предложили оно по прежнему проставляло дату в каждую ячейку если истина. Паразитируя на вашей формуле, слепил франкенштейна =ЕСЛИ(И(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);"")
В итоге добился того что хотел.
Картинка
Беру из "Период" только МЕСЯЦ+ГОД и результат ФОРМУЛЫ МЕСЯЦ+ГОД, если они равны и статус ИСТИНА, то Возвращаю результат формулы, иначе пусто. Что-то мне подсказывает что можно сделать по другому) но вроде работает.