Число интервалов по формуле стерджесса в экселе

Обновлено: 03.07.2024

Очень давно не писал блог. Расслабился совсем. Ну ничего, исправляюсь.

Продолжаю новую рубрику блога, посвященную анализу данных с помощью всем известного Microsoft Excel.

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

Статистический анализ в Excel можно осуществлять двумя способами:
• С помощью функций
• С помощью средств надстройки «Пакет анализа». Ее, как правило, еще необходимо установить.

Чтобы установить пакет анализа в Excel, выберите вкладку «Файл» (а в Excel 2007 это круглая цветная кнопка слева сверху), далее — «Параметры», затем выберите раздел «Надстройки». Нажмите «Перейти» и поставьте галочку напротив «Пакет анализа».

А теперь — к построению гистограмм распределения по частоте и их анализу.

Речь пойдет именно о частотных гистограммах, где каждый столбец соответствует частоте появления* значения в пределах границ интервалов. Например, мы хотим посмотреть, как у нас выглядит распределение значения предела текучести стали S355J2 в прокате толщиной 20 мм за несколько месяцев. В общем, хотим посмотреть, похоже ли наше распределение на нормальное (а оно должно быть таким).

*Примечание: для металловедческих целей типа оценки размера зерна или оценки объемной доли частиц этот вид гистограмм не пойдет, т.к. там высота столбика соответствует не частоте появления частиц определенного размера, а доле объема (а в плоскости шлифа — площади), которую эти частицы занимают.

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

График функции Гаусса

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

Построение гистограмм с помощью программ типа Excel является очень быстрым способом проверки стабильности работы оборудования и добросовестности коллектива: если получим «кривую» гистограмму, значит, либо прибор не исправен или мы данные неверно собрали, либо кто-то где-то преднамеренно мухлюет или же просто неверно использует оборудование.

А теперь — построение гистограмм!

Способ 1-ый. Халявный.

Способ 2-ой. Трудный, но интересный.

Будет полезен тому, кто по каким-либо причинам не смог установить Пакет анализа.

Перво-наперво нужно задать интервалы тех самых карманов, которые мы не стали вычислять в способе, описанном выше.
Интервал карманов вычисляют так: разность максимального значения и минимального значений массива, деленная на количество интервалов: (Xmax-Xmin)/n.
Для оценки оптимального для нашего массива данных количества интервалов можно воспользоваться формулой Стерджесса: n

На этом все. Ура!

Гистограмму-то мы построили, а что с ней делать дальше? В следующей статье расскажу о том, какую информацию можно извлечь из гистограмм. Так что не пропустите! А чтобы не пропустить, можно подписаться на обновления блога.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

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

- СЧЁТ (массив данных) – для определения объема выборки. Аргументом является диапазон ячеек, в котором находятся выборочные данные.

- ЧАСТОТА (массив данных; массив интервалов) – для построения вариационного ряда. Аргументами являются диапазон массива выборочных данных и столбец интервалов. Если требуется построить дискретный ряд, то здесь указываются значения варианты, если интервальный – то верхние границы интервалов (их еще называют «карманами»). Поскольку результатом является столбец частот, введение функции следует завершить нажатием сочетания клавиш CTRL+SHIFT+ENTER. Заметим, что задавая массив интервалов при введении функции, последнее значение в нем можно и не указывать – в соответствующий «карман» будут помещены все значения, не попавшие в предыдущие «карманы». Иногда это помогает избежать ошибки, состоящей в том, что наибольшее выборочное значение не помещается автоматически в последний «карман»

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

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

Для нахождения моды и медианы по выборке в Excel используются соответственно функции МОДА(массив данных) и МЕДИАНА (массив данных).

Наиболее простой характеристикой вариации признака является размах вариации:

где x_max – наибольшее, x_min – наименьшее значения в выборке.

В Excel размах вычисляется при помощи формулы:

МАКС(массив данных)-МИН(массив данных)

В Excel дисперсия выборки вычисляется при помощи функции

ДИСПР (массив данных)

Корень квадратный из дисперсии представляет среднее квадратическое отклонение или стандартное отклонение и вычисляется с помощью команды =КОРЕНЬ(номер ячейки).

