Приклад виконання завдання.

Літаки аеропорту міста обслуговують три напрямки, кожен із яких визначається № рейсу.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: № рейсу, Напрямок, Дата відльоту, Ціна одного квитка 1 класу, Фактична ціна квитка, Кількість дорослих квитків, Кількість дитячих квитків, Клас польоту, Вартість квитків.

На аркуші «Довідник» створити таблицю наступної структури: № рейсу, Ціна одного квитка 1 класу. Заповнити таблицю даними.

Заповнення даними вихідної таблиці відбувається таким чином:

1. для введення даних у колонки № рейсу, Напрямок та Клас польоту організувати списки, що розкриваються.

2. поле Ціна одного квитка 1 класу заповнити, використовуючи функцію ВПР зі звертанням до таблиці, що знаходиться на аркуші «Довідник».

3. в колонці Вартість квитків записати формулу, яка обчислює вартість квитків по кожному рейсу відповідної дати. Фактична ціна квитка обчислюється за формулою Середня ціна квитка помножена на коефіцієнт перерахунку. Коефіцієнт перерахунку для бізнес - класу – 1.3, для економ-класу – 0,75. Вартість дитячого квитка становить 50% вартості дорослого.

Завдання 1.1. Упорядкування

1. Дата відльоту – за збільшенням;

2. № рейсу – за зменшенням (первинний ключ), Кількість дорослих квитків – за збільшенням (вторинний ключ);

3. Клас польоту – за зменшенням (первинний ключ), Дата відльоту – за збільшенням (вторинний ключ), Вартість квитків – за збільшенням (третинний ключ).

Завдання 1.2. Фільтрація

№ рейсу – 117;

Вартість квитків знаходиться в межах від мінімального до середнього значення відповідного поля;

Вартість квитків – найбільша; Клас польоту – Економ;

Кількість дорослих квитків – три найменші.

Завдання 1.3. Проміжні підсумки

Для кожного рейсу обчислити загальну вартість квитків та максимальну кількість дитячих квитків (аркуш «Проміжні підсумки»);

Для кожної дати відльоту обчислити кількість рейсів, кількість дорослих та дитячих квитків (аркуш «Проміжні підсумки_1»).

Виконання:

1. Створюємо «шапку» таблиці (рис. 54).

Рис. 54

2. Створюємо у вільному місці Лист1 списки можливих значень (рис. 55).

Рис. 55

3. Виділяємо стовпчик A, в який будемо вводити значення із списку можливих значень № рейсу. Вибираємо КМ Данные – Проверка, в діалоговому вікні у вкладці Параметры вибираємо у полі Тип данных – Список і вказуємо у полі Источник діапазон клітин $O$2: $O$4 (№ рейсу).

4. Виділяємо стовпчик B, КМ Данные – Проверка, в діалоговому вікні у вкладці Параметры вибираємо у полі Тип данных – Список і вказуємо у полі Источник діапазон клітин $M$2: $M$4 (Напрямок).

5. Виділяємо стовпчик H, КМ Данные – Проверка, в діалоговому вікні у вкладці Параметры вибираємо у полі Тип данных – Список і вказуємо у полі Источник діапазон клітин $N$2: $N$4 (Клас польоту) (рис. 56).

Рис. 56

Тепер при активізації будь-якої клітини стовпчиків A, B, H біля клітини праворуч з’являється трикутник, при натисканні на який з’являється розкриваючий список можливих значень (рис. 57).

Рис. 57

6. Заповнюємо колонки таблиці № рейсу, Напрямок, Клас польоту використовуючи, списки що розкриваються. Колонки Дата відльоту, Кількість дорослих квитків, Кількість дитячих квитків заповнюємо даними.

7. Натискаємо праву кнопку миші (ПКМ) на ярлику Лист1, команда Переименовать, замість Лист1 вводимо назву «Початкова таблиця».

8. Натискаємо ПКМ на ярлику «Початкова таблиця», команда Цвет ярлычка… , в діалоговому вікні «Выбор цвета ярлычка» вказуємо потрібний колір, ОК (рис.58).

Рис. 58

Результат заповнення таблиці представлений на рис. 59.

Рис. 59

9. Переходимо на Лист2 і створюємо структуру другої таблиці заповнюємо її даними (рис. 60).

Рис. 60

10. Перейменовуємо Лист2 на «Довідник» та зафарбовуємо ярлик жовтим кольором (рис. 61).

Рис. 61

11. Заповнюємо поле Ціна одного квитка 1 класу, використовуючи функцію ВПР зі звертанням до аркуша «Довідник».

