Има грозна грешка в изчислението, която се появява в Excel. Изглежда, че проблемът навлиза дълбоко в механизма за изчисляване на Excel и няма да бъде лесно да бъде отстранен.
В основата на проблема стои един прост факт: Excel съхранява 15 цифри с точност в една клетка. Можете да имате числа с 20 цифри, но всички цифри между последната значима цифра и десетичната запетая трябва да са нула.
Наскоро видях два случая, когато механизмът за изчисление на Excel връщаше грешни резултати. Когато се впуснах в проблема и разгледах основния XML, бях изненадан да видя, че Excel тайно съхранява 17 цифри в XML.
Проблемът е, че Excel ще показва само 15 цифри. Така че смятате, че имате номер, съхраняван като 0.123456789012345, но той наистина се съхранява като 0.12345678901234567.
Не можете да видите последните две цифри. И повечето функции на Excel пренебрегват последните две цифри. Ако * всички * функции игнорираха последните две цифри, нямаше да имаме проблем. Но засега сортирането, RANK и FREQUENCY използват всичките 17 цифри.
По-долу е известен трик за класиране на клетки. Ако имате нужда всеки ранг да се появи точно веднъж, можете да комбинирате RANK и COUNTIF. На изображението по-долу Клер, Фло, Ивана и Люси са изравнени на 115%. Използвайки формулата RANK + COUNTIF, те трябва да бъдат класирани на 5, 6, 7 и 8.
Но формулата се проваля. Два реда са класирани като 7. Това никога не се случва. Четири формули в колона D се уверяват, че 115% в B6, B9, B12 и B15 са еднакви. В =B6=B15формула съобщава, че двете клетки съдържат едни и същи данни.
Докато се опитвах да изолирам проблема, погледнете само функцията RANK. Той трябва да отчита равенство в 4 посоки на 4-то място за хората със 115%. Но по някакъв начин Луси в ред 15 е класирана пред останалите три.
За да разбера, изпратих молба за помощ до другите MVP на Excel. Ян Карел Питерсе отвори файла на Excel и погледна в XML. В XML можете да видите, че те съхраняват 17 цифри с точност. Четирите клетки, които приличат на равенство в Excel, не са обвързани в XML. Един от 115% се съхранява като 1.1500000000000001, а останалите са 1.1499999999999999.
Досега сортирането, класирането и функцията ЧЕСТОТА използват допълнителните цифри. Защо това е проблем? Тъй като разчитаме на RANK и COUNTIF и двете да използват еднакъв брой цифри. С една функция, използваща 15 цифри, а другата с 17 цифри, имате проблем.
Засега решението изглежда преобразува всички ваши отговори с помощта =ROUND(A4,15).
Всеки петък изследвам грешка или друго поведение в Excel. Тази грешка в изчислението е трудна за откриване и се квалифицира като голяма риба.
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Всеки път, когато обедините клетки, вие убивате коте“
Силвия Юхас
