Валентин Арьков - Анализ распределения в Excel

STDEV. S

СТАНДОТКЛОН

Максимальное значение

MAX

МАКС

Минимальное значение

MIN

МАКС

Размах вариации — разность максимального и минимального значений: =F14-F13.

Коэффициент асимметрии:

SKEW

СКОС

Коэффицииент эксцесса:

KURT

ЭКСЦЕСС

Среднее линейное отклонение:

AVEDEV

СРОТКЛ

Объем выборки:

COUNT

СЧЕТ

Медиана:

MEDIAN

МЕДИАНА

Мода:

MODE

МОДА

Сумма:

SUM

СУММ

Здесь нужно дать небольшой комментарий про генеральные и выборочные характеристики. Генеральные показатели относятся к генеральной совокупности. Это всё множество значений. Можно даже скачать: бесконечное множество. Здесь расчёты делают по формуле «деление на n ». Генеральная совокупность по-английски называется POPULATION — буквально «население страны». Поэтому в названии функций ставится английская буква P. Она же случайно перекочевала в русское название соответствующих функций.

Выборочные показатели относятся к выборочной совокупности. Это выборка значений из генеральной совокупности. Здесь расчёты делают по формуле «деление на n — 1». Выборка по-английски называется SAMPLE — «образец, проба, замер, выборка». Поэтому в названии функций есть буква S. В русском переводе эта буква потерялась, что немного сбивает с толку. Так что следите за названиями и за смыслом.

«Минус один» в знаменателе — это поправка на ограниченный объём выборки. Подробности объясняются в курсе математической статистики. Для нас важно одно: при небольшой выборке эта разница будет заметной, и нам нужно использовать деление на n — 1.

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

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

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

Скопируем столбец исходной выборки и вставим его на новый лист. При вставке из буфера выбираем вариант

Values

Значения

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

Вставка значений из буфера

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

Вычисление суммы можно вызвать, выделив столбец чисел и нажав кнопку экспресс-анализа, как мы уже проделали в предыдущих разделах.

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

ABS

Используем найденные суммы и определяем значения показателей.

Вычисление показателей

Для вычисления относительных коэффициентов вариации и осцилляции можно не умножать не 100%, а установить формат вывода в процентах. В этом случае на экране появится знак процента. Это будет более наглядно.

Format Cells — Number — Percentage

Формат ячеек — Число — Процентный

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

Сравнение значений показателей

Реальные данные

Мы разобрались с основными приёмами работы на смоделированных, идеальных данных. Теперь попробуем взять в работу реальные данные.

Мы будем использовать данные о котировках — ценах на акции на фондовом рынке. Основные торги на сегодняшний день по большей части происходят на Московской бирже. Адрес в интернет:

MOEX.COM

Есть разные варианты загрузки биржевых данных. Мы будем использовать сайт компании «Финам». Здесь всё достаточно удобно и понятно.

Заходим на сайт finam.ru. Переходим в раздел Теханализ. Выбираем вкладку Экспорт котировок. Затем настраиваем вывод информации в файл, как показано на рисунке. После этого нажимаем кнопку Получить файли сохраняем файл на компьютере.

Настройка экспорта котировок

Полученный файл имеет расширение названия *.TXT. Содержимое файла можно изучить в приложении

Notepad

Блокнот

Файл с котировками

Загрузим полученный файл в Excel. Выбираем в верхнем меню:

File — Open — Browse

Файл —Открыть — Обзор

В диалоговом окне

Open

Открытие документа

выбираем

Text Files

Текстовые файлы

Открытие текстового файла

В окне Мастера импорта текстовых файлов

Text Import Wizard — Step 1 of 3

Мастер текстов (импорт) — шаг 1 из 3

устанавливаем

My data has headers

Мои данные содержат заголовки

Нажимаем кнопку

Next

Далее

Импорт файла с заголовками

Импорт файла с заголовками

На следующем экране

Step 2 of 4

шаг 2 из 3

убеждаемся, что наши данные разделены на столбцы. При необходимости настраиваем поле

Delimeters

Символом-разделителем является

Уже просматривается будущая таблица с данными. Нажимаем кнопку

Next

Далее

Предварительный просмотр таблицы

На третьем шаги импорта настраиваем формат представления даты. В разделе предварительного просмотра

Data preview

Образец разбора данных

щёлкаем по первому столбцу.

Устанавливаем формат даты:

Column Data Format — Date — YMD

Формат данных столбца — Дата — ГМД

В нашем случае формат даты

YMD (Year — Month — Day)

ГМД (Год — Месяц — День)

Формат даты

При необходимости можно установить дополнительную настройку, нажав кнопку

Advanced

Подробнее

В окне

Advanced Text Import Settings

Дополнительная настройка импорта текста

можно выбрать разделитель целой и дробной частей