Группа "Гости"



Добро пожаловать!
О нас
Образование в ДОУ
Наш опрос
Оцените наш детский сад

В какой дополнительной платной услуге нуждается Ваш ребенок?
Поделиться
Чат
500

Каталог статей

Главная » Статьи » Публикации » Из опыта работы Ермолик О.В.

Excel для скалолаза (расчет разряда) - дополняется

Что общего у АХР детского сада и скалолазания? Дети и необходимость всевозможных табличных расчетов.

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

Мне не удалось полностью рассчитать процесс, жду помощи в решении вопроса (welcome в комментарии или на почту adminer@dou17-spb.ru), буду весьма признательна за разъяснения.

Итак, рассмотрим только детей-подростков, т.е. с разрядами от "без разряда" и 1,2,3 юношеских до 1,2,3 взрослых разрядов (КМС, МС и прочих в ближайший год не рассматриваю), выступающих на Первенстве СПб (уровень соревнований влияет на порядок присуждения разрядов и званий).
Формула с сайта минспорта:

Требование (для всех дисциплин) - занять место, рассчитываемое по формуле:
I спортивный разряд: A = К1 + 0,8К2 + 0,4К3 + 0,2К4
II спортивный разряд: B = A + 0,2К3 + 0,4К4 + 0,2К5
III спортивный разряд: C = B + 0,2К4 + 0,4К5 + 0,3К6
I юношеский разряд: Д = C + 0,2К5 + 0,4К6 + 0,2К7
II юношеский разряд: Е = Д + 0,2К6 + 0,4К7 + 0,2К8
III юношеский разряд: М = Е + 0,2К7 + 0,4К8 + 0,3К9
Расшифровка:
A, B, C Д, Е, М - занятые места;
К0 - количество участников, принявших старт в виде программы;
К1 - количество МС и МСМК, занявших места в виде программы с 1 по N1, где N1=K0*0,75;
К2 - количество КМС, занявших места в виде программы с 1 по N2, где N2=K0*0,75;
К3 - количество участников I спортивного разряда, занявших места в виде программы с 1 по N3, где N3=K0*0,75;
К4 - количество участников II спортивного разряда, занявших места в виде программы с 1 по N4, где N4=K0*0,75;
К5 - количество участников III спортивного разряда, занявших места в виде программы с 1 по N5, где N5=K0*0,75;
К6 - количество участников I юношеского спортивного разряда или мужчин, женщин без спортивного разряда, занявших места в виде программы с 1 по N6, где N6=K0*0,75;
К7 - количество участников II юношеского спортивного разряда, занявших места в виде программы с 1 по N7, где N7=K0*0,75;
К8 - количество участников III юношеского спортивного разряда, занявших места в виде программы с 1 по N8, где N8=K0*0,75;
К9 - количество юношей, девушек (не старше 17 лет) без юношеского спортивного разряда, занявших места в виде программы с 1 по N9, где N9=K0*0,75.
Условия:
1. Результаты расчета занятых мест А, В, С, Д, Е, М, N1, N2, N3, N4, N5, N6, N7, N8, N9 округляются в меньшую сторону, цифры после запятой отбрасываются.

Скачать файл Excel

Открыть google-документ

Первый шаг: расчет К0 - количества участников, принявших старт в виде программы - рассчитывается одинаково в Excel и в Google по формуле =МАКС(A:A), т.е. участники пронумерованы, и мы берем максимальное значение в столбце А.

Далее высчитываем коэффициент N=K0*0,75 с условием, что результат округляется в меньшую сторону, цифры после запятой отбрасываются:
в Excel: =ОКРВНИЗ.МАТ(К0*0,75;1;0)
в Google: =FLOOR.MATH(К0*0,75;1;0)
Попутно переименовываем адреса ячеек:

Далее вычисляем коэффициенты К (начиная с К3, т.к. КМС и МС здесь не участвовали).
Возникает проблема выбора диапазона: мы видим, что N=36.

Сразу оговорю проблему: бывает, что среди участников есть выступающие "вне конкурса" - т.е., например, спортсмен из Вологды не может претендовать на место и разряд, выступая на Первенстве Санкт-Петербурга, но может участвовать для своего удовольствия и в таблице результатов будет располагаться на том месте, которое бы он занял, имея право на квалификацию, вместо номера места и разряда у него в таблице стоит "в/к", но производя расчеты в диапазоне, его данные (разряд до старта) попадают в формулу. 

По формуле =СЧЁТЕСЛИМН(ДВССЫЛ("D3:D" & ПОИСКПОЗ($B$55;A:A));"=" & $G55;ДВССЫЛ("A3:A" & ПОИСКПОЗ($B$55;A:A));"<>в/к") высчитывается количество стартующих с 1 разрядом в диапазоне D3:D, пока значение в столбце А не совпадет с N3, исключая "в/к" - вне квалификации). Аналогично рассчитываются все коэффициенты К.

Далее по разрядным требованиям рассчитываем, какое место участник должен занять для выполнения того или иного разряда:

Т.к. КМС и МС у нас не было, то формула сокращается. В данном примере для получения 1 взрослого разряда при заданном наборе участников необходимо занять место по формуле коэффициента А. Получаем 1,8. По условиям расчета полученный результат округляем в меньшую сторону, формат ячейки - 0 знаков после запятой.

Получаем, что на данных соревнованиях с данным набором участников для получения 1 разряда участник должен занять 1 место, для 2 разряда - не ниже 3 места, для 1 юношеского - не ниже 4 места, для 2 юношеского - не ниже 8 места, для 3 юношеского - не ниже 18 места. Участникам, занявшим 19 и далее места, разряд не присваивается.

Далее в списке участников вводим формулу на соответствие занятого места разряду:
если место участника меньше, чем требуемое для получения разряда +1, то =адрес ячейки с разрядом или значение разряда в кавычках для текстового или без для численного значения.

В формуле использованы и адреса, и имена ячеек.

При наличии КМС и МС среди стартующих в расчет коэффициента А добавляются коэффициенты К1 и К2:

 

Спасибо за помощь сообществу Google Документы и Диск .

Нерешенной осталась проблема учета возраста участников (см. выделенное фиолетовым в Расшифровке формулы), но для моей возрастной категории это пока не актуально:))

Дополнение:

Выше мы рассматривали результаты федерального Первенства (т.е. юношеские соревнования).

Рассмотрим результаты Чемпионата СПб, где нет деления по возрастам, но МС и КМС присуждаются с определенного возраста (МС выполняется с 15 лет, КМС - с 14 лет). Условия и требования смотрим на Листе1 гугл-документа.

Коэффициенты К0, N, К1-К9, А-М рассчитываются все по тем же формулам. Для определения места, которое необходимо занять для получения разряда КМС, используем формулу с учетом условий на рис. выше:
=ЕСЛИ(ИЛИ(C34+C33>14;C35>29);6;ЕСЛИ(ИЛИ(C34+C33>9;C35>19);4;
ЕСЛИ(ИЛИ(C34+C33>7;C35>14);3;ЕСЛИ(ИЛИ(C34+C33>4;C35>14);2;ЕСЛИ(ИЛИ(C35>9);1))))),
где С33 и С34 - кол-во участников МС и КМС, С35 - кол-во участников с 1 разрядом.

Сами разряды рассчитываются в столбце О по формуле:
=ЕСЛИ(ЕПУСТО(A3);" ";ЕСЛИ(И(A3<$I$41+1;(2018-C3)>13);$F$41;
ЕСЛИ(A3<$I$33+1;$F$33;ЕСЛИ(A3<$I$34+1;$F$34;ЕСЛИ(A3<$I$35+1;$F$35;
ЕСЛИ(A3<$I$36+1;$F$36;ЕСЛИ(A3<$I$37+1;$F$37;ЕСЛИ(A3<$I$38+1;$F$38;" ")))))))),
то есть, если участник в списке явился (занял какое-то место), выясняем соответствие занятого места для присуждения разряды, но учитывая, что КМС присуждается с 14 лет (разница между 2018 годом (на дату публикации) и годом рождения д.б. больше 13 лет - как заменить число 2018 в формуле на дату и как вводить в ячейку из даты рождения только год в формате даты, чтобы использовать формулу РАЗНДАТ, я не знаю).

Продолжение следует...

 

Категория: Из опыта работы Ермолик О.В. | Добавил: adminer6126 (09.04.2018)
Просмотров: 29 | Рейтинг: 5.0/1
Всего комментариев: 0
Имя *:
Email *:
Код *:
Вход на сайт
Поиск
Условия для детей
Родителям
Копилка
Отчеты
АХР
Наше творчество

Статистика

Онлайн всего: 1
Гостей: 1
Пользователей: 0
ЭО Ресурсы
 

 
 
 
 
 
 
 
 
 
 
Категории раздела
Из опыта работы Казаковой М.М. [4]Из опыта работы Торцевой Т.Б. [9]
Из опыта работы Балашовой Л.И. [18]Из опыта работы Риет И.Г. [2]
Из опыта работы Скуратовой М.А. [10]Из опыта работы Алексеевой И.Г. [1]
Из опыта работы Громовой Л.Н. [2]Из опыта работы Никитиной Т.В. [0]
Из опыта работы Неустроевой Т.В. [8]Из опыта работы Лютостанской А.Н. [1]
Из опыта работы Широковой О.В. [4]Из опыта работы Куадже Е.Е. [4]
Из опыта работы Рубаник Е.В. [1]Из опыта работы Тереховой В.В. [0]
Из опыта работы Лушниковой П.А. [2]Из опыта работы Чуйко Н.П. [4]
Из опыта работы Еуровой Н.В. [6]Из опыта работы Ермолик О.В. [31]
Из опыта работы Васильевой С.А. [1]Из опыта работы Шкурупей О.В. [0]
Из опыта работы Шилиной О.В. [0]Из опыта работы Мартыновой С.А. [4]
Из опыта работы Смирновой Е.А. [0]Из опыта работы Ипатовой И.Н. [0]
Из опыта работы Поляковой Е.Ю. [0]Из опыта работы Ивановой Н.П. [0]
Из опыта работы Алексеевой Е.В. [0]Из опыта работы Хрусталевой Е.Л. [0]
Из опыта работы Адурян Т.Э. [0]Из опыта работы Березовской В.В. [0]

Design by Olga Ermolick. © 2009-2018. All Rights Reserved
 Сайт ГБДОУ №17 Центрального района Санкт-Петербурга

Политика организации в отношении обработки персональных данных на сайте