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

Excel最小二乘法计算一二三维平面度方法总结
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

0.508471870.532566510.517141560.532457170.508368980.519674160.5032575

残差

0.002839760.013214050.002367030.000419150.00679354-0.00045570.0091757 0.011142990.008064220.00374830.008790340.008087860.008060090.00982864 0.00353907-0.0022862-0.00557590.002704820.00771399-0.0021392-0.0053457 0.000517060.00268691-0.00981780.000652490.00237765-0.0028404-0.0001691 -0.00302090.00850665-0.00107540.00331614-0.0048823-0.00154340.00263021 -0.0049081-0.0075991-0.0154043-0.0110284-0.0009204-0.0063561-0.0102411 -0.0084904-0.0157764-0.0148411-0.0137424-0.006528-0.0081675-0.0105222 -0.0065825-0.0080561-0.0107162-0.0045469-0.0051608-0.0178052-0.0048526 -0.00036910.007977050.000659040.0084928-0.00474020.005128480.00245671 0.005496220.005693880.010493650.010516310.00168550.004482860.0058324 -0.0044609-0.0065409-0.00156720.0038698-0.00280220.00183803-0.0070718 -0.0045143-0.0042622-0.005979-0.009032-0.0065365-0.0058353-0.0077374 -0.0057766-0.010685-0.0032284-0.0082162-0.0055161-0.0030615-0.0026225 -0.0004691-0.00426470.00169586-0.0012207-0.0027494-0.000399-0.0014528 0.006944640.010268450.009871040.00861884-0.00302840.008934740.00705582 0.002819630.005199110.019568480.009885560.003021830.007131810.0112382 -0.0023379-0.0023355-0.0005923-0.00256060.0012339-0.0014131-0.000466 -0.0023905-0.015557-0.0001045-0.0064625-0.0024004-0.0007865-0.007332 -0.0060531-0.003680.00734556-0.00284650.003920030.00128737-0.0005173 0.001855180.001540820.00617077-0.00075110.002486780.004749870.00145231 0.008268150.010173870.021345640.00978858-0.00259230.014183490.00776237 -0.0020995-0.00414510.00346953-0.0053651-0.0052321-6.873E-05-0.0081191 -0.0024451-0.0117006-0.0023877-0.01068970.00041872-0.004632-0.0027313 -0.0012376-0.00048010.00053809-0.0044943-0.00271450.00323039-0.0028612 -2.387E-050.002653270.012012650.0024456-0.00079360.00386413-0.0004517 0.005215770.008429290.00826430.001061160.001127480.004237830.00526665 -0.0038425-0.00380540.0034035-0.00468480.00233941-0.001607-0.0001376 -0.0018955-0.0033269-0.0036079-0.00598670.006105070.00111952-0.0022034 0.009368520.00439073-0.00287550.003836540.014030660.00637430.0077681 0.012775960.011511380.008250280.008731910.006797420.010836550.00983823 0.022090050.020344610.018024780.02167150.010617650.018870290.02134795 -0.0119907-0.0009334-0.0047108-0.0009408-0.0142875-0.0147411-0.0023333 0.00112110.00016099-0.00283940.001914560.008074130.001646140.00319462 -0.00303180.00128994-0.0087415-0.0026340.00039312-0.0040212-0.0012385 -0.0091442-0.0093581-0.0147886-0.0049189-0.0143762-0.0139293-0.0065705 -0.0035483-0.0033325-0.0153134-0.00225480.003603-0.0054328-0.0095184 -0.0053619-0.0039801-0.0130617-0.0043397-0.0055669-0.0107408-0.0103522

0.520527340.530514360.551380210.49888490.534315580.523652520.5187908 0.00632205-0.0020103-0.00700620.002740440.002910170.009633750.00783089 0.011917750.00505577-0.00970140.00750260.014392350.008588430.01070987 -0.0049459-0.0019001-0.01781770.00077633-0.0014881-0.00615890.00567296 0.00129324-0.0011122-0.00062010.00126755-0.00039650.000165780.00139845 0.0003305-0.0014391-0.0103818-0.00263560.00382099-0.0011160.00284524 -0.0072355-0.0087732-0.0147508-0.0065029-0.0013554-0.0025879-0.0049486 -0.0137685-0.01308520.00477632-0.0097842-0.0119129-0.0134388-0.0191586 -0.0082895-0.0042154-0.0021423-0.0054609-0.0078509-0.009466-0.0109576 0.00011113-0.00070780.00661032-0.00152450.010587530.007865230.00325439 0.008383250.00789873-0.01031740.005235160.008156450.007710130.00633858 -0.00370820.00081936-0.00119390.006627050.00076739-0.0063662-0.0004939 -0.0127779-2.147E-050.01290163-0.0115681-0.0099443-0.0086845-0.0079151 -0.0029369-0.00702730.01150255-0.0029453-0.00943-0.0133805-0.0070111 -0.0003580.003942760.01618374-0.0008219-0.0024677-0.0021075-0.0003099 0.009142790.013650410.016437060.000614450.011170350.010223690.00920205 0.010788610.008203050.011406880.007892850.010056420.010882610.00368419 -0.0023031-0.00187640.01323104-0.0021153-0.0046329-0.0007938-0.0057484 -0.0061729-0.00821730.00632582-0.0089104-0.0100444-0.0094125-0.0068696 0.00046844-0.00302310.000626870.0030125-0.0116301-0.0021083-0.0052656 0.003847240.004446940.00490876-0.0005643-0.00196830.010264390.00153548 0.010048120.012754560.018262220.013272160.004270040.014995910.00874725 -0.0095377-0.00176690.00913998-0.0005253-0.014428-0.0111894-0.01163 -0.0044732-0.0055272-0.0092375-0.0014166-0.0086184-0.0055179-0.0049634 0.00280557-0.0002572-0.0108559-0.0053934-0.0014563-0.000845-0.0002621 0.003906470.008250430.002497160.002443110.008882150.013986340.00194985 0.004249480.007543190.002697550.00166580.009394440.00429090.01215268 0.000758210.00126398-0.0084787-0.0021423-0.0042949-0.00018570.00332006 -0.0008116-0.0053766-0.01128350.00066244-0.0069064-0.0053042-0.0081012 0.004958460.00543115-0.00121110.006322250.00106448-0.00095960.00448431 0.017337120.00930111-0.00032920.013645550.007226580.005613460.00868546 0.018438610.019608640.017523560.020882070.023364660.026744390.02179723 -0.0054963-0.0057994-0.015144-0.00450140.00090061-0.00250480.00108226 -0.0046621-0.0015338-0.00241310.010531290.00172485-0.00547718.8408E-05 -0.0016281-0.0038628-0.0069968-0.0011330.00164144-0.0056969-0.0020735 -0.0090086-0.0076108-0.0058497-0.0066933-0.0008414-0.0016923-0.0047494 -0.0132063-0.0141393-0.007874-0.0170474-0.0066413-0.0077877-0.0101733 -0.0037868-0.0088873-0.0014264-0.0134073-0.0040226-0.0081835-0.0041482

0.53493930.530596690.509716340.544064030.515848720.547543190.50933633 -0.01277790.00662339-0.00443040.001430210.002195590.002411220.00243942 0.012784570.007968260.00693450.006850640.007369760.002190530.00456786 0.01029061-0.0041743-0.0067345-0.0014155-0.00246910.00490675-0.0029182 0.00105873-0.00089380.000948140.00206229-0.00568530.000995920.00159435 -0.018257-0.00541230.00030940.00901304-0.00044380.00014665-0.0063225 0.00434429-0.0033459-0.0012547-0.0087178-0.00452140.000738030.01823867 -0.001099-0.0130479-0.0024279-0.006887-0.0142139-0.01011150.01120519 -0.0055346-0.0058066-0.0071575-0.0050571-0.0044368-0.00354140.01036545 -0.01869240.005586-0.00642910.006954480.001463160.00896179-0.0047335 -0.01087330.006097330.003943630.009482050.004825620.01204823-0.0001753 -1.021E-05-0.0127953-0.00266170.00204446-0.0023418-1.183E-05-0.003961 0.00532917-0.0063369-0.0044091-0.0059121-0.0082864-0.0068386-0.0073071 0.01148372-0.00566330.0053506-0.0145858-0.010942-0.0082291-0.0083878 4.7688E-050.004578030.00162114-0.0009561-0.0025651-0.0040587-0.0029274 -0.00550950.010970490.007849280.019055640.017734220.012143950.00227375 -0.00279490.010445180.003603790.001713330.012817470.006365780.00322693 0.006668580.00185274-0.0017011-0.00262430.01885055-0.00959410.00074098 0.00880808-0.0001894-0.0001488-0.004881-0.0047947-0.0106207-0.0106053 0.017662140.001483880.00261102-0.00425420.002549740.001488440.0004144 0.00672668-0.00307430.00228192-0.0024250.000827060.00245882-0.0043252 -0.00713120.002818430.00341010.006987090.010326760.001761920.00037577 0.012294420.0025981-0.0041777-0.0062866-0.0022896-0.004191-0.0090691 0.013283880.0004580.00389992-0.0111874-0.0095689-0.0127242-0.0125107 -0.0016511-0.0066006-0.0028296-0.0059577-0.0081921-0.007354-0.0129505 -0.00950970.004791799.8703E-050.0037540.00500789-0.0013514-0.0019496 -0.01015480.004166740.007259040.000581760.004253220.0051578-0.0016294 -0.0014917-0.0065257-0.0028463-0.0032559-1.369E-05-0.0050023-0.0039154 0.008747360.00383232 6.5321E-06-0.0087123-0.0045588-0.0073291-0.0050612 0.017066890.002642190.01167823-0.00158620.003402360.000158020.00109621 0.003531740.028383680.007049050.005743460.006679420.008528480.00475662 -0.00172690.012476310.014577080.020055460.006879340.025431340.01435747 -0.0253666-0.015422-0.0087941-0.00700680.00288914-0.00082-0.0061746 0.01643364-0.00765630.002741780.003862440.002810650.001771430.01118624 8.793E-05-0.0088693-0.001625-0.0052293-0.00654150.003262760.01705058 -0.0225903-0.0056457-0.01837210.00330766-0.0097863-0.00842090.01914118 0.00725018-0.0033677-0.00411290.00460222-0.0006425-0.000173-0.0063487 -0.0087293-0.0029456-0.0060612-0.0005621-0.0085884-0.0005561-0.0117585

0.0470.0250.0050.035 1.756 2.473 1.040 1.040

0.53368923 0.00397253 0.00595929 0.00083456 -0.0049967 0.00554703 -0.0107713 -0.0106639 -0.0096517 0.00285033 0.00571103 -0.0041602 -0.0076419 -0.007998 0.00031432 0.01251616 0.01099778 0.00092645 -0.0049551 -0.0039112 0.00290114 0.01450301 0.00044581 -0.0121339 -0.0033215 0.00668042 0.00972458 0.00305313 -0.0016286 0.0047322 0.01104465 0.0178463 -0.0032451 -0.0021633 -0.0043799 -0.0099408 -0.0115684 -0.0074292

99.9%?

相关文档
最新文档