Download miễn phí Bài giảng Giải các bài toán tối ưu và thống kê trên Microsoft Excel
Mục lục
Chương 1. Giải các bài toán quy hoạch toán học trên Microsoft Excel.3
1.1. Bài toán quy hoạch tuyến tính có một chỉ số .3
1.2. Bài toán quy hoạch tuyến tính có hai chỉ số .5
1.3. bài toán quy hoạch phi tuyến .7
Bài tập .8
Chương 2. Giải các bài toán thống kê trên Microsoft Excel.10
2.1. Hồi quy tuyến tính bội .10
2.2. Hồi quy tuyến tính đơn .12
2.3. Hồi quy mũ .12
Bài tập .13
Để tải bản Đầy Đủ của tài liệu, xin Trả lời bài viết này, Mods sẽ gửi Link download cho bạn sớm nhất qua hòm tin nhắn.
Ai cần download tài liệu gì mà không tìm thấy ở đây, thì đăng yêu cầu down tại đây nhé:
Nhận download tài liệu miễn phí
Tóm tắt nội dung tài liệu:
Bài giảngGiải các bài toán tối −u và thống kê
trên Microsoft Excel
PGS. TS. Bùi Thế Tâm
Phòng Tối −u và Điều khiển
Viện Toán học
Viện Khoa học và Công nghệ Việt nam
Tóm tắt . Microsoft Excel 2000, 2003 có các công cụ toán học rất mạnh để
giải các bài toán tối −u và thống kê toán học. Excel có thể giải đ−ợc các loại bài
toán tối −u: bài toán quy hoạch tuyến tính tổng quát, các biến có thể có ràng buộc
hai phía, ràng buộc cũng có thể viết ở dạng hai phía; bài toán vận tải có hai chỉ số;
bài toán quy hoạch nguyên (các biến có điều kiện nguyên hay boolean); bài toán
quy hoạch phi tuyến. Số biến cúa bài toán quy hoạch tuyến tính hay nguyên có thể
lên tới 200 biến. Excel còn có thể giải các bài toán hồi quy trong thống kê toán
học: hồi quy đơn, hồi quy bội, hồi quy mũ.
Ch−ơng 1 có thể dạy bổ sung vào sau giáo trình Quy hoạch tuyến tính
hay Quy hoạch nguyên ở bậc đại học để sinh viên có thể giải ngay trên máy tính
các bài toán tối −u cỡ lớn phát sinh trong thực tiễn mà không cần lập trình.
Ch−ơng 2 có thể dạy bổ sung vào sau giáo trình Xác suất thống kê ở bậc đại
học để sinh viên có thể tính ngay đ−ợc các bài toán hồi quy trên máy tính. Cả hai
ch−ơng này đều có thể dạy cho sinh viên ngay sau phần Excel của môn Tin học
văn phòng. Đây là bài giảng của tác giả cho sinh viên một số tr−ờng kinh tế và kỹ
thuật.
Vài nét về tác giả. B.T.Tâm hiện làm việc tại Phòng Tối −u và Điều khiển
thuộc Viện Toán học, Viện khoa học và công nghệ Việt nam, bảo vệ Tiến sỹ năm
1978 tại Viện hàn lâm Khoa học Liên xô. Địa chỉ liên hệ: Bùi Thế Tâm, Viện Toán
học, 18 Hoàng Quốc Việt, 10307 Hà Nội. Địa chỉ email: [email protected]. Điện
thoại cơ quan: 7.563.474, số máy lẻ 211.
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
2
Mục lục
Ch−ơng 1. Giải các bài toán quy hoạch toán học trên Microsoft Excel ........................3
1.1. Bài toán quy hoạch tuyến tính có một chỉ số ...............................................................3
1.2. Bài toán quy hoạch tuyến tính có hai chỉ số ................................................................5
1.3. bài toán quy hoạch phi tuyến .......................................................................................7
Bài tập .................................................................................................................................8
Ch−ơng 2. Giải các bài toán thống kê trên Microsoft Excel ........................................10
2.1. Hồi quy tuyến tính bội ...............................................................................................10
2.2. Hồi quy tuyến tính đơn ..............................................................................................12
2.3. Hồi quy mũ ................................................................................................................12
Bài tập ...............................................................................................................................13
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
3
Ch−ơng 1
Giải các bài toán
quy hoạch toán học trên
Microsoft Excel
Dùng Solver ta có thể tìm cực đại hay cực tiểu của một hàm số đặt trong một ô gọi là
ô đích. Solver chỉnh sửa một nhóm các ô (gọi là các ô có thể chỉnh sửa) có liên quan trực
tiếp hay gián tiếp đến công thức nằm trong ô đích để tạo ra kết quả. Ta có thể thêm vào các
ràng buộc để hạn chế các giá trị mà Solver có thể dùng. Đối với bài toán quy hoạch tuyến
tính Solver dùng ph−ơng pháp đơn hình, đối với quy hoạch phi tuyến Solver dùng ph−ơng
pháp tụt gradient để tìm một cực trị địa ph−ơng.
1.1. Bài toán quy hoạch tuyến tính có một chỉ số
Xét bài toán quy hoạch
minmax / )(2211 →=+++ xfxcxcxc nn" (1)
11212111 Q bxaxaxa nn+++ "
22222121 Q bxaxaxa nn+++ "
"""""""""
mnmnmm bxaxaxa Q2211 +++ "
=
=
≥
1)or (0binary
interger
0
jx j = 1, . . . , n
trong đó Q là một trong các phép toán quan hệ =≤≥ , thứ tự các phép toán quan hệ
trong các ràng buộc là tuỳ ý. Nh− vậy bài toán (1) có thể là bài toán quy hoạch tuyến tính
thông th−ờng, quy hoạch tuyến tính nguyên hay quy hoạch boolean.
Cách bố trí dữ liệu cho trên bảng tính:
c[1] c[2] . . . . . . c[n] ∑ c[j] x[j]
a[1,1] a[1,2] . . . . . . a[1,n] ∑ a[1,j] x[j] b[1]
a[2,1] a[2,2] . . . . . . a[2,n] ∑ a[2,j] x[j] b[2]
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
a[m,1] a[m,2] . . . . . . a[m,n] ∑ a[m,j] x[j] b[m]
x[1] x[2] . . . . . . x[n]
Hàng cuối cùng là các giá trị ban đầu của các biến để các công thức của Excel hoạt
động, có thể lấy giá trị của tất cả các biến bằng 1.
Xét bài toán:
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
4
min4 321 →++ xxx (2)
20432 321 ≥++ xxx
1225 321 ≥+− xxx
22 321 ≤−+ xxx
124 321 ≤−+− xxx
0,, 321 ≥xxx
Các b−ớc thực hiện để giải bài toán:
B−ớc 1. Nhập dữ liệu bài toán vào bảng tính d−ới dạng sau:
Ph−ơng án ban đầu X = (1, 1, 1), nó có thể không chấp nhận đ−ợc.
B−ớc 2. Tính giá trị hàm mục tiêu tại ô E2 bằng công thức
= SUMPRODOCT($B$7 : $D$7, B2 : D2)
Hàm Sumproduct cho tích vô h−ớng của hai dãy ô. Copy công thức từ ô E2 sang dãy các ô
E3 : E6 nhằm tính giá trị vế trái của bốn ràng buộc bài toán (1).
B−ớc 3. Dùng lệnh Tools / Solver, xuất hiện hộp thoại Solver Parameters.
Mục Set Target Cell: chọn ô đích (chứa giá trị hàm mục tiêu), có thể nháy vào biểu t−ợng
của Excel bên phải hộp văn bản để xác định ô, trong ví dụ chọn ô E2. Mục Equal To: chọn
Max nếu cực đại hàm mục tiêu, chọn Min nếu cực tiểu hàm mục tiêu, chọn Value of và
nhập giá trị nếu muốn ô đích bằng một giá trị nhất định, trong ví dụ chọn Min. Mục By
Changing cells: chọn các ô chứa các biến của bài toán, ta chọn khối ô B7
7. Nháy nút Add để nhập tất cả các ràng buộc vào khung Subject to the Constraints (dòng đầu trong
khung ứng với ràng buộc không âm trên các biến, dòng thứ hai ứng với hai ràng buộc đầu
bài toán (2), dòng cuối ứng với 2 ràng buộc cuối). Khi nháy nút Add, hiện hộp thoại
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
5
Hộp văn bản Cell Reference để chọn các ô cần đặt ràng buộc lên chúng, hộp văn bản ở giữa
để chọn loại ràng buộc (>= = <= interger, binary), hộp văn bản Constraint để chọn giá
trị ràng buộc (có thể là số hay giá trị trong các ô).
Sau khi nhập xong các ràng buộc, nháy vào nút Options, hiện hộp thoại Solver
Options, đánh dấu kiểm vào mục Assume Linear Model (khảng định mô hình của ta là
tuyến tính).
B−ớc 4. Trong hộp thoại Solver Parameters nháy vào nút Solve để bát đầu giải bài
toán tối −u. Giải xong bài toán xuất hiện hộp thoại Solver Results, chọn mục Keep Solver
Solution (giữ lại lời giải), nháy OK, kết quả giải bài toán nằm ở các ô B7 : D7. Kết quả ta
đ−ợc ph−ơng án tối −u là X = (0.5 , 0 , 4.75), giá trị cực tiểu hàm mục tiêu là 5.25 ở ô E2.
1.2. Bài toán quy hoạch tuyến tính có hai chỉ số
Bài toán vận tải. Có m kho hàng (điểm phát) chứa một loại hàng hoá, l−ợng hàng ở
kho i là ia . Có n nơi tiêu thụ (điểm thu) loại hàng này, nhu cầu nơi j là jb . Chi phí vận
chuyển một đơn vị hàng từ điểm phát i tới điểm thu j là ijc . Xác định c...