Кроме того, для сравнения гистограммы или полигона вариационного ряда с нормальным распределением, вычисляют коэффициент асимметрии и эксцесс.

В Excel эти характеристики по выборке вычисляются соответственно функциями

СКОС(массив данных) и ЭКСЦЕСС(массив данных).

Заметим, что для вычисления основных выборочных характеристик в Excel можно использовать также процедуру «Описательная статистика» из надстройки «Пакет анализа».

Чтобы изменить (уменьшить) количество знаков после запятой в полученных значениях для выборочной средней, дисперсии,… надо выделить нужные ячейки, нажать правую кнопку мыши, выбрать «формат ячеек», «числовой» и поставить нужное число знаков после запятой.

Пример 1.1.

Имеются данные о выбросах загрязняющих веществ из 50 источников:

10,4	18,6	10,3	26,0	45,0	18,2	17,3	19,2	25,8	18,7
28,2	25,2	18,4	17,5	41,8	14,6	10,0	37,8	10,5	16,0
18,1	16,8	38,5	37,7	17,9	29,0	10,1	28,0	12,0	14,0
14,2	20,8	13,5	42,4	15,5	17,9	19,	10,8	12,1	12,4
12,9	12,6	16,8	19,7	18,3	36,8	15,0	37,0	13,0	19,5

Составить равноинтервальный ряд, построить гистограмму

Решение. Откроем таблицы Excel. Введем массив данных в диапазон А1:L5. Если Вы изучаете документ в электронной форме (в формате Word, например), для этого достаточно выделить таблицу с данными и скопировать ее в буфер, затем выделить ячейку А1 и вставить данные – они автоматически займут подходящий диапазон. Подсчитаем объем выборки n – число выборочных данных, для этого в ячейку В7 введем формулу =СЧЁТ(А1:J5). Заметим, что для того, чтобы в формулу ввести нужный диапазон, необязательно вводить его обозначение с клавиатуры, достаточно его выделить. Определим минимальное и максимальное значение в выборке, введя в ячейку В8 формулу =МИН(А1:J5), и в ячейку В9: =МАКС(А1:J5).

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

Рис.1.4. Пример 2. Построение равноинтервального ряда

Полученное значение не является целым, оно равно примерно 6,64. Поскольку при k=7 длина интервалов будет выражаться целым числом (в отличие от случая k=6) выберем k=7, введя это значение в ячейку С10. Длину интервала d вычислим в ячейке В11, введя формулу =(B9-B8)/C10.

Зададим массив интервалов, указывая для каждого из 7 интервалов верхнюю границу. Для этого в ячейке Е8 вычислим верхнюю границу первого интервала, введя формулу =B8+B11; в ячейке E9 верхнюю границу второго интервала, введя формулу =E8+B11. Для вычисления оставшихся значений верхних границ интервалов зафиксируем номер ячейки В11 в введенной формуле при помощи знака $, так что формула в ячейке Е9 примет вид =E8+B$11, и скопируем содержимое ячейки E9 в ячейки E10-E14, «потянув» за правый нижний уголок ячейки. Последнее полученное значение равно вычисленному ранее в ячейке B9 максимальному значению в выборке.

Рис.1.5. Пример 2. Построение равноинтервального ряда

Теперь заполним массив «карманов» при помощи функции ЧАСТОТА. Выделим столбец частот, введем формулу =ЧАСТОТА(А1:J5;E8:E14) и нажмем сочетание клавиш CTRL+SHIFT+ENTER.

Для контроля вычислим сумму частот при помощи функции СУММ (значок функции S в группе «Редактирование» на вкладке «Главная»), вычисленная сумма должна совпасть с ранее вычисленным объемом выборки в ячейке В7.

Рис.1.6. Пример 2. Построение равноинтервального ряда

По полученному вариационном ряду построим гистограмму: выделим столбец частот и выберем на вкладке «Вставка» «Гистограмма». Получив гистограмму, изменим в ней подписи горизонтальной оси на значения в диапазоне интервалов, для этого выберем опцию «Выбрать данные» вкладки «Конструктор». В появившемся окне выберем команду «Изменить» для раздела «Подписи горизонтальной оси» и введем диапазон значений варианты, выделив его «мышью».

Читайте также: