Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Вычислить среднее и максимальное значения по нескольким столбцам учитывая 2 условия, Вычислить среднее и максимальное значения по нескольким столбцам учитывая 2 условия
 
#1
30.08.2022 16:05:58
Добрый день,

Помогите, пожалуйста, вычислить среднее и максимальное значения по нескольким столбцам учитывая 2 условия, весь дипазон отмечен желтым, то есть нужно понять не помесячно среднее и максимальное, а за весь период и чтоб не затрагивало нули при вычислении

Спасибо!
Изменено: LordHenry - 30.08.2022 16:22:55
 
 
 
#2
30.08.2022 16:39:40
Код
E13    =СУММ(($A$2:$A$7=C13)*($B$2:$B$7=D13)*($F$2:$AK$7))/СУММ(($A$2:$A$7=C13)*($B$2:$B$7=D13)*($F$2:$AK$7>0))
F13    =МАКС(($A$2:$A$7=C13)*($B$2:$B$7=D13)*($F$2:$AK$7))
Вводить как формулу массива Ctrl+Shift+Enter.
Изменено: МатросНаЗебре - 30.08.2022 16:40:22
 
 
 
#3
30.08.2022 16:51:00
ещё в копилку
Среднее
=СУММПРОИЗВ((($B$2:$B$7=$D13)*($A$2:$A$7=$C13)*$F$2:$AK$7))/СУММПРОИЗВ((($B$2:$B$7=$D13)*($A$2:$A$7=$C13)*($F$2:$AK$7<>0)))
Максимальное
=АГРЕГАТ(14;7;$F$2:$AK$7/($A$2:$A$7&$B$2:$B$7=$C13&$D13);1)

Читайте ниже...оказывается ещё даты надо учитывать в C D
Изменено: Wild.Godlike - 30.08.2022 17:41:50
 
 
 
#4
30.08.2022 16:51:29
В новых версиях MS Excel имеются формулы СУММЕСЛИМН, СЧЁТЕСЛИМН и МАКСЕСЛИ, которые могут помочь в решении этой задачи без использования формул-массива.
 
 
 
