Интерактивные вычисления в excel что это
Microsoft Excel
трюки • приёмы • решения
Как в Excel с помощью итерационных вычислений посчитать чистую прибыль от вклада
Одним из способов решения данной проблемы является возможность предположить правильный ответ и затем посмотреть, насколько вы близко находитесь от реального результата. Например, в данном случае можно предположить, что, если вклад составляет 10% от чистой прибыли, мы можем взять эти 10% от валовой прибыли в 100 000. При этом сумма вклада получается равной 10 000. Если мы используем это число в формуле, то получим, что чистая прибыль равна 90 000, а 10% от нее равны 9 000. Таким образом, мы ошиблись на 1 000. Можно попробовать ещё раз. Возьмем вклад, равный 9 000. При этом чистая прибыль будет равна 91 000, а сумма вклада в 10% от этой суммы будет равна 9 100. Мы ошиблись уже всего на 100 рублей.
Если мы будем продолжать данный процесс, сумма вклада все ближе и ближе будет приближаться к истинному значению. Когда мы приблизимся достаточно близко (например, с точностью до рубля), мы можем остановиться и принять ответ за верное решение. Этот процесс называется итерациями. Конечно, мы не собираемся тратить свое драгоценное время или время компании на такие расчеты. Excel делает такие сложные вычисления очень простыми, если вы проделаете следующие шаги.
Рис. 3.5. Циклические ссылки
Рис. 3.6. Ответ после итерационных вычислений
Если вы хотите посмотреть, как именно происходит вычисление итераций в вашем случае, установите в параметрах Excel вычисления на листе вручную и предельное число итераций, равное 1. Нажимая на кнопку F9, вы сможете видеть результат вычисления очередной итерации.
Финансы в Excel
Перекрестный расчет себестоимости
Пример задачи
На крупном производственном предприятии имеется 2 цеха вспомогательного производства:
Первый производит электроэнергию для всего предприятия, второй вырабатывает тепло для обогрева всех производственных и административных зданий. Рассматривая каждый цех как центр затрат, можно выделить отдельный бюджет собственных расходов: зарплата персонала, содержание, ремонт, хозяйственные расходы и т.п. Особенностями этих цехов является то, что они формируют составляющие себестоимости продукции, а именно, потребленное электричество и тепло. В нашем случае себестоимость единицы электроэнергии будет равна сумме затрат энергоцеха в рублях, деленной на количество выработанного электричества в натуральном измерении – кВт/ч; аналогично, себестоимость единицы тепла равна сумме затрат теплоцеха в рублях, деленной на общую выработку тепла в натуральном измерении – мДж.
Сложность заключается в том, что сами цеха потребляют энергию друг друга – энергоцех отапливается, а теплоцех потребляет электроэнергию (например, на освещение). В реальной ситуации надо еще учитывать, что какая-то часть собственной энергии каждого цеха идет на внутреннее потребление. Этот момент в задаче не учитывается, так как он принципиально не влияет на алгоритм решения.
Итак, получается, что энергоцех для расчета себестоимости кВт/ч должен учитывать цену потребленного тепла, а теплоцех, в свою очередь, для расчета стоимости мДж должен знать цену кВт/ч. Типичный случай «перекрестной ссылки». Задача, как ни странно, часто встречается на практике именно в такой формулировке. Бывает еще более сложная ситуация, когда вспомогательных цехов с перекрестными затратами несколько: холодильный цех, газо- и водоснабжение, и т.п. Иногда взаимно потребляются полуфабрикаты, произведенные в другом цехе. На большинстве крупных производственных предприятий, особенно с корнями из советского прошлого, до сих пор считают цеховую себестоимость, и приходят в какой-то момент в описанный алгоритмический тупик.
Упрощенная форма задачи представлена в файле примера к статье:
Формула в ячейке В9 (=B8+B7*E10) определяет, что итоговые затраты энергоцеха складываются из бюджета внутренних расходов (в примере представлен одним числом) и стоимостью потребления тепла (объем, умноженный на цену). Ячейка B10 вычисляет себестоимость кВт/ч электроэнергии (=B9/B6). Если попытаться скопировать формулу расчета себестоимости единицы электроэнергии в формулу расчета единицы теплоэнергии (B10 в E10), то логично получим сообщение о циклической ссылке:
Какие же варианты решения имеются и используются на практике?
Преобразование математической модели
Кардинальный вариант – это пересмотр алгоритмов вычислений таким образом, чтобы исключить перекрестные ссылки между формулами.
В нашей задаче таким решением является отказ от цеховой (передельной) себестоимости и применение директ-костинга, то есть расчета себестоимости на уровне только прямых затрат. Этот метод решения, пожалуй, наиболее правильный и подходящий большинству предприятий. Но здесь останавливаться на нем не будем, так как цель статьи все-таки показать возможности Excel, а не устроить теоретическую дискуссию.
Назначенная себестоимость
Для решения задачи можно директивно установить (назначить) величину себестоимости для одного или обоих типов затрат. Например, стоимость электроэнергия устанавливается равной 2 руб за кВт/ч. Этого, в принципе, достаточно для решения нашей проблемы – теплоэнергию уже можно рассчитать по формуле без перекрестных ссылок. Но очевидно возникает другая проблема. Почему назначенная себестоимость равна двум, а не трем рублям? Дополнительно в итоговый расчет себестоимости надо вводить корректирующую сумму, которую непонятно к какому центру затрат отнести. Есть большой риск, что просто будет неверно посчитана полная себестоимость. То есть, себестоимость выработанной продукции не совпадет с затратами всего предприятия.
Как вариант предыдущего метода на практике часто используется в качестве назначенной фактическая себестоимость прошедшего периода (месяца). Этот вариант более устойчив к большим отклонениям в итоговых результатах, но проблему полностью не решает. Скорее это попытка минимизировать риск ошибки, причем в данном варианте обычно опускается расчет корректировок итоговых показателей полной себестоимости.
Подбор значения
Лучше попытаться подобрать величину себестоимости с максимальной степенью точности. То есть так, чтобы полная себестоимость произведенной продукции совпала с суммарными затратами завода. Этот вариант тоже можно считать методом назначенной стоимости, но здесь исключается ошибка итоговых расчетов. Очевидно, что перебирать значения вручную достаточно сложно. На практике можно воспользоваться интерфейсным средством Excel «Подбор параметра«:
Решение задачи найдено. Но обратите внимание, что «Подбор параметра» ссылался на контрольную ячейку математической модели, перебирая числа, до тех пор, пока там не оказалось нулевое значение. На практике у вас может не быть подобной контрольной точки, так как ее вычисление связано с множеством внешних данных. Еще один недостаток данного метода – его можно использовать для обработки только одной ячейки (подбора одного числа). Но забывать об этом варианте тоже не стоит – в некоторых случаях он даже удобнее рассматриваемого ниже варианта итерационных расчетов.
Итерационный расчет
Excel позволяет решать задачи с перекрестными ссылками в формулах при помощи итерационных вычислений. При этом необходимым условием является сходимость (приближение к результату) взаимозависимых формул после каждого шага повторения вычислений.
Для включения итерационного типа вычислений включите одноименный параметр Excel. После этого можно копировать формулу из ячейки B10 в E10. В результате получим верный результат и отсутствие сообщения о циклической ссылке:
Параметры итерационных расчетов (Предельное число итераций и Относительная погрешность) можно изменить, если необходима большая точность вычислений.
Заключение
Итерационные вычисления – это мощный встроенный механизм Excel для решения специфических задач с перекрестными взаимосвязями в формулах. Но использовать этот метод следует с большой осторожностью и только, если Вы действительно уверены в верно составленном алгоритме решения задачи. В большинстве случаев сообщение Excel о циклической ссылке указывает на некорректно выстроенную математическую модель, а не на необходимость применения итерационного расчета.
Для того чтобы быть уверенным в правильности итерационных расчетов, рекомендуется создавать специальные контрольные точки (служебные ячейки с формулами). В примере задачи в качестве такого контроля используется сравнение суммы затрат по центрам ответственности и итоговой себестоимости потребления ресурсов.
Также следует помнить, что итерационный расчет существенно замедляет общую скорость вычислений Excel. Рекомендуется после получения результата просто вставить значения вместо формулы в одну или в несколько ячеек с перекрестной ссылкой, сохранив где-нибудь копию формулы для повтора вычислений с измененными исходными данными.
Как управлять режимами автоматических и многопоточных вычислений в Excel 2013
По умолчанию Excel автоматически пересчитывает все формулы при открытии рабочего листа или при изменении значений, формул или имен в зависимых формулах. Процесс пересчета может длиться от нескольких секунд до нескольких минут в зависимости от количества и сложности формул в рабочей книге.
Пока формулы пересчитываются, указатель мыши меняется на песочные часы, и внести какие-либо изменения на рабочий лист невозможно. Для экономии времени можно временно отключить режим автоматических вычислений в Excel до завершения внесения данных. Сделать это можно без особого труда, и мы покажем Вам как.
Если Вы не хотите отключать режим автоматических вычислений, и Ваш компьютер имеет несколько процессоров, тогда можете применить режим многопоточных вычислений. Он поможет немного ускорить процесс пересчета в зависимости от количества процессоров компьютера. О том, как включить режим многопоточных вычислений в Excel, мы расскажем дальше.
Параметры вычислений
Следующий список поясняет опции, которые доступны в разделе Calculation options (Параметры вычислений):
Вы также можете переключаться между тремя основными режимами вычислений, используя команду Calculation Options (Параметры вычислений) в разделе Calculation (Вычисление) на вкладке Formulas (Формулы). Однако, если необходимо настроить параметры вычислений, все же придется обратиться к вкладке Formulas (Формулы) диалогового окна Excel Options (Параметры Excel).
Многопоточные вычисления в Excel
Excel 2013 располагает режимом многопоточных вычислений, который позволяет сокращать время пересчета формул. Если Вы предпочитаете не отключать автоматическое вычисление, попробуйте использовать указанную опцию (при наличии у компьютера нескольких процессоров), чтобы сократить время пересчета.
Если на компьютере уже запущены другие программы, Вы можете ограничить число используемых процессоров для пересчета формул электронной таблицы Excel.
Испытайте разные настройки режимов автоматических и многопоточных вычислений, чтобы определить, какие из них лучше подходят для Вашего компьютера.
Изменение пересчета, итерации или точности формулы в Excel
Для эффективного использования формул необходимо ознакомиться с тремя ключевыми понятиями.
Вычисление — это процесс расчета по формулам и последующего отображения значений результатов в ячейках, содержащих формулы. Во избежание ненужных вычислений, которые забрать время и замедлить работу компьютера, Microsoft Office Excel автоматически пересчитывает формулы только при изменении влияющих на формулу ячеек. Это является стандартной процедурой при первом открытии книги и ее редактировании. Однако тем, как и когда Excel будет пересчитывать формулы, можно управлять.
Итерация — это повторный пересчет на отдельном числовом условии. Excel не может автоматически вычислить формулу, которая ссылается (прямо или косвенно) на ячейку, содержаную эту формулу. Это называется циклской ссылкой. Если формула ссылается на одну из собственных ячеек, необходимо определить, сколько раз она должна пересчитываться. Цикловые ссылки могут итерироваться бесконечно. Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
Точность — это показатель степени сходимости вычислений. Excel хранит и выполняет вычисления с точностью 15 значащих цифр. Однако существует возможность изменить точность вычислений, так что Excel при пересчете формул будет использовать для вычислений не хранимое, а отображаемое значение.
Во время вычислений можно выбирать команды и выполнять ввод чисел или формул. Для выполнения команд или других действий вычисления прерываются, а затем возобновляются снова. Если книга содержит большое число формул либо листы содержат таблицы данных или функции, автоматически пересчитываемые при каждом пересчете книги, процесс вычислений может занять значительное время. Он также может быть длительным, если листы содержат связи с другими листами или книгами. Можно изменить способ выполнения вычислений, установив параметр пересчета вручную.
Важно: Изменение любого из этих параметров влияет на все открытые книги.
На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы.
В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Формулы».
Выполните одно из указанных ниже действий.
Для пересчета всех зависимых формул при каждом изменении значения, формулы или имени в разделе Параметры вычислений в группе Вычисления в книге выберите пункт Автоматически. Это — способ вычислений по умолчанию.
Чтобы пересчитать все зависимые формулы, кроме таблиц данных, при каждом изменении значения, формулы или имени в разделе «Параметры вычислений» в разделе «Вычисления книги» выберите значение «Автоматически» за исключением таблиц данных.
Для отключения автоматического пересчета и выполнения пересчета открытых книг только при явном требовании (с помощью клавиши F9) в разделе Параметры вычислений в группе Вычисления в книге выберите параметр Вручную.
Примечание: При выборе параметра Вручную Excel автоматически устанавливает флажок Пересчитывать книгу перед сохранением. Если сохранение книги занимает много времени, для его экономии снимите флажок Пересчитывать книгу перед сохранением.
Для ручного пересчета всех открытых книг, включая таблицы данных, и обновления всех открытых листов диаграмм на вкладке Формулы в группе Вычисление нажмите кнопку Пересчет.
Для ручного пересчета активного листа, всех диаграмм и листов диаграмм, связанных с этим листом, на вкладке Формулы в группе Вычисление нажмите кнопку Произвести вычисления.
Совет: Многие из этих параметров можно изменять и вне диалогового окна Параметры Excel. Откройте вкладку Формулы и в группе Вычисления щелкните элемент Параметры вычислений, а затем — Выполнять автоматически.
Примечание: Если лист содержит формулу, связанную с непересчитанным листом, и эта связь обновляется, появится сообщение о том, что исходный лист полностью не пересчитан. Для обновления связи с текущим значением, записанном в исходном листе, даже если оно неверно, нажмите кнопку ОК. Для прекращения обновления связи и использования предыдущего значения, полученного из исходного листа, нажмите кнопку Отмена.
Пересчет измененных после последнего вычисления формул и формул, зависящих от них, во всех открытых книгах. Если задан автоматический пересчет книги, нажимать клавишу F9 для пересчета не требуется.
Пересчет измененных после последнего вычисления формул и формул, зависящих от них, в активном листе.
Пересчет всех формул во всех открытых книгах независимо от того, были ли они изменены после прошлого пересчета.
Проверка зависимых формул, а затем пересчет всех формул во всех открытых книгах независимо от того, были ли они изменены после прошлого пересчета.
На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы.
В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Формулы».
В разделе Параметры вычислений установите флажок Включить итеративные вычисления.
Чтобы установить максимальное число пересчетов, введите в поле Предельное число итераций число итераций. Чем больше число итераций, тем больше времени потребуется для пересчета листа.
Для установки максимальной разности между результатами пересчетов введите ее в поле Относительная погрешность. Чем меньше это число, тем точнее результат, и тем больше времени требуется на пересчет листа.
Примечание: «Поиск решения» и «Поиск цели» — это часть набора команд, иногда называемых инструментами анализ «что если» средств. Обе команды используют итерацию контролируемым способом для получения нужных результатов. С помощью функции «Поиск решения» можно найти оптимальное значение для определенной ячейки, настроив значения в нескольких ячейках или применив определенные ограничения к одному или нескольким значениям в вычислениях. Если вы знаете нужный результат одной формулы, но не входные значения, необходимые для его определения, можно использовать средству поиска целью.
Изменяя точность вычислений в книге, необходимо учитывать важные факторы, указанные ниже.
По умолчанию в вычислениях используются хранимые, а не отображаемые значения
Отображаемые и печатаемые значения зависят от выбора формата и отображения хранимых значений. Например, ячейка, в которой отображается дата «22.06.2008», также содержит порядковый номер, который является хранимым значением даты в этой ячейке. Можно изменить отображение даты на другой формат (например, «22-июнь-2008»), но хранимое в ячейке значение не изменится.
С осторожностью изменяйте точность вычислений
При пересчете формулы Excel обычно использует значения, хранящиеся в ячейках, на которые она ссылается. Например, если в двух ячейках содержится значение 10,005, а формат ячеек предполагает отображение значений в виде денежных единиц, в каждой из ячеек будет отображаться значение 10,01 ₽. При сложении этих значений результат будет равен 20,01 ₽, поскольку Excel складывает хранимые значения 10,005 и 10,005, а не отображаемые значения.
При изменении точности вычислений в книге путем использования отображаемых (форматированных) значений Excel безвозвратно меняет точность хранимых в ячейках значений от полной (15 цифр) до любого другого числа десятичных знаков отображаемого формата. После такого изменения возврат к исходной точности невозможен.
На вкладке Файл нажмите кнопку Параметры и выберите категорию Дополнительно.
В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Дополнительные параметры».
В разделе При пересчете этой книги выберите нужную книгу и установите флажок Задать указанную точность.
Хотя Excel ограничивает точность до 15 десятичных разрядов, это не означает, что максимальное число, которое можно хранить в Excel, ограничено 15 разрядами. Для положительных чисел предельное значение равно 9,99999999999999E+307, а для отрицательных значений — –9.99999999999999E+307. Эти числа приблизительно равны 1 или –1 с 308 нулями.
Точность в Excel означает, что любое число с разрядностью более 15 хранится и отображается только с точностью в 15 десятичных разрядов. Десятичная запятая может стоять перед любым из этих разрядов. Все разряды справа от 15-го обнуляются. Например, у числа 1234567,890123456 16 разрядов (7 до запятой и 9 после). В Excel это число хранится и отображается как 1234567,89012345 (в строке формулы и ячейке). Если установить для ячейки формат, отображающий все разряды (вместо экспоненциального формата, такого как 1,23457E+06), вы увидите, что число показывается как 1234567,890123450. 6 в конце (16-й разряд) отбрасывается и заменяется на 0. Последним является 15-й разряд, а все последующие равны нулю.
Компьютер может содержать более одного процессора (несколько физических процессоров) или поддерживать многопоточность (несколько логических процессоров). На таких компьютерах можно уменьшить или контролировать время, требуемое для пересчета книг, содержащих множество формул, путем определения числа процессоров, используемых для пересчета. Во многих случаях пересчет может выполняться частями одновременно. Распределение нагрузки между несколькими процессорами может уменьшить общее время пересчета.
На вкладке Файл нажмите кнопку Параметры и выберите категорию Дополнительно.
В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Дополнительные параметры».
Для включения или отключения возможности использования для вычислений нескольких процессоров в разделе Формулы установите или снимите флажок Включить многопоточные вычисления.
Заметка Этот флажок по умолчанию установлен, и для вычислений используются все процессоры. Число процессоров на компьютере определяется автоматически и отображается рядом с параметром использовать все процессоры данного компьютера.
Дополнительно при выборе параметра Включить многопоточные вычисления можно задавать число используемых процессоров компьютера. Например, можно ограничить число процессоров, используемых для пересчета, если нужно выделить процессорное время другим выполняемым на компьютере программам.
Чтобы управлять числом процессоров, в области «Число потоковвычислений» выберите «Вручную». Введите число процессоров (максимальное число — 1024).
Чтобы гарантировать правильность пересчета старых книг, приложение Excel при первом открытии книги, сохраненной в более старой версии Excel, и книги, созданной в текущей версии, ведет себя по-разному.
При открытии книги, созданной в текущей версии, в Excel пересчитываются только формулы, зависящие от измененных ячеек.
При открытии книги, созданной в более ранней версии Excel, пересчитываются все формулы в книге. Это гарантирует полную оптимизацию книги для использования в текущей версии Excel. Исключением является ситуация, когда книга находится в другом режиме вычислений, например ручном.
Поскольку полный пересчет требует больше времени, чем частичный, открытие книги, которая не была сохранена в формате текущей версии Microsoft Excel, может потребовать больше времени. После сохранения книги в формате текущей версии Microsoft Excel книга будет открываться быстрее.
В Excel в Интернете результаты формулы автоматически пересчитываются при изменении данных в ячейках, которые используются в этой формуле. Этот автоматический пересчет можно отключить и вычислить результаты формулы вручную. Вот как это сделать:
Примечание: Изменение параметра вычисления влияет только на текущую книгу, а не на другие открытые книги в браузере.
В Excel в Интернете щелкните вкладку «Формулы».
Рядом с параметрами вычисленийвыберите один из следующих параметров в этом меню:
Чтобы пересчитать все зависимые формулы при каждом изменении значения, формулы или имени, выберите значение «Автоматически». Этот параметр установлен по умолчанию.
Чтобы пересчитать все зависимые формулы, кроме таблиц данных, при каждом изменении значения, формулы или имени нажимайте кнопку «Автоматически, кроме таблиц данных».
Чтобы отключить автоматический пересчет и пересчет открытых книг только явным образом, нажмите кнопку «Вручную».
Чтобы пересчитать книгу вручную (включая таблицы данных), нажмите кнопку «Вычислить книгу».
Примечание: В Excel в Интернете нельзя изменить количество пересчетов формулы до тех пор, пока не будет выполнены определенное числовый условие, а также изменить точность вычислений с помощью отображаемого значения, а не хранимого значения при пересчете формул. Однако это можно сделать в приложении Excel для настольных систем. Чтобы задать параметры вычислений и изменить пересчет, итерацию или точность формулы, откройте книгу с помощью кнопки «Открыть в Excel».
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.