РОБОТА В ТАБЛИЧНОМУ РЕДАКТОРІ MS EXCEL.

Тема: РОБОТА В ТАБЛИЧНОМУ РЕДАКТОРІ MS EXCEL.

План лекції

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

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

3. Дати.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Працюючи в Ехсеl, потрібно користуватися російськими (ЕСЛИ, И, ИЛИ) або англійськими (ІF, АND, ОR) назвами функцій.

3. Дати.

В економічних чи бухгалтерських задачах дати трапляються доволі часто. Дати в ЕТ чисто умовно зачисляють до даних типу дата. Насправді дати зберігаються в ЕТ як цілі числа і лише відображаються на екрані в тому чи іншому форматі, наприклад: 12 січня 2006 р. чи 12.01.2006, чи 2006-01-12, чи 8729. За точку відліку дат взято 1 січня 1900 року. Будь-яка :ата еквівалентна цілому числу, що дорівнює кількості днів, які :минули від точки відліку (38729 для 12 січня 2006 р.). Це дає змогу виконувати над датами такі операції: віднімати дати для «значення проміжку днів між двома датами, додавати чи віднімати від дат ціле число.

Можна застосовувати функції опрацювання дат з метою визначення окремих компонент дати: поточного номера дня в тижні (WЕЕКDАУ), в місяці (DАУ), номера місяця в році (МОNТН), року (YЕАR), поточної дати (ТОDАУ) тощо. Ці функції потрібно вставляти у вирази командами Вставити → Функція → Дати і час. Деякі з цих функцій мають параметри, дія яких описується у відповідному діалоговому вікні.

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

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

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

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

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

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

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

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

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

1. У будь-яку клітинку (але не в АІ) треба занести формулу =f(А1). У нашому випадку формула така: = 12*А1.
2. Вибрати цю клітинку і виконати команду Сервіс →Підбір параметра. Отримаємо діалогове вікно Підбір параметра.
3. Заповнити три поля: а) зазначити адресу формули (вона буде вказана автоматично, якщо клітинка з формулою була вибрана перед цим); б) бажане значення формули, тобто с (у нашому випадку 1000); в) адресу параметра —АІ.
4. Натиснути на ОК і у клітинці АІ отримати результат.

 Другий спосіб полягає у використанні можливостей програми («Пошук розв'язку»), що додається до Ехсеl. Вона дає змогу розв'язувати задачі з багатьма параметрами і з обмеженнями. Наприклад, такі: скільки треба купити одиниць двох чи трьох найменувань товарів (це параметри), щоб вкластися в заплановану суму (це функція) і щоб кількості товарів не перевищували деяких величин.

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

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

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

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

х₁₊₁ = (sinnx_i+ п)/2п,

де х_о - будь-яке початкове наближення, i=0, 1, 2,..., а замість п треба підставити значення свого варіанта. Домовимося, що коли і=8, то значення х_s ( тобто х₈) вважатимемо розв'язком рівняння.

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

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

3. Практична робота.

1.Запустіть програму ЕТ,  створіть нову книжку,  назвіть аркуш рівняння. Запишіть своє рівняння у вигляді 0=т(х) і розв'яжіть його методом підбору параметра.

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

. Зразок розв'язування задачі .

На  наступному  аркуші  розв'яжіть  нелінійне  рівняння методом простих ітерацій.

Введіть заголовок таблиці і назви стовпців так:

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

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

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

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

85 Наступна
С5 Похибка

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

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

86 <введіть свою формулу методу простої ітерації>
С6 =аbs(В6-А6)

А7 =В6

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

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

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

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

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

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

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

7. На  наступному аркуші  розв'яжіть  нелінійне  рівняння засобом Пошук розв'язку.

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

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

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

4. Рекомендована література:

1.     І. С.В.Симонович "Інформатика" базовый курс учебник для вузов. Изд. Питер, Санк-Петербург, 2011. - 638с.
2.     О.Колесник, И.Шишигин „Аппаратноые средства РС” .изд. «БХВ Петербург», Санкт-Петербург, 2009, 995с.
3.     С.В.Симонович «Спеціальная информатика».Изд.АСТпресс, Москва,2010, 478с.
4.     В.Є.Михайленко „Інженерна та комп’ютерна графіка” изд.”Каравела”, Київ,2008,335с.
5.     И.Т. Зарецкая "Інформатика"., К.: Форум, 2010. - 485 с.
6.     Я.Н.Глинський Практикум з інформатики , учбовий посібник, Львів – 2011. 295с.

5. Перелік питань до контролю самопідготовки

1. Як реалізуються розгалуження в ЕТ?
2. Який загальний вигляд має функція ЯКЩО?
3. Які ви знаєте логічні функції?
4. Яке призначення кнопки Автосума?

10.             Що таке абсолютна і змішана адреси клітинки?
11.            Як скопіювати таблицю на іншу сторінку?
12.            Яка відмінність між відносними і абсолютними адресами?

12.            Як ввести дату в клітинку?
13.            Який загальний вигляд має логічна функція І?

17.Який розділювач можна використовувати у списках аргументів функції?

18.            Як заповнити стовпець значеннями арифметичної прогресії?
19.            Як задати чи скасувати режим відображення формул?
20.            Який загальний вигляд має логічна функція АБО?
21.            Як вилучити рядок з таблиці?

25.             Яку стандартну функцію заміняє кнопка Автосума?
26.             Як виокремити несуміжні діапазони клітинок?
27.             У чому полягає метод підбору параметра?

29. Які задачі можна розв'язати методом підбору параметра?
30. Які є функцій для роботи з датами?
31. Яка відмінність між логічними функціями І та АБО?
32. Що таке засіб «Пошук розв'язку»?
33. Які є способи розв'язування нелінійного рівняння?

Завдання для перевірки знань

Вибрати і дати правильну відповідь або розмістити їх у правильному порядку:

1. Вкажіть функції, які відносяться до категорії «фінансові»:

а) МАКС;      б) БЗ;  в) МИН;  г) СУММ;  д) БДСУММ.

