Практические аспекты решения экономических задач в MS Excel

2.   Виды функций используемые в Excel при решение экономических задач

В Microsoft Excel имеется целый ряд встроенных математических функций, позволяющих легко и быстро выполнять различные специализированные вы­числения. Кроме того, множество математических функций включено в надстройку Пакет анализа.

Функция СУММ

Функция СУММ суммирует множество чисел. 

Эта функция имеет следующий синтаксис:

СУММ(число1;число2; …).

Аргумент числа может включать до 30 элементов, каждый из которых может быть числом, формулой, диапазоном или ссылкой на ячейку, содержащую ли возвращающую числовое значение. Функция СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения.

Поскольку СУММ является очень популярной функцией, на стандартной  панели инструментов имеется специальная кнопка для ввода этой функции. Если вы выделите ячейку и нажмете кнопку Автосумма (кнопка с символом (), Ехсеl создаст формулу =СУММ() и предложит ячейки для суммирования. Например, если  выделить ячейку   С16  в  листе,   представленном  ранее на рис. 1, и затем нажать кнопку Авто сумма, Excel предложит формулу =СУММ(С4:С15) и выведет подвижную рамку вокруг диапазона ячеек, использованного в формуле в качестве аргумента.

Если предложенный аргумент функции СУММ является правильным, нажмите клавишу Enter или еще раз кнопку Автосумма для фиксации ввода формулы и удаления подвижной рамки. Если предложенный аргумент неверен, его можно изменить, выделив, пока присутствует подвижная рамка,

правильный диапазон ячеек. Excel заменит предложенный аргумент выделенным диапазоном и перерисует подвижную рамку вокруг вашего выделения.

Функция СУММПРОИЗВ

Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений. Синтаксис выглядит следующим образом:

СУММПРОИЗВ(массив1;массив2;массив3; ...)

Массив1, массив2, массив3,...     — от 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.

Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!.

Функция СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.

Функция ПРОИЗВЕД

Функция ПРОИЗВЕД перемножает все числа, переданные как аргументы, и возвращает произведение. Например, если в ячейках A1 и A2 содержатся числа, формула =ПРОИЗВЕД(A1;A2) возвращает произведение этих чисел. Эту операцию также можно выполнить с помощью математического оператора умножения (*). Пример: =A1 * A2.

Функция ПРОИЗВЕД применяется в случаях, когда требуется перемножить много ячеек. Например, формула =ПРОИЗВЕД(A1:A3; C1:C3) эквивалентна произведению =A1 * A2 * A3 * C1 * C2 * C3.

Эта функция имеет следующий синтаксис:

ПРОИЗВЕД(число1, [число2], ...)

 Если аргумент является массивом или ссылкой, то перемножаются только числа из этого массива или ссылки. Пустые ячейки, логические значения и текст в них игнорируются.

Функция  ПРОИЗВЕД  может  иметь до  30   аргументов.   Excel  

Функции СЛЧИС и СЛУЧМЕЖДУ

Функция СЛЧИС возвращает равномерно распределенное случайное вещественное число, которое большее или равно 0 и меньше 1. Новое случайное вещественное число возвращается при каждом вычислении листа.

Синтаксис функции:

СЛЧИС()

Чтобы получить случайное вещественное число в диапазоне между a и b, можно использовать следующую формулу:

СЛЧИС()*(b-a)+a

Если требуется применить функцию СЛЧИС для генерации случайного числа, но изменение этого числа при каждом вычислении значения ячейки нежелательно, можно ввести в строке формулы =СЛЧИС(), а затем нажать клавишу F9, чтобы заменить формулу случайным числом.

Функция СЛЧИС является одной из функций Excel, которые не имеют аргументов. Как и для всех функций, у которых отсутствуют аргументы, после имени функции необходимо вводить круглые скобки.

Значение функции СЛЧИС изменяется при каждом пересчете листа. Если установлено автоматическое обновление вычислений, значение функции СЛЧИС изменяется каждый раз при вводе данных в этом листе.

Функция СЛУЧМЕЖДУ возвращает случайное целое число, находящееся в диапазоне между двумя заданными числами. При каждом вычислении листа возвращается новое случайное целое число.

Эта функция имеет следующий синтаксис:

              СЛУЧМЕЖДУ(нижн_граница;верхн_граница)

Нижн граница     — наименьшее целое число, которое возвращает функция СЛУЧМЕЖДУ.

Верхн граница     — наибольшее целое число, которое возвращает функция СЛУЧМЕЖДУ.

Функция ОКРУГЛ

Функция ОКРУГЛ округляет число до указанного количества дробных разрядов, и имеет следующий синтаксис:

ОКРУГЛ(число, число_разрядов)

Аргумент число может быть числом, ссылкой на ячейку, в которой содержится число, или формулой, возвращающей числовое значение. Аргумент количество_цифр, который может быть любым положительным или отрицательным целым числом, определяет, сколько цифр будет округляться. Задание отрицательного аргумента количество_цифр округляет до указанного количества разрядов слева от десятичной запятой, а задание аргумента количество_цифр равным 0 округляет до ближайшего целого числа. Excel округляет цифры, которые меньше 5, с недостатком (вниз), а цифры, которые больше или равны 5, с избытком (вверх). Следующая таблица содержит несколько при­меров действия функции ОКРУГЛ.

Функции ОКРУГЛВНИЗ и ОКРУГЛВВЕРХ имеют такой же синтаксис, как и функция ОКРУГЛ. Как указывают их имена, они округляют значения вниз (с недостатком) или вверх (с избытком).

Функции ЦЕЛОЕ и ОТБР

Функция ЦЕЛОЕ округляет число до ближайшего меньшего целого и имеет следующий синтаксис:

ЦЕЛОЕ(число).

Аргумент числа — это вещественное число, округляемое до ближайшего меньшего целого.

Если число отрицательное, функция ЦЕЛОЕ также округляет это число до ближайшего меньшего целого.

Функция ОТБР усекает число до целого, отбрасывая дробную часть Эта функция имеет следующий синтаксис:

ОТБР(число;число_разрядов)

Число разрядов - число, определяющее точность усечения. Значение по умолчанию — 0 (ноль).

Если второй аргумент опущен, он принимается равным нулю. Например, следующая формула возвращает значение 13: =ОТБР(13,978)

Сходство между функциями ОТБР и ЦЕЛОЕ заключается в том, что обе они возвращают целые числа. Функция ОТБР отбрасывает дробную часть числа. Функция ЦЕЛОЕ округляет число до ближайшего целого с недостатком с учетом значения дробной части. Эти функции различаются только при использовании отрицательных чисел: TRUNC(-4.3) возвращает значение -4, в то время как INT(-4.3) возвращает -5, поскольку -5 — ближайшее меньшее число.

Функция КОРЕНЬ

Функция КОРЕНЬ возвращает положительное значение  квадратного кореня и имеет следующий синтаксис:

КОРЕНЬ(число)

Аргумент число должен быть положительным числом. Например, следующая функция возвращает значение 2: =КОРЕНЬ(4)

Если аргумент «число» имеет отрицательное значение, функция КОРЕНЬ возвращает значение ошибки #ЧИСЛО!.

Так же для решения экономических задач используются финансовые функции: ПЛТ, ПС, КПЕР, АПЛ, СТАВКА, ФУО.

Функция ПЛТ

Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Эта функция имеет следующий синтаксис:

ПЛТ(ставка;кпер;пс;бс;тип)

Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.

Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».

Функция ПС

Возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат. Например, в момент займа его сумма является приведенной (нынешней) стоимостью для заимодавца.

Эта функция имеет следующий синтаксис:

ПС(ставка;кпер;плт;бс;тип)

При расчете аннуитетов используются следующие функции:

Аннуитет — это ряд выплат одинаковых денежных сумм, осуществляемых в течение длительного периода. Примерами аннуитета могут служить заем на покупку автомобиля или заклад.

Функция КПЕР

Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

Эта функция имеет следующий синтаксис:

КПЕР(ставка;плт;пс;бс;тип)

Функция СТАВКА

Возвращает процентную ставку по аннуитету за один период. Функция СТАВКА вычисляется путем итераций и может давать нулевое значение или несколько значений. Если последовательные результаты функции СТАВКА не сходятся с точностью 0,0000001 после 20 итераций, функция СТАВКА возвращает сообщение об ошибке #ЧИСЛО!.

Эта функция имеет следующий синтаксис:

СТАВКА(кпер;плт;пс;бс;тип;прогноз)

Функция АПЛ

Возвращает величину амортизации актива за один период, рассчитанную линейным методом.

Эта функция имеет следующий синтаксис:

АПЛ(нач_стоимость;ост_стоимость;время_эксплуатации)

Функция ФУО

Возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка.

Эта функция имеет следующий синтаксис:

ФУО(нач_стоимость;ост_стоимость;время_эксплуатации;период;месяцы)

Метод фиксированного уменьшения остатка вычисляет амортизацию, используя фиксированную процентную ставку. Для вычисления амортизации за период ФУО использует следующие формулы:

(нач_стоимость - суммарная амортизация за предшествующие периоды) * ставка

где:

ставка = 1 - ((ост_стоимость / нач_стоимость) ^ (1 / время_эксплуатации)), округленное до трех десятичных знаков после запятой.

Особым случаем является амортизация за первый и последний периоды. Для первого периода ФУО использует такую формулу:

нач_стоимость * ставка * месяцы / 12

Для последнего периода используется формула:

((нач_стоимость - суммарная амортизация за предшествующие периоды) * ставка * (12 - месяцы)) / 12

Excel поддерживает пять встроенных логарифмических функций: LOG10, LOG, LN, ЕХР и СТЕПЕНЬ. Здесь  я раскрою только функции LOG, LN и ЕХР. Надстройка Пакет анализа предоставляет еще несколько дополнительных и более сложных логарифмических функций.

Функция LOG

Функция LOG возвращает логарифм числа по заданному основанию. Эта функция имеет следующий синтаксис:

LOG(число; основание')

Например,  следующая  формула  возвращает значение 2,321928095,  то есть логарифм 5 по основанию 2: =LOG(5; 2)Если вы не укажете аргумент основание, Excel примет его равным 10.

Функция LN

Возвращает натуральный логарифм числа. Натуральный логарифм — это логарифм по основанию e (2,71828182845904).

Эта функция имеет следующий синтаксис:

LN(число).

Например, следующая формула возвращает значение 0,693147181: =LN(2)

Функция ЕХР

Функция ЕХР возвращает число «e» возведенную в указанную степень. Число «e» равно 2,71828182845904 и является основанием натурального логарифма.

Эта функция имеет следующий синтаксис:

ЕХР(число)

Чтобы вычислить степень с другим основанием, используйте операцию возведения в степень (^). Функция EXP является обратной к функции LN, то есть к натуральному логарифму числа.

Например,     следующая     формула     возвращает     значение     7,389056099 (12,718281828 x 2,718281828): =ЕХР(2). Функция   ЕХР  является  обратной  по отношению к  LN.   Например, пусть ячейка А1 содержит формулу =LN(8), тогда следующая формула возвращает значение 8: =ЕХР(А1)