Практическая часть. Задание 1.Создать сценарии для вариантных расчетов функции БЗ

Задание 1. Создать сценарии для вариантных расчетов функции БЗ. Сохранить варианты и сформировать по ним отчет.

Последовательность действий:

1. Открыть рабочую книгу предыдущей лабораторной работы.

2. Установить курсор на листе Анализ.

3. Выполнить команду меню Сервис → Сценарии.

4. Подготовить сценарии для вариантных расчетов согласно табл. 1

Таблица 1.Варианты сценариев

Параметры	Вар1	Вар2	Вар3	Вар4	Вар5	Вар6	Вар7
Норма	0,18	0,18	0,18	0,20	0,20	0,20	0,20
Число периодов	3,0	3,5	4,0	3,0	3,0	3,5	4,0
НЗ	-1000	-1000	-1000	-800	-1000	-1000	-800

5. В качестве изменяемых ячеек выбрать ячейки параметров: НЗ, Норма и Число периодов, что соответствует адресам: $В$1:$В$2;$В$4. Установить флажок Защита от изменений.

6. Последовательно вывести варианты сценариев.

7. Внести изменения в сценарий с именем Вар 7: новое значение параметра НЗ: - 750.

8. Подготовить отчет в форме структурной таблицы. Для вывода отчета указать адрес ячейки, содержащей функцию, зависящую от изменяемых параметров сценариев — ячейка В6. Отчет выводится на отдельный лист — Структура сценария.

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

10. Закрыть файл с сохранением.

Задание 2. Требуется проследить с помощью таблицы подстановки, как изменение параметра Выплата отражается на значении функций: БЗ, ПЗ, НОРМА.

Последовательность действий.

1. Открыть рабочую книгу с листом Анализ.

2. Установить курсор на листе Анализ в столбец L.

3. Подготовить значения параметра Выплаты в виде блока ячеек одной строки (рисунок 4), например, в ячейках L2:R2.

Ячейка К2 рассматривается как ячейка подстановки по столбцам, поскольку значения параметра представлены в строке. Если бы исходные данные параметра были подготовлены в столбце, то эта же ячейка рассматривалась бы как ячейка подстановки по строкам.

Рисунок 4. Таблица подстановки с одним параметром

4. Ввести формулы (БЗ, ПЗ, НОРМА) в столбец Функции.

Например, в ячейки введены формулы вида:

КЗ: =БЗ(18%/12;3*12;К2;-1000;0)

К4:=НОРМА(3*12;К2;-1000;1000;0) К5:=ПЗ(18%/12;3*12;К2;10000:0)

Число формул не ограничено, в каждой из них имеется ссылка на параметр Выплаты — адрес ячейки постановки (в данном случае К2).

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

6. Выполнить команду меню Данные®Таблица подстановки. В диалоговом окне Таблица подстановки указать адрес ячейки подстановки — Подставлять значения по столбцам в $К$2.

7. Нажать кнопку ОК.

В результате формируется массив формул вида {=ТАБЛИЦА(К2;)}. Ячейка К2 — ячейка подстановки.

8. Закрыть файл с сохранением.

Задание 3. Требуется с помощью таблицы подстановки проанализировать влияние параметров Норма (Параметр 1) и Выплата (Параметр 2) на функцию БЗ.

Последовательность действий:

1. Открыть рабочую книгу с листом Анализ.

2. Установить курсор на листе Анализ.

Подготовить значения параметров Норма и Выплата в виде блока ячеек, М13:М15 и N3:Т3, соответственно:

Параметр 1	Ячейка 1
Ячейка 2	Параметр 2
=Б3(М10/12;3*12;М11;-1000;0)	-100	-125	-150	-175	-200	-250	-500
18%
20%
24%

Рисунок 5. Таблица подстановки с двумя параметрами

Ячейка 1 соответствует ячейке подстановки для Параметра 1 (подстановка по строкам), ячейка 2 — соответственно для Параметра 2 (подстановка по столбцам). Эти ячейки могут, располагаться в произвольном месте на том же самом или даже другом листе. В данной случае используются ячейки М10 и М11 соответственно для Параметра 1 и Параметра 2.

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

5. Использовать ссылку на ячейки подстановки:

=БЗ(М10/12;3*12;М11;-1000;0)

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

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

Появляется диалоговое окно Таблица подстановки для указания адреса ячейки подстановки — Подставлять значения по столбцам в М11, Подставлять значения по строкам в М10. Нажать кнопку ОК.

8. В результате формируется массив формул вида {=ТАБЛИЦА(М11;М10)}.

9. Закрыть файл с сохранением.

3. Содержание отчета:

1. Название работы.

2. Цель работы.

3. Законспектировать теоретическую часть (Основные положения) лабораторной работы.