Державна служба України з надзвичайних ситуацій

Черкаський інститут пожежної безпеки ім. Героїв Чорнобиля

Факультет пожежної безпеки

Кафедра вищої математики та інформаційних технологій

ЗАТВЕРДЖУЮ

Начальник кафедри

ВМ та ІТ

к.ф-м.н., доц.,

полковник с.ц.з.

_______ І.П. Частоколенко

“___”______2014р.

Навчальна дисципліна: Інформатика та комп’ютерна техніка

1-й курс (стаціонар).

ЛЕКЦІЯ

Тема № 1.2. Програмні засоби створення розрахункових таблиць та табличних документів.

Час: 62, з них 16 годин лекцій, 26 годин практичних занять, 20 годин самостійної роботи

Тема лекції №5: Програмні засоби створення розрахункових таблиць та табличних документів.

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

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

Матеріально-методичне забезпечення: Дошка, крейда.

Розробив:

викладач

кафедри ВМ та ІТ А.П. Марченко

Лекція обговорена та схвалена на засіданні кафедри

Протокол №1 від “26” серпня 2014р.

Лекція 5: Оформлення розрахункових таблиць.

1. Функції. Поняття та призначення функцій

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

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

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

Синтаксис функцій

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

Для спрощення роботи з функціями більшість з них була названа від скорочення російськомовних значень цих функцій:

Наприклад:

СУММ – функція, що здійснює додавання елементів. СРЗНАЧ – функція, що визначає середнє значення. Формула починається зі знака «=», за яким вводиться ім’я

функції, дужка, що відкривається, список аргументів, розділених крапкою з комою «;», далі дужка, що закривається.

Наприклад:

=СУММ(В2;С2).

Бувають функції без аргументів, які мають такий синтаксис:

=Ім’я_функції().

Наприклад:

=СЕГОДНЯ(). Загальний вигляд функції:

=ім’я функції (параметр/и).

Існують різні типи аргументів: число, текст, логічне зна-чення (Истина або Лож), формули чи інші функції. В кожному конкретному випадку необхідно використовувати відповідний тип аргументу.

Введення функцій

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

Після введення функції та натискання кнопки Enter автоматично відбуваються обчислення і в комірці відображається результат.

2. Класифікація функцій

Функції, які використовуються найчастіше і дозволяють виконати сумування даних, визначити середнє, максимальне, мінімальне значення, винесені на панель вкладці Главная розділ Редактирование (піктограма ∑).

Для зручності роботи Excel функції розбиті за категоріями:

1. математичні функції;

2. статистичні функції;

3. логічні функції;

4. фінансові функції;

5. функції дати і часу;

6. вкладені функції;

7. функції роботи з базами даних;

8. текстові функції;

9. функції посилання та масивів.

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

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

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

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

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

3. Використання функцій

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

Зауваження 1. В наведених тригонометричних функціях використовується радіан як одиниця вимірювання величин кутів.

Зауваження 2. Функції ЦЕЛОЕ(x) і ОСТАТ(N,D) утворюють загальне правило обчислення частки і остачі від ділення націло числа N на число D: N=D*ЦЕЛОЕ(N/D)+ОСТАТ(N,D).

Зауваження 3. Функція ОКРУГЛ(x, k) виконує заокруглення за звичайним арифметичним правилом заокруглення (якщо цифра, яка відкидається, менша 5, то попередня цифра залишається без змін, у протилежному випадку попередня цифра збільшується на одиницю). Якщо k – додатне, то число x заокруглюється до указаної кількості десяткових розрядів справа від десяткової крапки. Якщо k=0, то число x заокруглюється до найближчого цілого. Якщо k – від’ємне, то число x заокруглюється до указаної кількості десяткових розрядів зліва від десяткової крапки.

Математичний запис	Запис в Excel
Sin x	SIN(x)
Cos x	COS(x)
Tg x	TAN(x)
Arcsin x	ASIN(x)
Arccos x	ACOS(x)
Arctg x	ATAN(x)
Ln x	LN(x)
Lg x	LOG10(x)
Logax	LOG(x, a)
e^x	EXP(x)
|x|	ABS(x)
	КОРЕНЬ(x)
	ПИ()
	x^3
	x^(1/3)
Заокруглення числа x до найближчого меншого цілого	ЦЕЛОЕ(x)
Обчислення остачі від ділення націло числа N на число D	ОСТАТ(N, D)
Заокруглення числа x до заданого числа розрядів k	ОКРУГЛ(x, k)

Приклади використання деяких функцій:

• Формула «=ЦЕЛОЕ(5.7)» дає результат 5, а формула «=ЦЕЛОЕ(-5.7)» дає результат 6.

• Формула «=ЦЕЛОЕ(23/3)» дає частку від ділення націло числа 23 на число 3, тобто 7.

• Формула «=ОСТАТ(23,3)» дає результат 2.

• Якщо x=143.3184, то формула «=ОКРУГЛ(x,2)» дає результат 143.32.

• Якщо x=143.3184, то формула «=ОКРУГЛ(x,0)» дає результат 143.

• Якщо x=143.3184, то формула «=ОКРУГЛ(x,-1)» дає результат 140.

Тут розглянуто приклади використання лише деяких функцій. Описи решти функцій слід шукати в області задач Справка Excel, яка з’являється після натискування клавіши <F1>. В цій області задач треба натиснути на пункт Оглавление і після його появи знайти в ньому розділ Справка по функциям.

Крім арифметичних виразів, важливим компонентом формул є логічні вирази, зокрема, логічні функції. Логічний вираз – це є спільна назва для висловлювання та предиката. Висловлюванням називається твердження, відносно якого відразу можна зробити висновок, вірне воно чи ні. Наприклад, значенням висловлювання «7>5» буде ИСТИНА. Значенням висловлювання «3>5» буде ЛОЖЬ. Висловлювання, яке містить змінні величина, називається предикатом. В залежності від значень змінних предикат може набувати значення ИСТИНА або ЛОЖЬ. Наприклад, результатом порівняння «x>3» буде ЛОЖЬ при x=2 і ИСТИНА – при x=6. Предикат утворюється внаслідок порівняння двох арифметичних виразів, з яких хоча б один містить змінні.

У логічних виразах можуть використовуватись такі операції порівняння: «>» – більше, «>=» – більше або дорівнює, «<» – менше, «<=» – менше або дорівнює, «=» – дорівнює, «<>» – не дорівнює. Треба пам’ятати, що операції порівняння мають нижчий пріоритет, ніж арифметичні операції.

У логічних виразах можуть використовуватись також логічні операції, реалізовані у вигляді логічних функцій: НЕ(x) – заперечення, И(x,y) – логічне множення, ИЛИ(x,y) – логічне додавання.

В арифметичних виразах логічне значення ИСТИНА поводить себе як число 1, а ЛОЖЬ – як число 0. І навпаки – в логічних виразах число 1 поводить себе як ИСТИНА, а число 0 – як ЛОЖЬ. Більше того, замість ИСТИНА можна указувати будь-яке число, відмінне від нуля.

Логічні вирази найчастіше застосовуються як перший аргумент логічної функції ЕСЛИ:

ЕСЛИ(лог_выражение, значение_если_истина, значение_если_ложь).

Ця функція має три аргументи, зміст яких такий: якщо лог_выражение дорівнює ИСТИНА, то значення функції обчислюється як значення другого аргументу значение_если_истина, а якщо лог_выражение дорівнює ЛОЖЬ, то значення функції обчислюється як значення третього аргументу значение_если_ложь. Особливість функції ЕСЛИ полягає в тому, що її тип наперед не визначений і співпадає з типом або другого, або третього свого аргумента.

Приклад 1. Задано число z. Побудувати формулу, яка дає результат z+1, якщо z>1, і дає результат z-1 у протилежному випадку.

Розв’язок: «=ЕСЛИ(z>1,z+1,z-1)».

Приклад 2. Задано число z. Побудувати формулу, результатом якої є повідомлення «Перевищено порогове значення», якщо z>100, і яка дає результат z у протилежному випадку.

Розв’язок: «=ЕСЛИ(z>100, "Перевищено порогове значення",z)».

Приклад 3. Задано число z. Побудувати формулу, яка дає результат z/2, якщо , дає результат 10, якщоz<10, і дає результат 25, якщо z>25.

Розв’язок: «=ЕСЛИ(z<10,10,ЕСЛИ(z>25,25,z/2))».

4. Редагування функцій

Для того щоб змінити аргумент функції, можна: виділити комірку, в якій введено функцію; розмістити курсор миші в рядку формул на імені потрібної нам функції (якщо у формулі використовується декілька функцій); натиснути кнопку для виклику функції з панелі інструментів.

Можна змінювати функцію безпосередньо в рядку формул. При цьому слід пам’ятати, що аргументи функції розділяються символом «;». Слід притримуватися загального синтаксису побудови функцій, описаного в першому розділі теми.

За замовчуванням в Excel встановлено режим автоматичного проведення розрахунків. Якщо в комірку введено формулу, здійснюються відповідні обчислення і відображається результат. Якщо значення однієї комірки визначається через значення іншої (наприклад, у В1 введено формулу =А1+А2), то при внесені змін у комірку А1 чи А2 буде автоматично перераховано значення комірки В1.

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

Автосума

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

1 Виділити всі комірки діапазону, які будуть додаватися та натиснути кнопку ∑ (автосума) на панелі інструментів. У наступній комірці нижче або правіше від виділеного діапазону з’явиться результат сумування.

2 Зробити активною комірку, в яку буде розміщено суму та натиснути ∑. Excel автоматично запропонує діапазон сумування, який буде відображатися у формулі. За необхідності його можна замінити.

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

Наприклад, сума діапазону комірок рядка від А1 до А20: =СУММ(А1:А20).

У цьому прикладі знак «:» означає діапазон з вказівкою першого і останнього членів діапазону.

У цьому випадку функція СУММ використовується з одним аргументом – діапазоном комірок. Однак вона може мати більше одного аргументу (до 30), розділених «;». При цьому кожний аргумент може бути як числом, адресою комірки, так і діапазоном комірок. Також можна додавати і діапазони комірок, не розміщені поруч, тобто окремі. Для виділення таких діапазонів необхідно утримувати клавішу Ctrl і мишкою виділяти діапазони.

Функції дати і часу

Для роботи зі значенням типу дата та час в Excel викорис-товують функції категорії ДАТА і ВРЕМЯ. Розглянемо деякі із них.

Функція ДАТА

Функція ДАТА повертає значення дати. Загальний вигляд функції

ДАТА(рік;місяць;день).

Функція ДАТА(2000;2;1) залежно від установленого формату дати повертає значення 01.02.00.

Функція ДЕНЬ

Функція ДЕНЬ повертає день дати в числовому форматі. Наприклад, у комірці F2 вміщена дата 28.10.2003, тоді значення функції ДЕНЬ(F2) дорівнює 28.

Функція ДЕНЬНЕД

Функція ДЕНЬНЕД визначає день тижня, на який припадає дата, визначена як аргумент. Синтаксис функції:

ДЕНЬНЕД(дата;тип). При цьому аргумент тип визначає порядок розрахунку і може мати значення:

1 (за замовчуванням) – число від 1 (неділя) до 7; 2 – число від 1 (понеділок) до 7; 3 – число від 0 (неділя) до 6.

Функція =ДЕНЬНЕД(28.10.2003) повертає значення 3, а функція =ДЕНЬНЕД(«23.10.2003»;2) – значення 2.

Функція СЕГОДНЯ

Функція СЕГОДНЯ має загальний вигляд СЕГОДНЯ() і повертає значення поточної дати.

Функція ЧАС

Функція ЧАС повертає значення часу в налаштованому часовому форматі.

Синтаксис запису: ЧАС(години;хвилини;секунди).

Функція ТДАТ

Функція ТДАТ повертає поточну дату та час. Синтаксис функції: ТДАТ().

Функція МЕСЯЦ

Функція МЕСЯЦ використовується для визначення місяця. Синтаксис функції: МЕСЯЦ(дата в числовому форматі). Наприклад, МЕСЯЦ(10.01.2007) повертає значення 1.

Функція ДНЕЙ360

Функція ДНЕЙ360 визначає кількість днів між двома датами, яку вона вираховує на основі 360-денного року.

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

5. Використання надбудов

Розглянемо приклад. Деяка установа надає послуги виду 1 та виду 2. Кожна послуга виду 1 дає прибуток 60 грн., а на її надання витрачається 1 одиниця ресурсу 1, 0.5 одиниць ресурсу 2 і 1 одна одиниця ресурсу 3. Кожна послуга виду 2 дає прибуток 160 грн., а на її надання витрачається 2 одиниці ресурсу 1, 0.4 одиниці ресурсу 2 і 4 одиниці ресурсу 3. Ресурси установи обмежені: щотижня вона може отримувати від своїх постачальників 130 одиниць ресурсу 1, 50 одиниць ресурсу 2 і 220 одиниць ресурсу 3. Наведені дані зафіксуємо у таблиці.

Послуги	Ресурс 1	Ресурс 2	Ресурс 3	Прибуток
Вид 1	1	0.5	1	60
Вид 2	2	0.4	4	160
Ресурси	130	50	220

Треба визначити, в якій кількості спланувати надання послуг виду 1 і виду 2, щоб прибуток був максимальним.

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

Сформулюємо задачу математично. Позначимо через ізаплановану до надання кількість послуг 1 і послуг 2 відповідно. Обмеженість ресурсів фірми означає, що мають задовольнятись такі нерівності:. Крім того, за змістом задачі її змінні мають бути невід’ємними, тобто:і. Вони також мають бути цілочисельними. Прибуток від запланованих до виробництва стільців та крісел визначається за формулою. Отже, оптимальний план фірми, тобто числаімають бути такими, щоб задовольнялись всі наведені нерівності, а прибуток F досягав максимального значення.

Один із допустимих планів цієї задачі такий: і. При цьому:, тобто ресурсу 3 вистачає із запасом, а ресурси 1 і 2 використовуються повністю. Прибуток при цьому складаєгрн. Указаний план є допустимим, але він не є оптимальним. Дійсно, для іншого плануіотримуємо:. При цьому ресурсу 2 вистачає із запасом, ресурси 1 і 3 використовуються повністю, а прибуток складаєгрн., що суттєво краще у порівнянні з попереднім.

Отже, розв’язок задачі розподілу ресурсів має багатоваріантний характер.

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

Порядок розв’язування задачі лінійного програмування:

• Установити надбудову Поиск решения. Для цього виконати команду Файл-Параметри-Надстройки-.... У списку Управление обрати Надстройки Excel. Внаслідок цього з’являється вікно Надстройки. В цьому вікні у прокручуваному списку Список надстроек: слід установити прапорець на пункті Поиск решения і натиснути кнопку OK.

• В робочому листі Excel створити наступну форму:

	A	B
1	Змінні:
2	x1 =
3	x2 =
4
5	Максимальне значення:
6
7	Обмеження:
8	№1:
9	№2:
10	№3:

• В комірки B8, B9 і B10 внести такі формули: “=B2+2*B3”, “=0.5*B2+0.4*B3”, “=B2+4*B3”.

• В комірку B5 внести формулу цільової функції: «=60*B2+160*B3».

• Звернутися до надбудови Поиск решения з метою розв’язування задачі. Для цього виконати команду вкладка Данные-група Анализ -Поиск решения.... Після цього на екрані з’являється вікно Поиск решения, в яке здійснюється внесення задачі лінійного програмування.

• В полі Установить целевую ячеку: надрукувати $B$5.

• Установити відмітку на пункті Равной: Максимальному значению.

• В полі Изменяя ячейки: надрукувати $B$2:$B$3.

• Ввести перше обмеження. Для цього натиснути кнопку Добавить. У діалоговому вікні Добавление ограничения в поле Ссылка на ячейку: ввести $B$8, в полі Ограничение: вибрати значок “<=” і надрукувати значення 130. Натиснути кнопку ОК.

• Аналогічно ввести друге і третє обмеження.

• Ввести умови невід’ємності змінних. Для цього натиснути кнопку Добавить. У діалоговому вікні Добавление ограничения в поле Ссылка на ячейку: ввести $B$2:$B$3, в полі Ограничение: вибрати значок «>=» і надрукувати значення 0. Натиснути кнопку OK.

• Ввести умови цілочисельності змінних. Для цього натиснути кнопку Добавить. У діалоговому вікні Добавление ограничения в поле Ссылка на ячейку: ввести $B$2:$B$3, в полі Ограничение: вибрати пункт цел. Натиснути кнопку OK.

• Установити відмітку на пункті Поиск решения линейных задач симплекс-методом в пункте Выбор метода решения і натиснути кнопку OK.

• Задачу ЛП повністю підготовлено. Натиснути у вікні Поиск решения кнопку Найти решение.

• З’являється вікно Результаты поиска решения, в якому повідомляється, що Решение найдено. Відмітити пункт Сохранить найденное решение і натиснути кнопку OK. На листі електронної таблиці бачимо оптимальний план.