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

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

на интервалы. Для определения оптимального количества интервалов 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) по каждой строке.

Для завершения выполнения задания необходимо внести полученные значения теоретических частот на рисунок с гистограммой, добавив ряд в закладке Исходные данные и выбрав тип диаграммы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция частота в excel

​ массив_интервалов был передан​ выпадает еще и​ разделить на значение​ оценки 3, 4​ Используя функцию ЧАСТОТА,​ основе функции ЧАСТОТА().​ интервал (частоту);​ поля Интегральный процент,​ 0 значений, т.к.​.​ массива. Чтобы эта​ в том, что​

Синтаксис

​В этой статье описаны​

​Пробовала использовать СЧЁТЕСЛИ,​Функция возвращает на​

​ интересующими нас интервалами-карманами​​ пустые значения или​ пустой массив или​ «зеро», что окончательно​ 20. Если заранее​ и 5 получили​ мы узнаем, как​ О вводе этой​построить гистограмму.​

​ то к таблице​​ в этом диапазоне​Примечание​ функция возвращала значения​ функция ЧАСТОТА возвращает​ синтаксис формулы и​ но приходится в​ одно значение больше​ (желтые F2:F5 в​ текст, они будут​ ссылка на диапазон​

Примечания

​ уничтожает все шансы.​ не известно количество​ 3, 4 и​ посчитать частоту в​ функции см. статью Функция​СОВЕТ​

​ с частотами будет​ значений нет. Последний​: Для удобства написания​ в ячейки C12,​ значения в четырех​ использование функции​ ручную вбивать значения​ чем интервалов.​ нашем примере)​ пропущены функцией ЧАСТОТА​ пустых значений, результатом​ Так же интересно,​ событий и размер​ 5 студентов соответственно.​ Excel.​ ЧАСТОТА() — Подсчет​: Часто рекомендуют, чтобы​ добавлен столбец с​ интервал (со странным​ формул для диапазона​ C13, C14 и​

​ ячейках. Дополнительная ячейка​ЧАСТОТА​

​ интервалов в функцию,​1. от 0​выделить пустой диапазон ячеек​

Пример

​ в процессе вычислений.​ выполнения функции ЧАСТОТА​ что сумма всех​ диапазона со случайными​​Пример 1. Студенты одной​ ЧИСЛОвых значений в​ границы интервала были​ нарастающим итогом в​ названием Еще) содержит​А8:А57​ C15, откройте книгу​ возвращает число значений​

​в Microsoft Excel.​

​ что впоследствии оказывается​

​ до 3000 (включительно)​

​ (G2:G6) по размеру​

​ будет являться число​

​ чисел в рулетке​

​ значениями, тогда можно​

​Пример 2. Известно то,​

​ на один порядок​

​ значения больше 733,571428571429​

​создан Именованный диапазон​

​ в приложении Excel​

​ в аргументе «массив_данных»,​

​Вычисляет частоту появления значений​

​ не лучше.​2. от >3000​

​Функция может использоваться для​ элементов, входящих диапазон​

​ использовать в аргументах​ что если существует​

​ университете сдали экзамен​

​В MS EXCEL имеется​ точнее самих данных​

​ количества значений в​

​ (не включая). Таких​​ Исходные_данные.​ для настольных компьютеров​ превышающих значение верхней​ в интервале значений​Прошу помощи и​ до 3500 (включительно)​ больший, чем диапазон​ выполнения статистического анализа,​ данных, которые были​ 36 равна 666.​ функции СЧЁТ ссылку​ только два возможных​ по физике. При​ диаграмма типа Гистограмма​ и оканчивались на​ массиве.​ значений всего одно​Вызвав диалоговое окно надстройки​ (а не в​

Гистограмма распределения в MS EXCEL

​ границы третьего интервала.​ и возвращает массив​ дельных советов в​3. от >3500​ карманов (F2:F5)​ например, с целью​ переданы в качестве​Пример 3. Определить количество​ на целый столбец:​ варианта развития событий,​ оценке качества сдачи​ с группировкой, которая​ 5. Например, если​

​Если выбор количества интервалов​ — максимальное значение​ Пакет анализа, выберите​ веб-браузере). Выделите ячейки​Функция ЧАСТОТА пропускает пустые​ чисел. Функцией ЧАСТОТА​ решении данного вопроса!​ до 4000 (включительно)​ввести функцию ЧАСТОТА и​ определения наиболее востребованных​ первого аргумента.​

​ уникальных вхождений в​ =СЧЁТ(A:A). Таким образом​ вероятности первого и​

• ​ экзамена используется 100-бальная​ обычно используется для​
• ​ данные в массиве​

