Транспартная задача ў excel. Пошук лепшага спосабу транспарціроўкі ад прадаўца да пакупніка

Excel - вельмі функцыянальная праграма. Яго можна выкарыстоўваць для вырашэння вялікай колькасці праблем, з якімі даводзіцца сутыкацца ў бізнэсе. Адзін з самых распаўсюджаных - транспарт. Уявіце сабе, што нам трэба зразумець, які спосаб транспарціроўкі ад вытворцы да канчатковага пакупніка найбольш аптымальны з пункту гледжання часу, грошай і іншых рэсурсаў. Дадзеная задача даволі папулярная, незалежна ад таго, у якой галіны вядзецца бізнес. Таму давайце больш падрабязна разгледзім, як яе рэалізаваць з дапамогай Excel.

Апісанне транспартнага задання

Такім чынам, у нас ёсць два контрагенты, якія пастаянна ўзаемадзейнічаюць адзін з адным. У нашым выпадку гэта пакупнік і прадавец. Трэба прыдумаць, як перавозіць грузы так, каб выдаткі былі мінімальнымі. Для гэтага трэба прадставіць усе дадзеныя ў схематычным або матрычным выглядзе. У Excel мы выкарыстоўваем апошні варыянт. У цэлым існуе два тыпу транспартных задач:

1. Закрыты. У гэтым выпадку попыт і прапанова знаходзяцца ў раўнавазе.
2. Адкрыты. Роўнасці паміж попытам і прапановай тут няма. Каб атрымаць рашэнне гэтай задачы, неабходна спачатку прывесці яе да першага тыпу, ураўняўшы попыт і прапанову. Для гэтага трэба ўвесці дадатковы паказчык – наяўнасць умоўнага пакупніка або прадаўца. Акрамя таго, трэба ўнесці пэўныя змены ў табліцу выдаткаў.

Як уключыць функцыю пошуку рашэння ў Excel

Для вырашэння транспартных задач у Excel існуе спецыяльная функцыя «Пошук рашэння». Ён не ўключаны па змаўчанні, таму вам трэба выканаць наступныя дзеянні:

1. Адкрыйце меню «Файл», якое знаходзіцца ў левым верхнім куце акна праграмы.
2. Пасля гэтага націсніце на кнопку з параметрамі.
3. Далей знаходзім падраздзел «Настройкі» і пераходзім у меню кіравання дапаўненнямі. Гэта невялікія праграмы, якія працуюць у асяроддзі Microsoft Excel. Мы бачым, што спачатку мы клікнулі па меню «Надбудовы», а затым у правай ніжняй частцы ўсталявалі пункт «Надбудовы Excel» і націснулі на кнопку «Перайсці». Усе неабходныя дзеянні вылучаны чырвонымі прастакутнікамі і стрэлкамі.
4. Далей ўключаем надбудову «Пошук рашэння», пасля чаго пацвярджаем свае дзеянні націскам кнопкі ОК. Зыходзячы з апісання налады, мы бачым, што яна прызначана для аналізу складаных дадзеных, такіх як навуковыя і фінансавыя.
5. Пасля гэтага пераходзім ва ўкладку «Дадзеныя», дзе бачым новую кнопку, якая называецца гэтак жа, як і надбудова. Яго можна знайсці ў групе інструментаў аналізу.

Застаецца толькі націснуць на гэтую кнопку, і мы прыступаем да рашэння транспартнай задачы. Але перад гэтым мы павінны пагаварыць крыху больш пра інструмент Рашальнік у Excel. Гэта спецыяльная надбудова Excel, якая дазваляе знайсці найбольш хуткае рашэнне праблемы. Характэрнай асаблівасцю з'яўляецца ўлік абмежаванняў, якія карыстальнік задае на этапе падрыхтоўкі. Кажучы простымі словамі, гэта падпраграма, якая дазваляе вызначыць найлепшы спосаб дасягнення пэўнай задачы. Такія задачы могуць уключаць наступнае:

1. Інвеставанне, пагрузка на склад або любая іншая падобная дзейнасць. У тым ліку і з дастаўкай тавараў.
2. Лепшы спосаб. Гэта ўключае ў сябе такія мэты, як дасягненне максімальнага прыбытку пры мінімальных выдатках, як дасягнуць лепшай якасці з даступнымі рэсурсамі і гэтак далей.

Акрамя транспартных задач, гэты дадатак таксама выкарыстоўваецца для наступных мэт:

1. Распрацоўка плана вытворчасці. Гэта значыць, колькі адзінак прадукту трэба вырабіць, каб атрымаць максімальны прыбытак.
2. Знайдзіце размеркаванне працы па розных відах работ так, каб агульныя затраты на вытворчасць тавару або паслугі былі найменшымі.
3. Усталюйце мінімальны час, якое спатрэбіцца для выканання ўсёй працы.

Як бачыце, задачы вельмі розныя. Універсальнае правіла прымянення гэтай надбудовы заключаецца ў тым, што перад рашэннем задачы неабходна стварыць мадэль, якая б адпавядала ключавым характарыстыках пастаўленай задачы. Мадэль - гэта набор функцый, якія выкарыстоўваюць зменныя ў якасці аргументаў. Гэта значыць каштоўнасці, якія могуць змяняцца.

Важна адзначыць, што аптымізацыя набору значэнняў праводзіцца выключна па адным паказчыку, які называецца мэтавай функцыяй.

Надбудова Solver пералічвае розныя значэнні зменных, якія перадаюцца ў мэтавую функцыю такім чынам, каб яна была максімальнай, мінімальнай або роўнай пэўнаму значэнню (гэта якраз і ёсць абмежаванне). Ёсць яшчэ адна функцыя, чымсьці падобная па прынцыпе працы, якую часта блытаюць з «Пошукам рашэння». Гэта называецца «Выбар варыянту». Але калі капнуць глыбей, розніца паміж імі каласальная:

1. Функцыя Goal Seek не працуе з больш чым адной зменнай.
2. Ён не прадугледжвае магчымасці ўсталёўваць абмежаванні на зменныя.
3. Ён здольны вызначыць толькі роўнасць мэтавай функцыі пэўнай велічыні, але не дае магчымасці знайсці максімум і мінімум. Таму для нашай задачы ён не падыходзіць.
4. Здольны эфектыўна разлічваць, толькі калі мадэль лінейнага тыпу. Калі мадэль нелінейная, то знаходзіць значэнне, якое найбольш блізкае да зыходнага значэння.

Транспартная задача значна больш складаная па сваёй структуры, таму надбудовы «Выбар параметраў» для гэтага недастаткова. Давайце больш падрабязна разгледзім, як рэалізаваць функцыю «Пошук рашэння» на практыцы на прыкладзе транспартнай задачы.

Прыклад рашэння транспартнай задачы ў Excel

Каб наглядна прадэманстраваць, як на практыцы вырашаюцца транспартныя задачы ў Excel, прывядзем прыклад.

Умовы задач

Дапусцім, у нас ёсць 6 прадаўцоў і 7 пакупнікоў. Попыт і прапанова паміж імі размяркоўваюцца адпаведна наступным чынам: 36, 51, 32, 44, 35 і 38 адзінак - прадаўцы, а 33, 48, 30, 36, 33, 24 і 32 адзінкі - пакупнікі. Калі вы прасумуеце ўсе гэтыя значэнні, вы ўбачыце, што попыт і прапанова знаходзяцца ў раўнавазе. Такім чынам, гэтая задача закрытага тыпу, якая вырашаецца вельмі проста.

Акрамя таго, у нас ёсць інфармацыя аб тым, колькі трэба выдаткаваць на праезд з пункта А ў пункт Б (у прыкладзе яны вылучаны жоўтымі клеткамі).

Рашэнне - пакрокавы алгарытм

Цяпер, пасля таго, як мы азнаёміліся з табліцамі з зыходнымі дадзенымі, мы можам выкарыстоўваць наступны алгарытм для вырашэння гэтай задачы:

1. Спачатку робім табліцу, якая складаецца з 6 радкоў і 7 слупкоў.
2. Пасля гэтага пераходзім да любой ячэйцы, якая не змяшчае значэнняў і пры гэтым ляжыць па-за толькі што створанай табліцы, і ўстаўляем функцыю. Для гэтага націсніце на кнопку fx, якая знаходзіцца злева ад радка ўводу функцыі.
3. У нас ёсць акно, у якім трэба выбраць катэгорыю «Матэматыка». Якая функцыя нас цікавіць? Той, які вылучаны на гэтым скрыншоце. Функцыя SUMPRODUCT памнажае дыяпазоны або масівы паміж сабой і сумуе іх. Якраз тое, што нам трэба. Пасля гэтага націсніце клавішу ОК.
4. Далей на экране з'явіцца акно, у якім неабходна задаць параметры функцыі. Яны наступныя:
   1. Масіў 1. Гэта першы аргумент, у які мы запісваем дыяпазон, які вылучаны жоўтым колерам. Вы можаце задаць параметры функцыі як з дапамогай клавіятуры, так і выбраўшы адпаведную вобласць левай кнопкай мышы.
   2. Масіў 2. Гэта другі аргумент, які з'яўляецца толькі што створанай табліцай. Дзеянні выконваюцца такім жа чынам.

Пацвердзіце свае дзеянні націскам кнопкі OK.

1. Пасля гэтага пстрыкаем левай кнопкай мышы па ячэйцы, якая служыць левай верхняй часткай зноў створанай табліцы. Цяпер зноў націсніце кнопку функцыі ўстаўкі.
2. Выбіраем тую ж катэгорыю, што і ў папярэднім выпадку. Але на гэты раз нас цікавіць функцыя SUM.
3. Цяпер ідзе этап запаўнення аргументаў. У якасці першага аргумента мы пішам верхні радок табліцы, якую мы стварылі ў пачатку. Як і раней, гэта можна зрабіць, вылучыўшы гэтыя вочкі на аркушы, або ўручную. Пацвярджаем свае дзеянні націскам кнопкі ОК.
4. Мы ўбачым вынікі ў вочку з функцыяй. У дадзеным выпадку гэта нуль. Далей перамесціце курсор у правы ніжні кут, пасля чаго з'явіцца маркер аўтазапаўнення. Выглядае як маленькі чорны плюш. Калі ён з'явіўся, то, утрымліваючы левую кнопку мышы, перамяшчаем курсор у апошнюю вочка нашай табліцы.
5. Гэта дае нам магчымасць перанесці формулу ва ўсе астатнія вочкі і атрымаць правільныя вынікі без неабходнасці правядзення дадатковых вылічэнняў.
6. Наступны крок - выбраць верхнюю левую ячэйку і ўставіць функцыю SUM у яе. Пасля гэтага ўводзім аргументы і з дапамогай маркера аўтазапаўнення запаўняем усе астатнія вочкі.
7. Пасля гэтага прыступаем непасрэдна да вырашэння задачы. Для гэтага мы будзем выкарыстоўваць надбудову, якую мы ўключылі раней. Пераходзім ва ўкладку «Дадзеныя», і там знаходзім інструмент «Пошук рашэння». Націскаем на гэтую кнопку.
8. Зараз перад вачыма з'явілася акно, праз якое можна наладзіць параметры нашага дапаўненні. Давайце разгледзім кожны з гэтых варыянтаў:
   1. Аптымізацыя мэтавай функцыі. Тут нам трэба выбраць вочка, якая змяшчае функцыю SUMPRODUCT. Мы бачым, што гэтая опцыя дазваляе выбраць функцыю, для якой будзе праводзіцца пошук рашэння.
   2. Раней. Тут усталёўваем опцыю «Мінімум».
   3. Змяняючы вочкі зменных. Тут мы паказваем дыяпазон, які адпавядае табліцы, якую мы стварылі ў самым пачатку (за выключэннем радка і слупка зводкі).
   4. З улікам абмежаванняў. Тут нам трэба дадаць абмежаванні, націснуўшы кнопку «Дадаць».
   5. Памятаем, якое абмежаванне трэба стварыць - сума значэнняў патрабаванняў пакупнікоў і прапаноў прадаўцоў павінна быць аднолькавай.
9. Заданне абмежаванняў выконваецца наступным чынам:
   1. Спасылка на вочкі. Тут мы ўводзім дыяпазон табліцы для разлікаў.
   2. Умовы. Гэта матэматычная аперацыя, па якой правяраецца дыяпазон, указаны ў першым полі ўводу.
   3. Значэнне ўмовы або абмежавання. Тут мы ўводзім адпаведны слупок у зыходную табліцу.
   4. Пасля выканання ўсіх крокаў націскаем кнопку ОК, тым самым пацвярджаючы свае дзеянні.

Сапраўды такія ж дзеянні выконваем і для верхніх шэрагаў, задаючы ўмова: яны павінны быць роўнымі.

Наступны крок - пастаноўка ўмоў. Нам трэба задаць наступныя крытэрыі для сумы вочак у табліцы – больш або роўна нулю, цэлае лік. У выніку мы маем такі пералік умоваў, пры якіх задача вырашаецца. Тут трэба пераканацца, што сцяжок побач з опцыяй «Зрабіць зменныя без абмежаванняў неадмоўнымі» пастаўлены. Таксама ў нашай сітуацыі патрабуецца выбар метаду рашэння задачы – «Пошук рашэння нелінейных задач метадамі OPG». Цяпер можна смела сказаць, што налада выканана. Такім чынам, засталося толькі правесці разлікі. Для гэтага націсніце на кнопку «Знайсці рашэнне».

Пасля гэтага ўсе дадзеныя будуць разлічаны аўтаматычна, а затым Excel пакажа акно з вынікамі. Гэта неабходна для таго, каб пераправерыць працу кампутара, так як магчымыя памылкі, калі ўмовы былі раней зададзены няправільна. Калі ўсё правільна, то націсніце кнопку «ОК» і ўбачыце гатовую табліцу.

Калі атрымаецца, што наша задача стала адкрытага тыпу, то гэта дрэнна, бо трэба адрэдагаваць зыходную табліцу, каб задача ператварылася ў закрытую. Аднак, калі гэта будзе зроблена, астатні алгарытм будзе такім жа.

заключэнне

Як бачыце, Excel можна выкарыстоўваць і для вельмі складаных разлікаў, якія на першы погляд недаступныя просты кампутарнай праграме, усталяванай практычна ва ўсіх. Аднак гэта так. Сёння мы ўжо разгледзелі прасунуты ўзровень выкарыстання. Гэтая тэма не такая ўжо і простая, але, як кажуць, дарогу здолее той, хто ідзе. Галоўнае - прытрымлівацца плана дзеянняў, і дакладна выконваць усе дзеянні, названыя вышэй. Тады памылак не будзе, і праграма самастойна выканае ўсе неабходныя разлікі. Не трэба будзе думаць, якую функцыю выкарыстоўваць і гэтак далей.