Sử dụng Solver trong Excel 97-2010

S d ng Solver trong Excel 97-2010

ử ụ

1. Add-Ins Solver

ầ ướ ả ử ụ ạ ử ụ ự ự ệ ả ố nhau. Các hình minh h a trong bài này s ng t ươ ọ c khi s d ng, chúng ta c n ph i thêm ti n ích này vào Excel. Tùy theo phiên b n Tr ả ệ Excel mà b n đang s d ng thì cách làm khác nhau đôi chút. Tuy nhiên, cách th c hi n ệ Add-In Solver cho các phiên b n Excel 97-2003 là gi ng nhau và cách th c hi n Add-In ẽ Solver trong Excel 2007-2010 cũng t ự s d ng phiên b n Excel 2003 và Excel 2007. ả ử ụ

1.1. Add-In Solver cho Excel 2003

Các b c th c hi n Add-Ins Solver cho Excel 2003 nh sau: ướ ự ư ệ

B c 1. Vào Tools | ch n Add-Ins. H p tho i Add-Ins xu t hi n. ướ ệ ạ ấ ọ ộ

Hình 1. Ch n l nh Add-Ins ọ ệ

B c 2. Trong h p tho i Solver, tích vào ô vuông Solver Add-In ướ ạ ộ

Hình 2. H p tho i Add-Ins ch a các ch c năng m r ng c a Excel ở ộ ứ ứ ủ ạ ộ

B c 3. Nh n nút OK đóng h p Add-Ins ướ ấ ộ

B c 4. Khi đó trong th c đ n Tools s xu t hi n thêm l nh Solver ự ơ ướ ẽ ệ ệ ấ

Hình 3. L nh Solver trong th c đ n Tools ự ơ ệ

1.2. Add-In Solver cho Excel 2007

ệ ề ổ ướ c Do giao di n Excel 2007 thay đ i nhi u so v i các phiên b n Excel cũ nên các b th c hi n Add-Ins s khác đôi chút. Các b ẽ ớ c th c hi n nh sau: ệ ướ ả ư ự ự ệ

B c 1. Nh p chu t vào nút Office | ch n Excel Options ướ ấ ộ ọ

Hình 4. Vào Excel Options trong Excel 2007 & Excel 2010

ướ ừ ộ c li B c 2. Trong h p tho i Excel Options, ch n Add-Ins t ạ các Add-Ins trong Excel đ ượ danh sách bên trái, danh sách t kê trong h p Add-Ins v i các phân nhóm khác nhau. ớ ọ ộ ệ

Hình 5. H p tho i Excel Options ạ ộ

B c 3. T i Manage, ch n Excel Add-Ins t ướ ạ ọ ừ ể ở ộ danh sách và nh n nút Go... đ m h p ấ

tho i Add-Ins. ạ

Hình 6. Solver Add-in

B c 4. Ch n Solver Add-in t danh sách Add-Ins avaiable và nh n nút OK. ướ ọ ừ ấ

B c 5. Trong ngăn Data xu t hi n thêm nhóm Analysis ch a l nh Solver. ứ ệ ướ ệ ấ

2. S d ng Solver ử ụ

2.1. Excel 97-2007

t Solver ta c n n m v ng các yêu c u thông s c n ph i khai báo trong ầ ố ầ ữ ả ắ Đ s d ng t ể ử ụ ầ ố h p tho i Solver Parameters: ạ ộ

Hình 1. H p tho i Solver Excel 97-2007 ạ ộ

ỉ ủ ụ ị i ơ ụ Set Target Cell: N i đây ta c n nh p vào đ a ch c a hàm m c tiêu. ằ ộ tr mong mu n nào đó thì nh p giá tr vào.) ầ ố ậ ố ị i.