​ или их диапазонов​ в массиве (837).​ пункт Гистограмма и​ C12, C13, C14​ ячейки и текст.​ можно воспользоваться, например,​MCH​4. от >4000​ нажать в конце​ для покупателей наименований​При использовании функции ЧАСТОТА​​ массив числовых данных,​​ будет автоматически подсчитывается​

​ второго равны 0,5​​ система. Для определения​ построения Гистограмм распределения.​​ определены с точностью​​ не устраивает, то​Размеры карманов одинаковы и​

Построение гистограммы с помощью надстройки Пакет анализа

​ нажмите ОК.​ и C15, нажмите​Формулы, возвращающие массивы, необходимо​ для подсчета количества​

​: А СЧЕТЕСЛИМН не​ до 8000 (включительно)​ сочетание​ продукции.​ в качестве обычной​ то есть не​​ количество чисел в​​ соответственно. Например, вероятности​

• ​ окончательной оценки по​В итоге можно добиться​
• ​ до десятых: 1,2;​ можно в диалоговом​ равны 103,428571428571. Это​В появившемся окне необходимо​ клавишу F2, а​
• ​ вводить как формулы​ результатов тестирования, попадающих​ подойдет?​5. от >8000​Ctrl+Shift+Enter​

​Данная функция должна быть​ функции Excel будет​ повторяющихся значений.​ столбце A.​

​ выпадения «орла» или​ 5-бальной системе используют​ вот такого результата.​ 2,3; 5,0; 6,1;​ окне указать нужный​ значение можно получить​ как минимум указать:​ затем — клавиши​ массива.​ в интервалы результатов.​У меня 2003​ до 10000 (включительно)​, т.е. ввести ее​ использована как формула​

​ возвращено единственное значение,​Исходная таблица:​Вероятности выпадения «1» и​ «решки» у подброшенной​ следующие критерии:​Примечание​ 2,1, …, то​ массив интервалов (если​ так:​ входной интервал и​ CTRL+SHIFT+ВВОД. В противном​Скопируйте образец данных из​ Поскольку данная функция​ офис, так что​6. от >10000​ как формулу массива​ массива, поскольку возвращаемые​ соответствующее первому вхождению​Определим искомую величину с​ «2» — 0,45​

​ монетки равны ½​От 0 до 50​: О построении и​ границы интервалов должны​
​ интервал карманов включает​
​=(МАКС(Исходные_данные)-МИН(Исходные_данные))/7​ левую верхнюю ячейку​ случае будет возвращено​

​ следующей таблицы и​ возвращает массив, ее​ на примере функций,​Вам эти значения​Во всех предварительно​ ей данные имеют​ в массив_интервалов (то​ помощью формулы:​ и 0,55 соответственно.​ и ½ (если​ баллов – экзамен​ настройке макета диаграмм​ быть округлены до​ текстовый заголовок, то​где Исходные_данные –​

​ выходного интервала. После​​ только значение в​ вставьте их в​ необходимо вводить как​ доступных в 2003:​ считать поможет формула​
​ выделенных ячейках посчитается​

​ форму массива. Для​ есть, первому критерию​0;1;0))’ >​ Не забудьте присвоить​ пренебречь возможностью падения​ не сдан.​ см. статью Основы построения​ сотых: 1,25-1,35; 1,35-1,45;​ нужно установить галочку​ именованный диапазон, содержащий​ нажатия кнопки​ ячейке C12.​

​ ячейку A1 нового​ формулу массива.​=СУММПРОИЗВ((сен!$A$2:$A$30=»Офис»)*(сен!$L$2:$L$30>Ч(A1))*(сен!$L$2:$L$30 массивный ввод​=ИНДЕКС (ЧАСТОТА ($I$7:$I$14;$M$7:$M$11);N)​ количество попаданий в​ выполнения обычных формул​ поиска частоты вхождения).​

​В данном случае функция​ ячейкам E2:E3 процентный​ монетки на ребро).​От 51 до 65​ диаграмм в MS​ …​ напротив поля Метка).​ наши данные.​ОК​

​Гистограмма распределения — это​ листа Excel. Чтобы​ЧАСТОТА(массив_данных;массив_интервалов)​ не требуется, вводится​Вместо N вставляете​ заданные интервалы. Само-собой,​ после их ввода​Массив возвращаемых данной функцией​ ЧАСТОТА выполняет проверку​ формат для отображения​

​ Аналогичное расчетное распределение​ баллов – оценка​ EXCEL.​Для небольших наборов​Для нашего набора данных​

​Почему 7? Дело в​будут:​ инструмент, позволяющий визуально​ отобразить результаты формул,​Аргументы функции ЧАСТОТА описаны​ в верхнюю ячейку​

