Запустіть ЕТ і відкрийте нову книжку.

2. Уведіть у три діапазони масиви чисел у вигляді матриць: масив А і В розмірності три на три (по 9 елементів) і масив-стовпець D з трьох елементів.

Надайте діапазонам імена А, В і D.

3. Обчисліть 2*А.

4. Обчисліть добуток масивів-матриць А*В.

5. Обчисліть суму масивів-матриць А+В.

6. Обчисліть добуток діапазонів D*D. Результат — діапазон з квадратами даних з D.

Обчисліть детермінант матриці А.

Якщо детермінант дорівнює нулю, змініть числа в матриці А,

8. Розв'яжіть систему лінійних алгебраїчних рівнянь АХ= D, Метод розв'язування Х= А"¹*D, де А¹ — обернена матриця.

9. Перейдіть на новий аркуш для розв'язування задачі № 6 У першому рядку введіть заголовки стовпців: Місяці, X, У, Апроксимація.

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

Виберіть стовпець і застосуйте формулу {=TRANSPOSE(A2:K2)

Виконайте алгоритм використання функції TREND().

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

Виберіть спочатку діапазон, що містить експериментальні (Y), апроксимовані та прогнозовані дані (Апроксимація). Запустіть Майстра побудови діаграм. На першому кроці виберіть на закладці нестандартних діаграм тип Графік | Гістограма, що дасть змогу накласти графік лінії тренду на діаграму.

Обчисліть значення m і b.

Для обчислення сум застосуйте формули масивів на зразок {=SUM(niana3OHl* діапазон2)} і Ctrl+Shift+Enter.

Обчисліть значення r2.

Застосуйте функцію LINEST().

Аргументи функції описані в теоретичних відомостях. Пере вірте, чи збігаються ваші результати щодо значень m, b r2,. зі значеннями, які дає ця функція.

Скопіюйте вхідні дані задачі № 6 на третій аркуш.

Дослідіть графічні способи прогнозування за допомогою ліній тренду різних типів. Нанесіть на аркуш шість діаграм з лініями тренду.

Виберіть діапазон чисел Y і побудуйте просту стовпчикову діа граму (або графік з маркерами тощо). Застосуйте описаний в теоретичних відомостях алгоритм нанесення ліній тренду а відповідними параметрами дляпрогнозу на дві одиниці вперед і виведення рівнянь регресії та значення r 2.

Зробіть висновок про те, який тип апроксимації є най ліпшим.

Який прогноз обсягів доходу фірми у листопаді та грудні?

Продемонструйте три аркуші та закінчіть роботу.

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

1. Що таке МНК і яке його призначення?

2. У чому полягає суть задачі апроксимації даних?

3. У чому полягає суть задачі прогнозування даних?

4. Що таке регресійний аналіз?

5. Що таке однофакторний аналіз? Наведіть приклад задачі.

6. Що таке багатофакторний аналіз? Наведіть приклад задачі.

7. Які види ліній використовують для апроксимації даних?

8. Як оцінюють якість апроксимації?

9. Як найпростіше отримати лінійний прогноз даних?

10. Яке призначення функції TREND()?

11. Який алгоритм використання функції TREND()?

12. Яке призначення функції GROWTH()?

13. Яке призначення функції LINEST()?

14. Яке призначення функції LOGEST()?

15. Що таке масив чисел в ЕТ?

16. Які дії можна виконувати над масивами?

17. Які є функції для дій з масивами?

18. Що таке формула-масив?

19. Яке призначення акорду Shift+Ctrl+Enter?

20. Яке призначення ліній тренду?

21. Які лінії тренду можна нанести на діаграму?

22. На які діаграми можна нанести лінії тренду?

23. Як нанести лінію тренду на діаграму?

24. Що таке метод «змінного середнього»?

25. Що означає оцінка г2?

 

Лабораторна робота №6. MS Excel. Організація розгалужень та ітерацій. Метод добирання параметра. Задача «Нарахування зарплатні». Задача «Розв'язування нелінійного рівняння»

 

Мета

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

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

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

ґ 12, якщо розряд = 3; Ставка = < 10, якщо розряд = 2;

V 8, якщо розряд = 1.

Протягом місяця працівники зайняті різну кількість днів Треба ввести кількість відпрацьованих днів і нарахувати зарплатню працівникам, якщо відрахування (податки тощо) становлять 13% від нарахувань. Скласти бухгалтерську відо мість (див. рис. 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). Цей спосіб розглянемо під час виконання роботи.

Словник

Підбір параметра Подбор параметра Goal Seak

Пошук розв'язку Поиск решения Solver

Дата Дата Date

Якщо/і/або Если/и/или If/ And/Or

Захист Защита Protection

Функція Функция Function

Ітерації Итерации Iterations

Клітинка-ціль Целевая ячейка Targer Cell

Обмеження Ограничения Constraints

Сторінка/Книжка Лист/Книга Sheet/Book

СЬОГОДНІ СЕГОДНЯ TODAY

Хід роботи