Correspondência de índice grande com vários critérios, valores duplicados

0

Eu consegui extrair as 10 melhores pontuações de uma tabela (tblData), com os parâmetros sendo:

  1. Digite = LPR
  2. Ano = este ano (TYEAR)
  3. QTR = neste trimestre do exercício financeiro (TRIMESTRE)
  4. A coluna A mantém as posições 1 - 10 usando a função LARGE .
{=LARGE(IF((tblData[Type]=LPR)*(tblData[Year]=TYEAR)*(tblData[QTR]=QUARTER),tblData[Score]),$A7)}

Agora preciso extrair, de tblData (a tabela original), as informações correspondentes de cada linha em que as 10 principais pontuações estão localizadas.

Usando as funções INDEX e MATCH, tenho o seguinte para extrair os dados da coluna 3 da tabela:

{=INDEX(tblData[#All],MATCH(1,(tblData[Type]=LPR)*(tblData[Year]=TYEAR)*(tblData[QTR]=QUARTER)*(tblData[Score]=$B7),0),3)}

Isso funciona bem até que haja uma pontuação duplicada no Top 10. Então, recebo as mesmas informações da coluna 3 do tblData contra as duas pontuações.

Eu sei que a resposta está me encarando na cara!

Meus dados brutos:

Minhasaída:

tblData:

    
por WierNixie 24.01.2018 / 06:31

1 resposta

0

Usando uma fórmula modificada de essa resposta do SO

=INDEX(tblData[Code], MATCH(1, INDEX((tblData[Score]=LARGE(IF((tblData[Type]=LPR)*(tblData[Year]=TYEAR)*(tblData[QTR]=QUARTER),tblData[Score]),$A7))*(COUNTIF(C$1:C1, tblData[Code])=0),),0))'

Explicação

Esta fórmula cria uma tabela top-10 e, em seguida, pesquisa a sequência de código apropriada. (COUNTIF(C$1:C1, tblData[Code])=0) esta parte aparece na coluna atual, e veja se uma string de código específica já está inserida. Se já estiver inserido, encontre a próxima ocorrência.

Por exemplo, se ABC e DEF tiverem um valor 9, a coluna mostrará que a quarta posição é ABC . Quando ele tenta procurar o valor de 9, essa parte da fórmula sabe que ABC já está na coluna, portanto, ele encontrará a próxima cadeia de código com o valor de 9, que é DEF . Então, agora na quinta posição, mostra DEF .

    
por 24.01.2018 / 11:12