Практичне завдання*

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

Вхідні дані: інформація про замовлення (продаж) путівок на фірмі.

Методичні вказівки

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

Порядок виконання завдання наступний.

1. На основі вхідних даних організувати базу даних замовлень і продажу путівок (не менше 10 позицій в кожній таблиці, напр. Назва туру: Львів-Прага; Дата – 20.12.2010 і т.д.). На окремих робочих листах навести список замовлень (лист 1) і продажу (лист 2) путівок такого змісту:

Назва туру

Кількість замовлених (Лист1)/ проданих (Лист 2) путівок

Дата

Ціна

Вартість

Назву туру та інші дані таблиці кожний студент вибирає самостійно.

Початок таблиці в комірці А1. Створити і заповнити таблицю, присвоїти фінансовий формат стовпцям ціна і сума (грн.., без десяткових знаків). Передбачити, щоб колонка Вартість розраховувалася автоматично (за формулою). Кожний робочий лист відповідно перейменувати.

2. Сформувати зведену таблицю з замовлення (лист 3) і продажу (лист 4) путівок, використовуючи меню Данные – Сводная таблица. Вказуємо діапазон – таблицю в базі даних на листі 1 (з замовлення), тобто виділяємо всі стовпці. Коли виділили діапазон, натискаємо клавішу «Дальше», у закладці «Макет» розставляємо назви рядків і стовпців. Назву туру переносимо в поле «Рядок», кількість і вартість переносимо в поле «Дані». Для підрахунку поля зведеної таблиці «Кількість» (клікнути мишею двічі по полю кількість), вибрати операцію «Сума» і назвати «Загальна кількість», присвоїмо формат «загальний». Для підрахунку поля «Вартість» також виберемо операцію «Сума» і назвемо «Загальна вартість». Тут вибираємо формат «фінансовий» без десяткових знаків. Далі натискаємо клавішу «Готово» і отримуємо таблицю такої форми:

	Дані	Всього
Назва туру	Загальна кількість
	Загальна вартість

Ми отримали зведену таблицю замовлення путівок.

Аналогічно на листі 4 сформуємо зведену таблицю продажу путівок. Але діапазон будемо вибирати на листі 2 (база даних з продажу).

3. Дана система повинна бути динамічною, тобто ми повинні мати можливість автоматично додавати і вилучати записи. Для цього створимо макроси, які назвемо «Замовлення» (лист 3) і «Продаж» (лист 4).

Спочатку створимо спеціальні функціональні кнопки і розмістимо їх біля зведених таблиць (Лист3/Лист4). Для цього в меню вибираємо Вид – Панель инструментов – Форми і кнопка. Розміщуємо її і назвемо макрос «Замовлення»/ «Продаж» – Записать. Опишемо: додати записи про нове замовлення. Після натиску на клавішу OK почався запис макросу.

Переходимо на лист 1 і розмістимо курсор в будь-якому місці таблиці (напр., в А2). Звертаємося в меню «Дані» і вибираємо рядок «Форма». Натискаємо клавішу Добавить, додамо нові дані в поля Назва туру, Кількість, Дата, Ціна, натиснути клавішу Закрыть. Запис додано.

Перейдемо на лист 3 і обновимо дані зведеної таблиці. Для цього розмістити курсор в будь-якій комірці зведеної таблиці, натиснути праву клавішу миші (ПКМ) і відмітити рядок «Обновить данные». Далі виконуємо: меню Сервис-Макрос-Остановить запись. Ми записали макрос.

Перейменувати функціональну клавішу відповідно до призначення – ПКМ − Переименовать (Нове замовлення/Новий продаж) і перевірити роботу створеного макроса, натиснувши на створену кнопку. Якщо макрос не зависає, − він працює вірно; в іншому випадку − його потрібно удалити (Сервис-Макрос-Макросы-Удалить) і ще раз переробити.

4. Сформувати таблицю, яка буде містити дані про фактичну наявність кожної путівки на фірмі.

Перед формуванням даної таблиці необхідно виконати наступне. У базу даних із продажу путівок необхідно додати стовпець між Назва туру і Кількість, який буде дублювати дані стовпця Кількість, але зі знаком «-». Новий стовпець назвати також «Кількість «-»».

На робочому листі 5, де буде формуватися дана таблиця, відзначити її початок (А1) і відповідно перейменувати лист.

Виконати команду Данные-Сводная таблица.

В першому діалоговому вікні виділити: в кількох діапазонах консолідації. Далі перемикач потрібно встановити в положення зведена таблиця. В другому вікні вказати: створити одне поле сторінки. В наступному вікні вказуємо, де містяться дані для формування зведеної таблиці. Для цього потрібно на листах, які містять бази даних замовлення і продажу путівок, вказати потрібний діапазон, тобто виділити на листі 1 і 2 по два стовпці: Назва туру і Кількість, на листі 2 – Назва туру і Кількість з «-».

В наступному діалоговому вікні «Макет» вказати структуру таблиці. Групуємо такі дані: Назва туру (поле Рядок) і Кількість (поле Дані) (див. п. 2).

В останньому діалоговому вікні виконати такі установки. Клацнути клавішу Параметри і в полі Формат відключити перемикач Загальна сума по рядках. Після клацання клавіші Готово на робочому листі буде створена таблиця про фактичний стан фірми.

5. Створити макрос для автоматичного перерахування даних таблиці про фактичний стан фірми. Аналогічно як створювали для макросів «Замовлення» і «Продаж» створюємо кнопку (див. п.3). Макрос називаємо «Фактичні_дані» − Записать. Описуємо: перерахунок даних – ОК. Встаємо в комірку зведеної таблиці, права клавіша мишки – Обновить данные. Зупинити макрос (див. п. 3). Перейменувати функціональну клавішу відповідно до призначення.

* Файл з практичним завданням записати на CD/DVD-RW.

Львівський інститут економіки і туризму Контрольна робота з дисципліни «Інформаційні системи та технології» студента(ки) ____ курсу групи _________ ПІБ____________________________ Варіант № ___ Адреса: __________________ __________________ __________________ Дата:_________________

Львів