Лабораторна робота 4

Работа добавлена на сайт samzan.net:

Лабораторна робота №4. Прості обчислення та побудова діаграм в MS Excel

Мета:

Освоїти методику побудови графіків та поверхонь, а також навитись розв’язувати нелінійні рівняння із застосуванням MS Excel.

ТЕОРЕТИЧНІ ВІДОМОСТІ

1. Побудова поверхні

Продемонструємо технологію побудови поверхонь на прикладі наступної функції, що залежить від двох аргументів:

z= х2 - у2 при х  [-2; 2], у  [-1; 1].

Перш ніж скористатися майстром діаграм, треба побудувати таблицю значень функції z по обох її аргументах, наприклад, по аргументі x від -2 до 2 з кроком 0.2, а по у от -1 до 1 з кроком 0.2.

Для цього:

1. Введіть в комірку А2 значення -2, а в комірку АЗ — значення -1.8. Виберіть діапазон комірок А2:А3. Розташуєте курсор миші на маркері заповнення цього діапазону і простягніть його на діапазон А4:А22. Таким, образом, значення аргументу x протабульовані від -2 до 2 з кроком 0.2.

2. Введіть у комірку В1 значення -1, а в комірку С1 значення -0.8. Виберіть діапазон комірок В1:С1. Розташуєте курсор миші на маркері заповнення цього діапазону і простягніть його на діапазон D1:L1. Значення аргументу у протабульовані від -1 до 1 з кроком 0.2.

3. У комірку В2 уведіть формулу:

=$А2^2 - В$1^2

4. Виберіть комірку В2, розташуєте курсор миші на маркері її заповнення і простягніть його вниз на діапазон B2:L22.

На лівому робочому листі (рисунок 1) показано результат табуляції функції, що залежить від двох аргументів, а на правому — частина формул, що були введені в комірки при копіюванні формули, введеної в комірку В2, переміщенням маркера заповнення на діапазон B2:L22.

Рисунок 1 - Таблиця значень функції, що залежить від двох аргументів

Примітка

Використання у формулі абсолютного посилання на рядок і стовпець істотно. Нагадаємо, що знак $ в імені комірки, що стоїть перед номером рядка, створює абсолютне посилання на рядок, а перед ім'ям стовпця — абсолютне посилання на стовпець. Тому при перетаскуванні формули з комірки В2 на діапазони B2:L22, у комірках цього діапазону будуть знайдені значення функції z при відповідних значеннях аргументів x и у.

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

1. Виберіть команду Вставка | Диаграмма.

2. У діалоговому вікні, що з'явилося Мастер диаграмм (шаг 1 из 4): тип диаграммы на вкладці Стандартные у списку Тип виберіть значення Поверхность, а в списку Вид укажіть стандартну поверхню (рисунок 2). Натисніть кнопку Далее.

Рисунок 2 - Діалогове вікно Мастер диаграмм: тип диаграммы

3. У наступному діалоговому вікні майстра діаграм на вкладці Диапазон данных виберіть перемикач Ряды в столбцах, так як дані розташовуються в стовпцях. У поле введення Диапазон приведіть ссилку на діапазон даних A1:L22, тобто діапазон, що містить у собі як значення аргументів, так і значення функції (рисунок 3). Натисніть кнопку Далее.

4. У діалоговому вікні, що з'явилося Мастер диаграмм (шаг 3 из 4): в параметры диаграммы на вкладці Заголовки у поле Название диаграммы уведіть поверхню, у поле Ось X (категорий) укажіть х, в поле Ось Y (рядов данных) задайте у, в поле Ось Z (значений) введіть z. На вкладці Легенда скиньте прапорець Добавить легенду (рисунок 4). Натисніть кнопку Готово.

Рисунок 3 - Діалогове вікно Мастер диаграмм: источник данных диаграммы

Рисунок 4 - Діалогове вікно Мастер диаграмм: параметры диаграммы

5. У діалоговому вікні Мастер диаграмм (шаг 4 из 4) оберіть місце розташування діаграми (рисунок 5). Натисніть кнопку Готово.

Рисунок 5 - Діалогове вікно Мастер диаграмм (4-4)

Результат пророблених кроків представлений на рисунку 6.

Рисунок 6 - Побудована поверхня і діалогове вікно «Формат трехмерной проекции»

2. Рішення нелінійних рівнянь

Знаходження коренів рівняння методом ділення відрізку навпіл

Гарною вправою по роботі з електронною таблицею Star Office є програмування на робочому листі алгоритму знаходження кореня рівняння F(x) = 0 методом поділу відрізка навпіл. Нехай неперервна функція F(x) має значення різних знаків на кінцях відрізка [а; b], тобто F(a)F(b) < 0.

Тоді рівняння F(x)=0 має корінь усередині цього відрізка. Відрізок [а;b] називається відрізком локалізації кореня. Нехай с = (а + b)/2 — середина відрізка [а; b]. Якщо F(a)F(c)  0, то корінь знаходиться на відрізку [а; с], який беремо за новий відрізок локалізації кореня. Якщо F(a)F(c) > 0, то за новий відрізок локалізації кореня беремо [с; b]. Відзначимо, що новий відрізок локалізації кореня в два рази менше первісного. Процес розподілу відрізка для локалізації кореня продовжуємо доти, поки його довжина не стане менше , точності знаходження кореня. У цьому випадку будь-яка точка відрізка локалізації відрізняється від кореня не більше ніж на .

На рисунку 7 приведено результати знаходження кореня з точністю до 0.001 методом розподілу відрізка навпіл рівняння х2 - 2 = 0. За первісний відрізок локалізації кореня обраний [0; 2].

Рисунок 7 - Знаходження кореня рівняння методом розподілу відрізка навпіл

Примітка

Насправді в діапазон C4:F4 не треба вводити формули з клавіатури. Просто виберіть діапазон C3:F3, розташуєте покажчик миші на маркері заповнення і протащіть його на один рядок нижче.

Для реалізації цього методу введіть у комірки робочого листа формули або значення (табл. 1).

Таблиця 1 - Формули для знаходження коренів рівняння

Комірка	Формула або значення
В1	0.001
A3	0
ВЗ	2
СЗ	=(А3+В3)/2
D3	=(А3^2-2)*(С3^2-2)
ЕЗ	=С3^2-2
F3	=ЕСЛИ(ВЗ-АЗ<$В$1;"Корінь знайдений і дорівнює " & ТЕКСТ(СЗ;"0.0000") ;"")
А4	=ЕСЛИ(D3<=0;АЗ;СЗ)
В4	=ЕСЛИ ( D3<=0 ; СЗ ; ВЗ )
С4	=(А4+В4)/2
D4	=(А4^2-2)*(С4^2-2)
Е4	=С4^2-2
F4	=ЕСЛИ(В4-А4<$В$1;"Корінь знайдений і дорівнює " & ТЕКСТ(С4; "0.0000");"")

Тепер залишилося тільки вибрати діапазон A4:F4, розташувати курсор миші на маркері його заповнення і протащити його вниз доти, поки в стовпці F не з'явиться повідомлення про те, що корінь знайдений. У даному випадку повідомлення з'явиться в комірці F14, а значення кореня з точністю до 0.001 дорівнює 1.414.

На закінчення відзначимо, що в розглянутому прикладі використовувалися:

- Операція конкатенації рядків, що поєднує кілька рядків в одну. Операція конкатенація позначається символом амперсанда &. При об'єднанні двох рядків другий рядок додається безпосередньо в кінець першої. Результатом є рядок більшого розміру, що містить обидві вихідні рядки. Наприклад, "Andrey " & "Garnaev" дорівнює одному рядку "Andrey Garnaev". Зверніть увагу, що пробіл наприкінці першого рядка використовується для того, щоб у результуючій рядку відокремити перше слово від другого. Замість символу конкатенації можна також використовувати функцію СЦЕПИТЬ (CONCATENATE). Той же самий результат дає формула =СЦЕПИТЬ("Andrey ";"Garnaev"). Якщо значення Andrey введено в ячейку A1, а значення Garnaev — у комірку B1, то той же результат виходить формулою =СЦЕПИТЬ(А1;" ";B1) або =A1 & " " & B1.

- Функція робочого листа з категорії функцій по роботі з текстом ТЕКСТ (TEXT). Дана функція перетворить значення в текст у заданому числовому форматі.

Синтаксис функції ТЕКСТ: ТЕКСТ(значение;формат)

- значение — або числове значення, або формула, обчислення якої дає числове значення, або ссилка на комірку, що містить числове значення;

- формат — числовий формат із вкладки Число діалогового вікна Атрибуты ячейки, відображуваного на екрані вибором команди Формат | Ячейка. Формат не може містити зірочку * і не може належати до категорії Общий.

Приведемо три приклади роботи функції ТЕКСТ :

- ТЕКСТ(2.7153; "0.00 р.") повертає 2.72 р.

- ТЕКСТ(2.7153; "0.000") повертає 2.715

- ТЕКСТ("17.5.60"; "Д МММ,ГГГГ") повертає 17 травень,1960

ХІД РОБОТИ

Ознайомитися з основними можливостями побудови поверхонь та розв’язку нелінійних рівнянь засобами MS Excel.
Створити нову книгу Microsoft Excel, в якій записати завдання на трьох різних листах для кожного пункту завдання.
Провести розрахунок на робочих листах Microsoft Excel згідно завдання.
Зробити висновки по роботі.
Оформити звіт по роботі. В звіті необхідно показати детально всі кроки розв’язку поставлених завдань. Обов’язково навести формули, які використовувались при розв’язанні.