Математика и кофе: заметки с тегом Google Таблицы

Нормальное распределение

Иван Балдин — Sun, 05 Apr 2020 15:06:07 +0300

Нормальное распределение

Количество SD	-3,000	-2,576	-2,000	-1,960	-1,645	-1,282	1,282	1,645	1,960	2,000	2,576	3,000
Вероятность накопленным итогом	0,0013	0,0050	0,0228	0,0250	0,0500	0,1000	0,9000	0,9500	0,9750	0,9772	0,9950	0,9987
Вероятность в границах +/- стольких SD	-0,9973	-0,9900	-0,9545	-0,9500	-0,9000	-0,8000	0,8000	0,9000	0,9500	0,9545	0,9900	0,9973

График и данные в Google Таблицах

Продвинутый способ расчета рейтингов

Иван Балдин — Sat, 21 Sep 2019 15:59:00 +0300

Крайне любопытная статья на сайте EvanMiller.org, «Ranking Items With Star Ratings», предлагает продвинутый способ расчета рейтингов, например, по пятибалльной шкале.

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

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

Простой пример — оценки двух сотрудников:

Осипов — 5, 5, 5, 5, 5, 2, 2, 2, 2, 2. Среднее = 3,50.
Сухонцев — 4, 4, 3, 3. Среднее = 3,50.

Неразрешимая, на первый взгляд, ситуация решается методами байесовской статистики (что бы конкретно это здесь ни значило), вуаля:

Осипов — 2,72.
Сухонцев — 2,63.

Чудесным образом то ли меньшее среднеквадратичное отклонение (0,58 против 1,58), то ли меньшее количество оценок (4 против 10), то ли все они вместе уточнили средний рейтинг Сухонцева, отдав ему предпочтение в несколько сотых.

Формула продвинутого расчета среднего рейтинга

Приготовьтесь, будет немного больно.

Итак, предполагается, что у нас есть K возможных оценок, считаемых по k, каждая оценка стоит s_k баллов («1» — это 1 балл, «2» — это 2 балла и т. д.). Имея N полученных оценок для каждого объекта, по n_k оценок для каждого k, можно посчитать рейтинг каждого объекта по формуле:

Где z_α/2 это 1−α/2 квантиль нормального распределения. Посчитанный рейтинг является нижней границей нормальной аппроксимации байесова доверительного интервала для среднего рейтинга. Принимая, например, α=0,10 (z=1,65), рассчитанный рейтинг S будет означать, что в 95% случаев средний рейтинг объекта будет выше S.

Упрощая, «продвинутый» расчет среднего рейтинга позволяет дать прогноз возможной средней оценки, рассчитываемой традиционным путем. Ну и, следовательно, как показано выше, ранжировать объекты даже при формально одинаковой средней оценке.

Пример расчета продвинутого среднего рейтинга

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

Среднее 1.0 — средний рейтинг обычный, Среднее 2.0 — средний рейтинг продвинутый.

«Таганский» упал со 2-го на 4-е место по всей видимости, из-за того, что выборка в 66 оценок не дает достаточной уверенности в том, что его средний рейтинг действительно настолько высок, и в 90% случаев его рейтинг прогнозируется выше всего лишь 4,55, что примерно соответствует 4-му месту.

«Академический» формально был на 13-м месте, но, благодаря надежным 249 оценкам, для него прогнозируется, в 90% случаев, средний рейтинг не ниже 4,4, что поднимает его до 10-го места.

У меня сложилось ощущение, что формула более убедительно работает для коротких шкал оценок, как «от 1 до 5» в приведенном примере.

В любом случае, делюсь файлом в Google Таблицах — по идее, он считает рейтинги для всех шкал «длиной» до 100 оценок включительно, позволяет импортировать до 10 000 строк с оценками и корректировать уровень достоверности (90% в нашем примере).

Cм. также

https://www.evanmiller.org/ranking-items-with-star-ratings.html

Продвинутый способ расчета рейтинга в Google Таблицах

4 смски «за», 4 смски «против»

Иван Балдин — Mon, 16 Sep 2019 20:03:11 +0300

Недавно слушал «Вести ФМ», где обсуждались итоги единого дня голосования 8 сентября.

