Как составить таблицу результатов по играм

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

Хочу рассказать о некоторых нюансах, которые будут полезны при создании подобных электронных таблиц.

Корректный ввод времени

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

Для данного столбца необходимо задать правильный числовой формат, то есть чтобы и время отображалось в ячейке верно, и чтобы само значение ячейки оставалось числовым.

Основной причиной, почему в дальнейшем не получается работать с формулами как раз и является то, что время вводят фактически в текстовом формате.

Чтобы все работало как надо, проделаем следующие действия. Выделяем столбец (1), в который будет заноситься время и открываем окно Формат ячейки (2). Создадим пользовательский формат (3). В списке можем найти уже имеющийся подходящий вариант (4). В нем через запятую указываются миллисекунды. Если нужно, чтобы отображалось два знака после запятой, то откорректируем маску, добавив еще один ноль (5).

Теперь данные в ячейку необходимо вводить именно в таком формате.

Все готово для автоматизации расчета рейтинга.

Особенности формулы ранжирования

Различным вариантам подсчета рейтинга в Excel было уже посвящено несколько видео (видео 1, видео 2, видео 3). Сейчас же, основываясь на ранее подробно рассмотренной формуле, создам столбец, в котором будет рассчитываться место, занятое участником соревнований.

Как я уже упомянул, логика данной формулы была подробно рассмотрена в отдельном видео, поэтому не буду сейчас повторяться и подробно разъяснять алгоритм расчета. Единственным отличием данного варианта формулы от рассмотренной ранее будет знак в условии. Ранее я рассматривал рейтинг учащихся по среднему баллу, поэтому чем был выше средний балл, тем должен был быть выше и рейтинг. В нашей ситуации задача обратная – есть время и чем оно будет меньше, тем лучше результат и, соответственно, выше рейтинг. Именно поэтому изменяется и знак сравнения.

Ну и нельзя забывать, что данная формула является формулой массива. Очень важно заканчивать ввод таких формул не простым нажатием клавиши Enter, а сочетанием Ctrl + Shift + Enter. Только это сочетание клавиш даст понять Эксель, что он имеет дело с формулой массива. В результате в формулу автоматически добавятся фигурные скобки, которые являются атрибутом формулы массива.

Протягиваем формулу по диапазону и убеждаемся, что рейтинг рассчитывается верно.

Ну и можно воспользоваться условным форматированием, чтобы подсвечивать лидера.

Теперь лучшее время будет дополнительно выделяться цветом.

Ссылки на мои ресурсы по Excel

★ YouTube-канал по Excel и Word

★ Телеграм

★ Серия видеокурсов “Microsoft Excel Шаг за Шагом”

★ Авторские книги и курсы

По теме: методические разработки, презентации и конспекты

Тема урока: «Построение
шаблона для определения результатов
спортивных соревнований и сдачи
нормативов средствами табличного
процессора Мicrosoft
Excel»

Цели урока:

• Формировать умения
  и навыки применения стандартных функций
  при решении практических задач.

• Развивать представления
  об электронных таблицах, как инструменте
  для решения практических задач.

Задачи.

• Образовательные:
  формирование знаний и умений учащихся
  применять встроенные функции в
  электронных таблицах для решения
  поставленной задачи.

• Развивающие:
  развитие познавательного
  интереса к предмету; умение пользоваться
  ранее полученными знаниями.

• Воспитательные:
  воспитывать общую и информационную
  культуру, трудолюбие, усидчивость.

Ожидаемые результаты:

После этого
урока ученики смогут:

• Использовать
  стандартные функции для проведения
  более сложных вычислений в электронных
  таблицах.

Методы:

• информационный
  (словесный);

• наглядно-иллюстративный;
  

• практический.

Тип урока:
урок-практикум.

Оборудование:
мультимедийный
проектор, экран, компьютеры.

Программное
обеспечение:
MS Office PowerPoint, MS Office Excel.

Ход урока

1. Организационная
часть.

• приветствие;

