Перемножение элементов нескольких столбцов и суммирование по строкам с помощью СУММПРОИЗВ обычно выполняется при решении задач оптимизации

Сведения по Excel, достаточные для решения Вашей задачи

Абсолютные и относительные ссылки (адреса)

Абсолютная ссылка в формуле (например, =2*$В$2) всегда указывает на одну и ту же фиксированную ячейку (В2) независимо от того, куда и каким образом вы копируете или перемещаете формулу. Ссылка типа В2 является относительной.

Копирование формул: обычно выполняется при решении задач оптимизации. Если в ячейке С4 содержится формула, использующая содержимое ячейки В2 с относительной ссылкой В2 (например, =2*B2), и затем вы копируете С4 в ячейку С6 (сдвиг ячейки на две позиции вниз), то адрес ссылки изменится, т. е., в ячейке С6 будет содержаться формула = 2*В4, (т.е., в формуле адрес ячейки также сдвинется на две позиции вниз B(2+2)). Таким образом, относительность ссылки сохраняется при копировании содержимого ячейки.

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

Пример. Пусть в ячейках А1,А2,А3 находятся числа 1,2 и 3, а в ячейке В1 — формула =2*А1. Если выделить ячейку В1 и протащить маркер заполнения на ячейку В2 и В3, то в В2 окажется формула =2*А2. После фиксации формул (например. нажатием ENTER) в ячейках В1 и В2 будут видны результаты вычислений — числа 2,4 и 6.

 

Диапазоны ячеек и операторы ссылок

Выражение типа С1:Е3 обозначает совокупность ячеек в прямоугольнике (диапазоне), в котором ячейка С1 является верхней левой а ячейка Е3 – нижней правой ячейкой выделяемого диапазона. Например, формула =СУММ(A1:A3) дает сумму трех чисел A1+A2+A3. Выражение С1:Е3; D4:E5 через; означает объединение дипазонов С1:Е3 и D4:E5.

 

 

Перемножение элементов нескольких столбцов и суммирование по строкам с помощью СУММПРОИЗВ обычно выполняется при решении задач оптимизации

- Пусть в диапазоне A1:B7 находятся числа. СУММПРОИЗВ(A1:A7;B1:B7) дает сумму произведений Ai*Bi по семи первым строкам.

 

 

< Инструмент Excel Поиск решения и его подключение

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

>

15 Использование инструмента Поиск решения для решения задачи задач оптимизации (задач линейного программирования)

Пример 1. Требуется найти значения переменных х и у, которые обеспечивают максимальное значение линейной целевой функции f(x,y)=2x+3y при наличии линейных ограничений: x+2y <= 0, 2x+y <= 3 а также x >= 0, y >= 0. (Термин линейное выражение здесь означает алгебраическую сумму переменных х, у …, умноженных на числа.)

Решение. Ячейки А1 и В1 отводим под значения х и у (Иными словами: резервируем эти ячейки под значения х и у. Вначале они остаются пустыми. После запуска Поиска решения в этих ячейках будут записаны полученные значения х и у). В ячейку А2 вводим формулу «= 2*А1+3*В1», в А3 вводим «=А1+2*В1», в ячейку А4 вводим «= 2*А1+В1». На вкладке Данные-Анализ выбираем Поиск решения. В диалоговом окне Поиск решения в поле Установить целевую ячейку вводим ссылку «А2», в поле Изменяя ячейки вводим «A1:B1». Далее нажимаем Добавить в списке Ограничения. Появится диалоговое окно Добавление ограничения. В его поле Ссылка на ячейку (для ограничения x+2y <=0) вводим адрес «А3», затем (правее) выбираем операцию сравнения <=, затем в поле Ограничение задаем константу 0 (или, в общем случае, ссылку на ячейку со значением или формулой). Кнопка ОК добавляет ограничение и закрывает окно. Для ограничения 2x+y <= 3: Добавить, в поле Ссылка на ячейку вводим «А4», затем выбираем операцию сравнения <=, затем в поле Ограничение задаем константу 3.

Примечание: приведенные ниже иллюстрации содержат данные другой задачи.

В окне Параметры поиска решения ставим флажок Неотрицательные значения для переменных x и y. После того, как все данные для инструмента Поиск решения будут заданы, следует нажать кнопку Выполнить.

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

 

 

Внимание!: В окне Добавление ограничения, помимо операций <= и >=, доступны условия: целочисленности (“цел”) и двоичности (“двоич”). При выборе “цел” переменные могут принимать только целые значения, При выборе “цел” – значения 0 или 1.

 

Пример 3. Требуется купить 300 г сыра (230 руб.кг), 500 г колбасы (295 руб.кг) а также некоторое количество бананов (65 руб.кг) и конфет (190 руб.кг). Всего надо затратить 400 руб, конфет требуется не более 500 г. Сколько бананов и конфет надо приобрести?

Решение. В ячейку С7 ввести формулу = 0,3*230 + 0,5*295+С5*65+С6*190. На Данные-Анализ щелкнуть Поиск решения, в поле Установить целевую ячейку ввести С7, в поле Изменяя ячейки ввести С5:С6. В группе Равной установить переключатель в положение Значению и ввести 400, Далее щелкнуть на Добавить в списке Ограничения. Появится диалоговое окно Добавление ограничения. В его поле Ссылка на ячейку ввести С6, затем (правее) выбрать операцию сравнения (<=), затем в поле Ограничение задается константа 0,5. Выполнить. В результате в ячейках С5 и С6 будут значения, обеспечивающие значение 400 в С7. Впоявившемся окне выбрать один из вариантов: сохранить найденное решение или восстановить исходные значения, OK.

 

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

Флажок Линейная модель используется для решения задач, в которых отсутствуют нелинейные зависимости (и в целевой функции и в ограничениях), что облегчает решение линейной задачи.

 

Пример 4. Фирма «Фасад» производит двери для продажи и продает всю производимую продукцию. На фирме работает 10 рабочих в одну смену (8 рабочих часов), 5 дней в неделю, что дает 400 часов в неделю. Рабочее время поделено между двумя технологическими процессами: собственно производством и конечной обработкой дверей. Из 400 рабочих часов в неделю 250 отведены под собственно производство и 150 под конечную обработку.

Фирма производит 3 типа дверей: стандартные, полированные и резные. В таблице приведены временные затраты и прибыль от продажи одной двери каждого типа.

	Время на производство (мин)	Время на обработку (мин)	Прибыль, у.е.
Стандартные
Полированные
Резные

 

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

Решение задачи.

a. В данном случае мы хотим максимизировать прибыль, следовательно, целевая функция должна вычислять полную прибыль. В качестве переменных задачи следует выбрать количества дверей каждого типа, которые следует произвести. Значит, в задаче будет 3 переменных: Х1 - количество стандартных дверей, Х2 – количество полированных и Х3 – количество резных дверей. При этом для целевой функции:

P = X1*45 + X2*90 + X3*120 (у.е.).

Данные на листе MS Excel можно организовать следующим образом.

	A	B	C	D	E	F
	Фирма «Фасад»
		Время на производство (мин)	Время на обработку (мин)	Прибыль у.е.	Переменные
	Стандартные					X1
	Полированные					X2
	Резные					X3
					Целевая функция
		=СУММПРОИЗВ (E3:E5;B3:B5)	=СУММПРОИЗВ (E3:E5;C3:C5)		=СУММПРОИЗВ (E3:E5;D3:D5)
	Ограничения	=250*60	=400*60-B8

 

Напомним, что начальные значения переменных неизвестны, но ссылаться на переменные при вычислениях необходимо. Целевая функция (ячейка Е7) задана с помощью стандартной функции Excel =СУММПРОИЗВ(), которая и вычисляет приведенное выше выражение для P.

На следующем этапе решения следует выяснить, при каких ограничениях нужно найти максимальную прибыль. Из условия следует, что можно затратить на производственную стадию не больше 250 часов в неделю, а на обработку не больше 150 часов. Так как в надстройке Поиск решения нельзя задавать ограничения в виде формул, то задание всех необходимых формул для задания ограничений следует сделать на листе Excel.

Итак, следует подсчитать, сколько времени на каждой стадии потребуется для реализации произвольного плана производства дверей. Для стадии производства это время будет равно t1=X1*30+X2*30+X3*60 (мин), а для стадии обработки - t2=X1*15+X2*30+X3*30 (мин),

По условию: t1 <= 250*60 (мин), а t2 <= 150*60 (мин).

Добавим эти формулы на лист с данными задачи (формулу для t1 в ячейку В7 и формулу для t2 в ячейку C7).

Теперь имеется вся информация, необходимая надстройке Поиск решения для определения оптимального по прибыли плана производства. В строке меню находим пункт Сервис, а внутри выпадающего меню пункт Поиск решения. Вызов надстройки Поиск решения приводит к появлению следующего диалогового окна Поиск решения. В окошке Установить целевую ячейку указываем ячейку, содержащую целевую функцию (E7). Переключатель оставляем в позиции Равной максимальному значению. В окошке Изменяя ячейки нужно указать ячейки, содержащие переменные решения (Е3:Е5).

Для того, чтобы добавить что-либо в окно Ограничения, следует нажать кнопку Добавить и в выпадающем окне Добавление ограничений ввести ограничения. В окне Ссылка на ячейку следует ввести В7:С7, в среднем окне - <=, и в окне Ограничение – В8:С8. Это означает, что число в ячейке В7 меньше или равно числа в ячейке В8, и число в ячейке С7 меньше или равно числа в ячейке С8.

В результате в окне Поиск решения будет: В7:С7 <= B8:C8.

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

Нажав ОК, возвращаемся в панель Поиск решения. Теперь можно нажимать кнопку Выполнить, после чего и будет найдено решение, о чем и сообщит панель Результаты поиска решения.

Для переменных будут получены значения: X1=0; X2=100; X1=200 (ячейки Е3:Е5). Для полученной прибыли (ячейка Е7) – значение 33000. Это и есть оптимальный план производства для базовой задачи (пункт а).