Відмінити виділення:

• курсор у довільне місце таблиці;

• ЛК1.

Під час роботи з даними в електронних таблицях може ви-никнути необхідність у використанні нижніх або верхніх індек-сів змінних. Розглянемо порядок введення таких індексів.

Введення нижніх і верхніх індексів

1. Ввести символ основного шрифту.

2. Вибрати команди головного меню Формат – Ячейки.

3. Вибрати вкладку Шрифт.

105

4 У групі команд Эффекты призначити Нижний индекс.

5 Призначити розмір шрифту 8 пт.

6 Натиснути на кнопку ОК.

7 Ввести нижні індекси.

8 Натиснути на [Enter].

Верхні індекси вводяться аналогічно.

Висновки

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

=Ім’я_функції(аргумент).

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

Після опрацювання теоретичного та практичного матеріа-лу за темою «Робота з функціями в Excel» студент повинен чі-тко розуміти призначення, сферу застосування функцій елект-ронної таблиці Excel, знати та оперувати основними функціями електронної таблиці на практиці, знати способи введення даних у функції та вміти їх редагувати, форматувати, здійснювати роз-рахунки за їх допомогою.

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

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

2006. – 359с. – С. 234 – 238.

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

2003. – 464 с. – С. 223– 230.

106

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

демія», 2001.– 696с. – С. 368 – 376.

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

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

«Астон», 2002. – 718с. – С. 585 – 601.

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

С. 206 – 227.

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

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

3. Глушков С. В. Персональний комп‟ютер / С. В. Глушков, О. С. Сурядний. – Харків: Фоліо, 2007. – 509с. – С. 299 – 318.

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

Академвидав, 2007. – 416с. – С. 163 – 178, 188 – 193.

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

2005. – 270с. – С. 42 – 85.

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

С. 558 – 563.

107

Лекція 5

ОБЧИСЛЕННЯ МАТЕМАТИЧНИХ ВИРАЗІВ В EXCEL

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

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

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

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

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

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

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

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

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 Обчислення значення y1

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

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

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

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

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

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

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

109

Початок

a ,

b

x

ln(b

x3

b 3

Y a

a b x x sin(a b x)

1

b

Y

Кінець

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

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

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

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

віатури.

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

110

=$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 – Виклик майстра функцій

111

а б

в г

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

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

112

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

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

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

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

113

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

5.9).

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

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

(рис. 5.8, 5.10).

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

114

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

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

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

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

115

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

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

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

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

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

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

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

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

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

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

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

116

Початок

a ,

b

x

x

10

Істина

Y2

e

sin x

x

3

Хибно

Yln(

x

1) cos(x2 x 3.14 a b)

2

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)).

3. Також можна використовувати Майстер функцій. Перший рядок аргументів містить умову 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.

117

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

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

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

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

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

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

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

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

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

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

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

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

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

118

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

чи значення 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).

119

Початок

a , b

Істина

Істина

x

x 6

Хибно

x 6

Хибно

x 20

Істина

Y₃ ³ b x

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

Хибно

x 14

Хибно

Y₃ ln( a b

Y₃

Кінець

Істина	Yea b x
	3

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))))).

120

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

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

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

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

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

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

121

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

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

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

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

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

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

И .

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

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

122

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

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))))

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

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

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

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

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

123

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

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

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

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

124

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

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

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

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

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

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

125

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

Висновки

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

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

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

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

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

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

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

126

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

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

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

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

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

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

2005.– 534с.

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

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

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

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

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

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

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

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

127

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

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

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

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

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

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

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

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

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

128

Лекція 6

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

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

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

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

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

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

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

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

1.1 Основні поняття діаграм

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

Діаграма – це графічний спосіб подання даних.

Діаграму можна розмістити на окремому листі або на тому самому листі, де знаходяться і дані, на основі яких вона побудо-вана. За допомогою діаграм можна прокотити початковий аналіз інформації, розміщеної в таблиці.

Кожна діаграма пов‟язана з таблицею значень або інфор-маційною таблицею.

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

129

Рядок даних – відображення значення одного рядка стовп-чика таблиці на діаграмі.

Маркер – відображення кожної комірки на діаграмі.

Маркером може бути точка на графіку, сегмент круга, сто-впчик гістограми та інші залежно від типу діаграми.

Сукупність маркерів одного кольору відображає один ря-док. Елементи діаграми подані на рис. 6.1.

1.2 Призначення діаграм

Більшість діаграм впорядковують дані відносно вертика-льної осі (осі у) та горизонтальної (осі х) осей. Ці осі аналогічні рядкам і стовпчикам. Як на робочому листку дані розміщуються по рядках і стовпчиках, так і на діаграмі вони орієнтуються по горизонтальній та вертикальній осям.

Вісь у називається віссю значень (або віссю рядів даних). Ця вісь відображає значення залежних змінних. Вісь х – це вісь категорій і відображає значення незалежних змінних.

За замовчуванням значень рядки таблиці утворюють ряд даних, а значення стовпчиків – категорію.

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

Поряд з діаграмою може бути розміщена легенда.

Легенда – це таблиця, яка пояснює, якого кольору ряд да-них і що визначає (відображає) цей ряд на діаграмі.

Крім того, на діаграмі можуть бути інші надписи (відо-браження долі входження кожного показника, підписи осей та інші).

При друку діаграми, що знаходяться поряд з даними, ра-зом з даними необхідно переконатися, що діаграма не виділена,

130

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

Рисунок 6.1 – Елементи площинної діаграми

1.3 Типи діаграм

Стандартні діаграми бувають: гістограма; лінійна; графік;

кругова діаграма; кільцева діаграма; діаграма з областями; точкова діаграма.

У таблиці 6.1 подані основні типи діаграм та їх вигляд.

131

Таблиця 6.1 – Типи діаграм

132

Продовження таблиці 6.1

133

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

2.1 Майстер діаграм

Розглянемо спосіб побудови діаграми з використанням

Майстра діаграм. Перед запуском. За допомогою Майстра діа-

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

При створенні діаграми можна використати дані, розміще-ні в несуміжних комірках. Для виділення таких комірок потріб-но виділяти їх по черзі, утримуючи натиснутою клавішу Ctrl.

Після виділення таблиці натискають кнопку Мастер диаг-рамм на панелі інструментів або користуються меню Вставка -

Диаграмма.

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

2.2 Перший крок роботи Мастера диаграмм

На першому кроці роботи Майстра діаграм вибирають тип діаграми (рис. 6.2). У даному вікні є дві вкладки: Стандар-

тные і Нестандартные.

• полі Тип диаграммы вибирається тип діаграми, в полі Вид – її формат (спосіб подання).

• більшості типів діаграм перший стовпчик поля Вид – це види (об'ємні або плоскі зображення), коли дані різних рядів подані окремими точками даних (стовпчиками, пірамідами, об-ластями тощо) різних кольорів. Другий стовпчик поля Вид - це так звані типи «з накопиченням», в яких точки різних рядів да-них відображаються на одному об'єкті (стовпчику, піраміді, об-ласті), але різного кольору. Третій стовпчик поля Вид відпові-дає «нормалізованим» типам діаграм, коли відображається від-

134

соток входження окремої точки даних кожного ряду в певному абсолюті – нормі.

Можна натиснути кнопку Готово і створення діаграми за-вершиться, діаграма матиме встановлені за замовчуванням па-раметри. При використанні наступних кроків параметри, вста-новлені при автоформатуванні, можна змінити на власний роз-суд.

Рисунок 6.2 – Перший крок роботи Майстра діаграм

2.2 Другий крок роботи Мастера диаграмм

Для переходу до наступного кроку натискаємо Далее. На екрані з'явиться вікно другого кроку роботи майстра діаграм. Якщо було заздалегідь вибрано діапазон даних, на основі яких будуємо діаграму, то буде відображено попередній вигляд діаг-рами вибраного типу і формату (рис. 6.3).

В полі Диапазон відображаються адреси комірок, які утво-рюютУ інформаційну таблицю. Якщо інформаційну таблицю не було виділено, то, використавши кнопку в кінці поля Диапазон, можна виділити потрібні комірки.

Перемикачі строках та столбцах поля Ряды в дозволяють змінити орієнтацію даних. При використанні перемикача стро-ках рядами даних будуть значення рядків інформаційної табли-ці. При використанні перемикача столбцах рядами даних є сто-впці інформаційної таблиці. У випадку, якщо інформаційна таб-лиця була правильно виділена перед початком роботи майстра, орієнтація даних за замовчуванням встановиться правильно.

135

Вкладка Ряд цього вікна призначена для додавання та ви-далення рядів даних, а також для задання підписів до рядів та до осі х. На цій вкладці потрібно переконатися, що перелік рядів даних, включених до діаграми, не містить зайвих (наприклад, до рядів даних не можна віднести нумерацію рядків таблиці).

Рисунок 6.3 – Другий крок роботи Майстра діаграм

Використовуючи кнопки Добавить та Удалить, можна додати чи видалити ряди даних. Поле Подпись оси X дозволяє задати написи, які будуть розміщені на осі х.

2.3 Третій крок роботи Мастера диаграмм

На третьому кроці роботи Майстра діаграм задаються па-раметри форматування діаграми (рис. 6.4). Вікно Параметри диаграммы містить кілька вкладок, у кожній з яких визначаєть-ся вигляд того чи іншого елемента діаграми. Вкладка Заголовки дозволяє задати назву діаграми та заголовки її осей. На вкладці Оси вказується режим відображення міток на осях діаграми. Як мітки осі х можна використовувати назви категорій або значен-ня часу. В другому випадку ціна поділки осі х буде дорівнювати певному проміжку часу. Такий тип міток застосовується авто-матично, якщо дані, на основі яких побудована діаграма, мають формат дати.

Вкладка Линии сетки слугує для нанесення на діаграму масштабної сітки. Сітка може мати великий або малий крок.

На вкладці Легенда користувач може вказати, чи потрібна на діаграмі легенда. Використання легенди дозволяє значно по-

136

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

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

Рисунок 6.4 – Третій крок роботи Майстра діаграм

Іноді поряд з діаграмою потрібно розмістити таблицю, яка містить значення, на основі яких побудовано діаграму. В цьому випадку використовують вкладку Таблица данных. При активі-зації перемикача ключ легенды поряд з назвами рядів даних у таблиці з'явиться значок, який показує, як саме даний ряд поз-начено на діаграмі.

2.4 Четвертий крок роботи Мастера диаграмм

На останньому кроці роботи Майстра діаграм задається розміщення діаграми: на окремому робочому листку або на лис-тку з таблицею (рис. 6.5).

Рисунок 6.5 – Четвертий крок роботи Майстра діаграм

137

Завершення роботи Майстра діаграм відбувається натис-ненням на кнопку Готово.

Створена діаграма з'явиться на робочому листку (рис. 6.6).

Рисунок 6.6 – Створена діаграма

Як приклад розглянемо етапи побудови графіка функцій f (x) , якщо

f (x)	10	12	15	20
x	4	8	11	17

Процес побудови графіка функцій почнемо зі створення таблиці у Excel (рис. 6.7).

Рисунок 6.7

Тепер нам необхідно виділити тільки рядок з функцією f (x) (рис. 6.8).

Рисунок 6.8

Після чого необхідно викликати Майстер діаграм та у вкладці Стандартные обрати тип График та вигляд графіка. Далі перейти на наступний крок, де у вікні з‟явиться графік

(рис. 6.9)

138

Рисунок 6.9

Із рис. 6.9 видно, що графік має не ті підписи за віссю Х, тому переходимо на вкладку Ряд. На цій вкладці необхідно об-рати меню Подпись по оси Х (рис. 6.10), де за допомогою від-повідної клавіші виділити у таблиці Excel ряд зі значеннями Х без назви (рис. 6.11).

Рисунок 6.10

Рисунок 6.11

Потім поле Подпись по оси Х (рис. 6.12) буде заповнене та на графіку з‟являться правильні підписи за віссю Х.

139

Рисунок 6.12

Після чого переходимо на наступний крок Майстра діаг-рам, де з‟являється вікно, що зображене на рис. 6.13.

Рисунок 6.13

На вкладці Заголовки можна зробити підписи графіка, під-писи осей Х та У (рис. 6.14).

Рисунок 6.14

140

Для налаштування ліній сітки необхідно обрати вкладку Линии сетки та поставити прапорець у полі Ось Х – основные линии (рис. 6.15).

Рисунок 6.15

За допомогою вкладки Легенда можна налаштувати роз-міщення легенди: зліва, справа, внизу, вгорі. За замовчуванням легенда розміщується справа. Після всіх налаштувань слід пе-рейти на четвертий крок Майстра діаграм, де необхідно вибра-ти місце розташування діаграми: на листку з даними або на чис-тому листку. Результатом завершення побудови є графік функ-ції, що зображений на рис. 6.16.

Рисунок 6.16

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

3.1 Редагування діаграми

Діаграму, як будь-який об'єкт, можна розмістити в довіль-ному місці робочого листка. Використовуючи кутові маркери, можна змінювати розмір діаграми.

141

Якщо необхідно повернутися на деякий крок роботи майс-тра побудови діаграм, щоб змінити ті чи інші налаштування, це можна зробити одним із способів (попередньо виділивши діаг-раму):

1 Вибрати з меню Диаграмма один із пунктів, на який по-трібно повернутися (див. рис. 3.1);

2 Викликати контекстне меню (натиснути праву кнопку мишки) у вільному місці діаграми, з контекстного меню вибрати один із пунктів (аналогічних як і в меню Диаграмма);

3 Натиснути кнопку Мастер диаграмм та повторити пот-рібні кроки роботи майстра. Якщо діаграма була виділена, то не створюється нова діаграма, а здійснюється зміна параметрів ак-тивної (рис. 6.17);

4 Скористатися потрібною піктограмою з панелі інстру-

ментів Диаграммы.

Після внесення потрібних змін, діаграма буде налаштова-

на.

Якщо змінити дані, на основі яких побудована діаграма, вона автоматично змінюється.

Іноді необхідно здійснити налаштування деякої частини діаграми (змінити колір рядів даних, здійснити зафарбування фону, змінити шкалу, шрифт підписів тощо).

У цьому випадку достатньо на потрібному елементі діаг-рами (заголовка, осі, ряду даних, області стінок діаграми тощо) натиснути праву клавішу мишки і з контекстного меню вибрати пункт Формат (наприклад, Формат линий сетки, Формат оси, Формат рядов данных).

Рисунок 6.17 – Меню діаграми для повернення на один із кроків роботи майстра діаграм

142

3.2 Додавання даних до діаграм

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

Коли Excel не може однозначно визначити, як розмістити додаткові дані, активізується діалогове вікно Специальная

вставка, де і виконуються необхідні уточнення.

Включення додаткових даних також можливе за допомо-гою контекстного меню форматування. За командою Исходные данные виділені додаткові дані включаються в діаграму.

3. Зміна орієнтації об’ємних діаграм

• об‟ємних діаграмах стовпці переднього рядка даних зав-жди затуляють стовпці заднього ряду (рис. 6.18, діаграма росій-ськомовна). За наявністю трьох і більше рядів інформативність подібних гістограм різко знижується і користувач змушений ви-брати інший тип або вигляд діаграми.

Рисунок 6.18 – Елементи об’ємної діаграми

Але в Excel є можливість розвороту об‟ємних діаграм, щоб приховані стовпці стали видимими. Для цього необхідно виділи-

143

ти область побудови гістограми, сумістити покажчики мишки з одним із маркерів-квадратиків і клацнути лівою клавішею миш-ки. При цьому на екрані відображається лише каркас діаграми, який можна розвернути в будь-якому напрямі. Після звільнення клавіші на екрані з‟являється початкова гістограма, але вже по-вернена на певний кут. Процес повторюється до здобуття гісто-грами зі стовпцем, що добре переглядається. Якщо це не вдаєть-ся, то залишається вибрати інший тип або вигляд діаграми.

Розворот об‟ємної діаграми можна здійснювати також за допомогою діалогового вікна Формат трѐхмерной проэкции. У цьому вікні необхідно задати кут і напрямок повороту діагра-ми, її висоту, піднесення, перспективу та інші. Вікно активізу-ється за командою Объемный вид із спеціального контекстного меню, яке можна викликати на екрані, коли виділено область побудови діаграми.

3.4 Виділення сегментів кругової діаграми

Кругові діаграми відображають значення тільки однієї змінної і демонструють співвідношення між цими даними у від-сотках. Для поліпшення інформативності та наочності таких діа-грам їхні сегменти можуть висуватися (рис. 6.19).

З цією метою досить виділити відповідний сегмент і потім перетягти його на відповідне місце діаграми.

Для відновлення первинного вигляду діаграми необхідно виділити сегмент, повернути його на колишнє місце і вилучити маркери-чотирикутники натисканням на клавішу ESC.

Рисунок 6.19 – Виділення сегментів колової діаграми

144

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

Розглянемо приклад, в якому за допомогою діаграм можна відновити втрачені дані шляхом інтерполювання даних.

Деяка інформація про попит на меблі не надійшла на підп-риємство. Відома інформація відображена в таблиці 6.2. Вини-кає завдання відновити інформацію про кількість проданих оди-ниць товару підприємства протягом року.

На основі даних стовпців Місяці та Кількість проданих одиниць товару побудувати діаграму – графік.

Таблиця 6.2 – Кількість проданих одиниць товару

На третьому кроці роботи Майстра побудови діаграм у ві-

кні Параметри диаграммы на закладці Линий сетки задати налаштування, як показано на рис. 6.20.

145

Рисунок 6.20 – Вікно налаштування параметрів діаграми

Щоб відобразити на графіку ті значення, яких немає в таб-лиці, необхідно викликати з меню Сервис - Параметры... вікно Параметры і на закладці Диаграмма встановити перемикач

Значення интерполируются так, як це показано на рис. 6.21.

Рисунок 6.21 – Вікно Параметри

У результаті графік матиме вигляд, наведений на рис. 6.22.

Рисунок 6.22 – Одержаний графік після інтерполяції

146

Для тих місяців, інформація про які не міститься в таблиці 6.2 (квітень, липень, жовтень), можна визначити точку перетину вертикальних ліній сітки з отриманим графіком. Це і буде приб-лизне значення кількості проданих одиниць товару в даному мі-сяці.

ВИСНОВКИ

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

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

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

лу за темою «Графічне подання даних в Excel» студент пови-

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

147

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

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

2006. – 359с. – С. 240 – 244.

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

2003. – 464 с. – С. 250– 255.

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

мія», 2001.– 696с. – С. 384 – 402.

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

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

«Астон», 2002. – 718с. – С. 602 – 616.

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

С. 229 – 251.

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

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

8. Юдин В. И. Ученик в Microsoft Excel XP. Электронный ре-сурс. – Краматорск.: Режим доступа: – Глава 6. Диаграм-мы и графики.

9. Глушков С. В. Персональний комп‟ютер / С. В. Глушков, О. С. Сурядний. – Харків: Фоліо, 2007. – 509с. – С. 318 –

337.

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

К.: Академвидав, 2007. – 416с. – С. 178 – 180.

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

573 – 580.

148

Лекція 7

РОЗВ’ЯЗАННЯ ПРИКЛАДНИХ ЗАДАЧ В EXCEL

Мета лекції – розглянути основні принципи знаходження розв‟язку прикладних задач у Microsoft Excel.

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

1 Підбір параметрів.

2 Пошук рішення.

3 Робота з макролсами.

1 Підбір параметрів

Щоб визначити значення однієї комірки при змінені зна-чення іншої (такі комірки мають бути пов‟язані формулою) ви-користовують підбір параметрів.

Розглянемо цю процедуру на прикладі.

Приклад 7.1 Необхідно визначити термін кредиту, за яко-го перший внесок (поле «Всього до сплати, грн..») становить 500 грн. (у таблиці на рис. 7.1 – 566.7);

Розв’язання

Формули для розрахунку відображені на рис. 7.2.

Рисунок 7.1 – Розрахунок початкового внеску за кредит

Рисунок 7.2 – Формули розрахунку для початкового внеску за кредит

149

Для цього спочатку встановлюють курсор у комірку G2, після чого необхідно активізувати команди Сервис→Подбор параметра. Внаслідок цих дій з‟явиться вікно (рис. 7.3), в яко-му в полі Установить в ячейке вводять адресу комірки, зна-чення якої необхідно знайти; в полі Значение: – числове зна-чення, яке потрібно знайти для активної комірки (G2); в полі Изменяя значение ячейки – адресу комірки С2, значення якої необхідно знайти та активізувати кнопкою ОК.

Після чого виведеться результат виконання операції (рис. 7.4). Якщо рішення знайдено, то при активізації кнопки ОК нове значення залишається в комірці G2, а кнопки Отмена

– відновлюється попереднє значення.

• результаті знайдений термін кредиту становить 28.6 мі-сяців (рис. 7.5)

Отже, процедура підбору параметра дає можливысть легко отримати потрібний результат, визначивши лише залежну комі-рку (або кілька комірок). Таку дію не можливо виконати влас-норуч, без використання цієї процедури.

Рисунок 7.5 – Результат розрахунку

Приклад 7.2 За допомогою Excel розв‟язати рівняння ln(x) 10 .

150

Тут необхідно в комірку А1 ввести значення 10, а у комір-ку В1 ввести формулу =Ln(A1) та натиснути клавішу Enter

(рис. 7.6).

Рисунок 7.6 – Завдання рівняння в Excel

Потім необхідно виділити комірку В1, та за допомогою меню Сервис→Подбор параметра викликати вікно Подбор па-

раметра. У цьому вікні задаємо значення підбору параметрів: Установить в ячейке – адресу комірки В1, де знаходиться фо-рмула, значення якої необхідно знайти, Значение – значення 10 (праву частину рівняння), Изменяя значение ячейки – адресу комірки А1, значення якої буде змінюватися.

Рисунок 7.7 – Підбір параметра

Після виконання одержимо результат, що зображено на рис. 7.8. Отже, результатом розв‟язку рівняння буде x 22025.84 . Правильність результату можна перевірити за до-помогою функцій Excel, якщо в будь-якій комірці набрати таку функцію =LN(A1), де А1=22025,84. Результат виконання цієї функції буде 9,999972. Таким чином, рівняння розв‟язане прави-льно.

151

Рисунок 7.8 – Результат виконання пошуку параметра

2 Пошук рішення

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

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

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

Наприклад, на основі таблиці з обчисленнями прибутку за-лежно від витрат на рекламу можна визначити такі оптимальні витрати на рекламу, які забезпечать максимальний прибуток тощо.

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

152

Комірка з кінцевим значенням називається цільовою комі-ркою, а комірки з вхідними даними, що підлягають зміні, нази-ваються змінними комірками. Цільова комірка обов‟язково по-винна містити формулу або функцію, параметрами якої є зна-чення змінних комірок.

Розв‟язання задачі оптимізації передбачає створення еко-номіко-математичної моделі задачі, а реалізація створеної моде-лі покладається на програму Поиск решения, яка знаходить оп-тимальне рішення.

Програма Поиск решения в Excel автоматично не встано-влюється, тому її спочатку необхідно налаштувати за допомо-гою меню Сервис→Надстройки та поставити прапорець у полі

Поиск решения (рис. 7.9).

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

Рисунок 7.9 – Вікно пошуку рішень

У вікні 1 – адреса цільової комірки, 2 – значення цільової комірки, яке необхідно встановити, 3 – діапазон значень, що змінюються, 4 – вікно задання обмежень, які можна добавити за допомогою кнопки Добавить, які можна редагувати за допомо-гою кнопки Изменить. Кнопка Выполнить активує програму.

Для кращого розуміння даного питання розглянемо прик-

лад.

153

Приклад 2.1 У таблиці на рис. 7.10 сумарна кількість това-рів Тип 1 – Тип 6 становить 1610 шт., дані витрати на кожну одиницю товару різного типу. Необхідно обчислити загальну вартість товарів. Визначити кількість одиниць продукції кожно-го типу, яку має виготовляти фірма, якщо на даному обладнанні не можна виготовляти більше ніж 2000 одиниць продукції, якщо на виготовлення всієї продукції виділяється 500000 грн.

Розв’язання

Спочатку обчислюються загальні витрати для кожного ти-пу товару за формулою

Загальні витрати=Кількість*Витрати на одиницю.

Спочатку створюється математична модель.

Позначимо кількість одиниць продукції першого типу як x₁ , другого типу – як x₂ , третього типу – x₃ і т. ін.

За умов задачі на виробництво продукції виділяється 500000 гривень, тобто загальні витрати мають сягати до 500000, то цільова функція матиме вигляд:

F (x_i ) 300 x₁ 200 x₂ 100 x₃ 250 x₄ 80 x₅

75 x₆ 500000

На значення параметрів x_i за умовою задачі накладаються

обмеження. Оскільки виготовляється продукція, то значення комірок від В2 до В7 повинно бути цілим, оскільки половину від товару не виготовляють, а виготовляють цілу одиницю това-ру. Також значення цих комірок повинно бути додатним, оскі-льки одиниці товару не може бути від‟ємним.

Запишемо обмеження математично:

x1 , x2 , x3 , x4 , x5 ,			x6	0,
x1 , x2 ,	x3 , x4 ,	x5 ,	x6	цілі,
x1x2	x3x4	x5x6		2000.

154

Рисунок 7.10 – Таблиця товарів різного типу

Оскільки на даному обладнанні не можна виготовляти бі-льше ніж 2000 одиниць продукції, то на сумарну кількість това-ру (комірку В8) необхідно накласти обмеження ≤ 2000.

Цільовою коміркою буде комірка, яка містить загальне значення виготовлення всієї продукції, тобто 500000.

Викличемо програму Поиск решения, після чого з‟явиться вікно програми (рис. 7.11). У зоні Установить целевую ячейку

треба вказати адресу цільової комірки, тобто D8, і показати, якої величини повинно бути значення комірки, тобто 500000.

У зоні Изменяя ячейки необхідно вказати адреси змінних комірок, тобто В2:В7, оскільки змінюється кількість одиниць продукції.

Рисунок 7.11 – Вікно Поиск решения

У зоні Ограничения необхідно занести обмеження, натис-нувши на кнопку Добавить. З‟явиться вікно для обмежень (рис.

7.12).

155

Рисунок 7.12 – Вікно Добавление ограничения

У полі Ссылка на ячейку вказується адреса комірок з об-меженнями, посередині вибирається умова (>=, <=, =, цел., дво-ичн.), а праворуч у полі Ограничение заноситься значення об-меження.

Кожного разу після занесення обмеження натискається кнопка Добавить, доки всі обмеження не будуть вказані, а по-тім натискається кнопка Ок.

Потім натискається кнопка Выполнить. Через деякий час з‟являється повідомлення, що зображене на рис. 7.13.

Рисунок 7.13– Результати пошуку рішення при знайденому рішенні

Якщо економіко-математична модель створена неправиль-но (обмеження суперечать одне одному або їх недостатньо для визначення оптимального рішення), видається повідомлення, показане на рис. 7.14.

Рисунок 7.14 – Результати пошуку рішення за відсутності рішення

На основі знайденого рішення можна створити Звіт за ре-зультатами обчислень (тип Отчета – Результаты), який міс-титиме інформацію про попередні значення і отримані значен-ня, а потім натиснути Ок.

156

Після виконання пошуку оптимального розв‟язку з‟явиться таблиця з оптимальним значенням (рис. 7.15).

Рисунок 7.15 – Таблиця з оптимальним значенням

Звіт показаний на рис. 7.16

Рисунок 7.16 – Звіт знайденого оптимального розв’язку

3 Робота з макросами

Макроси використовують для автоматизації повторюваних

дій.

Макрос – певна послідовність дій, що записана в модулі

Visual Basic.

157

Макрос може бути використаний за допомогою команд меню, створеної користувачем комбінації клавіш, графічних об‟єктів панелі інструментів Формы або вбудованих рисунків чи компонентів панелі Рисование.

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

Для активізації макросу необхідно активізувати команди

Сервис→Макрос →Начать запись, внаслідок чого на екрані з‟явиться вікно, що зображене на рис. 7.17.

Рисунок 7.17 – Створення макросу

У поле Имя макроса необхідно ввести ім‟я макросу. Пер-шим символом в імені макросу має бути літера, іншими симво-лами можуть бути літери, цифри або знаки підкреслення, в імені макросу не ставлять пробілів (замість них використовують знаки підкреслення). У цьому самому вікні можна створити комбіна-цію клавіш для виконання макросу (наприклад, Ctrl+Q). Для цього в полі Сочетание клавиш вводять літеру (не цифру). У полі Описание можна створити коментар до дій, які виконує макрос.

Макрос активізується кнопкою Ок, внаслідок чого на ек-рані з‟являється панель Остановить запись (рис. 7.18), що міс-

тить дві кнопки (Остановить запись та Относительная ссыл-

158

ка). Першу кнопку активізують після виконання всіх дій макро-су, другу – коли макрос, який створюється, можна застосувати до будь-якого діапазону комірок.

Рисунок 7.18 – Панель Остановить запись

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

Остановить запись.

Для виконання макросу необхідно активізувати команди Сервис→Макрос →Макросы. З переліку вибирають потрібний макрос та активізують його за допомогою кнопки Выполнить

(рис. 7.19).

Рисунок 7.19 – Вікно для виконання та редагування макросу

Макрос активізують також за допомогою кнопки. Для цьо-го відкривають панель інструментів Формы (рис. 7.20), активі-зують кнопку Кнопка, створюють її на робочому листі Excel. Внаслідок цього на екрані з‟являється вікно з переліком макро-сів. Необхідно вибрати потрібний та активізувати його кнопкою Ок. Створеній кнопці для зручності можна присвоїти ім‟я.

Рисунок 7.20 – Панель інструментів Формы

159

Призначення інструментів панелі інструментів Формы по-дані в таблиці 7.1.

Таблиця 7.1 – Призначення інструментів з панелі інструментів

Формы

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

160

чить макрос, вибрати потрібний макрос із переліку і активізува-ти кнопкою Ок.

Параметри створеного макросу можна змінити. Для цього активізують команди Сервис→Макрос→Макросы, внаслідок чого на екрані з‟являється вікно, в якому з переліку макросів обирають потрібний. Після цього активізують кнопку Парамет-ры та вносять потрібні зміні та активізують кнопкою Ок.

ВИСНОВКИ

Отже, електронна таблиця Microsoft Excel має потужні за-соби для розв‟язання прикладних задач: підбір параметра, по-шук рішення, макрокоманди.

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

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

лу за темою «Розв’язок прикладних задач в Excel» студент повинен чітко знати основні застосування Excel при розв‟язанні прикладних задач: підбір параметра, пошук рішень та вміти створювати макрокоманди за допомогою панелі інструментів

Форми.

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

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

2001.– 696с. – С. 408 – 425.

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

дання. – К.: Каравела, 2007.– 640с. – С. 330 – 335.

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

тон», 2002. – 718с. – С. 629 – 630.

161

4. Бондаренко С. Excel 2003. Популярный самоучитель