ỨNG DỤNG PHẦN MỀM MICROSOFT EXCEL VÀO GIẢI BÀI TOÁN QUY HOẠCH TUYẾN TÍNH

Chia sẻ: voquoctin170693_

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....

Nội dung Text: ỨNG DỤNG PHẦN MỀM MICROSOFT EXCEL VÀO GIẢI BÀI TOÁN QUY HOẠCH TUYẾN TÍNH

Ứ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):




Hồng Huệ
Hoa Cúc Lan
Đất (50) (70) (30) (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

Nên đưa bài toán về dạng chính tắc trước khi thực hiện bước 2
1
- 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 = 80
x11 + x21 + x31 = 50
x22 + x32 = 70
x13 + x23 + x33 = 30
x14 + x24 = 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 ừ 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
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 0 =A5+A6+A7+A8 60
3 1 =A9+A10+A11 80
4 1 =A1+A5+A9 50
5 1 =A6+A10 70
6 1 =A3+A7+A11 30
7 1 =A4+A8 30
8 1
9 1
10 1
11 1

Bảng 2 (Kết quả sau khi nhập dữ liệu)
A B C D
1 1 3 40 101
2 0 4 60
3 1 3 80
4 1 3 50
5 1 2 70
6 1 3 30
7 1 2 30
8 1
9 1
10 1
11 1

- 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:




Chọn địa chỉ hàm mục tiêu.
Set Target Cell: $D$1
Chọn mục tiêu tối ưu (Max hoặc Min).
Equal To: Max
Chứa các biến cần tìm x =(x11, x12, x13,....,x33,…)
By Changing Cells: $A$1:$A$11
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
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:




Hộp bên tay trái
Cell Reference: $A$1:$A$11
Chọn dấu >= Hộp ở giữa
Hộp bên tay phải
Constraint: 0
+ Nhấn nút Add để chọn thêm các ràng buộc, hộp hội thoại Add Constraints:


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,
2

chọn mục Solver Add - Ins.
Hộp bên tay trái
Cell Reference: $B$1:$B$7
Chọn dấu = Hộp ở giữa
Hộp bên tay phải
Constraint: $C$1:$C$7
+ 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 10 40 40 2000
2 0 60 60
3 0 80 80
4 30 50 50
5 0 70 70
6 30 30 30
7 30 30 30
8 0
9 40
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
Hồng Huệ
Hoa Cúc Lan
Đất (50) (70) (30) (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.
Đề thi vào lớp 10 môn Toán |  Đáp án đề thi tốt nghiệp |  Đề thi Đại học |  Đề thi thử đại học môn Hóa |  Mẫu đơn xin việc |  Bài tiểu luận mẫu |  Ôn thi cao học 2014 |  Nghiên cứu khoa học |  Lập kế hoạch kinh doanh |  Bảng cân đối kế toán |  Đề thi chứng chỉ Tin học |  Tư tưởng Hồ Chí Minh |  Đề thi chứng chỉ Tiếng anh
Theo dõi chúng tôi
Đồng bộ tài khoản