Elettracompany.com

Компьютерный справочник
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Медиана в excel это

Медиана в EXCEL

Для вычисления медианы в MS EXCEL существует специальная функция МЕДИАНА() . В этой статье дадим определение медианы и научимся вычислять ее для выборки и для заданного закона распределения случайной величины.

Начнем с медианы для выборок (т.е. для фиксированного набора значений).

Медиана выборки

Медиана (median) – это число, которое является серединой множества чисел: половина чисел множества больше, чем медиана , а половина чисел меньше, чем медиана .

Для вычисления медианы необходимо сначала отсортировать множество чисел (значения в выборке ). Например, медианой для выборки (2; 3; 3; 4 ; 5; 7; 10) будет 4. Т.к. всего в выборке 7 значений, три из них меньше, чем 4 (т.е. 2; 3; 3), а три значения больше (т.е. 5; 7; 10).

Если множество содержит четное количество чисел, то вычисляется среднее для двух чисел, находящихся в середине множества. Например, медианой для выборки (2; 3; 3 ; 6 ; 7; 10) будет 4,5, т.к. (3+6)/2=4,5.

Для определения медианы в MS EXCEL существует одноименная функция МЕДИАНА() , английский вариант MEDIAN().

Медиана не обязательно совпадает со средним значением (mean, average) в выборке . Совпадение имеет место только в том случае, если значения в выборке распределены симметрично относительно среднего . Например, для выборки (1; 2; 3 ; 4 ; 5; 6) медиана и среднее равны 3,5.

Чтобы в этом убедиться — построим гистограмму для симметричной выборки, состоящую из 36 значений, и вычислим среднее и медиану (см. файл примера лист Медиана-выборка ).

В чем же ценность медианы ? Почему ее используют зачастую наравне со средним значением ?

Оба параметра используются для определения «центральной тенденции» выборки . Для выборки с несимметричным распределением, медиана будет отличаться от среднего . Например, для (1; 2; 3 ; 4 ; 5; 600) медиана равна 3,5, а вот среднее равно 103,5 (смещено в сторону б о льшего значения).

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

Очевидно, что средняя зарплата (71 тыс. руб.) не отражает тот факт, что 86% сотрудников получает не более 30 тыс. руб. (т.е. 86% сотрудников получает зарплату в более, чем в 2 раза меньше средней!). В то же время медиана (15 тыс. руб.) показывает, что как минимум у половины сотрудников зарплата меньше или равна 15 тыс. руб.

Примечание : Так как медиана является 50-й процентилью и 2-й квартилью , ее также можно вычислить с помощью формул =ПРОЦЕНТИЛЬ.ВКЛ( Выборка;0,5 ) и =КВАРТИЛЬ.ВКЛ( Выборка;2 ) , где Выборка – это ссылка на диапазон, содержащий значения выборки.

Если выборка содержит нечетное количество чисел, то для вычисления медианы можно также воспользоваться формулой: НАИБОЛЬШИЙ(Выборка;СЧЁТ(Выборка)/2) .

Медиана непрерывного распределения

Если Функция распределения F (х) случайной величины х непрерывна, то медиана является решением уравнения F(х) =0,5.

Примечание : подробнее о Функции распределения см. статью Функция распределения и плотность вероятности в MS EXCEL .

Если известна Функция распределения F(х) или функция плотности вероятности p (х) , то медиану можно найти из уравнения:

Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана вычисляется по формуле =EXP(μ). При μ=0, медиана равна 1.

Обратите внимание на точку Функции распределения , для которой F (х)=0,5 (см. картинку выше) . Абсцисса этой точкиравна1. Это и есть значение медианы, что естественно совпадает с ранее вычисленным значением по формуле em.

В MS EXCEL медиану для логнормального распределения LnN(0;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,5;0;1) .

Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице.

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

Примечание : В статье о распределениях MS EXCEL приведены ссылки на распределения для которых в MS EXCEL существуют специальные функции ( нормальное распределение , гамма-распределение , Экспоненциальное и др.). Используя эти функции можно вычислить медиану соответствующего распределения.

Медиана в статистике

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

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

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

Читать еще:  Jquery table to excel

Допустим, мы имеем дело с техническими замерами отклонений от ожидаемой величины чего-нибудь (содержания элементов, расстояния, уровня, массы и т.д. и т.п.). Если все ОК, то отклонения, скорее всего, будут распределены по закону, близкому к нормальному, примерно, как на рисунке выше. Но если в процессе присутствует важный и неконтролируемый фактор, то могут появиться аномальные значения, которые в значительной мере повлияют на среднюю арифметическую, но при этом почти не затронут медиану.

Медиана выборки – это альтернатива средней арифметической, т.к. она устойчива к аномальным отклонениям (выбросам).

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

Формула медианы

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

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

Me – номер значения, соответствующего медиане,

N – количество значений в совокупности данных.

Тогда медиана обозначается, как

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

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

Для начала (после ранжирования данных) находят медианный интервал. Это такой интервал, через который проходит искомое медианное значение. Определяется с помощью накопленной доли ранжированных интервалов. Где накопленная доля впервые перевалила через 50% всех значений, там и медианный интервал.

Не знаю, кто придумал формулу медианы, но исходили явно из того предположения, что распределение данных внутри медианного интервала равномерное (т.е. 30% ширины интервала – это 30% значений, 80% ширины – 80% значений и т.д.). Отсюда, зная количество значений от начала медианного интервала до 50% всех значений совокупности (разница между половиной количества всех значений и накопленной частотой предмедианного интервала), можно найти, какую долю они занимают во всем медианном интервале. Вот эта доля аккурат переносится на ширину медианного интервала, указывая на конкретное значение, именуемое впоследствии медианой.

Обратимся к наглядной схеме.

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

где xMe — нижняя граница медианного интервала;

iMe — ширина медианного интервала;

∑f/2 — количество всех значений, деленное на 2 (два);

S(Me-1)— суммарное количество наблюдений, которое было накоплено до начала медианного интервала, т.е. накопленная частота предмедианного интервала;

fMe — число наблюдений в медианном интервале.

Как нетрудно заметить, формула медианы состоит из двух слагаемых: 1 – значение начала медианного интервала и 2 – та самая часть, которая пропорциональна недостающей накопленной доли до 50%.

Для примера рассчитаем медиану по следующим данным.

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

По последней колонке «Накопленная доля» определяем медианный интервал – 300-400 руб (накопленная доля впервые более 50%). Ширина интервала – 100 руб. Теперь остается подставить данные в приведенную выше формулу и рассчитать медиану.

То есть у одной половины товаров цена ниже, чем 350 руб., у другой половины – выше. Все просто. Средняя арифметическая, рассчитанная по этим же данным, равна 355 руб. Отличие не значительное, но оно есть.

Расчет медианы в Excel

Медиану для числовых данных легко найти, используя функцию Excel, которая так и называется — МЕДИАНА. Другое дело интервальные данные. Соответствующей функции в Excel нет. Поэтому нужно задействовать приведенную выше формулу. Что поделаешь? Но это не очень трагично, так как расчет медианы по интервальным данным – редкий случай. Можно и на калькуляторе разок посчитать.

Напоследок предлагаю задачку. Имеется набор данных. 15, 5, 20, 5, 10. Каково среднее значение? Четыре варианта:

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

Ниже видеоролик о том, как рассчитать медиану в Excel.

Анализ данных исследования в Excel: медиана значения столбца x times

Мне нужно проанализировать данные эмпирических исследований.

Так что для вопроса Q1, 7 человек полностью согласны, 4 согласны и т.д.

Я должен вычислить медиану для всех вопросов. Результат должен выглядеть так:

Читать еще:  Изменить заголовки столбцов в excel

На вопрос Q1 7 человек ответили «полностью согласен», 3-с «agree», 2-с «Neither» и 5-с «Disagree». Я хочу вычислить медиану ответов на вопрос Q1: «Fully agree» эквивалентно числу 1, «Agee»-2, ни к 3, ни к 4. Таким образом, все ответы на Q1 были бы: 1,1,1,1,1,1,1,2,2,2,3,3,3,4,4,4,4,4. Медиана составляет: 2

Для Q2 это: 1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2. Медиана равна 1

Обычно я не работаю с Excel, поэтому все функции для меня новы. Теперь я пробовал различные решения в течение нескольких часов: например, VLOOKUP (копировать значение N раз в Excel )

К сожалению, пока мне это не удалось; возможно, есть очень простое решение для вычисления медианы :). Надеюсь, что это решение не основано на VPA, так как у меня здесь еще меньше опыта. Большое спасибо за вашу помощь

4 Ответов

Вот забавный способ сделать это с помощью довольно простой формулы:

Смотрите столбец пояснений, который показывает, как я строю строку, состоящую из всех значений с REPT(1,B3)&REPT(2,C3)&REPT(3,D3)&REPT(4,E3) . Все, что мне нужно сделать, это извлечь среднюю Валлу, которая является тем, что MID(Explanation,Number of entries / 2, 1)

Если у вас есть четное число ответов, и вы хотите разделить разницу, скажем, средняя точка должна быть между значениями 2 и 3, Вы можете использовать эту введенную в массив формулу: =AVERAGE(VALUE(MID(REPT(1,B3)&REPT(2,C3)&REPT(3,D3)&REPT(4,E3),SUM(B3:E3)/2+ISEVEN(SUM(B3:E3))*<0,1>,1)))

Я решил повторить ваши однозначные оценки в строку и вырвать медиану с MID и некоторыми математиками.

Я использовал пользовательское форматирование чисел, чтобы оценки в B1:E1 оставались реальными числами, но вы могли бы жестко кодировать числа в функции REPT, если предпочитаете истинные текстовые метки.

Это то, чего ты хочешь?

EDIT :
ИБП. это «mean»

лучшее решение для медианы, которое я смог создать, было:

но это будет засчитываться как 1 «0» 🙁

но это будет считать каждый 0 как 0 🙁

OK. это должно сработать:

Похожие вопросы:

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

Как я могу использовать AWK для вычисления медианы столбца числовых данных? Я могу придумать простой алгоритм, но не могу его запрограммировать: То, что у меня есть до сих пор, это: sort | awk.

У меня есть вкладка данных Excel, которая имеет следующие столбцы: Ответ: Название Округа B: Название Отеля C: количество номеров в гостинице Д: Год Постройки Отеля На вкладке анализ у меня есть.

Я пытаюсь написать правила для prolog, которые определяют медиану списка с помощью метода разбиения. partition([], V, [], []). partition([X | L], V, [X | A], B) :- (V > X), !, partition(L, V, A.

У меня есть один большой столбец, мне нужна медиана каждого пятого значения в списке. У меня есть excel и SPSS. Как я могу сделать это, не делая это вручную? 600 значений. При расчете я должен иметь.

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

У меня есть два столбца данных, значения x и y, и мне нужно сохранить файл как файл excel, который будет открыт в excel. Есть ли какие-то модули, которые могут помочь мне в этом? Формат должен быть.

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

Мне нужно проанализировать данные эмпирических исследований: Даны следующие данные: Объяснение: 5 человек ответили на вопрос 1 с полностью согласны, что эквивалентно баллу 7. Ноль человек ответили.

Предположим, у меня есть набор данных в Excel, как 1, 10 2, 15 3, 20 4, 25 и скажем, слева начинается колонны на А1, правый столбец начинается на уровне В1. И скажем, я хочу получить медиану чисел в.

Условная медиана в MS Excel

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

Я использую MS Excel 2007. Я знаю утверждение AVERAGEIF (), но нет эквивалента для медианы. Главный трюк заключается в том, что есть строки без данных — например, 4-й «a» выше. В этом случае я не хочу, чтобы эта строка вообще учитывалась в расчетах.

Googling предложил следующее, Но Excel не примет формат формулы (возможно, потому, что это 2007?)

Excel дает ошибку, говоря, что что-то не так с моей формулой(что-то связано с * в условии) я также пробовал следующее, Но он считает пустые ячейки как 0 в расчетах:

Я знаю, что эти формулы возвращают Excel «массивы», что означает, что нужно ввести «Ctrl-shift-enter», чтобы заставить его работать правильно.

как я могу сделать условную оценку и не считать пустые ячейки?

4 ответов

вложенные операторы if.

не так много, чтобы объяснить — он проверяет, является ли A x. Если это так, он проверяет, является ли B непустым. Все, что соответствует обоим условиям, вычисляется как часть медианы.

учитывая следующий набор данных:

приведенная выше формула возвращает 3.5, что я считаю, что вы хотели.

Читать еще:  Как выбрать диапазон в excel

используйте формулу Googled, но вместо нажатия Enter после ввода в строку формул нажмите Ctrl + Shift + Enter одновременно (вместо Enter ). Это помещает скобки вокруг формулы и будет рассматривать ее как массив.

будьте осторожны, если вы редактируете его, вы не можете нажать Enter снова или формула не будет действовать. Если редактирование, вы должны сделать то же самое, когда сделано ( Ctrl + Shift + Enter ).

существует другой способ, который не включает формулу массива, которая требует операции CtrlShiftEnter. Он использует функцию Aggregate (), предлагаемую в Excel 2010, 2011 и за его пределами. Метод также работает для min, max и различных процентилей. Aggregate () позволяет игнорировать ошибки, поэтому трюк состоит в том, чтобы сделать все значения, которые не являются обязательными, причиной ошибок. Самый простой способ-выполнить поставленную выше задачу:

первый и последний параметры устанавливают сцену, чтобы сделать процентиль 50%, который является медианой, второй говорит игнорировать все ошибки (включая DIV#0), а третий говорит, выберите данные столбца B и разделите его на число, которое является одним для всех непустых значений, которые имеют x в столбце A, и ноль в противном случае. Нули создают исключение деления на ноль и будут игнорироваться, потому что A/1=a и A / 0=Div#0

метод работает для квартилей (с соответствующее значение p), все остальные процентили, конечно, и для max и min, используя большую или малую функцию с соответствующими аргументами.

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

возможно, чтобы обобщить его немного больше, вместо этого.

. вы можете использовать следующее:

обратите внимание, что фигурные скобки относятся к формуле массива; вы не должны помещать скобки в формулу, но нажмите CTRL+SHIFT+ENTER (или CMD+SHIFT+ENTER на macOS) при вводе формулы

тогда вы можете легко получить первый и третий квартиль, изменив последнее число из 2 в 1 или 3 соответственно. КВАРТИЛЬ.EXC-это то, что использует большинство коммерческих статистических программ (например, Minitab). «Регулярная» функция-квартиль.INC, или для более старых версий Excel, просто квартиль.

How to dou

Excel Среднее значение группы чисел

Table of Contents:

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

AVEDEV: среднее абсолютное отклонение

Функция AVEDEV обеспечивает меру дисперсии для набора значений. Для этого функция просматривает набор значений и вычисляет среднее абсолютное отклонение от среднего значения. Функция использует синтаксис

где number1 , [ number2 ] — ссылка на рабочий лист в диапазоне, в котором хранятся значения ,

Как и во многих других простых статистических функциях, вы можете включить несколько аргументов как часть аргумента диапазона в функции AVEDEV. Например, формулы = AVEDEV (B1, B2: B5, B6: B7, B8, B9) и = AVEDEV (B1: B9) эквивалентны.

Предположим, что у вас есть три значения — 100, 200 и 300 — в диапазоне рабочих листов, который вы передаете функции AVEDEV. Среднее значение этих трех значений составляет 200, рассчитанное как (100 + 200 + 300) / 3. Среднее отклонение от среднего составляет 66. 6667, рассчитанное как:

Примечание: Функция AVEDEV вычисляет среднее значение абсолютной величины отклонение. По этой причине функция вычисляет абсолютные различия или отклонения от среднего значения.

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

СРЕДНИЙ: средний

Функция AVERAGE вычисляет среднее арифметическое для набора значений. Функция использует синтаксис

где number1 , [ number2 ] — ссылка рабочего листа на диапазон, в котором хранятся значения.

Если ваш аргумент содержит три значения -, 100, 200 и 300 — функция возвращает значение 200, потому что (100 + 200 + 300) / 3 равно 200.

AVERAGEA: Альтернативное среднее

Функция AVERAGEA, подобно функции AVERAGE, вычисляет среднее арифметическое для набора значений. Однако разница с функцией AVERAGEA заключается в том, что AVERAGEA включает в себя ячейки с текстом и логическое значение FALSE в своих вычислениях как 0. Функция AVERAGEA включает логическое значение TRUE в своих вычислениях как 1.Функция использует синтаксис

где номер1 , [ номер2 ] является ссылкой на рабочий лист на диапазон, в котором хранятся значения — и, возможно, текстовые и логические значения.

Если ваш аргумент содержит три значения — 100, 200 и 300 — и три текстовые метки в диапазоне рабочих листов, которые вы передаете функции AVERAGEA, функция возвращает значение 100, потому что (100 + 200 + 300 + 0 + 0 +0) / 6 равно 100.

Как и в случае с функцией AVERAGE, вы можете предоставить до 255 аргументов функции AVERAGEA.

TRIMMEAN: Обрезка до среднего

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

где массив — это диапазон, содержащий значения и процентов

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector
×
×