• ậ • Equal To: Hàm m c tiêu mu n đ t t ạ ớ Max, Min hay Value of (b ng m t giá ị ỉ ứ

ậ ầ ả

• By Changing Cell: Nh p vào đ a ch ch a các bi n c a bài toán c n gi •

ị Subject to the constraints: Nh p vào các ràng bu c c a bài toán. ậ ế ủ ộ ủ

ổ ủ ị ủ ế ạ By Changing Cell đ n lúc nào i ị ộ ậ ả ế i ạ Equal To ị ộ ạ Subject to i

t l p các tùy ch n cho h p tho i Solver Parameters ta nh p chu t vào nút Options, ấ ọ ộ Cách làm c a Solver là thay đ i giá tr c a các bi n t i đó làm cho giá tr hàm m c tiêu t ạ Set Target Cellđ t m t giá tr qui đ nh t ạ ụ ị (Max, Min ho c Value of) và đ ng th i ph i thõa mãn t p các ràng bu c t ờ ồ ặ the constraints. Thi ế ậ h p tho i ộ ạ ạ Solver Options xu t hi n: ộ ấ ệ

Hình 2. Thi t l p thông s cho Solver: Ch đ m c đ nh ế ậ ế ộ ặ ị ố

2.2. Excel 2010

c thi t k l ế ế ạ ề ọ ủ ệ ộ i v giao di n và tên g i c a các h p khai i các thành ph n trên h p tho i. Solver trong Excel ạ i m i Evolutionary Solver d a trên ổ ộ ớ ươ ế ả ng pháp này cho phép gi ả ậ ấ ự ế i quy t ề c ượ ử ụ ạ i quy t các bài toán tuy n tính và phi tuy n và b sung thêm 2 lo i ế ế ế ệ ả ổ Solver trong Excel 2010 đã đ ượ báo thông s cũng nh s p x p l ế ạ ư ắ ố ầ ng pháp tìm ki m l 2010 đã b sung thêm ph i gi ờ các thu t toán di truy n (genetic algorithms). Ph ươ nh ng bài toán có s d ng b t kỳ hàm nào trong Excel. Solver trong Excel 2010 đ ữ i u cho vi c gi t ố ư báo cáo k t qu Linearity và Feasibility. ế ả

Hình 10. H p tho i Solver Parameters c a Excel 2010 ủ ạ ộ

ỉ ủ ụ ầ ị

• Set Object: N i đây ta c n nh p vào đ a ch c a hàm m c tiêu. ậ ơ • To: Hàm m c tiêu mu n đ t t i ụ

ằ ộ ị ố mu n nào đó thì nh p giá tr vào h p bên c nh) ạ ớ Max, Min hay Value of (b ng m t giá tr mong ị ạ ậ ộ ố

• By Changing Variable Cells: Nh p vào đ a ch ch a các bi n thay đ i c a bài

ổ ủ ỉ ứ ế ậ ị i. ả

•

toán c n gi ầ Subject to the constraints: Nh p vào các ràng bu c c a bài toán. ộ ủ ậ

Hình 11. H p tho i Solver Results ạ ộ

Báo cáo Linearity

ả ệ i trong các bài toán phi tuy n. Solver s ch ra các đi u ki n ẽ ỉ ế ề Khi không tìm đ i gi ràng bu c và các bi n không thõa trong bài toán. ộ c l ượ ờ ế

Hình 12. Báo cáo Linearity

Báo cáo Feasibility

i gi ể ả ả ị ủ ấ ẽ ề ị i kh thi cho bài toán. Solver hi n th thông báo không tìm i kh thi và báo cáo s giúp ta xác đ nh nguyên nhân c a v n đ không tìm i. i gi i gi N u không tìm đ c l ượ ờ ế c l đ ượ ờ ả c l đ ượ ờ ả ả

Hình 13. Báo cáo Feasibility

ỉ ạ ể ằ ố ệ ạ ộ ộ i bài toán b ng cách nh n vào nút ả ượ ổ c t ạ ng ng v i các ớ ấ ươ ứ B n có th tinh ch nh các thông s cho quá trình gi ấ Options trong h p tho i Solver Parameters. H p tho i Options xu t hi n và đ ch c thành 3 ngăn All Methods, GRG Nonlinear và Evolutionary t ph ng pháp tìm ki m l i gi i. ứ ươ ế ả ờ

ạ ộ ng trình Hình 14. H p tho i Options 3. Tìm nghi m cho h ph ệ ệ ươ

Tìm nghi m cho h ph ng trình sau: ệ ệ ươ

Các b ướ c th c hi n: ệ ự

B c 1. L p mô hình bài toán trên b ng tính trong vùng A7:F10 theo d ng sau: ướ ậ ả ạ

Hình 15. L p mô hình bài toán trên b ng tính ả ậ

B c 2. Nh p các h s bên v trái c a các ph ng trình: ướ ệ ố ủ ế ậ ươ

ệ ố ủ ệ ố ủ ậ ậ

• Trong vùng A8:C8 nh p các h s c a ph ươ • Trong vùng A9:C9 nh p các h s c a ph ươ • Trong vùng A10:C10 nh p các h s c a ph

ứ ứ ng trình th ba ệ ố ủ ậ ng trình th nh t ấ ng trình th hai ứ ươ

ng trình Hình 16. Nh p các h s bên trái c a các ph ệ ố ủ ậ ươ

ng trình vào các ô F8, F9 và ươ B c 3. ướ F10 t ng ng v i th t ị ở ế c a 3 ph ng trình. ươ Nh p các giá tr ớ ậ ứ v ph i c a các ph ả ủ ươ ứ ự ủ

ng trình Hình 17. Nh p các giá tr ậ ị ở ế v ph i c a các ph ả ủ ươ

Nh p các giá tr nghi m kh i t o cho các bi n x, y, z trong các ô D8, ướ ở ạ ệ ế ậ ị B c 4. D9 và D10 các giá tr tùy ý. Ví d b n nh p vào là 1 cho t t c các ô. ấ ả ụ ạ ậ ị

Hình 18. Nghi m kh i t o ở ạ ệ

ướ Tính toán k t qu cho v trái b ng vi c nhân các h s c a ph ằ ệ ố ủ ế ệ ả ươ ng B c 5. trình v i các nghi m x, y, z kh i t o. Có nhi u cách tính: ế ở ạ ệ ề ớ

Cách 1. Nhân c b n: ơ ả

ậ ạ ứ ố ứ ứ · T i ô E8 nh p vào công th c =A8*$D$8+B8*$D$9+C8*$D$10 ứ · Sao chép công th c xu ng cho các ô E9 và E10. Khi đó: o E9 có công th c là =A9*$D$8+B9*$D$9+C9*$D$10 o E10 có công th c là =A10*$D$8+B10*$D$9+C10*$D$10 Cách 2. S d ng hàm Sumproduct k t h p hàm Transpose đ tính v trái: ế ợ ử ụ ể ế

ạ ứ · T i E8 nh p vào công th c =Sumproduct(A8:C8,Transpose($D$8:$D$10)) ậ ô E8 cho các ô E9 và E10 · Sao chép công th c t ứ ừ

Hình 19. Tính v trái ế

Vào Data | nhóm Analysis | ch n l nh Solver, h p tho i Solver ướ ọ ệ ộ B c 6. ạ Parameters xu t hi n. Chúng ta ti n hành khai báo các thông s : ố ệ ế ấ

• T i By Changing Cells nh p vào đ a ch c a các nghi m kh i t o D8:D10 ị

ở ạ ỉ ủ ệ ạ ậ

Hình 20. Khai báo By Changing Cells

• T i h p Subject to the Constraints, nh p nút Add đ thêm ràng bu c vào

ể ộ ấ h p tho i Add Contraint nh hình sau: ạ ộ ộ ư ạ

Hình 21. Thêm ràng bu cộ

• Nh p nút OK sau khi khai báo xong ràng bu c, h p tho i Solver

ấ ạ ộ ộ Parameters nh sau: ư

Hình 22. Khai báo thông s cho Solver ố

ng trình. Khi Solver ể ắ ầ ệ ấ Nh p vào nút Solve đ b t đ u tìm nghi m h ph c nghi m thì h p tho i Solver Results xu t hi n nh hình sau: B c 7. ướ tìm đ ượ ươ ư ệ ấ ệ ệ ạ ộ

Hình 23. H p tho i Solver Results ạ ộ

B c 8. ướ Ch n ki u báo cáo: ể ọ

c trên b ng tính. ả ể ư ượ ả c và

• Ch n ọ Keep Solver Solution đ l u k t qu tìm đ • Ch n ọ Restore Original Values đ h y k t qu Solver v a tìm đ

ế ể ủ ượ ừ ế ả tr các bi n v tình tr ng ban đ u. ả ế ề c thành m t tình ạ • Ch n ọ Save Scenario… đ l u k t qu v a tìm đ ầ ế ể ư ượ ộ i sau này. Ngoài ra b n còn có th ch n 3 lo i báo ố ạ ể ạ ọ ả ừ ạ hu ng đ có xem l ể cáo b sung là Answer, Sensitivity và Limits. ổ

t quá trình ch y Solver. B c 9. ướ Ch n ọ OK đ hoàn t ể ấ ạ

Hình 10. Các nghi m c a h ph ng trình ủ ệ ệ ươ

ệ ệ ả ằ ươ ộ i b ng Solver trong bài trên có đ chính xác ạ i ệ ầ ộ ộ ỉ Nghi m c a h ph ủ m t ph n tri u, chúng ta có th tăng đ chính xác lênh b ng cách ch nh l ằ Precision trong Options c a h p tho i Solver Parameters. ạ ng trình gi ể ộ ủ