ПРАКТИЧЕСКАЯ РАБОТА
(с использованием табличного процессора MS Exel)
Тема: Построение простой компьютерной модели
физического, биологического или другого процесса. Проведение исследования на
основе построенной компьютерной модели.
Цель: закрепить алгоритм моделирования на примере
построения компьютерной модели, научиться анализировать результаты
моделирования, научиться использовать шаблон готовой компьютерной модели для
решения типовых задач.
Теоретические сведения к практической работе
Компьютерная модель или численная модель - компьютерная программа,
реализующая представление объекта, системы или понятия в форме, отличной от
реальной, но приближенной к алгоритмическому описанию, включающей и набор
данных, характеризующих свойства системы и динамику их изменения со временем.
Построение компьютерной модели базируется на
абстрагировании от конкретной природы явлений или изучаемого объекта-оригинала
и состоит из двух этапов - сначала создание качественной, а затем и
количественной модели. Чем больше значимых свойств будет выявлено и перенесено
на компьютерную модель - тем более приближенной она окажется к реальной модели,
тем большими возможностями сможет обладать система, использующая данную модель.
Компьютерное моделирование заключается в проведении серии вычислительных
экспериментов на компьютере, целью которых является анализ, интерпретация и
сопоставление результатов моделирования с реальным поведением изучаемого
объекта и, при необходимости, последующее уточнение модели и т. д.
Различают аналитическое и имитационное
моделирование. При аналитическом моделировании изучаются математические
(абстрактные) модели реального объекта в виде алгебраических, дифференциальных
и других уравнений, а также предусматривающих осуществление однозначной
вычислительной процедуры, приводящей к их точному решению. При имитационном
моделировании исследуются математические модели в виде алгоритма(ов),
воспроизводящего функционирование исследуемой системы путем последовательного
выполнения большого количества элементарных операций.
Основные этапы компьютерного моделирования:
1.
Постановка задачи и
её анализ:
1.1. Определить цель создаваемой модели.
1.2. Уточнить исходные результаты и в каком виде
следует их получить.
1.3.
Определить исходные данные для создания модели.
2.
Построение информационной
модели:
2.1. Определить параметры модели и выявить взаимосвязь
между ними.
2.2. Оценить, какие из параметров влиятельные для
данной задачи, а какими можно пренебрегать.
2.3.
Математически описать зависимость между параметрами модели.
3.
Разработка метода и
алгоритма реализации компьютерной модели:
3.1. Выбрать или разработать метод получения исходных
результатов.
3.2. Составить алгоритм получения результатов по
избранным методом.
3.3.
Проверить правильность алгоритма.
4.
Разработка
компьютерной модели:
4.1. Выбрать средства программной реализации алгоритма
на компьютере.
4.2. Разработать компьютерную модель.
4.3.
Проверить правильность созданной компьютерной модели.
5.
Проведение
эксперимента:
5.1. Разработать план исследования.
5.2. Провести эксперимент на базе созданной
компьютерной модели.
5.3. Проанализировать полученные результаты.
5.4.
Сделать выводы насчет свойств прототипа модели.
В процессы проведения эксперимента может выясниться,
что нужно:
· скорректировать план исследования;
· выбрать другой метод решения задачи;
· усовершенствовать алгоритм получения
результатов;
· уточнить информационную модель;
· внести изменения в постановку задачи.
В таком случае происходит возвращение к
соответствующему этапу и процесс начинается снова.
Универсальность моделей - это свойство, позволяющее применять модели
при описании или функционировании однотипных групп объектов.
Содержание работы:
Задание № 1.
Построить простую компьютерную модель экономической
задачи (с применением MS Excel) на примере составления штатного расписания автотранспортного
предприятия.
Основные этапы компьютерного моделирования:
1. Постановка задачи:
Генеральный директор АТП должен составить
штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и
с каким окладом он должен принять на работу.
2. Построение информационной модели:
Исходными данными являются:
Общий месячный фонд зарплаты, который составляет 10 000$.
Для нормальной работы АТП нужно:
·
5 - 7 диспетчеров;
·
8 - 10 ремонтных рабочих;
·
10 - 12 водителей;
·
1 заведующий гаражом;
·
3 механика;
·
1 главный инженер;
·
1 зам. директора по
эксплуатации;
·
1 генеральный директор.
На некоторых должностях число людей может
меняться. Например, руководитель может принять решение сократить число диспетчеров,
чтобы увеличить оклад каждому из них.
3. Разработка метода и алгоритма реализации
компьютерной модели:
Допустим, что:
Ремонтный рабочий должен получать в 1,5 раза больше диспетчера, т.е. A=1,5
B=0;
водитель в 3 раза больше ремонтного рабочего, т.е. А=3
B=0;
механик на 30$ больше, чем водитель, т.е. A=3
B=30;
зав. гаражом в 2 раза больше диспетчера, т.е. A=2
B=0;
зам. директора на 40$ больше ремонтного рабочего, т.е. A=1,5
B=40;
главный инженер в 4 раза больше диспетчера, т.е. A=4
B=0;
Ген. директор на 20$ больше главного инженера, т.е. A=4
B=20
4. Разработка модели:
Генеральный директор принимает для себя
следующую модель задачи. За основу берется оклад диспетчера, а все остальные
вычисляются через него.
Математическая модель
Каждый оклад является линейной функцией от
оклада диспетчера и рассчитывается по формуле: А С+В
где C - оклад диспетчера;
A - коэффициент, который определяет во сколько раз оклад какой-либо
должности больше оклада диспетчера;
B - коэффициент, который определяет, на сколько оклад какой-либо
должности больше оклада диспетчера.
Задав количество человек на каждой должности,
можно составить уравнение:
N1 (A1 C+B1)
+N2 (A2 C+B2) +...+N8 (A8 C+B8)=10000
где N1 - количество диспетчеров, N2
- количество ремонтных рабочих, и т.д.;
A1...A8 и B1...B8 - коэффициенты для каждой должности.
В этом уравнении нам известны A1...A8 и
B1...B8, но не известны C и N1...N8.
Решить такое уравнение можно путем подбора.
Взяв первоначально какие-либо приемлемые
значения неизвестных, подсчитаем сумму. Если фонд заработной платы превышен, то
можно снизить оклад диспетчера, либо отказаться от услуг какого-либо работника,
и т.д., пока эта сумма не будет равна установленному фонду оплаты труда. Проделать
такую работу вручную трудно. Для создания данной модели используется MS Excel
2010 (2013).
Компьютерная модель
1.
Создайте таблицу и
сохраните её в свою папку под именем Штатное расписание:
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
коэф.
А
|
коэф.
В
|
Должность
|
Зарплата
сотрудника
|
Кол-во
сотрудников
|
Суммарная
зарплата
|
|
Зарплата
диспетчера
|
1
|
0
|
Диспетчер
|
|
7
|
|
|
150,00
|
1,5
|
0
|
Ремонтный рабочий
|
|
9
|
|
|
|
3
|
0
|
Водитель
|
|
10
|
|
|
|
3
|
30
|
Механик
|
|
3
|
|
|
|
2
|
0
|
Зав. гаражом
|
|
1
|
|
|
|
1,5
|
40
|
Зам. директора
|
|
1
|
|
|
|
4
|
0
|
Гл. инженер
|
|
1
|
|
|
|
4
|
20
|
Ген. директор
|
|
1
|
|
|
|
|
|
Суммарный месячный фонд зарплаты:
|
|
|
|
2.
В столбце D следует
вычислить заработную плату для каждой должности.
В постановке задачи было объяснено, что
заработная плата вычисляется по формуле А С+В.
В нашей таблице коэффициенты А и В находятся в столбцах А и В, а С - зарплата диспетчера
указана в ячейке Н2. Обратите внимание, что формулы вычисления зарплаты
сотрудников должны содержать абсолютный адрес ячейки Н2.
·
В ячейку D2 введите
формулу =A2*$H$2+B2
·
Скопируйте формулу из
ячейки D2 в ячейки D3:D9.
·
При копировании адрес
ячейки с зарплатой диспетчера остался постоянным (абсолютным), а адреса A2 и B2
перенастраиваются (они относительные).
3.
В столбце F следует
вычислить заработную плату всех сотрудников каждой должности.
В столбце Е указано количество сотрудников
каждой должности. Данные в ячейках E2:E4 могут изменяться в пределах
штатного расписания, а количество сотрудников на других должностях неизменно
(см. постановку задачи).
·
В ячейку F2 введите
формулу =D2*E2 (т.е. "зарплата" * "количество
сотрудников").
·
Скопируйте формулу из
ячейки F2 в F3:F9.
4.
В ячейке F10 найдите
суммарный месячный фонд заработной платы всех сотрудников, т.е. сумму
значений ячеек F2:F9.
5.
Оформите таблицу:
6.
Составьте штатное
расписание: вносите изменения в зарплату диспетчера в ячейке H2 или меняйте количество сотрудников в ячейках E2:E4
(см. постановку задачи) до тех пор, пока полученный суммарный месячный фонд
заработной платы не будет равен заданному (т.е. в ячейке F10 необходимо
получить значение приблизительно равное 10000).
7.
Сохраните таблицу и
предъявите преподавателю файл работы Штатное расписание с 1 листом: Модель
Задание № 2.
4. Компьютерный эксперимент:
1.
Составьте штатное
расписание с использованием функции автоматизации расчетов – Подбор параметра.
Функция Подбор параметра удобное средство
Excel для анализа “Что - если”. При этом подбирается такое значения для
ячейки с изменяемым параметром, чтобы число в целевой ячейке стало равно
заданному.
·
Выберите команду меню Данные,
Анализ ”Что - если”, Подбор параметра и:
·
укажите в окне. Установить
в ячейке адрес целевой ячейки F10 (Фонд заработной платы);
·
введите в окно Значение
- 10000;
·
укажите в окне. Изменяя
значение ячейки адрес ячейки H2 (зарплата диспетчера), т.е. адрес именно
той ячейки, от которой зависит расчет всей таблицы;
·
нажмите OK.
·
Начнется процесс подбора параметра. На рисунке
показан результат подбора параметра.
·
Если нажать на кнопку OK,
значения ячеек в таблице будут изменены в соответствии с найденным решением.
2.
Создайте лист и
переименуйте его в Варианты.
3.
Составьте 4 варианта
штатного расписания и оформите их в виде таблицы: Варианты штатного
расписания
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
12
|
Варианты штатного расписания
|
13
|
|
Диспетчер
|
Рем. рабочий
|
Водитель
|
Зарплата
диспетчера
|
14
|
Вариант 1 (минимальное
количество сотрудников)
|
5
|
8
|
10
|
|
15
|
Вариант 2 (максимальное
количество сотрудников)
|
7
|
10
|
12
|
|
16
|
Вариант 3
(среднее количество сотрудников)
|
6
|
9
|
11
|
|
17
|
Вариант 4
(среднее количество сотрудников)
|
6
|
10
|
10
|
|
Для каждого из 4-х вариантов:
·
в основной таблице с
компьютерной моделью измените, количество сотрудников на должностях диспетчера,
ремонтного рабочего и водителя (фонд заработной платы в ячейке F10 сразу изменится);
·
подберите зарплату диспетчера
в новых условиях с использованием функции Подбор параметра;
·
скопируйте найденное
решение в таблицу Вариантов штатного расписания.
4.
Сохраните таблицу.
Анализ результатов моделирования
1.
Создайте копию листа Модель
и переименуйте его в Отчет.
2.
Выберите один из 4-х
(оптимальный с Вашей точки зрения) вариант штатного расписания. Выбор
обоснуйте.
3.
На листе Отчет
оформите таблицу, например, как на рисунке.
·
Вставьте перед таблицей
две строки (1-ю и 2-ю).
·
Оставьте столбцы C,
D, E и F, а столбцы. А, В, G,
Н скройте, выполнив команду
Скрыть контекстно-зависимого меню.
Внимание! Удалять столбцы A , В и H нельзя,
так как в таблице на них есть ссылки, но их можно скрыть (при этом ширина
столбцов становиться равной нулю).
|
|
Примечание: чтобы отобразить скрытый столбец (например, столбец
Н) следует выделить соседние с ним столбцы (столбцы G и I) и выполнить команду
меню Формат, Столбец, Отобразить или пункт Показать
контекстно-зависимого меню.
·
Введите заголовок ”Штатное
расписание АТП” и подзаголовок “Генеральный директор и Ваши Ф.И.О.”
·
Оформите заголовок и
подзаголовок.
·
Добавьте своё оформление.
4. Сохраните файл.
5.
Подготовьте листы Варианты
и Отчет к печати:
-
выберите альбомную
ориентацию страницы;
-
укажите в верхнем
колонтитуле фамилию, а в нижнем - дату и время.
6.
Сохраните файл.
7.
Предъявите преподавателю
файл АТП с 3 листами: Модель, Варианты, Отчет
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.