5. Создание совмещённых операций (таблиц подстановки)

Команда Данные - Совмещенные операции предоставляет программное средство планирования для вопросов "что-если". В электронную таблицу вводится формула для расчета результата на основе значений, которые хранятся в других ячейках. Далее устанавливается диапазон ячеек, в который вводится несколько фиксированных значений, после чего с помощью команды "Совмещенные операции" выполняется расчет результатов в зависимости от формулы.

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

Совмещённые операции позволяют производить вычисления по формулам, для одного из нижеприведенных случаев:

• имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая Совмещённые операции с одним входом;

• имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется Совмещённые операции с двумя входами.

5.1. Создание совмещённых операций с одним входом

Задание:

1. Для рассматриваемой задачи (на рабочем листе Поиск решения) загрузите сценарий Материал_1700.

2. Оформите таблицу подстановки рис.138. Вводите количество изделия А от 25 до 500 с шагом 25 (используйте функцию Автозаполнение).

Рис. 19

3. В ячейку В15 скопируйте формулу для расчета прибыли (ячейка В6). Таким же образом скопируйте формулы для расчета расхода материала и времени изготовления рис. 20. Помните, что формулы в таблице подстановки должны в точности повторять формулы в исходной таблице поиска решения.

Рис. 20

4. Выделите таблицу подстановки – диапазон А16:D35 (выделенный диапазон не должен включать формулы).

5. Выполните команду Данные⇒Совмещённые операции...

6. В поле Формулы подставьте диапазон с формулами B15:D15

7. Набор значений переменной (количество изделий А) расположен по строкам, поэтому в открывшемся диалоговом окне активизируйте поле Столбец ввода

8. При осуществлении поиска решений значения переменной будут подставляться в исходную таблицу поиска решения в ячейку с количеством изделий А (ячейка В2). В поле Столбец ввода укажите адрес ячейки В2 рис. 21. Нажмите кнопку ОК.

Рис. 21

9. Убедитесь, что OpenOffice.org Calc заполнил таблицу соответствующими результатами.

10. Сохраните файл.

5.2. Создание совмещённых операций с двумя входами

1. Оформите таблицу подстановки: введите по строкам значения количества изделия А от 25 до 500 с шагом 25, по столбцам - значения количества изделия В от 25 до 500 с шагом 25 рис. 22.

Рис. 22

2. В ячейку В38 скопируйте формулу для расчета прибыли рис. 23.

Рис 23

3. Нажмите кнопку ОК. Таблица подстановки заполнится.

4. Выделите диапазон ячеек В38:V58 (диапазон должен включать наборы значений обеих переменных).поиска решения рис. 24.

Рис 24

5. В таблице выделите значение прибыли, соответствующее текущим параметрам значений количества изделий

6. Выполните команду Данные⇒Совмещённые операции... В расположен по столбцам, поэтому в поле Столбец ввода:

7. укажите ссылку на соответствующую ячейку рис.25.

8. Набор Набор значений количества изделий А расположен по строкам, поэтому в поле рис.25. укажите ссылку на соответствующую ячейку Строка ввода

Рис.26

9. Сохраните файл.

Задание для самостоятельной работы:

1. Создайте аналогичную таблицу подстановки с двумя входами, отображающую значения расхода материала в зависимости от количества изделий А и В. В таблице выделите значение расхода материла, соответствующее текущим параметрам поиска решения

2. Создайте таблицу подстановки с двумя входами, отображающую значения затрат времени в зависимости от количества изделий А и В. В таблице выделите значение затрат времени, соответствующее текущим параметрам поиска решения

Задания для самостоятельной работы:

1. Откройте лист Задача. При отсутствии исходного файла Лист3 назовите Задача.

2. Ознакомьтесь с условием задачи.

Предприятие изготавливает три вида изделия А, В и С. Для выполнения существующего заказа предприятию нужно произвести не менее 50 штук изделий А, не менее 40 штук изделия В и не более 40 штук изделия С. Общий объем производства –300 единиц в день.

С целью предотвращения затоваривания склада изделием одной модели, запрещается производить одну и ту же модель в объеме, большем половины суммарного объема производства.

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

Известно, что на 1 штуку изделия А приходится $13 расходов, на 1 штуку изделия В -$18, на 1 штуку изделия С - $22.

3. Решите задачу, опираясь на данные рисунка 27.

Рис. 27

4. Создайте таблицу подстановки с двумя входами, отображающую значения общих расходов в зависимости от количества изделий В и С. В таблице выделите значение общего расхода, соответствующее текущим параметрам поиска решения.

5. Сохраните файл.