Давайте начнем с решения проблем с вашими двумя существующими формулами.
Ваша формула y * на =FREQUENCY(A3:I3,K3)самом деле вычисляет, сколько раз данные опускаются ниже или равны пороговому значению. Чтобы рассчитывать только значения ниже порога, и предполагая, что данные состоят только из целочисленных значений, вы должны использовать эту формулу: .=FREQUENCY(A3:I3,K3-1)
FREQUENCYЧасть вашего г * формулы FREQUENCY(IF(A3:I3<K3, COLUMN(A3:I3)), IF(A3:I3>K3, COLUMN(A3:I3)))следует, строго говоря, имеют >=вместо >. В вашей полной формуле z *, где вы просто извлекаете максимальное количество отсчетов, это на самом деле не имеет значения. Тем не менее, он может работать неправильно при использовании в более сложной формуле. Например, моя формула решения не работает правильно с >(для граничного случая, когда значение непосредственно перед самой длинной последовательностью равно пороговому значению).
Исправленная формула z *:
{=MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3))))}
Использование этой исправленной формулы в качестве основы приводит к следующему решению (введенному в массив N3), которое извлекает заголовок столбца первой ячейки самой длинной последовательности:
{=INDEX(1:1,IFERROR(SMALL(IF(A3:I3>=K3,COLUMN(A3:I3)),MOD(MAX(10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))+ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1),10^5))+1,COLUMN(A3:I3)))} Объяснение:
Предварительно подтвержденная версия приведенной выше формулы выглядит следующим образом:
{= INDEX( (1:1), IFERROR( SMALL( IF(A3:I3>=K3,COLUMN(A3:I3)), MOD( MAX( 10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3))) +ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1+IF(1,,"N:N needs to match the column of the cell this formula is entered into") ), 10^5 ) )+1, COLUMN(A3:I3) ) )} Формула работает так, что она изменяет FREQUENCY()счетчики «bin», чтобы они также содержали индекс bin. Затем индекс извлекается из числа бинов, соответствующего самой длинной последовательности, и используется SMALL()для получения нижнего порога для этого бина. Этот порог является номером столбца ячейки непосредственно перед первой ячейкой самой длинной последовательности. Наконец, номер столбца первой ячейки используется INDEX()для получения заголовка первой ячейки.
Для вашего предоставленного примера:
FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))→, массив бинов10^5*→, массив масштабированных биновCOUNT(IF(A3:I3>=K3,))→4который на единицу меньше количества ячеек (отсчитывается пороговое значение интервала, но количество ячеек на единицу больше)- Таким образом → → которые являются индексами в массиве масштабированных бинов,
ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1ROW(INDEX(N:N,1):INDEX(N:N,5))-1 +→, модифицированный массив счетчиковMAX()→400002, наибольшее количество модифицированных биновMOD(400002,10^5)→2ранг нижнего порога самого длинного бина последовательности в массиве порогов (индексы в массиве счетчиков бинов соответствуют рангу нижнего порога в массиве порогов)IF(A3:I3>=K3,COLUMN(A3:I3))→пороговый массивSMALL(,2)+1→3+1→4номер столбца первой ячейки самой длинной последовательности (SMALL()игнорирует логические значения; нижний порог, соответствующий ячейке, является номером столбца ячейки непосредственно перед первой ячейкой ячейки)IFERROR(SMALL(…)+1,COLUMN(A3:I3))требуется, поскольку для первого бина нет нижнего порога и если самая длинная последовательность соответствует первому бину (то есть самая длинная последовательность начинается в первой ячейке диапазона данных), мы получаемSMALL({…},0)+1→#NUM!.IFERROR()перехватывает эту ошибку иCOLUMN(A3:I3)возвращает номер столбца первой ячейки.INDEX((1:1),4)→4заголовок столбца первой ячейки самой длинной последовательности
Заметки:
- Предварительно подтвержденная формула действительно работает, если введена.
- Скобки вокруг
(1:1)необходимы, чтобы заставить1:1их оставаться на своей линии. ROW(INDEX(column,1):INDEX(column,…))используется вместо более распространенного,ROW(INDIRECT("1:"&…))поскольку он является энергонезависимым и также работает, когда строки / столбцы удаляются. (При условии, чтоcolumnон установлен в столбец ячейки, в которую, конечно, вводится формула.)IF(1,,"comment")это встроенный комментарий. (Значение всегда равно нулю, поэтому нет влияния на формулу.)- Если вы просто хотите отобразить номер столбца первой ячейки, формулу можно упростить, удалив крайнюю часть
INDEX().
Предостережение:
- Если существует более одной самой длинной последовательности, приведенная выше формула возвращает начальный заголовок последней самой длинной последовательности. Формула может быть изменена так, чтобы она возвращала начальный заголовок первой самой длинной последовательности, используя десятичное дополнение индекса бина вместо простого индекса при изменении количества бинов:
{= INDEX (1: 1, IFERROR (МАЛЕНЬКИЙ (ЕСЛИ (A3: I3> = K3, КОЛОННА (A3: I3)), 10 ^ 5- MOD (МАКС. (10 ^ 5 * ЧАСТОТА) (IF (A3: I3 <K3), COLUMN (A3: I3)), IF (A3: I3> = K3, COLUMN (A3: I3))) + 10 ^ 5- ( ROW (INDEX (N: N, 1)): INDEX (N: N, COUNT (ЕСЛИ (A3: I3> = K3,)) + 1)) - 1) ), 10 ^ 5)) + 1, КОЛОННА (A3: I3)))}