Bài giảng Giải các bài toán tối ưu và thống kê trên Microsoft Excel

Chú ý, nếu các lệnh Solver và Data Analysis không có trong menu Tools ta phải cài 
đặt bổ sung từ đĩa CD: dùng lệnh Tools / Add-Ins, hiện hộp thoại, chọn mục Solver Add in 
và Analysis ToolPak. 
Bài tập 
1. Giải bài toán quy hoạch tuyến tính nguyên bộ phận: 
min325 54321 →++++= xxxxxz 
225 5432 ≤−−+− xxxx 
75 521 ≥+− xxx 
46 4321 ≥+++ xxxx 
5,4,3,2,10 =≥ jx j 
3,2,1,interger == jx j 
Đáp số: trị tối −u là 12, ph−ơng án tối −u (2, 2, 0, 0, 0). 
2. Giải bài toán quy hoạch tuyến tính boolean (bài toán cái túi) sau: 
max111019862038131930 10987654321 →+++++++++ xxxxxxxxxx 
6215122085152791215 10987654321 ≤+++++++++ xxxxxxxxxx { } 10,,2,1,1,0 "=∈ jx j 
Đáp số: trị tối −u là 95, ph−ơng án tối −u là ( 1, 1, 0, 1, 0, 0, 1, 0, 0, 0). 
3. Giải bài toán phân công công việc. Có n đơn vị sản xuất cần sản xuất n loại sản 
phẩm, đơn vị i sản xuất sản phẩm j với chi phí là c[i,j]. Hãy phân công mỗi đơn vị sản xuất 
một sản phẩm để tổng chi phí là nhỏ nhất. Dạng bài toán: 
∑∑
= =
→
n
i
n
j
ijij xc
1 1
min 
∑
=
==
n
j
ij nix
1
,,2,1,1 " 
PGS. TS. Bùi Thế Tâm. Giải các bài toán tối −u và thống kê trên Excel 
 9
{ }∑
=
===
n
i
ijij xnjx
1
1,0,,,2,1,1 " 
Dùng Solver giải bài toán phân công với n = 4 và ma trận chi phí sau: 
Đáp số: trị tối −u hàm mục tiêu là 3200000 đồng, ph−ơng án tối −u là x[1,1]= x[2,4]= 
x[3,2]= x[4,3] = 1. 
4. Giải bài toán tìm luồng cực đại trên đồ thị có h−ớng. Cho đồ thị có h−ớng gồm 6 
đỉnh, nếu từ đỉnh u tới đỉnh đỉnh v có đ−ờng vận chuyển thì ta vẽ một cung j, l−ợng hàng 
vận chuyển trên cung này là x[j], khả năng vận chuyển tối đa trên cung này là q[j]. Tìm 
l−ợng hàng lớn nhất có thể vận chuyển từ đỉnh 1 đến đỉnh 6. 
Từ đồ thị trên ta có thể viết hàm mục tiêu và các ràng buộc nh− sau: 
x[1] + x[2] ===> Max 
x[1] - x[4] -x[5] = 0 
x[2] - x[3] - x[7] = 0 
x[3] + x[4] - x[6] = 0 
x[7] - x[8] = 0 
x[1] + x[2] - x[5] - x[6] - x[8] = 0 
0 <= x[j] <= q[j], j = 1, 2, 3, 4, 5, 6, 7, 8 
trong đó véc tơ q = (4, 2, 4, 4, 1, 2, 2, 2). 
Đáp số: l−ợng hàng tối đa có thể vận chuyển là 5, ph−ơng án tối −u là x = (3, 2, 0, 2, 
1, 2, 2, 2). Đối với các bài toán đồ thị khác, nếu bằng cách đặt biến ta có thể phát biểu 
chúng d−ới dạng đại số thì cũng có thể dùng Solver để giải. 
5. Giải bài toán quy hoạch lõm (có thể có nhiều cực tiểu địa ph−ơng) 
 Minxxxxxxxxxx →−+−+−+−+−+− 1801814842 525424323222121 
85322 54321 ≤+++−− xxxxx 
5001133597 54321 ≤−+−+− xxxxx 
150222 54321 ≤+−+− xxxxx 
3003.1 54321 ≤++++ xxxxx 
30054321 ≤++++ xxxxx 
0,,,, 54321 ≥xxxxx 
Với ph−ơng án ban đầu X = (50, 50, 50, 50, 50) dùng Solver ta đ−ợc lời giải tối −u là 
X = (0, 190, 0, 0, 110) và trị tối −u hàm mục tiêu là - 45640. 
PGS. TS. Bùi Thế Tâm. Giải các bài toán tối −u và thống kê trên Excel 
 10
Ch−ơng 2 
 Giải các bài toán thống kê 
trên Microsoft Excel 
 Trong Excel có một số hàm mảng để thực hiện hồi quy tuyến tính (Linest, Trend, 
Forecast, Slope, Intercept) và hồi quy mũ (Logest, Growth). Những hàm này đ−ợc nhập nh− 
những công thức mảng và cho kết quả mảng. 
2.1. Hồi quy tuyến tính bội 
 Ph−ơng trình hồi quy bội tuyến tính có dạng: 
 ,2211 bxmxmxmy nn ++++= " (1) 
trong đó x1, x2, . . . , xn là các biến độc lập, y là biến phụ thuộc, các hệ số m1, m2, , mn, 
b là các hệ số cần xác định. Các giá trị quan sát của các biến có thể bố trí theo dạng cột 
hoặc theo dạng hàng. 
 • Hàm Linest dùng để tính các hệ số của ph−ơng trình hồi quy tuyến tính bội, cú 
pháp: 
= LINEST(known_y's, known_x's, const, stats) 
trong đó known_y's là khối ô chứa các quan sát của biến y; known_x's là khối ô chứa các 
quan sát của các biến x1, x2, . . . , xn; biến const có giá trị logic (nhập True hoặc để trống 
nếu có tính b, nhập False nếu buộc b=0). Biến stats có giá trị logic, nhập False nếu không 
in các thống kê hồi quy, nhập True hoặc bỏ trống thì hàm cho các thống kê hồi quy dạng: 
bmmmm nn 121 """− 
bnn sesesesese 121 """− 
yser
2 
fdF 
residreg ssss 
trong đó bnn sesesesese 121 """− là các sai số chuẩn hoá của các hệ số m1, 
m2, ..., mn, b. Hệ số r2 là hệ số xác định thuộc [0, 1], nếu r2 = 1 thì có quan hệ hoàn hảo 
trong mẫu, nếu r2 = 0 thì ph−ơng trình hồi quy không có tác dụng dự đoán y. 
Hệ số yse là sai số chuẩn hoá cho −ớc l−ợng y. Hệ số F là thống kê F, dùng F để xác định 
liệu giữa biến phụ thuộc và các biến độc lập 
có thực sự quan hệ với nhau hay đó chỉ là thể 
hiện của tác động ngẫu nhiên. Hệ số fd là bậc 
tự do, dùng để xác định mức tin cậy của mô 
hình hồi quy. Các hệ số residreg ssss là tổng 
bình ph−ơng giá trị hồi quy và tổng bình 
ph−ơng độ lệch. 
Ví dụ 1. Bảng bên cho số liệu về doanh 
thu (Y), chi phí cho quảng cáo (X1), tiền 
l−ơng của nhân viên tiếp thị (X2) của 12 công 
ty t− nhân, đơn vị là 1 triệu đồng. Xây dựng 
hàm hồi quy tuyến tính bội Y phụ thuộc vào 
X1, X2. 
PGS. TS. Bùi Thế Tâm. Giải các bài toán tối −u và thống kê trên Excel 
 11
 Để −ớc l−ợng hàm hồi quy ta dùng hàm mảng Linest nh− sau: đánh dấu khối vùng ô 
B19: D23, nhập công thức =LINEST(A2 : A13, B2 : C13, True, True), ấn Ctrl + Shift + 
Enter, kết quả ta đ−ợc 12 số: 
 Nh− vậy ph−ơng trình hồi quy là 
 Y = 2.505729 X1 + 4.75869 X2 + 32.27726. (2) 
• Hàm TREND nhằm tính các giá trị y theo hàm −ớc l−ợng (1) với các bộ giá trị cho 
tr−ớc (x1, x2, . . . , xn), các bộ giá trị này có thể là các quan sát cũ hoặc các dự báo mới. Cú 
pháp hàm: 
= TREND( known_y's, known_x's, new_x's, const ) 
trong đó know_y's là khối ô chứa chứa các quan sát của biến y; known_x's là khối ô chứa 
các quan sát của các biến x1, x2, . . . , xn; biến const có giá trị logic (nhập True hoặc để 
trống nếu có tính b, nhập False nếu buộc b=0). Tham số new_x's là khối ô chứa các giá trị 
mới của x1, x2, . . . , xn mà ta cần tính các giá trị y t−ơng ứng theo (1); nếu bỏ trống tham 
số này thì coi nó chính là know_x's. 
 Trở lại ví dụ 1, dùng hàm Trend tính cột D (là các giá trị y tính theo (2) với các bộ giá 
trị x1, x2, , xn t−ơng ứng trong khối B2 : C13). Thao tác tính: đánh dấu khối vùng ô chứa 
kết quả của hàm là D2 : D13, nhập công thức = Trend(a2:a13, b2:c13), ấn Ctrl + Shift + 
Enter. So sánh khối ô D2:D13 với khối ô A2:A13 ta thấy đ−ợc sự sai khác giữa giá trị y 
tính theo hàm (2) với giá trị thực tế quan sát đ−ợc. 
 Tiếp theo, cho các bộ giá trị mới x1, x2 trong khối ô B15 : C17, cần dự báo các giá 
trị y đ−ợc tính theo (2) trong khối ô D15 : 
D17. Thao tác tính: đánh dấu khối vùng ô 
D15:D17, nhập công thức = Trend(a2: a13, 
b2: c13, b15: c17, True), ấn Ctrl + Shift + 
Enter. 
 • Lệnh Tools / Data Analysis nhằm 
tính các tham số của hàm hồi quy tuyến tính bội (1) và các thống kê. Xét ví dụ 1, giả sử ta 
đã nhập các quan sát của các biến y, x1, x2 trong khối ô A2: C13. Dùng lệnh Tools / Data 
Analysis, hiện hộp thoại Data Analysis, chọn mục Regression, nháy OK, hiện hộp thoại 
Regression: 
PGS. TS. Bùi Thế Tâm. Giải các bài toán tối −u và thống kê trên Excel 
 12
nhập khối ô chứa các quan sát của biến y vào mục Input Y Range, nhập khối ô chứa các giá 
trị quan sát của biến x1, x2 vào mục Input X Range, lựa chọn mục Output Range và nhập 
địa chỉ ô ở góc trên bên trái của vùng chứa kết quả, nháy OK. Kết quả cho trong bảng sau: 
 Trong bảng trên Hệ số xác định r2 nằm trong ô B28, sai số chuẩn hoá cho −ớc l−ợng 
y nằm trong ô B30, khối ô B37: B39 chứa các hệ số đ−ờng hồi quy b, m1, m2. Khối ô C37: 
C39 chứa các sai số chuẩn hoá của b, m1, m2. Thống kê F trong ô E33. 
2.2. Hồi quy tuyến tính đơn 
 Hồi quy tuyến tính đơn là tr−ờng hợp riêng của hồi quy tuyến tính bội (1) với n=1: 
 bmxy += (3) 
Do đó tất cả các hàm và lệnh đã trình bày với hồi quy tuyến tính bội cũng đúng với hồi quy 
tuyến tính đơn. Song đối với hồi quy tuyến tính đơn có thêm ba hàm mới. 
− Hàm Slope(known_y's, known_x's) −ớc l−ợng giá trị m của ph−ơng trình (3). 
− Hàm Intercept(known_y's, known_x's) −ớc l−ợng giá trị b của (3). 
− Hàm Forecast( x, known_y's, known_x's ): dự đoán y theo ph−ơng trình (3) với giá 
trị x biết tr−ớc. 
Ví dụ 2. Tính hàm hồi quy của y (sản l−ợng nông nghiệp) phụ thuộc vào x (l−ợng 
phân bón). 
Công thức trong ô D2 là = Slope(a2:a6, b2:b6), công thức trong ô E2 là 
=Intercept(a2:a6, b2:b6), công thức trong ô E5 là =Forecast(d5, a2:a6, b2:b6) để dự báo y 
với x = 1612. 
2.3. Hồi quy mũ 
Ph−ơng trình hồi quy mũ là 
 nxn
xx mmmby ∗∗∗∗= "21 21 (4) 
Nếu chỉ có một biến độc lập ph−ơng trình sẽ là xmby ∗= . 
PGS. TS. Bùi Thế Tâm. Giải các bài toán tối −u và thống kê trên Excel 
 13
Hàm Logest dùng để −ớc l−ợng các hệ số của ph−ơng trình (4), nó làm việc giống 
nh− hàm Linest (các đối số và mảng kết quả hoàn toàn giống). Cú pháp: 
= LOGEST( known_y's, known_x's, const, stats ). 
Hàm Growth dùng để tính các giá trị y theo (4) với các bộ giá trị (x1, x2,  , xn) 
cho tr−ớc, làm việc hoàn toàn giống hàm Trend. Cú pháp: 
= GROWTH( known_y's, known_x's, new_x's, const ). 
Bài tập 
1. Cho Y là nhu cầu thịt bò (đơn vị 100 tấn) của 12 tháng liên tiếp (X) trong một khu 
dân c−: 
 X: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 
 Y: 15, 18, 18, 16, 14, 18, 20, 21, 19, 20, 24, 26. 
Hãy −ớc l−ợng hàm hồi quy tuyến tính đơn, dự báo nhu cầu thịt bò cho 3 tháng tiếp theo. 
Đáp số : y = 0.793706 x + 13.92424. 
2. Trong 10 tháng liên tiếp l−ợng hàng bán ra của một công ty rất thấp, sau đó công ty 
tung ra thị tr−ờng một sản phẩm mới và nhận thấy l−ợng hàng bán ra tăng theo hàm mũ. Số 
đơn vị hàng bán ra (Y) trong 6 tháng tiếp theo (X) cho trong bảng sau: 
Hãy −ớc l−ợng hàm hồi quy mũ và dự báo l−ợng hàng bán ra trong các tháng 17, 18, 19, 20 
(dùng hàm Growth). Đáp số : xy 463276.13048.495 ∗= . 
3. Tính hàm hồi quy tuyến tính bội với số liệu cho trong bảng d−ới 
trong đó Y là thu nhập quốc dân, X1 là sản l−ợng điện, X2 là sản l−ợng than, X3 là sản 
l−ợng l−ơng thực, X4 là sản l−ợng thép. Dùng hai ph−ơng pháp: dùng hàm Linest và lệnh 
Tools / Data Analysis. Dự báo Y với X = (5.2, 65.1, 275.3, 37.8). Đáp số: dự báo Y = 
751.79289.