intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

Chương 6 - LẬP CÁC ĐƯỜNG CONG BIỂU DIỄN ĐỒ THỊ

Chia sẻ: Nguyễn Đức Lộc | Ngày: | Loại File: DOC | Số trang:10

Thêm vào BST
Báo xấu
319
lượt xem 68
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Trong chương này trình bầy về cách vẽ các đường cong dựa theo các điểm dữ liệu. Các phương pháp đơn giản nhất là sử dụng các hàm hồi quy tuyến tính của EXCEL: các hàm LINEST và LOGEST, hai hàm này thực hiện phép hồi quy tuyến tính bội. Bằng cách chuyển đổi một cách thích hợp các phương trình, bạn có thể làm xấp xỉ nhiều phương trình phi tuyến bằng các hàm này. Ngoài ra, có thể sử dụng hàm LINEST để thực hiện phép hồi quy đa thức cho việc làm xấp...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Chương 6 - LẬP CÁC ĐƯỜNG CONG BIỂU DIỄN ĐỒ THỊ

  1. Ch¬ng 6 LËp c¸c ®êng cong biÓu diÔn ®å thÞ Tãm t¾t Trong ch¬ng nµy tr×nh bÇy vÒ c¸ch vÏ c¸c ®êng cong dùa theo c¸c ®iÓm d÷ liÖu. C¸c ph- ¬ng ph¸p ®¬n gi¶n nhÊt lµ sö dông c¸c hµm håi quy tuyÕn tÝnh cña EXCEL: c¸c hµm LINEST vµ LOGEST, hai hµm nµy thùc hiÖn phÐp håi quy tuyÕn tÝnh béi. B»ng c¸ch chuyÓn ®æi mét c¸ch thÝch hîp c¸c ph¬ng tr×nh, b¹n cã thÓ lµm xÊp xØ nhiÒu ph¬ng tr×nh phi tuyÕn b»ng c¸c hµm nµy. Ngoµi ra, cã thÓ sö dông hµm LINEST ®Ó thùc hiÖn phÐp håi quy ®a thøc cho viÖc lµm xÊp xØ mét tËp d÷ liÖu b»ng mét ®a thøc. §èi víi c¸c ph¬ng tr×nh phøc t¹p h¬n, b¹n sÏ sö dông thuËt to¸n h¹ bËc nhanh nhÊt ®Ó t×m c¸c hÖ sè cho mét ph¬ng tr×nh phi tuyÕn. Sau ®ã b¹n sÏ tù ®éng ho¸ thuËt to¸n ®ã víi Solver vµ ch¬ng tr×nh macro. Khi viÖc lµm xÊp xØ c¸c d÷ liÖu thùc nghiÖm b»ng mét hµm ®· biÕt lµ khã kh¨n hoÆc kh«ng hîp lý, b¹n cã thÓ sö dông c¸c hµm t×m kiÕm trong b¶ng vµ hµm néi suy. B¹n sÏ khai triÓn mét hµm néi suy ®¬n gi¶n trong b¶ng tÝnh, vµ mét hµm phøc t¹p h¬n díi d¹ng mét hµm macro. Môc lôc : 6.1. Sö dông c¸c hµm cã s½n 6.1.1. PhÐp tÝnh håi quy 6.1.2. PhÐp tÝnh xÊp xØ tuyÕn tÝnh 6.1.3. PhÐp tÝnh xÊp xØ ®a thøc 6.1.3. KiÓm tra thèng kª 6.2. Sö dông c¸c hµm phøc 6.2,1. §iÒu chØnh b»ng tay 6.2,2. §iÒu chØnh tù ®éng 6.3. Tra b¶ng vµ néi suy 6.3,1. Néi suy tuyÕn tÝnh 6.3,2. Néi suy bËc 3( néi suy lËp ph¬ng) ============================================================== Mét d¹ng bµi to¸n th«ng thêng mµ c¸c nhµ khoa häc vµ c¸c kü s ph¶i gi¶i quyÕt trong thùc tiÔn s¶n xuÊt lµ ph¶i t×m ra mét ph¬ng tr×nh gi¶i tÝch cã c¸c gi¸ trÞ xÊp xØ gÇn ®óng so víi c¸c gi¸ trÞ cña mét tËp hîp c¸c ®iÓm d÷ liÖu . VÝ dô c¸c nhµ khoa häc thêng ph¶i lµm xÊp xØ mét ph¬ng tr×nh lý thuyÕt víi c¸c d÷ liÖu thùc nghiÖm nµo ®ã ®Ó chøng minh mét lý thuyÕt nµo ®ã. Cßn c¸c kü s thêng ph¶i lµm cho c¸c d÷ liÖu ®o lêng ®îc trªn c¸c m¸y ®o xÊp xØ víi c¸c gi¸ trÞ cã thÓ tÝnh ra ®îc theo mét ph¬ng tr×nh gi¶i tÝch nµo ®ã ®Ó hä cã thÓ chuyÓn ®Çu ra tõ c«ng cô ®o nµy sang tham sè vËt lý ®ang ®îc ®o. Lo¹i bµi to¸n nµy thêng ®îc gäi lµ bµi to¸n t×m hµm sè thùc nghiÖm vµ cã thÓ gi¶i b»ng nhiÒu ph¬ng ph¸p mµ trong ®ã quen thuéc nhÊt lµ ph¬ng ph¸p b×nh ph¬ng tèi thiÓu. §· cã nhiÒu s¸ch m« t¶ c¸c ch¬ng tr×nh mÉu theo c¸c ng«n ng÷ C hoÆc Pascal hoÆc Basic ®Ó thùc hiÖn c«ng viÖc nµy. Trong ch¬ng s¸ch nµy chóng ta sÏ bµn vÒ kh¶ n¨ng cña EXCEL ®Ó gi¶i quyÕt c«ng viÖc nãi trªn . Nãi chung, EXCEL cung cÊp 3 c¸ch thøc ®Ó lËp ra vµ vÏ ®êng cong biÓu diÔn hµm sè xÊp xØ theo c¸c ®iÓm gi¸ trÞ thùc nghiÖm cho tríc . B¹n cã thÓ vÏ ®êng cong biÓu diÔn cña hÇu hÕt c¸c ph¬ng tr×nh víi d÷ liÖu b»ng c¸c lÖnh håi
  2. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ qui tuyÕn tÝnh cµi s½n. B¹n còng cã thÓ sö dông phÐp håi qui tuyÕn tÝnh ®Ó vÏ ®êng cong d÷ liÖu phi tuyÕn b»ng c¸ch chuyÓn ®æi d÷ liÖu mét c¸ch thÝch hîp tr- íc khi vÏ. Ngoµi ra, b¹n cßn cã thÓ vÏ ®êng cong biÓu diÔn c¸c ph¬ng tr×nh phøc t¹p h¬n b»ng c¸ch hiÖu chØnh b»ng tay c¸c hÖ sè cña ph¬ng tr×nh cho ®Õn khi sai sè d (tæng b×nh ph¬ng cña c¸c sai sè gi÷a d÷ liÖu thùc vµ gi¸ trÞ tÝnh theo ®êng cong hµm sè xÊp xØ ) gi¶m ®Õn møc tèi thiÓu, hoÆc hÖ sè t¬ng quan t¨ng tíi cùc ®¹i. Cuèi cïng, trong trêng hîp d÷ liÖu kh«ng thÓ lµm xÊp xØ ®îc víi bÊt cø ®êng cong thÝch hîp nµo th× b¹n cã thÓ sö dông c¸c hµm t×m kiÕm trong b¶ng vµ phÐp néi suy ®Ó cung cÊp c¸c gi¸ trÞ xÊp xØ. 6.1 Sö dông c¸c hµm cµi s½n EXCEL cã kh¶ n¨ng dïng c¸c ®êng cong cµi s½n ( hµm sè cµi s½n ) ®Ó t¹o ra c¸c hµm xÊp xØ gièng nh phÐp håi quy tuyÕn tÝnh béi. Víi kh¶ n¨ng nµy, b¹n cã thÓ t×m ®êng cong hµm xÊp xØ d÷ liÖu víi mét ®êng ®¬n hoÆc víi mét ®a thøc phøc. B¹n cã thÓ hoµn thµnh phÇn lín c¸c c«ng viÖc vÏ ®êng cong b»ng kh¶ n¨ng håi quy tuyÕn tÝnh cña EXCEL 6.1.1 C¸c phÐp tÝnh håi quy Khi b¹n lµm xÊp xØ mét ®êng cong víi mét sè ®iÓm d÷ liÖu nhê sö dông phÐp håi quy, b¹n sÏ lµm gi¶m ®Õn møc tèi thiÓu sai sè b×nh ph¬ng sè d gi÷a c¸c ®iÓm d÷ liÖu vµ ®êng cong (gi¶i tÝch b×nh ph¬ng nhá nhÊt). Sai sè b×nh ph¬ng sè d (E) nµy ®îc tÝnh b»ng ph¬ng tr×nh sau: n 2 ∑ ( y( xi ) − ( yi ) i =1 E= ë ®©y y(xi) -- ph¬ng tr×nh ®êng cong ®ang ®îc lµm xÊp xØ, n -- sè lîng c¸c ®iÓm d÷ liÖu, xi vµ yi -- c¸c to¹ ®é cña c¸c ®iÓm d÷ liÖu. EXCEL sö dông phÐp håi quy tuyÕn tÝnh béi, v× vËy nã gi¶ thiÕt r»ng ®- êng cong y(xi) cã d¹ng: y(x1,i, x2,i, ...) = A + Bx1,i + Cx2,i + ... trong ®ã A, B vµ C -- c¸c hÖ sè cña ph¬ng tr×nh cÇn ®îc hiÖu chØnh ®Ó lµm cho ®- êng cong xÊp xØ víi d÷ liÖu. §Ó thùc hiÖn c«ng viÖc nµy cÇn ph¶i ®a hµm y(x1,i , x2,i, ...) vµo ph¬ng tr×nh tÝnh sai sè d, vµ sau ®ã lÊy ®¹o hµm ph¬ng tr×nh ®ã ®èi víi mét trong c¸c hÖ sè b»ng kh«ng. §iÒu nµy dÉn ®Õn kÕt qu¶ lµ mét ph¬ng tr×nh cho mçi hÖ sè díi d¹ng c¸c Tªn file: Chuong6.doc trang 2/ 10 in ngµy 04/21/11
  3. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ hÖ sè vµ c¸c ®iÓm d÷ liÖu kh¸c, mµ sau ®ã gi¶i c¸c ph¬ng tr×nh víi nh÷ng hÖ sè nµy. C¸c hµm sè håi quy cµi s½n cña EXCEL chó ý ®Õn tÊt c¶ c¸c phÐp tÝnh håi quy tuyÕn tÝnh béi cho b¹n. Cïng víi c¸c hÖ sè cña ph¬ng tr×nh håi quy, EXCEL cßn tÝnh mét sè d÷ liÖu thèng kª vÒ thÝch hîp ®êng cong nh sau : - Sai sè tiªu chuÈn cña íc lîng y (Sy.x). - ChØ sè t¬ng quan (hÖ sè x¸c ®Þnh) (r2). - Sai sè tiªu chuÈn cña c¸c hÖ sè (SA, SB, ...). - Sè liÖu thèng kª F. - Sai sè bËc tù do. - Tæng b×nh ph¬ng cña phÐp håi quy vµ cña c¸c sè d. 6.1.1.1. Sai sè chuÈn cña íc lîng y Sai sè chuÈn cña íc lîng y lµ sù íc tÝnh vÒ sai sè trong ®¬n trÞ y ®· ®îc tÝnh b»ng ph¬ng tr×nh xÊp xØ. Ngêi ta sö dông íc tÝnh nµy, cïng víi phÐp thö t cña Student, ®Ó tÝnh c¸c giíi h¹n tin cËy cña ®êng cong tÝnh to¸n. Giíi h¹n tin cËy lµ mét d¶i gÇn ®êng cong tÝnh to¸n, víi møc ®é tin cËy nµo ®ã (ch¼ng h¹n 95%), giíi h¹n ®êng cong thùc. Sai sè chuÈn cña íc lîng y ®îc tÝnh b»ng ph¬ng tr×nh: n ∑(y i − y ( xi )) 2 S yx = i =1 p ë ®©y p lµ sè bËc tù do (p = n - 2 ®èi víi ®êng cong tuyÕn tÝnh ®¬n gi¶n). 6.1.1.2. ChØ sè t¬ng quan ChØ sè t¬ng quan, hay hÖ sè x¸c ®Þnh, b»ng b×nh ph¬ng cña hÖ sè t¬ng quan (r) vµ lµ mét thíc ®o ®Ó thÊy ®êng cong xÊp xØ gÇn víi c¸c ®iÓm d÷ liÖu ®Õn møc nh thÕ nµo. ChØ sè nµy cã kho¶ng biÕn thiªn tõ 0 ®Õn 1, víi gi¸ trÞ 1 cho thÊy sù xÊp xØ hoµn h¶o víi c¸c ®iÓm d÷ liÖu. ViÖc lµm xÊp xØ ®êng cong tèt sÏ cã mét chØ sè t¬ng quan víi gi¸ trÞ lín h¬n 0,9. ChØ sè t¬ng quan ®îc tÝnh b»ng ph¬ng tr×nh: n ∑(y i − y ( xi )) 2 r 2 = 1− i =1 n ∑(y − < y i =1 i i >) 2 ë ®©y n ∑y i < yi >= i =1 n lµ gi¸ trÞ trung b×nh cña d÷ liÖu y. Tªn file: Chuong6.doc trang 3/ 10 in ngµy 04/21/11
  4. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ 6.1.1.3. Sai sè chuÈn cña c¸c hÖ sè Sai sè chuÈn cña c¸c hÖ sè lµ thíc ®o vÒ sai sè trong mçi hÖ sè håi quy. Sai sè chuÈn trong hÖ sè thø nhÊt (SA ) ®îc tÝnh b»ng c¸ch sö dông sai sè chuÈn cña íc lîng y : 1 + ( x) 2 S y−x 2 n n ∑ ( x − ( x) ) i =1 i SA = n ∑x i =1 i Trong ®ã (x) = n T¸c dông chÝnh cña sai sè chuÈn cña c¸c hÖ sè lµ ®Ó thö mét hÖ sè xem liÖu qua thèng kª nã cã gi¸ trÞ b»ng 0 hay kh«ng. V× tÊt c¶ c¸c hÖ sè nh©n víi sè h¹ng x tuyÕn tÝnh cho nªn nÕu mét hÖ sè b»ng 0 th× kh«ng cã t¬ng quan gi÷a sè h¹ng x ®ã víi d÷ liÖu y. §Ó thö mét hÖ sè, ta lÊy gi¸ trÞ t cña Student thÝch hîp víi kho¶ng tin cËy yªu cÇu (1 - α) vµ c¸c bËc tù do (p), vµ tÝnh: NÕu ph¬ng tr×nh trªn ®óng, th× hÖ sè nµy lµ ®¸ng kÓ vµ c¸c gi¸ trÞ cña y sÏ phô thuéc vµo c¸c gi¸ trÞ cña x mµ nh©n víi hÖ sè nµy. NÕu ph¬ng tr×nh trªn sai, th× c¸c gi¸ trÞ y kh«ng phô thuéc vµo c¸c gi¸ trÞ x ®ã, vµ ta nªn sö dông gi¸ trÞ 0 ®èi víi hÖ sè nµy. C¸c hÖ sè cßn l¹i ®îc xö lý theo c¸ch t¬ng tù. §Ó «n l¹i chót Ýt vÒ lý thuyÕt thèng kª , xin b¹n h·y t×m mét cuèn s¸ch viÕt vÒ thèng kª kü thuËt . Trong s¸ch ®ã còng sÏ cung cÊp cho b¹n nhiÒu th«ng tin h¬n vÒ c¸ch sö dông c¸c con sè thèng kª nµy nh thÕ nµo vµ sÏ bao cho mét b¶ng c¸c gi¸ trÞ t cña Student. Nãi chung, nÕu gi¸ trÞ tuyÖt ®èi cña hÖ sè cã ®é lín lín h¬n sai sè tiªu chuÈn cña hÖ sè ®ã th× b¹n cã thÓ ch¾c ch¾n r»ng nã lµ ®¸ng kÓ. NÕu b¹n cã Ýt nhÊt 4 bËc tù do (vÝ dô nh 6 ®iÓm d÷ liÖu cho mét sù xÊp xØ tuyÕn tÝnh) th× gi¸ trÞ t cña Student víi kho¶ng tin cËy 95% chØ gÇn b»ng 2,1 vµ víi nhiÒu bËc tù do h¬n th× gi¸ trÞ t nµy sÏ gi¶m xuèng. Do vËy, mét quy t¾c ngãn tay c¸i thÝch hîp lµ: nÕu gi¸ trÞ tuyÖt ®èi cña hÖ sè lín h¬n sai sè tiªu chuÈn cña hÖ sè ®ã 2,5 lÇn th× hÖ sè lµ ®¸ng kÓ. NÕu nã nhá h¬n, b¹n sÏ ph¶i t×m gi¸ trÞ t ®óng cña Student trong s¸ch thèng kª vµ ®a nã vµo ph¬ng tr×nh trªn ®Ó biÕt ch¾c liÖu hÖ sè nµy cã ®¸ng kÓ kh«ng. 6.1.1.4. Sè thèng kª F Ngêi ta thêng sö dông sè liÖu thèng kª F víi mét b¶ng c¸c gi¸ trÞ F ®Ó x¸c ®Þnh xem d÷ liÖu cã thùc sù theo d¹ng ®êng cong kh«ng, hoÆc liÖu sù xÊp xØ vÒ mÆt h×nh thøc nµy cã ph¶i chØ do c¸c biÕn thiªn ngÉu nhiªn trong d÷ liÖu hay kh«ng. Còng nh víi c¸c phÐp thö gi¸ trÞ t cña Student, ®Ó sö dông sè liÖu thèng kª F, b¹n cÇn cã mét b¶ng c¸c gi¸ trÞ F tõ tËp b¶ng to¸n häc hoÆc tõ mét cuèn s¸ch vÒ To¸n häc thèng kª. Tõ sè bËc tù do vµ giíi h¹n tin cËy (ch¼ng h¹n 95%), b¹n lÊy Tªn file: Chuong6.doc trang 4/ 10 in ngµy 04/21/11
  5. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ mét gi¸ trÞ F trong b¶ng vµ so s¸nh víi gi¸ trÞ F tÝnh to¸n. Chõng nµo mµ gi¸ trÞ F tÝnh to¸n lín h¬n gi¸ trÞ F trong b¶ng th× sù xÊp xØ nµy lµ do mèi t¬ng quan cã thËt chø kh«ng ph¶i do ngÉu nhiªn. B¶ng gi¸ trÞ F ®ßi hái 2 gi¸ trÞ bËc tù do thªm vµo giíi h¹n tin cËy. Sè h¹ng thø nhÊt, nf1, b»ng sè cña c¸c hÖ sè trong ph¬ng tr×nh håi quy trõ ®i mét. Sè h¹ng thø hai, p, lµ sè bËc tù do tiªu chuÈn vµ b»ng sè c¸c tËp d÷ liÖu trõ ®i sè c¸c hÖ sè trong ph¬ng tr×nh ®ang ®îc lµm xÊp xØ víi d÷ liÖu. Gi¸ trÞ p lµ gi¸ trÞ bËc tù do ®- îc ®a ra bëi hµm LINEST vµ ®îc sö dông cho phÐp thö gi¸ trÞ t cña Student. 6.1.1.5. Sè bËc tù do Sè bËc tù do, p, b»ng sè c¸c ®iÓm d÷ liÖu trõ ®i sè c¸c hÖ sè håi quy. Ph- ¬ng tr×nh cña mét ®êng cã hai hÖ sè: hÖ sè gãc vµ h»ng sè hoÆc sè h¹ng chªnh lÖch y. NÕu b¹n cã mêi ®iÓm d÷ liÖu th× sè bËc tù do sÏ lµ 8 (=10-2). CÇn cã sè bËc tù do cïng víi nhiÒu b¶ng thèng kª ®Ó tÝnh c¸c giíi h¹n tin cËy. 6.1.1.6. Tæng b×nh ph¬ng cña phÐp håi quy vµ c¸c sè d Hai sè liÖu thèng kª tæng b×nh ph¬ng nµy lµ phÐp ®o sai sè vÉn cßn tån t¹i trong viÖc lµm xÊp xØ ®êng cong. Tæng b×nh ph¬ng cña phÐp håi quy b»ng tæng c¸c sai ph©n gi÷a c¸c gi¸ trÞ d÷ liÖu y vµ møc trung b×nh cña c¸c gi¸ trÞ d÷ liÖu y b×nh ph¬ng: n ∑(y i =1 - (y))2 i Do vËy, nã lµ phÐp ®o sù ph©n t¸n cña d÷ liÖu ë gÇn møc trung b×nh. Tæng b×nh ph¬ng cña c¸c sè d lµ tæng cña c¸c sai ph©n gi÷a c¸c gi¸ trÞ d÷ liÖu y ban ®Çu vµ c¸c gi¸ trÞ d÷ liÖu y ®îc tÝnh t¬ng øng trªn ®êng cong b×nh ph¬ng: n ∑( y − y ( xi ) ) 2 i i =1 Do vËy, nã lµ phÐp ®o sù ph©n t¸n cña d÷ liÖu y ë gÇn ®êng håi quy. Khi b¹n chia c¸c gi¸ trÞ nµy cho sè bËc tù do, th× b¹n sÏ nhËn ®îc ph¬ng sai cña d÷ liÖu ë gÇn møc trung b×nh vµ ph¬ng sai l©n cËn ®êng håi quy. LÊy c¨n bËc hai cña ph¬ng sai, vµ b¹n sÏ cã ®îc ®é lÖch chuÈn cña d÷ liÖu ë gÇn møc trung b×nh vµ ®é lÖch chuÈn cña d÷ liÖu l©n cËn ®êng håi quy. 6.1.2. C¸c phÐp tÝnh håi quy tuyÕn tÝnh 6.1.2.1. PhÐp håi quy víi c¸c hµm b¶ng tÝnh Trong EXCEL chóng ta thùc hiÖn phÐp gi¶i tÝch håi quy tuyÕn tÝnh b»ng c¸c hµm LINEST, LOGEST, TREND vµ GROWTH . Hµm LINEST thùc hiÖn phÐp håi quy tuyÕn tÝnh ®¬n gi¶n trªn mét tËp ®iÓm d÷ liÖu. LOGEST lµ biÕn thÓ cña phÐp håi quy tuyÕn tÝnh mµ lµm xÊp xØ ph¬ng tr×nh sau víi d÷ liÖu: y = A(Bx1) (Cx2)... C¸c hµm LINEST vµ LOGEST khai b¸o c¸c hÖ sè cña c«ng thøc. Hµm TREND vµ GROWTH khai b¸o ®êng cong ®îc thÝch øng víi d÷ liÖu. TÊt c¶ 4 hµm nµy ®Òu khai b¸o c¸c m¶ng d÷ liÖu chø kh«ng ph¶i lµ c¸c gi¸ trÞ ®¬n. B¹n ph¶i ®a 4 hµm nµy vµo trong nhãm c¸c «, hoÆc sö dông hµm INDEX ®Ó rót mét phÇn tö ®¬n tõ m¶ng d÷ liÖu ®ã. Ch¬ng 1 m« t¶ c¸ch ®a mét hµm vµo trong khèi «. Khi b¹n ®a mét hµm m¶ng vµo nhãm c¸c «, b¹n kh«ng thÓ thay ®æi bÊt cø mét « riªng lÎ nµo trong nhãm ®ã. B¹n ph¶i thay ®æi toµn bé nhãm, hoÆc b¹n ph¶i xo¸ toµn bé Tªn file: Chuong6.doc trang 5/ 10 in ngµy 04/21/11
  6. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ nhãm vµ sau ®ã míi thùc hiÖn nh÷ng thay ®æi. Hµm LINEST vµ LOGEST cã có ph¸p sau: LINEST(y-array, x array, const, statistics) LOGEST(y-array, x array, const, statistics) ë ®©y y-array nãi ®Õn c¸c ®iÓm d÷ liÖu y, x-array nãi ®Õn mét hay nhiÒu tËp ®iÓm d÷ liÖu x, const lµ gi¸ trÞ logic kiÓm tra sè h¹ng kh«ng ®æi, vµ statistics lµ gi¸ trÞ logic x¸c ®Þnh liÖu cã ®a ra c¸c gi¸ trÞ thèng kª hay kh«ng. NÕu sè h¹ng x-array bÞ lo¹i bá th× sÏ sö dông tËp hîp c¸c sè {1, 2, 3,...}. NÕu sè h¹ng const lµ True hoÆc bÞ lo¹i bá th× sè h¹ng kh«ng ®æi trong viÖc vÏ ®êng cong (A) ®îc tÝnh b×nh thêng. NÕu const lµ False th× sè h¹ng kh«ng ®æi nµy buéc ph¶i b»ng 0 ®èi víi LINEST hoÆc b»ng 1 ®èi víi LOGEST. NÕu sè h¹ng statistics ®óng th× mét b¶ng gåm t¸m gi¸ trÞ thèng kª trë lªn ®îc ®a ra cïng víi c¸c hÖ sè cña ph¬ng tr×nh. 6.1.2.2. VÝ dô vÒ ®é dÉn nhiÖt cña chÊt b¸n dÉn Gallium Arsenide. Sau ®©y chóng ta sÏ vÏ ®å thÞ d÷ liÖu thùc nghiÖm vÒ ®é dÉn nhiÖt cña chÊt asenua gali (GaAs) víi mét ®êng cong ®å thÞ nµo ®ã. B¶ng 6.1 liÖt kª mét vµi d÷ liÖu thùc nghiÖm vÒ sù phô thuéc nhiÖt ®é cña ®é dÉn nhiÖt cña chÊt asenua gali ®îc pha chÊt kÝch t¹p nÆng, kiÓu-p. Tríc tiªn, chóng ta h·y thö lµm xÊp xØ d÷ liÖu kiÓu tuyÕn tÝnh ®¬n. NhiÖt ®é vµ c¸c gi¸ trÞ ®é dÉn nhiÖt cho b¶ng tÝnh ®îc liÖt kª trong B¶ng 6.1. B¶ng 6.1. §é dÉn nhiÖt cña chÊt asenua gali (GaAs) ®îc pha chÊt kÝch t¹p nÆng . T (K) K(W/cm-K) 250 0,445 300 0,362 350 0,302 400 0,256 450 0,223 500 0,197 550 0,176 600 0,158 650 0,144 700 0,132 750 0,121 800 0,112 850 0,103 Sau ®©y lµ tr×nh tù lµm viÖc trªn EXCEL : 1. B¾t ®Çu víi mét b¶ng tÝnh míi më réng hÕt cì. 2. §Æt cét D réng 2 ký tù vµ cét H réng 18 ký tù. 3. Gâ §é DÉn nhiÖt cña chÊt GaA , LËp §êng cong ®å thÞ xÊp xØ trong « A1. 4. Trong nh÷ng « A3:B4, gâ vµ c¨n gi÷a c¸c ®Ò môc díi ®©y: A3: T B3: K A4: (K) B4: (W/cm - K) Tªn file: Chuong6.doc trang 6/ 10 in ngµy 04/21/11
  7. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ 5. Trong c¸c « A5:A17, gâ c¸c gi¸ trÞ nhiÖt ®é ë B¶ng 6.1. 6. Trong c¸c « B5:B17, gâ gi¸ trÞ ®é dÉn nhiÖt ë B¶ng 6.1. LËp c«ng thøc ®Ó tÝnh c¸c íc tÝnh y cña sù lµm xÊp xØ tuyÕn tÝnh. 7. Trong « C3, gâ K1 vµ c¨n gi÷a. 8. Trong « E14 gâ : "Ghi chó : K1 lµ íc lîng cña K" 9. §Æt tªn c¸c « F7 vµ G7 lÇn lît lµ B vµ A. 10. Trong « C5, gâ =B*A5+A vµ sao chÐp nã sang c¸c « C6:C17. 11. §Þnh d¹ng c¸c « C5:C17 lµ 0.000. §Æt mét vÞ trÝ cho b¶ng chøa c¸c hÖ sè håi quy vµ sè liÖu thèng kª. Víi mçi hÖ sè håi quy th× b¶ng håi quy ®Çy ®ñ sÏ cã 5 hµng vµ 1 cét. §èi víi viÖc lµm xÊp xØ ®êng cong tuyÕn tÝnh ®¬n th× b¶ng nµy cã 2 cét. 12. NhËp c¸c ®Ò môc díi ®©y vµo c¸c cét E, F, G vµ H: E5: B¶ng Håi quy F6: B G6: A H7 : C¸c hÖ sè E9: r^2 F12: Håi quy G12: Residual H8: Sai sè Std cña c¸c HÖ sè E10: F H9: Sai sè Std cña íc lîng cña Y E11: Tæng Sq. H10: §é tù do 13.VÏ c¸c « sau: E5: F5 F6 E6: H6 G6 E7: H11 F12 E12: H12 G12 F7: G11 B©y giê h·y tÝnh c¸c hÖ sè. 14. Chän c¸c « F7:G11 vµ gâ c«ng thøc: =LINEST(B5:B17,A5:A17,True,True) 15. Ên Ctrl-Shift-Enter ®Ó ®a c«ng thøc trªn vµo tÊt c¶ c¸c « díi d¹ng mét m¶ng. 16. T¾t c¸c ®êng khung viÒn b»ng lÖnh Display trªn b¶ng chän “Option” B©y giê b¶ng tÝnh sÏ gièng nh H×nh 6.1. Khi nh×n vµo cét B vµ cét C råi so s¸nh viÖc lµm xÊp xØ tuyÕn tÝnh víi c¸c d÷ liÖu thùc nghiÖm vµ b¹n cã thÓ thÊy r»ng b¹n ®· lµm xÊp xØ ®îc chiÒu híng tæng thÓ cña d÷ liÖu, nhng c¸c ®iÓm riªng lÎ th× hoµn toµn kh«ng xÊp xØ. KiÓm tra r2, b¹n thÊy r»ng nã cã gi¸ trÞ lµ 0,875. nghÜa lµ xÊp xØ tuyÕn tÝnh rÊt kÐm chÝnh x¸c trong trêng hîp vÝ dô nµy. Tªn file: Chuong6.doc trang 7/ 10 in ngµy 04/21/11
  8. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ H×nh 6.1: §é dÉn nhiÖt cña GaAs: ChuÈn bÞ t×m ®êng cong xÊp xØ tuyÕn tÝnh. H×nh 6.2 lµ mét ®å thÞ nèi c¸c ®iÓm d÷ liÖu thùc nghiÖm vµ mét ® êng th¼ng xÊp xØ tuyÕn tÝnh ( b¹n rÊt dÔ dµng vÏ ra ®å thÞ nµy nhê c«ng cô Chart Wizard cña EXCEL ). §å thÞ nµy kh¼ng ®Þnh r»ng c¸c d÷ liÖu hoµn toµn kh«ng xÊp xØ. H×nh 6.2: Thö lËp ®êng th¼ng K1 xÊp xØ biÓu thÞ ®é dÉn nhiÖt cña GaAs . V× c¶ GaAs vµ Silicon ®Òu lµ hai chÊt b¸n dÉn nªn b¹n h·y thö lµm xÊp xØ ph¬ng tr×nh ®èi víi Silicon víi d÷ liÖu ®èi víi GaAs. §é dÉn nhiÖt cña Silicon xÊp xØ víi ph¬ng tr×nh ®¬n gi¶n sau ®©y: K0 K= ( T − T0 ) ë ®©y K0 vµ T0 lµ c¸c h»ng sè cÇn ®îc x¸c ®Þnh. Tuy nhiªn,®©y kh«ng lµ ph¬ng tr×nh tuyÕn tÝnh vµ kh«ng thÓ sö dông nã trong ch¬ng tr×nh håi quy tuyÕn tÝnh. Nhng khi gi¶i ph¬ng tr×nh trªn ®èi víi nhiÖt ®é ta sÏ cã : 1   K 0 + T0 T=  K  Nh vËy ®· t¹o ra ph¬ng tr×nh víi T lµ tuyÕn tÝnh theo biÕn 1/K, chø kh«ng ph¶i lµ t¹o ra ph¬ng tr×nh phi tuyÕn víi K theo biÕn sè T. B©y giê cã thÓ dÔ dµng s¾p xÕp l¹i b¶ng tÝnh nµy ®Ó tÝnh 1/K vµ sö dông nã díi d¹ng miÒn-x vµ T díi d¹ng miÒn-y. H·y thùc hiÖn c¸c thao t¸c sau ®©y : 1. Sö dông l¹i b¶ng tÝnh ®· t¹o ra tõ vÝ dô tríc . §Çu tiªn, b¹n lu nã víi mét tªn kh¸c nÕu b¹n muèn gi÷ nã. Tªn file: Chuong6.doc trang 8/ 10 in ngµy 04/21/11
  9. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ 2. NhÊn chuét vµo tiªu ®Ò cña cét C ®Ó chän toµn bé cét nµy vµ chän lÖnh Insert trªn b¶ng chän Edit ®Ó chÌn mét cét míi. 3. Thay ®æi réng cña c¸c cét tõ A ®Õn D thµnh 7 ký tù. 4. Gâ §é dÉn nhiÖt cña chÊt GaAs; Håi quy tuyÕn tÝnh cña c«ng thøc trong « A1. 5. Gâ 1/K trong « C3. 6. Trong « C4, gâ =1/B4 vµ sao chÐp nã sang « C5:C16. LËp c«ng thøc íc lîng míi cho K. 7. Gâ K0 trong « G6 vµ T0 trong « H6. 8. Chän c¸c « G6:H7, chän lÖnh Insert/ Name/ Create. Trªn b¶ng chän Create Names h·y chän Top Row vµ nhÊn OK. Thao t¸c nµy cã t¸c dông lµ ®Æt tªn cho c¸c « G7 vµ H7 lµ K0 vµ T0. Sö dông lÖnh Define Name ®Ó xo¸ c¸c khai b¸o cò lµ A vµ B. 9. Trong « D5, h·y K0/(A4-T0) vµ sao chÐp nã sang c¸c « D6:D17. B©y giê h·y tÝnh phÐp håi quy. 10. Chän c¸c « G7:H11 vµ gâ c«ng thøc =LINEST(A4:A16, C4:C16, TRUE, TRUE). 11. Ên Ctrl-Shift-Enter . Lóc nµy, b¶ng tÝnh sÏ gièng nh H×nh 6.3. Chó ý r»ng gi¸ trÞ cña r2 ®· tèt lªn rÊt nhiÒu; nã cã gi¸ trÞ lµ 0,998, chøng tá sù xÊp xØ tèt víi c¸c ®iÓm d÷ liÖu. H×nh 6.4 cho thÊy r»ng ®êng håi quy theo d÷ liÖu tèt h¬n nhiÒu so víi ®êng cong tuyÕn tÝnh. H×nh 6.3: §é dÉn nhiÖt cña GaAs: So s¸nh ®êng cong xÊp xØ vµ ®êng cong sè liÖu Tªn file: Chuong6.doc trang 9/ 10 in ngµy 04/21/11
  10. PGS.TS. NguyÔn ViÕt Trung . Sö dông EXCEL-97 gi¶i c¸c bµi to¸n Khoa häc-Kü thuËt Ch¬ng 6 :LËp c¸c ®êng cong biªu diÔn ®å thÞ H×nh 6.4: So s¸nh c¸c d÷ liÖu thùc nghiÖm víi lµm xÊp xØ ®êng cong phi tuyÕn. H·y lu ý r»ng b¹n ®· lµm xÊp xØ mét ®êng cong phi tuyÕn b»ng ch¬ng tr×nh lµm xÊp xØ ®êng cong tuyÕn tÝnh. Còng nªn nhí r»ng khi b¹n lµm xÊp xØ mét ph- ¬ng tr×nh tuyÕn phi b»ng c¸ch biÕn ®æi nã thµnh c¸i mµ b¹n tÝnh lµ xÊp xØ tèt nhÊt víi ph¬ng tr×nh ®· biÕn ®æi chø kh«ng ph¶i lµ víi ph¬ng tr×nh ban ®Çu. Trong phÇn lín c¸c trêng hîp, ®iÒu nµy sÏ kh«ng t¹o ra sù kh¸c biÖt lín, nhng víi c¸c hµm mò vµ hµm loga , b¹n cã thÓ nhËn thÊy r»ng d÷ liÖu t¹i mét ®Çu cña ®êng cong l©n cËn víi ®êng cong h¬n lµ d÷ liÖu ë ®Çu kia. Tªn file: Chuong6.doc trang 10/ 10 in ngµy 04/21/11
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
2=>2