Подбор параметра в MS Excel

Цель работы:

Ознакомиться с назначением и применением средства MS Excel - Подбор параметра.

Порядок выполнения работы

Ознакомьтесь с заданием к практической работе.

Ознакомьтесь с теоретическими сведениями.

Выполните задания в табличном процессоре Excel.

Предъявите работу преподавателю.

Теоретические сведения

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

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

Рисунок 1- Преобразование милей в километры

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

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

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

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

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

Чтобы применить средство Подбор параметра, выполните команду Сервис - Подбор параметра.

В открывшемся диалоговом окне Подбор параметра выполните следующее:

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

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

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

Заполнив все три поля ввода, для начала работы данного средства щелкните на кнопку ОК.

В результате появится диалоговое окно Результат подбора параметра, которое сообщит, что решение найдено. Отображаемые в этом окне Подбираемое значение – это то значение, которое вы указали в поле Значение диалогового окна Подбор параметра,  а Текущее значение – то значение, которое Excel смогла добиться от формулы при подборе параметра. Если числа совпадают, то  Excel действительно нашла решение задачи.

Для примера рассмотрим рабочий лист (рисунок 3), где в ячейке А1 содержится значение градусов по Фаренгейту, а в ячейке А2 записана формула =ПРЕОБР(А1;^"F^";^"C^"), преобразующая значение градусов по Фаренгейту в значение градусов по Цельсию.

Введите значение 100 в ячейку А1 и вы получите значение 37,8 в ячейке А2. Допустим, теперь вы хотите узнать, сколько градусов по Фаренгейту составляет 20 градусов по Цельсию.

Для нахождения значений нужно выполнить следующее:

Выберите команду Сервис - Подбор параметра. Откроется диалоговое окно Подбор параметра.

В поле ввода Установить в ячейке введите А2 или щелкните на ячейке А2.

В поле ввода Значение введите число 20.

В поле ввода Изменяя значение ячейки введите А1или щелкните на ячейке А1.

Щелкните на кнопку ОК.

После этих действий откроется диалоговое окно Результат подбора параметра, где оба значения, Подбираемое значение и Текущее значение, будут равняться числу 20. Таким образом, Excel найдет искомое решение, которое будет отражаться в ячейке А1 как число 68.

Примеры использования средства «Подбор параметра» для решения простых математических задач

Вычисление скорости, времени и расстояния

Для решения задач создадим рабочий лист (рисунок 4).

С помощью средства Подбор параметра значения скорости, времени и расстояния, будут вычисляться соответственно в столбцах А, D и G.

Скорость вычисляется в ячейке А4 как произведение “километров” и результата деления 60 минут на заданное количество минут (записано в ячейке А5), т.е. в ячейке А4 записана формула =А6*(60/А5)

Время вычисляется в ячейке D5 как произведение  “километров” и деления 60 минут на скорость (записана в ячейке D4), т.е. используется формула =D6*(60/D4)

Расстояние в ячейке G6 вычисляется по аналогичной формуле =G4*(G5/60)

Подбор параметра для скорости (в Приложении)

Задача: какое расстояние можно преодолеть за 12 минут при скорости 75 км в час?

В ячейку А5 введите число 12.

Выберите команду Сервис - Подбор параметра. Откроется диалоговое окно Подбор параметра.

В поле ввода Установить в ячейке введите А4 или щелкните на ячейке А4.

В поле ввода Значение введите число 75.

В поле ввода Изменяя значение ячейки введите А6 или щелкните на ячейке А6.

Щелкните на кнопку ОК.

Ответ: за 12 минут при скорости 75 км в час можно преодолеть 15 км.

Подбор параметра для времени (в Приложении)

Задача: с какой скоростью вы передвигаетесь, если 12 км преодолели за 8 минут?

В ячейку D6 введите число 12.

Выберите команду Сервис ® Подбор параметра. Откроется диалоговое окно Подбор параметра.

В поле ввода Установить в ячейке введите D5 или щелкните на ячейке D5.

В поле ввода Значение введите число 8.

В поле ввода Изменяя значение ячейки введите D4 или щелкните на ячейке D4.

Щелкните на кнопку ОК.

Ответ: если вы преодолели 12 км за 8 минут, то ваша скорость составляет 90 км в час.

Подбор параметра для расстояния (в Приложении)

Задача: за какое время вы преодолеете 85 км при скорости 75 км в час?

В ячейку G4 введите число 75.

Выберите команду Сервис ® Подбор параметра. Откроется диалоговое окно Подбор параметра.

В поле ввода Установить в ячейке введите G6 или щелкните на ячейке G6.

В поле ввода Значение введите число 85.

В поле ввода Изменяя значение ячейки введите G5 или щелкните на ячейке G5.

Щелкните на кнопку ОК.

Ответ: 85 км при скорости 75 км в час вы преодолеете за 71 мин.

ЗАДАНИЕ

На листе 2 создайте таблицы для подбора параметра при вычислении длины окружности и площади круга

Задание:

1) Подбор параметра для диаметра : какое значение имеет радиус круга, если его диаметр равен 6,25?

2) Подбор параметра для длины окружности : каково значение радиуса круга, если длина окружности равна 30?

3) Подбор параметра для площади круга : каково значение радиуса круга, если площадь круга равна 17?

На листе 3 создайте таблицы для подбора параметра при вычислении
кредита на покупку квартиры

Ежемесячный платёж рассчитывается с помощью функции = ПЛТ (Ставка; Кпер; Пс)

где:

Ставка – задает ежемесячную процентную ставку по кредиту

Кпер – количество периодов погашения кредита

Пс – сумма кредита

Задание:

1) Подбор параметра для вычисления суммы кредита : кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 руб. Какова минимальная сумма платежей?

(в поле Значение вводится отрицательное число)

2) Подбор параметра для вычисления срока погашения кредита : каков срок погашения кредита, если сумма кредита равна 2 500 000 руб., процентная ставка составляет 7% годовых, а ежемесячные платежи равны 14 230 руб.?

3) Подбор параметра для вычисления процентной ставки: кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

На листе 4 создайте таблицы подбора параметра при оптимизации продажи театральных билетов

Задание:

1) Подбор параметра для вычисления количества детских билетов: сколько необходимо продать детских билетов по цене 55 руб. для того, чтобы в кассе было 22 000 руб., если продано 150 “взрослых” билетов по цене 95 руб. и 100 льготных билетов по цене 75 руб?       

2) Подбор параметра для вычисления цены билетов для взрослых: какова должна быть цена билетов для взрослых для того, чтобы в кассе было 23 500 руб., если продано 45 детских билетов по цене 60 руб. и 100 льготных билетов по цене 80 руб. и планируется продать 155 билетов для взрослых?

3) Подбор параметра для вычисления количества льготных билетов: сколько необходимо продать льготных билетов по цене 85 руб. для того, чтобы в кассе было 20 700 руб., если продано 95 детских билетов по цене 60 руб. и 125 билетов для взрослых по цене 105 руб.?

На листе 5 создайте таблицы для подбора параметра при вычислении
 ссуды на покупку машины

Ежемесячный платёж рассчитывается с помощью функции  = ПЛТ (Ставка; Кпер; Пс)

где:

Ставка – задает ежемесячную процентную ставку по кредиту

Кпер – количество периодов погашения кредита

Пс – сумма кредита

Задание:

1) Подбор параметра для вычисления размера ссуды : ссуда берется на 6 лет с процентной ставкой 2,9% при условии, что сумма ежемесячных платежей не должна превышать 1 395 руб. Каков максимальный размер ссуды

(в поле Значение вводится отрицательное число)

2) Подбор параметра для вычисления срока погашения ссуды : каков срок погашения ссуды, если сумма ссуды равна 180 000 руб., процентная ставка составляет 1,7% годовых, а ежемесячные платежи равны 3 250 руб.?

3) Подбор параметра для вычисления процентной ставки: ссуда в размере 130 000 руб. берется на 5 лет с максимальными ежемесячными платежами 2 390 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

На листе 6 создайте таблицы для подбора параметра при расчетах по депозитам

Конечная сумма депозита  рассчитывается с помощью функции = БС (Ставка; Кпер;; –Пс)

где:

Ставка – задает ежемесячную процентную ставку депозита

Кпер – срок хранения депозита

Пс – начальная сумма депозита

Задание:

1) Подбор параметра для вычисления начальной суммы депозита : депозит открывается на 3 года с процентной ставкой 12,75% годовых. Какова должна быть начальная сумма депозита, если конечная сумма должна быть порядка 150 000 руб?

2) Подбор параметра для вычисления процентной ставки: депозит в размере 250 000 руб. открывается на 2 года с целью получить конечную сумму, не менее 300 000 руб. Какова при таких условиях может быть минимальная процентная ставка?

3) Подбор параметра для вычисления срока депозита : на какой срок необходимо открыть депозит, чтобы при процентной ставке 10,7% годовых из начальной суммы 125 000 руб. получить конечную сумму 200 000 руб.