none
Глюк в Excel 2003 RRS feed

  • Вопрос

  • Обнаружил странный глюк в Excel 2003 (проверил на SP2 и SP3).

    К сожалению, в этом форуме нельзя прикрепить файл, поэтому опишу словами.

    В новой пустой книге вводим в ячейки числовые значения:

    A1: 1.639634

    A2: 1.417759

    A3: 0.221875

    B1: 0 (можно оставить пустой).

    В A4 вводим формулу: =A1-B1-A2-A3.

    А в B4: =A1-A2-A3-B1.

    Ставим для A4 и B4 числовой формат с 30 знаками после запятой. Смотрим результат.

    Если у кого-то есть хоть какие-то соображения на этот счет - поделитесь.

    И еще проверьте, please, в Excel 2007 (у кого есть).

    Проблема встретилась во многих реальных файлах с разными данными (правда

    файлы однотипные). Иначе как глюком это не могу назвать и как с этим бороться не знаю.

    Столкнуться с этой проблемой, как я понял, несложно, особенно, если в файле проводятся множественные

    вычисления, требующие высокой точности (эти хвосты накапливаются), а вот обнаружить достаточно сложно.

    В общем, если кто знает, что с этим делать и куда обращаться - подскажите!

     

    2 ноября 2007 г. 8:35

Ответы

  • Привет!

     

    1) Это не Глюк, а Фича!

    2) Подробности: http://support.microsoft.com/kb/78113/en-us

    3) Еще способ обхода: =(A1-A2-A3)

    4) Формулы листа позволяют работать с макс. мантиссой из 15 цифр

    5) VBA работает с мантиссой до 28 цифр

    Образец кода

    Sub test()
        Dim A
        A = CDec("1234567890123456789012345678")
        A = A + A
        Debug.Print A
        Debug.Print Len(A)
    End Sub

     

     

    5 ноября 2007 г. 11:49

Все ответы

  • в 2007 глюк есть!

     

    1,639634000000000000000000000000
    1,417759000000000000000000000000
    0,221875000000000000000000000000
    0,000000000000000000000000000000  0,000000000000000055511151231258

     

    это пример таблицы из екселя 2007 (соотв. А1-А4 левый столбец и В1-В4 правый столбец)

    формат всех ячеек числовой с 30ю знаками

    2 ноября 2007 г. 9:00
  • В наиболее критичных точках используйте функцию принудительного округления до заданной точности

    =ОКРУГЛ(A1-A2-A3-B1;30).

    В приведенном Вами примере все считается нормально

    2 ноября 2007 г. 10:56
  • Спасибо за ответ, но...

    несколько комментариев:

    Во-первых, округление до 30 знаков в данном случае почти не влияет на результат - обрезаются только разряды после 30-го. Можно округлять до 15 знаков, но

    Во-вторых, а что делать, если надо считать хотя бы с точностью 20 знаков после запятой?

    В-третьих, это не метод устранения глюка, а метод его обхода. А самый лучший метод обхода я уже описал - это использование формулы (A1-B1-A2-A3).

    Вопрос только, как найти места, в которых нужно изменить формулы? Пример-то я привел тривиальный, только для иллюстрации, а в реальных файлах несколько десятков листов с сотнями взаимосвязанных расчетов на каждом.

    Так что вопрос - что делать с глюком - остается открытым.

    Кстати, он чем-то похож на недавно исправленный глюк с отображением чисел в Excel 2007.

    2 ноября 2007 г. 12:00
  • Сергей,

     

    Я не настаиваю, но мне кажется, что как раз перестановка слагаемых - это "обход" проблемы, а использование формулы - это решение. Если идти по пути перестановки слагаемых, то нужно анализировать и другие вариенты, когда "пустая" ячейка находится внутри, когда их несколько, и.т.д ... вам это надо ?

     

    Корень проблемы скорее всего заключается в том, что округления при окончательном выводе в соответствии с форматами отображения и округления при вычислении через функцию выполняются различными библиотеками. Следовательно, для решения проблемы надо найти такую библиотеку, которая даст гарантированно правильный результат.

     

    В Excel есть, к примеру, такой подводный камень. При выводе на печать, если использовать функцию автоматической подгонки результат менее адекватен, чем при задании конкретного масштаба.

     

    В данном случае, проблема не такая страшная, как в случае с отображением чисел на границе FFFF. Если быть увреенным, что функциональный расчет дает верный результат, то его можно применить в критических точках расчетов. не думаю, что их так уж очень много. Можно совершенно точно сказать, что проявится эта ошибка только если она накопится перед операциями умножения на соответствующе очень большое число. Во всех остальных случаях вы ее просто не заметите и легко отбросите на заключительном этапе.

     

    Впрочем, Ваша задача - вам решать.

    2 ноября 2007 г. 13:30
  • Привет!

     

    1) Это не Глюк, а Фича!

    2) Подробности: http://support.microsoft.com/kb/78113/en-us

    3) Еще способ обхода: =(A1-A2-A3)

    4) Формулы листа позволяют работать с макс. мантиссой из 15 цифр

    5) VBA работает с мантиссой до 28 цифр

    Образец кода

    Sub test()
        Dim A
        A = CDec("1234567890123456789012345678")
        A = A + A
        Debug.Print A
        Debug.Print Len(A)
    End Sub

     

     

    5 ноября 2007 г. 11:49
  • Честно говоря, я об этом подозревал, но чтобы так явно - от перестановки вычитаемых

    меняется результат - этого не ожидал ;(

    Спасибо, KL, теперь все стало понятно Smile

    6 ноября 2007 г. 7:08