- •Метод наименьших квадратов
- •Методические рекомендации
- •Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа ms Excel
- •Пример выполнения лабораторной работы
- •Решение
- •Варианты задач для самостоятельного решения Задача №1
- •Задача № 2
- •Задача №3
- •Исходные данные задачи №3
- •Решение оптимизационных задач в Excel с помощью инструмента Принятие решений.
- •Методика выполнения работы Статистический анализ данных, моделирование и прогноз
- •Поиск решения
- •Пример постановки задачи линейного программирования
- •Формирование математической модели
- •Ввод условий задачи:
- •Р ис. 1. Таблица для ввода условий задачи линейного программирования
- •Решение задачи
- •Задание к работе
- •Порядок выполнения работы
- •Проведения регрессионного анализа:
- •Решение задачи линейного программирования (злп):
- •Доложить преподавателю об окончании выполнения работы; Требование к защите работы
- •Содержание отчета
- •Контрольные вопросы
- •Варианты задач линейного программирования
- •Лабораторная работа №5.
- •Лабораторная работа №6
- •Лабораторная работа №7
- •Лабораторная работа №8
- •Вопросы при сдаче лабораторной работы:
- •Лабораторная работа № 9 Элемент управления типа «Флажок»
- •Задание 1.
- •Контрольные вопросы. Литература:
- •Лабораторная работа № 10
- •Вопросы при сдаче лабораторной работы:
- •Лабораторная работа № 11
- •Лабораторная работа № 12
- •Понятие запроса
- •Создание запроса
- •Окно конструктора запроса
- •Включение полей в запрос
- •Установка критериев отбора записей
- •Виды критериев
- •Логическая операция или
- •Оператор Between
- •In (“первый”,”второй”,”третий”)
- •Операторы для даты и времени
- •Сортировка данных в запросе
- •Вычисляемые поля
- •Использование построителя выражений
- •Итоговые запросы
- •Выполнение запроса
- •6.8. Запросы к нескольким таблицам
- •Методические рекомендации
- •Лабораторная работа № 13
- •Параметрический запрос
- •Вопросы при сдаче лабораторной работы:
- •Лабораторная работа №14
- •Вычисления в запросах
- •Лабораторная работа №15
- •Формы для связанных таблиц
- •Лабораторная работа №16
- •Лабораторная работа №17
- •Общие положения
- •Основные сведения об отчетах
- •Выбор мастера по разработке отчетов
- •Разделы отчета
- •Окно отчета и его инструменты
- •Сортировка и группировка
- •Задание диапазонов группировки
- •Использование вычисляемых значений
- •Добавление текущей даты и номера страницы
- •Вычисления в области данных
- •Объединение текстовых значений и скрытие повторяющихся значений
- •Вычисление процентов
- •Вычисление итогов с накоплением
- •Создание и внедрение подчиненных отчетов
- •Настройка отчетов
- •Просмотр готового отчета
- •Методические рекомендации
- •Лабораторная работа №18
Пример постановки задачи линейного программирования
Акционерное общество производит и реализует оптовыми партиями соответственно по цене 45 руб. и 30 руб. книги и настольные календари. Постоянные затраты общества (управленческие расходы, содержание офиса и т.п.) составляют 202200 руб. в квартал, переменные затраты (стоимость бумаги, услуги типографии и т.п.) – соответственно 15.75 руб. и 12.3 руб. в расчете на одну книгу и один календарь. Налог на добавленную стоимость составляет 16.67 % цены изделия.
Опыт реализации изделий в предыдущие плановые периоды показал, что в течение квартала можно реализовать не более 100000 книг и 40000 календарей.
Требуется на планируемый квартал определить объем производства и реализации изделий (структуру производства и реализации), при котором общество получит максимальную прибыль, а суммарные затраты на производство и реализацию изделий не будут превосходить 2000000 руб.
Формирование математической модели
Обозначим через х1(х2) неизвестный объем производства и реализации книг (календарей). Суммарные затраты Z на производство и реализацию изделий можно выразить в виде функции неизвестных объемов реализации изделий:
Z(x1,x2)=202200+15.75x1+12.3x2. (1.1)
Ожидаемая сумма выручки общества V, рассматриваемая как функция неизвестных объемов производства и реализации, составит:
V(x1,x2)=45x1+30x2. (1.2)
Сумма налога на добавленную стоимость N также выражается в виде функции неизвестных объемов реализации:
N(x1,x2)=0.1667*(45x1+30x2)=7.5x1+5x2. (1.3)
Прибыль акционерного общества P можно записать в виде функции неизвестных объемов x1 и x2 следующим образом:
P(x1,x2)=V(x1,x2)-Z(x1,x2)-N(x1,x2) = 45x1+30x2-202200 –157.5x1-123x2-7.5x1-5x2=
=21.75x1+12.7x2-202200. (1.4)
Таким образом, математическая модель ситуации записывается следующим образом:
21.75x1+12.7x2-202200max, (1.5)
202200+15.75x1+12.3x22000000, (1.6)
0 x1 100000, 0 x2 40000. (1.7)
Модель (1.5)–(1.7) можно записать в следующем виде:
21.75x1+12.7x2max, (1.8)
15.75x1+12.3x21797800, (1.9)
x1 100000, (1.10)
x2 40000, (1.11)
x1 0, x2 0. (1.12)
Ввод условий задачи:
Сформировать таблицу в диапазоне ячеек A1:F11, приведенную на рис. 1.
Р ис. 1. Таблица для ввода условий задачи линейного программирования
В ячейку D6 ввести формулу =СУММПРОИЗВ(B$3:C$3;B6:C6).
В ячейку D9 ввести формулу =СУММПРОИЗВ(B$3:C$3;B9:C9) и размножить по столбцу в ячейках D10 и D11.
Решение задачи
В меню выбрать «Сервис - Поиск решения». В поле «Установить целевую ячейку:» набрать $D$6. В поле «Равной:» установить маркер в «Максимальному значению».
В поле «Изменяя ячейки» ввести $B$3:$C$3.
Установить курсор-прямоугольник в поле «Ограничения». Нажать на кнопку «Добавить». В поле «Ссылка на ячейку:» ввести $B$3. Выбрать знак >=. В поле «Ограничение» ввести =$B$4. Нажать кнопку «Добавить».
В поле «Ссылка на ячейку:» ввести $С$3. Выбрать знак >=. В поле «Ограничение» ввести =$С$4. Нажать кнопку «Добавить».
В поле «Ссылка на ячейку:» ввести $D$9. Выбрать знак <=. В поле «Ограничение» ввести =$F$9. Нажать кнопку «Добавить»
В поле «Ссылка на ячейку:» ввести $D$10. Выбрать знак <=. В поле «Ограничение» ввести =$F$10. Нажать кнопку «Добавить»
В поле «Ссылка на ячейку:» ввести $D$11. Выбрать знак <=. В поле «Ограничение» ввести =$F$11. Нажать кнопку OK. В результате диалоговое окно Поиск решения должно быть заполнено также как на рис. 2.
Рис. 2. Диалоговое окно Поиск решения
Нажать кнопку «Параметры». Установить линейную модель. Нажать кнопку OK. Нажать кнопку «Выполнить».
В диалоговом окне «Результаты поиска решения», приведенном на рис. 3, установить маркер на опцию «Сохранить найденное решение» и выбрать в окне «Тип отчета – Результаты». Нажать OK.
Рис. 3. Диалоговое окно Результаты поиска решения
В таблице появятся выходные значения, приведенные на рис. 4.
|
Товары |
|
|
|
|
имя |
Книги(х1) |
Календари(х2) |
|
|
|
значение |
|
|
|
|
|
нижняя граница |
|
|
|
|
|
верхняя граница |
|
|
ЦФ |
напр |
|
коэф. в ЦФ |
21,75 |
12,70 |
|
макс. |
|
|
Ограничения |
|
|
|
|
вид |
|
|
левая часть |
знак |
правая часть |
b1 |
15,75 |
12,30 |
|
<= |
1797800,00 |
b2 |
1,00 |
0,00 |
|
<= |
100000,00 |
b3 |
0,00 |
1,00 |
|
<= |
40000,00 |
Рис. 4. Выходная таблица решения задачи линейного программирования