Обробка даних засобами електронних таблиць Excel

У середовищі електронних таблиць Excel виконати завдання:

Створити книгу з одним листком. У верхньому колонтитулі вказати: ПІБ і ЛР12 — Обробка даних в Excel – II, у нижньому — номери сторінок. Вибравши команду Файл-Властивості, заповнити на вкладці Документ потрібні поля.

IV. Розв’язування систем лінійних алгебраїчних рівнянь за допомогою надбудови Пошук розв’язку(Поиск решения). Якщо в меню Сервіс немає команди Пошук розв’язку, то треба вибрати команду Сервіс – Надбудови (Надстройки), відмітити Пошук розв’язку і натиснути кнопку ОК.

Надбудову Пошук розв’язку можна використовувати для розв’язування окремих рівнянь і систем рівнянь (лінійних і нелінійних, алгебраїчних і трансцендентних), для розв’язування задач оптимізації.

Для розв’язування систем лінійних алгебраїчних рівнянь у таблицю треба ввести значення коефіцієнтів () при невідомих () і значення вільних членів () системи рівнянь. Потім в інші клітинки таблиці ввести формули лівихчастин рівнянь (), використовуючи адреси введених значень коефіцієнтів () і використовуючи адреси ще порожніх клітинок, у яких має розміститися розв’язок () даної системи рівнянь. Після цього вибрати команду Сервіс – Пошук розв’язку.

У діалоговому вікні Пошук розв’язку в поле Встановити цільову клітинку (Установить целевую ячейку) ввести адресу, клацнувши по клітинці, яка містить формулу лівоїчастини першого рівняння (перше рівняння вибирати не обов’язково — можна вибрати і будь-яке інше рівняння); потім відмітити параметр Значенню і для нього ввести значення відповідного вільного члена (тобто значення ).

У поле Змінюючи клітинки (Изменяя ячейки) ввести діапазон клітинок, в які треба помістити розв’язок системи рівнянь — значення невідомих .

Натиснути кнопку Додати (Добавить) і в діалоговому вікні Додавання обмеження в полі Посилання на клітинку (Ссылка на ячейку) задати адресу формули другого рівняння системи, в наступному полі вибрати знак =, у полі Обмеження (Ограничение) ввести адресу клітинки, в якій знаходиться відповідне значення вільного члена, і натиснути кнопку Додати. Аналогічні дії треба виконати для решти рівнянь системи, при цьому після задання обмежень для останнього рівняння системи треба натиснути не кнопку Додати, а кнопку ОК.

Для прискорення пошуку розв’язку лінійної задачі можна (але не обов’язково) натиснути кнопку Параметри і в діалоговому вікні Параметри пошуку розв’язку встановити опцію Лінійна модель.

У діалоговому вікні Пошук розв’язку натиснути кнопку Виконати; потім ОК.

Дослідіть, чи при зміні значень коефіцієнтів і вільних членів автоматично перераховується результат розв’язування системи, чи для перерахунку задачі треба повторно виконати команду Сервіс – Пошук розв’язку – ОК. Попробуйте розв’язати систему з виродженою матрицею (що при цьому відбувається?). Розв’яжіть нелінійну систему . (розв’язок: 6 і 4)

При розв’язуванні задач оптимізації у діалоговому вікні Пошук розв’язку відповідно до задачі треба відмічати Максимальному/Мінімальному значенню, а в вікні Додавання обмеження вибирати знак >= чи <=.

V. Побудова графіків функцій. Щоб побудувати графік функції треба її протабулювати на деякому проміжку з певним кроком і скористатися діаграмою типу Точкова (Точечная). При цьому, для лінійної чи кусково-лінійної функції графік буде мати найкращий вигляд, якщо вибрати вид діаграми зі з’єднанням точок відрізками прямих без маркерів. Якщо ж функція не лінійна, то краще використати з’єднання точок згладжуючими лініями (але при досить дрібному крокові табуляції можна також вибрати з’єднання точок відрізками прямих).

На відрізку (з кроком ) побудуйте графіки функцій , , , а також графіки кількох своїх функцій.

VI. Розв’язування рівнянь (знаходження точок перетину графіка функції з віссю абсцис).Щоб знайти точки перетину графіка функції з віссю Ох (тобто розв’язати рівняння ), можна скористатися засобами ітераційного підбору розв’язків — Підбір параметрів. При ітераційному розв’язуванні рівнянь розв’язок залежить від вибору початкового наближення. Початкові наближення можна знайти або протабулювавши функцію на деякому проміжку, або побудувавши її графік.

Перед використанням засобів підбору розв’язку треба вибрати команду Сервіс – Параметри – вкладка Обчислення, відмітити опцію Ітерації і встановити максимальну кількість ітерацій і відносну похибку обчислень. За умовчанням обчислення припиняються після 100 ітерацій чи при зміні результату менше, ніж на 0,001.

Для знаходження кожного окремого розв’язку треба виконати наведену нижче послідовність дій. В одну клітинку ввести початкове наближення, в іншу — формулу для заданої функції. Вибрати команду Сервіс – Підбір параметра. У діалоговому вікні Підбір параметра в полі Встановити в клітинці (Установить в ячейке) задати адресу клітинки, яка містить формулу для функції; в поле Значення ввести нуль; у полі Змінюючи значення клітинки (Изменяя значение ячейки) задати адресу клітинки, яка містить початкове наближення і в яку поміститься результат розрахунку. Натиснути кнопку ОК. Знайдіть точки перетину графіка функції з віссю Ох (початкове наближення вибирайте трохи віддаленим — наприклад, на 0,5 — від реального розв’язку). Розв’яжіть ще якесь рівняння.

Звіт з лабораторної роботи має включати виконані завдання пунктів IV – VI з короткими коментарями