- •«Тюменская государственная академия
- •Лабораторная работа №1 Тема: Основные приемы работы. Создание и оформление таблиц.
- •1. Общие приёмы работы
- •5. Объединение/разбиение ячеек.
- •8.2. Выделение крайних значений
- •8.3. Форматирование с использованием гистограммы
- •8.4. Форматирование с использованием трехцветной шкалы
- •Лабораторная работа №2
- •Тема: Использование формул. Операторы. Математические расчёты.
- •Возможные ошибки
- •Лабораторная работа №3 Тема: Адресация ячеек. Относительные, абсолютные и смешанные ссылки. Использование логических выражений
- •Лабораторная работа №4 Тема: Создание и редактирование графиков и диаграмм. Поверхности
- •Изменение типа диаграммы. После создания можно изменить тип и вид диаграммы.
- •Изменение источника данных. После создания диаграммы можно изменить диапазон данных, представленных на диаграмме.
- •Выбор стиля оформления элемента. Стиль оформления элемента опреде-ляет цвет и эффекты заливки элемента, параметры линии контура и эффекты, примененные при оформлении элемента.
- •Заливка элементов диаграммы. Заливку элемента можно установить само-стоятельно, независимо от выбранного стиля элемента.
- •Восстановление параметров оформления. Можно отказаться от всех параметров оформления элемента, назначенных после применения стиля к диаграмме.
- •Лабораторная работа №5 Тема: Функции условия
- •3.2. Начисление налогов с помощью условий
- •Практическая работа № 6 Тема: Решение задачи табулирования функции. Построение и редактирование диаграммы
- •Лабораторная работа №7 Тема: Использование средства Подбор параметров
- •2.1. Кредит на покупку квартиры
- •2.2. Вычисление суммы кредита
- •2.3. Подбор параметра для вычисления срока погашения кредита
- •2.4. Подбор параметра для вычисления процентной ставки
- •3.1. Подбор параметра для вычисления начальной суммы депозита
- •3.2. Подбор параметра для вычисления срока депозита
- •3.3. Подбор параметра для вычисления процентной ставки
- •4. Подбор параметра для оптимизации продажи театральных билетов
- •4.1. Вычисление количества билетов для детей
- •4.2. Вычисление количества билетов для взрослых
- •Лабораторная работа №8 Таблицы подстановки
- •4. Использование таблиц подстановки для подсчета дивидендов
- •5. Возможные ошибки при работе с Таблицами подстановки.
- •Лабораторная работа №9 Тема: Поиск решения
- •3. Расчёты по банковским кредитам
- •Лабораторная работа №10 Тема: Сценарии
- •1. Создание и работа со сценариями
- •2. Самостоятельная работа. Использование сценариев для прогноза объемов продаж
- •3. Защита сценариев от изменений
- •Лабораторная работа №11 Тема: Функции для работы с матрицами
- •Лабораторная работа №12 Тема: Работа с датами. Объединение ячеек с текстовыми данными. Создание пользовательских форматов
- •1.1. Автоматически обновляемая текущая дата. Для вставки текущей автоматически обновляемой даты используется функция сегодня () (рис. 1).
- •1.4. День недели произвольной даты
- •4.1. Преобразование регистра текста. Для преобразования регистра текста используются три функции: прописн, пропнач, строч.
- •4. Назначение макроса графическому объекту или элементу управления.
- •5. Запуск макроса.
- •7. Удаление макроса.
- •Лабораторная работа №14 Контрольная работа. Статистические функции Excel
- •Контрольные вопросы
- •Литература
- •Содержание
2.1. Кредит на покупку квартиры
Создайте таблицу, содержащую следующие данные (рис. 4):
|
А |
В |
1 |
Кредит на покупку квартиры | |
2 |
Сумма кредита |
2 000 000р. |
3 |
Срок кредита (месяцы) |
480 |
4 |
Процентная ставка |
7,00% |
5 |
Ежемесячный платёж |
=ПЛТ(Ставка;Кпер;Пс) |
Рис. 4. Таблица данных для расчёта кредита
В ячейку В5 вводится финансовая функция ПЛТ, вычисляющая платёж. В этой функции аргумент Ставка задает ежемесячную процентную ставку по кредиту (поэтому в нашей формуле этот аргумент равен В4/12), аргумент Кпер — количество периодов погашения кредита (ячейка В3), аргумент Пс — сумма кредита (ячейка В2).
2.2. Вычисление суммы кредита
Задача: кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 руб. Какова максимальная сумма кредита?
1) В ячейку В3 введите число 180 (15 лет, умноженных на 12 месяцев). В ячейку В4 введите 5,75%.
2) В диалоговом окне Подбор параметра в поле ввода Установить в ячейке введите В5.
3) В поле ввода Значение введите число -11 000. В поле Значение вводится отрицательное число, что указывает на то, что ежемесячные платежи заемщик отдает, а не получает (как при банковских вкладах)
4) В поле ввода Изменяя значение ячейки введите В2 или щелкните на ячейке В2.
5) Щелкните на кнопке ОК, в открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: максимальная сумма кредита составит 1 324 647 руб.
2.3. Подбор параметра для вычисления срока погашения кредита
Задача: каков срок погашения кредита, если сумма кредита равна 2 250 000 руб., процентная ставка составляет 7% годовых, а ежемесячные платежи равны 14 230 руб.?
1) В ячейку В2 введите число 2 250 000, в ячейку В4 введите 7%.
2) Вызовите окно Подбора параметра.
3) В поле ввода Установить в ячейке введите В5 или щелкните на ячейке В5.
4) В поле ввода Значение введите число -14 230.
5) В поле ввода Изменяя значение ячейки введите В3 или щелкните на ячейке В3.
6) Щелкните на кнопке ОК. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: кредит берется на 439 месяцев (примерно 36,6 года).
2.4. Подбор параметра для вычисления процентной ставки
Задача: кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?
1) В ячейку В2 введите число 8 500 000, а в ячейку В3 число 360.
2) В поле ввода Установить в ячейке щёлкните по ячейке В5.
3) В поле ввода Значение введите число -52250.
4) В поле ввода Изменяя значение ячейки щёлкните по ячейке В3.
Ответ: можно согласиться на процентную ставку в размере 6,23%.
3. Расчеты по депозитам. Создайте таблицу, содержащую следующие данные: начальная сумма депозита – 250 000 р.; срок депозита (месяцы) – 48; процентная ставка – 9,15%. Конечную сумму вычисляет функция БС(Ставка;Кпер;;-Пс), где аргумент Ставка задает ежемесячную процентную ставку депозита (поэтому в нашей формуле этот аргумент равен процентная ставка/12), аргумент Кпер — срок хранения депозита (48), аргумент Пс — начальная сумма депозита (250 000р.). Эта функция подобна функции ПЛТ из предыдущего примера, но вычисляет не ежемесячные платежи, а конечную накопленную сумму.
Подбор параметров производится аналогично предыдущему упражнению.