Решение логистических задач в менеджменте туризма на Excel

А.Ю. Панков, Е.Л. Родигин, Л.А. Родигин

РЕШЕНИЕ  
ЛОГИСТИЧЕСКИХ ЗАДАЧ  
В МЕНЕДЖМЕНТЕ ТУРИЗМА  
НА EXCEL

Учебное пособие

Под общей редакцией Л.А. Родигина

Москва • Университетская книга • 2020

УДК 681.5:33
ББК 32.965:65.433
 
П16

Р е ц е н з е н т ы

В.И. Горелов , доктор технических наук,  
профессор кафедры ПИТ Института общественных наук  
Российской академии народного хозяйства  
и государственной службы при Президенте РФ; 
И.Н. Ковалева, доктор экономических наук,  
кандидат физико-математических наук, доцент,  
завкафедрой математики и информатики РМАТ

П
Панков А.Ю.
Решение логистических задач в менеджменте туризма на Excel : учеб. пособие / А.Ю. Панков,  
Е.Л. Родигин, Л.А. Родигин ; под общ. ред. Л.А. Родигина ; Российская международная академия туриз- 
ма. – М. : Университетская книга, 2020. – 126, [2] с. 
ISBN 978-5-98699-319-5

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

 
УДК 681.5:33
  
ББК 32.965:65.433

ISBN 978-5-98699-319-5
© Панков А.Ю., Родигин Е.Л.,  
    Родигин Л.А., 2020
© Российская международная академия  
    туризма, 2020
© Оформление. РМАТ,  
    Университетская книга, 2020

Содержание

Введение ...................................................................................5

Работа с мастером функций ....................................................7

Создание таблиц. Работа с формулами ................................11

Построение диаграммы .........................................................17

Работа с картой .......................................................................24

Размещение туристов в гостиницах .....................................33

Оптимизация ресурсов: поиск решения ..............................39

Поиск решения по оптимизации продаж .............................43

Поиск решения по кратчайшему расстоянию  
перевозки груза (транспортная задача) ................................47

Работа с тестами .....................................................................51

Выбор вакансий ......................................................................58

Расчет стоимости тура ...........................................................62

Анализ «что-если» .................................................................69

Статистический анализ данных ............................................75

Создание базы данных и работа с ней  ................................81

Содержание

Сводные таблицы ...................................................................88

Программирование функций обслуживания клиентов  
в турагентстве «Pay&Fly» .....................................................93

Лист «Заставка» ...............................................................94

Лист «Туры» .....................................................................98

Лист «Отели» .................................................................101

Лист «Транспорт» ..........................................................103

Лист «Клиенты» ............................................................105

Лист «ТУР-1» .................................................................106

Лист «Заявки» ................................................................117

Экспорт таблиц Excel в формат XML ................................120

Заключение ...........................................................................125

Библиографический список ................................................127

Введение

Работу туристского агентства сегодня трудно представить без использования современных средств вычислительной техники и систем автоматизации работы. Эти системы позволяют решать задачи, стоящие как перед руководителем туристской фирмы (утверждение нетто и брутто 
тарифов; контроль оплаты туров; оценка эффективности 
работы менеджеров; оценка прибыльности направлений;  
оценка оборота по туроператорам), так и перед менеджером  
(четкое следование утвержденным тарифам; выдача туристу на руки необходимых документов; своевременная подача отчетности; возможность «поднять» старые данные). 
В таких системах зачастую предусмотрена возможность 
ведения базы данных деловых партнеров и постоянных 
клиентов, встроены режимы рассылки факсов по модему  
и передачи сообщений по e-mail.
К настоящему времени созданы сложные интегрированные туристские системы, включающие функции формирования и расчета туров, базы данных со справочной 
информацией и коммуникационные функции. Усложнение 
систем и расширение их функций повышают требования  
к качеству подготовки менеджеров туристских фирм в сфере информационных технологий. 
От специалиста по менеджменту в первую очередь 
требуется четкое понимание круга задач, решаемых с помощью офисных систем, умение разрабатывать алгоритмы для решения этих задач и создавать соответствующие  
программы. 
Табличный процессор Excel (который называют также 
электронными таблицами) предназначен для автоматизации выполнения различных расчетов – научно-технических, чисто математических, финансово-экономических 
и др. Автоматическое выполнение расчетов в табличном 

Введение

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

 Работа с мастеРом функций

