MICROSOFT EXCEL БАҒДАРЛАМАСЫНЫҢ
МҮМКІНДІКТЕРІ
БЕЙСЕНОВА Д.Р., СЫЗДЫҚОВА
Н.Қ., ТҰРДЫБЕКОВА К.М.
Е.А.БУКЕТОВ атындағы ҚАРАҒАНДЫ
МЕМЛЕКЕТТІК УНИВЕРСИТЕТІ
Жұмыстың
мақсаты – транспорттық есептер үшін материалдық
қормен шектеп қамтамасыздандыруды есепке ала отырып,
азық-түлікті тасымалдаудың тиімді жоспарын құруды
үйрену, жоспарларды Microsoft Excel программаласындағы Solver
қосымшасымен сызықтық программалаудың
математикалық компьютерлік әдісімен тиімдендіру [1].
Тасымалдау жоспары кесте
түрінде беріледі, онда жабдықтаушылардың
қоймаларындағы азық-түлік қоры мен тұтынушы
үшін қажетті азық-түліктің мөлшері
көрсетілген. Жоспар құрылған кезде
өндірістің мақсаты – тасымалдауда шығынның аз
болуы [2].
Транспорттық есептің
жалпы қойылуы қандай да бір біркелкі жүкті A1, A2 ,…,Am
m жіберу бекеттерінен B1, B2
,…,Bn n қабылдаушы бекеттеріне тасымалдаудың тиімді
жоспарын анықтау болып табылады. Тиімділік белгісі ретінде барлық
жүкті тасымалдаудың ең төменгі құны алынады
немесе оны тасымалдауға кеткен ең аз уақыт алынады [3].
– i-ші жіберу бекетінен j-ші қабылдаушы бекетіне жүк
бірлігін тасымалдау тарифтары; 
– i-ші жіберу бекетіндегі жүк
қоры; 
– j-ші қабылдаушы бекетіне
жүк қажеттігі; 
– i-ші жіберу
бекетінен j-ші қабылдаушы
бекетіне тасымалдаушы жүк бірліктерінің саны. Сонда есептің
экономика-математикалық қойылуы – мынадай шарттарда
(1)
берілген функцияның 
(2) ең кіші мәнін анықтау.
Егер қабылдаушы бекеттерінде
жүк қажеттігі жіберу бекеттеріндегі жүк қорына
тең болса, яғни 
, (3) онда транспорттық есептің моделі
жабық деп, қарсы жағдайда ашық деп аталады.
Қажеттіктен гөрі
қор көп болса, немесе қажеттік қордан асып кетсе, онда
нөлдік тарифпен жалған жіберу бекеті немесе жалған
қабылдаушы бекеттері енгізіледі.
Есептің қойылуы
A1, A2 ,A3 үш базаға сәйкесінше 50,30
және 10-ға тең біркелкі жүк түсті. Осы
жүкті төрт B1, B2
, B3, В4 қабылдаушы
бекеттеріне сәйкесінше 30,20,10 және 20 бірліктерінде тасымалдау
керек. Жүк бірлігін тасымалдау тарифтары 1-кестеде берілген.
Транспорттық есептегі тасымалдаудың тиімді жоспарын табу керек.
Кестелік модель
Жоспарды кесте түрінде
құрамыз.
Кесте 1
|
|
A |
B |
C |
D |
E |
F |
|
1 |
Транспорттық есеп (минимум) |
|||||
|
2 |
Жабдықтаушылар |
Тұтынушылар |
Қорлар |
|||
|
3 |
B1 |
B2 |
B3 |
B4 |
||
|
4 |
A1 |
1 |
2 |
4 |
1 |
50 |
|
5 |
A2 |
2 |
3 |
1 |
5 |
30 |
|
6 |
A3 |
3 |
2 |
4 |
4 |
10 |
|
7 |
Қажеттіліктер |
30 |
20 |
10 |
20 |
|
Жүк бірліктерін
тасымалдау тарифтары
Жоспарлық кесте
құрған соң көрсеткіштерді есептеу формулаларымен байланыстыру
керек.
Кестедегі формулалар
Бастапқы деректердің
формулалары мен сандары 2 және 3 кестелерде көрсетілген.
Кесте 2
Бастапқы деректердің формулалары мен сандары
|
… |
A |
B |
C |
D |
|
8 |
|
|
|
|
|
9 |
|
B1 |
B2 |
B3 |
|
10 |
A1 |
|
|
|
|
11 |
A2 |
|
|
|
|
12 |
A3 |
|
|
|
|
13 |
Апарылды |
=СУММ(В10:В12) |
=СУММ(С10:С12) |
=СУММ(D10:D12) |
Кесте 3
2 кесте жалғасы
|
E |
F |
G |
|
|
|
|
|
B4 |
Әкетілді |
Қалдық |
|
|
=СУММ(В10:E10) |
=F4-F10 |
|
|
=СУММ(В11:E11) |
=F5-F11 |
|
|
=СУММ(В12:E12) |
=F6-F12 |
|
=СУММ(E10:E12) |
|
|
Ғ бағынында (тауардың
жабдықтаушыларға тасымалдану жоспары) әрбір жабдықтаушы
үшін жоспарлы азық-түліктің қосындысын есептеу
формулалары енгізілген. 13-жолда (тауардың тұтынушыларға
жоспарлы тасымалдануы) әрбір
тұтынушы үшін азық-түліктің қосындысын
есептеу формулалары енгізілген. G бағанында (қалдық)
әрбір жабдықтаушы үшін қоймалардағы
қалдықтарды есептеу формулалары енгізілген.
Жұмыстың орындалу реті
Жұмыстың
бастапқы кезеңдерінде тәжірибені және нәтижелерді
өңдеуді жоспарлауды автоматтандыру тиімсіз, өйткені студент
белсенділік көрсетпей, ізденбей, дайын нәтиже алады.
Тәжірибені өзі басқарып, зерттеу пәнін түсіне
бастаған соң, $B$10:$E$12 ұяшықтарындағы жабдықтаулар
көлемін өзгерте отырып, G13 ұяшығындағы
шығындарды азайта отырып, сонымен бірге G бағанындағы
қорлар шығынын бақылай отырып, жоспарларды және
басқаруды автоматтандыруға болады. Шығын қоймадағы қордан (Ғ бағаны) асып кетпеуі керек.
Тиімдендіру
бағдарламасының көмегімен берілген есептің орындалуын
жеңілдетеміз. «Сервис»
менюіндегі «Поиск решения»
командасын таңдаған соң, сұхбат терезе пайда болады,
сол терезеде келесі шарттарды көрсетеміз.
Установить целевую ячейку өрісі мақсатты
ұяшықты көрсету қызметін атқарады, оның
мәнін максималдап, немесе минималдап, немесе берілген санға
тең деп ала аламыз. Бұл ұяшықта формула болуы керек.
Біздің есебімізде бұл G13 ұяшығы (жалпы шығын)
(1-сурет).
1-сурет. «Поиск решения»
сұхбат терезесі
Равной батырмасы мақсатты ұяшықтың
берілген мәнінің вариантын таңдау үшін керек. Берілген
санды қою үшін оны өріске енгізу керек. Біздің
есебімізде пайданы максималдау
үшін максимальное значение батырмасын
басу керек.
Изменяя ячейки өрісінде Установить целевую ячейку өрісіндегі көрсетілген
ұяшықтардың мәндерін тиімдендіру шарты мен
қойылған шектеулер орындалғанша шешімнің іздестірілуі
кезінде өзгерілетін мәндер орналасқан ұяшықтар
көрсетіледі. Изменяя ячейки өрісіне
үтір арқылы ажыратылған өзгерілетін
ұяшықтар аттары немесе адрестері енгізіледі. Біздің
есебімізде азық-түлікті тасымалдаудың тиімді жоспарының
шамалары берілген В10:Е12
ұяшықтарының аралығын ензіземіз. Өзгерілетін
ұяшықтар тура немесе жанама түрде мақсатты
ұяшықпен байланысады. 200-ге дейін өзгерілетін
ұяшықтарды қоюға болады.
Предположить өрісі Установить
целевую ячейку өрісінде сілтемесі берілген формулаға
ықпал ететін ұяшықтарды автоматты түрде іздеу
үшін қолданылады. Іздеу нәтижесі Изменяя ячейки өрісінде кескінделеді.
Ограничения өрістерінде берілген есептің шекті
шарттарының тізімі кескінделеді.
Біздің жағдайымызда
бұл шамалар: $B$13:$E$13=$B$7:$E$7;
$F$10:$F$12≤$F$4:$F$6.
Добавить командасы Добавить ограничение сұхбат
терезесін кескіндеу үшін қажет. Изменить командасы Изменить
ограничения сұхбат терезесін кескіндеу үшін қажет. Удалить командасы курсормен
көрсетілген шектемелерд алып тастау үшін қажет. Выполнить командасы
қойылған есептің шешімін іздеуді бастау үшін
қажет. Закрыть командасы
қойылған есептің шешімін іздеуді жібермей, сұхбат терезеден
шығу үшін қажет. Бұл Параметры, Добавить, Изменить немесе Удалить батырмаларына басқан кезде шығатын сұхбат
терезедегі қоюлар сақталынады.
Параметры батырмасы Параметры
поиска решения сұхбат терезесін кескіндеу үшін қажет,
бұл терезеде тиімдендіру моделін сақтау немесе жүктеуге
болады және шешім іздеудің нұсқалары көрсетіледі.
Восстановить батырмасы сұхбат терезесінің
өрістерін тазалау үшін және шешім іздеудің үнсіз
келісім бойынша қолданылатын параметрлерінің мәндерін
қалыпқа келтіру үшін қажет.
Ссылка на ячейку өрісінде мәндеріне
шектеме қойылатын ұяшықтың немесе ұяшықтар
аралығының адресі немесе аты енгізіледі.
2-сурет. «Параметры
поиска решения» сұхбат терезесі.
Сілтеме мен шектеме арасына
ашылған тізімнен шартты оператор таңдалынады. Бұлар:
көпемес, аз емес, тең, және т.б.
Ограничения өрісінде сандар, формула немесе
шектелінетін мәндері бар немесе есептелінетін шектелінетін мәндері
бар ұяшықтардың аттары енгізіледі.
Жаңа шартты қою
үшін Добавить батырмасын басу
керек. Поиск решения сұхбат
терезесіне қайту үшін ОК
батырмасын басады. 2-суретке сәйкес модельді сызықтық деп
қабылдаймыз.
Выполнить батырмасына басқан соң 4-кестедегі
нәтижені аламыз.
Кесте 4
Алынған нәтиже
|
|
A |
B |
C |
D |
E |
F |
G |
|
8 |
|
|
|
|
|
|
|
|
9 |
|
B1 |
B2 |
B3 |
B4 |
Әкетілді |
Қалдық |
|
10 |
A1 |
20 |
10 |
0 |
20 |
50 |
0 |
|
11 |
A2 |
10 |
0 |
10 |
0 |
20 |
10 |
|
12 |
A3 |
0 |
10 |
0 |
0 |
10 |
0 |
|
13 |
Апарылды |
30 |
20 |
10 |
20 |
Жалпы шығын |
110 |
Шешім іздеу модельдердің нұсқаларын
сақтау мүмкіндігін және оларды тез жүктеу
мүмкіндігін береді. Бұл үшін Сервис менюінен Поиск
решения командасын таңдау керек. Содан кейін Параметры батырмасын басып, Сохранить
модель батырмасын басу керек. Сохранить
модель терезесі шығады (3 сурет)
3-сурет. «Сохранить модель»
сұхбат терезесі
Содан соң Задайте область модели өрісінде
тиімдендіру моделін орналастыратын бағанның жоғарғы
ұяшығына сілтеме қойылады.
Поиск решения және Параметры
поиска решения сұхбат терезелерінің басқару
элементтерінің мәндері параққа жазылады. Бір
парақта бірнеше тиімдендіру модельдерін қолдану үшін, оларды
әртүрлі аралықтарға (бағандарға)
сақтау керек.
Ұсынылатын
ұяшық аралығында әрбір шектеме үшін бір
ұяшық және де үш ұяшық болады. Модельді
сақтайтын бағанның жоғарғы ұяшығына
ғана сілтеме қоюға болады.
Загрузить модель сұхбат терезесі
тиімдендіру моделі жүктелетін облысты беру үшін қолданылады.
Сілтеме модельдің толық облысының адресін көрсету керек
(тек бірінші ұяшықты ғана емес).
Тиімдендендіруді орындауға
жіберу үшін Поиск решения
терезесіндегі Выполнить батырмасына
басу керек.
Программа жұмыс істей
бастағанда қатынас жолында (парақтың төменгі сол
жақ бұрышында) Постановка
задачи... деген мәлімет шығады. Модель және алгоритм
параметрлері жазылған кесте автоматты түрде математикалық
программалау есептерінің қойылуының стандартына келтіріледі.
Бұл Excel-дің артықшылығы. Есептің қойылуы
кезеңінен кейін есеп шешіледі.
Санау аяқталған
соң Результаты поиска решения
сұхбат терезесі шығады.
Сәйкес баырманы басып, табылған шешімді модельдің
ұяшықтарында сақтауға немесе бастапқы
мәндерді қалпына келтіруге болады.
Демек,
жабдықтаушылардың қоймаларындағы қор шектелген
кезде, аз шығын жұмсалды. Пайда табуды тездетіп, Solver тиімдендіру
бағдарламасы берілген экономикалық еместің шешімін
ұтымды жасады [4].
Берілген есептің шешімінен
қорытынды шығара отырып, практикалық мәселелерді шешуде
математикалық әдістерді қолдануды төрт
тұрғыдан қарасыруға болады:
1. Экономикалық ақпараттар жүйесін жетілдіру.
Математикалық әдістер экономикалық ақпараттар
жүйесін реттеуге, бар ақпараттың кемшіліктерін
айқындауға мүмкіндік береді.
Экономикалық-математикалық модельдер жасап, қолдану
экономикалық ақпаратты жетілдіру жолының бірі.
2. Экономикалық
есептеулердің дәлдігі жоғарылайды. Экономикалық
есептерді ЭЕМ қолдану арқылы шешу қолмен жүргізілетін
күрделі есептеулерді жеңілдетеді.
3. Экономикалық
мәселелерді сандық талдауды тереңдетеді. Модельдеу
әдісін қолданудың арқасында нақты сандық
талдау мүмкіндігі күшейеді.
4. Жаңа экономикалық
есептерді шешу. Математикалық модельдеу арқылы практикалық
түрде шешу мүмкіндігі жоқ экономикалық есептерді шешуге
болады, мысалы: халықшаруашылығы жоспарының тиімді жоспарын
табу, халықшаруашылық шараларды имитациялау, күрделі
экономикалық объектілердің жұмысын бақылауды
автоматтандыру.
Әдебиеттер тізімі
1. Горчаков А.А. Компьютерные
экономико-математические модели. – М.:ЮНИТИ, 1995. – 201 с.
2. Додж М. и др. Эффективная работа с MS Excel 2000. – СПб.:Питер, 2001. – 161 с.
3. Замков О.О., Толстопятенко А.В., Черемных Ю.Н.
Математические методы в экономике: Учебник. – М.:МГУ им. М.В. Ломоносова, Изд-во
«ДИС», 1997. – 368 с.
4. Карасев А.И., Кремер Н.Ш., Савельева Т.И.
Математические методы и модели в планировании. – М., 1987. – 263 с.