Автоматизация расчётов сотрудника гостиницы
Цели и задачи
- Цель по SMART: за 45 минут выполнить практическую работу в MS Excel по автоматизации расчётов сотрудника гостиницы, создав и проверив три листа с расчётами проживания, скидок, доплат и сводных показателей.
- Закрепить умение создавать таблицу с исходными данными о гостях, применять функции ЕСЛИ, СУММ, СРЗНАЧ и СЧЁТЕСЛИ.
- Отработать расчёт стоимости проживания, дополнительных услуг, скидок за длительность и доплаты за бронирование.
- Развивать навыки проверки формул, сортировки, условного форматирования и интерпретации полученных показателей.
- Формировать ответственное отношение к точности расчётов, необходимой в профессиональной деятельности в сфере туризма и гостеприимства.
Планируемые результаты
Личностные
- Осознают практическую значимость автоматизации расчётов для будущей работы в гостинице.
- Проявляют ответственность за корректность финансовых данных и соблюдение условий задания.
- Развивают готовность самостоятельно принимать решения при выборе исходных данных.
- Оценивают собственную готовность применять электронные таблицы в профессиональной ситуации.
Метапредметные
- Планируют последовательность создания листов, таблиц, формул и итоговых показателей.
- Работают с информацией: вводят, проверяют, сортируют и анализируют табличные данные.
- Выявляют ошибки в формулах и корректируют их по результатам самопроверки.
- Обсуждают в парах способы проверки расчётов и аргументируют выбор формулы.
- Используют цифровые инструменты для решения практической задачи, связанной с экономикой и профессиональной деятельностью.
Предметные
- Знать назначение функций ЕСЛИ, СУММ, СРЗНАЧ и СЧЁТЕСЛИ в электронных таблицах.
- Уметь рассчитывать цену номера, стоимость дополнительных услуг и итоговую сумму к оплате.
- Уметь применять формулы скидки за проживание от 7 суток и доплаты за бронирование.
- Уметь создавать условное форматирование и выполнять сортировку по заданному столбцу.
- Уметь создавать копии листов и формировать блок сводных показателей.
Универсальные учебные действия (УУД)
Личностные УУД
- Соотносят учебное задание с профессиональными обязанностями сотрудника гостиницы.
- Осознают необходимость точности при работе с ценами, скидками и итоговыми платежами.
- Проявляют самостоятельность при выборе данных для 14 гостей.
- Формируют установку на проверку результата перед передачей расчёта клиенту или руководителю.
Регулятивные УУД
- Формулируют последовательность действий при создании трёх рабочих листов.
- Контролируют соответствие формул условиям практической работы.
- Сопоставляют результат вычислений с исходными данными и находят несоответствия.
- Используют критерии оценивания для самооценки выполненной работы.
- Корректируют таблицу после взаимной проверки с одноклассником.
Познавательные УУД
- Выделяют условия, при которых применяются скидка, доплата и стоимость дополнительных услуг.
- Преобразуют текстовое описание профессиональной ситуации в формулы электронной таблицы.
- Анализируют взаимосвязь между количеством суток, ценой номера и итоговой суммой.
- Моделируют расчёт гостиничной услуги с помощью таблицы и логических функций.
- Сравнивают результаты до и после применения скидок и доплат.
Коммуникативные УУД
- Объясняют партнёру назначение формул и порядок проверки таблицы.
- Задают уточняющие вопросы о типе номера, количестве суток и условиях бронирования.
- Согласовывают критерии корректности расчёта в паре.
- Аргументированно сообщают о найденной ошибке и предлагают способ её исправления.
- Представляют один итоговый показатель и объясняют его профессиональный смысл.
Подготовка учителя к уроку
- Подготовить компьютерный класс с MS Excel или совместимым табличным процессором: один компьютер на каждого ученика или пару.
- Создать пустой файл для практической работы и проверить возможность копирования и переименования листов.
- Подготовить карточки с условиями расчётов и формулами: по одной на каждого ученика.
- Вывести на доску или экран структуру столбцов A–H, цены номеров и формулы для столбцов E, G и H.
- Подготовить памятку с функциями ЕСЛИ, СУММ, СРЗНАЧ и СЧЁТЕСЛИ.
- Распечатать лист самооценки и критерии оценивания — по одному экземпляру на каждого ученика.
- Подготовить карточки взаимопроверки — по одной на парту.
- Проверить, что на компьютерах доступны сортировка, условное форматирование и формулы с разделителем аргументов «;».
- Подготовить на экране профессиональный контекст: «Сотрудник гостиницы рассчитывает стоимость проживания 14 гостей и формирует итоговые показатели для администратора».
Ход урока
Этап 1. Организационный момент и мотивация (8 мин)
Цель этапа: включить учащихся в практическую деятельность и связать тему с будущей профессиональной работой.
Время | Действие учителя | Действие учеников |
|---|---|---|
1 мин | Учитель приветствует класс: "Здравствуйте, ребята. Сегодня мы работаем в роли сотрудников гостиницы. Представьте, что администратор передал вам данные о четырнадцати гостях и попросил быстро и без ошибок рассчитать стоимость проживания, услуг, скидок и доплат. Откройте электронные таблицы и проверьте готовность рабочего места." | Открывают табличный процессор, создают или открывают рабочую книгу, проверяют наличие мыши, клавиатуры и файла для сохранения. |
2 мин | Учитель показывает на экране схему «гость — номер — услуги — скидка — итоговая сумма» и задаёт вопрос: "Почему в гостинице нельзя рассчитывать такие суммы только устно или на калькуляторе? Какие ошибки должна предотвращать таблица?" Учитель фиксирует ответы: скорость, точность, повторяемость и возможность изменения исходных данных. | Обсуждают вопрос и формулируют ответы: "Таблица автоматически пересчитывает сумму", "можно проверить формулу", "удобно сравнить гостей и выручку". Записывают тему практической работы. |
Завершение этапа: учитель подводит итог: "Сегодня таблица станет рабочим инструментом гостиничного сотрудника. Сначала мы создадим базовые расчёты, затем добавим скидки и доплаты, а в конце подготовим показатели для анализа."
Этап 2. Актуализация знаний и инструктаж по заданию (9 мин)
Цель этапа: восстановить знания о формулах и уточнить порядок выполнения практической работы.
Время | Действие учителя | Действие учеников |
|---|---|---|
2 мин | Учитель выводит на экран таблицу соответствий: «Стандарт — 3500 рублей, Полулюкс — 5500 рублей, Люкс — 8000 рублей; дополнительные услуги — 1200 рублей в сутки». Учитель спрашивает: "Какая функция выберет цену в зависимости от типа номера? Какая функция посчитает сумму диапазона?" | Отвечают: "ЕСЛИ выбирает значение по условию", "СУММ складывает значения". Записывают цены и назначение функций в тетрадь. |
3 мин | Учитель объясняет порядок: "Лист 1 переименуйте в «Расчеты». Создайте столбцы от A до H и внесите 14 строк произвольных данных. Данные должны включать все три типа номеров, различное количество суток и гостей с дополнительными услугами. Формулы вводятся в первую строку расчёта и затем копируются вниз." | Создают лист «Расчеты», заголовки A–H и 14 строк исходных данных, проверяют наличие разных типов номеров и значений 0 или 1 в столбце F. |
Завершение этапа: учитель подводит итог: "Мы вспомнили назначение основных функций и условия исходной модели. Теперь переходим к созданию рабочего расчёта, где каждая формула должна отражать реальное правило гостиничного тарифа."
Этап 3. Выполнение задания 1: базовый расчёт проживания (18 мин)
Цель этапа: создать лист «Расчеты» и автоматизировать расчёт стоимости номера, дополнительных услуг и общей выручки.
Время | Действие учителя | Действие учеников |
|---|---|---|
4 мин | Учитель показывает на доске формулы: "В E2 определяем цену номера формулой =ЕСЛИ(C2="Стандарт";3500;ЕСЛИ(C2="Полулюкс";5500;8000)). В G2 записываем =ЕСЛИ(F2=1;1200;0). В H2 записываем =D2*(E2+G2). Обратите внимание: дополнительные услуги оплачиваются за каждые сутки, поэтому G2 входит в сумму внутри скобок." | Вводят формулы в E2, G2 и H2, копируют их до строки 15, проверяют несколько строк вручную и сравнивают результат с формулами. |
3 мин | Учитель обращается к классу: "Ниже таблицы создайте показатели. Суммарная выручка считается формулой =СУММ(H2:H15). Средняя стоимость проживания без учёта дополнительных услуг указана в задании формулой =СРЗНАЧ(D2:E15), а количество гостей с услугами — =СЧЁТЕСЛИ(F2:F15;1). Проверьте, что диапазоны включают все 14 строк." | Создают три строки показателей, вводят формулы, подписывают показатели и проверяют диапазоны. |
3 мин | Учитель даёт инструкцию: "Выделите строки таблицы и создайте условное форматирование: строки, где «Итого к оплате» больше 35 000 рублей, должны выделяться красным цветом шрифта. Затем выделите всю таблицу и отсортируйте её по столбцу «Итого к оплате» по убыванию." | Настраивают условное форматирование, сортируют всю таблицу по столбцу H, проверяют, что данные гостя не разделились между строками. |
Посмотрите план целиком
Зарегистрируйтесь — и откройте план урока по этой теме полностью: цели, ход урока и рефлексия по ФГОС.
Запись в тетрадях
Ученики записывают: «Цена номера зависит от типа номера; стоимость дополнительных услуг зависит от значения 0 или 1; итоговая сумма равна количеству суток, умноженному на сумму цены номера и стоимости услуг за сутки». Формулы: $H2=D2*(E2+G2)$; $G2=ЕСЛИ(F2=1;1200;0)$.
Завершение этапа: учитель подводит итог: "Базовый расчёт готов: таблица определяет тариф, учитывает услуги, считает оплату и позволяет увидеть наиболее дорогие заказы. Теперь усложним модель так, как это происходит в реальной гостиничной работе: добавим скидку за длительное проживание и доплату за бронирование."
Этап 4. Выполнение задания 2: скидки и доплаты (16 мин)
Цель этапа: скопировать базовую модель и добавить расчёты, зависящие от длительности проживания и наличия бронирования.
Время | Действие учителя | Действие учеников |
|---|---|---|
2 мин | Учитель объясняет: "Скопируйте лист «Расчеты» и назовите копию «СКИДКИ». Справа от столбца H добавьте столбцы I, J, K и L. В I укажите произвольное значение «да» или «нет» для каждого гостя. В I фиксируется наличие брони, а не её стоимость." | Копируют лист, переименовывают его, добавляют заголовки: «Бронь», «Скидка за длительность», «Доплата за бронь», «Итоговая сумма». Заполняют I2:I15 значениями «да» или «нет». |
4 мин | Учитель демонстрирует формулы: "В J2 скидка считается так: =ЕСЛИ(D2>=7;15%*(D2*E2);0). В K2 доплата за бронь: =ЕСЛИ(I2="да";10%*(D2*E2);0). В L2 итоговая сумма: =H2-J2+K2. Скидка и доплата рассчитываются только от стоимости проживания без дополнительных услуг." | Вводят формулы в J2, K2 и L2, копируют их до строки 15. Проверяют строку с проживанием 7 суток и строку с бронированием «да». |
3 мин | Учитель задаёт вопрос: "Что произойдёт со скидкой, если гость проживает 6 суток? А если 7? Почему дополнительные услуги не входят в основание скидки?" Учитель просит выбрать одну строку и объяснить расчёт партнёру. | Отвечают: "При 6 сутках скидка равна нулю, при 7 — составляет 15 процентов от стоимости номера за все сутки". В паре проговаривают порядок расчёта одной строки и находят значения J, K и L. |
Эталон решения
Для гостя со стандартным номером, проживанием 7 суток, дополнительными услугами и бронированием «да»: цена номера $E=3500$; услуги за сутки $G=1200$; базовая сумма $H=7*(3500+1200)=32900$; скидка $J=15\%*(7*3500)=3675$; доплата $K=10\%*(7*3500)=2450$; итоговая сумма $L=32900-3675+2450=31675$ рублей.
Завершение этапа: учитель подводит итог: "Мы построили более реалистичную модель расчёта: длительное проживание уменьшает сумму, а наличие бронирования добавляет плату. Осталось сформировать показатели и визуально выделить ситуации, важные для администратора."
Этап 5. Межпредметная практическая задача: показатели гостиницы (12 мин)
Цель этапа: применить навыки информатики для решения профессиональной задачи, связанной с математикой, экономикой и организацией гостиничного сервиса.
Время | Действие учителя | Действие учеников |
|---|---|---|
2 мин | Учитель предлагает контекст: "Администратору нужно быстро увидеть заказы свыше 50 000 рублей и гостей, проживающих не менее недели. Скопируйте лист «СКИДКИ», назовите его «Показатели». Ниже основной таблицы, начиная со строки 20, в столбцах B и C создайте блок сводных показателей." | Копируют лист, переименовывают его, создают в B20:C24 подписи и значения для суммарной выручки, среднего показателя, количества гостей с услугами и других выбранных итогов. |
3 мин | Учитель говорит: "Выделите столбец L и задайте правило: значения больше 50 000 рублей должны иметь светло-зелёную заливку и тёмно-зелёный шрифт. Затем настройте правило для строк, где количество суток в D не меньше 7: жёлтая заливка и красный шрифт. Проверьте, что форматирование помогает увидеть, а не скрывает информацию." | Настраивают два правила условного форматирования, проверяют выделенные строки и сравнивают их с условиями. |
2 мин | Учитель организует мини-обсуждение: "Какие показатели будут полезны администратору? Как таблица помогает принять решение о загрузке и выручке?" Учитель связывает задание с математикой и экономикой: проценты, среднее значение, выручка и тарифная политика. | Формулируют выводы: "Количество гостей с услугами показывает востребованность сервиса", "суммарная выручка показывает доход по данным гостям", "процентная скидка влияет на итоговую стоимость". |
Завершение этапа: учитель подводит итог: "Информатика здесь работает вместе с математикой, экономикой и профессиональными дисциплинами туризма. Мы не просто ввели формулы, а подготовили данные для управленческого решения в гостинице."
Этап 6. Самостоятельная проверка и взаимоконтроль (11 мин)
Цель этапа: проверить корректность структуры книги, формул и оформления результатов.
Время | Действие учителя | Действие учеников |
|---|---|---|
3 мин | Учитель раздаёт карточку «Контрольный маршрут»: "Проверьте три листа, наличие 14 гостей, формулы в расчётных столбцах, значения 0 и 1, условия 7 суток, порог 35 000 и порог 50 000 рублей. Не исправляйте работу партнёра молча: сначала объясните, какое условие нарушено." | Самостоятельно проходят контрольный маршрут, ставят отметки в листе самооценки, исправляют очевидные ошибки. |
2 мин | Учитель организует приём «Две звезды и пожелание»: "Обменяйтесь файлами или покажите партнёру лист «Показатели». Назовите две сильные стороны работы и одно конкретное пожелание по исправлению." | Проверяют работу партнёра, формулируют две положительные стороны и один совет, сверяют одну формулу с условием. |
2 мин | Учитель просит двух учеников прокомментировать типичную ошибку: "Почему нельзя сортировать только столбец L? Что произойдёт с ФИО и типом номера?" | Отвечают: "Строки могут перемешаться, и сумма окажется у другого гостя". Проверяют, что сортировалась вся таблица. |
Завершение этапа: учитель подводит итог: "Проверка показала, что надёжная таблица — это не только формулы, но и контроль структуры данных. Завершим урок рефлексией: оценим результат и определим, что ещё требует тренировки."
Этап 7. Рефлексия и домашнее задание (6 мин)
Цель этапа: осмыслить освоенные действия, зафиксировать затруднения и объяснить домашнюю работу.
Время | Действие учителя | Действие учеников |
|---|---|---|
2 мин | Учитель проводит рефлексию «Светофор»: "Покажите зелёную отметку, если вы можете самостоятельно создать формулу ЕСЛИ и скопировать её; жёлтую — если нужна тренировка; красную — если пока не понимаете условие. Одним предложением объясните свой выбор." | Показывают выбранный цвет и формулируют: "Мне уверенно удаётся считать итоговую сумму", "мне нужно повторить условное форматирование". |
2 мин | Учитель говорит: "Сохраните файл с понятным именем и проверьте, что в нём три листа. Домашнее задание выбирается по уровню. Перед выходом письменно ответьте на контрольные вопросы внизу плана." | Сохраняют файл, записывают выбранный уровень домашнего задания и отвечают на контрольные вопросы. |
Критерии оценивания практической работы
- «5» — созданы и корректно оформлены три листа, внесены 14 строк данных, без ошибок работают формулы E, G, H, J, K и L, рассчитаны показатели, выполнены обе сортировки и оба вида условного форматирования.
- «4» — практическая работа выполнена полностью, но допущена одна несущественная ошибка в оформлении, диапазоне показателей или условном форматировании; основные формулы работают корректно.
- «3» — создана базовая таблица и выполнены основные расчёты, но допущены ошибки в одной или нескольких формулах скидки, доплаты, итоговой суммы либо не завершено оформление листа «Показатели».
Рефлексия
Вопрос для ученика | Цель вопроса |
|---|---|
Какая формула сегодня была наиболее важной для автоматизации работы сотрудника гостиницы? | Выявление осознания назначения формул и связи с профессиональной задачей. |
Как изменяется итоговая сумма при проживании 7 суток и более? | Проверка понимания условия применения скидки. |
Какая ошибка может возникнуть при сортировке только одного столбца? | Диагностика понимания целостности табличной записи. |
Какой этап работы с таблицей требует от вас дополнительной тренировки? | Определение индивидуальных затруднений и планирование коррекции. |
Завершающее слово учителя
"Сегодня вы выполнили расчёты, которые действительно встречаются в работе гостиничного сотрудника: рассчитали проживание, услуги, скидки и доплаты, а затем подготовили показатели для администратора. Главная ошибка, которой следует избегать, — несоответствие диапазона формулы и исходных данных либо сортировка отдельного столбца. Сохраняйте файл и проверяйте расчёты по условию, а не только по внешнему виду таблицы. На следующем уроке мы сможем использовать подобные модели для анализа данных и принятия профессиональных решений."
Домашнее задание
Уровень | Что задать | Зачем |
|---|---|---|
Базовый (обязательный) | Проверить сохранённую книгу с тремя листами и письменно объяснить назначение формул для цены номера, дополнительных услуг, итоговой суммы, скидки и доплаты. | Закрепляет понимание алгоритма; при проверке обратить внимание на точность условий ЕСЛИ и различие между базовой и итоговой суммой. |
Средний (повышающий) | Создать в отдельной копии таблицы 5 новых записей гостей с другими сочетаниями типа номера, количества суток, услуг и бронирования; проверить автоматический пересчёт столбцов E, G, H, J, K и L. | Закрепляет перенос алгоритма на новые данные; проверить, что формулы скопированы, а не заменены ручным вводом чисел. |
Продвинутый (дополнительный) | Предложить и реализовать один дополнительный показатель для администратора, например количество гостей с проживанием 7 суток и более, и кратко объяснить его профессиональную пользу. | Развивает исследовательское и экономическое мышление; при проверке оценить корректность формулы и обоснованность выбранного показателя. |
Контрольные вопросы перед выходом: «Какая формула рассчитывает стоимость дополнительных услуг за сутки? При каком условии предоставляется скидка 15%? От какой суммы считается доплата за бронирование? Почему при сортировке нужно выделять всю таблицу? Какие данные выделяются зелёным цветом на листе «Показатели»?»