Практические_расчеты_на_EXСEL

КАФЕДРА ЕКОНОМІЧНОЇ КІБЕРНЕТИКИ ТА ІНФОРМАЦІЙНИХ ТЕХНОЛОГІЙ

МЕТОДИЧНІ ВКАЗІВКИ

до виконання лабораторних робіт

за темою "Практичні розрахунки на Excel"

зкурсів "Інформатика та КТ"

і"Економічна інформатика"

(для студентів всіх спеціальностей I курсу всіх форм навчання)

Рекомендовано на засіданні кафедри ЕК та ІТ

Протокол № 1 від 31.08.09

Затверджено на засіданні методради ДонДТУ

Протокол № 1 від 02.10.09

Алчевськ

ДонДТУ

2009

ББК У.в6

Методичні вказівки до виконання лабораторних робіт за темою "Практичні розрахунки на Excel" з курсів "Інформатика та КТ" і "Економічна інформатика" (для студ. всіх спец. I курсу всіх форм навч.) / Укл.: Н.М. Лепіло. – Алчевськ: ДонДТУ, 2009. – 48 c.

Наведено основні теоретичні відомості та завдання до лабораторних робіт за темою "Практичні розрахунки на Excel". Розглянуто організацію обчислень, побудову графіків, рішення рівнянь і систем лінійних алгебраїчних рівнянь, використання основних статистичних функцій і засобу Описова статистика, засобу Пошук рішення, прогнозування значень параметру у середовищі MS Excel.

Укладач	Н.М. Лепіло, доц.
Відповідальний редактор	С.І. Зайцев, проф.
Відповідальний за випуск	Л.А. Мотченко, інж.

	ЗМІСТ
1 ОРГАНІЗАЦІЯ ОБЧИСЛЕНЬ І ПОБУДОВА ГРАФІКІВ ...................		4
1.1	Загальні принципи організації обчислень......................................	4
1.2	Обчислення значень функції при зміні аргументу із заданим
	кроком і побудова графіка залежності функції від аргументу......	8
1.3	Побудова графіків при нерівномірному кроці аргументу ...........	10
1.4	Організація обчислень для алгоритмів розгалуженої структури. 11
2 РІШЕННЯ РІВНЯНЬ І СИСТЕМ ЛІНІЙНИХ АЛГЕБРАЇЧНИХ
РІВНЯНЬ ............................................................................................		13
2.1	Рішення рівнянь............................................................................	13
2.2	Функції для роботи з матрицями..................................................	15
2.3	Рішення систем лінійних алгебраїчних рівнянь матричним
	методом.........................................................................................	15
3 ВИКОРИСТАННЯ ЗАСОБУ ПОИСК РЕШЕНИЯ............................		17
3.1	Призначення і принцип використання засобу Поиск решения ...	17
3.2	Пошук найбільшого і найменшого значення функції при
	наявності обмежень ......................................................................	20
3.3	Транспортна задача ......................................................................	22
4 СТАТИСТИЧНИЙ АНАЛІЗ ДАНИХ В EXCEL ...............................		25
4.1	Основні статистичні функції........................................................	25
4.2	Призначення і основні можливості засобу Пакет анализа...........	27
4.2 Використання засобу Описательная статистика............................		28
5 ПОБУДОВА ЛІНІЇ ТРЕНДА І ПРОГНОЗУВАННЯ ЗНАЧЕНЬ
ПАРАМЕТРУ В EXCEL ....................................................................		29
5.1	Побудова лінії тренда і її використання для цілей
	прогнозування...............................................................................	29
5.2	Функції прогнозування.................................................................	30
ЗАВДАННЯ ДЛЯ ЛАБОРАТОРНИХ РОБІТ.......................................		32
СПИСОК РЕКОМЕНДОВАНОЇ ЛІТЕРАТУРИ...................................		48

3

1 ОРГАНІЗАЦІЯ ОБЧИСЛЕНЬ

ІПОБУДОВА ГРАФІКІВ

1.1Загальні принципи організації обчислень

Табличний процесор Excel здатний виконувати над даними математичні, логічні, статистичні та інші операції. Послідовність дій записується в комірки робочого листа у вигляді формул. Уведення формули починається з символу "дорівнює" (=) і закінчується натисканням Enter. Формула може включати числові константи (постійні величини), посилання на адреси комірок або діапазони комірок, знаки арифметичних операцій, круглі дужки для зміни пріоритетності операцій, функції, а також текст, укладений у лапки. В Excel використовуються також логічні операції порівняння, результатом виконання яких є логічне значення (Истина або Ложь).

Після введення формули на екрані монітора з'явиться результат розрахунку. Саму формулу можна побачити і відповідно відредагувати в рядку формул. Якщо необхідно для перевірки чи друку відобразити в комірках таблиці не результати розрахунку, а формули, то слід подати команду Сервис → Параметры і у вкладці Вид включити параметр

Формулы.

Адреси комірок можна вводити з клавіатури, користуючись латинським алфавітом, але краще задавати клацанням миші по комірці.

У формулах застосовуються три види посилань на адреси комі-

рок:

−відносна адресація, коли адреси комірок коректуються при копіюванні (А1, Е8);

−абсолютна адресація, коли адреси комірок не змінюються при копіюванні ($А$1, $Е$8);

−мішана адресація, коли при копіюванні фіксується адреса стовпця ($А1, $Е8) або рядка (А$1, Е$8).

Символ $ можна ввести з клавіатури або одержати при натисненні на клавішу F4 (перше натиснення – абсолютна адресація, друге – фіксація рядка, третє – фіксація стовпця, четверте – відносна адресація і т. д.).

4

Діапазони комірок включають частину стовпця, частину рядка або блок сусідніх комірок, що задається адресами лівої верхньої та правої нижньої комірок (наприклад, C1:E12). Для завдання діапазону комірок використовується двокрапка (:). Для об’єднання кількох посилань на клітинки в одне використовується символ ";".

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

−виділити комірку або діапазон комірок;

−подати команду Вставка → Имя → Присвоить або встановити курсор безпосередньо у поле імені (ліву клітинку рядка формул);

−у діалоговому вікні Присвоение имени ввести ім'я діапазону. До імен комірок і діапазонів комірок висуваються такі вимоги:

−ім'я повинне починатися з букви або символу підкреслення "_";

−в імені можуть використовуватися тільки букви, цифри й сим-

вол "_";

