Файл: контрольная работа №1 и №2.pdf

6 

В  нижней  части  диалогового  окна  описывается  назначение  данной  функции; 

ниже  дается  содержание  области  позиции  курсора  ввода  (на 

Рис. 1

  это  позиция 

Кон_период). Если после знака «=» отсутствует результат, следует проверить, все 

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

названием функции надо поставить знак «минус». 

Полученный ответ записать на листе задания. 

Задание 2. 

Рассчитать и правильно округлить полное количество лет погашения кредита, 

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

КПЕР и ОКРУГЛВВЕРХ. 

Сумма 

кредита 

Ежегодная 

сумма 

погашения 

Ставка 

кредита 

45000 8000  9%

функция 

функция 

Решение 

КПЕР() 

ОКРУГЛВВЕРХ() 

1) 

Срок 
погашения:  

лет 

8,19 9 

Рис. 2 Исходные данные для задания 2 

7 

Рис. 3 Применение функции КПЕР 

Особенности решения: 

•  При расчете срока погашения необходимо округлить результат вверх. Не 

целое число лет (8,19) не означает, что кредит будет  гаситься неполное 

количество  лет.  Это  значит  лишь,  что  в  последний  9-й  год  сумма 

последней выплаты будет несколько ниже. 

•  В  функции  КПЕР  необходимо  указать  периодическую  выплату  со 

знаком  минус.  Если  просто  поставить  минус  перед  всей  функцией,  а 

выплату оставить положительной, результат будет неверным  

•  Параметр  "Тип"  в  функции  КПЕР  должен  быть  равен 0 или  опущен 

(выплата  в  конце  периода),  поскольку  погашение  кредита  начнется 

только через год. 

Полученный ответ записать на листе задания. 

8 

Задание 3. 

Построить таблицу погашения кредита, используя функции ЕСЛИ или МИН, а 

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

и относительные адресные ссылки в формулах.  

Относительная  адресная  ссылка:  имеет  вид    А5,  где  А - буква  колонки 

таблицы Excel; 5 - номер  строки  таблицы Excel. Такая  ссылка  при  копировании 

или  перемещении  вправо/влево  изменяет  букву  обозначения  колонки;  при 

копировании вниз/вверх - меняет номер строки. 

Абсолютная  адресная  ссылка:  имеет  вид  $А$5,  т.е.  при  копировании  или 

перемещении  вправо/влево  или  вниз/вверх  не  изменяет  ни  букву  колонки,  ни 

номер строки. 

Смешанная  адресная  ссылка:  имеет  вид  $А5  или  А$5.  Такая  ссылка  при 

копировании  или  перемещении  оставляет  неизменным  только  фиксированный 

элемент. Закрепление (фиксирование) показывается знаком $ , который задается и 

снимается нажатием клавиши F4. 

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

двух колонок: Сумма выплаты и Остаток кредита.  

А 

В 

С D 

1 45000 

8000 

9% 

2 

Дата 

Сумма 
выплаты 

Остаток 
кредита 

3  

1-янв-2005  

45000 Год выдачи кредита 

4  

1-янв-2006 8000

41050  

5  

1-янв-2007 8000

36744,50  

6  

1-янв-2008 8000

32051,51  

7  

1-янв-2009 8000

26936,14  

8  

1-янв-2010 8000

21360,39  

9  

1-янв-2011 8000

15282,83  

9 

10  

1-янв-2012 8000

8658,28  

11  

1-янв-2013 8000

1437,53  

12  

1-янв-2014 

1566,91

0,00  

Рис. 4 Таблица погашения кредита 

Т.к.  выплаты  кредита  (из  условия  данной  задачи)  осуществляются  в  конце 

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

Начиная  со  второго  года  выплат,  следует  ввести  в  каждую  колонку  по 

формуле,  закрепив  адресные  ссылки,  которые  указывают  на  неизменяемые 

данные. 

В ячейку С4 ввести функцию МИН (

Рис. 5

).  

По  результатам  данной  функции  выбирается  минимальное  значение  из  двух 

величин: 

•  заданной суммы выплаты 

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

Рис. 5 Применение функции МИН 

10 

В  ячейку D4 вводится  формула,  вычисляющая  остаток  кредита  на  текущую 

дату: 

=D3*(1+$D$1)-C4 

Далее следует выделить ячейки С4 и D4 и скопировать их вниз. 

В  предпоследний (11-й)  год  выплат  остаток  кредита  становится  равным 

1437,53.  Таким  образом,  в  последний (12-й)  год  будет  выплачена  сумма  с 

начисленным процентом: 

1566,91 (см.Рис. 4). 

Полученный ответ записать на листе задания. 

Задание 4. 

По  данным  Курса  акций  построить  график,  определить  величину 

коэффициента  достоверности  аппроксимации  для  линейной  и  экспоненциальной 

зависимости,  выбрать  коэффициент,  бо́льший  по  значению.  Коэффициент, 

численное значение которого больше, указывает на  более близкую зависимость. 

Для выполнения этого задания нужно построить две колонки  и  Курс акции  и 

ввести последовательно значения из листка задания (см. Приложение). 

А 

В 

1 

х 

у 

2 

01.01.2014 200 

3 

01.02.2014 208 

4 

01.03.2014 206 

5 

01.04.2014 210 

6 

01.05.2014 212