Страницы: 1
RSS
Как корректно изменить цены, не меняя количество, чтобы сумма по прайсу стала равна заданной, Пропорция и/или скользящее округление формулами, PQ, VBA и прочими инструментами
 
Приветствую!

Дано: 2 столбца (количество "КОЛ" и цена за 1 единицу измерения "ЦЕНА")
Задача: получить НОВЫЕ ЦЕНЫ (путём вычисления коэффициента умножения) так, чтобы НОВАЯ СУММА (КОЛ * НОВ ЦЕНА) стала равна заданной (копейка в копейку)
Ограничения:
    • НОВЫЕ цены и суммы должны быть округлены (на самом деле, а не форматом) до 2ух знаков
    • все количества и цены больше ноля (иначе зачем всё это)
    • КОЛИЧЕСТВО может НЕ быть целочисленным (это нормальное явление, которое нужно учитывать)

Пропорция в данном случае полностью задачу не решает - остаётся хвост в 5 рублей, который надо куда-то прятать
Скрины «анализ», «вручную» и  «скользящее»
Вдохновлялся этой темой (пример как раз оттуда) и производственной необходимостью по работе  :)
Поиск по форуму «Скользящее округление» (особое внимание на посты sokol92 и БМВ)

Файл: Math. Proportion.xlsb (42.19 КБ)

UPD1: Понял, что задача не так проста, какой показалась. Всё-таки это комбинаторика, а не простая математика и логика
Полагаю, что можно свести задачу к виду «Изменить цены, не меняя количества так, чтобы сумма по прайсу стала равна заданной, а также свести к минимуму отклонения коэффициентов каждой цены от среднего коэффициента по всем ценам»

UPD2: VBA. Вариант через пошаговое приближение (принцип, как формулами на листе «скользящее»)
Изменено: Jack Famous - 23.08.2021 15:23:22
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
del
Изменено: buchlotnik - 21.08.2021 17:00:09
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, Миш, спасибо за вариант. Ты бы хоть там подробнее описал что-ли - куча данных и целой картины не видно))
Я так понимаю, что задача всё-таки для комбинаторики, а не простой математики и логики — сейчас соберу мысли о обновлю шапку темы
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
del
Изменено: buchlotnik - 21.08.2021 17:00:27
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik: я ему времени нет, а он
так я ж без наезда - просто по факту, что не понятно  :D
Цитата
buchlotnik: если например все количества кратны 100, то подбор можно будет осуществить только до рублей
одним из исходов как раз является НЕВОЗМОЖНОСТЬ подобрать заданную сумму
Цитата
buchlotnik: комбинаторика - это простая математика, раз уж я ее понимаю
тогда я тупой, а это не так, а значит всё-таки комбинаторика непростая штука, просто ты умный  :D

Мне надо что-то попроще, чем дискретность и прочие страшные слова. Попробую сделать решение перебором. Руками я делал так: сортируем по количеству (по возрастанию) и далее по цене (по убыванию). Меньше всего будет заметно (минимальное отклонение от коэффициента пропорции) в позициях с маленьким количеством и большой ценой. То есть, если по позиции количество 5, а цена 1 000, то сумму такой позиции можно менять с шагом в 0,01 * 5 = 0,05 (5 копеек), при этом отклонение самой цены с каждым шагом будет 0,01 / 1 000 = 0,00001 (примерно то же, что ты сказал, но своими словами)

Обновил шапку
Изменено: Jack Famous - 18.08.2021 11:14:52
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
del
Изменено: buchlotnik - 21.08.2021 17:00:39
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik: жадное и на формулах?
это оставим формулистам на суд - я не понял, что там (но спасибо, разумеется, за вариант)  :D
Предложу на VBA сейчас что-нибудь …
Изменено: Jack Famous - 18.08.2021 12:43:24
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
я не понял, что там
жадный алгоритм - ищет количество с минимальной разницей с искомым, плюсует (или минусует) и далее ищет уже оставшуюся разницу, не трудно заметить, что на нечетных просто циклится
Соблюдение правил форума не освобождает от модераторского произвола
 
