Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Найти наименьшее с условием обычными формулами - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти наименьшее с условием обычными формулами (Формулы)
Найти наименьшее с условием обычными формулами
Sineril Дата: Воскресенье, 29.12.2013, 03:41 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 80% ±

Excel 2010
Время доброе!
Есть 2 столбца: первый - с условиями, второй - со значениями. Требуется найти наименьшее значение во втором столбце, учитывая условие первого (т.е. наименование) без использования формул массива.
Столкнулся с, казалось бы, тривиальной задачей, но самостоятельно выход не нашёл. Долго роясь в интернетах, находил либо формулы массивов, либо неработающие. Но задачку с нахождением наибольшего значения решить удалось следующей формулой:
Код
=СУММПРОИЗВ(НАИБОЛЬШИЙ(B$2:B$12*(A$2:A$12=A3);1))


Заранее благодарю!
К сообщению приложен файл: 0976629.xls (26.0 Kb)


Сообщение отредактировал Serge_007 - Воскресенье, 29.12.2013, 15:08
 
Ответить
СообщениеВремя доброе!
Есть 2 столбца: первый - с условиями, второй - со значениями. Требуется найти наименьшее значение во втором столбце, учитывая условие первого (т.е. наименование) без использования формул массива.
Столкнулся с, казалось бы, тривиальной задачей, но самостоятельно выход не нашёл. Долго роясь в интернетах, находил либо формулы массивов, либо неработающие. Но задачку с нахождением наибольшего значения решить удалось следующей формулой:
Код
=СУММПРОИЗВ(НАИБОЛЬШИЙ(B$2:B$12*(A$2:A$12=A3);1))


Заранее благодарю!
Автор - Sineril
Дата добавления - 29.12.2013 в 03:41
AndreTM Дата: Воскресенье, 29.12.2013, 04:16 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
Можно так:
Код
=1/ПРОСМОТР(2;1/(B$2:B$12*(A$2:A$12=A2)))

Если ориентироваться на уже найденную вами формулу (если вы понимаете принцип её работы), то так:
Код
=1/СУММПРОИЗВ(НАИБОЛЬШИЙ(1/B$2:B$12*(A$2:A$12=A2);1))

Skype: andre.tm.007
Donate: Qiwi: 9517375010

Сообщение отредактировал AndreTM - Воскресенье, 29.12.2013, 04:25
 
Ответить
СообщениеМожно так:
Код
=1/ПРОСМОТР(2;1/(B$2:B$12*(A$2:A$12=A2)))

Если ориентироваться на уже найденную вами формулу (если вы понимаете принцип её работы), то так:
Код
=1/СУММПРОИЗВ(НАИБОЛЬШИЙ(1/B$2:B$12*(A$2:A$12=A2);1))
Автор - AndreTM
Дата добавления - 29.12.2013 в 04:16
OVN Дата: Воскресенье, 29.12.2013, 09:21 | Сообщение № 3
Группа: Гости
"Столкнулся с, казалось бы, тривиальной задачей..."

Согласен! Строим сводную, в поле значений дважды помещаем "Оценка" - для МИН и МАКС.
 
Ответить
Сообщение"Столкнулся с, казалось бы, тривиальной задачей..."

Согласен! Строим сводную, в поле значений дважды помещаем "Оценка" - для МИН и МАКС.
Автор - OVN
Дата добавления - 29.12.2013 в 09:21
AndreTM Дата: Воскресенье, 29.12.2013, 09:46 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
Цитата OVN, 29.12.2013 в 09:21, в сообщении № 3
Строим сводную, в поле значений ...
Человеку запретили даже формулы массива - какие сводные?? :)

Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
Сообщение
Цитата OVN, 29.12.2013 в 09:21, в сообщении № 3
Строим сводную, в поле значений ...
Человеку запретили даже формулы массива - какие сводные?? :)
Автор - AndreTM
Дата добавления - 29.12.2013 в 09:46
Sineril Дата: Воскресенье, 29.12.2013, 13:20 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 80% ±

Excel 2010
AndreTM,

1/sumproduct - это гениально! Всё оказалось предельно просто. Спасибо за оперативный и шикарный ответ!

З.Ы. Ну какбэ мне не запрещали, просто не люблю работать с массивами. Всё в таблице дополняемо и заменяемо + не вижу особого удобства работы с массивами.
 
Ответить
СообщениеAndreTM,

1/sumproduct - это гениально! Всё оказалось предельно просто. Спасибо за оперативный и шикарный ответ!

З.Ы. Ну какбэ мне не запрещали, просто не люблю работать с массивами. Всё в таблице дополняемо и заменяемо + не вижу особого удобства работы с массивами.
Автор - Sineril
Дата добавления - 29.12.2013 в 13:20
ShAM Дата: Воскресенье, 29.12.2013, 13:23 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1347
Репутация: 249 ±
Замечаний: 0% ±

Excel 2010
Ну, СУММПРОИЗВ тоже работает с массивами. Просто трехпальцевого ввода не требуется.
 
Ответить
СообщениеНу, СУММПРОИЗВ тоже работает с массивами. Просто трехпальцевого ввода не требуется.
Автор - ShAM
Дата добавления - 29.12.2013 в 13:23
AndreTM Дата: Воскресенье, 29.12.2013, 13:51 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
Цитата ShAM, 29.12.2013 в 13:23, в сообщении № 6
Просто трехпальцевого ввода не требуется
Вы, пожалуйста, не путайте неофитов :)
СУММПРОИЗВ() работает с любыми значениями (не только массивами) - главное, чтобы соблюдались определённые условия. Но возвращает она конкретное значение - поэтому и не требует ввода как формулы массива. Иногда, конечно, массивный ввод требуется - но исключительно из-за функций, входящих в её параметры.

Для примера (общего, не относящегося именно к СУММПРОИЗВ): формула, вычисляющая количество строк в диапазоне, содержащих в любых двух ячейках единственную последовательность из чисел (значения указаны в J1 и K1)
Код
=СУММ(--(МУМНОЖ(--(B2:G17=J1);СТРОКА(2:7))*МУМНОЖ((B2:G17=K1)-(B2:G17=J1);СТРОКА(2:7))>0))
не требует массивного ввода, хотя, вроде бы по всем канонам - должна :D

Skype: andre.tm.007
Donate: Qiwi: 9517375010

Сообщение отредактировал AndreTM - Воскресенье, 29.12.2013, 13:55
 
Ответить
Сообщение
Цитата ShAM, 29.12.2013 в 13:23, в сообщении № 6
Просто трехпальцевого ввода не требуется
Вы, пожалуйста, не путайте неофитов :)
СУММПРОИЗВ() работает с любыми значениями (не только массивами) - главное, чтобы соблюдались определённые условия. Но возвращает она конкретное значение - поэтому и не требует ввода как формулы массива. Иногда, конечно, массивный ввод требуется - но исключительно из-за функций, входящих в её параметры.

Для примера (общего, не относящегося именно к СУММПРОИЗВ): формула, вычисляющая количество строк в диапазоне, содержащих в любых двух ячейках единственную последовательность из чисел (значения указаны в J1 и K1)
Код
=СУММ(--(МУМНОЖ(--(B2:G17=J1);СТРОКА(2:7))*МУМНОЖ((B2:G17=K1)-(B2:G17=J1);СТРОКА(2:7))>0))
не требует массивного ввода, хотя, вроде бы по всем канонам - должна :D
Автор - AndreTM
Дата добавления - 29.12.2013 в 13:51
Serge_007 Дата: Воскресенье, 29.12.2013, 15:08 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Андрей, Алишер имел ввиду, что СУММПРОИЗВ, при наличии в ней логических критериев, полученных в результате вычислений, практически ничем не отличается от массивной СУММ(ЕСЛИ()), однако не требует массивного ввода

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеАндрей, Алишер имел ввиду, что СУММПРОИЗВ, при наличии в ней логических критериев, полученных в результате вычислений, практически ничем не отличается от массивной СУММ(ЕСЛИ()), однако не требует массивного ввода
Автор - Serge_007
Дата добавления - 29.12.2013 в 15:08
SergeyKorotun Дата: Воскресенье, 29.12.2013, 20:35 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 301
Репутация: 15 ±
Замечаний: 0% ±

Excel 2007
Цитата AndreTM, 29.12.2013 в 04:16, в сообщении № 2
Можно так:
Код
=1/ПРОСМОТР(2;1/(B$2:B$12*(A$2:A$12=A2)))
Если ориентироваться на уже найденную вами формулу (если вы понимаете принцип её работы), то так:
Код
=1/СУММПРОИЗВ(НАИБОЛЬШИЙ(1/B$2:B$12*(A$2:A$12=A2);1))
Первая формула не всегда дает правильный результат (см. вложение). Массив наверно должен быть упорядочен в порядке убывания.
К сообщению приложен файл: qwerty14.xls (50.0 Kb)
 
Ответить
Сообщение
Цитата AndreTM, 29.12.2013 в 04:16, в сообщении № 2
Можно так:
Код
=1/ПРОСМОТР(2;1/(B$2:B$12*(A$2:A$12=A2)))
Если ориентироваться на уже найденную вами формулу (если вы понимаете принцип её работы), то так:
Код
=1/СУММПРОИЗВ(НАИБОЛЬШИЙ(1/B$2:B$12*(A$2:A$12=A2);1))
Первая формула не всегда дает правильный результат (см. вложение). Массив наверно должен быть упорядочен в порядке убывания.
Автор - SergeyKorotun
Дата добавления - 29.12.2013 в 20:35
ShAM Дата: Воскресенье, 29.12.2013, 21:10 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1347
Репутация: 249 ±
Замечаний: 0% ±

Excel 2010
Цитата Serge_007, 29.12.2013 в 15:08, в сообщении № 8
Андрей, Алишер имел ввиду, что СУММПРОИЗВ, при наличии в ней логических критериев, полученных в результате вычислений, практически ничем не отличается от массивной СУММ(ЕСЛИ()), однако не требует массивного ввода
yes
Хотел ее назвать массивной, но где-то (не помню) читал тему-дискуссию о том, что такое массивная функция, поэтому не рискнул :)
 
Ответить
Сообщение
Цитата Serge_007, 29.12.2013 в 15:08, в сообщении № 8
Андрей, Алишер имел ввиду, что СУММПРОИЗВ, при наличии в ней логических критериев, полученных в результате вычислений, практически ничем не отличается от массивной СУММ(ЕСЛИ()), однако не требует массивного ввода
yes
Хотел ее назвать массивной, но где-то (не помню) читал тему-дискуссию о том, что такое массивная функция, поэтому не рискнул :)
Автор - ShAM
Дата добавления - 29.12.2013 в 21:10
AndreTM Дата: Понедельник, 30.12.2013, 12:13 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
Цитата SergeyKorotun, 29.12.2013 в 20:35, в сообщении № 9
Первая формула не всегда дает правильный результат
Да, это я поторопился... она возвращает последнее из значений, просто совпало так на примере :)

Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
Сообщение
Цитата SergeyKorotun, 29.12.2013 в 20:35, в сообщении № 9
Первая формула не всегда дает правильный результат
Да, это я поторопился... она возвращает последнее из значений, просто совпало так на примере :)
Автор - AndreTM
Дата добавления - 30.12.2013 в 12:13
_Boroda_ Дата: Понедельник, 30.12.2013, 14:07 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 16792
Репутация: 6557 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Я вот долго думал, а в чем же тайный смысл конструкции НАИБОЛЬШИЙ(...;1)? Так и не нашел разницы между ним и МАКС
Код
=СУММПРОИЗВ(МАКС(B$2:B$12*(A$2:A$12=A2)))

и, соответственно
Код
=1/СУММПРОИЗВ(МАКС(1/B$2:B$12*(A$2:A$12=A2)))

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЯ вот долго думал, а в чем же тайный смысл конструкции НАИБОЛЬШИЙ(...;1)? Так и не нашел разницы между ним и МАКС
Код
=СУММПРОИЗВ(МАКС(B$2:B$12*(A$2:A$12=A2)))

и, соответственно
Код
=1/СУММПРОИЗВ(МАКС(1/B$2:B$12*(A$2:A$12=A2)))
Автор - _Boroda_
Дата добавления - 30.12.2013 в 14:07
AndreTM Дата: Понедельник, 30.12.2013, 18:06 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
Цитата _Boroda_, 30.12.2013 в 14:07, в сообщении № 12
в чем же тайный смысл конструкции
Ну, мало ли. Я исходил из того, что ТС разобрался с работой исходной формулы именно в том виде, в котором привёл её (о чём и упомянул). И постарался показать, что именно она же решает и поставленную задачу - достаточно только скормить ей нужный набор данных.

Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
Сообщение
Цитата _Boroda_, 30.12.2013 в 14:07, в сообщении № 12
в чем же тайный смысл конструкции
Ну, мало ли. Я исходил из того, что ТС разобрался с работой исходной формулы именно в том виде, в котором привёл её (о чём и упомянул). И постарался показать, что именно она же решает и поставленную задачу - достаточно только скормить ей нужный набор данных.
Автор - AndreTM
Дата добавления - 30.12.2013 в 18:06
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти наименьшее с условием обычными формулами (Формулы)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!