Çfarë është Excel Solver?

2024 Autor: Abigail Brown | [email protected]. E modifikuara e fundit: 2023-12-17 07:01

Shtesa Excel Solver kryen optimizim matematikor. Kjo zakonisht përdoret për të përshtatur modele komplekse me të dhënat ose për të gjetur zgjidhje përsëritëse për problemet. Për shembull, mund të dëshironi të vendosni një kurbë përmes disa pikave të të dhënave, duke përdorur një ekuacion. Zgjidhësi mund të gjejë konstantet në ekuacion që japin përshtatjen më të mirë të të dhënave. Një aplikim tjetër është kur është e vështirë të riorganizohet një model për ta bërë produktin e kërkuar subjekt të një ekuacioni.

Ku është Solver në Excel?

Shtesa Solver përfshihet me Excel, por nuk ngarkohet gjithmonë si pjesë e një instalimi të paracaktuar. Për të kontrolluar nëse është i ngarkuar, zgjidhni skedën DATA dhe kërkoni ikonën Zgjidh në seksionin Analysis.

Nëse nuk mund ta gjeni Zgjidhjen nën skedën DATA, atëherë do t'ju duhet të ngarkoni shtesën:

1. Zgjidhni skedën FILE dhe më pas zgjidhni Opsionet.

   

2. Në kutinë e dialogut Opsionet zgjidhni Add-Ins nga skedat në anën e majtë.

   

3. Në fund të dritares, zgjidhni Excel Add-ins nga menyja rënëse Manage dhe zgjidhni Shko…

   

4. Shikoni kutinë e zgjedhjes pranë Shtesës Solver dhe zgjidhni OK.

   

5. Komanda Solver duhet të shfaqet tani në skedën DATA. Je gati të përdorësh Solver.

   

Përdorimi i Solver në Excel

Le të fillojmë me një shembull të thjeshtë për të kuptuar se çfarë bën Solver. Imagjinoni që ne duam të dimë se çfarë rreze do të japë një rreth me një sipërfaqe prej 50 njësi katrore. Ne e dimë ekuacionin për sipërfaqen e një rrethi (A=pi r²). Sigurisht, ne mund ta rirregullojmë këtë ekuacion për të dhënë rrezen e kërkuar për një zonë të caktuar, por për hir të shembullit le të pretendojmë se nuk dimë ta bëjmë këtë.

Krijoni një fletëllogaritëse me rreze në B1 dhe llogaritni zonën në B2 duke përdorur ekuacionin =pi()B1^2.

Mund ta rregullojmë manualisht vlerën në B1 derisa B2 të tregojë një vlerë që është mjaft afër 50. Në varësi të saktësisë ne duhet të jetë, kjo mund të jetë një qasje praktike. Megjithatë, nëse duhet të jemi shumë të saktë, do të duhet shumë kohë për të bërë rregullimet e kërkuara. Në fakt, kjo është në thelb ajo që bën Solver. Ai bën rregullime të vlerave në qeliza të caktuara dhe kontrollon vlerën në një qelizë të synuar:

1. Zgjidhni skedën DATA dhe Solver, për të ngarkuar kutinë e dialogut Parametrat e Zgjidhjes

2. Cakto qelizën e Objektivit të jetë Zona, B2. Kjo është vlera që do të kontrollohet, duke rregulluar qelizat e tjera derisa kjo të arrijë vlerën e saktë.

   

3. Zgjidhni butonin për Vlera e: dhe vendosni një vlerë prej 50. Kjo është vlera që duhet të arrijë B2.

   

4. Në kutinë me titull Duke ndryshuar qelizat e ndryshueshme: shkruani qelizën që përmban rreze, B1.

   

5. Lëri opsionet e tjera siç janë si parazgjedhje dhe zgjidhni Zgjidh. Optimizimi është kryer, vlera e B1 është rregulluar derisa B2 të jetë 50 dhe shfaqet dialogu Solver Results.

   

6. Zgjidh OK për të mbajtur zgjidhjen.

   