Добавил в шапку вариант на VBA через пошаговое приближение
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Поскольку задача первоначально носила практический характер, то я бы (как бухгалтер) поступил так (аналогично ответу #4):
1. На первом шаге пропорционально изменяем цены (в примере #1 остается разница 4,94)
2. В ручном режиме исправляем разницу строками с наименьшим количеством (в #1 можно поправить ценой по строке 28). По крайней мере, будешь знать, где подгонял под ответ.  :)  
Изменено: sokol92 - 18.08.2021 19:34:01
Владимир
 
sokol92, приветствую и спасибо за ответ!
Глаза глазами, а всё-таки бухгалтер решения на основе какой-то аналитики принимает (ты как раз говорил про количество, а я бы ещё и цену учитывал). А значит эту аналитику можно перенести на код (как минимум для надёжности и однообразности подгонов).
Попробую сделать вариант с пропорцией и расбросом хвостов, но сразу скажу, что в плане отклонения от коэффициента пропорции он будет хуже
Ну а видеть, что и насколько поменялось можно легко - ключи-то тоже передаются   ;)
Изменено: Jack Famous - 18.08.2021 19:45:15
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
sokol92 написал:
2. В ручном режиме исправляем разницу строками с наименьшим количеством
я б еще добавил и с наибольшей ценой. Это дает минимальное процентное искажение относительно начальной.
По вопросам из тем форума, личку не читаю.
 
БМВ, я про то же. Это и реализовано в VBA, а также визуализировано на листе "скользящее". Беспокоит только, что этот подход всё-таки не замена комбинаторике, которая лучше всего справится с критерием минимального отклонения от среднего коэффициента. Попробую что-то накидать завтра, но ссыкотно)
Михаилу Ч написал  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
Как корректно изменить цены, не меняя количество, чтобы сумма по прайсу стала равна заданной
т.е. в сообщении #1 написано, как это не нужно делать?
как только будут определены условия задачи - можно будет подумать как ее решать,
пока нет условий, а есть только какие-то метания из одного предположения к другому - решать нечего))
Изменено: Ігор Гончаренко - 18.08.2021 22:56:23
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ой, кажется кто-то опять нахлобучился на ночь глядя  :D  :D  :D
Проходи мимо, дядь)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
кто-то не понимает как решать задачу, в которой нет описания (нет четких критериев, которым должно соответствовать решение))
Изменено: Ігор Гончаренко - 19.08.2021 12:18:20
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, ок отвечу один раз, хоть и считаю ,что вы, сударь, тролль, каких поискать  :D
Цитата
Ігор Гончаренко: кто-то не понимает как решать задачу
ложь
Решил одним из способов. Получил корректный ответ. Что ещё нужно: вариант на комбинаторике как минимум. Есть подозрение, что мой вариант может не выйти на сумму, хотя исход возможен
Цитата
Ігор Гончаренко: нет описания (нет четких критериев, которым должно соответствовать решение)
снова ложь
Цитата
Jack Famous: Полагаю, что можно свести задачу к виду «Изменить цены, не меняя количества так, чтобы сумма по прайсу стала равна заданной, а также свести к минимуму отклонения от среднего коэффициента по всем ценам у полученных коэффициентов для каждой цены
по-моему крайне однозначно и чётко сформулировал
Изменено: Jack Famous - 19.08.2021 13:20:22
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
конечно тебе виднее понимаю я задачу или нет
и "ложь" - это мягко сказано и моих сообщениях, "гнусная ложь" - более подходящее определение)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Ігор Гончаренко, видишь? Ты ничего по делу не пишешь. Только какую-то чепуху несёшь и мусоришь. И так не только у меня. Прошу - не захожи в мои темы, пожалуйста, тебе тут не рады. Если в следующий раз будешь думать, почему я не отвечаю на твою ересь, то приди сюда и перечитай это сообщение
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Ігор Гончаренко, несмотря на мои терки с Jack Famous, тут я с ним полностью согласен. Уж больно часто ты стал выступать, с "задача не ясна, я умываю руки" в разных темах. Никто не заставляет ни делать, ни отписываться. Я понимаю, когда на наводящие вопросы по существу нет ответа , тогда все корректно, но вступить в #14 с таким, после того как даже ответы есть от тех, кто "курсы трактористов" не заканчивал, как минимум странно.
По вопросам из тем форума, личку не читаю.
 
