Возможности Excel по проведению статистического анализа данных

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

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

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

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

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

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

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

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

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

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

Формат записи функций включает имя функции и находящийся в круглых скобках список аргументов, разделенных, как правило, символом; (точка с запятой или запятая в зависимости от настоек Windows и/или Excel). Список аргументов может состоять из чисел, адресов ячеек или блоков ЭТ с числовыми данными, а также вложенных функций Вложенная функция - функция, значение которой выступает в качестве аргумента основной функции. Например, функция МИН(СУММ(В2:В15); СУММ(С2:С15)) позволяет вычислить минимальное значение из двух чисел, первое из которых является суммой чисел, находящихся в интервале В2:В15, второе - суммой чисел, находящихся в интервале С2:С15.

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

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

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

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

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

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

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

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

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

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

где:

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

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

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

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

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

 

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

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

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

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

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

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

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