Линейный раскрой металла в Excel чаще всего разваливается на этапе, когда деталей больше десятка, а стандартные хлысты по 6 или 12 метров перестают складываться в очевидные комбинации — и таблица, посчитанная вручную, даёт перерасход материала на целый прокат. Задача линейного (одномерного) раскроя формулируется просто: есть заготовки фиксированной длины (труба, профиль, арматура, уголок) и список деталей разной длины, которые нужно из них нарезать с минимумом отходов. Excel справляется с этой задачей без специализированных программ, если правильно построить модель.
Ниже разберём два рабочих подхода: расчёт через надстройку Поиск решения (Solver) и ручную схему с формулами для небольших номенклатур. Оба метода проверяемы, воспроизводимы и не требуют макросов, хотя вариант на VBA тоже кратко рассмотрим.
Что такое задача линейного раскроя и когда Excel подходит
Задача линейного раскроя (cutting stock problem) — классическая задача оптимизации: минимизировать количество исходных заготовок или суммарные отходы при заданном наборе деталей. В металлообработке это трубы, сортовой прокат, арматура, швеллеры, которые поставляются мерными или немерными длинами.
Excel оправдан, когда номенклатура деталей умеренная — ориентировочно до нескольких десятков позиций — и раскрой выполняется периодически, а не в потоке. Для ежедневного массового раскроя на производстве удобнее специализированные программы, но для разовых заказов, строительной площадки или небольшого цеха таблица в Excel полностью закрывает потребность.
Ключевое ограничение: классический Поиск решения работает с ограниченным числом переменных и ограничений, поэтому модель нужно строить экономно — не перебирать все мыслимые комбинации, а задавать разумный набор шаблонов раскроя.
Подготовка исходных данных для расчёта
Прежде чем строить формулы, соберите три блока данных. Первый — длина исходных заготовок: например, труба 6 м или 11,7 м, если поставщик отгружает немерный прокат. Второй — перечень деталей: длина каждой позиции и требуемое количество. Третий — технологические припуски: ширина реза (пропил дисковой пилой или ленточной пилой обычно составляет несколько миллиметров) и минимально допустимый остаток, который ещё можно считать деловым, а не отходом.
- 📏 Длина хлыста — берите из сертификата поставщика или спецификации, а не «по памяти».
- 📋 Список деталей — длина в миллиметрах, количество штук, без пропусков.
- 🪚 Ширина реза — уточните по инструменту, она напрямую съедает полезную длину.
- ♻️ Порог делового остатка — отрезки короче него сразу считайте отходом.
⚠️ Внимание: если не учесть ширину пропила, расчёт покажет красивую цифру, а на практике последняя деталь в хлысте не поместится. Закладывайте припуск на каждый рез, включая торцевую обрезку, если она требуется.
Структура таблицы: шаблоны раскроя
Основа модели — карта шаблонов. Каждый шаблон — это одна комбинация деталей, умещающаяся в один хлыст. Например: «2 детали по 2500 мм + 1 деталь по 900 мм» из трубы 6000 мм. Строки таблицы — детали, столбцы — шаблоны, на пересечении — количество деталей данного типа в шаблоне.
Для каждого шаблона формула проверяет, что суммарная длина с учётом резов не превышает длину хлыста. Удобно записать это так:
=СУММПРОИЗВ(длины_деталей; шаблон) + ширина_реза * (СУММ(шаблон) - 1) + торцевая_обрезкаРядом вычисляется отход шаблона: длина хлыста минус полученная сумма. Деловые остатки выше порога можно помечать отдельным столбцом — они потом пойдут в задел.
Генерируйте шаблоны не вручную, а отсортируйте детали по убыванию длины и комбинируйте сначала самые длинные — так вы быстрее найдёте плотные раскрои с малым отходом.
Настройка надстройки «Поиск решения»
Если надстройка не активирована, включите её через Файл → Параметры → Надстройки → Перейти → Поиск решения. Название пунктов может немного отличаться в зависимости от версии Excel — сверяйтесь со справкой вашей версии.
Модель для Solver строится так. Изменяемые ячейки — количество применений каждого шаблона (целые неотрицательные числа). Целевая функция — минимизация суммарного числа хлыстов либо суммарного отхода. Ограничение — для каждой детали сумма по всем шаблонам, умноженная на количество применений, должна быть не меньше требуемого количества.
- 🎯 Цель: минимум хлыстов (или минимум отхода — в зависимости от того, что дороже).
- 🔢 Переменные: целочисленные, галочка «неотрицательные» обязательна.
- 📐 Ограничения: обеспечить потребность по каждой позиции деталей.
- ⚙️ Метод: для целочисленной задачи используйте «Поиск решения линейных задач симплекс-методом».
☑️ Проверка модели перед запуском Solver
⚠️ Внимание: при большом числе шаблонов Solver может работать долго или останавливаться на локальном решении. Сократите набор шаблонов до разумного — уберите заведомо расточительные комбинации с огромным отходом.
Альтернатива: расчёт без Solver и типовые ошибки
Для 5–10 позиций деталей оптимизацию нередко выполняют вручную прямо в таблице: перебирают 3–5 удачных шаблонов и подбирают их количество так, чтобы закрыть потребность. Это быстрее, чем настраивать Solver, а результат для малой номенклатуры обычно близок к оптимальному. Помогает условное форматирование, подсвечивающее шаблоны с отходом выше заданного порога.
Типовые ошибки, из-за которых расчёт расходится с реальностью:
- ❌ Смешение единиц — метры в одном столбце, миллиметры в другом.
- ❌ Игнорирование кратности: хлысты продаются целиком, округлять нужно вверх.
- ❌ Отход ниже порога делового остатка записан как пригодный к использованию.
- ❌ Не учтена торцевая обрезка грязных концов проката.
Про VBA-автоматизацию
Для регулярных расчётов пишут макрос, который генерирует шаблоны раскроя перебором и передаёт их в Solver программно. Это снимает рутину, но требует навыков VBA и тщательной проверки на тестовых наборах — ошибка в генераторе шаблонов даст систематически неверные карты раскроя.
Сравнение подходов к расчёту раскроя
| Критерий | Ручной подбор в Excel | Excel + Поиск решения | Специализированная программа |
|---|---|---|---|
| Число позиций деталей | До ~10 | Десятки позиций | Сотни и более |
| Скорость настройки | Быстро | Требует построения модели | Зависит от освоения ПО |
| Качество оптимизации | Зависит от опыта | Близко к оптимуму | Высокое, эвристики и алгоритмы |
| Стоимость | Бесплатно | Бесплатно (надстройка встроена) | Лицензия или подписка |
| Учёт деловых остатков | Вручную | Через дополнительные ограничения | Обычно встроен |
Для разовых и периодических задач раскроя Excel с надстройкой «Поиск решения» — достаточный инструмент: правильно построенная модель с учётом припусков на рез даёт экономию материала, сопоставимую со специализированным ПО на небольших номенклатурах.
Практические приёмы повышения точности
Храните базу деловых остатков на отдельном листе и подключайте её к расчёту: прежде чем резать новый хлыст, проверяйте, нельзя ли выполнить деталь из остатка прошлого заказа. В таблице это реализуется дополнительным блоком «заготовки переменной длины», где длина каждого остатка задаётся индивидуально.
Второй приём — сценарный анализ. Посчитайте раскрой для разных длин поставки: иногда закупка хлыстов 12 м вместо 6 м заметно снижает отход, и наоборот. Разница в отходе между «удобной» и «неудобной» длиной поставки при неудачной номенклатуре деталей может достигать заметной доли от стоимости металла, поэтому такой пересчёт почти всегда окупается.
Наконец, выводите итоговую карту раскроя в печатную форму: номер хлыста, шаблон, детали, отход. Это рабочий документ для резчика, а не просто расчёт «для себя».
Добавьте в таблицу столбец «себестоимость отхода» — отход в килограммах, умноженный на цену металла за килограмм. Цифра в рублях дисциплинирует сильнее, чем проценты.
FAQ: частые вопросы о линейном раскрое в Excel
Можно ли считать раскрой без надстройки «Поиск решения»?
Да. Для небольшого числа деталей шаблоны подбирают вручную, а количество применений — простым перебором с контролем остатков по формулам. Это трудоёмко, но вполне работоспособно для 5–10 позиций.
Как учесть ширину реза в формулах?
Каждый рез уменьшает полезную длину хлыста. Если в шаблоне N деталей, резов обычно N−1 (плюс торцевая обрезка, если она нужна). Формула: сумма длин деталей + ширина реза × (N−1) + обрезка ≤ длина хлыста.
Почему Solver выдаёт дробные значения количества хлыстов?
Не задано ограничение целочисленности. Выделите изменяемые ячейки и добавьте ограничение типа «цел» (integer) в параметрах Поиска решения. Без этого надстройка решает непрерывную задачу, которая не соответствует реальности.
Что делать, если деталей слишком много и Solver работает медленно?
Сократите число шаблонов, убрав комбинации с большим отходом, сгруппируйте одинаковые детали в одну позицию с количеством и ограничьте время поиска в параметрах. При регулярной большой номенклатуре имеет смысл рассмотреть специализированное ПО для раскроя.
Подходит ли этот метод для листового металла?
Нет, листовой раскрой — это двумерная задача, и линейная модель ей не соответствует. Для листа нужны другие алгоритмы и, как правило, специализированные программы. В Excel двумерный раскрой реализуется лишь грубыми приближениями.