Помилки, що повертаються формулами
Формули робочого листа
Формулою в MS Excel називається вираз, що починається зі знака рівності. Формули можна копіювати, переміщати та видаляти. У формулах використовуються математичні операції, операції порівняння, конкатенації (злиття) рядків, адреси комірок або діапазонів, строкові константи, а також вбудовані й користувальницькі функції. Якщо ввести формулу в комірку, то в цій комірці на робочому листі відображається результат розрахунку по формулі. При виборі комірки текст формули видний у рядку формул.
У рядку редагування формули є три кнопки: щиглик по [v] підтверджує внесені у формулу зміни, по [x] – скасовує, а кнопка [fx] дозволяє вставити функцію MS Excel. Вставка у формулу пробілів і розривів рядка не впливає на результат обчислень, однак не можна відокремлювати ім'я книги від імені листа й комірки. Розрив рядка вводиться комбінацією <Alt+Enter>. Для того, щоб видалити з комірки формулу, залишивши тільки повернуте їй значення, потрібно вибрати комірку, а потім послідовно натиснути <F2>, <F9> та <Enter>.
У формулах припустимі наступні операції:
+ Додавання
- Вирахування або знак мінус
* Множення
/ Ділення
^ Зведення в ступінь
& Конкатенація (тобто зчеплення рядків)
= Логічне порівняння: дорівнює
< Логічне порівняння: більше ніж
> Логічне порівняння: менше ніж
<> Логічне порівняння: не дорівнює
>= Логічне порівняння: не менше ніж
<= Логічне порівняння: не більше ніж
Наведемо приклади формул робочого листа й виконувані формулами дії:
=3*2 Множить три на два
=А1+А2 Складає вміст комірок A1 й А2
= А2^2/3 Зводить вміст комірки А2 у ступінь 2/3
=СУММ(А1:А4) Повертає суму значень із діапазону комірок А1:А4
(використовується вбудована функція СУММ).
=А1>А2 Повертає значення ИСТИНА, якщо вміст комірки А1 більше вмісту комірки А2, і повертає ЛОЖЬ в противному випадку
=Al&B1 Конкатенація (з'єднання) двох текстових рядків з комірок А1 й В1 (якщо в комірці A1 введене – Иван, а в B1 – Сидоров, то формула поверне значення ИванСидоров).
За допомогою круглих дужок можна змінити у формулах звичайний порядок виконання операцій. При введенні у формулу посилання на комірку або діапазон переважніше не набирати це посилання із клавіатури, а вибрати мишею або шукану комірку, або діапазон. Посилання на обрану комірку або діапазон буде вставлене у формулу автоматично.
Помилки, що повертаються формулами
Іноді формула повертає не число або рядок тексту, а повідомлення, що починається із символу #, після якого йде спеціальний текст. Так MS Excel інформує користувача про те, що формула повертає помилку. У цьому випадку треба скорегувати або саму формулу, або значення в тих комірках, на які формула посилається. Перелічимо список помилок, які можуть бути повернуті формулами робочого листа:
#ПУСТО! Використано помилкового оператора перетинання діапазонів або помилкове посилання на комірку.
#ДЕЛ/0! Як дільник використовується посилання на комірку, у якій міститься нульове або порожнє значення (якщо посиланням є порожня комірка, той її вміст інтерпретується як нуль).
#ЗНАЧ! 1) Замість числового або логічного значення введений текст, і MS Excel не може перетворити його до потрібного типу даних; 2) Введення формули масиву завершено натисканням клавіші <Enter>, а не комбінацією клавіш <Ctrl>+<Shift>+<Enter>; 3) Використана неправильна матриця в одній з матричних функцій листа.
#ССЫЛКА! Посилання на неіснуючу комірку.
#ИМЯ? Помилка в написанні імені або використовується неіснуюче ім'я.
#ЧИСЛО! 1) Функція із числовим аргументом використовує неприйнятний аргумент; 2) Формула повертає числове значення, що занадто велике або занадто мале, щоб його можна було представити в MS Excel.
#Н/Д Значення помилки #Н/Д є скороченням терміна «невизначенідані» або «немає даних». Це значення допомагає запобігти використанню посилання на порожню комірку.
1.3. Абсолютна і відносна адресація, зовнішні та тривимірні посилання
У формулах використовуються відносні, абсолютні й змішані посилання. Відносне посилання - це посилання, що автоматично змінюється при зміні адреси комірки й позначається простим зазначенням відповідних рядків і стовпців, наприклад А10, С11 і т.д. Вона використовується за замовчуванням. Наприклад, якщо обчислюється вираз СУММ(С1:С10), то при переміщенні комірок у діапазон D1:D10, формула автоматично зміниться на СУММ(D1:D10).
Абсолютне посилання - це посилання, що не змінюється при змінах адреси комірки. Абсолютне посилання позначається знаком $, що встановлюється перед ім'ям стовпця і номером рядка, наприклад - $A$1.
Змішане посилання - це посилання, що є сполученням абсолютної і відносної, вона закріплює адресу комірки по одному параметру (стовпцю або рядку), і змінює його по іншому. Частина адреси, що закріплюється, позначається знаком $. Наприклад, якщо обчислюється вираз =СУММ($C1:$C10), те при переміщенні комірок у діапазон D10:D20 формула автоматично зміниться на =СУММ($C10:$C20).
Посилання на комірки, що знаходяться в інших книгах, називаються зовнішніми. Зовнішнє посилання організується завданням імені книги, листа та комірки: [Книга1]Лист1!A1.
MS Excel дозволяє у формулах використовувати тривимірні посилання на комірки та діапазони, тобто об'єднати в одному посиланні кілька комірок або діапазонів комірок, розташованих на різних робочих листах.
Як приклад використання тривимірного посилання наведемо наступну формулу, що повертає суму значень із діапазонів, розташованих у робочих листах Лист1, Лист2 та Лист3:
=СУММ(Лист1!:Лист3!А1:В2)
1.4. Використання майстра функцій
Введення формул в комірку можна робити або із клавіатури, або за допомогою діалогового вікна Мастер функций,що відображається або вибором команди Вставка->Функция,або натисканням кнопки Вставкафункциинапанелі інструментів Стандартная.Майстер функцій містить список всіх функцій робочого листа, довідки по їхньому синтаксису та застосуванню.
Продемонструємо роботу із майстром функцій на прикладі введення формули:
=COS((ПИ)*A1)^2 .
1. Виберіть комірку В1.
2. Натисніть кнопку Вставка функциина панелі інструментів Стандартная або виберіть команду Вставка->Функция.На екрані відобразиться діалогове вікно Мастер функций. Для MS Excel 2000 воно складається із двох частин (мал. 2.1.): ліва - Категория зі списком дванадцятьох категорій функцій; права - Функциязі списком імен функцій, що входять в обрану категорію.
Для MS Excel вікно буде трохи відрізнятися наявністю кнопки Найти й вікна для введення опису шуканої функції. Категорія Полный алфавитный переченьмістить всі вбудовані функції та їхні імена впорядковані за алфавітом. Категорія 10 недавно использовавшихсямістить імена десяти останніх застосованих функцій.
3. Функція COS відноситься до категорії Математические.Виберіть категорію й функцію, а потім натисніть кнопку OK. Після цього на екрані відобразиться панель формул і поле введення.
Мал. 2.1. Робота з майстром функцій в MS Excel
Мал. 2.2. Вікно введення аргументів функції в MS Excel
В поле Числовводиться аргумент функції - у розглянутому прикладі це ПИ()*А1. За допомогою клавіатури в це поле введіть тільки ПИ()*, а посилання на комірку A1 у формулу додайте, клацнувши по комірці A1 на робочому листі. Після натискання кнопки ОК в комірку B1 буде введена формула: =COS(ПИ()*A1).
4. За допомогою клавіатури додайте у формулу =COS(ПИ()*A1) операцію зведення функції у квадрат. Після всіх описаних дій у комірці В1 повинна з'явитися формула: =COS(ПІ()*A1)^2.
ІІ. Практична частина
Задача 1
Створити новий лист MS Excel, за допомогою майстра функцій в комірку B4 ввести формули відповідно до варіанта:
Варіант 1 : sin 
;
Варіант 2 : cos (2x+1);
Варіант 3 : arctg 3x;
Варіант 4 : cos 2x+1;
Варіант 5 : 3ctg x;
Варіант 6 : lg (-x);
Варіант 7 : 
;
Варіант 8 : 
;
Варіант 9 : |6x+3|;
Варіант 10 : sin(cos x).
В комірку B3 ввести значення x = - (номер за журналом).
Задача 2
У рядок 1 ввести ряд натуральних чисел 1...15 і зробити виділення комірок зі значенням відповідно до варіанта:
Варіант 1: більше 6 і менше 9;
Варіант 2: менше 2 або більше чи дорівнює 14;
Варіант 3: більше чи дорівнює 4 й менше 10;
Варіант 4: менше 13 і не дорівнює 5;
Варіант 5: менше 7 або більше 12, а також рівне 10;
Варіант 6: більше чи дорівнює 13 і не дорівнює 15;
Варіант 7: дорівнює 1, чи 3, чи 5;
Варіант 8: більше чи дорівнює 5 або дорівнює 8;
Варіант 9: не дорівнює 7 ,8, 9, 10;
Варіант 10: дорівнює 1, 2, 3 або 5, 6, 7.
Задача 3
Розрахувати загальну площу і кількість населення всього світу
Розрахувати за формулами відсоток площі кожного регіону. (Зробити комірку D7 активною. Занести формулу «=С7/$С$12». За допомогою маркера заповнення скопіювати цю формулу на діапазон D7: Dll. Вибрати для цього діапазону формат — Процентный, встановити 2 десяткові знаки).
Розрахувати за формулами відсоток населення кожного регіону.
Відформатувати таблицю за зразком.
•Об'єднати комірки діапазону В2:Е2. (Виділити комірки і натиснути кнопку панелі інструментів). Об'єднати також комірки діапазонів В5: В6, C5:D5 i E5:F5.
•Надати стиль шрифту і вирівнювання тексту за зразком.
•Оформити таблицю рамкою і кольором за зразком .
Зберегти документ у папку «Група <номер групи>», на давши йому ім’я.
У робочому зошиті пояснити необхідність використання відносних і абсолютних адрес комірок у формулі для обчислення відсотка площі Європи «=С7/$С$12».
Записати у зошит формулу, розміщену в комірці F7, і надати для неї відповідні пояснення.
Рекомендована література
1. Інформатика: 10 кл.: підручник для загальноосвіт. навч. закл.: академ. рівень, профіл. рівень / Й. Я. Ривкінд , В. В. Шакотько, О. М. Лисенко, Л. А, Чернікова; за заг. ред. М, З. Згуровського. – К.: Генеза, 2010. – 304 с.
2. Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Базовий курс інформатики. – К.: Вид. Група BHV, 2005. – 320 с.
3. Інформатика (підручник). Верлань А. Ф., Апатова Н. В. К:- Форум, 2001. – 255 с.
4. Гаєвський О.Ю. Інформатика: 7-11 кл. Навчальний посібник. – К.: Вид. А.С.К., 2004.– 512 с.: іл.
5. Глинський Я.М. Інформатика: 10-11 класи: Навч. посіб.: У 2 ч. –Львів: СПД Глинський, 2008
6. Інформатика: Комп’ютерна техніка. Комп’ютерні технології (посібник) / за ред. О. І. Пушкаря – К.: Академія, 2001. – 696 с.
7. Глинський Я. М. Практикум з інформатики: Навчальний посібник. – Львів: СПД Глинський, 2010. – 304 с.
8. http://informatic.org.ua/
9. http://www.eduwiki.uran.net.ua