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

Глава 16. Функция массива ЧАСТОТА

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Знакомство с функциями массива началось в главе 9. Мы узнали о функциях: ТРАНСП, МОДА.НСК и ТЕНДЕНЦИЯ. Настоящая заметка знакомит с четвертой функцией массива – ЧАСТОТА. Эта функция очень простая, но весьма мощная и универсальная. Она находит массу применений. Основная задача функции ЧАСТОТА – подсчитать, сколько чисел попадают в диапазон (рис. 16.1).

Рис. 16.1. Функция ЧАСТОТА подсчитывает, сколько результатов попали в тот или иной диапазон; диапазоны в D5:D10 не являются частью формулы; они показаны для иллюстрации

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

Функция ЧАСТОТА в диапазоне Е5:Е10 введена с помощью Ctrl+Shift+Enter. Функция возвращает вертикальный массив, показывающий число вхождений результатов гонки в каждую категорию (диапазон). Например, в диапазон от 45 до 50 с попало 5 результатов. Функция содержит два аргумента: массив_данных и массив_интервалов (массив_карманов). Обратите внимание, что функция возвращает значений на одно больше чем массив_интервалов. Экстра-значение нужно на случай, если вы не предоставите «правильное» максимальное значение в массиве интервалов, и найдутся значения, выходящие за верхнюю границу максимального диапазона. Обратите внимание:

  • Первый диапазон включает все значения, которые меньше или равны первой границе.
  • Далее диапазоны формируются так, что нижняя граница не входит в диапазон, а верхняя – входит.
  • Последний диапазон включает все значения, которые больше, чем последняя граница.
  • Функция возвращает вертикальный массив. Если вам нужен горизонтальный массив, используйте функцию ТРАНСП (рис. 16.2).
  • Если аргумент массив_карманов содержит N значений, диапазон введения функции ЧАСТОТА должен содержать N+1 ячеек.
  • Функция ЧАСТОТА игнорирует пустые ячейки и текст.
  • Если массив_интервалов содержит дубли, во все диапазоны-дубли, кроме первого, функция вернет 0.
  • После того, как функция введена с помощью Ctrl+Shift+Enter, результирующий массив становится единым блоком и отдельные ячейки нельзя ни удалить, ни отредактировать. Но вы можете удалить все значения.
  • Функция ЧАСТОТА может использоваться внутри больших формул массивов, возвращая вертикальный массив.

Рис. 16.2. Используйте функцию массива ТРАНСП, если нужно получить горизонтальный массив

Сравнение функций СЧЁТЕСЛИ, СЧЁТЕСЛИМН и ЧАСТОТА

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

Рис. 16.3. Функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН сложнее, чем ЧАСТОТА; Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Однако, если диапазоны включает нижнюю границу, но не верхнюю (рис. 16.4) функция ЧАСТОТА не подойдет. Кроме того, вы можете предусмотреть введение нижней и верхней границ для всех диапазонов, так что формулы примут одинаковый вид. В этом примере, вы отметаете функцию ЧАСТОТА, и скорее всего, предпочтете СЧЁТЕСЛИМН.

Рис. 16.4. СЧЁТЕСЛИ и СЧЁТЕСЛИМН более гибки по сравнению с функцией ЧАСТОТА при задании различных условий по вхождению границ в диапазоны

В следующей главе вы используете полученные знания о функции ЧАСТОТА для построения формул подсчета уникальных элементов в списке.

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

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

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

Пример 1. Студенты одной из групп в университете сдали экзамен по физике. При оценке качества сдачи экзамена используется 100-бальная система. Для определения окончательной оценки по 5-бальной системе используют следующие критерии:

  1. От 0 до 50 баллов – экзамен не сдан.
  2. От 51 до 65 баллов – оценка 3.
  3. От 66 до 85 баллов – оценка 4.
  4. Свыше 86 баллов – оценка 5.

Для статистики необходимо определить, сколько студентов получили 5, 4, 3 баллов и количество тех, кому не удалось сдать экзамен.

Внесем данные в таблицу:

Для решения выделим области из 4 ячеек и введем следующую функцию:

  • B3:B20 – массив данных об оценках студентов;
  • D3:D5 – массив критериев нахождения частоты вхождений в массиве данных об оценках.

