- •Методические указания и контрольные задания
- •«Автоматизированные информационные технологии в экономике»
- •Екатеринбург 2009
- •Требования к выполнению контрольной работы. При выполнении контрольнойработы необходимо руководствоваться следующими требованиями:
- •Задания к контрольной работе
- •Раздел I. Основные производственные фонды
- •Раздел II. «Оптимизация»
- •Раздел III. «Регрессии»
- •Раздел IV. «Финансовые вычисления»
- •Методические указания по выполнению контрольной работы
- •1 Этап. Составление математической модели
- •2 Этап. Решение задачи средствами табличного процессора Microsoft Excel
- •Пример решения задачи из раздела III «Регрессия»
- •Пример решения задачи из раздела IV «Финансовые вычисления»
- •Бз(норма, число периодов, выплата, начальное значение, тип)
- •Приложение 3
- •Приложение 4 Образец оформления обложки контрольной работы
- •Методические указания и контрольные задания
- •«Автоматизированные информационные технологии в экономике»
1 Этап. Составление математической модели
Пусть x – количество изделий модели A, выпускаемой в течение недели, y – количество изделий модели B. Тогда прибыль от этих изделий равна 2x+4y долл. Эту прибыль нужно максимизировать.
Функция, для которой ищется экстремум (максимум или минимум), носит название целевой функции.
Беспредельному увеличению количества изделий препятствуют ограничения.
Следовательно, 2x+4y – это целевая функция рассматриваемой задачи. Ограничения задачи:
3x+4y1700 (ограничения количества материала для полок);
0,2x+0,5y160 (ограничения на машинное время изготовления полок);
x0, y0 (т.к. количество изделий число неотрицательное).
Таким образом, задача оптимизации для рассматриваемого примера записывается так:
2 Этап. Решение задачи средствами табличного процессора Microsoft Excel
Запустим табличный процессор Microsoft Excel. Для этого выполним последовательность команд Пуск Программы Microsoft Excel.
Введем в ячейки столбца A данные, как указано на рис. 1
рис. 1
В столбец B введем формулы для расчета целевой функции и ограничений как указано на рис. 2.
рис.2
Воспользуемся средством Поиск решения. Для его запуска выполните последовательность команд: Сервис Поиск решения. В диалоговом окне:
В поле Установить целевую ячейку – укажем адрес ячейки, значение которой должно быть изменено (максимизировано, минимизировано или приравнено к какому-либо определенному указанному значению). В нашем случае это адрес ячейки Прибыль (т.е. B6).
В поле Равной: - установим переключатель на максимальное значение (т.к. по условию задачи мы максимизируем прибыль).
В поле Изменяя ячейки – установим ссылку на ячейки, которые будут изменены. В нашем случае это диапазон ячеек $B$2:$B$3.
В поле Ограничения – щелкнем на кнопке Добавить. В появившемся диалоговом окне:
В поле Ссылка на ячейку – укажем адрес ячейки, для которой должно действовать ограничения. В нашем случае это адрес ячейки Материал (т.е. В9).
В поле операторов выберем знак соотношения. В данном случае знак <=.
В поле Ограничение - укажем границу ограничения. В случае материала это число 1700.
Щелкнем OK
Аналогичным образом, добавим ограничения на время изготовления и ограничения на x и y.
Таким образом, диалоговое окно Поиск решения должно быть настроено следующим образом:
Щелкнем по кнопке Параметры и установим флажок Линейная модель. Щелкнем по OK.
Щелкнем по кнопке Выполнить.
Прочтем сообщение в окне Результаты поиска решения. Щелкнем по OK. Результаты решения видны в ячейках таблицы.
Сохраните результат в файле под именем оптимизация.xls
Пример решения задачи из раздела III «Регрессия»
Задача. Ниже приводиться урожайность зерновых (y) в СССP в 1922-1934 гг. (x).
x |
1922 |
1923 |
1924 |
1925 |
1926 |
1927 |
1928 |
1929 |
1930 |
1931 |
1932 |
1933 |
1934 |
y, ц/га |
7,6 |
7,2 |
6,2 |
8,3 |
8,2 |
7,6 |
7,9 |
7,5 |
8,5 |
8,7 |
7,0 |
8,8 |
8,5 |
Требуется найти коэффициенты a и b зависимости y от x вдоль прямой f(x) = ax+b, пользуясь методом наименьших квадратов, для того чтобы значения f наилучшим образом приближали значения y. Построить диаграмму с исходными данными и приближающим их линейным графиком.
Ход решения
| ||
= $B$16*A2+$B$17 Введем формулу методом автозаполнения по столбцу.
= B2-C2 Введем формулу методом автозаполнения по столбцу.
= СУММКВ(D2:D14)
|
рис. 1 | |
Целевая ячейка D16 – стремится к минимуму. Изменяемые ячейки В16 и В17 (коэффициенты а и b). Ограничений нет.
Функция прямой будет иметь вид: f(x)=ax+b=0,1x-185,207
|
рис. 2 |
Построим диаграмму с исходными данными и приближающим их линейным графиком f(x) = ax+b. Для этого выполним нижеуказанную последовательность шагов.
Нажмем кнопку Мастер диаграмм .
Шаг 1. Выбор типа и вида диаграммы.
Во вкладке Стандартные выберем Тип – Точечная, вид – Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров.
Прочтем описание выбранного вида диаграммы.
Нажмем кнопку Далее>.
Шаг 2. Источник данных диаграммы.
Во вкладке Диапазон данных активизируем опцию Ряды в столбцах.
Переключимся на вкладку Ряд.
Щелкнем по кнопке Добавить.
В поле Имя введем текст - Реальные данные.
В поле Значения X нажмем кнопку для возврата в таблицу и выделим там диапазон с годами, т.е. диапазонA2:A14. Нажмем кнопку для возврата в диалоговое окно Мастер диаграмм.
В поле Значения Y нажмем кнопку для возврата в таблицу и выделим там диапазонB2:B14. Нажмем кнопку для возврата в диалоговое окно Мастер диаграмм.
Щелкнем по кнопке Добавить для добавления второго ряда данных.
В поле Имя введем текст - Расчетные данные.
В поле Значения X укажем диапазон A2:A14.
В поле Значения Y диапазон С2:С14.
Нажмем кнопку Далее>.
Шаг 3. Параметры диаграммы.
Шаг 4. Размещение диаграммы.
|
рис. 3 рис. 4 |
рис. 5
10. Сохраните результат в файле под именем регрессия.xls.