Посібник_Лутай АП

This document is created with trial version of Document2PDF Pilot 2.16.100.

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

Рисунок 4.5.1 - Діалог Мастер Функций

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

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

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

Рисунок 4.5.2 - Палітра формул для функції СУММ

191

This document is created with trial version of Document2PDF Pilot 2.16.100.

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

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

Автододавання

Припустимо, вам потрібно скласти числа, записані до комірок стовпця А (рисунок 4.5.3,а). Опишемо два найпростіші способи додавання.

∙ Додавання виділених комірок. Виділіть комірки, зміст яких потрібно додати (рис.4.5.3,б), а потім натисніть кнопку (Автосума), розташованій на панелі інструментів. Значення суми буде вміщено до комірки під виділеним стовпцем. Аналогічно можна додати виділені комірки у рядку – результат буде записаний до найближчої комірки праворуч від виділеного фрагмента рядка.

∙ Додавання з автоматичним виділенням доданків. Щоб додати комір-

ки, вам не обов’язково їх виділяти – програма може це зробити за вас. Вам досить лише активізувати комірку, до якої ви хочете помістити суму і, натисн ути кнопку Автосума. Програма занесе до поточної комірки формули (у даному випадку СУММ(А1:А5) і виділить рухомим пунктиром ті комірки, що будуть підсумовані (рисунок 4.5.3,в). Натисніть Enter, і ви отримаєте результат у поточній комірці.

а	б	в

Рисунок 4.5.3 - Прийоми знаходження суми за допомогою кнопки Автосума: а – первинні комірки-доданки; б – додавання виділених комірок; в – додавання без виділення

Додавання за допомогою функції СУММ

Виділіть комірку, до якої буде занесено суму комірок, розташованих вище або лівіше. Потім натисніть кнопку Вставка функции у рядку формул і в категорії Математические оберіть функцію СУММ. У діалозі Палитра формул (рисунок 4.5.2) буде зазначено діапазон комірок, що додаються, і виведена сума. Щоб помістити суму до цільової комірки натисніть Ok.

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

Виведення результату обчислень у рядку стану

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

192

Рисунок 4.5.4. Рядок стану засобу Автообчислення і контекстного меню

This document is created with trial version of Document2PDF Pilot 2.16.100.

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

Якщо пі сля виділення комірок натиснути праву кнопку миші на рядку стану, то з’явиться контекстне меню (рисунок 4.5.4), з якого ви зможете вибрати будь - яку з доступних операцій (знаходження середнього, м і- німального, макси -мального значень тощо). У рядку стану післ я цього з’явиться запис Минимум=… або Максимум=… тощо.

Діагностування помилок у формулах

Реакцією на неправильне введення формул є повідомлення Excel про помилки, тобто значення в комірках, що починаються зі знака #. Наведемо список м о- жливих значень помилок.

∙##### - найчастіше з’являється у користувачів-початківців і означає, що ширина комірки недостатня для розміщення у ній числа, дати або часу. Змініть ширину комірки або змініть формат числа.

∙#ИМЯ? – неможливість розпізнати ім’я, що використовується. Ця помилка виникає, коли неправильно зазначене ім’я об’єкта або є посилання на видал е-

не ім’я, коли неправильно записана функція, коли при запису адрес замість лат и- ни використана кирилиця тощо.

∙#ЗНАЧ! – спроба некоректного використання функції. Поширеною п о- милкою є невідповідність даних встановленому формату, наприклад, замість чи с- ла або дати в аргументі стоїть текст.

∙#ЧИСЛО! – з’являється при неправильному запису або використанні чи-

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

∙#ССЫЛКА! – означає неправильне вживання посилань, які є у формулі. Можливо, формула містить посилання на комірку, що уже видалена або посила н- ня на комірку, до якої скопійовано вміст інших комірок.

∙#ДЕЛ/0! – спроба ділення на нуль. Така ситуація найчастіше виникає при використанні як дільника посилання на порожню комірку або комірку, яка містить нульове значення.

∙#ПУСТО! – значення помилки, що з’являєтьс я при заданні в посиланні порожньої множини комірок. Можливо, що ви помилилися у визначенні перетину двох діапазонів. Наприклад, поставили у вираз типу (А3:А6 С4: F7) замість коми пробіл.

∙#Н/Д – скорочення від терміна «невизначені дані». Це значення помилки

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

193

This document is created with trial version of Document2PDF Pilot 2.16.100.

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

Використання функцій для обчислення значень Логічні функції в Microsoft Excel.

І (И)- Повертає значення ІСТИНА, якщо всі аргументи мають значення ІСТИНА. НЕПРАВДА (ЛОЖЬ )-Повертає логічне значення НЕПРАВДА.

ЯКЩО (ЕСЛИ)-Визначає виконувану логічну перевірку.

НІ (НЕ) - Змінює на протилежне логічне значення свого аргументу.

АБО (ИЛИ)-Повертає ІСТИНА, якщо хоча б один аргумент має значення ІСТИНА.

ІСТИНА - Повертає логічне значення ІСТИНА.

Розглянемо функції:

ЯКЩО- Повертає одне значення, якщо задана умова при обчисленні дає значення ІСТИНА, і інше значення, якщо НЕПРАВДА.

Функція ЯКЩО використовується для умовної перевірки значень і формул.

Синтаксис ЯКЩО (лог_вираження;значення_якщо_істина;значення_якщо_неправда)

Лог_вираження - це будь-яке значення або вираження, що при обчисленні дає значення ІСТИНА або НЕПРАВДА.

Значення_якщо_істина - це значення, що повертається, якщо лог_вираження

має значення ІСТИНА. Якщо лог_вираження має значення ІСТИНА і значе	н-
ня_якщо_істина опущена, то повертається значення ІСТИНА. Значе	н-
ня_якщо_істина може бути іншою формулою.
Значення_якщо_неправда-це значен ня, що повертається, якщо
лог_вираження має значення НЕПРАВДА. Якщо лог_вираження має значення
НЕПРАВДА і значення_якщо_неправда опущена, то повертається значення Н	Е-
ПРАВДА. Значення_якщо_неправда може бути іншою формулою.

∙Функція ЯКЩО завжди повертає значе ння, що повертається обчисленим аргументом значення_якщо_істина і значення_якщо_неправда.

∙Якщо який-небудь аргумент функції ЯКЩО є масивом, то при виконанні

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

І- Повертає значення ІСТИНА, якщо всі аргументи мають значення ІСТ И- НА; повертає значення НЕПРАВДА, якщо хоча б один аргумент має значення НЕПРАВДА

Синтаксис

І(логічне_значення1; логічне_значення2; ...)

Логічне_значення1, логічне_значення2, ... - це від 1 до 30 умов, що перевіряют ь- ся, що можуть мати значення або ІСТИНА, або НЕПРАВДА.

194

This document is created with trial version of Document2PDF Pilot 2.16.100.

∙ Аргументи повинні бути логічними значеннями, масивами або посила н- нями, що містять логічні значення.

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

∙ Якщо зазначений інтервал не містить логічних значень, то И повертає значення помилки #ЗНАЧ!.

НЕПРАВДА - Повертає логічне значення НЕПРАВДА.

Синтаксис НЕПРАВДА( )

Можна безпосередньо ввести слово НЕПРАВДА в робочий лист або у формулу, і Microsoft Excel буде інтерпретувати його як логічне значення НЕПРАВДА.

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

Синтаксис
НІ(логічне_значення)
Логічне_значення - це значення або вираження, що при обчисленні дає І	С-
ТИНА або НЕПРАВДА. Якщо логічне_значення має значення НЕПРАВДА,	то

функція НІ повертає значення ІСТИНА; Якщо логічне_значення має значення І С- ТИНА, то функція НІ повертає значення НЕПРАВДА.

АБО - Повертає ІСТИНА, якщо хоча б один з аргументів має значення ІСТИНА; повертає НЕПРАВДА, якщо всі аргументи мають значення НЕПРАВДА.

Синтаксис АБО(логічне_значення1;логічне_значення2; ...)

Логічне_значення1, логічне_значення2, ... - це від 1 до 30 умов, що перев і- ряються, що можуть мати значення або ІСТИНА, або НЕПРАВДА.

∙Аргументи повинні бути виражені логічними значеннями, т акими як ІСТИНА або НЕПРАВДА, масивами або посиланнями, що містять логічні значення.

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

∙Якщо заданий інтервал не містить логічних значень, то функція АБО повертає значення помилки #ЗНАЧ! .

ІСТИНА - Повертає логічне значення ІСТИНА.

Синтаксис ІСТИНА( )

195

This document is created with trial version of Document2PDF Pilot 2.16.100.

Завдання для аудиторної контрольної роботи за темою “Microsoft Excelвикористання стандартних функцій” Завдання

1.Завантажити табличний процесор Microsoft Excel.

2.Ввести прізвище студента, дату роботи.

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

4.Підготувати документ до друку у режимі попереднього перегляду.

5.Зберегти робочу книгу у файлі (ім’я файла - прізвище студента).

6.Завершити роботу з табличним процесором Microsoft Excel.

Приклад виконання практичного завдання

Створити таблицю

Визначити суму торговельної знижки, яка дорівнює 10% від вартості товарів для товарів групи «Холод. тех ніка», 8% для холод. оборуд., 9% для побутової техніки.

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

№	Активна	Операція		Спосіб виконання / вміст
	чарунка
1		У комірках А1:D10
		створити таблицю
2	D3	Ввести формулу		з ви користанням майстра функції
				ввести формулу:=ЕСЛИ(A3="Холод.
				техніка";C3*0,1;ЕСЛИ(A3="Холод.
				оборудов.";C3*0,08;C3*0,09))
3	D3	Скопіювати формулу
		у комірки D4: D9
4	D10	Визначити підсумок		Клацнути на інструменті Автосумма,
		для торговельної зни-		натиснути клавішу Enter.
		жки
			196

This document is created with trial version of Document2PDF Pilot 2.16.100.

Контрольні запитання:

1.Які правила запису функції в електронних таблицях Excel?

2.Що таке вкладена функція?

3.Як ввести функцію до формули за допомогою майстра функцій?

4.Чи можна записати функцію вручну?

5.Назвіть найпростіші способи обчислення суми комірок.

6.Значення яких функцій можна вивести в рядок стану?

7.На які категорії поділяються вбудовані функції Excel?

8.Що таке значення помилки? Які бувають значення помилок?

9.Табличний процесор Microsoft Excel - логічні функції.

4. 6. Підтримка баз даних в ЕТ Компоненти списків

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

У вигляді списку побудований, наприклад, телефонний довідник, у якому записом є кожний окремий рядок, що відповідає конкретному об’єкту – абоненту, а полями є стовпці ПІБ, Адреса, Телефон.

В Excel записи формуються з рядків електронної таблиці, а поля – зі стовпців. Оскільки списки розміщаються на робочому аркуші Excel, структура аркуша накладає природні обмеження на майбутні списки: число полів не може перев и- щувати 256, а число записів – 65536 мінус один. Остання цифра обу мовлена тим, що повне число рядків на аркуші дорівнює 2 16 – 1 (=65535), і перший рядок зв и- чайно використовується для введення імен полів.

Створення списку

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

∙П.м. Сервис → к. Параметры → вкладка Списки → у вікно Элементы списка увести список, утримуючий найменування підприємств → кнопка Добавить → Ok;

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

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

Пошук даних Пошук значень і формул

197

This document is created with trial version of Document2PDF Pilot 2.16.100.

У процедурі пошуку за допомогою форми даних пошук здійснюється за з а- даними полями (стовпцями). Однак якщо вас цікавить пошук по всьому робочому аркуші якогось кон кретного значення або формули, то краще скористатися пр о- цедурою пошуку з меню Правка. Оберіть команду Правка→Найти (або натисніть Ctrl+F). У діалозі Найти (рисунок 4.6.1) у полі Найти: наберіть фрагмент змісту комірки. Потім клацніть по кнопці Найти далее. Якщо пошук виявився вдалим, буде активована комірка, що містить зазначений вами фрагмент. Закрийте діалог пошуку клацанням по кнопці Закрыть.

За замовчуванням пошук здійснюється по рядках. Ви можете змінити схему пошуку, задавши в діалозі Найти пошук по стовпцях. Для цього натисніть кнопку Параметры>>. У цьому самому діалозі можна уточнити, якого роду дані ви шук а- єте: значення, формули чи примітки.

Рисунок 4.6.1 - Пошук за допомогою діалогу Найти

Контрольні запитання:

1.Що таке список в електронній таблиці?

2.З яких елементів складаються списки?

3.Як виконати пошук даних по всьому робочому аркуші?

4.7. Упорядкування і фільтрація даних Упорядкування даних

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

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

Дані зазвичай сортуються за алфавітом, за числов им значенням або за д а- тою. Опишемо порядок сортування, прийнятий у Excel. Числа сортуються у п о- рядку зростання: від найменшого від’ємного до найбільшого додатного числа.

При сортуванні тексту порівн юється зміст комірок за символами зліва направо. Наприклад, слово «зоологія» буде розташоване після слова «золото». Регістр си м- волів при сортуванні не враховується.

У тексті, крім літер, можуть використовуватися цифри й інші символи. Упорядкована послідовність усіх можливих символів, що прийнята в Excel при сортуванні, така:

198

This document is created with trial version of Document2PDF Pilot 2.16.100.

(пробіл) ! « # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ `` { | } ~ + < = > 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Є Ж З И І

(укр.) Ї Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

Сортування за одним параметром

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

- сортування за зростанням або

- сортування за спаданням.

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

Сортування здійснюється по крайньому лівому полю (у даному разі по ст о- впцю А).

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

фрагменти рядків. Якщо такого результату заздалегідь не передбачити, то ваші
таблиці перетворяться в непотрібний набір даних.
Якщо сортування виявилося невдалим, можете його відразу скасувати, н	а-
тиснувши кнопку Отменить на панелі інструментів чи натиснувши Ctrl+Z.
Сортування за кількома параметрами
Якщо в списку є декілька стовпців, ви можете здійснити послідовне сорт	у-
вання. Сортування найзручніше задавати в такій послідовності, що відповідає
кращому сприйняттю інформації, поданої в таблиці.
Фільтрація даних. Автофільтр
Однією з найпоширеніших операцій над списками є добір записів, або, і	н-

акше, фільтрація.

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

Результатом фільтрації є створення списку з відфіль трованих записів. У Excel передбачені два режими фільтрації: Автофильтр і Расширенный фильтр.

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

Список кнопки відкривають позиції Все, (Первые 10…), (Условие), потім за алфавітом розташовуються елеме нти стовпця. Прокрутивши список, ви можете обрати необхідний вам критерій. Одразу після цього на екрані залишат ься лише відфільтровані записи, а інші будуть сховані. Заголовки відфільтрованих рядків зафарбовано в синій колір. Ви можете продовжити фільтрацію, вказуючи в спи с- ках інших стовпців необхідні елементи, а можете повернутися до початкового в и- гляду таблиці, відмітивши в списку пункт Все. Для повного скасування процедури фільтрації потрібно ввійти в меню Данные→Фильтр і зняти прапорець з команди Автофильтр.

199

This document is created with trial version of Document2PDF Pilot 2.16.100.

Автофільтр користувача

Зауважимо, що у списку автофільтра є позиція Условие. Вона дозволяє з а- дати точні критерії для фільтрац ії. Пользовательский автофильтр задайте нео б- хідну умову. Взагалі в даному діалозі можливе задання двох умов, записаних ч е- рез оператори порівняння равно, не равно, больше… й об’єднаних логічним И або ИЛИ.

Для текстових полів, окрім сказаних умов, можливі к ритерії вибору: Начинается (не начинается) із заданого тексту, Заканчивается (не заканчивается) заданим текстом і Содержит (не содержит) заданий текст.

Розширений фільтр

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

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

∙Активізуйте будь -яку комірку первинної таблиці і виконайте команду Данные→Фильтр→Расширенный фильтр.

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

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

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

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

∙ Закрийте діалог Расширенный фильтр натиснувши кнопку Ok. Якщо ви правильно зазначили діапазон і критерії фільтрації, то на аркуші з’явиться табл и-

ця-результат.
Скасувати дію розширено	го фільтра можна командою	Дан-

ные→Фильтр→Показать все. При цьому ви повернетеся до стану таблиці до фільтрації, Але таблиця-критерій буде присутня.

Контрольні запитання:

1. Яким шляхом упорядковуються дані в електронних таблицях?

200