Бак для воды? «Поиск решения» в Excel!
Опубликовано 02 Авг 2013
Рубрика: Справочник Excel | 5 комментариев
Попробуем «догнать двух зайцев» — познакомиться с работой очень интересного и мощного инструмента Excel – надстройки «Поиск решения» и научиться математически грамотно проектировать баки для воды на дачу… Что может связывать эти две темы?..
...Оказывается — очень многое.
Надстройка «Поиск решения» позволяет найти оптимальные значения аргументов в задачах «на экстремум» и иных задачах оптимизации функции нескольких переменных при заданных ограничивающих условиях. Слишком мудрено? Давайте рассмотрим практический пример — и все станет просто и понятно!
При проектировании и изготовлении любой емкости для хранения газов, жидкостей, сыпучих твердых материалов разработчик часто стремится получить максимальный объем при минимальной площади поверхности — при этом наиболее рационально и с пользой расходуется материал. Безусловно, в погоне за экономией материалов нельзя забывать вопросы прочности и жесткости стенок резервуара, а так же вопросы технологичности последующих эксплуатации и обслуживания емкости.
Формулируем условие задачи:
Из имеющегося в наличии стального листа толщиной 4 мм стандартных размеров 1,5 м на 6,0 м необходимо сварить бак для воды (дно и боковые стенки без крышки), например, на дачу максимального объема, оптимальной формы, с удобным и простым раскроем, с минимальной трудоемкостью изготовления.
Конечно, всем известно, что максимальный объем при минимальной площади поверхности имеет шар, но сложность и трудоемкость его изготовления, а так же неудобства эксплуатации и обслуживания «съедают» всю выгоду от минимального расхода материала. К тому же реальный коэффициент использования материала из-за криволинейного раскроя далек от единицы. Емкости-шары применяют для хранения газов под давлением для обеспечения равномерного распределения возникающих в стенке сосуда напряжений и исключения зон концентрации напряжений, приводящих к разрушению объектов. При поиске решения для нашей задачи мы шар рассматривать не будем.
Для решения поставленной задачи привлечем в помощь Excel.
При отсутствии на вашем компьютере программы MS Excel можно выполнить расчет в программе Calc из бесплатного пакета Open Office. Надстройке «Поиск решения» MS Excel в OOo Calc соответствует «Решатель».
Исходные данные — в ячейках со светло-бирюзовой заливкой, результаты расчетов — в ячейках со светло-желтой заливкой. Результаты работы надстройки «Поиск решения» — в ячейках с золотистой заливкой.
Открываем лист Excel и заполняем таблицу с параметрами прямоугольной листовой заготовки:
1. Толщину листа S в миллиметрах заносим
в ячейку D3: 4,000
2. Ширину листа В в метрах пишем
в ячейку D4: 1,500
3. Длину листа L в метрах пишем
в ячейку D5: 6,000
Предварительные расчеты в Excel выполняем по формулам:
4. Площадь поверхности (с одной стороны) A0 в метрах квадратных считаем
в ячейке D6: =D4*D5=9,000
A0=B*L
5. Массу G в килограммах (для справки) рассчитываем
в ячейке D7: =D3/10*D4*100*D5*100*7,85/1000=282,600
G=S*B*L*7,85
Первой проанализируем цилиндрическую форму емкости с круговым дном:
1. Записываем формулу для расчета площади поверхности A1 в квадратных метрах
в ячейку G13: =ПИ()*E13^2/4+ПИ()*E13*F13
A1=3.14*y^2/4+3.14*y*z
2. Записываем формулу для расчета объема бака V в кубических метрах
в ячейку H13: =ПИ()*E13^2/4*F13
V=3.14*y^2/4*z
3. Вызываем диалоговое окно надстройки «Поиск решения» (в Excel 2003: Сервис – Поиск решения…) и заполняем так, как на снимке экрана, представленном ниже.
Мы хотим получить в целевой ячейке H13 (V — объем бака) максимальное значение, изменяя значения ячеек E13 (y — диаметр бака) и F13 (z — высота бака). При этом мы сообщаем Excel ограничения: E13 (диаметр бака) и F13 (высота бака) – это положительные числа, а значение в ячейке G13 (A1 — площадь поверхности) равно значению в ячейке D6 (A0 — площадь листа-заготовки).
Таким образом, мы сформулировали задачу поиска диаметра и высоты цилиндрического бака из конкретного количества материала для получения максимального объема. О том, что это цилиндр с круглым дном, но без крышки «сказано» в формулах расчета A1 и V.
4. Нажимаем кнопку «Выполнить» окна Поиск решения». Окно «Поиск решения» исчезает и появляется окно «Результаты поиска решения». При этом найденные значения мгновенно отображается в соответствующих ячейках:
5. Диаметр бака y в метрах считываем
в ячейке E13: 1,954
6. Высоту бака z в метрах считываем
в ячейке F13: 0.977
7. Площадь поверхности бака A1 в метрах квадратных считываем
в ячейке G13: 9.000
8. Объем бака V в метрах кубических считываем
в ячейке H13: 2.932
9. Нажимаем на кнопку «ОК» — сохраняем найденное решение
10. Анализируем полученные результаты и делаем выводы:
— цилиндрическая емкость с дном и без крышки имеет максимальный объем и минимальную площадь поверхности при диаметре в два раза большем высоты;
— сделать технологически приемлемый и экономичный раскрой листа 1,5 м на 6,0 м для изготовления бака диаметром 1,954 м и высотой 0,977 м достаточно сложно (практически невозможно раскроить без отходов).
Попробуем изменить ограничения: зададим высоту бака z=1.5 м – по ширине исходного листа, а диаметр y=L/(3.14+1) – из условия, что в длину исходного листа должны вложиться донышко и стенка. Площадь поверхности A1 – положительное число. Раскрой деталей при этом будет предельно прост.
1. Формулу для расчета диаметра бака y в метрах записываем
в ячейку E14: =D5/(ПИ()+1)=1.449
2. Высоту емкости z в метрах заносим
в ячейку F14: 1.500
3. Копируем формулу для расчета площади поверхности A1 в квадратных метрах
в ячейку G14: =ПИ()*E14^2/4+ПИ()*E14*F14=8.475
4. Копируем формулу для расчета объема бака V в кубических метрах
в ячейку H14: =ПИ()*E14^2/4*F14=2.473
5. Анализируем полученные результаты и делаем вывод:
— в сравнении с предыдущим вариантом мы получили высокотехнологичный раскрой, минимум сварных швов, но «расплатились» за это уменьшением объема бака на 15,7%.
Далее выполним анализ формы емкости в виде параллелепипеда:
По сути — это тоже цилиндр с прямоугольным дном в основании.
Работаем с надстройкой «Поиск решения» по расписанному выше алгоритму. Отличие только в том, что переменных теперь не две, а три – ширина x, длина y, высота z. И формулы для расчета площади поверхности и объема для параллелепипеда другие:
A1=x*y+2*x*z+2*y*z
V=x*y*z
Я не буду подробно расписывать все шаги работы с параллелепипедом – они аналогичны расписанной выше по шагам работе с круговым цилиндром! Изучите самостоятельно результаты в таблице по баку-параллелепипеду максимального объема без крышки.
Лучшими решениями поставленной в начале статьи задачи с точки зрения математики без учета технологичности раскроя и трудоемкости изготовления являются варианты №1 и №3.
Лучшими практическими решениями задачи являются, безусловно, варианты №2, №5 и №6!
Какие можно сделать в итоге выводы?
1. При изготовлении цилиндрической емкости без крышки для наиболее эффективного использования материала следует высоту бака делать равной радиусу основания!
2. При изготовлении цилиндрической емкости без крышки с высотой равной радиусу основания для наиболее эффективного использования материала следует в качестве заготовки брать прямоугольный лист с отношением сторон 1/(1+3,14/2)=1 / 2,57!
3. При изготовлении емкости в виде параллелепипеда без крышки для наиболее эффективного использования материала следует основание делать квадратным, а высоту бака делать равной половине размера основания!
4. При изготовлении емкости в виде параллелепипеда без крышки с квадратным основанием и высотой равной половине размера основания для наиболее эффективного использования материала следует в качестве заготовки брать прямоугольный лист с отношением сторон 1/3!
5. Чем больше емкость по размерам, тем меньше нужно квадратных метров листового материала на кубический метр объема!
6. Замечательный инструмент в Excel «Поиск решения» — легко и просто решает задачи с несколькими переменными! Рекомендую!!!
7. Чтобы «хранить» на даче два с половиной кубических метра воды, можно купить тринадцать двухсотлитровых металлических бочек. Места они займут, конечно, много, но обойдутся в три раза дешевле…
Ссылка на скачивание файла: poisk-resheniya-v-excel (xls KB).
Статьи с близкой тематикой
Отзывы
5 комментариев на «Бак для воды? «Поиск решения» в Excel!»
Ваш отзыв
Посетители: 2,1 млн
А если сверху то же крышка будет, то как формулы изменются? Я думаю для цилиндра А1=3,14*y^2+3.14*y*z
и V=3.14*y^2*z или я совсем чушь несу?
Вы не совсем правы, будет где-то так:
A1=2*(3.14*y^2/4)+3.14*y*z=3.14*y^2/2+3.14*y*z
V=3.14*y^2/4*z
Эльдар, объем бака не изменится от того, что мы добавим сверху крышку! А вот площадь увеличится — станет две площади дна + та же боковая поверхность.
При изготовлении цилиндрической емкости с крышкой для наиболее эффективного использования материала следует высоту бака делать равной диаметру основания!
Спасибо. Здорово.
Здравствуйте. В Вашей статье речь идёт только об оптимизации раскроя металла, но существенное значение имеет и толщина стенок бака. При цилиндрической форме она будет меньше, что может послужить решающим фактором при выборе формы.
Согласен, Станислав, но цилиндр, занимая «то же место», имеет меньший объём, чем параллелепипед почти на 22% (при квадратной форме горизонтального сечения).
Безусловно, в каждом конкретном случае нужно принимать наиболее рациональное решение, исходя из целей, которые необходимо достигнуть.