NG D NG PH N M M MICROSOFT EXCEL VÀO GI IỨ Ụ Ầ Ề Ả
BÀI TOÁN QUY HO CH TUY N TÍNHẠ Ế
Th.s. Tr n Th Hòaầ ị
Khoa K toán – Tài chínhế
M c tiêu c a vi c gi i bài toán quy ho ch tuy n tính (QHTT) là tìm đ c ph ng án t iụ ủ ệ ả ạ ế ượ ươ ố
u và v n d ng ph ng án đó vào th c ti n. Tuy nhiên, trong th c t công vi c này l i kháư ậ ụ ươ ự ễ ự ế ệ ạ
ph c t p, gây không ít khó khăn và lúng túng cho nh ng đ i t ng quan tâm đ n nó. T iứ ạ ữ ố ượ ế ạ
sao chúng ta không“trút gánh n ng” này cho Excel nh ? Bài vi t này nh m gi i thi u cáchặ ỉ ế ằ ớ ệ
s d ng ph n m m ng d ng Microsoft Excel đ gi i bài toán QHTT và rút ra các ý nghĩaử ụ ầ ề ứ ụ ể ả
kinh t c a chúng. ế ủ
Đ gi i bài toán QHTT d a trên ph n m m ng d ng Microsoft Excel c n th c hi nể ả ự ầ ề ứ ụ ầ ự ệ
theo trình t sau:ự
1. Các b c th c hi nướ ự ệ
B c 1:ướ L p mô hình bài toán (n u bài toán ch a l p mô hình)ậ ế ư ậ 1;
B c 2:ướ V n d ng ph n m m ng d ng Microsoft Excel đ gi i bài toán QHTT d aậ ụ ầ ề ứ ụ ể ả ự
trên mô hình b c 1;ở ướ
B c 3:ướ K t lu n ph ng án t i u và giá tr c a hàm m c tiêu (n u có).ế ậ ươ ố ư ị ủ ụ ế
T t nhiên, đ th c hi n đ c b c 2 b n c n ph i có m t máy tính có cài đ t s n ph nấ ể ự ệ ượ ướ ạ ầ ả ộ ặ ẳ ầ
m m này.ề
2. ng d ng Microsoft Excel đ gi i bài toán QHTT Ứ ụ ể ả
Đ hi u rõ vi c gi i bài toán QHTT d a trên ph n m m ng d ng Excel, chúng ta hãyể ể ệ ả ự ầ ề ứ ụ
cùng nhau xem xét ví d sau:ụ
Công ty “Hoa Đà L t” c n tr ng 4 lo i hoa Cúc, H ng, Lan, Hu trên 3 m nh v nạ ầ ồ ạ ồ ệ ả ườ
khác nhau. Bi t r ng di n tích đ t hi n có ng v i m i m nh v n là 40 ha, 60 ha, 80 ha.ế ằ ệ ấ ệ ứ ớ ỗ ả ườ
Di n tích đ t ph i tr ng m i lo i hoa theo k ho ch là: Cúc: 50 ha, H ng: 70 ha, Lan: 30ệ ấ ả ồ ỗ ạ ế ạ ồ
ha, Hu : 30 ha. Ngoài ra, do tính ch t c a các lo i đ t tr ng khác nhau, nên hoa H ngệ ấ ủ ạ ấ ồ ồ
không th tr ng đ c trên m nh đ t th nh t, và hoa Hu không th tr ng đ c trênể ồ ượ ả ấ ứ ấ ệ ể ồ ượ
m nh đ t th ba. Bi t thu ho ch (L i nhu n) c tính c a t ng lo i hoa trên t ng lo i đ tả ấ ứ ế ạ ợ ậ ướ ủ ừ ạ ừ ạ ấ
tr ng nh sau (trăm ngàn đ ng/ha): ồ ư ồ
Hoa
Đ tấ
Cúc
(50)
H ngồ
(70)
Lan
(30)
Huệ
(30)
40 10 - 8 9
60 6 9 12 12
80 15 10 10 -
Yêu c u:ầ L p k ho ch tr ng hoa sao cho công ty thu đ c l i nhu n nhi u nh t.ậ ế ạ ồ ượ ợ ậ ề ấ
Gi i:ả
B c 1:ướ L p mô hình bài toánậ
- T ng di n tích đ t = 40 + 60 + 80 =180 = 50 + 70 + 30 + 30 = T ng di n tích tr ng hoaổ ệ ấ ổ ệ ồ
1 Nên đ a bài toán v d ng chính t c tr c khi th c hi n b c 2ư ề ạ ắ ướ ự ệ ướ
- G i xọij là s ha m nh v n i tr ng lo i hoa j, v i i= 1, 2, 3 và j = 1, 2, 3, 4 t ng ng làố ả ườ ồ ạ ớ ươ ứ
Cúc, H ng, Lan, Hu .ồ ệ
f = 10 x11 + 8x13 + 9x14 + 6x21 + 9x22 + 12x23 + 12x24 + 15x31 + 10x32 + 10x33 -> Max
x11 + x13 + x14 = 40
x21 + x22 + x23 + x24 = 60
x31 + x32 + x33 = 80
x11 + x21 + x31 = 50
x22 + x32 = 70
x13 + x23 + x33 = 30
x14 + x24 = 30
xij
≥
0,
ji,∀
B c 2:ướ ng d ng Excel đ gi i bài toán QHTT d a trên mô hình b c 1.Ứ ụ ể ả ự ở ướ
- Kh i đ ng Exelở ộ
- Nh p d li u vào b ng tính:ậ ữ ệ ả
+ C t A là giá tr có th có c a các bi n. Trong ví d này ta có 10 bi n c n tìm t xộ ị ể ủ ế ụ ế ầ ừ 11 đ nế
x33
+ C t B là công th c tính bi u th c v trái c a các ràng bu cộ ứ ể ứ ế ủ ộ
+ C t C là giá tr v ph i c a các ràng bu cộ ị ế ả ủ ộ
+ C t D là công th c tính hàm m c tiêuộ ứ ụ
Ban đ u ta cho giá tr tuỳ ch n vào c t. Trong ví d này, các s li u nh b ng 1 ầ ị ọ ộ ụ ố ệ ư ở ả
B ng 1ả
A B C D
11 =A1+A3+A4 40 =10*A1+8*A3+9*A4+6*A5+9*A6+12*A7+12*A8
+15*A9+10*A10+10*A11
20 =A5+A6+A7+A8 60
31 =A9+A10+A11 80
41 =A1+A5+A9 50
51 =A6+A10 70
61 =A3+A7+A11 30
71 =A4+A8 30
81
91
10 1
11 1
B ng 2ả (K t qu sau khi nh p d li u)ế ả ậ ữ ệ
A B C D
11 3 40 101
20 4 60
31 3 80
41 3 50
51 2 70
61 3 30
71 2 30
81
91
10 1
11 1
- Dùng Solver đ gi i bài toánể ả
+ T Menu Tool ch n Solverừ ọ 2, xu t hi n h p h i tho i ấ ệ ộ ộ ạ Solver Parameters:
Set Target Cell: $D$1 Ch n đ a ch hàm m c tiêu.ọ ị ỉ ụ
Equal To: Max Ch n m c tiêu t i u (Max ho c Min).ọ ụ ố ư ặ
By Changing Cells: $A$1:$A$11 Ch a các bi n c n tìm x =(xứ ế ầ 11, x12, x13,....,x33,…)
C n cho các bi n 1 giá tr kh i đ ng nào đóầ ế ị ở ộ
Ch ng h n xẳ ạ 11 = x13 =....= x33 =1; x12 = 0
Subject to the Constraints: Ch a các ràng bu c, nh n nút ứ ộ ấ Add đ ch n. ể ọ
+ H p h i tho i ộ ộ ạ Add Constraints:
Cell Reference: $A$1:$A$11 H p bên tay tráiộ
Ch n d u >=ọ ấ H p gi aộ ở ữ
Constraint: 0 H p bên tay ph iộ ả
+ Nh n nút ấAdd đ ch n thêm các ràng bu c, h p h i tho i ể ọ ộ ộ ộ ạ Add Constraints:
2 N u trong menu Tool không có Solver thì vào menu Tool, ch n Add - Ins, xu t hi n h p h i tho i Add - Ins,ế ọ ấ ệ ộ ộ ạ
ch n m c Solver Add - Ins.ọ ụ
Cell Reference: $B$1:$B$7 H p bên tay tráiộ
Ch n d u =ọ ấ H p gi aộ ở ữ
Constraint: $C$1:$C$7 H p bên tay ph iộ ả
+ Nh n ấOK, tr l i h p h i tho i ở ạ ộ ộ ạ Solver Parameters:
+ Nh n nút ấSolver, xu t hi n h p h i tho i ấ ệ ộ ộ ạ Solver Results:
+ Ch nọ Keep Solver Solution, nh nấ OK. Khi đó k t qu bài toán QHTT s đ c hi n thế ả ẽ ượ ể ị
nh sau:ư
A B C D
110 40 40 2000
20 60 60
30 80 80
430 50 50
50 70 70
630 30 30
730 30 30
80
940
10 40
11 0
B c 3:ướ K t lu n ế ậ
Bài toán có ph ng án t i u ươ ố ư x = (10, 0, 0, 30, 0, 30, 30, 0, 40, 40, 0, 0) và
fmax = 2000 trăm ngàn đ ng = 200 tri u đ ng.ồ ệ ồ
V y k ho ch tr ng các lo i hoa trên t ng lo i đ t đ c phân b nh sau: ậ ế ạ ồ ạ ừ ạ ấ ượ ổ ư
Đ n v tính: haơ ị
Hoa
Đ tấ
Cúc
(50)
H ngồ
(70)
Lan
(30)
Huệ
(30)
40 10 0 0 30
60 0 30 30 0
80 40 40 0 0
V i k ho ch tr ng hoa nh trên thì công ty “Hoa Đà L t” thu đ c l i nhu n nhi uớ ế ạ ồ ư ạ ượ ợ ậ ề
nh t, giá tr l i nhu n đ t đ n 200 tri u đ ng.ấ ị ợ ậ ạ ế ệ ồ
Nh v y, v n d ng ph m m m Excel đ gi i bài toán QHTT không nh ng làm cho bàiư ậ ậ ụ ầ ề ể ả ữ
toán QHTT tr nên đ n gi n h n r t nhi u mà còn mang ý nghĩa kinh t sâu s c, bi n cácở ơ ả ơ ấ ề ế ắ ế
con s “khô khan” trong mô hình toán h c đi vào th c ti n cu c s ng.ố ọ ự ễ ộ ố