Страницы: 1
RSS
Поиск значения в диапазоне дат по условию
 
Форумчане, помогайте. Никак не решу задачу.

Есть две вкладки, один исходный (Встречи) и Свод.
Во вкладке Свод имеется диапазон дат и из исходного файла необходимо вставить время (в случае наличия) в промежутке от "дата окончания" к "дата начала" ,в отношении конкретной машины из столбца D (вкладка Свод).

применил следующую формулу:
Код
=ИНДЕКС(Встречи!$A$1:$A$50000;СУММПРОИЗВ((Встречи!$B$1:$B$50000=$D2)*(Встречи!$A$1:$A$50000>B2)*(Встречи!$A$1:$A$50000<A3)*(СТРОКА(Встречи!$B$1:$B$50000)));0)
но она почему-то дает не всегда правильные значения. То есть когда нет машины во вкладке "Встречи" в запрашиваемый промежуток времени, ответ становит не корректный

Посмотрите, что делаю не так?
 
При несовпадении условий СУММПРОИЗВ(...)=0. Естественно, в диапазоне нет нулевой строки.
Чтобы не писать в формуле два раза СУММПРОИЗВ (для проверки и для вычисления), попробуем договориться с Excel:
МАКС(1;СУММПРОИЗВ(...))
Если условия не совпали, то МАКС(1;0) = 1, т.е. будет извлечено значение из первой строки диапазона. Сейчас это текст "Начало встречи". Можна заменить на "Караул, встреча не состоялась!" ).

Еще одно: Вы исключаете совпадение времени, правильно >=B2 и <=A3
=ИНДЕКС(Встречи!$A$1:$A$50000;МАКС(1;СУММПРОИЗВ((Встречи!$B$1:$B$50000=$D2)*
(Встречи!$A$1:$A$50000>=B2)*(Встречи!$A$1:$A$50000<=A3)*(СТРОКА(Встречи!$B$1:$B$50000)))))
 
vikttur Огромное спасибо, три дня мучился)

по поводу
Цитата
правильно >=B2 и <=A3
спасибо что подправили).

Применил Вашу доработку, В начале обрадовался, но далее обратил внимание что за 13.09, 16.09, 18.09 (далее красным выделили в файле) данные опять "поплыли",  подскажите, что не учёл? Может я вообще принципиально не тем путем пошел (не те функции учитываю в формуле)?
 
Порядок условий в формуле нелогичный, не заметил раньше ошибки. Посмотрите внимательно на знаки неравенства...
 
Порядок условий, логичен. Задача найти пересечение транспорта во время остановки. Тем не менее, посмотрел на знаки неравенства, вроде все нормально, но обратил внимание что некорректные данные там, где в разыскиваемом диапазоне дат, больше одной машины, то есть разыскиваемая машина указана более одного раза. может из-за этого формула дает некорректное значение? Нет ли возможности брать первое значение, не обращая внимание на последующие?
 
От порядка условий в Вашем формуле ничего не меняется, но логичнее (для восприятия) сначала указывать условие нижней границы, после него - верхней. а не шиворот навыворот.
Дата начала - столбец А, дата окончания - столбец . Т.е. сначала условие сравнения с А, после его - с В.

Ошибка - вот она
Цитата
>=B2 и <=A3
ссылки на разные строки
 
В смысле решения задачи, мне именно это и нужно, начало остановки это ячейка B2 (соответственно ищу больше этой даты и времени).
остановка заканчивается это ячейка A3 (соответственно ищу меньше этой даты и времени) и да, это разные строки. Так как именно этот диапазон мне и нужен. То есть формулой ни как не решить? Формула не дает сокращать диапазон даты беря значения из разных строк?  
 
Авто прописано в одной строке, даты - в разных...Не понимаю.
 
