Как посчитать путевки в экселе

Обновлено: 05.07.2024

1. Формирование турпакета с включением в него разных типов услуг:

- Лист 1. Расценки турпакета

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

- Лист 3. Продолжительность тура

- Лист 4.Стоимость тура

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

3. Стоимость услуги на тур на листе 3 рассчитать с учетом продолжительности тура, предусмотрев возможность автоматического пересчета при ее изменении.

4. Рассчитать стоимости услуг в рублях, используя абсолютную адресацию и предусмотрев автоматический пересчет при изменении текущего курса доллара.

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

Лист 1. Цены на услуги

Услуга	Тип услуги	Стоимость услуги (разовой/дневной), долл.
Авиаперевозки	Трансаэро
SAS
Трансфер	Автобус
Автомобиль
Проживание
Питание	в/в
h/f

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

Текущий курс долл. (руб.)

Лист 3. Продолжительность тура

Количество дней (число)

Лист 4. Стоимость тура

Расчет стоимости тура продолжительностью . дней

Услуга	Тип услуги	Стоимость услуга (разовой/дневной), долл.	Стоимость услуги на тур
долл.	руб.
Авиаперелет Трансфер Проживание Питание Общая стоимость тура	Трансаэро Автобус (кол-во дней) ( вид способа питания)

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

2. Стоимость услуги на тур на листе 4 рассчитать с учетом продолжительности тура, предусмотрев возможность автоматического пересчета при ее изменении.

3. Рассчитать стоимости услуг в рублях, используя абсолютную адресацию и предусмотрев автоматический пересчет при изменении текущего курса доллара.

Образец расчета стоимости тура
Услуга	Тип услуги	Стоимость услуги (разовой/дневной), долл.	Стоимость услуги на тур
долл.	руб.
Авиаперелет	Трансаэро
Трансфер	Автобус
Проживание
Питание	h\f
Общая стоимость тура

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

Практическое задание занятия "Формирование гостиничных услуг".

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

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

Что позволяет сделать калькулятор:

Рассчитать бюджет на билеты и проживание.
Рассчитать время на каждый этап (дорога, время на стыковки).

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

Ещё мои калькуляторы:

Как пользоваться калькулятором планирования времени поездки

Вводим название города отправления.
Вводим название города прибытия.
Вводим время отправления.
Вводим время прибытия.
Вводим стоимость билета.
Вводим стоимость проживания в отеле.

Внимание! Калькулятор писал для себя, использовал лишь пару раз и мог не заметить ошибку. Проверьте полученные данные.

Upd 6/05 Немного изменил калькулятор. К сожалению, спарклайны старые версии офиса не поддерживают, а мне хотелось чтобы Калькулятор планирования был полезен всем и поэтому переписал под версию MS Office Excel 2007.

Ежедневник, ведение списка контактов, телефонов, связь и т.д.

Promt- электронный переводчик

Перевод с одного языка на другой, различные виды словарей и т.д.

Заполните эту таблицу. Введите 10 программ.

Задание 4
Практическая работа «Расчет стоимости тура в EXCEL»

1. В программе М. Excel создайте книгу. В свойствах файла заполните поля «Название», «Автор», «Учреждение», «Группа».

2. Установите на файл «Пароль для открытия» и «Пароль для изменения».

3. Создайте в книге 3 листа: «заставка» (Рис. 1 ), «лист интерфейса» (Рис. 3 ), «рабочий лист» (Рис. 4 ).

На листе «заставка» (Рис. 1 )должна размещаться следующая информация:

Российская международная академия туризма
Курсовая работа по информатике «Расчет стоимости тура»
Выполнил: студент ___ гр. _______(Ф.И.О.)
Проверил: _______________.
Химки 200_
Фото студента выполнившего работу.

На рисунке эта информация представлена в виде объектов WordArt.

Создание объекта WordArt.

Вывести на экран панель рисования (Рис. 2 )

С помощью кнопки WordArt создать нужную надпись.

На «листе интерфейса» (Рис. 3) создаем два объекта WordArt (две надписи) «Выберите страну», «Выберите услуги» .

Далее, с помощью панели инструментов «Формы» на листе надо создать поле со списком: , пять флажков , и кнопку .

Работа менеджера по расчету стоимости тура будет проводиться на этом листе.

«Рабочий лист» (Рис. 4 )предназначен для хранения данных и проведения расчетов.

Поле со списком на «листе интерфейса» необходимо связать со списком стран на «рабочем листе» . Для этого надо вызвать контекстное меню поля со списком, в нем выбрать команду «формат объекта» и в открывшемся диалоговом окне (Рис. 5 ) выбрать вкладку «Элемент управления».

Далее установить курсор в поле «Формировать список по диапазону» и выделить на «рабочем листе» ячейки со странами (это ячейки А1-А5).

Затем установить курсор в поле «Связать с ячейкой» и выделить ячейку В1 на «рабочем листе» и нажать кнопку Ок. В эту ячейку поле со списком будет подставлять порядковый номер выбранной страны.

Теперь надо связать флажки на листе интерфейса с соответствующими ячейками на рабочем листе.

ФЛАЖКИ ПОДСТАВЛЯЮТ В ВЫБРАННУЮ ЯЧЕЙКУ ИСТИНА (ЕСЛИ ФЛАЖОК УСТАНОВЛЕН) ИЛИ ЛОЖЬ (ЕСЛИ ФЛАЖОК СНЯТ).

Для этого надо вызвать контекстное меню флажка (например, флажок «Авиабилет»), выбрать в нем команду «формат объекта». В диалоговом окне «Формат элемента управления» (Рис. 6 ) выбрать вкладку «Элемент управления» и установить курсор в поле связь с ячейкой. Затем надо выделить ячейку D1 на «рабочем листе» и нажать кнопку Ок. Надо связать с соответствующей ячейкой каждый флажок. (См. таблицу 2).

Предлагается решить задачу о расчете стоимости туристических путевок. В стоимость путевок включаются дополнительные обязательные экскурсионные программы. Первичный расчет стоимости путевок выполняется в долларах, которые затем переводятся в рубли по текущему курсу. Туристический бизнес сегодня развит настолько, что можно в любое время года купить путевку практически в любую точку Земли. Количество туристических фирм растет с каждым днем, соответственно и растет конкуренция между ними. Организацией было принято решение отслеживать доходы от реализации путевок для того, чтобы не допустить уменьшения прибыли предприятия, не начать терпеть убытки, но оставаться конкурентоспособными. Задача, которая будет решаться в программной среде MS Excel ежедневно, называется «Учет стоимость туров».

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

Данная задача будет решаться на ЭВМ в программной среде MS Excel 2010.

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

1.1. Условие задачи

Прайс туров

Латинские буквы в таблице указывают на элементы соответствующих расчетных формул.

Условно-постоянной информацией (справочной) служат следующие реквизиты: название страны, стоимость экскурсий.

Для решения задачи используется справочник «Стоимость экскурсий».

Структура документа «Стоимость экскурсий»

Название страны

Стоимость экскурсий

В результате следует получить ведомость со следующими реквизитами: номер п/п, место пребывания, количество реализованных туров, стоимость тура, стоимость экскурсий, общая стоимость туров. Информация выдается в следующем документе:

Структура результирующего документа «Итоговая ведомость доходов тур. фирмы от реалицаии путевок»

Курс доллара на текущую дату - ___ (D) руб.

№ п/п

Место пребывания

Количество реализованных туров

Стоимость тура, руб.

Стоимость экскурсий, руб

Общая стоимость туров, руб.

Итого общая стоимость туров, руб

Кроме того, информацию, находящуюся в таблицах для анализа, необходимо представить в виде диаграмм.

В технологии необходимо использовать межтабличные связи для организации ввода и контроля исходных данных, а также для организации процесса расчета функции ВПР, ПРОСМОТР и др.

1.2. Цель решения задачи

Рассмотрим такую ситуацию. Туристическая фирма ООО «Турист» осуществляет реализацию путевок на заграничные туры. В стоимость путевок включаются дополнительные обязательные экскурсионные программы. Первичный расчет стоимости путевок выполняется в долларах, которые затем переводятся в рубли по текущему курсу. Организацией было принято решение отслеживать доходы от реализации путевок для того, чтобы не допустить уменьшения прибыли предприятия, не начать терпеть убытки. Задача, которая будет решаться в программной среде MS Excel ежедневно, называется «Учет стоимость туров».

Математическая модель решения задачи

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

Стоимость тура в долларах;
Стоимость тура в рублях;
Стоимость экскурсий в рублях;
Общая стоимость каждого тура в отдельности (в рублях);
Общую стоимость всех туров в рублях.

Расчеты выполняются по следующим формулам:

где S – стоимость тура в долларах, L – стоимость перелета и трансфера, P – стоимость проживания и питания, V – стоимость страховки и визы, S_r – стоимость тура в рублях, D – курс доллара, E_r – стоимость экскурсий в рублях, E – стоимость экскурсий в долларах, S_t – Общая стоимость туров в рублях, K – количество реализованных туров, S_о – общая стоимость всех туров в рублях.

3. Технология решения задачи в MS Excel

Решение задачи средствами MS Excel

Вызовите Excel:

нажмите кнопку «Пуск»;
выберите в главном меню команду «Программы»;
меню Microsoft Office выберите MS Excel.

Переименуйте «Лист 1» в «Прайс туров»:
- установите курсор мыши на ярлык «Лист 1» и нажмите правую кнопку мыши (ПКМ);
- В контекстном меню выберите команду «Переименовать» и нажмите левую кнопку мыши (ЛКМ);
- Наберите на клавиатуре «Прайс туров» и подтвердите изменение нажатием клавиши «Enter».
В ячейку A1 введите заголовок таблицы «Стоимость предлагаемых туров, долл».
Введите в ячейки A2:F2 информацию, представленную на рисунке 2.

Рисунок 2 – Имена полей таблицы «Стоимость предлагаемых туров, долл»

В ячейки A3:F8 введите информацию, приведенную в таблице 1.

Таблица 1 – Стоимость предлагаемых туров, долл

Стоимость предлагаемых туров, долл

№п/п

Место пребывания

Перелет + трансфер

Проживание + питание

Страховка + виза

Стоимость тура

Созданная таблица «Стоимость предлагаемых туров, долл» представлена на рисунке 3.

Рисунок 3 – Вид таблицы «Стоимость предлагаемых туров, долл»

Переименуйте «Лист 2» в «Стоимость экскурсий» (аналогично действиям пункта2).
Создайте таблицу «Стоимость обязательной экскурсионной программы, долл» и введите в нее данные (рисунок 4).

Рисунок 4 – Вид таблицы «Стоимость обязательной экскурсионной программы, долл»

Отсортируем полученную таблицу по наименованию страны. Для этого выделим диапазон ячеек A2:B8, выберем пункт меню «Данные» - «Сортировка». В результате чего появится диалоговое окно, как показано на рисунке 5. В выпадающем меню «Сортировать по» выбираем «Название страны», порядок – по возрастанию (от А до Я). Нажимаем кнопку «ОК».

Рисунок 5 – Вид окна «Сортировка»

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

Рисунок 6 – Таблица «Стоимость обязательной экскурсионной программы, долл» в отсортированном виде

Присвоим имя группе ячеек таблицы «Стоимость обязательной экскурсионной программы, долл». Для этого:
- Выделите ячейки A3:B8, выберите команду «Присвоить имя» в разделе «Определенные имена» меню «Формулы» - «ОК»(рисунок 7).

Рисунок 7 – Вид окна «Создание имени»

Переименуйте «Лист 3» в «Итоговая ведомость».
Создайте таблицу «Доход туристической фирмы от реализации путевок» и введите в нее исходные данные (рисунок 8).

Рисунок 8 – Вид таблицы «Доход тур. фирмы от реализации путевок»

Вычислим стоимость тура в долларах (столбец F на листе «Прайс туров»). Для этого в ячейку F3 введем формулу

Далее, используя маркер автозаполнения, вычислим стоимость туров для остальных мест пребывания (рисунки 9,10).

Рисунок 9 – Ввод формулы для вычисления стоимости тура в долларах

Рисунок 10 – Результат заполнения столбца «Стоимость тура»

Заполним столбец «Стоимость тура, руб.» таблицы «Доход туристической фирмы от реализации путевок».

Для этого в ячейку D4 введем формулу:

С помощью маркера автозаполнения, вычислим стоимость тура в рублях для других стран, для этого «растянем» формулу до ячейки D9 (рис.11,12).

Рисунок 11 – Вычисление стоимости тура, руб. для одной страны

Рисунок 12 – Результат заполнения столбца «Стоимость тура, руб»

Заполним столбец «Стоимость экскурсий, руб.»:
- Сделайте ячейку E3 активной;
- Введем в ячейку «= »
- Воспользуйтесь командой «Формулы» - «Вставить функцию»;
- В поле «Категория» выберите «Ссылки и массивы»;
- В поле «Выберите функцию» нажмите «ВПР» (рисунок 13);

Рисунок 13 – Вид первого окна мастера функций

Рисунок 14 – Ввод имени массива в качестве аргумента функции

Рисунок 15 – Вид окна «Аргументы функции»

Рисунок 16 – Вычисление стоимости экскурсий в рублях

Сделайте ячейку E4 активной, установите курсор на маркер автозаполнения в правом нижнем углу ячейки E4, щелкните ЛКМ и протяните его до ячейки F9;
Результат заполнения столбца «Стоимость экскурсий, руб.» представлен на рисунке 17.

Рисунок 17 – Заполненный столбец «Стоимость экскурсий, руб.»

Вычислим общую стоимость туров в рублях. Для этого в ячейку F4 введем формулу:

Далее, используя маркер автозаполнения, вычислим общую стоимость туров для остальных стран (размножим формулу в ячейке F4 до ячейки F9, рисунки 18,19).

Рисунок 18 – Вычисление общей стоимости туров, руб.

Рисунок 19 – Заполненный столбец «Общая стоимость туров, руб.»

Вычислим итоговую общую стоимость туров. Для этого в ячейку F10 введем формулу, представленную на рисунке 20.
В результате таблица «Доход туристической фирмы от реализации путевок» примет вид, представленный на рисунке 21.

Рисунок 20 – Диалоговое окно функции «СУММ»

Рисунок 21 – Таблица «Доход туристической фирмы от реализации путевок» в заполненном виде

Представим наглядно результаты работы, создав диаграмму по данным итоговой ведомости о доходах туристической фирмы. Для этого:
- Выделите несмежные диапазоны ячеек, содержащие информацию о месте пребывания и информацию об общей стоимости туров, руб. При выделении несмежных диапазонов удерживаем клавишу Ctrl (рисунок 22);
- Выберите команду «Гистограмма» в разделе «Диаграммы» меню «Вставка»;
- Переименуйте получившуюся гистограмму в «Доходы тур. фирмы от реализации путевок» (рисунок 23).

Рисунок 22 – Выделение необходимых данных для построения диаграммы

Рисунок 23 – Гистограмма «Доход тур. фирмы от реализации путевок»

Заключение

Таким образом, формирование сводных таблиц на основании «Прайса туров» и стоимости экскурсий, позволяет отслеживать общую стоимость туров для предотвращения появления убытков. Полученные данные позволяют руководству принимать решения по регулированию стоимости услуг (перелет, проживание, питание, страховка, виза, экскурсии), предоставляемых клиентам. Создание диаграмм на основе данных сводной таблицы позволяет не только наглядно представлять результаты обработки информации для проведения анализа с целью принятия решений, но и достаточно быстро осуществлять манипуляции в областиих построения в пользу наиболее удобного представления результатов визуализации по задаваемым пользователем параметрам.

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

1. Информатика: учебное пособие / под ред. Б.Е. Одинцова, А.Н. Романова. – М.: Вузовский учебник : ИНФРА+М, 2012.

2. Информационные ресурсы и технологии в экономике: учебное пособие / под ред. Б.Е. Одинцова, А.Н. Романова. – М.: Вузовский учебник, 2012.

3. Информатика: Практикум для экономистов: учебное пособие /под ред. В.П. Косарева. – М.: Финансы и статистика : ИНФРА+М, 2009.

Если вам нужна помощь в написании работы, то рекомендуем обратиться к профессионалам. Более 70 000 авторов готовы помочь вам прямо сейчас. Бесплатные корректировки и доработки. Узнайте стоимость своей работы

Читайте также: