Використання логічних функцій в EXCEL

ОДЕСЬКА НАЦІОНАЛЬНА АКАДЕМІЯ ХАРЧОВИХ ТЕХНОЛОГІЙ

 

Кафедра комп'ютерних систем

і управління бізнес-процесами

М Е Т О Д И Ч Н І В К А З І В КИ

і завдання для самостійної роботи

з курсу «Інформатика та інформаційні технології»

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

6.051701

денної та заочної форм навчання

 

 

Затверджено

 

радою напряму підготовки

бакалаврів 6.051701

протокол № 5 від 25.02. 2013 р

 

Одеса ОНАХТ 2013

 

Методичні вказівки і завдання для самостійної роботи з курсу "Інформатика та інформаційні технології" для студентівв напряму підготовки 6.051701 денної та заочної форм навчання / Укладачі: Н.О. Макоєд, О.П. Соколова, Н.О. Лисенко.-Одеса: ОНАХТ, 2013.- 26 с.

Укладачі: Н. О. Макоєд, канд. пед. наук, доцент;

О.П. Соколова, асистент,

Н.О. Лисенко, асистент.

 

 

Відповідальний за випуск

зав. кафедри В.Е.Волков, канд. фіз-мат. наук, доцент

 

 

ВСТУП

Завдання, які пропонуються, призначені для відпрацьовування основних прийомів обробки даних в Excel і VBA-програмування студентами молодших курсів академії. Вони дають матеріал для самостійних занять і здобуття навичок для роботи на персональних комп'ютерах.

Формулювання більшості завдань універсальні в тому розумінні, що для написання програм можуть бути використані різноманітні мови програмування, а самі програми можуть виконуватися на різних типах комп'ютерів.

Методичні вказівки розраховані на студентів всіх спеціальностей, що вивчають курси «Інформатика», «Інформатика та інформаційні технології», «Обчислювальна техніка й програмування» і т.п.

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

 

Тема EXCEL

 

1. Обчислення за формулами:

1.1. ; ; x =0,654; a =1,752; b =3,635

1.2. ; ; x =0,124; a =7,562; b =4,372

1.3. ; ; x =0,545; a =3,726; b =1,322

1.4. ; ; x =0,534; a =1,555; b =4,366

1.5. ; ; x =0,635; a =8,324; b =1,002

1.6. ; ; x =2,375; a =0,422; b =3,355

1.7. ; ; x =0,522; a =0,944; b =2,628

1.8. ; ; x =0,633; a =2,566; b =8,416

1.9. ; ; x =1,244; a =7,522; b =0,322

1.10. ; ; x =0,853; a =0,668; b =2,532

 

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

1.Обчислити за формулами:

 

; ; x =0,411; a =3,232; b =1,922

Вирішення в Excel:

Використання логічних функцій в EXCEL

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

 

Таблиця 2.1

 

П.І.Б.	Стать	Рік народж.	Форма навчання	Фізика	Хімія	Математ.	Інформат.
Артемов В.П.	ч		контракт
Крейд С.Р.	ч		бюджет
Сидорова Е.А.	ж		контракт
Богданов А.С.	ч		бюджет
Буртик Н.К.	ж		контракт
Горкун В.Л.	ч		контракт
Горяев С.Н.	ч		контракт
Зайцева Н.В.	ж		бюджет
Іванов Р.Т.	ч		контракт
Шаркова П.Д.	ж		бюджет

 

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

П.І.Б.

Посада

Стаж

Оклад

 

 

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

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

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

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

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

П,І,Б.

Посада

Стаж

Оклад

 

 

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

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

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

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

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

 

2.28. Задано наступну таблицю, в якій відображені відомості про продаж книг:

Місяці	У тому числі	Разом
Наукова	Технічна	Художня
Січень				х
Лютий				х
Березень				х
Квітень				х
Травень				х
Червень				х
Середня кіл-ть	х	х	х
Максимальна кіл-ть	х	х	х
Усього	х	х	х

Знайти середню і максимальну кількість проданих книг за тематиками, а також їхню загальну кількість. В поля, позначені х, ввести необхідні формули.

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

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

П.І.Б.

Посада

Оклад

Знання мови

 

 

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

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

Таблиця 2.2

 

П.І.Б.	Стать	Дата народж.	Країна	Авіакомпанія	Дата вилиту	Час вилиту	Клас	Ціна (у.о)
Артемов В.П.	ч	11.12.92	Німеччина	Lufthansa	12.09.12	16:20	Економ
Крейд С.Р.	ч	12.11.81	Ізраїль	МАУ	13.10.12	22:30	Бізнес
Сидорова Е.А.	ж	19.11.55	Іспанія	МАУ	22.09.12	18:40	Бізнес
Богданов А.С.	ч	15.01.79	Іспанія	МАУ	30.12.12	18:40	Бізнес
Буртик Н.К.	ж	2.01.80	Туреччина	Turkish Airlines	6.10.12	7:30	Перший
Горкун В.Л.	ч	13.12.01	Іспанія	МАУ	19.11.12	18:40	Бізнес
Горяев С.Н.	ч	30.09.57	Франція	AirFrance	3.12.12	12:30	Економ
Зайцева Н.В.	ж	16.06.53	Туреччина	Turkish Airlines	6.10.12	7:30	Економ
Іванов Р.Т.	ч	1.08.73	США	American Airlines	22.12.12	10:30	Бізнес
Шаркова П.Д.	ж	5.06.78	Німеччина	Lufthansa	23.12.12	16:20	Бізнес
…..	….	…	….	….	….	….	….	…

 

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

2.33. Підрахувати кількість туристів, що прямують до Туреччини економ класом.

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

2.35. Підрахувати кількість туристів жіночої статі, що прямують до США, і вивести їхні прізвища.

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

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

2.38. Підрахувати кількість рейсів МАУ і вивести список країн, до яких вони прямують.

2.39. Визначити кількість туристів, що користуються МАУ, і вивести їхні прізвища.

2.40. Підрахувати кількість квитків вартості менш 400 у.о. і вивести назву їх авіакомпаній.

 

Приклади вирішення

Завдання 1. За даними таблиці 2. 3 вивести прізвища студентів-бюджетників, які народилися у 1981 році.

 

Таблиця 2.3

 

Ф.И.О.	Пол	Год рожд.	Кредит	Физика	Химия	Математ.	Информат.
Артемов В.П.	м		контракт
Мелков С.Р.	м		бюджет
Сидорова Е.А.	ж		контракт
Богданов А.С.	м		бюджет
Буртик Н.К.	ж		контракт
Горкун В.Л.	м		контракт
Горяев С.Н.	м		контракт
Зайцева Н.В.	ж		бюджет
Иванов Р.Т.	м		контракт
Шаркова П.Д.	ж		бюджет

 

 

Результати рішення задачі

 

 

Формули для вирішення задачі у стовпчику I:

 

Завдання 2. Інформаціяпро кількість опадів і температуру повітря протягом декади задана у вигляді двох масивів у стовпцях таблиці Excel. Визначити кількість опадів у вигляді дощу і у вигляді снігу, враховуючи, що дощить, якщо температура повітря вище 0^оС.

Вирішення задачі виглядатиме:

Формули для вирішення задачі такі: