интервал карманов excel что это
LiveInternetLiveInternet
—Метки
—Музыка
—Конвертер видеоссылок
—Подписка по e-mail
—Поиск по дневнику
—Интересы
—Постоянные читатели
—Трансляции
—Статистика
Построение гистограмм распределения в Excel
В связи с написанием диплома тема подсчёта статистики для меня крайне актуальна, посему делюсь найденной крайне полезной стаейкой по построению гистограмм распределения. Точнее частью этой статьи с наипростейшим алгоритмом постороения этих гистограмм Excel. Лично я строю этим способом гистограммы распределения значений показателей психологических тестов, ну а там уж каждому по потребностям, распределение чего надо посмотреть.
В современном мире к статистике проявляется большой интерес, поскольку это отличный инструмент для анализа и принятия решений, а также это отличное средство для поиска причин нарушений процесса и их устранения. Статистический анализ применим во многих сферах, где существуют большие массивы данных: естественно, в первую очередь я скажу, что металлургии, а также в экономике, биологии, политике, социологии и. много где еще. Статья эта будет, как несложно догадаться по ее названию, про использование некоторых средств статистического анализа, а именно — гистограммам.
Ну, поехали.
Статистический анализ в Excel можно осуществлять двумя способами:
• С помощью функций
• С помощью средств надстройки «Пакет анализа». Ее, как правило, еще необходимо установить.
Чтобы установить пакет анализа в Excel, выберите вкладку «Файл» (а в Excel 2007 это круглая цветная кнопка слева сверху), далее — «Параметры», затем выберите раздел «Надстройки». Нажмите «Перейти» и поставьте галочку напротив «Пакет анализа».
А теперь — к построению гистограмм распределения по частоте и их анализу.
Речь пойдет именно о частотных гистограммах, где каждый столбец соответствует частоте появления* значения в пределах границ интервалов. Например, мы хотим посмотреть, как у нас выглядит распределение значения предела текучести стали S355J2 в прокате толщиной 20 мм за несколько месяцев. В общем, хотим посмотреть, похоже ли наше распределение на нормальное (а оно должно быть таким).
*Примечание: для металловедческих целей типа оценки размера зерна или оценки объемной доли частиц этот вид гистограмм не пойдет, т.к. там высота столбика соответствует не частоте появления частиц определенного размера, а доле объема (а в плоскости шлифа — площади), которую эти частицы занимают.
График нормального распределения выглядит следующим образом:
График функции Гаусса
Мы знаем, что реально такой график может быть получен только при бесконечно большом количестве измерений. Реально же для конечного числа измерений строят гистограмму, которая внешне похожа на график нормального распределения и при увеличении количества измерений приближается к графику нормального распределения (распределения Гаусса).
Построение гистограмм с помощью программ типа Excel является очень быстрым способом проверки стабильности работы оборудования и добросовестности коллектива: если получим «кривую» гистограмму, значит, либо прибор не исправен или мы данные неверно собрали, либо кто-то где-то преднамеренно мухлюет или же просто неверно использует оборудование.
А теперь — построение гистограмм!
Способ 1-ый. Халявный.
Карман и частота в эксель
Построение гистограмм в Microsoft Excel
Перед построением гистограммы выполняется группировка данных по близким признакам. При группировании по количественному признаку все множество значений признака делится на
Для определения оптимального количества интервалов может быть использована формула Стерджесса:
где N — количество наблюдений. В этом случае величина интервала:
h = ( V max — V min )/ n
Поскольку количество групп не может быть дробным числом, то полученную по этой формуле величину округляют до целого большего числа.
Функция ЧАСТОТА (массив_данных, двоичный_массив) вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр, где
• Массив_данных — массив исходных данных, для которых вычисляются частоты;
Вызвать Мастер функций (кнопка f x ):
При завершении ввода данных нажать комбинацию клавиш Ctrl+Shift+Enter.
В предварительно выделенном столбце (C5:C12 – в данном примере) должен появиться массив
Столбец Накопленные частоты получается последовательным суммированием относительных частот (в процентном формате) в направлении от первого интервала к последнему.
В завершении с помощью Мастера диаграмм строится диаграмма абсолютных и накопленных частот с выбором типа диаграммы соотвественно гистограмма и график.
В появившемся списке выбрать инструмент Гистограмма и щелкнуть на кнопке ОК. Появится окно гистограммы, где задаются следующие параметры:
Входной интервал :– адреса ячеек, содержащие выборочные данные.
Интервал карманов : (необязательный параметр) – адреса ячеек, содержащие границы интервалов. Это поле предлагается оставить пустым, предоставив Excel самому вычислить границы интервалов (карманов – в терминах Excel).
Метки – флажок, включаемый, если первая строка во входных данных содержит заголовки. Если заголовки отсутствуют, то флажок следует выключить.
Выходной интервал: / Новый рабочий лист: / Новая рабочая книга.
Парето ( отсортированная гистограмма ) – устанавливается, чтобы представить j в порядке их убывания. Если параметр выключен, то j приводятся в порядке следования интервалов.
Интегральный процент – устанавливается в активное состояние для расчета выраженных в процентах накопленных относительных частот (аналог значений столбца Накопленные частоты ).
Вывод графика – устанавливается в активное состояние для автоматического создания встроенной диаграммы на листе, содержащем частоты.
В Excel 2007. 2010 встать на любой столбик гистограммы и правой кнопкой мыши выбрать
Формат ряда данных:
Для построения теоретической кривой нормального распределения по эмпирическим данным необходимо найти теоретические частоты.
В Excel для вычисления значений нормального распределения используются функция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения.
Функция имеет параметры:
х — значения выборки, для которых строится распределение; среднее — среднее арифметическое выборки; стандартное_откл — стандартное отклонение распределения;
интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.
Для получения абсолютных значений плотностей распределения (теоретических частот) достаточно найденные значения вероятности умножить на величину интервала h и количество наблюдений N = 100 по каждой строке.
Для завершения выполнения задания необходимо внести полученные значения теоретических частот на рисунок с гистограммой, добавив ряд в закладке Исходные данные и выбрав тип диаграммы
В Excel 2007. 2010 находясь в обласи гистограммы по правой кнопке мыши выбрать Выбрать данные (или по одноименной кнопке на вкладке Конструктор ):
и в появившемся окне провести манипуляции с вводом нового ряда «Теоретические частоты»:
Построим диаграмму распределения в Excel. А также рассмотрим подробнее функции круговых диаграмм, их создание.
Как построить диаграмму распределения в Excel
График нормального распределения имеет форму колокола и симметричен относительно среднего значения. Получить такое графическое изображение можно только при огромном количестве измерений. В Excel для конечного числа измерений принято строить гистограмму.
Внешне столбчатая диаграмма похожа на график нормального распределения. Построим столбчатую диаграмму распределения осадков в Excel и рассмотрим 2 способа ее построения.
Имеются следующие данные о количестве выпавших осадков:
Первый способ. Открываем меню инструмента «Анализ данных» на вкладке «Данные» (если у Вас не подключен данный аналитический инструмент, тогда читайте как его подключить в настройках Excel):
Задаем входной интервал (столбец с числовыми значениями). Поле «Интервалы карманов» оставляем пустым: Excel сгенерирует автоматически. Ставим птичку около записи «Вывод графика»:
После нажатия ОК получаем такой график с таблицей:
В интервалах не очень много значений, поэтому столбики гистограммы получились низкими.
Теперь необходимо сделать так, чтобы по вертикальной оси отображались относительные частоты.
Найдем сумму всех абсолютных частот (с помощью функции СУММ). Сделаем дополнительный столбец «Относительная частота». В первую ячейку введем формулу:
Способ второй. Вернемся к таблице с исходными данными. Вычислим интервалы карманов. Сначала найдем максимальное значение в диапазоне температур и минимальное.
Чтобы найти интервал карманов, нужно разность максимального и минимального значений массива разделить на количество интервалов. Получим «ширину кармана».
Представим интервалы карманов в виде столбца значений. Сначала ширину кармана прибавляем к минимальному значению массива данных. В следующей ячейке – к полученной сумме. И так далее, пока не дойдем до максимального значения.
Для определения частоты делаем столбец рядом с интервалами карманов. Вводим функцию массива:
Вычислим относительные частоты (как в предыдущем способе).
Построим столбчатую диаграмму распределения осадков в Excel с помощью стандартного инструмента «Диаграммы».
Частота распределения заданных значений:
Круговые диаграммы для иллюстрации распределения
С помощью круговой диаграммы можно иллюстрировать данные, которые находятся в одном столбце или одной строке. Сегмент круга – это доля каждого элемента массива в сумме всех элементов.
С помощью любой круговой диаграммы можно показать распределение в том случае, если
На основании имеющихся данных о количестве осадков построим круговую диаграмму.
Доля «каждого месяца» в общем количестве осадков за год:
Круговая диаграмма распределения осадков по сезонам года лучше смотрится, если данных меньше. Найдем среднее количество осадков в каждом сезоне, используя функцию СРЗНАЧ. На основании полученных данных построим диаграмму:
Получили количество выпавших осадков в процентном выражении по сезонам.
При анализе данных периодически возникает задача подсчитать количество значений, попадающих в заданные интервалы «от и до» (в статистике их называют «карманы»). Например, подсчитать количество звонков определенной длительности при разборе статистики по мобильной связи, чтобы понимать какой тариф для нас выгоднее:
=ЧАСТОТА( Данные ; Карманы )
Обратите внимание, что эта функция игнорирует пустые ячейки и ячейки с текстом, т.е. работает только с числами.
Для использования функции ЧАСТОТА нужно:
Во всех предварительно выделенных ячейках посчитается количество попаданий в заданные интервалы. Само-собой, для реализации подобной задачи можно использовать и другие способы (функцию СЧЁТЕСЛИ, сводные таблицы и т.д.), но этот вариант весьма хорош.
Кроме того, с помощью функции ЧАСТОТА можно легко подсчитывать количество уникальных чисел в наборе с помощью простой формулы массива:
Интервал карманов excel что это
Построение гистограмм в Microsoft Excel
Перед построением гистограммы выполняется группировка данных по близким признакам. При группировании по количественному признаку все множество значений признака делится на
Для определения оптимального количества интервалов может быть использована формула Стерджесса:
где N — количество наблюдений. В этом случае величина интервала:
h = ( V max — V min )/ n
Поскольку количество групп не может быть дробным числом, то полученную по этой формуле величину округляют до целого большего числа.
Функция ЧАСТОТА (массив_данных, двоичный_массив) вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр, где
• Массив_данных — массив исходных данных, для которых вычисляются частоты;
Вызвать Мастер функций (кнопка f x ):
При завершении ввода данных нажать комбинацию клавиш Ctrl+Shift+Enter.
В предварительно выделенном столбце (C5:C12 – в данном примере) должен появиться массив
Столбец Накопленные частоты получается последовательным суммированием относительных частот (в процентном формате) в направлении от первого интервала к последнему.
В завершении с помощью Мастера диаграмм строится диаграмма абсолютных и накопленных частот с выбором типа диаграммы соотвественно гистограмма и график.
В появившемся списке выбрать инструмент Гистограмма и щелкнуть на кнопке ОК. Появится окно гистограммы, где задаются следующие параметры:
Входной интервал :– адреса ячеек, содержащие выборочные данные.
Интервал карманов : (необязательный параметр) – адреса ячеек, содержащие границы интервалов. Это поле предлагается оставить пустым, предоставив Excel самому вычислить границы интервалов (карманов – в терминах Excel).
Метки – флажок, включаемый, если первая строка во входных данных содержит заголовки. Если заголовки отсутствуют, то флажок следует выключить.
Выходной интервал: / Новый рабочий лист: / Новая рабочая книга.
Парето ( отсортированная гистограмма ) – устанавливается, чтобы представить j в порядке их убывания. Если параметр выключен, то j приводятся в порядке следования интервалов.
Интегральный процент – устанавливается в активное состояние для расчета выраженных в процентах накопленных относительных частот (аналог значений столбца Накопленные частоты ).
Вывод графика – устанавливается в активное состояние для автоматического создания встроенной диаграммы на листе, содержащем частоты.
В Excel 2007. 2010 встать на любой столбик гистограммы и правой кнопкой мыши выбрать
Формат ряда данных:
Для построения теоретической кривой нормального распределения по эмпирическим данным необходимо найти теоретические частоты.
В Excel для вычисления значений нормального распределения используются функция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения.
Функция имеет параметры:
х — значения выборки, для которых строится распределение; среднее — среднее арифметическое выборки; стандартное_откл — стандартное отклонение распределения;
интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.
Для получения абсолютных значений плотностей распределения (теоретических частот) достаточно найденные значения вероятности умножить на величину интервала h и количество наблюдений N = 100 по каждой строке.
Для завершения выполнения задания необходимо внести полученные значения теоретических частот на рисунок с гистограммой, добавив ряд в закладке Исходные данные и выбрав тип диаграммы
В Excel 2007. 2010 находясь в обласи гистограммы по правой кнопке мыши выбрать Выбрать данные (или по одноименной кнопке на вкладке Конструктор ):
и в появившемся окне провести манипуляции с вводом нового ряда «Теоретические частоты»:
Гистограмма распределения — это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и в ручную с помощью функции ЧАСТОТА() и диаграммы.
Гистограмма поможет визуально оценить распределение набора данных, если:
Примечание : Для удобства написания формул для диапазона А8:А57 создан Именованный диапазон Исходные_данные.
Построение гистограммы с помощью надстройки Пакет анализа
Как видно из рисунка, первый интервал включает только одно минимальное значение 113 (точнее, включены все значения меньшие или равные минимальному). Если бы в массиве было 2 или более значения 113, то в первый интервал попало бы соответствующее количество чисел (2 или более).
Второй интервал (отмечен на картинке серым) включает значения больше 113 и меньше или равные 216,428571428571. Можно проверить, что таких значений 11. Предпоследний интервал, от 630,142857142857 (не включая) до 733,571428571429 (включая) содержит 0 значений, т.к. в этом диапазоне значений нет. Последний интервал (со странным названием Еще ) содержит значения больше 733,571428571429 (не включая). Таких значений всего одно — максимальное значение в массиве (837).
Почему 7? Дело в том, что количество интервалов гистограммы (карманов) зависит от количества данных и для его определения часто используется формула √n, где n – это количество данных в выборке. В нашем случае √n=√50=7,07 (всего 7 полноценных карманов, т.к. первый карман включает только значения равные минимальному).
Примечание : Похоже, что инструмент Гистограмма для подсчета общего количества интервалов (с учетом первого) использует формулу =ЦЕЛОЕ(КОРЕНЬ(СЧЕТ( Исходные_данные )))+1
Если выбор количества интервалов или их диапазонов не устраивает, то можно в диалоговом окне указать нужный массив интервалов (если интервал карманов включает текстовый заголовок, то нужно установить галочку напротив поля Метка ). 
Для нашего набора данных установим размер кармана равным 100 и первый карман возьмем равным 150. 
Построение гистограммы распределения без использования надстройки Пакет анализа
Порядок действий при построении гистограммы в этом случае следующий:
Примечание : Кроме использованного выше правила (число карманов = √n), используется ряд других эмпирических правил, например, правило Стёрджеса (Sturges): число карманов =1+log2(n). Это обусловлено тем, что например, для n=5000, количество интервалов по формуле √n будет равно 70, а правило Стёрджеса рекомендует более приемлемое количество — 13.
В итоге можно добиться вот такого результата. 
Одной из разновидностей гистограмм является график накопленной частоты (cumulative frequency plot). 
На этом графике каждый столбец представляет собой число значений исходного массива, меньших или равных правой границе соответствующего интервала. Это очень удобно, т.к., например, из графика сразу видно, что 90% значений (45 из 50) меньше чем 495.
Примечание : Когда количество значений в выборке недостаточно для построения полноценной гистограммы может быть полезна Блочная диаграмма (иногда она называется Диаграмма размаха или Ящик с усами ).
ТРЕБОВАНИЯ К СОДЕРЖАНИЮ И ОФОРМЛЕНИЮ ОТЧЕТА
Упражнение 6. Сравнение фактического распределения
Упражнение 5. Построение графиков
Упражнение 4. Группировка с помощью формул
Упражнение 3. Группировка с помощью статистической надстройки
Упражнение 2. Вычисление показателей вариации
Упражнение 1. Генерация исходных данных
МЕТОДИКА ВЫПОЛНЕНИЯ РАБОТЫ
КРАТКОЕ ОПИСАНИЕ ПРОГРАММНОГО КОМПЛЕКСА
Лабораторные работы выполняются в пакете Microsoft Excel, который представляет собой электронную таблицу.
Документ Excel имеет расширение *.xls и называется рабочей книгой. Рабочая книга состоит из листов. Переключатьсямежду листами можно, используя закладки (ярлычки) в нижней части окна «Лист 1» и т.д.
Каждый лист представляет собой таблицу. Таблица состоит из столбцов и строк. Количество столбцов в листе – 256, строк – 65536. Столбцы обозначаются буквами латинского алфавита (в обычном режиме) от A до Z, затем идет AA-AZ, BB-BZ и т.д. Строки обозначаются обычными арабскими числами.
На пересечении столбца и строки находится ячейка. Каждая ячейка имеет свой уникальный (в пределах данного листа) адрес, который составляется из буквы столбца (в обычном режиме) и
номера строки. Адрес ячейки используется для работы с данными (ячейками) и формулами.
Статистические расчеты выполняются тремя способами: с помощью формул, функций и статистической надстройки.
Формулы водятся в ячейку путем набора с клавиатуры. Формулы начинаются со знака равенства (=), например,
Статистические функции вставляют в формулы, выбрав в
верхнем меню [Формулы → Вставить функцию → Категория → Статистические] (в старой версии[Вставка → Функция → Категория → Статистические]). Справочные материалы по этим функциямможно получить в справочном руководстве Microsoft Excel ([Справка → Справка Microsoft Excel] или F1). Пример: вычисления среднего значения:
=СРЗНАЧ(B2:B151)
При выполнении работы также используется статистическая надстройка Microsoft Excel. Чтобы активировать надстройку в новой версии,необходимо выполнить следующие действия:
1. Нажмите кнопку Microsoft Office 
, а затем щелкните Параметры Excel.
2.Выберите команду Надстройки и в окне Управление выберите пункт Надстройки Excel.
3.Нажмите кнопку Перейти.
4.В окне Доступные надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.
Совет. Если Пакет анализа отсутствует в списке поля Доступные надстройки, то для проведения поиска нажмите кнопку Обзор.
В случае появления сообщения о том, что пакет статистического анализа не установлен на компьютере и предложения установить его, нажмите кнопку Да.
5. После загрузки пакета анализа в группе Анализ на вкладке становится доступной команда [Данные → Анализ данных].
В старой версии активировать надстройку можно командой[Сервис → Надстройки] в меню и поставить галочку напротив пункта [Пакет анализа] (см. рис. 1.4).
Рис. 1.4. Включение статистической надстройки MS Excel
После этого станет доступным пункт меню [Сервис →Анализ данных].
Для генерации исходных данных используется функция [Генерация случайных чисел]статистической надстройки Microsoft Excel (см. рис. 1.5).
Рис. 1.5. Вызов функции «Генерация случайных чисел»
После вызова функции, статистическая надстройка предложит указать параметры генерации выборки, как показано на рис. 1.6, 1.7.
Число переменныхустанавливается равным 1, объёмвыборки n (Число случайных чисел). Параметры распределения задаются согласно варианту задания (см. табл. 1.2). При генерировании равномерного распределения параметрами являются минимальное и максимальное значения диапазона (min и max), которые вводятся в окне [Параметры → Между]. Для нормального распределения указывают среднее значение μ и стандартное отклонение σ.
Случайное рассеивание– параметр генераторапсевдослучайных чисел, определяющий начало последовательности. Задавая одно и то же значение параметра, можно каждый раз получать одну и ту же последовательность. В качестве параметра задаются четыре последние цифры номера зачетной книжки.
Рис. 1.6. Выбор параметров генератора нормального распределения
Рис. 1.7. Выбор параметров генератора равномерного распределения
Сгенерированные случайные числа являются вещественными, их необходимо округлить до целых и поместить во второй столбец таблицы. Для этого можно воспользоваться математической функцией ОКРУГЛ, имеющей 2 аргумента: округляемое число и количество десятичных разрядов, до которого его нужно округлить. Число разрядов равно 0 в случае округления до целого.
Задаем функцию округления для одной из ячеек столбца «Округлённые числа». Затем ячейку с формулой копируем в буфер и вставляем во все остальные ячейка столбца (см. рис. 1.8). В дальнейшем, в работе используются только округлённые значения.
Рис. 1.8. Генерация случайных чисел и описательная статистика
Для вычисления показателей вариации применяется функция Описательная статистикастатистической надстройки MicrosoftExcel [Данные → Анализ данных→Описательная статистика](см. рис. 1.9). В диалоговом окне нужно выбрать Входной
интервал,Метки в первой строке,Выходной интервали Итоговая статистика. Вычислите показатели вариации,указанные в табл. 1.1.
Рис. 1.9. Использование функции «Описательная статистика»
При анализе показателей вариации можно использовать следующие правила:
− выборка считается однородной, если коэффициент вариации Vσ≤30%;
− если коэффициенты асимметрии и эксцесса близки к нулю, то форму распределения можно считать близкой нормальному. Критические значения А и Е вычисляют по оценкам дисперсий:
Группировка данных проводится двумя способами: с помощью стандартных функций Excel и статистической надстройки.
Вначале следует создать таблицу нижних границ интервалов группирования (рис. 1.10).
Рис. 1.10. Настройка функцииГистограмма
Для группировки данных с помощью статистической надстройки выбираем меню [Сервис → Анализ данных → Гистограмма]. Указываем следующие параметры:
Входные данные:
−Входной интервал– выборка исходных данных;
−Интервал карманов– нижние границы интерваловгруппирования.
Параметры вывода:
−Выходной интервал– расположение результатовгруппировки на листе;
−Вывод графика– построение гистограммы.
−Интегральный процент– вычисление накопленныхчастостей.
Результат работы функции Гистограмма представлен на
рис. 1.11. Сгенерированную таблицу необходимо дополнить недостающими столбцами. График необходимо настроить для корректного отображения. На рис. 1.12 показан пример рекомендуемого стиля оформления таблицы и графика.
Рис. 1.11. Результат вызова функции «Гистограмма»
Рис. 1.12. Настройка результатов группировки
Таблицу необходимо дополнить следующими столбцами:
− Интервал – подписи для столбцов гистограммы в видетекстовых меток, описывающих границы интервалов группировки;
В графе Всего выполняют подсчет суммы частот.
Для вычисления недостающих характеристик интервалов следует использовать формулы. Например, для вычисления накопленной частости для интервала 90..100 (ячейка G3) используется формула =G2+F3. Вычисление относительной частоты для интервала 80..90 (ячейка F3) выполняется с помощью формулы:
=100*E3/$E$8.
После вычислений следует убедиться в отсутствии грубых ошибок. Например, накопленная частость должна быть равна 100%.
Расположение столбиков гистограммы должно соответствовать границам интервалов группирования данных (их вручную перебиваем в интервалы). Для настройки графика щелкните по столбику гистограммы курсором и нажмите правую кнопку мыши. Выберите [Формат ряда данных → Параметры ряда]и установите нулевое значение параметров ПерекрытиеиШирина зазора.
Пример группировки с помощью формул приводится на рис. 1.13.
Подсчет частоты попадания в интервал значений определяется как разность количества значений меньше верхней границы и меньше нижней границы интервала. Например, частота для первого интервала (ячейка G6) рассчитана с помощью функции СЧЁТЕСЛИ:
Дата добавления: 2014-12-23 ; Просмотров: 2235 ; Нарушение авторских прав?
Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет