Как найти интегральный процент в экселе

ПК / МУ / ЗАДАНИЕ_1_СТАТИСТИКА / Дополнительные материалы / Построение гистограмм в Excel

Построение гистограмм в Excel Перед построением гистограммы выполняется группировка данных по близким признакам. При группировании по количественному признаку все множество значений признака N делится на интервалы. Для определения оптимального количества интервалов n может быть использована формула Стерджесса: n = [1 + 3,322 × lgN ] где . − операция округления до ближайшего целого. В этом случае величина интервала: h = ( x max — x min )/ n Нижнюю границу первого интервала принимают равной минимальному значению x min . Верхняя граница первого интервала соответствует значению ( x min + h ). Для последующих групп границы определяются аналогично, то есть последовательно прибавляется величина интервала h . В Excel для построения гистограмм используются статистическая функция ЧАСТОТА в сочетании с мастером построения обычных диаграмм и процедура Гистограмма из пакета анализа . Функция ЧАСТОТА (массив_данных, двоичный_массив) вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр, где • Массив_данных — массив исходных данных, для которых вычисляются частоты; • Массив_интервалов — это массив интервалов, по которым группируются значения выборки . Перед вызовом функции ЧАСТОТА необходимо выделить столбец c числом ячеек, равным числу интервалов n , в который будут выведены результаты выполнения функции. Вызвать Мастер функций (кнопка fx ) и функцию ЧАСТОТА . В поле Массив_данных ввести диапазон данных наблюдений А3:А102 (с листа ‘Расчетные данные’) . В поле Массив_интервалов ввести диапазон интервалов (A3:A10 – в данном примере). При завершении ввода данных нажать комбинацию клавиш Ctrl+Shift+Enter. В предварительно выделенном столбце (B3:B10 – в данном примере) должен появиться массив абсолютных частот . Столбец Накопленные частоты получается последовательным суммированием относительных частот (в процентном формате) в направлении от первого интервала к последнему. В завершении с помощью Мастера диаграмм строится диаграмма абсолютных и накопленных частот с выбором типа диаграммы соотвественно гистограмма и график.

Для автоматизированного построения гистограммы средствами Excel необходимо обратиться к меню « Сервис  Анализ данных» . В появившемся списке выбрать инструмент Гистограмма и щелкнуть на кнопке ОК. Появится окно гистограммы, показанное на рис. 2., где задаются следующие параметры: Рис. 2. Диалоговое окно режима Гистограмма Входной интервал : – адреса ячеек, содержащие выборочные данные. Интервал карманов : (необязательный параметр) – адреса ячеек, содержащие границы интервалов (кармана). Эти значения должны быть введены в возрастающем порядке. Метки – флажок, включаемый, если первая строка во входных данных содержит заголовки. Если заголовки отсутствуют, то флажок следует выключить. Выходной интервал: / Новый рабочий лист: / Новая рабочая книга. Включенный переключатель Выходной интервал требует ввода адреса верхней ячейки, начиная с которой будут размещаться вычисленные относительные частоты j . В положении переключателя Новый рабочий лист: открывается новый лист, в котором начиная с ячейки А1 размещаются частности j . В положении переключателя Новая рабочая книга открывается новая книга, на первом листе которой начиная с ячейки А1 размещаются частности j . Парето ( отсортированная гистограмма ) – устанавливается в активное состояние, чтобы представить j в порядке их убывания. Если параметр выключен, то j приводятся в порядке следования интервалов.

Интегральный процент – устанавливается в активное состояние для расчета выраженных в процентах накопленных относительных частот (аналог значений столбца Накопленные частоты ). Вывод графика – устанавливается в активное состояние для автоматического создания встроенной диаграммы на листе, содержащем частоты. Замечание . Как правило, гистограммы изображаются в виде смежных прямоугольных областей. Поэтому столбики гистограммы на рис. 3 целесообразно расширить до соприкосновения друг с другом. Для этого необходимо щелкнуть мышью на диаграмме, далее на панель инструментов Диаграмма , раскрыть список инструментов и выбрать элемент Ряд ‘Частота’ , после чего щелкнуть на кнопке Формат ряда . В появившемся одноименном диалоговом окне необходимо активизировать закладку Параметры и в поле Ширина зазора установить значение 0. Для построения теоретической кривой нормального распределения по эмпирическим данным необходимо найти теоретические частоты. В Excel для вычисления значений нормального распределения используются функция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения. Функция имеет параметры: НОРМРАСП (х; среднее; стандартное_откл; интегральная) , где: х — значения выборки, для которых строится распределение; среднее — среднее арифметическое выборки; стандартное_откл — стандартное отклонение распределения; интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения. Для получения абсолютных значений плотностей распределения (теоретических частот) достаточно найденные значения вероятности умножить на величину интервала (h = 10) и количество наблюдений (N = 100) по каждой строке. Для завершения выполнения задания необходимо внести полученные значения теоретических частот на рисунок с гистограммой, добавив ряд в закладке Исходные данные и выбрав тип диаграммы – график.

