#excel-formula
#excel-формула
Вопрос:
Итак, у меня есть набор данных, преобразованный из биржевого графика с шагом в 1 минуту, и я хочу извлечь ключевые точки данных из набора данных.
Проблема, с которой я сталкиваюсь, заключается в том, что когда я пытаюсь использовать функцию INDEX для сопоставления результатов MAXIF и MINIF, критерии времени не выполняются: первая функция для извлечения минимума дня из этого набора данных:
= MINIFS(E: E, B: B, «> 09:30», B: B, «<16:00»)
Вторая функция, из которой я пытаюсь извлечь время, когда извлекается точка данных с минимумом дня:
=INDEX(B: B,MATCH(MINIFS(E: E, B: B, «> 09:30», B: B, «<16:00»), E: E, 0))
Результат, который я получаю, равен 8:52 утра, что выходит за рамки установленных мною временных критериев. Похоже, что функция, похоже, извлекает самый первый экземпляр, который соответствует результату функции MINIF, полностью игнорируя временные критерии.
Также я хочу иметь в виду, что я хочу использовать функцию, которая не зависит от поиска отдельных ячеек, поскольку я надеюсь, что конечной целью является автоматизация процесса извлечения данных для экспорта всех значимых точек данных в новый лист Excel, и выполнение этого в течение нескольких сотендля тысяч наборов данных.
В идеале я хотел бы иметь функцию, которая может ссылаться на точную точку данных, которая была впервые извлечена, чтобы извлекать другие важные данные из той же строки и избегать возможной ссылки на неправильную точку данных только потому, что она дублируется в другом месте.
Комментарии:
1. Вероятно, это связано с тем, что в столбце E. есть дубликаты 2.06. Что вы можете попробовать: оператор match : =MATCH(1,(MINIFS(E: E, B: B, «> 09:30», B: B, «<16:00»)= E: E)*(КОСВЕННЫЙ (АДРЕС (R1, C1))<B:B)*(КОСВЕННЫЙ (АДРЕС (R2, C2))>B: B),0) Оператор index : =INDEX(B: B,MATCH(1,(MINIFS(E: E, B:B, «> 09:30», B: B, «<16:00»)= E: E) *(КОСВЕННЫЙ (АДРЕС (R1, C1))<B: B)*(КОСВЕННЫЙ (АДРЕС (R2, C2))> B: B), 0), E: E,0)) Замените R1, C1 и R2, C2 на строку и столбец ячейки, в которые вы ввели время 09:30 и 16:00 соответственно. Это приведет к получению таких чисел, как 0.395833 и 0.666667 соответственно.
2. Проблема в том, что строки, скорее всего, будут отличаться в зависимости от набора данных (например, в некоторых не будет никаких данных до 08:00), поэтому мне нужно будет использовать временные диапазоны, чтобы точно определить нужную строку, а затем выполнить индексацию. Если это вообще возможно.
Ответ №1:
Попробуйте следующую формулу массива, которую необходимо подтвердить с помощью CONTROL SHIFT ENTER…
=INDEX(B2:B10,MATCH(SMALL(IF(B2:B10>"09:30" 0,IF(B2:B10<"16:00" 0,E2:E10)),1),IF(B2:B10>"09:30" 0,IF(B2:B10<"16:00" 0,E2:E10)),0))
… и соответствующим образом отрегулируйте диапазоны. Однако для повышения эффективности я бы посоветовал избегать ссылок на целые столбцы.
Если диапазон со временем увеличивается, рассмотрите возможность преобразования ваших данных в таблицу. Ссылки будут автоматически корректироваться по мере добавления или удаления строк.
Обратите внимание, что 0 это добавляется ко времени. Это делается для преобразования строкового значения в истинное значение времени.
Редактировать
Поскольку похоже, что вы не хотите преобразовывать свои данные в таблицу, вот альтернатива, которая вместо этого использует определенные имена. Здесь также диапазоны будут автоматически корректироваться по мере добавления / удаления строк. Обратите внимание, что он использует столбец B для определения последней строки.
Сначала определите следующие имена (соответствующим образом измените имена листов)…
Name: LastRow
Refers to: =MATCH(9.99999999999999E 307,'Sheet 1'!$B:$B,1)
Name: TimeColumn
Refers to: ='Sheet 1'!$B$2:INDEX('Sheet 1'!$B:$B,LastRow)
Name: LowColumn
Refers to: ='Sheet 1'!$E$2:INDEX('Sheet 1'!$E:$E,LastRow)
Затем попробуйте следующую формулу, которую необходимо подтвердить с помощью CONTROL SHIFT ENTER…
=INDEX(TimeColumn,MATCH(SMALL(IF(TimeColumn>"09:30" 0,IF(TimeColumn<"16:00" 0,LowColumn)),1),IF(TimeColumn>"09:30" 0,IF(TimeColumn<"16:00" 0,LowColumn)),0))
Комментарии:
1. Смогу ли я уйти, сделав диапазоны намного шире, чем набор данных? т. е. .. в этом наборе данных около 847 строк… могу ли я сделать что-то вроде B2: B5000? Если да, то я буду работать с этим!
2. Если вы с уверенностью знаете, что данные никогда не выйдут за пределы 5000 строк, то, конечно, это было бы хорошо.
3. Смотрите мой пост, где я добавил альтернативу преобразованию ваших данных в таблицу.