ãã1ï¼EXCELè¡¨æ ¼ä¸æ两ç»å·²ç¥çæ°æ®(x1,x2,x3,...xn),(y1,y2,y3,...yn),æ¯ä¸å¯¹x,yç¸å¯¹åºãéè¦åä¸ä¸ªå½æ°æå ¬å¼ï¼ä½¿å¾ä»»æè¾å ¥ä¸ä¸ªä»äºx1å°xnä¹é´çXï¼è¯¥å½æ°è½è®¡ç®åºå¯¹åºçYã
ãã2ï¼å¦æè¾å ¥çXæ£å¥½çäºx1,x2,x3,...xnä¸çæä¸ä¸ªï¼è¿Yçç»æèªç¶ä¹æ¯y1,y2,y3,...ynä¸å¯¹åºçä¸ä¸ªãå¦æè¾å ¥çXä»äºæ两个xä¹é´ï¼ä¾å¦ä»äºx2,x3ä¹é´ï¼x2<X<x3ï¼,åYåºå¨y2,y3ä¹é´å线æ§æå¼ï¼å³(x3-X)/(X-x2)=(y3-Y)/(Y-y2)
ãã举ä¾ï¼
ãã对äºx:(10,11,12,...20)
ããy:(50,60,80,...200)
ããå¦æX=10ï¼åY=50
ããX=11ï¼åY=60
ããX=11.1ï¼åY=62
ããX=11.5ï¼åY=70
ãã3ï¼Xå¼å¨B6åå æ ¼è¾å ¥ï¼Yå¼æ¾ç¤ºå¨B8ï¼B8åå æ ¼çå ¬å¼å¦ä¸ï¼
ãã=IF(ISERROR(MATCH(B6,B1:D1,0)),INDEX(B2:D2,MATCH(B6,B1:D1,1))+(INDEX(B2:D2,MATCH(B6,B1:D1,1)+1)-INDEX(B2:D2,MATCH(B6,B1:D1,1)))*(B6-INDEX(B1:D1,MATCH(B6,B1:D1,1))/(INDEX(B1:D1,MATCH(B6,B1:D1,1)+1)-INDEX(B1:D1,MATCH(B6,B1:D1,1)))),INDEX(B2:D2,MATCH(B6,B1:D1,1)))
ãã4ï¼å ¬å¼åªç¨å°äºIFãIserrorãindexãMatchå个å½æ°ã
ãã5ï¼B7åå æ ¼çå ¬å¼å¦ä¸ï¼
ãã=IF(ISERROR(MATCH(B6,B1:D1,0)),"æ å¹é ",INDEX(B2:D2,MATCH(B6,B1:D1,1)))