Практична робота № 10. Використання стандартних функцій та виконання розрахунків в електронній таблиці Excel

Год.

Мета: Ознайомитися із використанням стандартних функцій в Excel. Одержати навички роботи з функціями і навчитися працювати із майстром функцій.

Теоретичні відомості:

Програма Excel має приблизно 60 математичних функцій за допомогою яких можна виконати загальні обчислення. Аргументами цих функцій є числові дані. Математичні функції функцій поділяються на 4 групи: арифметичні, логарифмічні, тригонометричні і матричні.

Арифметичні функції:

· ABS (число) повертає абсолютне значення числа. Наприклад, вираз ABS(-2) дає число 2.

· Корінь (число) повертає додатне число квадратного кореня числа. Якщо число від'ємне, то на екран виведеться повідомлення про помилку.

· Степінь (число; показник степеня) підносить число до вказаної степені.

· Округ. (число; кількість рядків) округлює число до вказаного значення кількості десяткових розрядів.

· ОКРВВЕРХ (х; точність) повертає результат округлення з надлишком до найближчого числа, що кратне точності. Наприклад, вираз ОКРВВЕРХ (2.2552; 1) = 3.

· ОКРВНИЗ (х; точність) – це округлення з недостачею. Ця функція подібна до ОКРВВЕРХ. Наприклад, ОКРВНИЗ (2,2556; 1)=2.

· ОТБР (число; кількість розрядів) залишає вказану кількість розрядів числа після коми відкидаючи всі наступні не роблячи округлення.

Тригонометричні функції:

· Пі () виводить на екран число Пі з точністю до 15 чисел.

· РАДИАНЫ (кут в градусах) обчислює значення кута в радіанах.

· SIN (кут в радіанах чи в градусах) знаходить значення синуса кута.

· Cos (число; TAN(число)) повертає косинус, заданого тангенсу відповідно.

· ASIN (число) повертає значення арксинуса числа, тобто число, синус якого = числу.

Матричні функції:

Матричні функції застосовуються для рівнянь.

· МОПРЕД (матриця) повертає визначник матриці. Наприклад, МОПРЕД ({2; 4; 3; 5}) повертає 2.

· МОБР (матриця) повертає обернену матрицю.

·

Застосування майстра функцій.

Функцію у формулу можна вводити так:

· Виділити комірку і виконати команду Вставка/ Функція.

· Натиснути клавішу Вставка на панелі інструментів Стандартная

· Натиснути кнопку Изменить формулу в рядку формул.

Використання функції ЯКЩО (ЕСЛИ, IF)

Зауважимо, що в перших двох випадках записується програма Майстер. Майстер функції побудований за математичним принципом. У лівому списку розміщуються назви категорій функцій, а у правому – назви функцій вибраної категорії. Функції викликаються клацанням ЛКМ на її назві. Після цього відкривається вікно у яке вводиться аргументи функції та адреси комірок.

Задача 1.“Нарахування зарплатні”.

У відомості нарахування зарплатні є прізвища шести-восьми працівників, які мають одну з трьох категорій: 1, 2, 3. Денна тарифна ставка залежить від категорії так:
Ставка = Протягом місяця працівники зайняті різну кількість днів. Треба ввести кількістьть відпрацьованих днів і нарахувати зарплатню працівникам, якщо відрахування (податки тощо) становлять 21% від нарахувань. Скласти бухгалтерську відомість.

Задача 2. “Розв’язування нелінійного рівняння”.
Дано нелінійне рівняння 2nx-n=sinnx, де n – номер варіанта. Розв’язати рівняння методом простих ітерацій.

Теоретичні відомості

Розгалуження в ЕТ реалізовують за допомогою функції ЯКЩО, яка використовується у формулах і має таку структуру: ЯКЩО (<умова>, <вираз 1>, <вираз 2>). Якщо умова істинна, то функція набуває значення першого виразу, інакше – другого. Вираз також може бути функцією ЯКЩО – так утворюють вкладені розгалуження. Умови записують як в алгоритмічних мовах – за допомогою операцій порівняння =, >, <, <=, >=, <>, визначених над виразами. Часто виразом є лише адреса клітинки, яка містить деяке значення.

Нелінійне рівняння можна розв’язати чотирма способами. Розглянемо метод простих ітерацій. Щоб нелінійне рівняння можна було розв’язати методом простих ітерацій, його зводять до вигляду x = (sinnx+ n)/2n Метод простої ітерації записують так (він буде збігатися): xi+1 = (sinnxi+ n)/2n де х0 - будь-яке початкове наближення, і=0, 1, 2, …, а замість n треба підставити значення свого варіанта. Нехай n=1, а в клітинку А6 введено початкове наближення. Тоді наступне наближення отримаємо у клітинці В6, ввівши туди формулу =(sin(A6)+1)/2.

У ході роботи описано, як отримати наступні наближення. Інший спосіб полягає у використанні властивостей ЕТ автоматичного багаторазового переобчислення, якщо ввімкнутий режим ітерацій у діалоговому вікні Параметри. Тут для розв’язування задачі достатньо двох клітинок. Оба способи розглянемо під час виконання роботи. Нелінійне рівняння можна розв’язати також способом добирання параметра. Ще інший спосіб полягає у використанні можливостей програми Solver, що додається до Excel.