• проверка отсутствующих.
  

2. Постановка цели
урока, актуализация знаний учебной
деятельности.

1) Учитель: Сегодня
мы продолжим изучение темы “Встроенные
функции”. Перед нами стоит задача
создать шаблон для определения
результатов спортивных соревнований
и сдачи нормативов средствами табличного
процессора Мicrosoft
Excel.

Необходимо построить
таблицу, в которую можно будет вводить
данные результатов соревнований по
бегу на 60 м и прыжкам в длину и видеть
результаты соревнования ( 1, 2, 3 место) и
выполнение нормативов по данному виду
спорта.

Чтобы протестировать
наш шаблон, результаты соревнований
будут вводиться с помощью случайного
числа.

2) Проверочный тест
(с обратной связью). Вопросы на слайдах.

1. Какие данные можно
   вводить в ячейку таблицы? (ответ:
   константы в виде числа или текста и
   формулы)

2. С какого знака
   начинается запись формулы в ячейку
   таблицы? (ответ: знак =).

3. Каким образом можно
   вставить в ячейку встроенную функцию?
   (ответ: в строке формул нажать на кнопку
   Вставка функции или нажать комбинацию
   клавиш Shift
   F3).

4. Как можно изменить
   формат числа, например два знака после
   запятой? (ответ: выделить ячейку и нажав
   правую клавишу мыши вызвать контекстное
   меню, выбрать команду «Формат ячеек»,
   на вкладке «Число» выбрать формат
   «числовой», задать количество знаков
   после запятой =2).

5. Как можно вводить
   адрес ячейки в формулу? (ответ: щелкая
   в ячейку с нужным адресом или набирая
   адрес с клавиатуры)

