Завдання 2. Обробка ет за заданими критеріями з використанням майстра функцій. Обробці підлягає ет, розташована на аркуші 2.

1. Обробити ЕТ за наступними критеріями (рис. Л7.2):

• підсумки сумарного виторгу по всіх місяцях для всіх магазинів (сума по стовпцях);

• суму виторгу кожним магазином за поточні місяці (сума по рядках);

• середній виторг по кожному магазину (середня по рядках). Встановити для середнього виторгу числовий формат комірок із двома знаками після коми;

• відсоток суми виторгу за поточні місяці для кожного магазину (тобто відношення суми виторгу магазину до підсумкового й перетворення у відсотки командами: Формат, Комірки, Число, Процентний);

• першість магазинів по отриманому виторгу (функція РАНГ);

• премію за перші три місця. За 1-е місце 10 % від загального виторгу за три місяці магазину, за 2-е - 5 %, за 3-е - 3 %. Для інших виводити повідомлення "Премії немає". Використовувати логічну функцію ЕСЛИ.

• для парних варіантів знайти мінімальний виторг для першого робочого місяця і вивести номер магазина, який має цей виторг.

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

2. Скопіювати оброблену таблицю з аркуша 2 на аркуш 3.

3. На аркуші 3 відобразити формули командами: Сервіс, Параметри, на вкладці Вид установити прапорець Формули й клацнути ОК.

4. Додати 4-й робочий аркуш.

Рис. Л7.2 -Приклад обробленої таблиці

Завдання 3. Захист аркуша 4 і зняття захисту для комірок введення даних за минулі місяці (в4:е7).

1. Скопіювати аркуш 2 на аркуш 4.

2. Виділити блок В4:Е7 і виконати команди меню: Формат, Комірки.

3. Вибрати вкладку Захист, зняти прапорець у полі Комірка, що захищається, і клацнути ОК.

4. Виконати команди основного меню: Сервіс, Захист, Захистити аркуш.

5. В цьому випадку в захищеному аркуші будуть доступні попередньо незахищені комірки. Перевірити це на таблиці аркуша 4.

Питання для самоперевірки

1. Як очистити робочий аркуш?

2. Як копіювати робочий аркуш?

3. Типи даних в Excel.

4. Коли комірки аркуша стають захищеними?

5. Як виконати виправлення тексту або числа в комірці?

6. Поясніть процедуру збереження робочого аркуша.

7. Як перемістити і скопіювати дані на робочому аркуші?

8. Поняття абсолютної і відносної адреси комірки у формулі.

9. Як виконується копіювання формул?

10. Запишіть логічну функцію для переведення з балів в оцінку.

11. Як обчислити % від числового вмісту комірки?

12. Для яких цілей застосовують статистичну функцію РАНГ?

13. Яка формула нарахування %-ів премії від виторгу за 1, 2, 3 місяці ?

Л а б о р а т о р н а р о б о т а № 8

Рішення задач в Excel

Мета роботи: Рішення задач із використанням різних функцій, вбудованих в EXCEL. Робота з Майстром функцій. При підготовці до роботи вивчити теоретичний матеріал (стор.12-16).

Кожна із задач має бути представлена у вигляді таблиці з формулами і таблиці з результатами обчислень.

Варіанти завдань вибираються за двома останніми цифрами індивідуального логіна студента з табл. Л8.1.

Таблиця Л8.1. - Варіанти для вибору завдань 1-3

№ вар.	Номери задач	№ вар.	Номери задач
1	1,11, 21,31	16	7, 16, 25,36
2	2,12, 22,32	17	8, 17, 24,37
3	3,13,23,33	18	9, 18, 23,38
4	4,14,24,34	19	10, 19, 22,39
5	5,15,25,35	20	1, 20, 21,40
6	6,16,26,36	21	3, 14, 30,31
7	7,17,27,37	22	4, 15, 29,32
8	8,18,28,38	23	5,16, 28,33
9	9,19,20,39	24	6, 17, 27,34
10	10,20,30,40	25	7, 18, 26,35
11	2, 11, 30,31	26	8, 19, 25,36
12	3, 12, 29,32	27	9, 20, 24,37
13	4, 13, 28,33	28	10, 13, 23,38
14	5, 14, 27,34	29	1, 12, 22,39
15	6, 15, 26,35	30	2, 11, 21,40

Завдання 1. У наведеній таблиці задані результати здачі студентами іспитів, а також деякі індивідуальні дані. По таблиці по варіантах вирішити наступні завдання:

П.І.Б.	Стать	Рік народж.	Форма навчання	Фізика	Хімія	Математ.	Інформат.
Артемов В.П.	ч	1982	контракт	10	10	9	6
Крейд С.Р.	ч	1981	бюджет	12	10	11	10
Сидорова Е.А.	ж	1982	контракт	7	6	6	5
Богданов А.С.	ч	1980	бюджет	11	9	10	11
Буртик Н.К.	ж	1980	контракт	6	5	7	8
Горкун В.Л.	ч	1982	контракт	11	10	10	10
Горяєв С.Н.	ч	1981	контракт	9	7	11	8
Зайцева Н.В.	ж	1980	бюджет	10	12	11	11
Іванов Р.Т.	ч	1980	контракт	10	9	9	8
Шаркова П.Д.	ж	1980	бюджет	6	7	9	6

1. Вивести прізвища студентів-бюджетників, у яких середній бал більше 10, і визначити їхню кількість.

2. Підрахувати кількість студентів - контрактників, що мають бал з хімії більше середнього з цього предмету, і вивести їх прізвища.

3. Вивести прізвища студентів - контрактників чоловічої статі, що народилися після 1980 року, і визначити їхню кількість.

4. Підрахувати кількість студентів- відмінників і вивести їхні прізвища.

5. Підрахувати кількість студентів чоловічої статі, що народилися в 1980 році, і вивести їхні прізвища.

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

7. Визначити кількість студентів, що мають бали з математики вищі, ніж з фізики, і вивести прізвища цих студентів.

8. Підрахувати кількість студентів - бюджетників, що мають бал з фізики, менше середнього з цього предмету.

9. Визначити кількість студентів, що мають бали з математики, не вище 9, і вивести їхні прізвища.

10. Підрахувати кількість студентів, що народилися в1980 і 1982 роках, і визначити, кого з них менше.

Завдання 2

11. В таблиці Excel у двох різних стовпцях введені найменування товарів і їхні ціни (не менш 8 шт.). За допомогою вбудованих функцій знайдіть максимальну ціну; виведіть список товарів, ціна яких вище середньої; підрахуйте кількість таких товарів.

12. В таблиці Excel у двох різних стовпцях введені прізвища працівників відділу АТС (не менш 8 чол.) і їхні оклади. Визначити кількість працівників, що мають оклад від 2000 до 3000 грн, та виведіть їх прізвища.

13. В таблиці Excel у двох різних стовпцях введені прізвища персоналу фірми і їхніх окладів (не менш 8 чол.). Змінити таблицю за наступним правилом: якщо оклад співробітника менше 2000, то йому дається премія з розрахунку 20% від зарплати, у протилежному випадку - з розрахунку 15% від зарплати.

14. В таблиці Excel введені прізвища і ріст хлопчиків 1 класу (не менше 20 чол.). Обчислити максимальний, мінімальний і середній ріст. Виведіть список учнів, у яких ріст вище за середній.

15. Молокозавод випускає 10 видів молочної продукції, характеристики якої наведені в таблиці Excel. Відома кількість молока, необхідна для одержання 1 кг кожного виду молочної продукції, а також щоденний випуск цієї продукції. Визначити щоденну потребу молока молокозаводу, а також знайти продукцію, для виготовлення якої потрібна найменша кількість молока.

16. Результати змагань по бігу на 100 м 10 спортсменів занесені в таблицю Excel у вигляді списку прізвищ спортсменів і їхніх досягнень. Визначити прізвище переможця й прізвище аутсайдера, а також кількість спортсменів, результати яких відрізняються від результату переможця на 10%.

17. Учням 1 – го класу (не менш 15 чол.) щодня видається безкоштовно склянка молока (200 мл.), якщо їхня вага становить менше 30 кг. Прізвища й вага кожного учня задані в таблиці Excel. Визначити, скільки літрів молока буде потрібно щодня для зазначеного класу, визначити також прізвище учня з мінімальною вагою.

18. У збиранні помідорів брала участь група з 10 студентів. За кожні 20 кг зібраних помідорів студент одержував 1 кг натурпродукту. Прізвища і вага зібраних помідорів задані в таблиці Excel. Визначити кількість помідорів, видану натурпродуктом для кожного студента, підсумкову кількість натурпродукту. Визначити прізвище студента, що зібрав найбільшу кількість помідорів.

19. Є таблиця відомостей про співробітників відділу (10 чол.).

П.І.Б.

Посада

Стаж

Оклад

Внести в таблицю зміни - всім інженерам підвищити оклад на 2000 грн., збільшити стаж всім співробітникам на 1 рік. Визначити максимальний та мінімальний оклади.

20. В таблиці Excel у двох різних стовпцях введені прізвища і заробітна плата кожного з 10 співробітників відділу. Визначити середню зарплату по відділу, а також кількість співробітників відділу, що одержують зарплату вище середньої. Вивести прізвища цих співробітників.

21. В таблиці Excel у трьох різних стовпцях наведені найменування 10 видів виробів, що випускаються підприємством, їхня кількість, а також кількість сировини (в кг), що потрібна для випуску кожного виробу. Визначити загальну кількість сировини для випуску всіх виробів, а також знайти виріб, для виготовлення якого потрібна найбільша кількість сировини.

22. В таблиці Excel у двох різних стовпцях введені прізвища 10 робітників і виробіток за день кожного з них. Визначити прізвище робітника, що дав максимальний виробіток, і прізвище робітника, що дав мінімальний виробіток.

23. Є таблиця відомостей про співробітників відділу (10 чіл.).

П.І.Б.

Посада

Стаж

Оклад

Підрахувати кількість співробітників відділу, у яких стаж більше 10 років, і вивести їхні прізвища. Підвищити оклад менеджерам на 10%.