Хід роботи
1. Запустіть програму ЕТ і задайте режим відображення формул на сторінці 1.
2. Введіть вхідні дані для розв’язування задачі 5 для п’яти-шести працівників так:
Адреси Дані
А1 Відомість нарахування зарплати від
Е1 =ДАТА (1999,2,18) Примітка: введіть сьогоднішню дату.
D2 8
E2 10
F2 12
G2 0.21
А3 Номер
В3 Прізвище
С3 Категорія
D3 Днів
E3 Оплата
F3 Нараховано
G3 Відрахування
Н3 Видати
А4 1
В4 <конкретне прізвище 1>
С4 <конкретна категорія 1,2,3>
D4 <кількість відпрацьованих днів>
А5 2
В5 <конкретне прізвище 2>
С5 <конкретна категорія>
D5 <кількість відпрацьованих днів>
і т.д. (введіть дані для п’яти-шести працівників).
3. Уведіть формули розв’язування задачі:
E4 =ЕСЛИ (C4=1; $D$2; ЕСЛИ (C4=2; $E$2; $F$2))
F4 =D4*E4
G4 =F4*$G$2
H4 =F4-G4
4. Скопіюйте формули у всю робочу таблицю.
5. Уведіть формули для обчислення балансу:
D10 Всього
F10 < обчисліть суму в стовпці F>
G10 < обчисліть суму в стовпці G>
Н10 < обчисліть суму в стовпці Н>
Н11 =G10+H10
H12 =ЕСЛИ (Н12=F10;”OK”;“Помилка”)
6. Відмініть режим відображення формул.
Скільки всього нараховано зарплатні? Чи збігається баланс? Скільки повинен отримати другий працівник?
7. Підвищіть денну оплату праці всім категоріям на три одиниці і зменшіть відрахування на 2%.
Скільки всього нараховано зарплатні тепер? Чи збігається баланс? Скільки тепер повинен отримати другий працівник?
8. Перейдіть на сторінку 2 для розв’язування задачі 2.
9. Введіть заголовок таблиці і назви стовпців так:
А1 Розв’язування нелінійного рівняння
А2 <введіть вигляд рівняння>
А3 методом простої ітерації
А5 Попередня Примітка: ітерація
В5 Наступна
С5 Похибка
10. Уведіть формули розв’язування задачі 2:
А6 <введіть будь-яке число>
В6 <введіть формулу методу простої ітерації>
С6 =abs(B6-A6)
A7 =B6
11. Скопіюйте формули з А7, В6, С6 вниз до 13-го рядка включно.
Скільки буде виконано ітерацій?
12. Відмініть режим відображення формул і в клітинці В13 отримаєте результат.
Який результат і яка різниця між двома останніми наближеними значеннями? У скільки разів зменшується ця різниця (похибка) після кожної ітерації?
13. Сформатуйте числові дані, щоб було п’ять знаків після десяткової крапки.
14. Розв’яжіть задачу 2 у двох клітинках.
Задайте режим ітерації: Сервіс Þ Параметри Þ Обчислення Þ Ітерації Þ ОК.
В А17 введіть початкове наближення.
В В17 введіть формулу методу простої ітерації як вираз від А17.
В А17 введіть цю ж формулу методу, що залежить від В17.
Який результат? Скільки було задано ітерацій?
15. Збережіть книжку з назвою Прізвище ЕТ5.
16. Закінчіть роботу. Здайте звіти.

Контролюючі запитання

1. Як реалізуються розгалуження в ЕТ?

2. Яке значення функції ЯКЩО(1>2, 1, 2)?

3. Як обчислити суму в стовпці?

4. Який загальний вигляд має функція ЯКЩО?

5. Як скопіювати формулу? Яке значення функції ЯКЩО(5=5, 4, 5)?

6. Як реалізуються скінченні цикли в ЕТ?

7. Як реалізуються ітераційні цикли в ЕТ?

8. Яке значення функції ЯКЩО(2>1, 10, 20)?

9. Яке призначення кнопки Автосума?

10. Наведіть приклад вкладеного розгалуження.

11. Як скопіювати таблицю на іншу сторінку?

12. Яка різниця між абсолютними і відносними адресами клітинок?

13. Яке значення функції ЯКЩО(1=2, 15, 25)?

14. Як увімкнути режим ітерацій?

15. Як ввести дату в клітинку?

16. Наведіть приклад прямокутного діапазону.

17. Які є категорії стандартних функцій?

18. Як відредагувати дане у клітинці?

19. Як вставити стовпець у таблицю? Яке значення ЯКЩО(5>2, 5, 2)?

20. Як зповнити стовпець значеннями арифметичної прогресії?

21. Як задати чи відмінити режим відображення формул?

22. Які типи даних опрацьовує ЕТ?

23. Як вилучити рядок з таблиці?

24. Як розграфити таблицю? Яке значення функції ЯКЩО(3<5, 8, 12)?

25. Яку стандартну функцію заміняє кнопка Автосума?

26. Як виокремити діапазон клітинок?

27. Які команди є в контекстному меню клітинки?

28. Як перейти на іншу сторінку?

29. Як очистити весь стовпець? Яке значення ЯКЩО(8>2, 2, 8)?

30. Які команди є в контекстному меню заголовка стовпця?

31. Як вставити чисту сторінку у книгу?

32. Що таке автоформат таблиці?

33. Які команди є у контекстному меню сторінки ЕТ?

34. Яке значення функції ЯКЩО(2>1, ЯКЩО(1>2, 5, 8)?

35. Як обчислити суму чисел у деякому рядку ЕТ?