Формулы Excel для учёта ставок: ROI, банкролл, серии

Таблица со ставками без формул это просто список. Считать по нему что-либо руками бессмысленно: через сотню строк вы перестанете это делать, и учёт превратится в архив. Ниже собраны формулы, которых достаточно для полноценного журнала ставок, каждая с разбором, почему она считает именно так.

Формулы даны в русской локализации Excel, в скобках английские имена для Google Таблиц и англоязычной версии. Разделитель аргументов в русском Excel это точка с запятой, в английском запятая.

Структура, от которой всё считается

В скачиваемом шаблоне заголовки находятся в строке 11, ставки — в строках 12–511. Жёлтые ячейки заполняются вручную. J, L и M содержат формулы.

Перед началом удалите примеры из A12:I17 и K15 на листе «Журнал», A9:D10 на листе «Движения». Формулы сохраните. В B4 задайте стартовый банк на дату начала своей истории. Фрибеты, налоги, комиссии и валютная конвертация в этот шаблон не включены.

Формулы ниже используют эту структуру. Для русской локали Excel — русские имена функций и «;». Для английской локали — английские имена и «,». В скачиваемом XLSX эти настройки обрабатываются приложением при открытии.

Прибыль по одной ставке

Вставьте в J12 и протяните до J511. При выигрыше: сумма × (коэффициент − 1); при проигрыше: минус сумма; при возврате: 0; при выкупе: K − F. Ожидание и неполные данные дают пустую ячейку. Выплата при выкупе 0 допустима.

=ЕСЛИ(ИЛИ(НЕ(ЕЧИСЛО(A12));НЕ(ЕЧИСЛО(F12));F12<=0;I12="");"";ЕСЛИ(I12="Ожидание";"";ЕСЛИ(I12="Выигрыш";ЕСЛИ(И(ЕЧИСЛО(E12);E12>=1);ОКРУГЛ(F12*(E12-1);2);"");ЕСЛИ(I12="Проигрыш";-F12;ЕСЛИ(I12="Возврат";0;ЕСЛИ(I12="Продано";ЕСЛИ(И(ЕЧИСЛО(K12);K12>=0);K12-F12;"");""))))))

Проверка: 1 000 ₽ по коэффициенту 1,80 при выигрыше дают +800 ₽; проигрыш 1 500 ₽ даёт −1 500 ₽; возврат 800 ₽ даёт 0; выкуп ставки 1 000 ₽ за 1 200 ₽ даёт +200 ₽. Неизвестный результат не приравнивайте к возврату.

ROI: главная метрика и главная ошибка

Для результата задайте процентный формат, без дополнительного умножения на 100. Колонка M включает только ставки с рассчитанной прибылью и статусом Выигрыш, Проигрыш или Продано. Возвраты и ожидания исключены. При нулевом обороте ROI пустой.

=ЕСЛИ(СУММ(M12:M511)=0;"";СУММ(J12:J511)/СУММ(M12:M511))

Формула M12, уже включённая в файл:

=ЕСЛИ(ЕЧИСЛО(J12);ЕСЛИ(ИЛИ(I12="Выигрыш";I12="Проигрыш";I12="Продано");F12;0);"")

В учебном файле: прибыль −220 ₽, оборот 4 200 ₽, ROI −5,24%. Возврат 800 ₽ и ожидание 600 ₽ не входят в оборот. Пополнения и выводы не являются доходом от ставок.

Для проверки общей суммы используйте калькулятор ROI. Если чистая прибыль −220 ₽ при обороте 4 200 ₽, выплаты по этой выборке составляют 3 980 ₽.

Винрейт без возвратов

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

=ЕСЛИ(СЧЁТЕСЛИ(I12:I511;"Выигрыш")+СЧЁТЕСЛИ(I12:I511;"Проигрыш")=0;"";СЧЁТЕСЛИ(I12:I511;"Выигрыш")/(СЧЁТЕСЛИ(I12:I511;"Выигрыш")+СЧЁТЕСЛИ(I12:I511;"Проигрыш")))

В учебных данных две победы и одно поражение: 2 / 3 = 66,67%. Это не означает положительную прибыль: суммы проигранной и выигранных ставок различаются.

Банкролл на конец даты

Вставьте в L12 и протяните вниз. B4 — начальный банк. На листе «Движения» вводите пополнения и выводы положительными числами: E автоматически присваивает знак. Банк на конец даты учитывает все операции этой даты и известную прибыль ставок с датой события не позже неё. Открытые ставки не резервируются; внутридневной свободный остаток здесь не рассчитывается.

=ЕСЛИ(НЕ(ЕЧИСЛО(A12));"";$B$4+СУММЕСЛИМН($J$12:$J$511;$A$12:$A$511;"<="&A12)+СУММЕСЛИМН('Движения'!$E$9:$E$208;'Движения'!$A$9:$A$208;"<="&A12))

Контроль: старт 10 000 ₽ + пополнение 1 500 ₽ − вывод 400 ₽ − убыток 220 ₽ = 10 880 ₽. Если нужен реальный остаток на момент расчёта, записывайте операции по времени фактического движения денег: дата события сама по себе его не восстанавливает.

Самая длинная серия поражений

Это дополнительная колонка N, которой нет в готовом файле. Отсортируйте журнал по дате и времени расчёта. В N12:

=ЕСЛИ(I12="Проигрыш";1;0)

В N13 и далее:

=ЕСЛИ(I13="Проигрыш";N12+1;ЕСЛИ(ИЛИ(I13="Выигрыш";I13="Продано");0;N12))

Максимум: =МАКС(N12:N511). Здесь возвраты и ожидания пропускаются, выигрыш и выкуп завершают серию. Эта договорённость нужна для воспроизводимого результата; для анализа только побед и поражений выкуп тоже можно пропускать. Подробнее — серии побед и поражений.

Срезы: где именно вы зарабатываете

Прибыль по футболу:

=СУММЕСЛИ(B12:B511;"Футбол";J12:J511)

ROI по футболу, процентный формат:

=ЕСЛИ(СУММЕСЛИ(B12:B511;"Футбол";M12:M511)=0;"";СУММЕСЛИ(B12:B511;"Футбол";J12:J511)/СУММЕСЛИ(B12:B511;"Футбол";M12:M511))

В знаменателе используется M, а не вся сумма F. Так возвраты и ожидания не искажают ROI. Для букмекера замените диапазон B на G. Положительный ROI отдельного среза описывает прошлое и сам по себе не подтверждает устойчивое преимущество.

Если нужен доступ с нескольких устройств, откройте тот же файл в Google Таблицах.

Где формул перестаёт хватать

Все расчёты выше отвечают на вопросы о прошлом. Проблема беттера обычно в настоящем: в момент, когда после двух проигрышей рука тянется поставить на коэффициент выше обычного и суммой побольше. Таблица об этом молчит, потому что она считает, а не предупреждает.

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

Трекер делает ровно то же самое, что формулы выше, но пересчитывает всё в момент занесения ставки и добавляет то, чего в таблице нет в принципе: предупреждение о подъёме коэффициента после серии поражений и стоп-лосс по дню. Если таблица у вас уже собрана, самый честный тест это месяц параллельного ведения.