Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Глава 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.
Теперь, когда вы знаете, как надо работать со средством Подбор параметра, пришло время рассмотреть несколько более сложных примеров, а вам — выполнить несколько упражнений.
Сделай сам: использование средства «Подбор параметра» для решения простых математических задач
Приведенные в этом разделе примеры и упражнения покажут, как применять средство Подбор параметра для решения следующих математических задач:
♦ вычисление скорости, времени и расстояния;
♦ вычисление диаметра, длины окружности и площади круга;
♦ нахождение корней алгебраических уравнений.
Вычисление скорости, времени и расстояния
Прежде чем приступать к решению объявленных задач, советую создать рабочий лист, показанный на рис.1.6.
Рис. 1.6. Рабочий лист для решения задач вычисления скорости, времени и расстояния
С помощью средства Подбор параметра значения скорости, времени и расстояния будут вычисляться соответственно в столбцах А, D и G.
♦ Скорость вычисляется в ячейке А4 как произведение «километров» и результата деления 60 минут на заданное количество минут (записано в ячейке А5). Другими словами, в ячейке А4 записана формула =А6*(60/А5).
♦ Время вычисляется в ячейке D5 как произведение «километров» и результата деления 60 минут на скорость (записана в ячейке D4), т.е. используется формула =D6*(60/D4).
♦ Расстояние в ячейке G6 вычисляется по аналогичной формуле =G4*(G5/60), т.е. как произведение скорости и результата деления 60 минут на заданное количество минут.
Подбор параметра для скорости
Задача: какое расстояние можно преодолеть за 12 минут при скорости 75 км в час? Ниже приведено решение задачи с помощью средства Подбор параметра.
1. В ячейку А5 введите число 12.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите А4 или щелкните на ячейке А4.
4. В поле ввода Значение введите число 75.
5. В поле ввода Изменяя значение ячейки введите А6 или щелкните на ячейке А6.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: за 12 минут при скорости 75 км в час можно преодолеть 15 км.
Подбор параметра для времени
Задача: с какой скоростью вы передвигаетесь, если 12 км преодолели за 8 минут? Ниже приведено решение задачи с помощью средства Подбор параметра.
1. В ячейку D6 введите число 12.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите D5 или щелкните на ячейке D5.
4. В поле ввода Значение введите число 8.
5. В поле ввода Изменяя значение ячейки введите D4 или щелкните на ячейке D4.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если вы преодолели 12 км за 8 минут, то ваша скорость составляет 90 км в час.
Подбор параметра для расстояния
Задача: за какое время вы преодолеете 85 км при скорости 75 км в час? Решение задачи с помощью средства Подбор параметра приведено ниже.
1. В ячейку G4 введите число 72.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите G6 или щелкните на ячейке G6.
4. В поле ввода Значение введите число 85.
5. В поле ввода Изменяя значение ячейки введите G5 или щелкните на ячейке G5.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: 85 км при скорости 75 км в час вы преодолеете за 71 мин.
Вычисление диаметра, длины окружности и площади круга
Для решения этих задач создайте рабочий лист, показанный на рис. 1.7.
На этом рабочем листе радиус в виде числового значения задается в ячейке А10, другие числовые характеристики вычисляются по следующим формулам.
♦ Диаметр как удвоенное значение радиуса вычисляется в ячейке АН по формуле =А10*2.
♦ Длина окружности в ячейке А12 вычисляется как число л, умноженное на диаметр, т.е. по формуле
=ПИ()*А11.
♦ Площадь круга в ячейке А13 вычисляется как число тт, умноженное на квадрат радиуса, т.е. по формуле
=ПИ()*СТЕПЕНЬ(А10;2).
Рис. 1.7. Рабочий лист для решения задач вычисления диаметра, длины окружности и площади круга
В этих задачах единицы измерения не имеют значения, можете подставить километры, миллиметры или дюймы.
Подбор параметра для диаметра
Задача: какое значение имеет радиус круга, если его диаметр равен 6,25? Решение задачи с помощью средства Подбор параметра таково.
1. Выберите команду Сервис =>Подбор параметра. Откроется диалоговое окно Подбор параметра.
2. В поле ввода Установить в ячейке введите АН или щелкните на ячейке АН.
3. В поле ввода Значение введите число 6,25.
4. В поле ввода Изменяя значение ячейки введите А10 или щелкните на ячейке А10.
5. Щелкните на кнопке ОК.
6. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если диаметр круга равен 6,25, то радиус равен 3,125.
Подбор параметра для длины окружности
Задача: каково значение радиуса круга, если длина окружности равна 30? Решение задачи с помощью средства Подбор параметра приведено ниже.
1. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
2. В поле ввода Установить в ячейке введите А12 или щелкните на ячейке А12.
3. В поле ввода Значение введите число 30.
4. В поле ввода Изменяя значение ячейки введите А10 или щелкните на ячейке А10.
5. Щелкните на кнопке ОК.
6. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если длина окружности равна 30, то радиус равен 4,8.
Подбор параметра для площади круга
Задача: каково значение радиуса круга, если площадь круга равна 17? Ниже приведено решение задачи с помощью средства Подбор параметра.
1. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
2. В поле ввода Установить в ячейке введите А13 или щелкните на ячейке А13.
3. В поле ввода Значение введите число 17.
4. В поле ввода Изменяя значение ячейки введите А10 или щелкните на ячейке А10.
5. Щелкните на кнопке ОК.
6. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если площадь круга равна 17, то радиус равен 2,3.
Вычисление корней алгебраических уравнений
Для решения задач этого раздела подготовьте рабочий лист, показанный на рис. 1.8.
Алгебраическое выражение ах + by + cz = d по значениям переменных а, b, с, х, у и z вычисляет значение переменной d. Имея значения любых шести переменных, с помощью средства Подбор параметра можно вычислить значение седьмой переменной.
Рис. 1.8. Рабочий лист для вычисления корней алгебраического уравнения
Подбор параметра для вычисления переменной с
Задача: зная значения переменных
♦ a = 1,
♦ b = 2,
♦ d = 12,
♦ x = 1,
♦ y = 2,
♦ z = 1,
необходимо найти значение переменной с.
1. Введите следующие значения в указанные ячейки:
ячейка А17: 1,
ячейка А18: 2,
ячейка С17: 1,
ячейка С18: 2,
ячейка С19: 1.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите А20 или щелкните на ячейке А20.
4. В поле ввода Значение введите число 12.
5. В поле ввода Изменяя значение ячейки введите А19 или щелкните на ячейке А19.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если а = 1, b = 2, d = 12, х = 1, у = 2, z = 1, то с = 7.
Подбор параметра для вычисления переменной z
Задача: зная значения переменных
♦ а = 2,
♦ b = 4,
♦ c = 3,
♦ d = 65,
♦ x = 5,
♦ y =7,
необходимо найти значение переменной z.
1. Введите следующие значения в указанные ячейки:
ячейка А17:2,
ячейка А18: 4,
ячейка А19: 3,
ячейка С17: 5,
ячейка С18:7.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите А20 или щелкните на ячейке А20.
4. В поле ввода Значение введите число 65.
5. В поле ввода Изменяя значение ячейки введите С19 или щелкните на ячейке С19.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если а = 2, Ь = 4, с = 3, d = 65, х = 5, у = 7, то z = 9.
Подбор параметра для вычисления переменной а
Задача: зная значения переменных
♦ a = 6,
♦ с = 2,
♦ d = 84,
♦ x = 4,
♦ у = 7,
♦ z = 9,
необходимо найти значение переменной а.
1. Введите следующие значения в указанные ячейки:
ячейка А18: 6, ячейка А19: 2, ячейка С17: 4, ячейка С18: 2, ячейка С19: 9.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите А20 или щелкните на ячейке А20.
4. В поле ввода Значение введите число 84.
5. В поле ввода Изменяя значение ячейки введите А17 или щелкните на ячейке А17.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если b = 6, с = 2, d = 84, х = 4, у = 2, z = 9, то а = 13,5.
Теперь, когда вы знаете, как использовать средство Подбор параметра для решения математических задач, испытаем его на задачах экономического характера.
Сделай сам: использование средства «Подбор параметра» для выполнения банковских расчетов
Приведенные в этом разделе примеры и упражнения покажут, как применять средство Подбор параметра для решения задач, связанных с кредитом на покупку квартиры, автомобиля и банковскими депозитами.
Кредит на покупку квартиры
Прежде чем рассматривать задачи, связанные с кредитом на покупку квартиры, создайте рабочий лист, показанный на рис. 1.9.
Рис. 1.9. Рабочий лист для решения задач о банковском кредите
На этом рабочем листе сумма кредита, срок погашения кредита (в месяцах) и годовая процентная ставка представлены в виде чисел, а ежемесячный платеж рассчитывается с помощью функции =ПЛТ(Ставка;Кпер;Пс). В этой функции аргумент Ставка задает ежемесячную процентную ставку по кредиту (поэтому в нашей формуле этот аргумент равен В5/12), аргумент Клер — количество периодов погашения кредита (ячейка В4), аргумент Пс — сумма кредита (ячейка ВЗ).
Подбор параметра для вычисления суммы кредита
Задача: кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 руб. Какова максимальная сумма кредита?
1. В ячейку В4 введите число 180 (15 лет, умноженных на 12 месяцев). В ячейку В5 введите 5,75%.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число -11000.
В поле Значение вводится отрицательное число, что указывает на то, что ежемесячные платежи заемщик отдает, а не получает (как при банковских вкладах).
5. В поле ввода Изменяя значение ячейки введите ВЗ или щелкните на ячейке ВЗ.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 руб., то максимальная сумма кредита составит 1 324 647 руб.
Подбор параметра для вычисления срока погашения кредита
Задача: каков срок погашения кредита, если сумма кредита равна 2 250 000 руб., процентная ставка составляет 7% годовых, а ежемесячные платежи равны 14 230 руб.?
1. В ячейку ВЗ введите число 2250000, в ячейку В5 введите 7%.
2. Выберите команду Сервиса => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число —14230.
5. В поле ввода Изменяя значение ячейки введите В4 или щелкните на ячейке В4.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: кредит в сумме 2 250 000 руб. с процентной ставкой 7% годовых и ежемесячными платежами в размере 14 230 руб. берется на 439 месяцев (примерно 36,6 года).
Подбор параметра для вычисления процентной ставки
Задача: кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?
1. В ячейку ВЗ введите число 8500000, в ячейку В4 введите 360.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число -52250.
5. В поле ввода Изменяя значение ячейки введите В4 или щелкните на ячейке В4.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб., то можно согласиться на процентную ставку в размере 6,23%.
Ссуда на покупку машины
Прежде чем рассматривать задачи, связанные со ссудой на покупку машины, создайте рабочий лист, показанный на рис. 1.10.
пересчитать
Рис. 1.10. Рабочий лист для решения задач о ссуде
На этом рабочем листе, похожем на рабочий лист из предыдущего примера, сумма кредита, ее срок погашения кредита (в месяцах) и годовая процентная ставка представлены в виде чисел, а ежемесячный платеж рассчитывается с помощью функции =ПЛТ(Ставка;Кпер;Пс). В этой функции аргумент Ставка задает ежемесячную процентную ставку по кредиту (которая вычисляется как В11/12), аргумент Кпер — количество периодов погашения кредита (ячейка В13), аргумент Пс — сумма кредита (ячейка В10).
Подбор параметра для вычисления размера ссуды
Задача: ссуда берется на 6 лет с процентной ставкой 2,9% при условии, что сумма ежемесячных платежей не должна превышать 1 395 руб. Каков максимальный размер ссуды?
1. В ячейку В11 введите число 2,9%. В ячейку В13 введите 72.
2. Выберите команду Сервис1^ Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В12 или щелкните на ячейке В12.
4. В поле ввода Значение введите число -1395.
5. В поле ввода Изменяя значение ячейки введите В10 или щелкните на ячейке В10.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если ссуда берется на 5 лет с процентной ставкой 2,9% при условии, что сумма ежемесячных платежей не должна превышать 1 395 руб., то максимальный размер ссуды составит 92 085,4 руб.
Подбор параметра для вычисления срока погашения ссуды
Задача: каков срок погашения ссуды, если сумма ссуды равна 180 000 руб., процентная ставка составляет 1,7% годовых, а ежемесячные платежи равны 3 250 руб.?
1. В ячейку В10 введите число 180000, в ячейку В11 введите 1,7%.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В12 или щелкните на ячейке В12.
4. В поле ввода Значение введите число -3250.
5. В поле ввода Изменяя значение ячейки введите В13 или щелкните на ячейке В13.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: ссуда в сумме 180 000 руб. с процентной ставкой 1,7% годовых и ежемесячными платежами в размере 3 250 руб. берется на 58 месяцев.
Подбор параметра для вычисления процентной ставки
Задача: ссуда в размере 130 000 руб. берется на 5 лет с максимальными ежемесячными платежами 2 390 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?
1. В ячейку В10 введите число 130000, в ячейку В13 введите 60.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В12 или щелкните на ячейке В12.
4. В поле ввода Значение введите число -2390.
5. В поле ввода Изменяя значение ячейки введите В11 или щелкните на ячейке В11.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если ссуда в размере 130 000 руб. берется на 5 лет с максимальными ежемесячными платежами 2 390 руб., то можно согласиться на процентную ставку в размере 3,93%.
Расчеты по депозитам
Прежде чем рассматривать задачи, связанные с расчетами по депозитам, создайте рабочий лист, показанный на рис. 1.11.
пересчитать
Рис. 1.11. Рабочий лист для выполнения расчётов по депозитам
На этом рабочем листе начальная сумма депозита, срок хранения депозита (в месяцах) и годовая процентная ставка представлены в виде чисел, а конечная сумма депозита рассчитывается с помощью функции =БС(Ставка;Кпер;;—Пс). В этой функции аргумент Ставка задает ежемесячную процентную ставку депозита (поэтому в нашей формуле этот аргумент равен В20/12), аргумент Клер — срок хранения депозита (ячейка В19), аргумент Пс — начальная сумма депозита (ячейка В18). Эта функция подобна функции ПЛТ из предыдущего примера, но вычисляет не ежемесячные платежи, а конечную накопленную сумму.
Подбор параметра для вычисления начальной суммы депозита
Задача: депозит открывается на 3 года с процентной ставкой 12,75% годовых. Какова должна быть начальная сумма депозита, если конечная сумма должна быть порядка 150 000 руб.?
1. В ячейку В19 введите число 36 (3 года, умноженные на 12 месяцев). В ячейку В20 введите 12,75%.
2. Выберите команду Сервис ^Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В21 или щелкните на ячейке В21.
4. В поле ввода Значение введите число 150000.
5. В поле ввода Изменяя значение ячейки введите В18 или щелкните на ячейке В18.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если депозит открывается на 3 года с процентной ставкой 12,75%, то для получения конечной суммы 150 000 руб. начальная сумма должна составлять 102 530 руб.
Подбор параметра для вычисления срока депозита
Задача: на какой срок необходимо открыть депозит, чтобы при процентной ставке 10,7% годовых из начальной суммы 125 000 руб. получить конечную сумму 200 000 руб.?
1. В ячейку В18 введите число 125000, в ячейку В20 введите 10,7%.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В21 или щелкните на ячейке В21.
4. В поле ввода Значение введите число 200000.
5. В поле ввода Изменяя значение ячейки введите В19 или щелкните на ячейке В19.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: чтобы при начальной сумме депозита 125 000 руб. и 10,7% годовых получить 200 000 руб., депозит необходимо открыть на 53 месяца (примерно 4,4 года).
Подбор параметра для вычисления процентной ставки
Задача: депозит в размере 250 000 руб. открывается на 2 года с целью получить конечную сумму не менее 300 000 руб. Какова при таких условиях может быть минимальная процентная ставка?
1. В ячейку В18 введите число 250000, в ячейку В19 введите 24.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В21 или щелкните на ячейке В21.
4. В поле ввода Значение введите число 300000.
5. В поле ввода Изменяя значение ячейки введите В20 или щелкните на ячейке В20.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: если депозит в размере 250 000 руб. открывается на 2 года с желаемой конечной суммой 300 000 руб., то для этого необходима процентная ставка не менее 9,15% годовых.
Сделай сам: использование средства «Подбор параметра» для оптимизации продажи театральных билетов
Приведенные в этом разделе примеры и упражнения покажут, как применять средство Подбор параметра для решения задач, связанных с оптимизацией процесса продажи театральных билетов. Однако сначала создайте рабочий лист, показанный на рис. 1.12.
Рис. 1.12. Рабочий лист для решения задач, связанных с оптимизацией процесса продажи театральных билетов
Этот рабочий лист прост для понимания. Он содержит данные о ценах билетов трех категорий (детские, для взрослых и льготные) и их количестве. Общая выручка, которую должна получить театральная касса, подсчитывается в ячейке В6 как сумма стоимостей билетов всех категорий.
Вычисление количества билетов
В этом подразделе рассматриваются задачи определения количества билетов одной из трех категорий таким образом, чтобы стоимость «кассы» составляла определенную сумму.
Подбор параметра для вычисления количества детских билетов
Задача: сколько необходимо продать детских билетов по цене 55 руб. для того, чтобы в кассе было 22 000 руб., если продано 150 «взрослых» билетов по цене 95 руб. и 100 льготных билетов по цене 75 руб.?
1. Введите следующие значения в указанные ячейки:
ячейка В2: 55,
ячейка ВЗ: 95,
ячейка СЗ: 150,
ячейка В4: 75,
ячейка С4: 100.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число 22000.
5. В поле ввода Изменяя значение ячейки введите С2 или щелкните на ячейке С2.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: при выполнении условий задачи необходимо продать 5 детских билетов.
Подбор параметра для вычисления количества билетов для взрослых
Задача: сколько необходимо продать билетов для взрослых по цене 90 руб. для того, чтобы в кассе было 21 500 руб., если продано 125 детских билетов по цене 60 руб. и 100 льготных билетов по цене 75 руб.?
1. Введите следующие значения в указанные ячейки:
ячейка В2: 60, ячейка С2: 125, ячейка ВЗ: 90, ячейка В4: 75, ячейка С4:100.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число 21500.
5. В поле ввода Изменяя значение ячейки введите СЗ или щелкните на ячейке СЗ.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: при выполнении условий задачи необходимо продать 72 билета для взрослых.
Подбор параметра для вычисления количества льготных билетов
Задача: сколько необходимо продать льготных билетов по цене 85 руб. для того, чтобы в кассе было 20 700 руб., если продано 95 детских билетов по цене 60 руб. и 125 билетов для взрослых по цене 105 руб.?
1. Введите следующие значения в указанные ячейки:
ячейка В2: 60,
ячейка С2: 95,
ячейка ВЗ: 105,
ячейка СЗ: 125,
ячейка В4: 85.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число 20700.
5. В поле ввода Изменяя значение ячейки введите С4 или щелкните на ячейке С4.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: при выполнении условий задачи необходимо продать 22 льготных билета.
Вычисление цены билетов
В этом подразделе рассматриваются задачи определения цены билетов одной из трех категорий таким образом, чтобы стоимость «кассы» составляла определенную сумму.
Подбор параметра для вычисления цены детских билетов
Задача: какова должна быть цена детских билетов для того, чтобы в кассе было 23 000 руб., если продано 150 «взрослых» билетов по цене 95 руб. и 100 льготных билетов по цене 75 руб. и планируется продать 30 детских билетов?
1. Введите следующие значения в указанные ячейки: ячейка С2: 30,
ячейка ВЗ: 95, ячейка СЗ: 150, ячейка В4: 75, ячейка С4:100.
2. Выберите команду Сервис =>Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число 22000.
5. В поле ввода Изменяя значение ячейки введите В2 или щелкните на ячейке В2.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: при выполнении условий задачи необходимо установить цену детских билетов 42 руб.
Подбор параметра для вычисления цены билетов для взрослых
Задача: какова должна быть цена билетов для взрослых для того, чтобы в кассе было 23 500 руб., если продано 45 детских билетов по цене 50 руб. и 100 льготных билетов по цене 80 руб. и планируется продать 155 билетов для взрослых?
1. Введите следующие значения в указанные ячейки:
ячейка В2: 60,
ячейка С2: 45,
ячейка СЗ: 155,
ячейка В4: 80,
ячейка С4:100.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число 23500.
5. В поле ввода Изменяя значение ячейки введите ВЗ или щелкните на ячейке ВЗ.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: при выполнении условий задачи необходимо установить цену билетов для взрослых 83 руб.
Подбор параметра для вычисления цены льготных билетов
Задача: какова должна быть цена льготных билетов для того, чтобы в кассе было 20 700 руб., если продано 95 детских билетов по цене 50 руб. и 125 билетов для взрослых по цене 105 руб. и планируется продать 40 льготных билетов?
1. Введите следующие значения в указанные ячейки:
ячейка В2: 50,
ячейка С2: 95,
ячейка ВЗ: 105,
ячейка СЗ: 125,
ячейка С4: 40.
2. Выберите команду Сервис => Подбор параметра. Откроется диалоговое окно Подбор параметра.
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число 20700.
5. В поле ввода Изменяя значение ячейки введите В4 или щелкните на ячейке В4.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: при выполнении условий задачи необходимо установить цену льготных билетов 71 руб.
Задачи, представленные в этом подразделе, будуттакже решены в главе 4 с помощью средства Поиск решения.
Ошибки при работе со средством «Подбор параметра»
После того как вы щелкнете на кнопке ОК в диалоговом окне Подбор параметра, чтобы найти нужное значение, Excel вместо диалогового окна Результат подбора параметра может вывести на экран одно из следующих сообщений об ошибке.
Ячейка должна содержать формулу. Это сообщение об ошибке появляется тогда, когда ячейка, адрес которой указан в поле ввода Установить в ячейке диалогового окна Подбор параметра, не содержит формулы. Чаще всего причиной этой ошибки является то, что вы в этом поле ввели адрес ячейки, который должен быть указан в поле ввода Изменяя значение ячейки. Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле Установить в ячейке адрес «правильной» ячейки, содержащей формулу. Затем снова щелкните на кнопке ОК.
Введено недопустимое значение. Следует ввести число. Это сообщение об ошибке появляется тогда, когда вы в поле ввода Значение кроме числа ввели еще какие-то символы, которые Excel не может распознать как числа. Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле Значение правильное число (целое или десятичное). Затем снова щелкните на кнопке ОК.
В поле ввода Значение к числу можно добавить знак денежной единицы или процента.
Ячейка должна содержать значение. Это сообщение об ошибке появляется тогда, когда ячейка, адрес которой указан в поле ввода Изменяя значение ячейки диалогового окна Подбор параметра, не содержит числового значения (а содержит, например, текст или формулу). Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле Изменяя значение ячейки адрес «правильной» ячейки, содержащей числовое значение. Затем снова щелкните на кнопке ОК.
Введенный текст не является правильной ссылкой или именем. Это сообщение об ошибке появляется тогда, когда в поле ввода Установить в ячейке или в поле ввода Изменяя значение ячейки диалогового окна Подбор параметра введено нечто, что Excel не может распознать как ссылку на ячейку. Чаще всего такая ошибка возникает тогда, когда вы вручную вводите адрес ячейки, а не указываете ячейку путем щелчка на ней. (Например, если вы вводите адрес ячейки «русскими» буквами.) Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле ввода правильный адрес ячейки. Затем снова щелкните на кнопке ОК.
Решение не найдено. Это сообщение появляется в диалоговом окне Результат подбора параметра (а не как сообщение об ошибке) тогда, когда Excel не может подобрать такое значение для изменяемого параметра, чтобы указанная формула возвратила заданное вами значение. Такое же сообщение появится, если в поле Значение введено экстремально маленькое или экстремально большое число. Чтобы исправить эту ошибку, сначала в диалоговом окне Результат подбора параметра щелкните на кнопке Отмена, данное окно закроется. Затем снова выберите команду Сервис => Подбор параметра и в поле Значение диалогового окна Подбор параметра введите другое число.
В этой главе мы изучили средство Подбор параметра — простое в использовании средство, призванное находить такое значение входной переменной, при котором указанная формула возвратит заданное вами значение. Упражнения «Сделай сам» помогли вам «набить руку» в работе с этим средством. Наконец, вы узнали о тех сообщениях об ошибках, которые вы можете встретить при использовании данного средства, и о том, как исправить эти ошибки.
Глава 2
Таблицы данных
Таблицы данных — это удобный способ представить в виде таблицы множественные результаты вычисления одной или нескольких формул. В этой главе вы узнаете, что такое таблицы данных, когда их следует применять и, наконец, как их создавать. Затем предложенный ряд упражнений поможет закрепить практические навыки работы с таблицами данных. В последнем разделе главы описаны возможные проблемы и ошибки, которые могут проявиться при работе с таблицами данных.
Назначение таблиц данных
Таблицы данных — это диапазон смежных ячеек, в которых показаны результаты вычисления определенных формул при изменении значений, влияющих на эти формулы. Таблицы данных предлагают удобный способ с помощью одного оператора вычислить, отобразить и сравнить несколько результатов вычисления определенных формул.
Например, на рис. 2.1 показана таблица соответствия между температурными шкалами Фаренгейта и Цельсия. В этой таблице данных в диапазоне АЗ:А71 содержатся числа от 32 до 100, соответствующие градусам по Фаренгейту. В диапазоне ВЗ:В71 находятся соответствующие им значения градусов по Цельсию (в интервале от 0 до 37,8 градуса). Из этой таблицы вы можете узнать, что, например, 96 градусов по Фаренгейту (это значение записано в ячейке А67) соответствует 35,6 градуса по Цельсию (ячейка В67).
Вам нет необходимости вводить вручную значения в диапазон ВЗ:В71. Для создания таблицы, показанной на рис. 2.1, надо иметь только значения в диапазоне АЗ:А71 и формулу в ячейке В2 (в данном случае имеем формулу =ПРЕОБР(B1;"F";"C")). При создании таблицы данных Excel автоматически вычислит значения в диапазоне ВЗ:В71.
Другой пример показан на рис. 2.2. Это таблица умножения, содержащая результаты попарного умножения чисел от 1 до 15. В диапазоне А4:А18 содержатся числа от 1 до 15, такие же числа записаны в диапазон ВЗ:РЗ. В таблице на пересечении строк и столбцов, соответствующих определенным числам из этих диапазонов, стоит значение произведения данных чисел. Например, в ячейке J10 содержится число 63, равное произведению чисел 7 (записано в ячейке А10) и 9 (записано в ячейке J3).
Рис. 2.2. Таблица умножения
Когда применяются таблицы данных
Таблицы данных применяются тогда, когда нужен удобный способ отобразить в виде таблицы результаты вычисления одной или нескольких формул для различных входных значений, от которых зависят эти формулы.
Например, вы хотите отобразить в виде таблицы данных список оптовых цен и список розничных цен, которые получаются из оптовых цен путем добавления к ним розничной надбавки. Такая таблица показана на рис. 2.3. Здесь в диапазоне АЗ:А102 записаны значения оптовых цен от 10 руб. до 1000 руб. с шагом 10 руб., в диапазоне ВЗ:В102 — соответствующие значения розничных цен при условии, что розничная надбавка составляет 8,8%. Из этой таблицы видно, что, например, оптовой цене 970 руб. соответствует розничная цена в размере 1 055 руб.
Рис. 2.3. Таблица данных, показывающая соответствие оптовых и розничных цен
Продолжая предыдущий пример, предположим, что для оптовой цены возможны скидки. Теперь вы хотите на основе оптовых цен создать таблицу розничных цен, где бы учитывалась розничная надбавка 8,8% и возможные скидки на оптовые цены. Такая таблица показана на рис. 2.4. Здесь в диапазоне ВЗ:В103 записаны значения оптовых цен от 10 до 1000 руб. с шагом 10 руб., а в диапазоне C3:V3 — значения скидки от 0 до 95% с шагом 5%. Теперь, чтобы определить розничную цену, если, допустим, оптовая цена равна 970 руб., а скидка равна 15%, достаточно посмотреть на значение ячейки F100, которая располагается на пересечении строки, соответствующей значению 970 руб., и столбца, соответствующего значению 15%, — в этой ячейке содержится число 897, такова розничная цена при данных условиях.
Рис.2.4. Таблица розничных цен с учетом оптовой скидки и розничной надбавки.
Создание таблиц данных
Прежде всего необходимо сказать, что таблицы данных бывают двух видов: таблицы данных с одним входом и таблицы данных с двумя входами. Различие между ними заключается в количестве входных переменных, значения которых влияют на вычисление формулы. В таблицах данных с одним входом таких переменных одна (она содержится в так называемой входной ячейке). В таблицах данных с двумя входами входных переменных две, и им соответствуют две входные ячейки. Для таблиц подстановок с одним входом во входную ячейку подставляются значения, предварительно записанные в диапазоне ячеек, который располагается в одной строке или одном столбце. Для таблиц данных с двумя входами создается два одномерных диапазона, один располагается в строке, а второй — в столбце; значения из этих диапазонов при создании таблицы данных подставляются в соответствующие входные ячейки. Формулы, результаты вычисления которых будут представлены в таблицах данных, обязательно должны ссылаться прямо или опосредованно (через другие промежуточные формулы) на входные ячейки.
Таблицы подстановок содержат также результирующие значения — значения, вычисленные по указанным формулам, когда во входные ячейки последовательно подставляются значения из предварительно созданных диапазонов ячеек.
Для примера рассмотрим рабочий лист, показанный на рис. 2.5. Здесь ячейки В1 и В2 являются входными ячейками; диапазон В4: В13 — столбец входных значений, соответствующий одной входной переменной; диапазон C3:L3 — строка входных значений, соответствующая другой входной переменной; диапазон C4:L13 содержит результирующие значения.
Рис. 2.5. Таблица данных для проверки теоремы Пифагора
Отметим, что в ячейке ВЗ содержится формула =К0РЕНЬ((В1^2)+(В2^2)), которая используется при создании таблицы данных, но о том, как использовать формулы в процессе создания таблиц данных, будет рассказано в следующих разделах.
Создание таблиц данных с одним входом
Для создания в Excel таблиц данных ваши данные должны быть организованы соответствующим образом. Если создается таблица данных с одним входом, то входные данные должны располагаться в ячейках одного столбца или одной строки, а формула должна ссылаться на одну входную ячейку.
Формула может ссылаться на любое количество ячеек, но входная ячейка должна быть одна. Именно значение переменной, записанной в назначаемой вами входной ячейке, будет изменяться при создании таблицы данных. Значения в других ячейках, на которые ссылается формула, изменяться не будут.
Если уже записаны входные данные, то для создания таблицы данных с одним входом выполните следующие действия.
1. Введите формулу в соответствующую ячейку:
• если входные значения располагаются в столбце, то формулу следует ввести в ячейку, которая располагается на одну строку выше и на один столбец правее первой ячейки диапазона входных значений;
• если входные значения располагаются в строке, то формулу следует ввести в ячейку, которая располагается на одну строку ниже и на один столбец левее первой ячейки диапазона входных значений.
2. Выделите диапазон ячеек, содержащий ячейку с формулой и входные значения.
3. Выберите команду Данные => Таблица данных. Откроется диалоговое окно Таблица данных.
4. В диалоговом окне Таблица данных укажите входную ячейку:
• если входные значения располагаются в столбце, то введите адрес входной ячейки в поле ввода Подставлять значения по строкам в (или просто щелкните на входной ячейке, когда курсор будет находиться в этом поле);
• если входные значения располагаются в строке, то введите адрес входной ячейки в поле ввода Подставлять значения по столбцам в (или просто щелкните на входной ячейке, когда курсор будет находиться в этом поле).
5. Щелкните на кнопке ОК.
На рис. 2.6 показана таблица с одним входом, когда входные значения располагаются в столбце. Заметьте, что ячейка с формулой располагается на одну строку выше и на один столбец правее первой ячейки диапазона входных значений. В ячейке В2 и диапазоне A3:А12 содержатся целые числа. В ячейку В2 введена формула =В1*В1, вычисляющая квадрат числа, записанного в ячейку В1. Таблица данных автоматически вычислит значения в диапазоне ВЗ:В12.
Рис. 2.6. Таблица данных с одним входом для вычисления квадратов целых чисел (входные значения располагаются в столбце)
На рис. 2.7 показана таблица данных с одним входом, когда входные данные расположены в строке. Обратите внимание на то, что формула =В1*В1 находится в ячейке В2, которая располагается на одну строку ниже и на один столбец левее первой ячейки диапазона входных значений С1:К1, содержащего числа от 2 до 10. Ячейка В1 является входной ячейкой.
Рис. 2.7. Таблица данных с одним входом для вычисления квадратов целых чисел (входные значения располагаются в строке)
Создание таблиц данных с двумя входами
В отличие от таблиц с одним входом для таблиц двумя входами входные значения (соответствующие разным переменным) должны быть записаны в двух диапазонах, один из которых должен располагаться в столбце, а второй — в строке. Формула, по которой будут рассчитаны результирующие значения, должна ссылаться на две входные ячейки. Формула может ссылаться на любое количество ячеек, но входных ячеек должно быть две. Именно значения переменных, которые записаны в назначаемых вами входных ячейках, будут изменяться при создании таблицы данных. Значения в других ячейках, на которые ссылается формула, изменяться не будут.
Для создания таблицы с двумя входами следует выполнить такие действия.
1. Введите формулу, вычисленные значения которой составят результирующие значения для таблицы данных. Формула должна ссылаться на две входные ячейки, которые не войдут в таблицу данных.
2. Введите входные значения, соответствующие первой переменной, в вертикальный диапазон, который должен располагаться в том же столбце, где находится ячейка с формулой, и сразу под этой ячейкой.
3. Введите входные значения, соответствующие второй переменной, в горизонтальный диапазон, который должен располагаться в той же строке, где находится ячейка с формулой, и правее этой ячейки.
4. Выделите диапазон ячеек, содержащий формулу, строку и столбец входных значений.
5. Выберите команду Данные => Таблица данных. Откроется диалоговое окно Таблица данных.
6. В диалоговом окне Таблица данных в поле ввода Подставлять значения по строкам в введите адрес входной ячейки (или просто щелкните на входной ячейке, когда курсор будет находиться в этом поле), в которую будут подставляться входные значения, расположенные в столбце.
7. В диалоговом окне Таблица данных в поле ввода Подставлять значения по столбцам в введите адрес входной ячейки (или просто щелкните на входной ячейке, когда курсор будет находиться в этом поле), в которую будут подставляться входные значения, расположенные в строке.
8. Щелкните на кнопке ОК.
На рис. 2.8 показан рабочий лист с построенной таблицей данных с двумя входами. В этой таблице подсчитывается сумма очков в баскетболе, когда за свободные броски назначается одно очко, а за «обычные» броски с площадки — 2 очка. Обратите внимание на то, как располагаются вертикальный и горизонтальный диапазоны с входными значениями относительно ячейки с формулой.
Рис. 2.8. Пример таблицы данных с двумя входами
Удаление таблиц данных
Если после создания таблицы данных вы обнаружили, что входные значения подставляются не в те входные ячейки, в которые следует (т.е. вы неправильно указали адреса входных ячеек в полях ввода диалогового окна Таблица данных), то, естественно, вы захотите создать заново таблицу данных. Для этого выполните такие действия.
1. На рабочем листе в таблице данных выделите только результирующие значения.
2. Выполните команду Правка => Очистить => Содержимое (или просто на клавиатуре нажмите клавишу <Del>).
3. Создайте таблицу данных заново.
Если вы хотите полностью удалить таблицу данных со всеми формулами, входными ячейками, входными и результирующими значениями, выполните следующие действия.
1. Выделите все ячейки, которые вы желаете очистить.
2. Выполните команду Правка => Очистить =>Все.
Преобразование таблиц данных
Попробуйте в таблице данных изменить какое-либо результирующее значение — Excel выдаст сообщение об ошибке Изменить часть таблицы данных нельзя. Но если вам все же необходимо изменить вручную некоторые результирующие значения, то можно преобразовать эти значения в обычные числа (другими словами, можно просто удалить формулы, по которым вычислялись результирующие значения). Для этого выполните такие действия.
1. На рабочем листе в таблице данных выделите только результирующие значения.
2. Выполните команду Правка => Копировать.
3. Не снимая выделения с результирующих значений, выполните команду Правка => Специальная вставка. Откроется диалоговое окно Специальная вставка.
4. В диалоговом окне Специальная вставка в области Вставить установите переключатель Значения.
5. Щелкните в этом окне на кнопке ОК.
6. Нажмите клавишу <Enter> для выхода из режима копирования.
Отмена пересчета таблиц данных
Если вы пересчитываете рабочую книгу, и эта книга содержит таблицы данных, то но умолчанию эти таблицы также пересчитываются, даже если результирующие значения в них не изменяются. Если в рабочей книге содержатся несколько таблиц данных, и эти таблицы большие и выполняются по сложным формулам, то пересчет рабочей книги может занять заметное время. Можно исключить пересчет таблиц данных при пересчете всей рабочей книги, тем самым включив режим ручного пересчета для таблиц. Для этого выполните следующие действия.
1. Выберите команду Сервис => Параметры. Откроется диалоговое окно Параметры, щелкните в нем на вкладке Вычисления.
2. На этой вкладке в области Вычисления установите переключатель автоматически кроме таблиц.
3. Щелкните на кнопке ОК.
Для того чтобы пересчитать таблицу данных «по желанию» в ручном режиме, щелкните сначала на ячейке, содержащей формулу, а затем нажмите клавишу <F9> для пересчета всех открытых рабочих книги либо нажмите клавиши <Shift+F9> для пересчета только активного рабочего листа.
Если у вас не включен режим ручного пересчета для таблиц, то любые изменения во входных значениях автоматически приведут к пересчету таблицы данных - очень удобное свойство, значительно расширяющее возможности таблиц данных.
Теперь, когда вы знаете, как создавать таблицы данных, предлагаю несколько примеров и упражнений для практического закрепления навыков работы с ними.
Сделай сам: использование таблиц данных для подсчета накопленной суммы по вкладу
В упражнениях этого раздела вы используете таблицы данных с одним и двумя входами для подсчета накопленной суммы по банковскому вкладу. Первые строки рабочего листа, в котором будут созданы таблицы данных, показаны на рис. 2.9. Здесь в ячейках В1: В4 содержатся следующие начальные данные:
♦ в ячейке В1 — первоначальная сумма вклада;
♦ в ячейке В2 — годовая процентная ставка;
♦ в ячейке ВЗ — срок хранения вклада;
♦ в ячейке В4 подсчитывается конечная сумма вклада, для чего используется функция =БС(Ставка;Кпер;;—Пс). В этой функции аргумент Ставка задает ежемесячную процентную ставку (поэтому в нашей формуле этот аргумент равен В2/12), аргумент Кпер — срок хранения вклада (ячейка ВЗ), аргумент Пс — начальная сумма вклада (ячейка В1).
Рис. 2.9. Рабочий лист перед созданием таблиц данных для подсчета накопленной суммы по вкладу
Таблица данных с одним входом для подсчета накопленной суммы по вкладу
Начальный рабочий лист с необходимым содержимым, на котором будет создана таблица данных с одним входом, показан на рис. 2.9.
Предположим, что необходимо представить в виде таблицы конечные суммы по вкладам, если начальная сумма вклада изменяется от 10 000 до 100 000 руб. с шагом 10 000 руб. Чтобы создать такую таблицу, а также отформатировать ее, выполните следующие действия.
1. В ячейку А5 введите число 10000.
2. Выделите диапазон ячеек А5:А14.
3. Выберите команду Правка => Заполнить => Прогрессия. Откроется диалоговое окно Прогрессия.
4. В диалоговом окне Прогрессия в поле Шаг введите значение 10000.
5. Щелкните на кнопке ОК. В диапазоне А5:А14 будет создана последовательность входных значений (числа от 10 000 до 100 000 с шагом 10 000).
6. Выделите диапазон ячеек А4:В14.
7. Выберите команду Данные => Таблица данных. Откроется диалоговое окно Таблица данных.
8. В диалоговом окне Таблица данных в поле ввода Подставлять значения по строкам в введите В1 (или сначала щелкните на этом поле, а затем — на ячейке В1).
9. Щелкните на кнопке ОК. Таблица данных будет создана.
10. Выделите диапазон ячеек А5:В14.
11. Выберите команду Формат => Ячейки. Откроется диалоговое окно Формат ячеек.
12. В диалоговом окне Формат ячеек щелкните на вкладке Число и затем на этой вкладке в списке Числовые форматы выберите формат Финансовый.
13. Щелкните на кнопке ОК. Таблица данных будет отформатирована.
Сравните результат ваших действий с рабочим листом, показанным на рис. 2.10.
Рис. 2.10. Отформатированная таблица данных с одним входом для подсчета накопленной суммы по вкладу
Таблица данных с двумя входами для подсчета накопленной суммы по вкладу
Начальный рабочий лист с необходимым содержимым, на котором будет создана таблица данных с двумя входами, показана на рис. 2.9.
Предположим, что необходимо представить в виде таблицы конечные суммы по вкладам, если начальная сумма вклада изменяется от 10 000 до 100 000 руб. с шагом 10 000 руб., а время хранения вклада — от 12 до 60 месяцев (5 лет). Чтобы создать такую таблицу, а также отформатировать ее, выполните следующие действия.
1. В ячейку В5 введите число 10000.
2. Выделите диапазон ячеек В5:В 14.
3. Выберите команду Правка => Заполнить => Прогрессия. Откроется диалоговое окно Прогрессия.
4. В диалоговом окне Прогрессия в поле Шаг введите значение 10000.
5. Щелкните на кнопке ОК. В диапазоне В5:В14 будет создана последовательность входных значений (числа от 10 000 до 100 000 с шагом 10 000).
6. В ячейку С4 введите число 12.
7. Выделите диапазон ячеек C4:G4.
8. Выберите команду Правка => Заполнить => Прогрессия. Откроется диалоговое окно Прогрессия.
9. В диалоговом окне Прогрессия в поле Шаг введите значение 12.
10. Щелкните на кнопке ОК. В диапазоне C4:G4 будет создана другая последовательность входных значений (числа от 12 до 60 с шагом 12).
11. Выделите диапазон ячеек B4:G14.
12. Выберите команду Данные ^Таблица данных. Откроется диалоговое окно Таблица данных.
13. В диалоговом окне Таблица данных в поле ввода Подставлять значения по строкам в введите В1 (или сначала щелкните на этом поле, а затем — на ячейке В1).
14. В диалоговом окне Таблица данных в поле ввода Подставлять значения по столбцам в введите ВЗ (или сначала щелкните на этом поле, а затем — на ячейке ВЗ).
15. Щелкните на кнопке ОК. Таблица данных будет создана.
16. Выделите диапазон ячеек B5:G14.
17. Выберите команду Формат => Ячейки. Откроется диалоговое окно Формат ячеек.
18. В диалоговом окне Формат ячеек щелкните на вкладке Число и затем на этой вкладке в списке Числовые форматы выберите формат Финансовый.
19. Щелкните на кнопке ОК. Таблица данных будет отформатирована.
Сравните результат ваших действий с рабочим листом, показанным на рис. 2.11.
Рис. 2.11. Отформатированная таблица данных с двумя входами для подсчета накопленной суммы по вкладу
Сделай сам: использование таблиц данных для подсчета авторского гонорара
В упражнениях этого раздела вы используете таблицы данных с одним и двумя входами для подсчета авторского гонорара музыкальных исполнителей за проданные компакт-диски. Первые строки рабочего листа, в котором будут созданы таблицы данных, показаны на рис. 2.12. Здесь в ячейках В1:В5 содержатся следующие начальные данные:
♦ в ячейке В1 записана предполагаемая розничная цена одного компакт-диска;
♦ в ячейке В2 записана оптовая цена одного компакт-диска в виде процента от розничной цены;
♦ в ячейке ВЗ записана процентная ставка авторского гонорара;
♦ в ячейке В4 записано количество выпускаемых компакт-дисков;
♦ в ячейке В5 подсчитывается сумма авторского гонорара, которая вычисляется по простой формуле =В1*В2*ВЗ*В4.
Рис. 2.12. Рабочий лист перед созданием таблиц данных для расчетов авторского гонорара
Таблица данных с одним входом для подсчета авторского гонорара
Начальный рабочий лист с необходимым содержимым, на котором будет создана таблица данных с одним входом, показана на рис. 2.12.
Предположим, что необходимо представить в виде таблицы суммы авторского гонорара, если количество проданных компакт-дисков
изменяется от 10 000 до 100 000 с шагом 10 000. Чтобы создать такую таблицу, а также отформатировать ее, выполните следующие действия.
1. В ячейку А6 введите число 10000.
2. Выделите диапазон ячеек А6:А15.
3. Выберите команду Правка => Заполнить => Прогрессия. Откроется диалоговое окно Прогрессия.
4. В диалоговом окне Прогрессия в поле Шаг введите значение 10000.
5. Щелкните на кнопке ОК. В диапазоне А6:А15 будет создана последовательность входных значений (числа от 10 000 до 100 000 с шагом 10 000).
6. Выделите диапазон ячеек А5:В15.
7. Выберите команду Данные => Таблица данных. Откроется диалоговое окно Таблица данных.
8. В диалоговом окне Таблица данных в поле ввода Подставлять значения по строкам в введите В4 (или сначала щелкните на этом поле, а затем — на ячейке В4).
9. Щелкните на кнопке ОК. Таблица данных будет создана.
10. Выделите диапазон ячеек В6:В15.
11. Выберите команду Формат => Ячейки. Откроется диалоговое окно Формат ячеек.
12. В диалоговом окне Формат ячеек щелкните на вкладке Число и затем на этой вкладке в списке Числовые форматы выберите формат Финансовый.
13. Щелкните на кнопке ОК. Таблица данных будет отформатирована.
Сравните результат ваших действий с рабочим листом, показанным на рис. 2.13.
Рис. 2.13. Отформатированная таблица данных с одним входом для подсчета авторского гонорара
Таблица данных с двумя входами для подсчета авторского гонорара
Начальный рабочий лист с необходимым содержимым, на котором будет создана таблица данных с двумя входами, показана на рис. 2.12.
Предположим, что необходимо представить в виде таблицы суммы гонораров, если количество проданных компакт-дисков изменяется от 10 000 до 100 000 с шагом 10 000, а процентная ставка авторского гонорара — от 8 до 10% с шагом 0,5%. Чтобы создать такую таблицу, а также отформатировать ее, выполните следующие действия.
1. В ячейку В6 введите число 10000.
2. Выделите диапазон ячеек В6:В 15.
3. Выберите команду Правка => Заполнить => Прогрессия. Откроется диалоговое окно Прогрессия.
4. В диалоговом окне Прогрессия в поле Шаг введите значение 10000.
5. Щелкните на кнопке ОК. В диапазоне В6:В15 будет создана последовательность входных значений (числа от 10 000 до 100 000 с шагом 10 000).
6. В следующие ячейки введите такие значения:
в ячейку С5: 8,0%;
в ячейку D5: 8,5%;
в ячейку Е5: 9,0%;
в ячейку F5: 9,5%;
в ячейку G5:10,0%.
7. Выделите диапазон ячеек B5:G15.
8. Выберите команду Данные => Таблица данных. Откроется диалоговое окно Таблица данных.
9. В диалоговом окне Таблица данных в поле ввода Подставлять значения по строкам в введите В4 (или сначала щелкните на этом поле, а затем — на ячейке В4).
10. В диалоговом окне Таблица данных в поле ввода Подставлять значения по столбцам в введите ВЗ (или сначала щелкните на этом поле, а затем — на ячейке ВЗ).
11. Щелкните на кнопке ОК. Таблица данных будет создана.
12. Выделите диапазон ячеек C6:G15.
13. Выберите команду Формат => Ячейки. Откроется диалоговое окно Формат ячеек.
14. В диалоговом окне Формат ячеек щелкните на вкладке Число и затем на этой вкладке в списке Числовые форматы выберите формат Финансовый.
15. Щелкните на кнопке ОК. Таблица данных будет отформатирована.
Сравните результат ваших действий с рабочим листом, показанным на рис. 2.14.
Рис. 2.14. Отформатированная таблица данных с двумя входами для подсчета авторского гонорара
Сделай сам: использование таблиц данных для подсчета дивидендов
В упражнениях этого раздела вы используете таблицы данных с одним и двумя входами для подсчета дивидендов по акциям (или ценным бумагам). Первые строки рабочего листа, где будут созданы таблицы данных, показаны на рис. 2.15. Здесь в ячейках В1:В4 содержатся следующие начальные данные:
♦ в ячейке В1 — цена одной акции;
♦ в ячейке В2 — количество акций, находящихся в вашей собственности;
♦ в ячейке ВЗ — дивидендная доходность в расчете на одну акцию;
♦ в ячейке В4 общая сумма причитающихся вам дивидендов подсчитывается по простой формуле =В1*В2*ВЗ.
Рис. 2.15. Рабочий лист перед созданием таблиц данных для подсчета дивидендов
Таблица данных с одним входом для подсчета дивидендов
Начальный рабочий лист с необходимым содержимым, на котором будет создана таблица данных с одним входом, показана на рис. 2.15.
Предположим, что необходимо представить в виде таблицы суммы дивидендов, если количество акций изменяется от 25 000 до 300 000 с шагом 25 000. Чтобы создать такую таблицу, а также отформатировать ее, выполните следующие действия.
1. В ячейку А5 введите число 25000.
2. Выделите диапазон ячеек А5:А16.
3 - 3540
3. Выберите команду Правка => Заполнить => Прогрессия. Откроется диалоговое окно Прогрессия.
4. В диалоговом окне Прогрессия в поле Шаг введите значение 25000.
5. Щелкните на кнопке ОК. В диапазоне А5:А16 будет создана последовательность входных значений (числа от 25 000 до 300 000 с шагом 25 000).
6. Выделите диапазон ячеек А4:В 16.
7. Выберите команду Данные => Таблица данных. Откроется диалоговое окно Таблица данных.
8. В диалоговом окне Таблица данных в поле ввода Подставлять значения по строкам в введите В2 (или сначала щелкните на этом поле, а затем — на ячейке В2).
9. Щелкните на кнопке ОК. Таблица данных будет создана.
10. Выделите диапазон ячеек В5:В16.
11. Выберите команду Формат => Ячейки. Откроется диалоговое окно Формат ячеек.
12. В диалоговом окне Формат ячеек щелкните на вкладке Число и затем на этой вкладке в списке Числовые форматы выберите формат Финансовый.
13. Щелкните на кнопке ОК. Таблица данных будет отформатирована.
Сравните результат ваших действий с рабочим листом, показанным на рис. 2.16.
Pиc. 2.16. Отформатированная таблица данных с одним входом для подсчета дивидендов
Таблица данных с двумя входами для подсчета дивидендов
Начальный рабочий лист с необходимым содержимым, на котором будет создана таблица данных с двумя входами, показан на рис. 2.15.
Предположим, что необходимо представить в виде таблицы суммы дивидендов, если количество акций изменяется от 25 000 до 300 000 с шагом 25 000, а цена одной акции изменяется от 750 до 1000 руб. с шагом 50 руб. Чтобы создать такую таблицу, а также отформатировать ее, выполните следующие действия.
1. В ячейку В5 введите число 25000.
2. Выделите диапазон ячеек В5:В16.
3. Выберите команду Правка => Заполнить => Прогрессия. Откроется диалоговое окно Прогрессия.
4. В диалоговом окне Прогрессия в поле Шаг введите значение 25000.
5. Щелкните на кнопке ОК. В диапазоне В5:В16 будет создана последовательность входных значений (числа от 25 000 до 300 000 с шагом 25 000).
6. В ячейку С4 введите число 750.
7. Выделите диапазон ячеек С4:Н4.
8. Выберите команду Правка => Заполнить => Прогрессия. Откроется диалоговое окно Прогрессия.
9. В диалоговом окне Прогрессия в поле Шаг введите значение 50.
10.Щелкните на кнопке ОК. В диапазоне С4:Н4 будет создана последовательность входных значений (числа от 750 до 1000 с шагом 50).
11. Выделите диапазон ячеек В4:Н14.
12.Выберите команду Данные => Таблица данных. Откроется диалоговое окно Таблица данных.
13. В диалоговом окне Таблица данных в поле ввода Подставлять значения по строкам в введите В2 (или сначала щелкните на этом поле, а затем — на ячейке В2).
14. В диалоговом окне Таблица данных в поле ввода Подставлять значения по столбцам в введите В1 (или сначала щелкните на этом поле, а затем — на ячейке В1).
15. Щелкните на кнопке ОК. Таблица данных будет создана.
16. Выделите диапазон ячеек С4:Н16.
17. Выберите команду Формат => Ячейки. Откроется диалоговое окно Формат ячеек.
18. В диалоговом окне Формат ячеек щелкните на вкладке Число и затем на этой вкладке в списке Числовые форматы выберите формат Финансовый.
19. Щелкните на кнопке ОК. Таблица данных будет отформатирована.
Сравните результат ваших действий с рабочим листом, показанным на рис. 2.17.
Рис.2.17. Отформатированная таблица данных с двум входами для подсчета дивидендов
Ошибки при работе с таблицами данных
После того как в диалоговом окне Таблица данных для создания таблицы данных вы щелкнете на кнопке OK, Excel вместо желаемой таблицы может вывести на экран одно из следующих сообщений об ошибке.
Выделенная область недопустима. Это сообщение об ошибке появляется тогда, когда Excel не может использовать выделенную область как основу для создания таблицы данных. Причиной этого может быть то, что выделена только одна ячейка, либо выделенная область не является прямоугольником или состоит из несмежных диапазонов. Чтобы исправить эту ошибку, закройте сообщение об ошибке, правильно выделите область, где будет находиться таблица данных, и снова выполните команду Данные => Таблица данных.
Введенный текст не является правильной ссылкой или именем. Это сообщение об ошибке появляется тогда, когда в поле ввода Подставлять значения по строкам в или в поле ввода Подставлять значения по столбцам в диалогового окна Таблица данных введено нечто, что Excel не может распознать как ссылку на ячейку. Чаще всего такая ошибка возникает тогда, когда вы вручную вводите адрес ячейки, а не указываете ячейку, щелкая на ней. (Например, если вы вводите адрес ячейки «русскими» буквами.) Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле ввода правильный адрес ячейки. Затем снова щелкните на кнопке ОК.
При создании таблиц данных также распространены следующие ошибки.
♦ В диалоговом окне Таблица данных ячейка для входных значений, содержащихся в вертикальном диапазоне, задана в поле ввода Подставлять значения по столбцам в вместо поля Подставлять значения по строкам в.
♦ В диалоговом окне Таблица данных ячейка для входных значений, содержащихся в горизонтальном диапазоне, задана в поле ввода Подставлять значения по строкам в вместо поля Подставлять значения по столбцам в.
♦ При выделении области для создания таблицы данных в нее не включена ячейка с формулой.
Даже если Excel создаст таблицу данных, она может содержать не те результаты, которые вы ожидаете. Как заново создать таблицу данных, рассказано в разделе «Удаление таблиц данных» ранее в этой главе.
В этой главе мы изучили таблицы данных — простое и удобное в использовании средство, призванное представить в табличном формате результаты множественных вычислений формул при различных значениях входных переменных этих формул. Упражнения «Сделай сам» помогли закрепить практические навыки работы с таблицами данных. Наконец, вы узнали о тех сообщениях об ошибках, которые можно встретить при создании этих таблиц, и о том, как исправить такие ошибки.
Глава 3
Сценарии
Сценарий — это сохраненные как единое целое ячейки рабочего листа, содержащие значения и формулы. В этой главе вы узнаете, что такое сценарии, когда их следует использовать и как их создавать. Затем предложенный ряд примеров и упражнений поможет закрепить практические навыки работы со сценариями. В последнем разделе главы описаны возможные проблемы и ошибки, которые могут проявиться при работе со сценариями.
Назначение сценариев
Сценарии — это набор ячеек рабочего листа, которые Excel рассматривает как отдельную группу. В качестве различных сценариев можно сохранить разные входные значения для формул и результаты их вычисления при этих входных значениях. Excel имеет возможность быстрого переключения между различными сценариями.
Например, на рис. 3.1 и 3.2 показан рабочий лист с расчетами по ипотечной ссуде при разных входных значениях. Здесь в ячейке В1 записана процентная ставка по ссуде, в ячейке В2 записан срок в месяцах, на который берется ссуда, в ячейке ВЗ — сумма ссуды, а в ячейке В4 вычисляется величина ежемесячных выплат по этой ссуде. Назовем вычисления, показанные на рис. 3.1, сценарием ссуды для покупки 3-комнатной квартиры, а вычисления на рис. 3.2 — сценарием ссуды для покупки 4-комнатной квартиры.
Рис. 3.1. Сценарий ссуды для покупки 3-комнатной квартиры
Программа Excel предлагает средства для быстрого переключения между сценариями, поэтому у вас есть возможность просмотреть различные сценарии и выбрать наиболее подходящий, например, по величине ежемесячных выплат по ссуде. Конечно, вы можете создать и другие сценарии для других входных значений и включить их в группу сравниваемых сценариев.
Рис. 5.2. Сценарий ссуды для покупки 4-комнатной квартиры
Кроме быстрого переключения между различными сценариями, всего лишь несколько раз щелкнув кнопкой мыши, вы можете создать итоговый отчет по всем сценариям, представленный или в виде структурированного рабочего листа или в виде сводной таблицы (специальный тип таблиц, предназначенный для анализа данных).
Когда применяются сценарии
Сценарии применяются тогда, когда возникает необходимость сравнить и проанализировать ряд результатов однотипных расчетов, включающих входные значения и значения, вычисленные по определенным формулам.
Для примера на рис. 3.3 показан рабочий лист с расчетом пропорциональной амортизации некоторого оборудования. Здесь в ячейке В1 записана начальная стоимость оборудования, в ячейке В2 — его остаточная стоимость, в ячейке ВЗ — срок (в годах) эксплуатации оборудования, а в ячейке В4 рассчитываются ежегодные амортизационные отчисления.
Рис. 3.3. Сценарий для расчета амортизационных отчислений
На основе этого рабочего листа можно создать множество сценариев с различными сроками эксплуатации оборудования, различными начальной и остаточной стоимостями. Такие сценарии помогут вам экономически обосновать наиболее эффективную покупку нового оборудования.
Вы можете задать вполне законный вопрос о том, почему в ситуации этого примера следует использовать сценарии, а не средство Подбор параметра (см. главу 1) или таблицы данных (см. главу 2). На этот вопрос существуют следующие ответы, показывающие, когда использование сценариев для анализа данных более эффективно, чем другие средства Excel.
♦ Средство Подбор параметра позволяет за раз изменять значение только одной входной переменной. Например, в нашем примере средство Подбор параметра может изменить только одно из значений в ячейках Bl, B2 или ВЗ. Сценарии же позволяют комбинировать любое количество входных значений. Кроме того, средство Подбор параметра работает только с одной формулой, в сценариях можно сохранить результаты вычисления практически любого количества формул.
♦ Таблицы данных ограничены своим форматом двумерной таблицы. Поэтому с их помощью можно отследить влияние на формулу только одной или двух входных переменных. Сценарии не имеют такого ограничения.
В действительности в сценарии можно сохранить до 32 изменяемых значений, а общее количество сохраненных сценариев ограничено только объемом доступной памяти компьютера. Итоговые отчеты могут содержать данные из 251 сценария.
Создание и работа со сценариями
Для создания нового сценария или работы с ранее созданными сценариями перейдите на рабочий лист, содержащий эти сценарии или данные, на основе которых будет создан сценарий. Затем выберите команду Сервис => Сценарии, откроется диалоговое окно Диспетчер сценариев, показанное на рис. 3.4.
Диалоговое окно Диспетчер сценариев имеет следующие элементы управления.
♦ Список Сценарии содержит перечисление всех доступных сценариев, сохраненных в активном рабочем листе.
♦ В поле Изменяемые ячейки отображаются адреса ячеек, содержащие изменяемые значения для выбранного сценария. Если в активном рабочем листе нет сохраненных сценариев, то это поле будет пустым.
Рис. 3.4. Диалоговое окно Диспетчер сценариев
♦ В поле Примечание отображаются комментарии, которые вы запишете при создании сценария или Excel создаст самостоятельно. Если в активном рабочем листе нет сохраненных сценариев, то это поле будет пустым.
♦ Щелчок на кнопке Вывести приводит к тому, что на рабочем листе в ячейки, указанные в поле Изменяемые ячейки, записываются значения, сохраненные в выбранном сценарии. Если в активном рабочем листе нет сохраненных сценариев, то эта кнопка будет недоступна.
♦ Щелчок на кнопке Закрыть закрывает диалоговое окно Диспетчер сценариев.
♦ Щелчок на кнопке Добавить открывает диалоговое окно Добавление сценария, предназначенное для создания и сохранения нового сценария.
♦ Щелчок на кнопке Удалить приводит к удалению из рабочего листа выбранного сценария.
♦ Щелчок на кнопке Изменить открывает диалоговое окно Изменение сценария, предназначенное для редактирования сценария. Если в активном рабочем листе нет сохраненных сценариев, то эта кнопка будет недоступна.
♦ Щелчок на кнопке Объединить открывает диалоговое окно Объединение сценариев, предназначенное для объединения сценариев из разных рабочих листов.
♦ Щелчок на кнопке Отчет открывает диалоговое окно Отчет по сценарию, где создается итоговый отчет по выбранным сценариям в виде структурированного рабочего листа или в виде сводной таблицы. Если в активном рабочем листе нет сохраненных сценариев, то эта кнопка будет недоступна.
В следующих разделах будут описаны процессы создания, отображения, изменения и объединения сценариев, а также создания итогового отчета.
Создание нового сценария
Для создания нового сценария с помощью команды Сервис => Сценарии откройте диалоговое окно Диспетчер сценариев, в котором щелкните на кнопке Добавить. Откроется диалоговое окно Добавление сценария, показанное на рис. 3.5.
Рис. 3.5. Диалоговое, окно Добавление сценария
Диалоговое окно Добавление сценария содержит следующие элементы управления.
♦ В поле ввода Название сценария необходимо ввести название сценария.
♦ В поле ввода Изменяемые ячейки вводятся адреса ячеек, содержащих изменяемые значения, которые будут сохранены в сценарии.
♦ В поле ввода Примечание вы вводите комментарии к создаваемому сценарию. Если вы не введете комментарии, то Excel автоматически создаст примечание, содержащее имя создателя сценария (по зарегистрированному имени пользователя) и дату его создания. Если в дальнейшем сценарий будет изменен, то Excel автоматически к существующему примечанию добавит имя изменившего сценарий и дату изменения.
♦ Если установить флажок запретить изменения (он находится в области Защита), то изменяемые значения в сценарии будут защищены от изменений при условии, что будет защищен рабочий лист. Отменить защиту можно в диалоговом окне Изменение сценария, сняв флажок запретить изменения. Подробнее о защите сценариев речь идет в подразделе «Защита сценариев от изменений» далее в этой главе.
♦ Если установить флажок скрыть, то после установки защиты рабочего листа название данного сценария не будет отображаться в списке Сценарии диалоговое окно Диспетчер сценариев.
После того как в диалоговом окне Добавление сценария вы щелкнете на кнопке ОК, откроется следующее диалоговое окно Значения ячеек сценария, показанное на рис. 3.6. Это окно имеет по одному полю ввода для каждого изменяемого значения, где вы можете увидеть и при необходимости изменить эти значения.
Рис. 3.6. Диалоговое окно Значения ячеек сценария
Итак, чтобы создать новый сценарий для текущего рабочего листа, выполните следующие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить.
3. В диалоговом окне Добавление сценария введите название сценария в поле ввода Название сценария.
4. В поле ввода Изменяемые ячейки введите адреса изменяемых ячеек (вручную или путем выделения их непосредственно на рабочем листе).
5. Щелкните в диалоговом окне Добавление сценария на кнопке ОК.
6. В открывшемся диалоговом окне Значения ячеек сценария введите значения для изменяемых ячеек.
7. Щелкните в диалоговом окне Значения ячеек сценария на кнопке Добавить, чтобы создать сценарий и вернуться в диалоговое окно Добавление сценария, либо на кнопке ОК, чтобы создать сценарий и вернуться в диалоговое окно Диспетчер сценариев.
Отображение сценариев
Чтобы отобразить на рабочем листе результаты вычислений, выполненных при условии, что в изменяемые ячейки подставлены значения, сохраненные в сценарии, надо выполнить такие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев в списке Сценарии выберите сценарий, который вы хотите отобразить.
3. Щелкните на кнопке Вывести.
Редактирование сценария
Для редактирования (изменения) ранее созданного сценария в диалоговом окне Диспетчер сценариев щелкните на кнопке Изменить. Откроется диалоговое окно Изменение сценария, которое полностью совпадает с диалоговым окном Добавление сценария, показанным на рис. 3.5.
Итак, чтобы внести изменения в ранее созданный сценарий, выполните такие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев в списке Сценарии выберите сценарий, который вы хотите отредактировать, и щелкните на кнопке Изменить.
3. В диалоговом окне Изменение сценария можно изменить название сценария, адреса изменяемых ячеек и изменить или удалить примечание.
4. После внесения необходимых изменений в диалоговом окне Изменение сценария щелкните на кнопке ОК.
5. В открывшемся диалоговом окне Значения ячеек сценария (см. рис. 3.6) можно отредактировать отдельные значения для изменяемых ячеек.
6. В диалоговом окне Значения ячеек сценария щелкните на кнопке ОК, чтобы сохранить внесенные изменения и вернуться в диалоговое окно Диспетчер сценариев.
Удаления сценария
Чтобы удалить сценарий из текущего рабочего листа, выполните следующие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев в списке Сценарии выберите сценарий, который необходимо удалить.
3. Щелкните на кнопке Удалить.
Создание отчета по сценариям
Для создания отчета по сценариям откройте окно Диспетчер сценариев и щелкните в нем на кнопке Отчет. Excel откроет диалоговое окно Отчет по сценарию, показанное на рис. 3.7.
Рис. 3.7. Диалоговое окно Отчет по сценарию
Это диалоговое окно содержит следующие элементы управления.
♦ Если в области Тип отчета установлен переключатель структура, то на отдельном рабочем листе будет создана структура, в которой будут отображены последовательно все сценарии данного рабочего листа.
♦ Если в области Тип отчета установлен переключатель сводная таблица, то на отдельном рабочем листе будет автоматически создана сводная таблица, в которой будут представлены данные из всех сценариев текущего рабочего листа.
♦ В поле ввода Ячейки результата надо указать адреса ячеек с формулами, на основе значений которых будет построен итоговый отчет.
Тема создания сводных таблиц на основе сценариев, к сожалению, выходит за рамки данной книги. Однако эта тема достаточно полно и понятно описана в справочной системе Excel в томе «Создание сводных данных на листах и в таблицах».
Итак, для создания итогового отчета по всем сценариям текущего рабочего листа выполните такие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Отчет.
3. В диалоговом окне Отчет по сценарию установите один из переключателей в области Тип отчета — переключатель структура для создания итогового отчета в виде структурированного рабочего листа, либо переключатель сводная таблица для создания итогового отчета в виде сводной таблицы.
4. В поле ввода Ячейки результата введите адреса ячеек (вручную или путем выделения их непосредственно на рабочем листе), содержащих итоговые результаты.
5. Щелкните на кнопке ОК.
Итоговый отчет будет легко читаться и будет понятен с одного взгляда, если изменяемым ячейкам сценариев вы присвоите уникальные имена, соответствующие их «сущности». (Присвоить имена ячейкам можно, в частности, с помощью команды Вставка => Имя => Присвоить.) Например, изменяемой ячейке, содержащей значение процентной ставки, можно присвоить имя Процент_ставка.
Объединение сценариев
Для объединения сценариев, хранящихся в разных рабочих листах, которые могут принадлежать даже разным рабочим книгам, откройте окно Диспетчер сценариев и щелкните в нем на кнопке Объединить. Excel откроет диалоговое окно Объединение сценариев, показанное на рис. 3.8.
Рис. 3.8. Диалоговое окно Объединение сценариев
Это диалоговое окно содержит следующие элементы управления.
♦ В раскрывающемся списке Книга содержатся названия всех открытых рабочих книг.
♦ В списке Лист содержатся названия всех рабочих листов той рабочей книги, которая выбрана в списке Книга.
Для объединения сценариев, содержащихся в разных рабочих листах, выполните следующие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Объединить.
3. В диалоговом окне Объединение сценариев в раскрывающемся ч списке Книга выберите рабочую книгу, сценарии из которой будут объединены со сценариями текущего рабочего листа.
4. В списке Лист выберите рабочий лист, содержащий сценарии, подлежащие объединению.
5. Щелкните на кнопке ОК.
И Объединение сценариев из разных рабочих листов может привести к непредвиденным результатам, поскольку эти сценарии могут хранить разные адреса изменяемых ячеек, даже если такие ячейки содержат однотипные данные. Чтобы избежать возможных проблем, советуем дать одинаковые имена изменяемым ячейкам, содержащим однотипные значения и расположенным на разных рабочих листах. (Присвоить имена ячейкам можно с помощью команды Вставка => Имя => Присвоить.) Например, изменяемым ячейкам, содержащим значения процентной ставки, на всех рабочих листах можно присвоить имя Процент_ставка независимо от их фактического местоположения (будь-то ячейка В1 на листе Лист1 или ячейка С20 на листе Лист2).
Защита сценариев от изменений
Для защиты сценариев от возможных изменений выполните следующие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев в списке Сценарии щелкните на том сценарии, который хотите защитить от изменений, и затем щелкните на кнопке Изменить.
3. В диалоговом окне Изменение сценария в области Защита установите флажок запретить изменения и/или флажок скрыть (в этом случае данный сценарий не будет отображаться в списке Сценарии диалогового окна Диспетчер сценариев).
4. Щелкните на кнопке ОК.
5. В открывшемся диалоговом окне Значения ячеек сценария щелкните на кнопке ОК.
6. В диалоговом окне Диспетчер сценариев щелкните на кнопке Закрыть.
7. Чтобы ввести в действие защиту сценария, выполните команду Сервис => Защита => Защитить лист.
8. В открывшемся диалоговом окне Защита листа в поле ввода Пароль для отключения защиты листа введите свой пароль, если вы хотите, чтобы отключение защиты сценария было возможно только после введения соответствующего пароля.
Если вы решили применить пароль, обязательно запишите его и сохраните в надежном месте — Excel не сможет подсказать забытый вами пароль.
9. В диалоговом окне Защита листа установите флажок Защитить лист и содержимое защищаемых ячеек.
10. В списке Разрешить всем пользователям этого листа снимите флажок изменение сценариев (если он установлен).
11. Щелкните на кнопке ОК.
Для отключения защиты сценариев выберите команду Сервис => Защита => Снять защиту листа. Если вы использовали пароль, то появится диалоговое окно Снять защиту листа, в котором вы должны ввести пароль. После ввода правильного пароля щелкните в этом окне на кнопке ОК.
Хотя изменение и удаление созданных ранее сценариев в защищенном рабочем листе невозможно, создавать новые сценарии на таком же листе можно. При этом разрешается изменять значения в защищенных ячейках, но нельзя их (ячейки) удалять.
Теперь, когда вы знаете, как работать со сценариями, пришло время приступить к практическим занятиям.
Сделай сам: использование сценариев для расчета стоимости разработки программных продуктов
В этом примере сценарии будут использованы для расчета стоимости разработки программных продуктов в некоторой фирме, занимающейся созданием программного обеспечения (ПО). Рабочий лист, подготовленный для выполнения таких расчетов, показан на рис. 3.9. Отметим, что всем изменяемым ячейкам присвоены соответствующие имена. Например, ячейка В10 имеет имя Тест_1.
Таблица для расчета стоимости состоит из двух разделов.
♦ В диапазоне A1.D3 записаны количества необходимых для выполнения программного проекта разработчиков ПО, руководителей групп разработчиков и менеджеров программных проектов, а также их почасовые ставки. Количество руководителей групп вычисляется в ячейке В2 таким образом, чтобы на четырех разработчиков приходилось не менее одного руководителя. Например, для шести разработчиков необходимо два руководителя групп.
♦ В диапазоне A5:D16 приведен список этапов программного проекта, для каждого этапа определены количество часов (на одного исполнителя) и исполнители, необходимые для выполнения этого этапа, а также вычисляется стоимость каждого этапа.
Сценарий наихудшего случая
Сначала создадим сценарий самого худшего случая разработки ПО, когда разработчиков не хватает, у них высокая почасовая ставка, и вследствие этого сокращено время выполнения отдельных этапов проекта. Для создания такого сценария выполните следующие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить.
3. В открывшемся диалоговом окне Добавление сценария в поле Название сценария введите Самый худший случай.
4. Щелкните в поле Изменяемые ячейки и затем выделите на рабочем листе сначала ячейку В1, затем при нажатой клавише <Ctrl> ячейки ВЗ, D1:D3 и В6:В15. В этом поле должно отобразиться $B$1;$B$3;$D$1 :$D$3;$B$6:$B$ 15.
5. В диалоговом окне Добавление сценария щелкните на кнопке ОК.
6. В открывшемся диалоговом окне Значение ячеек сценариев введите следующие значения для изменяемых ячеек, нажимая клавишу <Таb> по завершении ввода каждого числа.
К_разр: 3
Кмен: 1
Ст_разр: 100
Ст_рук: 120
Ст_мен: 150
Разр_спец: 2
Утв_спец: 1
Код_первое: 30
Код_второе: 20
Аттестация_1: 1
Код_третье: 10
Отладка: 10
Аттестация _2:1
Подг_версии: 2
Утв_версии: 1
7. Щелкните на кнопке ОК.
8. Щелкните на кнопке Закрыть.
Сценарий наилучшего случая
В наилучшем случае разработки программного продукта участвует достаточное число специалистов, они имеют относительно низкие почасовые ставки, а на выполнение этапов проекта отводится достаточное количество времени.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить.
3. В открывшемся диалоговом окне Добавление сценария в поле Название сценария введите Самый лучший случай.
4. Если вы ранее создали сценарий наихудшего случая, описанный в предыдущем подразделе, то в этом случае в поле Изменяемые ячейки уже будут отображаться адреса изменяемых ячеек B1;B3;D1:D3;B6:B15.
5. В диалоговом окне Добавление сценария щелкните на кнопке ОК.
6. В открывшемся диалоговом окне Значение ячеек сценариев введите следующие значения для изменяемых ячеек, нажимая клавишу <Таb> по завершении ввода каждого числа.
К_разр: 15
К_мен: 2
Ст_разр: 75
Ст_рук: 90
Ст_мен: 110
Разр_спец: 8
Утв_спец: 2
Код_первое: 100
Код_второе: 80
Аттестация _1:10
Код_третье: 60
Отладка: 50
Аттестация_2:10
Подг_версии: 15
Утв_версии: 5
7. Щелкните на кнопке ОК.
8. Щелкните на кнопке Закрыть.
Отчет по сценариям
Теперь просмотрим созданные сценарии, а затем построим на их основе итоговый отчет. Для этого выполните такие действия.
1. Выполните команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев в списке Сценарии щелкните на сценарии Самый худший случай, а затем — на кнопке Вывести. На рабочем листе в изменяемые ячейки будут введены значения, сохраненные в этом сценарии, и вы увидите результаты вычислений при таких значениях.
3. Для просмотра результатов вычислений в наилучшем случае в диалоговом окне Диспетчер сценариев в списке Сценарии щелкните на сценарии Самый лучший случай, а затем — на кнопке Вывести.
4. Для создания отчета в диалоговом окне Диспетчер сценариев щелкните на кнопке Отчет.
5. В открывшемся диалоговом окне Отчет по сценарию установите переключатель структура.
6. В диалоговом окне Отчет по сценарию щелкните в поле Ячейки результата, а затем на рабочем листе выделите диапазон D6:D 16. После этого в данном поле должно отображаться =$D$6:$D$16.
7. Щелкните на кнопке ОК.
Сделай сам: использование сценариев для прогноза объемов продаж
В этом примере сценарии будут использованы для прогнозирования объемов продаж безалкогольных напитков региональным оптовым дистрибьютерам. Рабочий лист, подготовленный для выполнения таких расчетов, показан на рис. 3.11. Отметим, что всем изменяемым ячейкам присвоены соответствующие имена. Например, ячейка С5 имеет имя Северный_розн.
Рис. 3.11. Таблица для прогноза объемов продаж
Таблица для расчета объемов продаж состоит из двух разделов.
♦ В диапазоне А1:В2 записаны предполагаемая розничная цена одной упаковки напитков и оптовая скидка, вычисляемая как процент от розничной цены.
♦ В диапазоне A4:D9 по каждому региону приведены предполагаемые объемы продаж упаковок напитков, их розничная и чистая (оптовая) стоимости.
Летний сценарий
Сначала создадим сценарий для летнего сезона продаж. Для создания такого сценария выполните следующие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить.
3. В открывшемся диалоговом окне Добавление сценария в поле Название сценария введите Летний сценарий.
4. Щелкните в поле Изменяемые ячейки и затем выделите на рабочем листе сначала ячейки В1:В2, затем при нажатой клавише <Ctrl> - ячейки В5:В8.
5. В диалоговом окне Добавление сценария щелкните на кнопке ОК.
6. В открывшемся диалоговом окне Значение ячеек сценариев введите следующие значения для изменяемых ячеек, нажимая клавишу <Таb> по завершении ввода каждого числа.
Розн_цена: 210
Скидка: 0,15
Северный: 45000
Восточный: 52000
Южный: 58000
Западный: 42000
7. Щелкните на кнопке ОК.
8. Щелкните на кнопке Закрыть.
Зимний сценарий
Теперь создадим сценарий для зимнего сезона продаж.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить.
3. В открывшемся диалоговом окне Добавление сценария в поле Название сценария введите Зимний сценарий.
4. Если вы ранее создали летний сценарий, описанный в предыдущем подразделе, то в этом случае в поле Изменяемые ячейки уже будут отображаться адреса изменяемых ячеек В1:В2;В5:В8.
5. В диалоговом окне Добавление сценария щелкните на кнопке ОК.
6. В открывшемся диалоговом окне Значение ячеек сценариев введите следующие значения для изменяемых ячеек, нажимая клавишу <Таb> по завершении ввода каждого числа.
Розн_цена: 170
Скидка: 0,17
Северный: 29000
Восточный: 33250
Южный: 38000
Западный: 26500
7. Щелкните на кнопке ОК.
8. Щелкните на кнопке Закрыть.
Отчет по сценариям
Теперь просмотрим созданные сценарии, а затем построим на их основе итоговый отчет. Для этого выполните такие действия.
1. Выполните команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев в списке Сценарии щелкните на сценарии Летний сценарий, а затем — на кнопке Вывести. На рабочем листе в изменяемые ячейки будут введены значения, сохраненные в этом сценарии, и вы увидите результаты вычислений при этих значениях.
3. Для просмотра результатов вычислений в наилучшем случае в диалоговом окне Диспетчер сценариев в списке Сценарии щелкните на сценарии Зимний сценарий, а затем — на кнопке Вывести.
4. Для создания отчета в диалоговом окне Диспетчер сценариев щелкните на кнопке Отчет.
5. В открывшемся диалоговом окне Отчет по сценарию установите переключатель сводная таблица.
6. В диалоговом окне Отчет по сценарию щелкните в поле Ячейки результата, а затем на рабочем листе выделите диапазон C5:D9. После этого в данном поле должно отображаться =$C$5:$D$9.
7. Щелкните на кнопке ОК.
Сделай сам: использование сценариев для прогноза проката видеокассет
В этом примере сценарии будут использованы для прогнозирования объемов проката видеокассет в некой сети пунктов проката. Рабочий лист, подготовленный для выполнения таких расчетов, показан на рис. 3.13. Отметим, что всем изменяемым ячейкам присвоены соответствующие имена. Например, ячейка В7 имеет имя К_птн.
Таблица для расчета объемов проката состоит из трех разделов.
♦ В ячейке В1 записан базовый средний ежедневный объем проката видеокассет в расчете на один пункт проката.
♦ В диапазоне A3: В9 приведены весовые коэффициенты объемов проката для каждого дня недели. Например, коэффициент 1,1 в ячейке В7 означает, что в пятницу в одном пункте проката средний объем проката составляет 110% от базового объема, записанного в ячейке В1.
♦ В диапазоне A12J17 приведен список количества пунктов проката в некоторых городах, и для каждого дня недели вычисляются ожидаемые объемы проката.
Сценарий на неделю проката блокбастера
Сначала создадим сценарий на неделю, когда в прокате появится блокбастер. Для создания такого сценария выполните следующие действия.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить.
3. В открывшемся диалоговом окне Добавление сценария в поле Название сценария введите Неделя с блокбастером.
4. Щелкните в поле Изменяемые ячейки и затем выделите на рабочем листе сначала ячейки В1, затем при нажатой клавише <Ctrl> ячейки ВЗ:В9 и В13:В16.
5. В диалоговом окне Добавление сценария щелкните на кнопке ОК.
6. В открывшемся диалоговом окне Значение ячеек сценариев введите следующие значения для изменяемых ячеек, нажимая клавишу <Таb> по завершении ввода каждого числа.
Среднее: 300
К_пон: 0,7
К_вт: 1,6
К_ср: 0,9
К_чет: 1,0
К_птн: 1,3
К_суб: 1,2
К_вос: 1,1
П_Москва: 12
П_Питер: 15
П_Новгрд: 8
П_Тверь: 3
7. Щелкните на кнопке ОК.
8. Щелкните на кнопке Закрыть.
Сценарий проката в обычную неделю
Теперь создадим сценарий для проката в обычную неделю.
1. Выберите команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить.
3. В открывшемся диалоговом окне Добавление сценария в поле Название сценария введите Обычная неделя.
4. Если вы ранее создали сценарий недели с блокбастером, описанный в предыдущем подразделе, то в этом случае в поле Изменяемые ячейки уже будут отображаться адреса изменяемых ячеек В1;ВЗ:В9;В13:В16.
5. В диалоговом окне Добавление сценария щелкните на кнопке ОК.
6. В открывшемся диалоговом окне Значение ячеек сценариев введите следующие значения для изменяемых ячеек, нажимая клавишу <Таb> по завершении ввода каждого числа.
Среднее: 200
К_пон: 0,5
К_вт: 1,0
К_ср: 0,6
К_чет: 0,7
К_птн: 1,1
К_суб: 1,0
К_вос: 0,9
П_Москва: 12
П_Питер: 15
П_Новгрд: 8
П_Тверь: 3
7. Щелкните на кнопке ОК.
8. Щелкните на кнопке Закрыть.
Отчет по сценариям
Теперь просмотрим созданные сценарии, а затем построим на их основе итоговый отчет. Для этого выполните такие действия.
1. Выполните команду Сервис => Сценарии.
2. В открывшемся диалоговом окне Диспетчер сценариев в списке Сценарии щелкните на сценарии Неделя с блокбастером, а затем на кнопке Вывести. На рабочем листе в изменяемые ячейки будут введены значения, сохраненные в этом сценарии, и вы увидите результаты вычислений при этих значениях.
3. Для просмотра результатов вычислений в наилучшем случае в диалоговом окне Диспетчер сценариев в списке Сценарии щелкните на сценарии Обычная неделя, а затем — на кнопке Вывести.
4. Для создания отчета в диалоговом окне Диспетчер сценариев щелкните на кнопке Отчет.
5. В открывшемся диалоговом окне Отчет по сценарию установите переключатель структура.
6. В диалоговом окне Отчет по сценарию щелкните в поле Ячейки результата, а затем на рабочем листе выделите диапазон C5:D9. После этого в данном поле должно отображаться =$C$6:$D$9.
7. Щелкните на кнопке ОК.
Сравните ваш отчет с отчетом на рис. 3.14.
Ошибки при работе со сценариями
При создании нового сценария или при изменении существующего могут появиться следующие сообщения об ошибках.
По крайней мере в одной из изменяющихся ячеек содержится формула. При использовании сценария формулы будут заменены на постоянные значения. Это сообщение появляется тогда, когда в диалоговом окне Добавление сценария или окне Изменение сценария в поле ввода Изменяемые ячейки введены адреса ячеек, содержащих формулы. Если вы сохраните эти адреса, то при просмотре сценария формулы в этих ячейках будут заменены на те значения, которые вы укажете в диалоговом окне Значение ячеек сценариев. Если этого необходимо избежать, закройте сначала сообщение, а затем в открытом диалоговом окне щелкните на кнопке Отмена. После этого вы вернетесь в диалоговое окно Диспетчер сценариев, откуда можно повторить попытку создания или изменения сценария.
Имена сценариев должны быть уникальными. Это сообщение об ошибке появляется тогда, когда новому сценарию вы даете имя уже существующего сценария. Если в списке сценариев диалогового окна Диспетчер сценариев нет сценария с задаваемым именем, а описываемое сообщение все равно появилось, то это значит, что сценарий с таким именем все же существует, но он не отображается в списке Сценарии. Такое возможно, если этот сценарий защищен и скрыт (см. подраздел «Защита сценариев от изменений»). Чтобы разрешить эту коллизию, присвойте новому сценарию другое имя, либо снимите защиту с рабочего листа и сценария и удалите сценарий с таким именем.
Введенный текст не является правильной ссылкой или именем. Это сообщение об ошибке появляется тогда, когда в диалоговом окне Добавление сценария или окне Изменение сценария в поле ввода Изменяемые ячейки введено нечто, что Excel не может распознать как ссылку на ячейку. Чаще всего такая ошибка возникает тогда, когда вы вручную вводите адрес ячейки, а не указываете ячейку путем щелчка на ней. (Например, если вы вводите адрес ячейки «русскими» буквами.) Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле ввода правильный адрес ячейки. Затем снова щелкните на кнопке ОК.
При работе со сценариями необходимо также учитывать следующие ограничения.
♦ Нельзя отменить удаление сценария. Если нужно восстановить удаленный сценарий, вы должны создать его заново.
♦ Если вы вывели сценарий и он заменил значения на рабочем листе, то восстановить автоматически исходные значения невозможно. Для восстановления исходных значений придется ввести их вручную, либо надо предусмотреть такую ситуацию заранее и создать еще один сценарий с исходными значениями. Эта ситуация бывает особенно обидной, когда значения из сценария заменяют формулы. К сожалению, не существует способа восстановить эти формулы — их придется вводить заново.
В этой главе мы изучили сценарии — простое и удобное в использовании средство для сохранения значений группы ячеек рабочего листа. Вы узнали, когда и как создавать сценарии, как их изменять, выводить, объединять и как на их основе создать итоговый отчет. Упражнения «Сделай сам» помогли закрепить практические навыки работы со сценариями. Наконец, вы узнали о тех сообщениях об ошибках, которые можно встретить при работе со сценариями, и о том, как исправить такие ошибки.
Глава 4
Поиск решения
Средство Поиск решения позволяет находить для одной формулы такие значения ее входных переменных, которые приводили бы к точно заданному значению, либо минимально или максимально возможному значение. Это средство позволяет также налагать ограничения на значения любых переменных, от которых зависит результат вычисления формулы.
В этой главе вы узнаете, что такое Поиск решения, когда его целесообразно применять и как с ним работать. Затем предложенный ряд примеров и упражнений поможет закрепить практические навыки работы с этим средством. В последнем разделе главы описаны возможные проблемы и ошибки, которые могут проявиться при использовании средства Поиск решения.
Назначение средства «Поиск решения»
Если говорить упрощенно, то средство Поиск решения путем изменения значений в заданных ячейках (такие ячейки называются изменяемыми ячейками) добивается того, чтобы в ячейке с формулой (называется целевой ячейкой) было или определенное (заданное вами) значение, либо чтобы эта формула принимала минимально или максимально возможное значение. Можно также наложить ограничения на изменяемые значения, причем эти ограничения могут быть как прямыми (например, значения не должны выходить из определенного интервала), так и опосредованными, когда несколько изменяемых значений связаны каким-либо соотношением и ограничение налагается на это соотношение.
Для примера рассмотрим задачу вычисления оптимальных цен и количеств театральных билетов разной категории таким образом, чтобы стоимость всех проданных билетов достигала определенной величины. В главе 1 эту задачу мы уже пробовали решить с помощью средства Подбор параметра. Напомним, что в театре имеются билеты трех категорий: детские, для взрослых и льготные (рис. 4.1).
В целевой ячейке В6 подсчитывается сумма стоимостей билетов всех категорий (для каждой категории билетов их стоимость подсчитывается как произведение количества билетов и их цены).
Рис. 4.1. Рабочий лист для поиска оптимальной цены театральных билетов
В главе 1 при использовании средства Подбор параметра за один раз мы могли изменять значение только одной переменной: значение цены или количество билетов одной категории. Средство Поиск решения также может изменять значения только одной переменной (т.е. может работать в режиме подбора параметра). Однако, чтобы оценить гибкость и мощь этого средства, следует использовать его для изменения нескольких переменных. Кроме того, это средство может добиться от целевой ячейки (точнее, от формулы, содержащейся в этой ячейке) не только определенного заранее значения, но и максимально или минимально возможного (для этой формулы) значения.
Кроме того, подчеркнем еще раз, в отличие от средства Подбор параметра средство Поиск решения позволяет налагать разнообразные ограничения на значения изменяемых переменных. Например, Поиск решения может найти, сколько необходимо продать театральных билетов для того, чтобы в кассе оказалось ровно 24 тыс. руб., при выполнении следующих ограничений.
♦ Цена детских билетов постоянна и равна 60 руб.
♦ Цена билетов для взрослых постоянна и равна 100 руб.
♦ Цена льготных билетов постоянна и равна 90 руб.
♦ Билетов каждой категории можно продать не более 100 шт.
♦ В театре может быть аншлаг, но «лишних билетиков» нет.
Поиск решения немедленно найдет решение этой задачи: для того, чтобы в кассе оказалось ровно 24 тыс. руб., необходимо продать 100 детских, 90 для взрослых и 100 льготных билетов. Поиск решения по специальному алгоритму перебирает возможные комбинации значений количеств билетов разной категории для того, чтобы найти искомое решение.
Другой пример решения задачи с ограничениями показан на рис. 4.2. Здесь некий завод производит три вида изделий, но может производить в смену суммарно не более 300 изделий. Необходимо определить при этом ограничении, а также при условии, что должно производиться не менее 30 шт. любого изделия, количество выпускаемых изделий таким образом, чтобы получить максимальный доход. Ответ, который дает Поиск решения, показан на том же рис. 4.2.
Рис. 4.2. Определение оптимальной структуры производства
Когда применяется «Поиск решения»
Средство Поиск решения предназначено для решения задач оптимизации. Это средство широко применяется для решения финансовых, деловых задач и задач управления ресурсами (понимая «ресурсы» в самом широком смысле: персонал, оборудование, материалы, время и т.д.). Средство Поиск решения также применяется в научных исследованиях для решения математических уравнений и выполнения самых разнообразных расчетов.
Ранее мы быстро «прошли» средство Подбор параметра, которое часто можно использовать в тех же ситуациях, что и средство Поиск решения. Однако, поскольку Поиск решения очень мощное и более «разнообразное» в применении средство, чем Подбор параметра, его изучение займет у нас значительно больше времени и потребует от вас определенных усилий. Так как оба средства, Поиск решения и Подбор параметра, решают примерно одинаковый круг задач, рассмотрим подробнее, в чем же состоят основные преимущества первого перед вторым.
♦ Подбор параметра может работать только с одной изменяемой ячейкой (где записано значение некоторой переменной), тогда как Поиск решения может работать с несколькими изменяемыми ячейками. Максимальное количество изменяемых ячеек, с которыми одновременно может работать Поиск решения, равно 200.
♦ Поиск решения может найти изменяемые значения, которые обеспечат не только определенное заданное значение целевой ячейки (как это делает Подбор параметра), но и минимально или максимально возможное значение целевой ячейки (что Подбор параметра делать не может даже для одной изменяемой переменной).
♦ Поиск решения позволяет налагать ограничения (условия) на изменяемые значения (Подбор параметра такого делать не позволяет). Средство Поиск решения позволяет задавать до 500 ограничений — по два простых односторонних ограничения на значения каждой из 200 возможных изменяемых переменных и дополнительно еще 100 ограничений. (Отметим, что если в диалоговом окне Параметры поиска решения установлен флажок Линейная модель, то в этом случае количество ограничений теоретически не ограничено.)
♦ Установки средства Подбор параметра в рабочей книге не сохраняются, тогда как установки для последнего выполнения средства Поиск решения сохраняются автоматически. Более того, можно сохранить установки для нескольких выполнений средства Поиск решения (такие установки этого средства называются моделью). В дальнейшем можно легко воспроизвести любую из сохраненных моделей и заново выполнить средство Поиск решения.
Средство Подбор параметра также имеет одно преимущество перед Поиск решения — Подбор параметра можно использовать тогда, когда изменяемая ячейка и целевая ячейка с формулой находятся на разных рабочих листах. Для средства Поиск решения изменяемые ячейки и целевая ячейка должны находиться на одном рабочем листе.
Теперь, заинтриговав читателя выдающимися свойствами средства Поиск решения, приступим к изучению того, как с ним работать и как его применить для решения своих задач.
Работа со средством «Поиск решения»
Прежде чем перейти к непосредственной работе со средством Поиск решения, изучим терминологию, которую необходимо знать
для квалифицированной работы с этим средством. В нелегком деле освоения новой терминологии нам поможет рис. 4.3, где показан рабочий лист для подсчета программистских «багов» (ошибок) в процессе выполнения некоторого проекта по разработке программного обеспечения.
♦ Целевая ячейка — ячейка с формулой, в которой Поиск решения установит заданное значение или для которой найдет минимально или максимально возможные значения. На рис. 4.3 любая ячейка из диапазонов B6:F7 и G2:H7 может быть целевой.
♦ Целевая функция — это термин из теории оптимизации, который описывает цель, которую мы хотим достичь, решая данную задачу (и используя для этого Поиск решения). Здесь «цель» заключается в том, чтобы формула в целевой ячейке достигла определенного значения. Например, выбирая ячейку G7 в качестве целевой ячейки (см. рис. 4.3), мы хотим, чтобы средство Поиск решения нашло такие значения в изменяемых ячейках, которые обеспечили бы в этой ячейке установление значения 25.
♦ Изменяемые ячейки — ячейки, значения в которых будет варьировать Поиск решения для того, чтобы достичь требуемого значения целевой функции. На рис. 4.3 изменяемыми ячейками могут быть любые ячейки из диапазона B2:F5.
♦ Ограничения — условия, налагаемые на возможные значения изменяемых ячеек. Для примера на рис. 4.3, если в качестве изменяемых ячеек задать диапазон ячеек B2:F5, то на значения этих ячеек можно наложить ограничения двух типов. Во-первых, эти значения не должны быть меньше 2. Во-вторых, эти значения должны быть целыми числами.
♦ Модель — совокупность адресов целевой и изменяемых ячеек, а также всех ограничений, используемых средством Поиск решения для решения текущей задачи, которые оно сохранило как единое целое.
Если применить средство Поиск решения к данным на рабочем листе (см. рис. 4.3), указав в качестве целевой ячейки ячейку G7 и значение 25 как значение целевой функции, указав в качестве изменяемых ячеек диапазон В2:В5, на значения которых налагаются ограничения: эти значения не должны быть меньше 2 и должны быть целыми числами, то Поиск решения найдет решение, которое показано на рис. 4.4.
Рис. 4.4. Решение, найденное средством Поиск решения для текущей модели
Установка средства «Поиск решения»
Поскольку средство Поиск решения не всегда устанавливается при инсталляции программы Excel, прежде чем использовать это средство, вы должны удостовериться, что оно для вас доступно, т.е. убедитесь, что в меню Сервис есть команда Поиск решения. Если в этом меню нет такой команды, выберите команду Сервис => Надстройки и в открывшемся диалоговом окне Надстройки в списке Доступные надстройки установите флажок Поиск решения. Затем щелкните на кнопке ОК — в меню Сервис должна появиться команда Поиск решения.
Если в списке Доступные надстройки диалогового окна Надстройки нет опции Поиск решения, необходимо переустановить саму программу Excel и в процессе ее переустановки выбрать Поиск решения в списке доступных надстроек. После завершения переустановки Excel в меню Сервис должна появиться команда Поиск решения.
Параметры диалогового окна «Поиск решения»
После выбора команды Сервис => Поиск решения открывается одноименное диалоговое окно, показанное на рис. 4.5.
Диалоговое окно Поиск решения содержит следующие элементы управления (перечисление идет сверху вниз и слева направо).
♦ В поле ввода Установить целевую ячейку вводится адрес ячейки рабочего листа, обязательно содержащей формулу, играющую роль целевой функции.
♦ Выбором одного из трех переключателей в области Равной вы указываете, какое значение должна принимать целевая функция.
• Выбор переключателя максимальному значению указывает, что целевая функция должна достичь своего наибольшего значения при условии выполнения всех ограничений.
• Выбор переключателя минимальному значению указывает, что целевая функция должна достичь своего наименьшего значения при условии выполнения всех ограничений.
• Выбор переключателя значению указывает, что целевая функция при условии выполнения всех ограничений должна достичь определенного значения, которое задается в соседнем поле ввода. По умолчанию это значение равно 0.
♦ В поле ввода Изменяя ячейки вводятся адреса изменяемых ячеек, значения которых Поиск решения будет варьировать при попытке установить в целевой ячейке заданное значение. Формула в целевой ячейке обязательно должна ссылаться, прямо или опосредованно (через другие промежуточные формулы), на эти ячейки.
♦ Кнопка Предположить используется для автоматического поиска ячеек, содержащих значения (не формулы) и влияющих на формулу в целевой ячейке.
♦ Список Ограничения содержит перечень всех ограничений, установленных для данной задачи.
♦ Щелчок на кнопке Добавить открывает диалоговое окно Добавление ограничения, где можно задать новое ограничение.
♦ Щелчок на кнопке Изменить открывает диалоговое окно Изменение ограничения, где можно изменить ограничение, предварительно выбранное в списке Ограничения.
♦ Щелчок на кнопке Удалить удаляет ограничение, выбранное в списке Ограничения.
♦ Щелчок на кнопке Выполнить — начало работы средства Поиск решения.
♦ Щелчок на кнопке Закрыть закрывает диалоговое окно Поиск решения, при этом данное средство не начинает работу.
После щелчка на кнопке Закрыть средство Поиск решения хотя не начинает свою работу, однако сохраняет все сделанные в диалоговом окне Поиск решения установки. Поэтому при следующем открытии данного окна вы их увидите снова, если, конечно, активным будет тот же самый рабочий лист.
♦ Щелчок на кнопке Параметры открывает диалоговое окно Параметры поиска решения, где можно задать дополнительные установки и параметры для решения данной задачи.
♦ Щелчок на кнопке Восстановить очищает диалоговое окно Поиск решения от всех сделанных установок и показывает его в первозданно чистом виде со значениями элементов управления, принятыми по умолчанию.
♦ Щелчок на кнопке Справка открывает тему справочной системы Excel, посвященную работе с этим диалоговым окном. (Аналогичные действия выполняют кнопки Справка в других диалоговых окнах средства Поиск решения. Поэтому данную кнопку при описании других диалоговых окон мы упоминать не будем.)
Итак, для того, чтобы заставить Поиск решения выполнить свое предназначение, следуйте инструкции.
1. Выберите команду Сервис =>Поиск решения. Откроется диалоговое окно Поиск решения.
2. Если вы хотите работать с чистым окном Поиск решения, щелкните на кнопке Восстановить.
3. Щелкните в поле ввода Установить целевую ячейку и введите адрес целевой ячейки (или просто щелкните на этой ячейке рабочего листа).
4. Установите один из переключателей в области Равной. Если вы установили переключатель значению, то введите соответствующее число.
5. Щелкните в поле ввода Изменяя ячейки и введите адреса или выделите на рабочем листе изменяемые ячейки.
6. Если необходимо ввести ограничения, щелкните на кнопке Добавить и в открывшемся диалоговом окне Добавление ограничения создайте ограничение. О том, как создавать ограничения, речь идет в следующем подразделе.
7. Щелкните на кнопке Выполнить.
8. По завершении работы средства Поиск решения откроется окно Результаты поиска решения, где щелкните на кнопке ОК.
В следующих разделах показано, как создавать и изменять ограничения, как работать с диалоговыми окнами Параметры поиска решения и Результаты поиска решения, как сохранять и загружать модели.
Создание и изменение ограничений
Если в диалоговом окне Поиск решения вы щелкнете на кнопке Добавить, то откроется диалоговое окно Добавление ограничения, показанное на рис. 4.6.
Рис. 4.6. Диалоговое окно Добавление ограничения
Чтобы создать новое ограничение, выполните такие действия.
1. В поле ввода Ссылка на ячейку введите адрес (или просто выделите на рабочем листе) ячейки или диапазон ячеек, для содержимого которых необходимо задать ограничение.
2. В раскрывающемся списке, которое находится справа от поля Ссылка на ячейку, выберите элемент, определяющий тип условия, которое будет налагаться на содержимое изменяемых ячеек.
• Выберите элемент <=, если вы хотите, чтобы содержимое ячейки (ячеек), указанной в поле Ссылка на ячейку, не превышало заданной константы.
• Выберите элемент =, если вы хотите, чтобы содержимое ячейки (ячеек), указанной в поле Ссылка на ячейку, было равно заданной константе.
• Выберите элемент =>, если вы хотите, чтобы содержимое ячейки (ячеек), указанной в поле Ссылка на ячейку, было не меньше заданной константы.
• Выберите элемент цел, если вы хотите, чтобы содержимое ячейки (ячеек), указанной в поле Ссылка на ячейку, было целым числом. Если вы выберете этот элемент, то в соседнем поле Ограничение автоматически появится слово целое. Такое ограничение можно налагать на значения только изменяемых ячеек.
• Выберите элемент двоич, если вы хотите, чтобы содержимое ячейки (ячеек), указанной в поле Ссылка на ячейку, могло принимать только два значения: Да - Нет, Истина-Ложь или 0-1. Если вы выберете этот элемент, то в соседнем поле Ограничение автоматически появится слово двоичное. Такое ограничение можно налагать на значения только изменяемых ячеек.
Наложение на изменяемые значения условия целочисленное™ резко увеличивает сложность решаемой задачи, что может привести к возрастанию времени вычислений или даже прерыванию процесса вычислений до получения необходимого результата.
3. Если вы выбрали условия типа <=, => или =, в поле ввода Ограничение введите число, ссылку на ячейку или формулу, вычисляющую его.
4. Щелкните на кнопке ОК для того, чтобы созданное ограничение было принято и для возврата в окно Поиск решения. Щелкните на кнопке Добавить для того, чтобы созданное ограничение было принято, при этом остается открытым окно Добавление ограничения, и вы можете создать новое ограничение. Щелкните на кнопке Отмена для возврата в окно Поиск решения без создания ограничения.
Для внесения изменений в существующее ограничение выберите его в списке Ограничения диалогового окна Поиск решения и щелкните на кнопке Изменить. Открывшееся окно Изменение ограничения полностью повторяет окно Добавление ограничения. В этом окне можно изменить адрес ячейки в поле Ссылка на ячейку, изменить тип условия либо ввести новую константу в поле Ограничение.
Параметры поиска решения
Если в диалоговом окне Поиск решения вы щелкнете на кнопке Параметры, то откроется диалоговое окно Параметры поиска решения, показанное на рис. 4.7.
Рис. 4.7. Диалоговое окно Параметры поиска решения
Это диалоговое окно содержит следующие опции и параметры.
♦ В поле ввода Максимальное время задается максимальное время (в секундах) решения задачи средством Поиск решения. Хотя максимальное значение, которое можно ввести в это поле, составляет 32 767 секунд (более 9 часов!), значения по умолчанию (100 секунд) вполне достаточно для решения большинства относительно небольших задач. Если средство Поиск решения не успеет найти решение за указанное время, оно сделает паузу и спросит у вас, закончить ли вычисления и принять текущие значения за окончательное решение или продолжить вычисления в течение еще одного временного периода.
♦ В поле ввода Предельное число итераций задается максимальное число итераций для нахождения промежуточных решений. Как и в поле Максимальное время, здесь можно ввести максимально допустимое число 32 767, однако значения по умолчанию (100 итераций) вполне достаточно для решения большинства относительно небольших задач. Если средство Поиск решения не найдет решения в течение этого количества итераций, оно сделает паузу и спросит у вас, закончить ли вычисления и принять текущие значения за окончательное решение или продолжить вычисления в течение еще такого же количества итераций.
♦ Поле ввода Относительная погрешность служит для задания точности выполнения ограничений и соответствия вычисленного значения в целевой ячейке заданному. Число в этом поле должно быть дробным из интервала от 0 до 1, при этом, чем меньше данное число, тем более высокая степень точности вычисленного результата. Отметим, что Поиск решения быстрее найдет решение, если установить меньшую точность.
♦ В поле ввода Допустимое отклонение задается максимальное отклонение в процентах для целочисленных решений. Этот параметр имеет смысл только в том случае, если задано хотя бы одно целочисленное ограничение. Чем выше значение в этом поле, тем быстрее Поиск решения найдет искомое решение, но достоверность этого решения будет меньше. По умолчанию допустимое отклонение равно 5%.
♦ Параметр Сходимость применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Число в этом поле должно быть дробным из интервала от 0 до 1, при этом, чем меньше данное число, тем более высокая степень точности вычисленного результата. Отметим, что Поиск решения быстрее найдет решение, если установить большее значение этого параметра. По умолчанию он равен 0,0001.
♦ Если установлен флажок Линейная модель, то к текущей задаче Поиск решения применяет линейную модель, что ускоряет поиск решения. Линейная модель предполагает, что все зависимости между изменяемыми значениями и формулой в целевой ячейке описываются линейными функциями. Линейная функция — это такая функция, которую можно записать в виде суммы парных произведений переменных и констант. Нелинейная функция — более сложная, чем линейная, функция. Если установлен флажок Линейная модель, то для решения задачи Поиск решения применяет быстрый и эффективный метод, называемый симплекс-методом. Если же этот флажок не установлен, то применяется градиентный метод, который более сложен для выполнения.
♦ Установка флажка Неотрицательные значения задает нулевую нижнюю границу для тех изменяющихся значений, для которых не были явно заданы нижние границы.
♦ Флажок Автоматическое масштабирование служит для включения автоматической нормализации входных и выходных значений, значительно различающихся по величине. Если в вашей задаче есть переменные, которые могут принимать очень большие значения (например, значения денежных сумм) и другие переменные, которые принимают малые значения (например, проценты, которые записываются в виде дроби), то в этом случае значительно возрастает сложность применяемых градиентных методов и вероятность того, что Поиск решения не сможет найти желаемого решения, особенно в нелинейных моделях. Поэтому примите практический совет: всегда устанавливайте флажок этого параметра.
♦ Если установлен флажок Показывать результаты итераций, то после выполнения очередной итерации поиск решения приостанавливается и на экран выводятся результаты, найденные на этой итерации.
♦ В области Оценки представлены два переключателя, которые служат для указания метода экстраполяции — линейный или квадратичный — используемого для получения исходных оценок значений переменных в каждом одномерном поиске.
• Переключатель линейная установлен по умолчанию. Он устанавливается, если решается линейная задача, когда используется линейная экстраполяция вдоль касательного вектора. При выборе этого переключателя Поиск решения работает быстрее, чем при установленном переключателе квадратичная, однако точность решения понижается.
•Установленный переключатель квадратичная показывает, что используется квадратичная экстраполяция, которая дает лучшие результаты при решении нелинейных задач.
♦ В области Разности также представлены два переключателя, которые служат для указания метода численного дифференцирования, который используется для вычисления частных производных целевой функции и функций ограничений.
• Переключатель прямые установлен по умолчанию и используется в большинстве задач, где скорость изменения ограничений относительно невысока.
• При установленном переключателе центральные используются центральные разности, которые применимы к функциям, имеющим разрывные производные.
Метод нахождения конечных разностей, задаваемый переключателем центральные, требует больше вычислений, однако его применение может быть оправданным, если Поиск решения выдает сообщение о том, что получить более точное решение не удается.
♦ Переключатели в области Метод поиска позволяют выбрать ^алгоритм оптимизации для решения данной задачи.
• При выборе переключателя Ньютона средство Поиск решения использует модифицированный метод Ньютона. Реализация этого метода требует больше компьютерной памяти, однако выполняет меньше итераций.
• Переключатель сопряженных градиентов указывает на то, что будет использоваться метод сопряженных градиентов. Данный метод используется тогда, когда решаемая задача достаточно велика и необходимо экономить память, а также если итерации дают слишком малое отличие в последовательных приближениях.
После установки в диалоговом окне Параметры поиска решения нужных опций (или проверки их значений по умолчанию) остается щелкнуть на одной из кнопок, расположенных в правой части этого окна.
♦ Щелчок на кнопке ОК сохраняет установки диалогового окна Параметры поиска решения и возвращает в диалоговое окно Поиск решения.
♦ Щелчок на кнопке Отмена не сохраняет сделанные установки диалогового окна Параметры поиска решения и возвращает в диалоговое окно Поиск решения.
♦ Щелчок на кнопке Загрузить модель открывает диалоговое окно Загрузка модели, в котором для загрузки ранее сохраненной модели надо указать адрес диапазона ячеек рабочего листа, содержащего параметры этой модели.
♦ Щелчок на кнопке Сохранить модель открывает одноименное диалоговое окно, в котором нужно задать адрес диапазона ячеек рабочего листа, где будут записаны параметры текущей модели. Используйте эту возможность сохранить модель в том случае, если у вас на рабочем листе реализовано несколько моделей — последняя модель сохраняется автоматически при сохранении рабочей книги.
Сохранение и загрузка моделей
Моделью в средстве Поиск решения называется совокупность целевой ячейки, всех изменяемых ячеек и всех ограничений, заданных для решения текущей задачи. Вместо того, чтобы каждый раз указывать эти установки в диалоговом окне Поиск решения, можно сохранить их на рабочем листе и вызывать по мере необходимости для применения средства Поиск решения.
Последние установки, сделанные в диалоговом окне Поиск решения, можно сохранить, выполнив команду Файл => Сохранить.
Сохранение модели
Для сохранения созданной модели выполните такие действия.
1. С помощью Поиск решения найдите решение задачи, модель которой хотите сохранить.
2. Выберите команду Сервис => Поиск решения.
3. В открывшемся диалоговом окне Поиск решения щелкните на кнопке Параметры.
4. В диалоговом окне Параметры поиска решения щелкните на кнопке Сохранить модель.
5. В диалоговом окне Сохранить модель введите или укажите местоположение первой ячейки вертикального диапазона, в котором будут записаны установки данной модели.
6. Щелкните на кнопке ОК.
По возможности не сохраняйте модели непосредственно под данными задачи, поскольку диапазон ячеек, где будет храниться модель, может быть очень большим. Используйте для хранения моделей свободные области рабочего листа, где может поместиться несколько моделей.
Если предполагается сохранить несколько разных моделей, то для них можно выделить отдельный рабочий лист или даже отдельную рабочую книгу. Кроме того, к каждой модели рекомендуем добавлять комментарии с кратким описанием решаемой задачи.
Средство Поиск решения записывает параметры модели в вертикальный диапазон ячеек, как показано на рис. 4.8.
Рис. 4.8. Модель, сохраненная на рабочем листе
В ячейках рабочего листа, показанного на рис. 4.8, хранятся следующие сведения о модели.
♦ В ячейке К14 записано, что средство Поиск решения должно найти максимальное значение для формулы, записанной в ячейке А12.
♦ В ячейке К15 записан адрес А1:А10 диапазона изменяемых ячеек.
♦ В ячейке К16 записано ограничение, налагаемое на значения ячеек диапазона А5:А8.
♦ В ячейке К17 записаны установки диалогового окна Параметры поиска решения:
• максимальное время — 100 с;
• предельное число итераций — 100;
• относительная погрешность — 0,000001;
• допустимое отклонение — 0,05 (5%);
• установлен флажок Линейная модель (значение ИСТИНА в последовательности значений в ячейке К17);
• не установлен флажок Неотрицательные значения (значение ЛОЖЬ в ячейке К17);
• установлен флажок Автоматическое масштабирование (значение ИСТИНА);
• установлен переключатель линейная (значение 1 в последовательности значений в ячейке К17);
• установлен переключатель прямые (значение 1 в ячейке К17);
• установлен переключатель Ньютона (значение 1);
• сходимость — 0,0001;
• не установлен флажок Показывать результаты итераций (значение ЛОЖЬ).
Установленные переключатели квадратичная, центральные и сопряженных градиентов имеют номер 2. Например, если в диалоговом окне Параметры поиска решения установлены переключатели линейная, центральные и Ньютона, то в записи модели будет тройка чисел 1:2:1.
Загрузка модели
Для загрузки ранее сохраненной модели надо выполнить следующие действия.
1. Выберите команду Сервис => Поиск решения.
2. В открывшемся диалоговом окне Поиск решения щелкните на кнопке Параметры.
3. В диалоговом окне Параметры поиска решения щелкните на кнопке Загрузить модель.
4. В диалоговом окне Загрузка модели введите или укажите местоположение диапазона ячеек, в котором записаны установки данной модели. Например, для примера, показанного на рис. 4.8, надо указать диапазон К14:К17.
5. Щелкните на кнопке ОК сначала в диалоговом окне Загрузка модели, затем в окне Параметры поиска решения.
6. В диалоговом окне Поиск решения щелкните на кнопке Выполнить.
При загрузке модели в диалоговом окне Загрузка модели необходимо указать все ячейки, в которых записаны параметры модели. Иначе, если не будут указаны все ячейки, то установки в диалоговых окнах Поиск решения и Параметры поиска решения, которые остались в «неуказанных» ячейках, будут замещены установками по умолчанию. В этом случае применение средства Поиск решения может привести к непредсказуемым результатам.
Работа с диалоговым окном «Результаты поиска решения»
При остановке работы средства Поиск решения появляется диалоговое окно Результаты поиска решения, показанное на рис. 4.9. (Причиной остановки работы средства Поиск решения может быть не только завершение поиска решения, независимо от того, найдено или не найдено требуемое решение, но оно также может завершиться по требованию пользователя и по некоторым другим причинам.)
Рис. 4.9. Диалоговое окно Результаты поиска решения
Прервать работу средства Поиск решения можно, нажав клавишу <Esc>. В этом случае Excel пересчитает рабочий лист с теми значениями изменяемых ячеек, которые имели место на последней прерванной итерации.
Если средство Поиск решения нашло решение, то оно в диалоговом окне Результаты поиска решения может отобразить одно из двух следующих сообщений.
Решение найдено. Все ограничения и условия оптимальности выполнены. Это сообщение означает, что все ограничения выполнены с точностью, установленной в диалоговом окне Параметры поиска решения, и найдено оптимальное (максимальное или минимальное) значение целевой функции, либо она принимает заданное значение (опять же с заданной точностью). Для задач с ограничениями целочисленности это сообщение означает, что найденное решение находится в пределах допустимого отклонения, заданного в диалоговом окне Параметры поиска решения.
Поиск свелся к текущему решению. Все ограничения выполнены. Это сообщение может появиться только при решении нелинейных задач. В этом случае поиск решения прекращен, поскольку разности значений целевой функции в течение последних пяти итераций не превышали значения, заданного в поле Сходимость диалогового окна Параметры поиска решения.
Если уменьшить значение в поле Сходимость диалогового окна Параметры поиска решения, то Поиск решения найдет более точное решение. Однако потратит на это значительно больше времени.
Если средство Поиск решения остановилось, не найдя решения, то в диалоговом окне Результаты поиска решения появятся другие сообщения, которые будут описаны в последнем разделе этой главы.
В диалоговом окне Результаты поиска решения, показанном на рис. 4.9, установка переключателя Сохранить найденное решение означает, что вы принимаете найденное решение и не возражаете, чтобы Excel поместила в изменяемые ячейки значения, составляющие найденное решение. Выбор переключателя Восстановить исходные значения приведет в восстановлению исходных значений в изменяемых ячейках.
В этом диалоговом окне в списке Тип отчета можно выбрать тип отчета с результатами поиска решения, который Excel создаст на отдельном листе рабочей книги. Эти отчеты будут описаны ниже.
Щелкните на кнопке ОК для того, чтобы принять установки, сделанные в диалоговом окне Результаты поиска решения, или щелкните на кнопке Отмена, если хотите восстановить исходные значения в изменяемых ячейках.
В диалоговом окне Результаты поиска решения есть также кнопка Сохранить сценарий, щелчок на которой открывает диалоговое окно Диспетчер сценариев. О том, что такое сценарий и как работать с диалоговым окном Диспетчер сценариев, подробно рассказано в главе 3.
Диалоговое окно «Текущее состояние поиска решения»
Если в диалоговом окне Параметры поиска решения вы установили флажок Показывать результаты итераций или если в процессе вычислений средство Поиск решения достигло временного предела, установленного в поле Максимальное время диалогового окна Параметры поиска решения, или выполнило предельное число итераций, заданное в том же диалоговом окне, то выводится диалоговое окно Текущее состояние поиска решения, показанное на рис. 4.10.
Рис. 4.10. Диалоговое окно Текущее состояние поиска решения
В этом окне Excel может вывести такие сообщения.
Поиск решения приостановлен. Текущие значения показаны на рабочем листе. Это сообщение появится тогда, когда в диалоговом окне Параметры поиска решения вы установили флажок Показывать результаты итераций.
Время, установленное для поиска решения, истекло. Продолжить? Это сообщение появится тогда, когда время вычисления Поиск решения достигло значения, установленного в поле Максимальное время диалогового окна Параметры поиска решения.
Достигнуто максимальное число итераций. Продолжить? Это сообщение появится тогда, когда Поиск решения выполнит то количество итераций, которое установлено в поле Предельное число итераций диалогового окна Параметры поиска решения.
Если в диалоговом окне Текущее состояние поиска решения щелкнуть на кнопке Стоп, то Поиск решения завершит вычисления и отобразит диалоговое окно Результаты поиска решения. Если вы щелкнете на кнопке Продолжить, то Поиск решения продолжит вычисления и может отобразить следующее окно Текущее состояние поиска решения (если вы установили флажок Показывать результаты итераций в диалоговом окне Параметры поиска решения). Щелчок на кнопке Сохранить сценарий позволяет сохранить текущее состояние поиска решения в виде сценария.
Создание отчетов по результатам поиска решения
Если средство Поиск решения нашло решение, то Excel предоставляет возможность на основе полученного решения создать отчеты следующих типов.
♦ В отчете Результаты выводятся исходные и полученные в результате поиска решения значения изменяемых ячеек и целевой функции, а также сведения об ограничениях задачи.
♦ Отчет Устойчивость дает основную информацию для анализа чувствительности линейных и нелинейных моделей. Этот анализ показывает, насколько чувствительно оптимальное решение к небольшим изменениям параметров модели. Этот тип отчета будет недоступен, если в модели используются ограничения целочисленности.
♦ Отчет Пределы представляет собой ограниченный вариант отчета Устойчивость. Здесь показаны наименьшее и наибольшее значения, которые может принимать каждая изменяемая переменная целевая функция. Этот тип отчета будет недоступен, если в модели используются ограничения целочисленности.
Для создания отчетов в диалоговом окне Результаты поиска решения (рис. 4.9) в списке Тип отчета выберите один или несколько типов отчетов и щелкните на кнопке ОК. Соответствующие отчеты будут созданы на новых листах в текущей рабочей книге, каждый отчет — на отдельном рабочем листе.
Отчет «Результаты»
В отчете Результаты, показанном на рис. 4.11, содержатся следующие данные.
♦ Адреса целевой ячейки и изменяемых ячеек и их имена (если они заданы) и значения в этих ячейках до начала выполнения Поиск решения и после завершения.
♦ Адреса ячеек, на значения которых налагаются ограничения, имена этих ячеек (если они заданы), значения в этих же ячейках, формулы ограничений, статус ограничения (связанное или не связанное) и значения разностей.
Рис. 4.11. Отчет Результаты
Значения разностей — это абсолютные разности между вычисленными значениями правых и левых частей неравенств. Если значение разности для некоторого ограничения равно нулю, то это ограничение называется связанным или лимитирующим, поскольку оно лимитирует найденное решение. Если значение разности для ограничения не равно нулю, то такое ограничение называется несвязанным или не лимитирующим, — найденное решение не зависит от этого ограничения.
Отчет «Устойчивость»
Данный тип отчета показан на рис. 4.12. Напомним, что такой отчет доступен только для задач, которые не имеют ограничений целочисленности. В этом отчете содержатся следующие данные.
♦ В таблице Изменяемые ячейки приведена информация о значениях изменяемых ячеек:
• адреса и имена (если заданы) изменяемых ячеек;
• значения этих ячеек, найденные средством Поиск решения;
• нормированная стоимость, показывающая, насколько изменится значение целевой функции, если на единицу изменится значение в данной изменяющейся ячейке при условии, что это значение достигло своей верхней или нижней границы;
• целевой коэффициент — коэффициент, стоящий при данной изменяемой переменной в уравнении целевой функции;
• значения в столбцах Допустимое увеличение и Допустимое уменьшение показывают, в каких пределах может изменяться целевой коэффициент при условии, что найденное значение целевой функции останется неизменным.
Для нелинейных задач в таблице Изменяемые ячейки будут отсутствовать столбцы Допустимое увеличение и Допустимое уменьшение, а вместо столбца Нормированная стоимость будет столбец Нормированный градиент (аналог нормированной стоимости для нелинейных задач).
♦ В таблице Ограничения приведена информация об ограничениях:
• адреса и имена (если заданы) ячеек, на значения которых наложены ограничения;
• значения в этих ячейках, найденные средством Поиск решения;
• теневая цена показывает, на сколько изменится значение целевой функции, если на единицу изменится значение правой части данного ограничения при условии, что это изменение лежит в пределах, указанных в столбцах Допустимое увеличение и Допустимое уменьшение;
• значения правых частей ограничений;
• значения в столбцах Допустимое увеличение и Допустимое уменьшение показывают, в каких пределах может изменяться значение правой части ограничения при условии, что найденное значение целевой функции останется неизменным.
Для нелинейных задач в таблице Ограничения будут отсутствовать столбцы Ограничения, правая часть, Допустимое увеличение и Допустимое уменьшение, а вместо столбца Теневая цена будет столбец Лагранжа множитель (аналог теневой цены для нелинейных задач).
Значения 1Е+30 в столбце Допустимое увеличение (или Допустимое уменьшение) таблиц Изменяемые ячейки и Ограничения показывают, что допускается неограниченное возрастание (или убывание) значения соответственно целевого коэффициента или правой части ограничения.
Рис. 4.12. Отчет Устойчивость
Отчет «Пределы»
Данный тип отчета показан на рис. 4.13. Напомним, что такой отчет доступен только для задач, которые не имеют ограничений целочисленности. В этом отчете показано значение в целевой ячейке. Для ограничений приведены значения в изменяемых ячейках и возможные значения целевой функции, если значения в изменяемых ячейках достигнут своих допустимых нижних и верхних границ (определяются заданными ограничениями).
Рис. 4.13. Отчет Пределы
Если на изменяемую переменную не налагаются ограничения, задающие ее верхнюю (или нижнюю) границу, то в столбцах Верхний предел и Целевой результат (или Нижний предел и Целевой результат) для этой переменной будут стоять значения #Н/Д, как показано на рис. 4.13.
Теперь, когда вы знаете, как работать со средством Поиск решения, пришло время приступить к практическим занятиям.
Сделай сам: использование средства «Поиск решения» для
решения простых математических задач
В данном разделе покажем, как Поиск решения используется для решения простых математических задач. Рабочий лист, подготовленный для выполнения таких расчетов, приведен на рис. 4.14. Он состоит из двух частей: верхняя часть рабочего листа используется для вычисления объема параллелепипеда, если известны его длина, ширина и высота; в нижней части вычисляется расстояние, пройденное объектом за определенное время при известной скорости.
Рис. 4.14. Рабочий лист для решения простых математических задач
Задача вычисления объема
Предположим, что ширина параллелепипеда равна 4 и объем равен 80. Необходимо найти длину и высоту параллелепипеда при условии, что все его параметры выражаются целыми числами. Для решения этой задачи с помощью средства Поиск решения выполните следующие действия.
1. Выберите команду Сервис => Поиск решения.
2. В диалоговом окне Поиск решения щелкните на кнопке Восстановить.
3. В поле Установить целевую ячейку введите В6 или щелкните в этом поле и затем — на ячейке В6.
4. Выберите переключатель значению и введите в соседнее поле число 80.
5. Щелкните в поле Изменяя ячейки, затем выделите диапазон ВЗ:В5.
6. Щелкните на кнопке Добавить.
7. В диалоговом окне Добавление ограничения щелкните в поле Ссылка на ячейку, затем щелкните на ячейке В4 (либо просто введите в это поле В4).
8. В соседнем раскрывающемся списке выберите элемент =.
9. В поле Ограничение введите число 4.
10. Щелкните на кнопке Добавить.
11. Щелкните в поле Ссылка на ячейку, затем выделите диапазон ВЗ:В5.
12. В раскрывающемся списке выберите элемент цел.
13. Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения, которое должно совпадать с тем, которое показано на рис. 4.15.
Рис. 4.15. Диалоговое окно Поиск решения, заполненное для решения задачи
14. Щелкните на кнопке Выполнить. Затем щелкните на кнопке ОК
в открывшемся окне Результаты поиска решения. Сравните ваш результат с рабочим листом на рис. 4.16.
Рис. 4.16. Решение задачи вычисления объема
Задача вычисления расстояния
Предположим, что объект проехал 125 км со скоростью 70 км/ч. Необходимо вычислить время, затраченное на преодоление этого расстояния. Для решения этой задачи с помощью средства Поиск решения выполните следующие действия.
1. Выберите команду Сервис => Поиск решения.
2. В диалоговом окне Поиск решения щелкните на кнопке Восстановить.
3. В поле Установить целевую ячейку введите В12 или щелкните в этом поле и затем — на ячейке В12.
4. Выберите переключатель значению и введите в соседнее поле число 125.
5. Щелкните в поле Изменяя ячейки, затем выделите диапазон В10:В11.
6. Щелкните на кнопке Добавить.
7. В диалоговом окне Добавление ограничения щелкните в поле Ссылка на ячейку, затем щелкните на ячейке В11 (либо просто введите в это поле В11).
8. В соседнем раскрывающемся списке выберите элемент =.
9. В поле Ограничение введите число 70.
10. Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения, которое должно совпадать с тем, которое показано на рис. 4.17.
11. Щелкните на кнопке Выполнить. Затем щелкните на кнопке ОК в открывшемся окне Результаты поиска решения.
Рис.4.17. Диалоговое окно Поиск решения, заполненное для решения задачи
Сравните ваш результат с рабочим листом на рис. 4.18.
Рис.4.18. Решение задачи вычисления расстояния
Сделай сам: использование средства «Поиск решения» для аукционных расчетов
В этом разделе показано, как Поиск решения используется для расчетов на аукционах, проводимых в онлайновом режиме на Web-узле аукционов. Рабочий лист, подготовленный для выполнения таких расчетов, показан на рис. 4.19.
Рис. 4.19. Рабочий лист для аукционных расчётов
На этом рабочем листе представлены такие данные.
♦ В столбце А приведены названия лотов (в данном случае это изделия из драгоценных металлов и камней).
♦ Стартовая цена лотов (столбец В).
♦ Шаг возрастания цены лота для каждой поданной заявки (столбец С).
♦ Количество поданных заявок по каждому лоту (столбец D).
♦ В столбце Е вычисляется текущая цена лота (она равна стартовой цене, плюс произведение шага возрастания цены и количества заявок).
♦ Количество дней, в течение которых лот выставлен на продажу (столбец F).
♦ В столбце G вычисляется среднее увеличение цены в день (оно равно разности между текущей и стартовой ценами, деленной на количество дней, в течение которых лот выставлен на продажу).
Вычисление количества заявок для одного лота
Для первого лота (серьги) определите, сколько должно быть подано заявок, чтобы в течение 6 дней продаж среднее увеличение цены в день составляло 120 руб.
1. Выберите команду Сервис => Поиск решения.
2. В диалоговом окне Поиск решения щелкните на кнопке Восстановить.
3. В поле Установить целевую ячейку введите G2 или щелкните в этом поле и затем — на ячейке G2.
4. Выберите переключатель значению и введите в соседнем поле число 120.
5. Щелкните в поле Изменяя ячейки. Затем выделите ячейку D2 и, нажав и удерживая клавишу <Ctrl>, выделите ячейку F2.
6. Щелкните на кнопке Добавить.
7. В диалоговом окне Добавление ограничения щелкните в поле Ссылка на ячейку, затем щелкните на ячейке F2 (либо просто введите в это поле F2).
8. В соседнем раскрывающемся списке выберите элемент =.
9. В поле Ограничение введите число 6.
10.Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения, которое должно совпадать с тем, которое показано на рис. 4.20.
Рис. 4.20. Диалоговое окно Поиск решения, заполненное для решения задачи
11.Щелкните на кнопке Выполнить. Затем щелкните на кнопке ОК в открывшемся окне Результаты поиска решения.
Сравните ваш результат с рабочим листом на рис. 4.21.
Рис. 4.21. Решение задачи определения числа заявок
Вычисление количества заявок для всех лотов
Решим задачу, подобную предыдущей, однако в данном случае найдем количество заявок не для одного лота, а для всех сразу. Итак, решаем следующую задачу: необходимо определить, какое количество заявок и количество дней продажи (по каждому лоту) обеспечат среднее за день возрастание цены (по всем лотам), равное 360 руб. При этом должны выполняться следующие ограничения.
♦ Количество заявок по любому лоту должно быть в пределах от 3 до 12.
♦ Количество дней продажи любого лота должно быть в пределах от 3 до 10.
♦ Значения количеств заявок и количеств дней должны быть целыми числами.
В этом упражнении предполагается, что выполнено предыдущее упражнение и вы начинаете работать с рабочим листом, показанным на рис. 4.21. В противном случае ваше решение может отличаться от того, которое получилось у меня.
1. Выберите команду Сервис => Поиск решения.
2. В диалоговом окне Поиск решения щелкните на кнопке Восстановить.
3. В поле Установить целевую ячейку введите G7 или щелкните в этом поле и затем — на ячейке G7.
4. Выберите переключатель значению и введите в соседнее поле число 360.
5. Щелкните в поле Изменяя ячейки. Затем выделите диапазон D2:D6 и, нажав и удерживая клавишу <Ctrl>, выделите диапазон F2:F6.
6. Щелкните на кнопке Добавить.
7. В диалоговом окне Добавление ограничения щелкните в поле Ссылка на ячейку, затем выделите диапазон D2:D6.
8. В поле Ограничение введите число 12.
9. Щелкните на кнопке Добавить.
10. Щелкните в поле Ссылка на ячейку, затем выделите диапазон D2:D6.
11. В раскрывающемся списке выберите элемент >=.
12. В поле Ограничение введите число 3.
13. Щелкните на кнопке Добавить.
14. Щелкните в поле Ссылка на ячейку, затем снова выделите диапазон D2:D6.
15. В раскрывающемся списке выберите элемент цел.
16. Щелкните на кнопке Добавить.
17. Щелкните в поле Ссылка на ячейку, затем выделите диапазон F2:F6.
18. В поле Ограничение введите число 10.
19. Щелкните на кнопке Добавить.
20. Щелкните в поле Ссылка на ячейку, затем выделите диапазон F2:F6.
21. В раскрывающемся списке выберите элемент >=.
22. В поле Ограничение введите число 3.
23. Щелкните на кнопке Добавить.
24. Щелкните в поле Ссылка на ячейку, затем снова выделите диапазон F2:F6.
25. В раскрывающемся списке выберите элемент цел.
26. Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения, которое должно совпадать с тем, которое показано на рис. 4.22.
Рис.4.22. Диалоговое окно Поиск решения, заполненное для решения задачи
27. Щелкните на кнопке Выполнить. Затем щелкните на кнопке ОК в открывшемся окне Результаты поиска решения.
Сравните ваш результат с рабочим листом на рис. 4.23.
Рис. 4.23. Решение задачи вычисления числа заявок для всех лотов
Сделай сам: использование средства «Поиск решения» для расчетов по банковским кредитам
В этом разделе показано, как Поиск решения используется для расчетов по банковским кредитам. Рабочий лист, подготовленный для выполнения таких расчетов, представлен на рис. 4.24.
Рис. 4.24. Рабочий лист для расчетов по банковскому кредиту
На этом рабочем листе содержатся такие данные.
♦ Сумма кредита (ячейка В1).
♦ Срок погашения кредита в месяцах (ячейка В2).
♦ Годовая процентная ставка по кредиту (ячейка ВЗ).
♦ Вычисляемые ежемесячные платежи по кредиту (ячейка В4).
Решим следующую задачу: необходимо определить максимально возможный размер кредита, если сумма ежемесячных выплат не должна превышать 45 тыс. руб., при этом известно, что процентная ставка не превысит 10%, а кредит берется на 5 лет (60 месяцев). Для решения этой задачи выполните такие действия.
1. Выберите команду Сервис => Поиск решения.
2. В диалоговом окне Поиск решения щелкните на кнопке Восстановить.
3. В поле Установить целевую ячейку введите В4 или щелкните в этом поле и затем на ячейке В4.
4. Выберите переключатель максимальному значению.
5. Щелкните в поле Изменяя ячейки, затем выделите диапазон В1:ВЗ.
6. Щелкните на кнопке Добавить.
7. В диалоговом окне Добавление ограничения щелкните в поле Ссылка на ячейку, затем щелкните на ячейке В2 (либо просто введите в это поле В2).
8. В соседнем раскрывающемся списке выберите элемент =.
9. В поле Ограничение введите число 60.
10. Щелкните на кнопке Добавить.
11. Щелкните в поле Ссылка на ячейку, затем щелкните на ячейке B3 (либо просто введите в это поле ВЗ).
12. В соседнем раскрывающемся списке выберите элемент =.
13. В поле Ограничение введите число 0,1.
14. Щелкните на кнопке Добавить.
15. Щелкните в поле Ссылка на ячейку, затем щелкните на ячейке B4 (либо просто введите в это поле В4).
16. В поле Ограничение введите число -45000.
17. Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения, которое должно совпадать с тем, которое показано на рис. 4.25.
Рис. 4.25. Диалоговое окно Поиск решения, заполненное для решения задачи
18. Щелкните на кнопке Выполнить. Затем щелкните на кнопке ОК в открывшемся окне Результаты поиска решения.
Сравните ваш результат с рабочим листом на рис. 4.26.
Рис. 4.26. Решение задачи вычисления максимальных ежемесячных платежей
Сделай сам: использование средства «Поиск решения» для производственных расчетов
В этом разделе показано, как Поиск решения используется для производственных расчетов. Некая компания планирует на следующий год реализовать 10 проектов в условиях ограниченного бюджета. Данные по этим проектам приведены на рабочем листе (рис. 4.27).
Рис. 4.27. Рабочий лист для производственных расчетов
На этом рабочем листе представлены такие данные.
♦ Суммы производственных расходов по каждому проекту (столбец В).
♦ Суммы фиксированных расходов по каждому проекту (столбец С).
♦ Суммы расходов на маркетинг по каждому проекту (столбец D).
♦ Суммы расходов на рекламу по каждому проекту (столбец Е).
♦ Полная стоимость каждого проекта (столбец F), которая вычисляется как сумма всех расходов по данному проекту.
♦ Общая стоимость всех проектов (ячейка F12).
Расчет по одному проекту
Решим следующую задачу, бюджет по первому проекту ограничен суммой 1 млн руб., при этом производственные и фиксированные расходы остаются неизменными, можно изменять только расходы на маркетинг и рекламу, но расходы на маркетинг должны быть не менее 120 тыс. руб., а расходы на рекламу не могут превышать 70 тыс. руб. Чтобы решить эту задачу, выполните такие действия.
1. Выберите команду Сервис => Поиск решения.
2. В диалоговом окне Поиск решения щелкните на кнопке Восстановить.
3. В поле Установить целевую ячейку введите F2 или щелкните в этом поле и затем — на ячейке F2.
4. Выберите переключатель максимальному значению.
5. Щелкните в поле Изменяя ячейки, затем выделите диапазон D2:E2.
6. Щелкните на кнопке Добавить.
7. В диалоговом окне Добавление ограничения щелкните в поле Ссылка на ячейку, затем щелкните на ячейке D2 (либо просто введите в это поле D2).
8. В соседнем раскрывающемся списке выберите элемент >=.
9. В поле Ограничение введите число 120000.
10. Щелкните на кнопке Добавить.
11. Щелкните в поле Ссылка на ячейку, затем щелкните на ячейке Е2 (либо просто введите в это поле Е2).
12. В поле Ограничение введите число 70000.
13. Щелкните на кнопке Добавить.
14. Щелкните в поле Ссылка на ячейку, затем щелкните на ячейке F2 (либо просто введите в это поле F2).
15. В поле Ограничение введите число 1000000.
16. Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения, которое должно совпадать с тем, которое показано на рис. 4.28.
17. Щелкните на кнопке Выполнить. Затем щелкните на кнопке ОК в открывшемся окне Результаты поиска решения.
Рис. 4.28. Диалоговое окно Поиск решения, заполненное для решения задачи
Сравните ваш результат с рабочим листом на рис. 4.29.
Рис. 4.27. Решение задачи вычисления бюджета одного проекта
Расчет по всем проектам
Решим задачу, аналогичную предыдущей: общий бюджет ограничен суммой 10 млн руб., при этом производственные и фиксированные расходы всех проектов остаются неизменными, можно изменять только расходы на маркетинг и рекламу, но расходы на маркетинг должны быть не менее 120 тыс. руб., а расходы на рекламу не могут превышать 70 тыс. руб. на один проект. Кроме того, бюджет любого проекта на должен превышать суммы 1,2 млн руб. Чтобы решить эту задачу, выполните такие действия.
В этом упражнении предполагается, что выполнено предыдущее упражнение, и вы начинаете работать с рабочим листом, показанным на рис. 4.29. В противном случае ваше решение может отличаться от того, которое получилось у меня.
1. Выберите команду Сервис =>Поиск решения.
2. В диалоговом окне Поиск решения щелкните на кнопке Восстановить.
3. В поле Установить целевую ячейку введите F12 или щелкните в этом поле и затем — на ячейке F12.
4. Щелкните на переключателе максимальному значению.
5. Щелкните в поле Изменяя ячейки, затем выделите диапазон D2:E11.
6. Щелкните на кнопке Добавить.
7. В диалоговом окне Добавление ограничения щелкните в поле Ссылка на ячейку, затем выделите диапазон D2:D11.
8. В соседнем раскрывающемся списке выберите элемент >=.
9. В поле Ограничение введите число 120000.
10. Щелкните на кнопке Добавить.
11. Щелкните в поле Ссылка на ячейку, затем выделите диапазон Е2.-Е11.
12. В поле Ограничение введите число 70000.
13. Щелкните на кнопке Добавить.
14. Щелкните в поле Ссылка на ячейку, затем выделите диапазон F2:F11.
15. В поле Ограничение введите число 1200000.
16. Щелкните в поле Ссылка на ячейку, затем щелкните на ячейке F12 (либо просто введите в это поле F12).
17. В поле Ограничение введите число 10000000.
18. Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения, которое должно совпадать с тем, которое показано на рис. 4.30.
Рис. 4.30. Диалоговое окно Поиск решения, заполненное для решения задачи
19. Щелкните на кнопке Параметры.
20.В диалоговом окне Параметры поиска решения установите флажки Линейная модель и Неотрицательные значения.
21. Щелкните на кнопке ОК. Вы вернетесь в диалоговое окно Поиск решения.
22. Щелкните на кнопке Выполнить. Затем щелкните на кнопке ОК в открывшемся окне Результаты поиска решения.
Сравните ваш результат с рабочим листом на рис. 4.31.
Рис. 4.31. Решение задачи вычисления бюджета всех проектов