• Активізуємо клітину D2 на аркуші «Початкова таблиця», , в діалоговому вікні вибираємо Категория: Ссылки и массивы, Функция: ВПР, ОК.

• З’являється діалогове вікно і заповнюємо його відповідно (рис. 62).

Рис. 62

• Копіюємо формулу з клітини D2 у клітини D3: D12 методом автозаповнення.

1. Обчислюємо поле Фактична ціна.

• , в діалоговому вікні вибираємо Категория: логические, Функция: ЕСЛИ, ОК.

• Заповнюємо перші два вікна відповідними значеннями (рис.63).

Рис. 63

• ставимо курсор у третє вікно і натискаємо кнопку , у розкриваючому списку (рис. 64), вибираємо функцію ЕСЛИ, ОК.

Рис. 64

• З’являється діалогове вікно функції ЕСЛИ, яка буде вкладеною функцією і заповнюємо його відповідно умові задачі (рис. 65).

Рис. 65

У клітині E2 буде введена формула:

• Копіюємо формулу з клітини E2 у клітини E3: E12 методом автозаповнення.

  1. Обчислюємо поле Вартість квитків. У клітину I2 вводимо формулу і копіюємо її в I3: I12.

Результат виконання представлений на рис. 66.

Рис. 66

Таблиця у формульному вигляді (рис 67):

Рис. 67

Завдання 1.1.

Робимо три копії аркуша «Початкова таблиця». Для цього натискаємо ПКМ на ярлику «Початкова таблиця», команда Переместить/скопировать…, в діалоговому вікні у полі перед листом: вказуємо Лист3 та відмічаємо перемикач Создавать копию. Створюємо ще дві копії.

Аркуш «Початкова таблиця (2)» перейменовуємо на «Упорядкування», Аркуш «Початкова таблиця (3)» – «Автофільтр», аркуш «Початкова таблиця (4)» на «Проміжні підсумки». ПКМ на відповідному ярлику, команда Переименовать, вводимо потрібну назву аркуша.

Робимо ще дві копії аркуша «Упорядкування».

Упорядкування. Дата відльоту – за збільшенням.

1. Переходимо на аркуш «Упорядкування».

2. Виділяємо всю таблицю разом з «шапкою» (діапазон клітин A1: I12), КМ Данные – Сортировка.

3. У полі Сортировать по вказуємо поле Дата відльоту та відмічаємо перемикач по возрастанию (рис. 68).

Рис. 68

1. Вставляємо перед першим рядком таблиці новий рядок. Для цього активізуємо клітину A1, КМ Вставка – Строки.

1. Вводимо заголовок для таблиці «Дата відльоту – за збільшенням».

Результат виконання представлений на рис. 69.

Рис. 69

Упорядкування. № рейсу – за зменшенням (первинний ключ), Кількість дорослих квитків – за збільшенням (вторинний ключ).

1. Переходимо на аркуш «Упорядкування (2)».

2. Виділяємо всю таблицю разом з «шапкою» (діапазон клітин A1: I12), КМ Данные – Сортировка.

3. У полі Сортировать по вказуємо поле № рейсу та відмічаємо перемикач по убыванию, у полі Затем по вказуємо поле Кількість дорослих квитків та відмічаємо перемикач по возрастанию (рис. 70).

Рис. 70

Результат виконання представлений на рис. 71.

Упорядкування. Клас польоту – за зменшенням (первинний ключ), Дата відльоту – за збільшенням (вторинний ключ), Вартість квитків – за збільшенням (третинний ключ).

1. Переходимо на аркуш «Упорядкування (3)».

2. Виділяємо всю таблицю разом з «шапкою» (діапазон клітин A1: I12). КМ Данные – Сортировка.

3. У полі Сортировать по вказуємо поле Клас польоту та відмічаємо перемикач по убыванию, у полі Затем по вказуємо поле Дата відльоту та відмічаємо перемикач по возрастанию, у полі В последнюю очередь, по вказуємо поле Вартість квитків та відмічаємо перемикач по возрастанию (рис. 72).

4. Вставляємо в таблицю рядок, вводимо заголовок для таблиці «Клас польоту – за зменшенням, дата відльоту – за збільшенням, вартість квитків – за збільшенням».

Рис. 71

Рис. 72

Результат виконання представлений на рис. 73.

Рис. 73

Завдання 1.2.

Фільтрація. № рейсу – 117.

1. Переходимо на аркуш «Автофільтр» і робимо три копії цього аркуша.