Мастер функций существенно упрощает работу, связанную с вычислениями, так как не надо вводить формулу или 
функцию. В диалоговом окне мастера функций необходимо 
указать, что нужно вычислить, и ввести исходные данные. 
Значок мастера функции находится на стандартной панели  
инструментов. Существует большое количество встроенных  
функций: математические, финансовые, логические и др.
Составим таблицу для магического квадрата Дюрера 
(рис. 1) и вычислим суммы по столбцам, строкам и диагоналям. Все суммы должны быть одинаковы и равны 34.

Рис. 1. Квадрат Дюрера

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

Работа с мастером функций

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

Рис. 2. Расчет статистических функций

Далее составим таблицу и вычислим финансовые функции: БС – величина будущего вклада, АМГД – амортизация 
(рис. 2, 3).

Рис. 3. Вычисление функции будущей стоимости инвестиций

На рис. 3: «Норма» – процент годовых; «Число периодов» – число лет, на которые положен вклад; «Выплата» – 
ежегодные взносы вкладчика; «БС» – значение величины 
будущего вклада.
После вычислений отформатируем данные строки БС  
в денежном формате (в рублях).
Окно мастера функций представлено на рис. 4.
Вычислим функцию АМГД. Исходные данные и расчет 
представлены на рис. 5 и 6.
На рис. 6: «Стоимость» – начальная стоимость имущества; «Ликвидная стоимость» – остаточная стоимость; 

Работа с мастером функций  
 
9

«Время эксплуатации» – срок эксплуатации; «Период» – 
время, на которое рассчитывается амортизация.

Рис. 4. Окно мастера функций БС  
при расчете величины будущего вклада

Рис. 5. Окно мастера функций АМГД при расчете амортизации

Работа с мастером функций

Рис. 6. Вычисление функции амортизации за период

 создание таблиц.  
 
Работа с фоРмулами

Составим ведомость на заработную плату за три месяца.
Выполним форматирование таблиц и расчет по формулам. Подготовим для группового ввода три листа. Откроем 
их все сразу, щелкнув на ярлычке рабочего листа и выполнив команду Выделить все листы.
Другой способ: в открытой книге, используя клавишу 
SHIFT, последовательно мышью выделить три листа.
В первую строку введем заголовок РАСЧЕТ ЗАРАБОТНОЙ ПЛАТЫ. Обратите внимание, заголовок одновременно отображается на остальных выделенных листах.
В строку, начиная с четвертой, введем заголовки столбцов: Месяц, Табельный номер, Фамилия Имя Отчество, 
Оклад, Кол-во рабочих дней, Заработная плата в день, Отработано дней, Начислено, Кол-во больничных дней, Процент по больничному листу, Начислено по больничному 
листу, Всего начислено, Подоходный налог, Пенсионный 
взнос, Алименты, Всего удержано, К выдаче. 
Если заголовки не умещаются, выполним выравнивание ячеек: Формат – Ячейки – Выравнивание – Переносить по словам. Затем изменим ширину столбца и высоту 
строки, если это необходимо. Отцентрируем текст в заголовках по вертикали и горизонтали с помощью команды  
Формат.  
 
Введем формулы, по которым будем вести расчеты.  
В столбец Заработная плата в день введем формулу: 

=[Оклад] / [Кол-во рабочих дней]

Создание таблиц. Работа с формулами

Ввод формулы начинается со знака «равно». Операнды 
вводим щелчком мыши на выбранной ячейке. Результат получаем при нажатии клавиши Enter. В ячейке появилось 
выражение #ДЕЛ/0 (деление на 0), так как у нас пока не 
введены числовые данные (рис. 7).

Рис. 7. Вид таблицы после ввода формул

Введем формулы в остальные столбцы:
Начислено: 

=[Заработная плата в день]* [Отработано дней]

Начислено по больничному листу:

=[Заработная плата в день]*[Кол-во больничных дней]* 
[Процент по больничному листу]

ВСЕГО начислено:

=[Начислено] +[Процент по больничному листу]

Подоходный налог:

=[ВСЕГО начислено]* 13%.

Пенсионный взнос:

=[ВСЕГО начислено] * 0%.

(с 1995 по 2000 год размер удерживаемого пенсионного 
взноса в РФ составлял 1%)
Алименты:

=[ВСЕГО начислено] *0%

(Процент вводится персонально для работника – максимальный размер алиментных платежей – 50% от заработка алиментоплательщика, а при наличии задолженности по