Обработка данных с помощью средств MS Excel

    В ячейку В23 скопировать формулу для  расчета ежемесячных выплат.

    Для расчета выплат по каждой из ставок воспользуйтесь возможностью автоматической подстановки значений в нужную ячейку (в нашем случае в В15). Для этого  нужно:

1. Выделить диапазон А23:В26, включив в него значения процентных ставок и расчетную формулу (формула должна находиться в ячейке, расположенной правее и выше заданных значений).
2. В меню Данные выбрать команду Таблица подстановки.
3. В поле «Подставлять значения по строкам в:» указать ячейку В15.

    Рядом с каждой процентной ставкой появится соответствующий результат.

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

    Функция БС

    Функция БС предназначена для расчета будущей стоимости периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки.

    БС(СТАВКА;КПЕР;ПЛТ;ПС;ТИП)

• СТАВКА — это процентная ставка за период.
• КПЕР — это общее число периодов платежей по аннуитету.
• ПЛТ — это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно ПЛТ состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если аргумент опущен, должно быть указано значение аргумента ПС. Например, ежемесячная выплата по четырехгодичному займу в 10 000 руб. под 12 процентов годовых составит 263,33 руб. В качестве значения аргумента выплата нужно ввести в формулу число -263,33.
• ПС — это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента ПЛТ.
• ТИП — это число 0 или 1, обозначающее, когда должна производиться выплата. Если этот аргумент опущен, то он полагается равным 0.

    Для аргументов СТАВКА и КПЕР используются согласованные единицы измерения. Если производятся ежемесячные платежи  по четырехгодичному займу из расчета 12% годовых, то СТАВКА должна быть 12%/12, а КПЕР должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то СТАВКА должна быть 12%, а КПЕР должно быть 4.

    Все аргументы, означающие деньги, которые вы платите (например, депозитные вклады), представляются отрицательными числами; деньги, которые вы получаете (например, дивиденды), представляются положительными числами.

    Например, вы собираетесь вложить 1000 руб. под 6% годовых; (что составит в месяц 6%/12 или 0,5%), Вы собираетесь вкладывать по 100 руб. в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету  в конце 12 месяцев?

    БС (0,5%; 12; -100; -1000; 1). Результат 2301,40 руб.

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

    Функция ПС

    Функция ПС предназначена для расчета текущей стоимости как единой суммы вклада (займа), так и будущих фиксированных периодических платежей. Этот расчет является обратным по отношению к будущей стоимости (БС).

    ПС — возвращает текущий объем вклада. Текущий объем — это общая сумма, которую составят будущие платежи. Например, когда вы берете взаймы деньги, заимствованная сумма и есть текущий объем для заимодавца.

    ПС(СТАВКА;КПЕР;ПЛТ;БС;ТИП)

• СТАВКА — это процентная ставка за период.
• КПЕР — это общее число периодов платежей по аннуитету.
• ПЛТ — это выплата, производимая в каждый период.
• БС — это будущая стоимость периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки.
• ТИП — это число 0 или 1, обозначающее, когда должна производиться выплата. Если этот аргумент опущен, то он полагается равным 0.

    Например, определите необходимую сумму текущего вклада в банк, чтобы через пять лет он достиг 5000 руб. при 20% годовых  и ежегодном начислении процентов  в конце года.

    ПС(20%, 5, 5000). Результат 2009,39.

    Функция КПЕР

    Для определения срока платежа и  процентной ставки используются функции  КПЕР и ПРПЛТ.

    Функция КПЕР вычисляет общее число периодов выплат как для единой суммы вклада (займа), так и для периодических  постоянных выплат на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, то для  того, чтобы найти число лет  выплат, общее число периодов надо разделить на число периодов в  году.

    КПЕР(СТАВКА;ПЛТ;ПС;БС;ТИП)

    СТАВКА — процентная ставка за период.

    ПЛТ — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.

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

    БС — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (например, будущая стоимость займа равна 0).

    ТИП — число 0 или 1, обозначающее, когда должна производиться выплата.

    Например, рассчитаем срок погашения ссуды  размером 5000 руб., выданной под 20% годовых  при погашении ежемесячными платежами  по 200 руб.

    Синтаксис: КПЕР (20%/12; -200; 5000). Результат 32,6 месяца или 2,7 года.

    Функция ПРПЛТ

    Функция ПРПЛТ определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение необходимо умножить на число расчетных периодов в году.

    ПРПЛТ(СТАВКА;ПЕРИОД;КПЕР;ПС;БС;ТИП)

    СТАВКА — процентная ставка за период.

    ПЕРИОД — это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «КПЕР».

    КПЕР — общее число периодов выплат годовой ренты.

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

    БС — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (например, БС для займа равно 0).

    ТИП — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент «тип» опущен, то он полагается равным 0.

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

    Синтаксис: ПРПЛТ (48; -200; 8000). Результат 0,008. или 0,8% в месяц или 9,6% годовых.

6.7. ПОДВЕДЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ТАБЛИЦЕ

    Если  необходимо рассчитать промежуточные  суммы в таблице, например, вычислить  итоги по продаже каждого из товаров, перечисленных в таблице, то удобно воспользоваться следующей процедурой:

• отсортировать таблицу по столбцу, содержащему группы, по которым надо подвести итоги;
• установить курсор в любую ячейку этого столбца;
• задать команду Данные ® Итоги;
• в поле При каждом изменении в указать столбец с группами, по которым надо подводить итоги;
• в поле Использовать функцию указать СУММА;
• в перечне Добавить итоги по указать столбцы, значения в которых должны быть просуммированы;
• нажать кнопку ОК.

    Для скрытия или высвечивания входящих в итоги промежуточных данных нажать кнопку с номером уровня (чем  выше номер, тем больше детализирующей информации отображается на экране). Для  скрытия детализирующих данных по определенной группе нажать кнопку «-» (минус) слева от данной группы. Нажатие кнопки «+» (плюс) приводит к высвету детализирующей информации по группе.

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

 

6.8. АНАЛИЗ ДАННЫХ

6.8.1. Подбор параметра

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

    Математическая  суть задачи состоит в решении  уравнения f(х) = а, где функция f(х) описывается заданной формулой, х -искомый параметр, а — требуемый результат формулы.

    Для решения этой задачи необходимо выполнить  следующие действия:

1. Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.
2. В меню Сервис выбрать команду Подбор параметра.
3. В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).
4. В поле Значение ввести значение, которое нужно получить по заданной формуле.
5. В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).

    6. Щелкнуть по кнопке ОК. Пример.

    Дано  уравнение

Х^2 + ЗХ - 2 = А,

где А  — требуемый результат формулы; X — искомый параметр. Определить такое значение параметра X, при котором  А будет равно 20.

1. Ввести в ячейку А4 указанную формулу. В формуле сделать ссылку на ячейку, в которой условно находится параметр X.
2. Задать команду Сервис > Подбор параметра.
3. В поле Установить в ячейке указать А4 (по умолчанию в это поле вводится адрес текущей ячейки).
4. В поле Значение ввести — 20.
5. В поле Изменяя значение ячейки указать адрес ячейки, в которой должен находиться параметр X.

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

    Подбор  параметра можно выполнять графически, перетаскивая точки данных на диаграмме.

6.8.2. Таблицы подстановки данных

    Пусть имеется формула, которая зависит  от некоторых переменных. Задача состоит  в определении результатов формулы  при различных значениях этих переменных. Математическая сущность задачи состоит в табулировании  функции. Эта задача является обратной к задаче подбора параметров.

    Анализ  выполняется при помощи таблицы  подстановки данных.

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

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

    Анализ  формулы начинается с подготовки таблицы подстановки:

1. Левую верхнюю ячейку блока, отведенного под таблицу, оставить пустой.
2. В левый столбец блока, начиная со второй ячейки, последовательно ввести значения варьируемой переменной.
3. В верхнюю строку блока, начиная со второй ячейки, ввести ссылки на ячейки с анализируемыми формулами.