Деловая графика в задачах планирования и управления. Регрессионные модели в Excel

Тема 2. Деловая графика в задачах планирования и управления.

Статистика и статистические данные. Получение регрессионных моделей в MS Excel. 

Деловая графика

При решении задач планирования и управления очень важна форма отображения результатов. Чем это отображение нагляднее, тем легче воспринимаются результаты человеком. Самой наглядной формой представления информации являются рисунки, графики и диаграммы. Это породило в информатике целое направление, называемое компьютерной графикой.

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

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

Виды диаграмм:

§  График позволяет отразить изменение одной или нескольких величин в виде непрерывных линий.

§  Круговая диаграмма служит для сравнения значений нескольких величин в одной точке. Применение круговой диаграммы бывает особенно наглядным, если величины в сумме составляют некоторое единое число (100%)

Диаграммы в MS Excel строятся с помощью Мастера диаграмм

§  Чтобы  создать диаграмму нужно:

§  Выбрать диапазон данных в таблице, которые будет отображаться на диаграмме.

§  Запустить мастер диаграмм: Вставка – Диаграмма или кнопкой  Мастер диаграмм - на стандартной панели инструментов.

§  В появившимся окне Мастера диаграмм выбрать тип диаграммы.

§  На четвертом шаге Мастера диаграмм мы увидим, как будет выглядеть наша диаграмма. Справа от диаграммы появится Легенда, которая содержит необходимые пояснения к диаграмме.

§  Уточнить детали отображения диаграммы: дать заголовок, определить оси Категорий и Значений.

§  Определить месторасположение диаграммы: на отдельном листе или на листе вместе с данными.

Используем функцию «Если» в Excel

Одна из самых популярных функций в приложении Excel – ЕСЛИ. Это логическое сравнение значения и получаемого результата. Если говорить проще, то функция может звучать так:

ЕСЛИ условие истинно, то выполняем это, а иначе делаем что-либо еще. Синтаксис этой функции достаточно прост:

ЕСЛИ (логическое_условие; значение_в_случае_ИСТИНА;  значение в случае_ЛОЖЬ)

Разберем подробнее: 

Логическое_условие – значение, которое будем проверять

Значение_в_случае_ИСТИНА – действие, когда требование выполняется Значение в случае_ЛОЖЬ – действие, когда Логическое_условие не выполняется

Как использовать функцию

Пример №1

На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем заданное условие А1>25. ЕСЛИ это требование выполняется, тогда выводим в ячейку «больше 25», иначе «меньше или равно 25».

Пример №2

Теперь более сложное задание. Ниже мы рассмотрим пример переаттестации сотрудников предприятия. Изначально таблица выглядит так:

Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ. Критерии у нас будут такими: кто набрал более 45 баллов, тот считается сдавшим экзамен, ну а все остальные нет.

Для выполнения задачи необходимо:

Выбрать ту ячейку, в которой будем вводить формулу. У нас это С3.

Затем определяемся с необходимыми параметрами:

Логическое_условие – B3>45

Значение_в_случае_ИСТИНА – Сдал, значение_в_случае_ЛОЖЬ – не сдал Таким образом, получаем формулу = ЕСЛИ (В3>45; «Сдал»; «Не сдал»)

Формула будет сравнивать число в ячейке В3 и 45. Если наше требование выполняется и Баллы больше 45, то в ячейке результата мы увидим «Сдал», иначе «Не

сдал».

Копируем формулу в каждую ячейку удобным способом, и смотрим результат.

Используем несколько условий

При необходимости, функцию ЕСЛИ можно вложить друг в друга. Таким образом, мы расширим варианты решений.

Для примера возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку: Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60. Оценку Хорошо можно будет получить, набрав от 45 до 60 балов. Ну и в остальных случаях ставим Плохо.

Для решения этой задачи составим формулу, включив в нее необходимые критерии оценивания: =ЕСЛИ(В3>60;«Отлично»;ЕСЛИ(B2>45;«Хорошо»;«Плохо»)).

 

Эта формула использует одновременно два условия. Первая проверка В3>60. Если баллы действительно больше 60, то в поле Результата мы получаем Отлично, и дальнейшая проверка условий не выполняется. Если Баллы меньше 60, то срабатывает вторая часть формулы, и мы проверяем В3>45 или нет. Если все верно, то возвращается значение «Хорошо», иначе «Плохо».

Затем достаточно скопировать формулу во все ячейки столбца и увидеть результат сдачи переаттестации.

Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить, что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.

О статистике и статистических данных.

