Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторна робота №4. Прості обчислення та побудова діаграм в MS Excel
Мета:
Освоїти методику побудови графіків та поверхонь, а також навитись розв’язувати нелінійні рівняння із застосуванням MS Excel.
Продемонструємо технологію побудови поверхонь на прикладі наступної функції, що залежить від двох аргументів:
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 - Побудована поверхня і діалогове вікно «Формат трехмерной проекции»
Гарною вправою по роботі з електронною таблицею 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
1. Побудувати в одній системі координат при графіки функцій:
2. Побудувати поверхню при .
3. Знайти всі корені рівняння
1. Побудувати в одній системі координат при графіки функцій:
2. Побудувати поверхню при .
3. Знайти всі корені рівняння: .
1. Побудувати в одній системі координат при графіки функцій:
2. Побудувати поверхню при .
3. Знайти всі корені рівняння .
1. Побудувати в одній системі координат при графіки функцій:
;
;
2. Побудувати поверхню
3. Знайти всі корені рівняння .
1. Побудувати в одній системі координат при графіки функцій:
2. Побудувати поверхню при
3. Знайти всі корені рівняння .
1. Побудувати в одній системі координат при графіки функцій:
2. Побудувати поверхню при .
3. Знайти всі корені рівняння .
1. Побудувати в різних системах координат при графіки функцій:
2. Побудувати поверхню при /
3. Знайти всі корені рівняння .