Формула - где максимум?

[bas32]

Две колонки: дата и температура. Рядом ячейка с макс. значением температуры (эту формулу знаю), и ячейка с датой соответствующей макс. значению температуры. Как это можно получить?

[JohnSUN]

С первой из них? Или с последней? На случай, если одна и та же температура была несколько раз...

[bas32]

С первой из них? Или с последней? На случай, если одна и та же температура была несколько раз...

Интересны все варианты.

[JohnSUN]

Любопытство - не порок 

[вложение удалено Администратором]

[VlhOwn]

JohnSUN,
1. Для чего MAX(ROUND(SUMPRODUCT(... ?
2. Попробуй в предложенном файле перед первой строкой вставить одну/несколько строк. Результат меняется?

[bas32]

Спасибо, понял где копать.
Сделал:
а) =LOOKUP(E2;B2:B253;A2:A253) - последний, но кратко.
б) 1) =INDEX(A2:A253;MATCH(E2;B2:B253;0);1;1) - первый, но уже длиней.
б) 2) =INDEX(A2:A253;MATCH(E2;B2:B253;1);1;1) - последний.

[JohnSUN]

1. Для чего MAX(ROUND(SUMPRODUCT(... ?

Это была шутка... Просто показал, что для решения этой задачи можно использовать сколь угодно запутанный способ. Например, внешний MAX там совершенно не нужен (максимум от одного параметра - сам этот параметр)...
А всё остальное?
($B$2:$B$1000=E2) - понятно: или Ложь (0), или Истина(1)
Этот ноль или единицу умножаем на номер строки.
Получаем длинный массив, состоящий из большого количества нулей и отдельных номеров строк (для этого набора данных это 8, 24, 60, 240).
MAX по идее возвращает самое большое значение из них - 240. Но для этого пришлось бы объявить функцию массивом, нажать Ctrl+Shift+Enter и, украсив формулу фигурными скобками, заполучить трудноуправляемую конструкцию.

Код Выделить Развернуть

{=INDEX($A$2:$A$1000;MAX(($B$2:$B$1000=E2)*ROW($B$2:$B$1000))-ROW(A2);1)}
Кому как, а мне формулы массива не нравятся. Особенно если они занимают большой диапазон ячеек... Не удобно с ними работать, все время приходиться жать Ctrl+/ чтобы выделить весь массив, не забывать заканчивать формулу Ctrl+Shift+Enter и прочее... (Это конечно компенсируется другой особенностью "массивных функций" - для всего диапазона результирующих ячеек используется всего одна формула, но в нашем примере ячейка результата всего одна)
Поэтому подсовываем наш MAX в качестве параметра в SUMPRODUCT. Чудная функция: берет в качестве параметра массив(ы), а возвращает одно значение. И значит можно обойтись без фигурных скобок.
Но есть у SUMPRODUCT один мелкий недостаток - функция предназначена для работы с произведениями вещественных чисел. И потому сплошь и рядом вместо ожидаемых 5.0 можно получить 4.99999999989... Поэтому саму SUMPRODUCT оборачиваем в ROUND, чтобы номер строки, который подсунем потом функции INDEX был целым... Поскольку множим 0 или 1 на целое число (номер строки), то вероятность возникновения такого значения невелика и можно обойтись без ROUND... Но я все-таки предпочитаю не рисковать
Ну, дальше понятно - INDEX работает так же как и в соседней ячейке...

2. Попробуй в предложенном файле перед первой строкой вставить одну/несколько строк. Результат меняется?

- Доктор, когда я делаю вот так, у меня ломит в пояснице...
- А вы не делайте "вот так"...

Вся эта длинная безумная конструкция вычисляет максимальный номер строки, в которой встречается нужное значение. А в INDEX нужно передать относительную позицию от начала массива. Наш массив данных начинается со второй строки, поэтому из вычисленного номера строки вычитаем единичку и получаем нужный индекс. И вот тут провокация! Нам добавляют строк перед массивом и соответственно поправка будет уже не единица, а немного больше... Это обходиться легко: вместо единички пишем ROW(A1), то есть "номер строки с заголовком массива"... Теперь, если будут добавляться строки перед данными, поправка автоматически увеличиться.
Значит, окончательный вариант запутанной формулы должен выглядеть так:

Код Выделить Развернуть

=INDEX($A$2:$A$1000;ROUND(SUMPRODUCT(MAX(($B$2:$B$1000=E2)*(ROW($B$2:$B$1000)))-ROW(A1);1)))
Можно конечно использовать и MATCH с параметром Тип=1, но в описании этой функции есть оговорка насчет упорядоченности данных по возрастанию.

[VlhOwn]

Спасибо. С sumproduct - замысловато, я чуть мозгами не тронулся, когда разбирал.

[JohnSUN]

Это всё ken johnson виноват!  Тот самый Ken Johnson (надо же! почти тёзка  ) из Сиднея, на которого иногда ссылается Питоньяк в своих книгах...