Практична робота  «Використання вбудованих функцій»

 

Мета практичної роботи: навчитися застосовувати функції таблич­ного процесора, закріпити навички створення та форматування таблиць і використання формул.

 

Завдання 1.

Створіть таблицю для введення й обчислення результатів медич­ного огляду працівників певного підприємства.

Хід виконання

1 Створіть таблицю за формою, поданою на рис. 1. Збережіть її у файлі з іменем Практ_3. xlsх. Уведіть дані у стовпці Табель­ний номер; Маса тіла, кг; Зріст, м і Частота пульсу.

 

Рис. 1. Форма таблиці для запису результатів медичного огляду працівників підприємства

2. На основі даних, наведених у табл. 3.1, визначте, чи є відхи­лення пульсу від норми. Для цього уведіть до клітинки G3 таку формулу: = ІF(FЗ<60;"Низький пульс";ІF(FЗ<=80;"Норма";"Підвищений пульс")).

Зміст формули такий: якщо пульс становить менше 60 ударів за хвилину, то це низький пульс, в іншому випадку треба ви­конати додаткову перевірку: якщо пульс менший за 80 ударів, то він у нормі, інакше — підвищений. Скопіюйте цю форму­лу в клітинки G4:G7.

Значення_пульсу	Повідомлення, яке потрібно вивести
60<= Пульс<=80	Норма
Пульс<60	Низький пульс
Пульс>80	Підвищений пульс

Таблиця 3.1. Визначення типу пульсу

3.   Для розрахунку індексу маси тіла працівника скористайтеся формулою Ind = х/у², де х — маса, кг; у — зріст, м. Для об­числення індексу маси тіла першого працівника введіть цю формулу у клітинку D3 і скопіюйте її до клітинок D4:D7 для визначення індексу маси тіла решти працівників.

4.      На основі даних табл. 3.2 за допомогою логічної функцій IF визначте для першого працівника відхилення маси від норми за формулою, яку введіть до клітинки ЕЗ.

Індекс маси тіла	Повідомлення, яке потрібно вивест
Ind <18	Дуже мала маса!
18 < Ind < 20	Мала маса!
20 < Ind < 26	Норма
26 < Ind < 31	Перевищення норми!
Ind >31	Треба худнути!

Таблиця 3.2. Відповідність між зростом та масою тіла людини

Формулу уведіть самостійно, застосувавши той самий прин­цип, що й у формулі для визначення типу пульсу. Скопіюйте введену формулу до клітинок Е4:Е7 (рис. 2).

Рис. 2. Таблиця результатів медичного огляду

5.      До клітинок В8 і В9 уведіть формули для обчислення макси­мальної та мінімальної маси тіла. Скористайтеся для цього відповідними статистичними функціями.

6.      Скопіюйте формули з клітинок В8:В9 у клітинки С8:С9. У результаті ви маєте отримати таку таблицю, як на рис. 2.

Завдання 2

Розв'яжіть таку математичну задачу: обчислити кількість діль­ників заданого користувачем числа, яке не перевищує 100. Ко­ристувач має вводити число у певну клітинку, а результат пови­нен автоматично відображатися в іншій клітинці.

Аналіз завдання

Отже, потрібно визначити, на скільки чисел у діапазоні [1;а] вве­дене користувачем число а ділиться без остачі. Замість діапазону [1;а] можна розглядати діапазон [1;100]: очевидно, що серед чи­сел, більших за а, дільників а не буде. Числа від 1 до 100 можна ввести у діапазоні А1:А100, а в діапазоні В1:В100 — обчислити ос­тачі від ділення а на відповідне число зі стовпця А. Залишиться визначити кількість нулів у діапазоні В1:В100 — вона дорівнюва­тиме шуканому результату.

Хід виконання

1. Створіть таблицю за формою, яка подана на рис. 1. Клітин­ка D1 призначена для введення числа, а клітинка D2 — для відображення результату. Збережіть таблицю у файлі з іменем Практ_3_2.xlsх.

 Рис. 3. Форма таблиці для завдання 2

2.      У діапазоні А1:А100 створіть арифметичну прогресію цілих чисел від 1 до 100 із кроком 1 .

3.      У клітинку В1 уведіть формулу для обчислення остачі від ділення числа, введеного користувачем (клітинка D1), на число у клітинці А1.

а)  Виділіть клітинку В1 і клацніть кнопку   (Вставка функції) зліва від рядка формул.

б)   У вікні Вставка функції з розкривного списку Категорія виберіть категорію Математичні, а у списку Виберіть функцію —функцію MOD (рос. ОСТАТ) і клацніть її двічі.

в)   У вікні Аргументи функції у поле Число введіть адресу D1 (клацніть у полі Число, а потім  на клітинці D1), а в поле Дільник адресу А1 (рис. 4).

г)    Клацніть кнопку ОК. У клітинку В1 буде введено формулу = MOD(D1;A1).

                                 

Рис. 3.  Введення аргументів функції MOD                     Рис. 4. Обчислення дільників числа

4.     Під час копіювання у введеній формулі зміщуватимуться обидві адреси, а нам потрібно, щоб адреса  D1 залишалась незмінною. Зафіксуйте номер стовпця у цьому посиланні самостійно

5.     Скопіюйте формулу з клітинки В1 в усі клітинки діапазону В2:В100. Якщо ввести у клітинку D1 число 18, аркуш набуде такого вигляду, як на рис. 4.

6. Тепер у клітинці D2 потрібно визначити кількість нулів у діапазоні В1:В100. Для цього скористайтеся функцією COUNTIF (рос. СЧЕТЕСЛИ).

             а)   Виділіть клітинку D2 і клацніть кнопку  (Вставка функції).

б)   У вікні Вставка функції в розкривному списку Категорія ви­беріть категорію Статистичні, а у списку Виберіть функцію — функцію COUNTIF і клацніть її двічі.

в)   Перейшовши у вікно Аргументи функції, в поле Діапазон вве­діть адресу В1:В100 (клацніть кнопку , а потім виділіть діапазон), а в поле Критерій — число 0 (рис. 5).

  Рис. 5. Введення аргументів функції COUNTIFV

г) Клацніть кнопку ОК, і у клітинці D1 з'явиться формула =COUNTIF(B1:B100;0) та буде обчислено результат.

7. Введіть у клітинку D1 різні значення, щоб перевірити роботу функцій. Зауважте: прості числа мають рівно два дільники. Як ви вважаєте, яке число в діапазоні [0; 100] має найбільшу кількість дільників?

 

Завдання 3

Є таблиця з відомостями щодо обсягів виробництва кількох промис­лових підприємств. У стовпці А записано назву підприємства та місто, де воно розташовано, а у стовпці В — обсяги виробництва. Визначіть середні обсяги виробництва київських підприємств.

Аналіз завдання

Відмінною ознакою київських підприємств є те, що в їхніх, назвах міститься послідовність символів Київ — або як окреме слово, або у складі іншого слова. Визначити, чи справді в текст і є такий підрядок, можна за допомогою функції FIND, яка поверне число, якщо потрібна послідовність символів у тексті є, і повідомлення про помилку #VALUE!, якщо така послідовність відсутня. Знаючи яке підприємство київське, а яке - ні, можна сформувати стов­пець значень, у якому київським підприємствам відповідатимуть їхні обсяги виробництва, а решті – порожні клітинки.  Залишиться тільки обчислити середнє арифметичне значень цього стовпця.

Хід виконання

1. Створіть таблицю за зразком, наведеним на рис. 6. Збе­режіть роботу у файлі Практ_3_3.xlsх. (Для клітинок В2:В6 не забудьте задати грошовий формат.)

Рис. 6. Відомості про обсяги виробництва промислових підприємств

2. У діапазон С2:С6 введіть функцію FIND (рос. НАЙТИ), яка шу­катиме текст Київ у клітинках стовпця А.

а)   Виділіть клітинку С2 і клацніть кнопку   (Вставка функції).

б)   Виберіть категорію Текстові, а в ній — функцію FIND і двічі клацніть її.

в)   У вікні Аргументи функції у поле Шуканий текст уведіть значен­ня Київ, у поле Текст перегляду — адресу А2 і клацніть кноп­ку ОК. У клітинку С2 буде введено формулу = FIND("Київ",A2).

г)   Скопіюйте зазначену формулу у клітинки діапазону С2:С6 (рис. 7). Результат має бути таким, як показано на рис. 8: якщо рядок містить відомості про київський завод, то у стовпці С відображатиметься число, в інших випадках — значення #VALUE! (#ЗНАЧ!).

     

                                                                                          Рис. 7. Таблиця в режимі відображення формул

3. Логіка обчислення значень у стовпці D буде такою: якщо У стовпці С міститься число, то у стовпці D має міститися те саме значення, що й у стовпці В, інакше у стовпці D має бути порожня клітинка.

Подібну логіку реалізують за допомогою функції IF (рос. ЕСЛИ), а щоб визначити, чи міститься у клітинці число, можна за­стосувати інформаційну функцію ISMUNBER (рос. ЕЧИСЛО), яку буде вкладено у функцію IF.

а)    Клацніть клітинку D2, потім кнопку   (Вставка функції), виберіть категорію Логічні та двічі клацніть назву функції IF.

б)   Логічним виразом у функції IF буде вираз ISNUMBER(C2), який потрібно ввести з клавіатури у поле Лог_вираз.

в)   У поле Значення_якщо_істина вводимо адресу В2, а в поле Значення_якщо_хибність — символи "", тобто порожній рядок.

г)    Скопіюйте формулу з клітинки D2 у клітинки діапазону D3:D6 (див. рис. 8).

      

Рис.8

4. Обчисліть середнє значення клітинок діапазону D2:D6. Для цього у клітинку D7 уведіть формулу =AVERAGE(D2:D6) (рос. СРЗНАЧ), скориставшись кнопкою Щ (Вставка функції) та ви­бравши функцію AVERAGE в категорії Статистичні. Результати виконаних обчислень наведені на рис. 9

Рис. 9. Результати обчислень середніх обсягів виробництва київських підприємств

5. Уведіть формули, що дадуть змогу автоматично визначати кількість підприємств, обсяги виробництва яких перевищу­ють величину, введену користувачем у клітинку Е1.