Кредит на покупку квартиры

Глава 1

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

Средство Подбор параметра — простой, сохраняющий время и лег­кий в использовании инструмент, предназначенный для вычисления входного значения (параметра) некоторой формулы для того, чтобы формула возвращала требуемый результат. В этой главе сначала вы узнаете, когда и как следует использовать данное средство, затем я предложу вам три набора упражнений для практического закрепле­ния изученного материала. В последнем разделе главы описаны воз­можные проблемы и ошибки, которые могут возникнуть при работе со средством Подбор параметра.

Назначение средства «Подбор параметра»

Средство Подбор параметра находит такое значение параметра (это значение будет записано в указанной ячейке рабочего листа), которое обеспечит требуемое значение, вычисленное по формуле, зависящей от этого параметра и записанной в другой ячейке рабо­чего листа.

Для примера рассмотрим две ячейки рабочего листа, показан­ного на рис. 1.1. В ячейку А1 введено число, допустим, это значение расстояния, измеренное в милях. В ячейке А2 содержится формула =ПРЕОБР(А1;"гги";"т")/1000, преобразующая значение милей в ки­лометры. Если в ячейку А1 ввести число 10, в ячейке А2 будет вы­числено значение 16,1 (приближенно). Но сколько миль будет соот­ветствовать 20 километрам? Можно попробовать подобрать нужное значение, последовательно вводя в ячейку А1 значения 10,11,12,12,5 и так далее до тех пор, пока в ячейке А2 не отобразится число 20 (или близкое к нему). Однако такой подбор чисел весьма утомителен (да и точное значение найти непросто, поскольку это дробное число), просто и быстро эту задачу выполнит средство Подбор параметра. (Между прочим, 20 км равно 12,4 мили.)


Рис. 1.1. Преобразование милей в километры

Когда применяется «Подбор параметра»

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

Другими словами, это средство применяется в случае, если на рабочем листе имеется одна ячейка с числовым значением, а дру­гая — с формулой, зависящей от значения в первой ячейке, и необ­ходимо подобрать такое значение в первой ячейке, чтобы в ячейке с формулой получилось заданное вами значение. Например, на ра­бочем листе, показанном на рис. 1.2, в ячейке А1 записана оптовая цена некоторого товара (595 руб.), а в ячейке А2 записана формула =ОКРУГЛ(А1+(А1*8,8%);2), которая вычисляет розничную цену это­го же товара, увеличивая его оптовую цену на 8,8%. Теперь необхо­димо узнать, какова оптовая цена другого товара, если его розничная цена равна 1099 руб. Подбор параметра быстро определит, что в этом случае оптовая цена составляет 1010 руб.

Рис. 1.2. Подбор параметра для определения оптовой цены

В другом примере, показанном на рис. 1.3, в ячейке А1 содержится число 1000, в данном случае это 1000 чайных ложек (есть такая мера измерения объемов жидкостей). В ячейке А2 с помощью формулы =ПPEOБP(A1;"tsp";"cup") 1000 чайных ложек пересчитывается на количество чашек (есть и такая мера измерения объемов жидкостей), а в ячейке A3 посредством формулы =ПРЕОБР(А2;"сир";Т) это количество чашек пересчитывается в литры. (По этим формулам будут получены числа 20,83... и 4,929... в ячейках А2 и A3 соответственно.) Если же вы хотите знать, сколько чайных ложек содержится в одном литре, то можно воспользоваться средством Подбор параметра, которое сразу даст искомое число 202,84 (чайных ложек).


Рис. 1.3. Подбор параметра для преобразования чайных ложек в литры

Как применить «Подбор параметра»

Чтобы применить средство Подбор параметра, выполните коман­ду Сервис^ Подбор параметра. Откроется одноименное диалоговое окно, в котором надо заполнить все поля ввода, а затем щелкнуть на кнопке ОК. В результате появится диалоговое окно Результат подбора параметра.

Диалоговое окно Подбор параметра очень просто в использова­нии — в нем надо заполнить всего три поля ввода: Установить в ячейке, Значение и Изменяя значение ячейки, которые показаны на рис. 1.4.

Рис. 1.4. Диалоговое окно Подбор параметра

Вот какую последовательность действий надо выполнить в откры­том диалоговом окне Подбор параметра.

1. В поле ввода «Установить в ячейке» введите адрес или просто, когда курсор будет находиться в этом поле, щелкните на ячейке, содержащей формулу, для результата вычисления которой вы хотите задать значение.

2. В поле ввода «Значение» введите число, которое вы хотите увидеть в ячейке, указанной в поле «Установить в ячейке».

3. В поле ввода «Изменяя значение ячейки» введите адрес или про сто щелкните на ячейке, содержащей числовое значение, которое вы хотите определить. Формула в ячейке, указанная в поле «Установить в ячейке», обязательно должна прямо или опосредованно (через другие формулы) ссылаться на ячейку, которую вы указали в поле «Изменяя значение ячейки».

Заполнив все три поля ввода диалогового окна Подбор параметра, для начала работы данного средства щелкните в этом окне на кнопке ОК. После этого появится диалоговое окно Результат подбора параметра, которое сообщит, что решение найдено. Обратите внимание на два числа, отображаемые в этом окне как Подбираемое значение и Текущее значение. Подбираемое значение, - это то значение, которое вы указали в поле «Значение» диалогового окна Подбор параметра, а Текущее значение — то значение, которое Excel смогла добиться от формулы (указанной в поле «Установить в ячейке» диалогового окна Подбор параметра) при подборе параметра, заданного в поле Изменяя значение ячейки того же окна Подбор параметра. Если числа Подбираемое значение и Текущее значение совпадают, это означает, что Excel действительно нашла решение задачи.

Кредит на покупку квартиры

Прежде чем рассматривать задачи, связанные с кредитом на по­купку квартиры, создайте рабочий лист, показанный на рис. 1.9.

Рис. 1.9. Рабочий лист для решения задач о банковском кредите

На этом рабочем листе сумма кредита, срок погашения креди­та (в месяцах) и годовая процентная ставка представлены в виде чи­сел, а ежемесячный платеж рассчитывается с помощью функции =ПЛТ(Ставка;Кпер;Пс). В этой функции аргумент Ставка задает ежеме­сячную процентную ставку по кредиту (поэтому в нашей формуле этот аргумент равен В5/12), аргумент Кпер — количество периодов погаше­ния кредита (ячейка В4), аргумент Пс — сумма кредита (ячейка ВЗ).