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

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

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

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

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

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

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

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

Н— додавання; віднімання; * - множення; / - ділення.

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

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

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

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

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

Уведення формули молена виконати інакше, не вводячи посилань на комірки, а вказуючи на них мишею (при роботі зі сенсорним екраном -виконуючи одинарний дотик):

до комірки, в якій має бути розміщено результат обчислень за формулою, ввести знак рівності (=) => клацнути лівою кнопкою миші комірку А1 р у комірці з’явиться адреса першої комірки формулир увести знак операції ділення “/” => клацнути на комірці В1 => у комірці з'явиться адреса другої комірки => натиснути Enter.

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

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

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

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

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

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

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

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

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

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

Потім потрібно викликати з меню Правка (Edit) директиву Спеціальна вставка (Paste Special), далі в діалоговому вікні натиснути на кнопку Вставити зв’язок (Paste Link) (рис. 7.5).

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

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

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

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

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

 

 

Процедура надання імен групам комірок у MS Office 2007-2010 й у LibreOfficeCalc простіша - досить виділити діапазон і у вікні адреси комірки розкрити список імен, увести в вікно адреси ім’я групи (рис. 7.6).

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

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

виокремити сусідні рядки (стовпці), включаючи перші комірки, де розташовані імена меню => меню Вставка (Insert) => відкрити підменю Ім'я (Name) => викликати директиву Створити (Create) => у діалоговому вікні, що відкрилося, потрібно вказати місцерозташування імен (у першій чи останній комірці рядка чи стовпця) => ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

Виконуємо

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

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

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

Підказка: додати стовпчик (комірки С2-<І5) із назвою “ПДВ робіт”. Для зручності використайте абсолютне посилання на комірку зі значенням коефіцієнта для ПДВ.

Проаналізуйте рис. 7.5 і його опис у тексті. Як називається група комірок, що відповідає діапазону А2-А10? Яким чином ці комірки заповнено значеннями? Як можна назвати числа, розміщено в комірках All та ВИ?

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

Знайдіть у Інтернеті пояснення щодо особливостей форматів файлів *.ods і *.csv. Занотуйте основні відмінності між форматами файлів. Збережіть створену електронна тяб.питтто r Фопмятях. нто шппіянятоться від того, в якому її подано.

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

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

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

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

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

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

5. Які відмінності між Шаблонами текстового процесора, презентаційпої системи і табличного процесора? Чим вони зумовлені?

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

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

8. Яким чином можна надати ім’я несуміжним коміркам? Знайдіть у Довідці Excel 2010 необхідні відомості.

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

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

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

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

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

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

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

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

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

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

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

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

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

клацнути ім'я функції (під полем Виберіть функцію буде показано синтаксис цієї функції та її короткий опис) =» двічі клацнути ім'я функції => функцію та її аргументи буде відображено в вікні Майстра Аргументи функції => вказати правильні аргументи, користуючись коротким описом та поясненнями, які наводяться під полями аргументів => ОК.

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

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

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

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

Таблиця 7.1

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

№	Назва функції	Опис
1	ABS()	Повертає абсолютне значення числа
2	CEILING0 ОКРУГЛТ0	Заокруглює число до найближчого цілого і кратного вказаному значенню
3	COS()	Повертає косинус числа
4	DEGREES() ГРАДУ СЫ()	Перетворює радіани на градуси
5	ЕХР()	Повертає число е, піднесене до вказаного степеня
6	FACT()	Повертає факторіал числа
7	FLOOR() ОКРУГЛВНИЗО	Заокруглює число до меншого за модулем, у напрямку нуля
8	GCD() нодо	Повертає найбільший спільний дільник
9	INTO	Заокруглює число до найближчого меншого цілого
10	LCM() иок o	Повертає найменше спільне кратне

№	Назва функції	Опис
11	MOD() остато	Повертає остачу від ділення
12	ріо	Повертає число р
13	POWER0 СТЕПЕНЬО	Повертає число, піднесене до степеня
14	RADIANS0 РАДИАНЫ0	Перетворює градуси на радіани
15	RAND() СЛЧИСО	Повертає випадкове число в інтервалі від 0 до 1
16	RANDBETWEEN0 С ЛУ ЧМЕЖДУ ()	Повертає випадкове число в зазначеному інтервалі
17	ROUND() ОКРУГЛО	Заокруглює число до вказаної кількості знаків
18	ROUNDDOWN0 ОКРУГЛВНИЗО	Заокруглює число до меншого, у напрямку нуля
19	CEILING0 ОКРУГЛВВЕРХ0	Заокруглює число вгору, у напрямку від нуля
20	SIGN() ЗНАКО	Повертає знак числа (1, якщо число більше нуля, 0, якщо число 0, -1, якщо число менше нуля)
21	SIN()	Повертає синус кута (значення кута подається в радіанах)
22	SQRT() КОРЕНЬО	Повертає невід’ємне значення квадратного кореня
23	SUM0 СУММО	Підсумовує аргументи
24	SUMIF0 СУММЕСЛЩ)	Підсумовує вміст комірок, визначених за вказаною умовою
25	SUMIFS0	Підсумовує вміст комірок у діапазоні, який відповідає кільком умовам
26	SUMPRODUCT0 СУММПРОИЗВ0	Повертає суму добутків відповідних елементів масиву
27	SUMSQ0 СУММКВО	Повертає суму квадратів аргументів
28	TAN()	Повертає тангенс числа
29	TRUNC() ОТБР()	Видаляє дробову частину числа

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

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

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

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

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

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

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

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

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

Вираз: =8иМ(8иМ(Пост_1!В2:Е9)*Пост_1!А10;8иМ(По-ст_2!В2:Е9)*Пост_2!А10;8иМ(Пост_3!В4:Е12)*Пост_3!А10) поверне значення загальної вартості товару від трьох постачальників.

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

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

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

розміщують у блоці суміжних комірок.

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

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

Можна написати й власні формули, що застосовуються до діапазонів комірок, результатом обчислення яких буде діапазон комірок. Наприклад: =Г4:Г9—04:09.

Для введення подібних формул (формул масиву):

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

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

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

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

 

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

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

Чим відрізняються Грошовий і Фінансовий формати подання чисел?

Чи є масивом іменована група комірок, у яких містяться як числові значення, так і текст?

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

У першому році нашої ери 1 г покладено в банк під 3 % річних. Скільки золота за такою угодою банк мав би видати сьогодні? Дехто оформив у банку кредит па 120000 гри па 10 років під ЗО % річних із помісячною виплатою (тіло кредиту виплачується однаковими внесками, відсоток сплачується з суми, яка була у використанні на місяць, що закінчився). Який прибуток має банк з такої угоди?

Виконуємо

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

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

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

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

Запишіть із використанням математичних функцій ABS і COS вираз у = -^CQSX. Значення х уводимо в окрему комірку.

X

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

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

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

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

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

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

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

Очевидно, що якщо в функції IF(yMOBa; результаті; результат2) заповнені крім умови обидва місця для результатів, отримуємо структуру розгалуження першого типу - “якщо умова - то оператори 1 - інакше -оператори2” (рис. 7.10), тобто повне розгалуження.

Якщо заповнено тільки місце для умови і першого результату, то отримуємо структуру “розгалуження” другого типу - неповне розгалуження (рис. 7.11).

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

Приклад 1. Якщо необхідно створити таблицю для відображення накопичення значень певного параметра для кількох об’єктів, а потім визначити відповідність накопичених сум певним критеріям, можна скористатись виразом: =Щ8иМ(П15:М15)<Ю;§Е$17;Щ8иМ(О15:М15)<50;$Е$18;Щ8иМ (П15:М15)<100;$Е$20))).

 

 

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

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

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

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

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

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

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

=Щ8иМ(В4:К4)<30;§О$4;Щ8иМ(В4:К4)<50;$О$5;Щ8иМ(В4:К4) <70;$О$6;$О$7))) можна подати у вигляді опису алгоритму :

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

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

З якою метою замість явного подання (приклад 3) назв інтервалів у формулі використано посилання? Навіщо ці посилання мають форму абсолютних посилань?

Яка частина (частини) формл'ли описує коитєбії віднесення значення до певного інтервалу?

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

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

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

6. Яку оцінку “поставить” алгоритм, якщо значення у стовпці L перевищуватимуть 100? Що треба зробити, для того щоб уникнути такої помилки'

Виконуємо

Спробуйте описати словесно й побудувати графічне подання алгоритму, який описано формулою =IF(SUM(D15:M15)<10;$E$17;IF(SUM(D15:M15)<50;$E$18;IF(SUM (D15:M15)<100;$E$20))).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функція має такий вигляд: АУЕКАОЕ(число1; чпсло2;...), де число 1, число2, ... - від 1 до 255 аргументів, для яких обчислюється середнє.

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

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

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

Функція має такий вигляд: МАХ(число1;число2;...), де числої, число2, ... - від 1 до ЗО чисел, серед яких визначається максимальне значення.

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

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

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

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

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

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

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

Різницю між значеннями, що повертають функції МАКС і МИН для вибірки, називають “розмахом вибірки”.

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

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

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

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

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

Функція має вигляд: MEDIAN (число!;число2;...), де число!; число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), але за її допомогою визначають місце (ранг) значення у масиві значень. Повертає числове значення, яке є місцем аргументу в списку чисел. (Якщо впорядкувати список, то ранг числа дорівнюватиме його позиції.)

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

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

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

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

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

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

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

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

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

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

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

На рис. 7.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.

Значення в комірці В4, як показано стрілками, впливає на значення в комірках C4=ROUND(B4/$B$9*100;1) і B9=SUM(B4:B8).

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

Значення в комірці В4, як показано стрілками, впливає на значення в комірках С4=КОІШВ(В4/$В$9*100;1) і В9=8иМ(В4:В8).

 

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

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

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

 

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

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

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

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

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

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

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

Виконуємо

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

Порада: дані (начебто результати вимірювання з використанням мікрометра) можна згенерувати, застосувавши формулу =20+КОиїГО(НАМВ()*0,1;3), а умови записати як =ЩАЗ<=20,025;1;ЩАЗ<=20,05;2;ЩАЗ<=20,075;3 ;ІЕ(АЗ<=20,1;4)))). Можна використати умовне форматування.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нижче наведено елементи діаграми:

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

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

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

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

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

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

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

Аркуш діаграми. Аркуш книги, який містить лише діаграму.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Послідовність дій подано на рис. 7.21.

Щоб додати елемент, який не передбачено стандартним макетом діаграми, натисніть кнопку Додати фігуру на панелі інструментів Макет.

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

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

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

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

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

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

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

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

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

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

Виконуємо

Побудуйте графік функції у=2х²-20 для значень -10 < х <10. ^

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

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

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

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

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

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

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

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

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

При використанні як пошуку, так і заміни можна зазначити, чи має враховуватись точне написання шуканого вмісту (малі або великі

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

Для виконання пошуку:

У меню Правка (Редагування) => виконати команду Знайти (або натиснути сполучення клавіш Ctrl+F) => у вікні діалогу Знайти в полі Знайти зазначити послідовність символів, які потрібно відшукати (зразок) (рис. 7.22. А) => у разі потреби можна зазначити інші параметри пошуку - натиснути Параметри (рис. 7.22. В) =» для завершення пошуку натиснути кнопку Закрити.

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

Сполучення клавіш, які дозволяють продовжувати пошук і без виведення на екран вікна діалогу Знайти: 8кір:+Р4 - пошук наступного входження; СігІ+8кі(і+Р4 - пошук попереднього входження.

Клацання миші на вкладці Замінити дозволяє перейти з вікна Знайти у вікно Замінити.

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

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

кнопку Знайти далі =» для виконання заміни натиснути кнопку Замінити.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Розглянемо такий варіант сортування:

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

• виконати команду меню Дані / Сортування;

• у діалоговому вікні Сортування даних у полях Сортувати за і Потім за вказати стовпці в тому порядку, який потрібен (спочатку за стовпцем “Вік”, а потім за стовпцем “Особа”). Натиснути ОК.

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

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

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

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

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

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

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

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

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

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

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

Слід зробити таке:

- виділити стовпці з даними;

- виконати команду меню Дані/Фільтр/Автофільтр;

- після виконання команди праворуч у назвах виділених стовпців з’являться кнопки зі стрілками ;

- натиснути на кнопку | стовпця з назвою “Вік” та обрати з переліку можливих варіантів фільтрування Умова;

- у діалоговому вікні Користувацький автофільтр у полі Вік обрати умову Менше чи дорівнює, у полі поряд вказати “12”;

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

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

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

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

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

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

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

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

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

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

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

9

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

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

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

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

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

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

після сортування, якщо у назві міста Львів літера і набиратиметься інколи як латинська, а інколи - як українська? Перевірте.

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

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

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

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

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

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

Що спільного між розділами var і type програми мовою Паскаль ' і табл. 7.2?

Виконуємо

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

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

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

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

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

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

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

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

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

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

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

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

Це не означає, що інформація “виникає з нічого” - інформація виникає як результат спільного опрацювання запиту і даних. Частина інформації, яку ми отримуємо в результаті залиту, вже міститься у БД, частину подаємо тоді, коли формулюємо умови залиту (табл. 7.3).

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

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

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

Синтаксис:

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

Номер функції - число від 1 до 11, яке вказує, яку функцію використовувати для обчислення підсумків у списку.

Посилання 1; Посилання 2; - від 1 до 29 інтервалів або посилань, для яких підводяться підсумки.

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

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

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

Таблиця 7.3

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

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

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

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

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

Для створення зведеної таблиці:

виокремте будь-яку комірку в діапазоні => у меню Дані оберіть команду Зведена таблиця => натисніть кнопку Далі, щоб погодитися з настройками, запропонованими на першому кроці => перевірте, чи правильно зазначено діапазон даних => натисніть кнопку Далі; => натисніть кнопку Макет. (У MS Excel 2007 перехід до макета відбувається автоматично) => у діалоговому вікні Макет перетягніть кнопку Account (Рахувок) зі списку праворуч до області рядків =» перетягніть кнопку Amount (Сума) зі списку праворуч до області Дані; => натисніть кнопку ОК => вкажіть, де потрібно розмістити результат - на новому аркуші або в певному розділі на створеному аркуші, і натисніть кнопку Готово.

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

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

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

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

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

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

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

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

Виконуємо

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

Таблиця має забезпечувати виконання таких запитів:

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

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

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

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

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

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

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

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

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

У полі Папка можна вказати папку, в якій має бути збережений шаблон. Якщо створюється шаблон книги або аркуша для використання за замовчуванням, то йому відповідно надається ім’я Kmira.xlt або Аркуш.xlt і він зберігається в папці XLStart, яка завантажується за замовчуванням та відображається при запуску Microsoft Excel. Microsoft Excel використовуватиме цей шаблон для створення книги або для вставки нових аркушів. Звичайне розташування папки XLStart: C:\Program Files\Microsoft Office\Officell\XLStart Для створення спеціального шаблону книги або аркуша необхідно вибрати папку Шаблони, звичайне розташування якої:

C:\Documents and Settings\iM*H користувача\Арр1ісаНоп Data\ Microsoft\Templates

Щоб зображення першої сторінки шаблону було показане в полі Перегляд діалогового вікна Шаблони (Загальні шаблони, область завдань Створити книгу), необхідно

вибрати у меню Файл команду Властивості => відкрити вкладку Документ => установити прапорець Створити малюнок для попереднього перегляду (рис. 7.23).

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

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

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

Слід зауважити, що ЕТ Excel, на відміну від текстового процесора Word, для увімкнення відображення у вигляді макету сторінки, вимагає обов’язкового встановлення в ОС хоча б одного принтера.

Особливість друкування таблиць полягає в тому, що вони не завжди можуть бути розміщеними на стандартному аркуші. Тому при друкуванні таблиць передбачено можливість додаткового автоматичного масштабу-вання (рис. 7.25). Воно застосовується в тому випадку, якщо на аркуш не поміщається невелика частина таблиці (кілька стовпчців або рядків).

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

Як має бути оформлена таблиця - джерело даних, щоб за нею можна було створити зведену таблицю?

Для чого використовується Макет зведеної таблиці?

Як потрібно змінити зведену таблицю, якщо:

- до початкового діапазону були додані нові записи?

- поточні початкові дані були змінені?

- не підходить структура зведеної таблиці?

- потрібні інші функції для обчислень?

- деякі дані полів мають бути приховані?

4. Як побудувати діаграму на основі зведеної таблиці?

Чим відрізняється створення шаблону книги і шаблону аркуша? ¹

2. Відформатуйте заголовок таблиці: виділіть блок комірок А1:Б1 і введіть команду контекстного меню Формат комірок; на вкладці Вирівнювання виберіть опції: по горизонталі - по центру виділення, по вертикалі - по верхньому краю; виділіть текст жирним шрифтом.

3. Викличте контекстне меню та виберіть команду Формат клітинок; на вкладці Вирівнювання задайте параметри: по горизонталі - за значенням, по вертикалі - по нижньому краю, переносити за словами - встановити прапорець, орієнтація - горизонтальний текст (за замовчуванням). Скопіюйте форматування на всі комірки, які передбачається заповнити.

4. Надайте кожному прізвищу порядковий помер, використовуючи авто-заповнепня. Введіть дані.

5. Відобразіть рамку і внутрішні лінії таблиці: виділіть блок комірок із даними в правому нижньому куті таблиці.

6. Відформатуйте таблицю таким чином, щоб вона повністю розмістилася па аркуші формату А5 (альбомної орієнтації). Надрукуйте аркуш на мережному принтері (або на віртуальному принтері, який створює документ формату *.р<Щ.

7. Закрийте ЕТ.

Завдання

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

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

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

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

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

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

Підказка: Використати функцію ЧАСТОТА (ВЛЕС^иЕ^У) або функцію СЧЕТЕСЛИ (СОІШТІЕ)

2. Визначте моду і медіану набору даних.

Створити таблицю “Річки Європи”, використовуючи такі дані: довжина (км) і площа басейну (тис. кв. км): Волга - 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.

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

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

для допитливих

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

Для того щоб оцінити якість вимірювань у процесі опрацювання результатів вимірювань, часто використовують статистичну функцію “стандартне відхилення у вибірці” (СТАНДОТКЛОН, аигл.: вТПЕУ). За допомогою значення цієї функції можна оцінити, наскільки широко розкидані дані з граничними значеннями щодо середнього значення набору даних.

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

8ТИЕУ (число 1; число2;...), де числої, число2,... - від 1 до 255 значень. Аргументами функції можуть бути: числа, числові масиви або посилання на комірки з числовими даними.

Функція СТАНДОТКЛОН для обчислення використовує такий

 

 

вибірки; <х> - середнєарифметичне значення елементів вхідного масиву

даних ('СРЗНАЧ(число1,число2,...)^,; п - розмір вибірки (кількість даних у масиві). Тобто обчислюється середнєквадратичне значення відхилень значень елементів вхідного масиву даних від середнього арифметичного їх значення.

Полегшення введення даних

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

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

увести в комірки першого рядка назви стовпців;

натиснути будь-яку комірку списку й вибрати команду Дані/Форма - з’явиться вікно форми даних, у поля якої внесені значення комірок першого рядка списку;

натиснути кнопку Додати. В результаті у верхньому правому куті вікна з’явиться напис Новий запис;

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

натиснути кнопку Додати;

натиснути кнопку Закрити, коли всі записи внесені.

Увага: назви елементів полів, що вводяться, мають збігатися з наявними назвами (наприклад, не “25 січня”, а “25.01.00”), слід дотримуватися правил, поданих у табл. 7.2.

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

Вісь значень - вісь, мітки якої розташовані пропорційно до їх значень, координатна вісь

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

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

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

Область побудови діаграми - частина області діаграми, обмежена осями Основні лінії сітки - лінії, які можна додати до діаграми для поліпшення сприйняття й оцінювання відображуваних даних Ряд даних - пов’язані одна з одною точки даних, нанесені на діаграму Фільтр— засіб для відбору даних за певиою ознакою (ознаками)

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