Объем материалов рассчитывается по норме excel как отключить
Excel умолчанию пересчитывает все формулы во всех листах всех открытых книг после каждого введения данных. Если лист содержит сотни или тысячи формул автоматический пересчет начинает заметно замедлять процесс работы с программой. Рассмотрим, как можно настроить Excel для повышения его производительности и беспрепятственной работы.
Автоматический и ручной пересчет
Для книги, которая содержит сотни сложных формул можно настроить пересчет по востребованию пользователя. Для этого:
- Введите формулу на чистый лист (чтобы можно было проверить как работает данный пример).
- Выберите инструмент: «Формулы»-«Параметры вычислений»-«Вручную».
- Убедитесь, что теперь после ввода данных в ячейку (например, число 7 вместо 1 в ячейке A2 как на рисунке), формула не пересчитывает результат автоматически. Пока пользователь не нажмет клавишу F9 (или SFIFT+F9).
Внимание! Быстрая клавиша F9 – выполняет пересчет во всех формулах книги на всех листах. А Комбинация горячих клавиш SHIFT+F9 – выполняет пересчет только на текущем листе.
Если на листе не содержится множество формул, пересчет которых может тормозить Excel, то нет смысла использовать выше описанный пример. Но на будущее все же стоит знать о такой возможности. Ведь со временем придется сталкиваться со сложными таблицами с множеством формул. Кроме того данная функция может быть включена случайно и нужно знать где ее выключить для стандартного режима работы.
Как отображать формулу ячейке Excel
В ячейках Excel мы видим только результат вычислений. Сами же формулы можно увидеть в строке формул (по отдельности). Но иногда нам нужно одновременно просмотреть все функции в ячейках (например, чтобы их сравнить и т.п.).
Чтобы наглядно отобразить пример данного урока нам потребуется лист содержащий формулы:
Изменим настройки Excel так, чтобы ячейки отображали формулы, а не результат их вычисления.
Чтобы добиться данного результата, нужно выбрать инструмент: «Формулы»-«Показать» (в разделе «Зависимости формул»). Чтобы выйти из этого режима нужно выбрать данный инструмент повторно.
Так же можно использовать комбинацию горячих клавиш CTRL+` (над клавишей Tab). Данная комбинация работает в режиме переключателя, то есть повторное нажатие возвращает в обычный режим отображения результатов вычисления в ячейках.
Примечание. Все выше описанные действия распространяются только на режим отображения ячеек одного листа. То есть на других листах при необходимости потребуется выполнять эти действия повторно.
По умолчанию Excel автоматически пересчитывает все формулы при открытии рабочего листа или при изменении значений, формул или имен в зависимых формулах. Процесс пересчета может длиться от нескольких секунд до нескольких минут в зависимости от количества и сложности формул в рабочей книге.
Пока формулы пересчитываются, указатель мыши меняется на песочные часы, и внести какие-либо изменения на рабочий лист невозможно. Для экономии времени можно временно отключить режим автоматических вычислений в Excel до завершения внесения данных. Сделать это можно без особого труда, и мы покажем Вам как.
Если Вы не хотите отключать режим автоматических вычислений, и Ваш компьютер имеет несколько процессоров, тогда можете применить режим многопоточных вычислений. Он поможет немного ускорить процесс пересчета в зависимости от количества процессоров компьютера. О том, как включить режим многопоточных вычислений в Excel, мы расскажем дальше.
Параметры вычислений
Следующий список поясняет опции, которые доступны в разделе Calculation options (Параметры вычислений):
- Automatic (Автоматически) – пересчитывает все зависимые формулы и обновляет все открытые или внедрённые диаграммы при любом изменении значения, формулы или имени. Данная настройка установлена по умолчанию для каждого нового рабочего листа Excel.
- Automatic except for data tables (Автоматически, кроме таблиц данных) – пересчитывает все зависимые формулы и обновляет все открытые или внедрённые диаграммы, за исключением таблиц данных. Для пересчета таблиц данных, когда данная опция выбрана, воспользуйтесь командой Calculate Now (Пересчет), расположенной на вкладке Formulas (Формулы) или клавишей F9.
- Manual (Вручную) – пересчитывает открытые рабочие листы и обновляет открытые или внедрённые диаграммы только при нажатии команды Calculate Now (Пересчет) или клавиши F9, а так же при использовании комбинации клавиши Ctrl+F9 (только для активного листа).
- Recalculate workbook before saving (Пересчитывать книгу перед сохранением) – пересчитывает открытые рабочие листы и обновляет открытые или внедрённые диаграммы при их сохранении даже при включенной опции Manual (Вручную). Если Вы не хотите, чтобы при каждом сохранении зависимые формулы и диаграммы пересчитывались, просто отключите данную опцию.
- Enable iterative calculation (Включить итеративные вычисления) – разрешает итеративные вычисления, т.е. позволяет задавать предельное количество итераций и относительную погрешность вычислений, когда формулы будут пересчитываться при подборе параметра или при использовании циклических ссылок. Более детальную информацию о подборе параметров и использовании циклических ссылок можно найти в справке Microsoft Excel.
- Maximum Iterations (Предельное число итераций) – определяет максимальное количество итераций (по умолчанию — 100).
- Maximum Change (Относительная погрешность) – устанавливает максимально допустимую разницу между результатами пересчета (по умолчанию — 0.001).
Вы также можете переключаться между тремя основными режимами вычислений, используя команду Calculation Options (Параметры вычислений) в разделе Calculation (Вычисление) на вкладке Formulas (Формулы). Однако, если необходимо настроить параметры вычислений, все же придется обратиться к вкладке Formulas (Формулы) диалогового окна Excel Options (Параметры Excel).
Многопоточные вычисления в Excel
Excel 2013 располагает режимом многопоточных вычислений, который позволяет сокращать время пересчета формул. Если Вы предпочитаете не отключать автоматическое вычисление, попробуйте использовать указанную опцию (при наличии у компьютера нескольких процессоров), чтобы сократить время пересчета.
- Чтобы включить режим многопоточных вычислений, нажмите на вкладку File (Файл) и выберите пункт Options (Параметры), чтобы открыть диалоговое окно Excel Options (Параметры Excel). Затем нажмите Advanced (Дополнительно).
- Опуститесь вниз до раздела Formulas (Формулы) и установите флажок возле пункта Enable multi-threaded calculation (Включить многопоточные вычисления). Вы можете вручную установить количество потоков вычисления, которое необходимо использовать, или указать Excel задействовать все процессоры компьютера, выбрав Use all processors on this computer (Использовать все процессоры данного компьютера).
Если на компьютере уже запущены другие программы, Вы можете ограничить число используемых процессоров для пересчета формул электронной таблицы Excel.
Испытайте разные настройки режимов автоматических и многопоточных вычислений, чтобы определить, какие из них лучше подходят для Вашего компьютера.
Для вычисления среднеквадратического отклонения в Microsoft Office Excel используется специальная формула, которой можно пользоваться несколькими способами. О них пойдет речь в данной статье.
Как вычислить среднее квадратическое отклонение в Excel
Это один из показателей вариации. В теории вероятностей этот показатель рассчитывается по формуле:
Путь к окошку «Мастер функций» в Microsoft Office Excel. Подробный алгоритм действий на одном скриншоте
В Эксель пользователю не нужно будет вручную рассчитывать среднеквадратичное отклонение. Существует ряд способов, позволяющих найти эту величину в кратчайшие сроки с помощью встроенных в Excel инструментов. Далее будут рассмотрены самые распространенные из них.
Способ 1. С помощью Мастера функций
Данный метод расчета стандартного отклонения в Excel занимает минимальное количество времени. Вычисление производится следующим образом:
- Левой клавишей манипулятора на рабочем листе программы выделить ячейку, в которой будет отображаться результат вычисления.
- Кликнуть по кнопке «Вставка функции», расположенной с левой стороны от строки ввода.
- После выполнения предыдущего действия откроется окошко Мастера функций. Здесь пользователю потребуется найти строку «СТАНДОТКЛОН.В», выделить ее ЛКМ и нажать на «ОК» внизу меню. В списке функций также есть записи «СТАНДОТКЛОН.Г» и просто «СТАНДОТКЛОН». На них нажимать не нужно!
- В следующем окне аргументов функции необходимо прописать числа совокупности в каждом поле. Если эти значения уже написаны на рабочем листе Excel, то можно указать координаты соответствующих ячеек.
- Нажать на «ОК» внизу меню «Аргументы функции», когда все строчки будут заполнены.
- Проверить результат. В выделенной на первом этапе ячейке будет прописано число. Это и есть среднее квадратичное отклонение.
Способ 2. Расчет стандартного отклонения в разделе «Формулы»
Через вкладку «Формулы» в программе Excel также возможно рассчитать стандартное отклонение. Алгоритм вычисления состоит из следующих шагов:
- По аналогичной схеме выделить любую свободную ячейку на рабочем листе. В этот элемент в дальнейшем будет выводиться результат.
- В графе инструментов сверху главного меню программы найти слово «Формулы» и щелкнуть по нему ЛКМ.
- В открывшейся области найти подраздел «Библиотека функций, а затем развернуть вкладку «Другие функции».
- В контекстном меню выбрать тип «Статистические» и поставить курсор мышки на это слово.
- Развернется дополнительное контекстное окно, в котором пользователю необходимо щелкнуть по строчке «СТАНДОТКЛОН.В».
- В запустившемся окне «Аргументы функции» надо заполнить два поля, указав координаты соответствующих ячеек на рабочем листе или в табличном массиве. Нужные значения также можно прописать вручную.
- После выполнения этих манипуляций нажать на «ОК».
- Удостовериться, что в указанной ранее ячейке отобразился результат работы формулы – число, характеризующее среднеквадратичное отклонение.
Важно! Если необходимо посчитать стандартное отклонение у нескольких параметров, то исходную формулу можно растянуть на оставшиеся ячейки табличного массива.
Способ 3. Ручной ввод формулы среднеквадратичного отклонения в Эксель
Чтобы рассчитать стандартное отклонение в Microsoft Office Excel, можно ввести специальную формулу с клавиатуры, результат будет одинаковым. Такой метод вычисления заключается в выполнении следующих этапов:
- Поставить курсор мыши в ячейку, куда будет выводиться результат.
- В выделенный элемент ввести с клавиатуры формулу «=СТАНДОТКЛОН.В(а,b,c,d)». Вместо букв в скобках нужно указать соответствующие аргументы. Это числа из табличного массива данных.
- Нажать на «Enter» для завершения процедуры.
- Проверить результат. В итоге в ячейке должно отобразиться конкретное значение среднеквадратичного отклонения.
Дополнительная информация! Также для расчета можно использовать формулу «СТАНОТКЛОН.Г()». Получится идентичный результат. Данный метод будет работать независимо от версии программного обеспечения.
Способ 4. Ручной подсчет
Такую операцию в Microsoft Office Excel нецелесообразно выполнять. Данный метод основан на применении обычного калькулятора, который также присутствует в Эксель. Для ручного счета необходимо проделать следующие действия по алгоритму:
- Левой клавишей манипулятора выделить ячейку табличного массива, в которую будет выводиться результат.
- Поставить знак «=» и с клавиатуры компьютера прописать числовые значения для расчета среднеквадратичного отклонения, указывая координаты соответствующих ячеек из таблички. Здесь надо подставлять параметры в формулу из первого рисунка.
- После написания формулы нажать на «Enter» и убедиться, что в ячейке отобразился результат стандартного отклонения.
Обратите внимание! Формулу для вычисления среднеквадратичного отклонения, написанную вручную в Excel, также можно растянуть на оставшиеся ячейки для их автоматического заполнения.
Заключение
Таким образом, стандартное отклонение в Microsoft Office Excel рассчитывается с помощью формулы или выбора соответствующей функции. Основные методы вычисления данного параметра были рассмотрены выше.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Многопотоковый пересчет листов впервые появился в Microsoft Office Excel 2007. Вы можете настроить до 1024 одновременных потоков, которые Excel будет использовать при пересчете, независимо от количества процессоров или ядер на компьютере.
С каждым потоком связаны затраты ресурсов операционной системы, поэтому не настраивайте больше потоков, чем нужно.
Если компьютер имеет несколько процессоров или ядер, за эффективное распределение потоков между процессорами отвечает операционная система.
Обзор многопотокового пересчета листов в Excel
Excel пытается определить части цепочки вычисления, которые можно пересчитывать одновременно в разных потоках. Ниже в качестве примера приведено очень простое дерево (где "x ← y" означает, что y зависит только от x).
Рисунок 1. Параллельные вычисления в разных потоках
Когда выполнено вычисление для ячейки A1, можно последовательно выполнить вычисление для ячеек A2 и A3 в одном потоке, в то время как в другом потоке последовательно выполняются вычисления для B1 и C1. Это возможно при условии, что все ячейки потокобезопасны.
Термин "Потокобезопасная ячейка" обозначает ячейку, содержащую только потокобезопасные функции. Список потоконебезопасных елементов см. в Какие элементы в Excel считаются потокобезопасными, а какие — нет.
Большинство используемых на практике книг содержат гораздо более сложные деревья зависимостей, чем этот пример. Кроме того, время пересчета ячейки не известно до завершения вычисления и может варьироваться в широких пределах в зависимости от аргументов функции. Чтобы получить наилучшие результаты, Excel пытается улучшить порядок вычисления после каждого вычисления, пока это возможно.
Excel использует один основной поток, чтобы запускать или выполнять:
функции интерфейса диспетчера надстроек XLL (функция xlAutoOpen и т. д.)
пользовательские команды Microsoft Visual Basic для приложений (Microsoft Visual Basic for Applications, VBA), часто именуемые макросами;
пользовательские функции VBA;
встроенные потоконебезопасные функции листа (см. список в следующем разделе);
пользовательские команды и функции листа макросов XLM;
функции и команды надстроек COM;
функции и операторов в выражениях условного форматирования;
функции и операторы в определениях определенных имен, используемых в формулах листа;
принудительную оценку выражения в поле редактирования формулы с помощью клавиши F9.
Вычисления по всем формулам листа, независимо от того, потокобезопасны функции или нет, выполняются в основном потоке, если не настроено использование нескольких потоков в Excel. Когда пользователь указывает, что следует использовать несколько потоков, дополнительные потоки используются для потокобезопасных ячеек. Обратите внимание, что основной поток также может использоваться для потокобезопасных ячеек, когда это целесообразно для балансировки нагрузки.
Стоит отметить, что Excel не выполняет более одной команды за раз, поэтому необязательно применять те же меры предосторожности, что и при написании потокобезопасных функций, например использовать локальную память потока и критические секции.
Какие элементы в Excel считаются потокобезопасными, а какие — нет
Excel считает покобезопасными только следующие элементы:
Все унарные и двоичные операторы в Excel.
Почти все встроенные функции листа, начиная с Excel 2007 (см. список исключений).
Потоконебезопасные встроенные функции листа:
PHONETIC
CELL, когда используется аргумент "format" или "address"
INDIRECT
GETPIVOTDATA
CUBEMEMBER
CUBEVALUE
CUBEMEMBERPROPERTY
CUBESET
CUBERANKEDMEMBER
CUBEKPIMEMBER
CUBESETCOUNT
ADDRESS, когда указывается пятый параметр (sheet_name)
Любая функция базы данных (DSUM, DAVERAGE, и т. д.), которая ссылается на сводную таблицу
ERROR.TYPE
HYPERLINK
Указанные ниже действия считаются небезопасными:
пользовательские функции VBA;
пользовательские функции надстроек COM;
пользовательские функции листа макросов XLM;
Вызов информационных функций XLM, например xlfGetCell (GET.CELL).
Вызов xlfSetName (SET.NAME) для определения или удаления внутренних имен XLL.
Вызов потоконебезопасных пользовательских функций с помощью xlUDF.
Вызов функции xlfEvaluate для выражений, содержащих потоконебезопасные функции или определенные имена, определения которых содержат потоконебезопасные функции.
Вызов функции xlAbort для сброса условия останова.
Вызов функции xlCoerce для получения значения невычисленной ссылки на ячейку.
Функции обратного вызова для C API — потокобезопасные:
xlCoerce (кроме случаев, когда не удается выполнить приведение невычисленных ссылок на ячейки)
xlFree
xlStack
xlSheetId
xlSheetNm
xlAbort (кроме случаев сброса состояния останова)
xlGetInst
xlGetHwnd
xlGetBinaryName
xlDefineBinaryName
Единственное исключение — функция xlSet, которая является эквивалентом команды и, поэтому, не может вызываться из функций листа.
Ниже приведены правила, которые необходимо соблюдать разработчикам при создании потокобезопасных функций.
Не вызывайте ресурсы в других библиотеках DLL, которые могут быть потоконебезопасными.
Не осуществляйте потоконебезопасные вызовы с помощью C API или COM.
Защищайте ресурсы, которые могут использоваться одновременно несколькими потоками, с помощью критических секций.
Используйте локальную память потока для хранения данных потока и заменяйте статические переменные в функциях локальными переменными потока.
В Excel действует дополнительное ограничение: потокобезопасные функции невозможно зарегистрировать как эквивалентные функциям листа макросов, поэтому они не могут вызывать информационные функции XLM и получать значения непересчитанных ячеек.
Состязание за память
Многопоковые системы должны решать две основные проблемы:
Как защитить память, с которой выполняется чтение или на которую выполняется запись, с помощью нескольких потоков.
Как создать память, связанную с выполняемым потоком и доступную только ему, и получить к ней доступ.
В операционной системе Windows и пакете средств разработки программного обеспечения (SDK) для Windows есть средства для их решения: критические секции и API для локального хранения данных потока (TLS) соответственно. Дополнительные сведения см. в статье Memory Management in Excel.
Первая проблема может возникнуть, например, когда двум функциям листа (или двум параллельно выполняемым экземплярам одной функции) нужен доступ к глобальной переменной в проекте DLL (например, для ее изменения). Помните, что эта переменная может быть скрыта в глобально доступном экземпляре объекта класса.
Вторая проблема может возникнуть, например, когда функция листа объявляет статическую переменную или объект в коде функции. Компилятор C/C++ создает только одну копию, которую используют все потоки. Это означает, что один экземпляр функции может изменить значение, а другой (в другом потоке) может использовать ранее заданное значение.
Примеры применения MTR
Любой XLL-модуль, который экспортирует функции листа, может использовать многопотоковый пересчет (MTR) в Excel, если эти функции не должны выполнять потоконебезопасные действия. Это позволяет Excel максимально быстро выполнять пересчет в книгах, в которых они используются, и поэтому MTR рекомендуется применять всегда.
В частности, MTR имеет большое влияние на время пересчета книг, которые вызывают пользовательские функции (UDF), которые в свою очередь вызывают внешние процессы для получения желаемых результатов. Рассмотрим функцию UDF, вызывающую удаленный сервер, который может обрабатывать большое количество запросов одновременно, и книгу, содержащую большое количество вызовов такой функции. Если пересчет книги однопотоковый, следующий вызов UDF и удаленного сервера не может начаться, пока не завершится предыдущий. В результате возможность сервера обрабатывать сразу несколько вызовов не используется. Если пересчет книги многопотоковый, Excel может совершать несколько вызовов одновременно или в быстрой последовательности.
Если в Excel и на сервере настроено использование одинакового количества потоков (N), при этом топология дерева зависимостей книги позволяет это, общее время пересчета можно сократить до значения, которое стремится к 1/N. Это возможно, даже если у клиентского компьютера (на котором обрабатывается книга) всего один процессор, особенно если время вызова сервера невелико по сравнению с временем обработки вызова сервером.
С каждым дополнительным потоком связаны затраты ресурсов операционной системы. Поэтому оптимальное количество потоков, которое должно использовать приложение Excel, для каждой книги, сервера и клиентского компьютера определяется опытным путем.
Рассмотрим компьютер с одним процессором, на котором запущено приложение Excel и обрабатывается книга, содержащая 1000 ячеек. Она вызывает функцию UDF, которая, в свою очередь, вызывает один или несколько удаленных серверов. Предположим, что 1000 ячеек не зависят друг от друга, поэтому Excel не нужно ожидать завершения одного вызова для совершения другого. (Это условие можно нарушить без последствий для этого примера.) Если серверы могут обрабатывать 100 запросов одновременно, а в Excel настроено использование 100 потоков, время выполнения можно сократить до 1/100 (сотой части от времени выполнения однопотокового пересчета). Чтобы приложение Excel могло распределять вызовы между потоками, а операционная система могла управлять 100 потоками, требуются значительные ресурсы. Это показывает, что на практике такого значительного сокращения времени не будет. Мы также предполагаем, что сервер характеризуется хорошей масштабируемостью, и что одновременная обработка 100 задач сильно не повлияет на время выполнения отдельных задач.
Пример практического применения, при котором этот способ дает отличные результаты, — использование методов Монте-Карло, а также выполнение других ресурсоемких задач, которые можно разделить на более мелкие подзадачи и обработать на серверах.
Рекомендации по использованию служб Excel
Службы Excel поддерживают загрузку, вычисление и обработку электронных таблиц Excel на сервере. Пользователи могут получить доступ к электронным таблицам и работать с ними, используя стандартные средства браузера.
В Microsoft Excel все открытые документы используют один и тот же режим вычисления независимо от режима, в котором они были сохранены.
Дополнительная информация
Чтобы объяснить, как определяется режим вычисления, в этой статье рассматриваются следующие гипотетические документы:
Имя файла | Тип документа | Режим, используемый для сохранения файла |
---|---|---|
Auto1.xlsx | Книга | Автоматически |
Manual1.xlsx | Книга | Manual |
Auto2.xlsx | Книга | Автоматически |
Следующие инструкции применяются к режимам вычисления в Excel:
- Первый открытый документ использует режим вычисления, в котором он был сохранен в последний раз. Документы, которые будут открываться позже, используют тот же режим. Например, если открыть Auto1.xlsx открыть Manual1.xlsx, в обоих документах используется автоматическое вычисление (режим, используемый Auto1.xlsx). Если открыть Manual1.xlsx, а затем открыть Auto1.xlsx, в обоих документах используется вычисление вручную.
- Изменение режима вычисления одного открытого документа изменяет режим для всех открытых документов. Например, если Auto1.xlsx и Auto2.xlsx открыты, изменение режима вычисления Auto2.xlsx вручную также изменяет режим Auto1.xlsx на ручной.
- Все листы, содержащиеся в книге, используют один и тот же режим вычисления. Например, если Auto2.xlsx содержит три листа, при изменении режима вычисления первого листа на ручной режим также изменяется режим вычисления на ручной на двух других листах.
- Если все остальные документы закрыты и вы создаете новый документ, новый документ использует тот же режим вычисления, что и ранее закрытые документы. Однако при использовании шаблона режим вычисления — это режим, указанный в шаблоне.
- Если режим вычисления в книге изменился и файл сохранен, сохраняется текущий режим вычисления. Например, если Auto1.xlsx открыт, Manual1.xlsx открывается и Manual1.xlsx немедленно сохраняется, режим вычисления сохраняется автоматически.
Управление режимом вычисления
Все открытые документы используют один и тот же режим вычисления. Для работы с документами, использующими различные режимы вычисления, необходимо выполнить специальные процедуры. Например, если вы работаете с Auto1.xlsx и хотите открыть Manual1.xlsx в режиме вычисления вручную, выполните одно из следующих действий:
- Задайте Auto1.xlsx режим вычисления вручную перед открытием Manual1.xlsx.
- Закройте Auto1.xlsx (и другие открытые документы) перед открытием Manual1.xlsx.
Существует четыре режима вычисления, которые можно выбрать в Excel. ��� �������� ��������� �������:
- Автоматическая
- Автоматическое, за исключением таблиц данных
- Manual
- Пересчет книги перед сохранением
Пересчет активного листа
Чтобы пересчитать только активный лист, используйте один из следующих методов:
- Нажмите клавиши SHIFT+F9.
- Щелкните "Вычислить лист" в меню "Формулы" в группе вычислений.
Пересчет всех открытых документов
Чтобы пересчитать все открытые документы, используйте один из следующих методов:
Читайте также: