В 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 по поводу Excel. Сегодняшняя мысль задуматься:
«Каждый раз, когда вы объединяете клетки, вы убиваете котенка»
Сильвия Юхас
