Упражнение №1
Содержание задачи: оформить и заполнить бланк «Накладная».
Откройте новый рабочий лист, создайте бланк, структура которого представлена на рисунке 1.11.
Рисунок 1.11 – Структура бланка «Накладная»
Лабораторная работа №2. Расчет покупки товара в кредит
Экономическое содержание задачи: пусть вы хотите приобрести какой-то товар в кредит, или предлагаете товары населению с рассрочкой платежа. Важно быстро рассчитать, какие суммы должны ежемесячно выплачиваться и какова общая стоимость товара с учетом процентов. Исходными данными для расчета являются стоимость товара, процент первоначального взноса, годовая ставка кредита, дата оформления кредита и срок кредитования в месяцах, а нужно рассчитать дату последней выплаты, сумму выплаченных процентов и общую сумму покупки.
Введите в ячейки первого столбца таблицы названия ячеек, а также заполните произвольными значениями ячейки с первой до пятой строки второго столбца. В столбце С будут выводиться даты погашения частей кредита. Для того, чтобы дата отображалась правильно, необходимо задать формат ячейки «Дата». В столбце D – выплачиваемые суммы. Структура таблицы представлена на рисунке 1.12.
Рисунок 1.12 – Заголовки и исходные данные
В ячейки со знаком «?» вписываем формулы, приведенные в таблице.
Вначале введем самые простые формулы. Чтобы узнать дату последней выплаты, нужно добавить к дате оформления кредита количество месяцев, на которые выдан кредит. В таблице 1.7 приведены формулы для расчета данной задачи.
Таблица 1.7 – Формулы для реализации задачи
Ячейки |
Формулы |
Область копирования |
В7 |
=ДАТАМЕС(В4;В5) |
- |
В8 |
=СУММ(D2:D19*)-B1*(1-B2) |
- |
B9 |
=B1+B8 |
- |
С2 |
=ЕСЛИ(СТРОКА()-1<=$B$5;ДАТАМЕС($B$4;СТРОКА()-1);"") |
С3 – С19 |
D2 |
=ЕСЛИ(СТРОКА()-1<=$B$5;$B$1*(1-$B$2)/$B$5*(1+(СТРОКА()-1)*$B$3/12);"") |
D3-D19 |
В данной задаче мы будем использовать функцию ДАТАМЕС, для работы которой необходимо, чтобы был установлен пакет анализа. Для его установки нажмите на кнопку Office и нажмите на клавишу Параметры Excel, выберете пункт Надстройки и нажмите клавишу Перейти. В открывшемся окне поставьте галочку напротив пункта Пакет анализа и нажмите OK. Если пакет анализа не установлен, то можно ввести в ячейку В7 следующую формулу:
=ДАТА(ГОД(В4);МЕСЯЦ(В4)+В5;1)
Значение ячейки В8 представляет собой сумму всех выплат, которые будут рассчитаны и помещены в столбец D из которой следует вычесть сумму кредита. А эта сумма вычисляется как разность между стоимостью товара и первоначальным взносом.
Общая сумма складывается из стоимости товара и выплаченных процентов.
Далее нужно заполнить ячейки в столбцах C и D. Но вначале поясним используемый при расчетах алгоритм. Считается, что кредит возвращается в течение всего срока равными долями. Каждый месяц погашается часть кредита, и вместе с нею выплачиваются проценты за пользование этой частью. В первый месяц возвращается часть суммы с процентами за пользование кредитом в течение одного месяца. Во втором месяце процент больше, так как пользование кредитом уже два месяца.
Рассмотрим, какие вычисления выполняются при расчете даты. Функция СТРОКА() возвращает номер текущей строки, то есть 2 для ячейки С2, и выражение СТРОКА()-1 позволяет определить порядковый номер месяца. Если номер месяца меньше или равен сроку кредитования, выводится дата, а в противном случае ячейка будет пуста.
Если вы работаете без установленного пакета анализа, в ячейку С2 введите формулу, а затем скопируйте ее в диапазон С3:С10
=ЕСЛИ(СТРОКА()-1<=$B$5;ДАТА(ГОД($B$4);МЕСЯЦ($B$4)+ СТРОКА()-1;1);””)
Как и для даты, сумма выводится только для месяцев погашения кредита, а для следующих месяцев выводятся пустые значения. Выражение $B$1*(1-$B$2) вычисляет сумму кредита, а разделив ее на содержимое ячейки В5, можно получить месячную долю погашения. Выражение $B$3/12 определяет месячный процент кредита, а с помощью выражения 1+(СТРОКА()-1)*$B$3/12) вычисляются проценты, выплачиваемые именно в этом месяце, они добавляются к погашаемой части кредита. В итоге мы получаем сумму, которую необходимо выплачивать в конкретном месяце.
Использование условий и применение в вычислениях номера строки помогает создавать достаточно сложные документы, поэтому хорошо разберитесь с принципами работы описанной таблицы. Они пригодятся при выполнении следующих лабораторных работ.