Обчислення математичних виразів в Excel

 

 

Мета лекції – навчитися обчислювати математичні вира-зи за допомогою електронної таблиці Excel та створювати алго-ритм обчислень.

Питання лекції

1 Обчислення значення Y₁.

2 Обчислення значення Y₂.

 

3 Обчислення значення Y₃.

 

4 Обчислення значення Y₄.

 

Постановка завдання. Використовуючи подані нижче ма-тематичні вирази, обчислити значення виразів Y₁, Y₂, Y₃ і ре-зультати подати у вигляді таблиці. За вхідними та вихідними даними обчислити максимальне, мінімальне та середнє значен-ня.

ln(b

x 3

Y

a

b ^3

a

b

x

x

sin(a b

x),

b

e sin(x)

x ^3,

x

10,

^Y 2

ln(

x

1)

cos(x ^2

x

3.14 a

b), x

10,

b

x,

x

6,

Y

ln(a

b

x ^3),

x

14,

e a b x ,

x

14.

Значення a

3.1,

b

5.9,

x

1, 13, 0, 6, 12, 3, 27. При

x 20та x

6 функція не визначена.

Значення Y4

обчислюється так само, як і Y3, проте викори-

стовуються інші способи перевірки умови.

Загальні поради

 

1 Створюємо заголовок таблиці: Використання математи-чних функцій для обчислення значення виразів. Формат комірок задається Числовой з двома знаками після коми.

2 У відповідні комірки робочого листка заносяться вхідні

значення a 3.1, b 5.9, x 1, 13, 0, 6, 12, 3, 27 (рис. 1).

 

3 Під значеннями розміщуються заголовки для визначен-ня мінімального, та середнього значень.

 

4 Для визначення виразів Y₁, Y₂, Y₃ і Y₄ використовують формули, які містять посилання на адреси зі вхідними значен-нями – коефіцієнтів a та b, значення x та вказані функції. Еле-менти формули поєднуються відповідними знаками (математич-ними та пунктуаційними) і дужками для зміни пріоритету вико-нання обчислень.

 

1 Обчислення значення Y₁

1 Формули для обчислення Y₁ необхідно занести у комірку D4, а потім скопіювати у комірки D5:D10, тому адреси коефіці-єнтів a та b мають бути абсолютними, тобто не змінюватися під час копіювання, а адреса значення x має бути відносною, тобто змінюватися під час копіювання.

 

2 Формалізація задачі:

1) введення a, b;

2) введення х;

 

3) розрахунок значення Y ₁;

 

4) виведення Y ₁.

 

3 Алгоритм задачі, зображений на рис. 5.1.

 

 

Початок

 

 

a,

b

x

ln(b

x 3

b ^3

Y a

a b x x sin(a b x)

b

 

 

Y

 

 

Кінець

 

Рисунок 5.1 – Алгоритм обчислення значення Y₁

 

Аналогічно формули для визначення Y₂, Y₃ і Y₄ заносять у комірки Е4:Е10, F4:F10, G4:G10 (рис. 5.2).

 

 

Рисунок 5.2 – Початкова таблиця для розрахунків в Excel

 

4 Формулу для обчислень в комірку D4 можна вводити з кла-

віатури.

Формула для обчислення виразу Y₁ має вигляд

 

=$A$4*(LN($B$4+ABS(C4^3))/$B$4)-$B$4* СТЕ-

ПЕНЬ($A$4+$B$4*C4;1/3)+C4+SIN($A$4*$B$4*C4),

 

де $A$4 – абсолютна адреса із значенням коефіцієнта a, $B$4 – абсолютна адреса із значенням коефіцієнта b; C4 – відносна ад-реса поточного значення x; LN(…) – функція для визначення натурального логарифма; ABS(…) – функція для визначення модуля числа; SIN(…) – функція для визначення синуса числа СТЕПЕНЬ(…; значення степеня) – функція для визначення сте-пеня числа.

 

5 Формулу для обчислень можна вводити, використовую-

чи Майстер функцій.

 

Для використання Майстра функцій необхідно: Активізувати комірку D4, натиснувши =, вибрати мишкою

 

адресу коефіцієнта a і зафіксувати її клавішею F4, натиснути знак * (у рядку формул буде показаний текст =$A$4).

 

Потім викликається майстер функцій (рис. 5.3), вибираєть-ся категорія математична (рис. 5.4), функція LN (рис. 5.5) і нати-скається Ок (рис. 5.6), мишкою вказується адреса коефіцієнта b і фіксується (клавіша F4), знак додавання і заноситься вкладена функція ABS (рис. 5.7). Її ім‟я вибирають по стрілці із зони, що розміщена ліворуч від рядка формул, як показано на рис. 5.7.

 

 

Рисунок 5.3 – Виклик майстра функцій

 

 

 

а б

 

в г

 

Рисунок 5.4 – Вибір функції за допомогою майстра функцій

 

 

Рисунок 5.5 – Вибір функції натурального логарифма

 

 

Рисунок 5.6 – Заповнення аргументу функції логарифма

 

Рисунок 5.7 - Додавання вкладеної функції ABS (модуль числа)

 

Відкривається Майстер функцій для функції ABS, де не-обхідно занести аргументи для ABS – вибрати мишкою адресу значення x та математичну функцію СТЕПЕНЬ (рис. 5.8)

 

 

Рисунок 5.8 – Використання математичної функції СТЕПЕНЬ

 

 

Для повернення із вкладеної функції необхідно у рядку формул мишкою вибрати ім‟я викликаючої функції (у даному випадку LN), перейти у кінець тексту та продовжити набір (рис.

 

5.9).

 

 

Рисунок 5.9 – Набір формули для обчислення виразу

 

Далі знову викликаємо функцію СТЕПЕНЬ для обчислен-ня кореня третьої степені, оскільки серед функцій Excel є тільки квадратний корінь, виконуючи раніше описані дії. Функція СТЕПЕНЬ містить два параметри:перший–це вираз,який під-носиться до степеня, а другий – це значення самого степеня

 

(рис. 5.8, 5.10).

 

 

Рисунок 5.10 – Обчислення степеня

 

Далі в аргументі функції СТЕПЕНЬ пишемо знак дода-вання + та викликаємо математичну функцію SIN, в аргументі якої задаємо значення, зазначені на рис. 5.11.

 

Рисунок 5.11 – Заповнення аргументу функції SIN

 

Після закінчення введення формули мишкою необхідно натиснути Ок і результат обчислення заноситься у комірку D4. Використовуючи кнопку автозаповнення, введену формулу у комірки вниз по стовпчику до рядка 10 (рис. 5.12).

 

 

Рисунок 5.12 – Автозаповнення формул

 

2 Обчислення значення Y₂

 

1 Для обчислення Y₂, використовують логічну функцію ЕСЛИ. Вона реалізує алгоритм із розгалуженням і містить триаргументи:

 

=ЕСЛИ(умова; дія, якщо ІСТИНА; дія, якщо умова ХИБНА).

2 При обчисленні виразу Y₂ перевіряється, чи поточне значення x більше за 10, і якщо ця умова виконується, то обчи-слення проводиться за виразом

Ye sin x	x ^3.
Якщо ж умова не виконується, обчислення проводиться за
виразом:
	Y ln(		x		1) cos(x ^2	x 3.14 a b).

 

Формалізація задачі

 

1 Введення значень a, b.

 

2 Введення значення x.

 

3 Перевірка виконання умови x 10. Якщо умова вико-

нується, то	Y	e sin x	x ^3,якщо умова не виконується,
то Y ln(	x	1)	cos(x ^2	x 3.14 a b).

4 Виведення значення Y ₂.

 

Блок-схема алгоритму розв‟язання задачі подана нижче на рис. 5.13.

 

 

Початок

 

 

				a,	b
				x
				x		Істина
				^Y 2	e	sin x	x
				Хибно
Y ln(		x		1) cos(x ^2 x 3.14 a b)

 

 

Y

 

 

Кінець

 

Рисунок 5.13 – Блок-схема алгоритму для розрахунку виразу Y₂

 

3 Текст функції можна вводити або з клавіатури або за допомогою Майстра функцій. Якщо формулу вводити з клавіа-тури, то вона буде мати вираз

 

=ЕСЛИ(C4>10;EXP(SIN(C4))-СТЕПЕНЬ(C4;3/2);LN(ABS(C4)+ +1)-COS(СТЕПЕНЬ(C4;2)+C4-3,14*$A$4*$B$4)).

 

4 Також можна використовувати Майстер функцій. Перший рядок аргументів містить умову C4>10. Другий

 

рядок аргументів містить формулу, якщо умова виконується – вираз

 

EXP(SIN(C4))-СТЕПЕНЬ(C4;3/2).

 

Третій рядок аргументів містить формулу, якщо умова не виконується – вираз LN(ABS(C4)+1)-COS(СТЕПЕНЬ(C4;2)+

 

+C4-3,14*$A$4*$B$4).

При цьому знову викликаються вкладені функції – EXP,

SIN, СТЕПЕНЬ, LN, ABS, COS.

 

 

 

Рисунок 5.14 – Використання Майстра функцій

 

Після введення всього тексту формули необхідно поверну-тися у викликану функцію ЕСЛИ і натиснути Ок (рис. 5.14), або в кінці формули у рядку формул натиснути клавішу Enter.

 

3 Обчислення значення Y₃

1 Для визначення виразу Y₃ так само використовують фун-кцію ЕСЛИ, проте аргументами функції є не тільки формули для обчислення виразу, а й вкладені функції ЕСЛИ у випадку, якщо введена умова виконується або не виконується.

 

Формалізація задачі

 

1 Введення значення a, b.

 

2 Введення значення x.

 

3 Визначення умов.

У даному випадку значення функції може визначатися:

1)	Y	^3 b	x,	якщо x	6.
2)	Y	ln(a b	x ^3),якщо6	x 14.
3)	Y	ea b x	,	якщо x	14.

