Vamos começar abordando os problemas com suas duas fórmulas existentes.
Sua fórmula y *, =FREQUENCY(A3:I3,K3) , na verdade calcula o número de vezes que os dados ficam abaixo, ou é igual a , o valor limite. Para contar apenas os valores abaixo do limite e supondo que os dados consistam apenas em valores inteiros, você precisa usar esta fórmula: =FREQUENCY(A3:I3,K3-1) .
A parte FREQUENCY da sua fórmula z *, FREQUENCY(IF(A3:I3<K3, COLUMN(A3:I3)), IF(A3:I3>K3, COLUMN(A3:I3))) , deve, a rigor, ter um >= em vez do > . Na sua fórmula z * completa, onde você extrai o máximo das contagens, na verdade não faz diferença. No entanto, pode não funcionar corretamente quando usado em uma fórmula mais complexa. Por exemplo, minha fórmula de solução não funciona corretamente com > (para o caso de borda em que o valor imediatamente antes da sequência mais longa é igual ao valor limite).
A fórmula z * corrigida é:
{=MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3))))}
O uso dessa fórmula corrigida como base leva à seguinte solução (entrada em matriz em N3 ), que extrai o cabeçalho da coluna da primeira célula da sequência mais longa:
{=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)))}
Explicação:
A versão prettificada da fórmula acima é a seguinte:
{=
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)
)
)}
A maneira como a fórmula funciona é que ela modifica as contagens de FREQUENCY() "bin" para que também contenham o índice bin. Em seguida, o índice é extraído da contagem de bin correspondente à sequência mais longa e usado com SMALL() para obter o limite inferior para esse bin. Esse limite é o número da coluna da célula imediatamente antes da primeira célula da sequência mais longa. Finalmente, o número da coluna da primeira célula é usado com INDEX() para obter o cabeçalho da primeira célula.
Para o seu exemplo fornecido:
-
FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))→{1;0;4;0;0}, o bin conta array -
10^5*{1;0;4;0;0}→{100000;0;400000;0;0}, o escaninho escalado conta matriz -
COUNT(IF(A3:I3>=K3,))→4, que é um a menos que o número de bins (isso conta os valores do limite de intervalo, mas o número de bins é um a mais que isso) - Assim,
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}, que são os índices na matriz de contagens dimensionadas,{100000;0;400000;0;0} -
{100000;0;400000;0;0}+{0;1;2;3;4}→{100000;1;400002;3;4}, o bin modificado conta matriz -
MAX({100000;1;400002;3;4})→400002, a maior contagem de binários de sequência modificada -
MOD(400002,10^5)→2, a classificação do limite inferior da maior sequência bin na matriz de limites (os índices na matriz de contagens de bin correspondem à classificação do limite inferior na matriz de limites) -
IF(A3:I3>=K3,COLUMN(A3:I3))→{FALSE,2,3,FALSE,FALSE,FALSE,FALSE,8,9}, a matriz de limites -
SMALL({FALSE,2,3,FALSE,FALSE,FALSE,FALSE,8,9},2)+1→3+1→4, o número da coluna da primeira célula da sequência mais longa (SMALL()ignora valores booleanos; o limite inferior correspondente a uma bin é o número da coluna da célula imediatamente anterior à primeira célula do bin) -
IFERROR(SMALL(…)+1,COLUMN(A3:I3))é necessário, pois não há limite inferior para o primeiro bin e, se a sequência mais longa corresponder ao primeiro bin (ou seja, a sequência mais longa começa na primeira célula do intervalo de dados), obtemosSMALL({…},0)+1→#NUM!.IFERROR()captura esse erro eCOLUMN(A3:I3)retorna o número da coluna da primeira célula. -
INDEX((1:1),4)→4, o cabeçalho da primeira célula da maior sequência
Notas:
- A fórmula prettificada realmente funciona se inserida.
- Os parênteses em torno de
(1:1)são necessários para forçar o1:1a permanecer em sua própria linha. -
ROW(INDEX(column,1):INDEX(column,…))é usado em vez do mais comumROW(INDIRECT("1:"&…)), pois é não volátil e também funciona quando as linhas / colunas são excluídas. (Desdecolumné definido para a coluna da célula a fórmula é inserida, é claro.) -
IF(1,,"comment")é um comentário in-line. (O valor é sempre zero, portanto, não há efeito líquido na fórmula). - Se você quiser apenas exibir o número da coluna da primeira célula, a fórmula pode ser simplificada removendo o mais externo
INDEX().
Advertência:
- Se houver mais de uma sequência mais longa, a fórmula acima retornará o cabeçalho inicial da última seqüência mais longa. A fórmula pode ser modificada para retornar o cabeçalho inicial da primeira sequência mais longa, usando o complemento dez do índice bin em vez do índice simples ao modificar as contagens binárias:
{=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)))}