Como encontrar todos os números/dados correspondentes em um intervalo

Question

Estou tentando encontrar um valor MAX e os anos aos quais ele pode estar relacionado.

• ANOS 2000-2010 (A1:J1)
• DADOS (A2:J2)
• MAX de DADOS é colocado em A3

Posso usar INDEX/MATCH para me dar o PRIMEIRO MAX ANO, onde corresponde ao MAX de DADOS, mas esse valor MAX pode aparecer em vários anos (por exemplo, 2002, 2005, 2009).

PRIMEIRO ANO MAX na B3

Eu tenho uma fórmula em C3 que agora está tentando iniciar outra correspondência de índice usando (OFFSET, 0, B3) para fazer a pesquisa começar após o PRIMEIRO ANO MÁXIMO, mas por algum motivo ela me dá o primeiro ano que encontra.

=INDEX(A1:J1,MATCH(A3,OFFSET(A2:J2,0,MATCH(B3, A1:J1,0))))

Arredondei meus números para que tenham um ponto decimal e sei que tenho dois valores MAX de DADOS correspondentes em meu intervalo A2: J2.

Atualização: uma imagem para explicar com mais clareza.

Alguma ideia?

Obrigado Michael

---

RESPONDER:

Segui o link do Abdul nos comentários abaixo e encontrei uma fórmula que atendeu muito bem aos meus requisitos.

=SEERRO(ÍNDICE(A1:K1,PEQUENO(SE(A2:K2=$A$3,COLUNA(A2:K2)-COLUNA(ÍNDICE(A2:K2,1,1))+1),enésimo))," ")

A enésima célula é o número 2. Portanto, ele está procurando a segunda ocorrência da minha célula A3.

Espero que isso ajude as pessoas. EExceljeté um ótimo site. Altamente recomendado.

obter a enésima correspondência com correspondência de índice

Answer 1

Você poderia fazer isso com fórmulas inseridas apenas em B3, C3, etc. Mas sugiro adicionar uma linha auxiliar (linha 4) para tornar as fórmulas mais legíveis.

Na célula B4 digite:

=MATCH($A3,OFFSET($A2:$K2,0,A4),0)+A4

Isso retorna 6qual é o número da coluna da primeira ocorrência de 19,0 na linha 2. Copie esta fórmula para a direita e você obterá 8e 11seguido por #N/Anas células restantes. Agora entre na célula B3:

=INDEX($A1:$K1,B4)

E copie a fórmula para a direita. Você obterá os anos em que o valor máximo foi encontrado: 2005e 2007seguido 2010nas #N/Ademais células.

Se não quiser ver os #N/Avalores, você pode usar a IFERRORfunção para alterá-los para espaços em branco.

Answer 2

Gosto da resposta de Blackwood, pois não requer uma fórmula de matriz. Mas se você não consegue cumprir a linha auxiliar, ou precisa fazer isso com apenas uma fórmula, você pode usar isso em A4 e preencher à direita:

 =IFERROR(SMALL(IF($A$2:$K$2=$A$3,$A$1:$K$1),COLUMN()),"")

É uma fórmula de matriz, portanto deve ser inserida com CTRL Shift Enter, em vez de apenas Enter.

Veja como isso funciona. O IF() produz uma matriz dos anos onde os dados correspondem a A3 (o máximo dos dados) e FALSE em todos os outros lugares:

{False;False;False;False;False;2005;False;2007;False;False;2010}

E então SMALL() seleciona o 1º, 2º, 3º, etc. menores valores dessa matriz usando o número da coluna como índice. Ele verifica apenas os números e ignora os valores “Falsos” na matriz.

Finalmente, IFERROR() substitui quaisquer erros #N/A por um espaço em branco.