в одной строке с машиной указано начало движения (дата начала) ячейка A1 и окончание движения (дата окончания) ячейка B1, в данной строке указана и машина. Но машина та которая должна подъехать во время остановки (после окончания движения, но до начала следующего движения ячейка A2). Поэтому и ссылки на разные строки B1 и A2 или B2 и A3 или B3 и A4 и так далее

Теперь понятна причина "некорректности" вводимых значений. В случае если в искомом диапазоне более одной машины, то формула суммирует строки и выдает значение из суммированной строки. Решить проблему пока не удалось.

Конечно, необходимо первое значение.
 
нашел решение)
Полностью изменил концепцию. Применил следующую формулу.
=МИНЕСЛИ(Встречи!A:A;Встречи!A:A;">="&B2;Встречи!A:A;"<="&A3;Встречи!B:B;D2)
данная формула сократила до необходимого диапазон, и позволила найти в исходном файле необходимое авто.
но при отсутствии в исходном файле искомой техники, выдавало не красивый ответ: 00.01.1900 00:00:00

Вспомнил что есть формула которая позволяет убирать такие "нулевые" значения в дате, она у меня записана следующим образом =ЕСЛИОШИБКА(ЕСЛИ(A8=0;"";ЕСЛИ(A8>0;ЕСЛИ(ЕТЕКСТ(A8);ДАТАЗНАЧ(A8);A8);A8));"")

применил (подставил вместо A8 вышеуказанную формулу) её для своей ситуации и получил следующую рабочую формулу:

=ЕСЛИОШИБКА(ЕСЛИ((МИНЕСЛИ(Встречи!A:A;Встречи!A:A;">="&B2;Встречи!A:A;"<="&A3;Встречи!B:B;D2))=0;"-";ЕСЛИ((МИНЕСЛИ(Встречи!A:A;Встречи!A:A;">="&B2;Встречи!A:A;"<="&A3;Встречи!B:B;D2))>0;ЕСЛИ(ЕТЕКСТ((МИНЕСЛИ(Встречи!A:A;Встречи!A:A;">="&B2;Встречи!A:A;"<="&A3;Встречи!B:B;D2)));ДАТАЗНАЧ((МИНЕСЛИ(Встречи!A:A;Встречи!A:A;">="&B2;Встречи!A:A;"<="&A3;Встречи!B:B;D2)));(МИНЕСЛИ(Встречи!A:A;Встречи!A:A;">="&B2;Встречи!A:A;"<="&A3;Встречи!B:B;D2)));(МИНЕСЛИ(Встречи!A:A;Встречи!A:A;">="&B2;Встречи!A:A;"<="&A3;Встречи!B:B;D2))));"-")

вдруг кому поможет)
Всем спасибо и отдельное спасибо vikttur
 
Цитата
makstel написал:
вдруг кому поможет)
запутаться на вечно
1. если в ячейке значение которое можно преобразовать в число или просто число, то  -- преобразует его в число или не изменит
этим =ЕСЛИОШИБКА(ЕСЛИ(A8=0;"";ЕСЛИ(A8>0;--A8;A8));"")
2 если  в ячейке что-то меньше 0 то -- тоже не изменит
=ЕСЛИОШИБКА(ЕСЛИ(A8=0;"";--A8);"")
3. если будет белебрда то будет ошибка и значит будет "" - это хорошо
4 . а что делать с 0, да все просто -- заменим на 1/A8 иии
=ЕСЛИОШИБКА(1/(1/A8);"")
в результате
4.1 если 0 то 1/0 вызовет ошибку и будет ""
4.2 если <>0 то оно и будет
4.3 если значение похожее на число, то это число
4.5 если значение не похожее на число, то ""

Теперь меняйте ваше А8 на формулу и радуйтесь.
Изменено: БМВ - 25.01.2022 07:35:04
По вопросам из тем форума, личку не читаю.
 
Цитата
написал:
Теперь меняйте ваше a8 на формулу и радуйтесь.
Спасибо огромное) Очень упростило формулу)
Страницы: 1
Наверх