Excel. Організація розгалужень та ітерацій. Задача "Нарахування зарплатні". Задача "Розв'язування нелінійного рівняння

Тема

Excel. Організація розгалужень та ітерацій. Задача "Нарахування зарплатні". Задача "Розв'язування нелінійного рівняння".

Мета

Уміти використовувати логічну функцію ЯКЩО (ЕСЛИ, IF) та абсолютні адреси клітинок для розв'язування типових економічних і математичних задач.

Задача 6 "Нарахування зарплатні"

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

Протягом місяця працівники зайняті різну кількість днів. Треба ввести кількість відпрацьованих днів і нарахувати зарплатню працівникам, якщо відрахування (податки тощо) становлять 21% від нарахувань. Скласти бухгалтерську відомість (рис. 1).

Задача  7 "Розв'язування нелінійного рівняння"

Дано нелінійне рівняння 2пх-п = sinnx, де п — номер варіанта. Розв'язати рівняння методом простих ітерацій (рис. 2).

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

Розглянемо поняття абсолютної і змішаної адреси клітинки у формулі. Абсолютною називається адреса, в якій є два символи $: один перед назвою стовпця, другий — перед номером рядка, наприклад, $Е$3. Змішана адреса містить лише один символ $. Правило: частина адреси після символу $ не модифікується під час копіювання формули. Абсолютні адреси слугують, зокрема, для посилання на клітинки, що містять константи, які входять у формули. Такою константою є, наприклад, відсотки (12% =.12) річних у задачі 2. Якщо для задачі 2 число .12 занести в клітинку ЕЗ, то в клітинку СЗ можна ввести формулу = ВЗ*$Е$3.

Розгалуження в ЕТ реалізовують за допомогою функції ЯКЩО, яка використовується у формулах і має таку структуру:

ЯКЩО(<логічний вираз>; <вираз 1>; <вираз 2>). 

Логічний вираз — це форма запису умови: простої або складеної.

Якщо умова істинна, то функція набуває значення першого виразу, інакше — другого.

Вираз 1 чи вираз 2 також може бути функцією ЯКЩО — так утворюють вкладені розгалуження. Часто виразом 1 чи виразом 2 є лише адреса клітинки, яка містить деяке значення або конкретне число.

Прості умови записують як в алгоритмічних мовах — за допомогою операцій порівняння =, >, <, <=, >=, <>, визначених над виразами, наприклад, 7>5, А5<=20 тощо.

Складні умови записують за допомогою логічних функцій І(< умова 1>;<умова 2>; ...) та АБО(<умова1>;<умова 2>;...).

Функція І (Й, AND) істинна, якщо всі умови в її списку істинні.

Функція АБО (ИЛИ, OR) істинна, якщо хоч би одна умова в її списку істинна.

Наприклад, функція ЯКЩО(АБО(5>7; 5<7); 5; 7) отримує значення 5, а функція ЯКЩО( І (5>7; 5<7); 5; 7) — значення 7.

Якщо користувач не пам'ятає вигляду функції, він може вставити її у вираз за допомогою майстра функцій, який викликається командою Вставити О Функція. У цьому випадку потрібно вибрати назву функції з запропонованого списку (крок 1) і заповнити поля значеннями параметрів (крок 2).

Працюючи з програмою Excel, потрібно користуватися російськими (ЕСЛИ, Й, ИЛИ) або англійськими (IF, AND, OR) назвами логічних функцій.

Продовжимо вивчати застосування електронних таблиць для розв'язування типових математичних задач. Розглянемо чотири способи розв'язування нелінійного рівняння: 1) метод простих ітерацій з побудовою таблиці; 2) метод простих ітерацій з використанням двох клітинок; 3) метод підбору параметра; 4) метод пошуку розв'язку спеціальною програмою.

Розглянемо метод простих ітерацій. Щоб нелінійне рівняння f(x) = 0 можна було розв'язати методом простих ітерацій, його зводять до вигляду х = z(x) так, щоб виконувалась нерівність: abs(z'(x)) < 1 (за цієї умови метод простих ітерацій збігається, тобто дає правильний розв'язок). Наприклад, рівняння 2пх-п = sinnx спочатку треба звести до такого вигляду:

х = (sinnx + п)/2п.

Метод простої ітерації реалізують за допомогою рекурентної формули так:

х_і+1 = (sinnx_і + п)/2п,

де х_о - будь-яке початкове наближення, і=0, 1, 2,..., а замість п треба підставити значення свого варіанта. Домовимося, що коли і=8, то значення х_і ( тобто x₈) вважатимемо розв'язком рівняння. Розглянемо реалізацію рекурентної формули в ЕТ. Нехай п=1, а в клітинку А6 введено будь-яке початкове наближення, наприклад, 2. Тоді наступне наближення отримаємо в клітинці В6, ввівши туди формулу =(sin(A6)+l)/2. Це значення приймаємо за початкове для наступної ітерації: в А7 заносимо значення В6. В клітинці В7 отримуємо наступне наближення і т.д. У клітинці В13 буде знаходитися останнє (восьме) наближення, яке і приймаємо за розв'язок.

Другий спосіб полягає у використанні властивості ЕТ автоматичного багаторазового переобчислення, якщо ввімкнений режим ітерацій у діалоговому вікні Параметри. Тут для розв'язування задачі достатньо двох клітинок (рис. 2, рядок 17). Цей спосіб розглянемо під час виконання роботи.

Нелінійне рівняння можна розв'язати також способом добирання параметра, щоб деяка, залежна від нього функція отри мала певне значення. Цей метод має важливе значення для розв'язування задач зворотнього аналізу, наприклад такої: скільки треба купити одиниць деякого товару (це є параметр), щоб вкластися в заплановану суму (це функція). Інша задача: яку встановити тарифну ставку (параметр) дванадцяти працівникам, щоб вкласти ся в запланований бюджет (функція) 1000 грн. тощо.

Нехай а1 - ім'я клітинки, що містить значення параметра ставки, a f(a1) = с — задане рівняння, наприклад, бюджет(а1) =12*a1 = 1000. Метод підбору параметра полягає в тому, що програма для будь-якого рівняння обчислює значення а1. Алгоритм дій користувача такий. Спочатку потрібно в будь-яку клітинку занести формулу = f(a1), вибрати цю клітинку і виконати команду Сервіс => Підбір параметра. Отримаємо діалогове вікно, у якому треба заповнити три поля:  1) зазначити адресу формули (вона буде вказана автоматично, якщо  клітинка з формулою була вибрана), 2) бажане значення формули, тобто с; 3) адресу клітинки a1. Натискаємо на ОК і у клітинці а1 отримаємо шуканий результат. Четвертий спосіб полягає у використанні можливостей програми Solver, що додається до Excel. Вона дає змогу розв'язувати задачі з багатьма параметрами і з обмеженнями, наприклад, такі: скільки треба купити одиниць двох чи трьох найменувань товарів (це параметри), щоб вкластися в заплановану суму (це функція) і щоб кількості товарів не перевищували деяких величин.

 Хід роботи

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

2.   Розгляньте умову задачі і введіть вхідні дані для шести працівників (див. рис. 1, але не копіюйте з нього дані).

Створіть список користувача з прізвищами працівників. У таблиці вводьте дані лише в стовпці А, В, С, D, пам'ятаючи, що є лише три категорії 1, 2, З і днів у місяці є не більше, ніж 31.

3. Уведіть формули  розв'язування задачі:

Е4        = ЕСЛИ (C4=1;$D$2; ЕСЛИ (С4=2; $Е$2; $F$2))

F4        = D4 * Е4

G4       = F4 * $G$2

Н4       = F4 - G4

4. Скопіюйте формули в усю робочу таблицю.

5. Уведіть формули для обчислення балансу:

D10     Всього

F10      <обчисліть суму в стовпці F>

G10      <обчисліть суму в стовпці G>

Н10      <обчисліть суму в стовпці Н>

Н12     = G10+H10

Н13     = ЕСЛИ (Н12=F10;"ОК";"Помилка")

6. Відмініть режим відображення формул.

Скільки всього нараховано зарплатні? Чи збігається баланс?Скільки повинен отримати другий працівник?

7. Підвищіть  денну  оплату   праці   (тарифні   ставки)   усім категоріям на три одиниці і зменшіть відрахування на 2%.

Скільки всього нараховано зарплатні тепер?  Чи збігається баланс? Скільки тепер повинен отримати другий працівник?

8. Зніміть захист з даних у стовпці Днів.  Захистіть  решту таблиці від несанкціонованих змін, задавши пароль: money.

Виберіть стовпець D і зніміть захист його клітинок командами Формат => Клітинки =>  Захист => Вимкніть перемикач захисту клітинки. Решту клітинок захистіть командою Сервіс => Захист => Захистити => Лист. Пам'ятайте: коли вводять пароль, на екрані відображаються зірочки. Переконайтеся, що в стовпець внести зміни не можна. Внесіть зміни в стовпець D: другий робітник відпрацював 28 днів. Скільки він заробив?

9. Перейдіть на наступну сторінку для розв'язування задачі 7.

Рис. 2. Зразок розв'язування задачі 7.

10. Введіть заголовок таблиці і назви стовпців так (рис. 2 (із практичного завдання №2-3)):

А1        Розв'язування нелінійного рівняння

А2        <введіть вигляд свого рівняння>

A3        методом простої ітерації

А5        Попередня              Примітка: маємо на увазі ітерації

В5        Наступна

С5        Похибка

11.  Уведіть формули розв'язування задачі 7:

А6        <введіть будь-яке число>

В6        <введіть свою формулу методу простої ітерації>

С6        =abs(B6-A6)

А7        =В6

Більше нічого вводити не треба.

12.  Скопіюйте формули з А7, В6, С6 вниз до 13-го рядка включно.

Скільки буде виконано ітерацій?

13.  Відмініть режим  відображення  формул  і  в клітинці  В13 отримаєте результат.

Який результат і яка різниця між двома останніми наближеними значеннями? У скільки разів зменшується ця різниця (похибка) після кожної ітерації?

14.  Сформатуйте числові дані,  щоб було п'ять знаків після десяткової крапки.

15.  Переробіть таблицю так,  щоб розв'язати цю ж задачу, користуючись іменами діапазонів.

Замість назви стовпця Попередня введіть назву х, замість назви Наступна - z. Змініть формули в клітинках В6 і С6 відповідним чином. Виконайте додатково ще дві ітерації. Яка відповідь тепер?

16.   Розв'яжіть задачу 7 у двох клітинках.

Задайте режим ітерації: Сервіс => Параметри => Обчислення => Ітерації =>  ОК. В А17 введіть будь-яке початкове наближення. У клітинку В17 введіть формулу методу простої ітерації як вираз від А17, наприклад, =(sin(A17)+l)/2. В А17 введіть цю ж формулу як вираз від В17, тобто =(sin(B17)+l)/2. Який результат?

17.  Запишіть своє рівняння у вигляді f(x)=0 і розв'яжіть його методом підбору параметра.

Перейдіть на нову сторінку. Нехай клітинка А1 міститиме х. У клітинку А2 введіть формулу = 2*n*A1-n-sin(n*A1), де п - номер варіанта. Сервіс =>  Підбір параметра. Заповніть вікно так: А2, 0, А1 =>  ОК. Відповідь буде в клітинці А1.

18.  Розв'яжіть  нелінійне  рівняння  засобом   Solver   (Пошук розв'язку).

Сервіс => Пошук розв'язку. Заповніть діалогове вікно: клітинка-ціль (має містити рівняння) -  А2, значення — 0, змінюючи клітинку А1. Обмежень немає. Виконати.

19.  Збережіть книжку на диску.

20.  Закінчіть роботу. Здайте звіти.

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

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

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

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

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

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

6.  Які ви знаєте логічні функції?

7.  Яке значення функції І(2=2; 3=3; 3<4)?

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

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

10.  Що таке абсолютна і змішана адреси клітинки?

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

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

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

14.  Яке значення функції АБО(1=2; 3=3; 4=5)?

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.  Що таке засіб Пошук розв'язку (Solver)?

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

35.  Які є способи розв'язування нелінійного рівняння?