Линейная и экспоненциальная регрессии – ЧАСТЬ 4

Чтобы  применить  функцию  ЧАСТОТА,  задайте  границы  интервалов,  как  делали  при работе  с  инструментом  Гистограмма,  затем  выделите   диапазон,  в    который  будут записываться выходные данные, — в  нашем  примере это диапазон G2:G21. Он должен быть частью одного столбца, это особенность функции ЧАСТОТА. Введите формулу, и в ней в качестве первого аргумента массив_данных укажите диапазон исходных данных, а второго (массив  _граииц)  —  диапазон  с  заданными  интервалами   границ.  Нажмите клавиши  Ctrl+Shift+Enter,   чтобы   преобразовать  эту   формулу   в    формулу   массива. Подробнее о создании массивов и работе  с  ними рассказывается в  разделе «Массивы» главы 12.

Ранг и персентиль

Предположим, требуется провести разбивку по рангам всех  экзаменационных баллов, представленных на рис. 17.9. Эту задачу можно  решить самостоятельно: отсортировать  все оценки в порядке убывания (лучшие — наверху, худшие — внизу),

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

Рис. 17.13. Функция ЧАСТОТА может организовать связь между результатами анализа и исходными данными

Но проще воспользоваться  инструментом Ранг и персентиль (Rank  And Percentile) пакета анализа. Он не только выполнит поставленную задачу, но и определит процентный ранг для каждого исходного значения. Чтобы активировать инструмент, выберите в меню Сервис команду  Анализ  данных, выделите в  списке название Ранг и персентиль и нажмите кнопку ОК. На экране появится одноименное диалоговое окно, изображенное на рис. 17.14.

Обратите внимание на рекомендации, которые не окажутся для вас лишними:

? Следует всегда принимать решение относительно состояния флажка Метки в первой строке (Labels In First Row). Если он установлен, Excel в качестве заголовка второго столбца  выходной таблицы  возьмет  название соответствующего столбца  входного диапазона. В противном  случае столбец по умолчанию получит название Столбец1 (Column 1).

ПРИМЕЧАНИЕ

Если этот флажок установить, но в исходный диапазон не включить  ячейку с заголовком, диапазон результатов также не получит заголовка — результаты будут следовать с первой ячейки. Так, в нашем примере с  рис.  17.14, если в качестве источника указать диапазон D2:D1001, заголовком диапазона-получателя в столбце G станет значение 936.

? В показанном на рис. 17.14 примере мы анализировали только суммарные оценки, но нам ничто не мешает обработать все данные одновременно (столбцы В, С и D). Тогда в качестве входного диапазона необходимо указать B1:D1001, и итоговая таблица будет состоять не из  четырех столбцов, а из 12 — по четыре столбца для каждого набора данных.

Рис. 17.14. Для анализа результатов экзаменов можно использовать инструмент Ранг и персентиль

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

? Теперь о том, как интерпретировать итоговые данные из нижней части рис. 17.14. Из первой строки  таблицы  результатов  (F2:I2)   видно, что  285-й  элемент  входного диапазона, балл которого равен 1206, имеет ранг 1, и он выше всех остальных.

Корреляция таблиц

Входной и выходной диапазоны, как показано на рис. 17.14 внизу,  имеют  один общий столбец Всего и одинаковое количество строк. Но, поскольку эти данные отсортированы по-разному,  строки  исходного  и  выходного диапазонов  не  совпадают друг  с  другом. Самый простой способ решить проблему — отсортировать выходные данные по столбцу Точка, в  котором отображаются номера позиций соответствующих  значений диапазонаисточника.  Картина,  которую  мы  после  этого   увидим,   показана  в    верхней  части приведенного ниже рисунка. Если затем удалить лишние столбцы (в данном случае Е, F и G), можно получить удобный отчет, подобный представленному на том же рисунке внизу.

Генерация случайных чисел

Встроенная  функция  Excel  СЛЧИС  (RAND)  генерирует  случайные   числа  из  совокупности, имеющей равномерное распределение в интервале от 0 до 1. Другими словами, вероятность появления любого  числа  из этого интервала одинакова.  Поскольку объем выборок, как  правило, не бывает достаточно большим, то нельзя считать, что данные в них  распределены  равномерно. Тем  не  менее  многократное  использование функции СЛЧИС  подтверждает, что  она  не  отдает   предпочтения  каким-либо  определенным значениям  внутри интервала   распределения.  Дополнительную  информацию  об  этой функции можно получить в разделе «Функции СЛЧИС и СЛУЧМЕЖДУ» главы 14.

ПРИМЕЧАНИЕ

Функция СЛЧИС относится к «подстраивающимся» функциям Excel, то  есть ее результат пересчитывается  каждый  раз  при  вводе  новых   значений  или  открытии  файла.  Если требуется  «зафиксировать»  полученный  ряд  случайных  чисел,  выделите  на  листе  все ячейки,  содержащие функции СЛЧИС, скопируйте их в буфер обмена, затем  выберите в меню Правка команду Специальная вставка (Paste Special) и  в открывшемся диалоговом окне   установите   переключатель   в    положение   Значения   (Values).   В   этом   случае подстраивающиеся  формулы в ячейках будут заменены возвращаемыми ими значениями. Однако   вместо   встроенной   функции   СЛЧИС   можно   воспользоваться   инструментом Генерация случайных чисел, который всегда возвращает числа, а не формулы.

