usto-excel

Клиент берёт в банке кредит на сумму $10000 на 12 месяцев под 24% годовых. На основе этих данных необходимо создать график платежей по кредиту, т.е. рассчитать какую сумму ежемесячно нужно будет платить клиенту, чтобы к концу срока полностью погасить основную сумму кредита плюс проценты.

Прежде всего, занесём в MS Excel вводные данные по кредиту, а именно:

• ПС - приведённая (текущая) стоимость, в нашем случае сумма кредита;
• Кпер - количество периодов, в течении которых нужно производить выплаты, т.е. если по условиям кредита клиент обязан ежемесячно погашать кредит, то количество периодов платежа равняется 12-ти;
• Ставка - процентная ставка под которую выдаётся кредит.

Рассчитываем ежемесячную сумму платежей

=ПЛТ(ставка; кпер; пс; [бс]; [тип])

Три первых аргумента (ставка, кпер, пс) у нас уже имеются.
Четвёртый аргумент "бс" означает будущую стоимость, которой мы хотим достичь после внесения последнего платежа. Данный аргумент является необязательным и принимается равным нулю, если не указывать его значение.
Пятый аргумент "тип" означает тип платежа, а именно производится ли платёж в начале периода (1) или же в конце периода (0). Данный аргумент также является необязательным и принимается равным нулю, если не указывать его значение.

Поскольку в ячейке С4 нами указана годовая процентная ставка, а в формуле, рассчитывается сумма платежа по каждому периоду равному одному месяцу, нужно разделить процентную ставку на количество периодов.

=ПЛТ(C4/C3;C3;C2)

Так как сумма платежа для каждого периода будет одинаковой, используем постоянные ссылки на ячейки, что позволит нам протянуть её вниз и получить одинаковую сумму платежа для всех периодов.



Как видим у нас тут две проблемы:

1. В связи с тем, что понятие платежа подразумевает денежную сумму, при первом вводе в ячейку функции ПЛТ() MS Excel автоматически меняет её формат на денежный. 
2. И так как платёж означает уменьшение оставшихся у клиента денежных средств результат функции ПЛТ() отображается со знаком минус.

Для решения первой проблемы просто выделим ячейки с формулами и изменим их формат на числовой. А чтобы преобразовать отрицательный результат формулы в положительный подкорректируем формулу с помощью функции ABS(), которая возвращает абсолютную величину числа.



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

Рассчитываем сумму ежемесячных платежей по процентам

=ПРПЛТ(ставка; период; кпер; пс; [бс]; [тип])

где период - это порядковый номер периода платежа, который мы будем брать из столбца В, используя относительную ссылку:

=ПРПЛТ($C$3/$C$2;B6;$C$2;$C$1)

Меняем формат ячейки на числовой и корректируем формулу, для отображения абсолютной величины суммы платежа, используя функцию ABS().

Рассчитываем сумму ежемесячных платежей по основной сумме долга

=ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип])

Опять же, изменим формат ячейки на числовой и добавим функцию ABS():



Если мы всё сделали правильно, то сумма платежей по процентам и основной сумме по каждому периоду будет равняться сумме общих платежей по данному периоду.

Рассчитываем остаточную сумму кредита

Таким образом, за 12 месяцев клиент должен будет заплатить $11 347,15 из которых $1 347,15 будут составлять проценты по кредиту.

Файл примера


В прошлой статье мы узнали о том как создавать сквозные формулы для суммирования показателей продаж сразу из нескольких листов книги. Эта формула суммирует значения ячеек "С8" на листах "Регион 1", "Регион 2" и на всех листах находящихся между ними. То есть "Регион 1" выступает в качестве верхней границы формулы а "Регион 2" в качестве нижней границы.

Следовательно, если мы добавим в книгу новый лист, например Регион 4 то значения из этого листа не будут учтены нашей формулой, поскольку находятся за пределами её нижней границы.


Мы можем поменять местами листы "Регион 4" и "Регион 3" и тогда значения из листа "Регион 4" будут учитываться нашей формулой, то есть лист "Регион 4" окажется в пределах действия формулы.


Но что делать если начальство требует, чтобы регионы в книге располагались по порядку их открытия. Или же если Вы создаёте книгу с формулами для использования другими сотрудниками и не можете надеяться на то, что они не забудут поменять местами листы?

Чтобы решить данную проблему:
      1. Создаём пустой лист с именем "Конец" ну или любым другим понятным Вам именем;
      2. Изменяем формулу и в качестве её нижней границы указываем созданный нами лист;
      3. Кликнув правой кнопкой мышки на названии листа выбираем команду "Скрыть".

Файл примера

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


Необходимо, в этой же книге создать итоговую таблицу с отображением суммы показателей продаж всех регионов.


Очень часто мне приходится наблюдать как мои коллеги в подобной ситуации ставят знак "=",  добавляют ссылку к первому листу, ставят знак "+", добавляют ссылку ко второму листу, и так далее. А теперь представьте, что в этой книге не 3 а 10, 50 или 100 листов с данными, которые нужно просуммировать.

ЗАДАЧА

Дана таблица (файл книги с примером) следующего вида:



В таблице указаны Код товара и цена за товар у каждого из трёх поставщиков.
Требуется создать формулу для нахождения минимальной из предложенных поставщиками цен, за любой выбранный пользователем товар.

РЕШЕНИЕ

=МИН(121;289;165)

Но как получить этот список значений из для любого, выбранного пользователем товара?
Для этого воспользуемся связкой функций ИНДЕКС()+ПОИСПОЗ().

Чтобы понять как работает эта формула, выделем её в строке формул и будем вычислять по частям с помощью клавиши F9.
Прежде всего, обратите внимание на следующую часть формулы:

ПОИСКПОЗ(G3;B3:B10;0)

Функция ПОИСКПОЗ() находит позицию указанного пользователем товара (ячейка G3) в столбце "Код товара" в основной таблице (диапазон B3:B10). Выделим данную часть формулы и нажмём F9:

Формула вычислила позицию товара в столбце "Код товара". То есть товар с кодом "А6" находится в шестой строке столбца "Код товара".
Перейдём к следующей части формулы:

ИНДЕКС(C3:E10;6;0)

В этой части скрыт наш главный секрет. Дело в том, что функция ИНДЕКС() возвращает значения находящиеся на пересечении указанных строк и столбцов. Так если бы вместо нуля мы указали цифру 1, то формула вернула бы нам значение находящееся на пересечении 6 строки (товар А6) и 1 столбца (Поставщик1) в диапазоне C3:E10. Но так как мы не указываем какой именно столбец нам нужен, то функция ИНДЕКС() возвращает нам значения всех ячеек указанной строки.

И чтобы сохранить сгенерированные уникальные значения нужно скопировать эти данные и с помощью последовательности команд “Специальная вставка > Значения” вставить их обратно в те же ячейки.

• общую сумму продаж за Январь;
• общую сумму продаж Продукта №1 за Февраль;
• общую сумму продаж более 5000 сомони Продукта №1 в Согде.

Вспомним наш пример, когда мы с помощью функции СЦЕПИТЬ() соединяли текст с датой. Тогда нам удалось сделать так, чтобы при изменении дат в ячейках "H2" и "I2", значения в других ячейках содержащих дату автоматически изменялись.



Теперь пойдём дальше, и сделаем так, чтобы даты в ячейках "H2" и "I2" автоматически менялись каждый день.
Для начала вставим в ячейку "I2" формулу "=СЕГОДНЯ()". Теперь, каждый день дата в этой ячейке будет меняться автоматически. 
Далее в ячейку "H2" вставим формулу "=СЕГОДНЯ()-1". То есть формула будет отнимать один день от сегодняшней даты и показывать предыдущий день. Однако, так как у нас в неделе 5 рабочих дней, следовательно нам нужно, чтобы в понедельник формула показывала нам в качестве предыдущего дня не воскресную дату а пятничную.Узнать на какой день недели приходится та или иная дата мы можем с помощью функции ДЕНЬНЕД(дата; тип). Как видим у фунции ДЕНЬНЕД() два аргумента и если с аргументом "дата" всё понятно, то для определения подходящего аргумента "тип" нужно свериться со справочной таблицей (скриншот №2). Для простоты используем тип №2 в котором неделя начинается с понедельника "=ДЕНЬНЕД(СЕГОДНЯ()-1;2)".


Далее с помощью функции ЕСЛИ() зададим условие, чтобы когда значение формулы "=ДЕНЬНЕД(СЕГОДНЯ()-1;2)" равняется 7 (воскресенье) в ячейке "H2" отображалась дата приходящаяся на пятницу (СЕГОДНЯ()-3).
В итоге получаем формулу "=ЕСЛИ(ДЕНЬНЕД(СЕГОДНЯ()-1;2)=7;СЕГОДНЯ()-3;СЕГОДНЯ()-1)".


Теперь, даты в ячейках "H2" и "I2" будут автоматически меняться в соответствии с днями недели.

Наверное каждый кто занимался отчётами сталкивался с задачей, когда нужно дать разбивку сумм по группам.
Например в нашем примере (скриншот №1) нужно узнать общую сумму и количество счетов остатки которых:

• меньше или равны 10 сомонам;
• больше 10 но меньше или равны 100 сомонам;
• больше 100 но меньше или равны 1000 сомонам;
• больше 1000 сомони.

В прошлом посте мы использовали функцию ЕСЛИ() для выбора между двумя условиями. Но что если нужно сделать выбор между тремя и более условиями?
К примеру на скриншоте №1 показана форма ввода для создания и распечатки договора (данные в форме вымышлены).


Как видите в ячейке "В8" вводится счёт договора, и нам нужно чтобы на основании этого счёта в ячейке "В11" Excel показывал нам его валюту.
В предыдущих постах мы уже научились находить валюту счёта с помощью функции ПСТР(). В нашем случае формула нахождения валюты будет выглядить следующим образом =ПСТР(B8;6;3).

Обычно у нас банки открывают счета в четырёх валютах:

• доллары США (840)
• российские рубли (810)
• евро (978)
• сомони (972)