Тема 7. Применение мастера функций и дополнения "пакет анализа" для статистической обработки данных

Цель работы: изучить возможности Excel по проведению статистического анализа данных, овладеть навыками работы с мастером ) функций и дополнением "Пакет анализа".

ВОЗМОЖНОСТИ EXCEL ПО ПРОВЕДЕНИЮ СТАТИСТИЧЕСКОГО АНАЛИЗА ДАННЫХ

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

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

Приведем примеры статистических функций, наиболее часто используемых при обработке числовых данных:

• СУММ (список аргументов) - функция для определения суммы всех аргументов;

• МАКС(список аргументов) - максимальное значение из листа аргументов;

• МИН (список аргументов) - минимальное значение из списка аргументов;

• СРЗНАЧ (список аргументов) - среднее арифметическое всех перечисленных аргументов;

• СЧЕТ(список аргументов) - количество числовых значений списке аргументов;

• ДИСП (список аргументов) - дисперсия по выборке;

• ДИСПР (список аргументов) - дисперсия для генеральной cовокупности.

Формат записи функций включает имя функции и находящийся в круглых скобках список аргументов, разделенных, как правило, символом; (точка с запятой)¹. Список аргументов может состоять из чисел, адресов ячеек или блоков ЭТ с числовыми данными а также вложенных функций².

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

• БДСУММ - суммирует числа в поле столбца записей БД удовлетворяющих заданному условию;

• БСЧЕТ - подсчитывает количество числовых ячеек в выборке БД по заданному критерию;

• БСЧЕТА - подсчитывает количество непустых ячеек в выборке БД по заданному критерию;

• ДМАКС - возвращает максимальное значение среди выделенных фрагментов БД; л

• ДСРЗНАЧ - возвращает среднее значение среди выделенных фрагментов БД;

• БДДИСП - определяет дисперсию по выборке из выделенной части БД;

• БДДИСПП - определяет дисперсию по генеральной совокупности из выделенной части БД.

Формат записи статистических функций баз данных:

имя_функции (база_данных; поле; критерий),

где:

база_данных - диапазон ячеек БД, включающий имена столбцов!

поле - имя столбца (в кавычках), номер столбца или адрес ячейки с именем столбца БД, по которому вычисляется значения функции;

критерий - диапазон ячеек, содержащий условие отбора. Диапазон включает имя/имена столбца(ов) и ячейку(и) с условием(ями) отбора.

Значения статистических функций в Excel удобно вычислят при помощи мастера функций. Для выполнения статистическог анализа может использоваться также дополнение "Пакет анализах Это дополнение позволяет выполнять дисперсионный (одно-двухфакторный), корреляционный и регрессионный анализ, проверку статистических гипотез о равенстве дисперсий и средний т.д.

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

РАБОТА С МАСТЕРОМ ФУНКЦИЙ

Мастер функций автоматизирует процесс вычисления значений различных функций. Для вызова мастера следует задать

Вставка-Функция или нажать кнопку на панели инструментов Стандартная. В результате на экране появится диалоговое окно Мастер функций.

На первом шаге работы мастера в списке Категория: следует выбрать категорию, к которой относится нужная функция, например Статистические, а затем в списке Функция: - имя функции, например СРЗНАЧ. Внизу диалогового окна выводится краткая информация по выбранной функции.

Для перехода ко второму шагу следует нажать кнопку ОК. Имя выбранной функции при этом заносится в строку формул, и на экране появляется диалоговое окно для определения аргументов функции. Внизу окна отображается справочная информация об этих аргументах. Если аргумент указан полужирным шрифтом, его ввод обязателен, если обычным - его можно опустить.

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

Например, функция РАНГ (А2;$А$2:$А$25) позволит вычислить ранг числа, находящегося в ячейке А2,;., относительно других чисел, расположенных в интервале $А$2:$А$25 (при задании аргументов функции здесь использовалась абсолютная адресация, так как предполагалось копировать формулу в ячейки A3, А4,...,А25).

Предпосылкой использования мастера функций при работе с базами данных является наличие на рабочем листе БД и области критериев. В списке Категория: диалогового окна мастера в этом случае следует выбрать - Работа с базой данных.

РАБОТА С ДОПОЛНЕНИЕМ "ПАКЕТ АНАЛИЗА"

Для работы с дополнением "Пакет анализа" следует задать Сервис-Анализ данных. При отсутствии в меню этой команды следует задать Сервис-Надстройки и в появившемся диалоговом окне Надстройки включить переключатель Пакет анализа.

В качестве примера рассмотрим выполнение средствами данного пакета регрессионного анализа.

После вызова дополнения "Пакет анализа" в появившемся диалоговом окне следует выбрать в списке подходящий инструмент -

"Регрессия" и нажать кнопку ОК. Далее в диалоговом окне Регрессия необходимо:

• в поле ввода Входной интервал Y: указать интервал ячеек, содержащий значения зависимого показателя;

• в поле ввода Входной интервал X: указать интервал ячеек, содержащий значения независимого показателя;

• включить переключатель Метки (только в том случае, если входной интервал содержит наименования показателей);

• выключить переключатель Константа-ноль (в противном случае линия уравнения регрессии будет проходить через начало координат);

• если требуется получить характеристики для уровня надежности, отличающегося от 95 %, включить переключатель Уровень надежности: и указать в соответствующем поле ввода требуемую величину;

• установить посредством переключателей Параметры вывода способ выдачи отчета. Если требуется вывести результаты анализа на текущий рабочий лист, в поле ввода Выходной интервал: следует указать адрес верхней левой ячейки выходного интервала. При выводе результатов на новый рабочий лист следует указать имя, которое будет присвоено этому листу;

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

