Як в excel вирішити проблему чисел з плаваючою комою - трюки і прийоми в microsoft excel

Користувачі Excel часто розмірковують над очевидною нездатністю Excel виконувати прості математичні операції. Це не помилка програми: комп'ютери за своєю природою не мають нескінченної точністю. Excel зберігає числа в двійковому форматі, використовуючи 8 байт, яких вистачає для обробки числа з 15 цифрами.

Деякі числа не можуть бути виражені саме за допомогою 8 байт, тому зберігаються у вигляді наближень. Щоб продемонструвати, як це обмеження може викликати проблеми, введіть наступну формулу в осередок А1: = (5,1-5,2) +1.

Результат повинен бути рівний 0,9. Однак якщо ви отформатіруете осередок так, щоб вона відображала 15 знаків після коми, то виявите, що програма обчислює формулу з результатом +0,899999999999999 - значенням, дуже близьким до 0,9, але ніяк не 0,9. Такий результат виходить, оскільки операція в круглих дужках виконується першої, і цей проміжний результат зберігається в двійковому форматі з використанням наближення. Потім формула додає 1 до цього значення, а похибка наближення поширюється на кінцевий результат.

У багатьох випадках помилки такого роду не уявляють проблеми. Однак якщо вам необхідно перевірити результат формули за допомогою логічного оператора, це може стати головним болем. Наприклад, наступна формула (яка передбачає, що попередня формула знаходиться в осередку А1) повертає БРЕХНЯ. = A1-0,9.

Одне з рішень цього типу помилки полягає у використанні функції ОКРУГЛ. Наступна формула, наприклад, повертає ІСТИНА. оскільки порівняння виконується з використанням значення в А1, округленого до одного знака після коми: = ОКРУГЛ (А1; 1) = 0.9. Ось ще один приклад проблеми точності. Спробуйте ввести наступну формулу: = (1,333-1,233) - (1,334-1,234). Ця формула повинна повертати 0, по натомість повертає -2,22045Е-16 (число, дуже близьке до нуля).

Якби ця формула була в осередку А1, то формула повернула б Not Zero: = ЕСЛИ (А1 = 0; "Zero"; "Not Zero"). Один спосіб впоратися з такою помилкою округлення полягає в використанні формулювання на кшталт цієї: = ЕСЛИ (ABS (А1)<0.000001;"Zero";"Not Zero"). В данной формуле используется оператор «меньше чем» для сравнения абсолютного значения числа с очень малым числом. Эта формула будет возвращать Zero.

Інший варіант - вказати Excel змінити значення листа відповідно до їх відображуваним форматом. Для цього відкрийте вікно Параметри Excel (виберіть Файл ► Установки) і перейдіть в розділ Додатково. Встановіть прапорець Задати точність як на екрані (який знаходиться в області При перерахунку цієї книги).

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