Практическая работа 1. Регрессионный анализ. Построение статических однофакторных моделей.
Содержательная постановка задачи. Имеется статистическая информация по центральному федеральному округу, которая представлена в таблице1:
Таблица 1. Число гостиниц и число ночевок в гостиницах
№ п/п | Наименование субъекта Федерации | Число гостиниц и аналогичных средств размещения, ед. | Число ночевок в гостиницах и аналогичных средствах размещения, тыс. ночевок |
Центральный федеральный округ | 914 | 18154,017 | |
1 | 30 | 219,146 | |
2 | 44 | 180,698 | |
3 | 69 | 635,366 | |
4 | 55 | 437,935 | |
5 | 27 | 230,146 | |
6 | 54 | 420,154 | |
7 | 35 | 198,556 | |
8 | 36 | 150,991 | |
9 | 17 | 134,012 | |
10 | 76 | 1594,568 | |
11 | 26 | 232,162 | |
12 | 45 | 259,978 | |
13 | 39 | 255,207 | |
14 | 15 | 83,304 | |
15 | 59 | 379,641 | |
16 | 43 | 267,449 | |
17 | 53 | 513,103 | |
18 | г. Москва | 191 | 11961,601 |
* - на базе данных Федеральной службы государственной статистики.
Пусть ряд наблюдений X - число гостиниц и аналогичных средств размещения, ряд наблюдений Y - число ночевок в гостиницах и аналогичных средствах размещения, тыс.
Часть I.
Требуется:
Пример. В исходной таблице произведем сортировку по столбцу С (число гостиниц и аналогичных средств размещения) по возрастанию числа гостиниц.
Рис.1. Сортировка по возрастанию числа гостиниц
Отсортированная таблица представлена на рисунке 2:
Рис.2. Отсортированная таблица по возрастанию числа гостиниц
По отсортированным данным, используя мастер диаграмм, построим точечную диаграмму (диапазон ячеек С1:D19) (Рис.3).
Рис. 3. Диаграмма по отсортированной таблице
После построения диаграммы, вызовем контекстовое меню, щелкнув правой кнопкой мыши по одной из точек диаграммы, и выберем в нем команду Добавить линию тренда…(Рис. 4):
Рис. 4. Вкладка Параметры линии тренда
Во вкладке Параметры линии тренда выберем Линейная и отметим флаги показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Чем R2 ближе к 1, тем удачнее регрессионная модель. На диаграмме появляется линия тренда (Рис. 5).
Рис.5. Диаграмма с линейной линией тренда
Чаще всего выбор производится среди следующих функций:
у = ах + b - линейная функция;
у = ах2 + bх + с - квадратичная (полиномиальная) функция;
у = аln(х) + b - логарифмическая функция;
у = аеbх - экспоненциальная функция;
у = ахb - степенная функция.
Отобразим на диаграмме все возможные тренды (Рис. 6.).
Рис. 6. Диаграмма с построенными линиями тренда
Часть II.
Требуется: рассчитать основные характеристики случайных величин.
Для расчета основных характеристик случайных величин используются следующие функции: СРЗНАЧ() – возвращает среднее арифметическое своих аргументов, КОРЕНЬ() – возвращает значение квадратного корня, а также ДИСП() и КОРРЕЛ().
ДИСП() - Оценивает дисперсию по выборке (Рис.7).
Синтаксис функции: ДИСП(число1;число2; ...).
Число1, число2,... — от 1 до 255 числовых аргументов, соответствующих выборке из генеральной совокупности.
Рис. 7. Аргументы функции, оценивающей дисперсию по выборке - Дисп()
КОРРЕЛ() – возвращает коэффициент корреляции между интервалами ячеек «массив1» и «массив2». Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера (Рис. 8).
Синтаксис функции: КОРРЕЛ(массив1;массив2).
Массив1 — это интервал ячеек со значениями, Массив2 — второй интервал ячеек со значениями.
Рис.8. Аргументы функции, возвращающей коэффициент корреляции Коррел()
Получим следующие результаты (Рис. 9):
Рис. 9. Результаты расчетов с использованием математических функций
Можно сделать вывод о том, что линейная зависимость между числом гостиниц и аналогичных средств размещения (ряд X) и числом ночевок в гостиницах и аналогичных средствах размещения (ряд Y) существует, т. к. коэффициент корреляции равен 0,93729 и 
.
Коэффициент корреляции значим, т. к. расчетный критерий Стъюдента больше табличного критерия: 10,7566 > 2.1190.
Рассчитаем коэффициент корреляции для исходных данных с помощью функции Корреляция пакета Анализ данных.
Вызвать окно Анализ данных можно с помощью команды Анализ данных меню Данные (Рис. 10).
Рис. 10. Анализ данных
Пакет Корреляция позволяет определить коэффициенты корреляции для n-го количества рядов данных. Выбор команды Корреляция вызывает окно Корреляция (Рис. 11).
Рис. 11. Окно Корреляция
Это окно содержит две панели Входные данные и Параметры вывода. Окно Входной интервал: предназначено для ссылки на диапазон, содержащий анализируемые данные. Эта ссылка должна состоять не менее чем из двух смежных диапазонов данных, расположенных по строкам или столбцам. Флаги Группирование: зависят от расположения данных в диапазоне. Флаг Метки в первой строке (Метки в первом столбце) устанавливается в том случае, если входной интервал включал название диапазонов. Если название диапазонов были включены в интервал, а данный флаг не выставлен, после нажатия кнопки Ок, Excel выдаст сообщение об ошибке «Входной интервал содержит нечисловые данные». Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически.
Если результаты необходимо поместить на имеющемся листе, то нужно установить переключатель рядом с окном Выходной интервал:, а в самом окне следует ввести ссылку на левую верхнюю ячейку выходного диапазона.
Если установить переключатель рядом с окном Новый рабочий лист:, то в книге откроется новый лист и результаты анализа будут вставлены в него, начиная с ячейки A1. При необходимости в окно можно ввести имя нового листа. По умолчанию имя листа будет соответствовать следующему после последнего имеющегося в книге листа.
Если установить переключатель рядом с окном Новая рабочая книга, то откроется новая книга, и результаты анализа будут вставлены в нее, начиная с ячейки A1 на первом листе в этой книге.
Поскольку коэффициент корреляции двух наборов данных не зависит от последовательности их обработки, то выходная область занимает только половину предназначенного для нее места.
Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждая строка или столбец во входном диапазоне полностью коррелирует с самим собой.
Заполняем все необходимые поля окна Корреляция (Рис. 12).
Входной интервал – это данные, по которым необходимо провести корреляционный анализ, в данном случае это исходные данные по числу гостиниц и аналогичных средств размещения и числу ночевок в гостиницах и аналогичных средствах размещения (С2:D19). Строка 1 также указана во входном интервале, но в ней содержатся заголовки столбцов, поэтому ставим флаг Метки в первой строке.
В выходном интервале ставим Новый рабочий лист, в котором будут вынесены результаты расчета.
Рис. 12. Расчет коэффициента корреляции
Полученные данные абсолютно идентичны коэффициентам полученным с помощью функции КОРРЕЛ() (Рис. 13).
Рис. 13. Результаты расчета коэффициента корреляции
Пакет Описательная статистика предназначен для расчета основных статистических показателей. Окно Описательная статистика (Рис. 14) содержит:
Рис.14. Описательная статистика
панель Входные данные, аналогичную панели в окне Корреляция; панель Параметры вывода содержит указание на выходной интервал, аналогичный окну Корреляция; флаг Итоговая статистика обеспечивает вывод в выходной интервал среднего, стандартную ошибку (среднего), медиану, мода, стандартное отклонение, дисперсию выборки, эксцесс, асимметричность, интервал, минимум, максимум, сумму и количество значений; флаг Уровень надежности, установка которого выводит в выходной интервал строку для уровня надежности. Значение, введенное в поле, соответствует требуемому уровню надежности; флаг К-тый наименьший и К-тый наибольший, установка которых выводит в выходной интервал строки для k-го наибольшего и k-го наименьшего значения для каждого диапазона данных. В соответствующем окне необходимо ввести число k. Если k равно 1, эта строка будет содержать минимум или максимум из набора данных.Далее вызовем функцию Описательная статистика из пакета Анализ данных (Рис. 15).
Рис. 15. Описательная статистика пакета Анализ данных
Выставив все необходимые флаги, нажимаем кнопку Ок, и получаем таблицу описательных статистик (Рис. 16).
Рис. 16. Таблица описательных статистик
Полученные данные совпадают с данными, рассчитанными с помощью математических функций (математическое ожидание по x и по y, дисперсия по x и по y, среднее квадратическое отклонение по x и по y).
Варианты заданий к практической работе 1.
х | у |
… | … |
Содержательная постановка задачи. Исходные данные:
Требуется:
построить точечную диаграмму, предварительно отсортировав таблицу; выдвинуть гипотезу о виде функции зависимости; рассчитать параметры модели регрессии, построить тренды; оценить адекватность построенного уравнения по величине достоверности аппроксимации; рассчитать теоретические значения по модели и построить графики фактических и расчетных данных. Создать отчет в Word по всем пунктам задания, используя экранные копии Excel. Написать вывод о виде функции зависимости, наилучшим образом описывающей модель.Вариант 10. Имеется статистическая информация по Южному и Уральскому федеральным округам*, которая представлена в таблице:
№ п/п | Число гостиниц и аналогичных средств размещения | Число ночевок в гостиницах и аналогичных средствах размещения, тыс. | |
Южный и Уральский федеральный округ | |||
1 | Республика Адыгея | 26 | 102,324 |
2 | Республика Калмыкия | 14 | 26,083 |
3 | 1025 | 14724,81 | |
4 | 80 | 116,707 | |
5 | 163 | 691,792 | |
6 | 295 | 955,277 | |
7 | 48 | 121,7 | |
8 | 315 | 2186,8 | |
9 | 335 | 2656,8 | |
10 | 201 | 1101,5 |
* - на базе данных Федеральной службы государственной статистики.
Практическая работа 2. Оптимизация функции одной переменной.
Если команда Поиск решения отсутствует, необходимо загрузить надстройку Поиск решения:
Открыть вкладку Файл и выбрать пункт Параметры, а затем выбрать пункт Надстройки Excel. Нажать кнопку Перейти. В окне Доступные надстройки (Рис. 17) установить флажок рядом с пунктом Поиск решения и нажать кнопку ОК.
Рис. 17. Доступные надстройки
Для подготовки рабочего листа к решению задачи необходимо:
Отвести диапазон ячеек для хранения переменных. До вызова инструмента Поиск решения этот диапазон будет пустым. После решения задачи в диапазон будут вставлены искомые значения; В отдельную ячейку занести целевую функцию, которая будет зависеть от переменных и условий задачи. Следовательно, в формуле целевой функции будут использоваться ссылки на ячейки, в которых хранятся переменные, и на ячейки, в которых хранятся данные, оговоренные условием задачи; В отдельные ячейки внести формулы для задания ограничений. Ограничения вводятся на переменные и оговариваются условиями задачи, таким образом, в формулах ограничений, также как и в целевой функции будут использованы ссылки на ячейки, в которых хранятся переменные, и на ячейки, в которых хранятся данные, оговоренные условием задачи; В отдельные ячейки внести числовые значения ограничений для каждой из формул ограничений.Пример. Оптимизация функции одной переменной.
Дана функция Y = - 2x2 + 3x + 50, интервал (-10,10). Найти оптимальное значение функции Y. Перед решением задачи полезно оценить вид функции, экстремум которой необходимо найти и уточнить интервал (a, b), в котором этот экстремум находится. Для этого необходимо построить график (Рис. 18) и определить диапазон нахождения оптимального значения функции. Диаграмма строится в режиме "Точечная".
Рис. 18. График функции
Вызовем окно Поиск решения (Рис. 19), которое в Microsoft Office Excel 2007 состоит из полей:
- Установить целевую ячейку, в котором ставится ссылка на ячейку с формулой (Y);
- Равной – выбираем максимальному значению;
- Изменяя ячейки, в котором ставится ссылка на ячейку с изменяемым параметром (первая граница а интервала (а, в));
- Ограничения – вводим ограничения по интервалу (-10,10).
Рис. 19. Поиск решения
После нажатия кнопки OK, появляется окно Результаты поиска решения (Рис. 20), сохраняем найденное решение.
Рис. 20. Результаты поиска решения
Таким образом, Ymax = 51,25; Xmax= 0,75. Полученное решение представлено на рисунке (Рис. 21):
Рис. 21. Отчет по результатам
Варианты заданий к практической работе 2.
Построить график и определить диапазон нахождения оптимального значения функции Y. Найти оптимальное значение функции Y (максимальное/минимальное) с помощью надстройки Поиск решения, вывести отчет Результаты. Подготовить отчет в Word, скопировать экранные формы (график функции, окно Поиск решения, Отчет по результатам), написать математическую постановку задачи и найденное оптимальное решение.Найти максимальное/минимальное значение функции:
Y=ax2+bx+c, коэффициенты а, b, c, интервал [x1,x2].
№ варианта | A | b | c | Интервал [x1,x2] | Значение функции |
10 | 5 | 8 | 10 | -10 ; 10 | минимальное |
Вопросы к практической работе 2.
Оптимизация функции одной переменной.
Как подключить надстройку Поиск решения в Excel. Постановка задачи оптимизации функции одной переменной. Метод дихотомии, метод золотого сечения, метод множителей Лагранжа. Какая задача решается: задача условной оптимизации или безусловной оптимизации. Укажите целевую функцию и ее вид (линейная или нелинейная). Укажите параметр управления (изменяемые ячейки). Укажите ограничения. Укажите найденное оптимальное решение.Практическая работа 3. Решение задачи линейного программирования (ЗЛП) графическим методом.
Часть I.
Несмотря на то, что графический метод решения задач линейного программирования применяется только для задач с двумя искомыми переменными (или в случае трехмерного пространства с тремя), этот метод позволяет понять основную суть линейного программирования.
Задача 1.
Рассмотрим систему неравенств
(1)
и линейную форму
(2)
Найти минимум и максимум линейной формы (2) из области решений системы (1).
Решение.
Построим выпуклый многоугольник, заданный системой неравенств (1). Для этого построим прямоугольную систему координат х1ох2. Если в этой системе координат построить прямую ах1+bх2=с, то эта прямая разбивает плоскость х1ох2 на две полуплоскости, каждая из которых лежит по одну сторону от прямой. Сама прямая в этом случае называется граничной и принадлежит обеим полуплоскостям. Координаты точек, лежащих в одной полуплоскости удовлетворяют неравенству ах1+вх2?с, а координаты точек, лежащих в другой полуплоскости, удовлетворяют неравенству ах1+вх2?с. Построим в плоскости х1ох2 граничные прямые:
1) 
4) 
2) 
5) 
3) 
В результате получим пятиугольник АВСDЕ (Рис. 22)
Значения х1 и х2 , удовлетворяющие системе неравенств (1), являются координатами точек, лежащих внутри или на границе найденного пятиугольника. Теперь задача сводится к тому, чтобы найти те значения х1 и х2 при которых линейная форма L (2) имеет минимум, и те значения х1 и х2 при которых линейная форма L достигает максимума. Из рис. 22 видно, что координаты всех точек, лежащих внутри или на границе пятиугольника, не являются отрицательными, т. е. все значения х1 и х2 больше или равны нулю.
Рис. 22. Решение задачи 1 графическим методом.
Для каждой точки плоскости х1ох2 линейная форма L принимает фиксированное значение. Множество точек, при которых линейная форма L принимает фиксированное значение L1 , есть прямая 
, которая перпендикулярна вектору 
. Если прямую 
передвигать параллельно самой себе в положительном направлении вектора 
, то линейная форма L будет возрастать, а в противоположном направлении – убывать. Построим прямую 
для того случая, когда L = 0, т. е. построим прямую 
. Как видно из рис. 22, при передвижении прямой 
в положительном направлении вектора 
она впервые встречается с вершиной А(0;2) построенного пятиугольника АВСDЕ. В этой вершине линейная форма L имеет минимум. Следовательно,
.
При дальнейшем передвижении прямой 
параллельно самой себе в положительном направлении вектора 
значение линейной формы будет возрастать, и оно достигает максимального значения в точке С (8;6). Таким образом,
.
Варианты к практической работе 3. Часть I.
В задачах построить выпуклый многоугольник, заданный системой неравенств и, пользуясь графическим методом, найти минимум и максимум линейной формы 
.
10. 
Часть II
Задача. Туристской фирме требуется не более 10 автобусов грузоподъёмностью 3 тонны и не более 8 автобусов грузоподъёмностью 5 тонн. Цена автобуса первой марки 20000 у. е., цена автобуса второй марки 40000 у. е. Туристская фирма может выделить для приобретения автобусов не более 400000 у. е. Сколько следует приобрести автобусов каждой марки в отдельности, чтобы их общая (суммарная) грузоподъёмность была максимальной.
Решение.
Пусть приобретено х1 трёхтонных, х2 пятитонных автобусов, тогда заданные условия задачи можно записать так:
или 
(3)
Линейная форма L (часто её называют целевой функцией) применительно к условиям нашей задачи имеет вид:
(4)
Требуется найти те значения х1и х2, при которых Lдостигает максимального значения. По условию задачи 
. Решим задачу графическим методом. Построим многоугольник АВСDЕ (Рис. 23), все точки которого удовлетворяют системе неравенств.
(5)
Рис. 23. Решение задачи линейного программирования (ЗЛП) графическим методом.
Затем построим вектор 
и прямую 
. Перемещая прямую 
параллельно самой себе в положительном направлении вектора 
, установим, что L достигает максимального значения в точке С, для которой х1 = 10 и х2= 5. Экономическая интерпретация результатов: Следовательно, туристской фирме следует приобрести 10 трёхтонных и 5 пятитонных автобусов. В этом случае общая грузоподъёмность составит 55 тонн. (
).
Варианты к практической работе 3. Часть II.
Туристской фирме требуется не более а трехтонных автобусов и не более в пятитонных автобусов. Отпускная цена автобусов первой марки 20000 у. е., второй марки 40000 у. е. Туристская фирма может выделить для приобретения автобусов не более с у. е. Сколько следует приобрести автобусов каждой марки в отдельности, чтобы их общая (суммарная) грузоподъёмность была максимальной. Решить задачу графическим методом.
Варианты
10. а = 20 в = 18 с = 1000000
Вопросы к практической работе 3.
В каких случаях используется графический метод решения ЗЛП?
Алгоритм (схема) решения ЗЛП графическим методом. Как построить область допустимых решений? Как построить линию уровня? Как определить точку максимума? Как определить точку минимума? Укажите полученное решение: X1опт, X2опт, Lопт. Дайте экономическую интерпретацию результатов.Практическая работа 4. Решение задачи линейного программирования симплекс-методом с помощью инструмента Поиск Решения в Excel.
Туристской фирме требуется не более 10 автобусов грузоподъёмностью 3 тонны и не более 8 автобусов грузоподъёмностью 5 тонн. Цена автобуса первой марки 20000 у. е., цена автобуса второй марки 40000 у. е. Туристская фирма может выделить для приобретения автобусов не более 400000 у. е. Сколько следует приобрести автобусов каждой марки в отдельности, чтобы их общая (суммарная) грузоподъёмность была максимальной.
Используя задание к практической работе № 3 нужно:
Сформулировать математическую постановку задачи. Решить ЗЛП симплекс-методом, используя инструмент Поиск решения в Excel. Дать экономическую интерпретацию результатов. Написать отчет в Word, содержащий копию экранов с исходными данными, окно Поиск решение, Отчет по результатам и вывод.Решение.
Математическая постановка задачи дана в условии.Используем решение к практической работе № 3
Пусть приобретено х1 трёхтонных, х2 пятитонных автобусов, тогда заданные условия задачи можно записать так:
или 
(1)
Линейная форма 
> min (2)
Требуется найти те значения х1 и х2, при которых L достигает максимального значения. По условию задачи 
. Решим задачу симплекс-методом, все точки которого удовлетворяют системе неравенств.
(3)
Заполним таблицу данных на листе 1, причём сначала придадим нашим переменным нулевые значения (рис 24):
Рис. 24. Решение задачи об автобусах.
Применим функцию Excel СУММПРОИЗВ, которая перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.
Синтаксис:
СУММПРОИЗВ(массив1, [массив2], [массив3],...)
Аргументы функции СУММПРОИЗВ описаны ниже.
- Массив1 Обязательный. Первый массив, компоненты которого нужно перемножить, а затем сложить результаты. Массив2, массив3... Необязательный. От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.
Итак, применим формулу СУММПРОИЗВ для вычислений левой части ограничений и целевой функции (Рис. 25):
D5 = СУММПРОИЗВ(B5:C5;B$4:C$4)
D7 = СУММПРОИЗВ(B7:C7;B$4:C$4
D8 = СУММПРОИЗВ(B8:C8;B$4:C$4)
D9 = СУММПРОИЗВ(B9:C9;B4:C4)
Рис. 25. Применение функции СУММПРОИЗВ
Далее установим курсор в ячейку D5 и вызовем инструмент Поиск решения, для этого дадим команды: Данные – Поиск решения. Зададим параметры, используя постановку задачи (Рис. 26):
(4) 
Рис. 26. Параметры поиска решения.
Нажимаем кнопку Найти решение.
Появляется окно Результаты поиска решения. Чтобы сохранить отчёт о решении, нажмём во вкладке Отчёты на Результаты, далее ОК (Рис. 27):
Рис. 27. Результаты поиска решения.
Получим:
Рис. 28. Результаты решения задачи.
Появится следующий лист:
Рис. 29. Отчёт по результатам
Итак, экономическая интерпретация результатов: туристской фирме следует приобрести 10 трёхтонных и 5 пятитонных автобусов. В этом случае общая грузоподъёмность составит 55 тонн. 
