Để các giải chương trình tuyến tính ( bậc nhất ) hay phi tuyến tính ( có nhiều bậc ), các bạn phải triển khai rất nhiều phép toán theo một số quy tắc trước khi tính được nghiệm. Phần mềm Excel có thể làm được điều này, bài viết sau giới thiệu một số phương pháp giải phương trình bằng Excel. Các bạn cần có kỹ năng về Excel: mở và lập một bảng tính, vẽ đồ thị, sử dụng các add-in Solver, GoalSeek trong Excel. 1. Giải phương trình bằng đồ thị: Sử dụng Excel rất dễ dàng vẽ đồ thị hàm f(x), dựa vào đồ thị ta có thể tìm nghiệm của phương trìnhf(x) = 0 chính là giao điểm đối với trục hoành của đồ thị. Ví dụ: giải phương trình phi tuyến tính sau bằng đồ thị f(x) = 2x5 – 3x2 – 5 = 0 Để giải phương trình này, ta chuẩn bị một trang tính mới với hai cột như trong hình 1, cột thứ nhất là giá trị của biến x , cột thứ hai tính toán giá trị của hàm f(x) với biến x trong khoảng–10 ≤ x≤ 10, sau đó vẽ đồ thị của hàm f(x). Điểm giao cắt giữa đồ thị và trục hoành chính là f(x) = 0. giá trị của x tại đó chính là nghiệm của phương trình. Lưu ý nhập công thức tại ô B2 chính xác là=2*A^5 – 3*A^2 – 5, công thức này sử dụng cho toàn bộ cột B. Để vẽ đồ thị hàm f(x) các bạn làm như sau: - Chọn cột B2:B22, sau đó bấm vào nút ChartWizard trên thanh công cụ, chọn Custom Types >Line on 2 Axis > Finish.
- Bấm chuột phải lên vùng biểu đồ, chọn Source Data > Series trong mục Category (X) Axis labels bạn điền vào =Sheet1!$A$2:$A$22 > OK. Sau đó format lại đồ thị, điền title, chọn lưới cho đẹp như hình vẽ.
Trên bảng tính, chúng ta thấy hàm f(x)cắt trục x tại một điểm nằm giữa x = 1 và x = 2 ( phần tô màu vàng ). Tuy nhiên sử dụng biểu đồ trong hình 1 rất khó nhận ra (vì phạm vi giá trị của f(x)quá lớn). Do đó, ta sẽ tính toán và vẽ lại đồ thị f(x) trong khoảng từ 0 ≤ x≤ 2 ( phần tô màu vàng ) như trong hình 2. Bạn cũng có thể dễ dàng vẽ đồ thị của bất kỳ hàm số nào để khảo sát biến thiên hoặc tìm miền giá trị (nghiệm) của các bất phương trình bằng phương pháp như trên.  Bây giờ ta có thể thấy rõ ràng trên đồ thị f(x) cắt trục hoành tại điểm x = 1.4, chúng ta kết luận nghiệm của phương trình đã cho xấp xỉ là: x = 1.4. Nếu giá trị này chưa thỏa mãn về độ chính xác, chúng ta sẽ sử dụng các phương pháp trình bày ở phần kế tiếp.  2. Giải phương trình bằng Goal Seek trong Excel: Sử dụng chức năng GoalSeek của Excel chúng ta có thể giải gần chính xác nghiệm của phương trình nhanh chóng và dễ dàng. Để làm quen, bạn giải phương trình sau: Ví dụ: Giải phương trình phi tuyến tính sau f(x) = 2x5 – 3x2 – 5 = 0 Để giải phương trình này, ta chuẩn bị một trang tính mới như trong hình 2.1, công thức tại ô B5 là = 2*B3^5 – 3B*3^2 – 5, đây là công thức hàm f(x) của phương trình. Tại ô B3 bạn nhập giá trị là 1 ( hoặc bất kỳ giá trị nào ), ta sẽ dùng ô này thử tính nghiệm của phương trình bằng Goal Seek. Bạn vào menu Tool chọn Gool Seek, mục đích của chúng ta là tìm giá trị của x để hàm f(x) trong ô B5 đạt giá trị bằng không, do vậy bạn điền vào mục Set cell= B5, To value = 0. Trong mục By changing cell, bạn điền vào $B$3, sau đó bấm OK, chúng ta sẽ có nghiệm x = 1.4041169như trong hình-2.2, giá trị này chính xác hơn ví dụ ban đầu. (Lưu ý là lúc này giá trị f(x) được Excel tính toán chỉ gần xấp xỉ bằng không theo thuật toán xấp xỉ lặp của chương trình).   3. Giải phương trình bằng Add-in Solver trong Excel: Sử dụng Solver của Excel chúng ta có thể giải nghiệm của phương trình giống như Goal Seek, tuy nhiên ưu điểm của Solver là bạn có thể giải phương trình với các biến số có điều kiện ràng buộc. Solver là một Add-in của Excel, do đó để sử dụng bạn phải vào menu Tool> Add-ins , sau đó đánh dấu chọn SolverAdd-in > bấm OK. Ví dụ: Giải phương trình phi tuyến tính sau f(x) = 2x5 – 3x2 – 5 = 0 ( với x ≥ 0 ) Để giải phương trình này, bạn tạo trang tính mới như trong hình 3.1, nhập giá trị 1.4 vào ô B3, công thức B5 =2*B3^5-3*B3^2-5. Vào menu Tool> Add-in sau đó bạn điền vào các mục như hình 3.1.  Chọn ô mục tiêu (Settarget Cell ) là B$5$, đánh dấu chọn mục value of và điền vào 0 (giá trị là 0). Điền $B$3 vào mục By changing Contraints. Như vậy bạn tìm nghiệm cho phương trình f(x) tại ô $B$5 sao cho f(x) = 0, tuy nhiên ở đây với điều kiện nghiệm x ≥ 0. Để thêm điều kiện x ≥ 0 vào, bạn bấm nút lệnh Add, sau đó chọn ô B3, chọn ≥ trên thanh công cụ Droplist, điền 0 vào như trong hình 3.2. Nhấn phím Add, sau đó nhấn Cancel để trở về như trong hình 3.3.   Điều kiện biến x ≥ 0 đã được thêm vào, bây giờ bạn nhấn Solver, chương trình Excel sẽ giải ra nghiệm là x = 1.404086 như hình 3.4 Bạn nhấn OK.
4. Giải hệ các phương trình tuyến tính bằng Ma trận trong Excel: Ma trận toán học chỉ đơn giản là mảng số hai chiều, các thành phần trong ma trận có chỉ số hàng và chỉ số cột. Một ma trận mà chỉ có một cột thì gọi là véc tơ. Phần này trình bày cách sử dụng các tính chất của ma trận để giải một hệ phương trình tuyến tính. a. Quan hệ giữa ma trận và hệ phương trình tuyến tính Cho một hệ phương trình tuyến tính có công thức tổng quát như sau a11x1+ a12x2+. . . + a1nxn=b1 a21x1+ a22x2+. . . + a2nxn=b2 . . . . . (4.1) an1x1+ an2x2+. . . + annxn=bn Trong đó aij, bi là tham số (đã biết), xj là biến số (chưa biết) của hệ phương trình. Hai chỉ số i và j là chỉ số hàng và cột của hệ phương trình. Theo định nghĩa ma trận, ta đặt lần lượt các ma trận A, véc tơ X, B như sau: | a11a21. . .a1n|| x1 || b1 | A = (aij)=| a21a22. . .a2n |X = (xj )=| x2|B = (bj )=| b2 | | . . .. . .. . ..|| . . . || . . . | | an1an2. . .ann| | xn|| bn | Với aij, bj là các tham số trong hệ phương trình (4.1) trên, xj là véc tơ nghiệm của hệ phương trình. Như vậy, hệ phương trình (4.1)có thể viết lại là: AX = B(4.2) Nhân cả hai vế với A-1( lưu ý A-1A = I với I là ma trận đơn vị ) ta được: A-1X = IX = A-1B(vớiIX = X) Vậy X = A-1B(4.3) Do đó, chúng ta kết luận: nghiệm của hệ phương trình tuyến tính (4.1) chính là ma trận tích của Ma trận đảo (chuyển vị) của A với ma trận B . Như chúng ta thấy trong phương trình (4.3). Chúng ta sẽ sử dụng kết luận trên để giải hệ phương trình tuyến tinh trong Excel, như sẽ trình bày trong phần sau. a. Các tính toánMa trận trong Excel Excel xem ma trận như là một khối vùng gồm các ô liền kề nhau, được excel xử lýnhư một ô riêng biệt. Điều đó có nghĩa là nếu bạn muốn nhập một ma trận có kích thước n hàng , m cột vào excel thì bạn phải chọn một khối vùng có n hàng, m cột nằm liền kề nhau trong bảng tính (lưu ý là chúng phải nằm liền kề nhau). Sau đó bạn nhập công thức mảng cho ma trận. Cuối cùng bạn nhấn tổ hợp phím Ctrl-Shift-Enter cùng lúc, công thức mảng sẽ xuất hiện trong dấu ngoặc cong { } trên thanh công thức (formula bar). Ví dụ {A1:C3} là mảng có kích thước 3hàng x 3cột gồm các ô nằm liền nhau từ A1 đến C3. (lưu ý: các bạn phải nhấn tổ hợp phím thì trên thanh công thức mới xuất hiện dấu ngoặc cong, nếu các bạn tự điền dấu ngoặc cong vào thì vẫn không hợp lệ, Excel không xử lý được). c. Giải hệ các phương trình tuyến tính bằng Ma trận đảo trong excel Sử dụng kiến thức trình bày trên ,chúng tagiải hệ phương trình dưới đây. Ví dụ: Giải hệ các phương trình tuyến tính sau 2x1 + 3x2=8 4x1-3x2=-2 Để giải hệ phương trình này, chúng ta sử dụng Excel để tính ma trận đảo của hệ phương trình. Bạn mở trang tính mới nhập ma trận (mảng giá trị) như trong hình 4.1. Chọn khối vùng A4:B5chứa ma trận A, G4:G5 chứa véc tơ B, I4:I5chứa vec tơ nghiệm X, vùng D4:E5 là ma trận đảo A-1 của ma trận A. Các ô từ A4:B5 bạn lần lượt nhập 2, 3 ; 4, -3 như hình 4.1, G4:G5 là 8, -2. Để tính ma trận đảo của A, chọn D4:E5, sau đó bạn nhập chính xác công thức =INVERSER(A4:B5), nhấn Ctrl-Shift-Enter sẽ xuất hiện công thức như hình 4.1, hàm inverser(array)của Excel tính toán và trả về mảng giá trị là ma trận đảo của array. Kết quả ma trận đảo A-1xuất hiện như trong hình 4.1.  Véc tơ X từ I4:I5 là nghiệm của hệ phương trình, bạn chọn I4:I5, nhập vào công thức =MMULT(D4:E5, G4:G5), sau đó nhấn tổ hợp phím Ctrl-Shift-Enter , công thức này nhân ma trận (mảng ) A-1 vớima trận B, kết quả là nghiệm của phương trình như trong hình 4.2 là x1 = 1, x2 = 2. (Để kiểm tra lại, các bạn nhập công thức mảng K4:K5 =MMULT(A4:B5, I4:I5) cho ra ma trận tích AX = B).  5. Giải hệ phương trình trong Excel bằng add-in Solver: Sử dụng chức năng Solver của Excel chúng ta có thể giải được các hệ phương trình phương trình tuyến tính (bậc nhất) cũng như hệ phương trình phi tuyến tính (hệ các phương trình có nhiều bậc). Sau đây là cách giải của từng loại hệ phương trình: a. Giải hệ các phương trình tuyến tính Giả sử hệ các phương trình tuyến tính có có dạng như sau: f1(x1, x2, … , xn) =0 f2(x1, x2, … , xn) =0 . . .. . fn(x1, x2, … , xn) =0 Đây là hệ phương trình gồm n phương trình với n ẩn, chúng ta phải tìm giá trị của x1, x2, … , xn để từng phương trình bằng không (zero). Có nhiều cách để giải hệ phương trình này, một trong các cách mà chúng ta sẽ chọn để giải bằng excel là bắt buộc phương trình y = f12 + f22+… +fn2(5.a) bằng zero.Đó là, tìm các giá trị x1, x2, … , xn sao cho phương trình (5.a) bằng không (zero).Vì các thành phần bên vế phải là bình phương, nên luôn ≥ 0,do vậy để y =0 thì từng thành phần fs phải đồng thời bằng 0. nên giá trị x1, x2, … , xn để y =0 sẽ là lời giải cho hệ phương trình trên. Do vậy, mục đích của việc dùng Solver để giải hệ là định nghĩa hàm mục tiêu (targer function) gồm tổng bình phương của từng phương thành phần như trong hình 5.1 sau đó tìm giá trị x1, x2, … , xn để hàm mục tiêu bằng không. Ví dụ: Giải hệ các phương trình tuyến tính sau 3 x1 + 2 x2- x3=4 2x1-x2+ x3 =3 x1+ x2- 2x3 = -3 Ta đặt f(x1, x2, x3), g(x1, x2, x3), h(x1, x2, x3) lần lượt bằng phương trình thứ nhất, thứ hai và thứ ba, vậy hệ phương trình viết lại là : f(x1, x2, x3) = 3 x1 +2 x2- x3 - 4 = 0 g(x1, x2, x3) = 2 x1 -x2+ x3 -3 = 0 h(x1, x2, x3)=x1+ x2- 2x3+ 3 = 0 muốn tìm x1, x2, x3 để ba phương trình trên bằng zero, ta đặt tổng y= f2 + g2 + h2 Nếu f = 0, g =0, h =0 thì y cũng đồng thời bằng không, các giá trị của f, g, h sẽ làm choy> 0, do đó vấn đề là tìm giá trị x1, x2, x3để y =0 sẽ chính là nghiệm của hệ phương trình. Để giải hệ phương trình trên bằng Solver , bạn mở một trang tính mới, nhập các công thức vào như trong hình 5.1. Các công thức trên ô B7, B8 và B9 là các hàm f(x1, x2, x3), g(x1, x2, x3), h(x1, x2, x3), ô B11 là công thức hàm y, các ô từ B3:B6sẽ chứa các giá trị nghiệm do Solvertính toán. Chọn ô B11 (chứa công thức phương trình y), sau đó chọn menu Tool> Solver > điền các tham số Solver vào như trong hình 5.2, Bấm nút Solver, bạn sẽ có kết quả cho lời giải nhưtrong hình 5.3   Sau khi đã Solver đã tìm ra lời giải , bạn chọn Keep SolverSolution, các giá trị kết quả x1, x2, x3 tìm được sẽ có trên các ô của bảng tính như trên hình 5.3. Như vậy lời giải cho hệ các phương trình tuyến tính trên làx1=1, x2 = 2, x3 = 3. (Các giá trị tương ứng của từng hàm f, g, h và y là xấp xỉ zero.) b. Giải hệ các phương trình phi tuyến tính Giải hệ các phương trình phi tuyến tính cũng tương tự như hệ tuyến tính.
Ví dụ: giải hệ các phương trình phi tuyến tính sau với điều kiện x1, x2≥ 0 x12+ 2 x22-5x1 + 2 x2=40 3x12 - x22+4x1 + 2 x2=28 Ta đặt f(x1, x2), g(x1, x2) lần lượt bằng phương trình thứ nhất và thứ hai , vậy hệ phương trình viết lại là: f(x1, x2)=x12+ 2 x22- 5x1+ 2 x2-40 = 0 g(x1, x2) = 3x12 - x22+ 4x1 + 2 x2-28 = 0 muốn tìm x1, x2, để hai phương trình trên bằng zero, ta đặt hàm mục tiêu y(x1, x2) = f2 + g2 ( với x1 ≥ 0, x2 ≥ 0 ) sau đó dùng Solverđể xác định giá trị x1, x2 để hàm mục tiêu bằng zero, giá trị tìm được sẽ là lời giải của hệ phương trình. Để giải hệ các phương trình phi tuyến tính trên bằng Solver , bạn mở một trang tính mới, nhập các công thức vào như trong hình 6.1. Các công thức trên ô B6,B7 là các hàm f(x1, x2), g(x1, x2), ô B9 là công thức hàm y, các ô B3, B4 sẽ chứa các giá trị nghiệm do Solver tính toán. Chọn ô B9 (chứa công thức phương trình y), sau đó chọn menu Tool> Solver > điền các thông số như trong hình 6.2.
Để giải điều kiện của hệ phương trình này là x1 ≥ 0, x2 ≥ 0, trong mục các điều kiện ràng buộc của Solver (Subject to the Constraints) bạn phải thêm các tham số điều kiện là $B$3 ≥0, $B4$≥0 bằng cách nhấn nút add như trong hình 6.3. Sau đó nhấn Cancel,Bấm nút Solver, bạn sẽ có kết quả cho lời giải nhưtrong hình 6.4     Sau khi đã Solver đã tìm ra lời giải , bạn chọn Keep SolverSolution, các giá trị kết quả x1, x2, x3 tìm được sẽ có trên các ô của bảng tính như trên hình 6.5. Như vậy lời giải cho hệ các phương trình tuyến tính trên làx1= 2.8, x2 = 3.6. (Các giá trị tương ứng của từng hàm f, g, và y là xấp xỉ zero.)  Ngoài các phương pháp giải đã nói ở trên, sử dụng phần mềm Excel còn có thể lập bảng tính các biểu thức tích phân, vi phân hoặc các phép toán thống kê… Vì thời gian có hạn nên không trình bày ở đây. Cuối cùng, chúc các bạn vui, khỏe, thông minh sáng tạo và thành công trong học tập! Châu Nhã Khánh Ngọc
Email: Chaunhangoc@yahoo.com (nguồn: Gottfried, Spreeadsheet Tools For Engineers Using Excel, mhhe press,2003)
School@net
|
Tìm kiếm
Khách: 8
Thành viên: 0
Tổng cộng: 8
