Приемы построения математических моделей и целевых функций задач принятия решений. Правила выделения блоков ячеек рабочего листа Excel 2000. Порядок использования операции "автосуммирование". Рассмотрение проблемы рационального расходования ресурсов.
Во р о неж ский Г о суда р ственный УниверситетВ работе п рак тическ ие вопросы, связанные с п ринятием рац ионал ьных решений в экономик е, с исп ол ьзованием EXCEL 2000 и ее модифик ац ий. Наоснове единого п одхода к решению эк стремал ьных задач излагаются п риемы п остроения математическ их модел ейи ц елевых функ ц ий задач п ринятия решений. Доп ол нител ьные рек омендац ии п о вопросам п оиск а рац ионал ьных решений в экономик е с исп ол ьзованием EXCEL 2000 и ее модифик ац ийможно п ол учитьп о адресу: 394068 г.В оронеж, ул .
Введение
В различных областях своей деятельности человек у п рак тическ и ежедневно п риходится стал к иваться с п робл емой п ринятия решений дл я достижения тех ил и иных ц ел ей. В экономик е ц ел ями могут быть увеличение п рибыл и, снижение затрат, п овыш ение п роизводител ьности труда, рац ионал ьное исп ол ьзование оборудования, п овыш ение эффективности инвестиц ийи многие другие. Задача достижения экономическ их ц ел ей п риводит к п робл еме рац ионал ьного исп ол ьзования ограниченных ресурсов (материальных, сырьевых, энергетическ их, финансовых, трудовых и других.). Дл я решения этой п робл емы человек у необходимо п ринимать оп редел енные решения. Естественно, что в п роц ессе п ринятия решений человек у, к ак п равил о, свойственно стремление выбрать наил учш ее дл я него решение.
В работе рассматриваются п рак тическ ие вопросы, связанные с п ринятием рац ионал ьных решений в экономик е на основе исп ол ьзования EXCEL 2000 и ее модифик ац ий.
В ып ол нение п риводимых заданий, п озвол ит В ам п риобрести п рак тическ ие навык и, необходимые дл я решения на к омп ьютере важных и ак туал ьных экономическ ихзадач.
1 Основные о пр еделения
Определение 1. Наил учш ее решение , с точк и зрения п ринимающ его это решение человек а, будем называть оп тимал ьным.
С незапамятных времен человек в п роц ессе п ринятия реш ени исп ол ьзовал свойоп ыти интуицию.
Дл я п ринятия оп тимал ьных реш енийв современных условиях к оп ыту и интуиции человек а добавляется возможность исп ол ьзования ЭВ М . ЭВ М п озвол яет в к оротк ий срок обработать бол ьш ой объем данных, необходимых дл я п ринятия решения, выработать рек омендац ии п о п ринятию оп тимал ьного решения, оценить п осл едствия от п ринимаемого решения, к оторые могутп роизойти в будущ ем.
С л едуетзаметить, что так ого родарасчеты ЭВ М может вып ол нять тол ьк о с исп ол ьзованием сп ец иал ьных к омп ьютерных п рограмм. Представителем к оторых является, например, EXCEL 2000 [1], реал изующ ая функ ц ии эл ек тронной таблиц ы. С реди функ ц ий EXCEL 2000 имеются математические функ ц ии, п редназначенные дл я решения эк стремал ьныхзадач.
Определение 2. Эк стремал ьная задача- это задачап о п оиск у наил учш его (оп тимал ьного) решения из множества (набора) доп устимыхреш ений.
Т еория и методы решения эк стремал ьных задач изучаются в наук е, п ол учивш ейназвание математическое п рограммирование .[2]
Дл я решения эк стремал ьной задачи на ЭВ М необходимо средствами математическ ой символик и описать заданную ц ел ь (например, п ол учение мак симал ьной п рибыл и), а так же зап ас имеющихся ресурсов и условия их исп ол ьзования дл я достижения ц ел и. При так ом описании выдел яютсл едующ ие двап онятия: · М атематическую модел ь; · Ц елевую функ ц ию.
Определение 3. М атематическая модел ь - это п рибл иженное описание к ак ого-л ибо к л асса явлений средствами математическ ой символик и. А нал из математическ оймодел и дает возможность п роник нуть в сущ ностьизучаемыхявл ений.
М атематическая модел ь эк стремал ьнойзадачи задает множество допустимых решений X . М ножество X оп редел яется имеющимися запасами ресурсов и условиями ихисп ол ьзования дл я достижения ц ел и.
В EXCEL 2000 множество допустимых решений называюттак же ограничениями задачи.
Определение 4. Ц елевая функ ц ия п редставл яет собой числовую характеристик у, бол ьш ему ил и меньшему значению к оторойсоответствует л учш ее решение , с точк и зрения п ринимающ его это решение человек а. Б удем обозначать ц елевую функ ц ию через f(x) где
( )
XT = x1,L,xj,L,xn .
Определение 5.
XT = x1,L,xj,L,xn , а X-
( )
В ек тор множество
XI X где допустимых реш енийбудем называтьреш ением эк стремал ьнойзадачи.
2 Пример ыэкстр ема льныхза да ч
О дним из п римеров эк стремал ьной задачи может служить задача мак симизац ии п рибыл и п редп риятия в условиях ограниченных ресурсов. Пусть некоторое п редп риятие , п рименяя имеющуюся технологию, может выпуск ать n видов п родук ц ии, исп ол ьзуя m видов ресурсов. Ц ел ью п редп риятия является п ол учение мак симал ьнойп рибыл и.
Построим математическую модел ьи ц елевую функ ц ию дл я решения задачи оп редел ения наиболее п рибыл ьного объемавып уск ап родук ц ии. Т о естьтак ого объема, к оторый может обеспечить п редп риятию п ол учение мак симал ьной п рибыл и.
Дл я п остроения математическ ой модел и введем следующ ие обозначения. О бозначим через XJ , j =1,n к ол ичество выпуск аемойп родук ц ии j-го вида. Т огдаобъем всей выпуск аемой п родук ц ии можно обозначить с п омощ ью век тора XT =(x1,L,XJ ,L,xn). О бозначим через bi i =1,m зап ас i-го вида ресурса, имеющийся на п редп риятии, а через gi(x), i =1,m - к ол ичество i-го ресурса, необходимого дл я выпуск а п родук ц ии, оп редел яемойвек тором х.
Заметим, что функ ц ии gi(x), к ак п равил о, оп редел яются исп ол ьзуемойнап редп риятии технологией
.
О чевидно, что выпуск п родук ц ии будет ограничен имеющимися запасами ресурсов. М атематическ и эти ограничения можно зап исатьв следующ ем виде:
gi (x ) ? bi i =1,m (1)
О бозначим через HJ , j =1,n верхние ограничения, обусл овл енные спросом, нап родук ц ию j-го вида, ачерез LJ , j =1,n , нижние ограничения обусл овл ены спросом, на ту же п родук ц ию. О чевидно, что выпуск п родук ц ии дол жен удовлетворять условиям спроса. М атематическ и эти условия можно зап исатьсл едующ им образом:
LJ ? XJ ? HJ j =1,n (2)
Естественно так же, что выпуск п родук ц ии удовлетворяет условиям неотриц ател ьности, XJ ? 0 j =1,n. (3) XJ , j =1,n а именно
О бозначим через f(x) п рибыл ь, п ол учаемую п редп риятием от реал изац ии п родук ц ии. Т огда задача оп редел ения объемавып уск ап родук ц ии, обесп ечивающ его п редп риятию мак симал ьную п рибыл ь, может быть зап исанасл едующ им образом.
Найти
max f (x) (4)
п ри условиях(1), (2), (3).
При этом функ ц ия f(x) называется ц ел евойфунк ц ией, век тор x - век тором п еременных, система неравенств (1),(2),(3) п редставл яет собой математическую модел ь задачи. И ногдасистему неравенств вида(1)-(3) называют ограничениями задачи.
Эк стремал ьную задачу (4), (1)-(3) называют так же задачейматематическ ого п рограммирования ил и задачей оп тимизац ии.
Дадим интерп ретац ию эк стремал ьнойзадачи (4), (1)-(3) к ак задачи п ринятия решения. К омп оненты век тора п еременных XJ , j =1,n модел ируютп ринятие к онк ретного решения. Ц елевая функ ц ия f(x) моделирует эффективность п ринимаемого решения. О граничения (1)-(3) задачи моделируют связи, нак л адываемые нак омп оненты век тора п еременных XJ , j =1,n способами исп ол ьзования ресурсов.
В общ ем случае эк стремал ьную задачу можно оп редел ить, например, следующ им образом.
Дано множество X и функ ц ия f(x), оп редел енная на множестве X. Т ребуется найти ( есл и они существуют ) точк и мак симумаил и минимумафунк ц ии f(x) намножестве X. Условимся записывать задачу мак симизац ии функ ц ии f(x) намножестве X следующ им образом: max f (x)
XI X
(5)
При этом функ ц ию f(x) будем п о-п режнему называть ц ел евой функ ц ией, век тор x - век тором п еременных, множество X будем называть множеством допустимых решений.
М ножество X оп редел яется неравенствами (1), (2), (3). К онк ретизируем рассмотренную выш е задачу.
2.1 За да ча о пр еделения на иб о лее пр иб ыльно го о б ъ ема выпуска пр о дукции
Предприятие может выпуск ать n видов исп ол ьзуя дл я этого m видов ресурсов. п роизводства одной единиц ы п родук ц ии п родук ц ии, Пусть дл я j-го вида исп ол ьзуется aij единиц ресурса i-го вида. Прибыл ь от реал изац ии однойединиц ы п родук ц ии j-го видаобозначим через Pj, j =1,n рублей. Т ребуется оп редел итьтак ойобъем выпуск а п родук ц ии, к оторыйобесп ечивает п редп риятию наибол ьш ую п рибыл ь.
О бозначим через xj, j = 1,n объем п родук ц ии j - го вида, выпуск аемойв соответствии с некоторым п л аном. Т огда математическую модел ь задачи можно записать в следующ ем виде aaijxj ?bi i =1,m (6) j=1 n
Эта модел ь оп редел яется ограничениями на выпуск п родук ц ии, обусл овл енными имеющимися запасами ресурсов. Ц елевую функ ц ию задачи можно записать следующ им образом n
W = APJXJ (7) j=1
Посл е п остроения математическ ой модел и и записи ц ел евой функ ц ии задача оп редел ения объема выпуск а п родук ц ии, обесп ечивающ его п редп риятию наибол ьш ую п рибыл ь, можетбытьсформул ированак ак задача n
Найти max W = APJXJ (8) j=1 п ри условии aaijxj ? i i = 1,m (6) j=1 n b x j ? 0 j = 1,n (9)
Условие (9), ук азывающ ее на неотриц ател ьность выпуск а п родук ц ии, необходимо задавать дл я решения задачи нак омп ьютере, сисп ол ьзованием EXCEL 2000.
В задаче (8), (6), (9) отсутствуют ограничения п о спросу на п родук ц ию, к оторым в рыночной экономик е п ринадл ежитважная рол ь. В ведем эти ограничения в задачу следующ им образом.
О бозначим через hj, j =1,n верхнее ограничение п о спросу на п родук ц ию j-го вида, а через lj нижнее ограничение п о спросу на п родук ц ию j-го вида, тогда задача(8), (6), (9) п риметсл едующ ийвид n
Найти max W = APJXJ (8) j=1
п ри условии aaijxj ? b j = 1,n (6) j=1 n i lj ? xj ? hj x j ? 0 j = 1,n (10)
j = 1,n (9)
В общ ем случае п рибыл ьсростом объемап роизводства может начать уменьшаться из-задоп ол нител ьных затрат, связанных, например, среал изац иейп родук ц ии.
О бозначим череза j j =1,n 0 <a j ?1 j =1,n степень влияния на п рибыл ь объема выпуск aj-го изделия. Т огдац елевая функ ц ия задачи может бытьзап исанав следующ ем виде:
W1 = j=1Pj X j j (11) n a a асамазадачап риметвид
MAXW = AP Xa j (12) j=1 n j j
1
п ри условиях(6), (10), (9).
Заметим, что есл и a j =1, то п рибыл ьне зависитотобъема выпуск aj-го изделия.
2.2 Т р а нспо р тна я за да ча
И меется m п унк тов п роизводства и n п унк тов п отребл ения.
К ол ичество п родук та в i-м п унк те п роизводства обозначим через ai, i =1,m;
Потребность в п родук те в j-м п унк те п отребл ения обозначим через BJ , j = 1,n
С тоимость п еревозк и однойединиц ы п родук таиз i-го п унк тап роизводства в j-йп унк т п отребл ения обозначим через cij (i =1,m j = 1,n) рублей.
Т ребуется составитьтак ойп л ан п еревозк и однородного п родук та так , чтобы общ ая стоимость п еревозок был а минимальной.
О бозначим через xij к ол ичество п родук та, п еревозимого из i-го п унк тав j-йп унк т.
В п ринятыхобозначениях axij к ол ичество п родук та, вывозимого изі-го п унк та j=1 n axij к ол ичество п родук та, доставляемого в j-йп унк т. i=1 m a acjxij суммарные транспортные расходы. i=1 j=1 m n
М атематическая модел ь транспортной задачи будет иметьсл едующ ийвид:
axij ? ai i =1,m (13) j=1 n axij ? bj j = 1,n (14) i=1 m xij ? 0 i =1,m j = 1,n (15)
Ц елевая функ ц ия можетбытьзап исанасл едующ им образом
V = a acijxij (16) i=1 j=1 m n
М инимизац ия трансп ортныхрасходов требуетреш ения следующ ейзадачи.
Найти MINV = a acijxij (17) i=1 j=1 m n п ри условиях: axij ? ai i =1,m (13) j=1 n axij ? bj j = 1,n (14) i=1 m xij ? 0 i =1,m j = 1,n (15)
3 Включение компьютер а и за пуск EXCEL
Дл я вып ол нения вычисл енийнап ерсонал ьном к омп ьютере сисп ол ьзованием EXCEL п режде всего необходимо: 1. В к л ючитьк омп ьютер;
2. Зап уститьп рограмму EXCEL.
3.1 По р ядо к включения компьютер а
Дл я вк л ючения к омп ьютеранеобходимо: 1. Нажатьк ноп к у вк л ючения п итания намониторе ; 2. Нажатьк ноп к у вк л ючения п итания насистемном бл ок е.
Посл е вып ол нения ук азанныхдействийначинает осущ ествл яться загрузк аядраоп ерац ионнойсистемы (О С ) Windows 98 в оперативную п амятьк омп ьютера.
Усп еш ныйрезул ьтатзагрузк и Windows 98 п риводитк п оявл ению наэк ране мониторап ик тографическ ого меню, п римерныйвидк оторого п ок азан нарис 3.1.
рис 3.1.
3.2 По р ядо к за пуска пр о гр а ММЫEXCEL
В О С Windows 98 имеется неск ол ьк о способов запуск а п рограмм.
О дин из них связан с исп ол ьзованием « М ой к омп ьютер», п ик тограммак оторого п оявл яется наэк ране мониторап осл е загрузк и Windows 98.
К другому способу можно отнести исп ол ьзование п ик тограмм быстрого доступ а. Например, есл и п осл е загрузк и Windows 98 наэк ране мониторап оявл яется п ик тограмма , то, установив на нее к урсор (ук азател ьмыш и) и затем дважды щ ел к нув л евойк л авиш ей мыш и, можно осущ ествитьзап уск п рограмм EXCEL.
Ещ е одним способом запуск ап рограмм может явиться исп ол ьзование меню к оманды « Пуск ». Рассмотрим этот способ дл я запуск ап рограммы EXCEL.
1. Установите к урсор в л еtrialжнийугол эк ранана к ноп к у « Пуск » (рис 3.1.) и щ ел к ните л евой к л авиш ей « мыш и».
Посл е вып ол нения указанных действий на эк ране п оявится меню к оманды « Пуск », к оторое имеетсл едующ ий вид (см. рис.3.2).
рис 3.2.
2.Установите к урсор нап унк те « Программы»и задержите его там наодну секунду. Посл е этого наэк ране п оявится меню « Программы», п римерныйвидк оторого п ок азан нарис.3.3.
рис 3.3.
3. Установите к урсор нап унк т и щ ел к ните на п ик тограмме этого п унк тал евойк л авиш еймыш и.
Посл е запуск а EXCEL л юбым из рассмотренных способов на эк ране монитора п оявится рабочий л ист EXCEL сэл ементами его уп равл ения (см.рис.3.4).
рис 3.4.
4 Основные пр иемыр а б о ты в EXCEL
Определение 6. EXCEL - это универсальное п рограммное средство, п редназначенное дл я эл ек тронной обработк и данных.
Данные в EXCEL хранятся в рабочихк нигах.
Определение 7.Рабочая к нига (WORKBOOK) - это универсал ьныйанал огк артотек и.
Подобно к арточк ам к артотек и, рабочая к нигавк л ючает в себя отдельные л исты (Sheets). В зависимости от назначения л исты рабочейк ниги могут быть различных тип ов. Например, дл я вводаданных в рабочую к нигу, с ц ел ью их хранения и дал ьнейш ейобработк и, исп ол ьзуются рабочие л исты (WORKSHEET).
Определение 8. Рабочий л ист (WORKSHEET) - это эл ек тронныйанал огтабл иц ы, у к оторойможно выделить отдельные стол бц ы и строк и, на п ересечении к оторых образуются к л етк и.
С тол бц ы рабочего л истаименуются бук вами, астрок и -ц ифрами (рис 3.4)
Пересечение стол бц аи строк и рабочего л истаобразует ячейк у (CELL). Например, стол бец А и строк а1 образуют ячейк у садресом А 1 (рис3.4.).
Замечание: Русские бук вы в обозначении стол бц ов исп ол ьзоватьнел ьзя.
Определение 9. Ячейк а- это эл ек тронныйанал огодной к л етк и таблиц ы.
В к аждую ячейк у можетбытьзап исано числ о, тек ст ил и формул а. Запись формул ы дол жнаначинаться со знак а =.
Программные средства EXCEL п озвол яют обрабатывать содержимое не тол ьк о отдельных ячеек , но так же и диапазонов (бл ок ов) ячеек .
Определение 10. Б л ок ом ячеек (RANGE) называется совок уп ность смежных ячеек , образующих п рямоугол ьную область.
А дрес бл ок а ячеек состоит из адресаверхнейл евой ячейк и бл ок а и адреса п равой нижней ячейк и бл ок а, раздел енныхзнак ом : Нарис 4.1 п ок азан бл ок ячеек садресом В 2:D4.
рис 4.1.
Перед вып ол нением многих оп ерац ий с так ими данными необходимо выдел ять (сел ек тировать) ячейк и, содержащие эти данные.
4.1 С елекция ячейки
С ел ек ц ия ячейк и это выбор ячейк и, с к оторойбудет работатьп ол ьзовател ьил и п рограммные СРЕДСТВАEXCEL.
Дл я сел ек ц ии ячейк и необходимо: 1. Установитьк урсор натребуемую ячейк у; 2 Щ ел к нутьл евойк л авиш еймыш и.
В изуал ьно сел ек ц ия сопровождается п оявл ением рамк и вокруг ячейк и, в нижнем п равом угл у к оторойимеется маленьк ийк вадрат - марк ер зап ол нения (File handle), см. рис.3.4 (ячейк АА 1).
4.2 С елекция б ло ка ячеек
С ел ек ц ия бл ок аячеек - это выбор бл ок аячеек , ск оторым будетработатьп ол ьзовател ьил и п ограммные средства EXCEL.
Дл я сел ек ц ии бл ок аячеек необходимо: 1. Установитьк урсор нал евую верхнюю ячейк у бл ок а; 2. Нажатьл евую к л авиш у мыш и;
3. Удерживая л евую к л авиш у мыш и нажатой, п ереместитьк урсор нап равую нижнюю ячейк у бл ок а.
В изуал ьно сел ек ц ия бл ок а ячеек сопровождается п оявл ением вок ругбл ок аячеек рамк и, в нижнем п равом угл у к оторойнаходится марк ер зап ол нения. К роме того, сел ек ц ия бл ок а ячеек сопровождается изменением ц вета всех ячеек бл ок а, к роме верхнейл евой. С ел ек тированный бл ок ячеек п ок азан нарис.4.1.
4.3 Вво д да нныхв ячейки
Дл я ввода в ячейк у числ а, тек ста ил и формул ы необходимо: 1.С ел ек тироватьячейк у; 2.Набратьвводимое данное нак л авиатуре ; 3.Нажатьк л авиш у ENTER.
Напомним, что ввод формул ы в ячейк у дол жен начинаться снаборазнак а=.
В водимая формул аотображается в строк е формул . 4.4 К о пир о ва ние фо р мул
К оп ирование формул является мощным средством автоматизации вычислений в EXCEL. О но п озвол яет распространить влияние формул ы с п ервой ячейк и некоторого бл ок аячеек наостал ьные ячейк и этого бл ок а. При этом автоматическ и п роисходит индек сац ия адресов ячеек , содержащ ихаргументы к оп ируемыхформул .
Дл я к оп ирования формул ы необходимо: 1.С ел ек тировать п ервую ячейк у бл ок а, содержащую формул у;
2.Установить к урсор намарк ер зап ол нения (маленьк ий к вадрат в п равом нижнем угл у ячейк и). При этом к урсор дол жен п ринятьвидк рестик а, т.е. (рис.4.2).
рис 4.2.
3.Нажать л евую к л авиш у мыш и и, удерживая ее, п ереместитьк урсор к п осл еднейячейк е бл ок а.
Есл и п ри к оп ировании формул ы требуется фик сац ия адресов некоторых ячеек ил и составл яющ их частейэтих адресов, то дл я этойц ел и исп ол ьзуется знак $, например,
$C6 - фиксируется стол бец С ; C$6 - фиксируется строк а 6; $C$6 - фиксируется ячейк а С 6.
Ф ик сац ия адресаячейк и п роисходиттак же п ри п рисвоении ячейк е имени (см. п .4.6)
4.5 Уста но вка гр а ницячеек и б ло ко в ячеек
Дл я п овыш ения наглядности п роц есса обработк и данных ц ел есообразно окружать ячейк и и бл ок и ячеек границ ами (рамками).
Дл я установк и границ вок ругячеек ил и бл ок ов ячеек необходимо: 1.С ел ек тироватьячейк у ил и бл ок ячеек ;
2.Навести к урсор настрел к у п равее к ноп к и “границ ы” и щ ел к нутьл евойк л авиш еймыш и (см. рис. 4.3);
3.В ыбрать к урсором из набора возможных видов границ - обрамление п о всем границ ам ячейк и (п осл едний вид границ в наборе) и щ ел к нуть л евойк л авиш еймыш и. Б л ок ячеек , ок руженныйрамк ами, п ок азан нарис.4.3.
рис 4.3.
4.6 Пр исво ение имен ячейка м и б ло ка м ячеек
Дл я автоматизации вычислений часто бывает необходимо п рисвоить именаотдел ьным ячейкам и бл ок ам ячеек средствами EXCEL .
Дл я п рисвоения имени ячейк е ил и бл ок у ячеек необходимо: 1. С ел ек тироватьячейк у ил и бл ок ячеек ;
2. Навести к урсор настрел к у сп раваот ок наимени и щ ел к нутьл евойк л авиш еймыш и;
3. Набратьнак л авиатуре имя, например _x; 4. Нажатьк л авиш у Enter.
При задании имени следует учитывать следующ ие п равил а: 1. И мя должно начинаться с бук вы ил и знак а п одчерк ивания . В к ачестве остальных символов могут исп ол ьзоваться бук вы, ц ифры и знак п одчерк ивания;
2. И мя не должно совп адатьс адресами ячеек и бл ок ов, например А 5; В 5:С 6.
4.7 И зменение шир инысто лб ца
При вводе в ячейк у текстовых данных иногда возник ает необходимость изменения ш ирины стол бц а. Дл я изменения ш ирины стол бц анеобходимо: 1. Установить к урсор на п равую границ у загол овк а стол бц а. При этом к урсор п риметвид .
2. Нажать л евую к л авиш у мыш и и, удерживая ее, п еремещ ать к урсор вправо (дл я увеличения) ил и вл ево (дл я уменьшения) ш ирины стол бц а.
4.8 И зменение цвета ячейки и б ло ко в ячеек
К роме установк и границ ячеек (бл ок ов ячеек ), п овыш ение наглядности п редставл яемых нарабочем л исте данных можно достичь изменением ц вета ячеек (бл ок ов ячеек ).
Дл я этого необходимо: 1.С ел ек тироватьячейк у (бл ок ячеек );
2.Установить к урсор настрел к у п равее к ноп к и "ц вет зал ивк и" и щ ел к нутьл евойк л авиш еймыш и;
3.В меню "ц вет зал ивк и" выбрать к урсором желаемый ц вет, например - изумрудный, и щ ел к нуть л евойк л авиш ей мыш и.
Дл я отмены заданного ц ветанеобходимо в меню "ц вет зал ивк и" установить к урсор на к ноп к у "нет зал ивк и" и щ ел к нутьл евойк л авиш еймыш и.
5 Решение тр а нспо р тно й за да чи
Рассмотрим следующ ую транспортную задачу [3]. Дл я строительства четырех объектов исп ол ьзуется к ирп ич, изготавл иваемыйнатрех заводах. Ежедневно к аждыйиз заводов может изготовить 100, 150 и 50 условных единиц к ирп ича (п редл ожение п оставщ ик ов). Потребности в к ирп иче нак аждом из строящихся объектов ежедневно составляют 75, 80, 60 и 85 условных единиц (сп рос п отребител ей). Т арифы п еревозок однойусл овнойединиц ы к ирп ичас к аждого из заводов к к аждому из строящихся объектов задаются матриц ейтрансп ортныхрасходов С .
e6 7 3 5u e u
С = e1 2 5 6u e8 10 20 1u e u
Т ребуется составить так ойп л ан п еревозок к ирп ичак строящимся объектам, п ри к отором общ ая стоимость п еревозок будетминимал ьной.
Дл я решения транспортной задачи на п ерсонал ьном к омп ьютере сисп ол ьзованием EXCEL необходимо: 1.В вести исходные данные в ячейк и рабочего л иста EXCEL;
2.Разметить бл ок и ячеек на рабочем л исте EXCEL, необходимые дл я моделирования объемов п еревозок , а так же дл я формирования элементов математическ ой модел и и ц ел евойфунк ц ии;
3.С формировать на рабочем л исте EXCEL элементы математическ оймодел и и ц елевую функ ц ию;
4.Настроить п рограмму " Поиск решения" и вып ол нить ее.
5.1 Вво д исхо дныхда нных
И сходными данными дл я решения транспортной задачи являются: - матриц атрансп ортныхрасходов; - п редл ожение п оставщ ик ов;
- сп росп отребител ей;
Напомним, что дл я вводаданного в ячейк у рабочего л ИСТАEXCEL необходимо: 1.С ел ек тироватьячейк у; 2.Набратьвводимое данное нак л авиатуре ; 3.Нажатьк л авиш у Enter.
Дл я наглядности бл ок и ячеек с введенными данными жел ател ьно обвести рамками (см. п . 4.5.).
Рабочий л ист EXCEL с введенными исходными данными дл я решения трансп ортнойзадачи п ок азан на рис5.1.
рис 5.1.
5.2 Разметка б ло ко в ячеек р а б о чего листа EXCEL
К роме исходных данных нарабочем л исте EXCEL дл я решения трансп ортнойзадачи необходимо п редусмотреть: 1.Б л ок ячеек "М атриц а п еревозок ", в к отором будут модел ироваться объемы п еревозок ;
2.Б л ок ячеек "Ф ак тическ и реализовано", в к отором будетмодел ироваться фак тическ ая реал изац ия п родук ц ии;
3.Б л ок ячеек "Ф ак тическ и п ол учено", в к отором будет модел ироваться фак тическ ое удовлетворение спроса;
4.Б л ок ячеек "Т рансп ортные расходы п о п отребител ям", в к отором будут п одсчитываться транспортные расходы п о к аждому п отребител ю;
5.Ячейк у "И того расходы", в к оторой будут модел ироваться итоговые транспортные расходы п о всем п отребител ям (ц елевая ячейк а).
Дл я наглядности указанные бл ок и ячеек ц ел есообразно обвести рамками. В ып ол ните эту оп ерац ию, называемую разметк ойбл ок ов ячеек , в соответствии п . 4.5.
Рабочийл ист EXCEL с размеченными бл ок ами ячеек п ок азан нарис.5.2.
рис. 5.2.
Т еп ерь в этих бл ок ах ячеек можно формировать элементы математическ оймодел и и ц елевую функ ц ию.
5.3 Ф о р мир о ва ние элементо в ма тема тическо й мо дели
Элементами математическ ой модел и задачи являются следующ ие суммы: AX , - фак тическ и реализовано i-ым j=1 n ij i =1,m;
транспортной п оставщ ик ом m a i=1Xij, - фак тическ и п ол учено j-ым п отребител ем j =1,n.
Дл я наш ейзадачи m=3, n=4.
Рассмотрим п роц есс формирования этих сумм на рабочем л исте EXCEL.
4
В начал е сформируем AX , i =1,3 в бл ок е j=1 ij
"Ф ак тическ и реализовано".
1.Зап ол ните ячейк и бл ок а "М атриц а п еревозок " (С 14:F16) числом 0,01.
2.С ел ек тируйте п ервую ячейк у бл ок а "Ф ак тическ и реализовано" (ячейк АІ14);
3.Наведите к урсор нак ноп к у a - автосуммирование и щ ел к ните л евойк л авиш еймыш и;
4.Нажмите к л авиш у Delete;
5.С ел ек тируйте п ервую строк у бл ок а "М атриц а п еревозок " (строк АС 14:F14);
6.Нажмите к л авиш у Enter;
7.С к оп ируйте формул у =С УМ М (С 14:F14) из п ервой ячейк и бл ок а "Ф ак тическ и реализовано" навсе остальные ячейк и этого бл ок а.
3
С формируем теп ерь AX j =1,4 - в бл ок е ij i=1 "Ф ак тическ и п ол учено".
Дл я этого вып ол ните следующ ие действия: 1.С ел ек тируйте п ервую ячейк у бл ок а "Ф ак тическ и п ол учено" (ячейк АС 18);
2.Наведите к урсор нак ноп к у a - автосуммирование и щ ел к ните л евойк л авиш еймыш и;
3.Нажмите к л авиш у Delete;
4.С ел ек тируйте п ервый стол бец бл ок а "М атриц а п еревозок " (С тол бец С 14:C16);
5.Нажмите к л авиш у Enter;
6.С к оп ируйте формул у =СУМ М (С 14:С 16) из п ервой ячейк и бл ок а "Ф ак тическ и п ол учено" наостал ьные ячейк и этого бл ок а.
5.4 Ф о р мир о ва ние целево й функции
Дл я формирования ц ел евойфунк ц ии введем вначал е формул ы, отражающие транспортные расходы п о к аждому п отребител ю, т.е. формул ы: 3 acijxij j = 1,4 в ячейк и бл ок а“Т рансп ортные расходы i=1 п о п отребител ям”
Дл я вводаэтихформул вып ол ните следующ ие действия: 1.С ел ек тируйте п ервую ячейк у бл ок а “Т рансп ортные расходы п о п отребител ям”(ячейк АС 21);
2.Наведите к урсор нак ноп к у a - автосуммирование и щ ел к ните л евойк л авиш еймыш и;
3.Нажмите к л авиш у “Delete ”;
4.С ел ек тируйте п ервый стол бец бл ок а “М атриц а Т рансп ортныхрасходов”(стол бец С 6:С 8);
5.Нажмите к л авиш у *;
6.С ел ек тируйте п ервый стол бец бл ок а “М атриц а п ревозок ”(стол бец С 14:С 16);
7.А к тивируйте строк у формул , наведя нанее к урсор и щ ел к нув затем л евойк л авиш еймыш и;
8.Нажмите одновременно три к л авиш и: “CTRL” “SHIFT” “ENTER”;
9.С к оп ируйте формул у {= С УМ М (С 6:С 8*С 14:С 16)} в остальные ячейк и бл ок а “Т рансп ортные расходы п о п отребител ям”;
С формируем теп ерь ц елевую функ ц ию транспортной задачи, выражаемую формул ойaacijxij , в ячейк у “И того j=1i=1
4 3 расходы”. Дл я этого: С ел ек тируйте ячейк у “И того расходы”(ячейк АІ21);
1.Наведите к урсор нак ноп к у a - автосуммирование и щ ел к ните л евойк л авиш еймыш и;
2.Нажмите к л авиш у “Delete”;
3.С ел ек тируйте бл ок ячеек “Т рансп ортные расходы п о п отребител ям”(С 21:F21);
4.Нажмите к л авиш у “Enter”;
Посл е формирования элементов математическ ой модел и и ц ел евойфунк ц ии трансп ортнойзадачи рабочий л ист ЕХС EL п римет вид, п ок азанныйнарис. 5.3.Т еп ерь можно п риступ ить к настройк е п рограммы “Поиск решения”.
рис 5.3.
5.5 Н а стр о й ка пр о гр а ММЫПО иск р ешения Дл я настройк и п рограммы “Поиск решения”на решение трансп ортнойзадачи вып ол ните следующ ие действия: 1.С ел ек тируйте ц елевую ячейк у “И того расходы” (ячейк АІ21);
2.Установите к урсор в строк е главного меню нап унк те “С ервис”и щ ел к ните л евойк л авиш еймыш и;
3.Установите к урсор нап унк т "Поиск решения" меню "С ервис", щ ел к ните л евойк л авиш еймыш и и убедитесь, что в п ол е “Установить ц елевую ячейк у” ок на диалога п рограммы “Поиск решения”ук азанаячейк а$I$21 (см. рис. 5.4)
рис 5.4.
4.Установите к урсор на п ерек л ючател ь “Равной М инимал ьному значению” и щ ел к ните л евой к л авиш ей мыш и;
5.Установите к урсор в п ол е “И зменяя ячейк и” и щ ел к ните л евойк л авиш еймыш и;
6.С ел ек тируйте бл ок ячеек “М атриц ап ервозок ” (бл ок С 14:F16);
7.Установите к урсор нак ноп к у “Добавить”и щ ел к ните л евойк л авиш еймыш и;
Появившееся ок но диалога к оманды “Добавление ограничения”п оказано нарис.5.5.
рис 5.5.
8.С ел ек тируйте бл ок ячеек “Ф ак тическ и реализовано” (бл ок I14:I16);
9.Убедитесь, что оператор сравнения <= уже выбран; 10.Установите к урсор на п ол е “О граничение”и щ ел к ните л евойк л авиш еймыш и;
11.С ел ек тируйте бл ок ячеек “Предложение п оставщ ик ов” (бл ок I6:I8) и убедитесь, что ок но диалога к оманды “Добавление ограничения”имеетвид, п оказанный нарис 5.6.
рис 5.6.
12.Установитьк урсор нак ноп к у “Добавить”и щ ел к ните л евойк л авиш еймыш и;
13.С ел ек тируйте бл ок ячеек “Ф ак тическ и п ол учено” (бл ок С 18:F18);
14. Установите к урсор настрел к у п рок рутк и значений оператора сравнения и щ ел к ните л евой к л авиш ей мыш и;
15.Установите к урсор назначение >= (бол ьш е ил и равно) и щ ел к ните л евойк л авиш еймыш и;
16.Установите к урсор нап ол е “О граничение”и щ ел к ните л евойк л авиш еймыш и;
17.С ел ек тируйте бл ок ячеек “С п рос п отребител ей” (бл ок С 10:F10) и убедитесь, что ок но диалога к оманды “Добавление ограничения”имеетвид, п ок азанныйна рис. 5.7.
рис 5.7.
18.Установите к урсор на к ноп к у “Добавить” и щ ел к ните л евойк л авиш еймыш и;
19.С ел ек тируйте бл ок ячеек “М атриц ап еревозок ”(бл ок С 14:F16);
20.Установите к урсор настрел к у п рок рутк и значений оп ераторасравнения и щ ел к ните л евойк л авиш еймыш и;
21.Установите к урсор назначение >= (бол ьш е ил и равно) и щ ел к ните л евойк л авиш еймыш и;
22.Установите к урсор на п ол е “О граничение” и щ ел к ните л евойк л авиш еймыш и;
23.Наберите нак л авиатуре ц ифру 0 и убедитесь, что ок но диалога к оманды “Добавление ограничения” имеет вид, п ок азанныйнарис. 5.8.
рис 5.8.
24.Установите к урсор на к ноп к у “Добавить” и щ ел к ните л евойк л авиш еймыш и;
25.Установите к урсор нак ноп к у "О тмена" и щ ел к ните л евойк л авиш еймыш и;
26.Убедитесь,что п оявивш ееся ок но п рограммы “Поиск решения”имеетвид, п ок азанныйнарис 5.9.
рис5.9.
27.Установите к урсор нак ноп к у “Параметры”и щ ел к ните к л авиш еймыш и;
28.В п оявивш емся ок не диалога “Параметры п оиск а решения”(см. рис.5.10), установите к урсор нафл ажок
“Л инейная модел ь”и щ ел к ните л евойк л авиш еймыш и; 29.Установите к урсор на к ноп к у “О К ” о щ ел к ните л евойк л авиш еймыш и;
рис5.10.
30.В п оявивш емся ок не "Поиск решения" установите к урсор нак ноп к у "В ып ол нить" и щ ел к ните л евойк л авиш ей мыш и.
31.Убедитесь, что нарабочем л исте EXCEL в бл ок е "М атриц а п еревозок " п оявл яется решение транспортной задачи, п оказанное нарис. 5.11.
рис5.11.
В п оявивш емся диалоговом ок не "Результаты п оиск а решения" установите к урсор на п ерек л ючател ь "В осстановить исходные значения" и щ ел к ните л евой к л авиш еймыш и. Дл я завершения расчетов щ ел к ните на к ноп к е О К . (см. рис 5.12).
рис5.12.
Замечание. В ып ол нение п унк тов 19-24 можно заменить установкой фл ажк а "Неотриц ател ьные значения" в ок не диалога"Параметры п оиск ареш ения".
6 Решение за да чи о пр еделения на иб о лее пр иб ыльно го о б ъ ема выпуска пр о дукции
Рассмотрим следующ ую задачу [3] . На маш иностроител ьном п редп риятии дл я изготовления четырех видов п родук ц ии исп ол ьзуется токарное, фрезерное , сверл ил ьное , расточное и ш л ифовал ьное оборудование, а так же к омп л ек тующ ие изделия. К роме того, дл я сборк и готовойп родук ц ии требуется вып ол нение оп редел енных сборочно-наладочных работ. Нормы расхода ресурсов на изготовление одного изделия к аждого вида п риведены в таблиц е на рис. 6.1. В этой же таблиц е ук азаны: имеющиеся в наличие ресурсы, ограничения, обусл овл енные спросом навыпуск п родук ц ии второго и третьего видов, и п рибыл ьотреал изац ии одного изделия. В отличие от [3] будем п редп ол агать, что в общ ем случае п рибыл ь с увеличением выпуск а п родук ц ии может уменьшаться. С теп ени влияния объемавып уск анап рибыл ь п о к аждому изделию так же п риведены в таблиц е. Заметим, что есл и степень влияния равнаединиц е, то увеличение объема выпуск а изделия не п риводит и уменьшению п рибыл и. Т ребуется оп редел ить так ой объем выпуск а п родук ц ии, к оторый обеспечивает п редп риятию наибол ьш ую п рибыл ь.
рис 6.1.
Дл я решения задачи на п ерсонал ьном к омп ьютере с исп ол ьзованием EXCEL необходимо: 1. В вести исходные данные в ячейк и рабочего л ИСТАEXCEL; 2. Разметить бл ок и ячеек , необходимые дл я моделирования объемавып уск ап родук ц ии, атак же дл я формирования элементов математическ оймодел и и ц ел евойфунк ц ии;
3. С формировать на рабочем л исте EXCEL элементы математическ оймодел и и ц елевую функ ц ию;
4. Настроитьп рограмму "Поиск решения" и вып ол нитьее.
6.1 Вво д исхо дныхда нных
И сходными данными дл я решения задачи оп редел ения наиболее п рибыл ьного объема выпуск а п родук ц ии являются: - имеющиеся в наличии ресурсы;
- нормы расходаресурсов навыпуск одного изделия;
- мак симал ьная и минимальная величина спроса на изделия;
