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

Почему при абсолютно равных результатах мальчику и девочке присудили разные разряды? Оказывается, с 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 в формуле на дату и как вводить в ячейку из даты рождения только год в формате даты, чтобы использовать формулу РАЗНДАТ, СМ. НИЖЕ).

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

UPD от 30.11.2018

Внезапно ребенок заняла первое место на Первенстве города, и по формуле получается 1 спортивный разряд. Но в формуле я не учла требования, что 1 и 2 спортивные разряды могут присуждаться только с 12 лет (вернее, с года, когда должно исполниться 12), по факту ребенку присудили за 1 место третий разряд, второе и третье места получили по второму разряду:((

Таким образом, формула принимает следующий вид (добавляем условие, чтобы разница между 2018 годом и годом рождения была больше 11 лет):

=ЕСЛИ(ЕПУСТО(A3);" ";ЕСЛИ(И(A3<$J$39+1;(2018-C3)>11);$G$39;ЕСЛИ(И(A3<$J$40+1;(2018-C3)>11);$G$40;ЕСЛИ(A3<$J$41+1;$G$41;ЕСЛИ(A3<$J$42+1;$G$42;ЕСЛИ(A3<$J$43+1;$G$43;ЕСЛИ(A3<$J$44+1;$G$44;" ")))))))

Создать копию файла с исправленной формулой

UPD. Дошло, как рассчитать разницу между годом текущим и годом рождения:

вставляем в формулу расчет: ГОД(СЕГОДНЯ())-С3, где С3 - адрес ячейки с годом рождения, получаем разницу между 2018 годом (текущий год на сегодня) и годом рождения спортсмена.
(Функция РАЗНДАТ не работает, вероятно, потому что указаны только года - неполные даты)

Теперь основная формула выглядит так:

=ЕСЛИ(ЕПУСТО(A3);" ";ЕСЛИ(И(A3<$O$42+1;(ГОД(СЕГОДНЯ())-C3)>11);$L$42;
ЕСЛИ(И(A3<$O$43+1;(2018-C3)>11);$L$43;ЕСЛИ(A3<$O$44+1;$L$44;
ЕСЛИ(A3<$O$45+1;$L$45;ЕСЛИ(A3<$O$46+1;$L$46;ЕСЛИ(A3<$O$47+1;$L$47;" ")))))))

Создать копию файла с исправленной формулой (см. лист "Разница годов")