2. Виділяємо всю таблицю (діапазон клітин A1: I12).

3. КМ Данные – Фильтр – Автофильтр. На заголовках таблиці з’являються . Натискаємо на у полі № рейсу та вибираємо із списку значення 117 (рис. 74).

Рис. 74

4. Вставляємо рядок для заголовка та вводимо заголовок таблиці.

Результат виконання представлений на рис. 75.

Рис. 75

Фільтрація. Вартість квитків знаходиться в межах від мінімального до середнього значення відповідного поля.

1. Переходимо на аркуш «Автофільтр (2)». Обчислюємо середнє та мінімальне значення поля Вартість квитків.

• Активізуємо клітину I13, , в діалоговому вікні вибираємо Категория: Статистические, Функция: МИН, ОК. В діалоговому вікні вказуємо діапазон I2: I12. У клітину I13 буде введена формула .

• Активізуємо клітину I14, , в діалоговому вікні вибираємо Категория: Статистические, Функция: СРЗНАЧ, ОК. В діалоговому вікні вказуємо діапазон I2: I12. У клітину I14 буде введена формула (рис. 76).

Рис. 76

2. Виділяємо всю таблицю (діапазон клітин A1: I12).

3. КМ Данные – Фильтр – Автофильтр. Натискаємо на у полі Вартість квитків та вибираємо із списку опцію (Условие…). В діалоговому вікні заповнюємо відповідні поля наступним чином (рис. 77).

Рис. 77

4. Вставляємо рядок для заголовка та вводимо заголовок таблиці (рис. 78).

3. Вставляємо рядок для заголовка та вводимо заголовок таблиці (рис.80).

Рис. 78

Рис. 79

Рис. 80

Фільтрація. Кількість дорослих квитків – три найменші.

1. Переходимо на аркуш «Автофільтр (4)». Виділяємо всю таблицю (діапазон клітин A1: I12).

2. КМ Данные – Фильтр – Автофильтр. Натискаємо на у полі Кількість дорослих квитків та вибираємо опцію (Первые 10…). В діалоговому вікні заповнюємо відповідні поля наступним чином (рис. 81).

Рис. 81

3. Вставляємо рядок для заголовка та вводимо заголовок таблиці (рис. 82).

Рис. 82

Завдання 1.3.

Проміжні підсумки. Для кожного рейсу обчислити загальну вартість квитків та максимальну кількість дитячих квитків (аркуш «Проміжні підсумки»).

1. Переходимо на аркуш «Проміжні підсумки» та робимо копію цього аркуша. Перейменовуємо копію аркуша в «Проміжні підсумки_1».

2. Виділяємо всю таблицю разом з «шапкою» (діапазон клітин A1: I12).

3. Упорядковуємо таблицю по полю № рейсу (КМ – Сортировка, Сортировать по № рейсу, перемикач по возрастанию ).

4. Виділяємо всю таблицю (діапазон клітин A1: I12). КМ Данные – Итоги… В діалоговому вікні заповнюємо поля відповідно умові Завдання 1.3 (а) (рис. 83).

Рис. 83

Результат представлений на рис. 84.

Рис. 84

При натисканні на кнопку у верхньому лівому кутку таблиці залишаються проміжні підсумки по кожному рейсу та загальний проміжний підсумок (рис. 85).

Рис. 85

5. Виділяємо всю таблицю (діапазон клітин A1: I12). КМ Данные – Итоги… В діалоговому вікні заповнюємо поля При каждом изменении в: № рейсу, Операция: Максимум, перемикач у полі Добавить итоги по: Кількість дитячих квитків та відключаємо перемикач Заменить текущие итоги (рис. 86).

Рис. 86

Результат виконання представлений на рис. 87.

Рис. 87

Проміжні підсумки. Для кожної дати відльоту обчислити кількість рейсів, кількість дорослих та дитячих квитків (аркуш «Проміжні підсумки_1».

1. Переходимо на аркуш «Проміжні підсумки_1».

2. Виділяємо всю таблицю (діапазон клітин A1: I12).

3. Упорядковуємо таблицю по полю Дата відльоту (КМ – Сортировка, Сортировать по Дата відльоту, перемикач по возрастанию).

4. Виділяємо всю таблицю (діапазон клітин A1: I12), КМ Данные – Итоги… В діалоговому вікні заповнюємо поля відповідно умові Завдання 1.3(б) рис. 88.

Рис. 88

Результат виконання представлений на рис. 89.

Рис. 89

66