Уважаемые коллеги, с вашего позволения хочу опубликовать на Форуме некоторые из своих старинных упражнений в 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 год:
Quote
15.3. В случае равенства очков у двух или более команд места команд (кроме первого) в текущей и итоговой таблице Чемпионата определяются: - по наибольшему числу побед во всех Матчах Чемпионата; - по лучшей разности забитых и пропущенных мячей во всех Матчах; - по результатам игр между собой (число очков, число побед, разность забитых и пропущенных мячей, число забитых мячей, число забитых мячей на чужом поле); - по наибольшему числу забитых мячей во всех Матчах; - по наибольшему числу мячей, забитых на чужих полях во всех Матчах. При равенстве всех этих показателей места команд определяются жребием.
Несколько слов о шахматке на листе "Положение". Если результат матча введен в колонках I и J на листе "Матчи", то в шахматке на листе "Положение" результат отображается в виде I:J в строке хозяина и зеркально в виде (J:I) в строке гостя. Если ячейки в колонках I и J пусты, то для данного матча в шахматке отображается номер тура предстоящего матча, например, 29т для хозяина и (29т) для гостя. Первая из двух ячеек в шахматке для каждой пары команд соответствует их матчу в 1-м круге, вторая - в 2-м.
Многие промежуточные формулы на различных листах находятся в скрытых колонках и строках - при необходимости вы их легко откроете.
Этими табличками я вёл чемпионаты 2005 и 2006 годов. Вот как-то так...
С удовольствием отвечу на возможные вопросы (если вспомню ).
P.S. Чуть позже я добавлю еще одну табличку одного забавного чемпионата - чемпионата нашего IT-отдела по настольному хоккею. Но не просто забавы ради, а из-за того, что в ней мне удалось реализовать пункт "ЭТОГО НЕТ" (см. выше в показателях рейтинга)
Уважаемые коллеги, с вашего позволения хочу опубликовать на Форуме некоторые из своих старинных упражнений в 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 год:
Quote
15.3. В случае равенства очков у двух или более команд места команд (кроме первого) в текущей и итоговой таблице Чемпионата определяются: - по наибольшему числу побед во всех Матчах Чемпионата; - по лучшей разности забитых и пропущенных мячей во всех Матчах; - по результатам игр между собой (число очков, число побед, разность забитых и пропущенных мячей, число забитых мячей, число забитых мячей на чужом поле); - по наибольшему числу забитых мячей во всех Матчах; - по наибольшему числу мячей, забитых на чужих полях во всех Матчах. При равенстве всех этих показателей места команд определяются жребием.
Несколько слов о шахматке на листе "Положение". Если результат матча введен в колонках I и J на листе "Матчи", то в шахматке на листе "Положение" результат отображается в виде I:J в строке хозяина и зеркально в виде (J:I) в строке гостя. Если ячейки в колонках I и J пусты, то для данного матча в шахматке отображается номер тура предстоящего матча, например, 29т для хозяина и (29т) для гостя. Первая из двух ячеек в шахматке для каждой пары команд соответствует их матчу в 1-м круге, вторая - в 2-м.
Многие промежуточные формулы на различных листах находятся в скрытых колонках и строках - при необходимости вы их легко откроете.
Этими табличками я вёл чемпионаты 2005 и 2006 годов. Вот как-то так...
С удовольствием отвечу на возможные вопросы (если вспомню ).
P.S. Чуть позже я добавлю еще одну табличку одного забавного чемпионата - чемпионата нашего IT-отдела по настольному хоккею. Но не просто забавы ради, а из-за того, что в ней мне удалось реализовать пункт "ЭТОГО НЕТ" (см. выше в показателях рейтинга)Gustav
Великолепно, был уверен, что без макросов такое невозможно. Спасибо за это! Хотелось бы увидеть, как во взрослом экселе такое можно слепить с использованием функции РАНГ
Великолепно, был уверен, что без макросов такое невозможно. Спасибо за это! Хотелось бы увидеть, как во взрослом экселе такое можно слепить с использованием функции РАНГshtiv
Чуть позже я добавлю еще одну табличку одного забавного чемпионата - чемпионата нашего IT-отдела по настольному хоккею. Но не просто забавы ради, а из-за того, что в ней мне удалось реализовать пункт "ЭТОГО НЕТ" (см. выше в показателях рейтинга)
Quote (shtiv)
Хотелось бы увидеть, как во взрослом экселе такое можно слепить с использованием функции РАНГ
Спасибо за проявленный интерес к теме. Да, пора-пора, обещал же еще летом. На самом деле оказалось всё не так страшно. Так обычно и бывает: кажется - ого-го, а начнешь делать - всё очень даже гуманно.
Итак, по поводу мини-чемпионатов среди команд, набравших равное количество очков. Оказалось, что надо к основной шахматке добавить еще одну, в которой отобразить только матчи среди команд с одинаковыми очками. По ней посчитать показатели и добавить в общую формулу ранга (на соответствующих позициях).
Эта дополнительная шахматка будет общей и единой (и вот в это не сразу верится!) для всех команд, имеющих равные очки с еще какими-нибудь командами. И неважно, какие равноочковые группы встретятся в ней. В примере из файла в дополнительную шахматку вынесены только матчи между командами, имеющими по 8 очков (4 команды) и по 6 очков (2 команды). Поначалу же казалось, что для каждой равноочковой группы нужно завести по отдельной таблице (и даже были громоздкие попытки реализации).
В общем, заглядывайте внутрь файла - и всё поймете в деталях.
К слову сказать, формулу для ранга тоже можно не делать такой дикой, с большим количеством десятичных разрядов (у меня-то - еще чуть-чуть и кончатся 16 значих цифр Excel'я). Можно посчитать ранги отдельно по каждой колонке (это будут числа от 1 до 10-20 или сколько там надо в конкретном турнире, думаю, всяко меньше 100), после чего собрать при помощи значительно меньших степеней 10.
Поэтому правило нашего смешного чемпионата по хоккею:
Quote
В случае равенства очков у двух и более игроков на круговом этапе соревнований преимущество (более высокое место) имеет игрок: - набравший наибольшее количество очков во всех матчах между этими игроками (в их "мини-турнире" внутри чемпионата); - имеющий лучшую разницу забитых и пропущенных шайб во всех матчах между этими игроками (в их "мини-турнире" внутри чемпионата); - имеющий лучшую разницу забитых и пропущенных шайб во всех матчах всего чемпионата; - имеющий наибольшее число побед во всех матчах всего чемпионата; - забросивший наибольшее количество шайб во всех матчах всего чемпионата; - при равенстве всех предыдущих показателей, места игроков определяются жребием.
можно было бы воплотить так с помощью степеней 100:
[vba]
Code
= 100^6 * ранг по очкам между всеми + 100^5 * ранг по очкам между собой + 100^4 * ранг по разностям между собой + 100^3 * ранг по разностям между всеми + 100^2 * ранг по победам между всеми + 100^1 * ранг по наибольшим голам между всеми + 100^0 (т.е.1) * ранг по случайным числам (имитатор жребия)
[/vba]
Или даже при помощи степеней числа команд-участниц (или чуть больше). В этом случае ранги визуально не просматриваются внутри значения общего ранга, как при степенях 100 или как в моей таблице (там прямо видны невооруженным глазом и очки, и разницы), но зато общие значения рангов значительно меньше. Например, для наших 12 участников-хоккеистов можно было бы взять число 20 (как "несколько больше 12"):
[vba]
Code
= 20^6 * ранг по очкам между всеми + 20^5 * ранг по очкам между собой ... + 20^1 * ранг по наибольшим голам между всеми + + 20^0 (т.е.1) * ранг по случайным числам (имитатор жребия)
[/vba]
Quote (Gustav)
Чуть позже я добавлю еще одну табличку одного забавного чемпионата - чемпионата нашего IT-отдела по настольному хоккею. Но не просто забавы ради, а из-за того, что в ней мне удалось реализовать пункт "ЭТОГО НЕТ" (см. выше в показателях рейтинга)
Quote (shtiv)
Хотелось бы увидеть, как во взрослом экселе такое можно слепить с использованием функции РАНГ
Спасибо за проявленный интерес к теме. Да, пора-пора, обещал же еще летом. На самом деле оказалось всё не так страшно. Так обычно и бывает: кажется - ого-го, а начнешь делать - всё очень даже гуманно.
Итак, по поводу мини-чемпионатов среди команд, набравших равное количество очков. Оказалось, что надо к основной шахматке добавить еще одну, в которой отобразить только матчи среди команд с одинаковыми очками. По ней посчитать показатели и добавить в общую формулу ранга (на соответствующих позициях).
Эта дополнительная шахматка будет общей и единой (и вот в это не сразу верится!) для всех команд, имеющих равные очки с еще какими-нибудь командами. И неважно, какие равноочковые группы встретятся в ней. В примере из файла в дополнительную шахматку вынесены только матчи между командами, имеющими по 8 очков (4 команды) и по 6 очков (2 команды). Поначалу же казалось, что для каждой равноочковой группы нужно завести по отдельной таблице (и даже были громоздкие попытки реализации).
В общем, заглядывайте внутрь файла - и всё поймете в деталях.
К слову сказать, формулу для ранга тоже можно не делать такой дикой, с большим количеством десятичных разрядов (у меня-то - еще чуть-чуть и кончатся 16 значих цифр Excel'я). Можно посчитать ранги отдельно по каждой колонке (это будут числа от 1 до 10-20 или сколько там надо в конкретном турнире, думаю, всяко меньше 100), после чего собрать при помощи значительно меньших степеней 10.
Поэтому правило нашего смешного чемпионата по хоккею:
Quote
В случае равенства очков у двух и более игроков на круговом этапе соревнований преимущество (более высокое место) имеет игрок: - набравший наибольшее количество очков во всех матчах между этими игроками (в их "мини-турнире" внутри чемпионата); - имеющий лучшую разницу забитых и пропущенных шайб во всех матчах между этими игроками (в их "мини-турнире" внутри чемпионата); - имеющий лучшую разницу забитых и пропущенных шайб во всех матчах всего чемпионата; - имеющий наибольшее число побед во всех матчах всего чемпионата; - забросивший наибольшее количество шайб во всех матчах всего чемпионата; - при равенстве всех предыдущих показателей, места игроков определяются жребием.
можно было бы воплотить так с помощью степеней 100:
[vba]
Code
= 100^6 * ранг по очкам между всеми + 100^5 * ранг по очкам между собой + 100^4 * ранг по разностям между собой + 100^3 * ранг по разностям между всеми + 100^2 * ранг по победам между всеми + 100^1 * ранг по наибольшим голам между всеми + 100^0 (т.е.1) * ранг по случайным числам (имитатор жребия)
[/vba]
Или даже при помощи степеней числа команд-участниц (или чуть больше). В этом случае ранги визуально не просматриваются внутри значения общего ранга, как при степенях 100 или как в моей таблице (там прямо видны невооруженным глазом и очки, и разницы), но зато общие значения рангов значительно меньше. Например, для наших 12 участников-хоккеистов можно было бы взять число 20 (как "несколько больше 12"):
[vba]
Code
= 20^6 * ранг по очкам между всеми + 20^5 * ранг по очкам между собой ... + 20^1 * ранг по наибольшим голам между всеми + + 20^0 (т.е.1) * ранг по случайным числам (имитатор жребия)
Спасибо за файлы. Для меня, как оказалось, все это большая школа. Я думал, что что то умею в экселе, но после этого оказалось, что совсем ничего. Вопросов море и не все по теме наверное. Речь идет о спортивной статистике, для простых людей Эксель это единственное доступное средство, которое доктор прописал. 1. Актуально для хоккея, в котором за сезон может быть сыграно до 8 очных встреч между отдельными командами. В таком случае зеркальная шахматка получается очень пухлой. Возможен ли переход к классике (домашние матчи по горизонтали, а выезд по вертикали - сокращаем вдвое, кол-во матчей в ячейке)?
2. Как создать рейтинг для НХЛ, КХЛ, где вмешивается доп. критерий - 1-е место в дивизионе? Общая схема на словах, хотя бы...
3. Самая большая проблема в экселе это не обработка информации. Здесь можно один раз заказать у понимающих людей и не важно, как оно будет сделано (с макросами или без), главное, что это в форме шаблона будет работать с любым новым чемпионатом...Главная проблема - это экспорт данных. При большом кол-ве данных, ручная сборка убивает много времени...Для каждого сайта это отдельная тема получается плюс еще не долго играющая (любой апгрейд источника может закончится обрывом связи). Нужен ли экселю в помощники на промежуточном этапе Экссес? Если располагаете таким опытом, можно ли общую схемку набросать, что меньше изобретать велосипедов (простыми словами желательно). P.S. (В вашем файлике по хоккею, в столбце CL чуток не туда ссылка смотрит )
Спасибо за файлы. Для меня, как оказалось, все это большая школа. Я думал, что что то умею в экселе, но после этого оказалось, что совсем ничего. Вопросов море и не все по теме наверное. Речь идет о спортивной статистике, для простых людей Эксель это единственное доступное средство, которое доктор прописал. 1. Актуально для хоккея, в котором за сезон может быть сыграно до 8 очных встреч между отдельными командами. В таком случае зеркальная шахматка получается очень пухлой. Возможен ли переход к классике (домашние матчи по горизонтали, а выезд по вертикали - сокращаем вдвое, кол-во матчей в ячейке)?
2. Как создать рейтинг для НХЛ, КХЛ, где вмешивается доп. критерий - 1-е место в дивизионе? Общая схема на словах, хотя бы...
3. Самая большая проблема в экселе это не обработка информации. Здесь можно один раз заказать у понимающих людей и не важно, как оно будет сделано (с макросами или без), главное, что это в форме шаблона будет работать с любым новым чемпионатом...Главная проблема - это экспорт данных. При большом кол-ве данных, ручная сборка убивает много времени...Для каждого сайта это отдельная тема получается плюс еще не долго играющая (любой апгрейд источника может закончится обрывом связи). Нужен ли экселю в помощники на промежуточном этапе Экссес? Если располагаете таким опытом, можно ли общую схемку набросать, что меньше изобретать велосипедов (простыми словами желательно). P.S. (В вашем файлике по хоккею, в столбце CL чуток не туда ссылка смотрит )shtiv
P.S. (В вашем файлике по хоккею, в столбце CL чуток не туда ссылка смотрит )
Ох, и правда! Спасибо за бдительность. Я даже перепроверил результаты. Хорошо, что места однозначно выявились на этапе очков между собой и до анализа разностей дело не дошло
Quote (shtiv)
1. Актуально для хоккея, в котором за сезон может быть сыграно до 8 очных встреч между отдельными командами. В таком случае зеркальная шахматка получается очень пухлой. Возможен ли переход к классике (домашние матчи по горизонтали, а выезд по вертикали - сокращаем вдвое, кол-во матчей в ячейке)?
Чисто технически сделать, конечно, можно. Но мне лично такой подход не очень по душе, хотя бы потому что он сложнее. Я бы делал обычную зеркальную. 8 кругов - так 8 кругов. Значит в одной "клетке" будет 8 ячеек - 2 строки x 4 столбца.
Quote (shtiv)
2. Как создать рейтинг для НХЛ, КХЛ, где вмешивается доп. критерий - 1-е место в дивизионе? Общая схема на словах, хотя бы...
Как и в случае выявления мест внутри равноочковых групп, нужны дополнительные разреженные ("полупустые") шахматки - по одной для каждого дивизиона (например, правее равноочковой шахматки), в которой бы отображались нужные матчи для определения мест в конкретном дивизионе. Фактически для каждой дивизионной шахматки придется повторить (почти весь) расчетный функционал самой главной шахматки. Потому что здесь, как и в случае равноочковости, мы имеем дело с мини-турнирами (по определенным признакам) внутри большого турнира.
Quote (shtiv)
3. Самая большая проблема в экселе это не обработка информации. Здесь можно один раз заказать у понимающих людей и не важно, как оно будет сделано (с макросами или без), главное, что это в форме шаблона будет работать с любым новым чемпионатом...Главная проблема - это экспорт данных. При большом кол-ве данных, ручная сборка убивает много времени...Для каждого сайта это отдельная тема получается плюс еще не долго играющая (любой апгрейд источника может закончится обрывом связи). Нужен ли экселю в помощники на промежуточном этапе Экссес? Если располагаете таким опытом, можно ли общую схемку набросать, что меньше изобретать велосипедов (простыми словами желательно).
Данные для двух футбольных чемпионатов я вносил вручную, тур за туром, натыкивая результаты матчей стилусом в КПК. Но я же их вносил не в саму шахматку (как в хоккее), а в несколько последовательных ячеек на другом листе, в которые можно было копипастить с какого-нибудь подходящего сайта. Но в принципе 8 матчей раз в неделю мне не сложно было и кропотливо друг за другом внести, получая данные в том числе и из обычной газеты или из радиоэфира, когда интернет-источники были недоступны (например, на даче). Другое дело, что расписание матчей я подливал заранее крупными блоками по несколько туров (можно было напрячься и сразу все 240 матчей прописать и дальше только цифры счёта вставлять). Про обрыв связи никогда не задумывался...
Quote (shtiv)
P.S. (В вашем файлике по хоккею, в столбце CL чуток не туда ссылка смотрит )
Ох, и правда! Спасибо за бдительность. Я даже перепроверил результаты. Хорошо, что места однозначно выявились на этапе очков между собой и до анализа разностей дело не дошло
Quote (shtiv)
1. Актуально для хоккея, в котором за сезон может быть сыграно до 8 очных встреч между отдельными командами. В таком случае зеркальная шахматка получается очень пухлой. Возможен ли переход к классике (домашние матчи по горизонтали, а выезд по вертикали - сокращаем вдвое, кол-во матчей в ячейке)?
Чисто технически сделать, конечно, можно. Но мне лично такой подход не очень по душе, хотя бы потому что он сложнее. Я бы делал обычную зеркальную. 8 кругов - так 8 кругов. Значит в одной "клетке" будет 8 ячеек - 2 строки x 4 столбца.
Quote (shtiv)
2. Как создать рейтинг для НХЛ, КХЛ, где вмешивается доп. критерий - 1-е место в дивизионе? Общая схема на словах, хотя бы...
Как и в случае выявления мест внутри равноочковых групп, нужны дополнительные разреженные ("полупустые") шахматки - по одной для каждого дивизиона (например, правее равноочковой шахматки), в которой бы отображались нужные матчи для определения мест в конкретном дивизионе. Фактически для каждой дивизионной шахматки придется повторить (почти весь) расчетный функционал самой главной шахматки. Потому что здесь, как и в случае равноочковости, мы имеем дело с мини-турнирами (по определенным признакам) внутри большого турнира.
Quote (shtiv)
3. Самая большая проблема в экселе это не обработка информации. Здесь можно один раз заказать у понимающих людей и не важно, как оно будет сделано (с макросами или без), главное, что это в форме шаблона будет работать с любым новым чемпионатом...Главная проблема - это экспорт данных. При большом кол-ве данных, ручная сборка убивает много времени...Для каждого сайта это отдельная тема получается плюс еще не долго играющая (любой апгрейд источника может закончится обрывом связи). Нужен ли экселю в помощники на промежуточном этапе Экссес? Если располагаете таким опытом, можно ли общую схемку набросать, что меньше изобретать велосипедов (простыми словами желательно).
Данные для двух футбольных чемпионатов я вносил вручную, тур за туром, натыкивая результаты матчей стилусом в КПК. Но я же их вносил не в саму шахматку (как в хоккее), а в несколько последовательных ячеек на другом листе, в которые можно было копипастить с какого-нибудь подходящего сайта. Но в принципе 8 матчей раз в неделю мне не сложно было и кропотливо друг за другом внести, получая данные в том числе и из обычной газеты или из радиоэфира, когда интернет-источники были недоступны (например, на даче). Другое дело, что расписание матчей я подливал заранее крупными блоками по несколько туров (можно было напрячься и сразу все 240 матчей прописать и дальше только цифры счёта вставлять). Про обрыв связи никогда не задумывался... Gustav
Здравствуйте! Помогите пожалуйста начинающему в похожей задаче Нужно сделать вот такое задание Задача 1 . Создать в листе « за 3 сезона » файла 1.xlsx рейтинги для команд : • Рейтинг количеству побед за 3 сезона • Максимальное количество ничейных матчей в сезоне • Рейтинг наименьшего количества поражений в сезоне • Лидеры в категории " набранные очки " • " Лучшее нападение " : команды забивают наибольшее количество голов в матче • " Лучшая защита " : команды , пропускали наименьшее количество голов в матче • Лучшая " домашняя" команда • Лучшая команда на выезде • Рейтинг команд забивали в домашних матчах наибольшее количество раз • Список команд , которые играли только 1 сезон за последние 3 года • Список команд , которые выбыли из чемпионата 1 раз • Список команд , которые выбывали из чемпионата хотя бы 1 раз
Задача 2 . В каждом листе файла 1.xlsx рассчитать таблицу (сводная таблица ) , что даст ответ на вопрос : • Количество матчей с конкретным счетом ; • Вероятность появления матча с конкретным счетом (см. Параметры значение поля -> вкладка « Отображать значения как » -> Параметр « Отображать значения как » -> Доля общей суммы). Ответить на вопрос (с помощью информации из сводных таблиц) : • Чему равна статистика выигрышей в домашних матчах , при условии что команды не пропустила ни одного гола ? • Чему равна статистика выигрышей в выездных матчах , при условии что команды не пропустила ни одного гола ? • Чему равна статистика выигрышей в домашних матчах ? • Чему равна статистика выигрышей в выездных матчах? • Чему равна статистика игр с ничейным счетом ? • Какой самый популярный счет в этом году ( или 3 года) ?
Файл в приложении.
Здравствуйте! Помогите пожалуйста начинающему в похожей задаче Нужно сделать вот такое задание Задача 1 . Создать в листе « за 3 сезона » файла 1.xlsx рейтинги для команд : • Рейтинг количеству побед за 3 сезона • Максимальное количество ничейных матчей в сезоне • Рейтинг наименьшего количества поражений в сезоне • Лидеры в категории " набранные очки " • " Лучшее нападение " : команды забивают наибольшее количество голов в матче • " Лучшая защита " : команды , пропускали наименьшее количество голов в матче • Лучшая " домашняя" команда • Лучшая команда на выезде • Рейтинг команд забивали в домашних матчах наибольшее количество раз • Список команд , которые играли только 1 сезон за последние 3 года • Список команд , которые выбыли из чемпионата 1 раз • Список команд , которые выбывали из чемпионата хотя бы 1 раз
Задача 2 . В каждом листе файла 1.xlsx рассчитать таблицу (сводная таблица ) , что даст ответ на вопрос : • Количество матчей с конкретным счетом ; • Вероятность появления матча с конкретным счетом (см. Параметры значение поля -> вкладка « Отображать значения как » -> Параметр « Отображать значения как » -> Доля общей суммы). Ответить на вопрос (с помощью информации из сводных таблиц) : • Чему равна статистика выигрышей в домашних матчах , при условии что команды не пропустила ни одного гола ? • Чему равна статистика выигрышей в выездных матчах , при условии что команды не пропустила ни одного гола ? • Чему равна статистика выигрышей в домашних матчах ? • Чему равна статистика выигрышей в выездных матчах? • Чему равна статистика игр с ничейным счетом ? • Какой самый популярный счет в этом году ( или 3 года) ?
tgoncharenko, Вашему вопросу не место в Готовых решениях. Варианты: 1) я переношу вопрос в раздел РАБОТА/ФРИЛАНС (платные услуги), так как это не вопрос, а полноценное техзадание 2) Вы начинаете что-то делать сами и задаёте конкретные вопросы, если что-то не получается, в разделе ВОПРОСЫ ПО EXCEL
tgoncharenko, Вашему вопросу не место в Готовых решениях. Варианты: 1) я переношу вопрос в раздел РАБОТА/ФРИЛАНС (платные услуги), так как это не вопрос, а полноценное техзадание 2) Вы начинаете что-то делать сами и задаёте конкретные вопросы, если что-то не получается, в разделе ВОПРОСЫ ПО EXCELPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Ну как... разобраться в формулах и осуществить вставку дополнительных ячеек в нужных местах Изначально простое тиражирование таблицы, к сожалению, не закладывалось. Хотя иногда меня посещали мысли о создании макроса-мастера такой таблицы. Может быть, настал подходящий момент...
А на сколько команд Вам надо? И на сколько кругов?
Ну как... разобраться в формулах и осуществить вставку дополнительных ячеек в нужных местах Изначально простое тиражирование таблицы, к сожалению, не закладывалось. Хотя иногда меня посещали мысли о создании макроса-мастера такой таблицы. Может быть, настал подходящий момент...
А на сколько команд Вам надо? И на сколько кругов?Gustav
Ellochka, попробую что-нибудь предложить в течение недели. А какой вид спорта у Вас? Таблица изначально настроена на футбол с выигрышем 3 очка, ничьей 1 оч и поражением 0 оч.
Ellochka, попробую что-нибудь предложить в течение недели. А какой вид спорта у Вас? Таблица изначально настроена на футбол с выигрышем 3 очка, ничьей 1 оч и поражением 0 оч.Gustav
Gustav, Такая и нужна вид спорта футбол. Ваша таблица RFPL_Lite.xls, в принципе, меня полностью устраивает. Но было бы совсем идеально, если бы количество команд в ней было не меньше 30ти
Gustav, Такая и нужна вид спорта футбол. Ваша таблица RFPL_Lite.xls, в принципе, меня полностью устраивает. Но было бы совсем идеально, если бы количество команд в ней было не меньше 30ти 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 и далее "Все имена". Окончания имен диапазонов совпадают с названиями колонок, начала же - состоят из префиксов в зависимости от принадлежности к листу: "пол_", "мат_" и "ком_".
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
Gustav, клавиша F9 ситуацию не изменила. В колонках "О", "И", "В", "Н", " П" по прежнему нули. В параметрах вычислений галка стоит на слове "Автоматически".
Gustav, клавиша F9 ситуацию не изменила. В колонках "О", "И", "В", "Н", " П" по прежнему нули. В параметрах вычислений галка стоит на слове "Автоматически".Ellochka
Ellochka, да, какой-то мой косяк... Если сгенерировать таблицу на 16 команд и 2 круга, и потом взять все данные из файла RFPL_Lite (список команд и 240 матчей), то всё рисуется хорошо. Я именно так и тестировался. Но если из "Матчей" потом стереть хоть одну клеточку, то всё зануляется... Вот же ж! Ну, что, беру тайм-аут небольшой...
Ellochka, да, какой-то мой косяк... Если сгенерировать таблицу на 16 команд и 2 круга, и потом взять все данные из файла RFPL_Lite (список команд и 240 матчей), то всё рисуется хорошо. Я именно так и тестировался. Но если из "Матчей" потом стереть хоть одну клеточку, то всё зануляется... Вот же ж! Ну, что, беру тайм-аут небольшой...Gustav
Да, уже знаю. Дело в использовании символа номера № в названиях именованных диапазонов: Excel его проглатывает в качестве допустимого символа, а Google - нет.
Пилюля такова: в единственном модуле VBA в файле TT_Gener_2.xls заменить по Ctrl+H одним махом все символы № (русский номер) на N (латинское эн). Сможете сами проделать эту операцию? А то мне пока не хотелось бы только из-за этого выкладывать обновленный файл, хотелось бы еще кое-какие усовершенствования в него внести...
Да, уже знаю. Дело в использовании символа номера № в названиях именованных диапазонов: Excel его проглатывает в качестве допустимого символа, а Google - нет.
Пилюля такова: в единственном модуле VBA в файле TT_Gener_2.xls заменить по Ctrl+H одним махом все символы № (русский номер) на N (латинское эн). Сможете сами проделать эту операцию? А то мне пока не хотелось бы только из-за этого выкладывать обновленный файл, хотелось бы еще кое-какие усовершенствования в него внести...Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
). 
