Давайте начнем с решения проблем с вашими двумя существующими формулами.
Ваша формула 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))) следует, строго говоря, есть a >= вместо > . В вашей полной формуле 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)
)
)}
Формула работает так, что она изменяет счетчик "bin" FREQUENCY() чтобы они также содержали индекс bin. Затем индекс извлекается из числа бинов, соответствующего самой длинной последовательности, и используется с SMALL() для получения нижнего порога для этого бина. Этот порог является номером столбца ячейки непосредственно перед первой ячейкой самой длинной последовательности. Наконец, номер столбца первой ячейки используется с INDEX() для получения заголовка первой ячейки.
Для вашего предоставленного примера:
FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3))) → {1;0;4;0;0} , корзина подсчитывает массив10^5*{1;0;4;0;0} → {100000;0;400000;0;0} , массив масштабированных биновCOUNT(IF(A3:I3>=K3,)) → 4 что на единицу меньше количества интервалов (это подсчитывает пороговые значения интервала, но количество интервалов на единицу больше)- Таким образом,
ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1 → ROW(INDEX(N:N,1):INDEX(N:N,5))-1 → {0;1;2;3;4} которые являются индексами в массиве масштабированных счетчиков бинов, {100000;0;400000;0;0}
{100000;0;400000;0;0}+{0;1;2;3;4} → {100000;1;400002;3;4} , модифицированный массив счетчиков биновMAX({100000;1;400002;3;4}) → 400002 , число бинов с модифицированной самой длинной последовательностьюMOD(400002,10^5) → 2 , ранг нижнего порога самого длинного бина последовательности в массиве порогов (индексы в массиве счетчиков бинов соответствуют рангу нижнего порога в массиве порогов)IF(A3:I3>=K3,COLUMN(A3:I3)) → {FALSE,2,3,FALSE,FALSE,FALSE,FALSE,8,9} , массив пороговSMALL({FALSE,2,3,FALSE,FALSE,FALSE,FALSE,8,9},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(SMALL(IF(A3:I3>=K3,COLUMN(A3:I3)),10^5-MOD(MAX(10^5*FREQUENCY(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(IF(A3:I3>=K3,))+1))-1)),10^5))+1,COLUMN(A3:I3)))}
