Использование пакета анализа в MS Excel

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

Функции пакета анализа можно использовать точно так же, как и любые другие функции MS Excel, а чтобы получить доступ к инструментам пакета анализа, необходимо выполнить следующие действия:

1. Выбрать в меню Сервис команду Анализ данных. Затем на экране появится окно диалога, содержащее список инструментов анализа.

2. Чтобы использовать инструмент анализа, необходимо выбрать его имя в списке и нажмите кнопкуОК.

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

Если команда Анализ данных отсутствует в меню Сервис или если формула, которая использует функцию пакета анализа, возвращает ошибочное значение #ИМЯ?, выберите в меню Сервис команду Надстройки и затем - Пакет анализа -VBA в списке надстроек, после чего наж-мите кнопку ОК. При анализе данных часто возникает необходимость определения различных статистических характеристик или параметров распределения. С помощью MS Excel можно анали-зировать распределение, используя несколько инструментов: встроенные статистические функции, функции для оценки разброса данных, инструмент Описательная статистика, который предоставляет удобные сводные таблицы основных параметров распределения, инструменты Гистограмма и Ранг и персентиль.

Задание 1. Рассмотреть на практическом примере применение инструмента Описательная статистика пакета анализа.

1. Создать новый файл, на 1-м листе построить таблицу по приведенной ниже форме.

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

2. Для использования инструмента Описательная статистика необходимо выбрать в меню Сервис команду Анализ данных, затем в окне диалога Анализ данных выбрать инструмент Описательная статистика и нажать кнопку ОК.

3. В открывшемся диалоговом окне установить следующие опции: Входной интервал: $A$1:$A$20, Группирование: по столбцам, Уровень надежности: флажок - 95%, К-ый наименьший: флажок – 20, К-ый наибольший – 20, Параметры вывода - Выходной интервал: $C$1, Итоговая статистика: флажок, Нажать кнопку ОК.

4. В результате в таблице будет отражен статистический анализ данного столбца (А1:А20).

5. Сохранить результаты на личном диске в файле под именем stat-2. xls.

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

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

1. Создать новый файл, на 1-м листе которого построить таблицу и двугодичную кривую спроса, показанные на рис. 1.

2. Для построения менее «шумной» линии тренда для этих данных можно использовать шестимесячное скользящее среднее. Первая точка в кривой скользящего среднего - это среднее за первые шесть месяцев (январь-июнь 2004 г.). Следующая точка - среднее со второго по седьмой месяцы (Февраль-Июль 2005 г.) и т.д. Инструмент Скользящее среднее может выполнить этот анализ вместо вас.

3. Чтобы использовать инструмент Скользящее среднее, выберите команду Анализ данных в меню Сервис, укажите инструмент Скользящее среднее в окне диалога Анализ данных и наж-мите кнопку ОК. Инструмент Скользящее среднее требует задания трех параметров: диапазона, который содержит анализируемые данные, диапазона для вычисляемых усредненных данных и интервала усреднения. Например, для определения трехмесячного скользящего среднего, задайте интервал 3.

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

Рис. 1. Спрос в месяц

Рис. 2. Скользящее среднее по 6 месяцам

5. Сохранить результаты на личном диске в файле под именем stat-3.xls.