−імена мають бути без пропусків (для відокремлення слів використовуються символи підкреслення "_", крапка "." або велика літера;

−різні імена не повинні відрізнятись лише регістром, оскільки Excel не розрізняє великі та малі літери;

−як імена можуть використовуватися одиночні букви за винятком R і C;

−не можна допускати, щоб імена збігалися з іменами клітинок

(наприклад, A6, x78).

Ім'я діапазону комірок дійсно для всіх листків робочої книги. Змінити імена або діапазони комірок, яким привласнені імена,

можна у вікні Присвоение имени. Імена, що виділені, можна видаляти за допомогою кнопки Удалить, заміняти (видалити старі і ввести нові імена) або змінювати для них комірки або діапазони в полі Формула.

Користувач може додавати імена у формули та функції. При вве-

5

денні нових формул на місці посилання на комірки необхідно використовувати команду Вставка → Имя → Вставить → виділити потрібне ім'я → Ок. Для заміни посилань на комірки на імена у вже існуючих формулах варто виконати команду Вставка → Имя → Применить → виділити при натиснутій клавіші Shift потрібні імена у списку імен → Ок.

Знаки арифметичних операцій й операцій порівняння приведено в таблиці 1.1.

Таблиця 1.1 – Арифметичні операції й операції порівняння

	Арифметичні операції			Операції порівняння
+		додавання	=	дорівнює
-		віднімання	<>	не дорівнює
*		множення	>	більше
/		ділення	<	менше
^		піднесення до степеня	>=	більше або дорівнює
%		відсоток (число множить-	<=	менше або дорівнює
		ся на 100)

Коли в одній формулі поєднуються кілька операцій, вони виконуються в наступному порядку:

−відсоток (%);

−піднесення до степеня (^);

−множення й ділення (*,/);

−додавання й віднімання (+,-);

−операції порівняння (=, <>, >, <, >=, <=).

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

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

аргументів. Аргументи завжди містяться у круглих дужках. Є функції, що не потребують аргументів (наприклад, ПИ(), СЛЧИС() тощо). Навіть якщо функція не має аргументів, круглі дужки повинні бути.

6

Таблиця 1.2 – Перелік основних математичних функцій

	Математичне			Ім'я функції	Призначення функції
	позначення			в Excel
	|x|			ABS	визначає модуль числа
	ex			EXP	визначає експоненту заданого числа
	lnx			LN	визначає натуральний логарифм числа
	logax			LOG	визначає логарифм числа за заданою
					основою a
	lgx			LOG10	визначає десятковий логарифм числа
				КОРЕНЬ	визначає додатне значення квадратного
		x
					кореня
	xa			СТЕПЕНЬ	визначає число в заданому степені
				СЛЧИС	повертає випадкове число між 0 та 1
	arccosx			ACOS	визначає арккосинус числа
	arcsinx			ASIN	визначає арксинус числа
	arctgx			ATAN	визначає арктангенс числа
	cosx			COS	визначає косинус числа
	sinx			SIN	визначає синус числа
	tgx			TAN	визначає тангенс числа
				ГРАДУСЫ	перетворює радіани на градуси
	p			ПИ	задає число p
				РАДИАНЫ	перетворює градуси на радіани
				ЦЕЛОЕ	округлює число до найближчого мен-
					шого цілого
	П			ПРОИЗВЕД	визначає добуток чисел
	å			СУММ	визначає суму чисел
				СУММПРОИЗВ	визначає суму добутків відповідних
					елементів масивів
				СЧЕТ	визначає кількість числових значень
					діапазону комірок
				СЧЕТЕСЛИ	визначає кількість клітинок інтервалу,
					що задовольняє задану умову

7

Аргументи функцій можна поділити на обов'язкові і необов'язкові. Excel самостійно приймає певне значення (як правило, нуль) для необов'язкових аргументів. Аргументами можуть бути константи, арифметичні вирази, посилання на комірки або діапазони комірок, імена діапазонів, функції. Якщо функція має декілька аргументів, вони розділяються символом ";".

Для обчислення середнього значення використовується функція СРЗНАЧ (категорія Статистические).

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

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

1.2 Обчислення значень функції при зміні аргументу із заданим кроком і побудова графіка залежності функції від аргументу

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

l = ax3 ; m = a + x , якщо a = 1,86; 0,4 ≤ x ≤ 2,6; x = 0,3 , тобто x a + x

змінюється від 0,4 до 2,6 з кроком 0,3.

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

В комірку A2 заносимо 0,4 і, виділив цю комірку, даємо коман-

ду Правка → Заполнить → Прогрессия. У вікні Прогрессия, що з'явилося, задаємо параметри, як показано на рисунку 1.1. Значення x заповняться автоматично.

8

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

Потім в комірку D2 вводимо 1,86 (значення a), а в комірку B2 уводимо формулу =$D$2*A2^3/($D$2+A2). При цьому для завдання абсолютної адреси після введення D2 натискаємо клавішу F4. Далі в комірку C2 уводимо формулу =КОРЕНЬ($D$2+A2) і поширюємо уведені формули на комірки відповідних стовпців. Отриманий вид робочого листа Excel показаний на рисунку 1.2.

Рисунок 1.2 – Вид робочого листа Excel

Для побудови графіка слід виділити комірки зі значеннями аргументу і функцій, включаючи заголовки (діапазон A1:C9), причому у аргументу не повинно бути заголовка і він повинен розміщатися рані-

ше функцій, клацнути по кнопці (Мастер диаграмм), вибрати

График і вид График с маркерами, помечающими точки данных

(крок 1), клацнути по Далее, перейти до кроку 3 і задати параметри. Для цього зайти в Заголовки, задати Название диаграммы і імена осей; зайти в Линии сетки, установити прапорці Основные линии. Потім виконати подвійне клацання, знаходячись на осі категорій, і у вікні, що з'явилося, скинути прапорець Пересечение с осью Y (значе-

9

ний) между категориями. Щоб усунути заливання, клацнути в облас-

ті заливання, клацнути по кнопці (Цвет заливки), вибрати Нет заливки. Вид графіків, що одержані, показаний на рисунку 1.3.

Графики l=f(x), m=f(x)

l,m

	8
	6
	4	l
		m

2
0
0,4	0,7	1	1,3	1,6	1,9	2,2	2,5

x

Рисунок 1.3 – Вид графіків функцій

Перед друком листа Excel, що містить графік або іншу діаграму, треба переконатися, що діаграма не виділена (інакше буде роздрукована тільки діаграма на повну сторінку).

Якщо розміщення аргументу й функцій на листі не задовольняє перерахованим вище умовам, для побудови графіка слід виділити комірки зі значеннями функцій, викликати Мастер диаграмм, виконати крок 1, перейти до кроку 2, перейти в Ряд, перейти в поле Подписи оси x, виділити комірки зі значеннями аргументу, клацнути по Далее, задати параметри.

1.3 Побудова графіків при нерівномірному кроці аргументу

Тип діаграми График дозволяє будувати графік тільки тоді, коли крок зміни аргументу рівномірний. Якщо крок зміни аргументу нерівномірний, для побудови графіку слід використовувати тип діаграми

Точечная, вибирати вид Точечная диаграмма, на которой значения соединены отрезками або Точечная диаграмма со значениями, соединенными сглаживающими линиями.

10