Пакет анализа позволяет генерировать случайные числа с  распределениями, отличными от равномерного. Такие множества  случайных чисел находят применение в  различных методах  моделирования. Всего в  пакете анализа таких различных типов  распределения имеется шесть: Равномерное (Uniform), Нормальное  (Normal), Бернулли (Bernoulli), Биномиальное (Binomial), Пуассона (Poisson) и Дискретное (Discrete) (определяемое пользователем). Кроме  того, для получения неслучайных чисел в  заданном интервале предусмотрен     еще     один     тип     распределения     —     Модельное     (Patterned).

Чтобы воспользоваться инструментом Генерация случайных чисел (Random Number Generation),  выберите в  меню  Сервис команду  Анализ данных,  выделите в  списке название Генерация  случайных  чисел  и  нажмите  кнопку  ОК.  На  экране  появится одноименное диалоговое окно, изображенное на рис. 17.15.

Рис. 17.15. Значения в разделе Параметры диалогового окна Генерация случайных чисел изменяются в зависимости от выбранного типа распределения

При работе с окном Генерация случайных чисел придерживайтесь следующих правил:

? В  полях  Число переменных (Number Of Variables) и  Число  случайных чисел (Number  Of  Random  Numbers)  указывается   количество столбцов и  количество случайных чисел в  каждом столбце  соответственно. Например, чтобы создать по 100 случайных чисел в 10 столбцах, нужно ввести значение 10 в поле Число переменных и 100 в поле Число случайных чисел.

? В поле Случайное рассеивание (Random Seed) можно ввести  «затравочное» значение для инициализации генератора случайных чисел. Тогда генерируемые повторно последовательности для определенного типа распределения будут всегда одинаковы. С учетом  этого задействовать параметр Случайное рассеивание следует только в  тех случаях,  когда  необходимо  несколько  раз  воспроизвести одну   и  ту  же  цепочку случайных чисел.

Содержимое диалогового окна Генерация случайных чисел определяется выбранным типом распределения. Как показано на рис.  17.15, когда установлен  тип Равномерное (Uniform), имеется  возможность задать начальную и конечную точки распределения с помощью полей Между (Between) и И (And).

Равномерное распределение

При   выбранном  варианте  Равномерное  генерируется   множество   случайных   чисел, равномерно распределенных  в   интервале,   определяемом  его  начальной  и  конечной границами.   В   этом    смысле    инструмент   работает   точно   так   же,   как   функция СЛЧИСМЕЖДУ. Но в некоторых случаях предпочтительнее остановиться именно на нем, а не на встроенной функции Excel, например когда интервал должен быть отличным от [0, 1] или если требуется получить несколько  одинаковых последовательностей случайных чисел, генерируемых с помощью одного и того же значения инициализации.

Нормальное распределение

Нормальное распределение имеет следующие особенности:

? среднее значение выпадает чаще, чем любое другое;

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

чем значения, приближенные к границам интервала;

? значения выше среднего выпадают с той же частотой, что и значения ниже среднего.

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

Распределение Бернулли

Распределение  Бернулли  характеризуется  тем,  что  все испытания  имеют  одинаковую вероятность успеха, причем результат одной попытки не влияет на исход последующих. (В данном случае слово «успех» не  несет никакой смысловой нагрузки, точно так же можно моделировать  обратное событие  — «неуспех».) Все значения в  распределении Бернулли равны либо 0, либо 1.

Шанс того, что в  каждой ячейке диапазона появится значение 1,  находится «в  руках» единственного параметра распределения р,  выбираемого из интервала от О до 1. Так, чтобы получить последовательность из 100 случайных чисел, наиболее вероятная сумма которых  будет  равна 27,  определите  выходной интервал в   100  ячеек  и  укажите  для параметра р значение 0,27.

Биномиальное распределение

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

(Точно так же в  этом случае можно моделировать вместо числа  успешных испытаний количество неудач.)

Так, предположим, вы проводите  по 10 деловых переговоров за неделю и в 20 % случаев заключаете выгодные контракты. Вам бы хотелось знать, насколько плодотворным в этом случае  может  оказаться  следующий  год.  Введите в   поле  Число  случайных  чисел (Number Of Random  Numbers) значение  50  (рабочих  недель в  году),  0,2  —  в  поле Значение р (Р Value) и число 10 — в  поле Число испытаний  (Number  Of Trials). В результате получится, что в  предстоящем году  вас  не обрадуют всего четыре недели, когда количество сделок упадет до нуля. Видите: к гадалке ходить не надо.

Распределение Пуассона

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

