Технології опрацювання числових даних у середовищі табличного процесора

Абсолютні, відносні й мішані посилання

ПОВТОРЕННЯ

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

Нагадаємо, що робоче поле табличного процесора MS Excel представлене у вигляді таблиці називають робочим аркушем. Клітинки таблиці мають власні адреси, що складаються з назв стовпців і номерів рядків. Кожна клітинка може містити число, текст або формулу.

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

ПОСИЛАННЯ НА АДРЕСИ КЛІТИНОК

 

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

Відомо ціпи товарів у гривнях. Потрібно перевести гривні в холіиги (рис. 7.1).

Зрозуміло, що для виконання поставленого завдання, в клітинку С4 треба записати формулу =В4/В1.

РОЗДІЛ 7. Технології опрацювання числових даних у середовищі табличного процесора

 

Але, якщо ми використаємо дію автоза-повнення та скопіюємо її вміст до клітинок С5 і С6, у цих клітинках виникнуть помилки (рис. 7.2).

 

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

 

Отже, ми бачимо, що в клітинках С5 і С6 ділення відбувається не на значення клітинки В1, у якій розташовано курс валют, а на клітинки розташовані нижче. Звичайно, ви отримаєте правильний результат, якщо зміните ці форм.ули, щоб ділення виконувалося правильно (рис. 7.4).

А якщо таких формул потрібно ввести не три, а двадцять, або навіть сто і більше? Що тоді робити? Як не припуститися помилки і скоротити час на введення формул?

Якщо уважно поглянути па останні, вже правильно введені формули, то всі воїш містять посилання на ту саму клітинку В1. Щоб формула в клітинці С4 у результаті копіювання не перетворювалася на неправильну, адресу клітинки В1 слід «зафіксувати». У програмі Excel це роблять за допомогою символа $. Його можна розташувати перед назвою стовпця і перед номером рядка. Залежно від того, яку частину адреси зафіксовано та чи зафіксовано її взагалі, розрізняють відносні, абсолютні та змішані посилання на адреси клітинок (табл. 7.1).

Таблиця7.1. Типи посилань

Адреса	Змінений адреси після її копіювання	Тип посилання
А1	Змінюється і назва стовпця, і номер рядка	Відносне
$А$1	Адреса не змінюється	Абсолютне
$А1 А$1	Назва стовпця не змінюється, а номер рядка змінюється Назва стовпця змінюється, а номер рядка — ні	Змішане Змішане

 

Отже, для розглянутого вище завдання, формула в клітинці С4 повинна мати вигляд: =В4/В$1 (якщо її копіювати лише в межах зазначеного в завданлі стовпчика) або =В4/$В$1 (якщо копіювати при потребі в будь-яку клітинку таблиці). Застосуємо дію автозаповие-

шш до даної формули. Після цього діапазон клітинок С4:С6 буде містити такі формули, як па рис. 7.5.

 

Зазначимо, що під час введення та редагування формул ви можете швидко змінювати тип посилання на адреси, щоразу натаскаючи клавішу РЛ. Покажемо це па прикладі адреси клітинки

Завдання. Створити таблицю за зразком (рис. 7.6). Ввести формули для виконання необхідних розрахунків.

 

1. Створіть таблицю та виконайте потрібне форматування.

2. Заповніть формулами клітинки виділені жовтим кольором.

3. Заповніть формулами клітинки виділені зеленим кольором. До клітинки С2 введіть формулу: =В2/$В$5. Для клітинок Е2 і 62 визначте формули самостійно.

4. Скопіюйте формули із зелених клітинок, застосувавши автозаповнення.

5. Встановіть у діапазонах клітинок С2:С4, Е2:Е4, С2:Є4 відсотковий формат.

6. Збережіть файл у власній папці під назвою Вправа_64_Прізвище.

ВПРАВА 65

 

1. Створіть таблицю за зразком (рис. 7.7) або завантажте файл Вправа_65 з папки Files на диску та виконайте потрібне форматування.

2. До клітинок D13 і D14 введіть поточні курси валют.

 

3. Заповніть формулами клітинки виділені зеленим кольором.

4. Скопіюйте формули з зелених клітинок, застосувавши автозаповнення.

5. Встановіть грошовий формат відповідно до зазначеної валюти.

6. Збережіть файл у власній папці під назвою Вправа_65_Прізвище.

Висновки

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

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

Для фіксування елементів адреси клітинки використовують знак $, який встановлюють перед тим елементом, який потрібно зафіксувати.

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

1. Яке призначення табличного процесора?

2. Що таке адреса клітинки?

3. Які дані може містити клітинка?

4. Які існують види посилань на адреси клітинок?

5. Як виконати перехід від одного виду посилання до іншого при введенні формули?

Питання для роздумів

1. У яких випадках можна використовз'вати відносні посилання, а коли абсолютні чи змішані? Наведіть приклади.

Завдання для досліджень

1. Дослідіть, для чого використовзтоть у MS Excel стиль посилань R1C1.

Імена клітинок та їх діапазонів.

Ьні Розв'язування задач на обчислення

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

Ім’я може починатися з літери, знака підкреслення або знака «\» і містити, крім літер, цифри, питання і крапки. Довжина імені мас бути не більше 255 символів і не може містити пробілів. За умовчанням імена клітинок або діапазонів клітинок вважаються абсолютними посиланнями.

 

Щоб присвоїти ім’я клітинці чи діапазону клітинок, потрібно виділити клітинку чи діапазон клітинок, у вкладці Формули обрати команду Визначити ім’я, у результаті чого з’явиться діалогове вікно Нове ім’я (рис. 7.8).

У цьому вікні необхідно:

• ввести ім’я клітинки;

• обрати область застосування (уся книга або лише даний аркуш);

• за потреби ввести певне пояснення у паш Примітка;

• змінити або залишити незмінним посилання на клітинку або діапазон, для яких встаїювлюоться ім’я.

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

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

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

 

У цьому вікні відображено перелік імен, у якому зазначено ім’я, значення, посилаппя, області застосування та примітки. Засобами цього вікна можна також створити нове ім’я, змінити або видалити те, що с.

Так, якщо в завданні, розглянутому в попередньому параграфі щодо визначення ціни товарів у холіпгах, для клітинки В1 встановити певне ім’я (наприклад, холінг), то у формулах не потрібно буде фікувати адреси. Замість адреси клітинки у формулах бзще вказано її ім’я (рис. 7.11).

ВПРАВА 66

Завдання. Виконати в таблиці необхідні розрахунки, надавши імена клітинкам ЕЗ, Е4, Е8.

 

 

