Системы принятия решения и оптимизации в Excel, конструирование баз данных в Access

 

    Разработанная система принятия решения содержит много избыточной для пользователя информации. Для исключения лишней информации воспользуемся командой «Скрыть строку». Скрываем строки с 4 по 8, затем с 16 по 21.

    В  режиме пользователя таблица  имеет вид

 
 
 
 

    Вид  таблицы в режиме показа формул

 

Задание 2. 

    Задачи оптимизации очень часто встречаются в управленческой, финансовой и научной деятельности. Они позволяют отыскать наилучшее (оптимальное) решение. Для решения подобных задач используются, как правило, методы математического программирования. На компьютере подобные задачи можно решить, используя имеющийся в табличном процессоре Excel режим Поиск решения.

    Режим  Поиск решения позволяет:

• использовать одновременно до 200 изменяемых параметров;
• задавать ограничения для этих параметров;
• используя метод последовательных приближений отыскивать оптимальное решение;

    Задачи, для решения которых используется  режим Поиск решения, должны  обладать рядом свойств:

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

    2.1. Построение математической модели.

    Обозначим  - количество продукции, отправляемой со склада i в магазин j; - стоимость перевозки единицы продукции со склада i в магазин j.

    Математическая  модель будет состоять из ряда  ограничений:

а) исходя из физического смысла задачи ; ;

б) ограничения  по предложению (со склада нельзя вывезти  продукции больше, чем там имеется)

в) ограничение  по спросу

    Общая  стоимость перевозок равна:

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

    Необходимым  и достаточным условием решимости  транспортной задачи является  условие баланса:

    , где левая часть уравнения – суммарное количество продукции на складах, а правая – суммарное количество продукции, требуемое в магазинах.

    В  нашем случае  .

    2.2. Разработка ЭТ с начальным  планом решения.

    В  ячейках B4:B7 помещаем сведения о наличии продукции на складах. В ячейках C9:G9 – сведения о потребностях магазинов. В ячейках C5:G7 – данные о стоимости перевозок единицы продукции со складов в магазин.

    Считаем,  что с каждого склада в каждый  магазин везут одну деталь (ячейки  C11:G13 заполняем единицами).

    В ячейку B11 введем формулу для вычисления количества продукции, вывозимой с первого склада: =СУММ(C11:G11). В ячейки B12, B13 введем формулы: =СУММ(C12:G12) и =СУММ(C13:D13). Для начального плана перевозок все суммы равны 5.

    В  ячейку C15 введем формулу для вычисления количества продукции, которую везем в первый магазин: =СУММ(C11:G13), в ячейки D15:G15 введем формулы для вычисления количества продукции, которую везем во 2-ой, 3-ий, 4-ый, 5-ый магазины:

    В  D15 =СУММ(D11:D13)

    В E15 =СУММ(E11:E13)

    В F15 =СУММ(F11:F13)

    В G15 =СУММ(G11:G13)

    Для определения стоимости перевозок в 1-ый магазин введем в ячейку C16 формулу: =СУММПРОИЗВ(C5:C7;C11:C13). Аналогично в ячейки D16:G16 введем формулы для вычисления стоимости перевозок в остальные магазины.

    Определим  общую стоимость перевозок. Введем в ячейку B17 формулу =СУММ(C16:G16). Для начального плана получилось 44. 

 

    2.3. Для оптимизации плана перевозок  используем режим Поиск решения  Excel. Введем данные:

    Целевая  ячейка B17

    Изменяя ячейки C11:G13

    Ограничения: C11:G13>=0

                            C11:G13=целые

                            C11:G13<=B5:B7

                            C15:G15>=C9:G9 
 
 
 
 
 

 

Задание 3 

    База  данных – это поименованная  совокупность структурированных сведений об объектах какой либо предметной области.

    Структурирование  – введение соглашений о способах  представления данных.

    Система  управления базами данных (СУБД) – это комплекс программ и  языковых средств, необходимых  для создания баз данных и для работы с ними.

    Структурные  элементы базы данных:

    поле  – элементарная единица логической  организации данных (1 столбец);

    запись  – совокупность логически связанных  полей (1 строка);

    отношение  – совокупность экземпляров записей  одной структуры (1 таблица);

    файл  – совокупность объектов БД (таблиц, форм, отчетов, запросов);

    ключ  – такое поле, по которому можно  однозначно найти любую запись  в БД. Если записи можно определить  только значением нескольких  полей, ключ называют составным.

    Чтобы  связать 2 таблицы БД нужно,  чтобы они имели одинаковый  ключ.

    Общая  логическая структура БД называется  моделью данных.

    Существует  несколько типов (моделей) организации  БД:

    Иерархический  – каждый узел описывает какой-то объект. К каждой записи есть только один путь от верхней (корневой) записи.

    Сетевой  – возможна связь каждого узла  с другим.

    Реляционный  – используется табличное представление  данных.

    Каждая реляционная таблица представляет собой двумерный массив и обладает следующими свойствами:

    - каждый элемент таблицы – один  элемент данных;

    - все столбцы в таблице однородные, т.е. имеют одинаковые тип и  длину;

    - каждый столбец имеет уникальное имя;

    - одинаковые строки в таблице  отсутствуют;

    - порядок следования строк и столбцов может быть произвольным.

    Все информационные объекты предметной области связаны между собой. Различаются связи нескольких типов, для которых введены следующие обозначения: