Создание двумерной таблицы данных
Анализ содержимого в одномерной таблице данных, как Вы увидели, показывают, что при закупке 5 акций фирма «Маква» не получит через 10 месяцев (300 дней) необходимую сумму. В связи с этим необходимо определить, какое количество акций необходимо купить, чтобы получить 800 тыс. долл. при их возврате эмитенту спустя 10 месяцев. Для этого необходимо построить двумерную таблицу данных, в которой определяется возвратная стоимость акций в зависимости от изменения значений двух аргументов: срока возврата и количества закупленных акций.
На рис. 9 представлена двумерная таблица данных. В ячейке, в которой записано слово «Формула», необходимо ввести формулу, устанавливающую зависимость одной функции от двух аргументов. В данном случае, это та же формула, которая применялась в одномерной таблице данных: формула расчета возвратной стоимости акций. Отличие формулы для одномерной таблицы состояло в том, в ней фиксировалось значение количества закупленных акций.
6.1.Создание шаблона двумерной таблицы данных.
Образец шаблона представлен на рис. 9. Последовательность значений срока возврата должна быть расположена в ячейках столбца под ячейкой со словом «Формула». Последовательность значений количества закупленных акций должна быть расположена в ячейках строки правее ячейки со словом «Формула». Формирование последовательности значений количества акций и срока возврата необходимо будет реализовать с помощью способа, который использовался при подготовке одномерной таблицы данных.
6.1.1.Создать шаблон таблицы, представленный на рис. 9. В ячейку со словом «Формула» копировать формулу «Возвратная стоимость акций» из соответствующей ячейки Таблицы 5.
6.1.2.Выберите самостоятельно первый элемент и разность для каждой арифметической прогрессии с учетом того, что размерность таблицы задана на рисунке. Разность арифметической прогрессии для количества закупленных акций не должна превышать числа 7.При этом примите во внимание, что существует одномерная таблица данных, и имеется возможность проверить правильность задания всех условий для Мастера подстановок и правильность полученных Мастером подстановок результатов. При этом примите также во внимание и то обстоятельство, что процесс решения задачи – итеративный, т.е. Вы можете не получить ответ после того, как Вы выбрали максимальные элементы последовательностей в первый раз.
6.1.3.Ниже двумерной таблицы данныхписьменно сформулируйте ответ на вопрос:
a)какое начальное значение и разность арифметической прогрессии для последовательности значений каждого из двухпараметров необходимо выбрать, чтобы полученные в этой таблице результаты можно было бы сопоставить с результатами в одномерной таблице данных?
6.2.Формировать содержимое в двумерной таблицеданных (выдать указание Мастеру подстановок).
| Формировать двумерную таблицу данных: Выделить поле таблицы► В строке меню кликнуть на пункте «Данные»► В разделе Ленты «Работа с данными» кликнуть на пункте «Анализ «что если»► <Диалоговое окно “Таблица данных”>► Проявить КВ в поле «Подставлять значения по столбцам» ► В этом поле установить адрес ячейки из Таблицы 5, в которой находится значение количества закупленных акций► Проявить КВ в поле «Подставлять значения по строкам» ► В этом поле установить адрес ячейки из Таблицы 5, в которой находится значение срока возврата► Ок► <Ячейки таблицы данных заполнились значениями>. |
| Двумерная таблица подстановки | ||||||||
| Возвратная стоимость акций: зависимость от количества акций и срока возврата | ||||||||
| Таблица 7 | ||||||||
| Количество закупленных акций | ||||||||
| Формула | ||||||||
| С | ||||||||
| р | ||||||||
| о | ||||||||
| к | ||||||||
| в | ||||||||
| о | ||||||||
| з | ||||||||
| в | ||||||||
| р | ||||||||
| а | ||||||||
| т | ||||||||
| а |
Рис. 9
6.3.Сравните полученные результаты с результатами в одномерной таблице данных. Равные результаты для количества закупленных акций – 5 свидетельствуют, что Вы правильно выполнили всю подготовительную работу до вызова Мастера подстановок. В противном случае проведите необходимые изменения.
6.4.Подбор значения арифметической прогрессии для последовательности значений количества закупленных акций.
Если при выбранной Вами разности арифметической прогрессии для значений количества закупленных акций в двумерной таблице данных не получен ответ на вопрос: сколько акций необходимо закупить, чтобы через 10 месяцев (300 дней) их возвратная стоимость превысила бы сумму 800 тысяч рублей, необходимо провести перерасчет. Перерасчет проводится без изменения начального значения количества закупленных акций (5) путем изменения значения разности арифметической прогрессии для последовательности «количество закупленных акций».
6.5.В двумерной таблице данных получить новые значения результатов с новыми значениями в последовательности (количество закупленных акций) путем изменения разности арифметической последовательности.
В двумерной таблице данных с имеющимися результатами получить новые результаты для новых исходных значений данных, расположенных в строке или в столбце, путем изменения разности арифметической последовательности:Изменить в соответствующей ячейке второе значение последовательности► Сформировать новые значения арифметической последовательности► <Excel самостоятельно проводит новый расчет и отображает новые результаты>.
6.6.Проведите несколько перерасчетов с различным значением арифметической последовательности для ряда значений количества закупленных акций с целью получить в качестве результата точное значение необходимой суммы – 800 тысяч долларов.При выполнении этого пункта задания можно увидеть, что при заданном начальном условии задача не имеет точного решения.Под ответом на предыдущий вопрос сформулируйте ответ на следующий вопрос:
b)При каких значениях срока возврата и количества закупленных акций Вы получили значение возвратной суммы (меньшее или большее), наиболее близкое к требуемому по абсолютной величине)? Какое значение возвратной суммы получено?
Подбор значения параметра
Мастер подстановок решает прямую задачу: по значениям аргумента определить значения функции. Вы увидели, что прямая задача может быть решена как для одного, так и для двух аргументов.Excelможет выполнять и обратную задачу: по заданному значению функции на основе формулы подобрать значение аргумента. Эта задача намного сложнее прямой задачи, поэтому в Excel она реализована применительно только к одному аргументу.
В составе Excel имеются специальные программы, расширяющие возможности Excel. Эти программы называются надстройками. Для решения рассматриваемой задачи имеетсянадстройка, называемая «Подбор параметра».
| A | B | C | D | E | F | G | H | I |
| Исходные данные для функционирования надстройки "Подбор параметра" | ||||||||
| Таблица 8 | ||||||||
| Имя ячейки | Название параметра | Значение или результат | Введенный в ячейку текст формулы | |||||
| Стоим_1_акц | Стоимость 1 акции (тыс. долл.) | |||||||
| Сумм_закуп | Сумма на закупку акций (тыс. долл.) | |||||||
| Количество | Количество закупленных акций (шт) | |||||||
| Закупочная стоимость акций (тыс. долл.) | ||||||||
| Ставка_наращ | Ставка наращивания стоимости акции в день(%) | 0,15% | ||||||
| Срок_возврата | Срок возврата акций эмитенту (дни) | |||||||
| Коэфф_наращ | Коэффициент наращивания стоимости 1 акции | 1,0000 | ||||||
| Возвратная стоимость акций (тыс. долл.) |
Рис. 10
Как правило, все обратные задачи решаются методом последовательных приближений (итеративно, путем подбора значений). По заданному значению функции и формуле делается грубая оценка начального значения аргумента. Проводится расчет по формуле и определяется первое значение функции. Это значение сравнивается с заданным значением. По результатам сравнения определяется новое значение аргумента, и проводится очередной расчет. Такие процедуры проводятся до тех пор, пока при прямом расчете не получится значение функции, наиболее близкое к заданному. Очевидно, что для выполнения такой процедуры Excel необходимо 2 ячейки: ячейка, в которую пользователь вводит заданное значение функции, и ячейка, в которуюExcel последовательно вводит значения аргумента.
Очевидно, что для решения этой задачи также необходимо иметь исходные значения данных и формулу. С этой целью на новом Рабочем листе должна будет создана представленная на рис. 10 таблица «Исходные данные для функционирования надстройки «Подбор параметра».
Как Вы видите, эта таблица по своей организации аналогична Таблице 5. Отличие состоит в содержимом. В этой таблице ячейкам со значениями и результатами также присвоены имена. Правда, эти имена отличаются по «формату» от имен ячеек в Таблице 5. В Таблице 8 именами ячеек являются сокращенные названия параметров.
В этом разделе должны быть решены 3 задачи:
a)Тестовая задача - проверка правильности выдачи задания надстройке «Подбор параметра» путем проведения пробного расчета с известным значением функции и аргумента;
b)Расчет для 5 акций - определение срока возврата 5 акций, которые фирма Маква имеет возможность купить, чтобы возвратная стоимость этих акций равнялась необходимой сумме – 800 тыс. долл.;
c)Проведение нескольких расчетов (проведение итераций), чтобы путем подбора определить, какое количество акций необходимо купить (какую необходимо иметь сумму на закупку акций), чтобы по истечении срока, минимально близкого к необходимому (300 дней), их возвратная стоимость составляла бы требуемую сумму (800 тыс. долл.).
7.1.Ввести после листа «Таблица_подстан» новый лист и присвоить ему имя «Подбор_парам». В первой строке этого листа написать заголовок: «Подбор значения параметра» и создать представленную на рис. 10 Таблицу 8.
7.2.Присвоение ячейкам собственных имен и ввод формул.
7.2.1.В Таблице 8присвоить ячейкам столбца «Значение или результат» имена, представленные в первом столбце этой таблицы.
7.2.2.Убедиться в том, что Вы присвоили необходимым ячейкам необходимые имена. С этой целью в поле «Имя» (при наведении на это поле графического указателяпоявляется всплывающая подсказка в виде имени этого поля – «имя») строки формул открыть выпадающее меню, выбирать последовательно по алфавиту имена ячеек и определять, какая ячейка при этом становится текущей.
7.2.3.Ввести в таблицу исходные значения параметров и формулы с использованием имен ячеек. В ячейки соответствующего столбца ввести тексты формул. Проверить правильность ввода формул, изменяя значение стоимости 1 акции и наблюдая результаты.
7.2.4.Сравнить записи формул в Таблице 5 и Таблице 8. Устно привести отрицательные и положительные стороны каждой из записей.
7.3.Проведение с помощью надстройки «Подбор параметра» пробного расчета с известным значением функции и аргумента.
На рис. 11 представлено окно «Подбор параметра», которое выдает надстройка «Подбор параметра»после её запуска. 2 ячейки со значениямифункции и аргумента, которые должны быть введены в поля этого окна, должны принадлежать Таблице 8. В этом диалоговом окне необходимо задать 3 параметра. Первый параметр:значение функции (поле «Значение»), которое интересует пользователя. Второй параметр связан с первым:адрес ячейки, в которую надстройка «Подбор параметра» запишет заданное пользователем значение функции (поле «Установить в ячейке»).Третий параметр: адрес ячейки, в которой надстройка «Подбор параметра» будет хранить подбираемые значения аргумента (поле «Изменяя значения ячейки»).Эти ячейки существуют в Таблице 8.