#57 И пак за закръглянето (или как банкерите цепят стотинката):)

И пак за закръглянето... Аман:):):)

     Първо малко теория свързана със закръглянето. Както са ни учили в училище, ако цифрата след знака до който закръгляме е по-голяма или равна на петица я увеличаваме с единица. Т.е. 1.474 закръглено до втория знак е 1.47, а 1.476 е 1.48. В това правило има нещо "нечестно". Цифрите при които НЕ се закръгля са 1,2,3,4, а цифрите при които се закръгля са 5,6,7,8,9. Оказва се, че имаме повече случаи при които се закръгля! Търсят се различни начини за оправяне на тази грешка. Един от тези начини е така нареченото "банкерско закръгляне" (bankers' rounding).  Това е едно от названията на метода: round half to even, unbiased rounding, convergent rounding, statistician's rounding, dutch rounding, gaussian rounding, odd-even rounding, broken rounding. Ето алгоритъма на закръгляне:

1. Ако следващата цифра след позицията за закръгляне е 0,1,2,3,4, не се извършва промяна на на последния знак: 1.444 си е 1.44. (така са ни учили в училище)
2. Ако следващата цифра след позицията за закръгляне е 6,7,8,9,  последният знак се увеличава с единица: 1.446 си е 1.45 (така са ни учили в училище).
3. Ако следващата цифра след позицията за закръгляне е пет и има още цифри след нея, последният знак се увеличава с единица: 1.4451 става 1.45, и 1.4351 става 1.44 (така са ни учили в училище).
4. Ако следващата цифра след позицията за закръгляне е пет и НЯМА други цифри след нея:
1. Ако последната цифра е НЕЧЕТНА се извършва увеличение на последния знак. т.е. 1.435 става 1.44 (така са ни учили в училище)
2. Ако последната цифра е ЧЕТНА,  НЕ СЕ ИЗВЪРШВА увеличение на последния знак. т.е. 1.445 си остава 1.44 (така НЕ СА ни учили в училище:)!!!

    Т.е. да резюмирам разликата между това което сте учили в училището и "банкерското" училище е, ЧЕ АКО СЛЕД ЗНАКА ЗА ЗАКРЪГЛЯНЕ ИМАМЕ ПЕТИЦА (САМО ПЕТИЦА!) И ПОСЛЕДНИЯ ЗНАК Е ЧЕТНО ЧИСЛО НЕ СЕ ИЗВЪРШВА УВЕЛИЧАВАНЕ!

       За повече информация четете тук: Rounding (статия във Wikipedia).

      Защо ви пълня главите с глупости?!:) Искам да ви предпазя от един подводен камък. По-скоро тези от вас, които в даден момент ще започнат да пишат макроси на Visual Basic for Application (VB) . Проблемът се състои в следното: функцията Round в Excel работна книга си работи "нормално". Т.е. тя си закръгля както са ни учили. 1.445 си го закръгля на 1.45! Функцията Round във VBA работи по банкерски!!! За илюстрация съм направил проста потребителска функция (UDF):

Function vbaRound(r As Range, digits As Byte) As Double
   vbaRound = Round(r.Value, digits)
End Function

След което сравних резултата от двете функции:

Сравняване на WorkSheet Round и Round във VBA

      Както се вижда разликата е при четна цифра (4,6...) и ПЕТИЦА без нищо след нея! Обърнете внимание, че ако след петицата има нещо друго, то двете функции си действат по един и същи начин!

     Така, че внимавайте със закръглянията във VBA! Те са различни от закръглянията в работен лист!

    П.П. Ако искате да получите "нормално" закръгляне и във VBA, може да използвате следния запис:Application.WorksheetFunction.Round(......

П.П Ето къде MS са смотали информацията: PRB: Round Function different in VBA 6 and Excel Spreadsheet

#56 Отново за закръглянето (или как Excel си пази тайните):) (За нинджи!)

     Как от една тема се появи втора! :) Ако няма кой да ни усложни живота, сами си го усложняваме! :) Всичко тръгна безобидно от писането на #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-ска му работа. Сума ти математици, може би току що, го намразиха;) Та и аз понаучих нещо недокументирано и реших да го споделя с вас.

Правилна разлика и сравнение

#055 Начини за закръгляне

      Няма да открия топлата вода с тази тема, но се надавам, че информацията от нея ще ви бъде полезна. Вече дискутирах в друга тема (#016 Закръгляне или кошмарът на счетоводителя) нуждата от закръгляне чрез функция! Съветвам ви да прочетете внимателно тази тема. В нея обсъдих само една от функциите за закръгляне. За да бъда коректен сега ще покажа всички начини, които са ДЕСЕТ(!) на брой!

Можем да ги класифицираме на три групи:

1. Методи за "отрязване" на цялата част от числото.

=Trunc - "реже" цялата част или или цялата част и определен брой знаци от десетичната част. Посочват се броя знаци до които се реже.
=Int - връща цяло число по-малко от даденото. Няма втори параметър.

   Разликата между тези две функции и при работа с отрицателни числа! В помощната информация на Excel е казано, че ако искаме да получим дробната част на едно число формулата е X-INT(X), което не е вярно (всъщност изречението е "Връща дробната част от положително реално число...", но хората не вникват в детайлите и по инерция смятат, че се отнася за ВСИЧКИ числа)!! Вярната формула е X-Trunc(X), защото тя отчита и отрицателните числа! NB! Ако искате да имате дробната част в положителен вид използвайте формулата =ABS(X-TRUNC(X;0)) !

Разлика между Trunc и Int

2. Методи за закръгляне до определен брой знаци след десетичната точка. Като параметър се посочва БРОЙ ЗНАЦИ.

=Round - закръгля според математическите правила
=RoundDown - винаги закръгля към по-малкото число
=RoundUp - винаги закръгля към по-голямото число

3. Методи за закръгляне към стойност която се дели на дадения множител без остатък. В този случай се посочва като параметър МНОЖИТЕЛ (не брой знаци!!). Например при множител 2 става дума за ЧЕТНО число!

=Mround - Закръгля към по-голямото или към по-малкото число, което се дели без остатък на посочения множител.
=Ceiling - Закръгля към следващото число което се дели без остатък на посочения множител (нещо като MRoundUP).
=Floor - Закръгля към предишното число което се дели без остатък на посочения множител (нещо като MRoundDown).
=Odd - Връща следващото нечетно число. Функцията няма втори параметър.
=Even - Връща следващото четно число. Функцията няма втори параметър.

Методи за закръгляне (Щракнете върху таблицата за да я видите в оригинален размер!)

      Едно от хитрите приложения на Ceiling е да закръгляте суми. Например ако искате да на боравите със жълти стотинки можете да закръгляте с множител 0.10, а ако искате да не работите с монети от 2 или 1 стотинка може да използвате множител 0.05! (NB! Разбира се може да използвате и Floor, но кой иска да губи!:):):)

Закръгляне на суми

Успех със закръглянето!:):) 

#016 Закръгляне или кошмарът на счетоводителя

Темата можеше да се казва и "Това което виждате не е това което е":)  Един от основните пропуски когато се обяснява Excel на начинаещи е фактът, че командата Format/Numbers визуално променя вида на клетките без да пипа тяхното съдържание. Т.е. това което потребителя вижда не е точно това което съдържа дадената клетка. Всичко върви добре до момента в който започнат да се правят таблици в които има важни данни. Недай си боже тези таблици да попаднат в ръцете на сметководител;:) Не са само те тези които нямат вяра на този "калкулатор" наречен Excel:) Виждал съм прекалено много таблици страдащи от този проблем и за това реших да му обърна внимание.

Грешно изградена таблица

Това е типична "студентска" таблица:)  Таблицата е направена по бързия метод... Данните са въведени в колона B. В клетка C1 формулата е =B1/3 а в клетка D1 е =B1-C1. Формулите са дръпнати надолу. В реда "Общо"  е използвана функцията SUM (или просто е натиснат бутона AutoSum) после е натиснат бутона за форматиране в паричен вид (Currency) с валута "лв." и готово. Студентът получава отлична оценка на изпита и всички са доволни:) Само не и човек който може да смята добре.... Лошото е, че тези таблици се създават и на работното място и както казах всеки грамотен човек добре работещ с числа ще каже, че Excel е голям боклук:)

Да проверим: 1666.67 + 3333.33 = 5000 ... Ура тук е вярно! Но.... 5 х 333.33 е 1666.65 (а не 1666.67!) и 5 х 666.67 е 3333.35 (а не 3333.33!)... Голяма работа:) +2 стотинки в едната колона и -2 стотинки в другата:) Кажете го на вашия счетоводител:)

Целия проблем е че както вече споменах Format/Numbers (в случая паричен формат с два знака)  НЕ ЗАКРЪГЛЯ реално съдържанието на клетките ами го закръгля само при показване. Т.е. съдържането на клетките реално е с повече от два знака след десетичната точка (333.3333333.... и 666.66666666666666). Както казах това е само визуална магия... Пуканки за очите:) Изглежда красиво, лесно се прави, но за съжаление е грешно. 

Вариант 1: Първия начин е ние да контролираме закръглянето. За целта в клетката C1 въвеждаме =ROUND(B1/3,2). Дърпаме надолу. И готово (няма нужда от пипане на формулата в D1!). Вече в колонката C1 имаме РЕАЛНО 333.33 и в колонката D реално 666.67 . Без закръгляни или скрити цифри... В резултат на което и сумите отдолу ще бъде 1666.65 и 3333.35! И счетоводителят е доволен:) Този вариант ни дава пълен контрол над данните. За повече информация вижте функциите за закръгляне Round, RoundUp, RoundDown, Ceiling, Trunc и INT. 

Вариант 2: Просто да кажем на Excel да работи с числата ТОЧНО както се виждат. Т.е. "Това което се вижда е точно това което участва в аритметичните операции". В този случай Excel във ваша чест умишлено ще "греши" в зависимост от оформлението:) Визуалната "магия" става реална:) Имайте предвид, че тази настройка важи за ЦЯЛАТА книга!

Ето как става това. 

1. Влиза се в File/Options за Excel 2010  или от офис бутона/Excel Options за Excel 2007.
2. Влиза се в Advanced групата
3. Включва се  Set precision as displayed като преди това се указва за коя книга се отнася! Excel учтиво ще ви предупреди за това че ще се загуби точност. Ако искате да не се губи точност (нормалното състояние) просто изключете настройката.

Настройки на закръгляне спрямо визуализацията на числата

Ми това е:) Имайте предвид тези тънкости когато правите сериозни таблици в които има деление.... Успех:)