Упражнение 3. Вычислите платежи по процентам за первый месяц от 3 летнего займа в 800000 тенге из расчета 10% годовых.
-
Перейдите на Лист3, переименуйте ее на Задача3.
-
Создайте таблицу на основе условии задачи
-
Для вычисления выплат по процентам используйте функция ПРПЛТ.
Упражнение 4. Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды 100000$ на срок 5 лет при годовой ставке 2%.
-
Перейдите на Лист4, переименуйте ее на Задача4.
-
Создайте таблицу 3
|
А
|
В
|
С
|
D
|
Е
|
1
|
Погашение долга
|
2
|
Ставка
|
2%
|
|
|
|
3
|
Срок
|
5
|
|
|
|
4
|
Ежегодная выплата
|
|
|
|
|
5
|
Размер ссуды
|
100000
|
|
|
|
6
|
Год
|
Сумма займа на начало года
|
Плата по процентам
|
Основная плата
|
Остаток долга
|
7
|
1
|
100000
|
|
|
|
8
|
2
|
|
|
|
|
9
|
3
|
|
|
|
|
10
|
4
|
|
|
|
|
11
|
5
|
|
|
|
| -
Для расчета ежегодной выплаты используйте: =ПЛТ(В3;В4;В5)
-
Расчет платы по процентам выполняется по формуле: =ПРПЛТ($B$2;A7;$B$3;$B$5), протяните это формулу и для остальных ячеек
-
Сумма платежи по процентам за первый год вычисляется по формуле: =ОСПЛТ($B$2;A7;$B$3;$B$5), протяните это формулу и для остальных ячеек
-
Остаток долга = предыдущий долг - основная плата, т.е вы должны набрать =B7+D7 (+ потому что основная плата отрицательное число), скопируйте формулу для остальных ячеек
-
в В8 введите следующую формулу: =Е7
-
В остатке долга на 5-й год у вас должно получится значение 0, т.к. происходит полное погашение долга и вы ничего не должны
Упражнение 5. Предположим вы купили за 6000 тенге компьютер, который имеет срок эксплуатации 5 лет, после чего оценивается в 1000 тенге. Определите снижение стоимости компьютера для каждого года эксплуатации.
Решение:Снижение стоимости компьютера для каждого года эксплуатации вычисляется по формуле АПЛ(нач_стоимость; остаточная_стоимость; время_эксплуатации).
Упражнение 6. Предположим вы купили за 6000 тенге. компьютер, который имеет срок эксплуатации 5 лет, после чего оценивается в 1000 тенге. Определите амортизацию компьютера за первый и пятый год эксплуатации.
Решение: Снижение стоимости компьютера за первый год эксплуатации вычисляется по формуле АСЧ(нач_стоимость; остаточная_стоимость; время_эксплуатации; период).
Задания для самостоятельной работы:
-
Вы собираетесь вкладывать по В тыс. тенге в течение N лет при годовой ставке П%. Сколько денег будет на счету через N лет?
Вариант |
1
|
2
|
3
|
4
|
5
|
В
|
52
|
53
|
55
|
60
|
54
|
П
|
10
|
10.5
|
11
|
12
|
10.9
|
N
|
5
|
6
|
8
|
10
|
9
| -
Определите платежи по процентам по займу в Т тыс. тенге, выданному на N лет под П% годовых, за К лет, если проценты начисляются квартально.
Вариант |
1
|
2
|
3
|
4
|
5
|
Т
|
125
|
350
|
420
|
355
|
425
|
П
|
8
|
9
|
11
|
10
|
7
|
N
|
5
|
6
|
7
|
8
|
5
|
К
|
3
|
4
|
5
|
6
|
2
| -
Определите ежемесячные выплаты по займу в Т тыс. тенге, взятому на К месяцев под П% годовых.
Вариант |
1
|
2
|
3
|
4
|
5
|
Т
|
25
|
30
|
40
|
35
|
42
|
П
|
6
|
8
|
9
|
10
|
7
|
К
|
5
|
6
|
7
|
8
|
5
| -
Вычислить n-годичную ипотечную ссуду покупки квартиры за Р тенге с годовой ставкой i% и начальным взносом А%. Сделать расчет для ежемесячных и ежегодных выплат для исходных данных, представленных в следующей таблице:
Варианты
|
1
|
2
|
3
|
4
|
5
|
n
|
7
|
8
|
9
|
10
|
11
|
Р
|
170000
|
200000
|
220000
|
300000
|
350000
|
i
|
5
|
6
|
7
|
8
|
9
|
А
|
10
|
10
|
20
|
20
|
15
| -
Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды Р тенге под годовую ставку i % на срок n лет.
Вариант
|
1
|
2
|
3
|
4
|
5
|
n
|
7
|
8
|
9
|
10
|
11
|
P
|
170000
|
200000
|
220000
|
300000
|
350000
|
i
|
5
|
6
|
7
|
8
|
9
|
Контрольные вопросы:
-
Какие финансовые функции для расчета платежей вы знаете?
-
Что означает функция ОСПЛТ?
-
Что такое амортизация?
-
Чем отличаются функции АСЧ от АПЛ?
-
Что означает функция ПРПЛТ?
Лабораторная работа № 17
Тема: «Сводные таблицы. Консолидация данных»
Цель работы: сформировать умение строить сводные таблицы, использовать консолидацию данных.
Основные понятия
Сводная таблица позволяет анализировать, обобщать, манипулировать данными, содержащихся в больших списках, базах данных, рабочих книгах, таблицах или других коллекциях данных. Сводные таблицы предлагают гибкий и интуитивный способ анализа данных. Сводная таблица связана с исходными данными. Однако можно изменить форматирование и произвести вычисления.
Сводная таблица нужна, например, для того чтобы узнать насколько хорошо продается тот или иной товар в каждом регионе. Сводную таблицу можно создать из нескольких источников. Чаще всего она создается на основе списка или базы данных Excel.
Упражнение 1. Создать сводную таблицу для отчетов по продаже компьютеров сети из трех магазинов подводящую сумму продаж по месяцам.
-
Создать заданную таблицу в Excel на Лист1 и переименуйте на Свод_табл. Для ускорения работы использовать копирование и автозаполнения данных.
-
Вызовите Мастер Сводных таблиц, выполнив команду ДанныеСводная таблица,
-
В появившимся окне установите переключатель в положение в списке или базе данных Microsoft Excel, щелкните по кнопке Далее.
-
Укажите диапазон, содержащий исходные данные (выделить всю таблицу с данными), щелкните по кнопке Далее, в следующим окне выберите переключатель в положение новый лист.
-
Кнопка Макет (для Excel 2000); в появившемся диалоговом окне, в котором создается структура сводной таблицы, выбрать поле, по которому будут подводиться итоги сводных таблиц, и перетащить кнопку, соответствующую этому полю, в область Данные (перетащить кнопку Стоимость в область Данные). Двойной щелчок по кнопке Стоимость в области Данные приведет к отображению диалогового окна Вычисление поля сводной таблицы, которое позволяет установить операцию, на основе которой будут подводиться итоги (выбрать операцию Сумма).
-
Выбрать поля, которые будут образовывать строки сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Строка. Перетащить кнопку Магазин в область Строка.
-
Выбрать поля, которые будут образовывать столбцы сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Столбец. Перетащить кнопку Месяц в область Столбец.
-
Получена сводная таблица, в которой рядом с названиями полей есть кнопки, открывающие списки. С их помощью можно производить выбор отображаемых данных в таблице.
-
Для перерасчета сводной таблицы, если изменились данные, на основе которых строилась эта сводная таблица: выделить ее, команда Данные / Обновить данные.
-
Проиллюстрируйте данные полученной сводной таблицы на графике.
Упражнение 2. Создание консолидации данных по расположению и по категориям
-
Переидите на Лист2 и переименуйте на Консол
-
Создайте на листе Консол. таблицу расчета заработной платы (за январь и за февраль).
-
Вычислите сумму подоходного налога и сумму к выдаче
-
Выполните консолидацию по расположению, для этого: Установите курсор в первую ячейку области, где будет располагаться консолидированная таблица. (Например в А10)
-
Выполните команду Данные, Консолидация.
-
В диалоговом окне << Консолидация>> выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца,
-
Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек A2:D7.
-
Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.
-
Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек F2:I7.
-
Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.
-
Нажмите кнопку < ОК>.
-
Скопируйте обе таблицы с листа Консол.распол. на лист Консол.категор. и измените вторую таблицу (за февраль): вставьте новый столбец Премия и заполнить его, добавьте строку с фамилией Дятлов.
-
Сделайте консолидацию данных по категориям:
-
Установите курсор в первую ячейку области, где будет располагаться консолидированная таблица.
-
Выполните команду Данные, Консолидация.
-
В диалоговом окне << Консолидация>> выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца.
-
Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек A2:D7.
-
Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.
-
Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек F2:J8.
-
Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.
-
Нажмите кнопку < ОК>.
Достарыңызбен бөлісу: |
