Gi i h ph ng trình đ i s tuy n tính b ng Excelả ệ ươ ạ ố ế ằ
Ngoai kha năng x ly bang tinh, Excel con co nhiêu kha năng khac ma co thê ban ch a kham ư ư
pha 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ê, kha ph c tap vi co nhiêu ân. Đê giai ươ ư ư
HPTTT, đây dung hai ph ng phap: ma trân va Gauss Seidel.ơ ươ
Ph ng pháp ma tr nươ ậ
S dung ph ng phap ma trân đê giai HPTTT la đ n gian nhât khi s dung Excel. HPTTT coư ươ ơ ư
dang:
Ax=b
trong đo A la ma trân hê sô, x la vect biên sô va b la 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 co thê viêt d i dang ma trân sau: ươ ươ
-8 1 2 x1 0
5 7 3 x2 = 10
2 1 2 x3 -2
Ta dê dang tim đ c nghiêm cua HPTTT băng cach dung ham MINVERSE (tinh ma trân ươ
nghich đao) va MMULT (tinh tich ma trân) trong Excel. Sau đây la 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 qua vao cac ôươ ơ
E6:E8
E6 0 E7 10 E8 -2
• B c 3: chon cac ô A11:C13, go côngươ
th c: ư=MINVERSE(A6:C8) va nhân
Ctrl+Shift+Enter đê chen công th c ư
nay vao ca vung đ c l a chon ta thu ươ ư
đ c ma trân nghich đao cua ma trân A.ươ
• B c 4: chon cac ô E11:E13, go công th c: ươ ư =MMULT(A11:C13,E6:E8) va nhân
Ctrl+Shift+Enter đê chen công th c nay vao ca 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 la
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 la kêt qua
tinh toan ân xk đ c đ a ngay vaoươ ư
tinh toan ân xk+1 trong cung môt b c ươ
lăp i, đây la 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 la cac b c giai HPTTT băng ươ
ph ng phap lăp Gauss-Seidel trongươ
Excel:
• B c 1: chonươ
Tools - Options -
Calculation tab va thay đôi Calculation t ưAutomatic thanh Manual, bo chon
Recalculate Before Save, chon Iterations va đăt Maximum Iteration băng 1,
Maximum change băng 0,001(xem hinh 2).
Hình 1
Hình 2
• B c 2: trong ô B3 nhâp ươ True, trong cac ô A8:A10 nhâp gia tri 0 (gia tri 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 gia tri trong côt C, cac gia tri nay lai nhân kêt ư
qua tinh toan t côt B, nh vây t công th c th hai trong côt B tr đi co thê s dung cac gia ư ư ư ư ư ơ ư
tri m i tinh cac công th c trên. ơ ơ ư
• B c 5: đinh dang cac ô B8:C10 la 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 gia tri kh i tao ban ơ ơ
đâu, sau đo thay đôi trang thai ô B3
thanh False va nhân F9 đê lăp lai qua
trinh tinh toan v i cac gia tri trong côt ơ
C, tiêp tuc nhân F9 cho đên khi cac
gia tri hôi tu 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 qua nhiêu b c lăpươ ơ ươ
nghia la phai nhân nhiêu lân F9 (trong
vi du trên phai lăp 10 b c) thi ta co ươ
thê tăng sô b c lăp trong môt lân ươ
nhân F9 băng cach chon Tool s-
Options va đăt Maximum Iteration l nơ
h n 1.ơ
Nhân Xet
Ph ng phap nghich đao ma trân đ n gian nh ng chi phu h p v i hê ph ng trinh co sô ânươ ơ ư ơ ơ ươ
không qua 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 y 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.
Hình 3
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 < 60 !
Có một số bài toán như mô hình Input-Output quốc gia có cả trăm ngành thì MINVERSE chịu
thua (một thầy giáo nói là MINVERSE chỉ tính được tới n = 54 !). Thầy giáo trên đã xử lý bài toán
Input-Output với n ≈ 100 bằng phần mềm Matlap (tôi không rõ lắm, nghe nói bên ĐHBK xài).
Bài toán cũng có thể giải bằng phương pháp Gauss. Nhưng có dịp thì đề cập sau.
Mục tiêu ở đây là dùng hàm MINVERSE với phạm vi vùng rộng hơn. Tôi tạo một hàm người dùng
tương tự MINVERSE của Excel để dùng cho trường hợp n lớn (mà nếu dùng MINVERSE có sẵn
của Excel thì không ra kết quả!).
Cách sử dụng thì giống y như MINVERSE chỉ khác cái tên hàm (tôi đặt tên NINVERSE).
Gửi lên mọi người xài thử xem sao.
http://www.giaiphapexcel.com/forum/showthread.php?15183-H%C3%A0m-t
%C3%ADnh-ma-tr%E1%BA%ADn-ngh%E1%BB%8Bch-%C4%91%E1%BA%A3o