Функция НОРМРАСП

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

Дополнительные сведения о новом варианте этой функции см. в статье Функция НОРМ.РАСП.

Синтаксис

Аргументы функции НОРМРАСП описаны ниже.

• X Обязательный. Значение, для которого строится распределение.
• Среднее Обязательный. Среднее арифметическое распределения.
• Стандартное_откл Обязательный. Стандартное отклонение распределения.
• Интегральная — обязательный аргумент. Логическое значение, определяющее форму функции. Если аргумент «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения; если этот аргумент имеет значение ЛОЖЬ, возвращается весовая функция распределения.

Замечания

• Если «standard_dev» не является числом, то возвращается #VALUE! значение ошибки #ЗНАЧ!.
• Если standard_dev ≤ 0, то нормДАТ возвращает #NUM! значение ошибки #ЗНАЧ!.
• Если среднее = 0, стандартное_откл = 1 и интегральная = ИСТИНА, то функция НОРМРАСП возвращает стандартное нормальное распределение, т. е. НОРМСТРАСП.
• Уравнение для плотности нормального распределения (аргумент «интегральная» содержит значение ЛОЖЬ) имеет следующий вид:
• Если аргумент «интегральная» имеет значение ИСТИНА, формула описывает интеграл с пределами от минус бесконечности до x.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Значение, для которого нужно вычислить распределение

Среднее арифметическое распределения

Стандартное отклонение распределения

Интегральная функция распределения для приведенных выше условий

Функция плотности распределения для приведенных выше условий

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Сведение и группировка статистических данных

Цель работы – получить навыки группировки данных в MS Excel.

Задание – сгруппировать статистические данные с помощью надстройки Excel “Анализ данных”

Сведение и группировка статистической информации целесообразно проводить с помощью модуля “Гистограмма” пристройки “Анализ”.

Режим “Гистограмма” служит для вычисления частот попадания данных в указанные границы интервалов, и так же для построения гистограммы интервального вариационного ряда распределения.

В диалоговом окне этого режима

задаются следующие параметры:

1. Входной интервал – вводятся ссылки на ячейки, которые содержат данные для анализа.

2. “Интервал карманов” (необязательный параметр) – вводится ссылки на ячейки, которые содержат набор граничных значений, которые определяют интервалы (карманы). Эти значения должны быть введены в возрастающем порядке. В MS Excel вычисляется число попаданий данных в сформированные интервал, при этом границы интервалов являются строгими нижними границами и нестрогими верхними.

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

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

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

5. Парето (отсортированная гистограмма) – устанавливается в активное положение, если нужно представить данные в порядке убывания частоты. Если флажок снят, то данные в выходном диапазоне будут приведены в порядке следования интервалов.

6. Интегральный процент – устанавливается в активное положение для расчета выраженных в процентах накопленных частот (накопленных частностей) и включения в гистограмму графика кумуляты.

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

Пример анализа статистических данных в Excel

Объем экспорта по регионам Украины за 2009 год приведен ниже.

Создание гистограммы в Excel

Гистограмма или диаграмма Парето (отсортированная гистограмма) – это столбчатая диаграмма, которая показывает частоту повторяемости значений. Для создания гистограммы в Excel необходимо, чтобы у вас был установлен пакет анализа.

1. Убедитесь, что пакет анализа установлен.
2. В один столбец введите исходные данные. В следующий столбец введите интервалы в возрастающем порядке.
3. Откройте вкладку «Данные» и щелкните «Анализ данных».
4. Выберите пункт «Гистограмма» и нажмите «ОК».
5. В разделе «Ввод» выберите диапазон ввода (ваши данные), а затем укажите интервал карманов.
6. В группе «Параметры вывода» выберите расположение выходных данных и настройте другие необходимые параметры.
7. Щелкните «ОК», чтобы закрыть диалоговое окно.

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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