Выделяем диапазон F3:F6 жмем сначала клавишу F2, а потом комбинацию клавиш Ctrl+Shift+Enter, чтобы функция ЧАСТОТА была выполнена в массиве. Подтверждением того что все сделано правильно будут служить фигурные скобки <> в строке формул по краям. Это значит, что формула выполняется в массиве. В результате получим:

Читать еще:  Как сделать структуру сайта в excel?

То есть, 6 студентов не сдали экзамен, оценки 3, 4 и 5 получили 3, 4 и 5 студентов соответственно.

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

Пример 2. Известно то, что если существует только два возможных варианта развития событий, вероятности первого и второго равны 0,5 соответственно. Например, вероятности выпадения «орла» или «решки» у подброшенной монетки равны ½ и ½ (если пренебречь возможностью падения монетки на ребро). Аналогичное расчетное распределение вероятностей характерно для следующей функции СЛУЧМЕЖДУ(1;2), которая возвращает случайное число в интервале от 1 до 2. Было проведено 20 вычислений с использованием данной функции. Определить фактические вероятности появления чисел 1 и 2 соответственно на основании полученных результатов.

Заполним исходную таблицу случайными значениями от 1-го до 2-ух:

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

Для определения количества сгенерированных 1 и 2 используем функцию:

  • A2:A21 – массив сгенерированных функцией =СЛУЧМЕЖДУ(1;2) значений;
  • 1 – критерий поиска (функция ЧАСТОТА ищет значения от 0 до 1 включительно и значения >1).

В результате получим:

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

Для подсчета количества событий используем функцию =СЧЁТ($A$2:$A$21). Или можно просто разделить на значение 20. Если заранее не известно количество событий и размер диапазона со случайными значениями, тогда можно использовать в аргументах функции СЧЁТ ссылку на целый столбец: =СЧЁТ(A:A). Таким образом будет автоматически подсчитывается количество чисел в столбце A.

Вероятности выпадения «1» и «2» — 0,45 и 0,55 соответственно. Не забудьте присвоить ячейкам E2:E3 процентный формат для отображения их значений в процентах: 45% и 55%.

Теперь воспользуемся более сложной формулой для вычисления максимальной частоты повторов:

1)*СТРОКА($A$2:$A$21)))-1′ class=’formula’>

Формулы в ячейках F2 и F3 отличаются только одним лишь числом после оператора сравнения «не равно»: <>1 и <>2.

Интересный факт! С помощью данной формулы можно легко проверить почему не работает стратегия удвоения ставок в рулетке казино. Данную стратегию управления ставками в азартных играх называют еще Мартингейл. Дело в том, что количество случайных повторов подряд может достигать 18-ти раз и более, то есть восемнадцать раз подряд красные или черные. Например, если ставку в 2 доллара 18 раз удваивать – это уже более пол миллиона долларов «просадки». Это уже провал по любым техникам планирования рисков. Так же следует учитывать, что кроме «черные» и «красные» иногда выпадает еще и «зеро», что окончательно уничтожает все шансы. Так же интересно, что сумма всех чисел в рулетке от 0 до 36 равна 666.

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

Пример 3. Определить количество уникальных вхождений в массив числовых данных, то есть не повторяющихся значений.

Определим искомую величину с помощью формулы:

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

  1. Если искомый элемент содержится в диапазоне значений, вместо фактического количества вхождений будет возвращено 1;
  2. Если искомого элемента нет – будет возвращен 0 (нуль).

Полученное значение (количество единиц) суммируется.

В результате получим:

То есть, в указанном массиве содержится 8 уникальных значений.

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

