Реферат: Компьютерные технологии MS EXEL

КОНТРОЛЬНАЯРАБОТА ПО ИНФОРМАТИКЕ

ЦЕЛЬ РАБОТЫ

Закрепление знаний ипрактических навыков работы на персональном компьютере с использованием современныхкомпьютерных технологий MS EXEL.

ЗАДАНИЯ ККОНТРОЛЬНОЙ РАБОТЕ

Контрольная работасостоит из 5 заданий, решение которых должно быть представлено в виде электронноговарианта книги MS Excel и пояснительной записки,составленной в MS Word.

Задание 1. Табулировние и построение графиковфункций.

Задание 2. Вычисление суммы функциональногоряда.

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

Задание 4. Финансовый анализ в Excel на примерах использования: Подборпараметра и Диспетчера сценариев.

Задание 5. Применение возможностей Excel на примерах решения практическихзадач.

Задание 1

Тема:Табулирование и построение графиков функций

Постановка задачи. Построить графики двух функций Y=cos2x и Z=sin2x “по точкам” на отрезке -2π≤X≤2π c шагом />, где n-число разбиения отрезка.

Решение

1. Строимматематическую модель и определяем исходные и результирующие данные.

Исходные данные: начало иконец отрезка, число разбиений отрезка.

Результаты: столбец — аргумента X и два столбца функцийY и Z, которые вычисляются в каждой точке отрезка с шагом H. В нашем случае шаг вычисляется поформулеH=4π/n, где n=20.

2. Технологиясоздания рабочего листа.

· Переименуемрабочий лист в “Табулирование”. Для этого дважды щелкнем мышкой по вкладкетекущего рабочего листа и на вкладке листа введем имя “Табулирование”.

· Введем исходныеданные с пояснениями и расчетные формулы для вычисления X,Y,Z,H в следующей последовательности:

Ø Ввод в ячейку F2 числа разбиений=20;

Ø Вычисление шага H: E2=4*ПИ()/$F$2;

Ø Формулавычисления начального значения X: B2=-2*ПИ();

Ø Удобно задаватьописание X как функцию, в которой последующеезначение X определяется через предыдущее,X=X+H.Тогда, сменивчисло разбиения n, автоматическипроизойдет пересчет по всем формулам на рабочем листе;

Ø Вычислениепоследующего значения Xопределяется по формуле B3=B2+$E$2;

Ø Формулы длявычисления начальных значений функций Y и Z определяются по формулам: C2=(cos(B2))^2; D2=sin(2*B2).

· Далее формулы X,Y, Z копируем вниз до последнего значения X.

Полученные результатыприведены на рабочем листе ”Табулирование” (рис.1), который представлен врежиме отображения значений. Внимательно посмотрите, правильно ли набраныформулы. Для этого представим рабочий лист в режиме отображения формул, которыйустанавливается нажатием клавиш Ctrl+ `(`этот значок на клавише, расположенной в левом верхнем углу клавиатуры, где ~(тильда)), или командой Сервис/Параметры/Вкладка Вид/Параметры окна-Формула.Проанализировав формулы, выполнив ту же последовательность команд, вернемсяв режим отображений значений.

· Построениеграфиков по точкам.

Графики (диаграммы) можносоздать с помощью команды Вставка/ Диаграмма или нажатием кнопки Мастердиаграмм на стандартной панели инструментов. Последовательность действийсоздания диаграммы:

Ø Выделите нарабочем листе данные, которые нужно отобразить- диапазон B1:D22;

Ø Нажмите на кнопкуМастер диаграмм;

Ø Выберите типдиаграммы – Точечный и нажмите на кнопку Далее;

Ø Выберитерасположение данных-По строкам или По столбцам. Выберите Постолбцам и нажмите на кнопку Далее;

Ø Насоответствующих вкладках задайте параметры: заголовки и надписи данных инажмите на кнопку Далее;

Ø Укажите, гдедолжна находиться новая диаграмма, — На отдельном листе или уже Существующем.Выберите – На существующем листе и нажмите кнопку Готово.

На текущем рабочем листепоявится Диаграмма-график. Как и любой объект, его можно выделить иперетащить с помощью мыши на новое место листа (рис.3).

Замечание.

Для построения одногографика Z=F(X) нужно Мастерудиаграмм задать несмежные области листа B2:B22 и D2:D22, которые можно выделить при нажатой клавише Ctrl.

Рис 1.

Рис.2

Рис. 3

Варианты заданий

№

Уравнение y=f(x)

Уравнение z=f(x)

Отрезок, содержащий

корень

Шаг

/>/>

[2;3]

0,1

[0;2]

0,2

[0,4;1]

0,05

[0,,85]

0,05

[1;2]

0,1

[0;0,8]

0,05

[;1]

0,1

[2;4]

0,2

[1;2]

0,1

[;2]

0,1

[0.1;1]

0,1

[1; 3]

0,2

[1,2; 2]

0,08

ex+lnx-10x=н

[3; 4]

0,1

[1; 2]

0,1

1-x+sinx-ln(1+x)=y

[0; 1,5]

0,15

3x-14+ex-e-x=y

[1; 3]

0,2

[0; 1]

0,1

x+cos(x0,52+2)=y

[0,5; 1]

0,05

3ln2x+6lnx-5=y

[1; 3]

0,2

sinx2+cosx2-10x=y

[0; 1]

0,1

x2 – ln(1+x) – 3=y

[2; 3]

0,1

2x*sinx – cosx=y

[0,4; 1]

0,05

[-1; 0]

0,1

lnx – x + 1,8=y

[2; 3]

0,1

[0,2; 1]

0,05

[1; 2]

0,1

[1; 2]

0,1

[0; 1]

0,1

0,6*3x-2,3*x – 3=y

[2; 3]

0,1

Задание 3

Нахождениекорней нелинейных (трансцендентных) уравнений, используя инструмент «Подборпараметра»

Пример. Найти корни уравнения />

Из рис.1 видно, что функцияменяет знак между значениями X диапазона[3,2;3,3]. Значит, в этом диапазоне существует корень. В качестве начальногоприближения Xкорень берем ячейку F3=3, значение функции Y задаем в ячейке F4=3*F3-4*ln(F3)-5.

Теперь выберем команду Сервис,Подбор параметра и заполним диалоговое окно Подбор параметра. Заполнениеокна смотрите на рис.1.

После нажатия кнопки OK средство Подбора параметровнаходит приближенное значение корня, которое помещает в ячейку F3, а корень-результат в ячейку F4(смотрите рис.2).

Рис.1

Рис.2

Варианты к заданию 3

№

Уравнения

Отрезок, содержащий

корень

Приближенное значение

1-x+sinx-ln(1+x)=0

[0; 1,5]

1,1474

3x-14+ex-e-x=0

[1; 3]

2,0692

[0; 1]

0,5768

x+cos(x0,52+2)=0

[0,5; 1]

0,9892

3ln2x+6lnx-5=0

[1; 3]

1,8832

sinx2+cosx2-10x=0

[0; 1]

0,1010

x2 — ln(1+x) — 3=0

[2; 3]

2,0267

2x*sinx — cosx=0

[0,4; 1]

0,6533

[-1; 0]

— 0,2877

lnx — x + 1,8=0

[2; 3]

2,8459

[0,2; 1]

0,5472

[1; 2]

1,0769

[1; 2]

1,2388

[0; 1]

0,4538

0,6*3x-2,3*x — 3=0

[2; 3]

2,4200

[2;3]

2,2985

[0;2]

1,0001

[0,4;1]

0,7376

[0;0,85]

0,2624

[1;2]

1,1183

[0;0,8]

0,3333

[;1]

0,5629

[2;4]

3,2300

[1;2]

1,8756

[;1]

0,7672

[;1]

0,8814

[1; 3]

1,3749

[1,2; 2]

1,3077

ex+lnx-10x=0

[3; 4]

3,5265

[1; 2]

1,0804

Задание 4. Финансовый анализ в Excel

Excelпредоставляет большой спектр функций финансового анализа: от нахождения платыпо процентам, амортизации оборудования, регулярных выплат по займу до оценкиэффективности капиталовложений. Рассмотрим функции финансового анализа Excel на большом количестве конкретныхпримеров.

Пример1

Постановказадачи. Вычислитьфинансовую функцию ППЛАТ(ПЛТ) расчета 30-летней ипотечной ссуды со ставкой 8%годовых при начальном взносе 20% от цены покупки и ежемесячной (ежегодной)выплате.

ФункцияППЛАТ(ПЛТ) вычисляет величину постоянной периодической выплаты ренты (кредита)при постоянной процентной ставке.

Синтаксис:

=ППЛАТ(ПЛТ) (ставка; кпер; ос; остаток; тип)

Аргументы:

ставка Процентнаяставка за период;

кпер количествопериодов выплат;

ос Общаясумма кредита, которую составят будущие платежи;

остаток Остатокили баланс наличности, который нужно достичь после последней выплаты. Еслиостаток опущен, то он полагается равным 0;

тип Число0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 илиопущен, то оплата производится в конце периода, если 1 — то в начале периода.

Оченьважно быть последовательным в выборе единиц измерения для задания аргументов ставкаи кпер. Например, если вы делаете ежемесячные выплаты почетырехгодичному займу из расчета 12% годовых, то для задания аргумента ставкаиспользуйте 12%/12, а для задания аргумента кпер — 4*12. Если вы делаетеежегодные платежи по тому же займу, то для задания аргумента ставкаиспользуйте 12%, а для задания аргумента кпер — 4.

Замечание.Обратите внимание,что в функциях, связанных с интервалами выплат, выплачиваемые вами деньги,такие как депозит на накопление, представляются отрицательным числом, а деньги,которые вы получаете, такие как чеки на дивиденды, представляются положительнымчислом. Например, депозит в банк на сумму 1000 руб. представляется аргументом-1000, если вы вкладчик, и аргументом 1000, если вы — представитель банка, т.е.что отдается банку-аргумент с минусом, при получении от банка-аргумент сплюсом.

Рабочийлист (рис.1) приведен в режиме отображения значений, а на рис.2-в режимеформул.

Рис.1.Расчет ипотечнойссуды

Рис. 2.Формулы для расчета ипотечной ссуды

Пример2 расчетаэффективности неравномерных капиталовложений с помощью функций НПЗ иинструмента Подбор параметра

Постановказадачи. Вас просятдать в долг 10000 руб. и обещают вернуть через год 2000руб., через два года—4000руб., через три года— 7000 руб. Определить при какой годовой процентнойставке эта сделка выгодна? Для решения задачи будем использовать финансовуюфункцию НПЗ(ЧПС).

Функция НПЗ(ЧПС)возвращает чистый текущий объем вклада, вычисляемый на основе рядапоследовательных поступлений наличных.

Синтаксис:НПЗ(ЧПС) (ставка; 1-е значение; 2-е значение; ...)

Аргументы:ставка Процентная ставка за период;

1-е значение, От1 до 29 аргументов, представляющих расходы и доходы;

2-е значение 1-езначение, 2-е значение,… должны быть равномерно распределены по времени иосуществляться в конце каждого периода. НПЗ использует порядок аргументов 1-езначение, 2-е значение,… для определения порядка поступлений и платежей.

Нарабочем листе (рис.3) введем исходные данные с пояснениями и расчетные формулыв следующей последовательности:

· Ввод текста изначений в диапазон A2:B6;

В ячейкуC6 введем формулу

C6==ЕСЛИ(Вб=1;«год»; ЕСЛИ(И(В6>=2; Вб<=4);«года»;«лет»))

· Первоначально вячейку В7 введем произвольный процент, например 3%.

· В ячейку B8 введем формулу вычисления текущеговклада B8=НПЗ(ЧПС)(B7;B3;B5).

Вводисходных данных завершен.

Рис.3.Расчет годовойпроцентной ставки

Далеевыполняем командуСервис, Подбор параметра и заполняем открывшеесядиалоговое окно Подбор параметра, как показано на рис.4

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

В поле Значениеуказываем 10000 — размер ссуды. В поле Изменяя значение ячейки даемссылку на ячейку В7, в которой вычисляется годовая процентная ставка. Посленажатия кнопки ОК средство подбора параметров определит, при какойгодовой процентной ставке чистый текущий объем вклада равен 10000 руб.Результат вычисления выводится в ячейку В7. В нашем случае годовая учетнаяставка равна 11,79%. Вывод: если банки предлагают большую годовую процентнуюставку, то предлагаемая сделка не выгодна.

Пример3 расчетаэффективности капиталовложений с помощью функции ПЗ(ПС)

Постановказадачи. Допустим,что у вас просят в долг 10000 руб. и обещают возвращать по 2000 руб. в течение6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?

Нарабочем листе ( рис.5) в ячейку В5 введена формула

=ПЗ(ПС)(В4; В2;-В3)

Нарабочем листе введем исходные данные в диапазон A1:B4.

В ячейкивведем следующие формулы:

· [B5]=ПЗ(B4; В2;-В3);

· =ЕСЛИ(В2=1;«год»; ЕСЛИ(И(В2>=2; В2<=4);«года»;«лет»));

· =ЕСЛИ (В1<В5;«Выгодно дать деньги в долг»; ЕСЛИ (В5=В1; «Вариантыравносильны»; «Выгоднее деньги положить под проценты»)).

Рис. 5. Расчет эффективностикапиталовложений

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

Синтаксис:

ПЗ(ПС)(ставка; кпер; выплата; остаток; тип)

Аргументы:

ставка Процентнаяставка за период

кпер Общеечисло периодов выплат

выплата Величинапостоянных периодических платежей

остаток Будущаястоимость или баланс наличности, который нужно достичь после последней выплаты.Если аргумент бз опущен, он полагается равным 0 (например, будущаястоимость займа равна 0)

тип Число0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 илиопущен, то оплата производится в конце периода, если 1 — то в начале периода

В данномразделе была рассмотрена задача с двумя результирующими функциями: числовой —чистым текущим объемом вклада и качественной, оценивающей, выгодна ли сделка.Эти функции зависят от нескольких параметров. Некоторыми из них вы можетеуправлять, например, сроком и суммой ежегодно возвращаемых денег. Часто бываетудобно проанализировать ситуацию для нескольких возможных вариантов параметров.Команда Сервис, Сценарии предоставляет такую возможность с одновременнымавтоматизированным составлением отчета. Рассмотрим способ применения этойкоманды для следующих трех комбинаций срока и суммы ежегодно возвращаемыхденег: 6, 2000; 12, 1500 и 7, 1500.

Выберемкоманду Сервис, Сценарии. В открывшемся диалоговом окне Диспетчерсценариев для создания первого сценария нажмите кнопку Добавить (рис.6).

Рис.6.Диалоговое окноДиспетчер сценариев

Вдиалоговом окне Добавление сценария в поле Название сценария введите,например ПЗ1, а в поле Изменяемые ячейки — ссылку на ячейки В2 и ВЗ, вкоторые вводятся значения параметров задачи (срок и сумма ежегодно возвращаемыхденег) (рис. 7).

Посленажатия кнопки ОК появится диалоговое окно Значения ячеек сценария, вполя которого введите значения параметров для первого сценария (рис.8).

Рис.7.Диалоговое окно Добавлениесценария

Спомощью кнопки Добавить последовательно создайте нужное число сценариев.После этого диалоговое окно Диспетчер сценариев будет иметь вид,показанный на рис. 9.

Рис.8.Диалоговое окно Значенияячеек сценария

Рис.9.Вывод сценариев нарабочий лист с помощью диалогового окна Диспетчер сценариев

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

Рис.10.Диалоговое окно Отчетпо сценарию

В этомокне в группе Тип отчета необходимо установить переключатель в положениеСтруктура или Сводная таблица , а в поле Ячейки результата датьссылку на ячейки, где вычисляются значения результирующих функций. Посленажатия кнопки ОК создается отчет. На рис. 11 показан отчет по сценариям типа Структура.

Рис.11.Отчет по сценариютипа Структура

Пример4 Финансовые функцииПЛПРОЦ и СНПЛАТ

Постановказадачи. Вычислитьосновные платежи, платы по процентам, общей ежегодной платы и остатка долга напримере ссуды 100000 руб. на срок 5 лет при годовой ставке 2% (рис. 12).

Рис.12. Вычислениеосновных платежей и платы по процентам

Ежегоднаяплата вычисляется в ячейке ВЗ по формуле:

В3=ППЛАТ(В1; В2;-В4).

Запервый год плата по процентам в ячейке В7 вычисляется по формуле:

В7=D6*0,02.

Основнаяплата в ячейке С7 вычисляется по формуле:

С7=$B$3-B7.

Остатокдолга в ячейке D7 вычисляется поформуле:

=D6-C7

Воставшиеся годы эти платы определяются с помощью протаскивания маркеразаполнения выделенного диапазона B7:D7 вниз по столбцам. Отметим, чтоосновную плату и плату по процентам можно было непосредственно найти с помощьюфункций оснплат (ррмт) и плпроц (ipmt), соответственно.

Функция плпроц возвращает платежи по процентамза данный период на основе периодических постоянных выплат и постоянной процентнойставки.

Синтаксис:

ПЛПРОЦ(ставка;период; клер; нз; бз; тип)

Функция оснплат возвращает величину выплаты заданный период на основе периодических постоянных платежей и постояннойпроцентной ставки.

Синтаксис:

ОСНПЛАТ(ставка;период; кпер; нз; бз; тип)

Аргументыфункций плпроц: и оснплат:

Период Период,за который требуется найти прибыль (должен находиться в интервале от 1 до кпер)

Ставка Процентнаяставка за период

кпер Общеечисло периодов выплат

нз Текущеезначение, т. е. общая сумма, которую составят будущие платежи

бз Будущаястоимость или баланс наличности, который нужно достичь после последней выплаты.Если аргумент бз опущен, он полагается равным 0 (например, будущаястоимость займа равна 0)

Пример5. Финансоваяфункция БЗ

ФункцияБЗ(БС) вычисляет будущее значениевклада на основе периодических постоянных платежей и постоянной процентнойставки. Функция БЗ(БС) подходитдля расчета итогов накоплений при ежемесячных банковских взносах.

Синтаксис:

БЗ(БС) (ставка; кпер; выплата; нз; тип)

Аргументы:

ставка Процентнаяставка за период

кпер Общеечисло периодов выплат

выплата Величинапостоянных периодических платежей

нз Текущеезначение, т. е. общая сумма, которую составят будущие платежи

тип Число0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 илиопущен, то оплата производится в конце периода, если 1 — в начале периода

Пример использования функции БЗ(БС). Предположим, вы хотитезарезервировать деньги для специального проекта, который будет осуществленчерез год. Предположим, вы собираетесь вложить 1000 руб. при годовой ставке 6%.Вы собираетесь вкладывать по 100 руб. в начале каждого месяца в течение года.Сколько денег будет на счете в конце 12 месяцев?

Спомощью формулы

=БЗ(б%/12;12; -100; -1000; 1)

получаемответ: 2 301.40р.

Провестирасчет, когда общее число периодов выплат –годовое.

Вариантызаданий упражнения 4

Пример1. Вычислить n-годичную ипотечную ссуду покупкиквартиры за Pруб. с годовойставкой i%и начальным взносом А%. Сделатьрасчет для ежемесячных и ежегодных выплат.

/>Варианты n Р i A

1 />7 70000 5 100

2 8 200000 6 100

3 9 220000 7 200

4 10 300000 8 200

5 11 350000 9 150

6 7 210000 10 150

7 8 250000 11 300

8 9 310000 12 300

9 10 320000 13 250

10 11 360000 14 250

11 7 300000 8 100

12 8 200000 6 100

13 9 220000 7 200

14 11 300000 8 200

15 10 350000 9 150

16 12 210000 10 150

17 8 250000 11 300

18 7 310000 12 300

19 10 320000 13 250
20 11 360000 14 250

Пример2. Вас просят дать вдолг Р руб. и обещают вернуть Р1руб. через год, P2руб. — через два года и т. д., наконец, Рпруб. — через п лет. При какой годовой процентной ставке эта сделкаимеет смысл?

Варианты п Р Р1 Р2 Р3 Р4 Р5

1 3 17000 5000 70008000

2 4 20000 6000 60009000 7000

3 5 22000 5000 8000 8000 7000 5000

4 3 30000 5000 1000018000

5 4 35000 5000 900010000 18000

6 5 21000 4000 5000 8000 10000 11000

7 3 25000 8000 900010000

8 4 31000 9000 1000010000 15000

9 5 32000 8000 10000 10000 10000 11000

10 3 36000 10000 1500021000

11 4 20000 6000 60009000 7000