Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как составить формулу, чтобы при суммировании получилось наиболее приближенное значение
 
#1
27.05.2019 23:23:19
Уважаемые форумчане при строительстве кровли объекта столкнулся вот такой проблемой:

Имеются готовые листы металлочерепицы длиной 85см, 185см и 220см.

Из этих листов надо выбрать наиболее подходящие листы, чтобы при монтаже кровли отходы  металлочер. были наименьшими.
Крыша 4-х скатная вальмовая, т.е. если начать с угла, то каждый последующий лист будет увеличиваться в размере.
При стыке 2-х и более листа нахлест листов составляет 14см,
Отклонение в меньшую  сторону 5см от заданных значений устраивает, отклонение в большую сторону, чем меньше тем лучше!

Например: - для 1 полосы кровли размер необходим 170см - соответственно выбираем лист размером 185см;

- размер 2 полосы кровли  421см - выбираем из 3-х листов размерами 185см, 85см и 185см (185+185+85-14-14см)= 427см, отход 6см.

- размер 3 полосы кровли  636см - (если выбрать из 185см х 4листа=740см-14-14-14=698см, то отход 62см; если выбрать из 220*3=660-(14*2)=632см, т.е. нехватает 4см, но отклонение меньше 5см. Значить выбираем 632см из 3-х листов и т.д.

Нужно из нескольких сот листов вот таким образом выбирать.

Как на таблице Excel составить формулу, чтобы решить эту задачу.  
Изменено: BakEA - 28.05.2019 07:55:13
 
 
 
#2
27.05.2019 23:25:21
Цитата
BakEA написал:
чтобы при их суммировании
Кого их? Это я про название темы.
 
 
 
#3
28.05.2019 00:33:06
подготовите задачу для решения в Excel решим ее в Excel, а пока - решайте самостоятельно на крыше
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#4
28.05.2019 07:44:14
BakEA, задачу сперва нужно решить на технологическом уровне, Вы указали параметр "отходы  металлочер. были наименьшими." - количество стыков, количество листов используемых при этом и их стоимость получается  не волнует? Насколько я понимаю, то остаток использовать нельзя, так как необработанный срез должен быть прикрыт, так? Ну и конечно замечание модератора про тему нужно исправить.
Изменено: БМВ - 28.05.2019 08:21:12
По вопросам из тем форума, личку не читаю.
 
 
 
#5
28.05.2019 07:54:50
Цитата
БМВ написал: х стои
Исправил)

Цитата
Насколько я понимаю, то остаток использовать нельзя, так как необработанный срез должен быть прикрыт, так?
Вы правы, остатки металлочерепицы не используются.

Пример прикрепил
 
 
 
#6
28.05.2019 10:33:59
Решение с помощью UDF, проверьте
Не универсально, чтобы сделать под любые размеры нужно существенно больше времени
 
 
 
#7
28.05.2019 18:14:54
Большое спасибо добрый друг!

Только есть некоторые неточности. Например, есть готовый лист 220см, для размера 225 программа дает результат 3 листа по 85см (85х3-14-14=227см). Хотя можно было бы взять лист размером 220, т.е.отклонение -5см допустимо. Если смотреть чисто с финансовой стороны, то 3 листа по 85 = 255см, разницу 35см приходится мне переплачивать  продавцу).
 
 
 
#8
28.05.2019 21:37:54
Цитата
BakEA написал:
для размера 225 программа дает результат 3 листа по 85см (85х3-14-14=227см). Хотя можно было бы взять лист размером 220, т.е.отклонение -5см допустимо.
Я специально так сделал, т.к. подумал, что если используется несколько листов, то допуск в -5 см можно отыграть нахлестом, при одном листе не допускается уменьшение размера.

Если допустимо для размера 225 брать лист 220, то это даже немного проще (убрал дополнительную проверку)
 
 
 
#9
28.05.2019 21:58:51
Цитата
приходится мне переплачивать  продавцу
Не плати продавцу вообще: покупай у изготовителя. Он и порубит сразу по размеру, без стыков и нахлёста будет. И качественней и дешевле
 
 
 
#10
28.05.2019 22:24:31
MCH,
нужно менять методику оценки оптимальнеого подбора листов
допустим нужно закрыть 10м = 1000 см.
можно взять 14 листов по 85 = 85 + (14-1)*(85-14) = 1008
или
5 по 220 = 220+4*206 = 1044
казалось бы 14 по 85 выгоднее - всего 8 см. в отходы?
а в случае 5х220 - имеем 44 см отходов. так?
а если просто посчитать сколько погонных метров листов было использовано, получим
1) 14 * 85 = 1190 см
2) 5 * 220 = 1100 см
в случае 1 нужно покупать на 90см больше материала, чем в случае2, не смотря на то, что в отходы 1) = 8см, 2) = 44
автор темы может точную сумму сказать, во сколько обойдуться 14 листов шириной 85 и 5 листов шириной 220 см.

и, видимо оптимальным будет 4х220 + 1х185, общая длина = 1065, длина покрытия = 185 +4*206 = 1009
начальная формулировка не соотв. сути задачи:
видимо
задача не в том чтобы получить минимум отходов
а
задача минимальным количеством погонных метров материала набрать требуемую длину с учетом нахлеста листов
Изменено: Ігор Гончаренко - 28.05.2019 22:54:21
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#11
28.05.2019 22:43:39
Александр Моторин,
1. не станет производмтель ничего менять в производстве - как выпускал 3 типоразмера 850, 1850, 2200 мм так и будет их выпускать, и даже если есть такие технические возможности, то кусок крыши например длиной 100 м будет трудно  (скорее невозможно) транспортировать до места монтажа и так же трудно потом смотнировать на месте.
2. не станет призводитель ничего продавать конечному потребителю дешевле, демпингуя тем самым на рынке, и ломая бизнес своим диллерам, которые покупают вагонами, и продают поштучно конечному потребителю, у которых сеть магазинов консультанты, продавцы, кассиры и пр.  сделать - это один бызнес, продать потребителю - другой
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#12
28.05.2019 22:55:15
Цитата
Ігор Гончаренко написал:
нужно менять методику оценки оптимальнеого подбора листовдопустим нужно закріть 10м = 1000 см.можно взять 14 листов по 85 = 85 + (14-1)*(85-14) = 1008 или5 по 220 = 220+4*206 = 1044
Мой алгоритм на 1000 см рассчитал 1*185+4*220 = 1065
с учетом нахлестов 1065 - 4*14 = 1009

В качестве критерия оптимальности я взял общую длину профиля, а не минимизацию обрези
Была бы известна цена на каждый лист, то можно было бы оптимизировать по этому критерию
 
 
 
#13
28.05.2019 23:03:25
MCH, извини, не смотрел в файл, думал, решается эта задача:
Цитата
BakEA написал:
чтобы при монтаже кровли отходы  металлочер. были наименьшими
но такую то же можно решать))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#14
28.05.2019 23:05:57
Off
Ігор Гончаренко, Нет не так  ( https://www.onduline.ru/blog/metallocherepica-standartnye-razmery) все индивидуально и длина ограничена только возможностью технологического оборудования. Другое дело что транспортировка сложнее, монтаж, не проще, несмотря на то что стыков меньше … .

Честно , не смотрел что у MCH, да и не стал решать, ведь пока  хотя жадный алгоритм применим, а размер такой, что всегда будет вариант в котором  доп. лист малого размера будет 0-1 лист. и также среднего.
По вопросам из тем форума, личку не читаю.
 
 
 
#15
29.05.2019 10:58:41
Цитата
MCH написал:
Я специально так сделал, т.к. подумал, что если используется несколько листов, то допуск в -5 см можно отыграть нахлестом, при одном листе не допускается уменьшение размера.Если допустимо для размера 225 брать лист 220, то это даже немного проще (убрал дополнительную проверку)
МСН, Вы реально "монстр" программирования!

Спасибо БОЛЬШОЕ!!!
 
 
 
#16
29.05.2019 11:41:09
Чуть поправил код UDF (на результат не повлияло) и добавил расчет потерь и частотный анализ

У меня решение простое, но не универсальное (сделано под текущую задачу с указанным наборов длин)
Основанное на переборе вариантов с двумя вложенными циклами.

Задача интересная с точки зрения общего случая, когда размеры листов можно указывать вручную (различный перечень размеров, их количество, ограничение в количестве каждого размера если нужно)
Постановка задачи схожа с "задачей о рюкзаке" и решения ее могут быть соответствующие:
Полный перебор всех вариантов и выбор наилучшего
Динамическое программирование
Линейное программирование
 
 
 
#17
29.05.2019 12:45:51
Цитата
MCH написал:
MCH
Можно ли делать привязку на ячейки размера листа, чтобы при удалении размера листа (85,185,220 или при добавлении дополнительных столбцов) данных, лист сформировался из оставшихся 2-х листов?
Например, если 85см листа нет в наличии у продавцов, то можно было скомпоновать листы из двух оставшихся листов (185см и 220см) или можно было добавить листы другого размера?

Образец файла прилагается

P.S. Позвонил продавцу металлочерепицы, он вроде бы говорить, что у них в данное время нет в наличии 85см листа черепицы. Попробовал удалить данные из ячейки C4, но таблица все равно считает этот размер (85см)
 
 
 
#18
29.05.2019 15:27:48
Данная задача не тривиальная
Во вложение решении с помощью динамического программирования
можно задавать различные размеры листов, указать не более какого количества использовать (если нужно исключить, то можно указать количество = 0)
А также задать цену на каждый лист, оптимизация происходит по данному параметру.
 
 
 
Страницы: 1
Наверх