Tóm tắt nội dung tài liệu
- Giải hệ phương trình đại số tuyến tính bằng Excel Ngoai khả năng xư lý bang tinh, Excel con có nhiêu khả năng khac mà có thể ban chưa kham ̀ ̉ ́ ̀ ̀ ́ ̣ ́ phá hêt. Bai viêt nay giơi thiêu cach dung Excel để giai hệ phương trinh đai số tuyên tinh ́ ̀ ́ ̀ ̣ ́ ̀ ̉ ̀ ̣ ́́ [HPTTT] - dang bai toan thương găp trong thưc tê, khá phưc tap vì có nhiêu ân. Để giai ̣ ̀ ́ ̣ ́ ̣ ̀ ̉ ̉ HPTTT, ơ đây dung hai phương phap: ma trân và Gauss Seidel. ̀ ́ ̣ Phương pháp ma trận Sư dung phương phap ma trân để giai HPTTT là đơn gian nhât khi sư dung Excel. HPTTT có ̣ ́ ̣ ̉ ̉ ́ ̣ ̣ dang: Ax=b trong đó A là ma trân hệ sô, x là vectơ biên số và b là vectơ kêt qua. ̣ ́ ́ ́ ̉ HPTTT đươc biên đôi thanh: ́ ̉ ̀ x=A-1b Xet hệ ba phương trinh ba ân sau: ́ ̀ ̉ -8x1 + x2 + 2x3 = 0 5x1 + 7x2 - 3x3 = 10 [*] 2x1 + x2 - 2x3 = -2 Hệ ba phương trinh nay có thể viêt dươi dang ma trân sau: ̀ ̀ ́ ̣ ̣ -8 1 2 x1 0 573 x2 = 10 212 x3 -2 Ta dễ dang tim đươc nghiêm cua HPTTT băng cach dung ham MINVERSE [tinh ma trân ̀ ̀ ̣ ̉ ̀ ́ ̀ ̀ ́ ̣ nghich đao] và MMULT [tinh tich ma trân] trong Excel. Sau đây là cac bươc giai HPTTT: ̣ ̉ ́ ́ ̣ ́ ̉ • Bươc 1: nhâp ma trân A vao cac ô A6:C8 ̣ ̣ ̀ ́ A6 -8 B6 1 C6 2
- A7 5 B7 7 C7 -3 A8 2 B8 1 C8 -2 • Bươc 2: nhâp vectơ kêt quả vao cac ô ̣ ́ ̀ ́ E6:E8 E6 0 E7 10 E8 -2 • Bươc 3: chon cac ô A11:C13, gõ công ̣ ́ thưc: =MINVERSE[A6:C8] và nhân ́ Ctrl+Shift+Enter để chen công thưc ̀ nay vao cả vung đươc lưa chon ta thu ̀ ̀ ̀ ̣ Hình 1 đươc ma trân nghich đao cua ma trân A. ̣ ̣ ̉ ̉ ̣ • Bươc 4: chon cac ô E11:E13, gõ công thưc: =MMULT[A11:C13,E6:E8] và nhân ̣ ́ ́ Ctrl+Shift+Enter để chen công thưc nay vao cả vung đươc lưa chon ta thu đươc nghiêm cua ̀ ̀ ̀ ̀ ̣ ̣ ̉ hệ ba phương trinh trên trong cac côt E11:E13 [xem hinh 1] ̀ ́ ̣ ̀ Nghiêm cua hệ phương trinh la: ̣ ̉ ̀ ̀ x1=1 x2=2 x3=3 Phương Pháp lặp Gauss-Seidel Ban chât cua phep lăp Gauss là ̉ ́ ̉ ́ ̣ nghiêm ơ bươc lăp i đươc dung để ̣ ̣ ̀ tinh cho bươc lăp i+1 con ban chât ́ ̣ ̀ ̉ ́ cua phep lăp Gauss-Seidel là kêt quả ̉ ́ ̣ ́ tinh toan ân xk đươc đưa ngay vao ́ ́ ̉ ̀ tinh toan ân xk+1 trong cung môt bươc ́ ́̉ ̀ ̣ lăp i, đây là môt bươc cai tiên đang kể ̣ ̣ ̉ ́ ́ phương phap Gauss. Ta xem xet viêc ́ ́ ̣ sư dung Excel để giai HPTTT theo ̣ ̉ phương phap Gauss-Seidel. ́ Biên đôi hệ phương trinh trên ta co: ́ ̉ ̀ ́ Sau đây là cac bươc giai HPTTT băng ́ ̉ ̀ phương phap lăp Gauss-Seidel trong ́ ̣ Excel: • Bươc 1: chon ̣ Hình 2 Tools - Options - Calculation tab và thay đôi Calculation tư Automatic thanh Manual, bỏ chon ̉ ̀ ̣ Recalculate Before Save, chon Iterations và đăt Maximum Iteration băng 1, ̣ ̣ ̀ ̀ ̀ Maximum change băng 0,001[xem hinh 2].
- • Bươc 2: trong ô B3 nhâp True, trong cac ô A8:A10 nhâp giá trị 0 [giá trị khơi tao ban đâu]. ̣ ́ ̣ ̣ ̀ • Bươc 3: trong ô B8 nhâp công thưc =[C9+2*C10]/8; trong ô B9 nhâp công thưc ̣ ̣ =[10- 5*C8+3*C10]/7; trong ô B10 nhâp công thưc =[2+2*C8+C9]/2 ̣ • Bươc 4: trong ô C8 nhâp công thưc =IF[B3=TRUE,A8,B8]; trong ô C9 nhâp công thưc ̣ ̣ =IF[B3=TRUE,A9,B9]; trong ô C10 nhâp công thưc =IF[B3=TRUE, A10,B10] ̣ Ta thây cac công thưc trong côt B tinh theo cac giá trị trong côt C, cac giá trị nay lai nhân kêt ́ ́ ̣ ́ ́ ̣ ́ ̀ ̣ ̣ ́ quả tinh toan tư côt B, như vây tư công thưc thư hai trong côt B trơ đi có thể sư dung cac giá ́ ́ ̣ ̣ ̣ ̣ ́ trị mơi tinh ơ cac công thưc trên. ́ ́ • Bươc 5: đinh dang cac ô B8:C10 là Number vơi ba số thâp phân sau dâu phây ̣ ̣ ́ ̣ ́ ̉ • Bươc 6: khi ô B3 ơ trang thai True ̣ ́ nhân F9 để tinh vơi giá trị khơi tao ban ́ ́ ̣ đâu, sau đó thay đôi trang thai ô B3 ̀ ̉ ̣ ́ thanh False và nhân F9 để lăp lai quá ̀ ́ ̣ ̣ trinh tinh toan vơi cac giá trị trong côt ̀ ́ ́ ́ ̣ ́ ̣ ́ ́ ́ C, tiêp tuc nhân F9 cho đên khi cac giá trị hôi tụ ta nhân đươc nghiêm cua ̣ ̣ ̣ ̉ hệ ba phương trinh trên trong cac ô ̀ ́ ̀ C8:C10 [xem hinh 3]. Trong trương hơp quá nhiêu bươc lăp ̀ ̣ nghia là phai nhân nhiêu lân F9 [trong ̃ ̉ ́ ̀ ̀ ví dụ trên phai lăp 10 bươc] thì ta có ̣̉ thể tăng số bươc lăp trong môt lân ̣ ̣ ̀ ́ ̀ ́ ̣ nhân F9 băng cach chon Tool s- Hình 3 Options và đăt Maximum Iteration lơn ̣ hơn 1. ̣ ́ Nhân Xet Phương phap nghich đao ma trân đơn gian nhưng chỉ phù hơp vơi hệ phương trinh có số ân ́ ̣ ̉ ̣ ̉ ̀ ̉ không quá lơn [dươi 60 ân] vơi số ân lơn hơn nên dung phương phap Gauss-Seidel. Ngoai ra ̉ ̉ ̀ ́ ̀ con nhiêu phương phap khac nhưng trong pham vi bai nay không đề câp đên, mong nhân ̀ ̀ ́ ́ ̣ ̀ ̀ ̣ ́ ̣ đươc sư đong gop ý kiên cua cac ban. ́ ́ ́ ̉ ́ ̣ 1. Hàm tính ma trận nghịch đảo Ma trận nghịch đảo A là ma trận vuông cấp n x n Nếu A không suy biến [định thức của A khác 0] thì A có ma trận nghịch đảo A1.
- Ma trận nghịch đảo được sử dụng để giải một số bài toán. Trong Excel, ma trận nghịch đảo được tính bằng hàm mảng MINVERSE. Ví dụ có ma trận A 3 x 3, dữ liệu được chứa trong vùng A1 :C3. A1 là ma trận nghịch đảo của A, cũng 3 x 3, sẽ được chứa trong vùng E1 :G3 [chọn vùng E1 :G3, gõ công thức =MINVERSE[A1 :C3], bấm tổ hợp phím CtrlShiftEnter]. [ Vấn đề là hàm MINVERSE trong Excel chỉ tính được cho ma trận có n
Page 2
YOMEDIA
Ngoaì kha ̉ năng xư lý ban̉ g tiń h, Excel coǹ có nhiêù kha ̉ năng khać ma ̀ co ́ thê ̉ baṇ chưa khaḿ phá hêt́ . Baì viêt́ naỳ giơi thiêụ cać h duǹ g Excel để giaỉ hệ phương triǹ h đaị số tuyêń tiń h [HPTTT] - daṇ g baì toań thương găp̣ trong thưc tê,́ khá phưc tap̣ vì có nhiêù ân̉ . Để giaỉ HPTTT, ơ đây duǹ g hai phương phaṕ : ma trâṇ va ̀ Gauss Seidel.
23-01-2011 1090 173
Download
Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2009-2019 TaiLieu.VN. All rights reserved.
Chào các bạn, Mình xin giới thiệu với các bạn các giải hệ phương trình trong Excel bằng Solver và dùng các hàm ma trận.
Ví dụ: Giải hệ phương trình sau:
Giải hệ phương trình dùng Solver
B1.
Xác định các biến, các hàm mục tiêu và lập mô hình trên bảng tính- Các ô trong mảng A26:C28 nhập các hệ số của các phương trình [mỗi phương trình nhập một dòng].
- Các ô D26, D27, D28 lần lượt chứa giá trị khởi động của các biến x, y, z
- Các ô F26, F27, F28 lần lượt chứa các giá trị ở vế phải của các phương trình [1], [2] và [3].
- Các ô E26, E27, E28 được tính bằng cách nhân các hệ số của phương trình với các giá trị khởi động của x, y, z [xem công thức minh họa trong hình 5.6].
Lập mô hình bài toán trên bảng tính
Ghi chú:
Cách khác để tính nhanh Vế trái của các phương trình là dùng kết hợp hàm Sumproduct [array1, array2] và hàm Transpose [array]. Cách làm như sau:- Chọn 3 ô E26 và nhập vào công thức sau:
=SUMPRODUCT[A26:C26,TRANSPOSE[$D$26:$D$28]]
- Sao chép công thức cho 2 ô còn lại E27 và E28.
- Vào thực đơn Tools à Add-Ins
Hộp thoại Add-Ins chứa các chức năng mở rộng của Excel
- Chọn Solver Add-in và chọn OK để chấp nhận.
- Đưa địa chỉ D2628 vào By Changing Cells
- Đưa các ràng buộc vào Subject to the Constraints:
Thêm ràng buộc
Nhấp nút OK để hoàn tất.
Nếu bài toán cần nhiều ràng buộc hơn thì thực hiện lại hai bước trên để nhập thêm các ràng buộc khác.
Khai báo thông số cho Solver
B3.
Nhấp nút Solve chạy tìm lời giải. Hộp thông báo sau sẽ xuất hiện:
Chọn Keep Solver Solution để lưu kết quả trên bảng tính. Chọn Restore Original Values để hủy kết quả Solver vừa tìm được và trả các biến về tình trạng ban đầu. Chọn Save Scenario để lưu kết quả vừa tìm được thành một tình huống để có xem lại sau này. Ngoài ra còn có 3 loại báo cáo là Answer, Sensitivity và Limits.
B5.
Chọn OK để hoàn tất quá trình chạy Solver.
Các nghiệm hệ phương trình là: x=4, y=-3 và z=9 TP.
Ghi chú về sử dụng Solver
Ghi chú về sử dụng Solver
Để sử dụng tốt Solver ta cần nắm vững các yêu cầu thông số cần phải khai báo cho Solver:
- Set Target Cell: Nơi đây ta cần nhập vào địa chỉ của hàm mục tiêu.
- Equal To: Hàm mục tiêu muốn đạt tới Max, Min hay Value of [bằng một giá trị mong muốn nào đó thì nhập giá trị vào.]
- By Changing Cell: Nhập vào địa chỉ chứa các biến của bài toán cần giải.
- Subject to the constraints: Nhập vào các ràng buộc của bài toán.
- Cách làm của Solver là thay đổi giá trị các biến tại By Changing Cell đến lúc nào đó làm cho giá trị hàm mục tiêu tại Set Target Cellđạt một giá trị qui định tại Equal To [Max, Min hoặc Value of] và đồng thời phải thõa mãm tập các ràng buộc tại Subject to the constraints. Thiết lập các thuộc tính cho Solver ta nhấp chuột vào nút Options, hộp thoại Solver Options xuất hiện:
Thiết lập thông số cho Solver: Chế độ mặc định
TP.
Giải hệ phương trình bằng phương pháp ma trận
[FONT="]Cách 2. Giải hệ phương trình bằng phương pháp ma trận
[/FONT] [FONT="]
Hệ phương trình trên là tương đương với phương trình ma trận sau:
[/FONT][/FONT]
- [FONT="]Các ô B7577 nhập vào hệ số của các phương trình [1], [2] và [3][/FONT]
- [FONT="]Các ô F75, F76, F77 là nhãn các nghiệm x, y, z[/FONT]
- [FONT="]Các ô H75, H76, H77 là các con số ở vế phải của các phương trình.[/FONT]
[FONT="] [/FONT]
Lập mô hình bài toán trên bảng tính
B2. Tìm ma trận nghịch đảo của ma trận hệ số A è Tìm A-1- Chọn vùng địa chỉ B8082
- Nhập vào công thức =Minverse[B7577] để nghịch đảo ma trận
- Nhấn tổ hợp phím Ctrl + Shift + Enter để thực hiện phép tính
Tính ma trận nghịch đảo
B3. Tìm nghiệm hệ phương trình
[/FONT]
- [FONT="]Chọn vùng địa chỉ B85:B87[/FONT]
- [FONT="]Nhập vào công thức =MMULT[B8082,H75:H77][/FONT]
- [FONT="]Nhấn tổ hợp phím Ctrl + Shift + Enter để thực hiện phép tính[/FONT]
Nghiệm hệ phương trình Lưu ý:Việc tính toán trên dãy số liệu [array] có một số tính chất sau:
[/FONT]- Khi nhập, xóa, chỉnh sửa công thức phải thực hiện trên toàn bộ dãy, do vậy cần chọn cả dãy trước khi thực hiện nhập, xóa hay chỉnh sửa.
- Nhấn phím F2 để vào chế độ chỉnh sửa
- Nhấn tổ hợp phím Ctrl + Shift + Enter khi hoàn tất.
Ma trận và sử dụng hàm ma trận
Ma trận và sử dụng hàm ma trận
- Kích thước ma trận được xác định theo số dòng vào số cột của ma trận, ma trận n x m đọc là n dòng và m cột.
- Hai ma trận chỉ nhân được với nhau khi số dòng cột của ma trận đứng trước bằng với số dòng của ma trận đứng sau. Ví dụ ma trận có kích thước nxp thì có thể nhân với ma trận có kích thước p x m. Dưới đây là công thức nhân hai ma trận đặc biệt có kích thước 1 x n và n x 1:
- Công thức tổng quát xác định giá trị của phần tử cij trong ma trận kết quả:
Ví dụ: Nhân hai ma trận sau:
Ví dụ về cách cách tìm các phần tử trong ma trận nghịch đảo từ ma trận
Một số yêu cầu về tính định thức và tìm nghịch đảo ma trận trong Excel:
- Phải là ma trận vuông, nếu không vuông sẽ báo lỗi #VALUE!.
- Nếu có phần tử nào trong ma trận là rỗng hoặc là chữ thì báo lỗi #VALUE!.
Cú pháp: MDETERM[array]
Array: là địa chỉ ma trận cần tính định thức
Hàm tìm ma trận nghịch đảo
Cú pháp: Minverse[array]
Array: là địa chỉ ma trận cần nghịch đảo
Nhấn tổ hợp phím Ctrl+Shift+Enter sau khi nhập xong công thức.
Hàm nhân hai ma trận
Cú pháp: MMULT[array1,array2]
Array1, array2 là địa chỉ các ma trận cần nhân.
Nhấn tổ hợp phím Ctrl+Shift+Enter sau khi nhập xong công thức. TP.
Việc giải hệ phương trình theo phương pháp Gauxơ có thể viết Code được o anh? Em có mấy bài toán dạng này mà viết code hoài o được. Nhờ anh giúp đỡ thêm........Rất mong tin anh, thank a lot!
Việc giải hệ phương trình theo phương pháp Gauxơ có thể viết Code được o anh? Em có mấy bài toán dạng này mà viết code hoài o được. Nhờ anh giúp đỡ thêm........Rất mong tin anh, thank a lot!
Thanh Phong