Оліфіров О.В. 9 страница

Рисунок 3.7 - Приклад візитки для співробітників підприємства

Рисунок 3.8 - Приклад конверта туристичного агентства

3.3 Використання електронних таблиць Microsoft Excel

Менеджер підприємства туристичної галузі стикається у своїй повсякденній роботі з необхідністю виконання безлічі рутинних операцій з обробки масивів даних. Для автоматизації цього процесу розроблено відповідне програмне забезпечення, що звичайно має назву електронні таблиці, або табличні процесори. Це універсальні засоби для автоматизації розрахунків великих обсягів табличних даних, що дозволяють зменшити витрати часу та спростити обробку даних. За допомогою електронних таблиць можна будувати різного роду діаграми, проводити складний економічний аналіз, моделювати і оптимізувати рішення різних господарських ситуацій та багато іншого [31].

Електрона таблиця створюється та зберігається у пам’яті комп’ютера, у подальшому її можні змінювати, переглядати, зберігати на магнітному носії, а також виводити її тверду копію (роздруковувати по папері за допомогою принтера). На екрані дисплея електронна таблиця відображається у вигляді двомірного масиву (матриці), що складається із стовпців та рядків, на перетині яких розташовуються клітинки.

У теперішній час серед електронних таблиць найбільш популярним є розроблений корпорацією Microsoft для операційної системи Windows табличний процесор Excel. Серед інших подібних програмних продуктів цей пакет виділяється зручним графічним інтерфейсом та можливістю взаємодії з іншими продуктами Microsoft Office. Функціональні можливості цього пакету дозволяють активно його використовувати для обробки та аналізу фінансово-економічної інформації, автоматизації обліково-контрольної діяльності, ефективної обробки великих обсягів інформації, заданої у табличному вигляді.

У поєднанні ж з мовою програмування Visual Basic for Application (VBA), табличний процесор MS Excel набуває універсальний характер і дозволяє вирішити взагалі будь-яке завдання, незалежно від її характеру.

При роботі з електронними таблицями слід дотримуватися основних правил, що зазначені нижче.

При записі формул необхідно пам’ятати, що формула розташовується у окремій клітинці, починається зі знака «рівно», а для завершення формули і проведення обчислень слід натиснути клавішу Enter.

При роботі з формулами використовується відносна та абсолютна адресації. При відносній адресації Excel інтерпретує адреси клітинок як вказівки на їх розташування відносно клітинки, в якій знаходиться формула. Після копіювання такої формули в іншу клітинку, відносні посилання будуть вказувати на інші клітинки так, щоб зберегти попередні відносини відповідно до нового положення. За замовчуванням всі формули у Excel є відносними. При абсолютній адресації посилання на клітинку не залежить від розташування формули на листі.

Для того, щоб перетворити відносне посилання в абсолютне, достатньо у режимі редагування формули встановити курсор безпосередньо на посиланні та натиснути клавішу <F4>.

Відносне посилання	Абсолютне посилання
F4	$F$4

Розглянемо приклад проведення розрахунків у середовищі табличного процесора MS Excel.

1. На Листі 1 створимо таблицю під назвою «Вартість проживання у готелі» (рис. 3.9), де представлена інформація про ціни на різні категорії номерів.

У клітинки А1:D12 внести назву таблиці і стовбців. Виділити клітинки А2:D2, для введення даних використовувати форму введення інформації Данные → Форма. Ввести дані за рядками.

2. На Листі 2 розміститьтаблицю, що містить кількість зайнятих номерів на конкретну дату 11.08.2012 (рис. 3.10). У клітинки А1:D12 внести назву таблиці і стовбців. Виділити клітинки А2:C2, для введення даних використовувати форму введення інформації Данные → Форма. Ввести данні за рядками.

Рисунок 3.9 –Вихідна таблиця «Вартість проживання у готелі»

Рисунок 3.10 –Структура таблиці «Зайнятість номерів»

У клітинку А21 вписати «Курс долара», у клітинку А22 внести значення поточного курсу (наприклад, 8,02).

3. Розрахуйте дохід готелю (загальний та за категоріями номерів) за 11.08.2012.

Внести в клітинку D3 формулу:

