纺织应用计算机实习拟合曲线.doc

纺织应用计算机实习一、实习目的（1）运用所学计算机应用的知识，结合纺织实际应用培养综合分析设计的能力（2）初步掌握解决实际应用问题时所应具有的查阅资料、技术标准和规范，以及绘图、调试、编程等能力，为毕业设计及今后从事专业工作打下基础。二、实习项目（1）利用EXCEL进行一元线性曲线拟合（2）利用EXCEL进行可化为线性的非线性曲线拟合（3）利用EXCEL进行无确定函数数据的多项式拟合三、实习内容（一）一元线性曲线拟合线性拟合是一种特殊的曲线拟合，其目的是将选中的数据点以X为自变量，以Y为因变量拟合为直线（用于建立标准曲线）。假设回归拟合的函数形式为y=a+bx,拟合后结果显示在EXCEL表格指定位置。为方便起见我利用EXCEL提供的随机函数产生一定量均匀分布的误差，假定方程系数a2和b3，来形成需拟合的数据如下图：xyy随机150.2867595.286759280.7291458.7291453110.20133511.201334140.89988614.899895170.37628717.376296200.10401120.104017230.59406123.594068260.61876226.618769290.79880629.7988110320.12215632.1221611350.58234435.5823412380.67984938.6798513410.82050241.8205（1）对上面的数据进行直线拟合。启动EXCEL，在工具菜单中选择数据分析，出现数据分析对话框，选择回归，然后按要求选择对应的数据区。（2）输出结果，对话框中选择线性拟合图即可得拟合图。得到的相关指标如下：Multiple R 0.999747R Square 0.999493F 21702.88Significance F 1.77E-19 CoefficientsP-valueIntercept2.3892771.42E-08X Variable 13.0192671.77E-19若保留2位有交数字，则该回归方程的截距为2.4，斜率为3.0，所以回归方程的表达式为：B=2.4+3.0A；方差分析结果：F=6378.04621702.88significance F0.01,所以所建回归方程非常显著下图为回归分析得到的拟合曲线。(二) 非线性曲线拟合非线性拟合，自定义某函数，将其变经变换后，转化成线性函数进行拟合，自定义函数如下：， 式中：为随机误差，由RAND()函数产生。将上式两边取对数后得：取a=2, 来形成需拟合的数据如下：xx2lnyy随机1.62.560.9098595.12167.33541.41.960.7877173.9250.400441.31.690.5518253.3829.370771.21.440.8482932.8817.814271.62.560.5469735.12167.33541.52.250.907284.590.017131.83.240.8196986.48651.9709110.57387327.3890561.21.440.1121582.8817.814271.11.210.3375562.4211.245861.52.250.518074.590.017131.72.890.8313125.78323.75921.41.960.0655363.9250.400441.62.560.8620035.12167.3354240.97194382980.958（1）对上面的数据进行直线拟合。启动EXCEL，在工具菜单中选择数据分析，出现数据分析对话框，选择回归，然后按要求选择对应的数据区。（2）输出结果，对话框中选择线性拟合图即可得拟合图。得到的相关指标如下：Multiple R 0.6817R Square 0.464715F 11.28612 Significance F 0.005126CoefficientsP-valueIntercept-2439.370.011911X Variable 11891.0360.005126若保留2位有效数字，所以回归方程的表达式为： 方差分析结果：F=11.28612significance F=0.0051260.01,所以所建回归方程非常显著下图为回归分析得到的拟合曲线（三）多项式拟合 对用多项进行逼近，由其产生的 n对观测数据(x1,y1),(x2,y2),(xn,yn) (xIxI+1)，用 m-1次多项式 进行曲线拟合（设定误差为0.1），可将其转换为多元线性回归，所需拟合的数据如下：xx2x3x4x5x6x7y1.62.564.0966.553610.4857616.7772226.84355167.33541.41.962.7443.84165.378247.52953610.5413550.400441.31.692.1972.85613.712934.8268096.27485229.370771.21.441.7282.07362.488322.9859843.58318117.814271.62.564.0966.553610.4857616.7772226.84355167.33541.52.253.3755.06257.5937511.3906317.0859490.017131.83.245.83210.497618.8956834.0122261.222651.970911111117.3890561.21.441.7282.07362.488322.9859843.58318117.814271.11.211.3311.46411.610511.7715611.94871711.245861.52.253.3755.06257.5937511.3906317.0859490.017131.72.894.9138.352114.1985724.1375741.03387323.75921.41.962.7443.84165.378247.52953610.5413550.400441.62.564.0966.553610.4857616.7772226.84355167.33542481632641282980.958具体步骤如下：（1） 在“X值输入区域”，要输入或选择x,x2,x3三列的数据区域。确定后便得到如下数据： Multiple R 0.991452R Square 0.982976F 211.7209 Significance F 5.23E-10 CoefficientsP-valueIntercept-23245.50.000148X Variable 154037.845.67E-05X Variable 2-41303.61.91E-05X Variable 310408.895.57E-06计算结果的分析方法与一元回归相同。由图中可知，加归方程为：y=-23245.5+54037.84x-41303.6x2+10408.89x3（2）x8x9x10x11x12x13x14y42.9496768.71948109.9512175.9219281.475450.36720.5759167.335414.7578920.6610528.9254740.4956556.6939179.37148111.120150.400448.15730710.604513.7858517.921623.2980930.2875139.3737629.370774.2998175.159786.1917367.4300848.916110.6993212.8391817.8142742.9496768.71948109.9512175.9219281.475450.36720.5759167.335425.6289138.4433657.6650486.49756129.7463194.6195291.929390.0171342.9496768.71948109.9512175.9219281.475450.36720.5759167.335414.7578920.6610528.9254740.4956556.6939179.37148111.120150.400448.15730710.604513.7858517.921623.2980930.2875139.3737629.370774.2998175.159786.1917367.4300848.916110.6993212.8391817.8142742.9496768.71948109.9512175.9219281.475450.36720.5759167.335425.6289138.4433657.6650486.49756129.7463194.6195291.929390.0171314.7578920.6610528.9254740.4956556.6939179.37148111.120150.4004442.9496768.71948109.9512175.9219281.475450.36720.5759167.33542565121024204840968192163842980.958按照上述方法继续操作，直到相临两式误差小于0. 1。所得多项式分别如下：Y3=-23245.5+54037.84x-41303.6x2+10408.89x3Y4=50254.84-154757x+176837.5x2-88973x3+16666.41x4Y5=-93251.6+356606.9x-541226x2+407848.3x3-152767x4+22794x5Y6=141494.5-649147x+1233715x2-1243921x3+702281x4-210699x5+26283.61x6Y11=-10788.8+33649.02x-95242.1x3+447949x5-890835x6+888274.4x7-528476x8+190971.7x9-38959.6x10+3464.488x11Y12=2016.225-5028.46x+7522.837x3-9129.97x5+8449.847x7-9046.16x9+7358.718x10-2444.02x11+308.3658x12Y13=211.4054-1054.