Функция ЧАСТОТА() — Подсчет ЧИСЛОвых значений в EXCEL

Функция ЧАСТОТА() — Подсчет ЧИСЛОвых значений в EXCEL

9 апреля 2013 г.

Функция ЧАСТОТА( ) , английская версия FREQUENCY() , вычисляет частоту попадания значений в заданные пользователем интервалы и возвращает соответствующий массив чисел.

Функцией ЧАСТОТА() можно воспользоваться, например, для подсчета количества результатов тестирования, попадающих в определенные интервалы (См. Файл примера )

Синтаксис функции

ЧАСТОТА ( массив_данных ; массив_интервалов )

Массив_данных — массив или ссылка на множество ЧИСЛОвых данных, для которых вычисляются частоты.

Массив_интервалов — массив или ссылка на множество интервалов, в которые группируются значения аргумента «массив_данных».

Функция ЧАСТОТА() вводится как формула массива после выделения диапазона смежных ячеек, в которые требуется вернуть полученный массив распределения (частот). Т.е. после ввода формулы необходимо вместо нажатия клавиши ENTER нажать сочетание клавиш CTRL+SHIFT+ENTER .

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

Пусть в диапазоне А2:А101 имеется исходный массив чисел от 1 до 100.

Подсчитаем количество чисел, попадающих в интервалы 1-10; 11-20; . 91-100.

Сформируем столбце С массив верхних границ диапазонов (интервалов). Для наглядности в столбце D сформируем текстовые значения соответствующие границам интервалов (1-10; 11-20; . 91-100).

Для ввода формулы выделим диапазон Е2:Е12 , состоящий из 11 ячеек (на 1 больше, чем число верхних границ интервалов). В Строке формул введем =ЧАСТОТА($A$2:$A$101;$C$2:$C$11) . После ввода формулы необходимо нажать сочетание клавиш CTRL+SHIFT+ENTER . Диапазон Е2:Е12 заполнится значениями:

  • в Е2 — будет содержаться количество значений из А2:А101 , которые меньше или равны 10;
  • в Е3 — количество значений из А2:А101 , которые меньше или равны 20, но больше 10;
  • в Е11 — количество значений из А2:А101 , которые меньше или равны 100, но больше 90;
  • в Е12 — количество значений из А2:А101 , которые больше 100 (таких нет, т.к. исходный массив содержит числа от 1 до 100).

Примечание . Функцию ЧАСТОТА() можно заменить формулой = СУММПРОИЗВ(($A$5:$A$104>C5)*($A$5:$A$104

Источник:
http://excel2.ru/articles/funkciya-chastota-podschet-chislovyh-znacheniy-v-ms-excel-chastota

Гистограмма частот в Excel 2016

Excel 2016 обзавелся новыми типами графиков. Причем, это не какие-нибудь дизайнерские новшества, а самые настоящие статистические диаграммы.

Так, «ящик с усами» применяется для анализа выборки. Диаграмма Парето пригодится при анализе вклада отдельных элементов в общую сумму. В этой заметке рассмотрим еще одну новую диаграмму из Excel 2016 – гистограмму частот.

На первый взгляд и в более ранних версиях Excel можно изобразить частоты с помощью диаграмм. Можно, но для этого предварительно необходимо числовые данные сгруппировать. То есть для каждой категории (интервала, группы, года и т.д.) нужно посчитать частоту. Теперь появилась возможность изобразить распределение данных буквально в один клик без предварительных расчетов и группировок.

Строится такая диаграмма в один клик. Выделяем ряд данных и нажимаем кнопку гистограммы частот.

Собственно, все. Тут же появляется соответствующая диаграмма.

Возникает вопрос: как Excel делит данные на интервалы? Справка Excel говорит, что с помощью формулы.

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

Интервалы легко перестроить под свои потребности. Можно, например, задать нижнюю и верхнюю границу, за пределами которых данные будут объединены в один интервал.

При выборе опции выхода за нижнюю и верхнюю границы, судя по той же справке, их значения рассчитываются, как расстояние ±3σ от средней арифметической.

Однако рассчитываемые автоматически значения легко изменить в окне настроек.

Это был пример, когда данные разбиваются на интервалы. Такой вариант группировки установлен по умолчанию (см. окно параметров настройки оси выше).

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

Проведем эксперимент. С помощью функции СЛУЧМЕЖДУ смоделируем равномерно распределенную выборку в пределах, скажем, от 0 до 200. Пусть выборка состоит из 100 значений. Теперь изобразим гистограмму частот.

Как видно, частоты примерно одинаковы.

Источник:
http://statanaliz.info/excel/diagrammy/gistogramma-chastot-v-excel-2016/

Как сделать частоту в excel?

1. Построение вариационного ряда

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

2. Построение группировочного статистического ряда

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

Таблица №1

Для вычисления минимального и максимального элемента воспользуйтесь встроенными в Excel статистическими формулами (главное меню – вставка – функция…) МИН и МАКС.

Таблица №2

начало промежутка

конец промежутка

Середина промежутка

Абсолютная частота

Относи-тельная частота

Накопленная частота

1

2

Для вычисления абсолютной частоты нужна статистическая функция ЧАСТОТА. При её использовании нужно выполнить следующие действия:

а) выделить весь диапазон ячеек, в которых будет располагаться результат подсчёта частот (т.е. это ячейки под заголовком Абсолютная частота в количестве равном числу промежутков)

b) не снимая выделения, поставить курсор в строку формул и нажать на кнопку вставка функции (чуть левее курсора) или Главное меню – вставка – формула.

