OSDev

для всех
Текущее время: 29 мар 2024, 00:11

Часовой пояс: UTC + 3 часа




Начать новую тему Ответить на тему  [ Сообщений: 10 ] 
Автор Сообщение
 Заголовок сообщения: Странности математики Excel
СообщениеДобавлено: 12 мар 2015, 13:43 
Аватара пользователя

Зарегистрирован: 14 мар 2011, 12:31
Сообщения: 970
Откуда: Дагоба
Столкнулся с двумя странностями в математических формулах в экселе. Причём, если природу одной из них я понимаю, то природу другой - нет.

Странность №1, понятная.
Берём колонку из 103 или более чисел 1000. Рассчитаем их среднее арифметическое. Очевидно, результат 1000. То же самое - среднее гармоническое. Теперь считаем среднее геометрическое (СРГЕОМ) и любуемся ошибкой экселя. Он считает, что результат будет #ЧИСЛО! Понятно, откуда взялся этот глюк, однако можно было бы легко модифицировать математику так, чтобы ошибки не возникало.

Странность №2, непонятная.
Берём матрицу целых чисел:
1 1 1
1 2 -1
3 4 1
Считаем её детерминант (МОПРЕД). Получаем результат... -6,66134E-16! Кто бы мне пояснил, как так можно рассчитать детерминант матрицы (пусть даже и в плавающей арифметике), содержащей только маленькие целые элементы так, чтобы возникла ошибка округления???

_________________
Yet Other Developer of Architecture.
The mistery of Yoda’s speech uncovered is:
Just an old Forth programmer Yoda was.

<<< OS Boot Tools. >>>


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 12 мар 2015, 14:56 
Аватара пользователя

Зарегистрирован: 17 фев 2013, 16:13
Сообщения: 163
Yoda писал(а):
Странность №1, понятная.

Не получилось воспроизвести в Excel 2013.

Yoda писал(а):
Странность №2, непонятная.

Я так понимаю, что разработчики, не долго думая, реализовали универсальный алгоритм вроде метода исключения Гаусса для приведения к диагональному виду. Собственно, любые данные сразу же становятся числами с плавающей точкой. А там достаточно одной операции деления и пары сложений, чтобы снизить точность до 14 знаков. Ещё одно деление - и даже 14 знаков не будет.

Оффтоп. Анекдоты был у нас: один аспирант защитил в кандидатской диссертации какой-то навороченный метод решения задачи сетевого планирования, которая решалась в реально существующем проекте. Потом загнали его тесты в Excel, и выяснилось, что тот справляется с задачей гораздо быстрее. Разумеется, мало кому приходило в голову проверять аспиранта до защиты. Сколько из тридцати тысяч аспирантов, успешно защищающихся каждый год в России, делают подобные работы? : ) Надеюсь, не совсем все. К сожалению, мне известны истории и более противные.


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 12 мар 2015, 16:59 
Аватара пользователя

Зарегистрирован: 14 мар 2011, 12:31
Сообщения: 970
Откуда: Дагоба
Zealint писал(а):
Не получилось воспроизвести в Excel 2013.

Видимо, пофиксили. У меня 2007.

Zealint писал(а):
Я так понимаю, что разработчики, не долго думая, реализовали универсальный алгоритм вроде метода исключения Гаусса для приведения к диагональному виду.

Обратите внимание на элементы матрицы - при приведении матрицы к диагональному виду там нет ни одного деления на числа, отличные от 1 :).

_________________
Yet Other Developer of Architecture.
The mistery of Yoda’s speech uncovered is:
Just an old Forth programmer Yoda was.

<<< OS Boot Tools. >>>


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 12 мар 2015, 17:12 
Аватара пользователя

Зарегистрирован: 17 фев 2013, 16:13
Сообщения: 163
Yoda писал(а):
Обратите внимание на элементы матрицы - при приведении матрицы к диагональному виду там нет ни одного деления на числа, отличные от 1 :).

Приводить можно по-разному, смотря с чего начинать. В матрице есть число 3, а это уже о многом говорит. Если я хочу вместо числа 3 получить 0, я могу поделить третью строку на 3, затем вычесть, сами знаете, что будет. Тройка в периоде и порождает "шум" в последних знаках промежуточных вычислений, этот шум тянется дальше. В общем, тут никакой мистики я не вижу.


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 12 мар 2015, 20:35 
Аватара пользователя

Зарегистрирован: 16 май 2007, 23:46
Сообщения: 1126
Это настолько избитая тема, что даже говорить о ней не хочется. Ошибки есть были и будут. Математика наука точная. До тех пор пока ей не занялись программисты. А ещё они мало предсказуемы.


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 13 мар 2015, 11:40 
Аватара пользователя

Зарегистрирован: 14 мар 2011, 12:31
Сообщения: 970
Откуда: Дагоба
Zealint писал(а):
Если я хочу вместо числа 3 получить 0, я могу поделить третью строку на 3, затем вычесть, сами знаете, что будет.

Да, наверное, так и есть. Просто я бы не делил третий ряд на три, а умножал первый, уже приведённый ряд, на 3. Да, деление - потенциально опасная операция, и лучше её по возможности избегать.

_________________
Yet Other Developer of Architecture.
The mistery of Yoda’s speech uncovered is:
Just an old Forth programmer Yoda was.

<<< OS Boot Tools. >>>


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 13 мар 2015, 14:42 
Аватара пользователя

Зарегистрирован: 17 фев 2013, 16:13
Сообщения: 163
Yoda писал(а):
Да, наверное, так и есть. Просто я бы не делил третий ряд на три, а умножал первый, уже приведённый ряд, на 3. Да, деление - потенциально опасная операция, и лучше её по возможности избегать.

Скорее всего в Excel реализован самый простейший вариант метода исключений. И он подразумевает деление. Умножение же на число быстро приводит к большим числам, что в итоге при плавающей арифметике может дать ничуть не меньшую погрешность. Чтобы правильно написать алгоритм подсчёта определителя, нужно реализовать целый ряд разных функций, срабатывающих в зависимости от входных данных. Например, в случае 3x3 я бы просто использовал расчёт определителя по его определению. Раскрыв все скобки получил бы 6 слагаемых, каждое из которых является произведением трех чисел. Но если в случае с целыми числами этот метод отлично работает, то в случае вещественных нужно делать расчёт устойчивости к входным данным и т. д. Сомневаюсь, что программисты Microsoft будут делать это для Excel, не стоит оно того.


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 13 мар 2015, 19:25 
Аватара пользователя

Зарегистрирован: 14 мар 2011, 12:31
Сообщения: 970
Откуда: Дагоба
Zealint писал(а):
Умножение же на число быстро приводит к большим числам, что в итоге при плавающей арифметике может дать ничуть не меньшую погрешность.

Ключевой момент - может. Точнее, умножение даст погрешность только в случае возникновения переполнения. А деление даёт погрешность всегда, когда делитель не кратен степени двойки.

_________________
Yet Other Developer of Architecture.
The mistery of Yoda’s speech uncovered is:
Just an old Forth programmer Yoda was.

<<< OS Boot Tools. >>>


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 13 мар 2015, 22:07 
Аватара пользователя

Зарегистрирован: 17 фев 2013, 16:13
Сообщения: 163
Yoda писал(а):
Ключевой момент - может. Точнее, умножение даст погрешность только в случае возникновения переполнения. А деление даёт погрешность всегда, когда делитель не кратен степени двойки.

Ну давайте подумаем, почему деление предпочтительнее в общем случае?

Я думаю так: деление позволяет оставаться в рамках небольших чисел, тогда как умножение очень быстро выводит промежуточные результаты в большие числа. Нам проще оставить на диагонали единицы, чем какие-то большие числа, которые при вычитании друг из друга (на промежуточных шагах метода исключения) просто не будут вычитаться. Думаю, поэтому классическое описание метода Гаусса содержит деление.

Ещё такой вариант. Мне в бытность приходилось считать определители разными способами. Там были и LU-разложения матрицы и некоторые виды рекуррентных формул, какие-то из которых я сам выдумал. При этом возникают такие невероятные комбинации, что при расчёте этого всего в дробях погрешность неудивительна. Очень может быть, что разработчики Excel шли по какому-то из этих путей. Алгоритмы LU(P)-разложения наверняка встроены в какие-нибудь библиотеки, а там в классическом виде требуется, чтобы L содержала единицы на диагонали. Отсюда деление. Когда я лазил по коду Maple, заметил, что там многие вещи тоже делаются через LU-разложение, так что наверняка этот подход и реализован в Excel, а не метод Гаусса.


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: Странности математики Excel
СообщениеДобавлено: 13 мар 2015, 22:14 
Аватара пользователя

Зарегистрирован: 16 май 2007, 23:46
Сообщения: 1126
Целые числа закончатся где то на размерности 6-8 длинные числа закончатся на 8-20 а реальные числа будут работать до 30-100 но с ошибками.


Вернуться к началу
 Профиль  
 
Показать сообщения за:  Поле сортировки  
Начать новую тему Ответить на тему  [ Сообщений: 10 ] 

Часовой пояс: UTC + 3 часа


Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 7


Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете добавлять вложения

Найти:
Перейти:  
Создано на основе phpBB® Forum Software © phpBB Group
Русская поддержка phpBB