Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
как округлить числа в массиве до целых, чтобы выйти на общую сумму
 
#1
02.07.2013 14:51:00
Краткая предыстория: менеджерам высылаются планы в общей сумме (например 11 600), их задача разбить эти планы по агентам. Они не сильно заморачиваются и разбивают по структуре продаж и в результате получаются, например, вот такие числа 1 646,372.
Однако при внесении в систему планирования допустимо вносить только целые числа, поэтому перед тем как вносить данные от менеджеров разбитые до агента их приходится вручную корректировать выставляя напротив каждого агента целое число. При этом нужно смотреть чтобы общая сумма после корректировок оставалась такой же как и была.

Вот подумалось, а вдруг получится формулами в эксель сделать "ручную работу" :-)

файл с примером прикреплю, у самого идей решения нет  :(  
очень надеюсь на помощь зала!

П.С. никакого четкого алгоритма какое из чисел куда округлять нету, подойдет любой способ
 
 
 
#2
02.07.2013 14:53:01
попробуйте так
Код
=ОКРУГЛТ(E5;1)
Изменено: китин - 02.07.2013 14:53:32
Вполне такой нормальный кинжальчик. Процентов на 100
 
 
 
#3
02.07.2013 14:53:27
Нерадивые менеджеры... - не есть название для форума по Excel. Исправил.
 
 
 
#4
02.07.2013 14:57:34
попробовал =ОКРУГЛТ(;1) : в первом массиве все нормально, а во втором - нет (((
в настоящих данных "менеджеров" несколько десятков и агентов под ними тоже по разному (от 4 до 8), т.е. большая вероятность, что =ОКРУГЛТ(;1) будет работать не так как надо (
 
 
 
#5
02.07.2013 14:59:21
Код
=округл(Х;0)
все кроме последнего
последний как разница = общая сумма - сумма округленных  ;)
 
 
 
#6
02.07.2013 15:02:32
почему именно последний? у него после запятой ,088, а есть те, у кого, например ,118 или даже больше!..
 
 
 
#7
02.07.2013 15:05:41
Цитата
Zhukov_K пишет: ... почему именно
Цитата
Zhukov_K пишет: ... никакого четкого алгоритма какое из чисел куда округлять нету, подойдет любой способ
... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#8
02.07.2013 15:29:12
не знаю, будет ли работать в других примерах, но как вариант...
Учимся сами и помогаем другим...
 
 
 
#9
02.07.2013 15:30:52
ну да, но имелась ввиду общая формула для всего диапазона, а не индивидуальная формула для одной из ячеек диапазона! )
 
 
 
#10
02.07.2013 15:33:31
ber$erk, спасибо! очень интересный подход! буду пробовать!
 
 
 
#11
02.07.2013 15:55:20
только у этого решения (решения берсерка) есть существенный минус в том, что формула захватывает ячейки выше, что в общем-то недопустимо, т.к. списки менеджеров в рабочем файле идут подряд, между ними нет пустых строк ((
 
 
 
#12
02.07.2013 16:00:49
Цитата
Zhukov_K пишет: ... только у этого решения (решения берсерка) есть существенный минус...
Увольте, Zhukov_K, - посмотрите свой пример, не вы ли его таким дали?!. А теперь о "существенности" заговорили - какой?.. Под которую новое решение требуется?.. Да еще и "за так"?
Может сюда заглянете - http://www.planetaexcel.ru/forum/?PAGE_NAME=list&FID=7
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#13
02.07.2013 16:27:44
Похожая тема: http://www.excelworld.ru/forum/2-2074-1

в файле реализован следующий алгоритм:
Цитата
1. Нормально по правилам округления получаем округленные значения ячеек во всех строках.

2. Для каждой строки вычисляем разницу [исходное значение] - [округленное].

3. Смотрим насколько отличается общая сумма по округленным от заданной суммы (в данном случае 10).

4. Если эта общая разница [заданная]-[округленная] положительна, то добавляем единичку в то значения, у которого разница по конкретной строке [исходное]-[округленное] положительна и максимальна. Затем во второе наибольшее значение, затем в третье и так далее, пока не будет исчерпана вся общая разница.

5. Если эта общая разница [заданная]-[округленная] отрицательна, то отнимаем единичку от того значения, у которого разница по конкретной строке [исходное]-[округленное] отрицательна и минимальна (т.е. наименьшая с учетом знака). Затем из второго наименьшего, затем из третьего и так далее, пока не будет исчерпана вся общая разница.
 
 
 
#14
02.07.2013 16:45:33
Z, спасибо, загляну, конечно

МСН - спасибо, интересное решение
 
 
 
#15
05.08.2017 13:04:15
Коллеги, добрый день. Прочитал много веток форума, но пока не нашел для себя ответа, пожалуйста помогите.
Необходимо округлить любое целое многозначное число либо до 5 либо до 10 например, в ходе вычислений получаем 142 - необходимо округлить до 145, либо получаем 146, тогда необходимо округлить до 150. то есть в диапазоне окончания числа от 0,6 до 4,6 необходимо округлять до 5, в случае если окончание получается от 5,5 и выше, необходимо округлить до следующего десятка. прилагаю экселевский фаил с комментарием к каждой строчке.  
 
 
 
#16
05.08.2017 13:26:30
Yar28, добрый и Вам. Так?
=ОКРВВЕРХ(F8;5)
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#17
05.08.2017 16:02:10
Цитата
Bema написал:
Необходимо округлить любое целое многозначное число либо до 5 либо до 10
Код
=ОКРУГЛВВЕРХ(A3/5;0)*5
 
 
 
#18
07.08.2017 06:15:35
Bema, z_sir, Коллеги, спасибо, но это не то, Цифры   должны оканчиваться либо на 5 (если число, например от 141 до 144, то есть должно округлиться до 145). Либо на   следующий десяток, если число, например от 146 до 149, то есть должно округлиться до 150.
 
 
 
#19
07.08.2017 06:33:05
Yar28,
Цитата
Yar28 написал:
спасибо, но это не то
голословно, пример работы формулы
Цитата
z_sir написал:
=ОКРУГЛВВЕРХ(A3/5;0)*5
на ваших числах - 141/5 = 28,2 , далее формула округляет вверх до целого , то  есть получаем 29, затем умножим на 5 = 145 - то, что вы и хотели.  Проверьте для начала, а потом пишите что это не то. =))
Изменено: Александр - 07.08.2017 06:36:56
 
 
 
#20
07.08.2017 07:03:13
Цитата
Yar28 написал:
Коллеги, спасибо, но это не то
Вы уверены?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#21
08.08.2017 05:16:22
Александр, Bema, Спасибо большое за отзыв!
Но я действительно проверил - не тот результат.
Прилагаю эксель с расчетами, нужно округлить вверх до окончания 5 или 0 значения столбцов F G H I, в этой таблице посчитана формула, ее результат необходимо округлить.
 
 
 
#22
08.08.2017 05:28:04
Цитата
Yar28 написал:
Но я действительно проверил - не тот результат.
В какой ячейке не тот результат?
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#23
08.08.2017 06:09:56
Михаил Лебедев, В этом сообщении, мне предоставили таблицу с резулаьтатом округления (http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=49381&amp...) я написал, что это не тот результат, который нужен, и привел в этом сообщении (http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=49381&amp...) таблицу с вычислениями, которые нужно округлить.
 
 
 
#24
08.08.2017 06:21:18
Цитата
Yar28 написал:
не тот результат
формула то подходящая
для строки 7
Код
 =ОКРВВЕРХ(((D7-4)*10+135);5)    =ОКРВВЕРХ(((D7-4)*10.2+120);5)      =ОКРВВЕРХ(((D7-4)*10.2+115);5)     =ОКРВВЕРХ(((D7-4)*10.2+110);5)
 
 
 
#25
08.08.2017 06:26:39
Yar28,
Цитата
Yar28 написал:
Но я действительно проверил - не тот результат.
Где в приложенном файле вы проверяли ? Не заметно что вы вообще туда пробовали хоть что то ввести из выше предложенного.
Цитата
Yar28 написал:
нужно округлить вверх до окончания 5 или 0 значения столбцов F G H I, в этой таблице
Исходя из этого, обе формулы работают так, как надо. Смотрите вложенный файл
Изменено: Александр - 08.08.2017 06:29:38
 
 
 
#26
08.08.2017 07:18:26
Александр, copper-top, Спасибо вам большое! Я действительно не разобрался и полез писать что вычисление неверное, прошу прощения и признаю свою неправоту.

Формулы которые вы предложили действительно подходят, и я это проверил на действующих вычислениях.
Подскажите, что бы по этой формуле округлялось только до 0 в верхнюю (от окончания 5) и нижнюю (от окончания 4) стороны, что нужно изменить в формуле? =ОКРУГЛВВЕРХ(((14,08-2)*10+110)/5;0)*5 - вот формула, здесь результат 231 округляется до 235, что нужно изменить в формуле что бы округлялся до 230, или в таком случае  =ОКРУГЛВВЕРХ(((6,73-2)*10+110)/5;0)*5 получается результат 157, что бы он округлился до 160.  (возможно ли все эти изменения вместить в одной формуле?)
 
 
 
#27
08.08.2017 07:41:48
Yar28,
Код
=ЕСЛИ(--ПРАВСИМВ(ОКРУГЛ(((D7-4)*10+135);0);1)>=5;ОКРВВЕРХ(((D7-4)*10+135);10);ОКРВНИЗ(((D7-4)*10+135);10))
 
 
 
#28
08.08.2017 07:46:07
Цитата
Yar28 написал:
Спасибо вам большое!
спасибо тем, кто дает формулы.
 
 
 
#29
08.08.2017 07:49:49
Yar28,Хотя так наверное будет правильнее, ибо 144,6 все таки меньше 145 и следовательно его надо округлять до 140 а не до 150
Код
=ЕСЛИ(--ПРАВСИМВ(ОКРУГЛВНИЗ(((D7-4)*10+135);0);1)>=5;ОКРВВЕРХ(((D7-4)*10+135);10);ОКРВНИЗ(((D7-4)*10+135);10))
 
 
 
#30
08.08.2017 10:35:29
Александр, Спасибо ВАМ большое, Формулы сошлись, очень приятно приятно, что найдено решение.
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх