Giải toán trên EXCEL

Chia sẻ: Huynh Thanh Tam | Ngày: | Loại File: DOC | Số trang:17

Thêm vào BST

Báo xấu

171
lượt xem 58
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Sau khi Click OK, tại vị trí con trỏ ô hiện hành (ô A22) chỉ xuất hiện số hạng ở dòng 1, cột 1 của C-1. Để hiển thị toàn bộ ma trận C-1, ta phải quét chọn khối xuất hiện của C-1(3 dòng và 3 cột), bắt đầu từ số đầu tiên vừa xuất hiện (ở đây ta quét chọn khối A22:C24).

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giải toán trên EXCEL

Chương 6 Giải toán trên EXCEL 6.1. ĐẠI SỐ MA TRẬN Xét các ma trận A, B và C ở bảng tính sau: 1. Lập ma trận chuyển vị (Transpose Matrix) của A: AT Các bước thực hiện: • Quét chọn khối ma trận A (vùng A3:D5) • Thực hiện lệnh Edit – Copy (hoặc gõ Ctrl+C) • Chọn vị trí lập ma trận chuyển vị (ô A15) • Dùng lệnh Edit – Paste Special. Xuất hiện hộp thoại • Chọn Transpose, và OK. Ta có kết quả: 2. Lập ma trận nghịch đảo (Inverse Matrix) của C: C-1 Các bước thực hiện: • Chọn vị trí lập ma trận nghịch đảo (ô A22) 159
• Dùng lệnh MINVERSE (hoặc Click biểu tượng trên Toolbar. Chọn Math & Trig, rồi chọn lệnh Minverse). Xuất hiện hộp thoại: • Chọn vùng xác định ma trận C (A8:C10) trong khung Array. • Click OK. Lưu ý: Sau khi Click OK, tại vị trí con trỏ ô hiện hành (ô A22) chỉ xuất hiện số hạng ở dòng 1, cột 1 của C-1. Để hiển thị toàn bộ ma trận C-1, ta phải quét chọn khối xuất hiện của C-1(3 dòng và 3 cột), bắt đầu từ số đầu tiên vừa xuất hiện (ở đây ta quét chọn khối A22:C24). Tiếp đến gõ F2, rồi thực hiện đồng thời: Ctrl + Shift + Enter. Ta có kết quả: 3. Nhân (multiply) hai ma trận A và B: A.B Các bước thực hiện: • Chọn vị trí lập ma trận tích (ô A27) • Dùng lệnh MMULT (hoặc Click biểu tượng trên Toolbar. Chọn Math & Trig, rồi chọn lệnh MMULT). Xuất hiện hộp thoại: 160
• Chọn vùng xác định ma trận A (A3:D5) trong khung Array1; Chọn vùng xác định ma trận B (F3:H6) trong khung Array2. • Click OK. Lưu ý: Sau khi Click OK, tại vị trí con trỏ ô hiện hành (ô A27) chỉ xuất hiện số hạng ở dòng 1, cột 1 của ma trận AB. Để hiển thị toàn bộ ma trận AB, ta phải quét ch ọn kh ối xuất hiện của AB (3 dòng và 3 cột, vì A cấp 3x3 – B c ấp 4x3 ), b ắt đ ầu t ừ s ố đ ầu tiên vừa xuất hiện. Tiếp đến gõ F2, rồi thực hiện đồng thời: Ctrl + Shift + Enter. Ta có kết quả: 4. Tính định thức của ma trận (Matrix determinant) vuông C: Det C Các bước thực hiện: • Chọn vị trí tính định thức (ô F9) • Dùng lệnh MDETERM (hoặc Click biểu tượng trên Toolbar. Chọn Math & Trig, rồi chọn lệnh MDETERM). Xuất hiện hộp thoại: 161
• Chọn vùng xác định ma trận C (A8:C10) trong khung Array. • Click OK. 6.2. GIẢI HỆ PHƯƠNG TRÌNH TUYẾN TÍNH Dùng lệnh Solver trong Menu Tools của Excel. Nếu trong trường hợp trong Menu Tools chưa có lệnh này, ta thực hiện các thao tác sau: Dùng lệnh: Tools | Add-Ins… Xuất hiện hộp thoại Add-Ins: 162
• Click chọn mục Solver Add-in. • Click nút OK. Trong Menu Tools sẽ xuất hiện lệnh Solver. Ví dụ: Giải hệ phương trình tuyến tính: 2 x + y + z = 2  x + y + 3z = 5    x + 5 y + z = −7 2 x − 3 y + 3z = 14  a) Trình bày bài toán trên bảng tính Excel: • A1:E1 và B7:C7 là dòng tiêu đề. • A2:A5 là các hệ số của x; B2:B5 là các hệ số của y, C2:C5 là các hệ số của z. • B8:B10 là tên các ẩn số. • C8:C10 là giá trị ban đầu của ẩn số. Sau khi giải xong, vùng này là nghi ệm c ủa phương trình tương ứng với các ẩn số. • Cột trái để trống. 163
• Cột phải (E2:E5) là các giá trị vế phải của hệ phương trình. b) Các bước giải bài toán: Bước 1: • Đánh dấu khối cột trái (D2:D5). • Dùng lệnh nhân ma trận: MMULT(A2:C5,C8:C10) (hoặc Click biểu tượng trên Toolbar. Chọn Math & Trig, rồi chọn lệnh MMULT), gõ F2, rồi ấn tổ hợp phím Ctrl + Shift + Enter. Bước 2: • Click chuột vào ô D2. • Gọi Solver từ menu Tools. Nhập các tham số trong cửa sổ Solver parameters như sau: Set Target Cell: Do chúng ta để ô định vị tại D2, nên sẽ hiển thị $D$2. Nếu chưa đúng phải gõ chính xác địa chỉ tuyệt đối này. Equal To: Click chuột đánh dấu Value of, và gõ vào khung bên cạnh giá trị là 2 (vì chúng ta phải giải sao cho vế bên trái bằng vế bên phải). 164
Subject to the Constraints: Đây là nơi ta xác định các điều kiện ràng buộc để thoả mãn cách giải bài toán trên (điều kiện là toàn bộ giá trị cột trái bằng giá tr ị c ột phải). Click nút Add, xuất hiện hộp thoại Add Constraints, và nhập vào các tham số như sau: Click OK để trở lại hộp Solver Parameters. • Sau khi đưa vào các tham số của hộp thoại Solver, Click vào nút chọn Solver. Nếu kết quả tốt, Excel thông báo là “found a solution”: Hãy chọn Keep Solver Solution để lưu kết quả trên bảng tính (nếu chọn Restore Original Values sẽ huỷ kết quả Solver vừa tìm được và trả lại giá trị khởi động của các biến). Kết quả trên bảng tính như sau: Nhìn trên bảng tính, ta thấy các giá trị trong c ột trái bằng đúng các giá trị trong cột phải. Cột khởi động đã thay đổi, mỗi giá trị mới ứng với mỗi ẩn số. Vậy nghiệm của hệ phương trình là: x = 1; y = -2, z = 2. ̣ ́ 6.3. QUI HOACH TUYÊN TÍNH 165
Qui hoach tuyên tinh là bai toan tim cực tiêu (hay cực đai) cua môt ham tuyên tinh f(x) ̣ ́́ ̀ ́̀ ̉ ̣ ̉ ̣̀ ́́ trên tập hợp D ⊂ Rn được xac đinh bởi môt hệ các phương trinh và/hoặc bât phương trinh ́ ̣ ̣ ̀ ́ ̀ tuyên tinh cho trước. ́́ ̀ ́ ̉ ́ 1. Bai toan QHTT tông quat Tim cac biên số x1, x2,..., xn sao cho: ̀ ́ ́ n ∑c x j → min (hay max) f(x) = (6.1) j j =1 ̉ ̃ ̀ ̣ thoa man điêu kiên n ∑a x j ≤ bi, i = 1, ... , m1, (6.2) ij j =1 n ∑a x j ≥ bi, i = m1 + 1, ... , m1 + m2, (6.3) ij j =1 n ∑a x j = bi, i = m1 + m2 + 1, ... , m, (6.4) ij j =1 xj ≥ 0, j = 1,..., n1, xj ≤ 0, j = n1 + 1,..., n1 + n2 ≤ n. (6.5) ̀ ́ Trong bai toan trên: • f goi là ham muc tiêu, môi hệ thức ở (6.2) - (6.5) goi là môt rang buôc. ̣ ̀ ̣ ̃ ̣ ̣̀ ̣ • Điêm x = (x1, x2, ..., xn) ∈ Rn thoa man moi rang buôc goi là môt phương an. ̉ ̉ ̃ ̣̀ ̣ ̣ ̣ ́ • Tâp hợp tât cả cac phương an, goi là miên rang buôc. ̣ ́ ́ ́ ̣ ̀ ̀ ̣ • Môt phương an thoả man (6.1) goi là phương an tôi ưu hay môt lời giai cua bai toan ̣ ́ ̃ ̣ ́́ ̣ ̉̉ ̀ ́ đã cho. 2. Giải bài toán QHTT bằng Excel Dùng lệnh Solver trong Menu Tools của Excel. a. BAI TOAN LÂP KẾ HOACH SAN XUÂT ̀ ́ ̣ ̣ ̉ ́ Môt xí nghiêp dự đinh san xuât hai loai san phâm là S 1 và S2. Để lam được môt đơn ̣ ̣ ̣ ̉ ́ ̣̉ ̉ ̀ ̣ vị S1 cân 4 đơn vị vât liêu V1, 5 đơn vị vât liêu V2. Để lam được 1 đơn vị S2 cân 3 đơn vị V1, ̀ ̣ ̣ ̣ ̣ ̀ ̀ 2 đơn vị V2. Giá ban môt đơn vị S1 là 50 ́ ̣ ngan đông, môt đơn vị S2 là 30 ngan đông. ̀ ̀ ̣ ̀ ̀ Hoi xí nghiêp nên san xuât bao nhiêu ̉ ̣ ̉ ́ đơn vị san phâm S1 và S2 để tông thu nhâp ̉ ̉ ̉ ̣ là lớn nhât, biêt răng xí nghiêp chỉ có 1200 ́ ́̀ ̣ đơn vị vât liêu V1 và 1080 đơn vị vât liêu ̣ ̣ ̣ ̣ V2. • Mô hinh toan học. ̀ ́ 166
Goi x1, x2 lân lượt là số đơn vị san phâm S 1, S2 cân san xuât. Số đơn vị vât liêu V1 ̣ ̀ ̉ ̉ ̀ ̉ ́ ̣ ̣ cân có là 4x1 + 3x2. Do xí nghiêp chỉ có 1200 đơn vị vât liêu V1 nên x1 và x2 phai thoa man ̀ ̣ ̣ ̣ ̉ ̉ ̃ 4x1 + 3x2 ≤ 1200. Tương tự, số đơn vị vât liêu V2 cân có là 5x1 + 2x2, vì thế x1 và x2 phai thoả man ̣ ̣ ̀ ̉ ̃ 5x1 + 2x2 ≤ 1080. Tât nhiên ta con phai có x1 ≥ 0 và x2 ≥ 0. ́ ̀ ̉ Tông thu nhâp cua xí nghiêp (cân lam cực đai) sẽ là f = 50x1 + 30x2 (ngan đông). ̉ ̣ ̉ ̣ ̀̀ ̣ ̀ ̀ Vây bai toan đăt ra được phat biêu thanh: Tim cac biên số x1 và x2 sao cho ̣ ̀ ́ ̣ ́ ̉ ̀ ̀ ́ ́ f = 50x1 + 30x2 → max, vơi cac điêu kiên ́́ ̀ ̣ 4x1 + 3x2 ≤ 1200, 5x1 + 2x2 ≤ 1080, x1 ≥ 0, x2 ≥ 0. • Giải pháp bảng tính: Để giải được một bài toán quy hoạch tuyến tính, đầu tiên ta ph ải xây d ựng mô hình bài toán vào bảng tính. Bảng tính này phải thể hiện rõ các bi ến s ố đầu vào, các h ạn chế, các ràng buộc của các biến số đó. Bước 1: Xây dựng mô hình sản xuất trên bảng tính và nhập vào một giải pháp ban đầu bất kỳ: Ta nhập vào một giải pháp ban đầu (S1,S2) = (10,10). Nhập lần lượt giá trị 10 vào các ô C2, D2. Kết quả cho thấy tổng thu nhập 167
của giải pháp sản xuất này đạt 800 ngàn ( tuy nhiên vẫn chưa sử dụng hết vật liệu trong kho). Bước 2: Tối ưu hoá. Gọi Solver từ menu Tools. Nhập vào mục tiêu và các ràng buộc của bài toán trong cửa sổ Solver parameters như sau: Tóm tắt tham số yêu cầu của Solver Thành phần của Solver Tham số nhập vào Nằm trong ô Hàm mục tiêu 1 Set Target Cell C9 2 Equal To Max, Min, Value of Max (loại bài toán) Các biến 3 By Changing Cells C2:D2 Các ràng buộc 4 Subject to the Constraints E6:E7, B6:B7 Sau khi đưa vào các tham số của hộp thoại Solver, Click vào nút chọn Solver để Excel bắt đầu giải bài toán. Kết quả tìm được ở hình sau: 168
b. BAI TOAN XAC ĐINH KHÂU PHÂN THỨC ĂN ̀ ́ ́ ̣ ̉ ̀ Môt xí nghiêp chăn nuôi cân mua hai loai thức ăn tông hợp T 1, T2 cho gia suc với tỉ ̣ ̣ ̀ ̣ ̉ ́ lệ chế biên: 1 kg T1 chứa 3 đơn vị dinh dưỡng D 1 (chât beo), 1 đơn vị dinh dưỡng D 2 ́ ́ ́ (Hyđrat cacbon) và 1 đơn vị dinh dưỡng D 3 (Protein); 1 kg T2 chứa 1 đơn vị D1, 1 đơn vị D2 và 2 đơn vị D3. Môi bữa ăn cho gia suc cân tôi thiêu 60 đơn vị D 1, 40 đơn vị D2 và 60 đơn vị ̃ ́ ̀ ́ ̉ D3. Hoi xí nghiêp cân mua ̉ ̣ ̀ bao nhiêu kg T1, T2 cho môi bữa ̃ ăn, sao cho vừa đam bao tôt dinh ̉ ̉ ́ dưỡng cho bữa ăn cua gia suc, ̉ ́ vừa để tông số tiên chi mua thức ̉ ̀ ăn là nhỏ nhât. Cho biêt 1 kg T1 ́ ́ giá 20 ngan đông, 1 kg T2 giá 15 ̀ ̀ ̀ ̀ ngan đông. ̀ ́ ̣ a) Mô hinh toan hoc. Goi x1, x2 lân lượt là số kg thức ăn T1, T2 cân mua cho môi bữa ăn. Số đơn vị chât D1 ̣ ̀ ̀ ̃ ́ có trong môi bữa ăn là 3x1 + x2, vì thế x1 và x2 cân thoa man ̃ ̀ ̉ ̃ 3x1 + x2 ≥ 60, Tương tự, để đap ứng nhu câu về chât D2 và D3 cho môi bữa ăn, x1 và x2 cân thoa man ́ ̀ ́ ̃ ̀ ̉ ̃ x1 + x2 ≥ 40, x1 + 2x2 ≥ 60, ́ ̃ ̀ ̉ Tât nhiên, ta cung đoi hoi x1 ≥ 0 và x2 ≥ 0. Số tiên chi mua thức ăn (cân lam cực tiêu) băng f = 20x1 + 15x2 (ngan đông). ̀ ̀̀ ̉ ̀ ̀ ̀ Vây bai toan nêu trên được phat biêu thanh: Tim cac biên số x1 và x2 sao cho ̣ ̀ ́ ́ ̉ ̀ ̀ ́ ́ 169
f = 20x1 + 15x2 → min, vơi cac điêu kiên ́́ ̀ ̣ x2 ≥ 60, 3x1 + x2 ≥ 40, x1 + x1 + 2x2 ≥ 60, x1 ≥ 0, x2 ≥ 0. b) Giải pháp bảng tính: Bước 1: Xây dựng mô hình bài toán trên bảng tính và nhập vào một giải pháp ban đầu bất kỳ: Ta nhập vào một giải pháp ban đầu (T1, T2) = (20,20). Kết quả cho thấy tổng số ti ền chi mua thức ăn của giải pháp này đạt 700 ngàn. Bước 2: Tối ưu hoá. 170
Gọi Solver từ menu Tools. Nhập vào mục tiêu và các ràng buộc của bài toán trong cửa sổ Solver parameters như sau: Kết quả tìm được ở hình sau: ̀ ́ ́ ̣ ̣ c. BAI TOAN PHA CĂT VÂT LIÊU Trong thực tế ta thường phai căt những vât liêu dai (thanh thep, ông nước, băng giây ...) ̉́ ̣ ̣ ̀ ́ ́ ́ có độ dai cho trước thanh những đoan ngăn hơn với số lượng nhât đinh để sử dung. Nên căt ̀ ̀ ̣ ́ ̣́ ̣ ́ như rhế nao cho tôn it vât liêu nhât ? ̀ ́́ ̣ ̣ ́ Ví dụ: Phân xưởng thép của công ty xây lắp mua thép xây dựng từ Trung tâm gang thép Thái Nguyên phục vụ nhu cầu xây lắp công trình. Thép được cung c ấp d ưới dạng t ừng thanh, chiều dài là 20 m. Theo yêu cầu của bộ phận xây dựng, Phân xưởng thép phải cung c ấp 150 đo ạn thép dài 5 mét, 200 đoạn thép dài 7 mét, 300 đoạn thép dài 9 mét. Đ ể th ực hi ện yêu c ầu này, Phân xưởng phải cắt mỗi thanh thép 20 mét ra làm nhi ều đo ạn con có chi ều dài ho ặc 171
5 mét, hoặc 7 mét, hoặc 9 mét. Việc cắt này sẽ để lại các mẫu thép th ừa không th ể s ử dụng. Có 6 phương án cắt được trình bày trong bảng sau: Hoi phân xưởng cần nhập về bao nhiêu thanh thép 20 mét và c ắt theo ph ương án nào ̉ để thép thừa là ít nhất. a) Mô hinh toan hoc. Goi xj (j = 1,…,6) là số thanh thep (dài 20 m) cân căt theo phương án j. ̀ ́ ̣ ̣ ́ ̀ ́ Bai toan trên sẽ được phat biêu thanh: Tim cac biên số x1, x2, x3, x4, x5, x6 sao cho ̀ ́ ́ ̉ ̀ ̀ ́ ́ f (tông số thep thừa) = 4x1 + 3x2 + x3 + x5 + 2x6 → min, ̉ ́ vơi cac điêu kiên ́́ ̀ ̣ 2x2 + 2x3 + 4x4 + x5 = 150. x1 + x2 + 2x5 = 200, x1 + x3 + 2x6 = 300, xj ≥ 0, j =1, …,6. b) Giải pháp bảng tính: Bước 1: Xây dựng mô hình bài toán trên bảng tính và nhập vào một giải pháp ban đầu bất kỳ Bước 2: Tối ưu hoá. Gọi Solver từ menu Tools. Nhập vào mục tiêu và các ràng buộc của bài toán trong cửa sổ Solver parameters như sau: 172
Kết quả tìm được ở hình sau: BÀI TẬP Bài 1. Tìm ma trận chuyển vị của các ma trận: 2 3 1  2 1 − 2 3 4 − 2 2    B=  a) A = − 1 5 2 4 b) 2 4 − 1  2 1 3 6     3 4 2 Bài 2. Tìm ma trận nghịch đảo (nếu có) của các ma trận:  3 − 1 0 1 1 1     a) A = 1 2 4 ; b) B = − 2 1 1  ;  2 − 1 4  1 3 9    Bài 3. Tìm ma trận tích AB và BA (khi chúng được xác định) 173
0 1 − 1  1 − 1 0 2  2 1 0    B=  a) A = 3 4 1 2 , 2 1 − 2 0 0 − 1 1      1 2 1  1  2 1 2 4 5  B =  b) A =  , −1 3 1 0 2  3 1 1 2  c) A =   , B=   − 2 0 − 1  2 − 1 1 − 2 − 5 A= 1 0 , B= d) 0   3 4  − 3 4    Bài 4. Tính các định thức sau: 1234 3111 2 −4 3 2341 1311 0 −2 ; 1 a) b) c) 3 4 1 2 . ; 1131 −2 3 3 1113 4123 Bài 5. Giải các hệ phương trình tuyến tính sau: x + 3 y + z = 5  x − y + z = −2 2 x + y + z = 2   a) 2 x + y − 2 z = 6 b)  − x − y − 5 z = 7  x + 2 y + 3z = 2  2 x + 3 y − 3 z = 14  Bài 6. Môt xí nghiêp đong tau đanh cá cân đong hai loai tau 100 mã lực và 50 ma ̃ l ực. ̣ ̣ ́ ̀ ́ ̀ ́ ̣̀ Trong xí nghiêp có ba loai thợ chinh quyêt đinh san lượng kế hoach. Thợ ren co ́ 2000 công, ̣ ̣ ́ ̣́ ̉ ̣ ̀ thợ săt có 3000 công và thợ môc có 1500 công. Đinh mức lao đông cho môi loai tau đ ược ́ ̣ ̣ ̣ ̃ ̣̀ ̉ cho trong bang sau: Đinh mức ̣ ̣̀ Loai tau 100 mã lực 50 mã lực ̣ lao đông Loai thợ ̣ Thợ săt (3000) ́ 150 70 Thợ ren (2000) ̀ 120 50 Thợ môc (1500) ̣ 80 40 ̉ ̉ (công/san phâm) Hoi xí nghiêp nên đong tau môi loai bao nhiêu để đat tông số mã lực cao nhât ? ̉ ̣ ́ ̀ ̃ ̣ ̣̉ ́ Bài 7. Môt xí nghiêp có thể sử dung tôi đa 510 giờ may can, 360 gi ờ may tiên va ̀ 150 gi ờ ̣ ̣ ̣ ́ ́ ́ ́ ̣ 174
may mai để chế tao ba loai san phâm A, B và C. Để chê ́ tao môt đ ơn vi ̣ san phâm A cân 9 ́ ̀ ̣ ̣̉ ̉ ̣ ̣ ̉ ̉ ̀ giờ may can, 5 giờ may tiên, 3 giờ may mai; môt đơn vị san phâm B cân 3 gi ờ may can, 4 ́ ́ ́ ̣ ́ ̀ ̣ ̉ ̉ ̀ ́ ́ giờ may tiên; môt đơn vị san phâm C cân 5 giờ may can, 3 giờ may tiên, 2 gi ờ may mai. ́ ̣ ̣ ̉ ̉ ̀ ́ ́ ́ ̣ ́ ̀ Môi san phâm A trị giá 48 ngan đông, môi san phâm B trị giá 16 ngan đông và môi san phâm ̃̉ ̉ ̀ ̀ ̃̉ ̉ ̀ ̀ ̃̉ ̉ C trị giá 27 ngan đông. ̀ ̀ Vân đề đăt ra là xí nghiêp cân chế tao bao nhiêu đơn vi ̣ san phâm môi loai đê ̉ tông sô ́ ́ ̣ ̣ ̀ ̣ ̉ ̉ ̃ ̣ ̉ giá trị san phâm xí nghiêp thu được là lớn nhât, với điêu kiên không dung quá số giờ hiên có ̉ ̉ ̣ ́ ̀ ̣ ̀ ̣ ̉ ̃ ̣ ́ cua môi loai may Bài 8. Một xưởng làm cửa sắt có những thanh thép dài 12 mét, c ần c ắt thành 8 đo ạn dài 4 mét, 5 đoạn dài 5 mét và 3 đoạn dài 7 mét. Có 5 mẫu cắt sau: • Mẫu 1: 3 đoạn 4 mét, không thừa. • Mẫu 2: 1 đoạn 4 mét và 1 đoạn 5 mét, thừa 3 mét. • Mẫu 3: 1 đoạn 4 mét và 1 đoạn 7 mét, thừa 1 mét. • Mẫu 4: 2 đoạn 5 mét, thừa 2 mét. • Mẫu 5: 1 đoạn 5 mét và 1 đoạn 7 mét, không thừa. Vấn đề đặt ra của xưởng là tìm các mẫu cắt tiết kiệm nhất? 175