Перейти к содержимому

Capilot в Office 365

Открываю на днях 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 (без знака равно). Получим

Далее подставляем текст формул из ячеек вместо ссылок AQ1# и AR1#, и т.д. На текущий момент мегаформула имеет вид:

Построчная разность значений

Для дальнейшего анализа я планирую использовать функцию BYROW(массив, функция). Напомню, что BYROW предназначена для построчных действий с массивами. Т.е., на вход BYROW берет массив n*m, а возвращает массив n*1, выполняя с каждой строкой действие, указанное во втором аргументе – функция. Но вот незадача: стандартной функции для нахождения разности BYROW не предлагает:

Рис. 6. В функции BYROW нет построчной разности

Зато есть LAMBDA. Сходу я также затрудняюсь написать код для LAMBDA, отсюда второй вопрос Capilot:

Рис. 7. Вопросы Capilot, как написать LAMBDA

На этот раз Capilot не справился. Но… есть проверенный ChatGPT:

Рис. 8. Функция LAMBDA в ChatGPT

Единственная неточность, которую допустил ChatGPT – использование запятой в качестве разделителя, вместо точки с запятой.

В итоге я нашел построчную разность с помощью формулы

Эта формула возвращает массив 10х1 с построчной разностью рейтингов двух команд, выбранных случайным образом.

Число пар с разницей по модулю ≥ 5

Сначала нужно взять по модулю значения массива BYROW(), а затем… нет не воспользоваться функцией СЧЁТЗ(). К сожалению, функция СЧЁТЗ() такая старая, что в MS не стали ее переписывать для работы с динамическими массивами. Так что СЧЁТЗ() не принимает на вход динамические массивы. Поэтому воспользуемся хитрым приемом. Во-первых, просто сравним BYROW() со значением 5…

… вернет массив из 10 значений ИСТИНА/ЛОЖЬ.

Во-вторых, превратим логические значения ИСТИНА/ЛОЖЬ в 1/0 поставив в начале два знака минус…

Теперь у нас массив из 10 значений 1/0, которые можно суммировать…

Вуаля!

Вот наша финальная мегаформула

Генерация большого числа значений

Можно воспользоваться недокументированным свойством инструмента Таблица данных. В ячейке А2 создайте массив последовательных натуральных чисел от 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 таких простеньких сеансов и бесплатная порция кредитов будет израсходована.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *