Теоретичні відомості. Інструктивно-методична картка

Інструктивно-методична картка

Тема. MS Excel. Організація розгалужень.

Мета

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

Задача 7 «Нарахування зарплатні»

мість (див. рис. 44).

Задача 8 «Розв'язування нелінійного рівняння»

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

План

1. Абсолютна і змішана адреси.

2. Логічні функції.

3. Дати.

4. Метод добирання параметра.

5. Метод простих ітерацій.

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

1. Абсолютна і змішана адреси. Розглянемо поняття абсолютної і змішаної адрес клітинки у формулі. Абсолютною називають адресу, в якій є два символи $: один перед назвою стовпця, другий — перед номером рядка, наприклад $Е$3. Змішана адреса місти ть, лише один символ $. Правило: частина адреси після символу $ не модифікується під час копіювання формули.

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

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

ЯКЩО(<логічний вираз>; <вираз 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) назвами функцій. 3. Дати. В економічних чи бухгалтерських задачах дати тра-I и миються доволі часто. Дати в ЕТ чисто умовно зачисляють до даних типу дата. Наспраді дати зберігаються в ЕТ як цілі числа і лише відображаються на екрані в тому чи іншому форматі, наприклад: 12 січня 2006 р. чи 12.01.2006, чи 2006-01-12, чи 88729. За точку відліку дат взято 1 січня 1900 року. Будь-яка дата еквівалентна цілому числу, що дорівнює кількості днів, які минули від точки відліку (38729 для 12 січня 2006 p.). Це дає змогу виконувати над датами такі операції: віднімати дати для визначення проміжку днів між двома датами, додавати чи від-німати від дат ціле число.

Можна застосовувати функції опрацювання дат з метою визначення окремих компонент дати: поточного номера дня в тижні (WEEKDAY), в місяці (DAY), номера місяця в році ( MONTH), року (YEAR), поточної дати (TODAY) тощо. Ці функції потрібно вставляти у вирази командами Вставити => Функція => Дати і час. Деякі з цих функцій мають параметри, дія яких описується у відповідному діалоговому вікні.

Час задається годиною, хвилиною, секундою. Йому від-даповідає десяткове число від 0 до 1, що відображає частину доби.

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

4. Метод добирання параметра. Метод добирання параметри призначений для розв'язування нелінійного рівняння f(x)=е, Суть методу полягає в тому, щоб автоматично визначити (а деякою точністю) таке значення параметра х, для якого функція f(x) одержує потрібне значення с

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

Розглянемо задачу: встановити тарифну ставку (це пара метр) дванадцятьом працівникам, щоб вкластися в запланований обсяг зарплатні (це функція) 1000 грн.

Модель задачі. Нехай А1 — адреса клітинки, що міститиме відповідь — значення параметра-ставки, a f(Al) — задана функ ція (мета дослідження), наприклад, зарплатня(А1) = 12*А1 Складаємо рівняння, яке є математичною моделлю задачі 12*А1 - 1000.

Хоча задача дуже проста, застосуємо для її розв'язування метод підбору параметра, який полягає в тому, що програма сама

має підібрати значення А1, щоб задовольнити будь-яке рівняння

Алгоритм дій користувача такий.

1. У будь-яку клітинку (але не в А1) треба занести формулу = f(Al). У нашому випадку формула така: = 12*А1.

2. Вибрати цю клітинку і виконати команду Сервіс => Підбір параметра. Отримаємо діалогове вікно Підбір параметра.

3. Заповнити три поля: а) зазначити адресу формули (вони буде вказана автоматично, якщо клітинка з формулою була вибрана перед цим); б) бажане значення формули, тобто с (у нашому випадку 1000); в) адресу параметра —А1.

4. Натиснути на ОК і у клітинці А1 отримати результат. Другий спосіб полягає у використанні можливостей програми Solver («Пошук розв'язку»), що додається до Excel. Вона дає змогу розв'язувати задачі з багатьма параметрами і з обмежсн нями. Наприклад, такі: скільки треба купити одиниць двох чи трьох найменувань товарів (це параметри), щоб вкластися в заплановану суму (це функція) і щоб кількості товарів не пере вищували деяких величин (це обмеження у вигляді нерівностей, див. роботу № 24).

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

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

Ідає правильний розв'язок. Наприклад, рівняння 2пх~п = sinnx спочатку треба звести до такого вигляду: х = (sinx + п)/2п.

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

x_l+l = (sinx + п)/2п.

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

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