Excel 2003: індикація помилок

Редакція БК
Бухгалтер&Ком'пютер Травень, 2009/№ 9
В обраному У обране
Друк
Відповідь на запитання

Excel 2003: індикація помилок

 

 

Шановна редакціє! При роботі з Excel на робочому листі інколи виникають повідомлення про помилки (#Н/Д, #ЧИСЛО тощо). Що вони означають і як бути в цій ситуації?

А. Фурсов, бухгалтер, м. Харків

Відповідає

Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства  

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

 

Таблиця 1

Повідомлення про помилки обчислень в MS Excel

Індикація

Проблема

1

2

#ИМЯ?

Некоректно записано ім’я функції (наприклад, «ЕСЛ» замість «ЕСЛИ»).Можливо, адресу комірки введено кирилицею, пропущено двокрапку в діапазоні або в імені функції є латинська літера замість російської

#ЗНАЧ!

Спроба виконати арифметичні дії над комірками, що містять не числа, а текст

#ДЕЛ/0!

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

#ЧИСЛО!

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

#ССЫЛКА!

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

#ПУСТО

Найчастіше помилка виникає, коли зазначено перетин двох областей, які насправді не мають загальних комірок. Оператором перетину областей є пробіл між посиланнями. Можливо, у формулі замість роздільника «;» (крапка з комою, об’єднання діапазонів ) або замість «:» (двокрапка, покажчик безперервного діапазону) набрали пробіл

#Н/Д

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

1. У функціях пошуку («ГПР()», «ВПР()», «ПРОСМОТР()», «ПОИСКПОЗ()») неправильно зазначено шукане значення, тому пошук неуспішний.

2. Цими функціями виконується пошук у невідсортованій таблиці, а параметр «інтервальний перегляд» установлено за умовчанням.

3. Неправильно чи не повністю визначено аргументи функції

 

Здебільшого помилку, звичайно ж, потрібно виправити

, тобто перевірити формулу, відкоригувати посилання, дані, параметри. Але так буває не завжди. Наприклад, при організації пошуку «#Н/Д» — це теж результат! Часто такий результат потрібно обробити і виконати певні дії. Найчастіше слід скасувати видачу повідомлень і замінити їх порожнім рядком. Ситуації трапляються різні. Тому в MS Excel передбачено багатий арсенал для оброблення помилок обчислень. Розглянути його повною мірою ми не зможемо, бо обсяг матеріалу не дозволяє. Але основні моменти зафіксуємо.

Отже, помилку можна обробити двома способами:

1. «Спіймати» її в момент виникнення та замінити результат.

2. Проаналізувати дані (посилання, параметри) до моменту появи помилки, тобто запобігти її виникненню. Це зробити складніше і не завжди можливо. Тепер по черзі.

Для «перехоплення» помилок в Excel є кілька функцій. Ось основні з них: «

ЕОШ(Ссылка)», «ЕОШИБКА(Ссылка)» — сигналізують про факт виникнення помилок. Аргумент «Ссылка» — це адреса на робочому листі. Якщо результат роботи функцій — «Истина», отже, у комірці за адресою «Ссылка» є помилка. Характер і причину помилки ці функції не покажуть. Тому (на мій погляд) універсальнішою функцією оброблення помилок є «ТИП.ОШИБКИ(ЗначениеОшибки)». Їй можна вказати адресу на робочому листі, а функція поверне код помилки. Якщо помилки немає, результатом функції буде «#Н/Д». Розшифрування коду помилок функції «ТИП.ОШИБКИ()» наведено в табл. 2.

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

Если()». Ось приклад. У комірці «А1» записано формулу. Я хочу обробити ситуацію ділення на нуль і видати про це повідомлення. Інакше жодного повідомлення видавати не потрібно. Функція «=ЕОШИБКА(A1)» в цьому випадку поверне результат «ИСТИНА». Значення формули «=ТИП.ОШИБКИ(A1)» дорівнюватиме «2». Вираз «=ЕСЛИ(ТИП.ОШИБКИ(A1)=2;"Деление на ноль";"")» видасть попередження про ділення на нуль. При виникненні інших помилок повідомлення не з’являтимуться. Але є одне «але». Якщо результат в «A1» буде правильним, то наша формула поверне «#Н/Д» (див. табл. 2)! Тому завчасно доведеться виконати ще одну перевірку вмісту в «A1». Логіка перевірки має бути двоетапною:

— спочатку функцією «

ЕОШИБКА(A1)» перевірити наявність помилки взагалі;

— якщо помилка є, то виконати її аналіз функцією «

ТИП.ОШИБКИ(A1)» та зробити остаточне оброблення.

 

Таблиця 2

Коди помилок функції «

ТИП.ОШИБКИ()»

Причина помилки

Результат роботи функції

#ПУСТО!

1

#ДЕЛ/0!

2

#ЗНАЧ!

3

#ССЫЛ!

4

#ИМЯ?

5

#ЧИСЛО!

6

#Н/Д

7

Будь-яке інше

#Н/Д

 

Отже, нам потрібно буде організувати перевірку двома

вкладеними функціями «ЕСЛИ()». А формула при цьому матиме такий вигляд:

«

=ЕСЛИ(ЕОШИБКА(A1);ЕСЛИ(ТИП.ОШИБКИ(A1)=2;"Деление на ноль";"");"")». Вона видасть попередження про ділення на нуль. При виникненні інших помилок повідомлення не з’являтимуться.

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

 

Таблиця 3

Функції для перевірки типу даних в MS Excel

Функція

Призначення

«ЕПУСТО(Значение)»

Перевіряє, чи вказує посилання «Значение» на порожню комірку. Якщо «Да», то результат — «ИСТИНА», інакше результат — «ЛОЖЬ»

«ЕССЫЛКА(Значение)»

Перевіряє, чи є «Значение» посиланням. Якщо «Да», то результат — «ИСТИНА», інакше результат — «ЛОЖЬ»

«ЕТЕКСТ(Значение)»

 

Перевіряє, чи є «Значение» текстом. Якщо «Да», то результат — «ИСТИНА», інакше результат — «ЛОЖЬ»

«ЕЧИСЛО(Значение)»

 

Перевіряє, чи є «Значение» числом. Якщо «Да», то результат — «ИСТИНА», інакше результат — «ЛОЖЬ»

«ТИП(Значение)»

 

Аналізує тип параметра «Значение» та повертає результат у вигляді числа. Якщо результат дорівнює «1», «Значение» є числом, якщо «2» — рядком, «4» — логічним значенням, «16» — помилкою, «64» — масивом значень

 

Використовуючи «

ЕСЛИ()» в поєднанні з функціями для перевірки типів даних, можна вчасно виявити джерело можливої проблеми та запобігти її виникненню.

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

 

Чекаю ваших листів, зауважень, запитань та пропозицій на

bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum
Оформи передплату та читай все Передплатити журнал

Схоже, що ви використовуєте блокувальник реклами :(

Щоб користуватися всіма функціями сайту, додайте нас у винятку!

Як відключити
Завантажуйте наш мобільний додаток iFactor

© Factor.Media, 1995 -
Всі права захищені

Використання матеріалів без узгодження з редакцією заборонено

Ознайомитись з договором-офертою

Приєднуйтесь
Адреса
м. Харків, 61002, вул. Сумська, 106а
Powered by
Factor Web Solutions
Ми використовуємо cookie-файли, щоб зробити сайт максимально зручним для вас та аналізувати використання наших продуктів та послуг, щоб збільшити якість рекламних та маркетингових активностей. Дізнатися більше про те, як ми використовуємо ці файли можна тут.
Закрити
icon-block
Заважає реклама?
Ви маєте можливість її відключити!
Бажаєте побачити, як сайт буде виглядати без реклами?
Вимкнути на 10 секунд