Використання формул при обчисленнях в MS Excel.

        Найбільш поширеним та популярним табличним процесором сьогодні є MS Excel (Microsoft Corporation). Він ввібрав в себе більшість функцій всіх існуючих електронних таблиць та в найближчому майбутньому скоріш за все не зазнає корінних змін, окрім подальшого розширення та вдосконалення.
   В Microsoft Excel наявний могутній апарат формул. Будь-яка обробка даних в Excel проходить за допомогою цього апарату. За допомогою формул можна складати, множити, ділити числа, взнавати корінь квадратний, обчислювати синуси і косинуси, логарифми і експоненти. Окрім часто обчислювальних дій з окремими числами, в Excel можна обробляти окремі рядки або стовпці таблиці, а також цілі блоки комірок. Зокрема, визначати середнє арифметичне, максимальне і мінімальне значення, середньоквадратичне відхилення, найбільш вірогідне значення, довірчий інтервал, тощо.

ЗМІСТ



ВСТУП 3
РОЗДІЛ 1. ГОЛОВНЕ ПРО ФОРМУЛИ ТА ЕЛЕМЕНТИ ФОРМУЛИ 4
1. 1. Формули як база для проведення обчислень в Excel 4
1. 2. Функції у формулах. 5
1. 3. Посилання у формулах. 8
1. 4. Константи у формулах 9
1. 5. Оператори у формулах. 10.
РОЗДІЛ 2. РОБОТА З ФОРМУЛАМИ ТА ПОВІДОМЛЕННЯ ПРО ПОМИЛКИ13
2. 1. Введення та редагування формул13
2. 2. Копіювання та переміщення формул15
2. 3. Повідомлення про помилки 17
РОЗДІЛ 3. ФОРМУЛИ МАСИВУ. ПЕРЕГЛЯД ТА ОБЧИСЛЕННЯ ФОРМУЛ.20
3. 1. Робота з масивами даних 20
3. 2. Перегляд таблиці у формульному режимі. 22
3. 3. Налаштування Excel на виконання обчислень23
ВИСНОВКИ 25
СПИСОК ВИКОРИСТАНОЇ ЛІТЕРАТУРИ 26

ВСТУП

Найбільш поширеним та популярним табличним процесором сьогодні є MS Excel (Microsoft Corporation). Він ввібрав в себе більшість функцій всіх існуючих електронних таблиць та в найближчому майбутньому скоріш за все не зазнає корінних змін, окрім подальшого розширення та вдосконалення.
В основі MS Excel лежить обчислювальний модуль, який відповідає за обробку чисел у таблицях. У вікні програми користувач бачить робоче поле, так званий “робочий лист”. Декілька листів об'єднані в “робочу книгу”, яка фізично є файлом. Робоче поле організовано у вигляді таблиці із 65536 рядками і 256 стовпцями. Рядки пронумеровані, а стовпці позначені літерами та сполученнями літер. На перехресті стовпців і рядків знаходяться комірки. Кожна з них має власну адресу на листі. Користувач вводить дані в різному форматі (текстовому, числовому та ін.) в комірки, після чого виконує над ними необхідні маніпуляції. На основі введених даних проводяться обчислення.
Excel пропонує багатий набір обчислювальних засобів, що будуть корисні для розв’язання найрізноманітніших задач. В Microsoft Excel можна використовувати як вбудовані в Excel формули і функції, так і створювати свої власні. За допомогою цієї програми реально легко упоратись з фінансовими, інженерними, статистичними та й з іншими розрахунками будь-якої складності.
В Microsoft Excel| наявний|з'явл могутній апарат формул. Будь-яка обробка даних в Excel| здійснюється за допомогою цього апарату. За допомогою формул можна складати, множити|множити|, ділити числа, взнавати корінь квадратний, обчислювати|обчисляти| синуси і косинуси, логарифми і експоненти. Окрім часто обчислювальних дій з|із| окремими числами, в Excel можна обробляти окремі рядки або стовпці таблиці, а також цілі блоки комірок|чарунок|. Зокрема, визначати|находити| середнє арифметичне, максимальне і мінімальне значення, середньоквадратичне відхилення, найбільш вірогідне значення, довірчий інтервал, тощо.


РОЗДІЛ 1. ЕЛЕМЕНТИ ФОРМУЛ.









1. 1. Формули як база для проведення обчислень в Excel

У комірки робочих таблиць можна вводити не лише дані, але й різноманітні формули, які використовуються для проведення обчислень з використанням даних, що зберігаються в інших комірках. Причому як аргументи у формулах можна використо вувати як адреси комірок, в яких знаходяться потрібні числові зна чення, так і числа, тобто константи, введені безпосередньо в формулу. Адреси комірок у формулі виступають у ролі змінних.
Отже, формула може складатися зі змінних, констант, функцій та знаків математичних та логічних операцій. Порядок обчислень визначається звичайними математичними законами. Формулу можна вводити як безпосередньо в активну комірку, так і в рядок набору формул (при цьому формула буде записуватись також в активну комірку). Перевага введення формули в рядок набору формул полягає в тому, що її можна без перешкод редагувати безпосередньо при введенні.
Формулою вважається будь-яка інформація в комірці, яка починається зі знаку рівності “=” і записана за певними правилами. Щоб програма Excel могла відрізнити формулу від тексту. Після знака рівності в комірку записується математичне вираження, що містить аргументи, арифметичні операції і функції.
У якості аргументів у формулі звичайно використовуються числа й адреси комірок. Для позначення арифметичних операцій можуть використовуватися наступні символи: + (додавання); - (вирахування); * (множення); / (розподіл).
Формула може містити посилання на комірки, що розташовані на іншому робочому листі чи навіть у таблиці іншого файлу. Один раз введена формула може бути в будь-який час модифікована. Вбудований Менеджер формул допомагає користувачу знайти помилку чи неправильне посилання у великій таблиці.
Крім цього, програма Excel дозволяє працювати зі складними формулами, що містять кілька операцій. Отже, якщо введення інформації в комірку розпочати зі знаку “=”, то Excel це буде сприймати як формулу і автоматично відбуватиметься перевірка правильності її введення. Для того щоб вміст комірки використовувався у формулі, у ній повинно знаходитись посилання на неї, тобто необхідно, щоб у формулі знаходилась адреса комірки. У ролі адреси комірки може виступати ім'я комірки.
Результатом виконання формули є деяке нове значення, що міститься у комірці, де знаходиться формула.
1. 2. Функції у формулах
Excel містить обширний список стандартних функцій, покликаних полегшити виконання простих і складних обчислень. Наприклад, функція ДОХІД використовується для обчислення доходу по облігаціях, який складає періодичні процентні виплати. Всі функції мають однаковий формат запису, який включає ім'я функції і перелік аргументів. Аргументи розташовуються в послідовності, визначуваній синтаксисом функції, і розділяються комою. Запис функції починається з вказівки її імені, потім слідує дужка, що відкривається, аргументи і закриваюча дужка. Функція може не мати аргументів. Вона може вводитися у комірку аркуша як частина формули. Функція дозволяє виконати обчислення на листах книги і на листах макросів. Функції, що є аргументом іншої функції, називаються вкладеними. У формулах Excel можна використовувати до семи рівнів вкладення функцій. При неправильному записі формули на екрані може з'явитися повідомлення про циклічне заслання. Імена функцій можна набирати рядковими буквами. Вони будуть перетворені в прописні після натиснення клавіші Enter.
Всі функції Excel покликані полегшити роботу при обчисленнях в електронних таблицях. Найпростішим прикладом виконання розрахунків є операція додавання. Скористаємося цією операцією для демонстрації переваг функцій. Не використовуючи систему функцій, потрібно вводити у формулу адресу кожної комірки окремо, додаючи до них знак плюс або мінус. У результаті формула виглядатиме так: =B1+B2+B3+C4+C5+D2.
Помітно, що на написання такої формули було затрачено багато часу, тому здається, що простіше цю формулу було б обчислити вручну. Щоб швидко і легко підрахувати суму в Excel, необхідно усього лише задіяти функцію суми, натиснувши кнопку з зображенням знака суми, або з Майстра функцій можна й вручну вдрукувати ім'я функції після знака рівності. Після імені функцій треба відкрити дужку, ввести адреси областей і закрити дужку. У результаті формула виглядатиме так: =SUM(B1:B3;C4:C5;D2).
Програма Excel пропонує більш як 400 заздалегідь вбудованих формул, що називаються функціями.
Всі функції розділені по категоріях: 

§ Математичні
§ Статистичні
§ Логічні
§ Фінансові
§ Перевірка
§ Дати і часу
§ Текстові
Умонтований Майстер функцій допомагає на всіх етапах роботи з формулами правильно застосовувати функції. Він дозволяє побудувати й обчислити більшість функцій за два кроки.
В Excel є упорядкований за алфавітом повний список усіх функцій, у котрому можна легко знайти функцію, якщо відомо її ім'я, у противному випадку варто робити пошук по категоріях.
Багато функцій різняться дуже незначно, тому при пошуку по категоріях корисно скористатися стислими описами функцій, що пропонує Майстер функцій. Майстер функцій робить допомогу у заданні будь-яких типів аргументів.
Вводити функцію у формулу можна різними способами:
§ Виділити комірку викликати команду “Вставка/Функція”.
§ Натиснути кнопку “вставка” на панелі інструментів “Стандартна”.
§ Натиснути кнопку “змінити формулу” в рядку формул.
Для обчислень деяких проміжних значень чи перевірки правильності обчислень не обов'язково створювати додаткові формули чи застосовувати калькулятор. Для цього існує функція автообчислень, яка дозволяє виконувати операції, описані в таблиці. Для цього використовують кнопку автосума, яка має випадаюче меню:

Мал. 1. 1. Меню Автосума

Табл. 1. 1. Призначення команд меню автосума.

1. 3. Посилання у формулах
Формула може містити посилання на комірку (її адреса) або на діапазон комірок, а також на імена, що представляють комірки або діапазони комірок. Для опису посилань на діапазони комірок використовуються оператори посилання.
Посилання передає в Microsoft Excel відомості про розташування значень або даних, які потрібно використовувати у формулі. За допомогою посилань можна використовувати в одній формулі дані, які містяться в різних частинах аркуша, а також використовувати в декількох формулах значення однієї комірки. Також можна створювати посилання на комірки інших аркушів тієї ж книги та на інші книги. Посилання на комірки інших книг називаються зв'язками.
За замовчуванням Microsoft Excel використовує стиль посилань A1, який визначає стовпці літерами (від A до IV, усього не більше 256 стовпців), а рядки номерами (від 1 до 65536). Ці літери й номери називаються заголовками рядків і стовпців. Для посилання на комірку потрібно ввести літеру стовпця, а потім номер рядка. Наприклад, посилання B2 вказує на комірку, розташовану на перетині стовпця B і рядка 2.
Посилання — це вказівка адреси комірки. Розрізняють відносні, абсолютні та мішані посилання.
Табл. 1. 2. Види посилань.
Відносне посилання у формулі, наприклад А1, базується на відносній позиції комірки, яка містить формулу та комірку, на яку вказує посилання. У разі зміни розташування комірки, яка містить формулу, змінюється й посилання. У разі копіювання формули вздовж рядків і вздовж стовпців посилання автоматично коректується. За замовчуванням у нових формулах використовуються відносні посилання. 
Абсолютне посилання комірки у формулі, наприклад $A$1, завжди посилається на комірку, розташовану в певному місці. У разі зміни розташування комірки, яка містить формулу, абсолютне посилання не змінюється. У разі копіювання формули вздовж рядків і вздовж стовпців абсолютне посилання не коректується. За замовчуванням у нових формулах використовуються відносні посилання, а для використання абсолютних посилань потрібно вибрати відповідний параметр. 
У тих випадках, коли при копіюванні або переміщенні формули необхідно зберегти незмінним лише номер рядка або лише найменування стовпця, застосовують мішане посилання. Мішане посилання містить або абсолютний стовпець і відносний рядок, або абсолютний рядок і відносний стовпець. Абсолютне посилання стовпців набуває вигляду $A1, $B1 і т. д. Абсолютне посилання рядків набуває вигляду A$1, B$1 і т. д. У разі зміни розташування комірки, яка містить формулу, відносне посилання змінюється, а абсолютне посилання не змінюється. У разі копіювання формули вздовж рядків і вздовж стовпців відносне посилання автоматично коректується, а абсолютне посилання не змінюється.
Якщо потрібно аналізувати дані з однієї й тієї ж комірки або діапазону комірок на декількох аркушах однієї книги, тоді використовуються тривимірні посилання. Тривимірне посилання містить посилання на комірку або діапазон, перед яким ставляться імена аркушів. Microsoft Excel використовує всі аркуші, які зберігаються між початковим і кінцевим іменами, вказаними в посиланні. Наприклад, формула =SUM(Аркуш2:Аркуш13!B5) підсумовує всі значення, які містяться у клітинці B5 на всіх аркушах у діапазоні від Аркуш2 до Аркуш13 включно.
Також в Microsoft Excel можна посилатися на комірки, що знаходяться на інших листах книги або в іншій книзі (для цього зручніше використовувати іменовані комірки), або на дані іншого застосування. Посилання на комірки інших книг називаються зовнішніми посиланнями. Посилання на дані інших застосувань називаються видаленими посиланнями.

1. 4. Константи у формулах
Константа являє собою готове (не обчислюване) значення. Наприклад, дата 09.10.2008, число 210 та текст “Прибуток за квартал” є константами. Вираз і його значення не є константами. Якщо в формулі у комірці немає посилань на інші комірки (наприклад, формула має вигляд =30+70+110), значення в такій клітинці змінюється лише після зміни формули вручну.

1. 5. Оператори у формулах 
Оператори позначають операції, які потрібно виконати над операндами формули. У Microsoft Excel є чотири типи операторів:
§ Арифметичні оператори 
§ Оператори порівняння 
§ Текстовий оператор 
§ Оператори посилання
Арифметичні оператори набули найбільш широкого поширення. Вони забезпечують складання, віднімання, множення, ділення, піднесення до степеня, знаходження відсотка за даними, приведеними в елементах електронної таблиці. Приклади використання арифметичних операторів наведені в таблиці 1. 3.

Табл. 1. 3. Використання арифметичних операторів.

Оператори порівняння використовуються для порівняння двох значень. Результатом порівняння є логічне значення: або TRUE (ІСТИНА), або FALSE (НЕПРАВДА).
Оператори порівняння можуть застосовуватися для порівняння тексту, записаного в різні комірки. При цьому потрібно враховувати, що оператор “=” застосовується в змісті збігу чи розбіжності текстів, а оператори “<” і “>” – у змісті розташування “до” чи “після” в упорядкованому списку текстових фрагментів.

Табл. 1. 4. Використання операторів порівняння.

Оператор конкатенації тексту, текстовий ператор амперсанд (&) використовується для об'єднання декількох текстових рядків в один рядок тобто об'єднання послідовностей символів в одну послідовність символів. 
Приклад об'єднання послідовностей знаків в одну послідовність із використанням текстового оператора амперсанда — “Буре”&“вій”.
Для опису посилань на діапазони клітинок використовуються оператори посилання.
Табл. 1. 5. Використання операторів посилання.




РОЗДІЛ 2. РОБОТА З ФОРМУЛАМИ ТА ПОВІДОМЛЕННЯ ПРО ПОМИЛКИ.





2. 1. Введення та редагування формул





Для введення формули у комірку потрібно її виділити і ввести в рядок формул або у комірку знак “=”.
Найпростішим способом введення формули є її безпосереднє вписання в рядок формул. У цього варіанту є лише один недолік — для вводу формули потрібно пам'ятати всі правила побудови формул в Microsoft Excel, назви вбудованих функцій і методи посилань на осередки листа. 
Рядок формул розташований над робочою областю. Він складається з наступних елементів:
§ Поле Ім’я. Вміст цього поля залежить від виконуваних дій. У звичайному режимі (тобто коли в рядку стану висвітлюється напис “Готово”) тут можна побачити адресу поточної комірки. Якщо виділити який-небудь діапазон комірок, в полі “Ім’я” з’явиться число виділених рядків і стовпців. Наприклад, напис 3Rх7C означає, що вибрано 3 рядки та 7 стовпців. А якщо у цьому полі ввести адресу будь-якої комірки і натиснути на клавішу “Enter”, ця комірка стане активною. Крім того, тут можна задати ім’я для будь-якої комірки чи діапазону комірок. Під час введення в комірку формули в полі “Ім’я” висвітлюється назва останньої функції яка використовувалась.
§ Кнопка списку Ім’я (Функції). Якщо клацнути на цій кнопці у режимі “Готово”, розкриється список імен комірок і діапазонів. А якщо клацнути на цій кнопці під час введення формули, розкриється список Функції.
§ Кнопка Скасувати. Стає доступною при активізації комірки (тобто в режимах “Ввід” і “Правка”). Клацнувши на цій кнопці, можна скасувати останню дію над коміркою.
§ Кнопка Ввід. Стає доступною при активізації комірки (тобто в режимах “Ввід” і “Правка”). Клацнувши на цій кнопці, можна завершити введення даних в комірку. Її використання еквівалентне натисканню на клавішу “Enter” чи на клавіші керування курсором.
§ Кнопка Вставка функції. Клацнувши на цій кнопці, з’являється діалогове вікно “Вставка функції”, що полегшує введення функцій під час створення формул.
§ Поле вводу. У поле вводу можна ввести дані, які мають бути розташовані в комірці (перед цим слід встановити курсор у потрібній комірці). І навпаки, уводячи дані безпосередньо в комурку, вони будть висвітлюватися також у полі вводу.
Формули практично завжди містять посилання на клітинки, вміст яких використовується в обчисленнях. Для введення формули потрібно:
§ Помістити покажчик комірки на комірку, у яку необхідно ввести формулу.
§ Вставити як перший символ знак рівності.
§ Ввести частину формули аж до першого посилання, як звичайний текст.
§ Задати посилання на комірку (або діапазон комірок). Це можна зробити або введенням з клавіатури, або за допомогою миші, клацнувши на потрібній комірці. Для задання діапазону можна виділити його за допомогою миші.
§ Після уведення іншої частини формули, належить завершити введення натисканням клавіші “Enter”.
У комірці відображається результат обчислення формули, хоча дійсним значенням комірки є формула. Сама формула буде відображатися, як вміст клітинки, у рядку формул.
Як бачимо, посилання можна задавати методом вказівки, або шляхом введення з клавіатури (природно, можна сполучати обидва способи при записуванні однієї формули). Спосіб завдання посилання методом вказівки є більш наглядним і дозволяє уникнути технічних помилок. При уведенні посилання з клавіатури можна використовувати як малі, так і великі літери. Якщо посилання задане вірно, після підтвердження вводу Excel перетворює всі літери у великі.
У формулі можна використовувати імена комірок і діапазонів комірок. Список визначених у поточній робочій книзі імен можна відкрити, клацнувши на кнопці зі стрілкою у полі “Ім’я”, яке розташоване зліва у рядку формул. Можна також натиснути клавішу “F5” та вибрати ім'я діапазону у вікні діалогу “Перехід”.
Діалогове вікно Вставка функції полегшує ввід функцій під час створення формул, які містять функції. При вводі функції у формулу діалогове вікно Вставка функції відображає ім'я функції, всі її аргументи, опис функції та кожного аргументу, поточний результат функції та всієї формули.
Щоб відредагувати формулу, потрібно виконати наступні операції:
§ виділити клітинку, що містить формулу
§ вибрати рядок формул і відредагувати формулу
§ завершити редагування, натиснувши кнопку ввід в рядку формул, або натиснувши клавішу “Enter”
§ для скасування редагування, потрібно натиснути клавішу “ESC”, або кнопку відміни в рядку формул 
У режимі редагування у верхній частині екрана (під рядком піктографічного меню) активізується рядок введення, у якій видна сама формула, готова до початку редагування, а не результат її обчислення.
2. 2. Копіювання та переміщення формул
Копіювання формул виробляють по тих же правилах, що і копіювання даних аркуша. Нижче вказані різні методи копіювання формул:
§ Копіювання формули методом перетягання.
§ Копіювання формул за допомогою команди “Копіювати” в меню “Правка”.
§ Копіювання формул за допомогою команди “Заповнити” в меню “Правка”. 

Якщо потрібно ввести ту саму формулу в суміжні комірки, то необхідно скопіювати одну формулу, у всі потрібні комірки. Для цього у Excel використовується прийом копіювання формули методом перетягання, тобто за допомогою маркера заповнення, послідовність дій з яким повинна бути наступна:
§ Активізувати комірку, у якій записана вихідна формула.
§ Навести курсор миші на маркер заповнення і протягнути маркер через комірки, у які потрібно скопіювати формулу.-
§ Відпустити кнопку миші, після чого в комірки будуть занесені копії формул (із зміненими адресами комірок), а вміст комірок буде розрахований по цих формулах.
Наведемо приклад копіювання формули методом перетягання. Передбачимо те, що потрібно піднести до третього степеня числа 5, 7 і 10, які знаходяться у комірках A3, А4 і А5. Результати обчислень запишемо відповідно у комірки ВЗ, В4 і В5. Виділимо комірку ВЗ і введемо в неї формулу =А3^3 (символ “^” використовується як оператор піднесення до степеня). Натиснемо клавішу “Enter”. У комірці з'явиться результат 125. Виділимо ще раз комірку ВЗ. Встановимо покажчик миші на маленький чорний квадратик — маркер заповнення. Натиснемо ліву кнопку миші і розтягнемо рамку ще на два вічка вниз. У виділених вічках відображуватимуться результати обчислень: 343 і 1000. Клацнемо вічко В4 — в рядку формул побачимо =А4^3, тобто відносна адреса вічка змінилася.
Такий спосіб копіювання можливий, оскільки у формулах використовуються відносні посилання. Якщо при копіюванні формули необхідно залишити її адресу незмінною, то використовується абсолютне посилання. Напишемо формулу у вигляді =$А$3^3. При копіюванні цієї формули в будь-яке місце таблиці завжди будуть піднесені до третього степеня дані, що знаходяться у комірці A3. 
Копіювання формул за допомогою команди Копіювати в меню Правка.
Аби скопіювати формулу, потрібно виділити комірку з формулою і вибрати в меню “Правка” команду “Копіювати”. Потім необхідно виділити комірку або діапазон комірок, куди буде вставлена формула, і вибрати команду “Вставити” у меню “Правка”. Комірки, в які копіюється формула, можуть знаходитися на іншому аркуші або в іншій книзі. 
При повторних обчисленнях по одних і тих же формулах можна скористатися ще одним способом копіювання формул — командою “Заповнити” у меню “Правка”. 
Щоб скористатись опцією автоматичного заповнення за допомогою цієї команди потрібно, обрати попередньо потрібний діапазон комірок, який включає клітинку з формулою, виконавши команду “Правка\Заповнити” та обравши у підменю, що з’явилось, напрямок заповнення. При використанні автоматичного заповнення за допомогою вище вказаної команди з'являється можливість заповнення несуміжних діапазонів комірок.
Для переміщення формули в іншу комірку необхідно виділити вихідну комірку, навести курсор миші на рамку комірки, щоб він прийняв форму товстої оконтуренної стрілки, і перетягнути відображення рамки початкової комірки на нове місце.
За допомогою Перетаскування можна переміщувати і копіювати не лише окремі осередки, але і зв'язані діапазони осередків. При цьому потрібно бути уважним, оскільки при цьому зміняться посилання у формулах.
Також для переміщення формули в іншу комірку можна використовувати команди “Вирізати” та “Вставити” з меню “Правка”.
2. 3. Повідомлення про помилки
Якщо формула у комірці не може бути правильно обчислена, тоді Microsoft Excel виводить у комірку повідомлення про помилку, що починається знаком # . Якщо формула містить посилання на вічко, що містить значення помилки, то замість цієї формули також буде виводитися повідомлення про помилку.
Щоб легше було знаходити й усувати помилки у формулах, у Excel передбачена найпростіша діагностика помилок, а саме: помилки розділяються по категоріях, і кожній категорії відповідає своє повідомлення. Список можливих значень помилок:
§ ##### – найбільш часте значення помилки в роботі в початківців, що з'являється, коли ширина комірки недостатня для розміщення в ній числа, чи дати часу. Щоб усунути помилку, потрібно розширити комірку або змінити формат числа.
§ #ИМЯ? — неможливість розпізнати використовуване ім'я. Це значення помилки виникає, коли неправильно зазначене ім'я об'єкта або мається посилання на ім'я, що було вилучено, коли невірно записана функція, коли при записі адрес замість латині використані кирилиця і т.д.
§ #ЗНАЧ! — спроба некоректного використання функції. Звичайною помилкою є невідповідність даних.установленому формату, наприклад, замість числа або дати в аргументі записано текст. Це ж значення помилки буде з'являтися, коли для функції або оператора, що вимагають одного значення аргументу, записують декілька.
§ #ЧИСЛО! — значення помилки, що означає проблему, зв'язану з представленням або з використанням чисел. Не виключено, що у функції з числовим аргументом використовується аргумент нечислового формату. Можливо також, що в комірку уведена формула, що повертає занадто велике значення по модулю (понад 1037), або результат обчислень по формулі не сходиться до кінцевого значення.
§ #ССЫЛКА! — означає наявність проблеми з інтерпретацією посилань, що маються у формулі. Можливо, що формула містить посилання на комірку, що уже вилучена, чи посилання на комірку, у яку скопійований вміст інших комірок.
§ #ДЕЛ/0! — спроба ділення на нуль. Така ситуація частіше виникає не через те, що в комірку записане явне ділення на нуль (оператор /0), а як наслідок використання як дільник посилання на порожню комірку або комірку, що містить нульове значення.
§ #ПУСТО! — значення помилки, що з'являється у випадку задання в посиланні порожньої комірки, або безлічі комірок. Можливо, що помилка у визначенні перетинання двох діапазонів.
§ #Н/Д — скорочення від терміна “невизначені дані”. Це значення помилки звичайно спеціально вводиться в комірки, щоб запобігти обчислення, що не можуть бути зроблені через відсутність даних.
Формула, що містить посилання на клітинку зі значенням помилки, також повертає значення помилки.
Для пошуку помилок Excel надає у розпорядження користувача можливості для відслідковування залежності, шляхом графічного представлення зв'язку між впливаючими та залежними клітинками. Стрілки у робочому листі, проте, будуть відображені тільки у випадку, якщо включений параметр “відображати” у полі “Об’єкти” на вкладці “Вигляд” вікна діалогу “Параметри” (викликається командою “Сервіс/Параметри”).
Відслідковувати залежності зручно за допомогою панелі інструментів “Залежності”, що містить кнопки для активізації відповідних команд. Задати відображення вказаної панелі на екрані можна командою “Сервіс/Залежності/Панель зависимостей”, або вибрати ім'я панелі з контекстного меню будь-якої відображеної панелі інструментів, або скористатися знайомою командою “Вигляд/Панелі інстрментів”. У двох останніх випадках треба ще виконати проміжну команду “Настройка…”.


РОЗДІЛ 3. ФОРМУЛИ МАСИВУ. ПЕРЕГЛЯД ТА ОБЧИСЛЕННЯ ФОРМУЛ









3. 1. Робота з масивами даних
В Microsoft Excel можна обчислювати значення не лише для аргументів, які знаходяться в окремих комірках, але й одночасно для цілих діапазонів комірок, тобто масивів. Згадаємо з математики, що масив — це впорядкований набір елементів (тобто кожен елемент сукупності має свій порядковий номер — адресу).
Масиви бувають одномірні (вектори), двомірні (матриці), тримірні і т.д. В електронних таблицях Excel можна виконувати операції лише над одномірними та двомірними масивами (векторами та матрицями). Програма дозволяє виконувати обчислення з усіма даними виділеного діапазону. При цьому використовується формула масиву. Вона відрізняється від звичайної формули тим, що записана у фігурних дужках, які і виступають ознакою формули масиву.
Excel дозволяє будувати формули, результатом обчислення яких є не одне скалярне значення, а цілий масив (сукупність) значень. Наприклад, у множину вбудованих функцій входять функції для роботи з матрицями: обчислення добутку матриць, оберненої матриці. Можна записати і свої власні формули, що застосовуються до діапазонів комірок, результатом обчислення яких буде діапазон комірок. Наприклад, =F4:F9–G4:G9.
Для обчислення елементів масиву в Excel використовується єдина формула на всі елементи масиву. Результатом введення такої фомули масиву беде те, що в кожній комірці діапазону комірок буде знаходитись формула у фігурних дужках.
Коли фігурні дужки ввести вручну (з клавіатури), то бажаного результату користувач не отримає (введена формула буде сприйматись як звичайний текст).
Для введення формул масиву необхідно:

§ Виділіти діапазон комірок, що повинні містити результати обчислення формули масиву. Розмірність виділеного діапазону повинна відповідати кількості значень, що повертаються формулою.
§ Ввести потрібну формулу, вказуючи посилання на діапазони комірок, що повинні використовуватися в обчисленнях.
§ Завершити уведення формули натисканням сполучення клавіш “Ctrl+Shift+Enter” (а не “Enter” як в звичайних формулах).
§ Excel помістить формулу масиву у фігурні дужки, що є ознакою формули масиву. У комірках виділеного діапазону будуть представлені результати обчислення формули.
Excel завжди інтерпретує масив як єдине ціле та не дозволяє змінити окремі клітинки масиву. Проте можна задати для окремих клітинок різноманітні параметри форматування. Клітинки не можуть бути переміщені з масиву, а нові клітинки — добавлені у масив.
Масиви констант можна використовувати замість посилань, якщо небажано вводити кожне значення константи в окрему клітинку аркуша.
Деякі вбудовані функції є формулами масиву. Для отримання належних результатів їх слід вводити як масиви.
Формула масиву для обчислення окремого результату спрощує модель аркуша, замінює декілька окремих формул на одну формулу масиву.
Наприклад, далі обчислюється підсумкове значення цін на акції, при цьому не використовується рядок комірок для обчислення та відображення окремих значень для кожної акції.
Мал. 2. 1. Формула масиву для обчислення окремого результату.

Якщо формулу ={SUM(B2:D2*B3:D3)} введено як формулу масиву, вона перемножує значення “Акції” та “Ціна” для кожної біржі, а потім підсумовує результати цих обчислень.
Формула масиву для обчислення декількох значень. Деякі функції повертають масиви значень або запитують масиви значень як аргумент. Щоб обчислити декілька значень за допомогою формули масиву, потрібно ввести масив у діапазон комірок, який має таку саму кількість рядків і стовпців, що й аргументи масиву.
Наприклад, за вказаним рядом із трьох значень продажу (стовпець В) і рядом із трьох місяців (стовпець А) функція TREND визначає продовження лінійного ряду обсягу продажу. Для відображення всіх обчислюваних значень формулу введено у три клітинки стовпця C (C1:C3).

Мал. 2. 2. Формула масиву для обчислення декількох значень.

Формула =TREND(B1:B3;A1:A3), введена як формула масиву, повертає три значення (22196, 17079 і 11962), обчислені за трьома обсягами продажу за три місяці.

3. 2. Перегляд таблиці у формульному режими 
З формулами можна ознайомитись, якщо помістити курсор на комірку, в якій вона міститься. У цьому випадку в рядку формул видно зміст формули.
Побачити формулу, за допомогою якої проведені обчислення, можна: двічі клацнувши мишкою по формулі. При цьому формула з'явиться у тому місці таблиці, де вона створена, причому кожен аргумент формули буде записано іншим кольором. Комірки, на які є посилання у даній формулі, також будуть обведені рамкою відповідного кольору.
Для того щоб побачити всі формули, що розміщені в комірках робочого. аркуша, потрібно вибрати пункт “Параметри” з меню “Сервіс” та на вкладці “Вигляд”, встановити прапорець у полі “Формули” і зафіксувати вказівник мишки на кнопці ОК. Коли потрібно заховати формули і показувати лише результати цих же формул, то прапорець у полі “Формули” потрібно зняти.

3. 3. Налаштування Excel на виконання обчислень формул
Внесення нових даних до комірок приводить до автоматичного перерахунку всіх формул. Якщо відкриті листи містять велику кількість формул, які вимагають багато часу на автоматичний перерахунок, то можна скоротити тимчасові витрати. Аби відмінити автоматичний перерахунок після кожного внесення змін до таблиці, потрібно вибрати команду “Параметри” у меню “Сервіс” а потім відкрити вкладку “Обчислення” У вкладці “Обчислення” можна встановити перемикач в одне з положень:
“автоматично”— забезпечує обчислення значень формул при кожному внесенні змін до даних, формули або назви. Ця установка використовується за замовчуванням;
“автоматично окрім таблиць” — забезпечує обчислення всіх залежних формул, за винятком таблиць даних. Перерахунок таблиць даних здійснюватиметься лише при натисненні кнопки “Обчислити” котра розміщена на цій вкладці, або при натинені клавіши “F9”;
“вручну” — виконання обчислень на всіх відкритих листах здійснюватиметься після натиснення кнопки “Обчислити” що розміщена на вкладці “Обчислення” або після натиснення клавіші “F9”. Для виконання розрахунків лише на активному листі потрібно натинути комбінацію клавіші “Shift+F9”. Після установки перемикача в це положення Excel автоматично встановлює прапорець “Перерахунок перед збереженням”, аби забезпечити збереження і облік всіх введених даних. 
Встановивши прапорець ітерації у поле “Граничне число ітерацій” і “Відносна погрішність” можна ввести числові значення.
Призначення деяких прапорців в розділі “Параметри книги”:
“оновлювати видалені посилання” — забезпечує обчислення і оновлення формул, що містять посилання на інші застосування;
“точність як на екрані” — знижує точність обчислень з внутрішнього представлення чисел (15 знаків після коми) до точності, використовуваної при відображенні комірок на екрані. Ця операція призводить до втрати значущих розрядів, і відновити їх неможливо;
“зберігати значення зовнішніх зв'язків” — зберігає копії значень, що містяться в зовнішньому документі, сполученому з аркушем Microsoft Excel. Якщо аркуш, пов'язаний з великим діапазоном комірок в зовнішніх документах, вимагає великої кількості дискової пам'яті або відкривається дуже довго, краще зняти цей прапорець, аби поліпшити ці показники.

ВИСНОВКИ

Електронні таблиці Microsoft Excel мають розвинуті засоби роботи з числовою інформацією. Дана програма дуже проста й ефективна в роботі. Використовуючи її, і натискаючи клавіші або працюючи з “мишею” можна дуже швидко й ефективно виконувати найскладніші обчислення. До найбільш корисних і ефективних функцій Excel при виконанні обчислень варто віднести: автозаповнення, формули та вбудовані функції У сукупності ці функції здатні не тільки полегшити роботу з електронними таблицями, але і відчутно скоротити час трудомістких обрахунків та аналізу даних.
Електронні таблиці Microsoft Excel — незамінний помічник у щоденній роботі. Вони відрізняється від інших зручністю роботи, легкістю в освоєнні, з яким справиться і починаючий користувач, різноманіттям корисних у щоденній роботі функцій і прийомів роботи.

СПИСОК ВИКОРИСТАНОЇ ЛІТЕРАТУРИ 

1. Верлань А.Ф., Апатова Н.В. Інформатика, — К., Квазар-Мікро, 1998.
2. Вольська С.Ю. Практикум з інформатики. Електорнні таблиці.Microsoft Excel. — К.: Видавництво Європейського університету, 2003.— 52 с.
3. Воробйов В.В. Microsoft Excel 2000: Посібник для початківця. — К.: 2000. — 36 с., з іл.
4. Іванов Є.О., Матвієнко В.Т., Попов Ю.Д.. Основи роботи з системою Microsoft Excel: Учбовий посібник для студентів всіх факультетів — К.: ВПЦ «Київський унiверситет», 1999, — 80 с.
5. Пробитюк А. Excel 7.0 для Windows в бюро. — К., BHV, 1997
6. Руденко В.Д., Макарчик О.М., Патланжоглу М.О. Курс інформатики / За ред. Мадзігона В.М. — К.: Фенікс, 2001. — 370 с.
7. Спірін О.М. Практична інформатика: 2-ге видання, перероблене і доповнене. Методичний посібник.— Житомир: Поліграфічний центр ЖДПУ, 2001.—176с., іл
8. Шестопалов Є.А. Excel’97&2000 для початківця, 2003. — 96 стор.


Немає коментарів:

Дописати коментар

Перегляди сторінки за останній тиждень