Excel ứng dụng trong kinh tế Bài 5

BÀI 5. GIẢI PHƯƠNG TRÌNH VÀ HỆ PHƯƠNG TRÌNH.Nguyên tắc chung để giải phương trình, hệ phương trình trên bảng tính là phải xác định các biến, các hàm, rồi lập mô hình và sau đó dùng Goal Seek hoặcSolver để dò tìm nghiệm. (sử dụng tập tin bai5-1.xls)
Nội dung trích xuất từ tài liệu:
Excel ứng dụng trong kinh tế Bài 5Chöông trình Giaûng daïy Kinh teá Fulbright Baøi 5.Giaûi phöông trình vaø heä phöông trìnhBAØI 5. GIAÛI PHÖÔNG TRÌNH VAØ HEÄ PHÖÔNG TRÌNH Nguyeân taéc chung ñeå giaûi phöông trình, heä phöông trình treân baûng tính laøphaûi xaùc ñònh caùc bieán, caùc haøm, roài laäp moâ hình vaø sau ñoù duøng Goal Seek hoaëcSolver ñeå doø tìm nghieäm. (söû duïng taäp tin bai5-1.xls)5.1. Giaûi phöông trình Giaûi phöông trình baäc hai x2 + 5x – 6 = 0B1. Xaùc ñònh bieán, haøm muïc tieâu vaø laäp moâ hình treân baûng tính Taïi oâ A6 vaø A7 nhaäp caùc giaù trò khôûi ñoäng baát kyø cho bieán x Taïi oâ B6 vaø B7 nhaäp caùc coâng thöùc theo phöông trình ñeà cho ñeå tính f(x)Hình 5.1. Laäp moâ hình treân baûng tínhB2. Choïn oâ B6, sau ñoù choïn Tools Goal Seek vaø khai baùo nhö hình 5.2. Nhaáp nuùt OK ñeå chaïy Goal Seek.Hình 5.2. Khai baùo cho Goal Seek tìm nghieäm thöù nhaát x1B3. Sau quaù trình chaïy Goal Seek thì hoäp thoaïi thoâng baùo xuaát hieän. Nhaáp OK ñeå chaáp nhaän keát quaû hoaëc nhaáp Cancel ñeå huûy keát quaû chaïy Goal Seek.Traàn Thanh Phong 41 ÖÙng duïng Microsoft Excel trong kinh teáChöông trình Giaûng daïy Kinh teá Fulbright Baøi 5.Giaûi phöông trình vaø heä phöông trìnhHình 5.3. Keát quaû chaïy Goal Seek laàn thöù nhaát vaø tìm ñöôïc nghieäm x1=1B4. Phöông trình baäc hai coù toái ña hai nghieäm, do vaäy ta caàn chaïy Goal Seek laàn nöõa ñeå tìm nghieäm thöù hai x2.Ghi chuù: Ñeå traùnh laàn chaïy Goal Seek thöù hai traû veà cuøng keát quaû vôùi laàn chaïy thöùnhaát, ta haõy cho giaù trò khôûi ñoäng x2 moät con soá aâm raát nhoû (Ví duï: -10000) roài chaïyGoal Seek. Neáu keát quaû truøng vôùi laàn chaïy ñaàu tieân thì haõy cho laïi giaù trò khôûi ñoängx2 moät con soá döông lôùn (Ví duï: 10000) roài chaïy laïi Goal Seek.B5. Cho laïi giaù trò khôûi ñoäng taïi oâ A7 laø -10000, choïn oâ B7 vaø choïn Tools Goal Seek. Khai baùo nhö hình 5.4.Hình 5.4. Khai baùo cho Goal Seek tìm nghieäm thöù nhaát x2B6. Sau quaù trình chaïy Goal Seek thì hoäp thoaïi thoâng baùo xuaát hieän. Nhaáp OK ñeå chaáp nhaän keát quaû hoaëc nhaáp Cancel ñeå huûy keát quaû chaïy Goal Seek.Hình 5.5. Keát quaû phöông trình baäc IITraàn Thanh Phong 42 ÖÙng duïng Microsoft Excel trong kinh teáChöông trình Giaûng daïy Kinh teá Fulbright Baøi 5.Giaûi phöông trình vaø heä phöông trình5.2. Giaûi heä phöông trình Giaûi heä phöông trình sau: x + 2y + 3z = 25 (1) 2x + y + z = 14 (2) x + 4y + 2z = 10 (3)Caùch 1. Giaûi heä phöông trình duøng SolverB1. Xaùc ñònh caùc bieán, caùc haøm muïc tieâu vaø laäp moâ hình treân baûng tính Caùc oâ trong maûng A26:C28 nhaäp caùc heä soá cuûa caùc phöông trình (moãi phöông trình nhaäp moät doøng). Caùc oâ D26, D27, D28 laàn löôït chöùa giaù trò khôûi ñoäng cuûa caùc bieán x, y, z Caùc oâ F26, F27, F28 laàn löôït chöùa caùc giaù trò ôû veá phaûi cuûa caùc phöông trình (1), (2) vaø (3). Caùc oâ E26, E27, E28 ñöôïc tính baèng caùch nhaân caùc heä soá cuûa phöông trình vôùi caùc giaù trò khôûi ñoäng cuûa x, y, z (xem coâng thöùc minh hoïa trong hình 5.6).Hình 5.6. Laäp moâ hình baøi toaùn treân baûng tínhGhi chuù: Caùch khaùc ñeå tính nhanh Veá traùi cuûa caùc phöông trình laø duøng keát hôïphaøm Sumproduct (array1, array2) vaø haøm Transpose (array). Caùch laøm nhö sau: 1. Choïn 3 oâ E26 vaø nhaäp vaøo coâng thöùc sau: =SUMPRODUCT(A26:C26,TRANSPOSE($D$26:$D$28)) 2. Sao cheùp coâng thöùc cho 2 oâ coøn laïi E27 vaø E28.B2. Vaøo thöïc ñôn Tools Solver. Neáu chöa thaáy chöùc naêng Solver treân thöïc ñôn Tools thì ta caàn boåsung chöùc naêng naøy vaøo Excel. Caùc böôùc ñeå boå sung chöùc naêng Solver cho Excel: 1. Vaøo thöïc ñôn Tools Add-InsTraàn Thanh Phong 43 ÖÙng duïng Microsoft Excel trong kinh teáChöông trình Giaûng daïy Kinh teá Fulbright Baøi 5.Giaûi phöông trình vaø heä phöông trình Hình 5.7. Hoäp thoaïi Add-Ins chöùa caùc chöùc naêng môû roäng cuûa Excel 2. Choïn Solver Add-in vaø choïn OK ñeå chaáp nhaän. Sau khi thöïc hieän leänh Tools Solver, hoäp thoaïi Solver xuaát hieän. Ta caànkhai baùo caùc thoâng soá cho Solver nhö sau: Ñöa ñòa chæ D26:D28 vaøo By Changing Cells Ñöa caùc raøng buoäc vaøo Subject to the Constraints: 1. Nhaáp nuùt Add vaø khai baùo nhö hình sau Hình 5.9. Theâm raøng buoäc 2. Nhaáp nuùt OK ñeå hoaøn taát. Neáu baøi toaùn caàn nhieàu raøng buoäc hôn thì thöïc hieän laïi ha ...