Обзор 3х способов: вручную, Пакетом анализа, стандартной диаграммой (с версии 2016).
***
В статистическом анализе часто требуется построить график, отображающий частоту элементов в выборке. Также такой график позволяет узнать, пригодны ли измерения для последующего анализа, и если пригодны, то какой именно анализ позже следует использовать. Наиболее распространен анализ данных при нормальном распределении в выборке. Нормальное распределение (очень упрощенно) — это когда большинство значений в ряду приближены к среднему значению, а остальные значения встречаются тем реже, чем ближе они к минимальному или максимальному значению ряда. Графически это можно представить так:
***Для тех, кто знаком со статистическими исследованиями, сразу прошу прощения за элементарность в объяснении и картинках. Мне бы не хотелось здесь «грузить» читателя, незнакомого со статистикой, расчетами стандартных отклонений, правилами 6-ти сигм и т.д., поэтому стараюсь здесь описывать ситуацию максимально простыми терминами.
То есть, график нормального распределения должен напоминать колокол с вершиной в центре. Перекосы на графике в сторону минимального или максимального значения обычно означают проблему: неверно настроен прибор, выпускающий деталь, неверно работает измерительный прибор и т.д. Либо же (если речь не идет об измерениях) перекосы могут означать ненормальное распределение, что значит, к данным надо применять уже другие исследования.
Это была «матчасть» вкратце :)
Практический пример
Теперь перейдем к задаче в Excel.
Допустим, есть ряд данных с измерениями (всего 56 измерений). Допустим, это измерения длины детали, которую вытачивает станок. Эталонная длина детали — 50 мм. Но в реальности длина деталей отличается от эталона:
Требуется построить график частот для просмотра и анализа отклонений.
Решение. Часть 1. Строим интервалы частот
Для начала следует определить, сколько интервалов частот имеет смысл сделать. На самом деле, их может быть любое количество, желательно, не менее 6, но и не слишком много. Для 56 измерений я возьму 9 интервалов. Размер каждого интервала рассчитаем по формуле
=(МАКС.знач.-МИН.знач)/КОЛИЧЕСТВО интервалов:
Далее строим список интервалов от минимального до максимального значения через этот найденный промежуток. Первая точка — минимальное значение ряда, каждая следующая — через найденный промежуток, последняя точка — максимальная точка ряда+небольшой запас, иначе максимальная точка не будет учтена при расчете частот. Итого получается 10 точек (тут маленькое видео, чтоб было понятно):
По поводу последней точки: ее нужно увеличить небольшим запасом, хотя бы на 0,01, чтобы максимальная точка тоже учитывалась при дальнейшем расчете частот:
После определения интервалов можно поступить 2 способами: самостоятельно рассчитать частоты и построить график, или воспользоваться пакетом анализа и с его помощью построить график. Начну со способа «все сделать самостоятельно».
Часть 2. Способ 1, самостоятельный
Используя функцию ЧАСТОТА, распределим значения по интервалам. Гифка с действиями:
2. Строим гистограмму получившихся частот. Я воспользовалась кнопкой Быстрый анализ:
Если хочется, диаграмму можно настроить для наглядности. Я добавила подписи данных, уменьшила боковой зазор и изменила цвет:
Как видим, наша диаграмма напоминает колокол, значит, исходные данные соответствуют нормальному распределению.
Часть 2. Способ 2, Пакет анализа
Имея список интервалов, построить гистограмму распределения частот можно с помощью Пакета анализа. Пакет анализа — это надстройка, входящая в Excel, но по умолчанию не включенная.
Чтобы активировать надстройку, надо перейти в Параметры Excel, выбрать Надстройки - Перейти и установить флаг Пакет анализа. Команда Анализ данных будет добавлена на вкладку Данные:
Теперь, чтобы построить диаграмму, выполняем:
Анализ данных — Гистограмма — ОК.
Входной интервал — диапазон исходных значений, Интервал карманов — диапазон интервалов, указываем также Выходной интервал — место, куда будет размещен результат анализа, и устанавливаем флаг Вывод графика:
Пакет анализа не только построит гистограмму, но и представит таблицу частот в указанном расположении. Результаты при этом полностью совпадут с теми, что ранее строили самостоятельно.
Часть 3. Способ последний, самый новый
Счастливым обладателям Excel 2016 и выше даже интервалы считать не надо: в этих версиях диаграмма частот появилась как стандартная, достаточно только иметь ряд данных для анализа:
Распределение по интервалам при этом произойдет автоматически, но если необходимо, количество интервалов можно изменить. Подписи горизонтальной оси содержат сразу охват интервала, например, [49,36 49,72], что значит, интервал 49,36-49,72. Для изменения количества интервалов нужно открыть настройки горизонтальной оси и задать там для интервалов либо длину, либо их количество. Я выставила 9, как и в других случаях:
И вот мы снова получили гистограмму частот (полностью совпадает с предыдущими), но уже без таблицы.
