Задача № 1

Постановка задачи:

Решить систему уравнений вида АХ=В и выполнить проверку решения.

9 5 4 7

А= 4 6 8 7

5 8 7 6

5 6 8 7

В= 6

Первый способ: решение с помощью обратной матрицы

1. Открываем Microsoft Excel, в ячейку А3 вводим «матрица А». Затем начиная с ячейки В5 до Е8 вводим данные матрицы А.

2. В ячейке G3 вводим «вектор-столбец В». Затем начиная с ячейки G5 по G8 вводим данные матрицы В.

3. Находим определитель матрицы А:

· в ячейку В11 вводим

=МОПРЕД(массив), затем Enter.

4. Находим обратную матрицу (в ячейке G13 вводим «обратная матрица »)13 вводим "Х=енных хода отображены на рис. :

· выделяем диапазон начиная с ячейки В13 и заканчивая ячейкой Е16, в этом диапазоне вводим:

=МОБР(массив), затем

Четыре выше, перечисленных хода отображены на рис. 1.

Рис. 1

5. Вычисляем матрицу Х (Х=):

· в ячейке А22 вводим «вектор-столбец Х»;

· выделяем диапазон, начиная с ячейки В24 до В27 → вводим:

=МУМНОЖ(<>;<В>) → ;

6. Проверка (А*Х):

· в ячейку А29 вводим «проверка»;

· выделяем диапазон начиная с ячейки В30 до В33 → вводим:

=МУМНОЖ(<А>;<Х>) → .

Второй способ: решение по методу Крамера.

1. Вычисляем :

· начиная с ячейки В37 до Е40 вводим данные матрицы А, заменяя первый столбец на матрицу В;

· в ячейке F38 вводим «ОПРЕД 1»;

· в ячейке G38 вводим:

=МОПРЕД(массив) → Enter.

2. Аналогично вычисляем ,

Два перечисленных хода (5, 6) и вычисление отображены на рис. 2

Рис. 2

3. Вычисляем =/:

· в ячейку В-65 вводим → в ячейку С-65 вводим =G38/В11 → Enter;

4. Аналогично вычисляем , и .

Вычисление , , и вычисление , , , отображено на рис. 3

Рис. 3

Задача № 2

Постановка задачи:

Найти минимальное значение функции y = - (3 - ) на отрезке [-1; 3] и построить график этой функции.

Решение:

1. В ячейке А2 вводим «Х»;

2. Начиная с ячейки А3 до А7 вводим интервал;

3. В ячейке В2 вводим «У»;

4. В ячейке В3 вводим:

=-(3-А3*А3) → Enter.

5. Аналогично вычисляем остальные значения У.

6. Вычисляем минимум функции:

· В ячейке А7 вводим «min»;

· В ячейке В7 вводим:

=МИН(массив).

7. Строим график функции:

· Вставка → диаграмма → точечная → далее → далее → в колонке название диаграммы вводим «У=-(3-Х*Х)» → готово.

Решение задачи № 2 отображено на рис. 4.

Рис. 4

Задача № 3

Постановка задачи:

у = sin х

у = cos х

Найти приближенное решение системы уравнений графическим способом.

Решение:

В Microsoft Excel вводим таблицы № 1и № 2

	Х	У=sin(Х)
0	0	=SIN(адрес Х)
П/4	=ПИ()/4	---
П/2	=ПИ()/2	---
3П/4	=3/4*ПИ()	---
П	=ПИ()	---
5П/4	=5/4*ПИ()	---
3П/2	=3/2*ПИ()	---
7П/4	=7/4*ПИ()	---
2П	=2*ПИ()	---

Табл. 1

	Х	У=cos(Х)
0	0	=COS(адрес Х)
П/4	=ПИ()/4	---
П/2	=ПИ()/2	---
3П/4	=3/4*ПИ()	---
П	=ПИ()	---
5П/4	=5/4*ПИ()	---

Табл. 2

Строим диаграмму:

Диаграмма → график → далее → исходные данные → добавить → имя (У=cos(Х)) → готово.

Решение задачи № 3 отображено на рис. 5

Рис. 5

Задача № 4

Взят кредит в размере 10000 на 6 лет. Кредит будет погашаться равными долями по 2000. Вычислите установленную годовую процентную ставку.

Решение:

В Microsoft Excel выделяем ячейку А1 → вставка → функция → категория → финансовые → OK → НОРМА → OK → в окне «аргументы функции» в строку «КПЕР» вводим 6 → «Плт» вводим -2000 → «Нз» вводим 10000 → «БС» вводим 0 → «Тип» вводим 0 → OK.

Заполнение окна «аргументы функции» отображено на рис. 6.

Рис. 6.

Решение задачи №4 отображено на рис. 7.

Рис. 7.

Задача № 5

Постановка задачи:

Взят кредит в размере 10 тыс. Он будет погашаться равными долями по 2 тыс. в конце каждого года. Установленная процентная ставка составляет 7 %. Вычислите количество лет, по истечении которых расчеты по взятому кредиту будут полностью выплачены и долг будет погашен.

Решение:

Заполнение окна «аргументы функции» отображено на рис. 8.

Рис. 8

Решение задачи № 4 отображено на рис. 9

Рис. 9

Задача № 6

Постановка задачи:

Рассчитать все пустые ячейки голубого цвета, изображенные на рис. 10.

Рис. 10

Решение:

1. В ячейке P9 вводим:

=СЧЁТ(B9:O9) → Enter → протягиваем маркер до ячейки P13.

2. В ячейке Q9 вводим:

=8*P9 → Enter → протягиваем маркер до ячейки Q13.

3. В ячейке R9 вводим:

=ЕСЛИ(СЧЁТЕСЛИ(B9:O9;”б/л”)=0;”---”;СЧЁТЕСЛИ(B9:O9;”б/л”)) → Enter → протягиваем маркер до ячейки R13.

4. В ячейке S9 вводим:

=ЕСЛИ(СЧЁТЕСЛИ(B9:O9;”от”)=0;”---”;СЧЁТЕСЛИ(B9:O9;”от”)) → Enter → протягиваем маркер до ячейки S13.

5. В ячейке T9 вводим:

=ЕСЛИ(СЧЁТЕСЛИ(B9:O9;”к”)=0;”---”;СЧЁТЕСЛИ(B9:O9;”к”)) → Enter → протягиваем маркер до ячейки T13.

6. В ячейке B14 вводим:

=СУММ(B9:B13) → Enter → протягиваем маркер до ячейки T14.

Решение задачи № 10 отображено на рис. 11.

Рис. 11

Задача № 7

Постановка задачи:

Функции пользователя.

Для определения остаточной стоимости основных фондов в конце периода эксплуатации расчет производится по формуле:

где:

- остаточная стоимость;

- начальная стоимость;

- конечная стоимость;

- число периодов эксплуатации;

- номер текущего периода эксплуатации.

Требуется:

1. Разработать функцию пользователя для вычисления остаточной стоимости.

2. Отладить программу.

3. Продемонстрировать работоспособность в программе на примере расчета по данным таблицы.

Расчет остаточной стоимости

Начальная стоимость	Конечная стоимость	Количество периодов эксплуатации	№ текущего периода	Остаточная стоимость
120000	1000	10	2
50000	1500	5	4
230000	10000	10	9
320000	5000	10	10

Решение:

1. Разрабатываем функцию пользователя для вычисления остаточной стоимости:

Function_ОстСтоим(НачСтоим,КонСтоим,КолПериод,НомТекПериод)

ОстСтоим = НачСтоим-НомТекПериод*(НачСтоим-КомСтоим)/КолПериод

End Function

2. Отладим программу:

· вызываем редактор Visual Basic:

Сервис → макрос → редактор Visual Basic;

· для создания функции ОстСтоим необходимо зайти в модуль:

Insert → Module, набираем программу в окне модуля I → сохраняем (этот ход отображен на рис. 12);

· откомпелируем программу:

Debug → Compile VBA Project → сохраняем → File → Close and Return to Microsoft Excel;

· в ячейке F4 вводим:

=ОстСтоим(диапазон) → Enter.

Рис. 12

Решение задачи № 6 отображено на рис. 13

Рис. 13

Задача № 8

Постановка задачи:

Разработать функцию пользователя для вычисления будущей суммы вклада по формуле

где:

S – сумма, которую нужно иметь на счете к концу периода;

i – годовая процентная ставка;

T – количество периодов (год) на которые сделан вклад.

Ставка

Срок

(месяцах)

Необходимая сумма в конце периода

Будущая сумма вклада

10%

101667,00 р.

10%

8400,00 р.

Решение:

Разрабатываем функцию пользователя для вычисления будущей суммы вклада:

Function_БудСумВкл(Ставка,СрокМес,СумВКонПер)

БудСумВкл=СумВКонПер/(1+Ставка/12*СрокМес)

Отладим программу:

· вызываем редактор Visual Basic:

Сервис → макрос → редактор Visual Basic;

· для создания функции БудСумВкл необходимо зайти в модуль:

Insert → Module, набираем программу в окне модуля I → сохраняем (этот ход отображен на рис. 14);

· откомпелируем программу:

Debug → Compile VBA Project → сохраняем → File → Close and Return to Microsoft Excel;

· в ячейке F4 вводим:

= БудСумВкл (диапазон) → Enter.

Рис. 14

Решение задачи № 7 отображено на рис. 15

Рис. 15

Задача № 9

Разработать функцию пользователя для вычисления накопленного долга для случая роста годовой процентной ставки и ежегодной капитализации процентов.

Разработать функцию пользователя для вычисления будущей суммы вклада по формуле

где:

P – величина кредита;

i – годовая процентная ставка;

n – срок кредита (лет).

Используя созданную функцию рассчитать графу «Сумма к возврату» в таблице:

Заемщик

Величина кредита

Срок кредита

Рост ставки в %

Сумма к возврату

10 000

15 000

25 000

100 000

200 000

5,50

4,00

6,00

6,50

3,50

Разрабатываем функцию пользователя для вычисления будущей суммы вклада:

Function_НакопДолг (ВелКред,СрокКред,Ставка)

НакопДолг = ВелКред*((1+Ставка)*(СрокКред/12))

Отладим программу:

· вызываем редактор Visual Basic:

Сервис → макрос → редактор Visual Basic;

· для создания функции НакопДолг необходимо зайти в модуль:

Insert → Module, набираем программу в окне модуля I → сохраняем (этот ход отображен на рис. 16);

· откомпелируем программу:

Debug → Compile VBA Project → сохраняем → File → Close and Return to Microsoft Excel;

· в ячейке F4 вводим:

= НакопДолг (диапазон) → Enter.

Рис. 16

Задача № 10

Постановка задачи:

Рассчитать все пустые ячейки голубого цвета, изображенные на рис. 17.

Рис. 17

Решение:

1. Рядом с таблицей создаем еще одну таблицу, в которой определим тип данных:

· в ячейке I3 вводим:

=ТИП(B3) → Enter → аналогично определяем тип данных в оставшихся ячейках.

2. Найдем количество пустых ячеек:

· в ячейке В12 вводим:

=СЧИТАТЬПУСТОТЫ(В3:В9) → Enter → протягиваем маркер до ячейки G12.

3. Найдем количество непустых ячеек:

· в ячейке В10 вводим:

=ЧСТРОК(В3:В9)-В12 → Enter → протягиваем маркер до ячейки G10.

4. Найдем количество ячеек с текстом:

· в ячейке В14 вводим:

=СЧЁТЕСЛИ(I3:I9;”2”) → Enter → протягиваем маркер до ячейки G14.

5. Найдем количество чисел:

· в ячейке В16 вводим:

=СЧЁТЕСЛИ(I3:I9;”1”) → Enter → протягиваем маркер до ячейки G16.

6. Найдем сумму чисел:

· в ячейке В18 вводим:

=СУММ(В3:В9) → Enter → протягиваем маркер до ячейки G18.

7. Найдем среднее значение чисел:

· в ячейке В20 вводим:

=В18/В16 → Enter → протягиваем маркер до ячейки G20.

Решение задачи № 11 отображено на рис. 18.

Рис. 18

Задача № 11

Клиент в течении 5 лет в начале каждого месяца делает вклады в банк. Годовая ставка по выбоанному виду вклада равна 10%. Рассчитать сумму платежа, чтобы в конце 5 года на счету клиента была сумма равная 14 000.

Решение:

В Microsoft Excel выделяем ячейку А1 → вставка → функция → категория → финансовые → OK → ППЛАТ → OK → в окне «аргументы функции» в строку «ставка» вводим 10 → «кпер» вводим 5 → «Нз» вводим 10000 → «БС» вводим 14000 → «Тип» вводим 0 → OK.

Заполнение окна «аргументы функции» отображено на рис. 19.

Рис. 19