Excel - Correspondência Indexada + Grande com 2 Critérios

Question

perguntas semelhantes foram feitas neste fórum adorável, mas nenhuma delas se aplica ao meu problema, mesmo depois de um pouco de pompa do meu lado.

Eu tenho duas folhas (Ranking, Painel) e eu preciso encontrar o valor 'k' superior (1º, 2º, 3º, 4º, etc.) para cada critério.

Na folha "Ranking", tenho uma coluna em A que lista os jornais. Na coluna D, há um critério, como "Reino Unido", "UE" e "Promo".

No G3 eu tenho o número que representa o valor 'k' para a fórmula LARGE - neste caso, é 1

Estou colocando a fórmula em H3.

{= ÍNDICE (Painel! $ D $ 4: $ D $ 17143, CORRESPONDÊNCIA (1, ÍNDICE ((GRANDE (SE (Painel! $ A $ 4: $ A $ 17143 = A3, Painel! $ O $ 4: $ O $ 17143) , G3) = Painel! $ O $ 4: $ O $ 17143) (Dashboard! $ A $ 4: $ A $ 17143 = A3) (Painel! $ I4: $ 17143 = D3), 0), 0 ))}

A explicação é:

Painel D: D é a Referência do Tour do Anúncio - é isso que eu quero trazer de volta.

Painel A: A é a lista de 17.000 nomes de papel (320 valores únicos)

Dashboard O: O é a pontuação de cada anúncio para cada trabalho - os 4 melhores são o que eu quero.

Painel I: eu sou o tipo de tour (Reino Unido, UE, Promo)

O que eu gostaria é que a fórmula procure no painel o k rank superior que satisfaz o Nome do Papel sendo o mesmo, o tipo de passeio sendo o mesmo especificado e a pontuação sendo a mesma k especificada .

Alguma ideia? Obrigado.

    

Answer 1

Depois de brincar com isso por um tempo, encontrei problemas quando o resultado encontrado com LARGE usando o nome (Painel A: A) não correspondia ao tipo (Painel I: I). Acho que encontrar o grande valor é apenas o primeiro passo que deve ser usado como parte de seus critérios de correspondência, que é realmente nome, tipo e pontuação. Experimente esta fórmula de matriz:

{=INDEX(Dashboard!$D$4:$D$17143,MATCH(1,(A3=Dashboard!$A$4:$A$17143)*(D3=Dashboard!$I$4:$I$17143)*(LARGE((Dashboard!$O$4:$O$17143)*(A3=Dashboard!$A$4:$A$17143)*(D3=Dashboard!$I$4:$I$17143),G3)=Dashboard!$O$4:$O$17143),0),0)}

Nota: não se esqueça de entrar como Ctrl-Shft-Enter .

LARGE((Dashboard!$O$4:$O$17143)*(A3=Dashboard!$A$4:$A$17143)*(D3=Dashboard!$I$4:$I$17143),G3) retorna o enésimo ( G3 ) maior valor da matriz que corresponde ao nome e ao tipo.

Agora que você tem o valor LARGE , você tem todos os três critérios necessários para obter sua correspondência: nome, tipo e pontuação.

MATCH procura o TRUE na multiplicação de matrizes dos arrays que correspondem ao nome, tipo e pontuação (Painel O: O) e fornece o índice para a linha Referência de roteiro do anúncio (Painel D: D) .

Ressalva: Não tenho certeza se isso seria importante para seu uso, mas se você tiver mais de uma linha que corresponda a nome, tipo e pontuação, isso só retornará a primeira linha e você não saberá que pode ter havido outros jogos.