Программа для раскроя в Excel и Calc
Опубликовано 11 Июн 2019
Рубрика: Справочник Excel | 14 комментариев
Достаточно функциональную программу для решения задач экономного линейного раскроя погонажных материалов можно создать самостоятельно и абсолютно бесплатно, используя исключительно встроенные функции и штатную надстройку «Решатель» программы Calc...
...из свободно распространяемого офисного пакета LibreOffice. При этом ни к созданию макросов, ни к написанию пользовательских функций, ни к программированию вообще обращаться нет необходимости!
«Решатель» Calc — это NLPSolver (wiki.openoffice.org/wiki/NLPSolver).
Предложенное далее решение прекрасно работает и в Calc, и в Excel. Причем даже переписывать формулы не нужно! Достаточно просто скопировать ячейки с формулами и данными и вставить на лист Excel, а вместо «Решателя» использовать надстройку «Поиск решения» — Excel Solver (solver.com/excel-solver-online-help) или лучше – надстройку OpenSolver (opensolver.org).
И все-таки – почему в данном случае стоит попробовать использовать не Excel, а Calc?
- Во-первых (и это важно), в «Решателе» Calc нет ограничения по количеству переменных. В Excel «Поиск решения» может принять для поиска оптимального раскройного плана только 200 схем раскроев. (Но, если скачать и в Excel подключить OpenSolver, то получите еще более широкие возможности, чем в LibreOffice Calc!)
- Во-вторых, интерфейс «Решателя» — на русском языке. Интерфейс OpenSolver — на английском языке.
- В-третьих, в «Решателе» Calc представлены 5 различных алгоритмов поиска решения. В Excel без надстройки OpenSolver набор алгоритмов скромнее.
- В-четвертых, LibreOffice Calc, в отличие от MS Excel, бесплатен для коммерческого использования.
- В-пятых, LibreOffice Calc не обязательно устанавливать на компьютер. На официальном сайте можно скачать полнофункциональную версию — LibreOffice Portable (libreoffice.org/download/portable-versions/).
Структура задачи и терминология.
Данная тема уже была подробно рассмотрена на блоге в статье «Линейный раскрой в Excel» 4 года назад. Вновь обратиться к ней побудило желание автоматизировать генерацию схем раскроев и сравнить результаты работы «Решателя» и OpenSolver.
Полную «задачу о распиле» можно разделить на две значительные подзадачи:
- Генерация всех возможных схем раскроев.
- Поиск на основе сгенерированных схем оптимального раскройного плана.
Первая подзадача в статье «Линейный раскрой в Excel» решалась методом составления схем раскроев «вручную» по определенному алгоритму.
Вторая подзадача решалась автоматически с использованием надстройки Excel «Поиск решения» (Excel Solver).
В представленной далее программе генерация схем раскроев выполняется автоматически! Это существенно упрощает работу, защищает от ошибок и экономит время при значительном количестве типоразмеров деталей и схем раскроев.
Поиск плана раскроя осуществим тремя способами:
- В LibreOffice Calc с помощью «Решателя» (NLPSolver).
- В MS Office Excel посредством штатного «Поиска решения» (Excel Solver).
- В MS Office Excel при помощи надстройки OpenSolver.
Определимся с терминологией и ограничениями:
- Заготовки – это исходный материал в виде рулонов, прутков, полос, стержней и т.д. одинаковой длины.
- Детали – это элементы, которые необходимо получить, разрезав исходные заготовки на части.
- Схема раскроя – один из вариантов раскроя заготовки, при котором длина отхода всегда меньше длины самой короткой детали.
- План раскроя – это перечень схем раскроя с количеством их повторений.
- *Ширина реза равна нулю.
Пример.
Исходные данные для примера я и в этот раз придумывать не стал, а взял из статьи «Задача раскроя» в Википедии (ru.wikipedia.org/wiki/Задача_раскроя).
Условие задачи:
Бумагоделательная машина производит рулоны (заготовки) шириной 5600 мм.
Нужно найти план раскроя для нарезки 13 типоразмеров конечных рулонов (деталей), используя минимальное количество исходных рулонов (заготовок).
Ширины конечных рулонов (размеры деталей) и их необходимое количество — в таблице слева.
Скриншот программы:
Область для генерации схем раскроев имеет размер 13×213 ячеек, что обусловлено исключительно условиями этой конкретной задачи, и может быть изменена и в ширину и в высоту в сторону увеличения или уменьшения по желанию пользователя с соответствующей корректировкой формул.
К сожалению, качественно показать на скриншоте всю область программы для раскроя затруднительно. Скачайте файлы по ссылке под рисунком для детального просмотра листов Calc и Excel с программами.
В ячейках с желтой заливкой – не защищенные от изменений формулы!!! Будьте внимательны! Изменять значения можно только в ячейках со светло-бирюзовой заливкой.
Прошу уважающих труд автора скачать файлы с программой после подписки на анонсы статей. Подписные окна расположены в конце статьи и наверху страницы.
Ссылки на скачивание файлов с программой:
- linejnyj-raskroj-2 (ods 106,4KB);
- linejnyj-raskroj-2 (xlsx 60,3KB).
Правила ввода исходных данных:
В светло-бирюзовые ячейки записываем исходные данные из условия задачи:
- длину исходных рулонов – заготовок — Lз;
- длины конечных рулонов – деталей — Lдi;
- количество конечных рулонов – деталей — Nдi.
Длины деталей Lдi следует вписывать в порядке уменьшения размеров, слева – направо:
Lд1 > Lд2 > Lд3 > … > Lд12 > Lд13
Внимание! Если типоразмеров деталей в другой вашей задаче будет меньше 13, например 10, то, чтобы не переделывать каждый раз поле схем раскроев, в первые 3 ячейки для длин деталей следует записать значения больше размера заготовки, а их количество указать равным нулю:
Lд1 = Lд2 = Lд3 = А
А > Lз
Nд1= Nд2= Nд3=0
Решение:
После ввода всех исходных данных программа для раскроя автоматически сгенерирует 213 схем, решив, таким образом, первую подзадачу.
Действия пользователя при решении второй подзадачи – поиск оптимального плана раскроя — подробно описаны в статье о линейном раскрое в Excel. Эти действия полностью идентичны при работе со всеми тремя вышеназванными надстройками. Повторять их здесь нет смысла, так как, перейдя по ссылке, можно получить подробную, развернутую инструкцию.
Единственное, что следует дополнительно настроить:
- Из окна «Решателя» нужно перейти в окно «Параметры» и выбрать в выпадающем списке «Механизма решателя»: LibreOffice CoinMP линейный решатель.
- Там же в окне «Настройки» рекомендую проставить все галочки, так как переменные у нас в примере неотрицательные и целочисленные.
Ответ:
Минимум из 73 заготовок (исходных рулонов) можно изготовить все детали (конечные рулоны) с долей отходов всего 0,401%! В плане раскроя используется 12 схем раскроев. Время поиска решения – менее 10 секунд! (План раскроя показан выше на скриншоте программы.)
Ответ в Википедии: 73 заготовки; 0,401% отходов; в плане – 10 схем раскроев.
Итоги.
Существует несколько решений — планов раскроя с 13, 12, 11, 10 схемами раскроев, состоящих из 73 заготовок.
В таблице ниже представлены решения, найденные с использованием разных программ и надстроек. При расчетах в Excel и Calc использовались линейные механизмы поиска.
Все программы справились с поставленной задачей – нашли раскройные планы, обеспечивающие минимальное количество отходов.
Для обеспечения работоспособности стандартного «Поиска решения» (Excel Solver) в Excel пришлось удалить из поиска 13 схем раскроев с большими длинами остатков.
Если для этой задачи применить прямолинейный «жадный» алгоритм, традиционно используемый на многих производствах (сначала режем широкие рулоны, а узкие – в конце, используя по возможности отходы), то потребуется 82 исходных рулона-заготовки. Более 11,3% материала уйдет в отходы. При этом в плане будет 15 схем раскроев.
В случае необходимости минимизировать количество схем раскроев в плане стоит применить нелинейные алгоритмы надстроек из списков механизмов поиска решения.
Не используя специальных программ с помощью свободной программы LibreOffice Calc или MS Excel можно эффективно решать весьма громоздкие задачи по линейному раскрою погонажных материалов.
Возможности программы для раскроя можно существенно расширить под соответствующие задачи, увеличив количество типоразмеров деталей и пространство для схем раскроя. Ограничивающими факторами расширения являются только размеры листа программы Calc или Excel, производительность процессора и … здравый смысл.
Статьи с близкой тематикой
Отзывы
14 комментариев на «Программа для раскроя в Excel и Calc»
Ваш отзыв
Посетители: 2,1 млн
Здравствуйте. Работаю инженером-конструктором. Расчеты зубчатых и звездочек очень пригодились. Остальное осваиваю в своей практике. Большое Вам спасибо за труд.
Огромное Вам спасибо за то что делитесь своими знаниями. Благодаря вашим статьям многому научился.
Больше спасибо за труд и доходчивые объяснения. Я изучал линейное программирование, но мы пользовались программой POM fow Windows для решения задач на минимум и максимум. Тогда еще не было такого пакета в Excel. Применение расчета позволило мне съэкономить более 150 тыс. рублей при расчете бруса на дом, так как фирма сознательно завышала объем.
Спасибо Андрей, Юрий и Антон за теплые отзывы.
Антон, я рад за Вас. Не очень понял применение какой именно программы — POM fow Windows или описанных в статье помогло Вам сэкономить более 150 тыс. рублей,но это и не важно. Важно то, что Вы задумались, вспомнили линейное программирование и не дали Вас провести... Может быть в этом и заключается одно из главных предназначений высшего образования — не поверить, что-то вспомнить и разобраться. Вполне возможно, что фирма Вас и не обманывала, а просто по-привычке «пилила по „жадному алгоритму“», выбрасывая отходы...
Благодарю вас! Отличную работу проделали!
Благодарю
Александр, большое спасибо за очень грамотные расчеты, которые мне очень помогают. Проектирую оборудование для инвалидов: подъемники по лестничным маршам, эвакуаторы и т.п. С уважением, Николай.
Здравствуйте. В вашем примере при изменении длины первой детали с 2200 на 2250 длина остатка становится отрицательной (ячейка Q12=-30). При этом после «решения» этот вариант разбивки предлагается к исполнению. Что я делаю не так?
Здравствуйте. С файлом xlsx бывает возникают вопросы...
Скачайте файл ods и откройте в LibreOffice — там всё работает правильно.
Спасибо
Скачал LibreOffice, действительно работает. Дело в том, что в файле Excel в желтых ячейках отсутствуют формулы.
Теперь возникла другая проблема. В LibreOffice в вашем примере, если поставить длину исходной заготовки 12000 (вместо 5600), то в столбце R количество заготовок в решении есть и отрицательные, и дробные.
Увеличив длину исходной заготовки до 12 метров Вы получили задачу, в которой не 213 схем раскроя, а гораздо больше. Поле программы нужно расширять. Прочтите внимательно текст статьи.
Чтобы не получать дробные и отрицательные значения Вы должны выбрать механизм «Решателя», настроить его параметры и внимательно проверить адреса ячеек и соответствующие ограничения.
Чем более громоздкая задача, тем сложнее «Решателю» с ней справляться, бывает выдает ошибки или вовсе не находит решение. Задачи линейного программирования не просты...
Здравствуйте!
Интересует возможность адаптации Вашего инструмента линейного раскроя для нашего предприятия.
Прошу сообщить возможно ли коммерческое сотрудничество?
Спасибо!
Алексей, здравствуйте. Ответ — на почте.