В чем суть автоматического перерасчета в ms excel
По умолчанию 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 Excel несколькими способами, например:
- путем ввода новых данных (если Excel находится в режиме автоматического пересчета, описанном далее в этой статье);
- явным указанием Excel пересчитать всю книгу или ее часть;
- путем удаления или вставки строки или столбца;
- путем сохранения книги при заданном параметре Пересчет перед сохранением;
- путем выполнения некоторых действий автофильтра;
- двойным щелчком по разделителю строк или столбцов (в режиме автоматического вычисления);
- путем добавления, редактирования или удаления заданного имени;
- путем переименования листа;
- путем изменения позиции листа относительно других листов;
- путем скрытия или отображения строк (не столбцов).
В этой статье не делается различий между непосредственным нажатием клавиши или кнопки мыши пользователем и выполнением этих задач командой или макросом. Пользователь запускает команду или делает что-либо, чтобы она запустилась, поэтому это также считается действием пользователя. Таким образом, слово "пользователь" также означает "пользователь либо команда или процесс, запущенные пользователем".
Зависимость, "грязные" ячейки и пересчитанные ячейки
Вычисление листов в Excel можно рассматривать как процесс из трех этапов:
- Создание дерева зависимостей
- Создание цепочки вычислений
- Пересчет ячеек
Дерево зависимостей сообщает Excel, какие ячейки зависят от других ячеек или, аналогично, какие ячейки являются прецедентами для других. Из этого дерева Excel составляет цепочку вычислений. В ней перечисляются все ячейки, которые содержат формулы, в том порядке, в котором их необходимо вычислять. Во время пересчета Excel изменяет эту цепочку, если обнаруживается формула, которая зависит от еще не вычисленной ячейки. В этом случае вычисляемая ячейка и зависящие от нее ячейки перемещаются вниз по цепочке. По этой причине время вычисления в первых нескольких циклах часто сокращается на листах, которые были только что открыты.
При структурном изменении книги, например при вводе новой формулы, Excel заново создает дерево зависимостей и цепочку вычислений. При вводе новых данных или новых формул Excel помечает все ячейки, которые зависят от новых данных, как требующие пересчета. Помеченные такие образом ячейки называются "грязными". Все прямые и косвенные зависимые ячейки помечаются как "грязные", поэтому если ячейка B1 зависит от ячейки A1, а ячейка C1 — от B1, то при изменении ячейки A1 ячейки B1 и C1 помечаются как "грязные".
Отметив ячейки как "грязные" при следующем пересчете, Excel повторно оценивает содержимое каждой "грязной" ячейки в порядке, определяемом цепочкой вычислений. В приведенном выше примере это означает, что сначала оценивается ячейка B1, а затем — C1. Пересчет происходит сразу после того, как Excel закончит отмечать ячейки как "грязные", если выбран автоматический режим пересчета. В противном случае это происходит позже.
Начиная с Microsoft Excel 2002, объект Range в Microsoft Visual Basic для приложений (VBA) поддерживает метод Range.Dirty, который отмечает ячейки как требующие подсчета. Когда он используется совместно с методом Range.Calculate (см. следующий раздел), он включает принудительный пересчет ячеек в заданном диапазоне. Это удобно при выполнении ограниченного вычисления в макросе, где установлен ручной режим подсчета (для избежания избытка вычисляемых ячеек, не относящихся к функции макроса). Методы подсчета диапазонов недоступны через API C.
В Excel 2002 и более ранних версиях Excel составлял цепочку вычислений для каждого листа в каждой открытой книге. Это несколько усложняло обработку ссылок между листами и требовало осторожности для обеспечения эффективного пересчета. В частности, в Excel 2000 необходимо сводить к минимуму зависимости между листами и присваивать листам имена в алфавитном порядке, чтобы листы, зависящие от других листов, следовали по алфавиту за листами, от которых они зависят.
В Excel 2007 логика была улучшена для поддержки пересчета в нескольких потоках, чтобы разделы цепочки вычислений не зависели друг от друга и для них можно было проводить подсчеты одновременно. Вы можете настроить Excel для использования нескольких потоков на компьютере с одним процессором или одного потока на многопроцессорном или многоядерном компьютере.
Асинхронные пользовательские функции
Когда вычисление обнаруживает асинхронную пользовательскую функцию, оно сохраняет состояние текущей формулы, запускает пользовательскую функцию и продолжает оценивать остальные ячейки. Когда вычисление завершает оценку ячеек, Excel ждет завершения асинхронных функций, если они еще выполняются. По мере того как каждая асинхронная функция сообщает о результатах, Excel завершает формулу, а затем запускает новую передачу вычисления, чтобы пересчитать ячейки, которые используют ячейку со ссылкой на асинхронную функцию.
Переменные и постоянные функции
Excel поддерживает переменные функции, то есть функции, значения которых в разные моменты могут отличаться, даже если ни один из аргументов (если они принимаются) не изменился. Excel повторно оценивает ячейки, которые содержат переменные функции, вместе со всеми зависимыми функциями при каждом пересчете. По этой причине чрезмерное использование переменных функций может замедлить пересчет. Используйте их экономно.
Переменными являются следующие функции Excel:
Интерфейсы API VBA и C поддерживают способы сообщить Excel, что пользовательскую функцию следует обрабатывать как переменную. В VBA пользовательская функция объявляется переменной следующим образом.
По умолчанию Excel предполагает, что пользовательские функции VBA не являются переменными. Excel узнает, что пользовательская функция является переменной, только при ее первом вызове. Переменную пользовательскую функцию можно сделать постоянной, как в следующем примере.
С помощью API C можно зарегистрировать функцию XLL как переменную до ее первого вызова. Он также позволяет включать и отключать переменное состояние функции листа.
По умолчанию Excel обрабатывает пользовательские функции XLL, которые принимают диапазоны в качестве аргументов и объявлены как эквиваленты листа макросов (изменчивые). Вы можете отключить это состояние по умолчанию с помощью функции xlfVolatile при первом вызове пользовательской функции.
Режимы вычисления, команды, выборочный пересчет и таблицы данных
В Excel есть три режима вычисления:
- Automatic
- Автоматический, кроме таблиц
- Manual
В автоматическом режиме вычисления пересчет происходит только после каждого ввода данных и после определенных событий, таких как примеры в предыдущем разделе. В очень больших книгах пересчет может занимать так много времени, что пользователям необходимо ограничивать эти условия, чтобы пересчет происходил только при необходимости. Для этого Excel поддерживает ручной режим. Пользователь может выбрать режим в системе меню Excel или программным способом с помощью API VBA, COM или C.
Таблицы данных — это специальные структуры на листе. Сначала пользователь настраивает вычисление результата на листе. Это зависит от одного или двух изменяемых наборов данных, введенных с клавиатуры, и других параметров. Затем пользователь может создать таблицу результатов для значений одного или обоих вводов с клавиатуры. Таблица создается с помощью мастера таблиц данных. После настройки таблицы Excel по одному отправляет наборы введенных данных в вычисление и копирует полученное значение в таблицу. Так как можно использовать один или два набора введенных данных, таблицы данных могут быть одномерными или двумерными.
Пересчет таблиц данных обрабатывается немного по-другому:
- Пересчет обрабатывается асинхронно в отличие от обычного пересчета книг, поэтому пересчет больших таблиц может занимать больше времени, чем пересчет остальных элементов книги.
- Циклические ссылки допускаются. Если вычисление, используемое для получения результата, зависит от одного или нескольких значений из таблицы данных, то Excel не возвращает ошибку циклической зависимости.
- Таблицы данных не используют многопоточные вычисления.
Учитывая, что Excel по-другому обрабатывает пересчет таблиц данных, а вычисление больших таблиц, зависящих от сложных или длинных вычислений, может занимать много времени, Excel позволяет отключить автоматическое вычисление таблиц данных. Для этого выберите режим вычисления "Автоматический, кроме таблиц". В этом режиме пользователь может пересчитывать данные, нажав клавишу F9 или выполнив эквивалентную программную операцию.
Excel предоставляет методы, с помощью которых можно изменять режим пересчета и управлять им. Эти методы улучшались от версии к версии, чтобы обеспечить возможность более точного управления. Возможности API C в этом отношении отражают возможности, доступные в Excel версии 5, поэтому не предоставляют такого управления, как при использовании VBA в более поздних версиях.
Эти методы чаще всего используются, когда Excel находится в ручном режиме вычисления, и позволяют выборочно вычислять книги, листы и диапазоны, полностью пересчитывать все открытые книги и даже полностью перестраивать дерево зависимостей и цепочку вычислений.
Вычисление диапазонов
VBA: Range.Calculate (представлен в Excel 2000, изменен в Excel 2007) и Range.CalculateRowMajorOrder (представлен в Excel 2007)
API C: не поддерживается
Ручной режим
Пересчитывает только ячейки в заданном диапазоне независимо от того, "грязные" ли они. Поведение метода Range.Calculate изменилось в Excel 2007. Но предыдущее поведение по-прежнему поддерживается методом Range.CalculateRowMajorOrder.
Режим "Автоматически" или "Автоматически, кроме таблиц"
Пересчитывает книгу, но не выполняет принудительный пересчет диапазона или каких-либо ячеек в нем.
Активное вычисление листов
VBA: ActiveSheet.Calculate
API C: xlcCalculateDocument
Все режимы
Пересчитывает ячейки, отмеченные для вычисления, только на активном листе.
Вычисление указанных листов
VBA: Worksheets( reference ).Calculate
API C: не поддерживается
Пересчитывает "грязные" ячейки и их зависимости только на указанном листе. Ссылка — это имя листа как строка или номер индекса в соответствующей книге.
Excel 2000 и более поздних версий предоставляет свойство листа Boolean (EnableCalculation). Если задать для него значение True вместо False, все ячейки на указанном листе будут помечены как "грязные". В автоматических режимах это вызывает пересчет всей книги.
В ручном режиме следующий код вызывает пересчет только активного листа.
Повторное создание и принудительный пересчет дерева книги
Клавиши: CTRL+ALT+SHIFT+F9 (появились в Excel 2002)
VBA: Workbooks( reference ).ForceFullCalculation (представлен в Excel 2007)
API C: не поддерживается
Все режимы
Указывает Excel заново создать дерево зависимостей и цепочку вычислений для определенной книги и вызывает пересчет всех ячеек, содержащих формулы.
Все открытые книги
VBA: Application.Calculate
API C: xlcCalculateNow
Все режимы
Пересчитывает все ячейки, которые Excel отметил как "грязные", то есть зависящие от переменных или измененных данных, и ячейки, программно отмеченные как "грязные". Если выбран режим вычисления "Автоматический, кроме таблиц", этот метод вычисляет таблицы, которые требуют обновления, а также все переменные функции и их зависимости.
Повторное создание и принудительное вычисление дерева всех открытых книг
VBA: Application.CalculateFull
API C: не поддерживается
Все режимы
Пересчитывает все ячейки во всех открытых книгах. Если выбран режим вычисления "Автоматический, кроме таблиц", выполняется принудительный пересчет таблиц.
Многопотоковый пересчет листов впервые появился в 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 на сервере. Пользователи могут получить доступ к электронным таблицам и работать с ними, используя стандартные средства браузера.
Excel умолчанию пересчитывает все формулы во всех листах всех открытых книг после каждого введения данных. Если лист содержит сотни или тысячи формул автоматический пересчет начинает заметно замедлять процесс работы с программой. Рассмотрим, как можно настроить Excel для повышения его производительности и беспрепятственной работы.
Автоматический и ручной пересчет
Для книги, которая содержит сотни сложных формул можно настроить пересчет по востребованию пользователя. Для этого:
- Введите формулу на чистый лист (чтобы можно было проверить как работает данный пример).
- Выберите инструмент: «Формулы»-«Параметры вычислений»-«Вручную».
- Убедитесь, что теперь после ввода данных в ячейку (например, число 7 вместо 1 в ячейке A2 как на рисунке), формула не пересчитывает результат автоматически. Пока пользователь не нажмет клавишу F9 (или SFIFT+F9).
Внимание! Быстрая клавиша F9 – выполняет пересчет во всех формулах книги на всех листах. А Комбинация горячих клавиш SHIFT+F9 – выполняет пересчет только на текущем листе.
Если на листе не содержится множество формул, пересчет которых может тормозить Excel, то нет смысла использовать выше описанный пример. Но на будущее все же стоит знать о такой возможности. Ведь со временем придется сталкиваться со сложными таблицами с множеством формул. Кроме того данная функция может быть включена случайно и нужно знать где ее выключить для стандартного режима работы.
Как отображать формулу ячейке Excel
В ячейках Excel мы видим только результат вычислений. Сами же формулы можно увидеть в строке формул (по отдельности). Но иногда нам нужно одновременно просмотреть все функции в ячейках (например, чтобы их сравнить и т.п.).
Чтобы наглядно отобразить пример данного урока нам потребуется лист содержащий формулы:
Изменим настройки Excel так, чтобы ячейки отображали формулы, а не результат их вычисления.
Чтобы добиться данного результата, нужно выбрать инструмент: «Формулы»-«Показать» (в разделе «Зависимости формул»). Чтобы выйти из этого режима нужно выбрать данный инструмент повторно.
Так же можно использовать комбинацию горячих клавиш CTRL+` (над клавишей Tab). Данная комбинация работает в режиме переключателя, то есть повторное нажатие возвращает в обычный режим отображения результатов вычисления в ячейках.
Примечание. Все выше описанные действия распространяются только на режим отображения ячеек одного листа. То есть на других листах при необходимости потребуется выполнять эти действия повторно.
Функция КПЕР в Excel предназначена для расчета количества периодов выплат погашения определенной суммы задолженности при известных значениях процентной ставки (простые проценты), суммы платежа для каждого периода (фиксированное значение), начальной суммы задолженности или общей суммы долга с учетом процентов, и возвращает соответствующее числовое значение.
Примеры как использовать функцию КПЕР в Excel
Пример 1. Вкладчик внес депозит под 16% годовых на сумму 120000 рублей с ежемесячной капитализацией вклада (простые проценты). Сколько лет потребуется для накопления 300000 рублей?
Формула для расчета:
- B3/B4 – процентная ставка за период капитализации;
- 0 – числовое значение, характеризующее ежемесячный платеж (дополнительное пополнение депозитного счета не производится);
- B2 – начальная инвестиция;
- -B5 – конечная сумма по окончанию договора.
Возвращенный функцией КПЕР результат разделен на количество периодов капитализации в году для расчета числа лет, необходимых для накопления требуемой суммы. Результат расчетов:
Вкладчик должен оставлять деньги на депозитном счете на протяжении почти 6 лет.
Расчет реальной суммы долга с процентами и переплатой в Excel
Пример 2. Клиенту банка был выдан кредит на сумму 10000 рублей под 23% годовых с ежемесячной оплатой 700 рублей. Сколько всего денег получит банк по окончанию срока кредитного договора?
Формула для расчета:
Общая сумма кредита рассчитывается как произведение фиксированной суммы ежемесячного платежа и количества периодов выплат. В данном случае количество периодов равно 16,85 (нецелое число), значит, последняя выплата должна составить меньше 700 рублей. Найдем целое число периодов:
Чтобы определить, какую часть тела кредита было погашено за 16 целых периодов выплат, воспользуемся следующей функцией:
За последний неполный период необходимо вернуть следующую часть тела кредита:
Рассчитаем оставшиеся проценты к уплате:
Так как платеж включает в себя оплату тела кредита и процентов, насчитанных за период, определим размер последнего платежа по формуле:
Общая сумма, которую получит банк, составит 11796 рублей, а размер последнего платежа – 597 рублей.
Расчет сроков погашения кредита с помощью функции КПЕР
Пример 3. Банк выдал кредит на сумму 35000 рублей под 27% годовых. Размер ежемесячного платежа составляет 1500 рублей. Через сколько месяцев клиент выплатить 50% кредита?
Исходная таблица данных:
На основании тождества аннуитетных платежей (сумма величины платежа в погашение тела кредита за все периоды, тела кредита и будущей стоимости равна нулю, то есть ОБЩДОХОД+ПС+БС=0) используем следующую формулу:
Выражение -B2*(1-50%)) характеризует будущую стоимость и было получено из уравнения:
Для выплаты 50% кредита потребуется вносить ежемесячный платеж на протяжении примерно 20 месяцев.
Особенности использования функции КПЕР в Excel
Функция КПЕР используется для решения финансовых задач совместно с функциями ПЛТ, БС, СТАВКА, ПС и имеет следующую синтаксическую запись:
=КПЕР( ставка;плт;пс ;[бс];[тип])
Описание аргументов (первые три аргумента – обязательные для заполнения):
- ставка – числовое значение, характеризующее ставку за 1 период выплат (для ссуд) или капитализации (для депозитных вкладов). Аргумент может быть указан в виде дробного числа или в качестве значения в процентном формате (например, 14,5% или 0,145 – эквивалентные варианты записи). Если в условии задачи указана годовая ставка, необходимо выполнить пересчет по формуле Rп=Rг/12, где Rп – ставка за период, Rg – годовая ставка, 12 – число месяцев в году.
- плт – числовое значение, соответствующее сумме выплаты за период, которая является фиксированной величиной (простые проценты).
- пс – числовое значение, характеризующее текущую стоимость инвестиции (например, сумма, выданная кредитной организацией в долг клиенту, или сумма средств, положенных на депозитный счет в банк).
- [бс] – числовое значение, соответствующее будущей стоимости инвестиции. Например, данный аргумент может характеризовать сумму, которую получит вкладчик по окончанию действия договора по депозитному вкладу. Если аргумент явно не указан или принимает значение 0 (нуль), функция КПЕР вернет количество периодов выплат до полного погашения задолженности. Аргумент необязателен для заполнения, по умолчанию принимается значение 0.
- [тип] – необязательный аргумент, характеризующий способ выплат (0 – выплата на конец периода, 1 – выплата на начало периода).
Читайте также: