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

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

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

Продолжаю новую рубрику блога, посвященную анализу данных с помощью всем известного 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.
  • Способ 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

    Построение гистограммы частоты признака

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

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

    В рассматриваемом варианте n = 53.

    Число интервалов группировки k в Excel вычисляется по формуле

    ,

    где, скобки означают – округление до целой части числа в меньшую сторону, следовательно. = 8.

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

    Тогда, так как , то

    .

    Строгого научного обоснования для определения числа интервалов группировки и их величины нет. Существует много эмпирических формул для определения числа k.

    Разброс значений числа k (числа интервалов группировки), который дают эти формулы, позволяет исследователю выбрать удобные для вычисления границы частичных интервалов группировки. Так в рассматриваемом варианте исходных данных 99,5, а максимальное значение 117,88. Дробные величины неудобны для восприятия.

    Тогда, пусть левая (нижняя) граница всего интервала будет равной = 98 (меньше 99,5), а величина интервала группировки ,

    следовательно, = 98+3 = 101,

    = 101+3 = 104,

    =107,

    = 110

    = 113

    = 116

    = 119

    Пусть верхняя граница последнего частичных интервалов группировки будет = 119, так как 117,88 входит в этот последний интервал.

    Получили границы интервалов группировки (карманы, как их называют в Excel) красивыми целыми числами. Занесите полученные результаты в столбец Excel, рис.7.

    Рис. 7. Массив границ (карманов) группировки A57:A64

    Теперь можно приступить к построению гистограммы.

    В главном меню Excel выбрать Данные → Анализ данных → Гистограмма → ОК.

    Далее необходимо заполнить поля ввода в диалоговом окне Гистограмма.

    Входной интервал: 53 случайных чисел (вариант, значений признака) в ячейках $B$2: $B$54;

    Интервал карманов: ввести массив границ интервалов группировки (карманов) ис 2 A57:A64;

    Выходной интервал: адрес ячейки, с которой начинается вывод результатов процедуры Гистограмма;

    Вывод графика – поставьте галочку. OK.

    Рис. 8. Диалоговое окно Гистограмма с заполненными полями.

    Если в диалоговом окне Гистограммаполе ввода Интервал карманов не заполняется, то процедура вычисляет число интервалов группировки k и границы интервалов автоматически.

    В результате выполнения процедуры Гистограмма появляется таблица, содержащая границы интервалов группировки (столбец – Карман) и частоту попадания признака выборки в k–ый интервал (столбецЧастота).

    Справа от таблицы – график гистограммы.

    Рис. 9. Фрагмент листа Excel с результатами процедуры Гистограмма

    Принято столбики гистограммы строить без зазора.

    Приведите гистограмму к виду как показано на рис. 10.

    Для этого щелкните правой кнопкой мыши на столбике диаграммы и выберите Формат ряда данных → Без зазора → Нет заливки.Выберите цвет границ, стили границ и толщину линии границ.

    Рис. 10. Гистограмма частот

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

    Рис 11. График гистограммы с модальным интервалом, интервалом предшествующим модальному и следующим за модальным интервалам.

    Для рассматриваемого варианта:

    = 107, = 110 — это границы модального интервала

    = 8 – частота интервала, предшествующего модальному интервалу;

    = 14 – частота модального интервала;

    = 11 – частота интервала, следующего за модальным интервалом.

    Среднее = 108,9134, Мода = 109 , Медиана = 109,5;

    Медиану можно найти графическим способом, построив кумуляту.

    Для построения кумуляты в таблице Карман-Частота добавьте столбец накопленных эмпирических частот . ( )

    Рис 12. Таблица Карман-Частота, полученная при построении гистограммы, с добавленным столбцом накопленных эмпирических частот.

    Далее постройте график кумуляты.

    Медиана соответствует варианте, стоящей в середине ранжированного ряда. Положение медианы определяется ее номером .

    На оси графика кумуляты отложите . Найдите соответствующее значение варианты

    Рис 13. График кумуляты с определенным графическим способом значением .

    Приблизительное равенство оценок = 108,9134, = 109 и = 109,5 позволяет предположить, что распределения признаков генеральной совокупности имеет нормальныйзакон.

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

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

    И, второе – используя критерий согласия Пирсона установить справедливость выбранной гипотезы.

    93.79.221.197 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

    Отключите adBlock!
    и обновите страницу (F5)

    очень нужно

    12 Функция Excel ЧАСТОТА

    Oynatıcı kontrollerini göster

    • katma 20 Mar 2017
    • Формула, подсчитывающая количество числовых значений, входящих в заданные интервалы. Файл с примером находится на странице statanaliz.info/osnovnye-formuly-excel/
      ► Корпоративный тренинг «Статистика в MS Excel»:
      statanaliz.info/statisticheskij-analiz-v-ms-excel-trening/
      ► Онлайн курс «Статистика в MS Excel»:
      statanaliz.info/statisticheskij-analiz-v-ms-excel/
      **************************
      *** Мой сайт об Excel и статистике ***
      statanaliz.info/
      *** Странички и аккаунты в соцсетях ***
      Facebook: statanaliz.info/
      Вконтакте: id_statanaliz_info
      Твиттер: statanaliz_info
      Telegram: t.me/statanaliz

    YORUMLAR • 38

    как задать интервал? например от 1 до 7. как он записывается в формуле

    Или сделать два столбца рядом. В первом нижняя граница (1), во втором — верхняя (5). В третьем столбце с помощью СЧЁТЕСЛИМН подсчитываете частоты, указав ограничения из соседних столбцов.

    @Світлана Світла вот так «1-5» записать не получится. Можно использовать функцию ЧАСТОТА с такой записью данных, как сказано в уроке. А рядом в столбце написать более понятные вам обозначения вроде «1-5»

    photos.app.goo.gl/H4NrKcvNU48CdaHL7
    16-29 как записать формулу ? вывести результат «в межах нормы»
    photos.app.goo.gl/5VVNJDHCjz33aAu19

    @Езепов Дмитрий Спасибо. Мне нужно записать в одной ячейке диапазон от 1 до 5. Например в А1 ввожу 1-5. Это обозначает 1,2,3,4,5 . не знаю как пишется в екселе. Нужно записать формулу. если вес птицы от 1до 5 то.

    Спасибочки. Ты просто не представляешь себе, как ты меня выручил.

    а как посчитать эмпирически и теоретически накопленные частоты?

    Чем «частота» отличается от «счетесли» ? только диапазонами? в частоте можно диапазон указать а в счетесли надо каждое возможное значение указать? например в частоте в ряде из 10 значений я могу указать диапазон например 2,5,8. а в счетесли надо задавать ряж значений 1,2,3,4,5,6,7,8,9,10. верно же?)

    Угу, верно. Частота — частоты в диапазоне + это формула массивов. СЧЁТЕСЛИ — количество значений, соответствующих заданному критерию.

    Спасибо большое, очень доступно и понятно. 🙂

    как посчитать интервалы?

    Дмитрий, добрый день, прохожу серию уроков Функции как Вы советовали, спасибо!
    По данному уроку, у меня вышла маленькая проблема.
    Я приложу ссылку на скриншот чтоб понятно было, не подскажите?
    У меня там чисел от 20(не включительно) до 25 должно быть 3, но эксель ставит 4, почему?
    rgho.st/7WYqRBHhc/image.png

    ЗДРАВСТВУЙТЕ! А если диапазон состоит из вариации двух цифр, например сума чека от 10- 30,40-60 и т.д. как считать частоту? покажите пожалуйста,очень буду благодарна

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

    Не раскрыл до конца. Что означает 15. Это от сколько до скольки? от 12 до 15? и почему? Если так то как он определил от 0 до 3, ведь 0 нет. Что означает число на 13й строке и почему получилось так? почему второй столбец меньше третьего на один? Вопросов больше чем ответов. Просто запомнить и делать так, не получится, как тогда я смогу такие данные использовать в моём случае, который отличается. Другое дело, когда ясен концепт, все становится просто

    Все доходчиво автор объяснил (для чайников), если вы невнимательно слушали или вам лень думать — автор ни при чем. На все ваши вопросы есть ответы в видео — главное слушать и вникать. 😉

    По-моему, что касается почему нужен был лишний пункт в финальном списке чисел — (23 результат у неё) — это те числа которые выше 33, функция ЧАСТОТА по всей видимости сама считает это последнее значение. Т.е вот мы посчитали количество цифр от 30 до 33, а последний подсчёт — числа которые уже свыше 33.

    на Геолога учишься.

    как сделать тоже самое , но для времени? пишет#Н/Д

    Время — это числа в специальном формет времени. Пришлите файл с примером.

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

    Лучше пришлите пример с данными.

    Наверное я непонятно излагаю. Может лучше изложить задачу? Есть ряд градаций серого цвета (0-255). Это первый столбец. Каждый из них представлен на изображении определеннным количеством пикселей (0,3, 7 и т.д.). Это второй столбец. Он отображает частоту встречаемости данной градации серого. Требуется рассчитать среднюю градацию серой шкалы, моду, и пр. стат параметры. Т.е. нужно как то преобразовать данные из 2 столбцов (значение и частота) в один вариационный ряд.

    То есть интервал заменить значением? Обычно заменяют средним. Берете верхнюю и нижнюю границу и рассчитываете среднее арифметическое для каждого интервала.

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

    Спасибо за видео. Можно ли как-то вставить объм информации, состоящий из нескольких рядов, в пустой ряд между уже имеющейся информации в таблице так, чтобы вводимая информация не уничтожила уже имеющиеся ряды, а сдвинула их вниз?
    Чтобы было понятно, что имеется в виду — у меня есть таблица с адресами домов, мне нужно под каждым домом вставить список жильцов, который я позже сгруппирую, но проблема в том, что при вставлении списка, он записывается поверх нижних адресов.

    Да, конечно, можно вставить со сдвигом вниз.
    1. копируете диапазон для вставки (Ctrl + C),
    2. выделяете верхнюю левую ячейку или целый ряд, над которым будет производиться вставка
    3. через правую кнопку мыши выбираете Вставить скопированные ячейки.
    4. появится окошко с выбором, куда сдвинуть имеющиеся данные, выбираете вниз.

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

    Спасибо за правильный комментарий. Конечно, есть надстройка «Анализ данных» с интересными инструментами анализа. Однако данный ролик именно про функцию. А вот по статистике еще будет много роликов, статей и даже целый курс.

    Читать еще:  Как сделать сворачивающиеся строки в excel?
  • Ссылка на основную публикацию
    Adblock
    detector