• нажать кнопку ОК.

Приведем основные обозначения, используемые в отчете с результатами анализа "Регрессия":

• множественный R - коэффициент множественной корреляции;

• R-квадрат - коэффициент множественной детерминации;

• df - число степеней свободы;

• SS - объем вариации;

• MS - дисперсия;

• t-статистика - фактическое значение t для критерия Стьюдента;

• Р-значение - табличное значение уровня значимости для величины t.

ПОСТРОЕНИЕ ЛИНИЙ ТРЕНДОВ

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

• линейная;

• логарифмическая;

• полиномиальная;

• степенная;

• экспоненциальная.

Возможно также построение линий трендов на основе скользящих средних.

Построение линий трендов выполняется в следующей последовательности:

• по фактическим значениям ряда динамики с помощью мастера диаграмм построить соответствующий график;

• вызвать контекстное меню для кривой фактических значений, щелкнув по ней правой кнопкой мыши;

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

• в появившемся диалоговом окне Линия тренда задать тип линии тренда, например линейный;

• раскрыть вкладку Параметры;

• указать длительность прогноза;

• включить переключатели: показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R²);

• нажать кнопку ОК.

Чтобы удалить линию тренда, следует выделить ее и нажать [Del].

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Какие встроенные статистические функции можно использовать при проведении статистического анализа средствами Excel?

2. Каковы правила записи аргументов функции РАНГ?

3. Для каких целей используется дополнение "Пакет анализа"?

4. Как можно вызвать мастер функций?

5. Какова последовательность работы с мастером функций?

6. Каков формат записи статистических функций при работе с базами данных?

7. Как загрузить дополнение "Пакет анализа"? Что надо предпринять, если в меню Excel отсутствует команда Сервис-Анализ данных!

8. Как следует подготовить исходные данные для проведения регрессионного анализа средствами дополнения "Пакет анализа"?

9. Как построить линию тренда и определить прогнозное значение исследуемого показателя?

10. Что показывает величина R²?

ЗАДАНИЯ

1. Вызвать Excel и загрузить файл, созданный при выполнении предыдущих практических заданий.

2. Создать новый лист Лист 10. Присвоить ему имя Статистика.

2. На листе Статистика создать таблицу "Исходные данные для статистической обработки" (табл. 3.9.).

3.9. Исходные данные для статистической обработки

Дата	Производство продукции, кг
	Цех 1	Цех 2	Цех 3	Всего
1.04
…
14.04

Данные в таблицу ввести, используя ссылки на данные таблицы "Производство продукции с шифром 0103 в апреле", расположенной на листе Апрель. Последний столбец рассчитать.

4. Выполнить статистическую обработку данных по производству продукции в отдельных цехах и в целом по предприятию за период с 01.04 по 14.04 (табл. 3.10). Для этого использовать мастер функций, а также инструмент "Описательная статистика" дополнения "Пакет анализа". Результаты обработки расположить на листе Статистика.

3.10. Результаты статистической обработки данных по производству продукции

Показатели	Цех 1	Цех 2	Цех 3	В целом по предприятию
Среднее значение
Минимальное значение
Максимальное значение
Размах вариации
Дисперсия
Стандартное отклонение
Коэффициент вариации, %
Количество объектов

5. Используя функцию Ранг, определить ранг значений показателя "Производство продукции в целом по предприятию". Результаты показать в таблице (ее форма дана в табл. 3.11), разместив ее на том же самом рабочем листе. Данные ввести, используя ссылки на таблицу "Исходные данные для статистической обработки".

3.11. Ранг значений показателя "Производство продукции в целом по предприятию"

6. Создать новый лист Лucm 11. Присвоить ему имя Тренд.

7. На листе Тренд подготовить исходные данные для построения тренда в виде матрицы, первый столбец (X) которой содержит номера периодов (числа: 1,2, ...,14), а второй (Y) - значения показателя "Производство продукции в целом по предприятию". В первой строке матрицы задать метки: "Дни" и "Производство, всего". Значения показателя ввести, используя ссылки на исходную таблицу.

8. Выполнить регрессионный анализ, используя инструмент "Регрессия" дополнения "Пакет анализа". В качестве входного интервала Y указать столбец значений анализируемого показателя, а в качестве входного интервала X - столбец, содержащий номера периодов. Включить переключатель Метки. Результаты анализа вывести на текущий рабочий лист, указав адрес верхней левой ячейки выходного списка. Определить коэффициенты регрессии (тренда).

9. Рассчитать теоретические значения динамического ряда, используя формулы Excel. Сделать прогноз развития производства на последующие три дня.

10. С помощью мастера диаграмм изобразить график производства продукции в целом по предприятию за период с 01.04 по 14.04.

11. Построить линии трендов с использованием линейной, полиномиальной и экспоненциальной функций. Для каждой из этих функций показать на графике уравнение кривой и величину R².

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

1 В качестве разделителя аргументов могут использоваться различные символы в зависимости от настроек Excel и/или Windows.

2 Ранг числа — порядковый номер числа относительно других чисел в списке аргументов.

3 Для переключения режимов абсолютной и относительной адресации можно использовать [F4].

1 Форматирование данных может осуществляться и перед вводом исходных данных и формул.

2 Перед вводом дробных чисел следует выяснить, каким символом отделяется целая часть числа от дробной. В зависимости от настроек Excel и/или Windows это может быть точка или запятая.

1 Для этого необходимо выделить первый интервал ячеек для консолидации на соответствующем листе ЭТ и нажать в окне Консолидация кнопку Добавить. Затем повторить те же действия для других интервалов ячеек, которые необходимо консолидировать

31