с) выбрать функцию ЧАСТОТА

d) ввести Массив_данных – диапазон, содержащий элементы выборки (в файле 2.xls это ячейки) B2:B101

e) ввести Массив_интервалов – диапазон ячеек под заголовком Начало промежутка начиная со строчки, соответствующей промежутку под номером 2 до строчки, соответствующей последнему промежутку.

f) нажмите на кнопку ОК и после закрытия окна для ввода аргументов функции ЧАСТОТА поставьте курсор обратно в строку формул.

g) Нажмите на три кнопки Ctrl+Shift+Enter (сначала на первые две, а потом, не отпуская их, нажмите на Enter).

Примечание. Формулу вычисления абсолютной частоты необходимо ввести как формулу массива. Нажатие комбинации клавиш CTRL+SHIFT+ENTER позволяет определить формулу как формулу массива. Если формула не будет введена как формула массива, единственное значение будет равно 1.

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

3. Построение гистограммы группировочного статистического ряда

В качестве элементов группировочного ряда надо взять середины промежутков и приведённые частоты.

Для построения гистограммы выполните следующие действия:

  1. Главное меню: Вставка – Диаграмма.
  2. Тип: точечная.
  3. Диапазон данных: выделите ячейки содержащие значения абсолютных частот.
  4. Ряд: Значения по X: укажите диапазон ячеек содержащий значения середины промежутков.
  5. Готово.

Источник:
http://flash-library.narod.ru/IT-MathSredstva/Lab-rab/lab3-metu.html

Динамическая гистограмма или график распределения частот в Excel

В двух словах: Добавляем полосу прокрутки к гистограмме или к графику распределения частот, чтобы сделать её динамической или интерактивной.

Уровень сложности: продвинутый.

На следующем рисунке показано, как выглядит готовая динамическая гистограмма:

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

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

Читайте также  Тестирование в эмпирическом исследовании по психологии; особенности сбора и обработки данных в дипломной работе

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

На какие вопросы отвечает гистограмма распределения?

Гистограмма – это один из моих самых любимых типов диаграмм, поскольку она дает огромное количество информации о данных.

В данном случае мы хотим знать, как много участников окажется в возрастных группах 20-ти, 30-ти, 40-ка лет и так далее. Гистограмма наглядно покажет это, поэтому определить закономерности и отклонения будет довольно легко.

«Неужели наше мероприятие не интересно гражданам в возрасте от 20 до 29 лет?»

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

Динамическая гистограмма

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

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

Как это работает?

Краткий ответ: Формулы, динамические именованные диапазоны, элемент управления «Полоса прокрутки» в сочетании с гистограммой.

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

Чтобы вычислить размер группы, разделим общее количество (80-10) на количество групп. Количество групп устанавливается настройками полосы прокрутки. Чуть позже разъясним это подробнее.

Далее при помощи функции ЧАСТОТА (FREQUENCY) я рассчитываю количество элементов в каждой группе в заданном столбце. В данном случае мы возвращаем частоту из столбца Age таблицы с именем tblData.

Функция ЧАСТОТА (FREQUENCY) вводится, как формула массива, нажатием Ctrl+Shift+Enter.

Динамический именованный диапазон

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

Когда пользователь перемещает ползунок полосы прокрутки, число строк в динамическом диапазоне изменяется так, чтобы отобразить на графике только нужные данные. В нашем примере задано два динамических именованных диапазона: один для данных – rngGroups (столбец Frequency) и второй для подписей горизонтальной оси – rngCount (столбец Bin Name).

Элемент управления «Полоса прокрутки»

Элемент управления Полоса прокрутки (Scroll Bar) может быть вставлен с вкладки Разработчик (Developer).

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

Гистограмма

График – это самая простая часть задачи. Создаём простую гистограмму и в качестве источника данных устанавливаем динамические именованные диапазоны.

Есть вопросы?

Что ж, это был лишь краткий обзор того, как работает динамическая гистограмма.

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

Более простой вариант гистограммы можно создать, используя сводные таблицы.

Пишите в комментариях любые вопросы и предложения. Спасибо!

Источник:
http://office-guru.ru/excel/dinamicheskaja-gistogramma-ili-grafik-raspredelenija-chastot-v-excel-470.html

Металловедение

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

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

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

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

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

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

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

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

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

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

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

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

