Encontre o valor máximo em um intervalo - Retorna o conteúdo da célula na linha correspondente

Question

Eu tenho uma planilha que é composta de duas colunas - Cada linha contém uma data e, em seguida, uma leitura do medidor do rio. Os dados cobrem 20 anos - 1995-2015. Gostaria de uma fórmula que pesquise o nível máximo do rio dentro de um mês & ano, retorna a data correspondente. Para facilitar as coisas, adicionei duas colunas adicionais, que dividem a data à esquerda para mês e ano. No exemplo abaixo, eu gostaria de saber o dia da leitura máxima em janeiro de 2015. A resposta seria 29 de janeiro. Lembre-se: tenho a leitura do medidor para todos os dias de 1º de janeiro de 1995 a 1º de janeiro de 2015.

Column A - Full Date
Column B - Month 
Column C - Year 
Column D - Gauge Reading

January 29, 2015    1   2015    26.86
January 26, 2015    1   2015    26.84
January 27, 2015    1   2015    26.78
January 23, 2015    1   2015    26.7
January 25, 2015    1   2015    26.69
January 15, 2015    1   2015    26.68
January 24, 2015    1   2015    26.65
January 16, 2015    1   2015    14.09
January 17, 2015    1   2015    13.72

    

Answer 1

Parece que você pode usar uma fórmula de matriz combinando MAX e IF . Copiei seus dados e os transformei em uma tabela, para que a fórmula seja mais fácil de seguir. A fórmula gigante única é semelhante a essa (inserida com CTRL + SHIFT + ENTER ):

{=MIN(IF(Table1[Month]=1,IF(Table1[Year]=2015,IF(Table1[Value]=MAX(IF(Table1[Month]=1,IF(Table1[Year]=2015,Table1[Value]))),ROW(Table1[Value])))))}

Mas eu recomendo dividi-lo em dois só assim é mais fácil de seguir ( H5 na segunda fórmula refere-se à célula onde eu coloquei a primeira fórmula):

{=MAX(IF(Table1[Month]=1,IF(Table1[Year]=2015,Table1[Value])))}
{=MIN(IF(Table1[Month]=1,IF(Table1[Year]=2015,IF(Table1[Value]=H5,ROW(Table1[Value])))))}

A primeira fórmula encontra a leitura máxima no mês e & ano. A segunda fórmula encontra a primeira ocorrência dessa leitura nesse mês e ano. Isso significa que você não terá visibilidade para outras leituras no mesmo mês que aconteceram da mesma forma.

Observe que você pode ter uma tabela de todos os meses e alterar os valores codificados de 1 e 2015 para serem referências, para que você possa ter a mesma fórmula para tudo. Tudo isso apenas te dá o número da linha com os dados nele. Depois disso, você pode usar INDEX + MATCH ou LOOKUP ou OFFSET ou qualquer que seja sua função de pesquisa favorita.