Розрахунки в Excel – межі точності

Created on Monday, 11 June 2012 22:49 Written by Edward

У цій статті я хотів би поділитися з Вами інформацією про межі точності розрахунків у Microsoft Excel. Ніхто не замислюється, але Excel вважатиме нам не будь-які речі, які ми в нього попросимо. Є обмеження. І про ці обмеження краще знати заздалегідь, щоб уникнути помилок, де вони не очікуються і зовсім неоднозначні.

Ми вже стикалися із системними помилками excel, наприклад, коли система не дораховує таблицю до кінця. Обмеження ж точності, про які я хочу розповісти, - це вже швидше архітектурне обмеження, з яким нам доведеться миритися, але про яке просто необхідно знати будь-якому користувачеві Excel.

У чому суть і як це працює

Microsoft Excel робить розрахунки на основі змінної з плаваючою комою. Це означає, що дані зберігаються у нього в особливому вигляді, а саме у вигляді дробу, де одна цифра стоїть як ціла частина, а решта складають дробову частину. Цей дріб множиться на 10 в n-му ступені.

Наприклад, число 12345 зберігатиметься і оброблятиметься (брати участь у розрахунках) в Excel'і як 1.2345E4 або 1.2345*10^4. Це в цілому правильно, і, здавалося б, нема про що турбуватися. Поки що ви не задумаєте астрономічні розрахунки.

Справа в тому, що загалом у цьому дробі система проігнорує всі цифри після п'ятнадцятого розряду. Дозвольте пояснити на прикладі.

Якщо ввести число 123,456,789,012,345,678.00 в Excel, то після натискання Enter система автоматично перетворює його у себе на 1.23456789012345E17. Що насправді дорівнює 123,456,789,012,345,000.00. Тобто, на 678 відрізняється від оригіналу. Те саме стосується точних дробів. Вони теж обрізатимуться таким чином, щоб загальнечисло розрядів у числі було менше 15. Це обмеження введено як необхідність для того, щоб обмежити непотрібну точність при величезних обчисленнях з поділом або дробовими ступенями (корінням), наприклад моделі дисконтованих грошових потоків при наведенні точно будуть викликати величезну кількість чисел з великою кількістю дробових знаків (особливо, якщо модель наводиться не за роками, а за півріччя, квартали або місяці).

І що?

Передчуючи питання "Та кому взагалі треба рахувати з такою точністю", спробую заперечити.

По-перше, є фізики, астрономи та інші, яким, мабуть, доведеться відмовитися від Excel на користь MathCAD через це погане обмеження.

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

приклад. Номер рахунку в банках України - двадцятизначний. Якщо спробувати забити його в числове поле, то ви втратите останні п'ять цифр (замість них будуть нулі). Неприємний вийде сюрприз, правда? Ваша компанія може використовувати довгі номенклатурні номери. Знову ж таки, не хотілося б втратити кілька цифр наприкінці. КБК (код бюджетної класифікації) не зберігається? Все про те ж - плаває кома.

Як уникнути помилки

Щоб уникнути цих помилок, достатньо змінити поле на текстове (мало, кому потрібно робити розрахунки, скажімо, з номерами банківських рахунків - множити, ділити, складати, віднімати, зводити в ступінь.)