2. Функція СЧЕТЕСЛИ відноситься до категорії:

а) «статистичні»;

б) «фінансові»;

в) «робота з базами даних»;

г) «математичні»;

д) «логічні».

3. Для того, щоб викликати будь-яку фінансову функцію, необхідно вибрати пункт меню Excel, підпункт:

а) Файл  Функція  Фінансові;

б) Вид  Функція  Фінансові;

в) Сервіс  Функція  Фінансові;

г) Сервіс  Вставка  Фінансові;

д) Вставка  Функція  Фінансові.

4. Фінансові функції можна використовувати для:

а) створення зведених таблиць;

б) створення запитів;

в) прогнозування;

г) запису математичних виразів;

д) визначення накопиченої суми.

5. Функція НПЗ використовується для:

а) розрахунку майбутнього значення на основі постійних вкладень грошових засобів;

б) обчислення поточної вартості єдиної суми вкладень або позики, для фіксованих періодичних платежів;

в) розрахунку чистої поточної вартості, застосовується здебільшого під час оцінювання інвестицій;

г) визначення строку платежів;

д) розрахунок відсоткової ставки.

6. Для визначення строку платежів використовується функція:

a) БЗ;   б) ПЗ;   в) НОРМА;  г) КПЕР;  д) НПЗ.

7. До складових функції БЗ входять:

a) сума 1;

б) норма;

в) число періодів;

г) виплати;

д) нове значення.

8. Норма означає:

а) кількість платежів;

б) початкове значення вкладів;

в) 0 або 1 залежно від періоду виплати;

г) ставка відсотка за період;

д) майбутня вартість.

9. Загальний вигляд функції НОРМА:

а) = ім’я функції (норма; число періодів; виплати; НЗ; тип);

б) = ім’я функції (норма; число періодів; виплати; БС; тип);

в) = ім’я функції (норма; сума1; сума2; ...);

г) = ім’я функції (норма; виплати; НЗ; БС; тип);

д) = ім’я функції (число періодів; виплати; НЗ; БС; тип);

10. У разі використання функції БЗ, якщо відсотки нараховуються щороку, число періодів (n) і відсоткова ставка (k) розраховуються, як:

a) n;   k;

б) n * 2;   k / 2;

в) n * 4;   k / 4;

г) n * 12;   k / 12;

д) n * 365;   k / 365.