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

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

СЕВЕРО-ЗАПАДНЫЙ ГОСУДАРСТВЕННЫЙ ЗАОЧНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

Кафедра информатики

Курсовая работа по информатике:

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

Выполнила:

Студентка 1-ого курса

Факультета ТАУМ

Специальность 3302

Макаровская Наталья

Шифр:

Проверила:

Афанасьева

Санкт-Петербург 2005

1. Системы принятия решения.

1.1.Основные понятия.

 Системы искусственного интеллекта – это программная имитация человеческого мышления.

 Системы искусственного интеллекта, которые разработаны для конкретной предметной области, называются экспертной системой или системой принятия решений.

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

   В разработке систем принимают участие:

1) эксперты в данной области, они разрабатывают наиболее важные характеристики предметной области. Они называют факты или атрибуты, а  так же вырабатывают правила принятия решения.

2) Специалисты инженерии знаний. Они разрабатывают реализацию экспертной системы на компьютере. Факты и правила принятия решений хранятся в специальной организованной области памяти, которая называется базой знаний.

Информация о состоянии предметной области в данный момент, которая представляется экспертной системе для анализа, называется базой данных.

 Задание 1

Разработать систему принятия решения для аттестации знаний студентов по разделу «Текстовой процессор Word». База знаний имеет вид:

№ п/п

Атрибут

Весовой фактор атрибута

1

Умеет создавать документ

20

2

Умеет редактировать документ

30

3

Умеет форматировать документ

30

4

Умеет создавать документ со списками

30

5

Умеет работать с табуляторами

30

6

Умеет работать с колонками

30

7

Умеет работать с колонтитулами

30

8

Умеет нумеровать страницы

30

9

Умеет работать с панелью «рисование»

30

10

Умеет вставлять рисунки и схемы

30

11

Умеет работать с таблицами

60

12

Умеет вырезать и вставлять рисунки с использованием редактора Paint

60

13

Умеет создавать макросы

50

14

Умеет проводить слияние документов из разных приложений

70

Правила вывода:

- Если студент набрал меньше 330 баллов, оценка «неудовлетворительно»

- Если сумма баллов 330-390 – оценка «удовлетворительно».

- При сумме баллов 390-440 – оценка «хорошо».

- Если сумма баллов больше 440 – оценка «отлично».

Решение:

Разработка системы принятия решений включает три этапа:

1. Разработка дерева принятия решений.

2. Разработка базы данных.

3. Компьютерная реализация.

1. Разработка дерева принятия решений.

Это дерево строится на основе правил вывода и представляет собой графическое изображение схемы решений. Дерево имеет вершины и ветви. Вершины обозначаются либо окружностями или эллипсами (проверка условий), либо прямоугольниками (принятие решений). Ветви соединяют вершины и показывают направление решений.

Сумма баллов абитуриента – S.

либо

2. База данных создается на основе базы знаний,  в которую добавляется столбец ответов, если ответ положительный весовой фактор атрибута сохраняется, если отрицательный фактор обнуляется.

3. Реализация экспертной системы в электронной таблице.

3.1.В ячейки A1:D3 введем заголовки и комментарии.

3.2.В A4:D17 размещается база знаний.

3.3. В C4:C17 – ответы.

3.4.В D4:D17 вводим формулы для обработки ответов.

3.1.1. в D4 вводится формула =B4*C4

3.1.2. копируем ее в ячейки D5:D17

3.5.В строке 18 вычислим суммарный весовой фактор S.

3.6.В 20-ой введем формулу для принятия решения.

2. Оптимизация управленческих и экономических решений.

   При решении многих задач в экономике и управлении возникает проблема найти оптимальные решения. Для решения существуют специальные разделы математики (линейное программирование). В Excel для нахождения оптимального решения существует специальный режим «Поиск решения».

Задание 2.

Администрации фирмы требуется определить штат и составить график работы обслуживающего персонала. При этом необходимо обеспечить следующие условия:

- Каждый сотрудник должен иметь пять рабочих дней в неделю и два выходных подряд;

- Все сотрудники имеют заработную плату 230 руб. в день;

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

День недели

понедельник

вторник

среда

пятница

суббота

воскресенье

Требуемое число сотрудников

25

30

35

25

25

10

7

На текущий момент в фирме работает 45 человек.

Определить штат сотрудников, обеспечивающий выполнение всех условий при минимальном фонде заработной платы.

Решение включает в себя три этапа:

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

2. Построение начального плана.

3. Оптимизация решения.

      1 этап.

1.1. Определим возможные режимы работы и занесем их в 1-ый столбец таблицы №1.

Составим предварительный график работы.

1.2. Обозначим   – число сотрудников, имеющих выходные в ПН т ВТ;  в ВТ, СР;  – СР, ЧТ;  – ЧТ, ПТ;  – ПТ, СБ; – СБ, ВС;  – ВС, ПН.

Согласно условиям задачи большего всего сотрудников должны работать в среду, значит, число отдыхающих  в этот день должно быть меньше всего. Например, возьмем ==5. Тогда ======7

Занесем значения Х во второй столбец таблицы.

1.3. Введем индексы выхода на работу в остальные столбцы, где, если это “1” –рабочий день, a “0” – выходной день.

1.4. Рассчитаем число выходов на работу каждый день. Для расчета числа выходов в ПН нужно перемножить и сложить значения столбца Х столбца ПН. И так далее.

1.5. Рассчитываем целевую функцию задачи – фонд зарплаты за неделю.

Z=BK

Где В – зарплата сотрудника за день, а К – число выходов на работу за неделю.

К=31+33+35+33+31+31+31=225

Z=225230=51750 рублей

На модель накладываются следующие ограничения:

1) переменные Х не отрицательные.

2) Х – целые

3) Количество выходящих на работу по графику не может быть меньше требуемого числа сотрудников.

A

B

C

D

E

F

G

H

I

1

График работы

2

Выходные

число имеющих этот график

ПН

ВТ

СР

ЧТ

ПТ

СБ

ВС

3

ПН, ВТ

7

0

0

1

1

1

1

1

4

ВТ, СР

5

1

0

0

1

1

1

1

5

СР, ЧТ

5

1

1

0

0

1

1

1

6

ЧТ, ПТ

7

1

1

1

0

0

1

1

7

ПТ, СБ

7

1

1

1

1

0

0

1

8

СБ, ВС

7

1

1

1

1

1

0

0

9

ВС, ПН

7

0

1

1

1

1

1

0

10

Число выходов по графику

=СУММ(B3:B9)

=СУММПРОИЗВ($B3:$B9;C3:C9)

=СУММПРОИЗВ($B3:$B9;D3:D9)

=СУММПРОИЗВ($B3:$B9;E3:E9)

=СУММПРОИЗВ($B3:$B9;F3:F9)

=СУММПРОИЗВ($B3:$B9;G3:G9)

=СУММПРОИЗВ($B3:$B9;H3:H9)

=СУММПРОИЗВ($B3:$B9;I3:I9)

11

Требуется

45

25

30

35

25

25

10

7

12

Зарплата за день

230

13

Число выходов за неделю

=СУММ(C10:I10)

14

Целевая функция

=B12*C13

15

2 этап. Построение начального плана.

2.1. В ячейках А1:I9 разместим начальный график работы согласно таблице №1.

2.2. Расчет числа выхода по графику:

а) в ячейке B10 вычислим общее число сотрудников фирмы необходимое для данного графика.

б) в С10 введем формулу для вычисления количества сотрудников, работающих  в понедельник.

в) копируем формулу из ячейки C10 в ячейки D10:I10.

2.3. В строку 11 заносим требования к графику работы согласно условиям задачи.

3 этап. Оптимизация решения.

3.1. Запускаем режим «Поиск решения» (сервис-поиск решения)

3.2. В окно поиска решения вводим:

- в целевую ячейку $C$14

- установить минимальные значения

- изменяя ячейки В3:В9

3.3. Добавить ограничения:

- В3:В9 >=0

- B3:B9 – целые

- C10:C10>=C11:I11

После проделанных действий щелкаем на «Выполнить»!

Вид таблицы и результаты:

A

B

C

D

E

F

G

H

I

1

График работы

2

число имеющих этот график

ПН

ВТ

СР

ЧТ

ПТ

СБ

ВС

3

ПН, ВТ

5

0

0

1

1

1

1

1

4

ВТ, СР

0

1

0

0

1

1

1

1

5

СР, ЧТ

0

1

1

0

0

1

1

1

6

ЧТ, ПТ

3

1

1

1

0

0

1

1

7

ПТ, СБ

7

1

1

1

1

0

0

1

8

СБ, ВС

15

1

1

1

1

1

0

0

9

ВС, ПН

5

0

1

1

1

1

1

0

10

Число выходов по графику

35

25

30

35

32

25

13

15

11

Требуется

45

25

30

35

25

25

10

7

12

Зарплата за день

230

13

Число выходов за неделю

175

14

Целевая функция

40250

4. Базы данных.

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

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

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

Модель данных – это структура хранения информации в БД. Имеется 3 модели данных :

- иерархическая

- сетевая

- реляционная (ввиде двумерных таблиц)

Наиболее распространенная СУБД (FoxPro, Paradox, Access) реляционные. Основными элементами реляционных БД являются : поле, запись, отношение, файл, ключ.

Поле – логическая единица записи информации (один столбец таблицы). При создании БД каждое поле описывают, указывая следующие характеристики:

- имя

- тип информации (текстовой, числовой, дата/время)

- длина (число символов  в столбце)

- точность (только для дробных чисел)

- формат (для дата/время)

Запись – это совокупность логически объединенных полей (1 строка таблицы). Отношение – это совокупность экземпляров записей одной структуры.(таблица)

Файл – совокупность объектов БД. Например, в Access основными объектами являются:

- таблицы (для хранения информации)

- формы (для просмотра и ввода данных)

- запросы (для отбора данных)

- отчеты (для подготовки  к печати)

Ключ это поле, по которому можно найти любую запись БД.

Основные этапы проектирования баз данных.

Задание №3.

Разработать информационно-логическую модель предметной области «Картины» с атрибутами:

- Автор

- Название картины

- Год создания

- Страховая стоимость

- Зал экспозиции

- Время работы экспозиции

- Название музея

- Город, где находится музей

- Страна, где находиться музей

- Год реставрации

- Место нахождения реставрационной мастерской

- Реставратор

Создать логическую структуры этой базы данных для СУБД Access. Заполнить таблицы базы данных записями. Создать отчет с параметрами АВТОР, НАЗВАНИЕ КАРТИНЫ, НАЗВАНИЕ МУЗЕЯ.

Для создания БД нужно:

1. Создать схему (информационно-логическую модель)

2. СУБД

3. Разработать логическую структуру БД.

4. Заполнить таблицы записями.

5. Создать запросы и отчеты.

1. Разработка информационно-логической модели:

Разобьем все атрибуты на четыре таблицы, связанные между собой ключами:

1. Автор (автор, картина)

2. Картина (картина, год создания, страховая стоимость, музей, год реставрации,  автор)

3. Музей (картина; музей; город, где находится музей; страна, где находиться музей; зал экспозиции; время работы экспозиции).

4. Реставрация (год реставрации, место нахождения реставрационной мастерской, реставратор, картина).

Строим информационно-логическую модель. (Схему данных)

2.   СУБД – выбираем Access.