6. Если текст достаточно
   большой, как поместить его в заданную
   ширину ячейки? (ответ: (выделить ячейку
   и нажав правую клавишу мыши вызвать
   контекстное меню, выбрать команду
   «Формат ячеек», на вкладке «Выравнивание»
   выбрать «Переносить по словам»).

7. Когда применяется
   абсолютная адресация ячейки таблицы?
   (ответ: абсолютная адресация используется
   в том случае, когда нужно использовать
   значение, которое не будет меняться в
   процессе вычислений. Для того, чтобы
   задать ячейке абсолютный адрес,
   необходимо перед номером строки и
   номером столбца указать символ “$”
   либо нажать клавишу F4.)

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

3. Теперь рассмотрим
новые функции, которые будем использовать
в нашем шаблоне.

1. Функция
   СЛЧИС

Функция
возвращает равномерно распределенное
случайное вещественное число, которое
большее или равно 0 и меньше 1. Новое
случайное вещественное число возвращается
при каждом вычислении листа.

Чтобы
получить случайное вещественное число
в диапазоне между a и b, можно использовать
следующую формулу: =a +СЛЧИС()*(b-a)

1. Функция
   Ранг

Функция
возвращает ранг числа в списке чисел.
Ранг числа — это его величина относительно
других значений в списке. (Если
отсортировать список, то ранг числа
будет его позицией).

РАНГ(число;ссылка;порядок).

Число  
— число, для которого определяется
ранг., т.е результат кросса для конкретного
ученика ячейка B2.

Ссылка  
— массив или ссылка на список чисел. В
данном случае список всех результатов
кросса.

Так
как при копировании формулы, список для
всех участников соревнования остается
неизменным, т.е. не меняется при
копировании, для диапазона необходимо
задать абсолютную адресацию.

Порядок  
— число, определяющее способ упорядочения.

Если
значение аргумента «порядок» равно 0
или опущено, ранг числа определяется в
Microsoft Excel так, как если бы ссылка была
списком, отсортированным в порядке
убывания.

Если
значение аргумента «порядок» — любое
число, кроме нуля, то ранг числа
определяется в Microsoft Excel так, как если
бы ссылка была списком, отсортированным
в порядке возрастания.

1. Практическая работа.
   

Технология
выполнения задания:

1. Запустите
   программу
   Microsoft Excel 2007.

2. Откройте
   файл « Соревнования_фамилии».

3. В
   ячейку С1
   введите текст: «Результаты бега на 60
   метров»

4. В

   ячейку С2
   введите с клавиатуры формулу для
   результата кросса, используя функцию
   случайного числа. Отформатируйте
   значение в ячейке, как число с двумя
   знаками после запятой.

Результаты
кросса будут принимать значения от 8
до 11 сек, тогда формула будет выглядеть
так: =8+слчис()*3.

1. Выполните
   копирование формулы для всех учеников.

2. В
   ячейку
   D1
   введите текст: “Прыжки в длину”.

3. Самостоятельно.
   В ячейку D2
   введите
   с клавиатуры формулу для результата
   прыжков в длину, используя функцию
   случайного числа. Результаты прыжков
   в длину находятся в диапазоне от 1,4 до
   2,4 метров.

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

5. В

   ячейку E1
   введите текст: “место по результатам
   кросса”.

6. Д
   ля
   определения места в соревновании по
   бегу будем использовать функцию РАНГ
   В ячейку E2
   вводим функцию Ранг: команда Вставить
   функцию. Выбрать категорию «Статистические»

7. В

   окне Мастер функций выберите функцию
   -Ранг. Число это результат конкретного
   ученика (C2),
   ссылка диапазон ячеек со всеми
   результатами кросса (адрес должен быть
   абсолютным $C$2:$C$21,т.к.
   лучшим результатом кросса является
   наименьшее значение в списке, порядок
   будет любое число, кроме нуля, например
   1.

8. Формулу копируем
   для всех участников соревнования

9. В
   ячейку E1
   введите текст: “место по прыжкам в
   длину”.

10. Самостоятельно.
    Определите места в соревновании по
    прыжкам в длину

В
ячейку F2

вводим функцию Ранг.

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

1. Выполним условное
   форматирование для результатов столбца
   «место»

Выделим
диапазон ячеекE2:E21.

В
ыберите
команду «Условное форматирование», «
Правила выделения ячеек», «Равно».

У
станавливаем
значение «1», выбираем форматирование,
например, светло-красная заливка и
темно-красный цвет.

Т
акже
устанавливаем форматирования для
значений «2» и «3»

1. Использование
   функции ЕСЛИ.

В
ячейку J1вводим
текст «норматив по бегу, сек»

В
ячейку H2
вводим значение 8,8

В
ячейку K1вводим
текст «норматив по прыжкам, метр»

В
ячейку K2
вводим значение 1,9

Добавим
в ячейку G1
«сдача норматива бегу 60 м» и в ячейку
H1
«сдача норматива по прыжкам в длину»

1. В
   ячейку G2 вставим функцию ЕСЛИ

Напоминание!
Функция ЕСЛИ относится к категории
логические.

Возвращает
одно значение, если заданное условие
при вычислении дает значение ИСТИНА, и
другое значение, если ЛОЖЬ.

Функция
ЕСЛИ используется при проверке условий
для значений и формул.

Синтаксис:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

В

первом поле пишется логическое выражение.
Значение результата кросса должно быть
меньше или равно нормативу. Во втором
поле пишем «да», т.е. норматив выполнен,
в третьем поле пишем «нет».

САМОСТОЯТЕЛЬНО!
Аналогично определите, кто сдал норматив
по прыжкам в длину.

САМОСТОЯТЕЛЬНО!
Выполните условное форматирование для
столбцов G
и H.
Например, слова «yes»
отформатируйте красным цветом на желтом
фоне, а «no»
красным цветом на голубом фоне.

1. З
   ащита
   ячеек с формулами. Чтобы пользователь
   шаблона случайно не удалил формулы,
   оставим доступными для изменения ячейки
   таблицы, куда вводятся результаты
   соревнований.

В
ыделите
столбцы со значениями результатов
соревнования, правой клавишей мыши
вызываем контекстное меню и выбираем
команду «Формат ячеек», вкладка «Защита»
и убираем галочку «Защищаемая ячейка».
Теперь Защитим лист с таблицей.

На вкладке
«Рецензирование», выбрать команду
«Защитить лист», в появившемся окне,
Ок.

Шаблон готов.

1. Подведение итогов.

О
каких встроенных функциях электронных
таблиц Excel вы сегодня узнали? Когда можно
применять функцию РАНГ?

1. Домашнее
   задание. Придумать практическую задачу,
   где можно использовать функцию РАНГ?
   Составить таблицу.

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

Турнир по круговой системе является одной из популярных схем организации соревнований как в настольных логических играх, так и во многих других подвижных видах спорта.

Чтобы определить очередность игры и цвета фигур соперников (в логических играх)  используют специальные таблицы очередности игры, которые также иногда называются таблицами Бергера.

Предварительно участники турнира получают определенный номер. Затем, по соответствующей таблице, определяется участники с какими номерами встречаются друг с другом в каждом туре.

Участник, номер которого указан в таблице очередности игры первым, начинает партию. Если в турнире принимает участие нечетное количество участников, то цифра, стоящая в первом столбике вне скобок, показывает, что участник под данным номером свободен от игры в данном туре.

Турнир по круговой системе. 3 или 4 участника

Тур	Пары участников
1	1 — (4)	2 — 3
2	(4) — 3	1 — 2
3	2 — (4)	3 — 1

Турнир по круговой системе. 5 или 6 участников

Тур	Пары участников
1	1 — (6)	2 — 5	3 — 4
2	(6) — 4	5 — 3	1 — 2
3	2 — (6)	3 — 1	4 — 5
4	(6) — 5	1 — 4	2 — 3
5	3 — (6)	4 — 2	5 — 1

Турнир по круговой системе. 7 или 8 участников

Тур	Пары участников
1	1 — (8)	2 — 7	3 — 6	4 — 5
2	(8) — 5	6 — 4	7 — 3	1 — 2
3	2 — (8)	3 — 1	4 — 7	5 — 6
4	(8) — 6	7 — 5	1 — 4	2 — 3
5	3 — (8)	4 — 2	5 — 1	6 — 7
6	(8) — 7	1 — 6	2 — 5	3 — 4
7	4 — (8)	5 — 3	6 — 2	7 — 1

Турнир по круговой системе. 9 или 10 участников

Тур	Пары участников
1	1 — (10)	2 — 9	3 — 8	4 — 7	5 — 6
2	(10) — 6	7 — 5	8 — 4	9 — 3	1 — 2
3	2 — (10)	3 — 1	4 — 9	5 — 8	6 — 7
4	(10) — 7	8 — 6	9 — 5	1 — 4	2 — 3
5	3 — (10)	4 — 2	5 — 1	6 — 9	7 — 8
6	(10) — 8	9 — 7	1 — 6	2 — 5	3 — 4
7	4 — (10)	5 — 3	6 — 2	7 — 1	8 — 9
8	(10) — 9	1 — 8	2 — 7	3 — 6	4 — 5
9	5 — (10)	6 — 4	7 — 3	8 — 2	9 — 1

Турнир по круговой системе. 11 или 12 участников

Тур	Пары участников
1	1 — (12)	2 — 11	3 — 10	4 — 9	5 — 8	6 — 7
2	(12) — 7	8 — 6	9 — 5	10 — 4	11 — 3	1 — 2
3	2 — (12)	3 — 1	4 — 11	5 — 10	6 — 9	7 — 8
4	(12) — 8	9 — 7	10 — 6	11 — 5	1 — 4	2 — 3
5	3 — (12)	4 — 2	5 — 1	6 — 11	7 — 10	8 — 9
6	(12)- 9	10 — 8	11 — 7	1 — 6	2 — 5	3 — 4
7	4 — (12)	5 — 3	6 — 2	7 — 1	8 — 11	9 — 10
8	(12) — 10	11 — 9	1 — 8	2 — 7	3 — 6	4 -5
9	5 — (12)	6 — 4	7 — 3	8 — 2	9 — 1	10 — 11
10	(12) — 11	1 — 10	2 — 9	3 — 8	4 — 7	5 — 6
11	6 — (12)	7 — 5	8 — 4	9 — 3	10 — 2	11 — 1

Турнир по круговой системе. 13 или 14 участников

Тур	Пары участников
1	1 — (14)	2 — 13	3 — 12	4 — 11	5 — 10	6 — 9	7 — 8
2	(14) — 8	9 — 7	10 — 6	11 — 5	12 — 4	13 — 3	1 — 2
3	2 — (14)	3 — 1	4 — 13	5 — 12	6 — 11	7 — 10	8 — 9
4	(14) — 9	10 — 8	11 — 7	12 — 6	13 — 5	1 — 4	2 — 3
5	3 — (14)	4 — 2	5 — 1	6 — 13	7 — 12	8 — 11	9 — 10
6	(14) — 10	11 — 9	12 — 8	13 — 7	1 — 6	2 — 5	3 — 4
7	4 — (14)	5 — 3	6 — 2	7 — 1	8 — 13	9 — 12	10 — 11
8	(14) — 11	12 — 10	13 — 9	1 — 8	2 — 7	3 — 6	4 — 5
9	5 — (14)	6 — 4	7 — 3	8 — 2	9 — 1	10 — 13	11 — 12
10	(14) — 12	13 — 11	1 — 10	2 — 9	3 — 8	4 — 7	5 — 6
11	6 — (14)	7 — 5	8 — 4	9 — 3	10 — 2	11 — 1	12 — 13
12	(14) — 13	1 — 12	2 — 11	3 — 10	4 — 9	5 — 8	6 — 7
13	7 — (14)	8 — 6	9 — 5	10 — 4	11 — 3	12 — 2	13 — 1

Турнир по круговой системе. 15 или 16 участников

Тур	Пары участников
1	1 — (16)	2 — 15	3 — 14	4 — 13	5 — 12	6 — 11	7 — 10	8 — 9
2	(16) — 9	10 — 8	11 — 7	12 — 6	13 — 5	14 — 4	15 — 3	1 — 2
3	2 — (16)	3 — 1	4 — 15	5 — 14	6 — 13	7 — 12	8 — 11	9 — 10
4	(16) — 10	11 — 9	12 — 8	13 — 7	14 — 6	15 — 5	1 — 4	2 — 3
5	3 — (16)	4 — 2	5 — 1	6 — 15	7 — 14	8 — 13	9 — 12	10 — 11
6	(16) — 11	12 — 10	13 — 9	14 — 8	15 — 7	1 — 6	2 — 5	3 — 4
7	4 — (16)	5 — 3	6 — 2	7 — 1	8 — 15	9 — 14	10 — 13	11 — 12
8	(16) — 12	13 — 11	14 — 10	15 — 9	1 — 8	2 — 7	3 — 6	4 — 5
9	5 — (16)	6 — 4	7 — 3	8 — 2	9 — 1	10 — 15	11 — 14	12 — 13
10	(16) — 13	14 — 12	15 — 11	1 — 10	2 — 9	3 — 8	4 — 7	5 — 6
11	6 — (16)	7 — 5	8 — 4	9 — 3	10 — 2	11 — 1	12 — 15	13 — 14
12	(16) — 14	15 — 13	1 — 12	2 — 11	3 — 10	4 — 9	5 — 8	6 — 7
13	7 — (16)	8 — 6	9 — 5	10 — 4	11 — 3	12 — 2	13 — 1	14 — 15
14	(16) — 15	1 — 14	2 — 13	3 — 12	4 — 11	5 — 10	6 — 9	7 — 8
15	8 — (16)	9 — 7	10 — 6	11 — 5	12 — 4	13 — 3	14 — 2	15 — 1