2013-12-28
995
МАТЕМАТИЧНА ОБРОБКА ДАНИХ ЗА ДОПОМОГОЮ ЕЛЕКТРОННИХ ТАБЛИЦЬ
Для обробки інформації в Microsoft Excel існує потужний механізм формул.
Формула – сукупність операндів, сполучених між собою знаками математичних операцій та дужками. Формально ознакою формули є те, що першим символом, з якого починається формула, має бути символ =. Операндами формули можуть бути: числа (включаючи дату і час), текст, адреси комірок таблиці (посилання на комірки або діапазон комірок), функції.
Microsoft Excel має велику кількість математичних функцій. Основні з них:
1 арифметичні: додавання (+), віднімання (–), множення (*), піднесення до ступеня (^), обчислення кореня квадратного (КОРЕНЬ()), обчислення модуля (ABS());
2 тригонометричні: синус (SIN()), косинус (COS()), тангенс (TAN()), як операнд число p (ПИ);
3 логічні: більше (>), менше (<), дорівнює (=), не дорівнює (<>), не менше (=>), не більше (<=).
Формули вводяться та редагуються у відповідному рядку, який розташовано як правило нижче панелі інструментів (позначка 4 на рис. 6.1). Для роботи із формулами використовується майстер функцій, що викликається натисканням кнопки 
зліва біля рядка вводу та редагування формул, або командою Вставка / Функція.
|
|
|
7.1 Посилання на комірки
Під час обробки інформації за допомогою функцій найчастіше оперують не числовими значеннями, а з адресами комірок, в яких ці значення знаходяться. Такий підхід робить введені формули універсальними відносно початкових даних і дає можливість обчислювати різні дані не змінюючи формули, а лише замінюючи значення у відповідних комірках на які посилається формула.
Адреса комірки таблиці включає заголовок колонки (стовпчика) і номер рядка, і використовується у формулі як аргумент функції або операнд. Таке використання адреси називається посиланням. Розрізняють відносні, абсолютні та змішані посилання.
Відносні посилання характерні тим, що зі зміною адреси комірки (наприклад, в результаті додавання або вилучання рядка або стовпчика) автоматично змінюється і посилання у формулі, яка його використовує. Під час розповсюдження формули за допомогою маркеру заповнення посилання такого типу також змінюється. Наприклад, якщо розповсюджувати формулу на комірки рядка, то в посиланні змінюватиметься літера, що відповідає заголовку стовпчика, якщо ж розповсюджувати формулу на комірки стовпчика – змінюватиметься число, що позначає рядок у посиланні, під час розповсюдження формули на прямокутний діапазон комірок відповідним чином змінюватимуться і заголовок стовпчика і номер рядка.
Абсолютні посилання не змінюються під час зміни адреси комірки, або розповсюдження формули, що використовує це посилання. Ознакою абсолютних посилань є знак $, наприклад $G$7 є абсолютним посиланням на комірку, що знаходиться на перетині стовпця G, та рядка 7.
|
|
|
У змішаних посиланнях абсолютною є адреса стовпчика, а відносною – адреса рядка, або навпаки (наприклад, $G7 або G$7). В цьому випадку під час зміни адреси або розповсюдженні формули змінюється тільки відповідна частина посилання.
Для зміни типу посилання потрібно:
1 Встановити курсор на відповідне посилання у рядку вводу та редагування формули.
2 Циклічно натискати клавішу <F4> до відображення потрібного виду адресації.
У формулі можуть використовуватися посилання на діапазон комірок, наприклад посилання G7:G17 вказує на діапазон з десяти комірок у стовпчику G. Щоб адресувати прямокутний діапазон комірок, у посиланнях зазначають адресу лівої верхньої та правої нижньої комірок.
Якщо необхідно використовувати інформацію, що міститься у комірці іншого аркуша, треба крім номера рядка та найменування стовпчика вказати назву аркуша, в якому розташовано цю комірку. Наприклад, Лист2!G7 або для діапазону Лист2!G7:G17.
Якщо посилатися на комірки, що розташовані в іншому файлі (тобто в іншій книзі Microsoft Excel) потрібно додавати повне ім’я цього файлу (шлях до файлу та назву). Наприклад, D:\МО-12\Книга1.xls\Лист2!G7.
7.2 Функції Microsoft Excel
Функція (function) – це заздалегідь визначена формула, яка оперує з одним або декількома значеннями та повертає значення (одне або кілька). Багато з функцій Microsoft Excel є стислими варіантами формул, що часто використовуються.
Вони призначені для виконання складних операцій обробки даних інформації в Microsoft Excel. Кожній функції в Microsoft Excel привласнюється унікальне ім’я. Після імені у круглих дужках вказуються аргументи (або аргумент), над якими здійснюється операція, яку позначає функція. Пробіл між іменем функції та круглими дужками не допускається. Значення, які повертаються функціями як відповіді, називаються результатами.
Якщо при вводі функції ви забули поставити закриваючу дужку, Microsoft Excel виправить помилку після натиснення клавіші <Enter> в тих випадках, коли це безсумнівно зрозуміло, де повинна знаходитися дужка. Але на цю рису Microsoft Excel не варто покладатися, тому що це може привести до непередбачених наслідків. Рекомендується завжди ретельно перевіряти розміщення круглих дужок.
7.3 Вбудовані функції Microsoft Excel
Microsoft Excel має понад 400 вбудованих функцій, поділених на категорії, відповідно до галузей знань.
Ввести функцію у формулу модна вручну, за допомогою клавіатури або за допомогою спеціального програмного засобу – Майстра функцій. Майстер функцій викликається або за допомогою однойменної кнопки на панелі інструментів, або за допомогою команди Функція з меню Вставка. Виконання цієї команди здійснюється в два етапи (два кроки).
На першому кроці треба вибрати категорію та назву потрібної функції.
Після вибору певної функції у нижній частині діалогу з’являється стислий опис цієї функції. Після цього натискаючи на ОК, викликаємо діалог, за допомогою якого можна ввести параметри обраної функції. Допускається вкладеність функцій, тобто можна використовувати функції як аргументи для інших функцій. Наприклад, =COS(ABS(A19)).
Можна також використовувати посилання на коміри цілого діапазону в книзі. Ці посилання називаються об’ємними (3-D references). Припустимо, що Ви створили 13 листів в тій самій книзі: по одному для кожного місяця і ще один лист для підсумку за рік на початку книги. Якщо всі листи з місячними даними мають однакову структуру, для підсумовування цих даних на підсумковому листі можна використати формулу з об’ємним посиланням. Наприклад, формула: =СУММ(Лист2:Лист13!B5) складає всі значення, які містяться в комірках В5 на всіх листах від Лист2 до Лист13. Щоб побудувати цю об’ємну формулу, виконайте дії, що описані нижче.
|
|
|
1 В комірку В5 листа Лист1 введіть =СУММ(.
2 Клацніть на ярличку Лист2 і потім за допомогою кнопки прокрутки до наступного листа (що розташована ліворуч від ярличків листів виведіть на екран ярличок Лист13.
3 При натиснутій клавіші <Shift> клацніть на ярличку Лист13.
4 Виділіть комірку В5, потім введіть) і натисніть <Enter>.
Зверніть увагу: після щиглику на ярличку Лист13 при натиснутій клавіші <Shift> всі ярлички від Лист2 до Лист 13 стають білого кольору, показуючи тим самим, що вони виділені для включення до посилання, що створюється.
Під час роботи з об’ємними посиланнями можна використовувати наступні функції:
| СУММ (SUM) | СТАНДОТКЛОН (STDEV) |
| СЧЁТЗ (COUNTA) | СТАНДОТКЛОНП (STDEVP) |
| СРЗНАЧ (AVERAGE) | ДИСП (VAR) |
| МАКС (MAX) | ДИСПР (VARP) |
| МИН (MIN) | СЧЁТ (COUNT) |
| ПРОИЗВЕД (PRODUCT) |
Функції перетворення тексту
Функції даної категорії дозволяють замінювати символи на інші, визначати кількість текстових символів, подавати текстову інформацію у потрібному вигляді, відокремлювати певну кількість символів ліворуч або праворуч вказаного текстового фрагменту, виконувати перетворення регістру тексту та інше.
Наприклад: СЖПРОБЕЛЫ (Т1) або в англійській версії TRIM(T1) – ця функція вилучає всі пусті позиції у вказаному тексті, залишаючи лише одну пусту позицію між словами.
ЛЕВСИМВ (текст1, N) –ця функція відокремлює N символів ліворуч від вказаного текстового фрагменту, де текст1 – текстовий рядок, або посилання на клітинку, що містить фрагмент тексту.
ПРАВСИМВ (текст1, N) – символи будуть відокремлюватись праворуч.
СЦЕПИТЬ (текст1, текст2,...) CONCATENATE (текст1, текст2,...) – ця функція поєднує текстову інформацію з кількох фрагментів у один фрагмент. Якщо необхідно додавати певні текстові символи, наприклад, символ пустої позиції, то такі символи беруться у лапки („_”).
Функції дати та часу
Функції цієї категорії дозволяють перетворювати дату або час з текстового формату у чисельний або навпаки, виділяти потрібну частину інформації, автоматично визначати дату сеансу роботи з таблицею.
|
|
|
СЕГОДНЯ () – повертає системну дату, встановлену на комп’ютері, ця функція не має параметрів.
ГОД (дата) – відокремлює від повної дати рік.
МЕСЯЦ (дата) – відокремлює від повної дати місяць.
ДЕНЬ (дата) – відокремлює від повної дати день місяцю.
Функції категорії Логічні
Функція ЕСЛИ (арг1, арг2, арг3): перший аргумент – це певна умова, від виконання якої залежить остаточний результат. Якщо умова арг1 виконується, то результатом функції буде другий аргумент арг2, якщо умова не виконується, результатом функції буде третій аргумент арг3.
Наприклад, ЕСЛИ(С2>25,”надбавка”, „_”) – якщо число, що міститься у комірці С2 більше за 25, то у комірку, в якій внесено формулу буде внесено слово ”надбавка”, якщо менше, або дорівнює – знак підкреслення.
Функцію можна застосовувати безпосередньо вводячи відповідний вираз у рядку формул або можна використовувати діалогове вікно цієї функції, яке можна викликати за допомогою майстра функцій 
, Категорія – Логічні / функція ЕСЛИ(), а потім натиснути кнопку ОК. Можна знайти посилання на комірку, де введена певна умова.
Функція И (арг1, арг2,...) – результатом роботи цієї функції буде ИСТИНА (True), якщо всі умови (арг1, арг2,...) виконуються. Якщо хоча б одна з умов не виконується, результатом буде значення ЛОЖЬ (False). Функцію И можна використовувати, якщо задати як аргумент арг1 у функції ЕСЛИ.
Функція ИЛИ (арг1, арг2,...) може приймати лише два значення ИСТИНА або ЛОЖЬ. Результатом функції ИЛИ буде ИСТИНА, якщо хоча б одна з умов (арг1, арг2,...) виконується, якщо жодна з умов не виконується, результатом буде ЛОЖЬ.
Функція НЕ (арг) має лише один аргумент і може приймати два значення ИСТИНА або ЛОЖЬ. Якщо умова арг виконується, то результатом функції НЕ буде ЛОЖЬ, якщо умова арг не виконується, то результатом функції НЕ буде ИСТИНА.
Функцію ЕСЛИ можна застосовувати шляхом поступового вкладення однієї функції ЕСЛИ у середину іншої функції ЕСЛИ. У Microsoft Excel дозволяється лише сім рівнів вкладеності функцій однієї в іншій, наприклад, функція з чотирма рівнями вкладеності:
=ЕСЛИ(B1=1,ЕСЛИ(C1=1,ЕСЛИ(D1=1,ЕСЛИ(E1=1,ЕСЛИ(F1=1,1,0),0),0),0),0)
Функції И, ИЛИ, НЕ використовуються для формування умови, яка потім входить до функції ЕСЛИ.
Функції категорії Статистичні
Ці функції дозволяють проаналізувати певний масив чисельної інформації та знайти найбільше значення, найменше, середнє, а також стандартні величини математичної статистики.
Функція МАКС (діапазон1, діапазон2,...) – дозволяє знайти найбільше значення серед усіх вказаних діапазонів комірок.
Функція МИН (діапазон1, діапазон2,...) – дозволяє знайти найменше значення серед усіх вказаних діапазонів комірок.
Функція СРЗНАЧ (діапазон1, діапазон2,...) – дозволяє знайти середнє арифметичне значення серед усіх вказаних діапазонів комірок.
Імена комірок та діапазонів
У певних задачах зручно користуватись не адресою комірки або діапазону, а іменем. Microsoft Excel дозволяє привласнювати коміркам імена використовуючи латинську, російську або українську абетки, і надалі звертатися до них за цими іменами.
Щоб призначити найменування потрібно:
1 Виділити потрібну комірку або діапазон комірок.
2 Обрати команду Вставка / Ім’я / Привласнити.
3 В полі Ім’я діалогового вікна, що розкриється ввести потрібне ім’я та натиснути кнопку Додати.
4 Закрити діалогове вікно натисканням кнопки ОК.
Після цього вказане найменування може бути використано для посилання на цей діапазон або комірку у формула та функціях.
Ім’я має починатися з букви, може містити будь-які букви та цифри, а також знак підкреслення, знак питання, крапку. Адреса виділеної комірки або діапазону з’явиться у полі Формула цього вікна. Для вилучення імені слід розкрити перелік Ім’я діалогового вікна Привласнити / Ім’я, виділити потрібне ім’я та натиснути на кнопку Скасувати.
7.4 Користувацькі функції
Хоча Microsoft Excel містить велику кількість вбудованих функцій часто виникає потреба в розрахунках, для яких нема потрібної функції. В цих випадках користувач може написати власну – користувацьку функцію, або функцію визначену користувачем (userdefined function). Щоб створити таку функцію потрібно написати спеціальну процедуру Visual Basic, що називається процедурой-функцієй (function procedure). Така функція отримує інформацію з робочого аркушу, виконує обчислення а потім повертає результат до робочого аркушу.
Аналогічно вбудованим функціям користувацькі викликаються Microsoft Excel під час обчислення формул на робочому аркуші. Як правило результат, що повертається користувацькою функцією є числовим значенням, проте це може бути також текстове, логічне, помилкове значення або масив значень.
Особливістю користувацьких функцій є те, що на відміну від макросів за допомогою цих функцій не можна виконувати дії у робочій області. Наприклад не можна змінювати розміри вікон, стовпчиків, редагувати формулу в комірці або змінювати параметри шрифту, колір тощо. Через цю особливість функція не може бути записана під час виконання як макрос. Але завдяки цій рисі в користувацьких функціях можна використовувати менше ключових слів (команд), ніж в макросах, оскільки користувацьким функціям дозволено лише повертати значення у формули робочого аркуша.
Створення користувацької функції
В Microsoft Excel макроси та користувацькі функції створюються та зберігаються в модулях. Оскільки макроси та користувацькі функції не залежать від конкретного аркушу, їх можна виконувати для різних аркушів, а також можна об’єднати кілька макросів та користувацьких функцій в одному модулі та розглядати його як бібліотеку.
Створення користувацької функції виконується в два етапи.
Перший етап – створення нового модуля.
Для цього потрібно:
1 Виконати команди: Сервіс / Макрос / Редактор Visual Basic, в меню Вставка редактора Visual Basic оберіть команду Модуль.
2 Ліворуч внизу у вікні проекту замість стандартного імені модуля потрібно ввести користувацьке.
3 Подвійним щигликом на імені модуля у дереві каталогів вікна проекту переходимо до режиму вводу та редагування функції.
Другий етап – написання інструкцій мовою Visual Basic.
Написання функції починається із зарезервованого слова Function, після якого через пробіл їде назва функцій, а потім круглі дужки, в яких потрібно перелічити аргументи функції. Послідовність інструкцій функції завершується зарезервованими словами End Function.
Розглянемо написання інструкцій на прикладі функцій для обчислення знижки.
Function Знижка(кількість, ціна)
If кількість >= 100 Then
Знижка = кількість * ціна * 0.1
Else
Знижка = 0
End If
Знижка = Application.Round (Знижка, 2)
End Function
Збільшувати відступи рядків від лівого краю можна так само як і у текстовому редакторі за допомогою клавіші <Tab>. Такі відступи допомагають легше читати програму під час її відладки. З цією ж метою Microsoft Excel виділяє сова різними кольорами залежно від їх функції в інструкціях (наприклад зарезервовані слова команд Visual Basic, назви користувацьких змінних тощо).
З точки зору наочності та зрозумілості програми необхідними є також коментарі, які пише сам користувач, і які пояснюють що робить та або інша інструкція програми. Перед коментарями треба вставляти одинарні лапки (‘) для того, щоб програмі було зрозуміло що це вже не інструкція функції а лише пояснення. Microsoft Excel ігнорує все, що написано після цього знаку та виділяє іншим кольором. Коментарі можна розміщувати праворуч у рядку із вже написаною інструкцією або на окремих рядках між інструкціями, але і в цьому випадку написання коментарю потрібно починати з лапок.
Для того, щоб викликати написану у прикладі користувацьку функцію у формулі потрібно написати її ім’я із аргументами у круглих дужках, так само як і вбудовану функцію Microsoft Excel: Знижка (В6, С6), комірки В6 та С6 повинні містити відповідно кількість придбаного товару та ціну товару.
Макроси – це набір інструкцій, що завдають послідовність дії, які Microsoft Excel виконує автоматично. Макроси є комп’ютерними програмами, але вони не працюють автономно, а лише в середовищі Microsoft Excel. Макроси призначені для автоматизації трудомістких задач та тих, що часто виконуються.
Можливі два способи створення макросів:
1 Записати послідовність інструкцій у спеціальному аркуші – модулі за допомогою мови програмування Visual Basic for Application.
2 За допомогою команд меню записати послідовність дій під час їх виконання.
Мова програмування Visual Basic for Application є потужним засобом для написання макросів. З її допомогою можна створювати користувацькі функції та макроси (тобто писати інструкції) для багатьох додатків, розроблених фірмою Microsoft (Microsoft Excel, Microsoft Word, Microsoft Access, тощо).
Розглянемо процедуру створення макроса шляхом запису послідовності дій під час виконання:
1 Обрати в меню команди Сервіс / Макорс / Почати запис.
2 Excel видасть на екран діалогове вікно Запись макроса, в якому потрібно у відповідних елементах управління вказати: назву макроса; сполучення гарячих клавіш, натисканням яких можна запустити макрос на виконання; місце зберігання макросу; стислий коментар до макроса (якщо потрібно).
3 Натискаємо кнопку ОК.
Після виконання вказаних дії починається запис макросу. Під час запису макросу потрібно послідовно, за допомогою звичайних прийомів роботи у Microsoft Excel, виконати все те, що в майбутньому повинен буде виконувати макрос, автоматично після виклику.
Після запуску процесу запису на екрані з’являється панель інструментів припинення запису, що містить кнопку Прирпинитизапис. Після виконання всіх потрібних дій, які будуть виконуватися макросом, для завершення запису потрібно натиснути цю кнопку.
Для запуску макросу на виконання не обов’язково знати сполуку гарячих клавіш, достатньо викликати діалогове вікно макросів обравши для цього команди Сервіс / Макрос / Макроси, у переліку Ім’я макросу зробити активною назву потрібного макросу та натиснути кнопку Виконати.
Для внесення змін у текст інструкцій макроса потрібно натиснути кнопку Змінити діалогового вікна макросів. Відкриється текст програми відповідного макроса у редакторі Visual Basic. У цьому редакторі можна переглядати текст інструкцій макроса, вносити зміни. Редактор Visual Basic має спеціальний інструментарій для відладки програм макросів та користувацьких функцій.
Написання програм у Visual Basic здійснюється за допомогою ключових (зарезервованих) слів – команд. Для отримання пояснень з будь-якого з таких слів потрібно стати на нього курсором та натиснути <F1>.
7.7 Практичні завдання
Завдання 1. Функції перетворення тексту
Набрати наведений нище перелік у діапазоні A3:A23, у точні відповідності зі зразком, та перетворити отриманий стовпчик у заголовок таблиці. Для виконання завдання потрібно:
1 За допомогою функції перетворення тексту видалити зайві символи (номер, крапку, пробіл, точку із комою).
2 За допомогою команди Спеціальна вставка транспонувати отриманий стовпчик у рядок, який розмістити у діапазоні A1:U1.
