Проверка корректности вычислений

[Shadowmaker]

Коллеги, всем привет!

Хочу задать вопрос, не столько по самому экселю и конкретным задачам и формулам - сколько по работе с таблицами в принципе. По роду деятельности пришлось столкнуться с задачами очень разными - как простыми, требующими базовых навыков во владении экселем, так и настолько головоломных, в результате которых рождались многомегабайтные файлы с дичайшими расчётами. По своей природе я человек абсолютно иррациональный и для меня аналитическая работа с цифрами это настоящее изнасилование мозга и тест на психоустойчивость. Однако, компания сейчас находится в ситуации когда такую работу проделать очень нужно. В своих же файлах я допускаю самые разнообразные косяки и ошибки. Много времени уходит на их проверку и корректировку. Часто бывает так, что сваяв определённый файл с расчётами, проверяешь его - вроде всё работает как надо и считает корректно. Но потом спустя некоторое время смотришь свежим взглядом и обнаруживаешь косяки. От логических ошибок в алгоритмах расчётов и до технических ошибок в формулах. Например - для меня было абсолютным откровением что нельзя высчитывать среднее по уже рассчитанным процентам в парах чисел из двух рядов - нужно брать суммарные абсолютные от каждого ряда и считать процент от них. Для людей с математическим образованием это очевидно - а я брал в сводных среднее в тоталах и думал что всё корректно...

В связи с этим у меня и возник вопрос.
Как проверять файлы со сложными расчётами на предмет их корректности?
Предвижу, что часть из гуру экселя скажет что это зависит от конкретного файла. Извините, но я с этим не согласен. Должны быть какие-то общие алгоритмы - скорее всего просто вы не задумывались об этом, но регулярно делаете это. Могу привести простой пример - существует мнение, что эффективность в продажах зависит от множества разных факторов, начиная от личностей и заканчивая психологией отношений. Однако, я как человек с огромным опытом продаж знаю точно - любая продажа имеет структуру, т.е. последовательность шагов, простое следование которым чаще всего обеспечивает львиную долю ожидаемого результата. Все остальные факторы начинают играть роль, скорее, вспомогательных или немного мешающих, но не настолько критичных факторов. Их конечно по мере возможности нужно учесть, но не стоит зацикливаться.

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

[vikttur]

В любом случае: сложное делить на простые вычисления (для проверки) и проверять корректность вычислений частями.
Но самое правильное то, что Вы не делаете изначально: впереди, с флагом,  должен идти алгоритм, а уже потом формулы и оформление. Если не уверены в правильности: продумать структуру вычислений, проверить на небольшом примере - только после этого огород городить. Иначе проблемы обеспечены надолго.
Мало знаний математических? Не стесняйтесь обращаться к знающим - за помощью, за советом, для проверки...

[Shadowmaker]

Согласен. И кстати, да - ПЛАНИРОВАНИЕ вычислений (т.е. создание алгоритма) очень здорово помогает.
А вот разбивать вычисления на мелкие шаги не всегда получается. Часть формул, для того чтобы получить результат, приходится делать многоэтажными... подозреваю что это (как не смешно) скорее, от слабого знания экселя, чем от продвинутости в нём.

И ещё момент - а как быть в случаях, когда от промежуточных этапов расчётов может измениться алгоритм?
Например, делая аналитику по продажам, необходимую для расчёта количества и структуры персонала в городах присутствия я вдруг понял что часть городов мне надо закрывать - там высокие риски не сделать план, а изначально в файл закладывалась возможность получать результаты играя только с самой структурой отдела и количеством персонала в городах - но чтобы удалять/добавлять города теперь нужно почти с нуля переделать весь файл. Предусмотреть это было очень сложно - когда я начинал работу над файлом мне даже в страшном сне не могло присниться что у нас могут быть города под сокращение...

[zs]

ЕСЛИ(Результаты находятся в определенных пределах;ИСТИНА;иначе ЛОЖЬ)
С уважением, ZS5

[NooBasTiK]

Согласен. И кстати, да - ПЛАНИРОВАНИЕ вычислений (т.е. создание алгоритма) очень здорово помогает.
А вот разбивать вычисления на мелкие шаги не всегда получается. Часть формул, для того чтобы получить результат, приходится делать многоэтажными... подозреваю что это (как не смешно) скорее, от слабого знания экселя, чем от продвинутости в нём.

И ещё момент - а как быть в случаях, когда от промежуточных этапов расчётов может измениться алгоритм?
Например, делая аналитику по продажам, необходимую для расчёта количества и структуры персонала в городах присутствия я вдруг понял что часть городов мне надо закрывать - там высокие риски не сделать план, а изначально в файл закладывалась возможность получать результаты играя только с самой структурой отдела и количеством персонала в городах - но чтобы удалять/добавлять города теперь нужно почти с нуля переделать весь файл. Предусмотреть это было очень сложно - когда я начинал работу над файлом мне даже в страшном сне не могло присниться что у нас могут быть города под сокращение...

В таких случаях лучше создать базу данных в access или sql. И все проблемы с вычислениями отпадут, алгоритм вычислений можно будет менять на лету

[Prist]

Предвижу, что часть из гуру экселя скажет что это зависит от конкретного файла. Извините, но я с этим не согласен. Должны быть какие-то общие алгоритмы

И все же не соглашусь. Как можно придумать общий алгоритм проверки для...Чего? Для всех алгоритмов вычислений? Т.е. даже не зная, что именно надо получить надо уже знать как проверить корректность результата? Бред. Если бы это было возможно - думаете все сидели бы и вручную проверяли свои формулы? Отнюдь. Excel, как и любой другой программный инструмент лишь выполняет то, что ему говорят делать. И если у Вас в алгоритме ошибка(не в самой формуле) - то как её отловить, не зная что ищем и какими методами? Только если знать заранее результат. А этого мы по-любому знать не можем.
Т.е. по сути желаемое Вам - утопия.
Как Вы думаете, почему толпы ученых всего мира проводят эксперименты по своим теориям? От нечего делать? Нет. У них тоже нет алгоритма проверить правильность своих расчетов. Они лишь предполагают какой-то результат и проверяют на практике - верны ли расчеты. Если расчет не верен - просматривают где косяк. Исправляют. Заново тесты и эксперименты.

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