Будем рассматривать пример нахождения зависимости частоты заболеваемости жителей города бронхиальной астмой от качества воздуха. 

Любому человеку понятно, что такая зависимость существует. Очевидно, что чем хуже воздух, тем больше больных астмой. Но это качественное заключение. Его недостаточно для того, чтобы управлять уровнем загрязненности воздуха. Для управления требуются более конкретные знания. Нужно установить, какие именно примеси сильнее всего влияют на здоровье людей, как связана концентрация этих примесей в воздухе с числом заболеваний. Такую зависимость можно установить только экспериментальным путем: путем сбора многочисленных данных, их анализа и обобщения.

В таких ситуациях на помощь приходит статистика – наука о сборе, измерении и анализе массовых количественных данных. Существует медицинская статистика, экономическая статистика, социальная статистика и др.. Математический аппарат статистики разрабатывает раздел науки под названием «Математическая статистика»

Рассмотрим пример из области медицинской статистики:

Известно, что наиболее сильное влияние на бронхиально-легочные заболевания оказывает угарный газ – оксид углерода. Поставив цель определить эту зависимость, специалисты по медицинской статистике проводят сбор данных. Они собирают сведения из разных городов о средней концентрации угарного газа в атмосфере и о заболеваемости астмой (число хронических больных на 1000 жителей).

Полученные данные можно свести в таблицу: 

C, мг\куб.м	2	2,5	2,9	3,2	3,6	3,9	4,2	4,6	5
P, бол.\тыс.	19	20	32	34	51	55	90	108	171

а также представить в виде точечной диаграммы:

Статистические данные являются приближенными, усредненными. Поэтому они носят оценочный характер. Однако они верно отражают характер зависимости величин. И еще одно важное замечание: для достоверности результатов, полученных путем  анализа статистических данных, этих данных должно быть много.

Из полученных данных можно сделать вывод, что при концентрации углекислого газа до 3 мг\куб.м его влияние на заболеваемость астмой несильное. С дальнейшим ростом концентрации наступает резкий рост заболеваемости

Чтобы построить математическую модель данного явления, нужно получить формулу, отражающую зависимость числа хронических больных Р от концентрации угарного газа С. На языке математики это называется функцией зависимости Р от С: Р(С). Вид такой функции неизвестен, ее следует искать методом подбора по экспериментальным данным.

График искомой функции должен проходить близко к точкам диаграммы экспериментальных данных. Строить функцию так, чтобы ее график точно проходил через все данные точки (см рис.) не имеет смысла. Во первых, математический вид такой функции может оказаться слишком сложным. Во-вторых, экспериментальные значения являются приближенными.

Основные требования к искомой функции:

•      Она должна быть достаточно простой для использования ее в дальнейших вычислениях.

•      График этой функции должен проходить вблизи экспериментальных точек так, чтобы отклонения этих точек от графика были минимальными и равномерны.  Полученную функцию (см рис.) в статистике принято называть регрессионной моделью.

Получение регрессионной модели происходит в 2 этапа:

•      Подбор вида функции;

•      Вычисление параметров функции.

Чаще всего выбор производится среди следующих функций:

•      y=ax+b – линейная функция;

•      y=ax²+bx+с – квадратичная функция (полином второй степени) ;

•      y=aln(x)+b – логарифмическая функция;  y=ae^bx – экспоненциальная функция;  y=ax^b – степенная функция.

Где x – аргумент, y – значение функции, a, b, c, d – параметры функций, ln(x) – натуральный логарифм, e – константа, основание натурального логарифма.

Метод наименьших квадратов

Если вы выбрали (сознательно или наугад) одну из предлагаемых функций, то следующим шагом нужно подобрать параметры (a, b, c и пр.) так, чтобы функция располагалась как можно ближе к экспериментальным точкам. Чтобы это сделать нужно воспользоваться методом наименьших квадратов, предложенном немецким математиком К.Гауссом в 18 веке.

Суть его заключается в следующем: искомая функция должна быть построена так, чтобы сумма квадратов отклонений y-координат всех экспериментальных точек от yкоординат графика функции была бы минимальной.

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

Важно понимать следующее: методом наименьших квадратов по данному набору экспериментальных точек можно построить любую функцию. А вот будет ли она нас удовлетворять, это уже другой вопрос – критерия соответствия.

График регрессионной модели называется трендом (с англ. общее направление, тенденция)