1. Створіть таблицю за зразком (рис. 7.12) або заванта-жете файл Вправа_66 з папки Files на диску відформатуйте її.

2. Надайте імена клітинкам ЕЗ, Е4, ..., Е8.

а) Виділіть клітинку ЕЗ.

б) У вкладці Формули оберіть команду Визначити ім'я.

в) У діалоговому вікні Нове ім’я введіть ім’я Опалення та натисніть ОК.

г) Аналогічно надайте імена іншим клітинкам.

3. Обчисліть вартість опалення в кожній квартирі за місяць.

а) До клітинки F12 введіть формулу для розрахунку вартості опалення за місяць: (ціна за 1 м²) * (площа квартири). Для цього:

• виділіть клітинку F12;

• введіть знак «=» і клацніть на клітинку ЕЗ, натисніть знак «*» і клацніть на клітинку В12;

• натисніть Enter. У результаті формула буде мати вигляд: =Опалення*В12.

б) Виконайте копіювання формули до клітинок знизу, використовуючи дію автозаповнення.

4. До інших клітинок формули введіть самостійно.

5. Збережіть файл під назвою Вправа_80_Прізвище.

І ВПРАВА 67

 

Завдання. Створити таблицю за зразком (рис. 7.13). Обчислити значення у за формулою:

1. Створіть таблицю за зразком.

 

2. Обчисліть значення змінних О, Т, увівши відповідні формули до клітинок В2 і С2.

3. Надайте імена клітинкам Р, О і Т, що містять значення змінних Р, О, Т.

4. До клітинки В5 введіть формулу для знаходження значення змінної^:

=(Р+2*А5)/0-А5^А2/(Р+1).

5. Застосуйте до попередньої формули дію автозаповне-ння, виконавши її копіювання в діапазон В5:В14.

6. Збережіть файл під назвою Вправа_67_Прізвище.

Висновки

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

За умовчанням імена клітинок або діапазонів клітинок вважаються абсолютними посиланнями.

Для падання імені потрібно виділити клітинку або діапазон клітинок і у вкладці Формули обрати команду Визначити ім'я.

За допомогою діалогового вікна Диспетчер імен можна додавати, редагувати та видаляти імена.

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

1. Що таке ім’я діапазону клітинок?

2. Для чого клітинці надають ім’я?

3. Як створити ім’я діапазону клітинок?

4. Як змінити ім’я клітинки?

5. Де можна переглянути імена клітинок, які вже названі?

І Питання для роздумів

1. Як задати ім’я клітинці тільки для даного аркз'ша?

2^е. Чи можна для створення нового імені діапазону використовувати клітинки, яким уже надано імена?

З*. Чи може та сама клітинка мати декілька імен?

Завдання для досліджень

1. Визначте, як надати ім’я клітинці за допомогою її контекстного мешо. 2. Визначте, як виділити одночасно декілька діапазонів за допомогою їхніх імен.

Розв'язування задач на обчислення

 

Завдання 1. Створити таблицю на першому аркуші за зразком (рис. 7.14) та виконати обчислення.

1. Створіть таблицю та виконайте необхідне форматування.

Примітка. Щоб змінити напрямок тексту, слід обрати інструмент Орієнтація в групі Вирівнювання на вкладці Основне.

2. Введіть необхідні формули до клітинок 32,К2, С5 і С6. Скопіюйте їх до відповідних діапазонів, використовуючи ав-тозаповнення.

3. У клітинці К6 введіть формулу для обчислення загального прибутку.

4. Надайте аркушу ім’я Завданняі та збережіть файл під назвою ПР14_ Прізвище.

 

Завдання 2. Створити таблицю множення чисел від 1 до 9 на другому аркуші.

1. Створіть таблицю за зразком (рис. 7.15).

2. Уведіть до клітинки В2 формулу =В$1*$А2, що перемножує число в рядку на число у стовпці, які розташовані ліворуч та зверху над даною клітинкою.

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

3. Виконайте копіювання формули з клітинки В2 до діапазону С2.32, застосувавши автозаповнення.

4. Виділіть діапазон В2:32 та застосуйте до нього дію автозаповнення до діапазону ВЗ:Л0. У результаті ви маєте отримати таблицю множення.

5. Надайте аркушу ім’я Завдання2 та збережіть файл.

6'. На третьому аркуші реалізуйте таку ж таблицю множення із створенням імен діапазонів.

7. Надайте аркушу ім’я Завдання2* та збережіть файл.

Математичні та статистичні функції

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

У сьомому класі ви вже ознайомилися з деякими найбільш уживаними функціями такими, як SUM, MAX, MIN, AVERAGE, COUNT, що призначені для знаходження суми, максимального і мінімального значень, середнього арифметичного та знаходження кількості числових значень.

Нагадаємо основні елементи, що мас містити функція:

• власне ім’я, наприклад SUM;

• аргументи, що відокремлені крапкою з комою та обмежені дужками.

Аргументом функції може бути число, текст, посилання на клітинку

або діапазон клітинок, формула, інша функція тощо.

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

MS Excel містить математичні, статистичні, логічні та інші функції.

МАТЕМАТИЧНІ ФУНКЦІЇ

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

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

Функція	Функція в MS Excel з російським інтерфейсом	Призначення
ABS(4HCHO)	ABS	модуль числа
COS(4H010)	COS	косинус кута (аргумент має бути в радіанах)
МТ(число)	ЦЕЛОЕ	округлення числа до найменшого цілого
МСЮ(число; дільник)	ОСТАТ	остача від ділення
PRODUCT(4MOiol; число2;...)	ПРОИЗВЕД	добуток чисел
QUOTIENT(4McenbHHK; знаменник)	ЧАСТНОЕ	ціла частина від ділення
RADIANS(Kyr)	РАДИЛИ ЬІ	перетворення градусів на радіани
ROUND(4hcho; кільк_ розрядів)	ОКРУГЛ	округлення числа до заданої кількості десяткових знаків
SIN(4H010)	SIN	синус кута (аргумент має бути в радіанах)
SUMPRODUCT(fliana-зоні; діапазон2;...)	СУММПРОИЗВ	визначення суми добутків відповідних діапазонів
SQRT(4hoio)	КОРЕНЬ	квадратний корінь числа
ТАМчисло)	TAN	тангенс кута (аргумент має бути в радіанах)

СТАТИСТИЧНІ ФУНКЦІЇ

 

Для здійснення статистичного аналізу даних у MS Excel використовують функції з категорії Статистичні. Більшість у цій категорії функцій використовує вузьке коло спеціалістів, проте частина з них є досить часто уживаними. Так, до статистичних функцій відносяться вже відомі вам MAX, MIN, AVERAGE та COUNT. У таблиці 7.3 наведено ще деякі функції, які використовують при обчислепиях з категорії Статистичні.

Таблиця 7.3. Перелік деяких статистичних функцій

Функція	Функція в MS Excel з російським інтерфейсом	Призначення
СОиМТА(значення1; значення2;...)	счЄтз	визначення кількості непорожніх клітинок
COUNTIF(fliana30H; критерій)	СЧЄТЕСЛИ	визначення кількості непорожніх клітинок, які задовольняють вказаний критерій
			RANK(4Hcno; посилання; порядок)	РАНГ	визначення рангу числа в переліку чисел

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

Аргументи функції RANK:

• число — число, для якого визначається ранг;

• посилання — діапазон чисел, серед яких визначається ранг;

• порядок — визначення способу впорядкування даних. Якщо порядок дорівнює нулю або його не вказано, то число, що є найбільшим значенням діапазону посилання, отримає значення ранг}' 1. Якщо порядок дорівнює одиниці, то значення рангу 1 отримає число, що є найменшим значенням діапазону посилання.

Починаючи з 2010 версії MS Excel, ця функція була замінена новими функціями: RANK.AVG (рос. РАНГ.СР) і RANK.EQ (рос. РАНГ.РВ), яга забезпечують більшу точність.

ВИКОРИСТАННЯ МАЙСТРА ФУНКЦІЙ

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

Потрібну футакцію можна вибрати в діалоговому вікні Вставлення функції, яке викликається декількома способами:

 

• натиснути кнопку , що розташована ліворуч від рядка формул;

 

 

 

 

 

 

 

 

 

та відкрито дужку для введешія аргументів. Також з’явиться підказка щодо переліку аргументів, які мас містити функція (рис. 7.20).

 

Після введення аргументів потрібно закрити дужку. Якщо формулу завершено, то клавішу Enter, якщо ні, то ввести інші знаки для продовження формули. Нагадаємо, що для відокремлення аргументів використовують «;».

ВПРАВА 68

Завдання. У клітинках А2, В2, С2 дано числові значення. Знайти значення інших змінних за вказаними нижче формулами.

 

 

 

 

 

 

в) У діалоговому вікні Вставка функції ооеріть категорію Математичні.

г) Оберіть функцію SQRT (рос. КОРЕНЬ) та натисніть ОК.

ґ) У діалоговому вікні Аргументи функції у полі Число введіть аргумент А2/В2.

д) У рядку формул встановіть курсор після останньої дужки, еї Поставте знак «+».

 

 

ж) Знову оберіть категорію Математичні, виберіть функцію АВБ і натисніть ОК.

з) Введіть аргумент даної функції В2-С2. Завершіть введення формули, натиснувши ОК.

3. Формули для обчислення значень у клітинках В5:В9 введіть самостійно.

4. Збережіть файл під назвою Вправа_68_Прізвище.

І ВПРАВА 69

Завдання. Виконати переведення числа від 0 до 1023 з десяткової системи числення в двійкову.

1. Створіть таблицю за зразком (рис. 7.22).

 

2. У клітинці С2 введіть формулу для знаходження остачі від ділення числа, що знаходиться в клітинці В2, на 2.

а) Виділіть клітинку С2 і викличте майстра функцій.

б) У категорії Математичні оберіть функцію MOD (рос. ОСТАТ).

в) У діалоговому вікні Аргументи функції у полі Число оберіть клітинку В2, у полі Дільник — 2.

г) Натисніть ОК. У результаті в рядку формул ви маєте одержати формулу = MOD(B2;2).

3. У клітинці ВЗ введіть формулу для знаходження цілої частини від ділення попереднього числа, що знаходиться в клітинці В2, на 2. Виконайте дії аналогічно до описаних у попередньому пункті, застосувавши функцію QUOTIENT (рос. ЧАСТНОЕ). В результаті в клітинці ВЗ ви маєте одержати формулу: = QUOTIENT(B2;2).

4. Застосуйте до введених формул дію автозаповнення, протягнувши їх до діапазонів В4:В11 і СЗ:С11. У результаті в клітинках діапазону С2:С11 буде одержано число записане в двійковій системі числення, якщо переглядати діапазон у зворотному порядку.

Примітка. Для з’єднання значень діапазону до однієї клітинки можна скористатися функцією CONCATENATE (рос. СЦЕПИТЬ). Для з’єднання клітинок діапазону С2:С11 у зворотному порядку формула буде мати вигляд: =CONCATENATE(C11;C10;C9;C8;C7;C6;C5;C4;C3;C2).

5. Збережіть файл під назвою Вправа_69_Прізвище.

 

II ВПРАВА 70

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

1. Створіть таблицю за зразком (рис. 7.23).

2. До клітинки D2 введіть формулу для обчислення рейтингу першого учня серед інших учнів.

а) У вікні майстра функцій в категорії Статистичні оберіть функцію RANK.EQ (рос. РАНГ.РВ) або RANK (рос. РАНГ).

б) У вікні діалогового вікна в полі Число оберіть клітинку С2, а в полі Посилання —діапазон $С$2:$С$11, у полі Порядок — число 0.

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

У результаті в рядку формул ви маєте одержати формулу: = RANK(C2;$C$2:$C$11;0).

3. Виконайте копіювання даної формули до клітинок 03:011, використовуючи дію автозаповнення.

 

4. Збережіть файл під назвою Вправа_70_Прізвище.

Висновки

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

Щоб виконати математичні обчислення, використовують функції з категорії Математичні, а щоб проаналізувати статистичні дані, — функції категорії Статистичні.

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

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

1. Що таке функція в MS Excel?

2. Які ви знаєте математичні функції? статистичні функції?

3. Яке призначення функції RANK?

4. Вкажіть способи виклику майстра функції.

5. Вкажіть послідовність дій для додавання фушщії за допомогою майстра.

6. Як увести формулу врз'чпу?

Питання для роздумів

1*. Чи можна в одній формулі ввести одну функцію за допомогою майстра, а іншу — вручну?

2. Навіщо в Excel використовують декілька способів виклику майстра функції?

Завдання для досліджень

1*. Дослідіть, скільки вкладень може мати одна функція.

2^е*. Визначте, чим відрізняються функції ЯАМК.АУО і ЯАМК.ЕО.

Логічні функції

ВИКОРИСТАННЯ ЛОГІЧНИХ ФУНКЦІЙ

У MS Excel можна вирішувати завдання, виконання яких залежить від певної умови. Частину таких завдань можна реалізз'вати, використовуючи функції з категорії Логічні. Однією з основних функцій даної категорії с функція IF (рос. ЕСЛИ), яка мас такий синтаксис:

=ІР(Лог_вираз;Значення_якщо_істина;Значення_якщо_хибність).

 

Як і в програмуванні, в табличному процесорі можна використовувати складені логічні вирази, для яких використовують логічні зв’язки AND (рос. И), OR (рос. ИЛИ), NOT (рос. НЕ). У дужках даних функцій логічні вирази записують через крапку з комою. Для порівняння числових значень використовують знаки порівнянь (=, >, < тощо), які записують так само, як і в мові програмування.

ВПРАВА71

 

1. Створіть таблицю за зразком (рис. 7.25).

2. У клітинці С2 введіть формулу для визначення типу фігури. Виконайте копіювання даної формули до діапазону С2:С8, використовуючи автозаповнення.

а) Зробіть активною клітинку С2 і викличте майстра функцій.

б) Оберіть функцію ІР (рос. ЕСЛИ) з категорії Логічні.

 

в) У діалоговому вікні в полі Лог_вираз введіть умову А2=В2. У полі Зна-чення_якщо_істина — «Квадрат», у полі Значення_якщо_хибність — «Прямокутник» (рис. 7.26).

Завдання. Відомо час, за який учні подолали дистанцію на 100 м. Визначте, яке місце посів кожний учень, і надайте відповідно до цього рекомендації: якщо 1, 2 або 3 місце — «Призер», інакше — «Тренуйся далі».

 

1. Створіть таблицю за зразком (рис. 7.27).

2. У клітинці СЗ введіть формулу для визначення місця, яке посіли учні, скориставшись функцією RANK (рос. РАНГ). Скопіюйте формулу до клітинок нижче, використовуючи дію автозаповнення.

 

3. У клітинці D3 введіть формулу для надання рекомендації. Виконайте копіювання даної формули до діапазону D4:D7, використовуючи автозаповнення.

а) Зробіть активною клітинку 03 та викличте майстра функцій.

б) Оберіть функцію ІР (рос. ЕСЛИ) з категорії Логічні.

 

в) У діалоговому вікні Аргументи функції, перебуваючи в полі Лог_вираз, відкрийте список функцій, клацнувши на стрілку в полі, що розташована ліворуч від рядка формул (рис. 7.28).

г) У списку оберіть функцію СЖ (рос. ИЛИ) або команду Інші функції для подальшого вибору функції СЖ.

ґ) У полі Лог_значення1 введіть умову С3=1. у полі Лог_ значення2 — С3=2, у полі Лог_значенняЗ — С3=3.

д) У рядку формул після введеної умови СЖ(СЗ=1;СЗ=2;СЗ=3) поставте крапку з комою. У результаті ви маєте повернутися до вікна функції ІЯ.

е) У полі Значення_якщо_істина введіть «Призер», у полі Значення_ якщо_хибність — «Тренуйся далі».

є) Натисніть ОК. У результаті для клітинки С2 у рядку формул має з'явитися формула: =ІР(СЖ(СЗ=1;СЗ=2;СЗ=3);«Призер»;«Тренуйся далі»). 4. Збережіть файл під назвою Вправа_72 Прізвище.

ВКЛАДЕНІ ФУНКЦІЇ

 

Щоб перевірити більш ніж одну умову, вико-ристовз'їоть вкладення функції ІР.

Так, наприклад, відомо результати ігор між двома командами «Квант» і «Алмаз». Потрібно визначити для кожної гри, хто переміг, або вивести повідомлення про нічию (рис. 7.29).

У MS Excel для цього використовують вкладення функції таким чином: =ІР(В2>С2;«Квант»;ІР(В2<С2;«Алмаз»;«Нічия»)).

Щоб ввести що вкладену функцію, використовуючи майстра функцій, треба викопати таку послідовність дій.

1. Виділіть клітинку D2. Оберіть функцію IF з категорії Логічні.

2. У діалоговому вікні в полі Лог_вираз введіть умову В2>С2, у полі Значення_якщо_істина — «Квант».

3. Перебуваючи в полі Значення_якщо_хибність, клацніть знову па функції IF у полі, яке розташоване ліворуч від рядка формул (рис. 7.31). Після чого мас з’явитися вікно

 

 

 

ВПРАВА 73

Завдання. Вивести інформацію про результати змагань між командами «Квант» і «Алмаз», керуючись даними та поясненнями поданими вище. Зберегти таблицю під назвою Вправа_73_Прізвище.

Висновки

Для виконання завдань з врахуванням умов у MS Excel використовують фз'пкщї з категорії Логічні.

Однісю з основних функцій даної категорії с функція IF (рос. ЕСЛИ), яка мас такий синтаксис:

=ІР(Лог_вираз;Значення_якщо_істина;Значення_якщо_хибність). Для формування складних логічних виразів використовують функції AND (рос. И), OR (рос. ИЛИ), NOT (рос. НЕ).

Для порівняння числових значень використовують знаки порівнянь.

1. Для чого використовують функції з категорії Логічні?

2. Опишіть синтаксис функції IF.

3. Для чого використовують функції AND, OR, NOT?

4. Опишіть синтаксис функції AND.

І Питання для роздумів

1. У яких випадках використовують вкладені логічні функції? 2. Скільки логічних виразів може містити функція О В?

Завдання для досліджень

1. Порівняйте синтаксис функції IF у MS Excel з цією ж функцією в VB.NET.

2. Яке призначення і синтаксис функції IFERROR (ЕСЛИОШИБКА)?

Умовне форматування

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

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

Створюючи умовне форматування в Excel, широко застосовують готові рішення, такі як кольорові шкали, гістограми, набори значків.

ВИКОРИСТАННЯ КОЛЬОРОВОЇ ШКАЛИ

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

Використання умовного форматування з кольоровою шкалою розглянемо, викопуючи наступну вправу.

Завдання. У таблиці з числовими даними застосуйте умовне форматування з використанням колірної шкали до діапазону А1 :АІ_33 для одер-

 

жання деякого зображення.

1. Відкрийте файл Вправа_74 з папки Files на диску.

2. Виділіть діапазон А1 :АІ_33.

3. У групі Стилі на вкладці Основне оберіть Умовне форматувнання ► Кольорові шкали (рис. 7.32).

4. Оберіть першу кольорову схему.

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

6. Спробуйте застосувати до даного діапазону команду умовного форматування Набори піктограм

 

ВИКОРИСТАННЯ ПРАВИЛ ВИДІЛЕНИХ КЛІТИНОК

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

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

1. Виділіть необхідний діапазон.

2. У групі Стилі на вкладці Основне оберіть Умовне форматування

► Правила виділених клітинок.

3. У меню Правила виділених клітинок оберіть потрібну команду порівняння: більше, менше, між, дорівнює тощо.

4. Якщо, наприклад, обрати команду Більше, то у вікні Більше в полі Форматувати клітинки, які БІЛЬШІ ЗА: потрібно ввести значення або клацнути на клітинці з необхідним значенням, а у вікні поряд обрати бажатгий спосіб форматування клітинки при виконанні вказаної умови (рис. 7.33).

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

ВПРАВА 75

Завдання. В електронному журналі червоним кольором виділити клітинки з оцінками високого рівня, зеленим кольором — достатнього рівня, жовтим — низького рівня.

 

1. Створіть таблицю за зразком (рис. 7.34) або відкрийте файл Вправа_75 з папки Files на диску.

2. Застосуйте умовне форматування для оцінок достатнього рівня (від 7 до 9), виділяючи їх зеленим кольором.

а) Виділіть діапазон B2:G11.

б) На лінійці Основне в області Стилі оберіть команду Умовне

 

 

6. Самостійно задайте умовне форматування для оцінок низького рівня (менше 4 балів), обравши команду Менше в меню команди Правила виділених клітинок. У результаті виконання завдання ви маєте одержати таке форматування таблиці, як на рис. 7.36. При зміні оцінок у таблиці кольорове зафарбування клітинок має автоматично змінюватися.

7. Збережіть файл під назвою Впра-ва_75_Прізвище.

ВИКОРИСТАННЯ ГІСТОГРАМ

 

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

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

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

СТВОРЕННЯ ВЛАСНОГО ПРАВИЛА З ВИКОРИСТАННЯМ ФОРМУЛ

 

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

I ВПРАВА 76 I

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

1. Створіть таблицю за зразком (рис. 7.38) або відкрийте файл Вправа_76 з папки Files на диску.

 

2. Виділіть діапазон клітинок С2:С9.

3. На вкладці Основне в групі Стилі оберіть команду Умовне форматування ► Створити правило... .

4. У вікні Нове правило форматування оберіть команду Використовувати формулу для визначення клітинок для форматування.

5. У полі Форматувати значення, для яких ця формула має значення ІСТИНА введіть формулу: =IF(C2>=$C$11;TRUE)

або =С2>=$С$11 (рис. 7.39).

Тут С2 є відносним посиланням, оскільки значення в діапазоні змінюються, $С$11 — абсолютне, оскільки воно є сталим для усіх значень діапазону.

 

6. Клацніть на кнопку Форматувати... і встановіть рожевий колір зафарбування клітинки.

7. Натисніть ОК. У результаті вікно таблиці буде виглядати, як на рис. 7.40.

Примітка. Якщо потрібно змінити створене правило, виділіть клітинки, до яких застосовано умовне форматування, та виконайте команду Умовне форматування ► Керування правилами..., оберіть правило та натисніть кнопку Редагувати правило... .

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

9*. Самостійно створіть правило, щоб виділеними були не тільки значення середніх балів, а також прізвище та ім’я учня.

10. Збережіть файл під назвою Вправа_76_Прізвище.

Висновки

Щоб змінити формат клітинки залежно від деяких умов, використовують умовне форматування .

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

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

Якщо для умовного форматування клітинок недостатньо готових рішень, користувач мас можливість створити власне правило.

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

1. Для чого використовують умовне форматування?

2. Які способи умовного форматування ви знаєте?

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

4. Який спосіб умовного форматування потрібно застосувати для видалення клітинок, значення яких належить числовому діапазону?

Питання для роздумів

1*. Наведіть приклади завдань, для яких доцільно використовувати умовне форматування за допомогою гістограм.

2*. Чи можна при створенні власного правила умовного форматування скористатися майстром формул?

Завдання для досліджень

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

2\ Дослідіть, як можна встановити умовне форматування для даних, які с більшими (меншими) за середнє арифметичне даних деякого діапазону.

ПРАКТИЧНА РОБОТА №15

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

Завдання 1. У заданому шаблоні робочого аркуша Тестування реалізувати перевірку відповідей на запитання тестів, оцінити відповіді за 12-бальною шкалою оцінювання. У бланку відповідей правильні відповіді підсвітити зеленим кольором, неправильні — рожевим.

1. Завантажте файл nP15.xls з папки Files на диску.

 

2. У файлі ПР15 на аркуші Тестування виконайте форматування таблиці за зразком (рис. 7.41).

Пояснення. У клітинках

АЗ:В22 введено запитання та варіанти відповідей. У клітинках Е2:Н2 — правильні відповіді на запитання, згодом вони будуть приховані, щоб той, хто буде давати відповіді на запитання, їх не бачив. У клітинках Е5:Н5 залежно від відповіді буде зараховано 1 або 0 балів, а в клітинці Е8 має бути виставлена оцінка за тест.

3. До клітинок Е4:Н4 введіть відповіді довільним чином.

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

відповідь є правильною: =IF(E2=E4;1;0). Скопіюйте цю формулу до діапазону F5:p, використовуючи автозаповнення.

5. У клітинці І5 полічіть загальну кількість набраних балів за тестування.

6. У клітинці Е8 введіть формулу для виставлення оцінки за 12-бальною системою оцінювання. Для цього потрібно загальну кількість балів (15) поділити на кількість запитань і помножити на 12. Результат має бути цілим числом.

 

7. До клітинок Е4:Н4 застосуйте умовне форматування: якщо в клітинці відповідь правильна, то вона зафарбована в зелений колір, якщо неправильна, то — в рожевий.

а) Виділіть діапазон Е4:Н4.

б) На вкладці Основне в групі Стилі оберіть команду Умовне форматування -> Створити правило.

в) У вікні Нове правило форматування оберіть команду Використовувати формулу для визначення клітинок для форматування.

г) У полі Форматувати значення, для яких ця формула має значення ІСТИНА введіть формулу: =IF(E5=1;TRUE) або =IF(E2=E4;TRUE).

ґ) Клацніть на кнопку Форматувати та встановіть зелений колір зафарбування клітинки. Натисніть ОК.

д) Самостійно створіть правило для встановлення умовного форматування, якщо відповідь неправильна.

8. Приховайте проміжні обчислення та правильні відповіді, щоб особа, яка проходить тестування, їх не бачила.

а) Виділіть клітинки D2:p і D5:I5.

б) Встановіть для них білий колір літер.

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

9'. Змініть формулу в клітинці Е8, враховуючи, якщо користувач не надав жодної відповіді, він має замість 0 балів отримати оцінку 1 бал.

Завдання 2. Розв’язати квадратне рівняння . Відповідь представити у вигляді двох чисел округлених до двох десяткових знаків або у вигляді повідомлення «немає розв'язків». Повідомлення про відсутність розв’язків підсвітити жовтим кольором, додатні розв’язки — рожевим, а від'ємні — блакитним.

1. Перейдіть до аркуша Квадратне рівняння.

2. У клітинках А2:С2 введіть довільні числа, які є коефіцієнтами квадратного рівняння.

3. У клітинці Е2 обчисліть дискримінант квадратного рівняння за формулою:

D = b²-4ac

4. До клітинок В4 і В5 введіть формули для обчислення коренів квадратного рівняння з урахуванням умови, якщо D < 0, то рівняння немає коренів.

а) До клітинки В4 введіть формулу:

=IF(E2<0;«HeMae po3B’n3Kie»;(-B2+SQRT(E2))/2*A2).

б) До клітинки В5 введіть формулу самостійно.

5. Виконайте умовне форматування клітинок В4 і В5, вказане в умові завдання.

Завдання 3. На аркуші Заробітна платня заповнити таблицю відповідно до запропонованих умов.

Нарахування за лікарняні дні залежить від стажу роботи: до 3-х років — 50%; від 3-х до 5-ти років — 60%; від 5-ти до 8-ми років — 70 %; більше 8-ми років — 100%.

Врахувати, що податок становить 15%, а відрахування до пенсійного фонду —2%.

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

Створення та налагодження діаграм різного типу

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

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

ОСНОВНІ ТИПИ ДІАГРАМ

Стовпчасті діаграми

 

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

 

Лінійчаті діаграми (або графіки)

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Зазначимо, що кожний тип діаграм має свої різновиди. Так Стовпчасті діаграми можуть бути плоскими, об’ємними, циліндричними, конічними, пірамідальними (рис. 7.48). Залежно від мети подання даних використовують звичайну діаграму і з накопиченням. У звичайній стовпчастій діаграмі графічні зображення числових даних з різних наборів розташовано порз'ч один з одним. У діаграмі з накопиченням — один на одному. Це дає змогу оцінити сумарні дані і внесок кожної складової в загальну суму. Користувач може обрати потрібний різновид з меню інструмента відповідного типу діаграм.

 

ВИБІР ТИПУ ДІАГРАМИ

Основне завдання побудови діаграм — вибрати правильно тип, щоб коректно відобразити дані. Найбільш широко використовують графіки, стовпчасті та кругові діаграми.

Для побудови графіків враховують:

• чи с залежність між двома числовими величинами;

• значення числових величин визначені через однакові проміжки часу.

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

Побудова стовпчастих діаграми доцільна, коли:

• с залежність між двома числовим величинами;

• дані, що мають бути відображені па діаграмі, не демонструють неперервний процес.

Побудову кругових діаграм здійснюють, якщо:

• необхідно відобразити дані лише одного ряду;

• жодне із значень не с нульовим або від’ємним;

• категорії відповідають частинам єдиного цілого.

Розглянемо вправу, що передбачає побудову нового типу діаграм — графіка, і пригадаємо послідовність дій щодо створення та налаштування діаграм.

 

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

1. Оберіть тип діаграми для відображення даних.

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

2. Побудуйте діаграму для графічного подання даних.

 

а) Виділіть діапазон А1:С8.

б) Оберіть інструмент Лінійчата на вкладці Вставлення.

в) У меню інструмента Лінійчата оберіть вид £-^::| Графік з маркером. У результаті буде побудовано графіки.

3. Виконайте оформлення діаграми за зразком (рис. 7.50). а) Додайте назву, обравши інструмент Назва діаграми

на вкладці Макет.

 

б) Змініть колір і тип маркерів графіків:

• клацніть на одному графіку та в контекстному меню оберіть команду Формат ряду даних;

• у діалоговому вікні Формат рядів даних параметри маркера

встановіть у розділах Параметри маркера, Заливка маркера та Колір лінії

маркера;

• у відповідних розділах встановіть колір, товщину і тінь лінії;

• аналогічно налаштуйте зовнішній вигляд другого графіка.

в) Додайте підпис вертикальної осі, обравши інструмент Назви осей на вкладці Макет.

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

ґ) Змініть за потреби розмір шрифту написів на діаграмі, обираючи необхідний елемент і встановлюючи параметри шрифту в групі Шрифт на вкладці Основне.

4. Збережіть файл під назвою Вправа_77_Прізвище.

ВПРАВА 78

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

 

1. Введіть дані до аркуша електронної таблиці за зразком (рис. 7.51).

2. Розгляньте дані розподілу часу учня протягом доби. Виберіть тип діаграми. Оскільки дані містять усього один ряд, не містять від'ємних і нульових

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

3. Побудуйте діаграму розподілу часу учня протягом доби.

а) Виділіть діапазон А5:В9.

б) На вкладці Вставлення оберіть інструмент Секторна та бажаний вид діаграми. В результаті діаграму буде побудовано.

4. Виконайте оформлення діаграми, додавши заголовок і значення біля її секторів.

а) На вкладці Макет оберіть інструмент Назва діаграми, обравши бажаний спосіб розташування назви діаграми. Введіть назву діаграми.

б) Додайте підписи даних у відсотках:

• на вкладці Макет оберіть інструмент Підписи даних і клацніть на команді Інші параметри підпису даних;

• у розділі Підписи даних зніміть прапорець з параметра Значення та встановіть біля параметра Відсотки;

• у цьому ж розділі встановіть розташування підписів Коло вершини, ззовні.

в) Змініть кольорове оформлення секторів за власним бажанням.

5. Розгляньте дані продажу товарів за день. Виберіть тип діаграми.

Оскільки між двома числовими величинами немає залежності, дані не є

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

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

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

Оскільки є залежність між двома величинами (залежно віддати встановлено

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

 

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

9. Збережіть файл під назвою Вправа_78_Прізвище.

Висновки

Діаграма — це наочне графічне представлення різних даних, що с зручним засобом для порівняння і демонстрації.

У MS Excel можна побудувати такі основні типи діаграм: стовпчасті, секторні, лінійчаті, точкові, гістограми, діаграми з областями, для побудови яких треба обрати відповідний інструмент на вкладці Вставлення.

Для вибору інших типів діаграм необхідно обрати інструмент Інші діаграми па вкладці Вставлення.

Для зміни типу діаграми необхідно обрати інструмент Змінити тип діаграми па вкладці Конструктор.

Правильний вибір типу діаграми — одне з основних завдань у процесі їх побудови.

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

1. Які с типи діаграм?

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

3. Які типи діаграм демонструють неперервну зміну даних за рівні проміжки часу?

4. Для чого використовують діаграми з областями?

5. Як змінити тип побудованої діаграми?

Питання для роздумів

1. Який тип діаграм краще підходить для побудови графіків функцій? 2°\ Розробіть алгоритм вибору типу діаграми залежно від числових даних.

Завдання для досліджень

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

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

 

Упорядковування даних у таблицях. Автоматичні та розширені фільтри. Проміжні підсумки

УПОРЯДКУВАННЯ ДАНИХ У ТАБЛИЦЯХ

Якщо користувач працює з великою кількістю даних, то зручно їх посортувати.

 

 

 

 

 

 

 

 

 

 

 

 

ФІЛЬТРАЦІЯ ДАНИХ

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

 

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

Є два типи фільтрації списків. Для створення простих умов відборз' використовують автофільтр, а для більш складних — розширений фільтр.

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

1. Клацнути на будь-якій клітинці таблиці даних.

2. Застосувати до таблиці режим автофільтра одним із способів.

 

1 спосіб. На вкладці Основне обрати в мешо інструмента Сортування й фільтр команду Фільтр.

2 спосіб. На вкладці Дані обрати інструмент

 

 

3. Клацнути па кнопці з трикутником у стовпці, який треба відфільтрувати. Зі списку, що відкриється, вибрати умову фільтрації.

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

Для вибору одного або декількох значень із запропонованих даних списку необхідно залишити навпроти них прапорці, а інші забрати. Після натиснення кнопки ОК буде виведено лише вибрані значення. Якщо натиснути на прапорець (Виділити все), буде знято всі прапорці.

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

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

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

 

При фільтруванні текстових даних зручно використовувати символи «?» і «*», що дозволяють здійснювати пошук за неповними даними. Тут знак «?» визначає один будь-який символ, а «*» — будь-яку кількість довільних символів. Так запис «а*» буде означати, що необхідно вивести всі дані, що починаються на літеру «а».

 

Щоб зняти фільтр з даного стовпчика, треба клацнути на трикутник біля назви стовпчика та обрати команду Видалити фільтр із. Щоб відмінити фільтр на всьому аркуші, потрібно па вкладці Дані зняти виділення з інструмента Фільтр, клацнувши на ньому.

 

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

Щоб застосувати розширений фільтр, треба викопати наступні кроки.

1. На аркуші створити критерій відбору даних, вказавши назви стовпців і значення пошуку. Так, на рис. 7.56 представлено критерій пошуку книжок за ім’ям автора—Дара Кор-пій, або за 2012 роком видашія.

2. На вкладці Дані обрати в групі Сортування й фільтр вибрати кнопку Додатково. В результаті з’явиться діалогове вікно Розширений фільтр (рис. 7.57).

3. У полі Вихідний діапазон встановити діапазон, у яком}' розташована таблиця з даними. У полі Діапазон умов — діапазон з критерієм. Натиснути ОК.

Після чого буде відображено лише дані, що задовольняють критерій відбору (рис. 7.58).

 

ПРОМІЖНІ ПІДСУМКИ

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

Для створення проміжних підсумків необхідно виконати послідовність дій.

 

1. Відсортувати діапазон даних стовпця, який формує групу, а потім виділити діапазон.

 

2. На вкладці Дані обрати кнопку

таті відкриється вікно Проміжні підсумки.

3. У групі При кожній зміні в: обрати стовпець, за яким будуть здійснюватися під суміш.

4. У групі Використовувати функцію обрати функцію, за якою будуть здійснюватися підсумки (Сума, Кількість, Середнє, Максимум, Мінімум, Добуток).

5. У групі Додати підсумки до: обрати стовпці, що містять значення, за якими будуть здійснюватися підсумки. Натиснути ОК.

Щоб видалити проміжні підсумки, треба у вкладці Дані обрати інструмент Проміжні підсумки, у вікні, що відкриється, натиснути кнопку Видалити все, а потім — ОК.

У ВПРАВА 79

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

1. Завантажте файл Вправа_79 з папки Files на диску.

2. Створіть чотири копії цього аркуша. Для цього в контекстному меню ярлика Аркуші оберіть команду Перемістити або копіювати. В діалоговому вікні, що відкриється, встановіть прапорець для опції Створити копію та натисніть ОК.

3. На першому аркуші відсортуйте спочатку за назвою видавництва, а потім — за назвою книжки.

а) Клацніть у довільному місці таблиці.

б) На вкладці Дані оберіть інструмент Сортувати.

в) У діалоговому вікні, що відкриється, встановіть дані згідно рис. 7.59 і натисніть ОК.

 

 

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

а) Клацніть у довільному місці таблиці.

б) На вкладці Дані оберіть інструмент Проміжні підсумки.

в) У діалоговому вікні, що відкриється, встановіть дані згідно рис. 7.60 і натисніть ОК.

5'. На другому аркуші самостійно визначте кількість книжок, виданих за кожний рік, використовуючи проміжні підсумки.

6. На третьому аркуші визначте всі книжки, прізвища авторів яких починаються з літери «К».

а) Клацніть у довільному місці таблиці.

 

б) На вкладці Дані оберіть інструмент Фільтр.

в) Клацніть на кнопці з трикутником у стовпці Автор.

г) У полі пошуку введіть запис «к*» і натисніть ОК.

7. На четвертому аркуші виведіть інформацію про книжки видавництва «Богдан» або ті, що видані в 2015 році, застосувавши розширений фільтр.

а) У діапазоні іиз створіть критерій відбору, як на рис. 7.61. Пояснення. Оскільки значення умов розташовані в різних рядках, до

них буде застосовано сполучник «або». Для застосування сполучника «і» дані розташовують в одному рядку.

б) Клацніть у довільному місці таблиці з даними.

в) На вкладці Дані в групі Сортування й фільтр оберіть кнопку Додатково. В результаті з’явиться діалогове вікно Розширений фільтр.

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

ґ) У полі Діапазон умов —діапазон з критерієм іиз.

д) Встановіть опцію Скопіювати результат в інше місце та в полі Помістити результат в діапазон вкажіть діапазон А26:А40. Натисніть ОК. У вказаному діапазоні буде розміщено результати фільтації.

8'. На п’ятому аркуші виведіть самостійно інформацію про п'ять найтовсті-ших книжок.

9. Збережіть файл під назвою Вправа_79_Прізвище.

Сортування — це розташування даних таблиці в заданому порядку.

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

Для автоматичних розрахунків спільного підсумку для декількох рядків взаємопов'язаних даних використовують проміжні підсумки.

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

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

1. Які є способи сортування даних?

2. За якими параметрами можна здійснити сортування в MS Excel?

3. Для чого використовують фільтрацію даних?

4. Як застосувати автофі-льтр?

5. Для чого використовують проміжні підсумки?

6. Як забрати проміжні підсумки з аркуша?

Питання для роздумів

1. У яких випадках використовзчоть автофільтр, а в яких — розширений фільтр?

2". Чому при обчисленні проміжних підсумків один із стовпців має бути відсортованим?

Завдання для досліджень

I^е. Дослідіть і поясніть призначення інстрз'мептів групи Знаряддя даних па вкладці Дані.

2^е. Визначте, яким буде критерій для розширеного автофільтра, якщо у вправі 79 потрібно вивести всі книжки, що мають більше 100 сторінок та видані у видавництві «Богдан» або «Ранок».

 

Встановлення параметрів сторінки. Друкування електронної таблиці

ВСТАНОВЛЕННЯ ПАРАМЕТРІВ СТОРІНКИ

 

На відміну від MS Word в Excel параметри сторінки задають, щоб вивести далі па друк, і по суті це е складовою частиною встановлення налаштувань друку.

Для налаштування параметрів сторінки потрібно скористатися одним з інструментів групи Параметри сторінки на вкладці Розмітка сторінки або викликати діалогове мешо цієї групи.

На вкладці Сторінка (рис. 7.62) можна встановити орієнтацію сторінки, масштаб, розмір паперу та якість дрз'ку.

Зручною можливістю при налаштуванні параметрів сторінки с встановлення кількості сторінок по горизонталі та вертикалі.

 

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

На вкладках Поля та Колонтитули можна задати значення полів та за потреби встановити верхній та нижній колонтитули.

На вкладні Аркуш (рис. 7.63) важливими для виведення па дрзчс є такі параметри:

• встановлення діапазонз' (за умовчанням на дрзчс 63'де виведено всі дані аркуша);

• встановлення параметрів дрз'ку сітки, заголовків рядків і стовпців (за умовчанням при дрзші вони не виводяться);

• послідовність виведення сторінок.

НАЛАШТУВАННЯ ДРУКУ

 

Щоб налаштувати параметри друку, потрібно виконати таку послідовність дій.

1. Перейти на вкладку Файл та обрати розділ Друк.

2. У вікні налаштувань (рис. 7.64) у групі Друк встановити кількість копій.

3. У групі Принтер обрати один із встановлених принтерів і за потреби налаштувати його, обравши посилання Властивості принтера.

4. У групі Параметри особливої уваги заслуговують перший та останній списки.

а) У першому списку молена обрати ділянки друку: (Надрукувати активні аркуші, Надрукувати всю книгу, Надрукувати виділений фрагмент);

б) В останньому списку визначається спосіб виведення даних на друк:

Без масштабування — дрз'к аркушів з фактичними розмірами;

 

Розмістити аркуш на одній сторінці — розміри всієї таблиці зменшено в масштабі та розміщено на одній сторінці;

Розмістити всі стовпці на одній сторінці — таблиця по ширині буде розміщена на одній сторінці, кількість сторінок залеясить від кількості рядків у ній;

Розмістити всі рядки на одній сторінці — таблиця по висоті буде розміщена на одній сторінці, кількість сторінок залежить ВІД КІЛЬКОСТІ СТОВПЦІВ З' ній.

5. Для виведення документа на дрз'к натиснути

Друк.

Одним з найбільш розповсюджених завдань виведення на дрз'к є друк виділеного діапазону, який потрібно розташз'вати на одній сторінці. На рис. 7.65 показано встановлені параметри саме для виконання такого завдання.

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

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

Щоб налаштувати параметри друку, потрібно перейти на вкладку Файл та обрати розділ Друк.

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

1. Які основні налаштування можна виконати у вікні Параметри сторінки?

2. Як вивести на друк виділений діапазон?

3. Як вивести на друк таблицю, розмістивши її на одній сторінці?

4. Де можна побачити попередні налаштування друку?

Питання для роздумів

1*. Для чого потрібні колонтитули при виведенні документа на друк?

Завдання для досліджень

1. Дослідіть і поясніть можливості інструментів Тло та Область друк} з групи Параметри сторінки.

Упорядкування даних у таблицях.

Автоматичні та розширені фільтри

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

1. Завантажте файл nP16.xls з папки Files на диску.

2. Виконайте копіювання даних таблиці ще на шість аркушів.

3. На першому аркуші здійсніть сортування в алфавітному порядку спочатку за назвою мультфільму, а потім — у зворотному порядку за найменшою ціною.

а) Клацніть у довільному місці таблиці.

б) На вкладці Дані оберіть інструмент Сортувати.

в) У діалоговому вікні, що відкриється, встановіть дані згідно рис. 7.66 та натисніть ОК.

 

4. Застосуйте до таблиці проміжні підсумки, щоб підрахувати суму касового збору та кількість проданих квитків за кожний мультфільм (рис. 7.67).

5. На другому аркуші визначте мінімальну та максимальну ціну квитка на кожний мультфільм, використовуючи проміжні підсумки.

а) Виконайте сортування таблиці за назвою мультфільмів.

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

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

6'. На третьому аркуші самостійно визначте суму касового збору та кількість проданих квитків у кожному кінотеатрі, використовуючи проміжні підсумки.

7. На четвертому аркуші визначте всі мультфільми, які було показано в кінотеатрах «Старт» і «Салют», застосувавши автофільтр.

8'. На п'ятому аркуші виведіть дані про мультфільми, ціна квитків на які становить від 40 грн до 100 грн включно.

9. На шостому аркуші виведіть відомості про всі мультфільми кінотеатру «Факел» або ті мультфільми, на які було продано більше 1100 квитків.

 

 

 

а) У діапазоні 11 :J3 створіть критерій відбору, як на рис. 7.68.

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

10'. На сьомому аркуші виведіть інформацію про мультфільми кінотеатру «Boomer», що мають касовий збір менше 20000 грн або на них було продано менше 900 квитків.

11. Збережіть файл під назвою ПР16_Прізвище.

 

Це матеріал з підручника Інформатика 8 клас Казанцева