Создание одномерной таблицы данных

В соответствии с п.В.«Плана выполнения работы» (см. раздел «Постановка задачи») необходимо получить в табличной форме последовательности значений двух функций: коэффициента наращивания и возвратной стоимости акций. Аргумент у этих функций общий – срок возврата акций.

В Excel табулирование функций по известным формулам реализуется с помощью программного модуля, который называется «Таблица данных» (В MO 2003 этот программный модуль назывался «Таблица подстановок»).В данной работе для удобства описания назовём этот модуль «Мастер подстановок». На рис. 6 представлена вся информация для получения одномерной таблицы данных для функцииF(x,y,z). Имеется математическая запись формулы.Имеется также запись формулы, по которой в Excel будут вычисляться значения функции. Мы привыкли считать аргументами параметры, обозначаемыми буквами “x”, “y”, “z”.В данном примере значения параметров y и z зафиксированы, и функция при этом превращается в функцию одной переменной. Функция может быть функцией двух переменных, если зафиксировать значение только одного параметра – z.

 

Рис. 6

 

Чтобы был понятен смысл содержимого каждой ячейки, слева от ячейки со значением параметра указывается его имя: a, b, c, x, y, z. Ячейка для значений аргумента может быть пустой, т.к. в нее Мастер подстановок последовательно во времени подставляет значения аргумента из таблицы данных. На рисунке представлен такжетекст формулы, записанный по правилам Excel, с указанием ячейки таблицы данных, в которой она должна находиться. Дополнительно на этом же рисунке отображено диалоговое окно с именем «Таблица данных», в поле которой с именем «Подставлять значения по строкам в» указан адрес ячейки ввода – ячейки, в которую Мастер подстановок вводит значения аргумента.

Чтобы уяснить, как можно применить Мастер подстановок для решения возникшей у руководства фирмы Маква задачи, сформулируем её в математическом плане.

Определим параметры, участвующие в задаче.

 

Математическое обозначение параметра	Имя параметра
p	Стоимость 1 акции
s	Сумма на закупку акций
g	Ставка наращивания стоимости 1 акции
n	Количество закупленных акций
t	Срок возврата акций
k	Коэффициент наращивания стоимости 1 акции

 

 

В этих обозначениях коэффициент наращивания стоимости 1 акции равен

, (2)

возвратная стоимость акций

или (3)

 

На рис.8 представлена заготовка одномерной таблицы данных для выполнения настоящего задания - вычисления по одному аргументу значений двух функций. На обоих рисунках – рис.6 и рис.8 - таблица называется одномерной, поскольку в ней используется 1 аргумент. В примере аргументом является параметр х, в таблице на рис.8 аргументом является параметрt - срок возврата акций эмитенту, а значения остальных параметров зафиксированы.

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

 

Особенностью таблицы данных является необходимость наличия в определенном месте этой таблицы ячеек с формулами для вычисления исследуемых функций (на рис. 6 формула записана в ячейку B10; на рис. 8 формулы должны находиться в ячейках, в полях которых записано слово «Формула»). Собственно таблицей, которую использует Мастер подстановок, является блок ячеек, поля которых на обоих рисунках залиты серым фоном. В ячейках с названиями столбцов содержатся пояснительные надписи исключительно для пользователя.

Процедура заполнения Мастером подстановок ячеек столбца со значениями функции состоит из последовательности шагов. На каждом шаге Мастер подстановок выбирает из столбца со значениями аргумента очередное значение аргумента. Выбранное значение аргумента Мастер подстановок копирует в ячейку(для данного примера – это ячейкаB4), адрес которой записан в формуле. Эта ячейка называется ячейкой ввода.Т.е. ячейкой ввода называется ячейка, которую пользователь зарезервировал для хранения значений аргумента и адрес которой указал в специальном диалоговом окне с именем «Таблица данных».Вэту ячейку Мастер подстановок на каждом шаге работы записывает очередное значение аргумента.После копирования Мастер подстановок проводит вычисление по формуле и полученное значение функции вводит в соответствующую ячейку столбца таблицы данных со значениями функции.

Иными словами. Собственно формула, по которой вычисляются значения функции, может содержать несколько параметров. Например, F(x, y, z) = ax³+by+cz². При использовании одномерной таблицы данных необходимо из 3-х параметров выбрать в качестве аргумента только один, например, х. Тогда для каждого из оставшихся параметров – y и z необходимо установить конкретное значение.

В диалоговом окне «Таблица данных» строка для ввода адреса ячейки ввода определяется расположением таблицы данных. Посмотрите на диалоговое окно с именем «Таблица данных». В этом окне для Мастера подстановок представлена информация двух видов. Информация первого вида: адрес ячейки ввода (ячейки со значениями аргумента) – адрес той ячейки, в которую Мастер подстановок в процессе формирования таблицы данных будет последовательно записывать значения аргумента. Информация второго вида: как на Рабочем листе Excel расположены ячейки, в которые Мастер подстановок должен записывать значения функции (в столбце или в строке). Расположение ячеек в столбце в терминологии разработчиков Excel означает, что они расположены по строкам, и Мастер подстановок должен записывать значения функции по строкам. И наоборот.

Данный раздел задания будет выполняться на собственном рабочем листе Книги Excel.

В соответствии с этим комментарием выполняются дальнейшие действия.

5.1. Переименовать лист с именем «Лист 3», присвоив ему имя «Таблицы_данных».

 

A	B	C	D	E	F	G	H	I
Исходные данные для функционирования Мастера подстановок
							Таблица 5
Имя ячейки	Название параметра	Значение или результат	Введенный в ячейку текст формулы
p	Стоимость 1 акции (тыс. долл.)
s	Сумма на закупку акций (тыс. долл.)
n	Количество закупленных акций (шт)
	Закупочная стоимость акций (тыс. долл.)
g	Ставка наращивания стоимости акции в день(%)
t	Срок возврата акций эмитенту (дни)
k	Коэффициент наращивания стоимости 1 акции
	Возвратная стоимость акций (тыс. долл.)

 

Рис. 7

 

На листе «Таблицы_данных» записать в строке 1 заголовок – «Создание одномерной и двумерной таблицы данных».

Пропустив 1 пустую строку, создать вместе с названием представленную на рис. 7 таблицутак, чтобы левыйстолбец этой таблицы располагался в столбце А. Присвоить этой таблице номер «Таблица 5».

5.2. Присвоение ячейкам собственных имен

При записи формул в ячейки Таблицы 5необходимо будет использовать вместо адресов ячеек их имена. В качестве имен ячеек будут использоваться буквенные обозначения параметров, которые применены в формулах (2) и (3).

5.2.1. Присвоить ячейкам столбца «Значение» Таблицы 5 собственные имена, которые представлены в столбце «Имя ячейки» (Рис. 7).

 

Присвоить имя ячейке:Вызвать контекстное меню для целевой (необходимой) ячейки► В полученном меню кликнуть на пункте «Присвоить имя»► <ДО «Создании имени»>► В поле «Имя» ввести необходимое имя ячейки► Ок► В поле «Адрес (имя) активной ячейки» нажать на кнопку вызова списка имён►<В списке имён ячеек имеется созданное имябез указания на адрес ячейки>.

Найти соответствие между именем ячейки и её адресом (Активной может быть любая ячейка):В строке меню кликнуть на пункт «Формулы» ►В разделе Ленты «Определённые имена»кликнуть на пиктограмме «Диспетчер имён» ►<ВДО «Диспетчер имён» представлен список имён ячеек с их адресами>.

Изменить (удалить) имя ячейки (Активной может быть любая ячейка):В строке меню кликнуть на пункт «Формулы» ►В разделе Ленты «Определённые имена»кликнуть на пиктограмме «Диспетчер имён» ►<В ДО «Диспетчер имён» представлен список имён ячеек с их адресами>►Кликнуть на строке с необходимым именем ячейки►Кликнуть на клавише «Изменить»(Удалить)► Ок

 

5.2.2. Убедиться в том, что Вы присвоили необходимым ячейкам необходимые имена. С этой целью в поле «Имя» строки формул открыть выпадающее меню, выбирать последовательно по алфавиту имена ячеек и определять, какая ячейка при этом становится текущей.

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

5.2.4. Сравнить записи формул в Таблице 2 и в Таблице 5. Устно привести отрицательные и положительные стороны каждой из записей.

 

 

Одномерная таблица данных
		Таблица 6
Срок возврата акций (дни)	Коэффиц. наращив.	Возвратная стоимость акций (тыс. долл)
	Формула	Формула

 

Рис. 8

 

5.3. Ниже Таблицы 5, пропустив 1 строку, создать заготовку таблицы данных по образцу, представленному на Рис. 8, и присвоить ей номер "Таблица 6".

В левый столбец Таблицы 6 необходимо ввести значения аргумента как арифметическую последовательность. При создании таблицы необходимо принять во внимание следующее обстоятельство.Окончательный расчет с производителем должен быть выполнен через 10 месяцев – 300 дней. Выберите самостоятельно первый элемент и разность арифметической прогрессии так, чтобы количество строк в таблице было в диапазоне 10 - 15.

 

Заполнить последовательность ячеек числами арифметической последовательности: В первую ячейку ввести первое число последовательности► Во вторую ячейку ввести второе число последовательности► Выделить обе ячейки► УМ – на маркер автозаполнения(квадратик в правом нижнем углу второй выделенной ячейки)► <УМ – в форме креста>► Тащить крест по последовательным ячейкам► <При выборе очередной ячейки появляется подсказка с записываемым значением очередного элемента последовательности>

 

5.4. Копировать из ячеек-источников Таблицы 52 формулы для вычисления коэффициента наращивания и возвратной стоимости акций в ячейки-приёмникиТаблицы 6 «Одномерная таблицаданных», отмеченные словом «Формула».

5.5. Сравнить формулы в ячейках – источниках с формулами в ячейках – приемниках. Анализировать записи формул в Таблице 2. Под таблицей 6сформулировать ответы на следующие вопросы:

a) Что можно сказать про записи формул в ячейках – приемниках (в Таблице 6) после копирования, если бы в ячейках – источниках (в Таблице 5) использовались адреса ячеек (как в Таблице 2), а не их имена (Для ответа на вопрос можно провести эксперимент и копировать формулы из ячеек Таблицы 2)? В чём причина отличия формул в ячейках-приёмниках Таблицы 6 при копировании формул из Таблицы 2 и Таблицы 5.

b) Какие существуют способы записи адресов операндов, при которых в формулах – копиях (в ячейках – приемниках) после выполнения операции «копирование» не изменяются адреса операндов?

5.6. Формировать содержимое в одномерной таблицеданных (выдать указание Мастеру подстановок).

 

Формировать содержимое в одномерной таблицеданных: Выделить поле таблицы (поле с полями ячеек, «закрашенных» серым фоном)►Кликнуть в строке меню на пункте «Данные»► В разделе Ленты «Работа с данными» кликнуть на пункте «Анализ «что если»►В появившемся выпадающем меню кликнуть на пункте «Таблица данных»►<Диалоговое окно “Таблица данных” (см. Рис. 6)>► Проявить КВ в поленеобходимой строки► Установить в этом поле адрес ячейки ввода (ячейки со значениями аргумента)► Ок► <Ячейки таблицы данных заполнились значениями>.

 

5.7. Наблюдать полученные Мастером подстановок значения параметров. Под таблицей 6, продолжая нумерацию, письменно ответить на следующие вопросы:

c) какой параметр является аргументом для рассмотренной таблицы данных?

d) в какую строкунеобходимо записать адрес ячейки ввода (адрес ячейки со значениями аргумента) в диалоговом окне «Таблица данных» (укажите имя строки и приведите обоснование своего решения)?

e) какова должна быть точность отображаемых в ячейках таблицы значений коэффициента наращивания и возвратной стоимости всех акций?

f) какие существуют 2способа измененияточности отображаемых в ячейках «Таблицы данных»значений параметров?

g) Какой способ изменения точности отображаемых в ячейках таблицы значений параметровВы выбрали и по какой причине?

5.8. Установите требуемую точность отображения чисел в таблице данных.