Суммировать до тех пор, пока не выполниться условие

Дата: Суббота, 21.11.2015, 11:35 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Доброго субботнего утра!

Из данных, что есть: столбец "G" с числами. Столбец "F" показывает какое было число в "G" : отрицательное или положительное.
В колонке J я указал, какие числа должны получиться в итоге.
А теперь, что надо сделать:
Для положительных чисел: суммируем ячейки столбца "G" до тех пор, пока сумма не примет отрицательное значение. В колонку "J" заносится максимальное значение, которое получалось при сложении.
Для отрицательных чисел: суммируем ячейки столбца "G" до тех пор, пока сумма не примет положительное значение. В колонку "J" заносится минимальное значение, которое получалось при сложении.
В колонку "А" я продублировал правила вычислений, чтоб нагляднее было.
Если что не понятно, уточните, я со своей колокольни размышляю. Некоторые вещи, которые мне понятны безусловно, могут вызвать вопросы у других. И наоборот

К сообщению приложен файл: _astronom.xlsx (31.2 Kb)

YouGreed

Дата: Суббота, 21.11.2015, 12:03 | Сообщение № 2

Группа: Проверенные

Ранг: Ветеран

Сообщений: 589

Репутация: 123 ±

Замечаний: 0% ±

Excel 2010

astronom, Не вполне наглядный пример. Можно покрутить формулы.

Код

=НАИМЕНЬШИЙ(($F$2:$F$973=$F$3)*$G$2:$G$973;1)

- для отрицательных

Код

=НАИБОЛЬШИЙ(($F$2:$F$973=$F$2)*$G$2:$G$973;1)

- для положительных

Формулы массивны, вводятся нажатием Ctrl+Shift+Enter одновременно.

Сообщение отредактировал YouGreed - Суббота, 21.11.2015, 12:04

Дата: Суббота, 21.11.2015, 12:28 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Попробовал так:

Код

=ЕСЛИ(G2>0;НАИБОЛЬШИЙ(($F$2:$F$973=$F$2)*$G$2:$G$973;1);НАИМЕНЬШИЙ(($F$2:$F$973=$F$3)*$G$2:$G$973;1))

Получилось не то. Где то лишние значки "$" ? Я в значках этих полный дуб... :(
Можете объяснить пошагово как работает формула? Без "если", просто вот эту:

Код

=НАИБОЛЬШИЙ(($F$2:$F$973=$F$2)*$G$2:$G$973;1)

Хочу понять что с чем и почему и где))
[moder]Оформляйте формулы тегами (кнопка fx)[/moder]

Сообщение отредактировал Pelena - Суббота, 21.11.2015, 14:19

Дата: Суббота, 21.11.2015, 20:03 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1130 ±

Замечаний: 0% ±

Excel 2003

Кросс
Получилось получить результат, но потребовался дополнительный столбец Е.
Основной части формулы присвоено имя "Формула"

Код

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Лист1!$G2;;;СТРОКА(Лист1!A$2:ИНДЕКС(Лист1!A:A;ПОИСКПОЗ(;0^(-1^(Лист1!$G2<0)*СУММЕСЛИ(Лист1!$E2:$E$973;"<="&Лист1!$E2:$E$973;Лист1!$G2:$G$973)<0);)))))

итоговая формула

Код

=ЕСЛИ(-1^(G2<0)>0;МАКС(Формула);МИН(Формула))

Результат немного отличается, думаю вы допустили ошибочку определения суммы с накоплением.
Какие массивы обрабатывает функция МАКС() и МИН() в каждой ячейке можно так.
Выделить имя формулы "Формула" в строке формул и нажать F9

Добавил второй вариант. Еще один доп. столбец и немного изменена именованная формула. Обработка стала быстрее.
В некоторых ячейках ошибка, так как поиск позиции не находит в массиве сумм отрицательное или положительное значение.

К сообщению приложен файл: 123_1.rar (0.0 Kb) · 123_2.rar (0.0 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Суббота, 21.11.2015, 21:41

Дата: Воскресенье, 22.11.2015, 09:49 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

архив 123_2 поврежден или имеет неизвестный формат...
Залейте без архива пожалуйста, или на облако майл.ру

п.с. пробовал Вашу первую версию. считает немножно неправильно, и очень долго соображает....

Дата: Воскресенье, 22.11.2015, 10:03 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1130 ±

Замечаний: 0% ±

Excel 2003

У меня архив открывается без проблем. Выкладываю архив еще раз.
Если есть неправильные результаты, то укажите их.
Во второй строке массив сумм {224:409:158:384:213:478:-14}, поэтому выбирает максимальное 478, а у вас почему-то массив сумм немного отличается
В третьей строке массив сумм {-120:-371:-145:-316:-51:-543:-224:-409:-72:-513:-245:-628:-285:-472:-9:-196:-23:-170:34} минимальное -628, а у вас -629
В пятой строке массив {-25:-196:69}, а у вас -25,-197,68
Разберитесь где неправильно.

К сообщению приложен файл: 1385586.rar (32.4 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Воскресенье, 22.11.2015, 10:10

Дата: Воскресенье, 22.11.2015, 10:16 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Вроде все правильно. Спасибо большое!

У меня таких листов с данными несколько десятков. Как мне применить к ним эту "формулу" ?
Где она "спрятана" ?

По поводу несовпадений: почему то изначальные данные в колонке G такие: например, в G2 стоит 529,000000000002. А в G5 стоит -251,000000000001
Причем это в файле 123. В вашем 123_2 такого нет. С чем это связано не знаю, там должны быть целые числа. Но все это не имеет значения, вияние на конечный результат практически нулевое.

Сообщение отредактировал astronom - Воскресенье, 22.11.2015, 10:19

Дата: Воскресенье, 22.11.2015, 10:20 | Сообщение № 8

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1130 ±

Замечаний: 0% ±

Excel 2003

Ctrl+F3 диспетчер имен. Смотрите там
Второй файл быстрее?
В нем дополнительные столбцы H и I
PS. Округление сделал я, не думал что важно такие числа. И в с такими дробями массивы контролировать визуально невозможно.

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Воскресенье, 22.11.2015, 10:24

Дата: Воскресенье, 22.11.2015, 10:24 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 11

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Второй гораздо быстрее. Ракета, по сравнению с первым вариантом