- •Оглавление
- •Введение
- •3.2.Элементы окна программы и окна рабочей книги Файл Element
- •1. Открыть файл Element.
- •2. Найти на экране все элементы окна, которые обозначены на рис.1.
- •3. Записать, в таблицу на рабочем листе "Элементы" их названии и выучить назначение каждого элемента, попробовать его в действии.
- •3.3. Операции по изменению структуры рабочей книги Файл Rabbook 1
- •3 А д а н и е 1. Добавить новый рабочий лист в открытую рабочую книгу "Rabbook1", дли этого:
- •3 А д я н и е 5. Вызвать на экран список всех рабочих листов, для этого:
- •3 А д а н и е 7. Переименовать лист "Лист 18", присвоив ему в качестве нового имени слово "Февраль", для этою:
- •3 А д а н и е 12. Выделить все листы рабочей книги, для этого:
- •3 А д а н и е 13. Отменить выделение всех листов:
- •3 А д а н и е 15. Выделить несколько рабочих листов не например листы Лист 1, Лист 3, Лист 5.
- •Файл Rabbook2
- •3.4. Способы перемещения активной клеточки по рабочему листу Файл Move
- •3 А д а н и е. На рабочем листе с именем "Поле" найти ячейку, в которую записано контрольное слово.
- •3.5. Правила ввода данных в ячейки таблицы Файл Vvod
- •3.6 . Правила копированиям размножения данных Файл Сору
- •3.7. Способы поиска и устранения ошибок при организации вычислений Файл Error
- •3.8. Форма тиров ание я чеек и данных, которые они содержа т Файл Ramka
- •1. Открыть файл Ramka.
- •2. Создать таблицу, образец которой приведен ниже.
- •3.9. Создание собственных списков автозаполнения Файл Spisok
- •3.10. Добавление примечаний к ячейке электронной таблицы
- •3.11. Использование рациональных способов ? копирования и размножения данных при составлении стандартных таблиц Файл Calendar
- •3 А д а и и е 1. Создать заготовку для набора карманных календарей.
- •3 А д а н и е 3. На листе "Задание" создать три календари используя шаблон, подготовленный на листе "Пример".
- •3.12. Раздельное копирование значений, формул, примечаний и форматов ячейки Файл Spets
- •1. Числа, выделенные жёлтым цветом, увеличить и 3 раза,
- •2. К числам, выделенным зелёным цветом, прибавить 43.
- •3. Числа, выделенные синим цветом, уменьшить в 5 раз.
- •3 А д а н и е 3. Открыть рабочий лист "Пример 3".
- •3.13.Относительные и абсолютные ссылки на ячейки Файл Есо
- •Стиль ссылок а1
- •3Адание1. Открыть лист "Пример", заполнить пустые ячейки в шести таблицах.
- •3Адание3. Заполнить пустые клетки колонки l.
- •3Адание4. Открыть рабочий лист "Прайс". Заполнить пустые клетки таблицы результатами вычислений.
- •Файл Kvadrat 1
- •Файл Kvadrat 2
- •3.14. Команды пункта меню данные: "текст по столбцам", "сортировка", "итоги" Файл Sort
- •3Адание.Открыть рабочий лист "Список". Убедиться, что список в колонке в неудобен для оценки деятельности работников
- •1. Подсчитать среднегодовую температуру для всех городов с точностью до шестого знака после запятой.
- •2. Расположить названия городов мира по возрастанию среднегодовых температур.
- •Файл Маpk
- •Выделить одновременно: на рабочем листе "Задание" строку № 10, столбец н, блок ячеек а16:в19, блок ячеек f13:g16 и на рабочем листе "Маркировка" блок ячеек но32001:11032008.
- •Ввести во все выделенные ячейки число 7.
- •На листе "Задание" в ячейке в12 подсчитать сумму всех введённых чисел.
- •3.15. Вычисления с спользованием циклических ссылок Файл Itog
- •3.16. Построение графиков и поверхностей Файл Diagrama
- •Открыть рабочий лист "Задание 1".
- •По данным, приведённым в таблице на рабочем листе "Пример", построить графи со следующими параметрами:
- •3.17. Построение гистограмм с накоплением Файл Term
- •3.18. Создание и применение макросов Файл Makros
- •2.3. Проверить работу макроса ЧислаНомергруппы:
- •3Адание4. Записать макрос для последовательного введения в ячейки рабочего листа цветных ячеек.
- •3Адание5. Написать макрос для введения в ячейки рабочего листа таблицы с данными.
- •5.2. Проверить работу макроса БланкНомергруппы.
- •3Адание6.
- •6.1. Записать макрос для полной очистки рабочего листа "Макрос" - всего, что появится после выполнения четырёх ваших макросов.
- •3Адание7.Создать кнопку для запуска макроса ЧислаНомергруппы.
- •3Адание8. Создать кнопки для запуска всех макросов на рабочем листе "Макросы".
- •3Адание9.
- •3Адание10.
- •3Адание11.
- •3.19. Подпрограмма мастер функций Файл Master
- •3Адание1. Открыть рабочий лист "Пример". И ячейку f3 записать функцию для определения порядкового номера дня недели, дата которого записана в ячейку f4.
- •3Адание2. В ячейке f6 определить сумму взноса в месяц (грн) для погашения взятого кредита в размере 1 млн под месячную ставку, равную 0,75%, при ежемесячных платежах в течение 12 месяцев.
- •3Адание3.1. В ячейку f11 записать, число, которое будет Медианой - числом одновременно больше одной половины чисел и меньше второй половины из блока ячеек н12:f12.
- •3Адание3.2. В ячейке f14 подсчитать число перестановок из 1000 по 3.
- •3Адание3.4. (Контрольное). Открыть рабочий лист "Слово". Сосчитать, сколько раз каждое из слов записано в блок ячеек в16:135.
- •3Адание4.1. Открыть рабочий лист "Пример". В ячейку f16 записать количество символов, которое содержится в следующей фразе:
- •3Адание4.2. Открыть рабочий лист "Сцепить". В колонке f объединить данные колонок с,d,e так, как показано в следующей таблице:
- •3Адание6.3. (Контрольное). Подсчитать суммы зарплат работников 3-го отдела.
- •3.20. Математическая функция суммпроизв Файл Sumproiz
- •1.1. Подсчитать выторг каждого продавца. Результаты поместить в строке 15.
- •1.2. Подсчитать пять сумм:
- •3.21. Команда "частичная сумма"
- •1. Сервис V Мастер V Частичная сумма.
- •3.22. Логическая функция если Файл Nalog
- •Файл Footboll
- •3.23. Условное форматирование ячеек Файл Uslov
- •3Адание 1. На листе "Задание" сделайте так, чтобы:
- •3,24. Статистическая функция "счёту Файл Probel
- •3.25. Статистическая функция "частота" Файл Chast
- •3.26. Матема тическая функция мумнож
- •Файл Matriza
- •3.27. Функциягпр(впр)изкатегории "ссылки и ма ссивы"
- •3.28. Работа с базами данных Файл Fillr
- •3.29. Сводные таблицы Файл Svod
- •Порядок построения "Сводной таблицы"
- •Последовательность действий при выполнении задания:
- •2. Выбрать Формулы → Вычисляемое поле.
- •2. Выбрать Параметры поля.
- •3.30. Команда "подборпараметра" Файл Podbor
- •3.31. Финансовые функции Файл Finans
- •3 А д а н и с 4. На рабочем листе "Пример пз" разобрать решение следующей задачи:
- •3.32. Финансовые функции для расчета амортизации Файл Amort
- •3 А д а н и е 1. На рабочем листе "Расчёт" подготовлены
- •3.33. Команда "поиск решения"
- •Файл Фарби
- •Файл Gruz
- •Файл Kuxni
- •Файл grafik
- •Файл Capital
- •Файл Trans
- •Файл Nabor
- •Часть 1
- •Часть 2
3.31. Финансовые функции Файл Finans
В категории "Финансовые" существуют функции, которые предназначены для расчёта финансовых операций по кредитам, ссудам и займам. Расчёты по этим функциям основаны на концепции временной стоимости денег и предполагают неравноценность стоимости денег, относящихся к разным моментам времени. Используя эти функции, можно производить следующие расчёты:
там времени. Используя эти функции, можно производить следующие расчёты:
• определение наращенной суммы (будущей стоимости);
• определение наращенной суммы (будущей стоимости);
• определение начального значения (текущей стоимости); • определение срока платежа и процентной ставки;
• расчет периодических платежей, связанных с погашением займов.
Общая формула расчёта, которую Exel использует при вычислении финансовых аргументов, связанных с денежными потоками, имеет вид
где:
pmt - фиксированная периодическая сумма платежа;
п - общее число периодов выплат;
r - процентная ставка за один период;
type - равно 0 при выплате в конце периода или равно 1
при выплате в начале периода;
pv - текущая стоимость вклада, по которому начисляются
проценты;
fv - будущая стоимость вклада или серии фиксированных
платежей.
В файле Finans разобраны примеры с использованием финансовых функций БС; БЗРАСПИС; ПС.
Функция БС может быть применена при необходимости рассчитать:
-
Будущую стоимость вклада, сделанного один раз (рабочий лист "Пример БЗ-1").
-
Будущую стоимость серии периодических платежей (рабочий лист "Пример БЗ-2").
Синтаксис функции: =БС (норма, число периодов, выплаты, из, тип), где:
-
норма - процентная ставка за период;
-
число периодов - общее число периодов выплат за год;
-
выплаты – суммы выплат, производимые в каждый период;
-
из - общая сумма всех будущих платежей;
-
тип - это число 0 или 1, (нуль означает. что выплаты осуществляются в конце периода, единица означает, что выплаты осуществляются в начале периода).
З а д а н и е 1. Определить, какая сумма окажется на счёте, если 2000 грн положить на срок 5 лет под 12% годовых. Проценты начисляются каждые полгода.
Последовательность действий при выполнении задания:
Это задача нахождения будущей стоимости суммы вклада, по которому начисляются сложные проценты за определённое число периодов. Известна формула для данного случая:
fv =pv * (I + г)^ п,
где:
fv - будущая стоимость вклада;
pv - текущая стоимость вклада;
r - процентная ставка по вкладу;
п - общее число периодов начисления процентов.
РЕШЕНИЕ ТРАДИЦИОННЫМ МЕТОДОМ
Подставим в формулу значения и с помощью калькулятора вычислим:
fv =2000 • (1 + 0,12/2) ^ (5 • 2) = 3581,70 (грн).
РЕШЕНИЕ С ИСПОЛЬЗОВАНИЕМ МАСТЕРА ФУНКЦИЙ
1. Сделать активной ячейку D20.
2. Вызвать Мастер функций.
3. Выбрать категорию Финансовые.
4 .Выбрать функцию БС.
5 .В диалоговое окно ввести:
-
норма (г) = 12%/2;
-
число период (n) = 5 • 2;
-
окно выплаты пропустить, так как по условиям примера периодических платежей нет;
-
из (pv) = -2000 (отрицательное число, означающее суму вложенных денег).
Нажатие клавиши Готово запускает программу на вычисление.
В строке формул видно выражение для вычисления = БС(12%/2; 5*2; ; -2000). Результат: 3581,70 грн.
Пояснение: Результат вычислений по двум вариантам оказался одинаков. В чём преимущество расчёта с использованием электронной таблицы? В том, что, имея уже введённую в ячейку формулу, можно в дальнейшем выполнять вычисления для любых начальных условий, затрачивая на это минимум времени.
З а д а н и е 2. На рабочем листе «Пример БЗ-2» решить следующую задачу:
Ежегодно, в течение 4 лет, предприниматель собирается вносить в банк по 3000 грн. Сколько денег окажется на счёте в конце 4-го года, если есть два варианта вложения средств:
-
в начале каждого года под 26% годовых,
-
в конце каждого года под 38% годовых?
Последовательность действий при выполнении задания:
Для первого варианта: в ячейку С19 введём формулу Формула для расчёта
=БС(26%;4;-3000;;1)
=БС(норма; число периодов; выплата; ; тип)
Результат: 22105,35 грн.
Для второго варианта: в ячейку С20 введём формулу Формула для расчёта:
=БС(38%;4;-3000;;0)
=БС(норма; число периодов; выплата; ; тип)
Результат: 20737,42 грн.
Пояснение: Оказалось, что выгоднее вносить платежи в начале периода. В этом случае, при меньшей процентной cтавке, можно получить более высокий доход по процентам.
З а д а н и е 3. На рабочем листе «Задачи БЗ» решить задачи, условия которых приведены ниже:
З а д а ч а 1.
Сколько денег будет на счету в конце года , если Вы можете вложить 1000 грн под 6% годовых? В начале каждого месяца вы собираетесь вкладывать по 100 грн.
О т в е т: 2301,40 грн.
З а д а ч а 2.
Рассчитать, какая сума будет на счете, если сумма размером в 5000 грн размещена под 12% годовых на 3 года, а проценты начисляются каждые полгода.
О т в е т: 7092,60 грн.
З а д а ч а 3.
По вкладу размером в 2000 грн начисляются 10% годовых. Рассчитать, какая сумма будет на счете через 5 лет, если проценты начисляются ежемесячно.
О т в е т: 3290,62 грн.
З а д а ч а 4.
На счет вносятся платежи по 200 грн в начале каждого месяца. Рассчитать, какая сумма окажется на счете через 4 года при процентной ставке 13,5% годовых.
О т в е т: 12779,34 грн.
З а д а ч а 5.
Просчитать будущие значение счета для условия п.4, если платежи вносятся в конце каждого месяца.
О т в е т: 12637,17 грн.
Функция БЗРАСПИС используется для расчета будущего значения инвестиции, если процентная ставка меняется с течением времени.
Синтаксис функции: = БЗРАСПИС (первичных; план), где :
• первичных – текущее значение вклада;
• план – массив переменных процентных ставок.
Задание 1. На рабочем листе "Пример БЗРАСПИС-1" решить задачу:
По облигации номиналом в 1000 грн, выпущенной на 6 лет, предусмотрен следующий порядок начислений процентов: в первый год - 10%, в два последующих года - 20%, в последние три года -25%. Необходимо рассчитать будущую стоимость облигации.
Последовательность действий при выполнении задания:
1. Составляется расчётная таблица:
|
|
|
C |
D |
E |
F |
G |
H |
14 |
Срок |
1-й год |
2-й год |
3-й год |
4-й год |
5-й год |
6-й год |
|
15 |
Процентная ставка |
10% |
20% |
20% |
25% |
25% |
25% |
|
16 |
|
|
|
|
|
|
|
|
17 |
Решение Eхcel |
|
|
3 094 грн |
|
|
2. В ячейку С17 записывается выражение:
=БЗРАСПИС(1000;D15:H15), по которому программа находит искомое значение.
З а д а н и е2. На рабочем листе «Пример БЗРАСПИС-2» решить задачу.
Исходя из плана начисления процентов, приведенного в таблице, рассчитать номинал облигации, если известно, что её будущая стоимость составила 1546,88 грн.
Последовательность действий при выполнении задания:
1. По данным условия задачи в блоке ячеек В6:I7 составляется таблица:
|
|
|
D |
E |
F |
G |
H |
I |
12 |
Срок |
|
1-й год |
2-й год |
3-й год |
4-й год |
5-й год |
6-й год |
13 |
Процентная |
10% |
20% |
20% |
25% |
25% |
25% |
|
ставка |
|
|
|
|
|
|
2. В ячейку Е18 записывается Формула = БЗРАСПИС(D18;D13:I13).
3. Вызывается функция Подбор параметра.
4. В диалоговое окно записывается: установить в ячейке Е18 значение
1546,88, изменяя значение ячейки D18.
5.ОК.
Программа находит решение: номинал облигации должен быть равен 500 грн.
Задание 3. На рабочем листе "Задачи БЗРАСПИС" решить задачи, условия которых приведены ниже:
Задача 1. Рассчитать будущую СТОИМОСТЬ облигации номиналом 300 грн, выпущенной на 5 лет, если предусмотрен следующий порядок начисления процентов: в первые два года - 13,5% годовых, в следующие два года - 15 % и в последний год - 20%.
3 а д а ч а 2. Ожидается, что будущая стоимость инвестиции размером 1500 грн к концу 4-го года составит 3000 грн. При этом за первый год доходность составит 15%, за второй год - 17%, за четвёртый год – 23%. Требуется рассчитать доходность инвестиций за третий год.
Указание: решить задачу, используя функцию Подбор параметра.
Функция ПС предназначена для расчета текущей стоимости как единой суммы вклада, а также будущих фиксированных платежей. Этот расчет является обратным к расчетам по функции БЗ.
Синтаксис =ПС (норма, кпер, выплата, бс, тип).
Функция может быть полезна при определении того, какую сумму необходимо положить на счёт сегодня, чтобы в конце периода она достигла заданного значения. Функция применяется при расчёте денежных потоков равной величины и равных интервалов между операциями.
В прикладных задачах используется понятие текущей стоимости будущих доходов и расходов. Это понятие базируется на том, что на начальный момент времени сумма денег, полученная в будущем, имеет меньшую стоимость, чем её эквивалент, полученный в начальный момент времени. Согласно концепции временной стоимости денег расходы и доходы, не относящиеся к одному моменту времени, можно сопоставить путём приведения к одному моменту, т.е. путём дисконтирования, Текущая стоимость получается как результат приведения будущих доходов и расходов к начальному моменту времени.