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

pdf
Số trang Excel ứng dụng trong kinh tế Bài 5 11 Cỡ tệp Excel ứng dụng trong kinh tế Bài 5 217 KB Lượt tải Excel ứng dụng trong kinh tế Bài 5 0 Lượt đọc Excel ứng dụng trong kinh tế Bài 5 1
Đánh giá Excel ứng dụng trong kinh tế Bài 5
4.8 ( 20 lượt)
Nhấn vào bên dưới để tải tài liệu
Đang xem trước 10 trên tổng 11 trang, để tải xuống xem đầy đủ hãy nhấn vào bên trên
Chủ đề liên quan

Nội dung

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 BAØ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ëc Solver ñ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 = 0 B1. 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ính B2. 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 x1 B3. 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ình Hình 5.3. Keát quaû chaïy Goal Seek laàn thöù nhaát vaø tìm ñöôïc nghieäm x1=1 B4. 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ïy Goal 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äng x2 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 x2 B6. 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 II Traà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ình 5.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 Solver B1. 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ính Ghi 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ôïp haø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-Ins Traà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àn khai 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 hai böôùc treân ñeå nhaäp theâm caùc raøng buoäc khaùc. Traàn Thanh Phong 44 ÖÙ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.10. Khai baùo thoâng soá cho Solver B3. Nhaáp nuùt Solve chaïy tìm lôøi giaûi. Hoäp thoâng baùo sau seõ xuaát hieän: Hình 5.11. Choïn kieåu baùo caùo B4. Choïn Keep Solver Solution ñeå löu keát quaû treân baûng tính. Choïn Restore Original Values ñeå huûy keát quaû Solver vöøa tìm ñöôïc vaø traû caùc bieán veà tình traïng ban ñaàu. Choïn Save Scenario ñeå löu keát quaû vöøa tìm ñöôïc thaønh moät tình huoáng ñeå coù xem laïi sau naøy. Ngoaøi ra coøn coù 3 loaïi baùo caùo laø Answer, Sensitivity vaø Limits. B5. Choïn OK ñeå hoaøn taát quaù trình chaïy Solver. Hình 5.12. Caùc nghieäm heä phöông trình Caùch 2. Giaûi heä phöông trình baèng phöông phaùp ma traän Heä phöông trình treân laø töông ñöông vôùi phöông trình ma traän sau: Traàn Thanh Phong 45 ÖÙng duïng Microsoft Excel trong kinh teá Chöông trình Giaûng daïy Kinh teá Fulbright A 1 2 3 2 1 1 1 4 2 * * Baøi 5.Giaûi phöông trình vaø heä phöông trình X = B x 25 y = 14 z 10 Î Nghieäm cuûa heä laø X = A-1*B B1. 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â B75:D77 nhaäp vaøo heä soá cuûa caùc phöông trình (1), (2) vaø (3) Æ Caùc oâ F75, F76, F77 laø nhaõn caùc nghieäm x, y, z Æ Caùc oâ H75, H76, H77 laø caùc con soá ôû veá phaûi cuûa caùc phöông trình. Hình 5.13. Laäp moâ hình baøi toaùn treân baûng tính B2. Tìm ma traän nghòch ñaûo cuûa ma traän heä soá A Î Tìm A-1 Æ Choïn vuøng ñòa chæ B80:D82 Æ Nhaäp vaøo coâng thöùc =Minverse(B75:D77) ñeå nghòch ñaûo ma traän Æ Nhaán toå hôïp phím Ctrl + Shift + Enter ñeå thöïc hieän pheùp tính Hình 5.14. Tính ma traän nghòch ñaûo B3. Tìm nghieäm heä phöông trình Æ Choïn vuøng ñòa chæ B85:B87 Æ Nhaäp vaøo coâng thöùc =MMULT(B80:D82,H75:H77) Æ Nhaán toå hôïp phím Ctrl + Shift + Enter ñeå thöïc hieän pheùp tính Hình 5.15. Nghieäm heä phöông trình Traàn Thanh Phong 46 ÖÙ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 Löu yù: Vieäc tính toaùn treân daõy soá lieäu (array) coù moät soá tính chaát sau: 1. Khi nhaäp, xoùa, chænh söûa coâng thöùc phaûi thöïc hieän treân toaøn boä daõy, do vaäy caàn choïn caû daõy tröôùc khi thöïc hieän nhaäp, xoùa hay chænh söûa. 2. Nhaán phím F2 ñeå vaøo cheá ñoä chænh söûa 3. Nhaán toå hôïp phím Ctrl + Shift + Enter khi hoaøn taát. 5.3. Söû duïng Solver Ñeå söû duïng toát Solver ta caàn naém vöõng caùc yeâu caàu thoâng soá caàn phaûi khai baùo cho Solver: Hình 5.16. Hoäp thoaïi Solver • Set Target Cell: Nôi ñaây ta caàn nhaäp vaøo ñòa chæ cuûa haøm muïc tieâu. • Equal To: Haøm muïc tieâu muoán ñaït tôùi Max, Min hay Value of (baèng moät giaù trò mong muoán naøo ñoù thì nhaäp giaù trò vaøo.) • By Changing Cell: Nhaäp vaøo ñòa chæ chöùa caùc bieán cuûa baøi toaùn caàn giaûi. • Subject to the constraints: Nhaäp vaøo caùc raøng buoäc cuûa baøi toaùn. Caùch laøm cuûa Solver laø thay ñoåi giaù trò caùc bieán taïi By Changing Cell ñeán luùc naøo ñoù laøm cho giaù trò haøm muïc tieâu taïi Set Target Cell ñaït moät giaù trò qui ñònh taïi Equal To (Max, Min hoaëc Value of) vaø ñoàng thôøi phaûi thoõa maõm taäp caùc raøng buoäc taïi Subject to the constraints. Thieát laäp caùc thuoäc tính cho Solver ta nhaáp chuoät vaøo nuùt Options, hoäp thoaïi Solver Options xuaát hieän: Traàn Thanh Phong 47 ÖÙ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.17. Thieát laäp thoâng soá cho Solver: Cheá ñoä maëc ñònh Tham soá Max Time Iterations Precision Tolerance Convergence Assume Linear Model Assume Non-Negative Use Automatic Scaling Show Iteration Results Traàn Thanh Phong Giaûi thích Thôøi gian toái ña ñeå giaûi baøi toaùn, giaù trò maëc ñònh laø 100 giaây duøng cho caùc baøi toaùn ñôn giaûn. Thôøi gian toái ña coù theå nhaäp vaøo laø 32.767 giaây. Soá laàn laëp toái ña ñeå giaûi baøi toaùn, giaù trò maëc ñònh laø 100 giaây duøng cho caùc baøi toaùn ñôn giaûn. Soá laàn laëp toái ña coù theå nhaäp vaøo laø 32.767 laàn. Ñoä chính xaùc cuûa baøi toaùn. Taïi ñaây coù theå nhaäp vaøo caùc soá trong khoaûng 0 vaø 1. Soá caøng gaàn 0 thì ñoä chính xaùc caøng cao. Giaù trò naøy ñieàu chænh ñoä sai soá cho taäp raøng buoäc. Giaù trò maëc ñònh laø 1 phaàn trieäu. Chæ aùp duïng ñoái vôùi baøi toaùn coù raøng buoäc nguyeân. Nhaäp vaøo sai soá coù theå chaáp nhaän ñöôïc, sai soá caøng lôùn thì toác ñoä giaûi caøng nhanh. Giaù tròmaëc ñònh laø 5% Chæ aùp duïng cho caùc baøi toaùn khoâng tuyeán tính (nonlinear). Taïi ñaây nhaäp vaøo caùc soá trong khoaûng 0 vaø 1. Giaù trò caøng gaàn 0 thì ñoä chính xaùc cao hôn vaø caàn thôøi gian nhieàu hôn. Choïn ñeå taêng toác ñoä giaûi baøi toaùn khi taát caû quan heä trong moâ hình laø tuyeán tính. Choïn tuøy choïn naøy neáu muoán Solver giaû ñònh laø taát caû caùc bieán laø khoâng aâm. Choïn khi baøi toaùn maø caùc döõ lieäu nhaäp vaø xuaát coù söï khaùc bieät lôùn. Ví duï baøi toaùn toái ña % lôïi nhuaän treân haøm trieäu USD voán ñaàu tö. Choïn neáu muoán Solver taïm döøng laïi vaø hieån thò keát quaû sau moãi laàn laëp. 48 ÖÙng duïng Microsoft Excel trong kinh teá Chöông trình Giaûng daïy Kinh teá Fulbright Estimates Derivatives Search Save Model Load Model Baøi 5.Giaûi phöông trình vaø heä phöông trình Choïn phöông phaùp cho Solver duøng ñeå öôùc löôïng caùc bieán: Tangent: Söû duïng caùch xaáp xæ tuyeán tính baäc nhaát. Quadratic: Söû duïng caùch xaáp xæ baäc boán Choïn caùch ñeå öôùc löôïng haøm muïc tieâu vaø caùc raøng buoäc Forward: ñöôïc duøng raát phoå bieán hôn, khi ñoù caùc giaù trò cuûa raøng buoäc bieán ñoåi chaäm. Central: Duøng khi caùc giaù trò cuûa raøng buoäc bieán ñoåi nhanh vaø ñöôïc duøng khi Solver baùo khoâng theå caûi tieán keát quaû thu ñöôïc. Qui ñònh giaûi thuaät tìm kieám keát quaû cho baøi toaùn: Newton: laø phöông phaùp maëc ñònh, noù söû duïng nhieàu boä nhôù hôn vaø coù soá laàn laëp ít hôn phöông phaùp Conjugate. Conjugate: Caàn ít boä nhôù hôn phöông phaùp Newton nhöng soá laàn laëp thì nhieàu hôn. Duøng phöông phaùp naøy cho caùc baøi toaùn phöùc taïp vaø boä nhôù thì coù giôùi haïn. Choïn nôi löu moâ hình baøi toaùn. Ñöôïc duøng khi caàn löu nhieàu hôn moät moâ hình treân moät worksheet. Moâ hình ñaàu tieân ñaõ ñöôïc löu töï ñoäng. Xaùc ñònh vuøng ñòa chæ cuûa moâ hình baøi toaùn caàn naïp vaøo 5.4. Ma traän Ma traän ñöôïc ñaët trong caëp moùc vuoâng: Kích thöôùc ma traän ñöôïc xaùc ñònh theo soá doøng vaøo soá coät cuûa ma traän, ma traän n x m ñoïc laø n doøng vaø m coät. Hai ma traän chæ nhaân ñöôïc vôùi nhau khi soá doøng coät cuûa ma traän ñöùng tröôùc baèng vôùi soá doøng cuûa ma traän ñöùng sau. Ví duï ma traän coù kích thöôùc n x p thì coù theå nhaân vôùi ma traän coù kích thöôùc p x m. Döôùi ñaây laø coâng thöùc nhaân hai ma traän ñaëc bieät coù kích thöôùc 1 x n vaø n x 1: [a1 Traàn Thanh Phong a2 ⎡ b1 ⎤ ⎢b ⎥ L a a ].⎢ 2 ⎥ = a1b1 + a2b2 + … + anbn ⎢M⎥ ⎢ ⎥ ⎣bn ⎦ 49 ÖÙ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 Coâng thöùc toång quaùt xaùc ñònh giaù trò cuûa phaàn töû cij trong ma traän keát quaû: n cij = ∑ aik bkj (i laø soá doøng; j laø soá coät) k =1 Ví duï: Nhaân hai ma traän sau: ⎡ 2 3 − 1⎤ A=⎢ ⎥ ⎣− 2 1 2 ⎦ ; ⎡ ⎡1⎤ ⎢ [2 3 − 1].⎢⎢ 2 ⎥⎥ ⎡ 1 3⎤ ⎢ ⎢⎣− 1⎥⎦ ⎥ ⎢ ⎡ 2 3 − 1⎤ ⎢ ⎢ ⎥ ⎢ 2 0⎥ = ⎢ ⎡1⎤ ⎣− 2 1 2 ⎦ ⎢ ⎥ ⎢ − 1 2 ⎣ ⎦ ⎢[− 2 1 2].⎢ 2 ⎥ ⎢ ⎥ ⎢ ⎢⎣− 1⎥⎦ ⎣⎢ 2x3 ⎡ 1 3⎤ ⎥ ⎢ B = ⎢ 2 0⎥ ⎢− 1 2⎥ ⎦ ⎣ ⎡ 3⎤ ⎤ [2 3 − 1].⎢⎢0⎥⎥ ⎥⎥ 4⎤ ⎢⎣2⎥⎦ ⎥ ⎡ 9 ⎥=⎢ ⎡3⎤ ⎥ ⎣− 2 − 2⎥⎦ [− 2 1 2].⎢⎢0⎥⎥ ⎥ ⎥ ⎢⎣2⎥⎦ ⎦⎥ 3x2 2x2 Ví duï veà caùch caùch tìm caùc phaàn töû trong ma traän nghòch ñaûo töø ma traän ⎡a c ⎤ A=⎢ ⎥ ⎣b d ⎦ Moät soá yeâu caàu veà tính ñònh thöùc vaø tìm nghòch ñaûo ma traän trong Excel: o Phaûi laø ma traän vuoâng, neáu khoâng vuoâng seõ baùo loãi #VALUE!. o Neáu coù phaàn töû naøo trong ma traän laø roãng hoaëc laø chöõ thì baùo loãi #VALUE!. Haøm tính ñònh thöùc Cuù phaùp: MDETERM(array) Array: laø ñòa chæ ma traän caàn tính ñònh thöùc Haøm tìm ma traän nghòch ñaûo Cuù phaùp: Minverse(array) Array: laø ñòa chæ ma traän caàn nghòch ñaûo Traàn Thanh Phong 50 ÖÙng duïng Microsoft Excel trong kinh teá
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.