Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск максимальной суммы серии положительных/отрицательных значений
 
#1
03.11.2024 03:09:56
Добрый день, всем.

Могли бы подсказать как решить задачу с поиском максимальной суммы серии идущих друг за другом положительных и отрицательных чисел столбца?
Серии могут быть как из одной ячейки так и несколькоих. Серия обрывается как только происходит смена знака с положительного(или нуля) на отрицательное значение. И наоборот, с отрицательного на положительное. Получается несколько групп из подряд положительных и несколько групп из подряд отрицательных чисел. У каждой группы есть сумма входящих в него чисел. Нужно найти группу с максимальной суммой положительных чисел и группу с минимальной суммой отрицательных чисел.

Во вложении пример с сериями чисел (столбец А), а так же вручную подсчитанные результаты, которые нужно получить (столбец С).
В столбце B просто для удобства визуального восприятия вручную разбил значения по группам и посчитал их суммы. В реальной таблице мне это не нужно будет делать.

Спасибо заранее.
 
 
 
#2
03.11.2024 08:32:58
по старинке :-) и летуче :-(
По вопросам из тем форума, личку не читаю.
 
 
 
#3
03.11.2024 08:46:15
pq m
Пришелец-прораб.
 
 
 
#4
03.11.2024 11:59:25
Цитата
написал:
по старинке :-) и летуче :-(
Ха-ха )) летуче.
Формула в 300 символов и летуче у меня рядом никогда не встанут.
Вы тут просто какие-то монстры экселя.

Спасибо большое.
Изменено: Cragle - 03.11.2024 12:09:45
 
 
 
#5
03.11.2024 12:09:20
Цитата
написал:
Прикрепленные файлы
1-12.xlsx  (17.91 КБ)
Спасибо большое.

Очень иззящное решение.
К сожалению совсем не владею PQ.
Из строк кода понял что в функции result ищется максимальное и минимальное значение в виртуальном списке calc. А вот как генерируется сам calc я уже не осилил )
 
 
 
#6
03.11.2024 18:30:19
Цитата
Cragle написал:
и летуче
относилось к использованию OFFSET/СМЕЩ

Была надежда, что р7 справится, но Libre справился так же как и Excel а Р7 спасовал.
Изменено: БМВ - 03.11.2024 19:47:39
По вопросам из тем форума, личку не читаю.
 
 
 
#7
03.11.2024 20:26:01
Цитата
написал:
Была надежда, что р7 справится, но Libre справился так же как и Excel а Р7 спасовал.
Здравствуйте, Михаил! Отличная мысль - проверять формулы сразу в 3 офисных пакетах!  :idea:  
Владимир
 
 
 
#8
03.11.2024 20:28:43
sokol92, Владимир, мысль хренова, слишком много разочарований в р7 :-(

Почему в трех?
По вопросам из тем форума, личку не читаю.
 
 
 
#9
03.11.2024 20:35:21
и правда, а почему только в трёх )
 
 
 
#10
03.11.2024 21:07:19
OFF
Цитата
БМВ написал:
Почему в трех?
MS Office Excel, LibreOffice Calc, Р7-Офис (OnlyOffice). У меня других нет.  :)
Владимир
 
 
 
#11
04.11.2024 08:30:56
OFF
Цитата
написал:
MS Office Excel, LibreOffice Calc, Р7-Офис (OnlyOffice)
+
Мой офис - не проверял
WPS - кстати там сработало
ALTEROFFICE - не проверял
FREEOFFICE
OFFICESIUT
ZOHO OFFICE

А вот и не летучий вариант и он уже в Р7 работает
=MAX(MMULT(--(TRANSPOSE(MMULT(--(ROW(A2:A39)>=TRANSPOSE(ROW(A2:A39)));((A1:A38<0)+ISTEXT(A1:A38))*((A2:A39>=0)+(A2:A39=""))+((A1:A38>=0)+ISTEXT(A1:A38))*((A2:A39<0)+(A2:A39=""))))=ROW(A2:A39)-1);--A2:A39))
даже так
=MAX(MMULT(--(TRANSPOSE(MMULT(--(ROW(A2:A38)>=TRANSPOSE(ROW(A2:A38))),--IFERROR(SIGN(IF(A2:A38=0,1,A2:A38))<>SIGN(IF(A1:A37=0,1,A1:A37)),1)))=ROW(A2:A38)-1),--A2:A38))

"Остапа несло"
=MAX(MMULT(--(TRANSPOSE(MMULT(-(ROW(A2:A38)>=TRANSPOSE(ROW(A2:A38))),-IFERROR(SIGN(A2:A38+1E-99)<>SIGN(A1:A37+1E-99),1)))=ROW(A2:A38)-1),A2:A38))  
Но не смотря на то что короче и не летуче, он медленнее чуток, при этом предположу, что с ростом таблицы будет расти и разница.

=MAX(IFERROR(SUBTOTAL(9,OFFSET(A1,SMALL(IF(((A1:A38<0)+ISTEXT(A1:A38))*((A2:A39>=0)+(A2:A39=""))+((A1:A38>=0)+ISTEXT(A1:A38))*((A2:A39<0)+(A2:A39="")),ROW(A1:A38)),ROW(A2:A38)-1),IFERROR(1/(1/FREQUENCY(IF(A2:A38>=0,ROW(A2:A38)),IF(A2:A38<0,ROW(A2:A38)))),1))),))        1175,781

