Как от една тема се появи втора! :) Ако няма кой да ни усложни живота, сами си го усложняваме! :) Всичко тръгна безобидно от писането на #055 Начини за закръгляне :) В една от таблиците първата колонка съдържа 0.2,0.4..... От тук може да си изтеглите примера: демонстрация за закръгляне. (таблицата съдържа потребителска функция и за това ще ви поиска разрешаването на макросите!)
 |
| Примерна таблица |
Реших да го направя "научно" (пореден случай, когато практиката прегазва науката):):) Записах в клетката А2 формулата =А1+0.2 и дръпнах. И готовоооооооо.... Но не би:( Всичко изглеждаше читаво, докато не въведох формулите =ODD(A2) и =EVEN(A2) в колонките "C" и "E". Реших да "проверя" Excel. И ми се стори много подозрително, че "ODD" за "1" резултата е "1", а за "3" резултата е "5"!?! Също така EVEN, за "2" дава "2", а за "4" дава "6"?! Въведох директно в клетка =Even(4) и резултатът беше "4"!
След първоначалните проверки дали не съм дръпнал неправилно, реших да опитам с допълнителна колона. В колонка "B" стойностите ги въведох с възможностите на Excel за запълване на клетки с поредица от стойности (Series). След като въведох функциите ODD и EVEN за колонка "B", се получиха резултатите в колонките "D" и "F". Направих условно форматиране и лъсна неприятната истина. Имаше две различия!
Какво се е случило? Нищо изненадващо. При добавянето на 0.2 се е натрупала грешка и 3.00 не е 3.00 и 4.00 не е 4.00! Понеже са малко отгоре, функциите ги закръглят към следващото четно/нечетно число! Оказва се, че ODD и EVEN са много чувствителни и реагират на тези минимални разлики.
За тези които искат да задълбочат, препоръчвам да изчетат статиите Numeric precision in Microsoft Excel (и връзките под нея) и Floating-point arithmetic may give inaccurate results in Excel.
Решенията на проблема са описани в #016 Закръгляне или кошмарът на счетоводителя и в злополучната #055 Начини за закръгляне. Например формулата в A2 може да се видоизмени като стане =round(A1+0.2;2)! Така, това което ще виждате е точно това което ще се използва при изчисленията. Другият вариант е да смените начина на изчисление както е описано в #16.
След като намерих решение, ме зачовърка въпроса, как да покажа истинската стойност на клетката или с колко тя се различава от показаното. И така убих още няколко часа от времето, което така и така го нямам:( Добавих колона в която да получа разликата между колонка "А" и колонка "B". И получих.... "геврек"... Т.е. нула. Даже много нули след опита ми да променя формата и да увелича точността:):) Excel упорито твърдеше, че разлика между двете колонки няма! Реших по-брутално да му докажа, че стойностите в двете колонки са различни. За целта просто ги сравних. И .... Нищо! Пак си твърдеше, че двете стойности са си равни и получих навсякъде True! Лошото (и хубавото на Excel) е че не иска да мъти главите на потребителите с глупости. Т.е. е максимално User Friendly. Но точно в такива ситуации това хич не е добре и няма как да обясня на някой как при две еднакви колонки резултата е различен?!? Странно е, че ODD и EVEN са толкова нетипично за EXCEL чувствителни. Явно някой математически маниак ги е правил!:):)
 |
| Разлика и сравнение |
След дискусия по форуми, в които се опитваха да ми обяснят проблема, а аз да им обясня, че проблема ми е ясен, но не ми е ясно как да изтръгна от Excel истината:):) След борба се оказа че май единствения начин е да се мине през правене на потребителска (UDF - User Defined Function) функция на VBA. Оказва се, че VBA е по-приказлив и всичко си казва!:)
Кодът на функцията е:
Function Diff(x As Range, dig As Byte) As Double
Diff = x.Value - Round(x, dig)
End Function
Функцията връща разликата между стойността на клетката и стойността на клетката закръглена до определен брой знаци след десетичната точка. В случая съм дал резултата от
=Diff(A2;2).
 |
| Разлика |
И истината лъсна. Както се вижда, разликите от стойността която виждаме и стойността която служи за изчисление са много много малки, но са достатъчни да променят резултатите.И това може да е фатално!
За това ЗАКРЪГЛЯЙТЕ!!!
П.П. И се намесиха нинджите! :):) И аз научих нещо ново! В дискусията след експерименти изскочи една тайна на Excel. Оказва се, че при определени ситуации Excel не се държи User Friendly, а изчислява и показва точно както си трябва.
Оказва се, че формулата =(A2-B2) дава различен резултат от =A2-B2!!!! Връща точния резултат! Математически няма разлика, но в Excel има разлика! Точен резултат дава и формулата =A2-B2-0 !!! И то без скоби! Що се отнася до сравнението се оказва, че правилното сравнение е =A2-B2=0 ! И тук може без скоби! Excel-ска му работа. Сума ти математици, може би току що, го намразиха;) Та и аз понаучих нещо недокументирано и реших да го споделя с вас.
 |
| Правилна разлика и сравнение |