style=»display:inline-block;width:468px;height:60px»
data-ad-client=»ca-pub-9341405937949877″
data-ad-slot=»7116308946″>

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

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

    Идем во вкладку «Анализ данных» и выбираем «Гистограмма».


  • Выбираем входной интервал.
  • Здесь же предлагается задать интервал карманов, т.е. те диапазоны, в пределах которых будут лежать наши значения. Чем больше значений в интервале — тем выше столбик гистограммы. Если мы оставим поле «Интервалы карманов» пустым, то программа вычислит границы интервалов за нас.
  • Если хотим сразу же вывести график,то ставим галочку напротив «Вывод графика».

  • Нажимаем «ОК».
  • Вот, вроде бы, и все: гистограмма готова. Теперь нужно сделать так, чтобы по вертикальной оси отображалась не абсолютная частота, а относительная.

  • Под появившейся таблицей со столбцами «Карман» и «Частота» под столбцом «Частота» введем формулу «=СУММ» и сложим все абсолютные частоты.
  • К появившейся таблице со столбцами «Карман» и «Частота» добавим еще один столбец и назовем его «Относительная частота».
  • Во всех ячейках нового столбца введем формулу, которая будет рассчитывать относительную частоту: 100 умножить на абсолютную частоту (ячейка из столбца «частота») и разделить на сумму, которую мы вычислил в п. 7.
  • Читайте также  Сложение, вычитание, умножение и деление в Excel

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

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

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

    1+3,322lgN, где N — количество всех значений величины. Например для N=100, n=7,6. Естественно, округляем до 8.

  • Для нахождения максимального и минимального значений воспользуемся соответствующими функциями: =МАКС(наш диапазон значений) и =МИН(наш диапазон значений).
  • Найдем разность этих значений и разделим его на количество интервалов, которое нам захочется. Пусть будет 10. Так мы вычислили ширину нашего «кармана».

    Теперь в каждой ячейке шаг за шагом прибавляем полученное значение ширины кармана: сначала к минимальному значению нашего массива (п. 3), затем в следующей ячейке ниже — к полученной сумме и т.д. Так постепенно доходим до максимального значения. Вот мы и построили интервалы карманов в виде столбца значений. Интервалом считается следующий диапазон : (i-1; i] или i >Скачать бесплатно видеокурc по Excel

    Источник:
    http://metallovedeniye.ru/analiz-dannyx-v-excel/postroenie-gistogramm-raspredeleniya-v-excel.html

    Как сделать частоту в excel?

    1. Построение вариационного ряда

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

    2. Построение группировочного статистического ряда

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

    Таблица №1

    Для вычисления минимального и максимального элемента воспользуйтесь встроенными в Excel статистическими формулами (главное меню – вставка – функция…) МИН и МАКС.

    Таблица №2

    начало промежутка

    конец промежутка

    Середина промежутка

    Абсолютная частота

    Относи-тельная частота

    Накопленная частота

    1

    2

    Для вычисления абсолютной частоты нужна статистическая функция ЧАСТОТА. При её использовании нужно выполнить следующие действия:

    а) выделить весь диапазон ячеек, в которых будет располагаться результат подсчёта частот (т.е. это ячейки под заголовком Абсолютная частота в количестве равном числу промежутков)

    b) не снимая выделения, поставить курсор в строку формул и нажать на кнопку вставка функции (чуть левее курсора) или Главное меню – вставка – формула.

    с) выбрать функцию ЧАСТОТА

    d) ввести Массив_данных – диапазон, содержащий элементы выборки (в файле 2.xls это ячейки) B2:B101

    e) ввести Массив_интервалов – диапазон ячеек под заголовком Начало промежутка начиная со строчки, соответствующей промежутку под номером 2 до строчки, соответствующей последнему промежутку.

    f) нажмите на кнопку ОК и после закрытия окна для ввода аргументов функции ЧАСТОТА поставьте курсор обратно в строку формул.

    g) Нажмите на три кнопки Ctrl+Shift+Enter (сначала на первые две, а потом, не отпуская их, нажмите на Enter).

    Примечание. Формулу вычисления абсолютной частоты необходимо ввести как формулу массива. Нажатие комбинации клавиш CTRL+SHIFT+ENTER позволяет определить формулу как формулу массива. Если формула не будет введена как формула массива, единственное значение будет равно 1.

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

    3. Построение гистограммы группировочного статистического ряда

    В качестве элементов группировочного ряда надо взять середины промежутков и приведённые частоты.

    Для построения гистограммы выполните следующие действия:

    1. Главное меню: Вставка – Диаграмма.
    2. Тип: точечная.
    3. Диапазон данных: выделите ячейки содержащие значения абсолютных частот.
    4. Ряд: Значения по X: укажите диапазон ячеек содержащий значения середины промежутков.
    5. Готово.

    Источник:
    http://flash-library.narod.ru/IT-MathSredstva/Lab-rab/lab3-metu.html