{ "version": "https:\/\/jsonfeed.org\/version\/1", "title": "Математика и кофе: заметки с тегом Excel", "_rss_description": "Отделы продаж, коллцентры, аналитика, цифры и данные, воронки продаж, матстатистика..", "_rss_language": "ru", "_itunes_email": "", "_itunes_categories_xml": "", "_itunes_image": "", "_itunes_explicit": "", "home_page_url": "https:\/\/mathandcoffee.ru\/tags\/excel\/", "feed_url": "https:\/\/mathandcoffee.ru\/tags\/excel\/json\/", "icon": "https:\/\/mathandcoffee.ru\/user\/userpic@2x.jpg?1559386410", "author": { "name": "Иван Балдин", "url": "https:\/\/mathandcoffee.ru\/", "avatar": "https:\/\/mathandcoffee.ru\/user\/userpic@2x.jpg?1559386410" }, "items": [ { "id": "45", "url": "https:\/\/mathandcoffee.ru\/all\/grafik-konversii-s-doveritelnym-intervalom\/", "title": "График конверсии с доверительным интервалом", "content_html": "

Некоторое время с удовольствием использую более свежую визуализацию конверсии, добавляя к своим диаграммам границы доверительного интервала.<\/b><\/p>\n

Конверсия офисов продаж<\/h2>\n
Итак, например, мы оцениваем эффективность работы территориальных офисов продаж. Под эффективностью<\/i><\/b> понимаем отношение числа совершенных продаж к числу заявок (конверсию заявок в продажи, или просто «конверсию»).<\/i><\/b> То есть, если в офисе «Сокольники» за квартал было 19 продаж на 33 заявки, их эффективность будем считать равной 19\/33 = 57,6%.<\/p>\n
\n $\"\"$ \n<\/div>\n
\n $\"\"$ \n<\/div>\n
Очевидно, что одни офисы работают эффективнее других: конверсия по офисам меняется от 57,6% до 17,6%. Заметно также, что и число заявок в офисах различно: от 33 заявок в «Сокольниках» до 706 заявок в «Лианозово».<\/p>\n
Обычно на этом этапе многие останавливаются, но есть несложный способ воспользоваться понятием «доверительного интервала» или «стандартного отклонения (SD)», чтобы показать то, что, на первый взгляд, не так заметно.<\/p>\n
Оцениваем размер выборки и величину SD<\/h2>\n
Как нетрудно заметить, из-за неравного числа заявок по разным офисам («Сокольники» отличаются в этом смысле от «Лианозово» почти в 22 раза), уверенность в надежности рассчитанной конверсии будет не одинакова. Так, для «Лианозово» результат в 36,1% достигнут на выборке из 706 заявок и может считаться вполне надежным; в «Сокольниках» мы получили результат 57,6% на небольшой выборке в 33 заявки, из-за чего нет уверенности, что, получи со временем последние свои 706 заявок, они бы удержали результат на том же уровне.<\/p>\n
Разумеется, необходимо прикинуть размер доверительного интервала для каждого офиса продаж, исходя из числа заявок, то есть, размера выборки.<\/p>\n
Уже знакомая нам формула стандартного отклонения (SD), или σ<\/i>:<\/p>\n
\n $\"\"$ \n<\/div>\n
где p<\/i> — величина конверсии, n<\/i> — число заявок.<\/p>\n
Считаем в колонке E:<\/p>\n
\n $\"\"$ \n<\/div>\n
Полученная величина стандартного отклонения (SD) показывает погрешность при расчете конверсии, и, очевидно, оказалась выше там, где была меньше выборка. Чем меньше данных, тем менее надежен рассчитанный результат, и тем меньше мы уверены в нашей оценке эффективности соответствующего офиса продаж.<\/p>\n
Считаем границы 90%-го доверительного интервала<\/h2>\n
Дополним нашу таблицу рассчитанными нижней и верхней границей 90%-го доверительного интервала. Другими словами, оценим разброс конверсий по каждому из офисов продаж, так, что с вероятностью 90% мы будем уверены, что истинная<\/i> конверсия лежит в пределах этого диапазона.<\/p>\n
\n $\"\"$ \n<\/div>\n
Зная о том, что границы 90%-го доверительного интервала лежат в пределах ±1,645SD, вычитаем и прибавляем 1,645SD для нижней и верхней границ, соответственно. Для «Лианозово» получаем, что их истинная<\/i> конверсия лежит в пределах от 33,1% до 39,1%. (По-прежнему, в 1 случае из 10 она выходит за границы нашего интервала, но зато в 9 случаях из 10 мы не ошиблись).<\/p>\n
Дополняем график, рисуя «свечи»<\/h2>\n
В Excel 2013 воспользуемся «биржевой диаграммой»,<\/b> указав вместо самого высокого<\/i><\/b> и самого низкого курсов<\/i><\/b> верхнюю и нижнюю границу наших доверительных интервалов, а вместо курса закрытия<\/i><\/b> — рассчитанную вначале конверсию:<\/p>\n
\n $\"\"$ \n<\/div>\n
\n $\"\"$ \n<\/div>\n
Доработанная подобным образом диаграмма не меняет выводов, полученных в самом начале. Однако, для наблюдательного руководителя она ненавязчиво напоминает, что полученные значения конверсий офисов продаж не конечны,<\/b> и особенно «не конечны» там, где оказались шире границы разброса конверсии.<\/p>\n
«Сокольники», предварительно, обогнали «Беговой», однако, если хороший результат «Бегового» надежен за счет узкого интервала, то результат «Сокольников» очень приблизителен, поэтому уверенные выводы возможно делать лишь о части офисов продаж, для остальных — нужно больше данных, а до тех пор их позиции в рейтинге можно считать лишь предварительными, или, как было сказано выше, не конечными.<\/p>\n
См. также:<\/h2>\n
http:\/\/italylov.ru\/blog\/all\/ctatisticheskaya-dostovernost-koltrekinga\/<\/a><\/p>\n", "date_published": "2021-05-25T01:13:50+03:00", "date_modified": "2021-05-25T01:17:18+03:00", "image": "https:\/\/mathandcoffee.ru\/pictures\/candles-sd-00.png", "_date_published_rfc2822": "Tue, 25 May 2021 01:13:50 +0300", "_rss_guid_is_permalink": "false", "_rss_guid": "45", "_e2_data": { "is_favourite": false, "links_required": [], "og_images": [ "https:\/\/mathandcoffee.ru\/pictures\/candles-sd-00.png", "https:\/\/mathandcoffee.ru\/pictures\/candles-sd-01.png", "https:\/\/mathandcoffee.ru\/pictures\/CodeCogsEqn.png", "https:\/\/mathandcoffee.ru\/pictures\/candles-sd-02.png", "https:\/\/mathandcoffee.ru\/pictures\/candles-sd-03.png", "https:\/\/mathandcoffee.ru\/pictures\/candles-sd-04.png", "https:\/\/mathandcoffee.ru\/pictures\/candles-sd-05.png" ] } }, { "id": "30", "url": "https:\/\/mathandcoffee.ru\/all\/ischem-anomalii\/", "title": "Ищем «аномалии», включаем красные и зеленые «лампочки»", "content_html": "
Переписываясь на днях с коллегой в Телеграме, в очередной раз увидел примерно вот такой отчет (сейчас просто нарисовал похожий) — сверху недели, сбоку, допустим, территориальные офисы продаж (там были месяцы и продажи по типам продукта, но для целей этой заметки это совершенно не имеет значения):<\/p>\n
\n $\"\"$ \n<\/div>\n
Воспользовавшись «Условным форматированием» в Экселе, замечаем, что на 6-й неделе в офисе «Академический» было 503 продажи. В общем, до этого момента ничего необычного, и так выжали 90% из данных, можно работать с отчетом и анализировать, что душе угодно.<\/p>\n
Однако, есть несложная доработка, позволяющая выжать из данных еще лишние 5%.<\/p>\n
Что, собственно, ищем<\/h2>\n
На картинке особо не видно, но чем ниже по списку, тем меньше в среднем продаж в каждом следующем офисе. То есть, будем считать, что офисы продаж все очень разные, и некорректно сравнивать «Академический» с «Якиманкой» — нехитрым вычислением получается, что «Академический» в среднем делал 242 продажи в неделю, а «Якиманка» — всего 13. Предположим, что тому есть объективные причины, и никто и не требовал от всех офисов показывать одинаковые результаты.<\/p>\n
И тогда можно задать себе вопрос: достаточно ли просто анализировать абсолютные показатели по нашим офисам? И не будет ли правильнее копнуть вглубь, и попробовать найти такие показатели, которые выбиваются из общей картины?<\/b> Такие недели, которые были аномальными<\/i><\/b> для данного офиса продаж.<\/p>\n
Здесь и далее под «аномалией»<\/i> я буду понимать такое значение продаж, которое слишком отличается от среднего по данном офису.<\/b> Как в большую (и надо разобраться, как повторить этот результат) или в меньшую (проанализировать, как избежать неудачи в будущем) сторону.<\/p>\n
Распределяем результаты офиса «Академический»<\/h2>\n
Изучив результаты продаж офиса «Академический» за прошедшие 43 недели, мы рассчитали, что в среднем они делают 241,5 продаж в неделю, при этом стандартное отклонение (SD) равно 86,3.<\/p>\n
Напомню формулы:<\/p>\n
=СРЗНАЧ(B2:AR2)<\/code><\/pre>
=СТАНДОТКЛОН.В(B2:AR2)<\/code><\/pre>Можно, гипотетически, представить, что мы имеем возможность наблюдать за результатами офиса «Академический» 200 (sic!) лет, при условии, что все это время среднее и стандартное отклонение не меняются, т. е., грубо говоря, они работают, как работали. В этом случае, мы увидели бы распределение результатов продаж, близкое к нормальному:<\/i><\/p>\n
\n $\"\"$ \n<\/div>\n
Давайте даже еще раз перерисуем картинку. 2 290 недель из 10 000 они бы делали от 200 до 249 продаж в неделю:<\/p>\n
\n $\"\"$ \n<\/div>\n
Понимаете, к чему я клоню?<\/p>\n
Если только допустить, что результаты продаж подчиняются законам нормального распределения<\/i> (грубо говоря, равновероятно продать как чуть больше, так и чуть меньше среднего), существует некоторое разумное отклонение от среднего, в пределах которого было бы глупо всерьез говорить о «спаде продаж» или «невероятном успехе». Иными словами, бессмысленно считать «аномалией» то, что лежит в пределах разумного отклонения от среднего.<\/p>\n
Остается сформулировать критерии «разумности» и научить отчет сигнализировать об «аномалиях».<\/p>\n
Вспоминаем теорию<\/h2>\n
Если вкратце, то, допустив на минутку, что мы имеем дело с нормальным распределением,<\/i> вычислив среднее значение<\/i> и стандартное отклонение (SD),<\/i> мы можем уверенно говорить о том, что 90% данных в отчете не будут выходить за границы ±1,645SD от среднего.<\/b><\/p>\n
Применительно к офису «Академический» речь идет о том, что для 90% времени результаты их продаж будут лежать в диапазоне от 100 до 383, или 241,5±142,0. Поэтому до тех пор, пока цифры не вышли за пределы этих границ, мы не наблюдаем ничего необычного.<\/p>\n
Сразу оговоримся: конечно, степень «необычности», или «аномалии», каждый определяет для себя сам. Для одних, подозрение могут вызывать показатели, выбивающиеся за рамки 80%-ной вероятности (±1,28SD), для других — терпимым будет отклонение в ±1,96SD, что соответствует 95%-й вероятности. Тогда, первые будут бить искать причины «аномалии» в 20% случаев, вторые — в 5%. Каждую пятую неделю но отчете у коммерческого директора первые будут объяснять, что произошло, и почему, тогда как вторые будут делать это раз в 4-5 месяцев.<\/p>\n
Допущение о том, что продажи в территориальных офисах, число посетителей на сайте, количество рекламных звонков, клики по баннеру распределяются по закону нормального распределения, дало нам потрясающую возможность оценивать вероятность наступления «аномалии» — слишком сильного отклонения от среднего значения. Обратно, оно учит нас не бить тревогу там, где отклонение, хотя и есть, не является достаточно сильным, и делает, отчасти, бессмысленным анализ и разбор ситуаций, когда показатель отклоняется в пределах разумного.<\/p>\n
Перекрашиваем отчет, включаем зеленые и красные «лампочки»<\/h2>\n
Теперь мы хотим переделать отчет о продажах в территориальных офисах таким образом, чтобы напротив подозрительно больших или подозрительно маленьких значений загорались бы зеленые и красные «лампочки».<\/p>\n
Нам необходимо научить отчет «включать» наши «лампочки», если значение в ячейке становится больше или меньше границ 90%-го диапазона, т. е. в примерно 90% случаев ни одна из «лампочек» «загораться» не будет, в примерно 5% случаев будет «загораться» красная «лампочка», и еще в примерно 5% — зеленая.<\/p>\n
Применительно к «Академическому», мы хотим выделять красным значения, меньшие чем 241,5-1,645*86,3, т. е., меньшие, чем 100, и мы ходим выделять зеленым значения, большие, чем 241,5+1,645*86,3, т. е., большие, чем 383.<\/p>\n
Нам остается рассчитать границы включения «лампочек» по каждому из офисов продаж, рассчитав последовательно: среднее значение продаж, стандартное отклонение (SD), нижнюю границу 90%-го диапазона, верхнюю границу 90%-го диапазона.<\/p>\n
\n $\"\"$ \n<\/div>\n
Используемые формулы:<\/p>\n
=СРЗНАЧ(B2:AR2)<\/code><\/pre>=СТАНДОТКЛОН.В(B2:AR2)<\/code><\/pre>=B2-1,645*C2<\/code><\/pre>=B2+1,645*C2<\/code><\/pre>У нас получилась следующая таблица, содержащая расчеты по нижним и верхним границам того, что мы далее будем считать «аномалией»:<\/p>\n \n\n<\/div>\nТеперь, используя инструмент «Условное форматирование» — «Правило выделения ячеек» — «Меньше...»\/«Больше...», последовательно для каждого из 17-ти офисов продаж настраиваем правила подсветки ячеек красным и зеленым, в зависимости от того, будет ли значение ниже нижней границы 90%-го диапазона, или выше верхней границы:<\/p>\n \n\n\n\n<\/div>\n<\/div>\nДополнительно выставляем светло-серый цвет текста, чтобы подсвеченные «аномалии» были еще более заметны. Добавляем градиент от белого к светло-серому, чтобы сохранить первоначальную идею выделять большие значения более темной заливкой. Законченная таблица приобретает следующий вид:<\/p>\n \n\n<\/div>\nВыводы<\/h2>\nИспользуя идею о разбросе значений вокруг среднего в нормальном распределении, нам удалось доработать наш отчет о территориальных офисах таким образом, что мы не просто видим результаты, но и теперь отдельно включаем красные и зеленые «лампочки» для тех результатов, которые представляют интерес, как «аномалии» — маловероятно маленькие или маловероятно большие значения, определив уровень «аномалии» как все, что выходит за пределы 90% вероятности.<\/p>\n", "date_published": "2019-09-12T15:27:33+03:00", "date_modified": "2019-09-12T17:41:05+03:00", "image": "https:\/\/mathandcoffee.ru\/pictures\/anomalii00.PNG", "_date_published_rfc2822": "Thu, 12 Sep 2019 15:27:33 +0300", "_rss_guid_is_permalink": "false", "_rss_guid": "30", "_e2_data": { "is_favourite": false, "links_required": [ "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/jquery\/jquery.js", "system\/library\/fotorama\/fotorama.css", "system\/library\/fotorama\/fotorama.js" ], "og_images": [ "https:\/\/mathandcoffee.ru\/pictures\/anomalii00.PNG", "https:\/\/mathandcoffee.ru\/pictures\/anomalii01.PNG", "https:\/\/mathandcoffee.ru\/pictures\/anomalii02.PNG", "https:\/\/mathandcoffee.ru\/pictures\/anomalii03.PNG", "https:\/\/mathandcoffee.ru\/pictures\/anomalii04.PNG", "https:\/\/mathandcoffee.ru\/pictures\/anomalii05.PNG", "https:\/\/mathandcoffee.ru\/pictures\/anomalii06.PNG", "https:\/\/mathandcoffee.ru\/pictures\/anomalii07.PNG" ] } }, { "id": "2", "url": "https:\/\/mathandcoffee.ru\/all\/skolzyaschee-srednee-ili-kak-sgladit-grafik\/", "title": "Скользящее среднее, или как сгладить график", "content_html": " Честно говоря, не знаю, как правильно называется эта штука, но пусть у нее будет рабочее название «скользящее среднее».<\/b><\/p>\n Очень часто бывает так, что у нас есть данные с разбивкой по дням.<\/b> Например, заходы на сайт или звонки в отдел продаж. И, в попытке проанализировать динамику<\/b> происходящего, мы строим график, получая примерно следующее:<\/p>\n \n\n<\/div>\nЧто здесь не так?<\/p>\n Достаточно очевидно, что, во-первых, график постоянно прыгает вверх-вниз, а, во-вторых, имеет некрасивые экстремумы<\/i> вроде 16 заявок в октябре или 122 заявок в августе. День на день не приходится, и данные слишком сильно колеблются вокруг среднего значения (кстати, среднее тут равно 63).<\/p>\n А там, где что-то так некрасиво прыгает, часто можно что-то сгладить, используя понятие скользящего среднего<\/i><\/b><\/a>.<\/p>\n Скользящее среднее. Простой способ.<\/h2>\nПопробуем «сгладить» наш прыгающий график путем расчета среднего числа заявок на дату,<\/i><\/b> исходя из предыдущих 6 дней (7-й — текущий день, итого ровно неделя).<\/p>\n Напротив 07.01.2018 напишем формулу<\/p>\n =СРЗНАЧ(B2:B8)<\/code><\/pre>\n\n<\/div>\nПротянув формулу по всему году до самой последней строки, получим среднее число заявок на каждую дату за предыдущую неделю<\/i>. Как будто рамку, шириной в одну неделю, мы двигали по году вдоль с шагом в один день.<\/p>\n \n\n<\/div>\nВизуально ничего не изменилось. Разве что, раньше были целые значения, а теперь, из-за усреднения, вылезли знаки после запятой — 79,9, 84,1. Обновим наш график:<\/p>\n \n\n<\/div>\nНа месте прежнего, «прыгающего», графика, теперь более гладкая линия. Исчезли аномальные дни с 16 и 122 звонками в октябре и в августе, зато теперь, на «недельном» масштабе, стал заметнее провал на майские праздники.<\/p>\n Скользящее среднее. Сложный способ.<\/h2>\nНекоторое время попрактиковавшись с вычислением скользящего среднего, вы обратите внимание, что, чем шире вы будете брать «окно» для расчета среднего на дату, тем сильнее будет сглаживаться ваш график. Теоретически, вы можете взять окно шириной в 365 дней... и получится практически ровная линия. А при окне шириной в 1 день — график не сглаживается вообще.<\/p>\n В этот момент становится понятно, что «7 дней» из первого примера — это просто случайное число, а на самом деле, оно может быть абсолютно любым — все зависит лишь от ваших предпочтений и представлений о том, что вы хотите увидеть и проанализировать.<\/p>\n Попробуем не задавать жестко ширину нашего «окна», а сделать его параметром<\/i> нашего графика. Пусть «окно» в 7 дней, используемое для сглаживания графика, будет зависеть от цифры «7», помещенной в ячейку C1. И пусть, если мы меняем «7» на «5» или «30», Эксель перестраивает наш график.<\/p>\n Итак, настало время для красивой формулы в ячейке C8:<\/p>\n =СРЗНАЧЕСЛИМН(B$2:B$365;A$2:A$365;"<="&A8;A$2:A$365;">="&(A8-$C$1+1))<\/code><\/pre>Функция<\/p>\n =СРЗНАЧЕСЛИМН()<\/code><\/pre>берет и считает среднее значение для тех дат, для которых будут выполняться оба условия:<\/p>\n \nДата, которая участвует в расчете среднего, должна быть больше или равна дате, отстоящей от даты, для которой мы рассчитываем среднее, назад на N-1 дней (где N — ширина нашего «окна»).<\/li>\n Дата, которая участвует в расчете среднего, должна быть меньше или равна дате, для которой мы это среднее рассчитываем.<\/li>\n<\/ol>\nПроще говоря, для расчета среднего числа заявок на 18 апреля при N=7 дней, мы возьмем среднее от числа заявок с 12 по 18 апреля (больше или равно 12 апреля и меньше или равно 18 апреля). Для расчета среднего числа заявок на 19 апреля — среднее от числа заявок с 13 по 19 апреля, и так далее.<\/p>\n Выделим ячейку C1 под наш параметр N, тогда формула для 07.01.2018 выглядит так:<\/p>\n =СРЗНАЧЕСЛИМН(B$2:B$365;A$2:A$365;"<="&A8;A$2:A$365;">="&(A8-$C$1+1))<\/code><\/pre>Итого, теперь мы имеем уже знакомый нам «сглаженный» 7-дневный график, но ширину «окна» задали не строго, а привязали ее к параметру, записанному в ячейке C1:<\/p>\n \n\n<\/div>\nМеняя значение параметра в ячейке C1, получаем все более и более ровную линию. Вот, для сравнения, наложенные друг на друга графики при N=1, N=5, N=30:<\/p>\n \n\n<\/div>\n", "date_published": "2019-05-26T11:23:27+03:00", "date_modified": "2019-05-26T11:23:15+03:00", "image": "https:\/\/mathandcoffee.ru\/pictures\/mvng_avrg_03.png", "_date_published_rfc2822": "Sun, 26 May 2019 11:23:27 +0300", "_rss_guid_is_permalink": "false", "_rss_guid": "2", "_e2_data": { "is_favourite": false, "links_required": [ "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css", "system\/library\/highlight\/highlight.js", "system\/library\/highlight\/highlight.css" ], "og_images": [ "https:\/\/mathandcoffee.ru\/pictures\/mvng_avrg_03.png", "https:\/\/mathandcoffee.ru\/pictures\/mvng_avrg_05.png", "https:\/\/mathandcoffee.ru\/pictures\/mvng_avrg_06.png", "https:\/\/mathandcoffee.ru\/pictures\/mvng_avrg_07.png", "https:\/\/mathandcoffee.ru\/pictures\/mvng_avrg_08.png", "https:\/\/mathandcoffee.ru\/pictures\/mvng_avrg_09.png" ] } } ], "_e2_version": 3565, "_e2_ua_string": "E2 (v3565; Aegea)" }