Recuperar enésima correspondência com INDEX
Para encontrar a enésima correspondência de um valor de um intervalo e recuperar seus dados correspondentes, você pode usar uma fórmula baseada no ÍNDICE, LINHA, PEQUENO e IF funções.
Como recuperar informações associadas à enésima correspondência de um valor com INDEX?
Para obter o informações sobre a 3ª partida de Usain Bolt na tabela conforme mostrado acima, você pode usar as funções ROW e IF juntas para obter uma matriz que consiste nos números das linhas de todos os valores de Usain Bolt. Em seguida, use a função SAMLL para recuperar o número da linha do enésimo valor correspondente que você especificou. Finalmente, alimente o resultado para a função INDEX para obter as informações correspondentes na mesma linha.
Sintaxe genérica
=INDEX(return_range,SMALL(IF(lookup_array=lookup_value,ROW(lookup_array-ROW(INDEX(lookup_array,1,1))+1),n))
√ Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + mudança + Entrar.
- intervalo_de_retorno: O intervalo do qual retornar as informações correspondentes à enésima correspondência. Aqui se refere ao ano ou intervalo de tempo.
- lookup_array: O intervalo de onde procurar a posição da linha da enésima correspondência. Aqui se refere ao intervalo vencedor.
- valor_pesquisa: O valor que você especificou para pesquisar sua enésima correspondência. Aqui se refere a Usain Bolt.
- n: Indique a enésima correspondência. Para encontrar a primeira correspondência de um valor, defina n como 1; para encontrar a 2ª correspondência, defina n como 2.
Para obter o informações sobre a 3ª partida de Usain Bolt, copie ou insira as fórmulas abaixo nas células G8 e G9 e pressione Ctrl + mudança + Entrar para obter os resultados:
Ano (Célula G8)
= INDEX (B6: B12, PEQUENO (SE (C6: C12=G5,FILEIRA(C6: C12-ROW (INDEX (C6: C12, 1,1)) + 1),G6))
Tempo (Célula G9)
= INDEX (D6: D12, PEQUENO (SE (C6: C12=G5,FILEIRA(C6: C12-ROW (INDEX (C6: C12, 1,1)) + 1),G6))
Explicação da fórmula
Aqui, usamos a fórmula abaixo como exemplo:
=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))
- ROW (C6: C12): A função ROW retorna os números das linhas de cada célula no intervalo C6: C12 em uma matriz como esta: {6;7;8;9;10;11;12}.
- ROW (INDEX (C6: C12,1,1)): As funções INDEX e ROW retornam o número da primeira linha do intervalo de pesquisa C6: C12 nesta planilha do Excel, que é 6.
- IF (C6: C12 = G5,ROW (C6: C12)-ROW (INDEX (C6: C12,1,1))+ 1) = IF (C6: C12 = G5,{6;7;8;9;10;11;12}-6+ 1): A função IF verifica se cada valor no intervalo C6: C12 corresponde ao valor na célula G5, Usain parafuso. Se sim, a função retornará o resultado de “seu número de linha correspondente-6 + 1”; Caso contrário, retorne um FALSE. Portanto, os resultados seriam em uma matriz como esta: {FALSO; 2; 3; 4; FALSO; FALSO; FALSO}.
- PEQUENA(IF (C6: C12 = G5,ROW (C6: C12)-ROW (INDEX (C6: C12,1,1))+ 1), G6) = PEQUENA({FALSO; 2; 3; 4; FALSO; FALSO; FALSO}, G6): O snippet retorna o 3rd (valor na célula G6) menor valor da matriz, que é 4.
- ÍNDICE (B6: B12,PEQUENA(IF (C6: C12 = G5,ROW (C6: C12)-ROW (INDEX (C6: C12,1,1))+ 1), G6)) = ÍNDICE (B6: B12,4) A função INDEX retorna o 4º valor na faixa do ano B6: B12, o que é Beijing 2008.
Funções relacionadas
A função INDEX do Excel retorna o valor exibido com base em uma determinada posição de um intervalo ou matriz.
A função IF é uma das funções mais simples e úteis na pasta de trabalho do Excel. Ele executa um teste lógico simples que dependendo do resultado da comparação, e retorna um valor se o resultado for VERDADEIRO ou outro valor se o resultado for FALSO.
A função ROW do Excel retorna o número da linha de uma referência.
Fórmulas Relacionadas
Recuperar enésima correspondência com VLOOKUP
Suponha que você tenha uma lista de valores e suas informações correspondentes; para recuperar as informações relacionadas da enésima correspondência de um valor, você pode usar uma fórmula baseada na função VLOOKUP.
Recupere informações associadas aos valores n mais baixos
Para recuperar dados que correspondem ao menor, segundo menor ou enésimo menor valor em uma lista, tabela ou linha do Excel, você pode usar uma fórmula INDEX e MATCH junto com a função SMALL.
Obtenha informações correspondentes ao valor máximo
Para recuperar dados que correspondem ao valor máximo em uma lista, tabela ou linha no Excel, você pode usar uma fórmula INDEX e MATCH junto com a função MAX.
Obtenha informações correspondentes ao valor mínimo
Para recuperar dados que correspondem ao valor mínimo em uma tabela, você pode usar uma fórmula INDEX e MATCH junto com a função MIN.
As melhores ferramentas de produtividade para escritório
Kutools for Excel - ajuda você a se destacar da multidão
Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.
- Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
- Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
- Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
- Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
