Пример 5. Описательная статистика

В качестве исходной статистической информации рассматривается база данных о котировке ценных бумаг на бирже, приведенная в таблице 5.1, сформированной на рабочем листе Excel.

Таблица 1.1

Задание

Необходимо рассчитать основные показатели описательной статистики.

Решение

1. Создайте рабочую книгу Пример_Описательная_статистика.

2. На листе рабочей книги Пример_Описательная_статистика постройте таблицу 5.1.

3. Рассчитайте среднее арифметическое.

Выборочное среднее

где  – выборочное среднее, n – объем выборки, – i-й элемент выборки, – сумма всех элементов выборки.

Для таблицы 5.1 имеем (рис. 5.1):

Рис. 5.1.

Функция СЧЕТ() подсчитывает количество чисел в списке аргументов.

4. Вычислите медиану.

Медиана

Медианой называется число, разделяющее выборку пополам: 50% элементов меньше медианы, а 50% – больше.

Данная формула неоднозначна. Ее результат зависит от четности или нечетности числа n.

1. Если выборка содержит нечетное количество элементов, медиана равна  элементу.

2. Если выборка содержит четное количество элементов, медиана лежит между двумя средними элементами выборки и равна среднему арифметическому, вычисленному по этим двум элементам.

Чтобы вычислить медиану выборки, ее сначала необходимо упорядочить

Для таблицы 5.1. имеем (рис. 5.2):

­ исходная таблица упорядочена,

­ n – четное,

­ медиана равна 1,02.

Рис. 5.2

5. Вычислите дисперсию и стандартное выборочное отклонение.

Выборочная дисперсия Выборочная дисперсия – это сумма квадратов разностей между элементами выборки и выборочным средним, деленная на величину, равную объему выборки минус один. где  – выборочное среднее, n – объем выборки, – i-й элемент выборки, – сумма квадратов разностей между элементами выборки и выборочным средним.

Стандартное выборочное отклонение Стандартное выборочное отклонение – квадратный корень из суммы квадратов разностей между элементами выборки и выборочным средним, деленным на величину, равную объему выборки минус один.

Чтобы вычислить выборочную дисперсию, следует выполнить следующее:

­ Вычислить разность между каждым элементом выборки и выборочным средним.

­ Возвести каждую разность в квадрат.

­ Сложить все разности, возведенные в квадрат.

­ Поделить результат на n-1.

Чтобы вычислить стандартное выборочное отклонение, необходимо извлечь квадратный корень из выборочной дисперсии.

Результат вычислений показан на рисунке 5.3.

Рис. 5.3

6. Рассчитайте коэффициент вариации.

Коэффициент вариации Коэффициент вариации равен стандартному отклонению, деленному на среднее арифметическое и умноженному на 100%. где S – стандартное выборочное отклонение,  – выборочное среднее.

В нашем примере (рис. 5.4) коэффициент вариации равен 5%.

Рис. 5.4

7. Рассчитайте основные показатели описательной статистики, используя Пакет анализа (Данные / Анализ /Анализ данных). Для этого:

 Если на вкладке Данные нет команды Анализ данныхв группеАнализ,то необходимо ее установить:

1) Кнопка «Office».

2) Параметры Excel

3) Надстройки. Кнопка Перейти .

4) В открывшемся диалоговом окне Надстройки выбрать Пакет анализа. ОК.

 Выберите на вкладке Данные пункт Анализ данныхв группеАнализ, появится окно с одноименным названием (рис. 5.5). В области Инструменты анализапредставлен список реализованных в Microsoft Excel методов статистической обработки данных.

Рис 5.5

 Выделите режим Описательная статистика, который служит для генерации одномерного статистического отчета по основным показателям положения, разброса и асимметрии выборочной совокупности. ОК. Появится диалоговое окно Описательная статистика (рис. 5.6).

Рисунок 5.6

 В диалоговом окне задайте следующие параметры ( рис. 5.6):

Входной интервал – вводится ссылка на ячейки, содержащие анализируемые данные (в нашем примере $H$2:$H$28).

Группирование – установите в положение По столбцам, т.к. входные данные расположены в столбце.

Метки в первой строке / Метки в первом столбце – устанавливается в активное состояние, если первая строка (столбец) во входном диапазоне содержит заголовки. В нашем случае флажок установить.

Выходной интервал / Новый рабочий лист / Новая рабочая книга – переключатель активизирует поле, новый лист или новую книгу, где необходимо разместить данные. В нашем случае – это ячейка M2.

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

Уровень надежности - установите в активное состояние для того, чтобы в выходную таблицу включить строку для предельной ошибки выборки при установленном уровне надежности. В поле, расположенное напротив флажка, введите требуемое значение уровня надежности (рис.1.2) (например, значение уровня надежности 95% равносильно доверительной вероятности g = 0,95 или уровню значимости a = 0,05).

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

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

 Результаты показаны на рисунке 5.7.

Рис. 5.7

8. Рассчитайте основные показатели описательной статистики, используя статистические функции Excel. Для этого введите в ячейки с O4 по O19 соответствующие формулы, как показано на рисунке 5.8.

Рисунок 5.8

9.  Результаты показаны на рисунке 5.9.

Рисунок 5.9