Reorganizou sua tabela de resultados um pouco para permitir alguma flexibilidade. Veja a imagem na parte inferior para referência de célula e lay out. Em vez de dizer rank alto, e segundo rank, eu coloquei um cabeçalho acima do que disse RANK e, em seguida, colocar um número que representa a posição de classificação. Eu fiz isso para que você pudesse simplesmente arrastar a fórmula para a direita e puxar a classificação com base no número de classificação no cabeçalho.
Usando os intervalos de referência no exemplo abaixo, coloque a seguinte fórmula em J3 e copie para baixo e para a direita conforme necessário.
=IFERROR(INDEX($2:$2,AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)),"None")
UPDATE-AExplicação
permitedividiressafórmulaemseuscomponentesindividuais
Vamoscomeçarcomafórmulaagregadaquetemalgumasfórmulasenterradasnela:
AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)
A razão pela qual eu tirei essa parte é que tudo o que acontece dentro dela é como operações de array. Vamos examinar um pouco mais a parte da partida e depois voltar de lá:
MATCH($I3,$A$3:$A$6,0)
Esta fórmula vai retornar um inteiro representando onde o valor em I3 (janeiro) está localizado na lista $ A $ 3: $ A $ 6. Em outras palavras, estamos tentando descobrir em qual linha da sua tabela de referência você quer estar olhando. Neste caso, é 1 para o primeiro item da lista. Se janeiro não estivesse na lista, ele retornaria um erro que se propagaria pelas fórmulas.
Em seguida, vamos ver a fórmula do índice:
INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)
INDEX(TABLE, ROWS TO GO DOWN, COLUMNS TO MOVE OVER)
Pense na fórmula do índice, defina sua tabela, desça as linhas Y e passe pelas colunas X. Nesse caso, $ B $ 3: $ H $ 6 representa seus dados sem rótulos de cabeçalho / linha. A fórmula MATCH está nos dizendo quantas linhas para descer. O 0 no final está fazendo um pequeno truque que é bem especial. Como você não pode passar por 0 colunas, neste caso especial de 0, INDEX interpreta isso como obter todas as colunas também conhecidas como a linha inteira da tabela definida. em uma nota similar acontece quando você usa 0 para quantas linhas descer.
O próximo passo é a operação e comparação da matriz.
INDEX() <> ""
Basicamente, ele passa por cada coluna e verifica se a entrada está em branco ou tem algo nela. Se estiver em branco, retornará FALSE e não em branco retornará TRUE. Agora, a razão pela qual fazemos isso é que o próximo passo será dividido pelo valor VERDADEIRO ou FALSO. O Excel converte VERDADEIRO e FALSO em 1 e 0, respectivamente, quando são enviados por meio de uma operação matemática. Então, duas coisas importantes acontecem aqui. Dividir por 1 não altera o valor de nada. essencialmente não faz nada. Dividir por 0 causa uma divisão por erro de 0, que irá se propagar. É realmente algo que precisamos. Agora, o que estamos realmente dividindo?
COLUMN($B$3:$H$3)
Isso ainda está na parte da matriz da fórmula agregada. Portanto, ele criará uma lista conforme o array percorre cada célula. Então, basicamente o que está acontecendo é que uma lista de números de coluna do SPREADSHEET está sendo gerada. e, mais importante, uma lista de números de colunas da planilha dividida por 1, que queremos, e dividida por 0, dando um erro que não queremos. Então, agora vamos ver o agregado:
AGGREGATE(15,6,COLUMN($B$3:$H$3)/(INDEX($B$3:$H$6,MATCH($I3,$A$3:$A$6,0),0)<>""),J$2)
AGGREGATE(Function number, OPERATION #, formula, Option value)
Basicamente, escolhemos a função 15, que classificará a lista de resultados da função e os ordenará do menor para o maior. A função 15 também força o agregado para executar operações de matriz. Nem todos os números de função serão. 6 informa agregado para IGNORE ERROR VALUES. Portanto, todos os erros divididos por 0 são ignorados, assim como uma correspondência de potencial não encontrada da função de correspondência. A fórmula discutida acima sobre o que está fazendo e o valor da opção nesse caso é qual posição do topo da lista você deseja. 1 retornaria o menor número, 2 o segundo menor número e assim por diante. Neste CASO eu configurei para olhar o número no cabeçalho em vez de codificá-lo na fórmula. No final, AGGREGATE, neste caso, retornará um único inteiro correspondente ao número da coluna SPREADSHEET que você está procurando. Então está de volta no ÍNDICE:
INDEX($2:$2,AGGREGATE(...))
Nesse caso, peguei toda a linha 2 como a tabela. Desde a sua única linha, não preciso fornecer linhas abaixo e colunas como na tabela anterior. Ele olha como uma lista e eu só preciso fornecer o quanto abaixo da lista eu quero ir. Portanto, como temos o número da coluna SPREADSHEET, precisamos examinar toda a linha da planilha em que o cabeçalho é, de forma que o número da coluna do spreadhseet retornado pelas linhas agregadas seja igual à tabela da planilha no índice.
No elenco onde ninguém é encontrado, ou seja, não há rank 1, Agregado retorna um erro. Para lidar com isso, usamos a função IFERROR. se não houver erro, a função opera normalmente e retorna seu resultado. Se houver um erro, ele retornará no nosso caso "nenhum".