Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Среднее арифметическое значение топ-5 максимальных видимых чисел
 
#1
26.11.2018 09:19:27
Добрый день, форумчане,

Подскажите пожалуйста, как найти Среднее арифмитическое значение топ-5 максимальных видимых значений чисел через функцию subtotal? без использования массива,

Пытался написать формулу, не работает: SUBTOTAL(1,LARGE(C2:C31,{1,2,3,4,5}))

Спасибо
Изменено: timur_kassymov - 26.11.2018 13:57:54
 
 
 
#2
26.11.2018 09:31:34
вариант массивная
Код
=СРЗНАЧ(НАИБОЛЬШИЙ(ЕСЛИ($B$2:$B$31="A";$C$2:$C$31);{1;2;3;4;5}))
Лень двигатель прогресса, доказано!!!
 
 
 
#3
26.11.2018 09:37:35
спасибо, но как изменится ваша формула если в ID больше 2 значений? я поэтому хочу через subtotal, спс
 
 
 
#4
26.11.2018 09:40:47
не пойму что не так ссылку на ячейку с ID делаете и все
Лень двигатель прогресса, доказано!!!
 
 
 
#5
26.11.2018 09:45:02
Цитата
timur_kassymov написал:
я поэтому хочу через subtotal
subtotal работает с диапазоном ячеек, а у вас массив.
 
 
 
#6
26.11.2018 09:45:23
Сергей, мне принципиально надо через subtotal, т.к. ID значениях у меня больше 10000.

Я правильно понял, что невозможно прописать мой случай без массива ???
 
 
 
#7
26.11.2018 09:50:49
Цитата
timur_kassymov написал: спасибо, но как изменится ваша формула если в ID больше 2 значений?
никак
Цитата
timur_kassymov написал: ID значениях у меня больше 10000
да хоть на весь лист
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#8
26.11.2018 09:53:33
Вам нужно среднее 5-и наибольших или нужно на спор написать с subtotal??
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#9
26.11.2018 10:06:28
для работы с фильтром массивная
Код
=СУММ(НАИБОЛЬШИЙ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C2;СТРОКА($C$2:$C$31)-2;;;));{1;2;3;4;5}))/5
Лень двигатель прогресса, доказано!!!
 
 
 
#10
26.11.2018 10:06:32
Ігор Гончаренко, мне нужно чтобы значения изменялись при использовании фильтра,  а не иметь очередное кол-во 10000 ячеек!

Спасибо
 
 
 
#11
26.11.2018 10:15:33
timur_kassymov, название темы нужно сменить, примерно так:
среднее арифметическое топ-5 видимых ячеек.
Попросите модераторов.
 
 
 
#12
26.11.2018 10:17:05
Михаил С.,

Да спасибо что заметили, я для этого и упоминул в теме функцию subtotal!!!!
 
 
 
#13
26.11.2018 10:20:46
Счас с андроида решение дать не могу, возможно вечером, если раньше никто не решит.
 
 
 
#14
26.11.2018 10:45:33
Цитата

в теме функцию subtotal!!!!
1. посмотрите сколько на форуме тем "помогите написать с ЕСЛИ"
когда в итоге функции ЕСЛИ вообще нет в решении
поэтому лично у меня - иммунитет на решить с помощью Х/З, решаю как умею.
2. лично у меня "топ 5 максимальных чисел" - ассоцировалось с 5-ю наибольшими, откуда мне знать что говоря о топ 5 максимальных, Вы говорите о 5 первых видимых значений в отфильтрованном списке?
3. подумайте почему Сергей в #2 написал среднее из 5-и наибольших? (не по то же причине?)
4. почему только в #12 Михаила осенило, что речь идет о топ 5-и видимых?
5. тут читают, что написано в теме и никто не читает Ваших мыслей, как бы сильно Вы не думали о верхних 5-и видимых, если написано о 5-и максимальных, то подскажут как посчитать среднее по ним, а не по тем 5-и на которых Вы смотрите в своем мониторе и думаете что именно их видят все
Изменено: Ігор Гончаренко - 26.11.2018 10:45:50
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#15
26.11.2018 10:55:26
ФМ!!!
=СРЗНАЧ(ЕСЛИОШИБКА(АГРЕГАТ(14;7;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;СМЕЩ($C$2:$C$31;СТРОКА($C$2:$C$31)-СТРОКА($1:$1)-1;0;1;1))/($B$2:$B$31="A");{1:2:3:4:5});""))
=СРЗНАЧ(ЕСЛИОШИБКА(АГРЕГАТ(14;7;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;СМЕЩ($C$2:$C$31;СТРОКА($C$2:$C$31)-СТРОКА($1:$1)-1;0;1;1))/($B$2:$B$31="B");{1:2:3:4:5});""))

+++
Забыл сказать, если отфильтровано + по условию будет менее 5ти значений, то недостающие значения будут заменены нулями. Имейте это ввиду!
+++
Да, ещё по названию уточнение:
Среднее арифметическое по пяти наибольшим видимым по условию значениям
+++
Формула "криво" работает со значениями менее 0.
Изменено: JayBhagavan - 26.11.2018 12:13:35

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#16
26.11.2018 11:27:46
А если такая формула (немассивный ввод)?
Код
=AVERAGE(AGGREGATE(14;6;C$2:C$31000/(B$2:B$31000=E1);{1;2;3;4;5}))
 
 
 
#17
26.11.2018 11:40:02
timur_kassymov, блин а че не так то с формулой из 9 сообщения работает по фильтру 5 наибольших из видимых
Лень двигатель прогресса, доказано!!!
 
 
 
#18
26.11.2018 11:55:28
Сергей, там ещё условие по столбцу с "ID".

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#19
26.11.2018 12:01:26
из примера единственное условие фильтрация по ид
Лень двигатель прогресса, доказано!!!
 
 
 
#20
26.11.2018 12:10:17
Сергей, пусть ТС решает, что ему на самом деле нужно.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#21
26.11.2018 12:32:00
timur_kassymov,
учитесь формулировать точные вопросы - это поможет получать точные ответы на них
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#22
26.11.2018 13:28:06
timur_kassymov, перечитайте #14 и #21 и предложите адекватное название темы - модераторы поменяют.
 
 
 
#23
26.11.2018 13:56:47
Юрий М,

Среднее арифметическое значение топ-5 максимальных видимых чисел без массива
Изменено: timur_kassymov - 26.11.2018 13:57:05
 
 
 
Страницы: 1
Наверх