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