Лабораторна робота №1 1 Приклад виконання

Лабораторна робота №1 1
Приклад виконання
Літаки аеропорту міста обслуговують три напрямки, кожен із яких відповідає № рейсу.
1. Створити вхідну таблицю наступної структури: № рейсу, Напрямок,
Дата відльоту, Ціна квитка 1-го класу, Фактична ціна квитка, Кількість дорослих місць, Кількість дитячих місць, Клас місць, Загальна вартість квитків рейсу.
2. На аркуші «Довідник» створити таблицю наступної структури:
№ рейсу, Ціна квитка 1-го класу. Заповнити таблицю даними.
3. Заповнення даними вхідної таблиці відбувається таким чином:
3.1. для введення даних у стовпчики № рейсу, Напрямок та Клас
місць створити випадаючи списки;
3.2. поле Ціна квитка 1-го класу заповнити з використанням функції
ВПР зі звертанням до таблиці, що знаходиться на аркуші
«Довідник»;
3.3. значення поля Фактична ціна квитка обчислюється за формулою Ціна квитка 1-го класу помножена на коефіцієнт перерахунку (коефіцієнт перерахунку для бізнес - класу – 1.3, для економ-класу – 0,75);.
3.3. у стовпчику Загальна вартість квитків рейсу записати формулу, що обчислює вартість квитків по кожному рейсу відповідної дати, враховуючи, що вартість дитячого квитка становить 50% вартості дорослого квитка.
4. Здійснити наступну обробку:
4.1. Упорядкування
а) Дата відльоту – за збільшенням; б) № рейсу – за зменшенням (перший ключ), Кількість дорослих
квитків – за збільшенням (другий ключ); в) Клас місць – за зменшенням (перший ключ), Дата відльоту – за збільшенням (другий ключ), Загальна вартість квитків
рейсу – за збільшенням (третій ключ).
4.2. Фільтрація
а) № рейсу – 117; б) Загальна вартість квитків рейсу менша за середнє значення відповідного поля; в) Клас місць починається з літери «Е»; г) Кількість дорослих квитків – три найменші.
4.3. Проміжні підсумки
а) для кожного напрямку обчислити загальну вартість квитків використовуючи аркуш «Проміжні підсумки_1»; б) для кожної дати відльоту обчислити кількість рейсів, максимальну кількість дитячих квитків, використовуючи аркуш «Проміжні підсумки_2».

Лабораторна робота №1 2
Порядок виконання роботи:
1. Натискаємо праву кнопку миші на ярлику аркуша Лист1 та обираємо команду «Переименовать», вводимо назву «Початкова таблиця».
2. Натискаємо праву кнопку миші на ярлику «Початкова таблиця» та обираємо команду «Цвет ярлычка…» і в діалоговому вікні (рис. 4.1) серед можливих кольорів обираємо необхідний, натискаємо ОК.
Рис. 4.1. Діалогове вікно обрання кольору ярлика
3. Аналогічно перейменовуємо назву аркуша Лист2 на «Довідник» і зафарбовуємо ярлик аркуша «Довідник».
4. Створюємо структуру вхідної таблиці на аркушу «Початкова таблиця»
(рис. 4.2).
Рис. 4.2. Структура таблиці
5. Створюємо у вільному місці аркушу «Початкова таблиця» списки можливих значень (рис. 4.3).
Рис. 4.3. Списки можливих значень
6. Створюємо випадаючий список для введення даних у комірки стовпчика А. Для цього виділяємо стовпчик A, в який будемо вводити значення із списку можливих значень поля «№ рейсу». Натискаємо кнопку

Лабораторна робота №1 3 на панелі Работа с данными вкладки Данные, в діалоговому вікні (рис.
4.4) у вкладці «Параметры» у полі «Тип данных» обираємо Список, а у полі
«Источник:» вказуємо діапазон комірок $K$2: $K$4, де розташовано можливі варіанти поля «№ рейсу».
Рис. 4.4. Діалогове вікно «Проверка вводимых значений»
7. Аналогічно створюємо випадаючи списки для комірок стовпчиків B і H.
Тепер при активізації будь-якої комірки стовпчиків A, B, H біля комірки праворуч буде з’являтися кнопка
, при натисканні на яку з’явиться випадаючий список (рис. 4.5), що був сформований із зазначених можливих значень.
Рис. 4.5. Випадаючий список можливих значень
8. Заповнюємо комірки таблиці «№ рейсу», «Напрямок», «Клас місць» використовуючи випадаючи списки. Стовпчики «Дата відльоту», «Кількість дорослих квитків», «Кількість дитячих квитків» заповнюємо числовими даними.
Результат заповнення таблиці представлений на рисунку 4.6.

