Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Отобрать табельные номера у которых пересекаются даты
 
#1
01.10.2020 13:00:13
Здравствуйте. Как отобрать табельные номера у которых пересекаются даты? То есть у таб №1 всего 3 периода и нет ни одного пересечения между собой этих дат. А например у таб №7 есть пересечение даты в количестве 2 дней

 
 
 
 
#2
01.10.2020 14:10:30
Цитата
Евгений А написал:
например
покажите
то в файле как должно быть ручками заполните

https://www.planetaexcel.ru/techniques/6/234/
Изменено: Mershik - 01.10.2020 14:21:11
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
01.10.2020 15:35:27
Формат того как эти пересечения будут отображаться - не принципиален. Посмотрите на сотрудника с табельный номером 7. У него всего 2 временных периода (условно скажем, поставленные ему СМЕНЫ) которые пересекаются 1-го и 2-го сентября. В итоге мы знаем что у всех все хорошо, кроме 7-го!


По поводу ссылки на "МЕДИАНА", читал до создания темы, но что то не вышло..


Заметьте что в одной ячейке 2 даты. Если нет подходящей функции чтобы их разделить, то не проблема, я сделаю это самостоятельно.
Изменено: Евгений А - 01.10.2020 15:38:14
 
 
 
#4
01.10.2020 15:36:17
Евгений А, а почему в 4 не да?
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#5
01.10.2020 15:39:23
Верно! Извините, не заметил..
 
 
 
#6
01.10.2020 15:51:21
Евгений А, с доп столбцами
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#7
01.10.2020 16:24:32
Ахренеть!!! Гуд Ждоб, как сказал бы какой нибудь америкашка))) Диктуй номер в личку, рад буду кинуть на сигареты)))
 
 
 
#8
01.10.2020 16:39:43
Евгений А, не стоит) это бесплатная ветка
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#9
01.10.2020 16:58:40
Договоримся на том что мой след. вопрос будет обязательно через платные посты)))) Выручили, спасибо!
 
 
 
#10
01.10.2020 17:40:39
Для разнообразия (да и проще применять :) ) UDF:
Код
Function Peresec(TabNr, TabNrDiap, Data1, Data2, Data1Diap, Data2Diap)
    Dim r As Range, r1 As Range, c As Range, i&

    Peresec = "НЕТ"
    Set r1 = Range(Cells(Data1, 1), Cells(Data2, 1))
    For Each c In TabNrDiap.Cells
        i = i + 1
        If c.Row <> TabNr.Row Then
            If c = TabNr Then
                Set r = Intersect(r1, Range(Cells(Data1Diap.Cells(i), 1), Cells(Data2Diap.Cells(i), 1)))
                If Not r Is Nothing Then Peresec = "ДА": Exit Function
            End If
        End If
    Next
End Function
Изменено: Hugo - 01.10.2020 17:52:20
 
 
 
#11
01.10.2020 17:44:42
Раз уж сделал - вариант без доп.столбца.
Там где больше 1 - входит в интервал. Единственное, показывает именно те строки, где есть такие интервалы, а не напротив всего клиента.


Однако. Опять движок глючит. Один раз ответил - файл прикреплен был. Второй раз ответил - файл туда задублировался и когда удалил из второго, удалился и отсюда...
Изменено: Дмитрий(The_Prist) Щербаков - 01.10.2020 17:51:13
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#12
01.10.2020 17:49:04
Цитата
Mershik написал:
с доп столбцами
хм...в Вашем файле не нашел где у 4-го идет пересечение последнего периода с другими? Стоит ДА, но на деле это период "14.08.20 - 31.08.20", который ближе всего к этому периоду у 4-го "27.03.20 - 13.08.20", но он явно с ним не пересекается...
А нет, затупил. Там же самый первый - 01.06.20 - 18.10.20
Изменено: Дмитрий(The_Prist) Щербаков - 01.10.2020 17:53:37
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#13
01.10.2020 17:56:56
Дмитрий(The_Prist) Щербаков, что-то у меня на 2010 не работает.
Кстати в мою UDF тоже вполне не сложно внедрить работу без допстолбцов... Лениво :)
Изменено: Hugo - 01.10.2020 17:58:46
 
 
 
#14
01.10.2020 18:01:30
Цитата
Hugo написал:
на 2010 не работает
а что получается на месте формулы?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#15
01.10.2020 18:02:51
Все #Н/Д
В 2010 наверное ТРАНСП по новому не работает.
Хотя я не в теме как там оно по новому должно работать...
Изменено: Hugo - 01.10.2020 18:04:47
 
 
 
#16
01.10.2020 18:04:56
Посмотрел - видимо, в принципе в отпуск пора мне :) Там не ТРАНСП не работает в старых версиях, скорее всего, а ПСТР с ДВССЫЛ. Не раскладывает диапазон. Да и в новых формула моя некорректно будет работать - то, что для некоторых выдает правильный результат больше совпадение. Надо дорабатывать.
Изменено: Дмитрий(The_Prist) Щербаков - 01.10.2020 18:10:19
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#17
01.10.2020 19:39:00
В файле UDF не использующая допстолбцы с датами, они оставлены только для сравнения с результатом встроенными от Mershik.
 
 
 
