Найти максимальное значение в диапазоне - вернуть содержимое ячейки в соответствующей строке

Question

У меня есть электронная таблица, которая состоит из 2 столбцов - каждая строка содержит дату, а затем показание индикатора реки. Данные за 20 лет - 1995-2015. Мне нужна формула, которая ищет максимальный уровень реки в течение месяца и года, а затем возвращает соответствующую дату. Чтобы упростить задачу, я добавил две дополнительные колонки, которые разбивают дату слева на месяц и год. В приведенном ниже примере я хотел бы знать день максимального чтения в январе 2015 года. Ответ будет 29 января. Имейте в виду, у меня есть показания датчика для каждого дня с 1 января 1995 года по 1 января 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

Похоже, вы можете использовать формулу массива, комбинирующую MAX и IF . Я скопировал ваши данные и превратил их в таблицу, чтобы легче было следовать формуле. Одиночная гигантская формула выглядит следующим образом (ввод с помощью 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])))))}

Но я рекомендую разбить его на две части, чтобы было легче следовать (H5 во второй формуле относится к ячейке, в которую я поместил первую формулу):

{=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])))))}

Первая формула находит максимальное значение за данный месяц и год. Вторая формула находит первый экземпляр этого чтения в этом месяце и году. Это означает, что вы не будете видеть другие показания в том же месяце, которые оказались такими же.

Обратите внимание, что вы можете иметь таблицу всех месяцев и изменить жестко запрограммированные значения 1 и 2015 на ссылки, чтобы вы могли иметь одну и ту же формулу для всего. Все это просто дает вам номер строки с данными в нем. После этого вы можете использовать INDEX+MATCH или LOOKUP или OFFSET или любую другую любимую функцию поиска.