Ứng dụng hàm Solver của Microsoft Excel giải các bài toán kinh tế
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (570.84 KB, 13 trang )
56
ỨNG DỤNG HÀM SOLVER CỦA MICROSOFT EXCEL
GIẢI CÁC BÀI TOÁN KINH TẾ
Hà Trọng Quang
*
TÓM TẮT
Hàm Solver là một trong những nội hàm của Microsoft Excel, cho phép tìm cực trị hoặc giá trị hàm số
một biến hay nhiều biến với những điều kiện ràng buộc nhất định. Trong ngành quản trị kinh doanh cũng
như khối ngành kinh tế bài toán tìm cực trị của hàm nhiều biến, bài toán tối ưu hóa hoặc giải hệ phương
trình phi tuyến khá phổ biến. Việc giải các bài toán này phức tạp và tốn nhiều thời gian. Bài báo giớ
i thiệu
một số mô hình bài toán và thuật toán giải với ứng dụng của hàm Solver. Từ các thuật toán này, có thể mở
rộng cho nhiều bài toán ở các chuyên ngành khác. Giới thiệu căn bản về hàm Solver, một số mô hình bài
toán, thuật toán ứng dụng hàm Solver sẽ được trình bày trong bài báo này.
THE IMPLICATION OF SOLVER FUNCTION IN SOLVING
BUSSINESS PROBLEMS
SUMMARY
Solver function is one of the add-in functions of Microsoft Excel, making it possible to determine
extremes of single-variant or multi-variant functions with some constraints. There are many problems
associated with optimization of multi-variant functions, non-linear equation system etc., In Business
Administration as well as economic bloc. To solve these problems is quite complicated and takes much
time. This paper introduces some models for solving economic problems and disciplines together with the
application of Solver function. Essential information on Solver and illustration of using Solver to solve
economic problems are demonstrated.
1. ĐẶT VẤN ĐỀ
Solver là một trong những nội hàm của
Microft Excel, được xây dựng và đưa vào sử
dụng từ phiên bản Microsoft Excel 97. Với
Solver, người dùng có thể giải các bài toán sau
đây thông qua bảng tính Excel: giải các hệ
phương trình bất phương trình tuyến tính, các
phương trình đại số bậc cao, hàm mũ ; giải
các bài toán thống kê, giải các bài toán kinh
tế quy hoạch tuyến tính tối ưu, bài toán nguyên
vật liệu, bài toán vận tải. Nhờ đó, tính ứng dụng
của nội hàm này càng trở nên phổ biến và
tiện ích hơn. Tuy nhiên, những ứng dụng này
chưa thực sự thể hiện hết với thế mạnh và
tiềm năng của nội hàm Solver. Trên thực tế,
nội hàm Solver chỉ hỗ trợ những nội dung tính
toán căn bản giúp cho việc giải toán nhanh, đưa
ra nhiều phương án chính xác kịp thời. còn việc
ứng dụng Solver để gi
ải quyết các bài toán kinh
tế là hoàn toàn phụ thuộc vào khả năng xây
dựng các mô hình bài toán và thuật toán để giải
quyết các bài toán đó.
Giới thiệu và xây dựng các mô hình thuật
toán kinh tế ứng dụng hàm Solver để giải các
bài toán chuyên ngành kinh tế, quản trị kinh
doanh là cần thiết để hỗ trợ các nhà quản lý,
nhà nghiên cứu vận dụng vào trong công việc
tính toán và lựa chọn phương án tối ưu giải
quyết kịp thời các công việc mộ
t cách hiệu
quả. Trong bài báo này, tác giả mong muốn
giới thiệu hàm Solver và xây dựng một số thuật
toán mô hình giải các bài toán kinh tế nói trên.
*
Giảng viên. Khoa Quản trị kinh doanh, Trường Đại học Công nghiệp TP,HCM
KINH TẾ – XÃ HỘI
Tạp chí Đại học Công nghiệp
57
2. GIỚI THIỆU HÀM SOLVER
Hàm Solver có hai phiên bản chính: Solver
chuẩn (Standard Solver) và Solver hoàn thiện
(Premium Solver). Solver chuẩn có thể giải các
bài toán quy hoạch tuyến tính với quy mô 400
biến và 200 ràng buộc cộng với 800 ràng buộc
cận đặt trên biến. Solver hoàn thiện cho phép
toàn cục từng đoạn để dùng cho các bài toán tối
ưu hóa toàn cục.
Để khởi động Solver, vào Menu File \ Options \
Add – Ins rồi chọn Solver Add – Ins bấm nút
Go. Để khởi động vào Menu Data \ Solver. Sau
khi khởi động, hộp thoại “Các tham số của
Solver (Solver Parameters)” xuất hi
ện như
trong Hình 1.
Hình 1. Hộp thoại Solver
Hàm mục tiêu (Set Objective). Giá trị trong ô
của bảng tính Excel có địa chỉ tuyệt đối ghi trong
khung Set Objective được gọi là hàm mục tiêu.
Biến và tham số (By Changing Variable
Cells). Địa chỉ của các ô trong bảng tính Excel
ghi các giá trị ban đầu của biến. Giá trị các biến
này sẽ bị thay đổi để đạt được giá trị hàm mục
tiêu mong muốn.
Ràng buộc (Subject to the Constraints).
Trong quá trình biến đổi các biến số để đạt
được giá trị hàm mục tiêu mong muốn, các biến
hoặc các tham số của bài toán phải thoả mãn
những quan hệ ràng buộc nhất định nào đó. Các
ràng buộc này được mô tả trong khung Subject
to the Constraints. Việc thêm vào, thay đổi hay
loại bỏ bớt đi một ràng buộc được thực hiện
nhờ các chức năng Add, Change hay Delete.
Các lựa chọn trong hộp thoại “Solver
Options” được thể hiện trong Hình 2.
Hình 2: Hộp thoại Solver Options
Độ chính xác (Constraint Precision). Con số
nhập vào ô này xác định giá trị tính toán của vế
trái ràng buộc phải xấp xỉ phù hợp với vế phải
như thế nào để các ràng buộc được thoả mãn.
Độ chính xác không nên nhỏ quá và không nên
lớn quá. Thông thường nằm trong phạm vi
1.0E
-6
đến 1.0E
-4
.
Sử dụng tỷ lệ tự động (Use Automatic
Scaling). Khi khung này được đánh dấu, Solver
sẽ cố gắng định tỷ lệ giá trị hàm mục tiêu và
ràng buộc để giảm thiểu ảnh hưởng của mô hình
có các đại lượng với giá trị độ lớn khác biệt.
Ứng dụng hàm Solver…
58
Hiển thị kết quả bước tính lặp (Show
Iteration Results). Khi chức năng này được lựa
chọn, kết quả từng bước lặp sẽ được hiển thị
trong bản tính của Solver.
Thời gian tính lớn nhất (Max time). Giá trị
trong khung Max Time xác định thời gian lớn
nhất tính theo giây để Solver sẽ chạy trước khi
dừng. Thời gian này bao gồm thời gian sắp xếp
(Setup time) và thời gian tìm nghiệm tối ưu.
Đây là một trong những điều kiện dừng của
Solver. Giá trị mặc định là 100 giây, thời gian
tối đa có thể nhập vào 32.767 giây.
Số bước tính lặp (Interations). Giá trị trong
khung Interactions xác định số bước tính lặp
lớn nhất Solver có thể thực hiện trên một bài
toán. Mỗi bước tính lặp tính ra một nghiệm
mới. Đây cũng là một trong những điều kiện
dừng của Solver.
Sự hội tụ (Convergence). Chỉ áp dụng cho
các bài toán không tuyến tính (Nonlinear). Tại
đây nhập vào các số trong khoảng 0 và 1. Giá
trị càng gần 0 thì độ chính xác cao hơn và cần
nhiều thời gian hơn.
Ước lượng hàm mục tiêu và các ràng buộc
(Derivatives). Có hai lựa chọn: Sai phân tiến
(Forward), sai phân trung tâm (Central).
+ Forward: Được dùng rất phổ biến hơn,
khi đó các giá trị của ràng buộc biến đổi chậm.
+ Central: Dùng khi các giá trị của ràng
buộc biến đổi nhanh và được dùng khi Solver
báo không thể cải tiến kết quả thu được.
3. ỨNG DỤNG SOLVER GIẢI CÁC
BÀI TOÁN KINH TẾ
3.1 Bài toán lập mô hình kinh tế tối ưu
Mục tiêu của việc giải bài toán quy hoạch
tuyến tính 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ó. 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 quy hoạch tuyến
tính và rút ra các ý nghĩa kinh tế của chúng.
Để hiểu rõ việc phần mềm ứng dụng Excel
để giải các bài toán kinh tế, 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
Mai, Hồng, Lan, Đào 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à: mai: 50 ha, hồng: 70 ha, lan: 30 ha, đào: 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 đào 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 (ha)
Đất (ha)
Mai
(50)
Hồng
(70)
Lan
(30)
Đào
(30)
40 10 – 8 9
60 6 9 12 12
80 15 10 10 –
Hãy lập kế hoạch trồng hoa sao cho công ty thu
được lợi nhuận nhiều nhất.
Trong ví dụ này 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
+ 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à mai, hồng, lan, đào.
Hàm mục tiêu: f(x) = 10 x
11
+ 8x
13
+ 9x
14
+ 6x
21
+ 9x
22
+ 12x
23
+ 12x
24
+ 15x
31
+ 10x
32
+ 10x
33
→ Max
x
11
+ x
13
+ x
14
= 40
x
21
+ x
22
+ x
23
+ x
24
= 60
x
31
+ x
32
+ x
33
= 80
x
11
+ x
21
+ x
31
= 50
x
22
+ x
32
= 70
x
13
+ x
23
+ x
33
= 30
x
14
+ x
24
= 30
x
ij
≥ 0,
ji,
∀
Tạp chí Đại học Công nghiệp
59
Bước 2: Ứng dụng Excel để giải bài toán 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 x
33
+ 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ị tùy chọn vào cột. Trong
ví dụ này, các số liệu như ở bảng 1
A B C D E
1
Biến Nghiệm Vế trái Vế phải Phương án tối ưu
2
x
11
1 =B2+B4+B5 40 =10*B2+8*B4+9*B5+6*B6+9*B7+12*B8+12*B9+
15*B10+10*B11+10*B12
3 x
12
0 =B6+B7+B8+B9 60
4 x
13
1 =B10+B11+B12 80
5 x
14
1 =B2+B6+B10 50
6 x
21
1 =B7+B11 70
7 x
22
1 =B4+B8+B12 30
8 x
23
1 =B5+B9 30
9 x
24
1
10 x
31
1
11 x
32
1
12 x
33
1
Bảng 1: Nhập liệu các số liệu của bài toán trên Excel
Sau khi nhập liệu ta tiến hành tính các công thức cho các ô theo các ràng buộc của bài toán. Kết
quả được thể hiện trong bảng sau:
A B C D E
1
Biến Nghiệm Vế trái Vế phải Phương án tối ưu
2 x
11
1 3 40 101
3 x
12
0 4 60
4 x
13
1 3 80
5 x
14
1 3 50
6 x
21
1 2 70
7 x
22
1 3 30
8 x
23
1 2 30
9 x
24
1
10 x
31
1
11 x
32
1
12 x
33
1
Bảng 2: Kết quả sau khi nhập dữ liệu
Ứng dụng hàm Solver…
60
Bước 3: Dùng Solver để giải bài toán
+ Từ Menu Data chọn Solver
1)
, xuất hiện hộp hội thoại Solver Parameters:
• Set Objective: $E$2 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: $B$2:$B$12 Chứa các biến cần tìm x = (x
11
, x
12
,
x
13
, ,x
33
,…), Cần cho các biến 1 giá trị khởi động nào đó, Chẳng hạn x
11
= x
13
= =
x
33
=1; x
12
= 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: $B$2:$B$12 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:
• Cell Reference: $C$2:$B$8 Hộp bên tay trái
• Chọn dấu = Hộp ở giữa
• Constraint: $D$2:$D$8 Hộp bên tay phải
+ Nhấn OK, trở lại hộp hội thoại Solver Parameters:
1)
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. Bấm mút Go.
Tạp chí Đại học Công nghiệp
61
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 sẽ được hiển thị như sau:
A B C D E
1
Biến Nghiệm Vế trái Vế phải Phương án tối ưu
2 x
11
10 40 40 2000
3 x
12
0 60 60
4 x
13
0 80 80
5 x
14
30 50 50
6 x
21
0 70 70
7 x
22
30 30 30
8 x
23
30 30 30
9 x
24
0
10 x
31
40
11 x
32
40
12 x
33
0
Bước 4: 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à
f
max
= 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 (ha)
Đất (ha)
Mai
(50)
Hồng
(70)
Lan
(30)
Đào
(30)
40 10 0 0 30
60 0 30 30 0
80 40 40 0 0
Ứng dụng hàm Solver…
62
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 không những làm cho bài toán 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.
3.2 Bài toán nguyên vật liệu
Việc quản lý nguyên vật liệu để cung cấp
cho quá trình sản xuất tạo ra sản phẩm rất cần
thiết. Sử dụng nguồn nguyên vật liệu hợp lý,
kịp thời, đầy đủ không những làm cho quá trình
sản xuất được thuận lợi mà còn đem lại nguồn
lợi nhuận cao nhất cho doanh nghiệp.
Bài toán: Một nhà máy dự định tiến hành sản
xuất năm loại sản phẩm Sj (j = 1-> 5), cả 5 loại
sản phẩm đều sử dụng 4 loại nguyên vật liệu
chính NVL
i
(i = 1-> 4), có mức tiêu hao nguyên
vật liệu, lợi nhuận đơn vị thu được và giới hạn
dự trữ như sau:
S
1
S
2
S
3
S
4
S
5
Dự
trữ
NVL
1
2 5 6 8 4 1200
NVL
2
3 1 5 6 1 800
NVL
3
7 5 4 5 2 2000
NVL
4
8 5 7 9 1 1865
Lợi
nhuận
đơn vị
300 250 500 150 320
Hãy xây dựng phương án sản xuất để nhà máy
đạt được tổng lợi nhuận lớn nhất.
Trong bài toán này, bước 1 lập mô hình bài
toán như sau:
Gọi x
j
là sản lượng sản phẩm loại j sẽ sản xuất
(x
j
≥ 0)
Nên phương án sản xuất của nhà máy là vectơ x
= (x
1
, x
2
, x
3
, x
4
, x
5
)
Hàm mục tiêu: f(x) = 300x
1
+ 250x
2
+ 500x
3
+
150x
4
+ 320x
5
→ Max
Các ràng buộc:
⎪
⎪
⎩
⎪
⎪
⎨
⎧
≤++++
≤++++
≤++++
≤++++
18659758
200025457
800653
120048652
54321
54321
54321
54321
xxxxx
xxxxx
xxxxx
xxxxx
Bước 2: Bài toán được tổ chức trên bảng tính Excel như sau:
A B C D E F G H
1 S1 S2 S3 S4 S5
2 Lượng sản phẩm 0 0 0 0 0 Hàm mục tiêu
3 Lợi nhuận đơn vị 300 250 500 150 320 0
4 Các ràng buộc Vế trái Vế phải
5 RB1 2 5 6 8 4 0 1200
6 RB2 3 1 5 6 1 0 800
7 RB3 7 5 4 5 2 0 2000
8 RB4 8 5 7 9 1 0 1865
Tạp chí Đại học Công nghiệp
63
Tại G3 nhập công thức:
=SUMPRODUCT(B2:F2,B3:F3)
Tại G5 nhập công thức:
=SUMPRODUCT($B$2:$F$2,B5:F5)
Sau đó kéo xuống cho các ô còn lại (G6,G7,G8)
Bước 3: Giải bài toán bằng Solver trong Excel
+ Vào Menu Data \ Solver, điền đầy đủ
thông tin vào hộp thoại Solver
Parameters.
• Set Objective: $G$3
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: $B$2:$F$2
Nghiệm của phương trình
• Tại Subject to the Constraints nhấn
nút Add để khai báo các ràng buộc.
• Cell Reference: Chọn ô chứa các
công thức ràng buộc
• Dấu: Chọn dấu tương ứng
• Constraint: Giá trị các ràng buộc
• Để khai báo nhiều ràng buộc phải
nhấn nút Add cuối cùng nhấn OK.
+ Trong bài này khai báo năm ràng buộc.
9 Các nghiệm của phương trình ≥ 0
9 Các ràng buộc còn lại là hệ bất
phương trình
Cell Reference Dấu Constraint
$B$2:$F$2 >= 0
$G$5 <= $H$5
$G$6 <= $H$6
$G$7 <= $H$7
$G$8 <= $H$8
+ Cuối cùng nhấn nút Solver để chương trình tìm nghiệm, kết quả như sau:
A B C D E F G H
1 S1 S2 S3 S4 S5
2 Lượng sản phẩm 200 0 0 0 200 Hàm mục tiêu
3 Lợi nhuận đơn vị 300 250 500 150 320 124000
4 Các ràng buộc Vế trái Vế phải
5 RB1 2 5 6 8 4 1200 1200
6 RB2 3 1 5 6 1 800 800
7 RB3 7 5 4 5 2 1800 2000
8 RB4 8 5 7 9 1 1800 1865
Ứng dụng hàm Solver…
64
Vậy phương án tối ưu là x = (200, 0, 0, 0, 200)
f(x) max = 124000.
Phương án sản xuất tối ưu của nhà máy là
sản xuất 200 đơn vị sản phẩm 1 và 200 đơn vị
sản phẩm 5, khi đó lợi nhuận tối ưu đạt được là
124000 đơn vị tiền tệ, không có nguyên liệu
nào bị lãng phí.
3.3 Bài toán vận tải
Bài toán vận tải là một bài toán hay, được
ứng dụng nhiều trong quy trình vận chuyển
hàng hóa từ nơi sản xuất đến nơi tiêu thụ, lập
mô hình vận chuyển sao cho chi phí vận chuyển
là thấp nhất, đem lại lợi nhuận cao cho doanh
nghiệp, đó là vấn đề cần thiết và hữu dụng mà
doanh nghiệp cần làm.
Bài toán: Lập phương án vận chuyển xăng
tối ưu từ 4 kho đến 5 trạm xăng bán lẻ của
Công ty kinh doanh xăng dầu khu vực II. Số
liệu được cho như sau:
Đ
1
Đ
2
Đ
3
Đ
4
Đ
5
Dự trữ
K
1
30 27 26 9 23 4
K
2
13 4 22 3 1 6
K
3
3 1 5 4 24 10
K
4
16 30 17 10 16 10
Nhu cầu tiêu thụ
7 7 7 7 2
• K
i
: Kho hàng cùng chứa một loại hàng
hóa (i= 1 -> 4)
• Đ
j
: Địa điểm tiêu thụ (j = 1 -> 5)
• Cột G: Lượng hàng có ở mỗi kho
• Dòng 7: Nhu cầu tiêu thụ ở từng địa điểm
• Các số liệu từ B3: F6 là cước phí vận
chuyển một đơn vị hàng hóa từ kho i
đến địa điểm tiêu tụ j
Hãy lập kế hoạch vận chuyển hàng từ kho
đến các địa điểm tiêu thụ sao cho tổng chi phí
vận chuyển là nhỏ nhất.
Trong bài toán này bước 1: xây dựng mô
hình bài toán
Gọi x
ij
là lượng hàng vận chuyển từ kho i
đến điểm tiêu thụ j, nên x
ij
≥ 0, i = 1 -> 4,
j = 1 -> 5
Hàm mục tiêu: f(x) = 30x
11
+ 27x
12
+ 26x
13
+
9x
14
+ 23x
15
+ 13x
21
+ 4x
22
+ 22x
23
+ 3x
24
+
x
25
+ 3x
31
+ x
32
+ 5x
33
+ 4x
34
+ 24x
35
+ 16x
41
+ 30x
42
+ 17x
43
+ 10x
44
+ 16x
45
→ Min
Các ràng buộc
x
11
+ x
12
+ x
13
+ x
14
+ x
15
≤ 4
x
21
+ x
22
+ x
23
+ x
24
+ x
25
≤ 6
x
31
+ x
32
+ x
33
+ x
34
+ x
35
≤ 10
x
41
+ x
42
+ x
43
+ x
44
+ x
45
≤ 10
x
11
+ x
21
+ x
31
+ x
41
≤ 7
x
12
+ x
22
+ x
32
+ x
42
≤ 7
x
13
+ x
23
+ x
33
+ x
43
≤ 7
x
14
+ x
24
+ x
34
+ x
44
≤ 7
x
15
+ x
25
+ x
35
+ x
45
≤ 2
Bước 2: Tổ chức dữ liệu trên bảng tính Excel.
Tạp chí Đại học Công nghiệp
65
A B C D E F G
1
Đ
1
Đ
2
Đ
3
Đ
4
Đ
5
Dự trữ
2 K
1
30 27 26 9 23 4
3 K
2
13 4 22 3 1 6
4 K
3
3 1 5 4 24 10
5 K
4
16 30 17 10 16 10
6 Nhu cầu tiêu thụ 7 7 7 7 2
7 Lượng xăng vận chuyển 0 0 0 0 0
8 0 0 0 0 0
9 0 0 0 0 0
10 0 0 0 0 0
11 Hàm mục tiêu 0 B11 =SUMPRODUCT($B$7:$F$10,B2:F5)
12
Vế trái Vế phải
13 Các ràng buộc
14 RB1 0 4 B14 =SUM(B7:F7)
15 RB2 0 6 B15 =SUM(B8:F8)
16 RB3 0 10 B16 =SUM(B9:F9)
17 RB4 0 10 B17 =SUM(B10:F10)
18 Tổng
30
19 RB4 0 7 B19 =SUM(B7:B10)
20 RB5 0 7 B20 =SUM(C7:C10)
21 RB6 0 7 B21 =SUM(D7:D10)
22 RB7 0 7 B22 =SUM(E7:E10)
23 RB8 0 2 B23 =SUM(F7:F10)
24 Tổng
30
Bước 3: Giải bài toán
+ Chọn ô B13 rồi vào Menu Data \ Solver điền đầy đủ thông tin vào hộp thoại Solver
Parameters.
• Set Objective: $B$13 Chọn địa chỉ hàm mục tiêu.
• Equal To: Min Chọn mục tiêu tối ưu (Max hoặc Min).
• By Changing Cells: $B$8:$F$11 Nghiệm của phương trình
• Tại Subject to the Constraints nhấn nút Add để khai báo các ràng buộc.
• Cell Reference: Chọn ô chứa các công thức ràng buộc
• Dấu: Chọn dấu tương ứng
• Constraint: Giá trị các ràng buộc
• Để khai báo nhiều ràng buộc phải nhấn nút Add cuối cùng nhấn OK.
Trong bài này khai báo 3 ràng buộc.
Ứng dụng hàm Solver…
66
+ Các nghiệm của phương trình ≥ 0
+ Các ràng buộc còn lại là hệ bất phương trình
Cell Reference Dấu Constraint
$B$8:$F$11 >= 0
$B$16:$B$19 <= $C$16:$C$19
$B$21:$B$25 <= $C$21:$C$25
+ Cuối cùng nhấn nút Solver để chương trình tìm nghiệm, kết quả như sau:
A B C D E F G
1
Đ
1
Đ
2
Đ
3
Đ
4
Đ
5
Dự trữ
2 K
1
30 27 26 9 23 4
3 K
2
13 4 22 3 1 6
4 K
3
3 1 5 4 24 10
5 K
4
16 30 17 10 16 10
6 Nhu cầu tiêu thụ 7 7 7 7 2
7 Lượng xăng vận chuyển 0 0 0 4 0
8 0 4 0 0 2
9 7 3 0 0 0
10 0 0 7 3 0
11 Hàm mục tiêu 227
12
Vế trái Vế phải
13 Các ràng buộc
14 RB1 4 4
15 RB2 6 6
16 RB3 10 10
17 RB4 10 10
18 Tổng
30
19 RB4 7 7
20 RB5 7 7
21 RB6 7 7
22 RB7 7 7
23 RB8 2 2
24 Tổng
30
Tạp chí Đại học Công nghiệp
67
+ Phân tích kết quả:
+ Phương án vận chuyển là:
+ x = (0, 0, 0, 4, 0, 0, 4, 0, 0, 2, 7, 3, 0, 0,
0, 0, 0, 7, 3, 0)
+ f(x) = 227, chi phí thấp nhất.
+ Vậy tổng lượng xăng dự trữ ở các kho
bằng tổng nhu cầu xăng ở các trạm 30
nên phương án tìm được là tối ưu.
3.4. Ứng dụng Excel để giải hệ phương
trình tuyến tính
Ngoài ứng dụng để giải các bài toán trên
Solver còn có thể ứng dụng để giải các bài toán
về hệ phương trình. Khi đó chỉ có các ràng
buộc dạng phương trình và không có hàm mục
tiêu. Các bước tiến hành giải hệ phương trình
hoàn toàn như khi giải bài toán kinh tế trên. Để
hiểu xa hơn tiến hành xét ví dụ sau:
Giải hệ phương trình bằng Solver
Ví dụ: Giải hệ phương trình sau:
⎪
⎩
⎪
⎨
⎧
=++
=++
=++
3034
182
33322
zyx
zyx
zyx
Bước 1: Tổ chức dữ liệu bảng tính trên Excel
A B C D E F
1
ax by cz Nghiệm Vế trái Vế phải
2 2 2 3 1 7 33
3 2 1 1 1 4 18
4 1 4 3 1 8 30
Tại ô E2 nhập công thức:
=SUMPRODUCT(A2:C2,TRANSPOSE($D$2:
$D$4)) kết thức bằng Ctrl + Shift + Enter, sau
đó copy xuống cho 2 ô còn lại.
Bước 2: Giải bằng công cụ Solver trong Excel
+ Vào Menu Data \ Solver, điền đầy đủ
thông tin vào hộp thoại Solver
Parameters
• By Changing Cells: $D$2:$D$4
Nghiệm của phương trình
• Tại Subject to the Constraints nhấn
nút Add để khai báo các ràng buộc.
Cell Reference Dấu Constraint
$E$2:$E$4 = $F$2:$F$4
+ Cuối cùng nhấn nút Solver để chương
trình tìm nghiệm, kết quả như sau:
A B C D E F
1
ax by cz Nghiệm Vế trái Vế phải
2 2 2 3 5 33 33
3 2 1 1 1 18 18
4 1 4 3 7 30 30
Vậy nghiệm của hệ phương trình là: x = 5; y = 1; z = 7
4. KẾT LUẬN
Ứng dụng hàm Solver…
68
Các tác giả đã giới thiệu căn bản về Hàm
Solver và các tham số ứng của nó. Các thông
tin này là cầu nối người sử dụng với hàm Solver
đến bài toán kinh tế.
Các thuật toán giải hệ phương trình đa biến,
bất phương trình, bài toán tối ưu hàm nhiều
biến, bài toán quy hoạch tuyết tính đã được xây
dựng để giải các bài toán kinh tế chuyên ngành.
Trên cơ sở các thuật toán đã xây dựng, người sử
dụng có thể ứng dụng giải cho nhiều bài toán ở
các chuyên ngành khác nhau.
Với các công dụng của hàm Solver, người
sử dụng chỉ cần kỹ năng tính toán trên Excel, đã
có thể giải quyết được những bài toán phức tạp
với thời gian ngắn, thực sự hiệu quả cho công
tác phân tích, tìm lời giải cho vấn đề lựa chọn
tối ưu trong quản lý kinh tế, tính toán thiết kế,
học tập và nghiên cứu.
TÀI LIỆU THAM KHẢO
[1]. Premium Solver Platform For use with Microsoft Excel, Frontline Systems, Inc. USA.
[2]. Trần Trí Dũng, Excel-Solver cho kỹ sư, Nhà xuất bản Khoa học và Kỹ thuật, 2005.
[3]. Nguyễn Đức Nghĩa, Tối ưu hóa, Nhà xuất bản Giáo dục, 2000.
[4]. Nguyễn Hải Thanh, Tối ưu hóa, Giáo trình cho ngành Công nghệ thông tin và Tin học, Nhà
xuất bản Bách khoa, 2006
[5]. Đặng Cảnh Thạc, Trần Thanh Thái, Trần Thanh Phong, Ứng dụng Microsoft Excel trong
kinh tế, Chương trình giảng dạy kinh tế Fulbright.
Microft Excel, được kiến thiết xây dựng và đưa vào sửdụng từ phiên bản Microsoft Excel 97. VớiSolver, người dùng hoàn toàn có thể giải các bài toán sauđây trải qua bảng tính Excel : giải các hệphương trình bất phương trình tuyến tính, cácphương trình đại số bậc cao, hàm mũ ; giảicác bài toán thống kê, giải các bài toán kinhtế quy hoạch tuyến tính tối ưu, bài toán nguyênvật liệu, bài toán vận tải đường bộ. Nhờ đó, tính ứng dụngcủa nội hàm này càng trở nên thông dụng vàtiện ích hơn. Tuy nhiên, những ứng dụng nàychưa thực sự bộc lộ hết với thế mạnh vàtiềm năng của nội hàm Solver. Trên thực tiễn, nội hàm Solver chỉ tương hỗ những nội dung tínhtoán cơ bản giúp cho việc giải toán nhanh, đưara nhiều giải pháp đúng mực kịp thời. còn việcứng dụng Solver để giải quyết các bài toán kinhtế là trọn vẹn nhờ vào vào năng lực xâydựng các quy mô bài toán và thuật toán để giảiquyết các bài toán đó. Giới thiệu và thiết kế xây dựng các quy mô thuậttoán kinh tế ứng dụng hàm Solver để giải cácbài toán chuyên ngành kinh tế, quản trị kinhdoanh là thiết yếu để tương hỗ các nhà quản trị, nhà nghiên cứu vận dụng vào trong công việctính toán và lựa chọn giải pháp tối ưu giảiquyết kịp thời các việc làm một cách hiệuquả. Trong bài báo này, tác giả mong muốngiới thiệu hàm Solver và kiến thiết xây dựng một số ít thuậttoán quy mô giải các bài toán kinh tế nói trên. Giảng viên. Khoa Quản trị kinh doanh thương mại, Trường Đại học Công nghiệp TP, HCMKINH TẾ – XÃ HỘITạp chí Đại học Công nghiệp572. GIỚI THIỆU HÀM SOLVERHàm Solver có hai phiên bản chính : Solverchuẩn ( Standard Solver ) và Solver hoàn thành xong ( Premium Solver ). Solver chuẩn hoàn toàn có thể giải cácbài toán quy hoạch tuyến tính với quy mô 400 biến và 200 ràng buộc cộng với 800 ràng buộccận đặt trên biến. Solver triển khai xong cho phéptoàn cục từng đoạn để dùng cho các bài toán tốiưu hóa toàn cục. Để khởi động Solver, vào Menu File \ Options \ Add – Ins rồi chọn Solver Add – Ins bấm nútGo. Để khởi động vào Menu Data \ Solver. Saukhi khởi động, hộp thoại ” Các tham số củaSolver ( Solver Parameters ) ” Open nhưtrong Hình 1. Hình 1. Hộp thoại SolverHàm tiềm năng ( Set Objective ). Giá trị trong ôcủa bảng tính Excel có địa chỉ tuyệt đối ghi trongkhung Set Objective được gọi là hàm mục tiêu. Biến và tham số ( By Changing VariableCells ). Địa chỉ của các ô trong bảng tính Excelghi các giá trị khởi đầu của biến. Giá trị các biếnnày sẽ bị biến hóa để đạt được giá trị hàm mụctiêu mong ước. Ràng buộc ( Subject to the Constraints ). Trong quy trình đổi khác các biến số để đạtđược giá trị hàm mục tiêu mong ước, các biếnhoặc các tham số của bài toán phải thoả mãnnhững quan hệ ràng buộc nhất định nào đó. Cácràng buộc này được diễn đạt trong khung Subjectto the Constraints. Việc thêm vào, đổi khác hayloại bỏ bớt đi một ràng buộc được thực hiệnnhờ các tính năng Add, Change hay Delete. Các lựa chọn trong hộp thoại ” SolverOptions ” được bộc lộ trong Hình 2. Hình 2 : Hộp thoại Solver OptionsĐộ đúng mực ( Constraint Precision ). Con sốnhập vào ô này xác lập giá trị đo lường và thống kê của vếtrái ràng buộc phải giao động tương thích với vế phảinhư thế nào để các ràng buộc được thoả mãn. Độ đúng chuẩn không nên nhỏ quá và không nênlớn quá. Thông thường nằm trong phạm vi1. 0E-6 đến 1.0 E – 4S ử dụng tỷ suất tự động hóa ( Use AutomaticScaling ). Khi khung này được ghi lại, Solversẽ cố gắng nỗ lực định tỷ suất giá trị hàm mục tiêu vàràng buộc để giảm thiểu ảnh hưởng tác động của mô hìnhcó các đại lượng với giá trị độ lớn độc lạ. Ứng dụng hàm Solver … 58H iển thị tác dụng bước tính lặp ( ShowIteration Results ). Khi công dụng này được lựachọn, hiệu quả từng bước lặp sẽ được hiển thịtrong bản tính của Solver. Thời gian tính lớn nhất ( Max time ). Giá trịtrong khung Max Time xác lập thời hạn lớnnhất tính theo giây để Solver sẽ chạy trước khidừng. Thời gian này gồm có thời hạn sắp xếp ( Setup time ) và thời hạn tìm nghiệm tối ưu. Đây là một trong những điều kiện kèm theo dừng củaSolver. Giá trị mặc định là 100 giây, thời giantối đa hoàn toàn có thể nhập vào 32.767 giây. Số bước tính lặp ( Interations ). Giá trị trongkhung Interactions xác lập số bước tính lặplớn nhất Solver hoàn toàn có thể triển khai trên một bàitoán. Mỗi bước tính lặp tính ra một nghiệmmới. Đây cũng là một trong những điều kiệndừng của Solver. Sự quy tụ ( Convergence ). Chỉ vận dụng chocác bài toán không tuyến tính ( Nonlinear ). Tạiđây nhập vào các số trong khoảng chừng 0 và 1. Giátrị càng gần 0 thì độ đúng mực cao hơn và cầnnhiều thời hạn hơn. Ước lượng hàm mục tiêu và các ràng buộc ( Derivatives ). Có hai lựa chọn : Sai phân tiến ( Forward ), sai phân TT ( Central ). + Forward : Được dùng rất thông dụng hơn, khi đó các giá trị của ràng buộc đổi khác chậm. + Central : Dùng khi các giá trị của ràngbuộc biến hóa nhanh và được dùng khi Solverbáo không hề nâng cấp cải tiến hiệu quả thu được. 3. ỨNG DỤNG SOLVER GIẢI CÁCBÀI TOÁN KINH TẾ3. 1 Bài toán lập quy mô kinh tế tối ưuMục tiêu của việc giải bài toán quy hoạchtuyến tính là tìm được giải pháp tối ưu và vậndụng giải pháp đó vào thực tiễn. Tuy nhiên, trong trong thực tiễn việc làm này lại khá phức tạp, gâykhông ít khó khăn vất vả và lúng túng cho những đốitượng chăm sóc đến nó. Bài viết này nhằm mục đích giớithiệu cách sử dụng ứng dụng ứng dụngMicrosoft Excel để giải bài toán quy hoạch tuyếntính và rút ra các ý nghĩa kinh tế của chúng. Để hiểu rõ việc ứng dụng ứng dụng Excelđể giải các bài toán kinh tế, tất cả chúng ta hãy cùngnhau xem xét ví dụ sau : Công ty “ Hoa Đà Lạt ” cần trồng 4 loại hoaMai, Hồng, Lan, Đào trên 3 mảnh vườn khácnhau. Biết rằng diện tích quy hoạnh đất hiện có ứng vớimỗi mảnh vườn là 40 ha, 60 ha, 80 ha. Diệntích đất phải trồng mỗi loại hoa theo kế hoạchlà : mai : 50 ha, hồng : 70 ha, lan : 30 ha, đào : 30 ha. Ngoài ra, do đặc thù của các loại đất trồngkhác nhau, nên hoa hồng không hề trồng đượctrên mảnh đất thứ nhất, và hoa đào không thểtrồng được trên mảnh đất thứ ba. Biết thu hoạch ( doanh thu ) ước tính của từng loại hoa trên từngloại đất trồng như sau ( trăm ngàn đồng / ha ) : Hoa ( ha ) Đất ( ha ) Mai ( 50 ) Hồng ( 70 ) Lan ( 30 ) Đào ( 30 ) 40 10 – 8 960 6 9 12 1280 15 10 10 – Hãy lập kế hoạch trồng hoa sao cho công ty thuđược doanh thu nhiều nhất. Trong ví dụ này bước 1 : Lập quy mô bài toán + Tổng diện tích quy hoạnh đất = 40 + 60 + 80 = 180 = 50 + 70 + 30 + 30 = Tổng diện tíchtrồng hoa + Gọi xijlà số ha mảnh vườn i trồng loạihoa j, với i = 1, 2, 3 và j = 1, 2, 3, 4 tương ứnglà mai, hồng, lan, đào. Hàm mục tiêu : f ( x ) = 10 x11 + 8×13 + 9×14 + 6×21 + 9×22 + 12×23 + 12×24 + 15×31 + 10×32 + 10×33 → Max11 + x13 + x14 = 4021 + x22 + x23 + x24 = 6031 + x32 + x33 = 8011 + x21 + x31 = 5022 + x32 = 7013 + x23 + x33 = 3014 + x24 = 30 ij ≥ 0, ji, Tạp chí Đại học Công nghiệp59Bước 2 : Ứng dụng Excel để giải bài toán dựatrên quy mô ở bước 1. – Khởi động Exel – Nhập dữ liệu vào bảng tính : + Cột A là giá trị hoàn toàn có thể có của các biến. Trong ví dụ này ta có 10 biến cần tìm từ11đến x33 + Cột B là công thức tính biểu thức vế tráicủa các ràng buộc + Cột C là giá trị vế phải của các ràngbuộc + Cột D là công thức tính hàm mục tiêuBan đầu ta cho giá trị tùy chọn vào cột. Trongví dụ này, các số liệu như ở bảng 1A B C D EBiến Nghiệm Vế trái Vế phải Phương án tối ưu111 = B2 + B4 + B5 40 = 10 * B2 + 8 * B4 + 9 * B5 + 6 * B6 + 9 * B7 + 12 * B8 + 12 * B9 + 15 * B10 + 10 * B11 + 10 * B123 x120 = B6 + B7 + B8 + B9 604 x131 = B10 + B11 + B12 805 x141 = B2 + B6 + B10 506 x211 = B7 + B11 707 x221 = B4 + B8 + B12 308 x231 = B5 + B9 309 x2410 x3111 x3212 x33Bảng 1 : Nhập liệu các số liệu của bài toán trên ExcelSau khi nhập liệu ta triển khai tính các công thức cho các ô theo các ràng buộc của bài toán. Kếtquả được bộc lộ trong bảng sau : A B C D EBiến Nghiệm Vế trái Vế phải Phương án tối ưu2 x111 3 40 1013 x120 4 604 x131 3 805 x141 3 506 x211 2 707 x221 3 308 x231 2 309 x2410 x3111 x3212 x33Bảng 2 : Kết quả sau khi nhập dữ liệuỨng dụng hàm Solver … 60B ước 3 : Dùng Solver để giải bài toán + Từ Menu Data chọn Solver1 ), Open hộp hội thoại Solver Parameters : • Set Objective : USD E USD 2 Chọn địa chỉ hàm mục tiêu. • Equal To : Max Chọn tiềm năng tối ưu ( Max hoặc Min ). • By Changing Cells : USD B USD 2 : USD B USD 12 Chứa các biến cần tìm x = ( x11, x1213, , x33, … ), Cần cho các biến 1 giá trị khởi động nào đó, Chẳng hạn x11 = x13 = = 33 = 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 : USD B USD 2 : USD B USD 12 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 : • Cell Reference : USD C USD 2 : USD B USD 8 Hộp bên tay trái • Chọn dấu = Hộp ở giữa • Constraint : USD D USD 2 : USD D USD 8 Hộp bên tay phải + Nhấn OK, trở lại hộp hội thoại Solver Parameters : 1 ) Nếu trong menu Tool không có Solver thì vào menu Tool, chọn Add – Ins, Open hộp hội thoại Add – Ins, chọnmục Solver Add – Ins. Bấm mút Go. Tạp chí Đại học Công nghiệp61Nhấn nút Solver, Open hộp hội thoại Solver Results : + Chọn Keep Solver Solution, nhấn OK. Khi đó tác dụng bài toán sẽ được hiển thị như sau : A B C D EBiến Nghiệm Vế trái Vế phải Phương án tối ưu2 x1110 40 40 20003 x120 60 604 x130 80 805 x1430 50 506 x210 70 707 x2230 30 308 x2330 30 309 x2410 x314011 x324012 x33Bước 4 : Kết luậnBài toán có giải pháp tối ưu x = ( 10, 0, 0, 30, 0, 30, 30, 0, 40, 40, 0, 0 ) vàmax = 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 chia như sau : Đơn vị tính : haHoa ( ha ) Đất ( ha ) Mai ( 50 ) Hồng ( 70 ) Lan ( 30 ) Đào ( 30 ) 40 10 0 0 3060 0 30 30 080 40 40 0 0 Ứng dụng hàm Solver … 62V ới kế hoạch trồng hoa như trên thì công ty “ Hoa Đà Lạt ” thu được doanh thu nhiều nhất, giá trị doanh thu đạt đến 200 triệu đồng. Như vậy, vận dụng phầm mềm Excel đểgiải bài toán không những làm cho bài toán trởnên đơn thuần hơn rất nhiều mà còn mang ýnghĩa kinh tế thâm thúy, biến các số lượng “ khôkhan ” trong quy mô toán học đi vào thực tiễncuộc sống. 3.2 Bài toán nguyên vật liệuViệc quản trị nguyên vật liệu để cung cấpcho quy trình sản xuất tạo ra mẫu sản phẩm rất cầnthiết. Sử dụng nguồn nguyên vật liệu hài hòa và hợp lý, kịp thời, không thiếu không những làm cho quá trìnhsản xuất được thuận tiện mà còn đem lại nguồnlợi nhuận cao nhất cho doanh nghiệp. Bài toán : Một xí nghiệp sản xuất dự tính thực thi sảnxuất năm loại loại sản phẩm Sj ( j = 1 -> 5 ), cả 5 loạisản phẩm đều sử dụng 4 loại nguyên vật liệuchính NVL ( i = 1 -> 4 ), có mức tiêu tốn nguyênvật liệu, doanh thu đơn vị chức năng thu được và giới hạndự trữ như sau : DựtrữNVL2 5 6 8 4 1200NVL3 1 5 6 1 800NVL7 5 4 5 2 2000NVL8 5 7 9 1 1865L ợinhuậnđơn vị300 250 500 150 320H ãy kiến thiết xây dựng giải pháp sản xuất để nhà máyđạt được tổng doanh thu lớn nhất. Trong bài toán này, bước 1 lập quy mô bàitoán như sau : Gọi xlà sản lượng sản phẩm loại j sẽ sản xuất ( x ≥ 0 ) Nên giải pháp sản xuất của nhà máy sản xuất là vectơ x = ( x, x, x, x, xHàm tiềm năng : f ( x ) = 300 x + 250 x + 500×150 x + 320 x → MaxCác ràng buộc : ≤ + + + + ≤ + + + + ≤ + + + + ≤ + + + + 1865975820002545780065312004865254321543215432154321 xxxxxxxxxxxxxxxxxxxxBước 2 : Bài toán được tổ chức triển khai trên bảng tính Excel như sau : A B C D E F G H1 S1 S2 S3 S4 S52 Lượng loại sản phẩm 0 0 0 0 0 Hàm mục tiêu3 Lợi nhuận đơn vị chức năng 300 250 500 150 320 04 Các ràng buộc Vế trái Vế phải5 RB1 2 5 6 8 4 0 12006 RB2 3 1 5 6 1 0 8007 RB3 7 5 4 5 2 0 20008 RB4 8 5 7 9 1 0 1865T ạp chí Đại học Công nghiệp63Tại G3 nhập công thức : = SUMPRODUCT ( B2 : F2, B3 : F3 ) Tại G5 nhập công thức : = SUMPRODUCT ( USD B USD 2 : USD F USD 2, B5 : F5 ) Sau đó kéo xuống cho các ô còn lại ( G6, G7, G8 ) Bước 3 : Giải bài toán bằng Solver trong Excel + Vào Menu Data \ Solver, điền đầy đủthông tin vào hộp thoại SolverParameters. • Set Objective : USD G USD 3C họn địa chỉ hàm mục tiêu. • Equal To : MaxChọn tiềm năng tối ưu ( Max hoặcMin ). • By Changing Cells : USD B USD 2 : USD F USD 2N ghiệm của phương trình • Tại Subject to the Constraints nhấnnút Add để khai báo các ràng buộc. • Cell Reference : Chọn ô chứa cáccông thức ràng buộc • Dấu : Chọn dấu tương ứng • Constraint : Giá trị các ràng buộc • Để khai báo nhiều ràng buộc phảinhấn nút Add ở đầu cuối nhấn OK. + Trong bài này khai báo năm ràng buộc. 9 Các nghiệm của phương trình ≥ 09 Các ràng buộc còn lại là hệ bấtphương trìnhCell Reference Dấu Constraint USD B USD 2 : USD F USD 2 > = 0 USD G USD 5 < = USD H USD 5 USD G USD 6 < = USD H USD 6 USD G USD 7 < = USD H USD 7 USD G USD 8 < = USD H USD 8 + Cuối cùng nhấn nút Solver để chương trình tìm nghiệm, hiệu quả như sau : A B C D E F G H1 S1 S2 S3 S4 S52 Lượng mẫu sản phẩm 200 0 0 0 200 Hàm mục tiêu3 Lợi nhuận đơn vị chức năng 300 250 500 150 320 1240004 Các ràng buộc Vế trái Vế phải5 RB1 2 5 6 8 4 1200 12006 RB2 3 1 5 6 1 800 8007 RB3 7 5 4 5 2 1800 20008 RB4 8 5 7 9 1 1800 1865 Ứng dụng hàm Solver … 64V ậy giải pháp tối ưu là x = ( 200, 0, 0, 0, 200 ) f ( x ) max = 124000. Phương án sản xuất tối ưu của xí nghiệp sản xuất làsản xuất 200 đơn vị chức năng mẫu sản phẩm 1 và 200 đơn vịsản phẩm 5, khi đó doanh thu tối ưu đạt được là124000 đơn vị chức năng tiền tệ, không có nguyên liệunào bị tiêu tốn lãng phí. 3.3 Bài toán vận tảiBài toán vận tải đường bộ là một bài toán hay, đượcứng dụng nhiều trong quy trình tiến độ vận chuyểnhàng hóa từ nơi sản xuất đến nơi tiêu thụ, lậpmô hình luân chuyển sao cho ngân sách vận chuyểnlà thấp nhất, đem lại doanh thu cao cho doanhnghiệp, đó là yếu tố thiết yếu và hữu dụng màdoanh nghiệp cần làm. Bài toán : Lập phương án luân chuyển xăngtối ưu từ 4 kho đến 5 trạm xăng kinh doanh bán lẻ củaCông ty kinh doanh thương mại xăng dầu khu vực II. Sốliệu được cho như sau : Dự trữ30 27 26 9 23 413 4 22 3 1 63 1 5 4 24 1016 30 17 10 16 10N hu cầu tiêu thụ7 7 7 7 2 • K : Kho hàng cùng chứa một loại hànghóa ( i = 1 -> 4 ) • Đ : Địa điểm tiêu thụ ( j = 1 -> 5 ) • Cột G : Lượng hàng có ở mỗi kho • Dòng 7 : Nhu cầu tiêu thụ ở từng khu vực • Các số liệu từ B3 : F6 là cước phí vậnchuyển một đơn vị chức năng sản phẩm & hàng hóa từ kho iđến khu vực tiêu tụ jHãy lập kế hoạch luân chuyển hàng từ khođến các khu vực tiêu thụ sao cho tổng chi phívận chuyển là nhỏ nhất. Trong bài toán này bước 1 : thiết kế xây dựng môhình bài toánGọi xijlà lượng hàng luân chuyển từ kho iđến điểm tiêu thụ j, nên xij ≥ 0, i = 1 -> 4, j = 1 -> 5H àm tiềm năng : f ( x ) = 30×11 + 27×12 + 26×139 x14 + 23×15 + 13×21 + 4×22 + 22×23 + 3×2425 + 3×31 + x32 + 5×33 + 4×34 + 24×35 + 16×41 + 30×42 + 17×43 + 10×44 + 16×45 → MinCác ràng buộc11 + x12 + x13 + x14 + x15 ≤ 421 + x22 + x23 + x24 + x25 ≤ 631 + x32 + x33 + x34 + x35 ≤ 1041 + x42 + x43 + x44 + x45 ≤ 1011 + x21 + x31 + x41 ≤ 712 + x22 + x32 + x42 ≤ 713 + x23 + x33 + x43 ≤ 714 + x24 + x34 + x44 ≤ 715 + x25 + x35 + x45 ≤ 2B ước 2 : Tổ chức tài liệu trên bảng tính Excel. Tạp chí Đại học Công nghiệp65A B C D E F GDự trữ2 K30 27 26 9 23 43 K13 4 22 3 1 64 K3 1 5 4 24 105 K16 30 17 10 16 106 Nhu cầu tiêu thụ 7 7 7 7 27 Lượng xăng luân chuyển 0 0 0 0 08 0 0 0 0 09 0 0 0 0 010 0 0 0 0 011 Hàm mục tiêu 0 B11 = SUMPRODUCT ( USD B USD 7 : USD F USD 10, B2 : F5 ) 12V ế trái Vế phải13 Các ràng buộc14 RB1 0 4 B14 = SUM ( B7 : F7 ) 15 RB2 0 6 B15 = SUM ( B8 : F8 ) 16 RB3 0 10 B16 = SUM ( B9 : F9 ) 17 RB4 0 10 B17 = SUM ( B10 : F10 ) 18 Tổng3019 RB4 0 7 B19 = SUM ( B7 : B10 ) 20 RB5 0 7 B20 = SUM ( C7 : C10 ) 21 RB6 0 7 B21 = SUM ( D7 : D10 ) 22 RB7 0 7 B22 = SUM ( E7 : E10 ) 23 RB8 0 2 B23 = SUM ( F7 : F10 ) 24 Tổng30Bước 3 : Giải bài toán + Chọn ô B13 rồi vào Menu Data \ Solver điền không thiếu thông tin vào hộp thoại SolverParameters. • Set Objective : USD B USD 13 Chọn địa chỉ hàm mục tiêu. • Equal To : Min Chọn tiềm năng tối ưu ( Max hoặc Min ). • By Changing Cells : USD B USD 8 : USD F USD 11 Nghiệm của phương trình • Tại Subject to the Constraints nhấn nút Add để khai báo các ràng buộc. • Cell Reference : Chọn ô chứa các công thức ràng buộc • Dấu : Chọn dấu tương ứng • Constraint : Giá trị các ràng buộc • Để khai báo nhiều ràng buộc phải nhấn nút Add sau cuối nhấn OK.Trong bài này khai báo 3 ràng buộc. Ứng dụng hàm Solver … 66 + Các nghiệm của phương trình ≥ 0 + Các ràng buộc còn lại là hệ bất phương trìnhCell Reference Dấu Constraint USD B USD 8 : USD F USD 11 > = 0 USD B USD 16 : USD B USD 19 < = USD C USD 16 : USD C USD 19 USD B USD 21 : USD B USD 25 < = USD C USD 21 : USD C USD 25 + Cuối cùng nhấn nút Solver để chương trình tìm nghiệm, hiệu quả như sau : A B C D E F GDự trữ2 K30 27 26 9 23 43 K13 4 22 3 1 64 K3 1 5 4 24 105 K16 30 17 10 16 106 Nhu cầu tiêu thụ 7 7 7 7 27 Lượng xăng luân chuyển 0 0 0 4 08 0 4 0 0 29 7 3 0 0 010 0 0 7 3 011 Hàm mục tiêu 22712V ế trái Vế phải13 Các ràng buộc14 RB1 4 415 RB2 6 616 RB3 10 1017 RB4 10 1018 Tổng3019 RB4 7 720 RB5 7 721 RB6 7 722 RB7 7 723 RB8 2 224 Tổng30Tạp chí Đại học Công nghiệp67 + Phân tích tác dụng : + Phương án luân chuyển là : + x = ( 0, 0, 0, 4, 0, 0, 4, 0, 0, 2, 7, 3, 0, 0,0, 0, 0, 7, 3, 0 ) + f ( x ) = 227, ngân sách thấp nhất. + Vậy tổng lượng xăng dự trữ ở các khobằng tổng nhu yếu xăng ở các trạm 30 nên giải pháp tìm được là tối ưu. 3.4. Ứng dụng Excel để giải hệ phươngtrình tuyến tínhNgoài ứng dụng để giải các bài toán trênSolver còn hoàn toàn có thể ứng dụng để giải các bài toánvề hệ phương trình. Khi đó chỉ có các ràngbuộc dạng phương trình và không có hàm mụctiêu. Các bước thực thi giải hệ phương trìnhhoàn toàn như khi giải bài toán kinh tế trên. Đểhiểu xa hơn triển khai xét ví dụ sau : Giải hệ phương trình bằng SolverVí dụ : Giải hệ phương trình sau : = + + = + + = + + 303418233322 zyxzyxzyxBước 1 : Tổ chức tài liệu bảng tính trên ExcelA B C D E Fax by cz Nghiệm Vế trái Vế phải2 2 2 3 1 7 333 2 1 1 1 4 184 1 4 3 1 8 30T ại ô E2 nhập công thức : = SUMPRODUCT ( A2 : C2, TRANSPOSE ( USD D USD 2 : USD D USD 4 ) ) kết thức bằng Ctrl + Shift + Enter, sauđó copy xuống cho 2 ô còn lại. Bước 2 : Giải bằng công cụ Solver trong Excel + Vào Menu Data \ Solver, điền đầy đủthông tin vào hộp thoại SolverParameters • By Changing Cells : USD D USD 2 : USD D USD 4N ghiệm của phương trình • Tại Subject to the Constraints nhấnnút Add để khai báo các ràng buộc. Cell Reference Dấu Constraint USD E USD 2 : USD E $ 4 = USD F USD 2 : USD F USD 4 + Cuối cùng nhấn nút Solver để chươngtrình tìm nghiệm, tác dụng như sau : A B C D E Fax by cz Nghiệm Vế trái Vế phải2 2 2 3 5 33 333 2 1 1 1 18 184 1 4 3 7 30 30V ậy nghiệm của hệ phương trình là : x = 5 ; y = 1 ; z = 74. KẾT LUẬNỨng dụng hàm Solver … 68C ác tác giả đã ra mắt cơ bản về HàmSolver và các tham số ứng của nó. Các thôngtin này là cầu nối người sử dụng với hàm Solverđến bài toán kinh tế. Các thuật toán giải hệ phương trình đa biến, bất phương trình, bài toán tối ưu hàm nhiềubiến, bài toán quy hoạch tuyết tính đã được xâydựng để giải các bài toán kinh tế chuyên ngành. Trên cơ sở các thuật toán đã kiến thiết xây dựng, người sửdụng hoàn toàn có thể ứng dụng giải cho nhiều bài toán ởcác chuyên ngành khác nhau. Với các hiệu quả của hàm Solver, ngườisử dụng chỉ cần kỹ năng và kiến thức giám sát trên Excel, đãcó thể giải quyết được những bài toán phức tạpvới thời hạn ngắn, thực sự hiệu suất cao cho côngtác nghiên cứu và phân tích, tìm giải thuật cho yếu tố lựa chọntối ưu trong quản trị kinh tế, thống kê giám sát phong cách thiết kế, học tập và nghiên cứu và điều tra. TÀI LIỆU THAM KHẢO [ 1 ]. Premium Solver Platform For use with Microsoft Excel, Frontline Systems, Inc. USA. [ 2 ]. Trần Trí Dũng, Excel-Solver cho kỹ sư, Nhà xuất bản Khoa học và Kỹ thuật, 2005. [ 3 ]. Nguyễn Đức Nghĩa, Tối ưu hóa, Nhà xuất bản Giáo dục đào tạo, 2000. [ 4 ]. Nguyễn Hải Thanh, Tối ưu hóa, Giáo trình cho ngành Công nghệ thông tin và Tin học, Nhàxuất bản Bách khoa, 2006 [ 5 ]. Đặng Cảnh Thạc, Trần Thanh Thái, Trần Thanh Phong, Ứng dụng Microsoft Excel trongkinh tế, Chương trình giảng dạy kinh tế Fulbright .