=MAX(MMULT(--(TRANSPOSE(MMULT(--(ROW(A2:A39)>=TRANSPOSE(ROW(A2:A39))),((A1:A38<0)+ISTEXT(A1:A38))*((A2:A39>=0)+(A2:A39=""))+((A1:A38>=0)+ISTEXT(A1:A38))*((A2:A39<0)+(A2:A39=""))))=ROW(A2:A39)-1),--A2:A39))        1441,406

=MAX(MMULT(--(TRANSPOSE(MMULT(--(ROW(A2:A38)>=TRANSPOSE(R
OW(A2:A38))),--IFERROR(SIGN(IF(A2:A38=0,1,A2:A38))<>SIGN(IF(A1:A37=0,1,A1:A37)),1)))=ROW(A2:A38)-1),--A2:A38))    1386,719

=MAX(MMULT(--(TRANSPOSE(MMULT(-(ROW(A2:A38)>=TRANSPOSE(ROW(A2:A38))),-IFERROR(SIGN(A2:A38+1E-99)<>SIGN(A1:A37+1E-99),1)))=ROW(A2:A38)-1),A2:A38))         1347,656
Изменено: БМВ - 04.11.2024 17:30:21
По вопросам из тем форума, личку не читаю.
 
 
 
#12
04.11.2024 18:41:48
Цитата
написал:
по старинке :-) и летуче :-(
Добрый вечер.

Извините, я снова к вам.

А что нужно изменить в вашем первом варианте, в случае если значения в столбце начинаются не с 2-й, а с третьей строки?
А так же какие нужны изменения чтобы сделать формулу более универсальной и распространить на весь столбец, с учетом будущих новых значений?

Спасибо.
 
 
 
#13
04.11.2024 18:57:29
Воспользуйтесь последним вариантом. там может и не совсем понятно, но диапазонов меньше, ну а их  можно с запасом делать, но желательно разумно.
По вопросам из тем форума, личку не читаю.
 
 
 
#14
04.11.2024 19:15:09
Цитата
написал:
Воспользуйтесь последним вариантом. ну а диапазон можно с запасом делать, но желательно разумно.
Спасибо большое.
Последнюю формулу смог адаптировать под свой вариант.
Но заметил, что если попроборвать добавить в формулу номерацию строк где еще нет значений, то получаю ошибку ЗНАЧ!
Например если в демо таблице данные заканчиваются на А39 а в формуле везде А39 заменить на А50, то получаю такую ошибку.
В принципе решение работает. Но при появлении новых строк, нужно будет вносить изменения в формулу и увеличивать номер последней строки всех диапазонов
 
 
 
#15
04.11.2024 19:26:35
просто надо пустышки в нули превратить
По вопросам из тем форума, личку не читаю.
 
 
 
#16
04.11.2024 19:34:08
Цитата
написал:
просто надо пустышки в нули превратить
Спасибо за совет.
Кстати в последнем файле Максимальная сумма отрицательнной серии поменялась с -17 на -15
Я вот в своем оригинальном файле когда пытался применить вашу формулу, получил такой же результат.
С максимальной суммой положительной серии все остается корректным + 30
 
 
 
#17
04.11.2024 20:09:28
Обязательно первая ячейка пустая должна быть? Ну или нужно делать исключение для этого?

Хотя можно и так
=MIN(MMULT(--(TRANSPOSE(MMULT(-(ROW(A2:A99)>=TRANSPOSE(ROW(A2:A99)));-IFERROR(SIGN(A2:A99+1E-99)<>SIGN(A1:A98+1E-99);1)))=ROW(A2:A99)-1);--A2:A99))

A2:A99 и A1:A98

Или так
=MIN(MMULT(--(TRANSPOSE(MMULT(-(ROW(A3:A99)>=TRANSPOSE(ROW(A3:A99)));-IFERROR(SIGN(A3:A99+1E-99)<>SIGN(IF(A2:A98="";0;A2:A98+1E-99));1)))=ROW(A3:A99)-1);--A3:A99))
Изменено: БМВ - 04.11.2024 20:30:49
По вопросам из тем форума, личку не читаю.
 
 
 
#18
04.11.2024 22:43:49
Цитата
написал:
Обязательно первая ячейка пустая должна быть? Ну или нужно делать исключение для этого?
Строчка пуская потому что для нее отсутствует возможность получить значение.
Так как нет более раннего периода чтобы понять там + какое то значение или -. Т.е. вырос показатель или снизился по сравнению с предыдущим, потому что предыдущего просто нет.
Наверное можно заменить на 0 чтобы не было пусто.

Спасибо большое. Последний вариант решает вопросы.
 
 
 
#19
04.11.2024 23:00:31
Цитата
Cragle написал:
Наверное можно заменить на 0 чтобы не было пусто.
нет, нужен признак перемены знака, а так как не известно какой знак у первого значения, то должен быть или + или - или что-то отличное, например текст. Пусто - равносильно нулю а ноль к положительным относим.
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх