#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 учтиво ще ви предупреди за това че ще се загуби точност. Ако искате да не се губи точност (нормалното състояние) просто изключете настройката.

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

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