Encontrando o maior valor em um intervalo bidimensional e retornando o cabeçalho da coluna

Question

Eu tenho lutado para encontrar uma solução para localizar o número máximo dentro de um intervalo bidimensional e, em seguida, procurar o cabeçalho da coluna em que o número máximo cai.

Por exemplo:

Estou procurando uma fórmula que pesquise o número máximo no intervalo A2 para C5 (neste exemplo 9669 ). Em seguida, pesquise a coluna em que esse número se encaixa (neste exemplo Pessoa C ) e, em seguida, retorne esse valor.

Parece simplesmente suficiente para um intervalo unidimensional, mas não consigo encontrar nada para lidar com duas dimensões.

Qualquer ajuda seria muito apreciada.

    

Answer 1

Como a própria Microsoft declara: " Então, você quer se tornar um verdadeiro usuário avançado do Excel? Nesse caso, você precisa saber como usar fórmulas de matriz " .

A solução consiste em três etapas:

1. define uma matriz que pesquisa o valor máximo,
2. transformar essa matriz em 1 e 0,
3. multiplique essa matriz por um vetor contendo os números das colunas.

Passo 1: Como experiência, tente selecionar um intervalo igual ao tamanho da sua tabela, e digite =<table range> = MAX(<table range>) e confirme com CTRL SHIFT ENTER (a chave padrão para inserir fórmulas de matriz).

Etapa 2: O operador double menos converte os valores TRUE / FALSE em 0 (para FALSE) e 1 (para TRUE).

Etapa 3: Use SUMPRODUCT e COLUMN para multiplicar a matriz e criar o vetor com números de coluna.

Portanto, sua fórmula é semelhante a: SUMPRODUCT(--( <table range> = MAX( <table range> )) * COLUMN( <table range> )) (claro, substitua <table range> pelo intervalo ou nome da sua tabela.

Observe que essa fórmula retorna o número da coluna da planilha absoluto do valor máximo encontrado. Então, 5 para a coluna E em sua planilha. Se você quiser o número relativo em sua tabela, subtraia a coluna da célula superior esquerda da sua tabela e adicione uma. Então sua fórmula se torna: SUMPRODUCT(--( <table range> =MAX( <table range> )) * COLUMN( <table range> )) - COLUMN( <left top cell of table> ) + 1

Observe também que essa fórmula final usa fórmulas de matriz, mas não é uma matriz, mas um único valor. Então, para entrar nesta fórmula, você pode simplesmente clicar em ENTER .

Meu exemplo de trabalho: