ФУНКЦИИ В MS EXCEL

Запись формул.

Как уже было сказано, для занесения формулы в ячейку таблицы необходимо ввести символ “=”, а далее набирать выражение, состоящее из операндов, соединенных между собой знаками операций. В качестве операндов формульного выражения могут быть: числа, адреса клеток, функции. Знаками арифметических операций служат те же символы, которые используются в математике, кроме операции умножения, для обозначения которой используется символ “*” (звёздочка). В Excel имеется набор функций, которые пользователь может использовать при занесении в клетки таблицы расчетных формул. Использование функций состоит в записи имени функции, после которого в круглых скобках записывается список аргументов. Аргументы разделяются в списке точкой с запятой. В качестве аргумента может использоваться число, адрес ячейки или арифметическое выражение.

Если ввод формулы начать щелчком на кнопке со знаком = (Изменить формулу) в строке формул, то под строкой формул появится палитра формул, обладающая свойствами диалогового окна. Она содержит значение, которое получится, если немедленно закончить ввод формулы. В левой части строки формул, где раньше располагался адрес текущей ячейки (поле имени), теперь появляется раскрывающийся список функций. Он содержит десять функций, которые использовались последними, а также пункт Другие функции. При выборе Другие функции запускается Мастер функций.

Работа с Мастером функций, запись функций. Встроенные функции MS Excel. Логические, математические и финансовые функции.

Мастер функций, который можно также вызвать командой Вставка/Функция или нажатием кнопки ”fx“ на панели инструментов “Стандартная”, облегчает выбор нужной функции. Мастер функций, как любой Мастер OS Windows, представляет собой последовательность взаимосвязанных диалоговых окон для задания параметров и значений.

Мастер функций имеет два списка. В левом списке окна выбирается категория функции, в правом списке даются имена всех функций, входящих в данную категорию. Выбрав в этом списке функцию, можно нажать кнопку Закончить и затем “вручную” отредактировать запись функции в ячейке, куда будет перенесена заготовка функции. Если же нажать кнопку Шаг, на экран поступит второе окно – Палитра формул. В этом окне можно ввести аргумент (аргументы) функции. В ходе ввода аргументов функции палитра формул изменяет вид. На ней отображаются поля, предназначенные для ввода аргументов. Если название аргумента указано полужирным шрифтом, аргумент является обязательным, и соответствующее поле должно быть заполнено. Аргументы, названия которых приводятся обычным шрифтом, можно опускать. В нижней части палитры приводится краткое описание функции, а также назначение изменяемого аргумента. Здесь можно также увидеть значение функции, вычисленное при заданных значениях аргументов.

В Excel имеются следующие наиболее широко используемые статистические и математические функции:

СРЗНАЧ(<спис. аргум.>) – дает среднее арифметическое из значений всех аргументов;

МАКС(<спис. аргум>) – дает максимальное число из списка аргументов;

МИН(<спис. аргум>) – дает минимальное число из списка аргументов;

ПРОИЗВЕД(<спис. аргум>) – дает произведение значений аргументов;

КОРЕНЬ(<аргум>) – дает корень квадратный из аргумента;

ФАКТР(<аргум>) – дает факториал целого числа, заданного в качестве аргумента

СЛЧИС – дает случайное число в интервале от 0 до1;

ABS(<аргум>) – дает абсолютное значение аргумента;

LN(<аргум>) – дает значение натурального логарифма от аргумента;

EXP(<аргум>) – дает значение экспоненты от аргумента;

SIN(<аргум>) – дает значение синуса от аргумента;

COS(<аргум>) – дает значение косинуса от аргумента;

TAN(<аргум>) – дает значение тангенса от аргумента.

В качестве аргументов статистических и математических функций могут быть конкретные числа, а также адреса ячеек (блоков ячеек) таблицы, значениями которых являются числа.

При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов в зависимости от выполнения или невыполнения одного или нескольких условий. Для реализации такой возможности в Excel имеются логические функции. Логические функции содержат в своей записи логические выражения.

Логические выражения (в отличие от арифметических) могут иметь лишь два значения, а именно: либо значение истина, либо значение ложь.

Простыми логическими выражениями называются выражения, в которых операнды связаны между собой операциями отношений. Существуют следующие операции отношений: = равно,<> не равно, < меньше, <= меньше или равно, больше >, >= больше или равно.

Рассмотрим три из имеющихся в Excel логических функций: ЕСЛИ, И, ИЛИ.

Функция ЕСЛИ дает одно из двух значений, которые записаны в качестве аргументов данной функции, в зависимости от значения логического выражения, также являющемся аргументом функции. Функция имеет следующую форму записи (синтаксис): ЕСЛИ(логич.выражение;1-е значение; 2-e значение).

Если логическое выражение имеет значение истина, то функция будет иметь значение, представляемое вторым аргументом функции, если же логическое выражение имеет значение ложь, то функция будет иметь значение, представляемое третьим аргументом функции.

Для получения из простых логических выражений составных логических выражения предназначены функции И, ИЛИ.

Синтаксис функции И следующий: И(лог. знач. 1; лог. знач. 2; лог. знач. 3;…). Эта функция имеет значение истина, если все аргументы имеют значение истина, и дает значение ложь, если хотя бы один аргумент имеет значение ложь.

Аргументы должны быть логическими значениями или ссылками, которые содержат логические значения.

Функция ИЛИ имеет синтаксис: ИЛИ(лог. знач. 1; лог .знач. 2; лог. знач.3;…). Эта функция имеет значение истина, если хотя бы один аргумент имеет значение истина, и дает значение ложь, если все аргументы имеют значение ложь.

Функции И, ИЛИ могут иметь от 1 до 30 аргументов.

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

В финансовой математике приняты следующие соглашения.

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

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

При использовании финансовых функций в MS Exсel надо учитывать знаки денежных сумм, помня, с чьей стороны рассматривается финансовая операция: кредитора или дебитора.

Рассмотрим простейшую схему кредитования. Пусть некоторая первоначальная сумма P предоставляется в кредит на определенный период времени. За использование кредита надо платить. По окончанию периода времени надо возвращать сумму:

S = P + I

Плата за использование кредита I в русскоязычной литературе носит название «процент». В англоязычной литературе плата за кредит обозначается буквой I (Interest – выгода). Иногда в англоязычной литературе используется термин «percent» (от лат. «сотая доля»).

Плата зависит от величины первоначальной суммы P и процентной ставки r (rate of Interest) за период времени пользования кредитом.

I = r ´ P

Обычно период времени пользования кредитом равен году, и процентную ставку относят к году. Например, процентная ставка за кредит составляет 6% годовых, или 0.06.

Сумму, которую нужно вернуть, называют «наращенной суммой» (ассumulated value):

S = P ´ ( 1 + r )

Отношение S/P называют коэффициентом наращения. Например, если процентная ставка составляет 50% годовых, то за 1 год первоначальная сумма увеличится в 1,5 раза, т.е. коэффициент наращения будет равен S/P=1,5.

Если кредит дается на несколько периодов времени, то различают простые и сложные проценты.

Для простых процентов процентная ставка постоянна в каждый период времени. Поэтому начальное P и будущее S значения сумм связаны уравнением:

S = P ´( 1 + r )N

где N – число периодов времени пользования кредитом.

Для сложных процентов процентная ставка различна в каждый период времени. Пусть на банковский счет положена первоначальная сумма P и пусть известны процентные ставки ri на несколько лет вперед. Определим будущие значения вклада по прошествии N лет:

S1=P*(1+r1) после 1-го года,

S2=P*(1+r1)*(1+r2) после 2-го года,

.     .           .           .           .           .           .           .           .

SN=P*(1+r1)*(1+r2)…(1+rN) после N-го года

Обычно ежегодные процентные ставки бывают одинаковыми. Тогда получаем:  r1 = r2=…= rN = r  и возвращаемся к простым процентам.

SN= P ´ ( 1 + r )N

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

Р = SN / [ ( 1 + r1 ) ( 1 + r2 )…( 1 + rN ) ] при разных ставках,

Р = SN / ( 1 + r )N при одинаковых ставках.

Эти формулы справедливы для случая, когда приращение основного капитала не изымается, а остается на счете в банке, участвуя в дальнейшем наращивании.

В MS Excel для вычисления простых процентов используются формулы для сложных процентов, поэтому для обоих случаев используются одни и те же функции.

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

Функция БЗ (будущее значение) вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.

БЗ( <ставка>; <количество периодов>; <выплата>; <начальное значение>; <тип> )

Аргументы функции:

<ставка> – процентная ставка по вкладу за один период, обычно за год. Ставка может записываться в форме процента: 6% или в форме десятичной дроби: 0,06.

<количество периодов> – общее число периодов выплат, взносов или хранения вклада.

<выплата> – величина постоянных периодических выплат или взносов. Вносимая сумма должна быть со знаком минус. Если параметр опущен, он равен 0.

<начальное значение> – первоначальное значение банковского вклада. Если параметр опущен, он равен 0.

<тип> – число 0 и 1, обозначающее, когда должна быть произведена выплата. Если <тип>=0 или опущен, то выплата должна производиться в конце периода. Если <тип>=1, то выплата должна быть произведена в начале периода.

Пусть мы собираемся вложить в банк 1000 руб. при годовой ставке 6%. Дополнительно мы будем вкладывать по 100 рублей в начале каждого месяца в течение 1 года. Сколько денег будет в конце 12 месяцев?

БЗ( 6%/12; 12; -100; -1000; 1 ) = 2301,40 р.

Если платежи производятся в конце периодов, то эти платежи называют постнумерандо (этому соответствует <тип>=0, по умолчанию, можно не указывать.). Если платежи производятся в начале периодов, то они называются пренумерандо (для них <тип>=1, указывается обязательно).

Часто приходится решать обратную задачу, т.е. определять настоящее (текущее) значение вклада при заданном ожидаемом доходе в будущем. Для этого в MS Exсel имеется функция ПЗ (первоначальное значение).

ПЗ( <ставка>; <количество периодов>; <выплата>;<будущее значение>; <тип> )

Здесь аргументы те же, что и у функции БЗ.

Допустим, мы хотели бы накопить 20000 рублей за 5 лет, положив некоторую сумму в банк при условии начисления 14% ежегодно. Каков должен быть первоначальный вклад?

ПЗ( 14%; 5; ; 20000 ) = -10 387,37 р.

Результат имеет знак минус, т.к. это сумма (инвестиция), которую надо вложить в банк. Легко проверить, что функция =ПЗ дает результат, обратный функции =БЗ.

КОММЕНТАРИИ