Лабораторна робота №1 4
Рис. 4.6. Заповнення аркушу «Початкова таблиця»
9. Переходимо на аркуш «Довідник» і створюємо структуру таблиці
«Довідник» з полями «№ рейсу» та «Ціна квитка 1-го класу», заповнюємо таблицю даними як на рисунку 4.7.
Рис. 4.7. Аркуш «Довідник»
10.
Заповнюємо поле «Ціна квитка 1 класу» аркушу «Початкова таблиця», використовуючи функцію ВПР зі звертанням до аркуша
«Довідник», здійснюючи наступні дії:
10.1. активізуємо комірку D2 на аркуші «Початкова таблиця»;
10.2. натискаємо на кнопку у рядку формул;
10.3. у діалоговому вікні «Мастер функций» обираємо у полі
«Категория:» категорію Ссылки и массивы, а у полі «Выберите функцию:» функцію ВПР, натискаємо ОК;
10.4. у діалоговому вікні «Аргуменеты функции» заповнюємо поля відповідно до рисунка 4.8;

Лабораторна робота №1 5
Рис. 4.8. Заповнення діалогового вікна «Аргументы функции» функції ВПР
10.5. натискаємо ОК;
10.6. копіюємо формулу з комірки D2 у комірки D3:D12 методом автозаповнення.
11.
Обчислюємо поле «Фактична ціна», для цього здійснюємо наступні дії:
11.1. натискаємо на кнопку у рядку формул;
11.2. у діалоговому вікні «Мастер функций» обираємо у полі
«Категория:» категорію Логические, а у полі «Выберите функцию:» функціюЕСЛИ, натискаємо ОК;
11.3. заповнюємо поля діалогового вікна відповідними значеннями як на рисунку 4.9;
Рис. 4.9. Заповнення діалогового вікна «Аргументы функции» функції
ЕСЛИ

Лабораторна робота №1 6
11.4. ставимо курсор у поле «Значение_если_ложь» діалогового вікна
(рис. 4.9) і натискаємо кнопку у рядку формул, у випадаючому списку (рис. 4.10) обираємо функцію ЕСЛИ, натискаємо ОК;
Рис. 4.10. Випадаючий список функцій
11.5. заповнюємо параметри діалогового вікна вкладеної функції ЕСЛИ відповідними значеннями як на рисунку 4.11;
Рис. 4.11. Заповнення діалогового вікна «Аргументы функции» для вкладеної функції ЕСЛИ
11.6. натискаємо ОК;
11.7. копіюємо формулу з комірки E2 у комірки E3: E12 методом автозаповнення.
В результаті зазначених дій у комірці E2 буде введена формула

Лабораторна робота №1 7
12.
Обчислюємо поле «Загальна вартість квитків рейсу». У комірку I2 вводимо формулу
і копіюємо її у комірки I3:I12.
13.
Застосовуємо умовне форматування до поля «Загальна вартість квитків рейсу». Умова наступна: якщо значення поля менше 10000, то необхідно застосувати до таких комірок світло-червоне забарвлення. Для цього здійснюємо наступні дії:
13.1. виділяємо діапазон комірок I2:I12;
13.2. натискаємо кнопку та обираємо варіант
,
;
13.3. у діалоговому вікні задаємо значення полів як на рисунку 4.12;
Рис. 4.12. Діалогове вікно введення параметрів умовного форматування
13.4. натискаємо ОК.
Результат виконання представлений на рисунку 4.13.
Рис. 4.13. Аркуш «Початкова таблиця» після проведених обчислень
14.
Робимо три копії аркуша «Початкова таблиця». Для цього:
14.1. натискаємо праву кнопку миші на ярлику «Початкова таблиця»;
14.2. обираємо команду «Переместить или скопировать…» та заповнюємо поля діалогового вікна відповідно до рисунку 4.14;

Лабораторна робота №1 8
Рис. 4.14 Діалогове вікно «Переместить или скопировать»
14.3. натискаємо праву кнопку миші на ярлику «Початкова таблиця (2)»;
14.4. обираємо команду «Переименовать» і вводимо назву
«Упорядкування_1».
Аналогічно створюємо аркуші «Упорядкування_2», «Упорядкування_3».
15.
Здійснюємо упорядкування за «Дата відльоту – за збільшенням».
Для цього:
15.1. переходимо на аркуш «Упорядкування_1»;
15.2. виділяємо будь-яку комірку з діапазону A1:A12 (структура таблиці);
15.3. натискаємо кнопку на панелі Сортировка и фильтр вкладки Данные;
15.4. у полі «Сортировать по» обираємо поле «Дата відльоту» та порядок «От старых к новым» (рис. 4.15);

Лабораторна робота №1 9
Рис. 4.15. Заповнення діалогового вікна «Сортировка» для умови 4.1.а)
15.5. натискаємо ОК;
15.6. вставляємо перед першим рядком таблиці новий рядок. Для цього:
15.6.1. активізуємо будь-яку комірку рядка «1»;
15.6.2. натискаємо кнопку панелі Ячейки вкладки Главная та обираємо варіант
;
15.6.3. вводимо назву заголовка «Дата відльоту – за збільшенням».
Результат виконання представлений на рисунку 4.16.
Рис. 4.16. Упорядкована таблиця за умовою 4.1.а)
16.
Здійснюємо упорядкування за «№ рейсу – за зменшенням (перший ключ), Кількість дорослих квитків – за збільшенням (другий ключ)». Для цього:
16.1. переходимо на аркуш «Упорядкування_2»;
16.2. виділяємо будь-яку комірку з діапазону A1:A12 (структура таблиці);
16.3. натискаємо кнопку на панелі Сортировка и фильтр вкладки Данные;
16.4. у полі «Сортировать по» обираємо поле «№ рейсу» та порядок
«По убыванию»; натискаємо кнопку
; у полі «Затем по» обираємо поле «Кількість дорослих квитків» та порядок «По возрастанию» (рис. 4.17);

Лабораторна робота №1 10
Рис. 4.17. Заповнення діалогового вікна «Сортировка» для умови 4.1.б)
16.5. вставляємо перед першим рядком таблиці новий рядок. Для цього:
16.5.1. активізуємо будь-яку комірку рядка «1»;
16.5.2. натискаємо кнопку панелі Ячейки вкладки Главная та обираємо варіант
;
16.5.3. вводимо назву заголовка «№ рейсу – за зменшенням, Кількість дорослих квитків – за збільшенням»; натискаємо ОК.
Результат виконання представлений на рисунку 4.18.
Рис. 4.18. Упорядкована таблиця за умовою 4.1.б)
17.
Здійснюємо упорядкування за «Клас місць – за зменшенням
(перший ключ), Дата відльоту – за збільшенням (другий ключ), Загальна вартість квитків рейсу – за збільшенням (третій ключ)». Для цього:
17.1. переходимо на аркуш «Упорядкування_3»;
17.2. виділяємо будь-яку комірку з діапазону A1:A12 (структура таблиці);
17.3. натискаємо кнопку на панелі Сортировка и фильтр вкладки Данные;
17.4. заповнюємо параметри діалогового вікна «Сортировка» як на рисунку 4.19;

Лабораторна робота №1 11
Рис. 4.19. Заповнення діалогового вікна «Сортировка» для умови 4.1.в)
17.5. натискаємо ОК;
17.6. вставляємо перед першим рядком таблиці новий рядок і вводимо назву заголовка «Клас місць – за зменшенням, Дата відльоту – за збільшенням, Загальна вартість квитків рейсу – за збільшенням».
Результат виконання представлений на рисунку 4.20.
Рис. 4.20. Упорядкована таблиця за умовою 4.1.в)
18.
Робимо чотири копії аркуша «Початкова таблиця». Для цього:
18.1. натискаємо праву кнопку миші на ярлику «Початкова таблиця»;
18.2. обираємо команду «Переместить или скопировать…» та заповнюємо поля діалогового вікна як на рисунку 4.21;

Лабораторна робота №1 12
Рис. 4.21. Діалогове вікно «Переместить или скопировать»
18.3. натискаємо праву кнопку миші на ярлику «Початкова таблиця (2)»;
18.4. обираємо команду «Переименовать» і вводимо назву
«Автофільтр_1».
Аналогічно створюємо аркуші «Автофільтр _2», «Автофільтр _3»,
«Автофільтр _4».
19.
Здійснюємо фільтрацію за «№ рейсу – 117». Для цього:
19.1. переходимо на аркуш «Автофільтр_1»;
19.2. виділяємо будь-яку комірку з діапазону A1:A12 (структура таблиці);
19.3. натискаємо кнопку на панелі Сортировка и фильтр вкладки
Данные;
19.4. на заголовках структури таблиці з’являються кнопки автофільтру;
19.5. натискаємо на кнопку у полі «№ рейсу» та у діалоговому вікні обираємо перемикач як на рисунку 4.22;

Лабораторна робота №1 13
Рис. 4.22. Діалогове вікно автофільтру для умови 4.2.а)
19.6. натискаємо ОК;
19.7. вставляємо перед першим рядком таблиці новий рядок і вводимо назву заголовка «№ рейсу – 117».
Результат виконання представлений на рисунку 4.23.
Рис. 4.23. Результат застосування автофільтру за умовою 4.2.а)
20.
Здійснюємо фільтрацію за «Загальна вартість квитків рейсу менша за середнє значення відповідного поля». Для цього:
20.1. переходимо на аркуш «Автофільтр_2»;
20.2. виділяємо будь-яку комірку з діапазону A1:A12 (структура таблиці);
20.3. натискаємо кнопку на панелі Сортировка и фильтр вкладки Данные;

Лабораторна робота №1 14 20.4. натискаємо на кнопку у полі «Загальна вартість квитків рейсу» та обираємо варіант «Числовые фильтры» та опцію «Ниже среднего» у діалоговому вікні (рис. 4.24);
Рис. 4.24. Діалогове вікно автофільтру для умови 4.2.б)
20.5. вставляємо перед першим рядком таблиці новий рядок і вводимо назву заголовка «Загальна вартість квитків рейсу менша за середнє значення відповідного поля».
Результат виконання представлений на рисунку 4.25.
Рис. 4.25. Результат застосування автофільтру за умовою 4.2.б)
21.
Здійснюємо фільтрацію за «Клас місць починається з літери «Е»».
Для цього:

Лабораторна робота №1 15 21.1. переходимо на аркуш «Автофільтр_3»;
21.2. виділяємо будь-яку комірку з діапазону A1:A12 (структура таблиці);
21.3. натискаємо кнопку на панелі Сортировка и фильтр вкладки Данные;
21.4. натискаємо на кнопку у полі «Клас місць» і обираємо варіант
«Текстовые фильтры» та опцію «начинается с…»;
21.5. заповнюємо параметри діалогового вікна «Пользовательский автофильтр» як на рисунку 4.26;
Рис. 4.26. Діалогове вікно «Пользовательский автофильтр» для умови
4.2.в)
21.6. вставляємо перед першим рядком таблиці новий рядок і вводимо назву заголовка «Клас місць починається з літери «Е»».
Результат виконання представлений на рисунку 4.27.
Рис. 4.27. Результат застосування автофільтру за умовою 4.2.в)
22.
Здійснюємо фільтрацію за «Кількість дорослих квитків – три найменші». Для цього:
22.1. переходимо на аркуш «Автофільтр_4»;
22.2. виділяємо будь-яку комірку з діапазону A1:A12 (структура таблиці);

Лабораторна робота №1 16 22.3. натискаємо кнопку на панелі Сортировка и фильтр вкладки Данные;
22.4. натискаємо на кнопку у полі «Кількість дорослих квитків» і обираємо варіант «Числовые фильтры» та опцію «Первые 10…»;
22.5. заповнюємо параметри діалогового вікна «Наложение условий
по списку» як на рисунку 4.28;
Рис. 4.28. Діалогове вікно «Наложение условий по списку» для умови
4.2.г)
22.6. натискаємо ОК;
22.7. вставляємо перед першим рядком таблиці новий рядок і вводимо назву заголовка «Кількість дорослих квитків – три найменші».
Результат виконання представлений на рисунку 4.29.
Рис. 4.29. Результат застосування автофільтру за умовою 4.2.г)
23.
Робимо дві копії аркуша «Початкова таблиця». Для цього:
23.1. натискаємо праву кнопку миші на ярлику «Початкова таблиця»;
23.2. обираємо команду «Переместить или скопировать…» і заповнюємо поля діалогового вікна як на рисунку 4.30;

