Системы принятия решений, оптимизация в Excel и базы данных Access
Формат: doc
Дата создания: 08.06.2005
Размер: 42.62 KB
Скачать рефератСЕВЕРО-ЗАПАДНЫЙ ГОСУДАРСТВЕННЫЙ ЗАОЧНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ
Кафедра информатики
Курсовая работа по информатике:
Системы принятия решений, оптимизация в Excel и базы данных Access.
Выполнила:
Студентка 1-ого курса
Факультета ТАУМ
Специальность 3302
Макаровская Наталья
Шифр:
Проверила:
Афанасьева
Санкт-Петербург 2005
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 – оценка «отлично».
Решение:
Разработка системы принятия решений включает три этапа:
-
Разработка дерева принятия решений.
-
Разработка базы данных.
-
Компьютерная реализация.
-
Разработка дерева принятия решений.
Это дерево строится на основе правил вывода и представляет собой графическое изображение схемы решений. Дерево имеет вершины и ветви. Вершины обозначаются либо окружностями или эллипсами (проверка условий), либо прямоугольниками (принятие решений). Ветви соединяют вершины и показывают направление решений.
Сумма баллов абитуриента – S.
либо
-
База данных создается на основе базы знаний, в которую добавляется столбец ответов, если ответ положительный весовой фактор атрибута сохраняется, если отрицательный фактор обнуляется.
-
Реализация экспертной системы в электронной таблице.
-
В ячейки A1:D3 введем заголовки и комментарии.
-
В A4:D17 размещается база знаний.
-
В C4:C17 – ответы.
-
В D4:D17 вводим формулы для обработки ответов.
-
в D4 вводится формула =B4*C4
-
копируем ее в ячейки D5:D17
-
В строке 18 вычислим суммарный весовой фактор S.
-
В 20-ой введем формулу для принятия решения.
A | B | C | D | |||
1 | Оценка знаний абитуриента | |||||
2 | Атрибут | Весовой фактор атрибута | Ответ | Весовой фактор ответа | ||
3 | База знаний | База данных | ||||
4 | Умеет создавать документ | 20 | 1 | 20 | ||
5 | Умеет редактировать документ | 30 | 1 | 30 | ||
6 | Умеет форматировать документ | 30 | 1 | 30 | ||
7 | Умеет создавать документ со спискомами | 30 | 1 | 30 | ||
8 | Умеет работать с табуляторами | 30 | 0 | 0 | ||
9 | Умеет работать с колонками | 30 | 0 | 0 | ||
10 | Умеет работать с колонтитулами | 30 | 1 | 30 | ||
11 | Умеет нумеровать страницы | 30 | 1 | 30 | ||
12 | Умеет работать с панелью «рисование»ние» | 30 | 1 | 30 | ||
13 | Умеет вставлять рисунки и схемы | 30 | 1 | 30 | ||
14 | Умеет работать с таблицами | 60 | 1 | 60 | ||
15 | Умеет вырезать и вставлять рисунки с использованием редактора Paint | 60 | 1 | 60 | ||
16 | Умеет создавать макросы | 50 | 1 | 50 | ||
17 | Умеет проводить слияние документов из разных приложений | 70 | 1 | 70 | ||
18 | Суммарный весовой фактор S | 400 | ||||
19 | Принятие решения | |||||
20 | хорошо | |||||
A | B | C | D | |||
1 | Оценка знаний абитуриента | |||||
2 | Атрибут | Весовой фактор атрибута | Ответ | Весовой фактор ответа | ||
3 | База знаний | База данных | ||||
4 | Умеет создавать документ | 20 | 1 | =B4*C4 | ||
5 | Умеет редактировать документ | 30 | 1 | =B5*C5 | ||
6 | Умеет форматировать документ | 30 | 1 | =B6*C6 | ||
7 | Умеет создавать документ со спискомами | 30 | 1 | =B7*C7 | ||
8 | Умеет работать с табуляторами | 30 | 0 | =B8*C8 | ||
9 | Умеет работать с колонками | 30 | 0 | =B9*C9 | ||
10 | Умеет работать с колонтитулами | 30 | 1 | =B10*C10 | ||
11 | Умеет нумеровать страницы | 30 | 1 | =B11*C11 | ||
12 | Умеет работать с панелью «рисование»ние» | 30 | 1 | =B12*C12 | ||
13 | Умеет вставлять рисунки и схемы | 30 | 1 | =B13*C13 | ||
14 | Умеет работать с таблицами | 60 | 1 | =B14*C14 | ||
15 | Умеет вырезать и вставлять рисунки с использованием редактора Paint | 60 | 1 | =B15*C15 | ||
16 | Умеет создавать макросы | 50 | 1 | =B16*C16 | ||
17 | Умеет проводить слияние документов из разных приложений | 70 | 1 | =B17*C17 | ||
18 | Суммарный весовой фактор S | 400 | =СУММ(D4:D16) | |||
19 | Принятие решения | |||||
20 | =ЕСЛИ(D18<330;”неуд”;ЕСЛИ(D18<390;”уд”;ЕСЛИ(D18<440;”хорошо”;”отлично”))) | |||||
-
Оптимизация управленческих и экономических решений.
При решении многих задач в экономике и управлении возникает проблема найти оптимальные решения. Для решения существуют специальные разделы математики (линейное программирование). В Excel для нахождения оптимального решения существует специальный режим «Поиск решения».
Задание 2.
Администрации фирмы требуется определить штат и составить график работы обслуживающего персонала. При этом необходимо обеспечить следующие условия:
-
Каждый сотрудник должен иметь пять рабочих дней в неделю и два выходных подряд;
-
Все сотрудники имеют заработную плату 230 руб. в день;
-
Исходя из специфики работы фирмы, имеются требования к минимальному количеству работающих сотрудников для каждого дня недели.
День недели | понедельник | вторник | среда | четверг | пятница | суббота | воскресенье |
Требуемое число сотрудников | 25 | 30 | 35 | 25 | 25 | 10 | 7 |
На текущий момент в фирме работает 45 человек.
Определить штат сотрудников, обеспечивающий выполнение всех условий при минимальном фонде заработной платы.
Решение включает в себя три этапа:
-
Построение математической модели.
-
Построение начального плана.
-
Оптимизация решения.
1 этап.
1.1. Определим возможные режимы работы и занесем их в 1-ый столбец таблицы №1.
Выходные дни | Число имеющих этот график | ПН | ВТ | СР | ЧТ | ПТ | СБ | ВС |
ПН, ВТ | =7 | 0 | 0 | 1 | 1 | 1 | 1 | 1 |
ВТ, СР | =5 | 1 | 0 | 0 | 1 | 1 | 1 | 1 |
СР, ЧТ | =5 | 1 | 1 | 0 | 0 | 1 | 1 | 1 |
ЧТ, ПТ | =7 | 1 | 1 | 1 | 0 | 0 | 1 | 1 |
ПТ, СБ | =7 | 1 | 1 | 1 | 1 | 0 | 0 | 1 |
СБ, ВС | =7 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
ВС, ПН | =7 | 0 | 1 | 1 | 1 | 1 | 1 | 0 |
Число выходов по графику | 45 | 31 | 33 | 35 | 33 | 31 | 31 | 31 |
Требуется | 45 | 25 | 30 | 35 | 25 | 25 | 10 | 7 |
Зарплата | 230 | |||||||
Число выходов | 225 | |||||||
Целевая | 51750 |
Составим предварительный график работы.
1.2. Обозначим – число сотрудников, имеющих выходные в ПН т ВТ; в ВТ, СР; – СР, ЧТ; – ЧТ, ПТ; – ПТ, СБ; – СБ, ВС; – ВС, ПН.
Согласно условиям задачи большего всего сотрудников должны работать в среду, значит, число отдыхающих в этот день должно быть меньше всего. Например, возьмем = =5. Тогда = = = = = =7
Занесем значения Х во второй столбец таблицы.
1.3. Введем индексы выхода на работу в остальные столбцы, где, если это “1” –рабочий день, a “0” – выходной день.
1.4. Рассчитаем число выходов на работу каждый день. Для расчета числа выходов в ПН нужно перемножить и сложить значения столбца Х столбца ПН. И так далее.
1.5. Рассчитываем целевую функцию задачи – фонд зарплаты за неделю.
Z=B K
Где В – зарплата сотрудника за день, а К – число выходов на работу за неделю.
К=31+33+35+33+31+31+31=225
Z=225 230=51750 рублей
На модель накладываются следующие ограничения:
-
переменные Х не отрицательные.
-
Х – целые
-
Количество выходящих на работу по графику не может быть меньше требуемого числа сотрудников.
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 |
-
Базы данных.
База данных – это поименованная область памяти для хранения структурированных данных.
Система управления БД это комплекс программных и языковых средств для создания и ведения БД.
Структурные элементы баз данных.
Модель данных – это структура хранения информации в БД. Имеется 3 модели данных :
- иерархическая
- сетевая
- реляционная (ввиде двумерных таблиц)
Наиболее распространенная СУБД (FoxPro, Paradox, Access) реляционные. Основными элементами реляционных БД являются : поле, запись, отношение, файл, ключ.
Поле – логическая единица записи информации (один столбец таблицы). При создании БД каждое поле описывают, указывая следующие характеристики:
- имя
- тип информации (текстовой, числовой, дата/время)
- длина (число символов в столбце)
- точность (только для дробных чисел)
- формат (для дата/время)
Запись – это совокупность логически объединенных полей (1 строка таблицы). Отношение – это совокупность экземпляров записей одной структуры.(таблица)
Файл – совокупность объектов БД. Например, в Access основными объектами являются:
- таблицы (для хранения информации)
- формы (для просмотра и ввода данных)
- запросы (для отбора данных)
- отчеты (для подготовки к печати)
Ключ это поле, по которому можно найти любую запись БД.
Основные этапы проектирования баз данных.
Задание №3.
Разработать информационно-логическую модель предметной области «Картины» с атрибутами:
-
Автор
-
Название картины
-
Год создания
-
Страховая стоимость
-
Зал экспозиции
-
Время работы экспозиции
-
Название музея
-
Город, где находится музей
-
Страна, где находиться музей
-
Год реставрации
-
Место нахождения реставрационной мастерской
-
Реставратор
Создать логическую структуры этой базы данных для СУБД Access. Заполнить таблицы базы данных записями. Создать отчет с параметрами АВТОР, НАЗВАНИЕ КАРТИНЫ, НАЗВАНИЕ МУЗЕЯ.
Для создания БД нужно:
-
Создать схему (информационно-логическую модель)
-
СУБД
-
Разработать логическую структуру БД.
-
Заполнить таблицы записями.
-
Создать запросы и отчеты.
-
Разработка информационно-логической модели:
Разобьем все атрибуты на четыре таблицы, связанные между собой ключами:
1. Автор (автор, картина)
2. Картина (картина, год создания, страховая стоимость, музей, год реставрации, автор)
3. Музей (картина; музей; город, где находится музей; страна, где находиться музей; зал экспозиции; время работы экспозиции).
4. Реставрация (год реставрации, место нахождения реставрационной мастерской, реставратор, картина).
Строим информационно-логическую модель. (Схему данных)
Автор
Картина
Год создания
Страховая стоимость
Музей
Год реставрации
Автор
Реставрация
Год реставрации
Место нахождения реставрационной мастерской
Реставратор
Картина
Автор
Картина
Картина
-
СУБД – выбираем Access.
Имя поля | Тип данных | Описание | |
ключ | автор | текстовой | |
картина | текстовой |
Автор | |
Автор | Картина |
Врубель | Парящий демон |
Паулюс Поттер | Цепная собака |
Шишкин | Мишки в сосновом бору |
Имя поля | Тип данных | Описание | |
ключ | картина | текстовой | |
Год создания | Дата/время | краткий | |
Страховая стоимость | Денежный | ||
Музей | текстовый | ||
Год реставрации | Дата/время | ||
Автор | Текстовый |
Картина | |||||
Картина | Год создания | Страховая стоимость | Музей | Год реставрация | Автор |
Мишки в сосновом бору | 1900 | 187 754,00р. | Третьяковская галерея | 1980 | Шишкин |
Парящий демон | 1880 | 70 000,00р. | Русский музей | 1977 | Врубель |
Цепная собака | 1780 | 1 234 567,00р. | Эрмитаж | 1950 | Паулюс Поттер |
Имя поля | Тип данных | Описание | |
картина | текстовой | ||
ключ | музей | текстовой | |
Город, где находиться музей | текстовой | ||
Страна, где находиться музей | текстовый | ||
Зал экспозиции | текстовый | ||
время работы экспозиции | Дата/время | ||
Музей | |||||||||||
Картина | Музей | Город, где находиться музей | Страна, где находиться музей | Зал экспозиции | время работы экспозиции | ||||||
Парящий демон | Русский музей | Санкт-Петербург | Россия | третий | 13-17 | ||||||
Мишки в сосновом бору | Третьяковская галерея | Москва | Россия | первый | 12-18 | ||||||
Цепная собака | Эрмитаж | Санкт-Петербург | Россия | пятый | 14-16 | ||||||
Имя поля | Тип данных | Описание | |||||||||
ключ | Год реставрации | Дата/время | краткий | ||||||||
Место нахождения реставрационной мастерской | Текстовой | ||||||||||
Реставратор | Текстовой | ||||||||||
Картина | текстовый | ||||||||||
реставрация | |||
Год реставрации | Место нахождения реставрации | реставратор | Картина |
1950 | Лондон | Локонс | Цепная собака |
1977 | Москва | Ильин | Парящий демон |
1980 | Санкт-Петербург | Морозов | Мишки в сосновом бору |