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

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

Ярлик сторінки «Лист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

67