Шаблон Турнирная Сетка Скачать

Мир MS Excel

Уважаемые коллеги, с вашего позволения хочу опубликовать на Форуме некоторые из своих старинных упражнений в Excel. Сегодня речь о турнирной таблице чемпионата. Не знаю, в правильном ли месте размещаю эту тему. Если нет, то "пусть старшие товарищи меня поправят" (с)

Итак. (гусляр ударил по струнам и начал. )

Весной 2005 года во время отпуска, играясь со своим, тогда еще новым, КПК, я "натыкал стилусом" в тамошнем покоцанном Excel'е решение задачки, к которой подбирался несколько лет. И было у меня стойкое внутреннее предубеждение, что без макросов VBA задачку эту не решить, поскольку в основе задачки - сортировка строк. Забегая вперед, скажу - удалось. Ибо задачка заработала на КПК, а, как известно, на КПК в Excel пока отсутствует какое-либо программирование. Там много чего важного пока отсутствует. Формулы массива, например, или функция RANK (РАНГ), которая должна была сыграть ведущую роль в моей задачке. И именно из-за ее отсутствия пришлось навернуть некий табличный алгоритм для ее имитации.

Что же это за задачка такая? Как следует из названия темы, это турнирная таблица, а конкретнее - турнирная таблица чемпионата России по футболу (премьер-лига, 2005 год). Причем, не просто итоговые "И В Н П М О" и сортировка команд по местам, а в дополнение к этому еще и самая настоящая двухкруговая шахматка со всеми результатами, автоматически-динамически сортирующаяся-перестраивающаяся после ввода результатов очередного тура (и даже одного матча).

Файлов два - RFPL.xls и RFPL_Lite.xls (после того как форум сгенерит им свои цифровые имена, я укажу их в скобках - не пришлось).

Чтобы отслеживать турнирное положение можно пользоваться любым из них.

ОСНОВНАЯ ИДЕЯ: на листе "Матчи" вводим результаты матчей, на листе "Положение" смотрим турнирную таблицу.

RFPL_Lite.xls - это облегченная версия RFPL.xls без функционала "Движение по турам", т.е. без формул в ячейках AS1:DY17 на листе "Положение" и без листа "(Туры)", на котором собственно и готовится информация для этих ячеек листа "Положение".

С другой стороны, в RFPL_Lite, которой я отдавал предпочтение в конце сезона 2005, есть лист "Урожайность" с общим количеством голов в турах, а также любопытная "встречаемость" вариантов счетов в чемпионате (самый популярный счёт был 1:0 - 59 матчей из 240 :-) )

В ячейки F2:J241 (5 колонок) листа "Матчи" по ходу чемпионата вводились результаты матчей: Дата, Х(озяин), Г(ость), г(олы)Х(озяина), г(олы)Г(остя). В колонки Х и Г вводятся коды команд, определенные на листе "(Команды)" - при этом в колонках "Хозяин" и "Гость" возникают полные названия команд (к сожалению, на КПК нельзя сделать выбор из списка - поэтому пришлось выкручиваться с одно-двух-трехбуквенными кодами).

Турнирное положение смотрим на листе "Положение". Каждый введенный результат на листе "Матчи" вызывает "подвижку" на листе "Положение".Можете поиграться самостоятельно, удалив результаты из "Матчей", сохранив их предварительно где-нибудь на другом листе, а затем последовательно, например, блоками по 8 матчей (по турам), вставлять обратно.

Блок, определяющий текущие места команд на листе "(Команды)" в ячейках AD1:AT18 (17 колонок). Причем, если бы на КПК имелась функция РАНГ, которая есть на "большом брате", то блок определения мест содержал бы только первую и последнюю из этих 17 колонок, а так - пришлось создавать решающую матрицу с использованием других доступных функций.

Рейтинг команд (колонка AD на листе "(Команды)") в основном определялся с учетом положений Регламента чемпионата 2005 для определения мест команд, а именно по следующим показателям (в порядке убывания приоритета):
- общее количество очков,
- общее количество побед,
- общая разница забитых и пропущенных мячей,
- ЭТОГО НЕТ: (далее, при равенстве предыдущих показателей у нескольких команд - их игры между собой - это как раз и не реализовано в таблице, КПК не потянет, хотя возможно, хоть и сложно, на "большом брате")
- общее количество забитых мячей,
- общее количество забитых мячей на чужом поле.
- порядковый номер на листе "Команды" - обычно это место в прошлогоднем чемпионате - для абсолютной уникальности "ранга" (как простой аналог жребия, без задействования механизма случайных чисел).

Для справки цитата из регламента чемпионата России по футболу среди команд клубов Премьер - Лиги на 2006 год:

200?'200px':''+(this.scrollHeight+5)+'px');">
15.3. В случае равенства очков у двух или более команд места команд (кроме первого) в текущей и итоговой таблице Чемпионата определяются:
- по наибольшему числу побед во всех Матчах Чемпионата;
- по лучшей разности забитых и пропущенных мячей во всех Матчах;
- по результатам игр между собой (число очков, число побед, разность забитых и пропущенных мячей, число забитых мячей, число забитых мячей на чужом поле);
- по наибольшему числу забитых мячей во всех Матчах;
- по наибольшему числу мячей, забитых на чужих полях во всех Матчах.
При равенстве всех этих показателей места команд определяются жребием.

Несколько слов о шахматке на листе "Положение". Если результат матча введен в колонках I и J на листе "Матчи", то в шахматке на листе "Положение" результат отображается в виде I:J в строке хозяина и зеркально в виде (J:I) в строке гостя. Если ячейки в колонках I и J пусты, то для данного матча в шахматке отображается номер тура предстоящего матча, например, 29т для хозяина и (29т) для гостя. Первая из двух ячеек в шахматке для каждой пары команд соответствует их матчу в 1-м круге, вторая - в 2-м.

Многие промежуточные формулы на различных листах находятся в скрытых колонках и строках - при необходимости вы их легко откроете.

Этими табличками я вёл чемпионаты 2005 и 2006 годов. Вот как-то так.

С удовольствием отвечу на возможные вопросы (если вспомню ).

P.S. Чуть позже я добавлю еще одну табличку одного забавного чемпионата - чемпионата нашего IT-отдела по настольному хоккею. Но не просто забавы ради, а из-за того, что в ней мне удалось реализовать пункт "ЭТОГО НЕТ" (см. выше в показателях рейтинга)

Сообщение отредактировал Gustav - Суббота, 09.06.2012, 18:21

200?'200px':''+(this.scrollHeight+5)+'px');"> Чуть позже я добавлю еще одну табличку одного забавного чемпионата - чемпионата нашего IT-отдела по настольному хоккею. Но не просто забавы ради, а из-за того, что в ней мне удалось реализовать пункт "ЭТОГО НЕТ" (см. выше в показателях рейтинга)

200?'200px':''+(this.scrollHeight+5)+'px');"> Хотелось бы увидеть, как во взрослом экселе такое можно слепить с использованием функции РАНГ

Спасибо за проявленный интерес к теме. Да, пора-пора, обещал же еще летом. На самом деле оказалось всё не так страшно. Так обычно и бывает: кажется - ого-го, а начнешь делать - всё очень даже гуманно.

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

Эта дополнительная шахматка будет общей и единой (и вот в это не сразу верится!) для всех команд, имеющих равные очки с еще какими-нибудь командами. И неважно, какие равноочковые группы встретятся в ней. В примере из файла в дополнительную шахматку вынесены только матчи между командами, имеющими по 8 очков (4 команды) и по 6 очков (2 команды). Поначалу же казалось, что для каждой равноочковой группы нужно завести по отдельной таблице (и даже были громоздкие попытки реализации).

В общем, заглядывайте внутрь файла - и всё поймете в деталях.

К слову сказать, формулу для ранга тоже можно не делать такой дикой, с большим количеством десятичных разрядов (у меня-то - еще чуть-чуть и кончатся 16 значих цифр Excel'я). Можно посчитать ранги отдельно по каждой колонке (это будут числа от 1 до 10-20 или сколько там надо в конкретном турнире, думаю, всяко меньше 100), после чего собрать при помощи значительно меньших степеней 10.

Поэтому правило нашего смешного чемпионата по хоккею:

200?'200px':''+(this.scrollHeight+5)+'px');"> В случае равенства очков у двух и более игроков на круговом этапе соревнований преимущество (более высокое место) имеет игрок:
- набравший наибольшее количество очков во всех матчах между этими игроками (в их "мини-турнире" внутри чемпионата);
- имеющий лучшую разницу забитых и пропущенных шайб во всех матчах между этими игроками (в их "мини-турнире" внутри чемпионата);
- имеющий лучшую разницу забитых и пропущенных шайб во всех матчах всего чемпионата;
- имеющий наибольшее число побед во всех матчах всего чемпионата;
- забросивший наибольшее количество шайб во всех матчах всего чемпионата;
- при равенстве всех предыдущих показателей, места игроков определяются жребием.

можно было бы воплотить так с помощью степеней 100:

200?'200px':''+(this.scrollHeight+5)+'px');"> = 100^6 * ранг по очкам между всеми
+ 100^5 * ранг по очкам между собой
+ 100^4 * ранг по разностям между собой
+ 100^3 * ранг по разностям между всеми
+ 100^2 * ранг по победам между всеми
+ 100^1 * ранг по наибольшим голам между всеми
+ 100^0 (т.е.1) * ранг по случайным числам (имитатор жребия)

Или даже при помощи степеней числа команд-участниц (или чуть больше). В этом случае ранги визуально не просматриваются внутри значения общего ранга, как при степенях 100 или как в моей таблице (там прямо видны невооруженным глазом и очки, и разницы), но зато общие значения рангов значительно меньше. Например, для наших 12 участников-хоккеистов можно было бы взять число 20 (как "несколько больше 12"):

200?'200px':''+(this.scrollHeight+5)+'px');"> = 20^6 * ранг по очкам между всеми
+ 20^5 * ранг по очкам между собой
.
+ 20^1 * ранг по наибольшим голам между всеми +
+ 20^0 (т.е.1) * ранг по случайным числам (имитатор жребия)

Сообщение отредактировал Gustav - Среда, 14.11.2012, 09:45

Ellochka. ну что, заходите пробовать!

Друзья, сегодня большой день в этой моей скромной теме. Родился параметрический инструмент - мастер-макрос - для создания в отдельном файле новой турнирной таблицы формата вышеобсуждаемой в этой теме таблицы "RFPL_Lite". Количество команд и кругов задается пользователем! Подробности - в прикрепленном к этому сообщению файлу TT_Gener.xls.

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

Что касается определения мест в турнирной таблице, то полностью и безоговорочно реализован алгоритм (наконец-то!) РФПЛ, включая и "разборки" по "мини-турнирам" между командами, набравшими одинаковое кол-во очков. Я назвал такие турниры равноочковыми группами, в макросе и заголовках колонок на эту тему можно будет видеть названия с суффиксом "_ро", т.е. "равные очки".

По поводу ведения таблицы. Вначале нужно ввести команды в колонки "Код" и "Название" на листе "Команды", а затем в процессе турнира построчно заполнять колонки "Дата","Х","Г","хГ","гГ" на листе "Матчи".

По поводу определения мест. Как я уже сказал, сейчас реализован алгоритм РФПЛ (он был одинаковым и 2005 году, и в 2014-м). С деталями определения рангов по различным показателям и окончательного ранга (места) команд можно ознакомиться в колонках ВС:CH на листе "Команды".

Обозначения ранговых колонок (на листе "Команды"):

* ранг10_О - число набранных очков общее (во всех матчах чемпионата)
* ранг20_В - число побед общее

* ранг30_О_ро - число набранных очков "между собой" (в мини-турнире равноочковой группы)
* ранг40_В_ро - число побед "между собой"
* ранг50_МР_ро - разница забитых и пропущенных мячей "между собой"
* ранг60_МЗ_ро - число забитых мячей "между собой"
* ранг70_МЗЧП_ро - число забитых мячей на чужом поле "между собой"

* ранг80_МР - разница забитых и пропущенных мячей общая
* ранг90_МЗ - число забитых мячей общее
* ранг100_МЗЧП - число забитых мячей на чужом поле общее

* ранг110_Ж - имитация жребия; предпочтение отдается команде, располагающейся выше (т.е. имеющей меньший номер) на листе "Команды"; аргумент: возможно, команды по номерам расположены в соответствии с занятыми местами в прошлом чемпионате, поэтому такой "жребий" как бы отражает прошлые заслуги. При желании можно самостоятельно изменить механизм "жребия", например, с помощью случайных чисел.

* РангОконч - результирующий ранг, определенный последовательным применением всех предшествующих рангов. В отличие от предшествующих рангов с порядком по возрастанию, окончательный ранг определяется с порядком по убыванию и в таком виде соответствует месту команды в турнирной таблице.

Несколько советов по управлению рангами. Турнир турниру - рознь, и регламент регламенту - тоже. В различных турнирах набор применяемых показателей при равных очках может отличаться как составом, так и последовательностью. Так, например, при ведении групповой таблицы на чемпионате мира по футболу отсутствует понятие чужого поля и соответственно показатель МЗЧП становится бессмысленным. Чтобы выключить этот ранг достаточно найти на листе "Команды" колонку "ранг100_МЗЧП" (или "ранг70_МЗЧП_ро") и прописать постоянное число "0" во все ячейки этой колонки.

Чтобы одним махом исключить из рассмотрения равноочковые ранги, надо на листе "Матчи" прописать значение ЛОЖЬ в колонку "РавныеОчки" (AL).

Наконец, возможна иная общая последовательность рангов. Так раньше довольно часто вторым показателем после набранного количества очков была общая разность забитых и пропущенных мячей (у нас это колонка "ранг80_МР"). И если нужно эту колонку сделать второй, то следует ее аккуратно переставить в место сразу после ранга очков (колонка "ранг10_О"). При возникновении трудностей - обращайтесь, помогу с этой операцией. В будущем попробую вынести последовательность применения рангов в настройки таблицы.

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

P.S. Поскольку генерируемые в новом файле формулы построены на использовании именованных диапазонов, то напоминаю, что вывести их адреса на свободное место любого рабочего листа можно, нажав F3 и далее "Все имена". Окончания имен диапазонов совпадают с названиями колонок, начала же - состоят из префиксов в зависимости от принадлежности к листу: "пол_", "мат_" и "ком_".

Сообщение отредактировал Gustav - Понедельник, 20.10.2014, 18:11

File-Portal

Поиск

Новые файлы