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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск максимальных значений по выбранным критериям - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск максимальных значений по выбранным критериям (Формулы/Formulas)
Поиск максимальных значений по выбранным критериям
zuzikmba Дата: Вторник, 12.09.2017, 08:31 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
Добрый день!

Прошу помочь с интересной задачкой.

Необходимо найти три максимальные значения по выбранным критериям.

Искал на этом форуме по предыдущим темам (ничего такого не смог найти).
К сообщению приложен файл: 7588693.xlsx (10.7 Kb)
 
Ответить
СообщениеДобрый день!

Прошу помочь с интересной задачкой.

Необходимо найти три максимальные значения по выбранным критериям.

Искал на этом форуме по предыдущим темам (ничего такого не смог найти).
Автор - zuzikmba
Дата добавления - 12.09.2017 в 08:31
Pelena Дата: Вторник, 12.09.2017, 08:50 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19176
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Массивная формула
Код
=НАИБОЛЬШИЙ(ЕСЛИ(($B$6:$B$36=E7)*($A$6:$A$36=F7);$C$6:$C$36);СЧЁТЕСЛИМН($E$7:E7;E7;$F$7:F7;F7))

немассивная формула
Код
=АГРЕГАТ(14;6;$C$6:$C$36/($A$6:$A$36=F7)/($B$6:$B$36=E7);СЧЁТЕСЛИМН($E$7:E7;E7;$F$7:F7;F7))

UPD. Сводной ещё можно. Перевложила файл
К сообщению приложен файл: 1554314.xlsx (15.9 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Массивная формула
Код
=НАИБОЛЬШИЙ(ЕСЛИ(($B$6:$B$36=E7)*($A$6:$A$36=F7);$C$6:$C$36);СЧЁТЕСЛИМН($E$7:E7;E7;$F$7:F7;F7))

немассивная формула
Код
=АГРЕГАТ(14;6;$C$6:$C$36/($A$6:$A$36=F7)/($B$6:$B$36=E7);СЧЁТЕСЛИМН($E$7:E7;E7;$F$7:F7;F7))

UPD. Сводной ещё можно. Перевложила файл
Автор - Pelena
Дата добавления - 12.09.2017 в 08:50
buchlotnik Дата: Вторник, 12.09.2017, 09:04 | Сообщение № 3
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
как-то так
Код
=НАИБОЛЬШИЙ($C$6:$C$36*(E7=$B$6:$B$36)*(F7=$A$6:$A$36);СТРОКА(A1))
К сообщению приложен файл: 9708854.xlsx (11.3 Kb)
 
Ответить
Сообщениекак-то так
Код
=НАИБОЛЬШИЙ($C$6:$C$36*(E7=$B$6:$B$36)*(F7=$A$6:$A$36);СТРОКА(A1))
Автор - buchlotnik
Дата добавления - 12.09.2017 в 09:04
zuzikmba Дата: Вторник, 12.09.2017, 09:08 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
Все супер!

Спасибо всем огромное!
 
Ответить
СообщениеВсе супер!

Спасибо всем огромное!
Автор - zuzikmba
Дата добавления - 12.09.2017 в 09:08
zuzikmba Дата: Вторник, 12.09.2017, 09:16 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
За счет этого форума слыву на работе знатоком в Эксель )))
Спасибо еще раз за то что выручаете дельными советами!!!
 
Ответить
СообщениеЗа счет этого форума слыву на работе знатоком в Эксель )))
Спасибо еще раз за то что выручаете дельными советами!!!
Автор - zuzikmba
Дата добавления - 12.09.2017 в 09:16
zuzikmba Дата: Вторник, 12.09.2017, 09:39 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
А если нужно максимум вывести по сегменту и просуммировать по коду клиента
как это сделать?
К сообщению приложен файл: 3521808.xlsx (10.6 Kb)
 
Ответить
СообщениеА если нужно максимум вывести по сегменту и просуммировать по коду клиента
как это сделать?
Автор - zuzikmba
Дата добавления - 12.09.2017 в 09:39
Pelena Дата: Вторник, 12.09.2017, 09:46 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19176
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Так просуммировать или максимум? Это немного разные вещи

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТак просуммировать или максимум? Это немного разные вещи
Автор - Pelena
Дата добавления - 12.09.2017 в 09:46
_Boroda_ Дата: Вторник, 12.09.2017, 09:52 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Суммировать так можно
Код
=СУММЕСЛИМН(C:C;A:A;F7;B:B;E7)

Код
=СУММПРОИЗВ((A6:A36=F7)*(B6:B36=E7)*C6:C36)

Первая лучше
К сообщению приложен файл: 3521808_1.xlsx (11.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеСуммировать так можно
Код
=СУММЕСЛИМН(C:C;A:A;F7;B:B;E7)

Код
=СУММПРОИЗВ((A6:A36=F7)*(B6:B36=E7)*C6:C36)

Первая лучше
Автор - _Boroda_
Дата добавления - 12.09.2017 в 09:52
zuzikmba Дата: Вторник, 12.09.2017, 11:29 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
нужно по коду клиента просуммировать остатки и уже от просуммированных остатков найти максимумы
 
Ответить
Сообщениенужно по коду клиента просуммировать остатки и уже от просуммированных остатков найти максимумы
Автор - zuzikmba
Дата добавления - 12.09.2017 в 11:29
_Boroda_ Дата: Вторник, 12.09.2017, 11:32 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Это как? В файле покажите

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЭто как? В файле покажите
Автор - _Boroda_
Дата добавления - 12.09.2017 в 11:32
zuzikmba Дата: Вторник, 12.09.2017, 11:33 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
есть остатки по клиентам. например у одного есть один остаток у другого два, а у третьего три. Необходимо сравнить какой совокупный остаток по клиентам максимальный, и вывести наибольшие три максимальных совокупных остатков по клиентно. То есть сперва суммируем остатки по каждому клиенту потом только сравниваем эти остатки и находим клиента (изначально какой код клиента мы незнаем).
 
Ответить
Сообщениеесть остатки по клиентам. например у одного есть один остаток у другого два, а у третьего три. Необходимо сравнить какой совокупный остаток по клиентам максимальный, и вывести наибольшие три максимальных совокупных остатков по клиентно. То есть сперва суммируем остатки по каждому клиенту потом только сравниваем эти остатки и находим клиента (изначально какой код клиента мы незнаем).
Автор - zuzikmba
Дата добавления - 12.09.2017 в 11:33
zuzikmba Дата: Вторник, 12.09.2017, 11:33 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
сейчас пример скину
 
Ответить
Сообщениесейчас пример скину
Автор - zuzikmba
Дата добавления - 12.09.2017 в 11:33
_Boroda_ Дата: Вторник, 12.09.2017, 11:39 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А сегменты? не смотрим на них или и поклиентно, и посегментно?

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА сегменты? не смотрим на них или и поклиентно, и посегментно?
Автор - _Boroda_
Дата добавления - 12.09.2017 в 11:39
zuzikmba Дата: Вторник, 12.09.2017, 11:44 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
кодов клиента могут быть тысячи.
К сообщению приложен файл: 4667773.xlsx (11.4 Kb)
 
Ответить
Сообщениекодов клиента могут быть тысячи.
Автор - zuzikmba
Дата добавления - 12.09.2017 в 11:44
zuzikmba Дата: Вторник, 12.09.2017, 11:46 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
сегмент и в нем три клиента с максимальными совокупными остатками.
 
Ответить
Сообщениесегмент и в нем три клиента с максимальными совокупными остатками.
Автор - zuzikmba
Дата добавления - 12.09.2017 в 11:46
TimSha Дата: Вторник, 12.09.2017, 12:36 | Сообщение № 16
Группа: Проверенные
Ранг: Ветеран
Сообщений: 627
Репутация: 94 ±
Замечаний: 0% ±

Excel 2013 Pro +
Цитата zuzikmba, 12.09.2017 в 11:46, в сообщении № 15 ()
сегмент и в нем три клиента с максимальными совокупными остатками.

Вам Елена во втором посте предложила вариант сводной - почему и в этом случае его не применить - три наибольших в сегменте?!. ;)

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Ответить
Сообщение
Цитата zuzikmba, 12.09.2017 в 11:46, в сообщении № 15 ()
сегмент и в нем три клиента с максимальными совокупными остатками.

Вам Елена во втором посте предложила вариант сводной - почему и в этом случае его не применить - три наибольших в сегменте?!. ;)
Автор - TimSha
Дата добавления - 12.09.2017 в 12:36
zuzikmba Дата: Вторник, 12.09.2017, 12:37 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
если в файле что либо непонятно просьба написать.

Я старался максимально подробнее расписать.
 
Ответить
Сообщениеесли в файле что либо непонятно просьба написать.

Я старался максимально подробнее расписать.
Автор - zuzikmba
Дата добавления - 12.09.2017 в 12:37
and_evg Дата: Вторник, 12.09.2017, 12:40 | Сообщение № 18
Группа: Проверенные
Ранг: Обитатель
Сообщений: 452
Репутация: 78 ±
Замечаний: 0% ±

Excel 2007
zuzikmba, Чем не устраивает самый простой вариант - сводной таблицей?
К сообщению приложен файл: 6259798.xlsx (15.0 Kb)


Сообщение отредактировал and_evg - Вторник, 12.09.2017, 12:45
 
Ответить
Сообщениеzuzikmba, Чем не устраивает самый простой вариант - сводной таблицей?
Автор - and_evg
Дата добавления - 12.09.2017 в 12:40
zuzikmba Дата: Вторник, 12.09.2017, 12:50 | Сообщение № 19
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 0% ±

