Методические указания и задания для практических занятий Москва 2005 (стр. 7 из 8)

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании смешанной ссылки из ячейки A2 в ячейку B3, она изменяется с =A$1 на =B$1.

Таким образом, используются следующие виды ссылок.

$A$1 (абсолютный столбец и абсолютная строка)	$A$1
A$1 (относительный столбец и абсолютная строка)	C$1
$A1 (абсолютный столбец и относительная строка)	$A3
A1 (относительный столбец и относительная строка)	C3

Для преобразования типа ссылки может быть использована клавиша F4.

Работа с листами в групповом режиме

Групповой режим работы с листами представляет собой процедуру одновременного внесения одинаковых изменений в нескольких листах одной книги. Для выполнения данной процедуры необходимо выделить листы, удерживая нажатой клавишу CTRL или SHIFT.

Основные функции используемы для выполнения заданий

Функция ЕСЛИ

Функция ЕСЛИ используется при проверке условий для значений и формул. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.

Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент — строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.

Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.

Функция СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.

СЧЁТЕСЛИ(диапазон;критерий)

Диапазон — диапазон, в котором нужно подсчитать ячейки.

Критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

Функция СУММЕСЛИ

Функция СУММЕСЛИ суммирует ячейки, заданные критерием.

СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

Диапазон — диапазон вычисляемых ячеек.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Диапазон_суммирования — фактические ячейки для суммирования.

Функция СРЗНАЧ

СРЗНАЧ(число1; число2; ...)

Возвращает среднее (арифметическое) своих аргументов.

Число1, число2, ... — это от 1 до 30 аргументов, для которых вычисляется среднее.

Финансовые функции

Финансовая функция БЗ

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

БЗ (норма, число_периодов, выплата, нз, тип)

Существуют различные варианты использования этой функции при решении конкретных задач.

Если необходимо рассчитать будущую стоимость единой суммы вклада, по которой начисляются сложные проценты определённое число периодов, то эту величину можно рассчитать по формуле

,

где

- будущая стоимость вклада или займа; - текущая стоимость вклада (займа); n – общее число периодов начисления процентов; r – процентная ставка по вкладу (займу).

В этом случае на рабочем листе формула примет вид:

БЗ (норма, число_периодов, нз)

Бывают ситуации, когда платежи производятся систематически, а не один раз, как в предыдущем варианте. Эти платежи могут осуществляться в начале каждого расчётного периода (так называемые «обязательные платежи» или пренумерандо) или в конце («обычные платежи» или постнумерандо) в течение n периодов. В каждом периоде вносится одинаковая сумма. Необходимо найти их будущую стоимость в конце n-го периода для обоих случаев. Отличие в расчёте при этом заключается в том, что во втором случае не происходит начисления процентов на последний вклад, т.е. все вклады пренумерандо увеличиваются на сложные проценты на один расчётный период больше, чем вклады постнумерандо.

Для расчёта будущей стоимости серии фиксированных периодических платежей, если они вносятся в начале каждого периода, используется формула:

,

где

- будущая стоимость серии фиксированных периодических платежей; - фиксированная периодическая сумма платежа; n– общее число периодов выплат; r– постоянная процентная ставка.

В этом случае на рабочем листе формула примет вид:

БЗ (норма, число_периодов, выплата, ,1)

Здесь тип=1, т.к. он определяет время начисления процентов: в конце/начале (0/1) периода. Если тип опущен, он считается равным 0.

Для расчёта будущей стоимости серии фиксированных периодических платежей, если выплаты происходят в конце периода, то формула модифицируется:

В этом случае на рабочем листе Excel формула примет вид:

БЗ (норма, число_периодов, выплата, ,0) или

БЗ (норма, число_периодов, выплата)

При решении конкретной задачи вместо названий аргументов следует записать соответствующие числа. Результаты должны совпадать с расчётом формул.

Функция ПЗ

Данная функция предназначена для расчёта текущей (настоящей) стоимости, как единой суммы вклада (займа), так и будущих фиксированных периодических платежей. Этот расчёт является обратным к определению будущей стоимости при помощи функции БЗ.

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

Если известно будущее значение вклада (займа) и требуется определить текущее значение этого вклада, т.е. сумму, которую необходимо положить на счёт сегодня, чтобы в конце n-го периода она достигла заданного значения. Это значение можно получить из формулы:

В этом случае на рабочем листе формула примет вид:

ПЗ(норма, кпер, , бс)

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

В этом случае на рабочем листе формула примет вид:

ПЗ(норма, кпер, выплата, , 1)

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

Соответствующая этому расчёту формула примет вид:

ПЗ(норма, кпер, выплата)

Функция НПЗ

Функция вычисляет чистую текущую стоимость (NPV) периодических платежей переменной величины как сумму ожидаемых доходов и расходов, дисконтированных нормой процента r: