Как рассчитать отношение шансов в excel
Отношение шансов (OR, odds ratio) — это широко используемый статистический показатель, позволяющий сравнивать частоту воздействия факторов риска в эпидемиологических исследованиях. Отношение шансов является ретроспективным сравнением влияния данного фактора риска на две группы лиц.
Термин « шанс » пришёл из азартных игр и означает отношение числа выигрышей к числу проигрышей или, другими словами, отношение числа случаев, когда событие наступило, к числу случаев, когда оно не наступило.
Расчёт отношения шансов
Расчёт отношения шансов для набора данных несложен: необходимо построить таблицу сопряжённости так, чтобы в первой строке стояла группа испытуемых, а в первом столбце — фактор риска.
Рассмотрим первый пример
Представьте, что Вы решили провести обследование мутации в гене X, предположительно вызывающего некую болезнь. Вы проанализировали гены однородных групп заболевших и здоровых и нашли, что распределение мутаций выглядит так (табл. 1):
Шанс найти мутацию в группе заболевших = (A x (A + B))/(B x (A + B)) = A/B = 332/164 = 2.0244
Шанс найти мутацию в контрольной группе = (C x (C + D))/(D x (C + D)) = C/D = 230/262 = 0.8779
Затем следует найти OR путём деления шансов найти мутацию в группе заболевших и в контрольной группе:
OR = 2.0244/0.8779 = 2.306
Если свести все эти действия в одну формулу, то получим
OR = (A/B)/(C/D) = (А x D)/(В х С) = (332×262)/(164×230) = 2.306
. и это именно та формула, которая используется для определения OR.
Рассмотрим второй пример
Предположим, что в выборке из 100 мужчин 90 пили вино в предыдущую неделю, а в выборке из 100 женщин только 20 пили вино в тот же период (табл. 2).
Расчет OR (воздействия фактора риска) является хорошим инструментом, но поскольку он основан на выборке, то он является не более чем оценкой. Точность этой оценки отчасти зависит от размера выборки, и, в целом, чем больше выборка, тем правдоподобнее оценка (хотя следует с большой осторожностью подходить к интерпретации OR в исследованиях с огромными размерами выборки). По этой причине кроме расчёта OR обычно вычисляют и стандартное отклонение (SE) с доверительным интервалом ( p ) 95%.
Есть несколько различных способов расчёта SE при заданном p для отношения шансов. Приведём один из них:
при p = 95% ln(SE) = 1.96(1/A + 1/B + 1/C + 1/D)^0.5
Для первого примера :
при p = 95% ln(SE) = 1.96(1/332 + 1/164 + 1/230 + 1/262)^0.5 = 0.25760567, соответственно
OR ± SE = от e^0.57790875 до e^0.25760567 или от 1.7823073 до 2.9835686
Для второго примера :
при p = 95% ln(SE) = 1.96(1/90 + 1/10 + 1/20 + 1/80)^0.5 = 0.817
В этих примерах доверительный интервал составляет 95%, но если нужно воспользоваться другой шириной доверительного интервала, то следует заменить 1.96 в уравнении соответствующим стандартным для нормального распределения значением.
Интерпретация отношения шансов
Предполагаемый фактор риска является значимым (т. е. с большой вероятностью вызовет наступление события, напр. болезнь), если OR больше единицы.
Следует иметь в виду, что само по себе значение OR нечувствительно к размеру выборки (напр., если во втором примере мы используем вдесятеро меньшие значения, то тоже получим OR = 36), однако от размера выборки зависит размер стандартного отклонения (так, во втором примере при вдесятеро меньших значениях мы вместо 2.26 получим SE = 13, т. е. ошибка измерения составит 37%).
Очень часто при работе в Excel необходимо использовать вычисления вероятности появления некоторого события. Для этого используется статистическая функция ВЕРОЯТНОСТЬ.
Примеры использования функции вероятность для расчетов в Excel
Стоит отметить, что используются часто в Excel и другие статистические функции, к примеру:
Функция выполняет вычисление вероятности того, что значения с интервала находятся в заданных пределах. В случае, если верхний предел не будет задан, то будет возвращена вероятность того, что значения аргумента x_интервал будет равно значению аргумента под названием нижний_предел.
Вычисление процента вероятности события в Excel
Пример 1. Дана таблица диапазона числовых значений, а также вероятностей, которые им соответствуют:
Необходимо при использовании данной статистической функции вычислить вероятность события, что значение с указанного интервала входит в интервал [1;4].
Для этого введем функцию со следующими аргументами:
- х_интервал – это начальные данные (0, …, 4);
- интервал вероятностей является множеством вероятностей для начальных данных (0,15; 0,1; 0,15; 0,2; 0,4);
- нижний предел равен значению 1;
- верхний предел равен 4.
В результате выполненных вычислений получим:
Пример 2. В условии предыдущего примера нужно вычислить вероятность события «значение х равно 4».
Введем в ячейку С3 введем функцию с такими аргументами:
- х_интервал – начальные параметры (0, …, 4);
- интервал вероятностей – совокупность вероятностей для параметров (0,1; 0,15; 0,2; 0,15; 0,4);
- нижний предел – 4;
В данном примере верхний предел не указан, поскольку необходимо конкретное значение вероятности, а именно для значения 4.
Функция ВЕРОЯТНОСТЬ при нескольких условиях интервалов
Пример 3. В условии примера 1 нужно вычислить вероятность того, что значения интервала [0; 4] будут находится находятся внутри интервалов [0;1] и [3;4].
Описание формул аналогичные предыдущим примерам.
В результате выполненных вычислений получим:
Таким образом составив формулу можно с помощью данной функции вычислить процент вероятности при нескольких условиях.
Коэффициент вариации в статистике применяется для сравнения разброса двух случайных величин с разными единицами измерения относительно ожидаемого значения. В итоге можно получить сопоставимые результаты. Показатель наглядно иллюстрирует однородность временного ряда.
Коэффициент вариации используется также инвесторами при портфельном анализе в качестве количественного показателя риска, связанного с вложением средств в определенные активы. Особенно эффективен в ситуации, когда у активов разная доходность и различный уровень риска. К примеру, у одного актива высокая ожидаемая доходность, а у другого – низкий уровень риска.
Как рассчитать коэффициент вариации в Excel
Коэффициент вариации представляет собой отношение среднеквадратического отклонения к среднему арифметическому. Для расчета в статистике используется следующая формула:
- CV – коэффициент вариации;
- σ – среднеквадратическое отклонение по выборке;
- ǩ – среднеарифметическое значение разброса значений.
Коэффициент вариации позволяет сравнить риск инвестирования и доходность двух и более портфелей активов. Причем последние могут существенно отличаться. То есть показатель увязывает риск и доходность. Позволяет оценить отношение между среднеквадратическим отклонением и ожидаемой доходностью в относительном выражении. Соответственно, сопоставить полученные результаты.
При принятии инвестиционного решения необходимо учитывать следующий момент: когда ожидаемая доходность актива близка к 0, коэффициент вариации может получиться большим. Причем показатель значительно меняется при незначительном изменении доходности.
В Excel не существует встроенной функции для расчета коэффициента вариации. Но можно найти частное от стандартного отклонения и среднего арифметического значения. Рассмотрим на примере.
Доходность двух ценных бумаг за предыдущие пять лет:
Наглядно это можно продемонстрировать на графике:
Обычно показатель выражается в процентах. Поэтому для ячеек с результатами установлен процентный формат.
Значение коэффициента для компании А – 33%, что свидетельствует об относительной однородности ряда. Формула расчета коэффициента вариации в Excel:
Сравните: для компании В коэффициент вариации составил 50%: ряд не является однородным, данные значительно разбросаны относительно среднего значения.
Интерпретация результатов
Прежде чем включить в инвестиционный портфель дополнительный актив, финансовый аналитик должен обосновать свое решение. Один из способов – расчет коэффициента вариации.
Ожидаемая доходность ценных бумаг составит:
Среднеквадратическое отклонение доходности для активов компании А и В составляет:
Ценные бумаги компании В имеют более высокую ожидаемую доходность. Они превышают ожидаемую доходность компании А в 1,14 раза. Но и инвестировать в активы предприятия В рискованнее. Риск выше в 1,7 раза. Как сопоставить акции с разной ожидаемой доходностью и различным уровнем риска?
Для сопоставления активов двух компаний рассчитан коэффициент вариации доходности. Показатель для предприятия В – 50%, для предприятия А – 33%. Риск инвестирования в ценные бумаги фирмы В выше в 1,54 раза (50% / 33%). Это означает, что акции компании А имеют лучшее соотношение риск / доходность. Следовательно, предпочтительнее вложить средства именно в них.
Таким образом, коэффициент вариации показывает уровень риска, что может оказаться полезным при включении нового актива в портфель. Показатель позволяет сопоставить ожидаемую доходность и риск. То есть величины с разными единицами измерения.
Не знаю, как на форуме вставлять красиво формулы, поэтому посмотрите здесь, там описывается 2 подхода к вычислению значения р ручками.
Не знаю, как на форуме вставлять красиво формулы, поэтому посмотрите здесь, там описывается 2 подхода к вычислению значения р ручками.
Я согласен про доверительные интервалы, но ввиду не особых знаний в области медицинской статистики наша профессура требует пресловутое p.
И вы что-то говорили про два способа расчета. Какой второй?
Задача оценки статистической значимости равенства OR или RR единице эквивалентна задаче проверки равентства двух частот. Это легко обнаружить даже эмпирически если попытаться получить для двух одинаковых частот значения OR или RR отличные от 1. Задачу сравнения двух частот можно решать массой способов: точными критериями, рандомизационным критерием Монт-Карло, критериями на основе распредления хи-квадрат (Хи-кватрат Пирсона, отношение (лог)правдоподобия G-квадрат, критерий Фримана-Тьюки). Если вы продолжите поиски, то где-нибудь возможно встретите искомую формулу, но это будет не более грамотный, а всего лишь ещё один способ решения одной и той-же задачи, который, скорее всего, будет эквивалентен одному из перечисленных выше критериев. Вообще говоря р и OR (RR) отвечают на совсем разные вопросы и дополняют друг друга. Удобно рассуждать в такой последовательности:
1) Значимы ли различия частот статистически? Смотрим р, полученное с помощью любого из корректных для имеющихся данных статистических критериев, лучше для точного перестановочного теста. Допустим значимы.
2) Насколько велики эти различия? Смотрим RR или менее удобное для интерпретации таблиц частот OR.
3) Насколько точна эта оценка при моих объёмах выборок? Смотри 95%-ные ДИ для RR или OR.
Я согласен про доверительные интервалы, но ввиду не особых знаний в области медицинской статистики наша профессура требует пресловутое p.
Ох уж эта профессура, люблю её развлекать
Есть у нас частоты событий (можем оформить как таблицу сопряженности 2*2):
Case 21 67
Control 15 33
21 - случай +, 67 - случай -;
15 - контроль +, 33 - контроль -.
Напомним профессуре, как расчитывается величина отношения шансов: OR = (a * d) / (b * c) = (21 * 33) / (15 * 67) = 0,689
Мы не можем вычислить напрямую статистическую значимость величины отношения шансов (потому что она асимметрично распределена (skewed)), но можем для величины log(OR), которая с некой аппроксимацией нормально распределена = log(0,689) = -0.372514. Вот мы имеем числитель из той замечательной формулы в статье, которую Вы нашли. Поехали дальше к знаменателю.
Знаменатель - это SE, она же стандартная ошибка величины log(OR), которая в данном случае вычисляется по формуле: SE = sqrt(1/a + 1/b + 1/c + 1/d) = sqrt(1/21 + 1/67 + 1/15 + 1/33) =
Теперь мы имеем все, чтобы рассчитать значение z (z-score). Одно из критических значений для z при использовании 95% ДИ это 1,96 и -1,96. Если мы получим значение z в диапазоне от -1,96 до 1,96, р-значение будет больше 0,05 и у нас не будет достаточно доказательств, чтобы отклонить нулевую гипотезу. Считаем z = log(OR) / SE = -0.372514 / 0,397 =
Получил 0.1741, умножил на 2 (это опять же в формуле написано, которую Вы нашли), чтобы получить 2-сторонний (2-tailed), опять же с аппроксимацией, получил
0.348 (0,35 Вы получите в результате теста хи-квадрат, с поправкой Yates - большее значение, хотя этот Yates не стоит того, чтобы его применяли, как по-мне, но вокруг этого дебаты как всегда). Из этого делаем вывод, что при наших данных у нас недостаточно доказательств, чтобы утверждать про эффективность препарата (из этого вовсе не следует, что препарат не является эффективным).
P.S. гляньте на такой калькулятор, многое собрано в одном эксель-файле, может пригодится.
LaMorte___stat_tools.rar ( 181,57 килобайт ) Кол-во скачиваний: 575
Читайте также: