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

Глава 1

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

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

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

Для примера рассмотрим две ячейки рабочего листа, показан­ного на рис. 1.1. В ячейку А1 введено число, допустим, это значение расстояния, измеренное в милях. В ячейке А2 содержится формула =ПРЕОБР(А1;"mi";"m")/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 с помощью форму­лы =ПРЕОБP(A1;"tsp";"cup") 1000 чайных ложек пересчитывается на количество чашек (есть и такая мера измерения объемов жидкостей), а в ячейке A3 посредством формулы =ПРЕОБР(А2;"сup";"l") это ко­личество чашек пересчитывается в литры. (По этим формулам будут получены числа 20,83... и 4,929... в ячейках А2 и A3 соответственно.) Если же вы хотите знать, сколько чайных ложек содержится в одном литре, то можно воспользоваться средством Подбор параметра, кото­рое сразу даст искомое число 202,84 (чайных ложек).

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

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

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

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

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

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

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

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

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

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

Для примера рассмотрим рабочий лист, показанный на рис. 1.5, где в ячейке А1 содержится значение градусов по Фаренгейту, а в ячейке А2 записана формула =ПPEOБP(A1;"F";"C"), преобразующая значение градусов по Фаренгейту в значение градусов по Цельсию. Введите значение 100 в ячейку А1 и вы получите значение 37,8 в ячейке А2. Допустим, теперь вы хотите узнать, сколько градусов по Фаренгейту составляют 20 градусов по Цельсию.

Рис. 1.5. Преобразование значения температуры по Фаренгейту в значение температуры по Цельсию

Чтобы удовлетворить свое любопытство, вы должны выполнить такие действия.

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

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

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

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

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

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

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