24. В таблиці Excel, у стовпці А введені дійсні числа (10 шт). Визначите кількість від’ємних і кількість додатних чисел у блоці, а також, яких чисел більше.

25. Для формування збірної країни по хокею попередньо обрано 10 гравців. На основі протоколу складена таблиця, в якій зафіксовані прізвища спортсменів і штрафний час (2, 5 або 10 хвилин) кожного гравця в кожній грі (ігор-5). Вивести список збірної, враховуючи, що гравці, оштрафовані більше чим на 10 хвилин, з кандидатів у збірну виключаються.

26. В таблиці Excel у двох стовпцях введені прізвища й річні оцінки з інформатики кожного з 10 студентів групи. Вивести прізвища студентів, оцінка яких менше середньої оцінки по групі, а також максимальну й мінімальну оцінку.

27. В таблиці Excel у стовпці А введені суми торговельних угод. Зроблено 10 угод за день. Торговельний агент одержує доплату у вигляді відсотка від суми зробленої угоди. Якщо обсяг угоди перевищує 5000 грн., то доплата становить 5%; якщо обсяг менше 3000 грн., - то 2%, в інших випадках - 3%. Визначити доплату торговельного агента за максимальну й мінімальну угоди, а також загальну суму доплати.

28. В таблиці Excel в окремих стовпцях введені найменування товару, ціна товару, кількість та час покупки. Товарів в таблиці повинно бути не менше 8. Визначити, скільки повинен заплатити покупець, якщо в магазині діє 5% -на знижка на вартість товару, що перевищує 1000 рублів, і нічна 3% -на знижка, діюча від 1 до 6 години ранку.

29. В таблиці Excel в окремих стовпцях введені прізвища і зарплата кожного з 10 співробітників фірми за січень, лютий і березень місяці поточного року. Підрахувати суму виплат щомісяця, суму виплат кожному працівникові фірми, а також середню зарплату співробітників. Вивести прізвища співробітників, у яких сумарна зарплата вище за середнє значення.

30. Є таблиця відомостей про співробітників відділу (10 чіл.).

П.І.Б.

Посада

Оклад

Знання мови

За знання іноземної мови нараховується надбавка до окладу у розмірі 12%. Підрахувати надбавку за знання мови, середній, максимальний і мінімальний оклади. Підвищити оклад на 500 грн всім технікам.

Завдання 3.

Наведено фрагмент таблиці туристської фірми з деякими індивідуальними даними (дані і їх кількість можна змінювати за бажанням студента). За даними таблиці вирішити наступні завдання:

П.І.Б.	Стать	Дата народж.	Країна	Авіакомпанія	Дата вилиту	Час вилиту	Клас	Ціна (у.о)
Артемов В.П.	ч	11.12.92	Німеччина	Lufthansa	12.09.15	16:20	Економ	340
Крейд С.Р.	ч	12.11.81	Ізраїль	МАУ	13.10.15	22:30	Бізнес	470
Сидорова Е.А.	ж	19.11.05	Іспанія	МАУ	22.09.15	18:40	Бізнес	490
Богданов А.С.	ч	15.01.79	Іспанія	МАУ	30.12.15	18:40	Бізнес	490
Буртик Н.К.	ж	2.01.80	Туреччина	Turkish Airlines	6.10.15	7:30	Перший	440
Горкун В.Л.	ч	13.12.04	Іспанія	МАУ	19.11.15	18:40	Бізнес	490
Горяєв С.Н.	ч	30.09.57	Франція	AirFrance	3.12.15	12:30	Економ	395
Зайцева Н.В.	ж	16.06.53	Туреччина	Turkish Airlines	6.01.15	7:30	Економ	300
Іванов Р.Т.	ч	1.08.73	США	American Airlines	22.12.15	10:30	Бізнес	850
Шаркова П.Д.	ж	5.06.78	Німеччина	Lufthansa	23.12.15	16:20	Бізнес	450
…..	….	…	….	….	….	….	….	…

31. Вивести прізвища туристів чоловічої статі, що народилися після 1960 року, і визначити їхню кількість.

32. Підрахувати кількість дітей до 12 років і зробити їм знижку на квитки у розмірі 30%, вивести їхні прізвища.

33. Підрахувати кількість туристів жіночої статі, що народилися в 1980 році, і вивести їхні прізвища.

34. Підрахувати кількість туристів, що вилітають у жовтні, скільки з них - чоловічої статі.

35. Підрахувати кількість туристів, що вилітають взимку в Німеччину. і вивести прізвища цих туристів.

36. Підрахувати кількість рейсів, що вилітають по понеділках, і вивести їх список.

37. Підрахувати кількість рейсів, що вилітають вранці, і вивести назву їх авіакомпаній.

38. Підрахувати кількість туристів віком більше 55 років і зробити їм знижку на квитки у розмірі 10%, вивести їхні прізвища.

39. Підрахувати кількість туристів, що вилітають на Різдво, і зробити їм знижку на квитки у розмірі 40%, вивести їхні прізвища.

40. Підрахувати кількість рейсів, що вилітають у першу декаду місяця, і вивести назву відповідних авіакомпаній.