Меня заинтересовала следующая реплика ведущего, с 01:45:05:

Кстати, вот, слушатели из того же Хабаровского края пишут и, примерно, по количеству смсок делятся «50 на 50». 50% считают, что они позитивный выбор совершили, а 50% считают, что стало хуже, и это был негативный выбор. Это, понятно, не социологическое исследование. Ну, вот, просто я вижу десяток, восемь, где-то, смсок, и они примерно пополам делятся. Тоже любопытно.

К чести ведущего, абсолютно корректное замечание-«дисклеймер», что это не «социологическое исследование». И все же, что можно сказать о том, как, в реальности, делятся голоса, если у вас в наличии только 4 смски «за» и 4 смски «против»? Насколько соотношение «50 на 50», полученное на выборке в 8 смсок, подтверждает ровно то же самое распределение голосов в генеральной совокупности?

Считаем в Гугл Таблицах

Быстро воспроизводим эксперимент в Гугл Таблицах:

Итак, в тот день 4 человека прислали смски «за», 4 человека прислали смски «против». Логично предположить, что день на день не приходится, и сегодня это были одни слушатели, завтра смски будут присылать другие слушатели, и соотношение сил может быть «3 к 5», «5 к 3», «2 к 6» или «7 к 1» — любое сочетание теоретически возможно. Однако, если мы предполагаем, что взгляды аудитории делятся поровну, то чуть более вероятны сценарии «4 к 4», «3 к 5» или «5 к 3», а сценарии «8 к 0» или «1 к 7», например, менее вероятны.

Технически, мы имеем дело с биномиальным распределением — из 8 смсок мы ожидаем получить 4 смски «за», но не знаем наверняка, сколько их будет. Вероятность получить смску «за» равна 50% (допустим, что ровно 50% аудитории — «за»), в этом случае стандартная ошибка (SD, или σ) биномиального распределения рассчитывалась бы по формуле:

где p = 50%, а n = 8.

Считаем:

Получается, если вероятность получить смску «за» равняется 50%, то стандартное отклонение при выборке в 8 смсок равняется 17,68%!

Что же это означает на практике?

Это означает, что, поскольку имеющаяся выборка (8 смсок) крайне мала, доля случайности в нашем результате «4 „за“, 4 „против“», наоборот, крайне велика, и мы не можем уверенно говорить о строгом распределении голосов «50 на 50» среди всей аудитории «Вести ФМ». Единственное, что мы можем утверждать более-менее точно, это то, что истинная доля голосов «за» лежит в некотором интервале вокруг 50%. И величина этого интервала будет тем шире, чем больше мы захотим быть уверены в его надежности.

Предположим, мы хотим быть уверены в нашем доверительном интервале на 90%. (Оставляем себе право на ошибку в 10% случаев, другими словами). Согласно законам нормального распределения (а биномиальное распределение — это частный случай нормального), данный интервал определяется как 50%±1,645SD.

Такое несложно рассчитать в Гугл Таблицах:

Получается, что истинная доля голосов «за» лежит в интервале 50%±29,08%, т. е. от 20,92% до 79,08%. Примерно вот так это выглядит:

Значит, мы и близко не можем говорить о том, что «слушатели ... примерно ... делятся 50 на 50»! В лучшем случае (даже оставляя 10% на то, что мы ошибемся), мы можем говорить лишь об интервале от 21% до 79%.

Уточнение расчетов

Однако, интервал p±1,645SD тоже является достаточно грубой оценкой. Существуют более сложные, и немного более точные, способы оценить границы интервалов.

Воспользовавшись калькулятором Wolfram Alpha, можно получить следующие границы интервала:

Clopper-Pearson confidence interval for a binomial parameter	0,1929	0,8071
Wilson score confidence interval for a binomial parameter with continuity correction	0,2034	0,7966
standard confidence interval for a binomial parameter	0,2092	0,7908
Jeffreys confidence interval for a binomial parameter	0,2393	0,7607
Wilson score confidence interval for a binomial parameter	0,2486	0,7514
Agresti-Coull confidence interval for a binomial parameter	0,2486	0,7514

Ну а если хотим, хотя бы, 45-55% получить?

Вот еще интересно: на какого размера выборке, если голоса в ней по-прежнему делятся строго «50 на 50», мы сможем говорить о доверительном интервале, суженном хотя бы до 45-55%?

Рассчитать такое несложно. Если речь идет об интервале 50%±5%, (и мы продолжаем придерживаться уровня уверенности в результате, равном нашим любимым 90%), то 5% должны составлять 1,645 стандартных отклонений (SD). Отсюда, SD = 3,04%. По формуле стандартного отклонения:

откуда несложно найти n = 270,6. Получается, нужно 270-272 смски с распределением голосов строго пополам, чтобы говорить об интервале от 45% до 55% с уровнем уверенности 90%.

См. также

https://en.wikipedia.org/wiki/Binomial_proportion_confidence_interval
Калькулятор на WolframAlpha.com
https://cyberleninka.ru/article/n/doveritelnye-intervaly-dlya-chastot-i-doley.pdf
Cтатистическая достоверность для застройщиков

«В каждом пятом» звонке проблемы, проверяем

Иван Балдин — Sun, 09 Jun 2019 16:45:58 +0300

На днях пришел в голову такой пример: предположим, подрядчик жалуется на плохую связь «в каждом пятом» звонке.

Наша задача проверить, справедлива ли гипотеза, что 20% звонков имеют проблемы со связью. Причем, как всегда, мы не просто сделаем 100 тестовых звонков (на это у нас нет ресурсов), а сформулируем нулевую гипотезу, альтернативную гипотезу, и проверим ее с заданным уровнем достоверности.

Выдвигаем гипотезу и определяем уровень достоверности

Нулевой гипотезой (H₀) пусть будет предположение, что со связью все в порядке, или, по крайней мере, проблемы встречаются реже, чем в 20% звонков.

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

Уровень достоверности — это наша уверенность в результатах эксперимента. Чем он выше, тем больше придется сделать проверочных звонков, поэтому мы заложим 1% на возможную ошибку, и выберем уровень достоверности в 99% (1%, что, если даже эксперимент не подтвердит проблем со связью, они, в действительности, могут быть).

Cобираем формулу для расчета выборки

Предположим, цель эксперимента — опровергнуть альтернативную гипотезу H₁ («есть проблемы»), подтвердив нулевую гипотезу H₀ («все в порядке»). Чтобы сделать это, нам будет достаточно продемонстрировать N подряд успешных звонков без признаков проблем со связью, при этом допуская вероятность, равную или меньше 1%, что нам просто повезет, и, при наличии, в действительности, проблем с оборудованием, они случайно не проявят себя ни в одном из N звонков.

Из предположения подрядчика вытекает, что 80% звонков не имеют проблем. Вероятность отсутствия сбоев в N звонках подряд равна 0,80^N. Нам нужно подобрать минимальное N, при котором вероятность упадет до 1%: 0,80^N = 1%

Получается, нам нужно вычислить логарифм 1% по основанию 80%!

Загружаем в Гугл Таблицы:

Формула для ячейки C5 будет выглядеть как

=LOG(1-C2;1-C3)

Нужно сделать 20,64 звонка. (Проверяем: 0,80^20,64 = 0,9995%, идеально.)

Остается только добавить округление:

=ОКРУГЛВВЕРХ(C5)

или сразу

=ОКРУГЛВВЕРХ(LOG(1-C2;1-C3))

Проверяем гипотезу

Если альтернативная гипотеза H₁ нашего подрядчика верна, и мы испытываем проблемы со связью в каждом пятом звонке, то, вероятность не заметить проблем в 21 тестовом звонке подряд составляет порядка 1%. Иными словами, либо это крайне редкое совпадение (1%), либо альтернативная гипотеза о проблемах в 20% звонков неверна (99%), и мы оставляем нулевую гипозеу H₀. С вероятностью 99% мы уверены, что проблем со связью не наблюдается.

Качество звонков: сколько нужно прослушать

Иван Балдин — Sun, 26 May 2019 16:46:00 +0300

Распространенным инструментом оценки качества работы менеджеров отдела продаж является аудит качества телефонных звонков, «прослушка».

Предположим, вы задались целью не просто замерить качество телефонных звонков, но зафиксировать рост этого качества. Например, провели обучение (тренинг) менеджеров, либо предложили новую мотивацию за соблюдение стандартов качества, либо что-то еще.

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

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

