Упражнение 7.36

1. Создайте рабочую книгу Свод из трех листов. Присвойте первому листу имя «Ноябрь», второму - «Декабрь», третьему - «Итог».

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

3. Снимите выделение листов и внесите необходимые изменения в названия трех таблиц.

4. Введите заголовки и заполните данными на листе «Ноябрь»таблицу 1, а на листе «Декабрь»таблицу 2.

5. Выполните расчеты следующим образом: · графа «Доходы бюджета» = графа «Выручка» - графа «Погашено»; графа «Средняя взвешенная цена» = графа «Выручка»/графа «эмиссия» * 100.

6. Подсчитайте итоговые суммы за ноябрь и декабрь методом автосуммирования.

7. Перейдите на лист «Итог» и введите заголовок «Итоговая таблица».

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

9. Подсчитайте общие суммы по каждой графе листа «Итог».

10. На листе «Декабрь» рассчитайте промежуточные итоги по каждому номеру выпуска ГКО.

Таблица 1

Таблица 2

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

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

Математическая суть задачи состоит в решении уравнения f (х) = а, где функция f (х) описывается заданной формулой, х – искомый параметр, а –требуемый результат формулы.

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

• ссылку на ячейку c расчетной формулой (поле Установить в ячейке);

• ссылку на ячейку, содержащую значение изменяемого параметра ( поле Изменяя значение ячейки);

• нажать на кнопку <ОК>.

Пусть с помощью подбора параметра надо ответить на вопрос: как обеспечить накопление на банковском счете в течение 6 лет суммы, равной 70 тыс. руб. при ежегодной процентной ставке в 20%? Иными словами, надо определить размер ежегодных взносов на банковский счет.

Для решения этой задачи примем, что для вычисления суммы, накапливаемой на счете, будет использована функция БС, а допустимая для клиента сумма ежегодного взноса равна 5000. Исходная таблица с окном Мастера функций при этом имеет вид (рис.7.41).

Рис. 7.41. Пример работы финансовой функции БС

Как видим, результат, т.е. накопленная через 6 лет сумма, составит всего лишь 59579,52 руб., что явно не соответствует запросу клиента.

Используя инструмент Подбор параметра, определим при каком проценте можно накопить нужную сумму при прежней сумме ежегодного взнос в 5000 руб.. Для этого установим курсор в ячейку B5, выберем команду Сервис\Подбор параметра, в окно Установить в ячейке введем адрес ячейки B5, содержащей формулу расчета, в окне Значение введем с клавиатуры величину 70000, а в окне Изменяя значение ячейки введем адрес ячейки B3. Выполним операцию нажатием клавиши <ОК> и получим искомое значение, равное 24,8%. (рис.7.42).

Рис. 7.42. Пример работы инструмента Подбор параметра

Сущность инструмента Таблица подстановки заключается в следующем. Пусть имеется формула, которая зависит от некоторых переменных. Задача состоит в определении результатов формулы при различных значениях этих переменных. Математическая сущность задачи состоит в табулировании функции. Эта задача является обратной к задаче подбора параметров.

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

Анализ расчетной формулы начинается с подготовки таблицы подстановки. Для этого:

1. Левая верхняя ячейка блока, отведенного под таблицу, остается пустой.

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

3. В верхнюю строку блока, начиная со второй ячейки, вводим ссылку на ячейку с анализируемыми формулами.

4. Выделяем таблицу подстановки и задаем команду Данные\Таблица подстановки.

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

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

6. После щелчка по кнопке <ОК> таблица будет заполнена значениями.

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

• в левую верхнюю ячейку блока, отведенного под таблицу, надо ввести ссылку на ячейку с анализируемой формулой;

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

• в верхнюю строку блока, начиная со второй ячейки, ввести значения другой варьируемой переменной;

• выделить таблицу подстановки и задать команду Данные\Таблица подстановки;

• в поле Подставлять значения по строкам в ввести ссылку на ячейку с переменной, значения для которой расположены в левом столбце таблицы подстановки;

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

После щелчка по кнопке <ОК> таблица будет заполнена значениями.