Excel最小二乘法计算一二三维平面度方法总结

序号中值上公差下公差上限下限样品1

样品2

10.5000.0250.0250.5250.4750.5100.53420.5000.0250.0250.5250.4750.5160.53830.5000.0250.0250.5250.4750.5080.53640.5000.0250.0250.5250.4750.5090.52550.5000.0250.0250.5250.4750.4980.52660.5000.0250.0250.5250.4750.5050.52970.5000.0250.0250.5250.4750.5000.51680.5000.0250.0250.5250.4750.5040.52490.5000.0250.0250.5250.4750.5400.520100.5000.0250.0250.5250.4750.5120.524110.5000.0250.0250.5250.4750.5080.528120.5000.0250.0250.5250.4750.5120.520130.5000.0250.0250.5250.4750.5070.518140.5000.0250.0250.5250.4750.5040.517150.5000.0250.0250.5250.4750.5040.527160.5000.0250.0250.5250.4750.5120.519170.5000.0250.0250.5250.4750.5050.516180.5000.0250.0250.5250.4750.5050.512190.5000.0250.0250.5250.4750.5180.515200.5000.0250.0250.5250.4750.5110.514210.5000.0250.0250.5250.4750.5100.518220.5000.0250.0250.5250.4750.5080.512230.5000.0250.0250.5250.4750.5090.512240.5000.0250.0250.5250.4750.5240.503250.5000.0250.0250.5250.4750.5280.510260.5000.0250.0250.5250.4750.5270.512270.5000.0250.0250.5250.4750.5320.512280.5000.0250.0250.5250.4750.5250.507290.5000.0250.0250.5250.4750.5250.523300.5000.0250.0250.5250.4750.5260.521310.5000.0250.0250.5250.4750.5260.530320.5000.0250.0250.5250.4750.5230.509330.5000.0250.0250.5250.4750.5110.522340.5000.0250.0250.5250.4750.5180.509350.5000.0250.0250.5250.4750.5000.508360.5000.0250.0250.5250.4750.5070.51537

0.500

0.025

0.025

0.525

0.475

0.506

0.512

平面度改动后0.00000.05000.00000.0500.0000.0470.030

残差最大

0.02970.0170残差最小

-0.0176-0.0130斜率b 此处把序号看成X，二维时将序号变为另一维

SLOPE函数

平面度=残差最大-残差最小

常数系数

a

0.507586160.52840295

10.001683170.0056320.008079860.010910643-0.00031710.008710334-0.0001854-0.00112265-0.01092040.000585616-0.00444660.003380787-0.0098613-0.00918968-0.0058046-0.000173590.02966898-0.0039521100.001555320.0009367611-0.00331570.0049377120.00092925-0.002056913-0.0045284-0.003972814-0.0073714-0.004456915-0.00829810.0064649516-0.0003782-0.000786317-0.0081488-0.003585318-0.0080039-0.0076799190.0047382-0.003195820-0.0025056-0.003980121-0.00353160.0001420722-0.0061783-0.005301123-0.0056395-0.0051147240.00971704-0.0129985250.01259064-0.005377260.01123339-0.0032914270.01606359-0.0030905280.00880859-0.0073855290.008593520.00930244300.009150130.00771823310.008923680.01704320.00601104-0.002631733-0.00651540.010******* 5.1408E-05-0.001605935-0.0176358-0.002766536-0.01166150.0053766237

-0.0125503

0.00251507

残差=实际值-拟合

值

INTERCEPT函数

0.49730.551565120.526713560.528383390.538229590.535284270.527121310.5327763 0.0077-0.00072510.005570560.00575620.00798625-0.00332670.009162060.0076292 0.011-0.00508860.012974380.002326460.011442890.005026740.020628390.01062993 0.0022-0.0072431-0.0006185-0.006721-0.0021589-0.00889850.011912790.00536548 5E-05-0.0042197-0.0004939-0.00273050.00323253-0.00285080.00587937-0.0022611 -0.004-0.00149070.000110230.0022950.001375410.002596670.00201904-0.0018752 -0.003-0.0066864-0.0065895-0.0080727-0.0043962-0.0091242-0.0008936-0.0060459 -0.007-0.0036837-0.009834-0.0159412-0.0098608-0.0145233-0.0059925-0.0101289 3E-05-0.004873-0.0001815-0.0083697-0.0027938-0.0087207-0.0041136-0.0065816 -0.0060.00306450.009969420.006730420.0110440.00121203-0.001170.00466602 0.00350.006166710.011974790.006477620.005292580.00754249-0.0004645-0.0028599 -0.0060.00086428-0.0031731-0.0011867-0.003323-0.002114-0.0043018-0.0043585 -0.004-0.0034113-0.0055242-0.0099219-0.0041679-0.006341-0.0037744-0.0027563 -0.003-0.0001522-0.0087473-0.0039402-0.0105709-0.0069138-0.0049149-0.0068436 -0.0050.004658290.002805540.00283111-0.00110370.0015884-0.00403610.0001035 0.00050.016895940.010255890.009331260.012634210.01112130.001508260.00695098 -0.0020.00843169-0.00150920.015017040.001973430.0101561-0.00233380.0081012 -0.0020.00272909-0.00425730.00495284-0.00784190.00539949-0.0115709-0.0048975 -0.005-0.0019463-0.0123081-0.0069824-0.01008650.00067283-0.0120435-0.0093949 0.0030.00191274-0.0065312-0.0031007-0.00708970.00420007-0.00448420.00091779 0.00280.00302322 2.1174E-050.0089707-0.00312250.0076021-0.00540510.00366462 -7E-040.015961020.006671830.01527075-0.00168410.017334780.000238720.01001192 0.00710.00114494-0.0113157-0.004904-0.00999870.00461989-0.0077017-0.0024263 0.0026-0.0112992-0.0120933-0.0083106-0.0101171-0.0038519-0.0129165-0.0061084 -0.006-0.0005887-0.00564080.00016089-0.0062499-0.0018498-0.0086377-0.0016608 0.00360.004448940.002509730.004460970.004488750.00608323-0.00419380.00318656 -0.0010.002308260.002286880.010042230.007086920.00821076-0.00133350.00499801 -4E-04-0.00069420.00083927-0.003522-0.00162930.00215436-0.0010709-0.0037004 -0.001-0.0097698-0.0073117-0.0053573-0.00557440.0016277-0.0039432-0.0045975 0.0132-0.00236780.000593340.002990330.007773210.006257460.00746130.00197734 0.00780.008342750.005845870.007461680.011640030.005659610.009240190.00752445 0.01380.020680410.021696490.022861860.027278750.015592180.018883980.01777125 -0.009-0.0105158-0.0009481-0.002108-0.0003845-0.0065044-0.0107134-0.0102104 0.0056-0.00231140.00265199-0.00077550.00774344-0.00112560.008473480.00661982 -0.003-0.00438980.00844816-0.0020017-0.0024674-0.00637470.00536722-0.0052468 -0.011-0.0121862-0.0034167-0.0140882-0.006094-0.0155609-0.0025849-0.0071208 0.0006-0.0034465-0.0029331-0.0135585-0.0071751-0.01359530.009336180.00081574 -0.006-0.0035432-0.0017983-0.0063446-0.003102-0.01298260.00848361-0.0018591