Ky shembull i thjeshtë tregoi se si funksionon zgjidhësi. Në këtë rast, zgjidhjen mund ta kishim më lehtë në mënyra të tjera. Më pas do të shikojmë disa shembuj ku Solver jep zgjidhje që do të ishte e vështirë të gjendeshin në ndonjë mënyrë tjetër.

Përshtatja e një modeli kompleks duke përdorur shtesën Excel Solver

Excel ka një funksion të integruar për të kryer regresion linear, duke vendosur një vijë të drejtë përmes një grupi të dhënash. Shumë funksione të zakonshme jolineare mund të linearizohen që do të thotë se regresioni linear mund të përdoret për të përshtatur funksione të tilla si eksponencialet. Për funksione më komplekse, Zgjidhësi mund të përdoret për të kryer "minimizimin e katrorëve më të vegjël". Në këtë shembull, ne do të shqyrtojmë përshtatjen e një ekuacioni të formës ax^b+cx^d me të dhënat e paraqitura më poshtë.

Kjo përfshin hapat e mëposhtëm:

1. Rregulloni grupin e të dhënave me vlerat x në kolonën A dhe vlerat y në kolonën B.
2. Krijoni 4 vlerat e koeficientëve (a, b, c dhe d) diku në fletëllogaritëse, këtyre mund t'u jepen vlera fillestare arbitrare.

3. Krijoni një kolonë me vlera të përshtatura Y, duke përdorur një ekuacion të formës ax^b+cx^d i cili referon koeficientët e krijuar në hapin 2 dhe vlerat x në kolonën A. Vini re se për të kopjuar formulën poshtë kolona, referencat ndaj koeficientëve duhet të jenë absolute ndërsa referencat për vlerat x duhet të jenë relative.

   

4. Megjithëse jo thelbësore, mund të merrni një tregues vizual se sa mirë përshtatet ekuacioni duke vizatuar të dy kolonat y kundrejt vlerave x në një tabelë të vetme shpërndarjeje XY. Ka kuptim të përdoren shënuesit për pikat e të dhënave origjinale, pasi këto janë vlera diskrete me zhurmë, dhe të përdoret një vijë për ekuacionin e përshtatur.

   

5. Më pas, ne kemi nevojë për një mënyrë për të përcaktuar diferencën midis të dhënave dhe ekuacionit tonë të përshtatur. Mënyra standarde për ta bërë këtë është llogaritja e shumës së diferencave në katror. Në një kolonë të tretë, për çdo rresht, vlera origjinale e të dhënave për Y zbritet nga vlera e ekuacionit të përshtatur dhe rezultati është në katror. Pra, në D2, vlera jepet me =(C2-B2)^2 Më pas llogaritet shuma e të gjitha këtyre vlerave në katror. Meqenëse vlerat janë në katror ato mund të jenë vetëm pozitive.

   

6. Tani jeni gati për të kryer optimizimin duke përdorur Solver. Janë katër koeficientë që duhen rregulluar (a, b, c dhe d). Ju gjithashtu keni një vlerë të vetme objektive për të minimizuar, shumën e diferencave në katror. Hapni zgjidhësin, si më sipër, dhe vendosni parametrat e zgjidhësit që t'i referohen këtyre vlerave, siç tregohet më poshtë.

   

7. Çzgjidh opsionin për të Bëni variablat e pakufizuara jo-negative, kjo do t'i detyronte të gjithë koeficientët të marrin vlera pozitive.

   

8. Zgjidhni Zgjidh dhe rishikoni rezultatet. Grafiku do të përditësohet duke dhënë një tregues të mirë të mirësisë së përshtatjes. Nëse zgjidhësi nuk prodhon një përshtatje të mirë në përpjekjen e parë, mund të provoni ta ekzekutoni përsëri. Nëse përshtatja është përmirësuar, provoni ta zgjidhni nga vlerat aktuale. Përndryshe, mund të provoni të përmirësoni manualisht përshtatjen përpara se ta zgjidhni.

   

9. Pasi të arrihet një përshtatje e mirë, mund të dilni nga zgjidhësi.

Zgjidhja e një modeli në mënyrë të përsëritur

Ndonjëherë ekziston një ekuacion relativisht i thjeshtë që jep një dalje në terma të disa hyrjeve. Megjithatë, kur përpiqemi të përmbysim problemin nuk është e mundur të gjejmë një zgjidhje të thjeshtë. Për shembull, fuqia e konsumuar nga një automjet jepet përafërsisht me P=av + bv^3 ku v është shpejtësia, a është një koeficient për rezistencën e rrotullimit dhe b është një koeficient për zvarritje aerodinamike. Megjithëse ky është një ekuacion mjaft i thjeshtë, nuk është e lehtë të riorganizohet për të dhënë një ekuacion të shpejtësisë që automjeti do të arrijë për një fuqi të dhënë të dhënë. Megjithatë, ne mund të përdorim Zgjidhësin për të gjetur në mënyrë të përsëritur këtë shpejtësi. Për shembull, gjeni shpejtësinë e arritur me një fuqi hyrëse prej 740 W.

1. Krijoni një fletëllogaritëse të thjeshtë me shpejtësinë, koeficientët a dhe b dhe fuqinë e llogaritur prej tyre.

   

2. Nisni Zgjidhjen dhe vendosni fuqinë, B5, si objektiv. Vendosni një vlerë objektive prej 740 dhe zgjidhni shpejtësinë, B2, si qelizat e ndryshueshme për të ndryshuar. Zgjidh zgjidh për të filluar zgjidhjen.

   

3. Zgjitësi rregullon vlerën e shpejtësisë derisa fuqia të jetë shumë afër 740, duke siguruar shpejtësinë që kërkojmë.

   

4. Zgjidhja e modeleve në këtë mënyrë shpesh mund të jetë më e shpejtë dhe më pak e prirur ndaj gabimeve sesa përmbysja e modeleve komplekse.

Të kuptuarit e opsioneve të ndryshme të disponueshme në zgjidhës mund të jetë mjaft e vështirë. Nëse keni vështirësi për të marrë një zgjidhje të arsyeshme, atëherë shpesh është e dobishme të aplikoni kushte kufitare për qelizat e ndryshueshme. Këto janë vlera kufizuese përtej të cilave ato nuk duhet të rregullohen. Për shembull, në shembullin e mëparshëm, shpejtësia nuk duhet të jetë më e vogël se zero dhe gjithashtu do të ishte e mundur të vendosni një kufi të sipërm. Kjo do të ishte një shpejtësi për të cilën jeni shumë i sigurt se automjeti nuk mund të ecë më shpejt se. Nëse jeni në gjendje të vendosni kufij për qelizat e ndryshoreve të ndryshueshme, atëherë kjo bën që opsionet e tjera më të avancuara të funksionojnë më mirë, si p.sh. multistart. Kjo do të ekzekutojë një numër zgjidhjesh të ndryshme, duke filluar nga vlera të ndryshme fillestare për variablat.

Zgjedhja e metodës së zgjidhjes mund të jetë gjithashtu e vështirë. Simplex LP është i përshtatshëm vetëm për modelet lineare, nëse problemi nuk është linear ai do të dështojë me një mesazh se ky kusht nuk është përmbushur. Dy metodat e tjera janë të përshtatshme për metodat jolineare. GRG Jolinear është më i shpejti, por zgjidhja e tij mund të varet shumë nga kushtet fillestare të fillimit. Ajo ka fleksibilitetin që nuk kërkon që variablat të kenë kufij të caktuar. Zgjidhësi evolucionar është shpesh më i besueshmi, por kërkon që të gjitha variablat të kenë kufijtë e sipërm dhe të poshtëm, gjë që mund të jetë e vështirë të përpunohet paraprakisht.

Shtesa e Excel Solver është një mjet shumë i fuqishëm që mund të zbatohet për shumë probleme praktike. Për të aksesuar plotësisht fuqinë e Excel-it, provo të kombinosh Solver me makro Excel.