Считаем размер выборки

На 15-й странице работы «Планирование размеров выборки для исследований в бихевиоризме» мне попался подходящий пример 2.4 и формула для расчета таких выборок:

В данном примере рассматривается изменение оценки ACT-теста по математике с 24,5 (дисперсия 8,2) до 26,0 баллов при α = 0,05 и мощности = 0,90.

Для удобства работы, я собрал приведенную формулу в Гугл-таблицах:
Калькулятор размера выборки

Вам остается скопировать файл, и можете подставлять нужные вам значения. Достоверность разумно выбирать от 80% до 95%, значение мощности — от 60% до 80%. Указываете средний балл оценки звонков до изменений, стандартное отклонение (SD) оценки звонков «до», и ожидаемый средний балл оценки звонков после изменений.

Верификация полученных результатов

Важно понимать, что, даже прослушав требуемое количество звонков «после», все равно необходимо проверять наличие статистически значимых различий через калькулятор А/Б-тестов.

См. также:

https://habr.com/ru/post/339798/
https://people.ucsc.edu/~dgbonett/docs/wrkshp/LectureNotes.pdf

t-Критерий Стьюдента

Иван Балдин — Mon, 15 Apr 2019 11:44:02 +0300

Если однажды перед вами оказывались два набора похожих данных, вам, вероятно, приходило в голову задаться вопросом: насколько эти данные различаются между собой? Или, что еще более важно, наблюдаются ли статистически значимые различия между этими выборками?

Поясню, о чем идет речь.

Допустим, вы проанализировали звонки за прошедший год и обратили внимание, что среднее время звонка в первой половине дня — 2 мин 45 сек, а во второй половине дня — 2 мин 57 сек. Следует ли из этого, что звонки после обеда в среднем длятся дольше? Или это простое совпадение, и, возьми вы звонки за год до этого, вы бы увидели другую картину?

Или, например, вы замеряли уровень гемоглобина у контрольной группы до начала исследований нового лекарства, и после. Предположим, средний уровень вырос с 142,5 г/л до 147,1 г/л. Достаточно ли опираться на увеличение среднего, чтобы сделать заключение об эффективности лекарства? Или, возможно, исследование нужно повторить? Увеличив размер контрольной группы, например?

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

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

Рассмотрим вопрос на примере.

Анализ длительности звонков Асланян и Евтушенко

В вашем отделе продаж работают 2 менеджера — Ольга Асланян и Кирилл Евтушенко. Вы получили данные по длительности их разговоров с покупателями и хотите проверить гипотезу, что разговоры Асланян в среднем длятся дольше разговоров Евтушенко.

Посчитаем среднюю длительность звонка, стандартное отклонение и число звонков, которые попали в выборке.

=СРЗНАЧ(B2:B999)

=СТАНДОТКЛОН(B2:B999)

=СЧЁТ(B2:B999)

В среднем, звонки Асланян длятся на 34,5 сек дольше звонков Евтушенко. (Кроме того, разброс длительности ее звонков больше, т. к. больше стандартное отклонение. Грубо говоря, короткие и длинные звонки у Асланян найти проще, чем у Евтушенко).

Достаточно ли полученных данных, чтобы сделать вывод о правильности гипотезы, что Асланян в среднем дольше общается с клиентами, чем Евтушенко? На самом деле, нет. Всегда существует вероятность, что в выборку Асланян случайно попали более длинные звонки, а в выборку Евтушенко — более короткие. Чем больше звонков доступно для анализа (а нам достались 242 и 209 звонков, что не так уж и мало), тем более надежен результат, но он никогда не надежен на 100%.

Впрочем, надежность 100% нам и не нужна. Не ракету к Марсу запускаем. Даже если нам удастся проверить нашу гипотезу с вероятностью 90-95%, этого будет вполне достаточно для большинства случаев. Пускай мы оставим себе шанс ошибиться в 5-10% случаев, зато нам не нужно будет ждать несколько лет, чтобы накопить достаточно данных для анализа, и управленческие решения (разбор звонков с менеджером, анализ продаж, корректировки скриптов) мы сможем принять уже сейчас.

Рассмотрим два способа, как нам проверить, случайность ли, что звонки Асланян в среднем длиннее звонков Евтушенко.

Проверка гипотезы о равенстве среднего. Простой способ

И в Google Таблицах, и в Microsoft Excel, есть функция ТТЕСТ. Воспользуемся ей для анализа наших выборок.

=ТТЕСТ(B2:B999;C2:C999;2;3)

У функции 4 атрибута, идущие через точку с запятой.

Диапазон ячеек, содержащих первую выборку.
Диапазон ячеек, содержащих вторую выборку.
Количество хвостов распределения. Выбираем «2», чтобы проверить наличие различий вообще, и «1», чтобы проверить, звонки Асланян длиннее, а не наоборот.
Тип применения t-критерия. По умолчанию выбираем «3». («2» выбираем если стандартные отклонения очень близки, «1» — если, например, вы сравниваете средний балл одних и тех же учеников на начало и конец года попарно.)

Итак, Т-тест дал вероятность 0,04595, или, округленно, 4,6%.

Что же это за вероятность? В нашем примере это вероятность того, что статистически значимые различия между звонками Асланян и Евтушенко отсутствуют. Технически, это вероятность, что наша «нулевая гипотеза» («нет разницы между выборками») была верна, а «альтернативная» («Асланян общается с покупателями дольше Евтушенко») — неверна.

Оставшиеся 95,4% составляют вероятность того, что между выборками есть статистические различия, и «альтернативная гипотеза» о различиях между выборками верна.

Вывод: с вероятностью 95,4% Асланян, действительно, в среднем общается с клиентами дольше Евтушенко. (С вероятностью 4,6% статистически значих различий между их звонками нет).

Проверка гипотезы о равенстве среднего. Сложный способ

Сложный способ будет состоит из двух этапов: расчет t-критерия Стьюдента и сравнение полученного значения t-критерия с контрольным.

На первом этапе рассчитаем t-критерий Стьюдента по следующей формуле:

X₁ и X₂ — средняя длина звонков в первой и второй выборке (238,6 сек и 204,1 сек)
s₁ и s₂ — стандартные отклонения первой и второй выборок в квадрате (их дисперсии, другими словами) (201,2² и 164,7² для наших выборок)
n₁ и n₂ — число звонков в первой и второй выборках (242 и 209 звонков)

Воспользуемся листочком бумаги и калькулятором, или же посчитаем все прямо в Google Таблицах:

=(F2-G2)/КОРЕНЬ(F3^2/F4+G3^2/G4)

t-Критерий равен 2,0014.

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

Но перед этим посчитаем число степеней свободы по формуле n₁+n₂-2:

242 + 209 — 2 = 449 степеней свободы

Воспользуемся теперь таблицей коэффициентов Стьюдента из Википедии, найдя строку, соответствующую нашим 449 степеням свободы.

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

Наше значение 2,0014 находится между 1,9623 и 2,3301: 1,9623 < 2,0014 < 2,3301

В шапке таблицы это соответствует 95%-му и 98%-му квантилю распределения Стьюдента, т. е. мы захватили 95%-й квантиль, но не захватили 98%-й:

Если расчетное значение t-критерия Стьюдента больше контрольного, значит, «альтернативная гипотеза» верна с соответствующей вероятностью (95%), и выборки статистически различаются.

Если бы мы получили значение t-критерия больше, чем 2,3301 (98%), мы бы могли говорить по правильности «альтернативной гипотезы» уже с 98%-й вероятностью. Аналогично, если бы мы получили значение t-критерия меньше, чем 1,9623 (95%), но больше 1,6464 (90%), мы бы говорили о правильности гипотезы на 90%.

Вывод: расчетное значение t-критерия Стьюдента 2,0014 соответствует, по меньшей мере, 95% уверенности в том, что между выборками есть статистически значимые различия, и звонки Асланян, действительно, в среднем длиннее звонков Евтушенко.

Наша «альтернативная гипотеза» получила 95%-ое подтверждение, мы можем быть уверены в результате и принимать решение о дальнейшей работе с полученный информацией.

Полезные ссылки

http://www.evanmiller.org/ab-testing/t-test.html