Программа для раскроя в Excel и Calc

Опубликовано 11 Июн 2019
Рубрика: Справочник Excel | 6 комментариев



Значок Честный распил!Достаточно функциональную программу для решения задач экономного линейного раскроя погонажных материалов можно создать самостоятельно и абсолютно бесплатно, используя исключительно встроенные функции и штатную надстройку «Решатель» программы 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.

Полную «задачу о распиле» можно разделить на две значительные подзадачи:

  1. Генерация всех возможных схем раскроев.
  2. Поиск на основе сгенерированных схем оптимального раскройного плана.

Первая подзадача в статье «Линейный раскрой в Excel» решалась методом составления схем раскроев «вручную» по определенному алгоритму.

Вторая подзадача решалась автоматически с использованием надстройки Excel «Поиск решения» (Excel Solver).

В представленной далее программе генерация схем раскроев выполняется автоматически! Это существенно упрощает работу, защищает от ошибок и экономит время при значительном количестве типоразмеров деталей и схем раскроев.

Поиск плана раскроя осуществим тремя способами:

  1. В LibreOffice Calc с помощью «Решателя» (NLPSolver).
  2. В MS Office Excel посредством штатного «Поиска решения» (Excel Solver).
  3. В MS Office Excel при помощи надстройки OpenSolver.

 Определимся с терминологией и ограничениями:

  1. Заготовки – это исходный материал в виде рулонов, прутков, полос, стержней и т.д. одинаковой длины.
  2. Детали – это элементы, которые необходимо получить, разрезав исходные заготовки на части.
  3. Схема раскроя – один из вариантов раскроя заготовки, при котором длина отхода всегда меньше длины самой короткой детали.
  4. План раскроя – это перечень схем раскроя с количеством их повторений.
  5. *Ширина реза равна нулю.



Пример.

Таблица Рулоны - деталиИсходные данные для примера я и в этот раз придумывать не стал, а взял из статьи «Задача раскроя» в Википедии (ru.wikipedia.org/wiki/Задача_раскроя).

Условие задачи:

Бумагоделательная машина производит рулоны (заготовки) шириной 5600 мм.

Нужно найти план раскроя для нарезки 13 типоразмеров конечных рулонов (деталей), используя минимальное количество исходных рулонов (заготовок).

Ширины конечных рулонов (размеры деталей) и их необходимое количество — в таблице слева.

Скриншот программы:

Область для генерации схем раскроев имеет размер 13×213 ячеек, что обусловлено исключительно условиями этой конкретной задачи, и может быть изменена и в ширину и в высоту в сторону увеличения или уменьшения по желанию пользователя с соответствующей корректировкой формул.

К сожалению, качественно показать на скриншоте всю область программы для раскроя затруднительно. Скачайте файлы по ссылке под рисунком для детального просмотра листов Calc и Excel с программами.

В ячейках с желтой заливкой – не защищенные от изменений формулы!!! Будьте внимательны! Изменять значения можно только в ячейках со светло-бирюзовой заливкой.

Скриншот Программа для раскроя в Calc

Прошу уважающих труд автора скачать файлы с программой после подписки на анонсы статей. Подписные окна расположены в конце статьи и наверху страницы.

Ссылки на скачивание файлов с программой:

Правила ввода исходных данных:

В светло-бирюзовые ячейки записываем исходные данные из условия задачи:

  • длину исходных рулонов – заготовок — ;
  • длины конечных рулонов – деталей — i;
  • количество конечных рулонов – деталей — i.

Длины деталей i следует вписывать в порядке уменьшения размеров, слева – направо:

1 > 2  > 3 > … > 12 > 13

Внимание! Если типоразмеров деталей в другой вашей задаче будет меньше 13, например 10, то, чтобы не переделывать каждый раз поле схем раскроев, в первые 3 ячейки для длин деталей следует записать значения больше размера заготовки, а их количество указать равным нулю:

1 = 2 = 3 = А

А >

1= 2= 3=0

Решение:

После ввода всех исходных данных программа для раскроя автоматически сгенерирует 213 схем, решив, таким образом, первую подзадачу.

Действия пользователя при решении второй подзадачи – поиск оптимального плана раскроя — подробно описаны в статье о линейном раскрое в Excel. Эти действия полностью идентичны при работе со всеми тремя вышеназванными надстройками. Повторять их здесь нет смысла, так как, перейдя по ссылке, можно получить подробную, развернутую инструкцию.