#18
01.10.2020 19:42:16
А вот так
=SUMPRODUCT(((RIGHT(C2;8)+RIGHT($C$2:$C$23;8)-ABS(RIGHT(C2;8)-RIGHT($C$2:$C$23;8)))-(LEFT(C2;8)+LEFT($C$2:$C$23;8)+ABS(LEFT(C2;8)-LEFT($C$2:$C$23;8)))>=0)*$A$2:$A$23=A2))>1

Не UDF без доп столбцов. LEFT / RIGHT(C2;8) под конкретный пример , а  так дело техники заменить для извлечения
Изменено: БМВ - 01.10.2020 20:11:46
По вопросам из тем форума, личку не читаю.
 
 
 
#19
01.10.2020 20:02:30
БМВ, Странно, а у меня ошибку #VALUE выдает.
Причем оба варианта, первый с SUM и второй с SUMPRODUCT.
Изменено: memo - 01.10.2020 20:04:15
 
 
 
#20
01.10.2020 20:06:14
У меня тоже эта версия не работает.
А вот та оригинальная (где СУММПРОИЗВ()!) - работает! :)
Изменено: Hugo - 01.10.2020 20:09:20
 
 
 
#21
01.10.2020 20:08:39
Будет работать, если дата в региональных настройках установлена как dd.mm.yy или dd.mm.yyyy. Если нет, то надо усложнять формулу.
Владимир
 
 
 
#22
01.10.2020 20:11:20
хм, могу грешить на региональные, хотя странно


Цитата
sokol92 написал:
Будет работать, если дата в региональных настройках установлен
Владимир, привесттвую. вроде как стандартные для России
Изменено: БМВ - 01.10.2020 20:35:05
По вопросам из тем форума, личку не читаю.
 
 
 
#23
01.10.2020 20:19:19
Для России - да! Пример memo из #19 у меня работает корректно. У автора, вероятно, офис 365. А что выдает у memo

Код
Debug.Print Now
?
Изменено: sokol92 - 01.10.2020 20:24:16
Владимир
 
 
 
#24
01.10.2020 20:24:17
sokol92, Да, так и есть. Сменил точки на слеши и все заработало.
 
 
 
#25
01.10.2020 20:25:11
Отлично!
Владимир
 
 
 
#26
01.10.2020 20:57:18
Цитата
sokol92 написал:
Отлично!
Локализация - ЗЛО!!!
По вопросам из тем форума, личку не читаю.
 
 
 
#27
01.10.2020 21:03:59
Семь раз подумай, один раз локализуй.
Изменено: sokol92 - 01.10.2020 21:04:26
Владимир
 
 
 
#28
02.10.2020 02:46:11
Что-то не спится мне и решил я слегка дополнить формулу БМВ, чтобы она могла работать с буржуйскими региональными настройками без правки диапазона.
Код
=SUMPRODUCT(((SUBSTITUTE(RIGHT(C2;8);".";"/")+SUBSTITUTE(RIGHT($C$2:$C$23;8);".";"/")-ABS(SUBSTITUTE(RIGHT(C2;8);".";"/")-SUBSTITUTE(RIGHT($C$2:$C$23;8);".";"/")))-(SUBSTITUTE(LEFT(C2;8);".";"/")+SUBSTITUTE(LEFT($C$2:$C$23;8);".";"/")+ABS(SUBSTITUTE(LEFT(C2;8);".";"/")-SUBSTITUTE(LEFT($C$2:$C$23;8);".";"/")))>=0)*($A$2:$A$23=A2))>1
Изменено: memo - 02.10.2020 03:21:01
 
 
 
#29
02.10.2020 07:37:27
memo, Так нельзя. День и месяц могут меняться местами.  Для универсальности или в формате YY-MM-DD  формировать
=20&MID(C2;7;2)&"-"&MID(C2;4;2)&"-"&LEFT(C2;2)
или DATE() использовать
=DATE(20&MID(C2;7;2);MID(C2;4;2);LEFT(C2;2))
В 365м можно LET использовать, что немного сократит формулу. В остальных в имена запихнуть.
=SUMPRODUCT(((DATE(20&RIGHT(C2;2);MID(C2;15;2);MID(C2;12;2))+DATE(20&RIGHT($C$2:$C$23;2);MID($C$2:$C$23;15;2);MID($C$2:$C$23;12;2))-ABS(DATE(20&RIGHT(C2;2);MID(C2;15;2);MID(C2;12;2))-DATE(20&RIGHT($C$2:$C$23;2);MID($C$2:$C$23;15;2);MID($C$2:$C$23;12;2))))-(DATE(20&MID(C2;7;2);MID(C2;4;2);LEFT(C2;2))+DATE(20&MID($C$2:$C$23;7;2);MID($C$2:$C$23;4;2);LEFT($C$2:$C$23;2))+ABS(DATE(20&MID(C2;7;2);MID(C2;4;2);LEFT(C2;2))-DATE(20&MID($C$2:$C$23;7;2);MID($C$2:$C$23;4;2);LEFT($C$2:$C$23;2))))>=0)*($A$2:$A$23=A2))>1
Изменено: БМВ - 02.10.2020 08:25:32 (файл в более позднем сообщении)
По вопросам из тем форума, личку не читаю.
 
 
 
#30
02.10.2020 07:55:43
Ещё вариант:
=СУММПРОИЗВ((-ЛЕВБ(C2;8)>=-ПРАВБ(C$2:C$23;8))*(-ПРАВБ(C2;8)<=-ЛЕВБ(C$2:C$23;8))*(A2=A$2:A$23))>1
 
 
 
Страницы: 1 2 След.
Наверх