Создание таблицы с двумя переменными

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

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

1. Создайте базовую таблицу, включив в нее необходимую для расчета формулу – прототип (Рисунок 9.23).

! При создании формулы убедитесь, что она использует ссылки на две ячейки ввода, значения которых должны изменяться (в нашем примере – это ячейка В4 с процентной ставкой и ячейка B3 со сроком кредитования).

Рисунок 9.23. Базовая таблица

 

2. Измените базовую таблицу (Рисунок 9.24):

a) Введите один список входных значений в столбец под формулой-прототипом.

b) Введите второй список в одну строку с формулой-прототипом справа от нее.

Рисунок 9.24. Ввод списка входных значений

 

3. Выделите диапазон ячеек, содержащих формулу (A 9), строку и столбец значений (A10:A17 и B9:F9), а также ячейки, в которые нужно поместить вычисленные значения (B10:F17).

4. На вкладке Данные в группе Работа с данными выберите команду Анализ "что если" и выберите пункт Таблица данных.

5. В поле Подставлять значения по столбцам в введите ссылку на ячейку ввода для входных значений в строке – B5.

6. В поле Подставлять значения по строкам в введите ссылку на ячейку ввода для входных значений в столбце – B4.

7. Нажмите кнопку ОК.

Рисунок 9.25. Результирующая Таблица данных с двумя переменными

Ускорение вычислений для листов, содержащих таблицы данных

1. Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Формулы.

2. В разделе Параметры вычислений в группе Вычисление выберите вариант Автоматически, кроме таблиц данных.

Рисунок 9.26. Настройка параметров вычислений

! Если выбран этот параметр вычисления, таблицы данных не пересчитываются при пересчете остальной части книги.

! Чтобы пересчитать таблицу данных вручную, выделите ее формулы и нажмите клавишу [F9].

Поиск решения

Общие сведения

Подбор параметра хорошо работает в случае решения простых проблем. Но он имеет ограничения в использовании. В частности, средство подбора параметров поддерживает только одну переменную. Для решения более сложных задач, вы можете воспользоваться надстройкой Поиск решения (Solver), позволяющей спроецировать результаты для большего количества переменных.

Традиционными задачами, решаемыми с помощью надстройки Поиск решений, например, являются:

1. Ассортимент продукции. Сколько можно выпустить определенного товара при ограничении сырья.

2. Штатное расписание. Как составить штатное расписание для достижения лучшего результата и при наименьших расходах.

3. Планирование перевозок. Как минимизировать затраты на перевозки.

4. Составление смеси. Как составить смесь заданного качества при наименьших затратах.

Все эти задачи имеют три общих свойства:

· У них имеется только одна цель: или минимизировать затраты, или максимизировать прибыль.

· Имеются ограничения на сырьё, время и т.д.

· Имеется набор входных значений, влияющий на ограничения

Таким образом, основными компонентами механизма поиска решения являются:

· Целевая ячейка – ячейка, содержащая формулу, значение которой нужно оптимизировать;

· Ячейки переменных решения – ячейки, которые содержат значения, изменяемые поиском решения для достижения желаемой цели;

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

· Ячейки ограничения – это ячейки, которые содержат значения, используемые для ограничения возможных решений.

! Под ограничениями понимают соотношения вида А1<=B1, A1=A2, A1>=0. При этом, по крайней мере одна из ячеек в соотношении должна зависеть от переменных решения, в противном случае это соотношение не влияет на решение задачи.

! Правильная формулировка ограничений является самой ответственной частью постановки задачи.

! Ограничения имеют тот же синтаксис, что и формулы, но воспринимаются надстройкой Поиск решения иначе: формулы выполняются точно, а ограничения с некоторой погрешностью

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