Единственное, что следует дополнительно настроить:

  • Из окна «Решателя» нужно перейти в окно «Параметры» и выбрать в выпадающем списке «Механизма решателя»: LibreOffice CoinMP линейный решатель.
  • Там же в окне «Настройки» рекомендую проставить все галочки, так как переменные у нас в примере неотрицательные и целочисленные.

Ответ:

Минимум из 73 заготовок (исходных рулонов) можно изготовить все детали (конечные рулоны) с долей отходов всего 0,401%! В плане раскроя используется 12 схем раскроев. Время поиска решения – менее 10 секунд! (План раскроя показан выше на скриншоте программы.)

Ответ в Википедии: 73 заготовки; 0,401% отходов; в плане – 10 схем раскроев.

Итоги.

Существует несколько решений — планов раскроя с 13, 12, 11, 10 схемами раскроев, состоящих из 73 заготовок.

В таблице ниже представлены решения, найденные с использованием разных программ и надстроек. При расчетах в Excel и Calc использовались линейные механизмы поиска.

Таблица Excel Количество заготовок по планам и раскроям

Все программы справились с поставленной задачей – нашли раскройные планы, обеспечивающие минимальное количество отходов.

Для обеспечения работоспособности стандартного «Поиска решения» (Excel Solver) в Excel пришлось удалить из поиска 13 схем раскроев с большими длинами остатков.

Если для этой задачи применить прямолинейный «жадный» алгоритм, традиционно используемый на многих производствах (сначала режем широкие рулоны, а узкие – в конце, используя по возможности отходы), то потребуется 82 исходных рулона-заготовки. Более 11,3% материала уйдет в отходы. При этом в плане будет 15 схем раскроев.

В случае необходимости минимизировать количество схем раскроев в плане стоит применить нелинейные алгоритмы надстроек из списков механизмов поиска решения.

Не используя специальных программ с помощью свободной программы LibreOffice Calc или MS Excel можно эффективно решать весьма громоздкие задачи по линейному раскрою погонажных материалов.

Возможности программы для раскроя можно существенно расширить под соответствующие задачи, увеличив количество типоразмеров деталей и пространство для схем раскроя. Ограничивающими факторами расширения являются только размеры листа программы Calc или Excel, производительность процессора  и … здравый смысл.

Другие статьи автора блога

На главную


Введите Ваш e-mail:

Статьи с близкой тематикой

Отзывы

6 комментариев на «Программа для раскроя в Excel и Calc»

  1. Андрей 12 Июн 2019 09:59

    Здравствуйте. Работаю инженером-конструктором. Расчеты зубчатых и звездочек очень пригодились. Остальное осваиваю в своей практике. Большое Вам спасибо за труд.

  2. Юрий 12 Июн 2019 11:25

    Огромное Вам спасибо за то что делитесь своими знаниями. Благодаря вашим статьям многому научился.

  3. Антон 16 Июн 2019 22:26

    Больше спасибо за труд и доходчивые объяснения. Я изучал линейное программирование, но мы пользовались программой POM fow Windows для решения задач на минимум и максимум. Тогда еще не было такого пакета в Excel. Применение расчета позволило мне съэкономить более 150 тыс. рублей при расчете бруса на дом, так как фирма сознательно завышала объем.

  4. Александр Воробьев 16 Июн 2019 23:28

    Спасибо Андрей, Юрий и Антон за теплые отзывы.

    Антон, я рад за Вас. Не очень понял применение какой именно программы — POM fow Windows или описанных в статье помогло Вам сэкономить более 150 тыс. рублей,но это и не важно. Важно то, что Вы задумались, вспомнили линейное программирование и не дали Вас провести... Может быть в этом и заключается одно из главных предназначений высшего образования — не поверить, что-то вспомнить и разобраться. Вполне возможно, что фирма Вас и не обманывала, а просто по-привычке «пилила по „жадному алгоритму“», выбрасывая отходы...

  5. Дмитрий 17 Авг 2019 00:00

    Благодарю вас! Отличную работу проделали!

  6. serik 08 Фев 2020 17:25

    Благодарю

Ваш отзыв



  • Подписчики: 7,1 тыс.

  • Посетители: 1,4 млн