Открываю на днях Excel, и вижу:
Рис. 1. Capilot в Office 365
Скачать заметку в формате Word или pdf, примеры в формате Excel
А я пишу заметку про новую фишку в Fantasy Premier League и мне нужна хитрая формула в Excel, чтобы рассчитать статистику. Дай, думаю, протестирую.
Суть задачи. В английской премьер-лиге 20 команд играют попарно 10 матчей в туре. Перед каждым туром они ранжированы в турнирной таблице. Как часто в матчах будут встречаться команды, которые разделены в таблице на 5 и более позиций. Например, на 19 января 2025 г. запланированы 4 матча
Рис. 2. Расписание матчей премьер-лиги 19 января 2025 г.
А турнирная таблица выглядит следующим образом:
Рис. 3. Турнирная таблица премьер-лиги на утро 19 января 2025 г.
Эвертон и Тоттенхэм разделяет одна позиция в таблице, МЮ и Брайтон – 3, Ноттингем и Саутгемптон – 17, Ипсвич и Мансити – 10. В этих четырех матчах два пройдут между командами, которых разделяет 5 и более позиций. А сколько таких матчей должно быть в среднем?
Как аналитически ответить на вопрос, я сходу не придумал, поэтому решил смоделировать. Для этого мне нужно:
- случайным образом разбить 20 команд на пары,
- найти разность значений в паре,
- подсчитать число пар с разницей по модулю ≥ 5,
- получить много таких значений, например 1000,
- рассчитать среднее и стандартное отклонение по выборке.
Массив из 20 команд, случайным образом разбитых на пары
Ну что ж, начнем с Capilot.
Рис. 4. Начало диалога
Отличная подсказка! Не уверен, что я бы нашел решение самостоятельно. И уж точно не за три минуты, которые потребовались мне, чтобы написать промт! Но… Capilot еще не знает, что я хочу создать мегаформулу, поэтому он предлагает череду действий и формул, ссылающихся на диапазоны ячеек. Я немного изменю предложенный алгоритм:
Рис. 5. Массив 10 случайных пар
В ячейке АQ1 я задам массив из последовательных чисел от 1 до 20. В ячейке AR1 – массив из 20 случайных чисел в диапазоне от 0 до 1. В ячейке AT1 – массив чисел из АQ1, отсортированных по AR1. Для моих целей все равно, как сортировать: по убыванию или возрастанию. В ячейке AV1, используя подсказку Capilot, создам массив размерности 10х2, который представляет случайное распределение 20 команд по парам.
Как собирать мегаформулы? Нужно идти с последней формулы и подставлять текст формул вместо ссылок. Сначала в AV1 заменяем AT1# на формулу в ячейке AT1 (без знака равно). Получим
1 |
=ИНДЕКС(СОРТПО(AQ1#;AR1#;1);ПОСЛЕД(10;2)) |
Далее подставляем текст формул из ячеек вместо ссылок AQ1# и AR1#, и т.д. На текущий момент мегаформула имеет вид:
1 |
=ИНДЕКС(СОРТПО(ПОСЛЕД(20);СЛМАССИВ(20;;0;1;ЛОЖЬ);1);ПОСЛЕД(10;2)) |
Построчная разность значений
Для дальнейшего анализа я планирую использовать функцию BYROW(массив, функция). Напомню, что BYROW предназначена для построчных действий с массивами. Т.е., на вход BYROW берет массив n*m, а возвращает массив n*1, выполняя с каждой строкой действие, указанное во втором аргументе – функция. Но вот незадача: стандартной функции для нахождения разности BYROW не предлагает:
Рис. 6. В функции BYROW нет построчной разности
Зато есть LAMBDA. Сходу я также затрудняюсь написать код для LAMBDA, отсюда второй вопрос Capilot:
Рис. 7. Вопросы Capilot, как написать LAMBDA
На этот раз Capilot не справился. Но… есть проверенный ChatGPT:
Рис. 8. Функция LAMBDA в ChatGPT
Единственная неточность, которую допустил ChatGPT – использование запятой в качестве разделителя, вместо точки с запятой.
В итоге я нашел построчную разность с помощью формулы
1 2 |
=BYROW(ИНДЕКС(СОРТПО(ПОСЛЕД(20);СЛМАССИВ(20;;0;1;ЛОЖЬ);1); ПОСЛЕД(10;2));LAMBDA(row;ИНДЕКС(row;1)-ИНДЕКС(row;2))) |
Эта формула возвращает массив 10х1 с построчной разностью рейтингов двух команд, выбранных случайным образом.
Число пар с разницей по модулю ≥ 5
Сначала нужно взять по модулю значения массива BYROW(), а затем… нет не воспользоваться функцией СЧЁТЗ(). К сожалению, функция СЧЁТЗ() такая старая, что в MS не стали ее переписывать для работы с динамическими массивами. Так что СЧЁТЗ() не принимает на вход динамические массивы. Поэтому воспользуемся хитрым приемом. Во-первых, просто сравним BYROW() со значением 5…
1 |
ABS(BYROW())>=5 |
… вернет массив из 10 значений ИСТИНА/ЛОЖЬ.
Во-вторых, превратим логические значения ИСТИНА/ЛОЖЬ в 1/0 поставив в начале два знака минус…
1 |
--ABS(BYROW())>=5 |
Теперь у нас массив из 10 значений 1/0, которые можно суммировать…
1 |
СУММ(--ABS(BYROW())>=5) |
Вуаля!
Вот наша финальная мегаформула
1 2 |
=СУММ(--(ABS(BYROW(ИНДЕКС(СОРТПО(ПОСЛЕД(20);СЛМАССИВ(20;;0;1;ЛОЖЬ);1); ПОСЛЕД(10;2));LAMBDA(row;ИНДЕКС(row;1)-ИНДЕКС(row;2))))>=5)) |
Генерация большого числа значений
Можно воспользоваться недокументированным свойством инструмента Таблица данных. В ячейке А2 создайте массив последовательных натуральных чисел от 1 до 1000…
1 |
=ПОСЛЕД(1000) |
Он нам нужен для удобства задания диапазона повторений мегаформулы. Саму формулу поместите в ячейку В1. Выделите диапазон А1:В1001, пройдите Данные –> Анализ «что-если» –> Таблица данных. В окне Таблица данных поставьте курсор в поле Подставлять значения по строкам в:, кликните на любое свободное поле. Нам не нужна какая-то зависимость, мы просто хотим 1000 раз повторить формулу из В1:
Рис. 9. Задание 1000 повторений с помощью Таблицы данных
В столбце В появится 1000 независимых реализаций формулы. А поскольку формула в В1 основана на случайных числах, то и результаты будут случайными.
Теперь мы можем рассчитать среднее и стандартную ошибку, а также построить диаграмму рассеяния результатов моделирования:
Рис. 10. Статистика и диаграмма рассеяния
Как можно интерпретировать статистику? С вероятностью 95% истинное среднее лежит в диапазоне 6,365±0,098. Т.е. матчей между командами, которые расположены в турнирной таблице на 5 и более строчек выше, в среднем в одном туре будет 6,4. Приблизительно в половине туров таких матчей будет 6 или 7.
В сезоне 2023/24 таких матчей было 235 или в среднем 6,2 за тур.
Что касается ИИ-помощников. Пока ChatGPT мне представляется удобнее и полезнее.
После сеанса я проверил, сколько кредитов Capilot потребили мои вопросы. Относительно много:
Рис. 11. Использованные кредиты Capilot
Т.е. 15 таких простеньких сеансов и бесплатная порция кредитов будет израсходована.