Тема: Логические встроенные функции. Некоторые встроенные экономические функции в MSExcel.

 

Цель: изучить некоторые встроенные экономические и логические функции, уметь использовать их при решении практических задач.

Теоретические сведения

Группа ло­гических функций:

1. ЕСЛИ(УСЛОВИЕ; ВЫРАЖЕНИЕ!; ВЫРАЖЕНИЕ2) – функция проверки условия. В текущую ячейку заносится величина, вычисленная в соответ­ствии с выражением, если условие (одно или несколько) истинно, в противном случае эта величина вычисляется по выражению 2.

2. И и ИЛИ предназначены для проверки выполнения нескольких условий.

Когда условия соединены логическим И, результатом проверки нескольких условий считается:

• значение ИСТИНА, если все условия имеют значение ИС­ТИНА;

• значение ЛОЖЬ, если хотя бы одно условие имеет значение ЛОЖЬ.

Когда условия соединены логическим ИЛИ, результатом провер­ки условий считается:

• значение ИСТИНА, если хотя бы одно из условий имеет зна­чение ИСТИНА;

• значение ЛОЖЬ, если все условия имеют значение ЛОЖЬ.

Логические функции можно при необходимости комбинировать, например:

ЕСЛИ(И (УСЛОВИЕ 1; УСЛОВИЕ2); ВЫРАЖЕНИЕ; ВЫРАЖЕНИЕ2).

3. ПРОСМОТР(ИСКОМОЕ ЗНАЧЕНИЕ; ДИАПАЗОН) - группа функций ссылки и массивы (1-й вариант — векторный просмотр; 2-й вариант — массив).

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

Группа финансовых функ­ций:

ППЛАТ(НОРМА;КПЕР;НЗ;БС;ТИП)

• НОРМА — норма прибыли за период займа;

• КПЕР — общее число периодов выплат годовой ренты;

• НЗ — текущая стоимость: общая сумма всех будущих плате­жей с настоящего момента;

• БС — будущая стоимость или баланс наличности, которую
нужно достичь после последующей выплаты;

• ТИП — логическое значение (0 или 1), обозначающее, долж­на ли производиться выплата в конце периода (0) или в на­чале периода (1).

Функция ППЛАТ может быть использована для анализа всевозможных ссуд. Необходимым условием является непротиворечивость аргументов функции.

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

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

Синтаксис: БЗ (СТАВКА; КПЕР; ПЛАТА; НЗ; ТИП).

СТАВКА — это процентная ставка за период.

КПЕР — это общее число периодов выплат годовой ренты.

ПЛАТА — это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно плата состоит из основного платежа и платежа по про­центам, но не включает других налогов и сборов.

НЗ — это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент НЗ опущен, то он полагается равным 0.

ТИП— это число 0 или 1, обозначающее, когда должна произ­водиться выплата: 0 — в конце периода, 1 — в начале периода. Ес­ли аргумент опущен, то он полагается равным 0.

Для аргументов СТАВКА и КПЕР используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то СТАВКА должна быть 12%/12, а КПЕР должно быть 4*12. Если производят­ся ежегодные платежи по тому же займу, то СТАВКА должна быть 12%, а КПЕР должно быть 4.

Все аргументы, означающие деньги, которые вы платите (на­пример, депозитные вклады), представляются отрицательными числами; деньги, которые вы получаете (например, дивиденды), представляются положительными числами.

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

ПЗ — возвращает текущий объем вклада. Текущий объем — это общая сумма, которую составят будущие платежи. Например, когда вы берете взаймы деньги, заимствованная сумма и есть текущий объем для заимодавца.

Синтаксис: ПЗ (СТАВКА; КПЕР; ПЛАТА; НЗ; ТИП).

Для определения срока платежа и процентной ставки исполь­зуются функции КПЕР и НОРМА.

Функция КПЕР вычисляет общее число периодов выплат как для единой суммы вклада (займа), так и для периодических постоянных выплат на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, то для того, чтобы найти число лет выплат, общее число периодов надо разделить на число периодов в году.

Синтаксис: КПЕР (СТАВКА; ПЛАТЕЖ; НЗ; БЗ; ТИП). СТАВКА — это процентная ставка за период. ПЛАТЕЖ — это выплата, производимая в каждый период; он может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам, никакие другие сборы или налоги не учитываются.

НЗ — это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.

БЗ — это будущая стоимость, или баланс наличности, который должен быть достигнут после последней выплаты. Если аргумент БЗ опущен, то предполагается, что он равен 0 (будущая стоимость займа, например, равна 0).

ТИП — это число 0 или 1, обозначающее, когда должна произ­водиться выплата.

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

Синтаксис: НОРМА (КПЕР; ВЫПЛАТА; НЗ; БЗ; ТИП; НАЧ ПРИБЛ).

Описание смысла аргументов КПЕР, ВЫПЛАТА, НЗ, БЗ и ТИП дано в предыдущем разделе.

НАЧ ПРИБЛ — это предполагаемая величина нормы.

Если НАЧ ПРИБЛ опущено, то оно полагается равным 10%.

Если функция НОРМА не сходится, следует попытаться ис­пользовать различные значения НАЧ ПРИБЛ. Обычно функция НОРМА сходится, если НАЧ ПРИБЛ имеет значение между 0 и 1.

 

Задание

1. Изучите предложенные функции.

2. Выполните упражнение

Технология работы

1. Использование функции ЕСЛИ.

1.1.В ячейки А11:А15 введите значения: 920, 1110, 1500, 1350, 760.

1.2. В зависимости от значений ячеек А11:А15 вы­полните: если значение больше 1000, делим его на 100, если нет — делим на 10. Результат должен быть получен в ячейках В11:В15.

1.3. Функция вводится сначала в ячейку В11: =ЕСЛИ(А11>1000;А11/100;А11/10). Словами это условие можно выразить так: «Если значение в ячейке А11 больше 1000, то его де­лим на 100; в противном случае делим его на 10». Результат деле­ния получится в ячейке В11 (там, куда вводилась функция ЕСЛИ).

1.4. Скопируйте формулу для всех ячеек до В15. В ячейку В11 вводим:

2. Использование функции И/ИЛИ

Для каждой ячейки из диапазона А11:А15 примера 1 прове­рить условие: если значение ячейки {A_i } больше 900 и одновре­менно меньше 1500, то умножить его на 100, в противном случае оставить значение ячейки {A_i} неизменным. Результат должен быть получен в ячейках С11:С15.

2.1. В ячейку С11 ввести: =ЕСЛИ(И (А11 >900; А11 <1500);А11 *10;А11).

2.2. Выполните те же действия с использованием мастера функции. Сначала вызывается функция ЕСЛИ, затем из списка встроен­ных функций в левой части строки формул вызывается функция И для ввода логического условия.

После завершения ввода усло­вий в соответствующие поля окна ИЛИ, следует щелкнуть указа­телем мыши в конце строки формулы и продолжить ввод значе­ний в окне ЕСЛИ.

2.3. Из ячейки С11 формулу скопируйте в ячейки С12:С15.

3. Функция поиска данных в некотором диапазоне

По номеру месяца определить его название. Для этого создает­ся отдельная таблица, где номеру месяца ставится в соответствие его название.

3.1. Используя автозаполнение, введите: в ячейки с J11:J12 цифры от 1 до 12; в ячейки К1:К12 названия месяцев с января по декабрь.

3.2. В ячейку Н9 введите любое число от 1 до 12.

3.3. В ячейку 19 вставьте функцию ПРОСМОТР, выбрав первый способ задания аргументов (отдельно вектор просмотра и вектор результата):

=ПPOCMOTP(H9;$J$1:$J$12;$K$1:$K$12) — знак $ устанавливает абсолютные ссылки на адреса областей расположения номеров и названий месяца. (Для фиксации адреса нажмите <F4> в конце адреса). В ячейке I9 появится название соответст­вующего месяца.

3.4. В ячейку Н10 введите новое число от 1 до 12.

3.5. Скопируйте в ячейку I10 формулу из ячейки I9.

По номеру месяца от текущей даты определить название меся­ца. Результат должен быть получен в ячейке I11.

3.6. В ячейку I11 вставить функцию ПРОСМОТР, выбрав второй способ задания аргументов (сразу весь массив). В качестве иско­мого значения укажите номер текущего месяца, воспользовав­шись вложенными функциями:

=ПРОСМОТР(МЕСЯЦ(СЕГОДНЯ());$J$1:$К$12). В ячейке I11 получим название месяца текущей даты.

4. Оценка ежемесячных выплат

Предположим, что нужно воспользоваться 9-процентной 15-летней ссудой. Объем ссуды составляет 150 000 000 рублей. С помощью Мастера функций можно определить величины ежеме­сячных выплат. Предварительно следует привести все другие зна­чения к месячной норме.

4.1. Введите таблицу (рис.1), начиная с ячейки А15:

Процентная ставка	9%
Период
Удельная ставка
Число выплат
Объем ссуды	-150000000
Ежемесячная выплата

Рис. 1. Определение величины ежемесячных выплат

4.2. В ячейки В16 и В17 введите соответствующие формулы.

Процентная ставка (НОРМА) — годовая, поэтому для получе­ния месячной ставки (Удельная ставка) соответствующее значе­ние делится на 12 (0,09/12).

Срок действия ссуды — 15 лет, поэтому с учетом 12 платежей в год общее количество месячных выплат (КПЕР) составит 12x15.

4.3. Для ячейки В20 пошаговыми действиями Мастера функций выполните настройку функции ППЛАТ.

4.4. После этого в поле Значе­ние диалогового окна Мастера функций вы увидите сумму ежеме­сячного взноса. А после нажатия на кнопку Готово результат ото­бразится в ячейке.

Определить какими будут выплаты по ссуде при меняющейся процентной ставке.

4.5. В ячейки А22:В26 введите следующие значения, оставив пус­той строку перед числовыми значениями (рис.2):

Процентная ставка	Выплаты
7%
8%
10%

Рис. 2. Определение величины ежемесячных выплат с использованием таблицы подстановки

4.6. В ячейку В23 скопировать формулу для расчета ежемесячных выплат.

4.7. Для расчета выплат по каждой из ставок воспользуйтесь воз­можностью автоматической подстановки значений в нужную ячейку (в нашем случае в В15). Для этого нужно: выделить диапазон А23:В26, включив в него значения про­центных ставок и расчетную формулу (формула должна на­ходиться в ячейке, расположенной правее и выше заданных значений).

4.8. В меню Данные выбрать команду Таблица подстановки.

4.9. В поле «Подставлять значения по строкам в:» указать ячейку В15.

Рядом с каждой процентной ставкой появится соответствую­щий результат.

4.10. Измените значения процентных ставок или расширьте пред­лагаемый диапазон и вновь воспользуйтесь таблицей подстанов­ки значений.

5. Функция БЗ

Например, вы собираетесь вложить 1000 руб. под 6% годовых (что составит в месяц 6%/12 или 0,5%). Вы собираетесь вклады­вать по 100 руб. в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету в конце 12 месяцев?

БЗ (0,5%; 12; -100; -1000; 1). Результат 2301,40 руб.

5.1. Для выполнения расчета вызовите Мастер функций, в поле Категории выберите финансовые функции и в поле Функция выберите функцию БЗ.

5.2. В появившемся окне заполняются соот­ветствующие поля путем подстановки значений аргументов, а если данная функция вычисляется в расчете, то вместо этого указываются адреса исходных данных из таблицы расчета.

6. Функция ПЗ

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

Синтаксис: ПЗ (20%, 5, 5000). Результат 2009,39.

7. Функция КПЕР

Рассчитаем срок погашения ссуды размером 5000 руб., выданной под 20% годовых при погашении ежемесячными плате­жами по 200 руб.

Синтаксис: КПЕР (20%/12; -200; 5000). Результат 32,6 месяца или 2,7 года.

8. Функция НОРМА

Например, надо определить процентную ставку для четырех­летнего займа в 8000 руб. с ежемесячной выплатой в 200 руб.

Синтаксис: НОРМА (48; -200; 8000). Результат 0,008, или 0,8% в месяц или 9,6% годовых.

9. Упражнение

9.1. Создать новую книгу и ввести таблицу (рис. 3), начиная с третьей строки:

• шапку таблицы ввести в две строки, задав для ячеек, содер­жащих названия граф, расположение по центру столбца;

• текст «Объем страховых сделок» расположить по центру че­тырех столбцов;

• названия граф «№ п/п», «Комиссионное вознаграждение»
расположить в 2 ячейки по вертикали, объединив эти ячей­ки;

• для граф «№ п/п», «за III квартал», «Комиссионное возна­граждение» — задать перенос по словам;

• названия граф «июль», «август», «сентябрь» ввести исполь­зуя автозаполнение.

9.2. Ввести фамилии, после этого переопределить ширину столбца В, сделав его равным максимальной длине фамилии.

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

9.3. Ввести числовые данные в столбцы D, Е и F.

9.4. Автоматически пронумеровать все фамилии в столбце А.

9.5. Сделать сортировку всей таблицы по фамилиям (без столбца (А).

№ п/п	Фамилия	Объем страховых сделок	Комиссионное вознаграждение
за III квартал	июль	август	сентябрь
	Иванов
	Федоров
	Антонов
	Орлов
	Смирнов
	Владимиров
	Егоров
	Громов
	Антонов
	Борисов
	Всего:
	Средний объем сделок
	Максимальный объем
	Минимальный объем
	Количество неотчитавшихся

Рис. 3. Отчет о деятельности страховых агентов

9.6. Вычислить для каждого страхового агента объем сделок за III квартал как сумму сделок за июль, август и сентябрь.

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

9.8. Ввести тарификационную таблицу, предварительно задав для диапазона столбца со значениями процентов процентный фор­мат. Присвоить имя «Тариф» диапазону ячеек, содержащему числовые данные тарификационной таблицы (см. ниже).

9.9. По каждой фамилии рассчитать данные в графе «Комисси­онное вознаграждение» как произведение «Объема страховых сде­лок за III квартал» на значение процента, вычисленного функци­ей ПРОСМОТР по Тарификационной таблице. Для функции ПРОСМОТР выбрать синтаксическую форму ПРОСМОТР (искомое_значение;массив) и указать в формуле имя массива — Тариф. Для уточнения действий функции ПРОСМОТР воспользоваться Справочной системой Excel.

9.10. Ввести текст примечаний в ячейки с фамилиями агентов, имеющих максимальный и минимальный объем сделок за III квартал.

9.11. Справа от графы «Комиссионное вознаграждение» рассчи­тать ранг каждого страхового агента по показателем III квартала.

9.12. Создать имя для диапазона ячеек с данными за III квартал и применить его к ячейкам диапазона с данными о ранге.

9.13. Рассчитать процентную норму ранга за III квартал.

9.14. Разграфить тарификационную таблицу, написать заголовок по центру табли­цы более крупным шрифтом.

	8%
	10%
	12%
	15%
	18%
	20%
	25%

 

Работа № 10