Цель занятия: освоить приемы работы с функциями Excel, предназначенными для расчета амортизации и способами организации элементов пользовательского интерфейса на рабочих листах.
План занятия:
Создать на листе Excel исходную таблицу.
Заполнить расчетный блок формулами и настроить ячейки для выбора данных в соответствии с указаниями, приведенными ниже.
Самостоятельно создать расчетные блоки для функций расчета амортизации.
Построить динамический график для сравнения методов, применяемых в исследуемых функциях.
Порядок выполнения работы
Создайте новый документ Exсel и в нем заполните приведенную ниже таблицу, начиная с ячейки А1.
|
Инвентарный номер |
Наименование |
Начальная стоимость |
Годовая норма амортизации |
Дата приобретения |
Дата постановки на учет |
Остаточная стоимость |
|
10010 |
ПК |
17 770р. |
20,0% |
30.09.99 |
01.10.99 |
0 |
|
20010 |
ПК |
18 700р. |
20,0% |
01.04.96 |
01.10.00 |
0 |
|
12000 |
ПК |
2 300 000р. |
10,0% |
01.05.96 |
01.09.96 |
0 |
|
20001 |
Здание |
120 000р. |
1,0% |
09.05.00 |
10.05.00 |
0 |
|
25001 |
Сооружение |
125 000р. |
5,0% |
01.04.00 |
01.06.00 |
0 |
|
56789 |
Машина |
250 000р. |
10,0% |
01.05.00 |
01.05.00 |
0 |
Начиная с ячейки А9, создайте таблицу для ввода параметров функции АМР:
|
Инвентарный номер |
|
|
Наименование |
|
|
Стоимость |
|
|
Ликвидационная стоимость |
|
|
Время амортизации |
|
|
АМР |
В ячейке В9 создадим раскрывающийся список для ввода данных об инвентарном номере. Для этого необходимо выделить ячейку В9 и выбрать команду меню Данные – Проверка. Указать тип данных – Список. Источник – блок ячеек А2-А7, содержащий инвентарные номера (см. рис).
Для остальных ячеек ввести расчетные формулы:
В10: ПРОСМОТР(B9;A2:A7;B2:B7) – Возвращает значение из строки, из столбца или из массива. В данном случае она показывает название компонента с данным инвентарным номером.
В11: ПРОСМОТР(B9;A2:A7;C2:C7) – возвращает цену выбранного компонента.
В12: ПРОСМОТР(B9;A2:A7;G2:G7) – возвращает ликвидационную стоимость из исходной таблицы
В13: 1/ПРОСМОТР(B9;A2:A7;D2:D7) – возвращает величину, обратную норме амортизации. Эта величина используется для вычисления значения функции АМР.
В14: АМР(B11;B12;B13) – вычисляет амортизацию.
Проверьте работу расчетного блока, выбирая в ячейке В9 различные инвентарные номера.
Создадим расчетный блок для вычисления функции АМОРУВ.
Объедините ячейки А18 и В18 и введите в них заголовок: Функции АМОРУВ и АМОРУМ.
Создайте заготовку таблицы, начиная с ячейки А19.
|
Инвентарный номер |
|
|
Наименование |
|
|
Стоимость |
|
|
Ливидационная стоимость |
|
|
Дата приобретения |
|
|
Первый период |
|
|
Норма амортизации |
|
|
Базис |
|
|
Период расчета |
В ячейке В19 создадим раскрывающийся список для ввода данных об инвентарном номере. Для этого необходимо выделить ячейку В19 и выбрать команду меню Данные – Проверка. Указать тип данных – Список. Источник – блок ячеек А2-А7, содержащий инвентарные номера (см. рис).
В остальные ячейки поместите расчетные формулы:
В20: ПРОСМОТР(B19;A2:A7;B2:B7)
В21: ПРОСМОТР(B19;A2:A7;C2:C7)
В22: ПРОСМОТР(B19;A2:A7;G2:G7)
В23: ПРОСМОТР(B19;A2:A7;E2:E7)
В24: КОНМЕСЯЦА(B23;12-МЕСЯЦ(B23)) – Функция МЕСЯЦ возвращает месяц в дате, заданной в числовом формате. Месяц возвращается как целое число в диапазоне от 1 (январь) до 12 (декабрь). Возвращает числовой формат последнего дня месяца, отстоящего на указанное количество месяцев от начальной даты. Функция КОНМЕСЯЦА используется для вычисления даты вступления в силу или даты платежа, которая приходится на конец месяца.
В25: ПРОСМОТР(B19;A2:A7;D2:D7)
В26: Создадим раскрывающийся список. Выберите команду меню Данные – Проверка, укажите тип данных – Список, источник – значения 0, 1, 3, 4.
Создадим таблицу для вычисления амортизации по периодам для искомых функций, начиная с ячейки А27. Таблица будет иметь вид:
|
Период расчета |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
Аморув |
||||||||
|
Аморум |
В строку АМОРУВ добавьте формулу
АМОРУВ($B$21;$B$23;$B$24;$B$22;B27;$B$25;$B$26)
И размножьте ее по всем ячейкам строки.
В строку АМОРУМ добавьте формулу
АМОРУМ($B$21;$B23;$B$24;$B$22;B27;$B$25;$B$26)
И размножьте ее по всем ячейкам строки.
Проверьте работу расчетного блока, выбирая в ячейке В19 различные инвентарные номера.
Создадим диаграмму, отражающую сравнительные результаты применения функций расчета амортизации.
Нажмите кнопку добавления диаграммы.
Выберите тип: Обычная гистограмма.
Выберите вкладку РЯД.
Нажмите кнопку ДОБАВИТЬ. В поле ЗНАЧЕНИЯ укажите ряд значений функции АМОРУВ. В поле ПОДПИСИ ПО ОСИ Х укажите ряд номеров периодов (1, 2, …).
Нажмите кнопку ДОБАВИТЬ. В поле ЗНАЧЕНИЯ укажите ряд значений функции АМОРУМ. В поле ПОДПИСИ ПО ОСИ Х укажите ряд номеров периодов (1, 2, …).
Нажмите кнопку ДАЛЕЕ и введите подписи по осям. Выберите размещение диаграммы на текущем листе.
Разместите диаграмму таким образом, чтобы она была видна одновременно с расчетными данными.
Проверьте автоматическое изменение диаграммы при выборе в ячейке В19 различных инвентарных номеров.
Сделайте вывод о различии алгоритмов функций АМОРУВ и АМОРУМ.
Контрольные вопросы:
Как использовать команду "Проверка"?
Каков синтаксис функции "Просмотр"?
Какие функции служат для вычисления амортизации и каков их синтаксис?