Методичка по информационным технологиям
.pdf
21
Тогда формула, дающая решение задачи, имеет вид:
=БС(28%/4, 4*1,5, , 20000). Она возвращает результат -$30 014,61.
ЗАДАНИЕ 1.
Сумма 2000 размещена под 9% годовых на 3 года. Проценты начисляются раз в квартал. Какая сумма будет на счёте через 3 года?
ЗАДАНИЕ 2.
Гражданин взял ссуду $2000 под 5% в месяц и обязуется возвращать банку $100 ежемесячно. Почему гражданин не сможет расплатиться с банком?
ЗАДАНИЕ 3.
Какова сумма долга через 26 месяцев, если его первоначальная величина 500 000 долларов, ставка 20% годовых, начисление поквартальное?
ЗАДАНИЕ 4.
Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100% или на 6 месяцев под 110%. Как выгоднее вкладывать деньги на полгода: дважды на три месяца или один раз на 6 месяцев?
ЗАДАНИЕ 5.
Рассчитать будущее значение вклада 1000 долларов через 0, 1, 2...5 при годовых процентных ставках 10%, 20%,..., 50%. Дополнительные поступления и выплаты отсутствуют. Решение оформить в виде таблицы.
Построить семейство графиков зависимости будущего значения от срока.
ЗАДАНИЕ 6.
На счёт в банке вносится сумма 10 000 долларов в течении 10 лет равными долями в конце каждого года. Годовая ставка 4%.
•Какая сумма будет на счёте после 10 лет?
•Какая сумма будет на счёте после 10 лет, если сумма вносится в начале каждого года?
ЗАДАНИЕ 7.
Рассматриваются две схемы вложение денег на 3 года:
•Вначале каждого года под 24% годовых
•В конце каждого года под 36% ежегодно вносится по $4000. Какая схема выгоднее?
ЗАДАНИЕ 8.
За какой срок в годах сумма, равная 75 000 долларов, достигнет $200 000, при начислении процентов по сложной ставке 15%
•раз в году;
•поквартально?
ПОДСКАЗКА. Воспользуйтесь функцией
КПЕР(норма, выплата, начальное_значение, будущее_значение, тип)
Обратите внимание, что нет необходимости набирать все нули в начальной и конечной сумме — достаточно сохранить между ними пропорциональность.
22
ЗАДАНИЕ 9.
Ссуда 63 200 рублей, выданная под 32% погашается ежеквартальными платежами по 8400 рублей. Рассчитайте срок погашения ссуды.
ЗАДАНИЕ 10.
Пусть в долг на полтора года дана сумма 2000 долларов с условием возврата $3000. Вычислить годовую процентную ставку.
ПОДСКАЗКА. Воспользуйтесь функцией
СТАВКА(количество_периодов, выплата,, начальное_значение, будушее_значение, тип, начальное приближение)
Функция возвращает процентную ставку за один период. Начальное приближение по умолчанию составляет 10%.
Тема 6. Решение задач с матрицами
Простейшие операции, которые можно проделывать с матрицами, используя процессор электронных таблиц Excel: сложение (вычитание), умножение на число, перемножение, транспонирование, вычисление обратной матрицы.
Задание 18. Сложение матриц
Сложить матрицы М и N, где
M |
2 |
3 |
7 |
и N |
1 |
0 |
4 . |
|
1 |
5 |
6 |
|
2 |
3 |
5 |
Решение
Для решения этого примера воспользуемся так называемыми табличными формулами (т.е. формулами массива). Введем матрицы М и N в блоки А1:С2 и
Е1:G2.
1способ
Вблок А4:С5 введем табличную формулу {= А1:С2 + E1:G2} (заметьте, что табличные
формулы указываются в фигурных скобках!). Для этого:
выделим блок ячеек А4:С5, в этом блоке активна ячейка А4.
Обратите внимание, что выделен блок, имеющий те же размеры, что и исходные матрицы.
наберем знак равенства =;
выделим блок ячеек А1:С2, наберем знак +, выделим блок ячеек Е1:G2;
закончим ввод формулы нажатием комбинации из трех клавиш
|
|
|
|
|
|
|
|
|
|
|
CTRL+SHIFT+ENTER |
. |
Во |
всех |
ячейках |
блока |
появится |
||||
|
|
|
|
|
|
|
|
|
|
|
формула {= А1:С2 + E1:G2}.
Это очень важно! Если бы Вы нажали ENTER, то формула была бы введена только в активную ячейку блока А4. Фигурные скобки, окружающие формулу, говорят о том, что это табличная формула. Эти скобки нельзя набирать вручную (формула будет воспринята как текст).
2 способ
23
Использование имен делает процедуру ввода табличной формулы намного проще. Дадим диапазонам А1:С2 и E1:G2 имена М и N. Для этого:
выделим блок ячеек А1:С2;
дадим команду ВставкаИмяПрисвоить…
в диалоговом окне Присвоение имени зададим имя блока M.
Действуя аналогично, присвоим имя N диапазону E1:G2,. Убедитесь, что теперь можно выделять эти блоки, выбирая из выпадающего списка в поле имени соответствующее имя.
Вблок E4:G5 введем табличную формулу {= М + N}. Для этого
выделим блок ячеек E4:G5;
наберем знак равенства =;
нажмем функциональную клавишу F3 (или дадим команду ВставкаИмяВставить…).
появится диалоговое окно Вставка имени. Выберем имя M и щелкнем
«ОК»;
наберем знак +;
вновь нажмем функциональную клавишу F3 и в диалоговом окне Вставка имени выберем имя N. Формула примет вид = M + N;
Нажмем сочетание клавиш CTRL+SHIFT+ENTER.
Результат, естественно, тот же, что и в способе 1:
M N |
1 |
3 |
11 . |
|
1 |
2 |
11 |
Матричные функции
Для матричных операций в Excel предусмотрены функции, входящие в категорию «Математические». Перечислим эти функции:
МОПРЕД — вычисление определителя матрицы; МОБР — вычисление обратной матрицы; МУМНОЖ — перемножение матриц; ТРАНСП — транспонирование.
Первая из этих функций возвращает число, поэтому вводится как обычная формула. Остальные функции возвращают блок ячеек, поэтому они должны вводиться как табличные формулы (для матричных операций без табличных формул не обойтись!). Первая буква «М» в названии трех функций — сокращение от слова «матрица».
Задание 19. Вычисление определителя матрицы
Вычислить определитель и обратную матрицу для матрицы
73 78 24 A 92 66 25 80 37 10
Проверить правильность вычисления обратной матрицы умножением ее на исходную.
24
Решение
Разместим исходную матрицу в блоке А1:СЗ. В ячейке В5 поместим формулу для вычисления определителя = МОПРЕД(А1:СЗ). В блок А7:С9 введем формулу для вычисления обратной матрицы. Для этого
выделим блок А7:С9 (он имеет три строки и три столбца, как и исходная матрица);
введем формулу {= МОБР(А1:СЗ)};
даже если вы используете «Мастер функций», нужно завершать ввод
нажатием комбинации CTRL+SHIFT+ENTER (вместо щелчка по кнопке
«ОК»).
Если вы забыли предварительно выделить блок А7:С9, а ввели формулу в ячейку А7 как обычную формулу Excel (закончив ввод нажатием ENTER ), то не нужно вводить
еезаново:
выделите А7:С9,
нажмите клавишу F2 (редактирование), но не изменяйте формулу, а просто нажмите клавиши CTRL+SHIFT+ENTER.
Для дальнейших вычислений присвоим матрицам на рабочем листе имена: А1:СЗ — А, А7:С9 — Ainv. Чтобы в уже введенных формулах появились эти имена, выберите в меню пункт ВставкаИмяПрименить, выделите в диалоговом окне нужные имена и щелкните «ОК».
Теперь проверим правильность вычисления обратной матрицы. В блок А12:С14 введем формулу {= МУМНОЖ(А; Ainv)}. У вас должен получиться результат:
|
|
|
A |
|
|
|
B |
|
C |
|
|
|
D |
|
|
|
|
|
-73 |
|
78 |
24 |
|
|
|
|
|
|
|||
|
1 |
|
|
|
|
|
|
|
|
|
|||||
|
|
|
92 |
|
66 |
25 |
|
|
|
|
|
|
|||
|
2 |
|
|
|
|
|
|
|
|
|
|||||
|
3 |
|
|
-80 |
|
37 |
10 |
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
= МОПРЕД (А) |
|
|||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
4 |
|
|
|
|
|
|
|
|
|
|
||||
|
5 |
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
6 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-256 |
|
108 |
366 |
|
|
|
|
|
|
|||
|
7 |
|
|
|
|
|
|
{= МОБР (А)} |
|
||||||
|
8 |
|
|
-2920 |
|
1190 |
4033 |
|
|
|
|
||||
|
|
|
|
|
|
|
|||||||||
|
|
|
|
|
|
|
|
|
|
||||||
|
9 |
|
|
8684 |
|
-3539 |
-11994 |
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
||||||
|
10 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
11 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
{= МУМНОЖ (A; Ainv)} |
|||
|
12 |
|
|
1 |
|
|
-2,91Е-11 |
|
-5,82Е-11 |
|
|
||||
|
|
|
|
|
|
|
|
||||||||
|
|
|
0 |
|
1 |
0 |
|
|
|
|
|
|
|||
|
13 |
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
||||||
14 |
0 |
0 |
1 |
Как и следовало ожидать, получилась матрица, близкая к единичной.
Задание 20. Решить систему линейных уравнений
2x1 |
3x2 |
7x3 |
6x4 |
1, |
3x1 |
5x2 |
3x3 |
x4 |
3, |
5x1 |
3x2 |
x3 |
3x4 |
4, |
3x1 |
3x2 |
x3 |
6x4 |
5. |
|
|
|
|
|
25
двумя способами:
1) матричным методом; 2) методом Крамера.
Решение
Для нахождения решения системы линейных уравнений сначала необходимо построить матрицу системы из коэффициентов при неизвестных (это матрица А) и столбец из свободных элементов (это столбец В). Создайте новый документ Excel «Системы уравнений». Переименуйте Лист1 – «Метод обратной матрицы», а Лист2 – «Метод Крамера».
На обоих листах введите матрицу А в диапазон ячеек A2:D5 и задайте диапазону имя A, введите столбец B в диапазон ячеек F2:F5 и задайте диапазону имя B. (Чтобы данные вводились сразу на оба листа, предварительно выделите их: прижмите SHIFT и щелкните мышкой по ярлычкам каждого листа). На каждом листе у вас должно получиться:
|
А |
B |
|
C |
D |
E |
|
F |
G |
H |
|
|
|
А |
|
|
|
В |
|
|
|
1 |
|
|
|
|
|
|
|
|||
2 |
2 |
3 |
|
7 |
6 |
|
1 |
|
|
|
|
3 |
5 |
|
3 |
1 |
|
3 |
|
|
|
3 |
|
|
|
|
||||||
4 |
5 |
3 |
|
1 |
3 |
|
4 |
|
|
|
|
3 |
3 |
|
1 |
6 |
|
5 |
|
|
|
5 |
|
|
|
|
||||||
6 |
|
|
|
|
|
|
|
|
|
|
1) Работаем с рабочим листом «Метод обратной матрицы». Для нахождения корней системы методом обратной матрицы, в ячейках A7:D10 вычислим обратную матрицу используя формулу {= МОБР (A)} (не забудьте закончить ввод формулы
нажатием клавиш CTRL+SHIFT+ENTER ). Корни уравнений разместим в ячейках F7:F10,
задав формулу {= МУМНОЖ (A7:D10 ;B)}.
2) Работаем с рабочим листом «Метод Крамера». Для решения системы линейных уравнений методом Крамера необходимо вычислить определитель матрицы A и построить четыре вспомогательные матрицы (назовем их DA1, DA2,
DA3, DA4).
А |
B |
C |
D |
E |
F |
G |
H |
1 |
|
А |
|
|
В |
|
|
|
2 |
2 |
3 |
|
7 |
6 |
|
1 |
|
|
|
|
|
|
|
3 |
3 |
5 |
|
3 |
1 |
Опреде- |
3 |
|
|
|
|
|
|
|
4 |
5 |
3 |
|
1 |
3 |
лители |
4 |
|
|
|
|
|
|
|
5 |
3 |
3 |
|
1 |
6 |
-420 |
5 |
|
|
|
|
|
|
|
6 |
|
|
DА1 |
|
|
|
= МОПРЕД (А) |
|
|
|
|
||
|
7 |
1 |
3 |
|
7 |
6 |
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|||||
|
8 |
3 |
5 |
|
3 |
1 |
|
|
|
|
|
|
|
|
|
9 |
4 |
3 |
|
1 |
3 |
|
|
|
|
|
|
= E10 / E5 |
|
|
10 |
5 |
3 |
|
1 |
6 |
-78 |
|
X1= |
0,185714 |
||||
|
|
|
|
|
||||||||||
|
|
|
|
|
||||||||||
|
11 |
|
|
DА2 |
|
|
|
|
|
|
|
|
|
|
|
12 |
2 |
1 |
|
7 |
6 |
|
|
|
|
|
|
|
|
|
|
|
|
= МОПРЕД (DА1) |
|
|
|
|||||||
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
26 |
|
|
13 |
3 |
3 |
3 |
1 |
|
|
|
14 |
5 |
4 |
1 |
3 |
|
|
= ? |
15 |
3 |
5 |
1 |
6 |
-327 |
X2= |
0,778571 |
16 |
|
|
DА3 |
|
|
|
|
17 |
2 |
3 |
1 |
6 |
|
= МОПРЕД (DА2) |
|
18 |
3 |
5 |
3 |
1 |
|
||
|
|
|
|||||
19 |
5 |
3 |
4 |
3 |
|
|
= ? |
20 |
3 |
3 |
5 |
6 |
267 |
X3= |
-0,63571 |
21 |
|
|
DА4 |
|
|
= МОПРЕД (DА3) |
|
22 |
2 |
3 |
7 |
1 |
|
||
|
|
|
|||||
23 |
3 |
5 |
3 |
3 |
|
|
|
24 |
5 |
3 |
1 |
4 |
|
|
= ? |
25 |
3 |
3 |
1 |
5 |
-192 |
X4= |
0,457143 |
26 |
|
|
|
|
|
= МОПРЕД (DА4) |
|
27 |
|
|
|
|
|
||
|
|
|
|
|
|
|
|
Сначала построим вспомогательные матрицы DA1, DA2, DA3 и DA4 и зададим |
|||||||
имена соответствующим диапазонам ячеек. Затем зададим в ячейке E5 формулу |
|||||||
для вычисления определителя матрицы A, в ячейке E10 – для вычисления определителя |
|||||||
матрицы DA1, и т.д. |
|
|
|
|
|
|
|
Согласно правилу Крамера xi=ОПРЕД(DAi)/ОПРЕД(A). Формулы для вычисления |
|||||||
корней системы уравнений x1, x2, x3 и x4 разместим в ячейках G10, G15, G20 и G25 соответственно.
Задание 21. Контрольная работа
Задание А
Спроектируйте лист Excel для вычисления матриц C и D, заданных матричными выражениями, где символ Т задает операцию транспонирования, Е — единичная
матрица, а A 1 — обратная матрица. Произведите вычисления (расчеты). Документу задайте имя «Матричные операции». Установите защиту листа,
оставив право на редактирование только ячеек с данными матриц A и Y.
Вариант 1.
D 3A 1 |
E , |
C |
Y T AT A2Y , где |
|
|||
|
9 |
5 |
4 |
7 |
|
2 |
|
A |
4 |
6 |
8 |
7 |
Y |
6 |
|
5 |
8 |
7 |
0 |
2 . |
|||
|
, |
||||||
|
5 |
6 |
8 |
7 |
|
3 |
|
Вариант 2.
D 5E A 1 , C Y T A3Y , где
27
|
9 |
0 |
3 |
8 |
|
3 |
|
A |
4 |
6 |
7 |
4 |
Y |
5 |
|
2 |
0 |
5 |
3 |
1 . |
|||
|
, |
||||||
|
4 |
8 |
3 |
7 |
|
3 |
|
Вариант 3. |
|
|
|
|
|
|
|
D |
4A 1 |
E , |
C |
Y T AT A3 , где |
|
||
|
2 |
3 |
5 |
2 |
|
3 |
|
A |
0 |
2 |
7 |
5 |
Y |
1 |
|
4 |
2 |
1 |
7 , |
2 . |
|||
|
|
||||||
|
7 |
5 |
4 |
1 |
|
2 |
|
Вариант 4. |
|
|
|
|
|
|
|
D |
ET |
7A , |
C |
Y T AT A 1Y , где |
|
||
|
1 |
4 |
2 |
5 |
|
1 |
|
A |
4 |
0 |
5 |
3 |
Y |
2 |
|
1 |
2 |
6 |
8 |
5 . |
|||
|
, |
||||||
|
0 |
7 |
3 |
2 |
|
1 |
|
Вариант 5.
D 6E |
A 1 , |
|
C |
Y T A3 AT , где |
|
||
|
0 |
6 |
3 |
0 |
|
3 |
|
A |
4 |
6 |
7 |
4 |
Y |
5 |
|
2 |
3 |
5 |
3 |
1 . |
|||
|
, |
||||||
|
4 |
8 |
3 |
7 |
|
0 |
|
Вариант 6. |
|
|
|
|
|
|
|
D A 1 |
12E , |
|
C |
Y T A2 AT , где |
|
||
|
2 |
4 |
7 |
0 |
|
2 |
|
A |
4 |
1 |
6 |
2 |
Y |
1 |
|
8 |
3 |
6 |
7 |
4 . |
|||
|
, |
||||||
|
6 |
3 |
5 |
7 |
|
1 |
|
Вариант 7. |
|
|
|
|
|
28 |
|
|
|
|
|
|
|
|
|
D 3ET |
A 1 , |
C |
|
Y T A2Y , где |
|
||
|
3 |
3 |
4 |
5 |
|
|
1 |
A |
0 |
6 |
4 |
6 |
|
Y |
2 |
3 |
4 |
5 |
5 |
|
1 . |
||
|
, |
|
|||||
|
1 |
9 |
3 |
6 |
|
|
2 |
Вариант 8. |
|
|
|
|
|
|
|
D E |
9A 1 , |
|
C |
|
Y T A 1 AT , где |
|
|
|
2 |
1 |
5 |
5 |
|
|
3 |
A |
0 |
2 |
2 |
6 |
|
Y |
1 |
2 |
2 |
1 |
0 |
|
2 . |
||
|
, |
|
|||||
|
1 |
3 |
3 |
1 |
|
|
1 |
Вариант 9.
D A 18E
0 6
T , C |
|
Y T A 1 AT Y , где |
2 |
7 |
1 |
A |
4 |
9 |
5 |
|
5 |
|
Y |
1 |
2 |
3 |
4 |
|
0 |
|
3 . |
||
|
|
, |
|
|||||
|
1 |
5 |
6 |
|
9 |
|
|
3 |
Вариант 10. |
|
|
|
|
|
|
|
|
D 2A |
ET , |
C |
|
Y T A 1 AT A , где |
||||
|
3 |
6 |
5 |
2 |
|
|
|
2 |
A |
4 |
0 |
3 |
5 |
|
|
Y |
0 |
2 |
3 |
2 |
6 |
|
|
1 . |
||
|
, |
|
|
|||||
|
2 |
4 |
3 |
0 |
|
|
|
1 |
Задание В
Создайте документ «Системы линейных уравнений», в котором решите систему линейных уравнений AX B двумя методами:
1)на Листе 1 — методом обратной матрицы;
2)на Листе 2 — методом Крамера.
Назовите Лист 1 и Лист 2 именами соответствующих методов.
29
Добавьте на лист Метод обратной матрицы блок проверки правильности найденных корней системы линейных уравнений.
На листе Метод Крамера добавить сообщение «Решения нет!» для корней
уравнения, если система линейных уравнений несовместна (когда A 0 ).
Установите защиту листов, оставив право на редактирование только ячеек с данными матриц A и B.
Вариант 1.
x1 2x2 |
3x3 |
1 |
|
2x1 |
x2 |
x3 |
5 |
3x1 |
2x3 |
0 |
|
Вариант 2. |
|
|
|
x1 |
2x2 |
1 |
|
2x1 |
x2 |
2x3 |
5 |
3x1 |
3x2 |
2x3 |
0 |
Вариант 3. |
|
|
|
2x2 |
7x3 |
1 |
|
4x1 |
x2 |
x3 |
4 |
11x1 |
8x2 |
6x3 |
1 |
Вариант 4.
5x1 2x2 7x3 0 3x2 7x3 4
13x1 x2 9x3 3
Вариант 5.
14x1 2x2 7x3 
3 2x1 x2 9x3 10
10x1 x2 7
30
Вариант 6. |
|
|
|
6x2 |
11x3 |
7 |
|
3x1 |
5x2 |
2x3 |
5 |
4x1 |
x2 |
13x3 |
0 |
Вариант 7.
2x1 5x2 11x3 7 3x1 4x3 
5
4x1 x2 9x3 0
Вариант 8.
x1 5x2 x3 2 2x1 7x2 x3 0 5x2 11x3 9
Вариант 9.
8x1 |
x3 |
3 |
|
2x1 |
x2 |
2x3 |
5 |
14x1 |
3x2 |
4x3 |
11 |
Вариант 10. |
|
|
|
5x1 |
9x2 |
x3 |
9 |
3x2 |
4x3 |
4 |
|
3x1 |
x2 |
7x3 |
13 |
Тема 7. Анализ данных, составление отчетов и консолидация.
Задание 22. Составление простейших итоговых отчетов
1.Откройте файл «Население». Плотность населения вычислите сами.
2.Отсортируйте данные вначале по полю Полушарие, затем по полю Часть света и по полю Страна.
|
|
|
Площадь, |
Население, |
Плотность |
|
Полушарие |
Часть света |
Страна |
населения, |
|||
тыс. кв. км |
тыс. чел. |
|||||
|
|
|
чел. / кв. км |
|||
|
|
|
|
|
||
Восточное |
Азия |
Вьетнам |
331,7 |
60863 |
|
|
Восточное |
Азия |
Китай |
9 597,0 |
1317000 |
|
|
Восточное |
Азия |
Монголия |
1 566,5 |
1866 |
|