Решение логистических задач в менеджменте туризма на 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% от заработка 
алиментоплательщика, а при наличии задолженности по