›
›

Ứng dụng phần mềm microsoft excel vào giải bài toán quy hoạch tuyến tính

Download
Ứ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  
40  
Cúc  
(50)  
Hồng  
(70)  
Lan  
(30)  
Huệ  
(30)  
Đất  
10  
6
-
9
8
9
12  
-
60  
80  
12  
10  
15  
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 xij 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  
x11 + x21 + x31  
x22 + x32  
x13 + x23 + x33  
x14 + x24  
= 80  
= 50  
= 70  
= 30  
= 30  
xij  
0, i, j  
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ừ x11 đế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
1
1 =A1+A3+A4  
40 =10*A1+8*A3+9*A4+6*A5+9*A6+12*A7+12*A8  
+15*A9+10*A10+10*A11  
2
3
0 =A5+A6+A7+A8  
60  
80  
50  
70  
30  
30  
1 =A9+A10+A11  
4
1 =A1+A5+A9  
5
1 =A6+A10  
6
1 =A3+A7+A11  
7
1 =A4+A8  
8
1
1
1
1
9
10  
11  
Bảng 2 (Kết quả sau khi nhập dữ liệu)  
A
1
0
1
1
1
B
C
D
1
2
3
4
5
3
4
3
3
2
40  
101  
60  
80  
50  
70  
6
7
1
1
1
1
1
1
3
2
30  
30  
8
9
10  
11  
- Dùng Solver để giải bài toán  
+ Từ Menu Tool chọn Solver2, xuất hiện hộp hội thoại Solver Parameters:  
Set Target Cell: $D$1  
Equal To: Max  
Chọn địa chỉ hàm mục tiêu.  
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 =(x11, x12, x13,....,x33,…)  
Cần cho các biến 1 giá trị khởi động nào đó  
Chẳng hạn x11 = 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  
Chọn dấu >=  
Hộp bên tay trái  
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  
Chọn dấu =  
Hộp bên tay trái  
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
1
2
3
4
5
10  
0
40  
60  
80  
50  
70  
40  
60  
80  
50  
70  
2000  
0
30  
0
6
7
30  
30  
0
30  
30  
30  
30  
8
9
40  
40  
0
10  
11  
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  
Cúc  
(50)  
Hồng  
(70)  
Lan  
(30)  
Huệ  
(30)  
Đất  
40  
10  
0
0
0
30  
0
30  
0
60  
80  
30  
40  
40  
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.  
doc 5 trang baolam 28/04/2022 7440
Download
Bạn đang xem tài liệu "Ứng dụng phần mềm microsoft excel vào giải bài toán quy hoạch tuyến tính", để tải tài liệu gốc về máy hãy click vào nút Download ở trên

File đính kèm:

  • docung_dung_phan_mem_microsoft_excel_vao_giai_bai_toan_quy_hoac.doc