Bài giảng Tối ưu hóa trong thiết kế cơ khí - Chương 10: Ứng dụng Excel Solver giải các bài toán tối ưu hóa

Trường Đại học Công nghiệp thành phố Hồ Chí Minh 
Khoa Công nghệ Cơ khí 
CHƯƠNG 10: 
ỨNG DỤNG EXCEL SOLVER GIẢI CÁC 
BÀI TOÁN TỐI ƯU HÓA 
Thời lượng: 3 tiết 
2 
NHỮNG NỘI DUNG CHÍNH 
3 
KÍCH HOẠT EXCEL SOLVER 
1) Từ Excel 2010 trở về sau, ta vào Menu File > Options 
2) Chọn Add-Ins > Tìm chỗ Manage > Ấn vào Go 
3) Trong cửa số Add-Ins Checkbox, ta tích chọn Solver Add-in check 
box > OK Mục Solver Add-Ins sẽ có ở menu Data của Excel 
4 
GIẢI PHƯƠNG TRÌNH PHI TUYẾN 1 ẨN 
Giải phương trình: 
2
sin 0
3
x
x 
1) Tạo file Excel, ví dụ PTPT_bac1.xlsx 
2) Đặt tên bài toán ở đầu file để biết được nội dung bài toán 
3) Ở ô A3, A4 ghi nội dung: Biến số và phương trình. Mục đích để 
hiểu bài toán 
5 4) Ở ô B3, B4 ghi x và nội hàm của phương trình. Đây chỉ là thuần túy 
Cho việc hiển thị để hiểu về nội dung bài toán 
5) Ở ô C3 ta nhập 1 số cụ thể, ví dụ như 2. Đây sẽ là 1 giá trị ví dụ cho 
biến x sau này. 
6 6) Nhấp chuột vào ô C3 (chứa số 2) Chọn menu “Formulas” 
Chọn Define Name 
7) Trong cửa sổ New Name hiện ra, Ở trường Name ghi x Ấn OK 
7 8) Trong ô C4, ghi công thức =2*x/3-sin(x) Enter 
Chú {: ô C4 lúc này là công thức nên nhập mọi phép tính một cách 
chính xác chứ không phải như ở ô B4 chỉ là để hiển thị 
8 9) Bôi đen từ A3 đến C4 hết vùng của bài toán: 
10) Data > Solver 
9 11) Chọn các thuộc tính: 
1 
2 
3 
4 
5 
6 
10 12) Chọn các thuộc tính để in kết quả: 
1 
2 
3 
1.495780977x 
13) Xem Sheet Answer Report 1: 
11 
GIẢI HỆ PT & BPT PHI TUYẾN 
Giải hệ phương trình – bất 
phương trình sau: 
1 2 1
1 2 2
2 2 2
1 2
2
2 3 2 0
3 2 2 0
6 0
0
0
0
x x u x
x x u x
x x s
u s
s
u
  
  
 
1) Tạo file Excel, ví dụ HPTBPT1.xlsx 
2) Đặt tên bài toán ở đầu file để biết được nội dung bài toán 
12 
3) Ghi các biến và biểu thức của các phương trình và bất phương 
trình trong các ô. Mục đích để hiểu nội dung bài toán (hình thức) 
Để viết được như cột A thì phải 
thiết lập cột A ở dạng text. Ta bôi 
đen toàn bộ cột A, ấn chuột phải, 
Number > chọn Text > OK 
13 
4) Ta nhập những giá trị bất kz cho các tham biến. Đây được coi là các 
điểm khởi đầu trong việc tìm kiếm các lời giải. Những giá trị này về sau 
sẽ thay đổi thành lời giải sau khi Excel giải xong bài toán. 
5) Gán các k{ hiệu cho các tham biến thiết kế và hằng số tại ô giá trị: 
14 
15 6) Từ ô B11 đến B16 nhập công thức chính thức Enter 
Để chọn biến nào, 
như x1, x2 hay u ta có 
thể dùng chuột ấn 
vào các ô B5, B6 hoặc 
B7 
16 
17 
7) Từ ô C11 đến C16 nhập giá trị và bất đẳng thức (Mang tính chất hình thức để 
người đọc hiểu được phương trình/bất phương trình) 
8) Vì đây là 1 hệ phương trình - bất phương trình tổng hợp, nên ta cần chọn ra 1 
phương trình để đại diện làm mục tiêu hướng tới của bài toán. Giả sử ta chọn 
phương trình 1 (đầu tiên) làm mục tiêu hướng tới 
18 9) Chọn các tham biến điều khiển để đạt được mục tiêu đề ra là phương trình 
đầu bằng 0 
10) Bổ sung thêm 5 ràng buộc là 5 phương 
trình và bất phương trình còn lại của bài 
toán: 
19 
11) Giải bài toán: 
20 
12) Xem kết quả: 
Lời giải 
Giá trị thực của các biểu thức 
PT-BPT 
21 
13) Xuất Report: 
22 
TỐI ƯU HÓA KHÔNG RÀNG BUỘC 
Tìm cực tiểu của 
hàm số sau: 
 2 2 21 2 3 1 2 3 1 2 2 3, , 2 2 2 2f x x x x x x x x x x 
