Страницы: 1
RSS
Наибольшие значение с множеством условий, Прописать формулу наибольших условий с множеством условий
 
Добрый день.

Прошу помочь прописать формулу.

Дано:
1. Данные по месяцам по дням по складу КЦБ (данные за целый год).
2. Дана максимальная отгрузка 159600

Задача:
Посчитать по трем месяцам в отдельности среднее значение 5ти наибольших чисел, которые больше максимальной отгрузки 159600.

Пыталась решить задачку с помощью функций срзначеслимн и наибольший, но формула не срабатывает.
Изменено: Галина Головко - 24.05.2022 17:51:43
 
Попробуйте так, ничего в голову лучше не пришло
Код
=СРЗНАЧ(ЕСЛИ(НАИБОЛЬШИЙ(--(МЕСЯЦ($D$2:$D$366)=K7)*$E$2:$E$366;СТРОКА($1:$5))>$K$4;НАИБОЛЬШИЙ(--(МЕСЯЦ($D$2:$D$366)=K7)*$E$2:$E$366;СТРОКА($1:$5))))

Или если 2019 и выше
Код
=СРЗНАЧ(ФИЛЬТР(НАИБОЛЬШИЙ(--(МЕСЯЦ($D$2:$D$366)=K7)*$E$2:$E$366;СТРОКА($1:$5));НАИБОЛЬШИЙ(--(МЕСЯЦ($D$2:$D$366)=K7)*$E$2:$E$366;СТРОКА($1:$5))>K4))
Изменено: Msi2102 - 24.05.2022 18:58:21
 
формула массива:
Код
=ЕСЛИ(СУММПРОИЗВ((МЕСЯЦ($D$2:$D$366)=H7)*($E$2:$E$366>$H$4))<5;"Нет столько больших чисел";(СУММЕСЛИМН($E$2:$E$366;$C$2:$C$366;H7;$E$2:$E$366;">" & НАИБОЛЬШИЙ(ЕСЛИ(МЕСЯЦ($D$2:$D$366)=H7;$E$2:$E$366);5))+(5-СЧЁТЕСЛИМН($C$2:$C$366;H7;$E$2:$E$366;">" & НАИБОЛЬШИЙ(ЕСЛИ(МЕСЯЦ($D$2:$D$366)=H7;$E$2:$E$366);5)))*НАИБОЛЬШИЙ(ЕСЛИ(МЕСЯЦ($D$2:$D$366)=H7;$E$2:$E$366);5))/5)
Изменено: Ігор Гончаренко - 24.05.2022 19:43:34
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
написал:
формула массива:
Код
    [URL=#]?[/URL]       1      =ЕСЛИ(СУММПРОИЗВ((МЕСЯЦ($D$2:$D$366)=H7)*($E$2:$E$366>$H$4))<5;  "Нет столько больших чисел"  ;(СУММЕСЛИМН($E$2:$E$366;$C$2:$C$366;H7;$E$2:$E$366;  ">"   & НАИБОЛЬШИЙ(ЕСЛИ(МЕСЯЦ($D$2:$D$366)=H7;$E$2:$E$366);5))+(5-СЧЁТЕСЛИМН($C$2:$C$366;H7;$E$2:$E$366;  ">"   & НАИБОЛЬШИЙ(ЕСЛИ(МЕСЯЦ($D$2:$D$366)=H7;$E$2:$E$366);5)))*НАИБОЛЬШИЙ(ЕСЛИ(МЕСЯЦ($D$2:$D$366)=H7;$E$2:$E$366);5))/5)   
 
Для чего в формуле прописано из даты определить месяц, если номер месяца есть в исходных данных?  
Изменено: Галина Головко - 24.05.2022 19:51:37
 
вычисление месяца в формуле написано, потому что я его вычислил а только потом заметил, что есть отдельная колонка с месяцами
Изменено: Ігор Гончаренко - 24.05.2022 19:57:46
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, Не совсем подходит данная формула. Если 5 чисел не набирается, необходимо среднее посчитать по 4, 3 или 2.  
 
Цитата
написал:
Попробуйте так, ничего в голову лучше не пришло
Код
    [URL=#]?[/URL]       1      =СРЗНАЧ(ЕСЛИ(НАИБОЛЬШИЙ(--(МЕСЯЦ($D$2:$D$366)=K7)*$E$2:$E$366;СТРОКА($1:$5))>$K$4;НАИБОЛЬШИЙ(--(МЕСЯЦ($D$2:$D$366)=K7)*$E$2:$E$366;СТРОКА($1:$5))))   
 
Или если 2019 и выше
Код
    [URL=#]?[/URL]       1      =СРЗНАЧ(ФИЛЬТР(НАИБОЛЬШИЙ(--(МЕСЯЦ($D$2:$D$366)=K7)*$E$2:$E$366;СТРОКА($1:$5));НАИБОЛЬШИЙ(--(МЕСЯЦ($D$2:$D$366)=K7)*$E$2:$E$366;СТРОКА($1:$5))>K4))   
 
Я могу заменить закреплённые диапазоны на выбор всего столбца или не сработает? Данные в таблице могут меняться. Их может стать как больше так и меньше....  
 
это вы сейчас придумали, а раньше об этом что-то было написано?
условия задачи я брал из сообщения 1
и определял среднее из 5-и наибольших чисел за месяц, превышающих 159600
если у вас задача взять среднее из 5 чисел а вам дали только 4 числа - у задачи нет решения, кривые данные

выделенное жирным курсивом отлично подходит для названия темы, нет у вас в задаче никакого множества условий
Изменено: Ігор Гончаренко - 24.05.2022 20:08:59
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
написал:
это вы сейчас придумали, а раньше об этом что-то было написано?
условия задачи я брал из сообщения 1
и определял   среднее из 5-и наибольших чисел за месяц, превышающих 159600  
если у вас задача взять среднее из 5 чисел а вам дали только 4 числа - у задачи нет решения, кривые данные

выделенное жирным курсивом отлично подходит для названия темы, нет у вас в задаче никакого множества условий
Никто нечего не придумывал, задачка из жизни)
Есть множество условий - это определённый месяц, числа больше 159600 и наибольшие.

Ваше решение имеет место быть, спасибо вам за ваш труд. К сожалению мне оно не совсем подходит.


Решение от Msi2102 подходит, но как бы переписать формулу что бы при изменении строк в большую или меньшую сторону, не нужно было руками пере прописывать закрепленные строки?
Изменено: Галина Головко - 24.05.2022 20:25:49
 
Может так устроит?
Код
=AVERAGE(IFERROR(LARGE(IF((MONTH($D$2:$D$5000)=K7)*($E$2:$E$5000<>"")*($E$2:$E$5000>$K$4);$E$2:$E$5000);ROW($1:$5));""))
 
Цитата
Галина Головко написал:
не нужно было руками пере прописывать закрепленные строки
попробуйте использовать умные таблицы
 
=СРЗНАЧ(ЕСЛИОШИБКА(НАИБОЛЬШИЙ(ЕСЛИ(($C$2:$C$366=K7)*($E$2:$E$366>=$K$4);$E$2:$E$366);СТРОКА($H$1:$H$5));""))
Страницы: 1
Наверх