Как пропорционально распределить сумму из двух таблиц
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как распределить сумму на N чисел по графику степенной функции в екселе
Есть определенная сумма, например 1000. Есть таблица формата: Нужно сумму распределить так, чтобы значение, относительно параметра «номер» возрастало. А сам график из этих данных, где ось X — это номер,а Y — значение — был в виде графика степенной функции. Я перерыл кучу сайтов, но не смог найти даже намека на формулу и логику того, как это можно сделать. Знаю метод как пропорционально распределить сумму, но по той формуле — график получается линейный. Там мы берем номер, умножаем его на сумму и делим на сумму всех номеров.Пример для нахождения первого значения по данной формуле (1*1000/55 (55 — это сумма числе от 1 до 10)) С помощью этой формулы можно добиться того, что после проделанных операций, если взять сумму второго столбца, то мы получим 1000. Но мне нужно получить не линейное распределение, а степенное так, чтобы сумма значений была равна первоначально заданной сумме
Отслеживать
задан 27 ноя 2022 в 14:39
CyberNoble CyberNoble
63 5 5 бронзовых знаков
берете квадрат от номеров, складываете, делите 1000 на полученную сумму, умножаете значения квадратов на этот коэффициент. Готовая функция врядли существует
27 ноя 2022 в 14:42
или воспользуйтесь тем, что сумма 1+2**2 + 3**2. = n(n+1)(2n+1)/6
27 ноя 2022 в 14:52
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Задача имеет отношение больше к математике, чем к электронным таблицам, но решать её будем именно в таблице Google (так поставлен вопрос).
Итак, аргументы степенной функции (числа 1..10) стоят в колонке A. Ячейке A1 содержит показатель степени нашей функции, для примера он равен 3. К исходным данным относится также требуемая сумма (1000) в ячейке C1. Далее идут вычисления.
В колонке B — результат возведения аргументов в степень с итоговой суммой в ячейке B1. В колонке C — результат «нормировки», то есть пропорциональное изменение всех слагаемых из колонки B так, чтобы их сумма составила требуемую 1000. Формула нормировки раскрыта на рисунке. Наконец, если в конечном итоге нам требуется целочисленное распределение, то необходимо округлить полученные дроби по обычным правилам, используя функцию FLOOR. Результат округления записан в колонку D, а сумма целых — в ячейку D1. У нас она оказалась 999, поскольку применено округление. Итоговый график степенной функции построен на основе значений из колонок A (аргументы) и D.
В данном случае задача решена пошагово, что удобно для понимания алгоритма. Одной функцией мне обойтись не удалось, хотя можно было написать свою пользовательскую функцию для тех же целей.
Свобода в действии
Допустим, интернет-магазин предоставляет клиенту скидку на заказ (по бонусной карте, купону или ещё как-то). Скидка даётся на весь заказ, но её действие нужно пропорционально распределить по товарам, чтобы, например, правильно сформировать кассовый чек (в нём каждую позицию чека нужно расписать: цена до применения скидки и сумма с учётом скидки).
Пример:
1) Товар №1 — цена 1500 руб.
2) Товар №2 — цена 1700 руб.
Итого сумма заказа получается 3200 руб. Допустим, клиенту предоставляется скидка 10%. В данном случае легко посчитать, что скидка в процентах будет одинаковой для каждого товара:
1) Товар №1 — цена со скидкой 1500 — 10% = 1350 руб.
2) Товар №2 — цена со скидкой 1700 — 10% = 1530 руб.
Это лёгкий пример, где никакого распределения не потребовалось. Теперь изменим условия примера. Допустим, скидка на заказ предоставляется не в процентах, а в рублях, — например, 500 руб. Как её учесть в стоимостях товаров? Вот тут уже требуется распределение. Да, можно было бы скидку целиком вписать в один какой-то товар — но это было бы некрасиво; да к тому же не универсально, ведь все товары в заказе могли бы стоить меньше, чем сумма скидки — что ж теперь отрицательную цену делать?! Нет конечно.
Итак распределяем. Очевидно, что первый товар стоит дешевле, значит, и скидку не него надо сделать меньше, чем на второй товар. Считаем сумму заказа, а потом долю стоимости каждого товара в заказе. Полученную долю умножаем на скидку на заказ.
1) Товар №1 1500 * 100 / 3200 = 46,875% — такова доля стоимости первого товар в общем заказе
2) Товар №2 1700 * 100 / 3200 = 53,125%. Проверим, что мы не ошиблись в округлении и не потеряли какой-нибудь доли заказа: 46,875 + 53,125 = 100% — всё верно.
Скидка распределяется согласно полученным долям:
1) 500 * 46,875 / 100 = 234,375. Получилось не очень-то красивое число. Во-первых, суммы допустимо указывать с копейками, а копейки — это только сотые доли. А тут получились тысячных. Во-вторых, в интернет магазине вообще могут не захотеть иметь дела с копейками. Требуется округление. Приводим к скидке 234 руб. Т.е. цена товара с учётом скидки равна 1500 — 234 = 1266 руб.
2) 500 * 53,125 / 100 = 265,625 — аналогично математическим округлением получаем 266 руб. Цена с учётом скидки 1200 — 266 = 934 руб.
Проверим, все ли 500 рублей мы вписали в виде отдельных скидок по товарам: 234 + 266 = 500 — всё верно.
Это простой пример, который довольно легко поддаётся алгоритмизации и составлению функции. Подобные функции для различных языков мне попадались в интернете. Но есть тут и подвохи, не будь которых — не было бы и статьи.
Подводные камни
Подвоха два:
1. Округление — не всегда округление скидок по товарам в сумме даёт скидку заказа. С этим авторы многих функций решают путём проверки и учёта разницы в последнем или самом дорогом товаре. Алгоритм моей функции навеян функцией отсюда Распределение суммы прапорционально
2. Не все скидки вообще возможно распределить. Это справедливо, когда требуется учитывать ещё и количество товара — т.е. цена товара никогда не должна быть с точностью больше двух десятичных знаков (т.е. копеек), а в большинстве случаев реальных магазинов — вообще без копеек. Т.е. нужна заданная точность. Невозможно ведь иметь три штуки товара в сумме 1000 руб. — тогда каждый из товаров стоил бы 333,(3) (три в периоде) руб. Вот этот момент вообще нигде не нашёл в сети. Автоматическое распределение алгоритмом, как показан выше вполне может выдать такой неделимый результат. Значит алгоритм требует доработки.
Функция распределения
Мой вариант функции (для языка PHP 7) учитывает количество по каждой позиции и заданную точность. Возвращаемый результат — всегда массив с таким же порядком и количеством элементов, что и входящий массив. А все неразрешимые ситуации генерируют исключение. Таким образом функцию не безопасно использовать обычным образом, — требуется организация перехвата исключения и какая-то реакция на исключительное поведение.
Какие могут быть варианты реакций на неразрешимые распределения? Если распределяется некая скидка по купону, то можно пойти на встречу клиенту и в неразрешимой ситуации накинуть рубль или два к скидке — этого вполне может хватить чтобы подыскать ближайший возможный вариант распределения. Потребуется перебор возможных вариантов.
Если это применение бонусов с личного счёта клиента, то неправильно было бы применить больше, чем есть на счёте — тут наоборот нужно подыскать первый доступный вариант с уменьшением скидки (и не забыть пояснить клиенту, что скидка именно такая по математическим и бухгалтерским причинам).
А если, например, первоначальный взнос клиента по кредиту невозможно равномерно распределить по товарам (для печати чека, например), то тут уже надо запрашивать алгоритм действия у бухгалтерии и руководства.
Варианты всегда есть — нужно просто помнить об исключительных ситуациях и адекватно на них реагировать.
Вот сама функция:
/** * Метод выполняет пропорциональное распределение суммы в соответствии с заданными коэффициентами распределения. * Также может выполняться проверка полного деления суммы коэффициента на его количество. Например, * при нулевой точности для чётного количества штук товара было неправильно получить нечётную сумму * после распределения, - правильно немного увеличить сумму распределения (в ущерб пропорциональности), * чтобы добиться ровного распределения по количеству. * Используется, например, при распределении скидки равномерно по позициям корзины. * @param float $sum Распределяемая сумма * @param array $arCoefficients Массив коэффициентов распределения, где ключи - определённые значения, * которые также будут возвращены в виде ключей результирующего массива. Значения - массив с ключами: * "sum" - величина коэффициента (сумма, а не цена) * "count" - количество для коэффициента * @param int $precision Точность округления при распределении. Если передать 0, * то все суммы после распределения будут целыми числами * @throws Exception Выбрасывается исключение в случае, * если невозможно ровно распределить по заданным параметрам * @return array Массив, где сохранены ключи исходного массива $arCoefficients, а значения - массив с ключами: * "init" - начальная сумма, равная соответствующему входному коэффициенту * "final" - сумма после распределения */ public static function getProportionalSums(float $sum, array $arCoefficients, int $precision) : array < $arResult = []; /** * @var float Сумма значений всех коэффициентов */ $sumCoefficients = 0.0; /** * @var float Значение максимального коэффициента по модулю */ $maxCoefficient = 0.0; /** * @var mixed Ключ массива для максимального коэффициента по модулю */ $maxCoefficientKey = null; /** * @var float Распределённая сумма */ $allocatedAmount = 0; foreach ($arCoefficients as $keyCoefficient =>$coefficient) < if (is_null($maxCoefficientKey)) < $maxCoefficientKey = $keyCoefficient; >$absCoefficient = abs($coefficient['sum']); if ($maxCoefficient < $absCoefficient) < $maxCoefficient = $absCoefficient; $maxCoefficientKey = $keyCoefficient; >$sumCoefficients += $coefficient['sum']; > if (!empty($sumCoefficients)) < /** * @var float Шаг, который прибавляем в попытках распределить сумму с учётом количества */ $addStep = (0 === $precision) ? 1 : (1 / pow(10, $precision)); foreach ($arCoefficients as $keyCoefficient =>$coefficient) < /** * @var boolean Флаг, удалось ли подобрать сумму распределения для текущего коэффициента */ $isOk = false; /** * @var integer Количество попыток подобрать сумму распределения */ $i = 0; // Далее вычисляем сумму распределения с учётом заданного количества do < $result = round(($sum * $coefficient['sum'] / $sumCoefficients), $precision) + $i * $addStep; // Проверим распределённую сумму коэффициента относительно его количества if (isset($coefficient['count']) && $coefficient['count'] >0) < if (round($result / $coefficient['count'], $precision) != ($result / $coefficient['count'])) < // Не прошли проверку по количеству - ровно по заданному количеству не распределяется >else < $isOk = true; >> else < // Количество не задано, значит не проверяем распределение по количеству $isOk = true; >$i++; if ($i > 100) < // Мы старались долго. Пора признать, что ничего не выйдет throw new Exception( 'Не удалось распределить сумму для коэффициента ' . $keyCoefficient ); >> while (!$isOk); // Если сюда дошли, значит удалось вычислить сумму распределения $arResult[$keyCoefficient] = [ 'init' => $coefficient['sum'], 'final' => (0 === $precision) ? intval($result) : $result, 'count' => $coefficient['count'] ]; $allocatedAmount += $result; > if ($allocatedAmount != $sum) < // Есть погрешности округления, которые надо куда-то впихнуть $tmpRes = $arResult[$maxCoefficientKey]['final'] + $sum - $allocatedAmount; if (!isset($arResult[$maxCoefficientKey]['count']) || (isset($arResult[$maxCoefficientKey]['count']) && 1 === $arResult[$maxCoefficientKey]['count']) || (isset($arResult[$maxCoefficientKey]['count']) && $arResult[$maxCoefficientKey]['count'] >0 && (round($tmpRes / $arResult[$maxCoefficientKey]['count'], $precision) == ($tmpRes / $arResult[$maxCoefficientKey]['count'])) ) ) < // Погрешности округления отнесём на коэффициент с максимальным весом $arResult[$maxCoefficientKey]['final'] = (0 === $precision) ? intval($tmpRes) : $tmpRes; >else < // Погрешности округления нельзя отнести на коэффициент с максимальным весом // Надо подыскать другой коэффициент $isOk = false; foreach ($arCoefficients as $keyCoefficient =>$coefficient) < if ($keyCoefficient != $maxCoefficientKey) < // Пробуем погрешность округления впихнуть в текущий коэффициент $tmpRes = $arResult[$keyCoefficient]['final'] + $sum - $allocatedAmount; if (!isset($arResult[$keyCoefficient]['count']) || (isset($arResult[$keyCoefficient]['count']) && 1 === $arResult[$keyCoefficient]['count']) || (isset($arResult[$keyCoefficient]['count']) && $arResult[$keyCoefficient]['count'] >0 && (round($tmpRes / $arResult[$keyCoefficient]['count'], $precision) == ($tmpRes / $arResult[$keyCoefficient]['count'])) ) ) < // Погрешности округления отнесём на коэффициент с максимальным весом $arResult[$keyCoefficient]['final'] = (0 === $precision) ? intval($tmpRes) : $tmpRes; $isOk = true; break; >> > if (!$isOk) < throw new Exception('Не удалось распределить погрешность округления'); >> > > return $arResult; >Проверим на тестовых значениях:
Пример, где всё распределяется без дробной части:
$arProduct = [ [ 'sum' => 1000, 'count' => 1 ], [ 'sum' => 2000, 'count' => 2 ] ]; $arResult = getProportionalSums(1000, $arProduct, 0); echo ''; print_r($arResult); echo '';
Array ( [0] => Array ( [init] => 1000 [final] => 332 [count] => 1 ) [1] => Array ( [init] => 2000 [final] => 668 [count] => 2 ) )Пример, где невозможно распределить без дробной части:
$arProduct = [ [ 'sum' => 1000, 'count' => 3 ], [ 'sum' => 2000, 'count' => 3 ] ]; $arResult = getProportionalSums(1111, $arProduct, 0); echo ''; print_r($arResult); echo '';
Как пропорционально распределить сумму из двух таблиц
(3)Формулой это типа:
Ч1 = 150
Ч2 = 100
Ч3 = 50
Сумма = Ч2 / (Ч1 + Ч3)
Ч1 = Ч1 + Ч1 * Сумма
Ч3 = Ч3 + Ч3 * СуммаВсе понятно, всем спасибо.
(8) Чтобы не путаться в понятиях:
Коэффициент = Ч2 / (Ч1 + Ч3)
Ч1 = Ч1 + Ч1 * Коэффициент
Ч3 = Ч3 + Ч3 * Коэффициент(8) А зачем так:
Ч1 = Ч1 + Ч1 * Коэффициент ?Функция РассчитатьДельту(СуммаВходящая,ВсегоСуммаВходящая,СуммаРаспределения)
ПроцентОтОбщей=СуммаВходящая /(ВсегоСуммаВходящая/100);
Дельта=Окр(СуммаРаспределения/100*ПроцентОтОбщей,2,РежимОкругления.Окр15как20);
Возврат Дельта;
КонецФункции+ потом если последняя сумма надо не функцию вызывать а тупо прибавлять оставшийся хвост. а то копейки остаются
мдэ. а что, в Советское время тоже образование хромало?
(12) хвост в виде лишней копейки, если коэффициент вида 0,3333333333333, лучше не к последней сумме прибавлять, а к максимальной. Аккуратнее получается. 🙂
(0) «программист»-гуманитарий?
(0) это шутка ?
(14)Можно и так 🙂есть 2 (два) осажденных города.
в одном 150 защитников. в другом 50 защитников.
им на подмогу идет обоз с мукой.
везут 100 кг.
Задача: сколько муки нужно завезти в каждый город через подземный ход чтобы всем защитникам досталось поровну?
Решение:
Складываем всех защитников города, делим всю муку на кол-во всех защитников, находим сколько муки приходится на 1 защитника по норме.
Умножаем норму на кол-во защитников в каждом городе.
разделяем всю муку на 2 части ПРОПОРЦИОНАЛЬНО полученной норме для каждого города.(14) В социальном государстве лучше прибавлять к минимальной сумме.
а теперь в запросе и так, чтобы копейки не терялись :)))
лично у меня — УГ получилось 🙂(20)
declare t table (id int, b decimal)
declare @sum as decimalinsert t values (1, 100), (2, 50)
select
cast(@sum / (SUM(b) OVER()) * b as decimal(15,2)) AS t
from
tУсложним задачу:
Есть главный документ.
Сумма документа = 150
Нал = 50
БезНал = 100Он делится на 2 документа.
Первый Документ
Сумма = 80
Нал = ?
БезНал = ?Второй Документ
Сумма = 70
Нал = ?
БезНал = ?Вот как тут рассчитать оплату из главного документа ?
(22) распредели сумму в 100 пропорционально между 3 одинаковимы значениями, чтобы копейки не потерялись
*одинаковыми
типа
100 + 100 + 100 + 100 => 133,34 + 133,33 + 133,33(14) В исходной задаче две суммы. В случае, если у одной суммы будет 0,3333333333333 — значит у другой 0,6666666666666. В общем, при округлении все будет хорошо. Вот если было три суммы (и более). Возникла бы ситуёвина, когда одному 0,3333333333333, другому 0,3333333333333 и третьему столько же. В результате копеечки могут и не бить.
(27) решение таки должно быть универсальным
(0) размазывать нужно как в (6) но с одной оговоркой
отсортировать суммы по убыванию и последней сумме добавить остатокСуть этого гемора такова:
Определенный товар продается на ИП, остальной на ООО (одна касса работает с двумя фискальниками), до того пока не подключили банк все было нормально, вчера подключили.т.е.
Один Чек ККМ делится на два в зависимости от товара.Для Каждого Строка Из ТЗ Цикл
Результат = НужныйРезультат * Строка.Основание / База + Отклонение;
Отклонение = (Результат — Окр(Результат, 2));
Строка.Результат = Окр(Результат, 2);
КонецЦикла;Это 1с розница, программно деление чека выглядит так:
Если Константы.ДваФР.Получить() Тогда
Запрос = Новый Запрос(»
|ВЫБРАТЬ * ПОМЕСТИТЬ ТабТов ИЗ &ТабТов КАК ТТ;
|ВЫБРАТЬ *
|ИЗ
| ТабТов КАК Товары
|ГДЕ
| Товары.Номенклатура.ТоварОрганизации = &Организация»);
Запрос.УстановитьПараметр(«ТабТов», Товары.Выгрузить());
ВремКассаККМ = КассаККМ;
Оплаты = Оплата.Выгрузить();
Организации = Справочники.Организации.Выбрать();
Пока Организации.Следующий() Цикл
Если Организации.Ссылка = Магазин.ОсновнойСклад.Организация Тогда;
Организация = Справочники.Организации.ПустаяСсылка();
Иначе
Организация = Организации.Ссылка;
КонецЕсли;
Запрос.УстановитьПараметр(«Организация», Организация);
Результат = Запрос.Выполнить();
Если Не Результат.Пустой() Тогда
ЭтотОбъект.Товары.Загрузить(Результат.Выгрузить());
ФР = ПолучитьСерверТО().ПолучитьИдентификаторПоИдКассы(Организация);
Если ЗначениеЗаполнено(Организация) И Не ПустаяСтрока(ФР) Тогда
КассаККМ = ПолучитьСерверТО().ПолучитьКассуККМ(ФР);
Иначе
КассаККМ = ВремКассаККМ;
КонецЕсли;
ИтогСуммы = Товары.Итог(«Сумма»);
ИтогОплат = Оплаты.Итог(«Сумма»);
Если ИтогСуммы <> ИтогОплат Тогда
ЭтотОбъект.Оплата.Загрузить(Оплаты);
Для Каждого ФормаОплат Из Оплата ЦиклКонецЦикла;
КонецЕсли;
ЗавершитьЗакрытиеЧека2(Печать, РучнойРежим, ВыбратьДокументПечати, ФР);
КонецЕсли;
КонецЦикла;
Иначе
ЗавершитьЗакрытиеЧека2(Печать, РучнойРежим, ВыбратьДокументПечати);
КонецЕсли;(32) запросом же
(34)Если использовать временные таблицы, то можно распределить результат, а остаток оставить на максимальной/минимальной строке
Получилось как то так:
.
Для Каждого ФормаОплат Из Оплата Цикл
Если ПервыйПроход Тогда
СуммаОплаты = ИтогСуммы * Окр(ФормаОплат.Сумма / ИтогОплат, 2);
СписокОплат.Добавить(ФормаОплат.Сумма — СуммаОплаты);
ФормаОплат.Сумма = СуммаОплаты;
Иначе
ФормаОплат.Сумма = СписокОплат.Получить(ФормаОплат.НомерСтроки — 1).Значение;
КонецЕсли;
КонецЦикла;
ПервыйПроход = 0;
.(36) В УТ или УПП глянь документ доп. расходы. Там есть алгоритм распределения суммы по кол-ву или объему