Лабораторна робота №1 17
Рис. 4.30. Діалогове вікно «Переместить или скопировать»
23.3. натискаємо праву кнопку миші на ярлику «Початкова таблиця (2)»;
23.4. обираємо команду «Переименовать» і вводимо назву «Проміжні підсумки_1».
Аналогічно створюємо аркуш «Проміжні підсумки_2».
24.
Здійснюємо обчислення для кожного напрямку загальної вартості квитків. Для цього:
24.1. переходимо на аркуш «Проміжні підсумки_1;
24.2. упорядковуємо таблицю по полю «№ рейсу», а саме: активізуємо будь-яку комірку таблиці; натискаємо кнопку на панелі
Сортировка и фильтр вкладки Данные; заповнюємо параметри діалогового вікна «Сортировка» як на рисунку 4.31; натискаємо ОК;
Рис. 4.31. Заповнення діалогового вікна «Сортировка» для упорядкування поля «Напрямок»
24.3. активізуємо будь-яку комірку таблиці;
24.4. натискаємо кнопку на панелі Структура вкладки
Данные;
24.5. заповнюємо параметри діалогового вікна «Промежуточные итоги» як на рисунку 4.32;

Лабораторна робота №1 19
Рис. 4.34. Вигляд Обчислення проміжних підсумків для умови 4.3.а)
25.
Для кожної дати відльоту обчислюємо кількість рейсів та максимальну кількість дитячих квитків. Для цього:
25.1. переходимо на аркуш «Проміжні підсумки_2;
25.2. упорядковуємо таблицю по полю «Дата відльоту, а саме:
25.2.1. активізуємо будь-яку комірку таблиці;
25.2.2. натискаємо кнопку на панелі Сортировка и фильтр вкладки Данные;
25.2.3. заповнюємо параметри діалогового вікна «Сортировка» як на рисунку 4.35;
Рис. 4.35. Заповнення діалогового вікна «Сортировка» для упорядкування поля «Дата відльоту»
25.2.4. натискаємо ОК;
25.3. активізуємо будь-яку комірку таблиці;
25.4. натискаємо кнопку на панелі Структура вкладки
Данные;
25.5. заповнюємо параметри діалогового вікна «Промежуточные итоги» як на рисунку 4.36;

Лабораторна робота №1 20
Рис. 4.36. Заповнення діалогового вікна «Промежуточные итоги» для 1- шої частини умови 4.3.б)
25.6. натискаємо ОК;
25.7. натискаємо кнопку на панелі Структура вкладки
Данные;
25.8. заповнюємо параметри діалогового вікна «Промежуточные итоги» як на рисунку 4.37; натискаємо ОК.
Рис. 4.37. Заповнення діалогового вікна «Промежуточные итоги» для 2-гої частини умови 4.3.б)
Результат представлений на рисунку 4.38.

Лабораторна робота №1 22 26.
Для побудови змістовної діаграми на окремому аркуші необхідно проаналізувати, що необхідно представити на діаграмі. Наприклад, потрібно представити загальні вартості квитків кожного напрямку рейсу. Для поставленої задачі найкраще обрати тип діаграми «Кругова» і виконати наступні дії:
26.1. перейти на аркуш «Проміжні підсумки_1» та натиснути кнопку у верхньому лівому кутку таблиці;
26.2. виділити діапазон комірок як на рисунку 4.39;
Рис. 4.39. Обрання даних для побудови діаграми
26.3. натиснути кнопку на панелі Диаграммы вкладки Вставка;
26.4. обрати тип діаграми, наприклад,
;
26.5. натиснути кнопку на панелі Макеты диаграмм контекстно-залежної вкладки Работа с диаграммами (Конструктор);
26.6. натиснути кнопку на панелі Расположение контекстно- залежної вкладки Работа с диаграммами (Конструктор);
26.7. заповнити параметри діалогового вікна
«Перемещение диаграммы» як на рисунку 4.40;
Рис. 4.40. Діалогове вікно «Перемещение диаграммы»