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

Електронні таблиці створюються у спеціальному програмному середовищі, яке також навивається електронною таблицею, або табличним процесором. У вільно поширюваному офісному пакеті Libre Office - це електронні таблиці Libre Office Calc (рис. 8.1), а в офісному пакеті Microsoft Office - Microsoft Office Excel (рис. 8.2). Електронний документ, який створюється табличним процесором, навивають Книгою. Книга складається в аркушів, вміст одного в яких виводиться на екран. Аркуш відповідно складається в комірок (клітинок). Форматом напису навивають описання вигляду та ровміру символів, ровташування напису відносно поля.

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

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

8.1. Обчислення в середовищі табличного процесора

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

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

Цля вручності можна включити режим, у якому ЕТ відображатимуть не ревультат обчислення формули, а власне формулу.

Нехай у комірці Аі таблиці міститься число 100, а в комірці В1 — число 20. Щоб поділити перше число на друге й результат помістити в комірку В1, у комірку В1 слід увести формулу =А1/В1 і натиснути Enter.

Уведення формули можна виконати інакше, не вводячи посилань на комірки, а вказуючи на них мишею (під час роботи зі сенсорним екраном — виконуючи одинарний дотик): до комірки, у якій мас бути розміщено результат обчислень за формулою, ввести .знак рівності (=)<^> клацнути лівою кнопкою миші комірку А1 <=> у комірці з'явиться адреса першої комірки формуливвести знак операції ділення "/" ^ клацнути на комірці В1 ^ у комірці з'явиться адреса другої комірки ^ натиснути Enter.

У всіх табличних процесорах можливе подання адрес комірок у форматі з нумеруванням як рядків (англ, row), так і стовпців (англ. column) (наприклад, формули матимуть вигляд = RC[-2]-R[-l]C[-l] ). Перехід до такого подання показано на рис. 8.3.

Застосування складних формул продемонструємо на прикладі (рис. 8.4). Нехай потрібно обчислити вартість виконання певних робіт.

У стовпці D таблиці зазначено час (у годинах), витрачений на виконання роботи, у стовпці Е - вартість однієї години роботи, а в стовпці F — проміжну суму, яку треба сплатити за роботу. У комірці F6 потрібно показати загальїі}' вартість усіх робіт. Для цього в коміркз' слід записати таку формулу: = F3+F4+F5.

Для обчислення податку на додану вартість отриману суму слід помножити на 0,15 і результат помістити в комірку: F7: =F6*0,15.

Для обчислення кінцевої суми, яка підлягає оплаті (наприклад, у комірці F8) треба спочатку отримати проміжні суми, а потім результат помножити на 1,15. Формула матиме такий вигляд: =(F3+F4+F5)*1,15.

Звичайно, можна було б додати вміст комірок Е6 і Е8. Для додавання кількох чисел можна також використовувати функцію суми 8иМ( ), тоді формула матиме такий вигляд: =8ит(ЕЗ:Е5)*1,15.

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

Для звернення до значення, що міститься в комірці, розташованій на іншому робочому аркуші, потрібно вказати ім’я цього аркуша разом із адресою відповідної комірки. Наприклад, для звертання до комірки Б6 на робочому аркуші SheetЗ потрібно ввести формулу: =81іееіЗ!В6.

Для обчислення суми вмісту групи комірок не потрібно називати (перелічувати) в формулі окремі комірки або вводити діапазон у форматі F3:F5. Досить виокремити всю групу та надати їй ім’я.

Надалі це ім’я можна буде використовувати в формулах. Щоб надати ім’я групі комірок, виконуємо таке (для MS Office 2003):

меню Вставка (Insert) відкрити підмеию Ім’я (Name)

^ викликати директиву Надати => у полі введення діалогового вікна Надання імені (Define Name) вказати ім’я цієї групи (у це вікно виведено список уже наданих групових імен, які розташовані на аркуші) *=> натиснути Додати (Адд)^с^>ОК.

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

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

Потім потрібно викликати з мсшо Правка (Edit) директиву Спеціальна вставка (PasteSpecial), а відтак у діалоговому вікні натиснути на кнопку Вставити зв’язок (PasteLink) (рис. 8.5).

У програмах Excel і Libre Office Calc можна ввести посилання і на комірку_у яка розташована в іншій таблиці (іншому файлі).

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

Процедура надання імен групам комірок у MS Office 2007-2010 та у Libre Office Calc простіша — досить виділити діапазон і у вікні адреси комірки розкрити список імен, ввести у вікно адреси ім'я групи (рис. 8.6).

У Libre Office Calc ім'я може бути набране тільки латиницею Ім'я групи мас починатися з літери та містити не більше 255 символів.

Не допускається використання пробілів. Ім’я групи не мас збігатися з адресами комірок (А1, В6 тощо).

Якщо таблиця містить заголовки рядків і стовпців, то їх також можна використовувати як імена цих діапазонів. Для цього потрібно: виокремити сусідні рядки (стовпці), включаючи перші комірки, де розташовані імена меню *=> меню Вставка (Insert) & відкрити підменю Ім’я (Name)^с? викликати директиву Створити (Create) & у діалоговому вікні, яке відкрилося, вказати місце розташування імен (у першій чи останній комірці рядка чи стовпця) ОК.

Для редагування функцій потрібно:

двічі клацнути лівою кнопкою миші комірку, в якій міститься функція •=> в комірці, яка містить результат обчислення, або в Рядку формул ср відредагувати °РЕпіег

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

Якщо адрес}' комірки записати у формул}⁷, наприклад так: А4, або діапазону комірок так: А8:В20, то при копіюванні або перенесенні формули в іншу комірку цю адресу буде змінено — номер стовпців збільшено або зменшено на стільки, на скільки стовпців перенесено формул}⁷, і так само буде змінено номери рядків.

Адреси комірок (і діапазонів комірок), які змінюються при копіюванні (перенесенні) формул, називаються відносними.

Якщо потрібно, щоб при копіюванні формул посилання не змінювалися, їх записують так: $А$4, $А$8:$В$20.

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

Абсолютним с й адресування з використанням імен.

Інколи потрібно, щоб при копіюванні формули змінювалися тільки значення стовпця або рядка. У такому випадку символ "$" розташовують лише перед номером рядка або літерою стовпця, наприклад так: А$29 або $А29. Такі записи адрес є мішаними.

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

Іноді потрібно в комірки певного діапазону (наприклад, А5:А25) ввести значення, кожне наступне з яких більше за попереднє на певне значення ("крок", або різниця).

Для цього слід у комірку А5 ввести перше значення, а в комірку А6 - формулу =А5+а, де а - значення різниці. Після цього досить скопіювати вміст комірки Аб у комірки А7:А25 і вони заповняться необхідною послідовністю чисел.

Виконуємо

Проаналізуйте рис. 8.4 і його опис у тексті. Створіть ЕТ. Які посилання доцільно використовувати в формулах, записаних у комірках ¥&-¥& Чому?

Модифікуйте створену за попереднім завданням ЕТ таким чином, щоб значення податку на додану вартість можна було оперативно

змінювати, вводячи з клавіатури.

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

Підказка: додати стовпчик (комірки G2-G5) із назвою "ПДВ робіт", для зручності використайте абсолютне посилання на комірку зі значенням коефіцієнта для ПДВ.

Проаналізуйте рис. 8.5 і його опис у тексті. Як називається група комірок, що відповідає діапазону А2—А10? Яким чином ці комірки були заповнені значеннями? Як можна назвати числа, розміщені в

комірках All та ВИ?

Створіть ЕТ, подану на рис. 8.6. Модифікуйте таблицю таким чином: збільшіть кількість рядків до 20; змініть формули у стовпчику А так, щоб можна було отримувати послідовності чисел, які віпшзняються не на 1, а на довільну величину (крок).

Знайдіть у Інтерпеті пояснення особливостей форматів файлів *.ods і *.csv. Занотуйте основні відмінності між форматами файлів. Збережіть створену електпонт' тяблтппо R гЬопмятях. ТЛО R1 спізняються

від того, в якому її подано

Виконайте, якщо можливо, пересилання телефонної книги з мобільного телефону на комп’ютер (у файл *.csv) і відкрийте цей файл текстовим редактором Блокнот, текстовим процесором і табличним процесором. Зробіть висновки.

Перевіряємо себе

1. Назвіть основні формати збереження ЕТ табличного процесора Microsoft Excel 2010.

2.    Для чого призначено формат *.xml? Знайдіть у Довідці Excel 2010 необхідні відомості.

3.    Для чого призначено формати *.xlt та *.xls?

4.    До яких програмних засобів можливий експорт електронних

документів, створених у Excel 2010 ?

5.    Які відмінності між Шаблонами текстового процесора,

презентаційиої системи і табличного процесора? Чим вони зумовлені?

Знайдіть у Довідці Excel 2010 відомості щодо форматування та властивостей ЕТ, створених у застосунку Excel 2010, які не

зберігаються у файлах інших форматів.

7.    У яких випадках доцільно використовувати різні види посилань у

формулах? Наведіть приклади й перевірте їх у таблиці.

8.    Яким чином можна надати ім’я несуміжним коміркам? Знайдіть у

Довідці Excel 2010 необхідні відомості.

9.    Проаналізуйте рис. 8.5 і його опис у тексті. Коли доцільно використовувати кожен із видів вставлення?

8.2. Призначення й використання основних математичних (Ьункцій табличного ппопесопа

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

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

 

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

Аргумент функції може займати одну комірку чи розміщуватися б групі комірок. Є аргументи різних типів: число, текст, логічне значеній (TRUE та FALSE), масиви, значення помилки (наприклад #N/A) абс посилання на комірку. У кожному окремому випадку потрібне використовувати відповідний тип аргументу. Константи, формули абс функції також використовуються як аргументи.

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

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

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

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

Використання вікна Вставка функції полегшує введення функцій під час створення формул, які містять функції, та допомагає вставити правильну формулу й потрібні аргументи (рис. 8.8).

Для введения функції слід:

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

користуючись коротким описом та поясненнями, які наводяться під полями аргументів ^ ОК.

Для редагування функцій:

 

Деякі математичні функції електронних таблиць

№	Назва функції	Опис
1	2	3
1	ABS0	Повертає абсолютне значення числа
2	CEILING0 ОКРУГЛТО	Заокруглює число до найближчого цілого і кратного вказаному значенню
3	COSQ	Повертає косинус числа
4	DEGREES0 ГРАДУСНО	Перетворює радіани на градуси

5	EXPO	Повертає число е, піднесене до вказаного степеня
6	FACTO	Повертає факторіал числа
7	FLOORO ОКРУГЛВНИЗО	Заокруглює число до меншого за модулем, у напрямку нуля
8	GCDO НОД0	Повертає найбільший спільний дільник
9	INTO	Заокруглює число до найближчого меншого цілого
10	LCMO HOK o	Повертає найменше спільне кратне
11	MODO OCTATO	Повертає остач}' від ділення
12	PIQ	Повертає число я
13	POWERO СТЕПЕНЬО	Повертає число, піднесене до степеня
14	RADIANSO РАДИАН Ы0	Перетворює градуси на радіани
15	RANDO СЛЧИСО	Повертає випадкове число в інтервалі від 0 до 1
16	RANDBETWEENO С ЛУЧМЕЖДУ 0	Повертає випадкове число в указаному інтервалі
17	ROUNDO ОКРУГЛО	Заокруглює число до вказаної кількості знаків
18	ROUNDDOWNO ОКРУГЛВНИЗО	Заокруглює число до меншого, у напрямку нуля
19	CEILINGO ОКРУГЛ BBEPXO	Заокруглює число вгору, у напрямку від нуля
20	SIGNO 3HAK0	Повертає знак числа (1, якщо число більше нуля, 0, якщо число 0, -1, якщо число менше нуля)
21	SINO	Повертає синус кута (значення кута подається в радіанах)
22	SQRTO КОРЕНЬО	Повертає невід’ємне значення квадратного кореня
23	sumo СУММО	Підеумовз'є аргументи
24	SUMIFO СУММЕСЛИО	Підсумовз'є вміст комірок, визначених за вказаною умовою
25	SUMIFSO	Підсумовує вміст комірок у діапазоні, який відповідає кільком умовам
26	SUMPRODUCTO	Повертає суму добутків відповідних елементів

	СУММПРОИЗВ0	масиву
27	SUMSQ0 СУММКВО	Повертає суму квадратів аргументів
28	TAN0	Повертає тангенс числа
29	TRUNC0 ОТБР0	Вида.чяс дробову частину числа

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

Досить часто доводиться розв’язувати задачі, подібні до такої.

Приклад. Доставили 105 л пального. Його потрібно розмістити у каністрах, ємність кожної з яких становить 20 л. Скільки потрібно каністр?

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

Тому, якщо деякий вантаж можна розмістити порціями, не більшими за певну (одне вантажомісце, у задачі, наведеній як приклад — каністра для пального), то навіть для порції вантажу, маса якої значно менша від визначеної для вантажомісця, треба надати окреме вантажомісце, тобто для визначення кількості вантажомісць потрібно виконати "заокруглення вгору" (ROUNDUP).

Застосування деяких формул до чисел показано на рис. 8.9. До чисел першого рядка застосовано формули, назви яких подано в стовпці F. Формули застосовано в рядках 2...8.

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

товару від постачальника. Потрібно знайти загальну кількість товару і його вартість, якщо облік постачання товару в натуральному вимірі ведеться на різних аркушах: Пост_1, комірки В2:Е9; Пост_2, комірки В2:Е9; Пост_3, комірки В4:Е12, а ціни зберігаються в комірках Пост_1ІАЮ, Пост_2!АЮ, Пост_ЗІАЮ.

Вираз:

=SUM(SUM(nocT_l!B2:E9)*nocT_l!Al0;SUM(nocT_2!B2:E9)*nocT_2!Al 0;8иМ(Пост_3!В4:Еі2)*Пост_3!Аі0) поверне значення загальної вартості товару від трьох постачальників.

Особливим випадком аргументу є масив даних.

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

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

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

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

Можна написати й власні формули, що застосовуються до діапазонів комірок, результатом обчислення яких буде діапазон комірок. Наприклад, =F4:F9-G4:G9.

Для введення подібних формул (формул масиву): виокремите діапазон комірок, що jмають містити результати обчислення формули масиву (розмір виокремленого діапазону мас відповідати кількості значень, що створюються формулою) & натисніть клавішу F2 введіть потрібну формулу, вказуючи посилання на діапазони комірок, що jмають використовуватися в обчисленнях завершіть уведення формули натисканнями сполучення клавіш Ctrl+Shift+Enter (а не натисканням кнопки ОКу підменю вибору даних!).

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

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

Перевіряємо себе

Які типи значень можуть бути операндами математичних

функцій?

2.    Які математичні функції можна застосз'вати до числа -1?

3.    Чим відрізняються Грошовий і Фінансовий формати подання

чисел?

Чи с масивом іменована труна комірок, у яких містяться як числові значення, так і текст¹'

•У яких випадках попільно використовувати автоматичне заповнення комірок?

Виконуємо

Знайдіть (використовз'їочи Довідкз⁷ Microsoft Excel або довідковз⁷ систему іншого табличного процесора, з яким ви працюєте) у табл. 8.1 з⁷сі функції, які не потребзчоть операндів.

Знайдіть (використовз'їочи Довідкз⁷) в табл. 8.1 з^тсі ф зик ції, які потребують тільки одного операнда.

Знайдіть (використовз'їочи Довідкз⁷) ^в табл. 8.1 з⁷сі фзгнкції, які потребзчоть тільки двох операндів.

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

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

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

7. Створіть ЕТ для обчислень значень фзшкції у = 2 х'² - 5 х + Юв інтервалі значень х від -10 до 10 з кроком 1. Для створення масивз⁷ значень х використайте автоматичне заповнення комірок. Як зробити так, щоб крок послідовності (різницю) можна бз⁷ло змінювати без редагування формз'ли?

8.3. Призначення й використання основних логічних функцій табличного процесора

Для опрацювання даних із вибором способу опрацювання використовують логічні вирази, тобто формули, які описують перевірку умови (або кількох умов). Значень, яких можуть набувати такі вирази, лише два: істинне або хибне (англ. true або false), так або ні, 1 або 0. Наприклад, вираз: С1> 10 матиме значення true, якщо в комірці СІ міститиметься число більше 10, і false в усіх інших випадках.

Логічні вирази використовують для визначення дій, які виконуються після перевірки істинності певної умови, наприклад, формула =IF(G7<>0;F7/G7;"na нуль ділити не можна"), яку записано у комірку Н7, означає, що вміст комірки F7 буде поділено на вміст комірки G7, якщо та містить нснульове значення або не с порожньою, результат обчислення буде записаний в комірку Н7, інакше в комірку Н7 запишеться текст "на нуль ділити не можна".

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

 

 

заповнено крім умови обидва місця для результатів, отримуємо структуру розгалуження першого типу:    "якщо

умова — то оператори 1 — інакше — оператори2 — все" (рис. 8.10), тобто повне розгалуження.

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

використання вкладення.

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

=Щ8иМ(Пі5:М15)<10;$Е$17;Щ8иМ(В15:Мі5)<50;$Е$18;ІЕ(8ЦМ(П

15:М15)<100;$Е$20))).

У комірках В15:М15 містяться значення параметра (наприклад, оцінки, отримані протягом семестру), а в комірках Е17, Е18, Е20 - назви груп, до яких належать накопичені значення.

Створення таблиць, призначених для опрацювання масивів даних, отриманих для груп іменованих об’єктів (наприклад, розташованих у різішх містах філій банку), може спростити застосування функції 8ЦМІЕ8, яка виконує додавання значень із використанням певної умови.

Приклад 2. Нехай у стовпці С розташовані значення прибутку філій установи, а у стовпці В — назви міст, у яких вони розташовані. Формула =8иМІЕ8(В4:В30;"Черкаси";С4:С30) поверне суму прибутків тільки тих філій, які розташовані в м. Черкаси.

Приклад 3. Нехай п учнів складали 10 тестів, відповідь на кожен з яких можна оцінити від 0 до 10 балів. Треба створити ЕТ для визначення результату тестування кожного учня з використанням оцінок "недостатньо", "задовільно", "добре", "відмінно", якщо відомо, що ці оцінки виставляють при сумах набраних балів: до ЗО, від 31 до 50, від 51 до 70, від 71 до 100 відповідно.

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

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

Формулу (рис. 8.12)

=ПЧ8иМ(В4:К4)<30;$0$4;Щ8иМ(В4:К4)<50;$0$5;Щ8ЦМ(В4:К4)<70;$0$

6;$0$7)))

можна подати у вигляді опису алгоритму :

якщо 81)М(В4:К4)<30 то оцінка = $0$4 інакше якщо 8иМ(В4:К4)<50 то оцінка = $0$5 інакше якщо 8иМ(В4:К4)<70 то оцінка = $0$6 інакше назва інтервалу = $0$7

 

 

» Як можна розширити список учнів (рис. 8.12)?

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

інтервалу?

Чому у формулі жодного разу не використано значення нижньої межі інтервалів?

►Яку оцінку "поставить" алгоритм, якщо значення у стовпчику L перевищуватимуть 100? Що треба зробити, щоб уникнути такої помилки?

Виконуємо

Апробуйте описати на словах і побудувати графічне подання алгоритму, описаного формулою

=IF(SUM(D15:M15)<10;$E$17;IF(SUM(D15:M15)<50;$E$18;IF(SUM(D 15:М15)<100;$Е$20))).

Перепишіть алгоритм, передбачивши надання назві інтервал}' значення без використання посилання на комірку. Яка частина таблиці після цього перестане бути необхідною?

Створіть таблищо для обчислення найменшого спільного кратного двох чисел.

Створіть таблицю для обчислення найбільшого спільного дільника двох чисел.

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

8.4. Призначення й використання основних статистичних функцій табличного процесора

Опрацювання великих наборів даних і отримання на основі їх аналізу відомостей щодо об’єкта або сукупності об’єктів - одна з основних задач науки статистики.

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

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

Статистичне опрацювання отриманих даних мас відповісти на

такі запитання.

"Яке середнє значення розміру деталі?"

"У яких межах змінюється цей розмір?"

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

"З якою надійністю можна гарантувати, що розмір деталі, взятої довільно з партії, міститься в певних межах¹?"

"Скільки деталей певного розміру є в партії?"

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

Тому для подібних досліджень використов\лоть методи, які в ЕТ описано у вигляді статистичних функцій. Статистичні функції ЕТ забезпечзлоть можливість опрацювання реззшьтатів фізичних, хімічних експериментів, соціальних досліджень. У резз'льтаті такого опрацювання великих масивів однотипних даних можна знайти взаємозв’язок між величинами, прогиозз'вати значення даних у залежностях, тобто визначити тенденцію рОЗВИТКЗ' певного процесу.

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

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

AVERAGE(4hcho1; число2;...), де число 1, число2, ... — цс від 1 до 955 яптументів, для яких обчислюється середнє.

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

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

Для обчислення найбільшого (максимального) значення даних, що містяться в діапазоні комірок, слугує функція МАКС (англ. МАХ).

Функція має такий вигляд:

МАХ(число1;число2;...) де число 1, число2, ... — від 1 до ЗО чисел, серед яких визначається максимальне значення.

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

* Піл час иоботи з масивами (Ьункшя опоаньовуе тільки числа.

Приклад. У таблиці в діапазоні комірок А1А6 міститься послідовність чисел {4; 5; 2; 3; 4; 5; 2; 3]. Визначити максимальне значення числа в цій послідовності. Для цього створюємо формулу = MAKC(Al А6), яка повертає число 5.

Для обчислення мінімального значення з певного діапазону значень використовують функцію МИН (англ. MIN). Синтаксис функції аналогічний синтаксису функції МАКС.

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

U

НАИБОЛЬШИИ (аіггл. LARGE). Функція визначає ї-тс найбільше значення з множини даних, наприклад, функцію можна застосувати для визначення першого, другого й третього місць серед учнів класу, які брали участь у змаганнях.

Функція мас такий вигляд:

НАИБОЛЬШИЩмасив; і), де масив - діапазон даних, серед яких потрібно визначити і-те найбільше значення; і — позиція в діапазоні даних.

* Різницю між значеннями, що повертають функції МАКС і

 

У вибірці (наборі значень) мода — це значення, що найбільш часто трапляється;

медіана - це значення, рівновіддалене від максимального і мінімального значень;

середнє - це середнє арифметичне значення.

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

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

Для обчислення медіани використовують функцію МЕДИАНА (англ. MEDIAN).

Функція мас вигляд MEDIAN (число 1;число2;...), де число 1; число2; ...— від 1 до 255 аргументів, для яких потрібно знайти медіану.

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

Приклади: МЕДИАНА(1; 2; 3; 4; 5) дорівнює З, МЕДИАНА(1; 2; 3; 4; 5; 6) дорівіпоє 3,5, середнє арифметичне 3 і 4.

Мода обчислюється за допомогою функції МОДА (англ. MODE)

Якщо набір даних не містить однакових даних, то функція МОДА повертає значення помилки #Н/Д (немає даних).

Приклад. MODE ({5,6; 4; 4; 3; 2; 4}) дорівіпоє 4.

Подібним чином працює і функція РАНГ (англ. RANK), але за її допомогою визначають місце (ранг) значення у масиві значень. Повертає числове значення, яке є місцем аргументу в списку чисел. (Якщо впорядкувати список, то ранг числа дорівнюватиме його позиції.)

Функція мас вигляд В.А]>іК(число; посилання; порядок), де число -число, ранг якого потрібно визначити; посилання — посилання на список чисел. Нечислові значення в посиланні ігноруються; порядок — параметр, що визначає, як розподіляються порядкові номери.

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

Функція RANK призначає числам, що повторюються, однаковий ранг. Це впливає на ранги наступних чисел. Наприклад, якщо у списку цілих чисел, відсортованих за зростанням, число 10 трапляється двічі й мас ранг 5, то число 11 матиме ранг 7 і жодноліу числу не буде призначено ранг 6.

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

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

 

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

Для опису подібішх задач та їх дослідження засобами електронних таблиць використовують функцію ЧАСТОТА (англ. FREQUENCY).

Функція має такий вигляд:    FREQUENCY(Macue_da7mx;

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

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

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

 

називають ‘відносною частотою події . Якщо подію визначити як "знаходження значення з масиву даних у певному діапазоні", то сума всіх відносних частот подій завжди дорівнюватиме 1, або 100 %.

На рис. 8.14 подано приклад аналізу даних, які розміщені в комірках A2:W2. Треба було визначити, як ці значення розподіляються в інтервалах

0...20; 21...40; 41...60; 61...80; 81... 100, значення верхніх меж яких занесене в комірки А4:А8. Оскільки наперед б}'ло відомо, що значення, менші за 0, і значення, більші за 100, неможливі, то для масиву результатів виділено п’ять комірок — В4:В8. Для комірки В4 засобами ЕТ позначено комірки, що впливають на її значення: це діапазон даних A2:W2 і комірка А4, в якій міститься значення верхньої межі першого інтервалу. Для контролю та наступного обчислсішя відносних частот у комірці В9 уведено формулу =SUM(B4:B8). Значення, яке повертатиме ця формула, має дорівнювати кількості елементів масиву даних, розміщеного в комірках A2:W2.

 

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

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

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

Перевіряємо себе

•    Що є об’єктом дослідження статистики?

2.    У процесі розв’язування яких задач виникає потреба застосувати статистичні формули?

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

Який параметр масиву чисел називають частотою? Відносною

частотою?

5. Чому дорівнює сума частот значень для вибірки? Сума відносних частот?

•    Які параметри значень, отриманих у результаті вимірювань, е суттєвими для визначення якості процесу вимірювання?

Як можна використати ЕТ для опрацювання результатів соціологічного опитування, для якого використано анкет}', що містила п’ять запитань, на кожне з яких можна було відповісти, увівши число 0...10? Запропонуйте варіант, який дасть можливість отримати дані не тільки щодо всього опитуваїшя, а й за кожним запитанням окремо.

Виконуємо

* Відомо, що деякі деталі (наприклад, поршневі пальці та поршні автомобільних двигунів) добирають за групами, які позначають певним кольором. Створіть ЕТ, вхідними даними для якої будуть масиви значень діаметрів поршневих пальців, значення граничних розмірів для груп, кольори грзчі. Кількість груп — пе більше чотирьох. У результаті маємо отримати для кожної деталі (поршневого пальця) номер грзши та колір, яким деталь слід позначити.

Порада: дані (начебто результати вимірювання з використанням мікрометра) можна згенерувати, застосувавши формул}' =2()чЧЮиКВ(КАКВ()*0,1;3),    а    умови    записати    як

=IF(A3<=20,025;1;IF(A3<=20,05;2;IF(A3<=20,075;3;IF(A3<=20,1;4)))). Можна використати умовне форматування.

Визначте, як розташовано дані в ЕТ. представленій на

рис. 8.14. Відтворіть електронну таблицю.

Визначте, як розташовано дані в ЕТ, представленій на

 

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

Форматування комірок і написів у комірках полягає у наданні певного кольору, накреслення літер, заливки тла, кольору й товщини меж комірок.

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

Умовне форматування — це визначення певного формату

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

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

Табличний процесор Excel 2007 і його наступні версії забезпечують, порівняно з попередніми версіями, ширші можливості Умовного форматування та більш зручні засоби його налаштування (рис. 8.1о).

Для визначення умов форматування вмісту комірки або блоку комірок потрібно:

виокремити комірки, для формату яких обчислюватиметься значення логічного виразу & викликати зі стрічкового меню Головне групи Стилі <=> натиснути кнопку Умовне форматування & вибрати кнопку необхідного логічного виразу у відповідне поле ввести значення, адресу комірки або вираз, складений із адрес комірок та значень.

Позиції меню:    Створити правило..., Правила очищення...,

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

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

Щоб позначити червоним кольором тексту значення температур, що зберігаються у комірках В2...В10, залежно від того, чи перебуває за цієї температури і нормального тиску деяка речовина в рідкому стані, необхідно в певну комірку (наприклад, А2) ввести значення температури плавлення (наприклад, для води 0 °С, бензол}' 5,45 °С, камфори 178,5 °С). Потім виконати дії в певній послідовності (рис. 8.15).

Можливі й інші варіанти умов і форматів, які реалізуються при набутті логічним виразом значення ^4іістина" (true).

Перевіряємо себе

1.    Що таке умовне форматування''

2.    Як накласти дві-три умови в процесі умовного

форматування?

3.    Що таке стиль?

4.    Як створити новий стиль?

5.    Як відб}'ваеться імпортування стилів?

6.    Що таке автоформат?

7.    Які параметри автоформату можна виставляти додатково'

Які типи значень можна використовувати при описанні

правил (логічних виразів) умовного форматування?

9.    Які з перших шести правил (умов) можна застосувати до

значень текстового типу (рис. 8.15)?

10.    Яке правило можна записати так: а = х, якщо а — стала, а х — значення в комірці? До яких типів даних можна застосовувати це

правило?

11.    Яке правило можна записати так: а < х, якщо а — стала, а х — значення в комірці? До яких типів даних можна застосовувати це

правило?

12.    Яке правило можна записати так: х < Ь, якщо Ь - стала, а .г — значення в комірці? До яких типів даних можна застосовувати це

правило?

13.    Яке правило можна записати так: а < х < Ь, якщо аі Ь — сталі, а х — значення в комірці? До яких типів даних можна застосовувати це

правило?

Чому не завжди доцільно застосовувати правило "Дорівнює..." до значень типу числового типу (дійсних чисел)? Що потрібно зробити, щоб, наприклад, порівняти правильність розв’язку задачі, отриманого }' вигляді числа, з еталоннним?

Виконуємо

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

Для діапазону комірок АЗ:С8 визначте правило умовного форматування: "якщо значення, введене в комірку, менше за значення, яке зберігається в комірці А2, залити комірку блідо-червоним

кольором".

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

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

і вік учня. До баскетбольної секції приймають дітей зростом не менше 160 см. Вік не може перевищувати 13 років. Вихідні дані для заповнення таблиці підберіть самостійно (не менше 10 рядків). Таблиця має позначати зеленим кольором прізвища дітей, які можуть бути прийняті до секції.

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

8.6. Створення та налагодження діаграм

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

Спеціальні види діаграм, тривимірні діаграми. Налагодження діаграм.

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

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

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

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

 

діаграму,    на    якій

відображаються    одночасно

мінімальне, максимальне і середнє значення деякої величніш.

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

"пелюсткову" діаграму (рис. 8.16).

Рис. 8.16. Вибір типу діаграми

 

на окремому аркуші або розташувати як впроваджений об’єкт па артілі даних. Крім цього, діаграму можна опублікувати на веб-сторінці. Щоб створити діаграму, потрібно спочатку ввести для неї дані на аркуші. Після цього, виділивши ці дані, слід запустити Майстер діаграм для покрокового створення діаграми, під час якого вибираються її тип і різні параметри, або за допомогою панелі інструментів Діаграма створити базову діаграму', яку згодом можна буде змінити.

 

створена, і в разі зміни даних автоматично оновлюється.

Нижче наведено назви та описання елементів.

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

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

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

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

Імена рядів даних діаграми. Microsoft Excel також використовує заголовки стовпчиків або рядків як імена рядів даних. Імена рядів даних показані в легенді.

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

Наприклад, якщо навести курсор на легенду, з’явиться підказка, яка містить слово «Легенда».

Аркуш діаграми. Аркуш книги, який містить лише діаграму. Відмінність від попередніх версій полягає в тому, що не обов’язково дотримуватися послідовності дій.

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

Якщо затримати курсор миші па елементі діаграми, .з'явиться підказка з назвою цього елемента.

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

Процедура створення діаграми для Microsoft Excel 2003 і попередніх могла бути виконана з використанням Майстра діаграм (ChartWizard) покроково, за п’ять кроків.

На рис. 8.17 показано послідовність побудови діаграми для Microsoft Excel 2007-2013.

Після кроку, на якому на аркуші з’являється порожнє полотно діаграми, у вікні Excel 2007-2010 з’являється контекстний інструмент "Робота з діаграмами", що містить три стрічки "Конструктор", "Макет", "Формат". Інструменти роботи з діаграмами в Excel 2007-2010 прості й зрозумілі (рис.8.18).

Можливим є й інший спосіб створення діаграми, який показано на рис. 8.17 (кроки 5, 6 і наступні). Після того як на аркуші з’явилось полотно діаграми, можна, не користуючись стрічковими меню, викликати натисканням правої кнопки миші на полотні діаграми, через команду Вибрати дані... контекстного мегао викликати вікно Вибір джерела даних (кроки 5 і 6 на рис. 8.17 і рис. 8.18).

У вікні Вибір джерела даних є такі об'єкти.

Список Елементи легенди (ряди). Відображає список імен існуючих рядів даних.

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

Кнопка Видалити. Вилучає вибраний ряд даних із діаграми.

Додавання та вилучення рядів даних на діаграмі не впливає на дані на аркуші.

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

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

Якщо діаграма міститься на окремому робочому аркз'ші, то для її актуалізації можна використовувати команду' Вибрати дані (New Data) з меню Вставлення (Insert). У діалоговому вікні цієї команди потрібно вказати область таблиці, яку додано. Для цього або виділіть цю область, або вкажіть її адресу, попередньо створивши новий ряд (кроки 5 і 6 на рис. 8.17).

Щоб додати текст у будь-який елемент діаграми, клацніть на ньому правою кнопкою миші, виберіть команду' Змінити текст, а потім уведіть текст.

Особливості деяких діаграм.

Точкова діаграма - єдина діаграма, для якої осі X і У можна вважати осями декартової системи координат і будувати графіки гбунки.ій так само, як на папері.

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

На рис. 8.19 показано графік функції у = 0.5я²- 10, побудований як точкова діаграма.

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

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

Перевіряємо себе

1.    Які основні елементи містить стандартний макет діаграми?

2.    Як викликати певний тип діаграми?

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

Чи можна змінювати тип діаграми після зв’язування її з

даними? Коли це можливо й доцільно?

•Діаграма якого типу вимагає двох наборів даних?

•Яким чином можна додати дані на діаграму?

7.    У яких випадках потрібно користуватися контекстним меню

редагування елементів діаграми?

8.    Як додати підписи (значення) до зображень елементів ряду і коли

це доцільно робити?

9.    Як редагувати текстові елементи діаграми?

Виконуємо

Побудуйте графік функції у — 2х²- Зх - 20 для значень

- 10 < х < 10.

Визначте з графіка приблизні значення коренів рівняння 2Х²- Зх - 20 - 0. Перевірте обчислеїшям.

Створіть ЕТ і діаграми, подані на рис. 8.20.

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

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

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

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

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

Можна виділити декілька робочих аркушів, тилі самим задавши пошук потрібного вмісту' відразу в кількох аркушах. При заданні зразка шуканого вмісту⁷ можна використовувати будь-які літери, цифри та спеціальні символи. Крім цього, є такі символи підстановки:

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

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

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

У меню Правка виконати команду Замінити або скористатися сполученням клавіш Сігі+Н & у поле Знайти ввести послідовність символів, які потрібно знайти (зразок) (рис. 8.22. А) «=> у полі Замінити на вказати послідовність символів для заміни.

За потреби можна зазначити інші параметри пошуку -натиснути Параметри (рис. 8.22. В) для продовження пошуку натиснути кнопку Знайти далі => для виконання заміни натиснути кнопку Замінити.

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

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

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

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

Програма виконує декілька варіантів сортування. Найпростішим варіантом сортування є впорядкування за зростанням. Цей варіант сортування встановлено за замовчуванням.

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

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

Сортування даних за кількома полями.

Засоби Excel дають змогу одночасно сортувати записи за трьома полями. Послідовність сортування полів вибирається в діалоговому вікні Сортування діапазону в списках, що розкриваються: Сортувати за, Потім за, В останню чергу за.

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

Перемикач Ідентифікз'вати діапазон даних за дає змогу ідентифікувати дані за підписами або позначеннями стовпців аркуша.

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

Фільтрування використовується у роботі з великими таблицями і дає змогу бачити не всю таблицю, а лише ту її частин}', яка висвітлюється за певними ознаками (критеріями).

Щоб вибрати критерії, за якими здійснюється фільтрування, потрібно викопати такі дії: виокремити діапазон (або всю таблицю), скористатись командою ДаніУФільтр/Автофільтр. Відразу в кожній комірці верхнього рядка з’явиться кнопка списку.

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

Упорядкування за зростанням (за спаданням).

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

•    порожні комірки завжди вміщуються у кінець відсортованого списку (як у варіанті сортування за зростанням, так і у варіанті за спаданням);

•    числові типи даних сортуються від найменшого від’ємного до найбільшого додатного (у варіанті сортування за спаданням - навпаки);

•    текстові типи даних сортуються познаково зліва направо);

•текстові дані сортуються за таким порядком: спочатку цифри, потім

пробіл та символи цифрових клавіш верхнього регістра, і тільки після цього літери у алфавітному порядку;

•під час сортування логічних значень значення ХИБНІСТЬ (False, 0) ставиться перед значенням ІСТИНА (True, 1).

Сортування за кількома критеріями відбору.

Розглянемо такий варіант: виокремити діапазон комірок, де слід виконати сортування; виконати команд}' меню Дані / Сортування; у діалоговому вікні Сортування даних у полях Сортувати за і Потім за вказати стовпці в такому порядку, який потрібен (спочатку за стовпцем "Вік", а потім за стовпцем "Особа"). Натиснути ОК.

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

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

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

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

Таблиця 8.2

Організація структур даних

№	Особливості стовпця	Форма подання даних
1	Подібні елементи розташовувати в одному стовпці	Упорядковувати дані слід таким чином, щоб в усіх рядках подібні елементи містилися в одному й тому ж стовпці
2	Діапазон розташовується окремо	Слід залишати принаймні один порожній стовпець і один порожній рядок між діапазоном споріднених даних і рештою даних на аркуші. Тоді ЕТ ефективніше виявляє та виокремлює діапазон під час сортування, фільтрування або вставлення автоматичних проміжних підсумків
3	Важливі дані мають міститися над діапазоном або під ним	Слід уникати розташування важливих даних ліворуч або праворуч від діапазону, тому що при фільтруванні діапазону вони можуть стати прихованими
4	Рядки та стовпці мають бути відображені	Перш ніж вносити зміни до діапазону, слід переконатися, що приховані рядки та стовпці відображено. Якщо в діапазоні не відображаються деякі рядки або стовпці, можливе помилкове видалення даних
Формат даних
0	Використовувати форматовані підписи стовпців	Рекомендується створювати в першому рядку діапазону даних підписи стовпців. Ці підписи використовуються в ЕТ для створення звітів, пошуку та впорядкування даних. Для підписів стовпців слід використовувати шрифт, вирівнювання, тло, межу або регістр, відмінні від параметрів форматування, які мають дані в діапазоні. Перш ніж уводити підписи стовпців, слід установити для комірок текстовий формат
6	Використовувати межі комірок	Якщо потрібно відокремити підписи від даних, бажано виконати це за допомогою меж, а не вставляти порожні рядки або риски між підписами та рядками даних
7	Уникати використання порожніх рядків і стовпців	Для того щоб ЕТ оптимально знаходила й виокремлювала діапазони споріднених даних, не слід вставляти в діапазон порожні рядки або стовпці
8	Не застосовувати пробіли перед даними або після них	Зайві пробіли на початку або в кінці комірки заважають правильному сортуванню або пошуку. Для зсування тексту в комірці слід застосовувати відступи

9

Розширення форматів даних і формул

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

Зверніть увагу! Фільтрування від сортування відрізняється тим,

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

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

Найзручнішим інструментом фільтрування с Автофільтр. Цей інструмент містить такі фільтри:

•    Перші 10 — для відображення перших 10 записів даних;

•    Сортування за зростанням (сортування за спаданням) — відображає всі записи, впорядковані за зростанням (за спаданням);

•Умова ... — інструмент для відображення на екрані записів, що задовольняють певні критерії, які можна задати в діалоговому вікні Користувацький автофільтр. Тут можна задавати як прості, так і складені умови фільтрування.

Розглянемо застосування автофільтра на прикладі. Нехай у списк}' треба показати всіх осіб віком до 12 років.

Слід зробити таке: виділити стовпці з даними виконати

команду меню Дані/Фільтр/Автофільтр <=> після виконання команди праворуч у назвах виділених стовпців з'являться кнопки зі стрілками & натиснути на кнопку стовпця з назвою "Вік" і обрати з переліку можливих варіантів фільтрування Улюва у діалоговому вікні Користувацький автофільтр у полі Вік обрати умову Менше або дорівнює, у полі поряд вказати "12".

Розширений фільтр. Якщо потрібно відібрати дані за складених умов відбору, зручніше застосЗ’Вати інструмент для фільтрування "розширений фільтр".

Умови можуть бути такими:

1)    декілька умов для одного стовпця даних;

2)    одна умова для кількох стовпців даних;

3)    одна умова для одного стовпця й інша для іншого;

4)    набір умов для кількох стовпців;

5)    пошук за умовою у вигляді формули.

Для використання функцій фільтрування і сортування таблиця має мати структуру, описану в табл. 8.2.

Перевіряємо себе

1.    Чому при застосуванні фільтрування та сортування дані мають бути введені з суворим дотриманням певних правил?

2.    Чим сортз'вапня відрізняється від фільтрування?

У латинському та українському алфавітах є однакові на вигляд літери. Що буде зі списком адрес шкіл України після сортування, якщо у назві міста Львів .літера "і" інколи набиратиметься як латинська,

а інколи - як українська? Перевірте

4. До яких наслідків може призвести порушення структури,

рекомендованої в табл. 8.2, п. 1?

5. До яких наслідків може призвести порушення структури,

рекомендованої в табл. 8.2, п. 8?

6.    Які інструменти для фільтрування даних вам відомі?

7.    З яких етапів складається створення розширеного фільтра?

8.    Які можливості надає розширений фільтр?

9.    Наведіть приклади доцільності використання автофільтра,

фільтра коршлувача, розширеного фільтра.

Чим подібні розділи var і type програми мовою Паскаль

табл. 8.2

Виконуємо

З веб-порталу фірми КРІ-Сервіс завантажте прайс-лист http://web-portal.kpiservice.com.ua/kpi_new/price/pricc_opt.zip.

2.    Відкрийте аркуш "Вспомогательное оборудование" і встановіть автофільтр (Дані/Фільтр/Автофільтр).

3.    За допомогою встановленого фільтра виберіть із прайс-листа джерела безперебійного живлення back pro (кнопка списку поля Тип).

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

8.8. Підсумки й проміжні підсумки

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

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

Якщо електронну таблицю вже створено з дотриманням правил, викладених у табл. 8.2, то цс означає, що отримано новий об’єкт, який можна назвати базою даних (БД).

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

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

Застосовуючи фільтр до даних, розміщених у комірках ЕТ, заповнених даними за правилами, викладеними в табл. 8.2, тобто до бази даних, здійснюємо запит до бази даних.

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

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

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

Наприклад, у БД містяться дані щодо поживності основних продуктів, які ми будемо споживати, їх вартості. Якщо ми робимо запит: "Показати, які й скільки продуктів мені потрібно взяти з собою в похід на 10 днів, якщо передбачаються витрати енергії 3500 ккал щодня. Загальна маса продуктів не більше 3 кг, мінімальна порція кожного продукту не менше 50 г, продукти мають бути досить різноманітними, а загальна вартість їх не мас перевищувати 2000 гри", то ми до інформації з БД застосовуємо досить складне правило, сформульоване з урахуванням декількох вимог. Отже, запит теж містить певну інформацію.

Запит до БД, створеної в ЕТ, можна сформулювати й застосувати, використовуючи фільтри та інші засоби (табл. 8.3).

Для числового наповнення БД можна створити список із проміжними підсумками, використовуючи команд}' Підсумки в меню Дані. Якщо список із проміжними підсумками вже створений, його можна модифікувати, редагуючи формулу з функцією Проміжні підсумки.

Синтаксис:

ПРОМІЖНІ.ПІДСУМКИ(номер_функцїї;посилання;посилапня;...)

Номер_функції — це число від 1 до 11, яке вказує, яку функцію використовувати для обчислення підсумків у списку. Посилання 1; Посилання 2; — від 1 до 29 інтервалів або посилань, для яких підводяться підсумки.

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

Функція ПРОМІЖНІ.ПІДСУМКИ ігнорує всі приховані рядки, які стають такими в результаті фільтрування списку. Це важливо, оскільки стає можливим підвести підсумки тільки для тих даних, що містяться в рядках, які відтворюються після фільтрування списку.

Таблиця 8.3

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

Номер_ функції	Функція	Опис функції
1	СРЗНАЧ (AVERAGE)	Повертає середнє (арифметичне) своїх аргументів
2	СЧЕТ (COUNT)	Підраховує кількість чисел у списку аргументів. Використовується для отримання кількості числових значень у інтервалах або масивах значень
3	СЧЕТЗ	Підраховує кількість непорожніх значень у списку аргументів. Використовується для підрахунку кількості комірок з даними в інтервалі або масиві
4	МАКС	Повертає найбільше значеїшя з набору значень
5	МИН	Повертає найменше значення з набору значень

6	ПРОИЗВЕЛ	Перемножує числа, вказані як аргументи, і повертає їх добз'ток
9	СУММ	Додає всі числа в інтервалі комірок

Те, що ПРОМІЖНІ.ПІДСУМКИ, призначені для діапазону комірок, за наявності сформованого для цього ж діапазону комірок фільтра, обчислюються тільки для тих комірок, значення яких "пройшли фільтр", дуже важливо. Ми отримуємо змогу сформувати запит до БД, який можна описати так: "Покажи результат опрацювання значень у комірках стовпця, розташованих у рядках, дані в яких відповідають певним об’єктам".

Приклад. Нехай дані щодо об’єктів класу "зернові культури" розташовані в комірках (полях) рядків, які відповідають стовпцям із назвами "Назва культури", "Урожайність, центнерів з гектара", "Країна", "Рік". Тоді за запитом "Покажи середню врожайність для "Назва культури" = "Кукурудза" і "Країна" = "СПІА" отримаємо певне значення. Для такого ж запиту, але з "Назва культури" = "ячмінь" отримаємо інше значення. Змінюючи фільтр по країні, отримуємо інформацію щодо врожайності зернових у різних країнах протягом кількох років. Порівнюючи отримані значення між собою, доходимо висновку щодо доцільності вирощування певної культури в конкретній країні.

Подібним чином можна виконати й інші дослідження, застосовуючи спільно засоби фільтрування даних і опрацювання результатів з використанням функції ПРОМІЖНІ.ПІДСУМКИ.

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

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

Перевіряємо себе

1.    Як відображаються властивості об’єкта в електронній таблиці?

2.    У яких випадках доцільно застосовувати попереднє сортування

(фільтр) до даних, які є аргументами проміжних підсумків?

3.    Яку інформацію дасть застосування до даних обчислення проміжних підсумків функції з номером 3 (табл. 8.3)? Чи можна такий запит використати задля контролю правильності формування зведеної таблиці?

4.    Що потрібно зробити насамперед, щоб створити зведену таблищо?

5.    Чому відповідають і що містять поля зведеної таблиці?

6.    Чому потрібно суворо дотримуватися однаковості форматів даних, які є аргументами проміжних підсумків?

Виконуємо

Створіть таблицю, як}' можна вважати базою даних щодо успішності в навчанні учнів класу.

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

8.9. Надання значень параметрам книги й аркуша

У Microsoft Excel можна створити Стандартний шаблон книги і Стандартний шаблон аркуша.

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

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

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

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

J Створення шаблону:

у меню Файл вибрати команду Зберегти як *=> у списку Тип файла вибрати пункт Шаблон & у рядку Ім'я файла набрати ім'я (при створенні шаблону книги для використання за замовчуванням ввести текст Книга, при створенні спеціального шаблону книги ввести довільне припустиме ім'я файла; при створенні шаблону аркуша для використання за замовчуванням ввести текст Аркуш,

при створенні спеціального шаблону аркуша ввести довільне припустиме ім'я файла & натиснути Зберегти.

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

Щоб зображення першої сторінки шаблону було показане в полі Перегляд діалогового вікна Шаблони, потрібно вибрати в меню Файл команду Властивості <=> відкрити вкладку Документ <=> установити прапорець Створити малюнок для попереднього перегляду.

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

 

Подібним чином можна змінювати властивості ЕТ й у Microsoft Excel 2010 і 2013, Libre Office Calk (рис. 8.23).

Завдання

1.У таблиці подано дані про площу та населення деяких країн Європи. Ввести у відповідні комірки формз'ли для обчислення:

1)    загальної площі та загальної кількості населення;

2)    густоти населення в кожній країні (осіб/км²);

3)    частки, яку становить населення кожної з цих країн щодо

загальної кількості населення в усіх країнах.

Країна	Площа, км2	Населення, мли осіб	Густота населення	Відсоток
Україна	603700	46,3
Франція	547030	67,3
Іспанія	504782	40,5
Швеція	449964	9,1

А. Створити таолищо для опрацювання результатів вимірювання

густини речовини методом обмірювання і зважування.

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

4.    Створити таблицю для опрацювання результатів лабораторної

роботи "Визначення питомої теплоємності речовини".

5.    Створити таблицю для опрацювання результатів лабораторної роботи "Вимірювання опору провідника за допомогою амперметра і

вольтметра".

 

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

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

відсоткове співвідношення оцінок в групі.

2. Створити електронну таблицю для опрацювання результатів лабораторної роботи "Вивчення теплового балансу за умов змішування води різної температури". Передбачити, що в таблиці опрацьовуватимуться результати, отримані в п’яти експериментах. Таблиця мас обчислювати: теоретичне (за рівнянням теплового балансу) значення

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

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

Підказка. Для зображень сніжинки і сонечка використайте шрифт Wingdings, шістнадцяткові коди 00AF або 0054 (сніжинка) і шрифт Wingdings 2, шістнадцятковий код 00F0 (сонечко). Колір падайте з використанням умовного форматування.

Завдання

Створити таблицю "Річки Європи", використовуючи такі дані: протяжність (км) і площа басейну (тис. кв. км): Волга — 3688 і 1350; Дніпро - 2285 і 504000; Дністер - 1 362 і 72 100; Дунай - 2850 і 817; Рейн -1330 і 224; Ельба - 1150 і 148; Вісла - 1090 і 198; Луара - 1020 і 120;

Урал - 2530 і 220; Дон - 1870 і 422; Сена - 780 і 79; Темза - 340 і 15.

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

стовпці. ^ Визначити найдовшу і иайкоротшу річки. ^ Підрахувати сумарщ' й середню площу басейнів річок, сумарну та середню протяжність великих річок Європи (виконати з використанням

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

За допомогою умовного форматування виокремити назви річок,

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

Завдання

1.    Із веб-порталу фірми КРІ-Сервіс завантажити прайс-лист http://web-portal.kpiscrvicc.com.ua/kpi_new/price/price_opt.zip (або інший подібний документ за вказівкою вчителя).

2.    Відкрити аркуш "Вспомогательное оборудование" і встановити автофільтр (Дані/Фільтр/Автофільтр).

3.    За допомогою встановленого фільтра вибрати з прайс-листа джерела безперебійного живлення back pro (кнопка списку поля Тип).

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

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

6.    Надрукувати аркуш на мережевому принтері (або па віртуальному принтері, який створює документ формату *.pdf).

7.    Закпити ЕТ.

Словничок

 

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

Змішані адреси комірок	- адреси комірок (і діапазонів комірок), які лише частково змінюються при копіюванні формул.
Масив	- сукупність однотипних величин, кожен елемент якої нумерований.
Медіана	- значення (у вибірці (наборі значень)), рівновідцалене від найбільшого та найменшого значень.
Мода	- значення (у вибірці (наборі значень)), що трапляється найчастіше.
Розмах вибірки	- різниця між значеннями, що повертають функції МАКС і МИН для вибірки.
Середнє	- середнє арифметичне значення у вибірці (наборі значень).
Сортування	- упорядковування даїшх за одним або кількома заданими критеріями.
Стандартний шаблон аркуша	- Аркуш.хіі, який створюється для зміни використовуваного за замовчуванням формату новостворюваних порожніх аркушів.
Стандартний шаблон книги	- Книга.xlt, яка створюється для зміни використовуваного за замовчуванням формату новостворюваних порожніх книг.
Умовне форматування	— встановлення певного формату подання вмісту комірок залежно від значень числових величин, що містяться в цих комірках, значень логічних виразів, для обчислення яких використовуються дані інших комірок.

 

Це матеріал з підручника Інформатика 8 клас (поглиблений рівень) Гуржій