Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

22 Июль 2019, 19:14 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Вы можете задать вопрос по LibreOffice или Apache OpenOffice  без регистрации, используя форму
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Формула - где максимум?  (Прочитано 5285 раз)
0 Пользователей и 1 Гость смотрят эту тему.
bas32
Новичок
*
Offline Offline

Сообщений: 3


« Стартовое сообщение: 11 Март 2011, 21:27 »

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

Пол: Мужской
Расположение: Киев
Сообщений: 2 764


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #1: 11 Март 2011, 22:35 »

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

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
bas32
Новичок
*
Offline Offline

Сообщений: 3


« Ответ #2: 11 Март 2011, 22:39 »

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

Пол: Мужской
Расположение: Киев
Сообщений: 2 764


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #3: 11 Март 2011, 23:13 »

Любопытство - не порок  Смеющийся

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

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
VlhOwn
Форумчанин
***
Offline Offline

Пол: Мужской
Расположение: Ростов-на-Дону
Сообщений: 1 076


« Ответ #4: 12 Март 2011, 00:27 »

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

Сообщений: 3


« Ответ #5: 12 Март 2011, 12:00 »

Спасибо, понял где копать.
Сделал:
а) =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
Капитана в тот день называли на "ты"
Гуру
*******
Offline Offline

Пол: Мужской
Расположение: Киев
Сообщений: 2 764


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #6: 12 Март 2011, 15:00 »

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, но в описании этой функции есть оговорка насчет упорядоченности данных по возрастанию.
« Последнее редактирование: 12 Март 2011, 15:03 от JohnSUN » Записан

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
VlhOwn
Форумчанин
***
Offline Offline

Пол: Мужской
Расположение: Ростов-на-Дону
Сообщений: 1 076


« Ответ #7: 12 Март 2011, 18:11 »

Спасибо. С sumproduct - замысловато, я чуть мозгами не тронулся, когда разбирал. Улыбка
Записан
JohnSUN
Капитана в тот день называли на "ты"
Гуру
*******
Offline Offline

Пол: Мужской
Расположение: Киев
Сообщений: 2 764


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #8: 12 Март 2011, 18:22 »

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

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!