EXCEL Разбить целое число на несколько целых чисел
У меня есть целое число, которое я хочу разделить на шесть разных целых чисел, чтобы их пропорции как можно ближе соответствовали заданным весам. Пример ниже:
Какой самый чистый способ сделать это? Я сделал это, используя сложную комбинацию ОКРУГЛ ВНИЗ и РАНГА, но это не на 100% надежно.
Ответы 4
Попробуйте следующее:
=ROUND($A$2*(B2/SUM(B$2:B$7)),0)
Или: Могу пролить это:
=LET(α,B2:B7,ROUND(A2*α/SUM(α),0))
Это не сработает. В приведенном выше примере рассмотрим, что произойдет, если вы измените последний вес с 60 на 12, в сумме результаты дадут 787, а не 786.
@JakubSzyguła ах да, я обновлю
Чтобы использовать самый простой пример того, как я ожидаю обработки проблемных округлений. Разделение 7 на 6 частей с одинаковым весом должно дать 2 1 1 1 1 1 .
@JakubSzyguła обратное не работает из-за проблем с плавающей запятой.
Я бы сначала отсортировал веса от большего к меньшему.
Для первых пяти используйте =ROUND($A$2*D2/SUM($D$2#),0), чтобы округлить до ближайшего целого числа.
Затем для последнего веса просто используйте =A2-SUM(E2:E6), чтобы сумма всегда была 786.
Не математически строго, но я думаю, что в большинстве случаев это должно работать.
Рассмотрим веса «10, 10, 10, 10, 10, 4». Первые 5 записей равны (без округления) 145,5556, которые вы округляете до 146, но шестая запись равна (без округления) 58,222, но вы выводите 56…
@Chronocidal Да, твоя версия намного лучше.
Если у вас MS365, то эта LAMBDA формула даст желаемые результаты.
=LET(tgt,F2,weights,G2:G7,fx,tgt/SUM(weights),sub,REDUCE(0,DROP(weights,1),LAMBDA(a,c,a+ROUND(fx*c,0))),BYROW(weights,LAMBDA(r,IF(ROW(r)=ROW(tgt),tgt-sub,ROUND(fx*r,0)))))
Если выполняются оба следующих условия:
- число меньше суммы весов
- есть веса незначительной величины
тогда функция может выдать распределение с нулевым или отрицательным значением (хотя технически это все равно будут целые числа).
(если, как я подозреваю, это своего рода упражнение по распределению финансов, то и то, и другое никогда не будет правдой)
Хорошо, это может быть немного сложно, но:
=LET(total,$A$2,weights,$B$2:$B$7,valTbl,MAKEARRAY(COUNTA(weights),2,LAMBDA(y,x,IF(x=1,y,total*INDEX(weights,y)/SUM(weights)))),sortTbl,SORTBY(valTbl,MOD(INDEX(valTbl,,2),1),-1),lowtotal,SUM(ROUNDDOWN(INDEX(valTbl,,2),0)),outTbl,MAKEARRAY(COUNTA(weights),2,LAMBDA(y,x,ROUNDDOWN(INDEX(sortTbl,y,x),0)+IF(AND(x=2,y<=(total-lowtotal)),1,0))),INDEX(SORT(outTbl,1,1),,2))
(Из-за использования функций LAMBDA и MAKEARRAY это не будет работать в более старой версии Excel)
Как это работает:
Сначала он создает массив valTbl, который содержит номер строки и точное пропорциональное значение (включая десятичные дроби).
Затем он сортирует это значение по убыванию по десятичной части значения в sortTbl:
Он суммирует целые части значений (783), вычитает это из общей суммы (786-783 = 3), которую мы временно будем называть 𝕐, затем создает новый outTbl, беря целые части значений и добавляя 1 до первых 𝕐 записей:
Наконец, мы сортируем это по возрастанию по строке (что возвращает исходный порядок) и выводим только столбец «Значение»:
Благодаря тому, как это работает, это работает даже в таких случаях, когда наши веса приводят к совпадению десятичных частей: (при этом приоритет отдается более ранним строкам, но вы можете расширить SortBy, чтобы изменить это, если вы хотите установить приоритет больших или меньших целочисленных значений или чего-то еще). )
или даже
Отличный ответ. Скорее всего, я не буду работать со старой версией Excel, которая есть у меня дома, но она содержит ответ в одной формуле. (Я использовал ту же методологию, но мне пришлось хранить промежуточные результаты в ячейках, а не в массивах.)
Имея данные, изложенные, как в опубликованном ответе, вы можете использовать
=IF(ROW()-1<COUNT($B$2:$B$7),ROUND($A$2*B2/SUM($B$2:$B$7),0),A$2-SUM(C$1:C1))вC2и скопировать вниз - это эффективно округляет все веса, за исключением последнего, на который распределяется влияние любых округлений.