ПРАКТИЧЕСКАЯ РАБОТА №3

Рассмотрим полезные приемы работы с Excel, которые можно использовать в разных случаях. . Допустим, вы хо­тите приобрести какой-то товар в кредит, или предлагаете товары населению с рассрочкой платежа. Важно быстро рассчитать, какие суммы должны ежемесяч­но выплачиваться и какова общая стоимость товара, с учетом процентов.

Исходными данными для расчета являются стоимость товара, процент первоначального взноса, годовая ставка кредита, дата оформления кредита и срок кредитования в месяцах. Необходимо рассчитать дату последней выплаты, сумму выплаченных процентов и общую сумму покупки. Введите в ячейки первого столбца таблицы на­звания ячеек, а также заполните произвольными значениями ячейки с первой до пятой строки второго столбца. В третьем столбце будут выводиться даты погашения частей кредита, а в четвертом - выплачиваемые суммы. Введите заголовки этих столбцов в первой строке (Рис. 3.1). Чтобы таблица выглядела лучше, выбе­рите наиболее подходящие форматы для дат, процентов. и денежных величин.

Вначале введем самые простые формулы. Чтобы узнать дату последней вы­платы, нужно добавить к дате оформления кредита количество месяцев, на кото­рые выдан кредит. Введите в ячейку B7формулу =ДАТАМЕС(В4;В5). Чтобы рабо­тала функция ДАТАМЕС, должен быть установлен пакет анализа. Для установки пакета анализа нужно выбрать команду Сервис → Надстройки и установить в по­явившемся диалоге флажок рядом с названием Пакет анализа. В случае, когда данного названия нет в диалоге, нужно выполнить дополнительную установку Excel, чтобы добавить в систему нужную надстройку. Если вы не можете или не хотите установить пакет, введите в ячейку В7формулу =ДАТА(ГОД(В4);МЕСЯЦ(В4)+В5;1). Для простоты мы вычисляем первое число месяца, так как нужен только месяц, а не дата.

В ячейку В8 введите формулу =CУMM(D2:D61)-B1*(1-B2). То есть, из суммы всех выплат, которые будут рассчитаны и помещены в четвертый столбец, нужно вычесть сумму кредита. А эта сумма вычисляется как разница между стоимостью товара и первоначальным взносом. В ячейку В9 введите формулу =В1+В8, то есть общая сумма складывается из стоимости товара и выплаченных процентов.

Далее нужно заполнить ячейки в столбцах С и D, но вначале поясним ис­пользуемый при расчетах алгоритм. Считается, что кредит возвращается в тече­ние всего срока равными долями. Каждый месяц погашается часть кредита, и вместе с нею выплачиваются проценты за пользование этой частью. В первый месяц возвращается часть суммы с процентами за пользование кредитом в тече­ние одного месяца. Во втором месяце процент больше, так как пользовались кре­дитом уже два месяца. С каждым следующим месяцем проценты вырастают, по­этому возвращать нужно все больше и больше.

Вначале нужно вывести месяцы выплат по кредиту. Если вы работаете с ус­тановленным пакетом анализа, введите в ячейку С2 формулу

=ЕСЛИ(СТРОКА()-1<=$В$5;ДАТАМЕС($В$4;СТРОКА()-1);"")

Рассмотрим, какие вычисления выполняются с помощью данной формулы. Функция СТРОКА() возвращает номер текущей строки, то есть 2 для ячейки С2, и выражение CTP0KA()-1позволяет определить порядковый номер месяца. Ес­ли номер месяца меньше или равен сроку кредитования, выводится дата, а в противном случае ячейка будет пуста. Далее вы поймете, зачем используется ус­ловие, а не просто выводится дата. Для вычисления очередного месяца исполь­зуется почти такая же формула, что и для определения даты последней выпла­ты, но добавляется не общее количество месяцев, а порядковый номер месяца. В итоге, в ячейке появится дата первого месяца погашения кредита. Конечно, нужно выбрать соответствующий формат, чтобы дата выводилась как дата, а не как число.

Если вы работаете без установленного пакета анализа, все рассуждения точ­но такие же, но функция ДАТАМЕСнеработает, поэтому введите в ячейку С2 формулу

=ЕСЛИ(СТРОКА()-1<=$В$5;

ДАТА(ГОД($В$4);МЕСЯЦ($В$4) +СТРОКА()-1;1);"")

Далее нужно вычислить сумму, возвращаемую в каждом месяце. Введите в ячейку D2 формулу

=ЕСЛИ(СТРОКА()-1< =$В$5;

$В$1*(1-$В$2)/$В$5*(1+(СТРОКА()-1)*$В$3/12);"")

Как и дата, сумма выводится только для месяцев погашения кредита, а для следующих месяцев выводятся пустые значения. Выражение $В$1*(1-$В$2) вы­числяет сумму кредита, а разделив ее на содержимое ячейки В5, можно получить месячную долю погашения. Выражение $В$3/12определяет месячный процент кредита, а с помощью выражения 1+(СТРОКА()-1)*$В$3/12 вычисляютсяпро­центы, выплачиваемые именно в этом месяце, и они добавляются к погашаемой части кредита. В итоге мы получаем сумму, которую нужно выплатить в конкрет­ном месяце.

Протяните формулы из ячеек С2и D2 до ячеек С61и D61. При этом выведены значения будут только в начальных ячейках, а остальные останутся пустыми, хотя формулы в них работают правильно. Именно для того, чтобы не отображать не­нужную информацию, в формулах используются условия.

Чтобы выполнить расчеты, вве­дите исходные данные в таблицу, и слева появятся даты и суммы, кото­рые нужно выплатить в эти месяцы (Рис. 3.2). Уменьшите срок кредито­вания, и заполненными окажутся меньше строк.

Использование условий и при­менение в вычислениях номера строки помогает создавать достаточно сложные таблицы, поэтому хорошо разберитесь с принципами работы опи­санной таблицы. Они могут вам пригодиться в будущем.