Giải hệ phương trình bằng Excel

Tóm tắt nội dung tài liệu

1. 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
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).
3. • 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 A­1.
4. 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. A­1 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 Ctrl­Shift­Enter). [ 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 1093 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.

Ngoài khả năng xử lý bảng tính, Excel còn có nhiều khả năng khác mà có thể bạn chưa khám phá hết. Bài viết này giới thiệu cách dùng Excel để giải hệ phương trình đại số tuyến tính (HPTTT) - dạng bài toán thường gặp trong thực tế, khá phức tạp vì có nhiều ẩn. Để giải HPTTT, ở đây dùng hai phương pháp: ma trận và Gauss Seidel.

Phương pháp ma trận

Sử dụng phương pháp ma trận để giải HPTTT là đơn giản nhất khi sử dụng Excel. HPTTT có dạng:

       Ax=b

trong đó A là ma trận hệ số, x là vectơ biến số và b là vectơ kết quả.

HPTTT được biến đổi thành:

       x=A-1b

Xét hệ ba phương trình ba ẩn sau:

-8x1  + x2   + 2x3  =   0

5x1   + 7x2   - 3x3  = 10     (*)

2x1   + x2    - 2x3  =  -2

Hệ ba phương trình này có thể viết dưới dạng ma trận sau:

  -8   1   2      x1        0

   5   7   3      x2   = 10

   2   1   2      x3       -2

Ta dễ dàng tìm được nghiệm của HPTTT bằng cách dùng hàm MINVERSE (tính ma trận nghịch đảo) và MMULT (tính tích ma trận) trong Excel. Sau đây là các bước giải HPTTT:

• Bước 1: nhập ma trận A vào các ô 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ả vào các ô E6:E8

E6  0         E7  10       E8  -2

• Bước 3: chọn các ô A11:C13, gõ công thức: =MINVERSE(A6:C8) và nhấn Ctrl+Shift+Enter để chèn công thức này vào cả vùng được lựa chọn ta thu được ma trận nghịch đảo của ma trận A.

• Bước 4: chọn các ô E11:E13, gõ công thức: =MMULT(A11:C13,E6:E8) và nhấn Ctrl+Shift+Enter để chèn công thức này vào cả vùng được lựa chọn ta thu được nghiệm của hệ ba phương trình trên trong các cột E11:E13 (xem hình 1)

Nghiệm của hệ phương trình là:

x1=1     x2=2     x3=3

Phương Pháp lặp Gauss-Seidel

Bản chất của  phép lặp Gauss là nghiệm ở bước lặp i được dùng để tính cho bước lặp i+1 còn bản chất của phép lặp Gauss-Seidel là kết quả tính toán ẩn xk được đưa ngay vào tính toán ẩn xk+1 trong cùng một bước lặp i, đây là một bước cải tiến đáng kể phương pháp Gauss. Ta xem xét việc sử dụng Excel để giải HPTTT theo phương pháp Gauss-Seidel.

Biến đổi hệ phương trình trên ta có:

Sau đây là các bước giải HPTTT bằng phương pháp lặp Gauss-Seidel trong Excel:

• Bước 1: chọn Tools - Options - Calculation tab và thay đổi Calculation từ Automatic thành Manual, bỏ chọn Recalculate Before Save, chọn Iterations và đặt Maximum Iteration bằng 1, Maximum change bằng 0,001(xem hình 2).

• Bước 2: trong ô B3 nhập True, trong các ô A8:A10 nhập giá trị 0 (giá trị khởi tạo 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 các công thức trong cột B tính theo các giá trị trong cột C, các giá trị này lại nhận kết quả tính toán từ cột B, như vậy từ công thức thứ hai trong cột B trở đi có thể sử dụng các giá trị mới tính ở các công thức trên.

• Bước 5: định dạng các ô B8:C10 là Number với ba số thập phân sau dấu phẩy

• Bước 6: khi ô B3 ở trạng thái True nhấn F9 để tính với giá trị khởi tạo ban đầu, sau đó thay đổi trạng thái ô B3 thành False và nhấn F9 để lặp lại quá trình tính toán với các giá trị trong cột C, tiếp tục nhấn F9 cho đến khi các giá trị hội tụ ta nhận được nghiệm của hệ ba phương trình trên trong các ô C8:C10 (xem hình 3).

Trong trường hợp quá nhiều bước lặp nghĩa là phải nhấn nhiều lần F9 (trong ví dụ trên phải 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 cách chọn Tool s- Options  và đặt Maximum Iteration lớn hơn 1.

Nhận Xét

Phương pháp nghịch đảo ma trận đơn giản nhưng chỉ phù hợp với hệ phương trình có số ẩn không quá lớn (dưới 60 ẩn) với số ẩn lớn hơn nên dùng phương pháp Gauss-Seidel. Ngoài ra còn nhiều phương pháp khác nhưng trong phạm vi bài này không đề cập đến, mong nhận được sự đóng góp ý kiến của các bạn.