OFF
Цитата
БМВ: терки
так - а чё я не в курсе, что у нас тёрки какие-то?  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Вариант решения:
1. Определяем новые цены для получения нужной суммы, возможны варианты:
1.1. Округляем цены до копеек с учетом пропорционального множителя
1.2. Округляем цены вниз
1.3. Округляем ценны вверх
1.4. Любое другое округление

В результате п.1. получаем приближенную сумму, она может быть больше или меньше искомой

2. Подбираем корректировки цен, которые приведут к минимальному отклонению суммы от нужной
Корректируем на целые копейки, задачу сводим к решению задачи "сумма подмножеств" с помощью динамического программирования, где в качестве слагаемых будут количества

3. Для наименьшего отклонения от пропорционального коэффициента:
задаем ограничение - на сколько можно менять цену в копейках
сортируем цены от наибольшей к наименьшей, что при решении задачи "сумма подмножеств" будет давать корректировки наибольших цен

В примере получилось, что допустимо корректировать цены не более чем на 2 копейки при любом из вариантов первоначального округления.
 
MCH, огромное спасибо за отличный вариант!
Как и ожидалось - вариант подбора является самым оптимальным с точки зрения отклонения: 2 копейки против 8ми в моём VBA-варианте "скользящего приближения" (и ужасные 62 копейки, если вручную)
Попробую сделать что-то подобное, но своё. Сижу, разбираюсь … :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Внес поправки в модель:
Сделал возможность округлять цены до кратности в копейках (1, 5, 10, 50, 100 копеек), чтобы не было проблем при добавлении к цене НДС и получалось целое число
Добавил возможность работать с дробным количеством (3 знака после запятой), за счет округления итоговая сумма может отклоняться от искомой на несколько копеек
Дополнил строки, можно использовать перечень из 100 позиций

Не ставил перед собой задачу - поиск наилучшего результата корректировки, при ограничении на сколько копеек можно менять цену и так получится приемлемый результат, который рассчитывается очень быстро
 
MCH, это отличный вариант — спасибо!  :idea:

Я тут создал отдельную тему, чтобы написать более универсальное решение, которое подойдёт, в том числе, и для решения ЭТОЙ задачи. Помогите, если сможете, пожалуйста…
Тема для написании функции полного (почти) перебора
Краткая суть:
Изменено: Jack Famous - 25.08.2021 11:36:39
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Jack Famous написал:
функция для перебора заданного количества значений с анализом каждой комбинации на попадание в диапазон и отсевом ненужных
Так уже есть что то подобное: Подбор слагаемых под нужную сумму и Задача о рюкзаке

В задаче "Сумма подмножеств" (подбор слагаемых под нужную сумму) есть вариант полного перебора с использованием МВиГ и выводом всех возможных вариантов
А тот же "рюкзак", если его применить к текущей задаче, лучше решать динамическим программированием, где в качестве цены нужно задать отклонение цены от среднего коэффициента и выбрать тот вариант, в котором будет наименьшее суммарное отклонение.
 
MCH, прошу, если возможно, сделайте, пожалуйста, в теме вариант функции на основе своих наработок, согласно условий (входящие данные и отчёт)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1
Наверх