Уже с первого взгляда хочется отбраковать вариант линейного тренда. График линейной функции – прямая. Полеченная по МНК прямая  отражает факт роста заболеваемости от концентрации угарного газа, но по этому графику трудно что-либо сказать о характере этого роста.

А вот квадратичный(внизу) и экспоненциальный (справа)  тренды ведут себя очень правдоподобно.

Обратите внимание на надписи функций, присутствующие на графиках. Это записанные в явном виде искомые функции – регрессионные модели.

Обратите внимание, на графиках присутствует еще одна величина, полученная в результате построения трендов. Она обозначена как R². В статистике эта величина называется коэффициентом детерминированности. Именно он определяет, насколько удачной является полученная регрессионная модель. 

Коэффициент  детерминированности всегда заключен в диапазоне от 0 до 1. Если он  равен 1, то функция точно проходит через табличные значения, если 0, то выбранный вид регрессионной модели предельно неудачен. Чем ближе R² к 1, тем удачнее регрессионная модель.

Из трех выбранных моделей значение R² наименьшее у линейной. Значит она самая неудачная. Значения же R² у двух других моделей достаточно близки. Они одинаково удачны.

Построение регрессионных моделей с помощью MS Excel

Сначала следует ввести табличные данные и построить точечную диаграмму (легенду можно игнорировать).

•      Щелкнуть мышью по полю диаграммы;

•      Выполнить команду Диаграмма – Добавить линию тренда;

•      В открывшемся окне на закладке Тип выбрать Линейный тренд (степенной, экспоненциальный и др.);

•      Перейти к закладке Параметры; установить галочки на флажках «показывать уравнения на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации»;

•      Щелкнуть по кнопке ОК. Прогнозирование по регрессионной модели.

Мы получили регрессионную математическую модель и можем прогнозировать процесс путем вычислений. Теперь можно оценить уровень заболеваемости астмой не только для тех значений концентрации угарного газа, которые были получены путем измерений, но и для других значений. Это очень важно с практической точки зрения. Например, если в городе планируется построить завод, который будет выбрасывать в атмосферу угарный газ, то рассчитав возможную концентрацию газа, можно предсказать, как это отразится на заболеваемости астмой жителей города.

Существует два способа прогнозов по регрессионной модели. Если прогноз производится и пределах экспериментальных значений независимой переменной ( в нашем случае это значение концентрации угарного газа – С), то это называется восстановлением значения.

Прогнозирование за пределами экспериментальных данных называется экстраполяцией.

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

Построим следующую электронную таблицу:

 

Подставляя в ячейку А2 значение концентрации угарного газа, в ячейке В2 будем получать прогноз заболеваемости. Пример:

 

Заметим, что число, получаемое по формуле в ячейке В, на самом деле является дробным. Однако не имеет смысла считать число людей, даже средне, в дробных величинах. Дробная часть удалена – в формате вывода числа указано 0 цифр после запятой.

Экстраполяционный прогноз выполняется аналогично. Табличный процессор дает возможность производить экстраполяцию графическим способом, продолжая тренда пределы экспериментальных данных. Как это выглядит при использовании квадратичного тренда для С=7 показано на рисунке.

Чтобы выполнить экстраполяционный прогноз в MS Excel необходимо выполнить команду: Диаграмма – Добавить линию тренда – Параметры – Прогноз  (на ___ единиц вперед или назад).

В ряде случаев с экстраполяцией надо быть осторожным. Применимость всякой регрессионной модели ограничена, особенно за пределами экспериментальной области. В нашем примере при экстраполяции не следует далеко уходить от величины 5 мг\куб. м. Вполне возможно, что далее характер зависимости существенно меняется. Слишком сложной является система «экология – здоровье человека», в ней много различных факторов, которые связаны друг с другом. Полученная регрессионная функция является всего лишь моделью, экспериментально подтвержденной в диапазоне концентраций от 2 до 5 мг\куб. м. Что будет вдали от этой области, мы не знаем. Всякая экстраполяция держится на гипотезе: «предположим, что за пределами экспериментальной области закономерность сохраняется». 

Квадратичная модель в данном примере в области малых значений концентрации, близких к 0, вообще не годится. Экстраполируя ее на С = 0 мг\куб.м, получим 150 человек больных, т.е. больше,  чем при 5 мг\куб.м. Очевидно, это нелепость. В области малых значений С лучше работает экспоненциальная модель. Кстати, это довольно типичная ситуация: разным областям данных могут соответствовать разные модели.

Краткое описание документа:

Лекционный материал по дисциплине "Информационные технологии" для студентов 4 курса специальности 31.02.01 Лечебное дело