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

Автор bas32, 11 марта 2011, 21:27

0 Пользователи и 1 гость просматривают эту тему.

bas32

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

JohnSUN

С первой из них? Или с последней? На случай, если одна и та же температура была несколько раз...
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

bas32

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

JohnSUN

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

[вложение удалено Администратором]
Владислав Орлов aka 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

#6
Цитата: VlhOwn от 12 марта 2011, 00:27
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 работает так же как и в соседней ячейке...

Цитата: VlhOwn от 12 марта 2011, 00:27
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, но в описании этой функции есть оговорка насчет упорядоченности данных по возрастанию.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

VlhOwn

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

JohnSUN

Это всё ken johnson виноват!  :roll: Тот самый Ken Johnson (надо же! почти тёзка  ;D ) из Сиднея, на которого иногда ссылается Питоньяк в своих книгах...
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне