Вариант 9

1. Решено перестроить здание отеля с целью создания системы конференц-залов для проведения семинаров и конференций. Определено, что отель должен иметь не менее 5 малых и 15 больших конференц-залов. Ожидаемые затраты на строительство: 1 малого зала – 180000 р., 1 большого зала – 451500 р. Предполагаемая сумма расходов – 7000000 р. Найти решение, наиболее близко отвечающее данным условиям, если нежелательными отклонениями являются: уменьшение количества малых и больших залов, перерасход средств.

2. Решить задачу 1 при дополнительном целевом ограничении: общая площадь конференц-залов должна составить по плану 1760 кв.м.; при этом площадь малого зала – 40 кв.м., большого зала – 120 кв.м; нежелательными отклонениями являются как уменьшение, так и превышение общей плановой площади залов.

3. Решено перестроить здание отеля с целью создания системы конференц-залов для проведения семинаров и конференций. Определено, что отель должен иметь не менее 5 малых (40 кв.м), 10 средних (75 кв.м) и 15 больших конференц-залов (120 кв.м). Общая площадь залов должна составить 2430 кв.м. Ожидаемые затраты на строительство: 1 малого зала – 180000 р., 1 среднего зала – 330000 р., 1 большого зала – 451500 р. Предполагаемая сумма расходов – 10000000 р.

Найти решение, наиболее близко отвечающее данным условиям, если нежелательными отклонениями являются: уменьшение количества малых, средних и больших залов, перерасход средств, уменьшение и превышение общей плановой площади залов.

Лабораторная работа № 11. Корреляционный и регрессионный анализ

Цель работы: изучить основы корреляционного и регрессионного анализа и научиться их выполнять с помощью табличного процессора.

Напомним, что корреляционным анализом называется раздел математической статистики, посвященный изучению взаимосвязей между случайными величинами.

Основная задача корреляционного анализа – установление характера и тесноты связи между результативными и факторными показателями в изучаемом явлении или процессе.

Теснота связи определяется с помощью коэффициента корреляции R, который лежит в интервале от -1 до +1. Если 0,9<│R│≤1, то отмечается сильная корреляционная связь. Если 0,6<│R│≤0,9, то связь – хорошая. Если │R│≤0,6, то связь – слабая.

Регрессионный анализ имеет целью вывод уравнения регрессии, включая статистическую оценку его параметров. Уравнение регрессии позволяет найти значение результативной (зависимой) переменной, если величины факторных (независимых) переменных известны. Уравнение регрессии еще называют трендом (тенденцией).

По числу факторов различают одно-, двух- и многофакторные уравнения регрессии.

Определение параметров линейного однофакторного уравнения регрессии.

Пусть x₁, x₂, … x_n – совокупность значений независимого признака;

y₁, y₂, … y_n – совокупность соответствующих значений зависимого признака; n – количество наблюдений.

Предположим, что между x и y существует линейная взаимосвязь, то есть

,

где a и b – параметры.

Для нахождения уравнения регрессии вычисляются следующие величины:

1. Средние значения ; .

В Excel для их расчета используется статистическая функция СРЗНАЧ(число1;число2;…).

2. Отклонения от средних величин ; .

3. Дисперсии D и среднеквадратичные отклонения σ

; ; ; .

В Excel для их расчета используются статистические функции ДИСП(число1;число2;…) и СТАНДОТКЛОН(число1;число2;…).

Дисперсия и среднеквадратичное отклонение характеризуют разброс наблюдаемых значений вокруг среднего значения. Чем больше дисперсия, тем больше разброс.

4. Корреляционный момент (коэффициент ковариации)

.

В Excel для его расчета можно использовать формулу:

= СУММПРОИЗВ(массив Δx;массив Δy)/(СЧЁТ(массив x)-1),

где СУММПРОИЗВ(массив1;массив2;…) – математическая функция, вычисляющую сумму произведений соответствующих элементов массивов; СЧЁТ(число1;число2;…) – статистическая функция, подсчитывающая количество чисел в списке аргументов.

Корреляционный момент отражает характер взаимосвязи между x и y. Если K_xy>0, то взаимосвязь прямая. Если K_xy<0, то взаимосвязь обратная.

5. Коэффициент корреляции вычисляется по формуле:

.

В Excel для его расчета можно использовать функцию КОРРЕЛ(массив x;массив y).

Коэффициент корреляции находится в интервале от -1 до +1 (). Величина называется коэффициентом детерминации.

Если , то вычисления продолжаются.

6. Вычисления параметров регрессионного уравнения:

; .

В Excel параметры линейного однофакторного уравнения регрессии могут быть определены при построении диаграммы путем выполнения следующих действий:

1. Запустить Мастер диаграмм, выбрав в меню Вставка-Диаграмма;

2. На 1-м шаге Мастера диаграмм выбрать тип диаграммы Точечная;

3. На 2-м шаге Мастера диаграмм выбрать диапазон с массивами x и y;

4. Выполнить остальные шаги и завершить работу Мастера диаграмм кнопкой Готово.

5. Добавить линию тренда (Диаграмма-Добавить линию тренда), выбрав тип Линейная и во вкладке Параметры пометив галочками пункты Показывать уравнение на диаграмме (для вывода на диаграмме уравнения линии тренда) и «поместить на диаграмму величину достоверности аппроксимации R^2» (для отображения на диаграмме значения коэффициента детерминации).

Определение параметров линейного двухфакторного

уравнения регрессии

Линейное двухфакторное уравнение регрессии имеет вид

;

где a, b₁, b₂ – параметры.

Для нахождения параметров этого уравнения можно использовать следующую статистическую функцию Excel

ЛИНЕЙН(массив y;массив x; константа; статистика),

где массив y – диапазон ячеек со значениями переменной y;

массив x – диапазон ячеек со значениями переменных x₁ и x₂;

константа – логическое значение, принимающее значение ИСТИНА, если параметр a нужно вычислять обычным образом, и ЛОЖЬ – если a должен быть равен 0;

статистика – логическое значение, принимающее значение ИСТИНА, если нужно вывести дополнительную регрессионную статистику, и ЛОЖЬ – если нужно вывести только параметры a, b₁, b₂.

Правильное использование функции ЛИНЕЙН для двухфакторного уравнения требует выполнения следующих действий:

1. Выделить для вывода результатов пустой диапазон ячеек шириной в 3 ячейки и высотой в 5 ячеек, если нужно выводить статистику, иначе – высотой в 1 ячейку;

2. Вставить статистическую функцию ЛИНЕЙН, в окне аргументов которой ввести: в 1-м поле диапазон ячеек значений y; во 2-м поле - диапазон ячеек значений x₁ и x₂; в 3-м поле – ИСТИНА, если параметр a не должен быть равен 0, иначе – ЛОЖЬ; в 4-м поле – ИСТИНА, если нужно выводить статистику, иначе – ЛОЖЬ;

3. Нажать F2, а затем Ctrl+Shift+Enter.

В ответ должна выйти таблица значений, в 1-й строке которой выводятся значения параметров b₂, b₁ и a (именно в таком порядке!).