Получить номер строки из 2 критериев (с 1 нечетким поиском)

Question

Я пытаюсь создать несколько графиков с динамическими ссылками на ячейки в качестве индексов. Эти графики основаны на отметках времени, сгенерированных с тестовой машины. У меня есть столбец логических значений (0 или 1), которые помечают "принудительные журналы", и электронную таблицу с различными полями данных для машины. Этот аппарат также создает электронную таблицу в отдельной рабочей книге (эта таблица содержит фактические метки времени, которые отмечают время начала и окончания), которые я вручную копирую и вставляю в другой лист рабочей книги с данными (в ней примерно 5000 строк). Я хочу найти мои начальные и конечные индексы для моего графика на основе меток времени. К сожалению, отдельно создаваемая книга Excel с правильными временными метками на выбор отстает примерно на 1 секунду от временной метки в основной таблице данных (эта задержка является переменной, но от 0 до 2 секунд). Кроме того, мои логические значения "журнал силы" должны быть равны 1 для отметок времени, но часто у меня есть дополнительные журналы силы, которые не соответствуют правильному времени для запуска графика. Ниже приведен репрезентативный образец по запросу.

Worksheet containing data (Call this Sheet 2)
  A                     B           C          D
  Time Stamp            Force Log  Field 1    Field 2   
1 2/6/2015 3:21:22 PM   1          100        500      <- Extraneous boolean value
2 2/6/2015 3:21:23 PM   0          101.4      499.2
3 2/6/2015 3:21:24 PM   1          99.3       501.4    <- I want this index for graph start
4 2/6/2015 3:21:25 PM   0          100.3      498.9
5 2/6/2015 3:21:26 PM   0          102.4      500.7
6 2/6/2015 3:21:27 PM   1          101.8      499.3    <- I want this index for graph end

Worksheet containing machine generated data summary (call this Sheet 1)
Start Time    End Time
3:21:23 PM    3:21:26   <- The times here lead the other worksheet by less than 2 seconds
                        They are also off by 2/6/2015, but I can generate a helper column
After obtaining the indices 3 and 6, I would graph the values of Field 1 and Field 2.

Вот что, исходя из моей проблемы, я собрал, что мне нужно сделать: выполнить нечеткий поиск меток времени путем усечения или условного тестирования на эквивалентность через диапазон (плюс минус 2 секунды), и убедитесь, что "force log" логический Значение столбца также установлено в 1.

Я новичок в Excel справочных и поисковых функций, поэтому я не уверен, что я что-то упустил. До сих пор я пробовал LOOKUP, VLOOKUP (не работало, потому что мои столбцы данных не отформатированы правильно для table_array), INDEX MATCH (не работало из-за нечеткого требования одного из моих поисков), и, кажется, не могу найти любые простые для понимания решения, позволяющие выполнять поиск по нескольким критериям, который возвращает несколько результатов (у меня есть несколько раз, когда каждому нужен один результат, а не один раз, когда требуется несколько результатов; я изо всех сил пытался понять формулы массива в Excel).

Я заранее очень благодарен всем, у кого есть решение или совет.

РЕДАКТИРОВАТЬ: я понимаю, что мой вопрос может иметь очень странный фон. Пожалуйста, дайте мне знать, если вам нужно, чтобы я уточнил или иным образом уточнил, что именно мне нужно делать. Спасибо!

Answer 1

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

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

1. Я преобразовал ваши данные в таблицу с именем по умолчанию Table1 . Когда формула ссылается на все значения для "метки времени", вы увидите ее как Table1[Time Stamp] .
2. Я назвал эти две клетки с начала и конца времен StartTime и EndTime , чтобы сделать формулу легче следовать. В противном случае он будет отображаться как A10 и B10 и за ним будет не так просто следить.

Вот формула, чтобы дать вам номер индекса для времени начала:

{=MATCH(1,IF(Table1[Force Log]=1,IF(HOUR(Table1[Time Stamp])=HOUR(StartTime),IF(MINUTE(Table1[Time Stamp])=MINUTE(StartTime),IF(SECOND(Table1[Time Stamp])-SECOND(StartTime)<=2,IF(SECOND(Table1[Time Stamp])-SECOND(StartTime)>=0,1))))),0)}

... и вот оно в последний раз:

{=MATCH(1,IF(Table1[Force Log]=1,IF(HOUR(Table1[Time Stamp])=HOUR(EndTime),IF(MINUTE(Table1[Time Stamp])=MINUTE(EndTime),IF(SECOND(Table1[Time Stamp])-SECOND(EndTime)<=2,IF(SECOND(Table1[Time Stamp])-SECOND(EndTime)>=0,1))))),0)}

Ключевым моментом является то, что обе они являются формулами массива, поэтому их необходимо вводить с помощью Ctrl+Shift+Enter. Не пытайтесь вставлять фигурные скобки {}, потому что это не сработает. Вставьте все, кроме них, используйте Ctrl+Shift+Enter, и появятся фигурные скобки {}.

---

Вот как это работает:

На самом деле это просто набор вложенных формул IF которые приводят к массиву 1 и 0 с формулой MATCH которая ищет первые 1 . Операторы IF устанавливают серию критериев, и, если каждый критерий удовлетворяется, значение равно 1 . Если какой-либо из них не выполняется, то значение равно 0 . (Ну, это на самом деле FALSE но это оценивается в 0 ) Он проверяет, что Force Log = 1 , час одинаковый, минута одинаковая и что секунды включительно находятся в пределах +0 / +2 секунд от времени, которое вы ищете. Обратите внимание, что я не проверял, чтобы дата была такой же, как ваше время начала и окончания, не включают значения даты.

Ключевой момент: если существует несколько записей, соответствующих этому критерию (т. Е. Если в вашем примере отметка времени 2/6/2015 15:21:23 PM имела значение Force Log 1), то эта формула найдет первую запись, которая соответствует критериям.