Статистическая обработка данных с помощью Excel

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

,

где x1, x2, ... , xn – значения случайной величины X , n – число измерений. Оно широко используется в грубоориентировочных расчетах случайной величины, когда значение случайной величины заменяют ее средним.

Важно также знать, как сильно значения изучаемой величины отличаются от ее среднего, или, иначе говоря, насколько широк разброс случайной величины. Рассеивание случайной величины вокруг ее среднего характеризует дисперсия D[X]. Чем больше дисперсия, тем «случайнее» случайная величина. Для приближенного значения дисперсии дискретной случайной величины X используют следующую формулу:

.

На практике часто используют и другую характеристику рассеивания – среднеквадратичное отклонение sx, вычисляемое по формуле .

Величина sx также характеризует размах колебаний случайной величины X около среднего значения, но sx, в отличие от D[X], имеет ту же размерность, что и случайная величина X.

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

Excel предусматривает также применение 18 статистических инструментов анализа, в том числе такие, как описательная статистика, гистограмма, генерация случайных чисел, корреляция, ковариация и ряд других. Эти инструменты позволяют автоматизировать анализ данных и статистических параметров. Доступ к ним можно получить, выбрав в меню “Сервис” команду “Анализ данных”. Затем в диалоговом окне “Инструменты анализа” следует выбрать нужный инструмент и задать входной и выходной интервалы, а также другие требуемые параметры. Например, инструмент анализа “Описательная Статистика” создает список одномерных статистических характеристик для данных во входном интервале. При помощи этого инструмента можно получить информацию об основной тенденции и изменчивости данных. Инструмент “Описательная Статистика” генерирует, в частности, следующие выходные значения: дисперсию выборки, среднеквадратичное отклонение, медиану, моду и скос. Эти выходные данные вычисляются с помощью тех же алгоритмов, которые используются соответствующими функциями Excel. Подробнее об этих функциях можно прочитать в соответствующих разделах встроенной Справки.

Анализ данных в Excel

Excel располагает средствами для анализа данных – от разрешения простых вопросов типа «что – если» (например, «что будет, если увеличится процентная ставка по кредиту» – как изменятся ежемесячные выплаты и т.п.) до решения сложных задач оптимизации. Эти средства – «Подбор параметра», «Таблица подстановки» и «Поиск решения». Встроенная справочная система содержит их детальное описание и пошаговые инструкции по их применению.

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

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

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