Excel 2010
количество строк и столбцов динамичные (постоянно меняются). поэтому думал если есть возможность зафиксировать через формулы то так и сделать.
Если уж совсем формулами нереально сделать то конечно придется через сводную таблицу решать этот вопрос.
 
Ответить
Сообщениеколичество строк и столбцов динамичные (постоянно меняются). поэтому думал если есть возможность зафиксировать через формулы то так и сделать.
Если уж совсем формулами нереально сделать то конечно придется через сводную таблицу решать этот вопрос.
Автор - zuzikmba
Дата добавления - 12.09.2017 в 12:50
_Boroda_ Дата: Вторник, 12.09.2017, 12:51 | Сообщение № 20
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Остаток
Код
=НАИБОЛЬШИЙ(СУММЕСЛИМН(C$4:C$37;A$4:A$37;A$4:A$37;A$4:A$37;L4;B$4:B$37;B$4:B$37);1+СУММПРОИЗВ(СЧЁТЕСЛИМН(A$4:A$37;L4;B$4:B$37;ЕСЛИ(L$3:L3=L4;M$3:M3))))

Если остатки всегда различны, то
Код
=ИНДЕКС(B$4:B$37;ПОИСКПОЗ(N4;СУММЕСЛИМН(C$4:C$37;A$4:A$37;A$4:A$37;A$4:A$37;L4;B$4:B$37;B$4:B$37);))

Если могут совпадать, то
Код
=ИНДЕКС(B$4:B$37;ПОИСКПОЗ(НАИБОЛЬШИЙ(СУММЕСЛИМН(C$4:C$37;A$4:A$37;A$4:A$37;A$4:A$37;P4;B$4:B$37;B$4:B$37)+ПОИСКПОЗ(B$4:B$37;B$4:B$37;)%%;1+СУММПРОИЗВ(СЧЁТЕСЛИМН(A$4:A$37;P4;B$4:B$37;ЕСЛИ(P$3:P3=P4;Q$3:Q3))));СУММЕСЛИМН(C$4:C$37;A$4:A$37;A$4:A$37;A$4:A$37;P4;B$4:B$37;B$4:B$37)+ПОИСКПОЗ(B$4:B$37;B$4:B$37;)%%;))


А сводной да, попроще конечно
В файле ниже ею тоже

==========================
Цитата zuzikmba, 12.09.2017 в 12:50, в сообщении № 19 ()
количество строк и столбцов динамичные

Если Вы про динамический диапазон для сводной, то сделал его Вам для произвольного кол-ва строк. А столбцы-то с чего вдруг меняются?
Файл перевложил
К сообщению приложен файл: 4667773_1.xlsx (17.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Остаток
Код
=НАИБОЛЬШИЙ(СУММЕСЛИМН(C$4:C$37;A$4:A$37;A$4:A$37;A$4:A$37;L4;B$4:B$37;B$4:B$37);1+СУММПРОИЗВ(СЧЁТЕСЛИМН(A$4:A$37;L4;B$4:B$37;ЕСЛИ(L$3:L3=L4;M$3:M3))))

Если остатки всегда различны, то
Код
=ИНДЕКС(B$4:B$37;ПОИСКПОЗ(N4;СУММЕСЛИМН(C$4:C$37;A$4:A$37;A$4:A$37;A$4:A$37;L4;B$4:B$37;B$4:B$37);))

Если могут совпадать, то
Код
=ИНДЕКС(B$4:B$37;ПОИСКПОЗ(НАИБОЛЬШИЙ(СУММЕСЛИМН(C$4:C$37;A$4:A$37;A$4:A$37;A$4:A$37;P4;B$4:B$37;B$4:B$37)+ПОИСКПОЗ(B$4:B$37;B$4:B$37;)%%;1+СУММПРОИЗВ(СЧЁТЕСЛИМН(A$4:A$37;P4;B$4:B$37;ЕСЛИ(P$3:P3=P4;Q$3:Q3))));СУММЕСЛИМН(C$4:C$37;A$4:A$37;A$4:A$37;A$4:A$37;P4;B$4:B$37;B$4:B$37)+ПОИСКПОЗ(B$4:B$37;B$4:B$37;)%%;))


А сводной да, попроще конечно
В файле ниже ею тоже

==========================
Цитата zuzikmba, 12.09.2017 в 12:50, в сообщении № 19 ()
количество строк и столбцов динамичные

Если Вы про динамический диапазон для сводной, то сделал его Вам для произвольного кол-ва строк. А столбцы-то с чего вдруг меняются?
Файл перевложил
Автор - _Boroda_
Дата добавления - 12.09.2017 в 12:51
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск максимальных значений по выбранным критериям (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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