Практическа задача Excel: Спортен турнир — IF, AND, OR, валидация на данни и условно форматиране с решение

Представяме ви практическа задача по Excel на тема „Ученически спортен турнир". Задачата проверява умения за валидиране на данни, логически функции (IF, AND, OR), условно форматиране и форматиране на таблица. Подходяща е за ученици в 11. и 12. клас при подготовка за ДЗИ по Информационни технологии.

🎥 Видео урок: Решение на задачата

Условие на задачата

Даден е файл turnir.xlsx, съдържащ лист „Опит" с три стойности: Няма, Редовно, Състезателен. Добавете нов лист с ime „Резултати" и изградете таблица, която автоматично определя кои ученици са допуснати до участие в турнира и в кой отбор ще играят.

1. Структура на таблицата

В лист „Резултати" въведете следните колони:

Числовият формат на колона D „Успех" да е с два знака след десетичната запетая.

2. Валидиране на данни (диапазон ред 2 до ред 20)

Колона C „Възраст" — само цели числа от 12 до 19. Error Alert → стил Stop.
Data → Data Validation → Whole number → between 12 and 19

Колона D „Успех" — само числа от 2,00 до 6,00. Error Alert → стил Stop. Добавете Input Message с подсказка.
Data → Data Validation → Decimal → between 2 and 6

Колона E „Отсъствия" — само цели числа от 0 до 50. Error Alert → стил Stop.
Data → Data Validation → Whole number → between 0 and 50

Колона F „Спортен опит" — падащ списък с данни от лист „Опит".
Data → Data Validation → List → изберете диапазона от лист „Опит"

Колона G „Медицинско разрешение" — падащ списък: Да, Не.
Data → Data Validation → List → въведете „Да,Не"

3. Данни за попълване

4. Колона H „Резултат" — IF, AND, OR

Ученикът е „Допуснат", ако едновременно:

• Възрастта (C) е от 14 до 18 години включително
• Отсъствията (E) са не повече от 10
• Медицинското разрешение (G) е „Да"
• Успехът (D) е поне 5,00 ИЛИ спортният опит (F) е „Състезателен"

Формулата за Excel 2016 (английска версия) — въведете в клетка H2:

=IF(AND(C2>=14;C2<=18;E2<=10;G2="Да";OR(D2>=5;F2="Състезателен"));"Допуснат";"Недопуснат")

Копирайте формулата надолу до H9.

5. Колона I „Отбор" — независимо условие

Ученикът е в „Основен отбор", ако:

• Спортният му опит (F) е „Състезателен"
• ИЛИ успехът (D) е поне 5,80 И отсъствията (E) са не повече от 3

Формулата — въведете в клетка I2:

=IF(OR(F2="Състезателен";AND(D2>=5,8;E2<=3));"Основен отбор";"Резервен отбор")

Копирайте формулата надолу до I9.

Важно: Отборът се определя за всички ученици — независимо от резултата в колона H. Двете колони са напълно независими!

6. Очаквани резултати

7. Условно форматиране

Home → Conditional Formatting → New Rule

Колона D „Успех":

• Стойност ≥ 5,50 → зелено запълване
• Стойност < 5,00 → червено запълване

Колона E „Отсъствия":

• Стойност > 10 → червен фон
• Стойност ≤ 5 → светлозелен фон

Колона H „Резултат":

• Клетката съдържа „Допуснат" → зелен цвят на текста
• Клетката съдържа „Недопуснат" → червен цвят на текста

Начин: Format only cells that contain → Specific Text → containing → „Допуснат"

Колона I „Отбор":

• Клетката съдържа „Основен отбор" → тъмносин цвят, удебелен

8. Форматиране на таблицата

• Маркирайте цялата таблица → Home → Borders → All Borders
• Удебелете заглавния ред (ред 1)
• Закрепете първия ред: View → Freeze Panes → Freeze Top Row

Анализ на граничните случаи

• Айлин Хасан (възраст 13) — Недопусната заради възраст под 14, но е в Основен отбор заради успех 5,80 и само 3 отсъствия
• Димитър Христов (успех 4,90) — Допуснат само благодарение на клона „Състезателен", въпреки успех под 5,00
• Невена Георгиева (успех 5,00; отсъствия 10) — Допусната — граничните стойности 5,00 и 10 са включени в условията
• Юсуф Керимов (12 отсъствия) — Недопуснат само заради отсъствия над 10
• Преслава и Кристиян — Недопуснати само заради „Не" в медицинско разрешение
• Сибел Рамадан (възраст 19) — Недопусната заради възраст над 18

Какво проверява задачата

Успяхте ли да решите задачата — и кой граничен случай ви изненада най-много? Споделете в коментарите!

Ресурсни файлове

⬇ Свали задачата и ресурсните файлове