Распределение  Пуассона  имеет  только  один  параметр,  Лямбда  (Lambda),  представляющий собой ожидаемое количество событий. Например, в сервисном центре в среднем за день принимают 10 вызовов. Требуется узнать, как часто на протяжении года можно ожидать 18 и  более вызовов   в  день. Для этого введите  значение 260 (52 недели по 5 рабочих дней) в поле Число случайных чисел и 10 в поле Лямбда (ожидаемое среднее количество  вызовов). Результаты моделирования говорят о том, что на год придется всего около трех напряженных дней, когда можно ожидать по 18 и более клиентов.

Дискретное распределение

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

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

Модельное распределение

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

Рис. 17.16. Модельное распределение позволяет создавать арифметические последовательности с циклическими внутренними повторениями

Работа  инструмента  Модельное  распределение  напоминает   команду   Прогрессия (Series).   Он   создает   одну   или   несколько   арифметических   последовательностей  с (необязательными)    внутренними    циклическими    повторениями.   Например,    чтобы получить последовательность чисел 1, 1, 4, 4, 7, 7, 10, 10, 1, 1, 4, 4, 7, 7, 10, необходимо заполнить окно так, как это показано на рис. 17.16. То есть задать две последовательности  чисел с 1 по 10, с шагом 3 и повторением каждого числа в последовательности  два раза.

Если  для  выбранного шага  последнее  число  цепочки  выходит за   пределы  верхней границы интервала, в качестве него подставляется сама граница. Соответственно, если в интервале с 1 по 10 задать шаг 4, Excel создаст следующий ряд: 1, 5, 9 и 10.

Выборка

Инструмент Выборка (Sampling) извлекает подмножество чисел из большой группы или генеральной  совокупности чисел.  Задав входной   диапазон,  можно  выбрать из  него заданное  количество  значений  либо   случайным  образом,  либо  только  каждое  п-е значение. Затем выбранные числа копируются в  диапазон-получатель. Чтобы применить данный  инструмент,  выберите в  меню  Сервис команду  Анализ  данных,  выделите в списке название Выборка и щелкните на кнопке ОК. Появится одноименное диалоговое окно, изображенное на рис. 17.17.

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

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

СОВЕТ

Чтобы  сделать  выборку  из  диапазона,  содержащего  текстовые   значения,  необходимо сначала  создать  столбец  с  порядковыми  номерами  тестовых  записей  —  возрастающей последовательностью  целых  чисел,  начиная  с  1.  Затем  нужно  с  помощью  инструмента Выборка  извлечь  числа  из  этой  последовательности  и  сопоставить   их   с  текстовыми значениями, подставив номера в качестве аргументов функции ИНДЕКС. Подробнее об этой функции рассказывается в разделе «Функция ИНДЕКС» главы 14.

Скользящее среднее

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

Пусть  у  нас  есть  кривая спроса  на  продукцию  предприятия  за  18  месяцев работы, подобная  показанной  на  рис.  17.18.  Чтобы  сгладить  ее,  можно  использовать метод скользящего среднего с периодом усреднения в шесть месяцев.

Первая точка на кривой — это выведенное среднее за первые шесть месяцев продаж (с января по июнь 2003 года); вторая — среднее со второго по седьмой месяц (с февраля по июль 2003 года) и т.  д.  Чтобы  не  выполнять усреднение  вручную,  воспользуемся инструментом  Скользящее среднее.   Для   этого   выберем  в   списке   диалогового  окна   Анализ  данных  инструмент Скользящее среднее и нажмем кнопку ОК. На экране появится одноименное диалоговое окно, представленное на рис. 17.19.

Рис. 17.18. Кривая фактических объемов продаж за полтора года работы предприятия

Рис. 17.19. Параметры сглаживания кривых устанавливаются в диалоговом окне Скользящее среднее

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

На графике рис. 17.20 усредненная кривая с интервалом усреднения 6 месяцев наложена на исходную кривую с рис. 17.18. Вычисленные сглаженные значения записаны в столбце С, именно они и  использовались  для построения более гладкой ломаной на графике. Обратите внимание, что первые пять ячеек выходного диапазона  содержат  ошибочные значения  #Н/Д.  Вообще  говоря, если  задан  интервал усреднения  п,  то  в   первых п-1 ячейках выходного диапазона  всегда будут получаться  ошибки. Но они не вызывают проблем — Excel игнорирует их, оставляя пустой начальную часть кривой.

Рис. 17.20. После применения инструмента Скользящее среднее общая тенденция развития проявляется более наглядно

СОВЕТ

Обратите внимание, что на рис. 17.20 каждая ячейка, в которой  находятся сглаженные методом скользящего среднего значения, в верхней левой части маркирована флажком. Эти флажки указывают на ошибку. Если щелкнуть на такой ячейке, на экране появится смарт-тег с  предупреждением,  что  формула  в  этой  ячейке  ссылается  на  диапазон,  к  которому прилегают другие значения, то есть формула не  охватывает смежные ячейки. В нашем случае  так  и  должно  быть.  Чтобы  удалить  флажки,  выделите  все  маркированные  ими ячейки, щелкните на значке смарт-тега и в его меню выберите команду Пропустить ошибку (Ignore Error).

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий