Мне нужно использовать функцию СЛУЧМЕЖДУ Microsoft Excel, и я знаю, что новый набор случайных чисел генерируется каждый раз, когда вы делаете что-либо еще в любом месте рабочего листа. Я также знаю об обходных путях, чтобы «заморозить» набор случайных чисел, который был сгенерирован, скажем, для сортировки n списков случайных чисел от a до b. Мой вопрос таков: ПОЧЕМУ сгенерированные случайные числа изменяются? Есть ли что-то в алгоритме, что требует этого? FWIW, Google Sheets, LibreOffice Calc, Apple Numbers — все они ведут себя одинаково.
решение1
Чарльз Уильямс хорошо объяснил, как Excel вычисляет вещи и почему.
http://www.decisionmodels.com/calcsecretsi.htm
По сути, если рабочая книга содержит изменчивые функции (см. список Чарльза, поскольку то, что является изменчивой функцией, менялось с годами в разных версиях Excel), пересчет всей рабочей книги будет запущен при изменении любой ячейки в рабочей книге (если Excel настроен на автоматический расчет). Если в рабочей книге нет изменчивых функций, будут пересчитаны только те ячейки, которые затронуты последним изменением.
Другие здесь утверждали, что автоматический расчет всегда будет рассчитывать все в рабочей книге при изменении любой ячейки, но это неверно. Только изменчивые функции вызывают автоматический пересчет всех ячеек в рабочей книге. Без изменчивых функций Excel пересчитывает только то, что нужно пересчитать.
Вот почему люди вроде меня, которые знают разницу, настоятельно рекомендуют избегать изменчивых функций, таких как OFFSET() или INDIRECT() в ячейках листа, если это возможно, поскольку они замедлят работу книги, вызывая полный пересчет после каждого изменения ячейки. Изучение использования INDEX() вместо OFFSET() может привести к значительному повышению скорости, поскольку Index не изменчив (см. статью Чарльза для получения более подробной информации).
Расчетный движок Excel основан на философии «пересчитай или умри», которая отличала Excel от конкурирующих продуктов, когда он находился в стадии разработки. Ознакомьтесь с этой статьей:https://www.geekwire.com/2015/recalc-or-die-30-years-later-microsoft-excel-1-0-vets-recount-a-project-that-broved-the-odds/
решение2
Другие ответы фокусируются на механике пересчета и роли изменчивых функций, но я думаю, что это упускает часть сути вопроса. Это касается правил, которые были использованы, но не столько «почему». Я сосредоточусь на этом.
Концепт дизайна
Позвольте мне начать с концепции дизайна и строительных блоков большинства современных электронных таблиц. Существует «разделение ответственности». Функции — это выделенные процедуры, которые выполняют определенную задачу и делают это каждый раз, когда их просят. Помимо этого, приложение электронных таблиц контролирует, когда их просят.
Сами функции не содержат никакой логики для выбора пересчета или не пересчета. Природа всех функций Excel заключается в выполнении определенного типа операции при каждом запуске. Ни одна функция не имеет собственной «памяти» своего текущего значения или истории своих прошлых вычислений. Она не может узнать, в первый ли раз ее просят выполнить свою работу. Поэтому ни одна собственная функция не может быть запущена только один раз. Если какая-либо функция пересчитывается, она сделает то, для чего она предназначена, и выдаст новое значение.
Приложение для работы с электронными таблицами содержит некоторый интеллект, который позволяет ему экономить время, пропуская ненужные пересчеты (как описано в ответе Тейлин).толькоПересчет пропускается, если приложение знает, что значение функции не было затронуто изменениями в электронной таблице, вызвавшими необходимость пересчета.
А что, если вам нужно, чтобы функция была выполнена один раз, а затем ее значение сохранилось? Возьмем ваш пример с созданием случайного числа и последующим сохранением результата. Это описывает создание случайно сгенерированных констант, и вы можете сделать это с помощью Excel.
Excel не может знать, как вы хотите использовать его возможности; хотите ли вы продолжать создавать новые значения или сохранить последний набор. Каждый вариант — это вариант использования, который поддерживает Excel. Excel учитывает оба варианта, предоставляя вам инструменты для вычисления новых значений и инструменты для сохранения старых значений. Вы можете создать все, что захотите. Но пользователь несет ответственность за то, чтобы заставить его делать то, что он хочет.
Функция Логика
Давайте рассмотрим логику того, что делает функция. Может ли иметь смысл для логики пересчета пропускать случайную функцию, поскольку ее значение не должно зависеть от других изменений в электронной таблице?
Изменится ли значение функции при пересчете, зависит от ее цели и того, на чем она основана. Расчет на постоянных значениях всегда даст тот же результат. Операция, основанная на значениях ячеек, которые не изменились, даст тот же результат.
Однако некоторые функции разработаны с намерением и целью производить каждый раз другой результат. Рассмотрим, например, функции, основанные на текущем времени. Они дадут новый результат на основе времени при пересчете. Вы не можете иметь функцию, целью которой является создание значения на основе текущего времени, и не обновлять его при пересчете.
Цель контроля пересчета — всегда заставлять все отражать текущее положение вещей при запуске пересчета. Этот критерий не будет выполнен, если функции, основанные на текущем времени, не будут обновлены.
"Случайные" функции, такие как RANDBETWEEN, имеют целью создание нового случайного числа при пересчете. Именно для этого они и предназначены. Вы можете утверждать, что пересчет можно пропустить, поскольку на значение не должны влиять другие события, происходящие в электронной таблице.
Если бы это было поведением по умолчанию, вы бы обновляли электронную таблицу, но случайное значение оставалось бы постоянным. Это не очень случайное поведение. Это поддерживало бы один вариант использования, но не другой. Возвращаясь к базовой концепции дизайна, это обрабатывается как любая другая функция. Поведение по умолчанию заключается в том, чтобы Excel пересчитал его. Если вы хотите сохранить предыдущее значение для вашего варианта использования, вам решать, как это сделать с помощью доступных инструментов.
решение3
Каждое изменение в рабочем листе запускаетавтоматический перерасчетвсех формул, что является поведением по умолчанию.
Вы можете отключить его или заморозить фактическое значение, преобразовав его в чистое значение (в противоположность формуле).
решение4
Несмотря на героические усилия заставить это звучать как фича, а не как ошибка, я считаю крайне маловероятным, что такое поведение обычно выгодно. «Фичи» облегчают нам жизнь, «баги» ее усложняют. В моем случае ввод метки или объяснения в ячейку, на которую не ссылается ни одна другая ячейка, приводит к повторной рандомизации, с которой неудобно иметь дело. Я бы назвал это «багом».
Да, я могу управлять пересчетом вручную (мучает) или могу вставлять значения, чтобы предотвратить это (опять же мучение), но в каких случаях повторная рандомизация, вызванная вводом текста в удаленную область листа, облегчает жизнь?
Даже если есть некоторая логика в обработке «изменчивых» функций, кажется, что было бы тривиально легко инициировать пересчет с помощью соответствующих критериев, отличных от простого нажатия «enter» после ввода текста в несвязанных и неиспользуемых ячейках, или с помощью других совершенно несвязанных правок. Предотвращение этого должно быть по умолчанию, и я не могу себе представить, что это будет сложно реализовать. Если кто-то хочет эту «функцию», он может включить ее, но я не могу себе представить, когда это будет хорошо. В лучшем случае это может быть относительно безвредно. Для меня это большая проблема из-за типа листа, который я создаю.
Наконец, Analysis Toolpak позволяет вам создавать распределения случайных чисел различными способами, и диапазоны чисел, созданные таким образом, вообще не пересчитываются (не рандомизируются), если вы не укажете им сделать это. Поэтому я рекомендую людям использовать Analysis Toolpak, когда он отвечает их потребностям.