4. Функции и вычисления

(прогнозирование, финансовые функции, "Подбор параметра" и "Поиск решения" )

Функции прогнозирования. Одной из наиболее часто используемых возможностей Excel является экстраполяция ряда имеющихся данных с целью оценки характера их изменения и получения прогноза на будущее. Изве­стны различные способы решения этой задачи - простые (на основе арифметической и геометрической прогрессии) и специально разработанные методы. Среди статистических функций Excel есть две функции - Тенден­ция и Рост, вычисляющие соот­ветст­венно линейную и экспоненциальную экстраполяцию. Эти функции имеют по три обязатель­ных аргумента (известные значения y; известные значения х и новые значения х). По первым двум наборам данных Excel строит зависимость у от х; после обработки этих значений формиру­ет­ся новая линия - тренд (прямая для Тенден­ции и кривая для Роста), которая затем продолжает­ся до заданного нового значения х. Можно проиллюстрировать решение такой задачи на следую­щем примере. Этот пример достаточно подробно описан и может быть выполнен самостоятельно.

Финансовые функции. Более 50 различных функций предоставляет Excel-2000 для обработ­ки бухгалтерских ведомостей и банковских капиталов, для вычисления процентов по вкладам и займам, для работы с ценными бумагами. Рассмотрим некоторые часто используемые функции.

БЗ  будущее значение вклада с периодическими постоянными платежами и постоянной процентной ставкой. Синтаксис функции Б3: Б3 (ставка; кпер; плата; нз; тип), где 3 обязательных аргумента: - процентная ставка за период, - количество периодов выплат, - размер одной выплаты;

и 2 необязательных: НЗ  текущая (начальная) стоимость или общая сумма всех будущих платежей с настоящего момента (если аргумент опущен, то он равен 0); Тип  0 или 1 (0  выплата в конце периода, 1  в начале); если аргумент опущен, то Тип = 0.

Функции амортизации позволяют распределить стоимость имущества на весь период его полезного существования (жизненного цикла), по истечении которого имущество приходит в негодность. Амортизация  это условная учётная величина, которая отражает истинную стоимость имущества в конкретный момент времени. При вычислении амортизации применяются различные методы. Метод равномерного начисления износа использует разницу между начальной и ликвидной стоимостью имущества, поделённую на количество лет его жизни (функция АМР). Метод ускоренного начисления (функция АМГД) даёт неравномерный износ  боль­ший в начале эксплуатации и меньший в конце. Рассмотрим эти функции.

AMP  функция, определяющая амортизацию имущества за один период при равномерном распределении износа. Её синтаксис:

АМР (стоимость; остаток; период), где стоимость  начальная стоимость имущества, остаток  ликвидная стои­мость в конце периода аморти­зации, период - количество периодов эксплуатации.

АМГД  более полезный способ учёта износа, так как вычисляется амортизация для каждого периода отдельно. Возвращает амортиза­цию имущества для указанного периода.

Синтаксис: АМГД (стоимость; остаточная_стоимость; время_эксплуатации; период), где

Стоимость  начальная стоимость имущества, остаточная_стоимость  остаточная стоимость в конце периода амортизации, время_эксплуатации  количество периодов, за которые собственность аморти­зи­руется (иногда называется периодом амортизации), период  заданный период (должен быть измерен в тех же единицах, что и время полной амортизации).

Метод подбора параметра. Реализуется командой Сервис-Подбор параметра, которая устанавливает режим итерационных вычислений для отыскания нужного решения при одном изменяющемся параметре в заданной формуле. Причём, начальное ("грубое") значение параметра должно быть известно. Метод итераций состоит в следующем: проверяется начальное значение функции, содержащей параметр, в "целевой" ячейке. Если это значение не дает нужную величину фун­­кции, то значение параметра изменяется и  следующий цикл. Для завершения вычислений задается точность и/или количество циклов-итераций (по умолчанию 100). Если точность за 100 ци­клов не достигнута, вычисления прекращаются, выдается результат и соответствующее сообщение.

Поиск решения. Более сложные задачи, в которых значение целевой функции зависит от нескольких параметров, а допустимые значения подчиняются некоторым ограничениям, требуют более сложных средств и методики обработки данных. Метод "Поиск решения" (ПР) позволяет решать такие задачи. Он также основан на итерационных методах, но имеет ряд отличий по сравнению с подбором параметра:

• поиск решений может одновременно использовать большое количество изменяемых данных;

• позволяет задавать для них ограничения; например, при поиске решения, обеспечивающего максимальную прибыль, можно потребовать, чтобы расходы не превысили N-руб.;

• поиск решения отыскивает оптимальное решение  наилучшее из возможных с учётом всех ограничений.

Задачи, решаемые этим методом, имеют ряд общих свойств:

- имеется единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным, равным заданному числу (чистая прибыль, транспортные расходы…);

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

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