| OSDev http://osdev.su/ |
|
| Странности математики Excel http://osdev.su/viewtopic.php?f=18&t=1065 |
Страница 1 из 1 |
| Автор: | Yoda [ 12 мар 2015, 13:43 ] |
| Заголовок сообщения: | Странности математики Excel |
Столкнулся с двумя странностями в математических формулах в экселе. Причём, если природу одной из них я понимаю, то природу другой - нет. Странность №1, понятная. Берём колонку из 103 или более чисел 1000. Рассчитаем их среднее арифметическое. Очевидно, результат 1000. То же самое - среднее гармоническое. Теперь считаем среднее геометрическое (СРГЕОМ) и любуемся ошибкой экселя. Он считает, что результат будет #ЧИСЛО! Понятно, откуда взялся этот глюк, однако можно было бы легко модифицировать математику так, чтобы ошибки не возникало. Странность №2, непонятная. Берём матрицу целых чисел: 1 1 1 1 2 -1 3 4 1 Считаем её детерминант (МОПРЕД). Получаем результат... -6,66134E-16! Кто бы мне пояснил, как так можно рассчитать детерминант матрицы (пусть даже и в плавающей арифметике), содержащей только маленькие целые элементы так, чтобы возникла ошибка округления??? |
|
| Автор: | Zealint [ 12 мар 2015, 14:56 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Yoda писал(а): Странность №1, понятная. Не получилось воспроизвести в Excel 2013. Yoda писал(а): Странность №2, непонятная. Я так понимаю, что разработчики, не долго думая, реализовали универсальный алгоритм вроде метода исключения Гаусса для приведения к диагональному виду. Собственно, любые данные сразу же становятся числами с плавающей точкой. А там достаточно одной операции деления и пары сложений, чтобы снизить точность до 14 знаков. Ещё одно деление - и даже 14 знаков не будет. Оффтоп. Анекдоты был у нас: один аспирант защитил в кандидатской диссертации какой-то навороченный метод решения задачи сетевого планирования, которая решалась в реально существующем проекте. Потом загнали его тесты в Excel, и выяснилось, что тот справляется с задачей гораздо быстрее. Разумеется, мало кому приходило в голову проверять аспиранта до защиты. Сколько из тридцати тысяч аспирантов, успешно защищающихся каждый год в России, делают подобные работы? : ) Надеюсь, не совсем все. К сожалению, мне известны истории и более противные. |
|
| Автор: | Yoda [ 12 мар 2015, 16:59 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Zealint писал(а): Не получилось воспроизвести в Excel 2013. Видимо, пофиксили. У меня 2007. Zealint писал(а): Я так понимаю, что разработчики, не долго думая, реализовали универсальный алгоритм вроде метода исключения Гаусса для приведения к диагональному виду. Обратите внимание на элементы матрицы - при приведении матрицы к диагональному виду там нет ни одного деления на числа, отличные от 1  .
|
|
| Автор: | Zealint [ 12 мар 2015, 17:12 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Yoda писал(а): Обратите внимание на элементы матрицы - при приведении матрицы к диагональному виду там нет ни одного деления на числа, отличные от 1 .Приводить можно по-разному, смотря с чего начинать. В матрице есть число 3, а это уже о многом говорит. Если я хочу вместо числа 3 получить 0, я могу поделить третью строку на 3, затем вычесть, сами знаете, что будет. Тройка в периоде и порождает "шум" в последних знаках промежуточных вычислений, этот шум тянется дальше. В общем, тут никакой мистики я не вижу. |
|
| Автор: | pavia [ 12 мар 2015, 20:35 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Это настолько избитая тема, что даже говорить о ней не хочется. Ошибки есть были и будут. Математика наука точная. До тех пор пока ей не занялись программисты. А ещё они мало предсказуемы. |
|
| Автор: | Yoda [ 13 мар 2015, 11:40 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Zealint писал(а): Если я хочу вместо числа 3 получить 0, я могу поделить третью строку на 3, затем вычесть, сами знаете, что будет. Да, наверное, так и есть. Просто я бы не делил третий ряд на три, а умножал первый, уже приведённый ряд, на 3. Да, деление - потенциально опасная операция, и лучше её по возможности избегать. |
|
| Автор: | Zealint [ 13 мар 2015, 14:42 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Yoda писал(а): Да, наверное, так и есть. Просто я бы не делил третий ряд на три, а умножал первый, уже приведённый ряд, на 3. Да, деление - потенциально опасная операция, и лучше её по возможности избегать. Скорее всего в Excel реализован самый простейший вариант метода исключений. И он подразумевает деление. Умножение же на число быстро приводит к большим числам, что в итоге при плавающей арифметике может дать ничуть не меньшую погрешность. Чтобы правильно написать алгоритм подсчёта определителя, нужно реализовать целый ряд разных функций, срабатывающих в зависимости от входных данных. Например, в случае 3x3 я бы просто использовал расчёт определителя по его определению. Раскрыв все скобки получил бы 6 слагаемых, каждое из которых является произведением трех чисел. Но если в случае с целыми числами этот метод отлично работает, то в случае вещественных нужно делать расчёт устойчивости к входным данным и т. д. Сомневаюсь, что программисты Microsoft будут делать это для Excel, не стоит оно того. |
|
| Автор: | Yoda [ 13 мар 2015, 19:25 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Zealint писал(а): Умножение же на число быстро приводит к большим числам, что в итоге при плавающей арифметике может дать ничуть не меньшую погрешность. Ключевой момент - может. Точнее, умножение даст погрешность только в случае возникновения переполнения. А деление даёт погрешность всегда, когда делитель не кратен степени двойки. |
|
| Автор: | Zealint [ 13 мар 2015, 22:07 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Yoda писал(а): Ключевой момент - может. Точнее, умножение даст погрешность только в случае возникновения переполнения. А деление даёт погрешность всегда, когда делитель не кратен степени двойки. Ну давайте подумаем, почему деление предпочтительнее в общем случае? Я думаю так: деление позволяет оставаться в рамках небольших чисел, тогда как умножение очень быстро выводит промежуточные результаты в большие числа. Нам проще оставить на диагонали единицы, чем какие-то большие числа, которые при вычитании друг из друга (на промежуточных шагах метода исключения) просто не будут вычитаться. Думаю, поэтому классическое описание метода Гаусса содержит деление. Ещё такой вариант. Мне в бытность приходилось считать определители разными способами. Там были и LU-разложения матрицы и некоторые виды рекуррентных формул, какие-то из которых я сам выдумал. При этом возникают такие невероятные комбинации, что при расчёте этого всего в дробях погрешность неудивительна. Очень может быть, что разработчики Excel шли по какому-то из этих путей. Алгоритмы LU(P)-разложения наверняка встроены в какие-нибудь библиотеки, а там в классическом виде требуется, чтобы L содержала единицы на диагонали. Отсюда деление. Когда я лазил по коду Maple, заметил, что там многие вещи тоже делаются через LU-разложение, так что наверняка этот подход и реализован в Excel, а не метод Гаусса. |
|
| Автор: | pavia [ 13 мар 2015, 22:14 ] |
| Заголовок сообщения: | Re: Странности математики Excel |
Целые числа закончатся где то на размерности 6-8 длинные числа закончатся на 8-20 а реальные числа будут работать до 30-100 но с ошибками. |
|
| Страница 1 из 1 | Часовой пояс: UTC + 3 часа |
| Powered by phpBB® Forum Software © phpBB Group http://www.phpbb.com/ |
|