1) Tạo file Excel, ví dụ TUHKRB1.xlsx 
Làm tương tự các bước ở 
các bài phía trên ở những 
nội dung viết hình thức, để 
người đọc nhìn vào hiểu 
được bài toán. 
23 
Tương tự cho x2, x3 
2) Gán các k{ hiệu cho các tham biến thiết kế và hằng số tại ô giá trị: 
24 3) Nhập công thức chính thức của hàm mục tiêu 
25 4) Xác định hàm mục tiêu và các tham biến điều khiển trong Excel 
26 
27 5) Xem kết quả: 
28 6) Xuất Report: 
29 
QUY HOẠCH TUYẾN TÍNH 
Giải bài toán QHTT sau: 
1 2
1 2
1 2
1 2
1 2
4 max
2 5
2 4
1
, 0
f x x
x x
x x
x x
x x
1) Tạo file Excel, ví dụ QHTT1.xlsx 
Làm tương tự các bước 
ở các bài phía trên ở 
những nội dung viết 
hình thức, để người 
đọc nhìn vào hiểu được 
bài toán. 
30 
Tương tự cho x2 
2) Gán các k{ hiệu cho các tham biến thiết kế và hằng số tại ô giá trị: 
31 3) Nhập công thức chính thức của hàm mục tiêu và các ràng buộc 
32 4) Xác định hàm mục tiêu và các tham biến điều khiển trong Excel 
33 5) Xác định các ràng buộc 
Tương tự cho các ràng buộc 2 và 3 
34 6) Giải bài toán 
35 7) Xem kết quả: 
36 8) Xuất Report: 
37 9) Nếu muốn bổ sung điều kiện các tham biến là số nguyên: 
38 
39 
40 10) Xem kết quả số nguyên: 
41 11) Xem Report: 
42 TỐI ƯU HÓA HÀM NHIỀU BIẾN PHI TUYẾN 
VỚI RÀNG BUỘC 
Thiết kế dầm tấm với mặt cắt như hình vẽ sao cho khối lượng dầm 
nhỏ nhất và thỏa mãn các điều kiện kỹ thuật. Cho biết toàn bộ các 
công thức tính toán. 
L=25 m – chiều dài dầm 
E=210 GPa – môđun đàn hồi dầm 
σy=262 MPa – giới hạn chảy 
σa=0.55σy=144.1 MPa – ƯS uốn cho phép 
τa=0.33σy=86.46 MPa – ƯS cắt cho phép 
σt=255 MPa – ƯS mỏi cho phép 
Da=L/800 m – độ võng cho phép 
Pm=104 kN – lực tập trung cho mômen uốn 
Ps=155 kN – lực tập trung cho lực cắt 
n=1+50/(L+125) – hệ số ảnh hưởng tải trọng 
trực tiếp 
Số liệu: 
 , , ,f wh b t t x
43 1. Diện tích mặt cắt ngang của dầm: 22 , mw fA h t b t  
2. Mômen quán tính chính trung tâm: 
 33
4
22
, m
12 3 2
f ffw
bt h h tbtt h
I
3. Tải phân bố của dầm: 19 77 , kN mw A 
4. Mômen uốn: 
 2
, kN m
8
mP wL L
M
 
5. Ứng suất uốn: , MPa
1000 2
f
M h
t
I

6. Ứng suất ổn định mặt bích: 
2
72845 , MPa
f
f
t
b

7. Ứng suất cong vênh lòng mc: 
2
3648276 , MPaww
t
h

8. Lực cắt: 0.5 , kNsS P wL 
9. Độ võng lớn nhất trong dầm: 
 38 5
, m
384 6
mP wL L
D
e EI

10. Ứng suất cắt trung bình: , MPa
1000 w
S
ht
 
44 Cực tiểu hóa thể tích vật liệu của dầm: 
 32 min, mw fV A L h t b t L    
1. Ứng suất uốn không được vượt quá ứng suất uốn cho phép: a  
2. Ứng suất uốn không được vượt quá ứng suất ổn định mặt bích : 
f  
3. Ứng suất uốn không được vượt quá ứng suất cong vênh lòng mc : 
w  
4. Ứng suất cắt trung bình không được vượt quá ứng suất cắt cho 
phép : 
a  
5. Độ võng lớn nhất trong dầm không được vượt quá độ võng cho 
phép: 
aD D 
6. Ứng suất uốn không được vượt quá một nửa của ứng suất mỏi: 
2
t 
45 
?
0.3 2.5
0.3 2.5
0.01 0.1
0.01 0.1
f
h
b
t
t
1) Tạo file Excel, ví dụ TUHPTRB1.xlsx 
Nhập dữ liệu 
của bài toán 
46 2) Gán các k{ hiệu cho các tham biến thiết kế và hằng số tại ô giá trị: 
Tương tự cho b, tf, tw, L, E, sigma_y, sigma_t, Pm,Ps 
Kiểm tra và quản l{ 
toàn bộ các tham 
biến trong bài toán 
47 
3) Xây dựng các công thức cho các đại lượng dựa trên các tham biến 
thiết kế và hằng số đã có 
48 
4) Với mỗi 1 công thức được xây dựng nó sẽ tính ra được giá trị của 
nó. Sau đó ta cũng lại gán giá trị cũng như công thức đó thành tên 
của 1 đại lượng khác. 
 Như dưới hình ta gán công thức tính diện tích vào đại lượng có k{ 
hiệu là A, tương tự I, v.v 
49 
Tức là bản thân những công 
thức phụ thuộc vào các tham 
biến sẽ tính ra các con số. Ta lại 
gán các con số tính được đó cho 
các k{ hiệu của các đại lượng 
mới đó như A, I, w, v.v để sử 
dụng những đại lượng mới đó 
cho các công thức tiếp theo. Cứ 
như vậy cho những bài toán 
phức tạp. 
50 5) Hàm mục tiêu: 
6) Các ràng buộc: 
7) Khai báo cho Excel Solver biết đâu là mục tiêu, đâu là tham biến 
điều khiển và đâu là ràng buộc: 
51 a) Hàm mục tiêu 
52 b) Tham biến điều khiển: 
53 c) Ràng buộc về ngưỡng trái và ngưỡng phải của tham biến 
54 
55 d) Ràng buộc phiếm hàm 
56 8) Kết quả: 
57 9) Xem report