Данная функция имеет следующую синтаксическую запись:

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

  • массив_данных – данные в форме массива либо ссылка на диапазон значений, для которых необходимо определить частоты.
  • массив_интервалов — данные в формате массива либо ссылка не множество значений, в которые группируются значения первого аргумента данной функции.
  1. Если в качестве аргумента массив_интервалов был передан пустой массив или ссылка на диапазон пустых значений, результатом выполнения функции ЧАСТОТА будет являться число элементов, входящих диапазон данных, которые были переданы в качестве первого аргумента.
  2. При использовании функции ЧАСТОТА в качестве обычной функции Excel будет возвращено единственное значение, соответствующее первому вхождению в массив_интервалов (то есть, первому критерию поиска частоты вхождения).
  3. Массив возвращаемых данной функцией элементов содержит на один элемент больше, чем количество элементов, содержащихся в массив_интервалов. Это происходит потому, что функция ЧАСТОТА вычисляет также количество вхождений величин, значения которых превышают верхнюю границу интервалов. Например, в наборе данных 2,7, 10, 13, 18, 4, 33, 26 необходимо найти количество вхождений величин из диапазонов от 1 до 10, от 11 до 20, от 21 до 30 и более 30. Массив интервалов должен содержать только их граничные значения, то есть 10, 20 и 30. Функция может быть записана в следующем виде: =ЧАСТОТА(<2;7;10;13;18;4;33;26>;<10;20;30>), а результатом ее выполнения будет столбец из четырех ячеек, которые содержат следующие значения: 4,2, 1, 1. Последнее значение соответствует количеству вхождений чисел > 30 в массив_данных. Такое число действительно является единственным – это 33.
  4. Если в состав массив_данных входят ячейки, содержащие пустые значения или текст, они будут пропущены функцией ЧАСТОТА в процессе вычислений.

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

  • Данная функция должна быть использована как формула массива, поскольку возвращаемые ей данные имеют форму массива. Для выполнения обычных формул после их ввода необходимо нажать кнопку Enter. В данном случае требуется использовать комбинацию клавиш Ctrl+Shift+Enter.
  • Построение выборочной функции распределения в Excel

    Выборочный метод и выборочная функция распределения

    На практике часто бывают ситуации, когда полное исследование каждого объекта из интересующей совокупности по различным причинам невозможно. В этих случаях из всей совокупности объектов случайным образом отбирают ограниченное число объектов и подвергают их исследованию. Вся совокупность объектов, из которых производится выборка называется генеральной совокупностью. Совокупность случайно отобранных из генеральной совокупности объектов называется выборочной совокупностью. Число объектов в совокупности называется ее объемом. На практике сведения о законе распределения случайной величины получают независимыми многократными повторениями опыта. На основе полученной информации из полученной выборки можно вычислить приблизительные значения для функции распределения и другие характеристики случайной величины. Выборочной или эмпирической функцией распределения случайной величины называют функцию равную частоте появления событий F (x)= nx/n.
    Для построения выборочной функции распределения весь диапазон изменения случайной величины Х разбивают на ряд интервалов одинаковой ширины (от 5 до 15) и затем вычисляют количество значений случайной величины Х, попав-ших в каждый интервал.

    Построение выборочной функции распределения

    В табличном процессоре для построения выборочной функции распределения используется специальная функция ЧАСТОТА и инструмент пакета анализа Гистограмма . Функция ЧАСТОТА вычисляет частоты появления случайных величин в интервалах значений и выводит их как массив чисел. Функция имеет параметры:
    ЧАСТОТА ( массив_данных; массив_интервалов ),
    где:
    массив_данных – массив или ссылка на диапазон данных, для которых вычисляются частоты;
    массив_интервалов – массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных . Количество элементов в возвращаемом массиве на единицу больше, чем в задано в параметре массив_интервалов. Дополнительный элемент содержит количество значений больших, чем максимальное значение в интервалах.
    Инструмент Гистограмма служит для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. Выходным результатом является таблица и гистограмма. Чтобы включить инструмент Гистограмма следует на ленте Данные в группе Анализ выбрать Анализ данных (Data Analysis) .
    В раскрывшемся диалоговом окне Анализ данных из списка следует выбрать Гистограмма (Histogram) (рис. 1) – откроется диалоговое окно Гистограмма . Вид диалогового окна Гистограмма приведен на рис. 2.

    Диалоговое окно имеет следующие параметры:
    Входной интервал (Input Range) – поле, предназначенное указания адресной ссылки на диапазон, содержащий исследуемые данные;
    Интервал карманов ( Bin Range )– поле, в котором может быть указана ссылка на диапазон ячеек, содержащий выбранные интервалы, в которые группируются значения аргумента Входной интервал ;
    -поле Выходной диапазон ( Output Range ) предназначено для ввода адресной ссылки на верхнюю левую ячейку выходного диапазона;
    -опция Интегральный процент (Comulative Percentage) устанавливает режим генерации интегральных процентных соотношений и включает в гистограмму график интегральных процентов;
    — опция Вывод графика (Chart Output) устанавливает режим автоматического вывода графика на рабочий лист, содержащий входной диапазон.
    Технологию построения эмпирического распределения рассмотрим на примере.
    Пример . Построить эмпирическое распределение рейтинга студентов по результатам экзаменов, оцененных в баллах для следующей произвольной выборки: 48, 51, 64, 62, 55, 71, 74, 79, 80, 86, 91, 99, 83, 50. Задачу решить двумя способами: с применением функции ЧАСТОТА с применением инструмента Гистограмма пакета анализа.

    Решение с применением функции ЧАСТОТА
    1. В ячейку A2 рабочего листа введем текст “Наблюдения”, а в диапазон A3:A16 – числа из заданной выборки (см рис. 3).

    2. В ячейке B2 запишем текст “ Шкала баллов ”, а в ячейки диапазона B3:B6 – баллы, соответствующие шкале для вывода пятибалльной оценки – 50, 70, 85, 100. Это означает, что баллы диапазона 1 – 50 эквивалентны оценке “неудовлетворительно”, баллы, находящиеся в диапазоне 51 – 70 – оценке “удовлетворительно” и т.д.
    3. В ячейки C2, D2 и E2 введем тексты “ Абсолютные частоты ”, “ Относительные частоты ” и “ Накопленные частоты ” соответственно. Абсолютные частоты – это частота попадания случайной величины из выборки в соответствующий интервал. Относительная частота представляет собой частное от деления значения относительной частоты на количество элементов выборки. Накопленные частоты – это сумма относительных частот.
    4. Выделим диапазон C3:C7 и на ленте Формулы выберем Вставить функцию . В открывшемся окне диалога Мастер функций выберите категорию Статистические , а в списке функций – функцию ЧАСТОТА (рис. 4).

    Раскроется диалоговое окно функции ЧАСТОТА .
    5. Установим параметры функции:
    массив_данных – установим ссылку на диапазон, содержащий выборку случайных величин (A3:A16);
    массив_интервалов – установим ссылку на диапазон, содержащий шкалу для вывода оценки (B3:B6).
    6. Так как функция ЧАСТОТА возвращает результат в виде массива, нажмем комбинацию клавиш Ctrl + Shift + Enter. В ячейки диапазона C3:C7 будет выведен результат – абсолютные частоты попадания случайных величин в интервалы, заданные в ячейках диапазона B3:B6 (рис. 3).
    Таким образом, в результате проведенного исследования получены статистические оценки частот по случайной выборке: неудовлетворительно – 2, удовлетворительно – 4, хорошо – 5, отлично – 3.

    Решение с применением инструмента Гистограмма
    1. В ячейку A2 рабочего листа введем текст “Наблюдения”, а в диапазон A3:A16 – числа из заданной выборки (см. рис. 5).

    2. В ячейке B2 запишите текст “ Шкала баллов ”, а в ячейки диапазона B3:B6 – баллы, соответствующие шкале для вывода пятибалльной оценки.
    3. На ленте Данные в группе Анализ выберем Анализ данных – откроется диалоговое окно Анализ данных.
    4. В окне диалога Анализ данных выберем из списка Гистограмма – откроется диалоговое окно Гистограмма .
    5. Введите параметры в соответствующие поля диалогового окна Гистограмма :
    Входной диапазон – укажем диапазон ячеек, в котором размещены результаты выборки (A3:A16);
    Интервал карманов –укажем ссылку на диапазон ячеек, содержащий выбранные интервалы – шкалу для вывода оценки (B3:B6);
    -установим переключатель Выходной_интервал ;
    Выходной диапазон — введем адресную ссылку на верхнюю левую ячейку выходного диапазона (C2);
    -установим опцию Интегральный процент ;
    -установим опцию Вывод графика .
    6. Кликнем на кнопке ОК. В результате на рабочий лист будет выведена таблица и диаграмма .
    Как видно из полученных результатов оба рассмотренные способа дают одинаковые результаты. На основании полученных результатов выборочную функцию распределения можно записать в виде:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Гистограмма

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

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

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

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

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

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

    Ссылка на основную публикацию
    Adblock
    detector