Регрессионные математические модели строятся в тех случаях, когда известно, что зависимость между двумя факторами существует и требуется получить ее математическое описание. А сейчас мы рассмотрим задачи другого рода. Пусть важной характеристикой некоторой сложной системы является фактор А. На него могут оказывать влияние одновременно многие другие факторы: В, С, D и так далее. Мы рассмотрим два типа задач:
1. требуется определить, оказывает ли фактор В какое-либо заметное регулярное влияние на фактор А?
2. какие из факторов В, С, D и так далее оказывают наибольшее влияние на фактор А?
В качестве примера сложной системы будем рассматривать школу. Пусть для первого типа задач фактором А является средняя успеваемость учащихся школы, фактором В — финансовые расходы школы на хозяйственные нужды: ремонт здания, обновление мебели, эстетическое оформление помещения и т. п. Здесь влияние фактора В на фактор А не очевидно. Наверное, гораздо сильнее на успеваемость влияют другие причины: уровень квалификации учителей, контингент учащихся, уровень технических средств обучения и другие.
Специалисты по статистике знают, что для того, чтобы выявить зависимость от какого-то определенного фактора, нужно максимально исключить влияние других факторов. Проще говоря, собирая информацию из разных школ, нужно выбирать такие школы, в которых приблизительно одинаковый контингент учеников, квалификация учителей и пр., но хозяйственные расходы школ разные (у одних школ могут быть богатые спонсоры, у других — нет).
Итак, пусть хозяйственные расходы школы выражаются количеством рублей, отнесенных к числу учеников в школе (руб/чел.), потраченных за определенный период времени (например, за последние 5 лет). Успеваемость же пусть оценивается средним баллом учеников школы по результатам окончания последнего учебного года. Еще раз обращаем ваше внимание на то, что в статистических расчетах обычно используются относительные и усредненные величины.
Итоги сбора данных по 20 школам, введенные в электронную таблицу, представлены на рис. 2.16.
Рис. 2.16. Статистические данные
| А | В | С | |
| № п/п | Затраты (руб/чел) | Успеваемость (ср.балл) | |
| 3,81 | |||
| 4,13 | |||
| 4,30 | |||
| 3,96 | |||
| 3,87 | |||
| 4,33 | |||
| 4,21 | |||
| 4,4 | |||
| 3,99 | |||
| 3,9 | |||
| 4,07 | |||
| 4,15 | |||
| 4,1 | |||
| 3,76 | |||
| 4,25 | |||
| 3,88 | |||
| 4,50 | |||
| 4,12 | |||
| 4.32 |
Значения обеих величин: финансовых затрат и успеваемости учеников имеют значительный разброс и, на первый взгляд, взаимосвязи между ними не видно. Однако она вполне может существовать.
Зависимости между величинами, каждая из которых подвергается не контролируемому полностью разбросу, называются корреляционными зависимостями. Раздел математической статистики, который исследует такие зависимости, называется корреляционным анализом. Корреляционный анализ изучает усредненный закон поведения каждой из величин в зависимости от значений другой величины, а также меру такой зависимости.
Оценку корреляции величин начинают с высказывания гипотезы о возможном характере зависимости между их значениями. Чаще всего допускают наличие линейной зависимости. В таком случае мерой корреляционной зависимости является величина, которая называется коэффициентом корреляции. Коэффициент корреляции (обычно обозначаемый греческой буквой р) есть число, заключенное в диапазоне от -1 до +1;
‒ если это число по модулю близко к 1, то имеет место сильная корреляция, если к 0, то слабая;
‒ близость р к +1 означает, что возрастанию одного набора значений соответствует возрастание другого набора, близость к -1 означает обратное;
Значение р легко найти с помощью Excel (формулы расчета коэффициента корреляции встроены в Excel).
В Excel функция вычисления коэффициента корреляции называется КОРРЕЛ и входит в группу статистических функций. Покажем, как ей воспользоваться. На том же листе Excel, где находится таблица, представленная на рис. 2.16, надо установить курсор на любую свободную ячейку и запустить функцию КОРРЕЛ. Она запросит два диапазона значений. Укажем В2:В21 и С2:С21. После их ввода выведется ответ: р = 0,500273843. Эта величина говорит о среднем уровне корреляции.
Наличие зависимости между хозяйственными затратами школы и успеваемостью нетрудно понять. Ученики с удовольствием ходят в чистую, красивую, уютную школу, чувствуют там себя, как дома, и поэтому лучше учатся.
В следующем примере проводится исследование по определению зависимости успеваемости учащихся старших классов от двух факторов: обеспеченности школьной библиотеки учебниками и обеспеченности школы компьютерами. И та и другая характеристика количественно выражаются в процентах от нормы. Нормой обеспеченности учебниками является их полный комплект, то есть такое количество, когда каждому ученику выдаются из библиотеки все нужные ему для учебы книги. Нормой обеспеченности компьютерами будем считать такое их количество, при котором на каждые четыре старшеклассника в школе приходится один компьютер. Предполагается, что компьютерами ученики пользуются не только на информатике, но и на других уроках, а также во внеурочное время.
В таблице, изображенной на рис. 2.18, приведены результаты измерения обоих факторов в 11 разных школах. Напомним, что влияние каждого фактора исследуется независимо от других (то есть влияние других существенных факторов должно быть приблизительно одинаковым).
| Обеспечение учебного процесса | ||||
| Обеспеченность | Успеваемость | Обеспеченность | Успеваемость | |
| № | учебниками (%) | (ср.балл) | компьютерами (%) | (ср.балл) |
| 3,81 | 3,98 | |||
| 4,15 | 4,01 | |||
| 4,69 | 4,34 | |||
| 4,37 | 4,41 | |||
| 4,53 | 3,94 | |||
| 4,23 | 3,62 | |||
| 4,73 | 4,6 | |||
| 3,69 | 4,24 | |||
| 4,08 | 4,36 | |||
| 4,2 | 3,99 | |||
| 4,32 | 4,5 | |||
| Р= 0,780931 | р = 0,572465 |
Рис. 2.18. Сравнение двух корреляционных зависимостей
Для обеих зависимостей получены коэффициенты линейной корреляций. Как видно из таблицы, корреляция между обеспеченностью учебниками и успеваемостью сильнее, чем корреляция между компьютерным обеспечением и успеваемостью (хотя и тот и другой коэффициенты корреляции не очень большие). Отсюда можно сделать вывод, что пока еще книга остается более значительным источником знаний, чем компьютер.
Вопросы и задания
1. Что такое корреляционная зависимость?
2. Что такое корреляционный анализ?
3. Какие типы задач можно решать с помощью корреляционного анализа?
4. Какая величина является количественной мерой корреляции? Какие значения она может принимать?
5. С помощью какого средства табличного процессора можно вычислить коэффициент корреляции?
Задание для самостоятельного выполнения
В приведенной ниже таблице содержатся данные о парных измерениях двух величин, произведенных в некоторой школе: температуры воздуха в классе х и доли простуженных учащихся у:
| Данные измерений | |||||||||
| X | У | X | У | X | У | X | У | X | У |
Зависимость носит статистический характер, поскольку нельзя достоверно сказать, например, что при температуре 15°С в школе болеет 5% учащихся, а при температуре 20°С — 2%. Кроме температуры, есть и другие факторы, влияющие на простудные заболевания, различные для разных школ, и все их проконтролировать невозможно.
Последовательно выполнить следующее:
=> ввести данные в Excel;
=> построить с помощью Мастера диаграмм точечную диаграмму, визуально отображающую табличную зависимость;
=> ответить на вопрос, можно ли на основании этой точечной диаграммы выдвинуть гипотезу о наличии линейной корреляции между величинами;
=> если ответ очевидно отрицательный, то исправить таблицу так, чтобы гипотеза о наличии линейной корреляции стала более правдоподобна;
=> используя функцию КОРРЕЛ, найти коэффициент корреляции и подтвердить или опровергнуть указанную гипотезу.
Оптимальное планирование
Объектами планирования могут быть самые разные системы: деятельность отдельного предприятия, отрасли промышленности или сельского хозяйства, региона, наконец, государства. Постановка обобщенной задачи планирования выглядит следующим образом:
‒ имеются некоторые плановые показатели: х, у и другие;
‒ имеются некоторые ресурсы: Rl, R2 и другие, за счет которых эти плановые показатели могут быть достигнуты. Эти ресурсы практически всегда ограничены;
‒ имеется определенная стратегическая цель, зависящая от значений х, у и других плановых показателей, на которую следует ориентировать планирование.
Нужно определить значение плановых показателей с учетом ограниченности ресурсов при условии достижения стратегической цели. Это и будет оптимальным планом.
Рассмотрим очень простой пример. Школьный кондитерский цех готовит пирожки и пирожные. В силу ограниченности емкости склада за день можно приготовить в совокупности не более 700 изделий. Рабочий день в кондитерском цехе длится 8 часов. Если выпускать только пирожные, за день можно произвести не более 250 штук, пирожков же можно произвести 1000, если при этом не выпускать пирожных. Стоимость пирожного вдвое выше, чем пирожка. Требуется составить дневной план производства, обеспечивающий кондитерскому цеху наибольшую выручку.
Выработаем математическую модель задачи.
Плановыми показателями являются:
1) х - дневной план выпуска пирожков;
2) у - дневной план выпуска пирожных.
Ресурсами производства являются:
1) длительность рабочего дня — 8 часов;
2) вместимость складского помещения — 700 мест.
Предполагается для простоты, что другие ресурсы (сырье, электроэнергия и пр.) не ограничены.
Получим соотношения, следующие из условий ограниченности времени работы цеха и вместимости склада, то есть суммарного числа изделий.
Из условия задачи следует, что на изготовление одного пирожного затрачивается в 4 раза больше времени, чем на изготовление одного пирожка. Если обозначить время изготовления пирожка — t мин, то время изготовления пирожного будет равно 4t мин. Значит, суммарное время на изготовление х пирожков и у пирожных равно
tx + 4ty = (x + 4y)t.
Но это время не может быть больше длительности рабочего дня. Отсюда следует неравенство:
(х + 4y)t < 8 • 60,
или
(х + 4y)t < 480.
Легко вычислить t — время изготовления одного пирожка. Поскольку за рабочий день их может быть изготовлено 1000 штук, то на один пирожок затрачивается 480/1000 = 0,48 мин. Подставляя это значение в неравенство, получим:
(х + 4у) • 0,48 < 480. Отсюда:
х + 4у < 1000.
Ограничение на общее число изделий дает совершенно очевидное неравенство:
х + у < 700.
К двум полученным неравенствам следует добавить условия положительности значений величин х и у (не может быть отрицательного числа пирожков и пирожных). В итоге мы получаем систему неравенств:
х+4у <1000;
х + у < 700; (а)
х > 0; y >0.
Формализуем стратегическую цель: получение максимальной выручки. Выручка - это стоимость всей проданной продукции. Пусть цена одного пирожка — r рублей. По условию задачи, цена пирожного в два раза больше, то есть 2r рублей. Отсюда стоимость всей произведенной за день продукции равна
rх + 2rу = r(х + 2у).
Будем рассматривать записанное выражение как функцию от х, у:
f(x,y) = r(х + 2у).
Она называется целевой функцией.
Поскольку значение r - константа, то максимальное значение f(x,y) будет достигнуто при максимальной величине выражения (х + 2у). Поэтому, в качестве целевой функции можно принять
f(x, y) =x+2y (р)
Следовательно, получение оптимального плана свелось к следующей математической задаче: найти значения плановых показателей х и у, удовлетворяющих системе неравенств (а), при которых целевая функция (p) принимает максимальное значение.
Итак, математическая модель задачи оптимального планирования для школьного кондитерского цеха построена.
Математическая дисциплина, которая посвящена решению таких задач, называется математическим программированием. А поскольку в целевую функцию f(x,y) величины х и у входят линейно (то есть в первой степени), то наша задача относится к разделу этой науки, который называется линейным программированием.
Система написанных выше неравенств представляется на координатной плоскости четырехугольником, ограниченным четырьмя прямыми, соответствующими линейным уравнениям
х + 4y = 1000;
х + у = 700;
х = 0 (ось OY);
у = 0 (ось ОХ).
На рис. 2.19 эта область представляет собой четырехугольник ABCD и выделена заливкой. Любая точка четырехугольника является решением системы неравенств (а). Например, такой точкой является точка с координатами х = 200, у = 100. Ей соответствует значение целевой функции /(200,100) = 400. А точке х = 600, у = 50 соответствует
 |
Рис. 2.19. Область поиска оптимального плана
f(600,50) = 700. Но, очевидно, искомым решением является та точка области ABCD, в которой целевая функция максимальна. Нахождение этой точки производится с помощью методов линейного программирования.
Использование MS Excel для решения задачи оптимального планирования
В программу Excel встроены возможности решения задач математического программирования.
Средство «Поиск решения» реализовано в форме надстройки. Соответствующая команда находится в меню Сервис (MS Excel 97-2003) или в группе Анализ вкладки Данные.
Замечание. Средство поиска решения является надстройкой - вспомогательной программой, служащая для добавления в Microsoft Office специальных команд или возможностей. Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее. Для этого в MS Excel 7 нажмите кнопку MS Office 
, а затем щелкните Параметры Excel. Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel. Нажмите кнопку Перейти. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.
В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.
Покажем на рассмотренном нами простейшем примере («пирожки и пирожные»), как воспользоваться средством Поиск Решения.
Вначале надо подготовить электронную таблицу к решению задачи оптимального планирования. В режиме отображения формул таблица показана на рис. 2.20. Ячейки В5 и С5 зарезервированы соответственно для значений х (план по изготовлению пирожков) и у (план по изготовлению пирожных). Ниже этих ячеек представлена система неравенств (а), определяющая ограничения на искомые решения. Неравенства разделены на левую часть (столбец В) и правую часть (столбец D). Знаки неравенств в столбце С имеют чисто оформительское значение. Целевая функция (p) занесена в ячейку В15.
| А | В | С | D | |
| Оптимальное планирование | ||||
| Плановые показатели | ||||
| X (пирожки) | У (пирожные)^ | |||
| Ограничения | ||||
| Левая часть | Знак | Правая часть | ||
| Время производства: | =В5+4*С5 | <= | ||
| Общее количество: | =В5+С5 | <= | ||
| Положительность X: | =В5 | >= | ||
| Положительность Y: | =С5 | >= | ||
| Целевая функция | =В5+2*С5 | |||
Рис. 2.20. Таблица, подготовленная к вычислению оптимального плана
Теперь следует вызвать программу оптимизации «Поиск решения» и сообщить ей, где расположены данные. Для этого надо выполнить команду => Сервис => Поиск решения. На экране откроется соответствующая форма (рис. 2.21).
Рис. 2.21. Начальное состояние формы «Поиск решения»
Далее надо выполнить следующий алгоритм:
Ввести координату ячейки с целевой функцией. В нашем случае это В15. (Заметим, что если перед этим установить курсор на ячейку В15, то ввод произойдет автоматически).
Поставить отметку «максимальному значению», то есть сообщить программе, что нас интересует нахождение максимума целевой функции.
В поле «Изменяя ячейки» ввести В5:С5, то есть сообщить, какое место отведено под значения переменных - плановых показателей.
В поле «Ограничения» надо ввести информацию о неравенствах-ограничениях, которые имеют вид B10<=D10;
B1K=D11;
B12>=D12;
B13>=D13.
Ограничения вводятся следующим образом:
=> щелкнуть по кнопке «Добавить»;
в появившемся диалоговом окне «Добавление ограничения» ввести ссылку на ячейку В10, выбрать из меню знак неравенства <= и ввести ссылку на ячейку D10; снова щелкнуть по кнопке «добавить» и аналогично ввести второе ограничение B11<=D11 и так далее. В конце надо щелкнуть на кнопке ОК.
Закрыть диалоговое окно «Добавление ограничения».Снова появится форма «Поиск решения» (рис. 2.22).
Теперь надо дать последние указания: задача является линейной (это многократно облегчит программе ее решение). Для этого следует щелкнуть по кнопке «Параметры» — появится форма «Параметры поиска решения» (рис. 2.23).
 |
