Задания. Задание 1. Построить таблицу, состоящую из следующих граф (столбцов): День; Курс продаж; Продано в день; Продано всего; Курс покупки; Куплено в день; Куплено

Задание 1. Построить таблицу, состоящую из следующих граф (столбцов): День; Курс продаж; Продано в день; Продано всего; Курс покупки; Куплено в день; Куплено всего; Сумма на счете; Доход в день; Доход всего: Расход в день; Расход всего.

Исходные данные для проведения расчета:

– число жителей в городе:

– коэффициент ажиотажа:

– ежедневные расходы, руб.:

– время между покупкой и продажей акции, дней: ;

- начальный капитал, руб.:

- число купивших акции в первый день:

Исходные данные оформить отдельной таблицей и использовать их в рабочей таблице с абсолютной адресацией. Сдвиг волны «покупка-продажа» задать программно с помощью функций Excel из категории Ссылки и массивы, например, СМЕЩ, используя ее как аргумент функцииЕСЛИ.

Функция СМЕЩ возвращает ссылку на ячейку или диапазон ячеек, отстоящие от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть как отдельной ячейкой, так и диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов.

Синтаксис функции:

СМЕЩ(ссылка; смещ_по_стр; смещ_по_столбц; выс; шир)

Аргументы:

ссылка – это ссылка на ячейку или на диапазон смежных ячеек, от которых вычисляется смещение, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!;

смещ_по_стр – это количество строк, которые нужно отсчитать вверх или вниз, так чтобы верхняя левая ячейка результата ссылалась на это место. Если значение положительное, то отсчитывается ниже начальной ссылки, если отрицательное, то выше начальной ссылки;

смещ_по_столбц – это количество столбцов, которые нужно отсчитать влево или вправо, так чтобы верхняя левая ячейка результата ссылалась на это место. Если значение положительное, то отсчет ведется вправо от начальной ссылки, если отрицательное, то влево от начальной ссылки;

выс – это высота (число строк) возвращаемой ссылки. Высота должна быть положительным числом;

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

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

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

Задание 2. Построить график изменения суммы денег на счете , взяв реальный диапазон дней. Реальным назовем диапазон, в котором сумма денег на счете неотрицательна.

На отдельном поле построить графики изменения суммарных доходов и расходов организаторов пирамиды. Проанализировать графики, рассмотрев характер их поведения.

Задание 3. Определить максимальную сумму на счете организаторов пирамиды и день ее достижения используя функции Excel МАКС и ПОИСКПОЗ. Найти максимальную прибыль организаторов пирамиды. Найти день нулевой суммы на счете.

Функция ПОИСКПОЗ возвращает относительное положение (позицию) элемента массива, который соответствует заданному значению указанным образом[3].

Синтаксис функции:

ПОИСКПОЗ(иск_знач; интервал; тип_сопост)

Аргументы:

иск_знач – это значение, которое сопоставляется со значениями в аргументе интервал. Может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение;

интервал – непрерывный интервал ячеек, возможно содержащих искомые значения. Интервал может быть массивом или ссылкой на массив;

тип_сопост – число –1, 0 или 1. Если тип_сопост равен 1 или опущен, то функция находит наибольшее значение, которое равно или меньше, чем иск_знач. Интервал должен быть упорядочен по возрастанию. Если тип_сопост равен 0, то функция находит первое значение, которое в точности равно аргументу иск_знач, при этом значения в Интервале могут быть не упорядочены. Если тип_сопост равен –1, то функция находит наименьшее значение, которое равно и больше чем иск_знач. Интервал должен быть упорядочен по убыванию.

Использование этой функции для решения поставленной задачи облегчается тем, что номер строки в таблице в точности соответствует дню.

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

Для этого необходимо предварительно найти с помощью функции МИН локальный минимум в столбце «Сумма на счете» на начальном участке строительства пирамиды (в диапазоне дней от до , а затем, используя сервисное средство Excel Подбор параметра (Сервис | Подбор параметра – для Excel 2003, Данные | Работа с данными | Анализ «что-если» | Подбор параметра – для Excel 2007), подобрать такое значение начального капитала, которое обеспечило бы неотрицательное значение этого локального минимума (самый рисковый вариант – нулевое значение (рис.4.1), менее рисковый – некоторое положительное значение равное, например, ежедневным расходам ).

Указание. Для определения адреса ячейки правого конца диапазона дней (соответствующего дню достижения максимума ) следует использовать функцию ИНДЕКС (в синтаксической форме массива) из категории Ссылки и массивы.

Рис. 4.1. Диалоговое окно Подбора параметров

Эта функция возвращает значение элемента таблицы или массива,, заданного номером строки и номером столбца.

Синтаксис функции:

ИНДЕКС(массив; номер_строки; номер_столбца)

Массив – диапазон ячеек или массив констант.

Номер_строки – номер строки в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.

Номер_столбца – номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.

Если массив содержит только одну строку или один столбец, аргумент «номер_строки» (или, соответственно, «номер_столбца») не является обязательным.

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

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

Результаты исследований оформить на новом листе в виде таблицы параметрического исследования модели (табл. 4.2). Сделать выводы о том, какие параметры сильнее влияют на развитие пирамиды.

Таблица 4.2 – Параметрическое исследование модели

Изменяемый параметр	Увеличение параметра	Уменьшение параметра
Значение параметра	День	Сумма на счете	Значение параметра	День	Сумма на счете

Для представления результатов параметрических исследований удобно использовать Диспетчер сценариев (Сервис | Сценарии | Диспетчер сценариев - для Excel 2003, Данные | Работа с данными | Анализ «что-если» | Диспетчер сценариев – для Excel 2007).

Диспетчер сценариев используется для проведения многовариантных расчетов в Excel. Создание первого сценария производится с помощью команды Добавить после того, как на листе получено решение задачи для одного (опорного) варианта (рис. 4.2).

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

После нажатия кнопки ОК в диалоговом окне Значения ячеек сценария вводятся значения параметров для первого сценария и с помощью диалогового окна Диспетчер сценариев (рис. 4.3, команда Добавить) добавляется необходимое число сценариев.

Рис. 4.2. Диспетчер сценариев: создание первого сценария

С помощью кнопки Отчет открывается диалоговое окно Отчет по сценарию (рис.4.4), где определяется тип отчета – Структура или Сводная таблица (рекомендуется Структура) и задаются ячейки листа, где вычисляются результаты – максимум суммы на счете и день ее достижения.

Таким образом, на листе Excel будет находиться только одно (опорное) решение; все остальные варианты — в отчете.

Рис. 4.3. Диспетчер сценариев: главное меню

Рис. 4.4. Диспетчер сценариев: формирование отчета