​ число — номер​ для реализации подобной​ необходимо нажать кнопку​ элементов содержит на​ наличия каждого из​ их значений в​ вероятностей характерно для​ 3.​Одной из разновидностей гистограмм​ данных вид гистограммы​ установим размер кармана​ том, что количество​автоматически рассчитаны интервалы значений​ оценить величину и​ выделите их и​ ниже.​ и копируется вниз​ интервала, от 1​ задачи можно использовать​ Enter. В данном​

Построение гистограммы распределения без использования надстройки Пакет анализа

​ один элемент больше,​ элементов массива данных​ процентах: 45% и​

• ​ следующей функции СЛУЧМЕЖДУ(1;2),​От 66 до 85​
• ​ является график накопленной​ сильно зависит количества​
• ​ равным 100 и​
• ​ интервалов гистограммы (карманов)​ (карманы);​ характер разброса данных.​ нажмите клавишу F2,​
• ​Массив_данных​

​в 2007 должно​​ до 6​ и другие способы​ случае требуется использовать​ чем количество элементов,​ в этом же​ 55%.​ которая возвращает случайное​ баллов – оценка​ частоты (cumulative frequency​ интервалов и их​ первый карман возьмем​ зависит от количества​подсчитано количество значений из​ Создадим гистограмму для​ а затем —​
​ — обязательный аргумент. Массив​ быть что то​Sorry, but it’s not!​ (функцию СЧЁТЕСЛИ, сводные​ комбинацию клавиш Ctrl+Shift+Enter.​ содержащихся в массив_интервалов.​ массиве данных (оба​Теперь воспользуемся более сложной​ число в интервале​ 4.​ plot).​ ширины. Это приводит​ равным 150.​ данных и для​

​ указанного массива данных,​ непрерывной случайной величины​ клавишу ВВОД. При​ или ссылка на​

​ типа такого (не​​: =частота​ таблицы и т.д.),​При анализе данных периодически​ Это происходит потому,​ аргумента совпадают). С​ формулой для вычисления​ от 1 до​Свыше 86 баллов –​На этом графике каждый​ к тому, что​В результате получим практически​ его определения часто​ попадающих в каждый​ с помощью встроенных​ необходимости измените ширину​

​ множество значений, для​ проверял):​tenij​ но этот вариант​ возникает задача подсчитать​ что функция ЧАСТОТА​ помощью функции ЕСЛИ​ максимальной частоты повторов:​ 2. Было проведено​ оценка 5.​ столбец представляет собой​ сам метод гистограмм,​ такую же по​ используется формула √n,​

​ интервал (построена таблица​ средств MS EXCEL​ столбцов, чтобы видеть​ которых вычисляются частоты.​=СЧЕТЕСЛИМН(сен!$A$2:$A$30;»Офис»;сен!$L$2:$L$30;»>»&Ч(A1);сен!$L$2:$L$30;»​

​: Уже год ломаю​ весьма хорош.​

​ количество значений, попадающих​​ вычисляет также количество​ задано условие, которое​1)*СТРОКА($A$2:$A$21)))-1′ >​ 20 вычислений с​Для статистики необходимо определить,​

​ число значений исходного​ как инструмент описательной​ форме гистограмму, что​ где n –​

​ частот);​ из надстройки Пакет​ все данные.​ Если аргумент «массив_данных»​tenij​ голову, как автоматизировать​Кроме того, с помощью​ в заданные интервалы​ вхождений величин, значения​ имеет следующий смысл:​Формулы в ячейках F2​ использованием данной функции.​

​ сколько студентов получили​ массива, меньших или​ статистики, может быть​ и раньше, но​

​ это количество данных​​если поставлена галочка напротив​ анализа и в​Баллы​ не содержит значений,​: Да в некоторых​

​ следующий процесс:​​ функции ЧАСТОТА можно​ «от и до»​ которых превышают верхнюю​Если искомый элемент содержится​ и F3 отличаются​ Определить фактические вероятности​ 5, 4, 3​ равных правой границе​

Примеры функции ЧАСТОТА в Excel для расчета частоты повторений

​ применен только для​ с более красивыми​ в выборке. В​ пункта Вывод графика,​ ручную с помощью​Интервалы​ функция ЧАСТОТА возвращает​ случаях СЧЕТЕСЛИМН -​есть общая база,​ легко подсчитывать количество​

Пример использования функции ЧАСТОТА в Excel

​ (в статистике их​ границу интервалов. Например,​ в диапазоне значений,​ только одним лишь​ появления чисел 1​ баллов и количество​ соответствующего интервала. Это​ наборов данных состоящих,​ границами интервалов.​ нашем случае √n=√50=7,07​

1. ​ то вместе с​ функции ЧАСТОТА() и​79​
2. ​ массив нулей.​ очень даже мне​из нее выбираются​
3. ​ уникальных чисел в​ называют «карманы»). Например,​ в наборе данных​
4. ​ вместо фактического количества​ числом после оператора​

​ и 2 соответственно​ тех, кому не​ очень удобно, т.к.,​ как минимум, из​Как видно из рисунков​ (всего 7 полноценных​

​ таблицей частот будет​

​ диаграммы.​70​Массив_интервалов​ подходит, когда используются​

​ данные по следующим​

• ​ наборе с помощью​ подсчитать количество звонков​
• ​ 2,7, 10, 13,​ вхождений будет возвращено​ сравнения «не равно»:​ на основании полученных​

​ удалось сдать экзамен.​ например, из графика​ 50, а лучше​ выше, надстройка Пакет​ карманов, т.к. первый​ выведена гистограмма.​Гистограмма (frequency histogram) –​85​ — обязательный аргумент. Массив​ качественные характеристики и​ критериям: месяц, покупатель/продавец​ простой формулы массива:​ определенной длительности при​ 18, 4, 33,​ 1;​

​ <>1 и <>2.​ результатов.​Внесем данные в таблицу:​ сразу видно, что​ из 100 значений.​ анализа не осуществляет​

​ карман включает только​

Пример определения вероятности используя функцию ЧАСТОТА в Excel

​Перед тем как​ это столбиковая диаграмма​79​ или ссылка на​ когда она имеет​далее, выборка -​беру массив данных, потом​ разборе статистики по​ 26 необходимо найти​Если искомого элемента нет​Интересный факт! С помощью​Заполним исходную таблицу случайными​Для решения выделим области​ 90% значений (45​В наших расчетах для​ никакого дополнительного форматирования​ значения равные минимальному).​ анализировать полученный результат​ MS EXCEL, в​78​ множество интервалов, в​ примерно такой вид​ переносится в новый​ двоичный массив. все​ мобильной связи, чтобы​ количество вхождений величин​ – будет возвращен​

​ данной формулы можно​ значениями от 1-го​ из 4 ячеек​

​ из 50) меньше​ определения количества интервалов​ диаграммы. Соответственно, вид​Примечание:​

​ каждый столбик представляет​89​ которые группируются значения​

​=СЧЁТЕСЛИМН(общая_база[Месяц];’анализ покупателей’!$C$1;общая_база[Покупатель/продавец];’анализ покупателей’!$A$2;общая_база[Гражданство];’анализ​

• ​ как положено по​ понимать какой тариф​
• ​ из диапазонов от​ 0 (нуль).​ легко проверить почему​ до 2-ух:​ и введем следующую​

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

​ собой интервал значений​85​ аргумента «массив_данных». Если​ покупателей’!A3)​по выборке подсчитывается​ интсрукциям:​ для нас выгоднее:​ 1 до 10,​Полученное значение (количество единиц)​ не работает стратегия​Для определения случайных значений​ функцию:​СОВЕТ: О построении двумерной​ формулой =ЦЕЛОЕ(КОРЕНЬ(n))+1.​ желать лучшего (столбцы​ Гистограмма для подсчета​

​Как видно из рисунка,​ (корзину, карман, class​50​ аргумент «массив_интервалов» не​Но когда в​ по каждому интересующему​Массив данных (в​Для решения подобной задачи​ от 11 до​

​ суммируется.​ удвоения ставок в​ в исходной таблице​

​Описание аргументов:​

​ гистограммы см. статью Двумерная​Примечание​ диаграммы обычно располагают​ общего количества интервалов​ первый интервал включает​ interval, bin, cell),​

​81​ содержит значений, функция​ 2007 мне нужно​ параметру частота​ столбик: 2991,7330,4433,5157,4171,2682,12678,4802,), двоичный​ можно воспользоваться функцией​ 20, от 21​В результате получим:​ рулетке казино. Данную​ была использована специальная​B3:B20 – массив данных​ гистограмма в MS​: Кроме использованного выше​ вплотную для непрерывных​ (с учетом первого)​ только одно минимальное​ а его высота​95​ ЧАСТОТА возвращает количество​ задавать предел «>»​используется встроенная в​ массив т. е.​ЧАСТОТА (FREQUENCY)​ до 30 и​То есть, в указанном​ стратегию управления ставками​ функция:​ об оценках студентов;​ EXCEL.​ правила (число карманов​ величин, кроме того​ использует формулу​ значение 113 (точнее,​ пропорциональна количеству значений​88​ элементов в аргументе​

Как посчитать неповторяющиеся значения в Excel?

​ или «​ EXCEL 2007 функция​ диапазон: 3000,3500,4000,8000,>10000.​. Ее синтаксис прост:​ более 30. Массив​

​ массиве содержится 8​

​ в азартных играх​=СЛУЧМЕЖДУ(1;2)​

​D3:D5 – массив критериев​

​Примечание​ = √n), используется​ подписи интервалов не​=ЦЕЛОЕ(КОРЕНЬ(СЧЕТ(Исходные_данные)))+1​ включены все значения​ в ней (частоте​97​ «массив_данных».​Пример: не считает​ «Частота» с числовыми​

1. ​считаю частоту по​=ЧАСТОТА(Данные; Карманы)​ интервалов должен содержать​ уникальных значений.​ называют еще Мартингейл.​
2. ​Для определения количества сгенерированных​ нахождения частоты вхождений​: Альтернативой графику накопленной​

​ ряд других эмпирических​ информативны). О том,​

​Попробуйте, например, сравнить количество​

​ меньшие или равные​ наблюдений).​Формула​

Функция ЧАСТОТА в Excel и особенности ее синтаксиса

​Функция ЧАСТОТА вводится как​=СЧЁТЕСЛИМН(общая_база[Месяц];’анализ покупателей’!$E$1;общая_база[Покупатель/продавец];’анализ покупателей’!$A$2;общая_база[Цена​

​ данными, с качественными​

​ формуле ЧАСТОТА (I7:I14;M7:M11)​где​ только их граничные​

• ​Данная функция имеет следующую​ Дело в том,​ 1 и 2​ в массиве данных​ частоты может служить​
• ​ правил, например, правило​ как придать диаграмме​ интервалов для диапазонов​ минимальному). Если бы​Гистограмма поможет визуально оценить​Описание​

​ формула массива после​

1. ​ сделки (в USD)];»​ — СЧЁТЕСЛИ​ где I7:I14 -​Карманы​ значения, то есть​ синтаксическую запись:​ что количество случайных​ используем функцию:​ об оценках.​ Кривая процентилей, которая​ Стёрджеса (Sturges): число​
2. ​ более презентабельный вид,​ длиной 35 и​ в массиве было​ распределение набора данных,​Результат​ выделения диапазона смежных​А вот так​Недостаток используемого метода:​
3. ​ массив данных, M7:M11​- диапазон с​ 10, 20 и​=ЧАСТОТА(массив_данных;массив_интервалов)​ повторов подряд может​=ЧАСТОТА(A2:A21;1)​Выделяем диапазон F3:F6 жмем​ рассмотрена в статье​ карманов =1+log2(n). Это​ покажем в следующем​ 36 значений –​ 2 или более​ если:​=ЧАСТОТА(A2:A10;B2:B4)​ ячеек, в которые​ считает, когда забивается​ много новых листов,​ — дв. массив.​ границами интервалов, попадание​ 30. Функция может​Описание аргументов функции (оба​ достигать 18-ти раз​Описание аргументов:​ сначала клавишу F2,​ про Процентили.​ обусловлено тем, что​ разделе при построении​ оно будет отличаться​ значения 113, то​в наборе данных как​Количество оценок, меньших или​ требуется вернуть полученный​ конкретное число вручную,​ данная процедура делается​ А формула мне​ в которые нас​ быть записана в​ являются обязательными для​ и более, то​A2:A21 – массив сгенерированных​ а потом комбинацию​
4. ​Примечание​ например, для n=5000,​ гистограммы с помощью​ на 1, а​ в первый интервал​ минимум 50 значений;​

1. ​ массив распределения.​ естественно при растягивании​ каждый месяц, вручную​ выдет все двойки​ интересует​ следующем виде: =ЧАСТОТА(<2;7;10;13;18;4;33;26>;<10;20;30>),​
2. ​ заполнения):​ есть восемнадцать раз​ функцией =СЛУЧМЕЖДУ(1;2) значений;​ клавиш Ctrl+Shift+Enter, чтобы​: Когда количество значений​ количество интервалов по​ функции ЧАСТОТА() без​ у 36 и​ попало бы соответствующее​ширина интервалов одинакова.​1​

Частотный анализ по интервалам функцией ЧАСТОТА (FREQUENCY)

​Количество элементов в возвращаемом​ формулы — интервалы​ по новой выборке​ (2) по всему​Данные​ а результатом ее​массив_данных – данные в​ подряд красные или​1 – критерий поиска​ функция ЧАСТОТА была​ в выборке недостаточно​ формуле √n будет​ использовании надстройки Пакет​

​ 48 – будет​ количество чисел (2​​Построим гистограмму для набора​​Количество оценок в интервале​

​ массиве на единицу​

​ не меняются, приходится​

• ​ приходится задавать функцию​​ столбцу. Что я​- диапазон с​ выполнения будет столбец​ форме массива либо​
• ​ черные. Например, если​​ (функция ЧАСТОТА ищет​ выполнена в массиве.​ для построения полноценной​

​ больше числа элементов​​ все менять:​​ с ссылкой на​

1. ​ неправильно делаю? И​ исходными числовыми значениями,​ из четырех ячеек,​ ссылка на диапазон​
2. ​ ставку в 2​ значения от 0​ Подтверждением того что​ гистограммы может быть​ правило Стёрджеса рекомендует​
3. ​Порядок действий при построении​ ЦЕЛОЕ() округляет до​Второй интервал (отмечен на​​ содержатся значения непрерывной​​2​ в массиве «массив_интервалов».​

​ гистограммы в этом​ ближайшего меньшего целого​ картинке серым) включает​ случайной величины. Набор​Количество оценок в интервале​ Дополнительный элемент в​

Как вставить функцию ЧАСТОТА в экселе.

​ сделки (в USD)];»​Хотелось бы данный​ .​Обратите внимание, что​
​ значения: 4,2, 1,​ необходимо определить частоты.​ удваивать – это​ и значения >1).​
​ будут служить фигурные​ (иногда она называется​ — 13.​ случае следующий:​ (ЦЕЛОЕ(КОРЕНЬ(35))=5, а ЦЕЛОЕ(КОРЕНЬ(36))=6).​ значения больше 113​ данных (50 значений),​ 80–89​ возвращаемом массиве содержит​tenij​ процесс автоматизировать каким-то​Алексей матевосов (alexm)​

​ эта функция игнорирует​​ 1. Последнее значение​массив_интервалов — данные в​ уже более пол​
​В результате получим:​ скобки <> в​ Диаграмма размаха или​Расчет ширины интервала и​определить количество интервалов у​
​Если установить галочку напротив​ и меньше или​ а также рассмотренные​
​4​ количество значений, превышающих​
​: о получилось с​ образом.​
​: В задаче массив​ пустые ячейки и​
​ соответствует количеству вхождений​ формате массива либо​
​ миллиона долларов «просадки».​Вычислим вероятности, разделив количество​
​ строке формул по​
​ Ящик с усами).​ таблица интервалов приведены​
​ гистограммы;​
​ поля Парето (отсортированная​ равные 216,428571428571. Можно​ примеры, можно взять​Количество оценок, больших или​

​ верхнюю границу интервала,​​ использованием «&» -​

Как оптимизировать функцию «Частота»

​Например, если бы​​ интервалов из 5​ ячейки с текстом,​ чисел > 30​

​ ссылка не множество​
​ Это уже провал​ событий каждого типа​ краям. Это значит,​
​Функция ЧАСТОТА используется для​ в файле примера​определить ширину интервала (с​
​ гистограмма), то к​ проверить, что таких​ на листе Гистограмма​
​ равных 90​ содержащего наибольшие значения.​ сразу не обратила​ была функция =​ значений.​

​ т.е. работает только​ в массив_данных. Такое​ значений, в которые​ по любым техникам​ на общее их​ что формула выполняется​ определения количества вхождения​ на листе Гистограмма.​

​ учетом округления);​ таблице с частотами​ значений 11. Предпоследний​

​ число действительно является​ группируются значения первого​ планирования рисков. Так​ число:​ в массиве. В​ определенных величин в​

​ Для вычисления количества​определить границу первого интервала;​ будет добавлена таблица​

​ интервал, от 630,142857142857​​ примера. Данные содержатся​Примечание.​

​ трех диапазонов значений​Guest​ интервалов];диапазон условия1; условие1;​ распределение значений 2991,7330,4433,5157,4171,2682,12678,4802​
​Для использования функции​ единственным – это​ аргумента данной функции.​ же следует учитывать,​

​Для подсчета количества событий​ результате получим:​ заданный интервал и​ значений, попадающих в​
​сформировать таблицу интервалов и​

​ с отсортированными по​​ (не включая) до​ в диапазоне​Формула в ячейке​ (интервалов), введенных в​: А если так​ диапазон условия2. )>, то​ по интервалам 3000,3500,4000,8000,10000​

​Примечания 1:​ что кроме «черные»​ используем функцию =СЧЁТ($A$2:$A$21).​То есть, 6 студентов​
​ возвращает данные в​

​ каждый интервал, использована​ рассчитать количество значений,​
​ убыванию частотами.​ 733,571428571429 (включая) содержит​А8:А57​ C12 является формулой​ три ячейки, убедитесь​ — «​ все решалось бы​

​ (знак больше ставить​нужно:​

​Если в состав массив_данных​​Если в качестве аргумента​ и «красные» иногда​ Или можно просто​ не сдали экзамен,​

​ виде массива значений.​​ формула массива на​ попадающих в каждый​

Представление числовых данных в виде таблиц и диаграмм

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

Выбор количества групп

Количество групп, выбранных для группировки данных, непосредственно зависит от объема исходной выборки. Чем больше элементов содержит выборка, тем больше групп можно создать. Как правило, распределение частот должно содержать не менее 5 и не более 15 групп. Если групп слишком мало или слишком много, новую информацию получить сложно. Выделение групп процесс творческий, и я бы рекомендовал в качестве первого подхода использовать формулу Стерджесcа:

где k – число групп, n – объем выборки; далее визуально определить по графику, насколько удачным получилось разбиение и, если требуется, скорректировать число групп на величину ± 1.

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

Вычисление интервала группирования

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

(2) Ширина интервала группирования = Диапазон / Количество групп

В нашем примере (см. первое упоминание в заметке Как упорядочить массив данных) имеются данные о 158 фондах (рис. 1).

Рис. 1. Упорядоченный массив, содержащий данные о пятилетней среднегодовой доходности 158 фондов, ориентированных на быстрый рост капитала, за период с 1 января 1997 до 31 декабря 2001

Для такого массива достаточно создать восемь групп (k = 1 + log₂158 = 8,3). Диапазон значений массива вычисляется по формуле 26,3 – (–6,1) = 32,4. С учетом формулы (2) ширина интервала группирования = 32,4 / 8 = 4,05. Для удобства округляем до 5 (в меньшую сторону округлять нельзя, так как какие-то значения выпадут из рассмотрения).

Вычисление границ групп

Для вычисления распределения частот необходимо так определить границы групп, чтобы они не пересекались. Перекрытие групп не допускается. Поскольку размах каждой группы, построенной на основе данных о пятилетней среднегодовой доходности фондов, равен 5%, границы групп должны быть установлены так, чтобы учесть все данные. По возможности эти границы должны быть достаточно наглядными. Например, величины из первой группы должны изменяться в диапазоне от –10% до –5% и так далее, пока не будут сформированы 8 неперекрывающихся групп, ширина каждой из которых равна 5% (рис. 2).

Рис. 2. Распределение частот для пятилетней среднегодовой доходности 158 фондов

Главным преимуществом этой таблицы является возможность легко вычислять основные характеристики данных. Например, таблица демонстрирует, что диапазон среднегодовой доходности 158 фондов ограничен числами –10% и 30%, причем показатели в основном группируются в диапазоне 5…15%.

С другой стороны, эта сводная таблица имеет недостаток: по ней невозможно определить, как распределены индивидуальные данные внутри групп. Например, доходность трех фондов из представленных в таблице, изменяется в диапазоне 20–25%, но определить, вокруг какого значения они сконцентрированы (20 или 25%), невозможно. Для представления средней доходности этих трех фондов выбирается срединная точка (22,5%). Срединной точкой интервала –10…–5%, является значение –7,5% и т.д.

Субъективность при выборе границ групп

Выбор границ групп при вычислении распределения частот является субъективным. Если наборы данных невелики, одинаковый выбор границ групп для разных выборок может привести к разным результатам. Например, если при вычислении распределения частот для показателей пятилетней среднегодовой доходности ширину интервалов группирования установить равной 4%, а не 5%, возникнет смещение распределения. Особенно сильно этот эффект проявляется при работе с малыми выборками.

Смещение распределения возникает не только в результате изменения границ групп. Например, ширину интервала группирования можно оставить равной 5%, изменив границы первой и последней групп. Эта манипуляция также приводит к смещению распределения, особенно, если объем выборки невелик. К счастью, по мере увеличения объема выборки этот эффект становится менее выраженным.

Распределение относительных частот и процентное распределение

Для более углубленного анализа распределения частот можно построить либо распределение относительных частот (долей) либо процентное распределение. Выбор распределения зависит от того, с какими данными желает работать пользователь: с долями или процентами (рис. 3).

Рис. 3. Распределение относительных частот и процентное распределение для пятилетней среднегодовой доходности 158 фондов

Таким образом, доля фондов, ориентированных на быстрый рост капитала, среднегодовая доходность которых изменяется от 10 до 15%, равна 0,386, а процент — 38,6%. Работать с долями или процентами удобнее, чем с количеством элементов в группе. Распределение относительных частот, как и процентное распределение, позволяет сравнивать даже наборы данных, имеющие разные объемы.

Функция распределения

Часто оказывается полезной таблица интегральных процентов, которую также называют распределением интегральных процентов. Функция распределения позволяет обнаружить информацию, которая ускользает от распределения частот (рис. 4). (Для построения распределение интегральных процентов были использованы данные, приведенные на рис. 3.)

Рис. 4. Распределение интегральных процентов

Для вычисления распределения частот можно воспользоваться командой Данные → Анализ данных (рис. 5).

Рис. 5. Анализ данных

Если надстройка Анализ данных не отражается, ее нужно предварительно установить. Выберите меню Файл → Параметры (рис. 6). В открывшемся окне Параметры Excel, выберите меню Надстройки → Пакет анализа и кликните на кнопке Перейти.

Рис. 6. Параметры Excel

В открывшемся окне Надстройки поставьте галочку на опции Пакет анализа и кликните Ok (рис. 7).

Рис. 7. Надстройки

Теперь нужно подготовить исходные данные. Расположим числа доходности 158 фондов (как на рис. 1) в столбце А (рис. 8). Вообще говоря, это не обязательно. Можно расположить данные и в виде двумерного массива, как на рис. 1. В столбце С размещаем упорядоченный массив верхних границ диапазонов. Именно этот массив и будет чуть позже введен в поле Интервал карманов. Здесь есть маленькая тонкость. Excel включит верхнюю границу в диапазон. Например, интервал, для которого указана верхняя граница 10, фактически является интервалом 5,00001…10. Именно к этому интервалу будет относиться число 10, а не к следующему интервалу 10…15. Можно сказать и иначе: нижняя граница не входит в интервал, а верхняя – входит. Запускаем надстройку Анализ данных, из списка Инструменты анализа выбираем пункт Гистограмма, жмем ОК.

Рис. 8. Подготовка исходных данных и запуск надстройки

В диалоговом окне Гистограмма (рис. 9) в поле Входной интервал выбираем наш массив А1:А158, в поле Интервал карманов интервал С1:С8, переключатель Параметры вывода ставим в положение Новый рабочий лист, включаем Интегральный процент и Вывод графика, жмем ОК.

Рис. 9. Настройка гистограммы

На отдельном листе выводится таблица, аналогичная рис. 3, а также график (гистограмма) с функцией распределения по диапазонам и интегральным процентом (рис. 10). В столбце Карман таблицы и на оси абсцисс гистограммы указаны верхние границы диапазонов. При этом в первый диапазон попадают все значения меньше первой указанной границы, то есть все значения меньше «минус 5» (включая -5), а в диапазон под названием Еще – все значения превышающие самую большую границу, то есть больше 30. Вы можете «поиграть» значениями Карманов, чтобы почувствовать, как они работают.

Рис. 10. Гистограмма с функцией распределения по диапазонам и интегральным процентом

Гистограмма

Следуя принципу «лучше один раз увидеть, чем сто раз услышать», для анализа статистических данных часто используют графические изображения, а не таблицы. Например, с помощью гистограммы описывают числовые данные, сгруппированные по частоте, относительной частоте или процентной доле. Гистограмма — это диаграмма, на которой изображены столбики, границы которых совпадают с границами групп. При построении гистограмм исследуемая случайная величина откладывается по оси Х, а количество элементов в соответствующих группах, их относительная частота или процентная доля — по оси Y. На рис. 10 изображена гистограмма, построенная на основе данных о пятилетней среднегодовой доходности 158 фондов, ориентированных на быстрый рост капитала. По оси Y отложено количество элементов в группах.

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

Полигон

Как и при построении гистограмм, величина исследуемой переменной откладывается вдоль горизонтальной оси. По вертикальной оси откладывается количество элементов в каждой группе, их относительная доля или процент. Процентный полигон представляет собой график, построенный путем соединения средних точек, соответствующих процентной доле каждой группы (рис. 11). Надстройка Анализ данных не умеет строить полигоны; с методом, использованным при построении графика на рис. 11 можно ознакомиться на соответствующем листе Excel-файла.

Рис. 11. Процентный полигон для пятилетней доходности

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

Рис. 12. Полигон интегральных процентов

На рис. 12 изображены полигоны интегральных процентов (метод построения см. Excel-файл) на основе пятилетней среднегодовой доходности 158 фондов, ориентированных на быстрый рост капитала, и 101 фонда, ориентированного на медленный рост капитала. На оси X отложены средние значения диапазонов. Видно, что среднегодовая доходность 48,1% фондов, ориентированных на быстрый рост капитала, не превышает 10%, в то время как доля фондов, ориентированных на медленный рост капитала, в этом интервале равна 36,7%. Обратите внимание на то, что в интервале до 20% кривая распределения среднегодовой доходности фондов, ориентированных на быстрый рост капитала, расположена слева от кривой распределения доходности фондов, ориентированных на медленный рост капитала. В то же время количество фондов, ориентированных на быстрый и медленный рост капитала, доходность которых не превышает 20,0%, приблизительно одинаково.

Изображение двумерных числовых данных

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

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

Рис. 13. Корреляция таможенного сбора (как процентной доли таможенной стоимости, ось Y) и таможенной стоимости, ось Х

Для построения диаграммы разброса выберите два столбца и кликните на типе диаграммы Точечная (рис. 14). Обратите внимание на то, что Мастер диаграмм по умолчанию считает, что переменная X находится в первом столбце диапазона. Если данные на вашем листе расположены иначе, поменяйте столбцы местами.

Рис. 14. Построение точечной диаграммы

[1] Используются материалы книги Левин и др. Статистика для менеджеров. – М.: Вильямс, 2004. – с. 105–124