САДИ
Кафедра ЭУН
Компьютерное моделирование в MS Excel
Методические указания и задания для студентов строительных специальностей, изучающих дисциплины
«Информатика», «Информационные технологии в строительстве», «Строительная информатика»
Оглавление
Введение. 2
ЗАДАЧА 1. МОДЕЛИРОВАНИЕ РАЗМЕЩЕНИЯ СТРОЯЩЕГОСЯ ОБЪЕКТА.. 2
Компьютерная модель: МЕСТОПОЛОЖЕНИЕ СТРОЯЩЕГОСЯ ОБЪЕКТА.. 3
ВАРИАНТЫЗАДАНИЙ.. 8
МЕСТОПОЛОЖЕНИЕ СТРОЯЩЕГОСЯ ОБЪЕКТА.. 8
ЗАДАЧА 3. МОДЕЛИРОВАНИЕ ОПТИМИЗАЦИИ ПЕРЕВОЗОК.. 10
Постановка задачи.. 10
Варианты заданий.. 10
Рекомендации по компьютерной модели и решению... 12
Введение
Рассматриваются задачи компьютерного моделирования для инженерных задач и решение их в среде MS Excel для строительных специальностей.
MS Excel имеет встроенный аппарат численного анализа данных, позволяющий решать сложные задачи линейного и нелинейного программирования со многими неизвестными и ограничениями. Для этих целей есть Поиск решения, который можно подключить через НАДСТРОЙКИ.
Методические указания содержат примеры решения задач оптимизации в среде MS Excel, а также задания для студентов строительных специальностей.
Задачи поиска оптимального решения на сегодняшний день разнообразны по своему характеру. Эти задачи могут быть реализованы с помощью методов математического моделирования. Затем математическая модель реализуется в виде алгоритма и программы.
Здесь предлагается другой подход – компьютерное моделирование. С помощью надстроек универсальных программных комплексов выстроить компьютерную модель, формально не привлекая знания в области математического моделирования.
В качестве универсального программного комплекса предлагается табличный процессор MS Excel. На листе процессора выстраивается компьютерная модель в виде формул и ссылок в ячейках листов книги MS Excel.
В результате решается инженерная задача с возможностью вариантного экспериментирования.
ЗАДАЧА 1. МОДЕЛИРОВАНИЕ РАЗМЕЩЕНИЯ СТРОЯЩЕГОСЯ ОБЪЕКТА
Задача может иметь несколько возможных вариантов постановки, отличающихся друг от друга количеством объектов и их расположением на координатной плоскости.
Рассмотрим конкретно один из вариантов этой задачи.
Постановка задачи: имеются четыре жилых дома. Определить местоположение объекта для нового строительства - школы. Требуется построить школу в удобном для всех жителей микрорайона месте, предполагая, что сумма расстояний от построенного объекта до всех жилых домов будет минимальным значением (рис. 1). Это расстояние и является целевой функцией моделирования.
Рис. 1
Другие варианты задачи о строительстве объектов могут быть сформулированы как для различных значений количества домов, местоположения этих домов, так и для различных видов целевой функции.
Компьютерная модель: МЕСТОПОЛОЖЕНИЕ СТРОЯЩЕГОСЯ ОБЪЕКТА
Координаты исходных домов будут отображены в ячейках на листе книги MS Excel и иметь адреса на листе B2:B5 для координаты x и D2:D5 для координаты y.
Координаты (х, у) для школы, которую предполагается построить, можно расположить, например, в ячейках B12 и С12 на листе. Они служат переменными, каждая из них может принимать действительные значения в результате решения. Через эти адреса будет формулироваться компьютерная модель, эти ячейки являются изменяемыми.
Целью в данной задаче будем рассматривать сумму расстояний от этой точки (х, у) до каждой из точек (хi,уi), где i =1,2,3,4, являющихся координатами уже построенных домов
x1 x2 x x3 x4
Рис. 2
Расстояние от i – го дома до школы определяется по формуле:
.
Общее расстояние от всех четырех домов до школы будет определяться выражением:
Нужно определить - при каких значениях (х, у) расстояние будет наименьшим.
Создадим компьютерную модель на листе MS Excel. Для этого расположим формулы для вычисления расстояний 
в ячейках B7:B10, используя адреса B2:B5 и D2:D5 для построенных домов и B12 и D12 для нового объекта. Для того, чтобы при копировании с переадресацией ссылки в формулах формировались правильно, B12 и D12 надо сделать абсолютными: $B$12 и $D$12. В ячейке G12 будет помещено значение целевой функции (расстояние). Формула для ее вычисления: =СУММ(B7:B10).
В ячейках B7 будет введена формула:
=КОРЕНЬ(($B$12-B2)^2+($D$12-D2)^2)
В ячейках B8: B10 эта формула копируется с переадресацией (протяжка).
Рис. 3
Далее следует обратиться к надстройке «поиск решения», для чего необходимо выполнить операцию главного меню Сервис | Поиск решения (рис 4). Или в следующих версиях MS Excel эта надстройка видна в меню Данные.
Рис. 4
В поле с именем Установить целевую ячейку ввести абсолютный адрес ячейки $G$12- значение целевой функции, а в поле с именем Изменяя ячейки ввести абсолютный адрес ячеек $B$12:$D$12 (рис. 4 ). Поля с ограничениями можно оставить пустыми (рис. 5).
Параметры поиска решения задаются в каждом случае отдельно.
Результат выполнения задачи о строительстве школы вместе с графическим представлением показан ниже (рис. 6).
Рис. 5
Рис.6
Графическое представление выполнено с применением мастера диаграмм, используя (рис. 7) Стандартные –Точечная, не забыв при этом указать, т.е. выделить данные для графика.
Рис. 7
На листе разместить постановку задачи и описать полученный результат.
ВАРИАНТЫЗАДАНИЙ