#5
30.08.2022 16:52:03
Цитата
написал:
Код
    [URL=#]?[/URL]       1  2      E13    =СУММ(($A$2:$A$7=C13)*($B$2:$B$7=D13)*($F$2:$AK$7))/СУММ(($A$2:$A$7=C13)*($B$2:$B$7=D13)*($F$2:$AK$7>0))    F13    =МАКС(($A$2:$A$7=C13)*($B$2:$B$7=D13)*($F$2:$AK$7))  
  Вводить как формулу массива Ctrl+Shift+Enter.
Спасибо,
только я наверное не совсем корректно написала, а как вывести максимальную сумму в месяц, не максимальное значение за весь период, а максимальная сумма в месяце
 
 
 
#6
30.08.2022 16:53:16
Цитата
написал:
В новых версиях MS Excel имеются формулы СУММЕСЛИМН, СЧЁТЕСЛИМН и МАКСЕСЛИ, которые могут помочь в решении этой задачи без использования формул-массива.
считает только по одному столбцу, а не по нескольким как мне нужно
 
 
 
#7
30.08.2022 16:54:00
LordHenry, За период который у вас указан в стобцах C и D?

IKor, если я правильно помню то в этих формулах диапазоны условий 2,3 и т.д. должны быть равны диапазову первого условия в противном случае будет ошибка #ЗНАЧ, так что ТС это не подойдёт.
Изменено: Wild.Godlike - 30.08.2022 16:57:01
 
 
 
#8
30.08.2022 16:55:16
Цитата
написал:
LordHenry, За период который у вас указан в стобцах C и D?
да
 
 
 
#9
30.08.2022 17:05:49
LordHenry,
По вашей логике попадает ли вот это значение в расчёт среднего
1 февраля 2022 г.
1,523809524
если
Дата старта
08.02.2022

т.е. мы в столбцах C и D берём в расчёт только месяц и год или нет?
 
 
 
#10
30.08.2022 17:09:06
Это значение идет с расчетом за весь месяц как коэфициент, так что да, нужно брать все значения кроме нулей, нули не должны попадать в расчет среднего
 
 
 
#11
30.08.2022 17:16:10
Цитата
Среднее
=СУММПРОИЗВ((($B$2:$B$7=$D13)*($A$2:$A$7=$C13)*$F$2:$AK$7))/СУММПРОИЗВ((($B$2:$B$7=$D13)*($A$2:$A$7=$C13)*($F$2:$AK$7<>0))) - отлично сработало, спасибо

А вот как сделать чтоб выводило максимальную сумму за месяц из всех месяцев?
Изменено: LordHenry - 30.08.2022 18:19:31
 
 
 
#12
30.08.2022 17:26:21
LordHenry, Не знаю что у вас там сработало, в #3 вообще не учитывает даты/период.....
И зачем вы всё сообщение процитировали.....цитата это акцент на фразе..... лучше исправить.

Ну кароче вот наковырял франкеншейна с учётом периодов
Среднее
=СУММПРОИЗВ((($B$2:$B$7=$D13)*($A$2:$A$7=$C13)*(ТЕКСТ($F$1:$AK$1;"ММГГГГ")>=ТЕКСТ($C$2:$C$7;"ММГГГГ"))*(ТЕКСТ($F$1:$AK$1;"ММГГГГ")<=ТЕКСТ($D$2:$D$7;"ММГГГГ"))*$F$2:$AK$7))/СУММПРОИЗВ((($B$2:$B$7=$D13)*($A$2:$A$7=$C13)*(ТЕКСТ($F$1:$AK$1;"ММГГГГ")>=ТЕКСТ($C$2:$C$7;"ММГГГГ"))*(ТЕКСТ($F$1:$AK$1;"ММГГГГ")<=ТЕКСТ($D$2:$D$7;"ММГГГГ"))*($F$2:$AK$7<>0)))
Максимальное
Вводить как формулу массива Ctrl+Shift+Enter.
=МАКС(($B$2:$B$7=$D13)*($A$2:$A$7=$C13)*(ТЕКСТ($F$1:$AK$1;"ММГГГГ")>=ТЕКСТ($C$2:$C$7;"ММГГГГ"))*(ТЕКСТ($F$1:$AK$1;"ММГГГГ")<=ТЕКСТ($D$2:$D$7;"ММГГГГ"))*$F$2:$AK$7)

P.S. хотелось бы посмотреть на более лаконичные решения местных гуру формулистов :)

Изменено: Wild.Godlike - 30.08.2022 17:44:23
 
 
 
#13
30.08.2022 18:03:23
Wild.Godlike, теперь научилась отвечать на имя, а не на цитату, это конечно впечатляет, спасибо, но максимальное выводится просто значение, а не сумма, а нужна максимальная сумма в месяце из всех месяцев
 
 
 
#14
30.08.2022 22:05:40
в приложении пара вариантов наиболее короткие
=SUMPRODUCT((A$2:A$7=C13)*(B$2:B$7=D13)*(COLUMN(F$1:AK$1)-5>=MATCH(C$2:C$7;F$1:AK$1))*(COLUMN(F$1:AK$1)-5<=MATCH(D$2:D$7;F$1:AK$1))*F$2:AK$7)/SUMPRODUCT((A$2:A$7=C13)*(B$2:B$7=D13)*(COLUMN(F$1:AK$1)-5>=MATCH(C$2:C$7;F$1:AK$1))*(COLUMN(F$1:AK$1)-5<=MATCH(D$2:D$7;F$1:AK$1))*(F$2:AK$7>0))
и
=MAX((A$2:A$7=C13)*(B$2:B$7=D13)*(COLUMN(F$1:AK$1)-5>=MATCH(C$2:C$7;F$1:AK$1))*(COLUMN(F$1:AK$1)-5<=MATCH(D$2:D$7;F$1:AK$1))*F$2:AK$7)
Цитата
LordHenry написал:
а не сумма, а нужна максимальная сумма в месяце из всех месяцев
вы хоть поясните что имеете в виду а то я тоже понял видимо неправильно.
Изменено: БМВ - 30.08.2022 22:09:41
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх