Обчислення проміжних підсумків

В Excel для автоматичного обчислення проміжних підсумків призначена КМ Данные – Итоги. Ця команда дозволяє обчислювати проміжні підсумки для групи записів, у яких співпадають значення вказаного поля. Перед виконанням цієї команди список повинен бути упорядкований. Для обчислення проміжних підсумків потрібно виконати наступне:

 

1. Виділити таблицю разом з її структурою («шапкою»);

2. КМ Данные – Итоги

3. В діалоговому вікні Промежуточные итоги у полісписку При каждом изменениив вибрати заголовок стовпчика, для якого потрібно обчислити проміжні підсумки після кожної зміни даних;

4. У полі списку Операция вибрати потрібну опцію;

5. У полі списку Добавить итоги виводяться назви всіх полів списку. Активізувати поле або поля, для яких потрібно обчислити проміжні підсумки;

6. Натиснути кнопку ОК.

В діалоговому вікні Промежуточные итоги знаходяться наступні опції:

1. Заменить текущие итоги. Будь – які існуючі формули проміжних підсумків замінюються новими.

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

3. Итоги под данными. За замовченням рядки, що містять значення проміжних підсумків, вставляються під рядками з початковими даними. Якщо проміжні підсумки необхідно розмістити над початковими даними, потрібно відключити опцію Итоги под данными.

4. Убрать все. Із спискувилучаються всі рядки з проміжними підсумками.

5.Для обробки даних можуть використовуватися функції, вказані в таблиці 4.

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

1 – залишає лише загальний підсумок по всій таблиці;

2 – залишає проміжні підсумки та загальний підсумок;

3 – залишає всі дані таблиці разом з проміжними та загальним підсумками.

Таблиця 4

Функция	Произначення
Сумма	Обчислює суму всіх значень та видає загальний ітог
Количество значений	Визначає кількість елементів ряду
Среднее	Обчислює середнє арифметичне для ряду значень
Максимум	Визначає найбільше значення в ряді значень
Минимум	Визначає найменше значення в ряді значень
Произведение	Обчислює добуток всіх значень ряду
Количество чисел	Обчислює кількість клітин, що містять числові значення в вказаній області

Приклад: в таблиці 3 потрібно підрахувати середні бали по кожній дисципліні для кожної групи окремо.

 

Порядок виконання:

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

2. Таблиця виділена, КМ Данные – Итоги…

3. В діалоговому вікні «Промежуточные итоги» у полі При каждом изменении в: вказуємо № групи (поле, по якому попередньо виконувалося упорядкування), в полі Операция: вказуємо Среднее, у полі Добавить итоги по: відмічаємо перемикачі полів до яких потрібно примінити операцію Среднее, (рис. 52), кнопка ОК.

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

Рис. 52

 

Рис. 53

 

Індивідуальні завдання

Варіант 1

В автоколоні працюють автомобілі чотирьох марок, по чотири машини кожної марки. Кожна машина має унікальний (єдиний). Коди машин повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д. Норма витрат пального на 100 км залежить від виду пального і становить: дизельне пальне – 11 л, газ – 10 л, АИ-92 – 9,5 л, АИ-95 – 8 л. Ціни за 1 л пального становлять: дизельне пальне – 9,25 грн., газ – 4,88 грн., АИ-92 – 9,47 грн., АИ-95 –9,76 грн.

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

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Вид пального, Ціна на пальне, Витрати пального на 100 км.

 

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

1) для введення даних у колонки Код автомобіля, Марка автомобіля, Вид пального організувати списки, що розкриваються;

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

3) поле Витрати обчислюється як Ціна на пальне • (Пробіг за тиждень/100 • Витрати пального).

 

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

Витрати – за збільшенням;

Марка автомобіля – за алфавітом (первинний ключ), Пробіг за тиждень – за збільшенням (вторинний ключ);

Вид пального – за алфавітом (первинний ключ), Ціна на пальне – за зменшенням (вторинний ключ), Витрати – за зменшенням (третинний ключ).

 

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

Вид пального – дизельне;

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

Пробіг за тиждень – найбільший;

Витрати – три найменші.

 

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

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

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

 

Варіант 2

П’ять магазинів замовляють на складі по 5 книг трьох авторів. Номери магазинів повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

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

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури:Код книжки, Назва книжки.

 

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

1) для введення даних у колонки Номер магазину, Код книжки, Автор організувати списки, що розкриваються;

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

3) відомо, що якщо замовлення магазину становить більше 30 примірників, то склад зменшує Ціну за одиницю на 10%, а за 50 і більше примірників – на 15% – це і буде фактична ціна за одиницю. Поле Загальна вартість замовлення обчислюється як (Фактична ціна за одиницю • Замовлену кількість).

 

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

Замовлена кількість – за збільшенням;

Автор – за алфавітом (первинний ключ), Замовлена кількість – за збільшенням (вторинний ключ);

Номер магазину – за збільшенням (первинний ключ), Автор – за алфавітом (вторинний ключ), Ціна за одиницю – за зменшенням (третинний ключ).

 

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

а) Автор – на літеру «Д»;

б) Загальна вартість замовлення перевищує середнє значення відповідного поля;

в) Замовлена кількість – найбільша;

г) Фактична ціна за одиницю – три найменші.

 

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

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

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

 

Варіант 3

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

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити таблицю наступної структури: Назва господарства, Прізвище фермера, Код продукції, Назва продукції, Ціна за 1 кг, Обсяг поставки, кг, Вартість поставки. Коди продукції повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код продукції, Ціна за 1 кг.

 

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

1) для введення даних у колонки Назва господарства, Прізвище фермера, Назва продукції організувати списки, що розкриваються;

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

3) поле Вартість поставки обчислюється як добуток Ціна за 1 кг на Обсяг поставки, кг.

 

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

Назва господарства – за алфавітом;

Прізвище фермера – за алфавітом (первинний ключ), Обсяг поставки, кг – за збільшенням (вторинний ключ);

Назва продукції – за алфавітом (первинний ключ), Прізвище фермера – за алфавітом (вторинний ключ), Вартість поставки – за зменшенням (третинний ключ).

 

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

Назва господарства – будь-яке, вибране Вами;

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

Назва продукції – «молоко»;

Вартість поставки – три найбільші.

 

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

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

по кожному господарству обчислити загальний обсяг поставок та кількість видів продукції, що постачаються (аркуш «Проміжні підсумки_1»).

 

Варіант 4

 

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

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити таблицю наступної структури: № школи, Дата замовлення, Код товару, Назва товару, Кількість, Ціна за одиницю, Фактична ціна, Сума до сплати. Номери шкіл повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код товару, Ціна за одиницю.

 

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

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

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

3) відомо, що якщо обсяг поставки відповідної продукції становить більше 30 одиниць, то ціна за одиницю зменшується на 10% – це і буде фактична ціна, інакше фактична ціна співпадає з ціною за одиницю. Поле Сума до сплати обчислюється як добуток Фактичної ціни на Кількість.

 

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

Назва товару – за алфавітом;

Назва товару – на літеру «ш» (первинний ключ), Кількість – за збільшенням (вторинний ключ);

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

 

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

Назва товару – будь-яка, вибрана Вами;

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

Ціна за одиницю – найбільша;

Всі замовлення – на вказану дату.

 

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

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

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

 

Варіант 5

Бухгалтер ЖЕКу розраховує відомість оплати за газ та опалення. В коло його обов’язків входить виконати розрахунок сплати послуг для двох будинків (№9, №11) по вул. Приозерній та один будинок (№5) по вул. Квітковій. В таблицю занести дані по п’яти квартирах кожного будинку. Відомо, що будинок №9 по вул. Приозерній оснащений електричними плитами.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: Вулиця, № будинку, Код будинку, № квартири, Кількість мешканців, Площа квартири, кв.м, Плата за газ на 1 людину, Плата за опалення, Нарахована сума. Коди будинків повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код будинку, Тариф за газ.

Заповнити таблицю даними. Для будинку з електроплитами тариф за газ дорівнює нулю, для газових плит – 7,82 грн. на 1 мешканця.

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

1) для введення даних у колонки Вулиця, № будинку та Код будинку організувати списки, що розкриваються;

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

3) поле Плата за опалення обраховується як добуток тарифу за опалення (тариф дорівнює 2,26) на площу квартири. Поле Нарахована сума обчислюється як (Плата за опалення + Плата за газ на 1 людину • Кількість мешканців).

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

а) Вулиця – за алфавітом;

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

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

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

а) Площа квартири – більша 60;

б) № будинку - 5, Нарахована сума – менше 100;

в) Площа квартири – від 50 кв. м до 60 кв. м;

г) Нарахована сума – три найбільші.

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

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

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

 

Варіант 6

Туристична фірма організовує екскурсії по чотирьох містах України за п’ятьма маршрутами в кожному місті.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: Місто, Код маршруту, Назва маршруту, Категорія групи, Вартість екскурсії, Транспортні витрати на 1 людину, Кількість екскурсантів в групі, Вартість замовлення. Коди маршрутів повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код маршруту, Назва маршруту, Вартість екскурсії.

Заповнити таблицю даними. Існують такі маршрути: «Місто вночі», «Пам’ятники міста», «Легендарні вулиці», «Історія краю», «Мистецькі куточки». Ціни екскурсій на однакові маршрути в кожному місті однакові.

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

1) для введення даних у колонки Місто, Код маршруту та Категорія групи (туристи, школярі, пенсіонери тощо) організувати списки, що розкриваються;

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

3) поле Вартість замовлення обчислюється як (Вартість екскурсії + Транспортні витрати на 1 людину • Кількість екскурсантів в групі • Коефіцієнт перерахунку). Коефіцієнт перерахунку для школярів – 0,75, для туристів –1.

 

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

а) Маршрут – за алфавітом;

б) Місто – за алфавітом (первинний ключ), Кількість екскурсантів в групі – за збільшенням (вторинний ключ);

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

 

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

а) Категорія групи – школярі;

б) Місто – Москва, Вартість екскурсії менше 500;

в) Місто – на К, Категорія – турист;

г) Вартість замовлення – Найбільша.

 

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

а) для кожного міста обчислити загальну вартість замовлень та максимальні ціни на екскурсії;

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

 

Варіант 7

Чотири страхові компанії займаються автострахуванням.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: Назва компанії, ПІБ автовласника, Стать, Категорія страховки, Рік отримання прав, Рік початку страхування, Сума страховки, Знижка, Місячний внесок.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Категорія страховки, Сума страховки. Заповнити таблицю даними (будемо вважати, що у всіх страхових фірм сума страховки однакова відносно категорії). Кожна страхова компанія повинна мати не менше шести застрахованих автовласників.

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

1) для введення даних у колонки Назва компанії, Стать та Категорія страховки організувати списки, що розкриваються;

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

3) поле Знижка обчислюється як Сума страховки помножена на коефіцієнт перерахунку. Коефіцієнт перерахунку 10% надається тільки автовласникам, які мають страховий поліс вже більше 4 років. Поле Місячний внесок обчислюється за формулою 1% від Сума страховки – Знижка.

 

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

а) ПІБ автовласника – за збільшенням;

б) Назва компанії – за збільшенням (первинний ключ), Рік початку страхування – за збільшенням (вторинний ключ);

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

 

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

а) Стать – чоловіча;

б) Всі жінки, чиє прізвище закінчується на «ко».

в) Сума страхування – найбільша;

г) Місячний внес ок – три найменших.

 

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

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

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

Варіант 8

 

Чотири фірми займаються виготовленням меблів: диванів, кухонь, столів, стільців, шаф.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: Назва фірми, Код товару, Найменування товару, Вартість товару, грн., Транспортні витрати, Фактичні транспортні витрати, Націнка, Кількість товару, Вартість партії. Коди товарів повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код товару, Націнка. Заповнити таблицю даними.

 

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

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

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

3) поле Фактичні транспортні витрати дорівнює полю Транспортні витрати, якщо кількість товару не перевищує 8, якщо кількість товару більша 8, то сума транспортних витрат знижується на 3%. Поле Вартість партії обчислюється за формулою (Вартість товару, грн. + Фактичні транспортні витрати + Вартість товару, грн., • Націнка) • Кількість товару.

 

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

а) Найменування товару – за алфавітом;

б) Назва фірми – за збільшенням (первинний ключ), Вартість партії – за збільшенням (вторинний ключ);

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

 

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

а) Найменування товару – комп’ютерний стіл;

б) Націнка – 1% або 2%;

в) Кількість товару – від 5 до 10;

г) Вартість партії – три найменших.

 

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

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

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

Варіант 9

Бухгалтер ЖЕК-2 розраховує відомість оплати комунальних послуг за статтею «Плата за утримання будинків». В коло його обов’язків входить розрахунок сплати послуг 2 будинків (№3, №5) по вул. Шевченка та 1 будинок (№23) по вул. Франка. В таблицю занести дані по будь-яких шести квартирах в кожному будинку.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: Вулиця, № будинку, № квартири, Поверх, Кількість кімнат, Кількість мешканців, Площа квартири, м², Плата за утримання будинків, Нарахована сума. Номери будинків повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Кількість кімнат, Площа квартири. (Будемо вважати, що будинки однієї серії). Заповнити таблицю даними.

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

1) для введення даних у колонки Вулиця, № будинку та Кількість кімнат організувати списки, що розкриваються;

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

3) в поле Плата за утримання будинків записати формулу, яка обчислює величину цієї плати як Площа квартири,м² помножена на тариф 2,33. Нарахована сума дорівнює величині плати за утримання будинків, якщо квартира знаходиться на 1 або на 2 поверсі, в інших випадках до плати за утримання будинків додається величина 2,50 помножена на Кількість мешканців.

 

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

а) Вулиця – за алфавітом;

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

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

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

а) Поверх – третій;

б) Вулиця - Франка, Кількість мешканців – 3;

в) Площа квартири – від 50 м² до 60 м²;

г) Нарахована сума – найбільша.

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

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

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

в)

Варіант 10

 

Фірма організує продаж туристичних путівок по шести напрямках.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: Код напрямку, Напрямок, Прізвище туриста, Клас готелю (від 2 до 4), Вартість путівки, Фактична вартість путівки, Вартість перельоту, Кількість путівок, Вартість замовлення. Коди напрямків повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д. По кожному напрямку повинно бути не менше чотирьох замовників.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код напрямку, Вартість перельоту. Заповнити таблицю даними.

 

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

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

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

3) поле Фактична вартість путівки залежить від класу готелю. Якщо клас готелю четвертий, то Фактична вартість путівки обчислюється як (Вартість путівки + 2% від Вартості путівки), в іншому випадку Фактична вартість путівки співпадає з Вартістю путівки. Поле Вартість замовлення обчислюється за формулою (Вартість путівки + Вартість перельоту) • Кількість путівок.

 

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

а) Прізвище туриста – за алфавітом;

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

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

 

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

а) Напрямок – Франція;

б) Прізвище туриста – на літеру «М» або «С»;

в) Вартість перельоту – два найбільші.

 

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

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

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

Варіант 11

 

Чотири меблеві фабрики займаються виготовленням меблів: по п’ять видів столів та шаф.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: № фабрики, Код виробу, Найменування виробу, Категорія, Коефіцієнт перерахунку, Вартість заготовок, Вартість роботи, Вартість фурнітури, Собівартість виробу.. Коди виробів повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д. По кожному виробу повинно бути не менше чотирьох замовлень з кожної фабрики.

 

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код виробу, Категорія, Коефіцієнт перерахунку. Заповнити таблицю даними. Для столів використати наступні категорії: кухонний, журнальний, комп’ютерний; для шаф – книжковий, для одягу.

 

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

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

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

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

 

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

а) Найменування виробу – за алфавітом;

б) № фабрики – за збільшенням (первинний ключ), Найменування виробу – за алфавітом (вторинний ключ);

в) № фабрики – за зменшенням (первинний ключ), Вартість заготовок – за збільшенням (вторинний ключ), Вартість роботи – за збільшенням (третинний ключ).

 

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

а) Найменування виробу – стіл;

б) Категорія – книжковий, Собівартість виробу – менше середнього значення відповідного поля;

в) Категорія – на літеру «К», фабрика – №1;

г) Собівартість виробу – три найбільших.

 

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

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

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

 

Варіант 12

Ательє «Золоті руки» спеціалізується з пошиття верхнього одягу (пальта, плащі, костюми, сорочки). В ательє працює дві бригади, в кожній бригаді працює п’ять працівників. За пошив одного пальта робітник отримує 200 грн., за плащ – 150 грн., за костюм – 180 грн., за сорочку – 100. Відомо, що кожний виріб має категорію складності: перша або друга.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю, яка містить наступні колонки: Номер бригади, Прізвище працівника, Код виробу, Назва виробу, Категорія складності, Кількість пошитих одиниць виробу, Тариф за пошиття однієї одиниці виробу, Ціна за одиницю виробу, Оплата за працю. Коди виробів повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д. По кожному виробу повинно бути не менше одного замовлення у кожного працівника.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код виробу, Тариф за пошиття однієї одиниці виробу. Заповнити таблицю даними.

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

1) для введення даних у колонки Номер бригади, Прізвище працівника, Назва виробу, Категорія складності організувати списки, що розкриваються;

2) поле Тариф за пошиття однієї одиниці виробу заповнити, використовуючи функцію ВПР зі звертанням до таблиці, що знаходиться на аркуші «Довідник»;

3) поле Ціна за одиницю виробу обчислюється як Тариф за пошиття однієї одиниці виробу помножити на Коефіцієнт складності. Якщо виріб відноситься до першої категорії, то його коефіцієнт складності становить 1,2, для другої категорії - Ціна за одиницю виробу співпадає з Тарифом за пошиття однієї одиниці виробу. Поле Оплата за працю обчислюється за формулою: Кількість пошитих одиниць виробу помножити на Ціну за одиницю виробу.

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

а) Оплата за працю – за збільшенням;

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

в) Номер бригади – за збільшенням (первинний ключ), Прізвище працівника – по алфавіту (вторинний ключ), Оплата за працю – за збільшенням (третинний ключ).

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

а) Назва виробу - плащі;

б) Прізвище працівників, оплата праці яких знаходиться в межах мінімального та середнього значень та номер бригади перший;

в) Тариф за пошиття однієї одиниці виробу – найменший;

г) Оплата за працю – чотири найбільші.

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

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

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

Варіант 13

Сільський кооператив спеціалізується по збиранню яблук чотирьох сортів («Семеренко», «Джонатан», «Шафран», «Голден»), які можуть мати дві категорії якості (1 та 2). В кооперативі працює дві бригади по три людини.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: Номер бригади, Прізвище працівника, Код сорту, Сорт яблук, Кількість збираних яблук (кг), Категорія якості, Вартість 1 кг яблук 1 категорії, Фактична ціна 1 кг, Оплата за роботу. Коди сортів повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д. Кожний працівник повинен збирати кожен сорт яблук.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код сорту, Сорт яблук, Ціна 1 кг яблук 1 категорії. Заповнити таблицю даними.

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

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

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

3) поле Оплата роботу обчислюється, як Кількість збираних яблук помножити на Фактична ціна 1 кг. Фактична ціна 1 кг розраховується як Вартість 1 кг яблук 1 категорії помножена на коефіцієнт перерахунку. Відомо, що коефіцієнт перерахунку Вартість 1 кг яблук 2 категорії 0,9,а 1 категорії 1. За 1 кг збираних яблук 1 категорії якості сорту «Семеренко» працівник отримує 0,47 грн., «Джонатан» - 0,5 грн., «Шафран» - 0,4 грн.

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

а) Прізвище працівника – по алфавіту;

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

в) Номер бригади – за зменшенням (первинний ключ), Прізвище працівника – по алфавіту (вторинний ключ), Оплата за роботу – за збільшенням (третинний ключ).

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

а) Сорт яблук - «Джонатан»;

б) Оплата за роботу,що знаходиться в межах від середнього до максимального значення;

в) Кількість збираних яблук – найбільша;

г) Оплата за роботу – дві найменших.

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

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

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

 

Варіант 14

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

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