Задача 2 – подстановка по двум параметрам
Задача 2 выполняется на листе 5.
Теперь создадим таблицу подстановки с двумя переменными – процентной ставкой и сроком вклада. В результате должны быть получены суммы выплат при различных процентных ставках и сроках.
Процентные ставки берём чуть меньше и чуть больше начальной. Обратимся к выводу предыдущей работы:
«Для получения в конце срока вклада желаемой суммы в размере 500 000 рублей нужно:
2)увеличить срок вклада с 1,5 до 9,61 лет, т.е. положить 200 000 руб. на 9,61 года под 10% годовых
3)найти банк с процентной ставкой 84,2% годовых вместо 10%, но такой процентной ставки в банках нет, поэтому данный вариант не может быть рассмотрен».
Так как процентная ставка получилась нереальная, будем рассматривать разные варианты срока, ближе к полученному (9,61 лет). Зададим следующие значения срока: 8,5; 8,75; 9; 9,25; 9,5; 9,75 лет.
Чтобы создать таблицу подстановки с двумя переменными, немного изменим таблицу – в ячейку D3 введём формулу:
=((1+В3)^В2)*В4
А в ячейки Е2 – Н2 различные сроки вклада.
Примечание: В таблице подстановки с двумя переменными формула для определения значений должна находиться на пересечении списков значений (в нашем примере – в ячейке D3).
Рисунок 17 – Рабочий лист перед применением функции создания таблиц подстановки
15
Рисунок 18 – Рабочий лист в режиме проверки формул
Выделите диапазон ячеек D1 – Н16 и активируйте команду Таблица подстановки из меню Данные. Как и в части 1 для значений процентных ставок будет отведена ячейка В3, адрес которой укажите в поле
Подставлять значения по строкам. В поле Подставлять значения по столбцам введите адрес ячейки В2, в которой указывается срок вклада.
Рисунок 19 – Диалоговое окно Таблица подстановки
После нажатия кнопки ОК в диалоговом окне Таблица подстановки на рабочем листе появится таблица подстановки с результатами.
Рисунок 20 – Составленная таблица подстановки для двух параметров
16
Цветом выделяем значения полученной суммы, близкие к желаемой (500 000 рублей).
Делаем вывод: чтобы получить в конце срока вклада сумму 500 000 рублей нужно положить 200 000 рублей:
-на 8,5 лет под 11,5% годовых;
-на 8,75 лет под 11% годовых;
-на 9 лет под 10,75% годовых;
-на 9,25 лет под 10 % годовых;
-на 9,5 лет под 9,75% годовых;
-на 9,75 лет под 10% годовых.
Диапазон ячеек Е1 – Н16 будет содержать формулу:
{=ТАБЛИЦА(В2;В3)}
Рисунок 21 – Составленная таблица подстановки для двух параметров в режиме проверки формул
В этом случае в формуле должны быть указаны два аргумента. Первый аргумент представляет собой ссылку на ячейку, в которую подставляются значения первого параметра, расположенные в строке. Вторым аргументом является адрес ячейки, в которую подставляются значения второго параметра (из столбца).
17
Задача 3 – подстановка по двум параметрам
Задача 3 выполняется на листе 6. Теперь создадим таблицу подстановки с двумя переменными – суммой вклада и сроком вклада. Исходными данными берём из вывода предыдущей работы. Например, мы хотим в конце срока получить 500 000 руб.
Изначальная сумма вклада 200 000 руб. Но с помощью функции Подбор параметра в предыдущей работе мы получили 433 392 руб. Следовательно, в столбец D заносим значения от 200 000 до 440 000 руб. с равномерным шагом.
Изначальный срок вклада был 1,5 года. Но с помощью функции Подбор параметра в предыдущей работе он изменился и стал 9,6 лет. Следовательно, в первую строку вносим года начиная от 1,5 до 9 с равномерным шагом.
Рисунок 22 – Исходные данные для задачи 3
Диапазон ячеек Е1 – Н16 будет содержать формулу
{=ТАБЛИЦА(В2;В1)}
18
Рисунок 23 – Диалоговое окно Таблица подстановки
Врезультате должны быть получены суммы выплат при различных суммах вклада и сроках. В составленной таблице подстановки выделяем цветом суммы, близкие к желаемой сумме выплат.
Ввыводе указываются варианты сочетаний размеров и сроков вклада для получения желаемой суммы выплат в размере 500 000 рублей (рисунок 21):
1) 210 000 руб. на 9 лет;
2) 280 000 руб. на 6 лет
3) 330 000 руб. на 4,5 года
4) 380 000 руб. на 3 года
5) 430 000 руб. на 1,5 года
Рисунок 24 – Результат решения задачи 3
19