Бак для воды? «Поиск решения» в Excel!

Опубликовано 02 Авг 2013
Рубрика: Справочник Excel | 3 комментария



Шар, цилиндр, параллелепипедПопробуем «догнать двух зайцев» — познакомиться с работой очень интересного и мощного инструмента 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

Таблицы "Бак для воды? Поиск решения в Excel!" 

Первой проанализируем цилиндрическую форму емкости с круговым дном:

Баки для воды - цилиндрический и параллелепипед

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: Сервис – Поиск решения…) и заполняем так, как на снимке экрана, представленном ниже.

Всплывающее  рабочее окно надстройки "Поиск решения" MS Excel

Мы хотим получить в целевой ячейке 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

Всплывающее окно "Результаты поиска решения" надстройки "Поиск решения" MS Excel

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

Уважаемые читатели, для получения анонсов статей моего блога прошу оформить подписку в окне «Подпишитесь на новости», расположенном вверху страницы. Введите адрес своей электронной почты и нажмите на кнопку «Получать анонсы статей». Один раз в 7…10 дней к вам на почтовый ящик будет приходить небольшое уведомление о появлении на моем блоге новой статьи, ее название и краткое описание. Если вам что-то не понравится или просто надоест автор или тема, вы прямо в почте всегда можете отказаться от подписки.

Жду ваших комментариев!

Ссылка на скачивание файла: poisk-resheniya-v-excel (xls KB).

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

На главную


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

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

Отзывы

3 комментария на «Бак для воды? «Поиск решения» в Excel!»

  1. Эльдар 30 Сен 2013 02:56

    А если сверху то же крышка будет, то как формулы изменются? Я думаю для цилиндра А1=3,14*y^2+3.14*y*z

    и V=3.14*y^2*z или я совсем чушь несу?

  2. Александр Воробьев 30 Сен 2013 10:43

    Вы не совсем правы, будет где-то так:

    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

    Эльдар, объем бака не изменится от того, что мы добавим сверху крышку! А вот площадь увеличится — станет две площади дна + та же боковая поверхность.

    При изготовлении цилиндрической емкости с крышкой для наиболее эффективного использования материала следует высоту бака делать равной диаметру основания!

  3. Vladimir 17 мая 2016 17:11

    Спасибо. Здорово.

Ваш отзыв







  • Посетители: 663 803

  • Подписчики: 3 451