4) При x 20 функція Y₃ не визначена.

5) При x 6 функція Y₃ не визначена.

 

У даному випадку реалізується алгоритм із розгалужен-ням і перевіркою складної умови.

 

 

Спочатку використовується функція ЕСЛИ і перевіряється

чи значення x 6, і якщо так, то за умовою функція Y визнача-ється за позицією 1. У протилежному разі x більше або дорів-

нює 6. Таким чином, якщо умова x 6 не виконується, то необ-хідно перевіряти такі умови, щоб обчислити інші значення фун-кції Y.

 

Слід знову викликати функцію ЕСЛИ і зробити перевірку, чи значення x 6. Якщо ця умова виконується то необхідно ви-

вести повідомлення «функція не визначена».

Для перевірки інших значень знову необхідно викликати

функцію ЕСЛИ та перевірити умову x 20. Якщо ця умова ви-

 

конується то знову необхідно вивести повідомлення «функція не визначена».

 

Якщо вищезазначені умови не виконуються, то x знахо-диться в проміжку від 6 до 20. Отже, знову необхідно викликати функцію ЕСЛИ та перевірити умову x 14. Якщо ця умова ви-конується, то ми потрапляємо у проміжок від 14 до 20 і функція Y визначається за позицією 3.

Якщо ж ця умова не виконується, то ми потрапляємо в останній проміжок 6 x 14, де функція визначається за пози-цією 2.

Блок-схема алгоритму наведена нижче (рис. 5.15).

 

Початок

 

 

a, b

 

Істина

 

Істина

 

 

x

 

 

x 6

 

Хибно

 

x 6

 

Хибно

 

x 20

 

Істина

 

Y ₃ ³ b x

 

Функція не визначена

 

Хибно

 

x 14

 

Хибно

 

Y ₃ln(a b

 

 

Y₃

 

 

Кінець

 

 

Істина	Yea b x

 

 

x ³)

 

Рисунок 5.15 – Блок-схема алгоритму для розрахунку виразу Y₃.

 

4 Виведення значення Y.

2 При введені з клавіатури, повний текст функції Y₃ по-даний нижче: =ЕСЛИ(C4<6;СТЕПЕНЬ($B$4*C4;1/3);ЕСЛИ(C4=6;"не визна-

 

чена"; ЕСЛИ(C4>20;"не визначена"; ЕСЛИ(C4>14; EXP($A$4*B4*C4); LN($A$4*$B$4+СТЕПЕНЬ(C4;3))))).

 

 

3 При використанні майстра функцій існує чотири етапи. Перший етап перевірки проілюстрований на рис. 5.16.

 

 

Рисунок 5.16 – Перший етап перевірки

 

Другий етап перевірки проілюстрований на рис. 5.17.

 

Рисунок 5.17 – Другий етап перевірки

 

Третій етап перевірки проілюстрований на рис. 5.18.

 

Рисунок 5.18 – Третій етап перевірки

 

 

Четвертий етап перевірки проілюстрований на рис. 5.19.

 

Рисунок 5.19 – Четвертий етап перевірки

 

Потім натискається Ок, обчислене значення записується у комірку і копіюється вниз по стовпчику до рядка 10.

 

У наведеному прикладі всі умови, що перевіряються, є простими, тобто за один раз перевіряється лише одна умова, проте відокремлювалися і визначалися інтервали, в яких функ-ція Y₃ набувала того чи іншого значення (позиції 1 – 5).

 

 

4 Обчислення значення Y₄

1 Для обчислення Y₄ скористаємося іншими способами

перевірки умов.
			Формалізація задачі
		Функція Y4	не визначена, якщо x	або дорівнює 6, або
більше за 20. Тут необхідно використати функцію ИЛИ.
		Якщо ж значення x лежить у інтервалі, де x більше за
	та	менше або	дорівнює 14, то	Y4	обчислюється
Y	ln(a b x ^3). Тут необхідно використати логічну функцію

И.

 

3 Якщо ж x не лежить у жодному з перелічених інтерва-лів, то залишаються два інтервали. Для визначення одного з них необхідно використати функцію ЕСЛИ. Якщо, наприклад,

x 14, то ми потрапляємо в інтервал 14 x 20 Y ₄ e^{a b x} .

 

 

4 Якщо ж жодна з вищеперелічених умов не виконується, то ми автоматично потрапляємо в інтервал x 6, де Y₄ обчис-

люється за формулою Y	b x.

 

2 При обчисленні з клавіатури одержуємо таку формулу: =ЕСЛИ(ИЛИ(C8=6;C8>20);"не визначена"; ЕСЛИ(И(C8>14;

 

C8<=20); EXP($A$4*$B$4*C8); ЕСЛИ(И(C8>6;C8<=14); LN($A$4*B8+СТЕПЕНЬ(C8;3));СТЕПЕНЬ($B$4*C8;1/3))))

 

3 При обчисленні за допомогою Майстра функцій одер-жимо три етапи.

 

Перший етап виконання зображений на рис. 5.20 та рис. 5.21.

 

 

Рисунок 5.20 – Перший етап обчислення

 

Рисунок 5.21 – Використання функції ИЛИ

 

Другий етап виконання зображений на рис. 5.22 та рис. 5.23.

 

 

 

Рисунок 5.22 – Другий етап обчислення

 

 

Рисунок 5.23 – Використання функції И на другому етапі

 

Третій етап виконання зображений на рис. 5.24 та рис. 5.256.

 

 

Рисунок 5.24 – Третій етап обчислення

 

 

Рисунок 5.25 – Використання функції И на третьому етапі

 

Результат обчислень зображений на рис. 5.26.

 

Рисунок 5.26 – Результат виконання обчислень за простими (Y₃) та складними (Y₄) умовами

 

4 Мінімальне, максимальне та середнє значення можна обчислити, використовуючи статистичні функції МИН, МАКС та СРЗНАЧ.

 

Потім введений текст копіюють з комірок С11:С13 до ко-мірок D – G, праворуч.

 

У результаті матимемо такі розрахунки, що зображені на рис. 5.27.

 

 

 

Рисунок 5.27 – Остаточний результат

Висновки

 

 

Отже, електронна таблиця Microsoft Excel має дуже поту-жний вбудований апарат функцій, що виконують складні мате-матичні вирази, що відповідають лінійним та розгалуженим ал-горитмічним структурам.

 

Повинні знати

 

Після опрацювання теоретичного та практичного матеріа-

лу за темою «Обчислення математичних виразів в Excel»

 

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

 

Список літератури

 

1. Ярмуш О.В. Інформатика і комп‟ютерна техніка: навч. посібник / О. В. Ярмуш, М. М. Редько. – К.: Вища освіта, 2006. – 359с.

 

2. Баженов В.А. Інформатика. Комп‟ютерна техніка. Комп‟ютерні технології: підручник / В. А. Баженов. – 2-ге видання. – К.: Каравела, 2007.– 640с.

 

3. Інформатика: Комп‟ютерна техніка. Комп‟ютерні тех-нології: посібник / за редакцією О. І. Пушкаря. – К.:

 

ВЦ «Академія», 2001. – 696с.

 

4. Баженов В.А. Інформатика: Комп‟ютерна техніка. Комп‟ютерні технології: підручник / В. А. Баженов. –

 

К.: Каравела, 2003. – 464с.

 

5. Інформатика та комп‟ютерна техніка: навч.-метод. по-сібник / за редакцією О. Д. Шарапова. – К.: КНЕУ,

 

2005.– 534с.

 

6. Інформатика: Комп‟ютерна техніка. Комп‟ютерні тех-нології: посіб. / за редакцією О. І. Пушкаря. – 2-ге вид., перероб. і доповн. – К.: ВЦ «Академія», 2002.– 702с.

 

7. Тхір І. Л. Посібник користувача ПК / І. Л. Тхір, В. П. Галушка, А. В. Юзьків. – 2-ге видання. – Терно-піль: СМП «Астон», 2002. – 718с.

 

8. Бондаренко С. Excel 2003. Популярный самоучитель / С. Бондаренко, М. Бондаренко. – СПб.:Питер, 2005. – 320с.

9. Кузьмин В. Microsoft Office Excel 2003. Учебный курс

/ В Кузьмин. – СПб.:Питер, 2004. – 493с.

 

10. Дибкова Л. М. Інформатика і комп‟ютерна техніка. на-вч. посіб. / Л. М. Дибкова. – 2-ге вид., перероб. і до-повн. – К.: Академвидав, 2007. – 416с.

 

11. Корнеев, В. П. Информатика и компьютерная техника: учебное пособие: в 4 ч. Часть III. Обработка информа-ции с помощью электронных таблиц Microsoft Excel / В. П. Корнеев, В. Й. Николайчук. – 2-е издание. – К.:

 

ПП Графіка, 2005. – 270с.

 

12. Степанов А. Н. Информатика: учебник для вузов / А. Н. Степанов – 5-е изд. – СПб.: Питер, 2007. – 765 с.

 

13. Глушков С. В. Персональний комп‟ютер / С. В. Глуш-

ков, О. С. Сурядний. – Харків: Фоліо, 2007. – 509с.

14. Культин Н. Б. Microsoft Excel. Быстрый старт

/ Н. Б. Культин – СПб.: БХВ-Петербург, 2002. – 208с.

15. Шитов В. Н. Excel. Единый справочник / В. Н. Шитов.

– М.: ГроссМедиа, 2005. – 512 с.

 

16. Берлинер Э. М. Microsoft Office 2003 / Э. М. Берлинер, И. Б. Глазырина, Б. Э. Глазырин. – М.: ООО «Бином-

 

Пресс», 2004. – 576с.

17.	Холи Р., Холи Д. Excel. Трюки / Р. Холи, Д. Холи –
	СПб.:Питер, 2005. – 287 с.
18.	Лавренов С. М. Excel: Сборник примеров и задач
	/ С. М. Лавренов. – М.: Финансы и статистика, 2003. –
	336 с.

 

 

Лекція 6

ГРАФІЧНЕ ПОДАННЯ ДАНИХ

 

 

Мета лекції –розглянути основні типи діаграм електрон-ної таблиці Microsoft Excel та їх призначення. Ознайомитися з технологією створення, редагування та форматування діаграм.

Питання лекції

1 Призначення, основні поняття та типи діаграм.

 

2 Технологія створення діаграм.

3 Редагування та форматування діаграм.

 

4 Інтерполяція даних за допомогою діаграм.