Como usar as fórmulas Index e Match para vários critérios

Question

Eu tenho uma planilha que contém informações olímpicas, com os seguintes campos em colunas:

A) Athlete, B) Age, C) Country, D) Year, E) Closing Ceremony Date, F) Sport G) Gold Medals.

Eu quero responder a essa pergunta sobre os dados:

Qual é o nome de um atleta que ganhou a maior medalha de ouro do país a seguir, por qualquer ano?

O país é a Noruega (em D38 ). Qualquer ajuda?

Eu estava usando inicialmente a fórmula:

 =INDEX(OlympicAthletes!A:A,MATCH(MAX(OlympicAthletes!G:G),OlympicAthletes!G:G,0),1)

No entanto, isso só retorna Michael Phelps. Eu preciso da fórmula para devolver o atleta com as maiores medalhas de ouro da Noruega. Obrigado!

    

Answer 1

Você pode fazer isso com uma fórmula de matriz repetitiva. Não é tão bonito, mas vai te dar o que você quer. Digite o seguinte e digite pressionando Ctrl + Deslocar + Enter .

=INDEX(A:A,MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1)))

O que isso faz (além de fazer você gostar de SQL):

• IF(C:C="Norway",G:G,-1) representa uma matriz do tamanho da coluna C que contém o valor da coluna G se o país desse registro for Noruega e -1 se o país não for a Noruega.
• MAX(IF(C:C="Norway",G:G,-1)) retorna o valor máximo desta matriz, que deve ser o número máximo de medalhas ganhas por um norueguês na sua tabela.
• IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1) verifica todas as linhas para duas condições: primeiro, que o atleta é norueguês e, segundo, que o número de medalhas de ouro ganhas por aquele atleta é igual ao número máximo ganho por um norueguês. O resultado dessa expressão é uma matriz do tamanho da coluna C que contém o número da linha, se ambas as condições forem atendidas, ou -1 , se não forem.
• MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1)) retorna o valor máximo desta matriz. Este será o número da linha da partida. Se houver mais de uma linha que atenda aos critérios, ela retornará aquela com o maior número de linha.
• Finalmente, INDEX(A:A,MAX(IF(C:C="Norway",IF(G:G=MAX(IF(C:C="Norway",G:G,-1)),ROW(G:G),-1),-1))) retorna o valor (nome) da coluna A no número da linha identificado como atendendo aos critérios.

Uma palavra rápida de aviso:

Tenha em atenção que, ao utilizar referências de colunas completas, por exemplo, A:A , numa fórmula de matriz, terá um impacto negativo perceptível na velocidade de cálculo. Se possível, você deve usar intervalos limitados, como A1:A10 , especialmente se estiver criando várias dessas fórmulas em sua pasta de trabalho.

    

Answer 2

Você pode querer pensar em colocar seus dados em uma Tabela Dinâmica. Ele permite resumir e consultar seus dados de várias maneiras. Especificamente, se você criou uma Tabela Dinâmica com cabeçalhos de linha Ano, depois País e, em seguida, Atleta, adicione as Medalhas de Ouro à área de dados. Isso dividirá os dados para você com muita precisão e fornecerá acesso a filtros que você pode usar para consultá-los com mais facilidade.