=Лист1!D3*C3/$A$22 Enter

де $A$22 – абсолютне посилання на клітинку.

За допомогою маркера заповнення скопіювати формулу на решту клітинок. Для розрахунку підсумкового значення потрібно внести у клітинку D13 формулу:

=СУММ(D3:D12) Enter

4. Відсортуйте вихідну таблицю за кількістю мешканців та категорією номеру: виділить клітинки A2:С12, виконайте команду Данные → Сортировка, встановить у списку Сортировать по значення Категория номера і напрямсортування По возрастанию.

5. Побудуйте на окремому листі об’ємний варіант гістограми, що відображує дохід готелю за категоріями номерів.

Перед тим, як будувати діаграму, необхідно підготувати підписи, які повинні мати вигляд як склеювання трьох рядків [номер поверху], «поверх –», [категорія номеру]. У клітинки F3 внесіть формулу:

=СЦЕПИТЬ(A3;" поверх - ";B3)

За допомогою маркера заповнення скопіюйте формулу на решту чарунок. Побудова діаграми (рис. 3.11):

- Виділити діапазони D2:D12.

- Запустити майстер побудови діаграм .

- Вибрати вид діаграми - гістограма.

- В Исходных данных додати ім’я ряду та підписи по осі Х.

- Встановити розміщення.

6. За допомогою функції Автофильтр оберіть інформацію про зайняті номери: виділить клітинки A2:D12, виконайте команду Данные→Фильтр→ Автофильтр, у списку Количество занятых номеров оберіть значення Условие, у списках задания условий встановить не равно 0.

Рисунок 3.11 – Діаграма аналізу доходу готелю за категоріями номерів

7. Встановить проміжні підсумки за категорією номеру: виділить клітинки A2:D12, виконайте команду Данные →Итоги, виберіть При каждом изменении в – Категорія номеру, Операция – Сумма, у списку Добавить итоги по вибрати Дохід готеля.

Для аналізу даних також використовується інструмент Умовне форматування.

Умовне форматування здійснює виділення клітинки або діапазону клітинок, що відповідають заданій умові, виділення нетипових значень та візуалізацію даних за допомогою гістограми, кольорних шкал та наборів значків. Умовне форматування змінює зовнішній вигляд діапазону клітинок на основі умови (або критерію), якщо воно є істинним.

При створенні умовного формату можна посилатися на інші клітинки на листі, наприклад =Лист1!A5, але зовнішні посилання на іншу книгу використовувати неможна.

Для виконання умовного форматування необхідно:

1) виділити клітинки, які повинні автоматично змінювати свій колір;

2) виберіть у меню Формат - Условное форматирование;

3) у відкритому вікні можна задати умови та, натиснувши кнопку Формат, параметри форматування клітинок, якщо умова виконується.

Розглянемо декілька прикладів виконання умовного форматування на прикладі таблиці, що містить результати поточного модульного контролю студентів (рис. 3.12).

Рисунок 3.12 – Результати поточного модульного контролю

Приклад 1. У даному прикладі клітинки зі значеннями більше 18 балів заливаються зеленим, від 12 до 18 - жовтим, а менше 12 - помаранчевим кольором.

Виконання: 1) виділити діапазон B3:E10; 2) у вікні умовного форматування вписати дані з рис. 3.13.

Рисунок 3.13 – Вікно умовного форматування

Кнопка А также>> дозволяє додати додаткові умови.

Якщо для діапазону клітинок заданий критерій умовного форматування, то більше не можна форматувати ці клітинки через меню Формат - Ячейки уручну. Щоб повернути собі цю можливість потрібно видалити умови у вікні Условное форматирование за допомогою кнопки Удалить.

Існує також можливість перевірити не значення виділених клітинок, а задану формулу (рис. 3.14). Якщо формула є вірною (повертає значення ИСТИНА), то спрацьовує потрібний формат. В цьому випадку можна перевіряти одні клітинки, а форматувати інші.

Рисунок 3.14 – Варіанти створення умов

Приклад 2. Необхідно виділити кольором прізвище студента, якщо він набрав у сумі за 4 модуля більше 90 балів (рис. 3.15 і 3.16).

Рисунок 3.15 – Вікно умовного форматування

Рисунок 3.16 – Результат умовного форматування

Знак долара ($) повинен ставитися перед літерою стовпця в адресі - він фіксує стовбець, оставляючи незафіксованим посилання на рядок.

Значення, що перевіряються беруться зі стовпців B, C, D, E, по черезі з кожного наступного рядка.

Приклад 3. Виділити максимальні та мінімальні значення за модулями. Виділити діапазон B3:E10, у відкритому вікні вписати:

Рисунок 3.17 – Умовне форматування з двома умовами

Відбувається перевірка, чи дорівнює значення клітинки максимальному або мінімальному у діапазоні, а також їх заливка відповідним кольором.

Прізвище студента	Модулі
1-й	2-й	3-й	4-й
Іванов
Петров
Сидоров
Кузнєцов
Савин
Тарасов
Михайлов
Лукин

Рисунок 3.18 – Результат умовного форматування

Приклад 4. Виділення рядків таблиці у вигляді «зебри».

Виділити клітинки таблиці (окрім "шапки"), відкрити меню Формат - Условное форматирование, вибрати у списку варіант Формула замість Значение та ввести таку формулу: =ОСТАТ(СТРОКА(A3);2)=0

Рисунок 3.19 – Умовне форматування для виділення рядків у вигляді «зебри»

Ця формула бере номер поточного рядка (функція СТРОКА, де A3 - перша клітинка виділення), розділює його на 2 та перевіряє залишок від ділення (функція ОСТАТ()). Якщо він дорівнює нулю, тобто номер рядка парний, то відбувається форматування клітинки (необхідно задати колір шрифту або заливку). Якщо необхідно залити не кожний 2-ий, а, наприклад, кожний 5-ий рядок, то потрібно змінити в цій формулі дільник 2 на 5.

У версії MS Excel 2007 при умовному форматуванні з’явилася можливість розміщувати у клітинках значки, діаграми, кольорні шкали.

Приклад 5. Використання набору значків

У клітинках зі значеннями більше 18 балів поставити зелені значки , від 12 до 18 – жовті , а менше 12 – червоні .

Виконання (для версії MS Excel 2007):

1) виділити діапазон B3:E10;

2) на панелі Главная вибрати значок Условное форматирование , підпункт Наборы значков .

Рисунок 3.20 – Умовне форматування з використанням нобору значків

Приклад 6. Використання діаграми

Для форматування клітинок таблиці з використанням діаграм потрібно на панелі Главная вибрати значок Условное форматирование, підпункт Гистограммы, обрати колір форматування.

Рисунок 3.21 – Умовне форматування з використанням діаграм

Приклад 7. Використання кольорних шкал

Для форматування клітинок таблиці з використанням кольорних шкал потрібно на панелі Главная вибрати значок Условное форматирование, підпункт Цветовые шкалы, обрати кольорну схему.

Рисунок 3.22 – Умовне форматування з використанням кольорних шкал

На відміну від попередніх версій табличного процесора, MS Excel 2007 надає більш широкі можливості проведення умовного форматування (рис. 3.23): форматування перших та останніх значень, тільки тих значень, що знаходяться вище або нижче середнього, унікальні або значення, що повторюються.

Рисунок 3.23 – Можливі варіанти створення правил форматування

3.4 Використання процесора Microsoft Excel для прогнозування економічних показників

Керівники туристичних фірм вимушені приймати рішення в умовах постійного тиску обставин, досить часто не маючи повної і точної інформації. Статистичний аналіз допомогає вилучати інформацію з данних і оцінювати якість цієї інформації. Таким чином, статистичні методи слід розглядати як важливу частину процеса прийняття рішень, що дозволяє виробляти обґрунтовані стратегічні рішення, які поєднують інтуїцію фахівців з ретельним аналізом наявної інформації. Стратегічне планування визначає поточний стан підприємства задає напрям для майбутньої діяльності, координує зусилля різних підрозділів, оптимізує розподіл ресурсів.

Для туристичного бізнесу особливе місце у статистичному аналізі посідає прогнозування економічних показників, що дозволяє визначити поточне положення фірми, задає напрям для майбутньої діяльності, координує зусилля різних підрозділів, оптимізує розподіл ресурсів. Достатньо потужним інструментом прогнозування є аналіз трендів і сезонності, що являє собою безпосередній, інтуїтивний підхід до оцінювання чотирьох компонентів помісячних або поквартальних часових рядів: довгостроковий тренд (тенденція), сезонність, циклічна варіація і нерегулярний компонент. Для спрощення сприйняття будемо розглядати довгостроковий тренд, який вказує дійсно довгострокову поведінку часового ряду і сезонну компоненту, що визначає вплив пори року.

Для проведення прогнозування Microsoft Excel пропонує декілька інструментів. Проста лінійна або експоненціальна залежність може бути екстрапольована за допомогою маркера заповнення (невеликий чорний квадрат у правому нижньом куті виділеного блоку, при наведенні на маркер заповнення покажчик приймає вигляд чорного хрестика). Для екстраполяції складних і нелінійних даних можна застосовувати стандартні функції або засіб регресійного аналізу.

Автоматичне заповнення значень.

Виділіть не менш двох клітинок, що містять початкові значення. Якщо потребується підвищити точність прогнозу, вкажіть додаткові початкові значення. Протягніть маркер заповнення у напряму зростання або зменьшення значень.

Наприклад, якщо клітинки C1:E1 містять початкові значення 3, 5 і 8, то при протягненні вправо значення будуть зростати, вліво — зменьшуватися. Автоматично клітинки заповнюються даними, що відвопідають лінійній залежності. Щоб змінити її на експоненціальну виконайте протягнення правою кнопкою мищі.

Створення арифметичної і геометричної прогресії за допомогою команди «Прогрессия»

Виділіть клітинку, у якій знаходиться перше значення створюваної прогресії.

Команда Прогрессия видаляє із клітинок попередні дані, заміняючи їх новими. Якщо необхідно зберегти попередні дані, скопіюйте їх в інше місце листа, а потім приступайте до створення прогресії.

В меню Правка оберіть пункт Заполнить, потім клацніть пункт Прогрессия (рис. 3.24).

Рисунок 3.24 – Вікно Прогрессия

Виконайте одну з наступних дій.

Якщо необхідно заповнити прогресією частину стовпця, встановіть прапорець по столбцам (Rows).

Якщо необхідно заповнити прогресією частину рядка, встановіть прапорець по строкам (Colunms).

У полі Шаг (Step value) введить число, яке визначає значення шагу прогресії.

Таблиця 3.1 – Параметри прогресій

Тип прогресії	Результат шагу
Арифметична	Шаг — це число, що додається до кожного наступного члена прогресії
Геометрична	Початкове значення помножується на шаг. Отриманий результат і кожний наступний результат помножуються на шаг.

У розділі Тип оберіть тип прогресії — арифметична або геометрична.

У полі Предельное значение (Stop value) введіть число, яке визначить значення останнього члену прогресії (воно не перевищує граничне значення).

Якщо у клітинках вже містяться перші члени прогресії і потребується, щоб Microsoft Excel створив прогресію автоматично, встановіть прапорець Автоматическое определение шага (Trend).

Визначення тенденцій за допомогою додавання лінії тренду на діаграму

Для додавання лінії тренду на діаграму потрібно зробити таке:

1.Оберіть ряд даних, до якого потрібно додати лінію тренду.

2. Оберіть команду Добавить линию тренда в меню Диаграмма.

3.На вкладці Тип оберіть потрібний тип регресійної лінії тренду. При виборі типу Полиномиальная введіть в поле Степень найбільший ступінь для незалежної змінної.

4. У полі Прогноз вперед на введіть кількість бажаних періодів, протягом яких лінія тренду буде простягатися уперед.

5. На вкладці Параметры установіть прапорці Показывать уравнение на диаграмме і Поместить на диаграммму величину достоверности аппроксимации (R^2).

6. Клакніть по кнопці ОК.

На діаграмі чорною тонкою лінією буде відображена лінія тренду (рис. 3.23). За відповідною величиною достовірності апроксимації (R²®1) за допомогою отриманого рівняння можна виконувати прогнозування даних на наступні періоди (наприклад, для 10 періоду кількість туристів буде складати 106 осіб).

Дата добавления: 2014-10-15