F4
F6:F 15
17:111
113:115
=СУММПРОИЗВ($В$3:$Е$3;В4:Е4)
=H6-F6
=F12-H12
Чтобы ввести, например, формулу для вычисления целевой функции:
1.46. Щелкните ячейку F4.
1.47. Введите с клавиатуры знак равенства.
1.48. В левой части строки формул раскройте список функций и щелкните имя функции СУММПРОИЗВ. Если ее там нет, выберите в списке функций пункт Другие функции. В открывшемся окне в категории Математические найдите функцию СУММПРОИЗВ и дважды щелкните ее имя.
1.49. Введите аргументы функции СУММПРОИЗВ (рис. 1.19).
1.50. Щелкните кнопку ОК.
Сразу после ввода формулы в ячейке F4 появится 380, так как формула вычисляется с переменными, которые мы положили равными единице (легко проверить, просуммировав числа в 4-й строке — в этом преимущество значений переменных, равных единице).
Другой возможный способ ввода формулы доступен в Excel 2010 и 2007. Введите в ячейку знак равенства, а затем начальные буквы названия функции, например «су». Для прокрутки появившегося списка функций, имеющих подходящие названия, используйте клавишу Стрелка вниз, затем дважды щелкните в списке нужную функцию — в ячейку будет введено ее имя с открывающей
|
|
Аргументы функции | mr*i | |
СУММПРОИЗВ | ||
Массив1 |$Б$3:$Е$3 [fSi] | = {i;i;i;i} | |
Массив2 |в4:Е4 [!Зё| | = {70;60;110;м0} | |
МассивЗ | [ЁЗШ | = массив | |
= 380 | ||
Возвращает сумму произведений диапазонов или массивов. | ||
Массив^ маса'1в1;масо4в2;... | от 2 до 255 массивов, соответствующие | |
компоненты которых* нужно сначала перемножить, а затем сложить | ||
полученные произведения. Все массивы должны иметь одинаковую | ||
Значение: 380 | ||
Справка по этой функции | 1 ок | [ Отмена ] |
Рис. 1.19. Ввод аргументов функции СУММПРОИЗВ
круглой скобкой, в нашем случае =СУММПРОИЗВ(. После задания аргументов, которые нужно разделять точкой с запятой (;), вводимой с клавиатуры, нажмите клавишу Enter (или кнопку Ввод в строке формул) — закрывающая круглая скобка будет добавлена автоматически, а в ячейке отобразится результат вычислений. Увидеть формулу можно в строке формул, выделив соответствующую ячейку.
Функция СУММПРОИЗВ позволяет вычислить сумму произведений двух массивов, первый из которых содержит значения переменных, а второй — коэффициенты целевой функции. Чтобы указать соответствующие диапазоны, нужно их выделить. Если они закрыты окном для ввода аргументов, его можно перетащить с помощью мыши, потянув за строку заголовка, или воспользоваться кнопками свертывания, расположенными справа от полей ввода (они позволяют временно сворачивать окно).
Ссылка на первый диапазон должна быть абсолютной, со знаками доллара перед каждой буквой и цифрой: $В$3:$Е$3 (чтобы изменить относительную ссылку на абсолютную, нажмите клавишу F4 непосредственно после ввода этой ссылки). Если сразу этого не сделать, то в дальнейшем понадобится сначала выделить ссылку, а затем нажать клавишу F4. Ссылка на второй диапазон В4:Е4, напротив, должна быть относительной: это понадобится в дальнейшем при копировании формулы.
|
|
После ввода формулы для вычисления целевой функции необходимо задать формулы левых частей ограничений. Для этого скопируйте формулу пз ячейки F4 в ячейки F6:F15. Чтобы копировалась только сама формула (без формата ячейки, к которому относятся, например, рамка, заливка и обозначение денежной единицы), нужно воспользоваться специальной вставкой. Для этого щелкните ячейку F4 правой кнопкой мыши и в контекстном меню выберите команду Копировать, затем выделите диапазон F6:F15, щелкните его правой кнопкой мыши и в контекстном меню выберите:
® в Excel 2010 — параметр вставки Формулы;
® в предыдущих версиях Excel — команду Специальная вставка (затем в открывшемся окне выберите параметр формулы и щелкните кнопку ОК).
При копировании относительная ссылка В4:Е4 будет меняться, указывая на массивы коэффициентов соответствующих ограничений, а абсолютная $В$3:$Е$3 останется неизменной. Для проверки формулы в ячейке достаточно дважды щелкнуть эту ячейку. Формула будет отображена на экране, ссылки на ячейки выделены цветом, а сами ячейки — обведены рамками соответствующих цветов (после просмотра нажмите клавишу Esc).
Аналогично, с помощью копирования, вводятся формулы в ячейки 16:115 для вычисления разницы между правыми и левыми частями ограничений. Для ограничений со знаком < из правой части ограничения вычитается левая, а для ограничений со знаком > — из левой правая (для ограничений со знаком = вычитать можно из правой части левую или из левой правую — все равно). Таким образом, если ограничения выполняются п план допустим, разница всегда будет неотрицательной.
После ввода формул вы можете подставлять в ячейки ВЗ:ЕЗ любые значения (количество выпускаемой продукции), получая соответствующую величину прибыли в ячейке F4 и объем израсходованных ресурсов в столбце Лев.часть. Столбец Разница покажет, допустим ли соответствующий производственный план (в этом случае разница неотрицательна), какие ресурсы дефицитны (для них разница равна нулю) и каков остаток недефицнтных ресурсов.
Вид нашей модели после ввода формул показан на рис. 1.20. Ясно, что, план, предполагающий выпуск одной единицы каждого продукта, недопустим, так как нарушаются ограничения Мин.ГИ и МИН.П4 (разница отрицательна).
т | А | В | С | ■> | Е | F | G | Н | J | |
Производственный план | ||||||||||
Продукт | П1 | П2 | пз | П4 | ||||||
Произведенное к-во | Прибыль | |||||||||
Удельная прибыль | 70,00р. | 60,00р. | 110,00р. | 140,00р. | 380,00р. | |||||
Ограничения | Расход ресурсов | Лев.часть | | Прав.часть | Разница | ||||||
труд | < | |||||||||
Сырье | < | |||||||||
Финансы | 5,00р. | 7,00р. | 9,00р. | 8,00р. | 29,00р. | < | 100,00р. | 71,00р. | ||
Мах.П1 | < | |||||||||
Мах.ПЗ | < | |||||||||
Мах.П4 | < | |||||||||
Мин.П1 | > | -2 | ||||||||
Мин.П2 | > | |||||||||
Мин.ПЗ | > | |||||||||
Мин.П4 | > | -1 | ||||||||
Рис. 1.20. Модель с введенными формулами