Рис. 2.23. Форма «Параметры поиска решения»
Надо выставить флажок на переключателе «Линейная модель» Остальная информация в форме «Параметры поиска решения» служебная, автоматически устанавливаемые значения нас устраивают. Следует щелкнуть по кнопке ОК, что возвратит нас в форму «Поиск решения».
Вся информация введена. Далее надо щелкнуть по кнопке «Выполнить» — мгновенно в ячейках В5 и С5 появится оптимальное решение (числа 600 и 100), а также число 800 в ячейке В15 — максимальное значение целевой функции (рис. 2.24).
| А | В | С | D | |
| Оптимальное планирование | ||||
| Плановые показатели | ||||
| X (пирожки) | Y (пирожные) | |||
| в | ||||
| Ограничения | ||||
| Левая часть | Знак | Правая часть | ||
| Время производства: | <= | |||
| Общее количество: | <= | |||
| Положительность X: | >= | |||
| Положительность Y: | >= | |||
| Целевая функция | ||||
Рис. 2.24. Результаты решения задачи
Кроме того, на экране появилась еще одна форма — «Результаты поиска решения».
На первом этапе освоения возможностей программы на эту форму можно не обращать внимания (хотя в принципе в ней может оказаться очень полезная информация).
Итак, в результате применения инструмента «Поиск решения», мы получим следующий оптимальный план дневного производства кондитерского цеха: нужно выпускать 600 пирожков и 100 пирожных. В этой точке значение целевой функции f(600,100) = 800. Если один пирожок стоит 2 рубля, то полученная выручка составит 1600 рублей.
Решение, которое мы получили, вполне разумно как с экономической точки зрения, так и с медицинской. Много сладкого — вредно для здоровья, а пирожки и сытнее и полезнее.
Полученная электронная таблица и настроенная на нее сервисная функция «Поиск решения» являются средством, с помощью которого можно решать задачу оптимального планирования при меняющихся условиях. Например, может измениться длина рабочего дня. Тогда надо внести новое значение в ячейку D10 и оптимальный план автоматически пересчитается. Так же может измениться допустимое суммарное число изделий в ячейке D11.
Представьте себе, что в вашей школе учатся неисправимые сладкоежки. И, кроме всех прочих ограничений, перед кондитерским цехом ставится обязательное условие: число пирожных должно быть не меньше числа пирожков. При такой постановке задачи система неравенств (а) примет вид:
х + 4у < 1000;
х + у < 700;
х >0; у>х.
Соответствующее изменение легко внести в электронную таблицу. Для этого достаточно в ячейке D13 вместо 0 записать В5. Результаты поиска решения будут следующими: х = 200, у = 200, f(x,y) = 600. Таким планом вряд ли будет доволен директор кондитерского цеха, поскольку потери прибыли окажутся очень существенными.
Следует иметь в виду, что при решении подобных задач могут возникнуть проблемы, о которых мы здесь не говорили. Например, искомого оптимального решения может вовсе не существовать — тогда программа об этом сообщит
