Таблица эксель для отеля

Технология работы в электронной таблице. Разработка программы расчета с клиентами за проживание в гостинице

1. Постановка задачи.

Создать таблицу расчетов с клиентами гостиницы, о которых известны дата въезда, съезда, цены проживания и бронирования, текущий курс доллара . Гостиница имеет одно- и двухместные номера. Оплата за номер определяется числом дней проживания. Кроме того имеются скидки и доплаты. Если клиент проживает в номере больше 10 дней, ему делается скидка по оплате в 15% за каждый день проживания свыше десятого. Если номер был заранее забронирован клиентом, то клиент доплачивает за бронь сумму в размере 10% средней арифметической стоимости за номер (одноместный и двухместный).

Информация о ценах на гостиничные услуги, курсе доллара и доходах гостиницы за определенный период хранится на четырех листах:

Лист 1. Расценки.

Цены проживания и бронирования

Тип номера Цена в сутки одного койко-места, долл.
1-местный 33
2-местный 55
Цена бронирование 4,4
Тип номера Цена в сутки одного койко-места, долл.
1-местный 33
2-местный 55
Цена бронирование =СРЗНАЧ(B3:B4)*10%

Лист 2. Курс доллара.

Лист 3. Расчёты с клиентами гостиницы.

Скидка на проживание 15%
Клиент Бронь Проживание Цена номера Оплата Скидка Оплата со скидкой Общая сумма
с: по:
Иванов да 01.12.2009 20.12.2009 55
Сидоров да 11.12.2009 16.12.2009 33
Каспарян нет 01.12.2009 05.12.2009 55
Кавтарадзе нет 01.12.2009 03.12.2009 55
Хван нет 02.12.2009 25.12.2009 33

Лист 4. Доход гостиницы.

Дата Число проживающих в номерах Число бронирований Доход гостиницы
Одноместных Двухместных долл. руб.
11.12.2009 34 51 11
12.12.2009 28 59 16
13.12.2009 40 62 10
14.12.2009 35 49 8
15.12.2009 30 33 15
16.12.2009 25 42 20
  • Рассчитать доход гостиницы за определенный период.
  • Построить цилиндрическую диаграмму дохода гостиницы по датам.

2. Формулы для расчета.

Он = (tк- tн)*Цном,
Он – оплата номера;
tк – дата съезда;
tн – дата заезда;
Цном – цена номера.

Скидка за оплату за проживание в отеле: С = ЕСЛИ ((tк – tн)>10), Он *15%, Он) если количество дней проживания в отеле больше 10.

Доплата: D = ((Цоддв)/2)*10%, если номер забронирован. D – доплата, Цод – цена одноместного номера, Цдв – двухместного номера.

Общая сумма оплаты за проживание: если номер забронирован Соб = С+D.

В результате получается:

Расчёты с клиентами гостиницы (в режиме отображения формул)

Таблица в режиме отображения значения

Источник

bcn 1 / 7(Дом_работа) / Задание13(Отель)

Создание базы данных «Отель» (использование EXCEL для создания базы данных; функции ВПР, ЕО­ШИБКА – посмотреть их значение через Справку).

1. Переименуйте: Лист1- БД Отель; Лист2 — Вспомогательные таблицы;

2. На листе «Вспомогательные таблицы» создайте следующие таблицы, учитывая соглашения:

в графы, не выделенные серым цветом, введите исходные данные;

диапазону ячеек A2:A5 присвойте имя Типы_номеров, диапазону ячеек E2:E5 – Вид_пансиона (Вставка Имя Присвоить);

выделенную серым цветом графу заполните с помощью списка (Данные Проверка; закладка Пара­метры далее Тип данных Список; Источник Типы_номеров.)

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

Рис. Лист «Вспомогательные таблицы»

Первая цифра номера комнаты – это этаж. На 3-м и 4-м этажах размещение типов номеров аналогично вто­рому этажу.

Для того чтобы проставить цену номеров воспользуйтесь функцией ВПР (категория Ссылки и мас­сивы), аргументами которой являются:

Искомое_значение – тип номера (В8),

Табл_массив – таблица, в которой ведется поиск ($A$2:$B$5),

Номер_индекса_столбца – номер столбца в таблице, где находится стоимость ( у нас — 2),

Графа «Занятость» заполняется по формуле:

Для ввода этой формулы вызываете функ­цию ЕСЛИ, затем сразу функцию ЕОШИБКА (категория Про­верка свойств и значений), затем функцию ВПР (контролируете строку формул). Задаете аргументы для функции ВПР, затем щелкаете по строке фор­мул и заканчиваете вводить формулу.

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

Введите формулу для расчета Итого занято.

3. На листе «БД Отель» наберите заголовки столбцов

4. В графу «Тип номера» введите формулу, выводящую тип номера в зависимости от номера комнаты:

Размножьте эту формулу на 50 строк.

Аналогично составьте и введите формулу для вывода цены номера в день в зависимости от типа номера. Размножьте формулу на 50 строк.

Вид пансиона оформите как поле со списком (ДанныеПроверка далее Тип данныхСписок, затем ИсточникВид_пансиона), размножьте формулу на 50 строк.

Для вывода цены пансиона в день используйте формулу:

ЕСЛИ(F2<>””;ВПР(F2;‘Вспомогательные таблицы’!$E$3:$F$5;2;0);” “), размножьте формулу на 50 строк.

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

Если «Вид пансиона» <> “”, то «Цена номера в день» + «Цена пансиона», иначе «Цена номера в день». Размножьте формулу на 50 строк.

Скопируйте заголовки столбцов с листа «БД Отель» на лист «Архив».

В столбец I введите заголовок «Дата выезда», в столбец J введите заголовок «Количество дней», в стол­бец K введите заголовок «Общая стоимость».

Введите формулу для выдачи даты выезда ЕСЛИ(С2>19;СЕГОДНЯ();” “) (СЕГОДНЯ() – это функция), установите для столбца формат ДАТА, размножьте формулу на 50 строк.

Введите формулу для расчета количества дней: если номер комнаты>19, то «Дата выезда» — «Дата за­езда» + 1, иначе пусто. Размножьте формулу на 50 строк.

Введите формулу для расчета общей стоимости:

Если номер комнаты>19, то «Оплата за номер в день» * «Количество дней», иначе пусто. Размножьте формулу на 50 строк.

Проверьте работу формул на листе «БД Отель». Для этого введите не менее 10 записей о клиентах с раз­ными датами заезда и другими исходными данными.

Проверьте работу формул на листе «Архив». Для этого оформите выезд из отеля двух клиентов, учиты­вая следующие соглашения.

На лист «Архив» заносятся данные о клиентах, которые выезжают из отеля. Для того чтобы перенести дан­ные о клиенте с листа «БД Отель» на лист «Архив», выполняются следующие действия:

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

на листе «Архив» активизируется ячейка в столбце А в пустой строке.

выполняется вставка из буфера обмена.

на листе «БД Отель» удаляется строка, в которой была запись о выехавшем клиенте.

На листе «Вспомогательные таблицы» с помощью Автофильтра найдите список свободных номеров.

На листе «БД Отель» научитесь сортировать записи по датам и номерам комнат.

Создайте копию листа «БД Отель» с новым именем «Итоги». Подведите промежуточные и общие итоги среднего значения оплаты по разным типам номеров.

Создайте копию листа «БД Отель» с новым именем «Критерии». С помощью расширенного фильтра подготовьте списки клиентов (фамилия, вид пансиона, оплата за номер), проживающих в номерах раз­ного типа: люксе, 1-местном, 2-местном.

По «БД Отель» постройте сводную таблицу для анализа спроса на разные виды пансиона клиентами, проживающими в номерах различного типа. Постройте диаграмму по созданной сводной таблице.

Источник

Оцените статью