Excel 2013 - Как сделать так, чтобы Lookup возвращал первую дату после указанной даты?

233
Derek

Поэтому я хочу создать функцию, которая ищет уникальный идентификационный код в одном столбце и возвращает соответствующую метку времени из другого столбца. Однако к каждому уникальному идентификационному коду прикреплено несколько временных отметок. Я хочу вернуть только метку времени, которая следует сразу после указанного времени.

Например, для уникального идентификатора "9f45Q3" к нему прикреплены 4 метки времени:

  • 11:00
  • 11:15
  • 11:40
  • 11:55

Мне нужно найти способ сказать Excel, чтобы он возвращал отметку времени для «9f45Q3», которая наиболее немедленно последовала за 11:30. (просто произвольное время для объяснения) В этом случае я бы хотел, чтобы Excel вернул 11:40, потому что он наиболее точно следует моему указанному времени.

Любая помощь приветствуется. Спасибо!

0
Временные метки в одном столбце или в нескольких столбцах? Nayrb 6 лет назад 0
Отметки времени все в одном столбце, отдельные строки. Таким образом, в случае примера уникальный идентификатор будет одинаковым во всех 4 строках с разными соответствующими временными метками для каждого Derek 6 лет назад 0

1 ответ на вопрос

0
Forward Ed

Используйте INDEX и AGGREGATE

Используйте агрегат, чтобы определить номер строки, который соответствует вашим критериям или второму или третьему. В твоем случае второе. AGGREGATE выполняет операции с массивами, поэтому вам следует избегать использования полных ссылок на столбцы.

=AGGREGATE(14,6, row(range of interest)/(true condition check(range of interest)*true condition check n (range of interest)),2)  14 tells AGGREGATE to sort an array of results from smallest to largest.  6 tells AGGREGATE to ignore any errors and exclude them from the array of results)  ROW(range of interest) will return the row number corresponding to the current calculation  Condition check will be some formula you come up with that returns a TRUE or FALSE result. If it is false it will result in a divide by 0 calculation which the 6 will tell aggregate to ignore. You can apply multiple conditions and separate them by a * which will act as an AND function. 2 tells AGGREGATE to return the 2nd result in the sorted array. So in this case it should be the second row number that matches your results.

Следующее, что нужно сделать, это поместить функцию AGGREGATE внутрь INDEX, чтобы она возвращала нужную вам информацию. Поскольку INDEX не выполняет вычисления, подобные массиву, безопасно использовать полные ссылки на столбцы. Допустим, ваша метка времени находится в столбце B. Ваша формула индекса будет выглядеть примерно так:

=INDEX(B:B,AGGREGATE())

Итак, в конце концов, если предположить, что ваш идентификатор был в диапазоне A2: A8, а ваша отметка времени была в диапазоне B2: B8. И код идентификатора, который вы искали, был в C1, ваша формула может выглядеть следующим образом:

=INDEX(B:B,AGGREGATE(14,6,ROW(A2:A8)/(A2:A8=C1),2)

Теперь, если вы хотите добавить немного проверки ошибок, вы можете включить функцию IFERROR и сделать ее похожей на:

=IFERROR(INDEX(B:B,AGGREGATE(14,6,ROW(A2:A8)/(A2:A8=C1),2),"Could NOT find the darned thing")

Похожие вопросы