Statistical ?????? Excel: PEARSON

???? ?????? ???? ??????
???? ID: 828129 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

?? ???? ??? Microsoft Excel PEARSON ?????? ?? ????? ???? ??? ?? ???? ????? ???? ?? ?? ?????? ?? ????? ???? ????? ?? ????? ???? ???? ??? ??, ?? ???? ?? Excel ?? ????? ????????? ??? PEARSON ?? ?????? ?? ??? Microsoft Office Excel 2003 ?? Excel ?? ??? ?? ????????? ??? PEARSON ?? ???????

???? ???????

PEARSON (array1, array2) ?????? ???? ?? ?? ??????? ?? ??? ??????? ?????? ?? ??????-???? ?? Pearson ?? ?????

????????

PEARSON(array1, array2)
????, array1 ?? array2, ???? ????????, ???, ???? ?????, ?? ?????? ??? ?????? ???????? ????? ????

???? ??????? ????? ?? PEARSON ?????? ??? ????, ???? PEARSON (A1:A100, B1:B100) ?? ?? ????????? ??? ????? ???

????? ?? ??????

PEARSON ?????? illustrate, ?? ????? ?? ???? ????:
  1. Excel ?? ???? ????? ????????????? ?????, ?? ???? ??? ????? ?????? ?? ????????? ??????
    ?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
    13 + 10 = ^ D $ $ 2???? ?????? ?? ??? ???? ?? 10
    24 + 10 = ^ D $ $ 20
    32 + 10 = ^ D $ $ 2
    45 + 10 = ^ D $ $ 2
    5= 4 + 10 ^ D $ $ 2
    6= 7 + 10 ^ D $ $ 2pre-Excel 2003
    ?? D2 = 7.5
    =PEARSON(A1:A6,B1:B6)0.702038
    =CORREL(A1:A6,B1:B6)0.713772
    ?? D2 = 8
    # DIV/0!
    0.713772
  2. ???? ????? Excel ????? ????? ??? ???? A1 ?? ??? ????, ?? ??? ???? ????????????? ??? ???? A1:D13 ?????? ???? ?? ???? ???????????? ????????
  3. ????? ??????????? ?? ?????????, ?? ???? ????????? ??????? ??????. ?????? ?????? ??? ?? ?????, ?? ??? ?????? ???? ?? ??? Excel ?? ??????? ?? ??? ??????? ?? ??? ??? ?? ????? ???????????? ?? ????? ????:
    • Microsoft Office Excel 2007 ???, ????? ????????? ???????? ??,???????????????????, ?? ???? ????????? ????? ?????.
    • Microsoft Office Excel 2003, ??? ????? ???? ?? ????????????? ??????????????, ?? ???? ????????? ???.
    ???:???? B1:B6 ?? ?? ??? ??? ???????? ???? ????? ?? ???? ????????0 ????? ?? ??? ???? ???
???? A1:A6 ?? B1:B6 A8 ?? A9 ?????? ??? PEARSON ?? CORREL ?????? ?? ??? ???? ?? ??? ?? ?????? ??? ????? ??? ???? ??? ?? ???? ?? ?? ??????? ??? ??? PEARSON ?? CORREL ????? compute Pearson ??????-???? ??????? ?????? ?? ?? ???? ???????? ?? ??????? ???? ??????

Excel ?? ????????? ??? ?? Excel 2003 ?? ????, PEARSON exhibit ????????? ?????-??? ?? ???? ??? Excel 2003 ?? Excel ?? ??? ?? ????????? ??? PEARSON ?? ??????? ?? ????? ???? ??? ??? CORREL ??? ????? ??? ?? Excel 2003 ?? Excel ?? ??? ?? ????????? ??? ????? ????????? ?? ??? ????? ???? ???? ??? ??? ?????, ??? ?? Excel 2003 ?? ???? Excel ?? ???? ??????? ?? ??? PEARSON ????? ?? ??? ???, ?? Microsoft ??????? ???? ?? ?? ???? ???? CORREL ?? ??????

Excel ?? ????????? ??? ?? Excel 2003 ?? ?????? ???, ?? ?? ????? ?? ???? ?????????? ?? ???? ??? ?? experiment ????? ?? ????????? ?????-??? ?? ???? ?? ??? ???? ?? ???? ??? ???????? B1:B6 ??? ?????? ???? ?? ??? ?? ???????? ??????, PEARSON ?? CORREL ?? ??? ?? ???????? ???? ???? ?????? ??? ?? D2 ??? ??? ?? ?????? ???, ?? ??? ???????? B1:B6 ??? ????? ??? ??? ??? D2 7 ?? ?? ??, ??? ?????-??? ?????? PEARSON ?? ???? ?? ????? ??????? ??? ??? ???? ???? ???? ?? 7.25, 7.5, 7.75, ?? ???? ??? 8 D2 ?? ??? ?????? ?????????? ?? ???? D6:D13 ?????? PEARSON ?? CORREL ?? ??? ?? D2 = 7.5 ?? ?? D2 8, respectively = ?????

CORREL ??? ?? ??? ??, ????? PEARSON ??? ?????-??? ?????? ????? ????? ?? 0 ?????? ??? ???? ?? ?? ?? ?? D2 = 8?

Excel ?? ????? ????????? exhibit ?? ?????? ??? ??? ????? ?? ??????? ?????-??? ?????? ?? ?????? ?? computational ????? ?? ????????? ?????? ????? ?? ???? profound ???? ??? ??, extreme ?? ??? ??? ?????? ??? ?? experiment ???? ?? ?????

Excel 2003 ?? Excel ?? ??? ?? ????????? ???, ???? ????? ????? PEARSON ?? ??? ??? ??? ???????? ???? ??? experiment ?? ?????? ????? ???????, ???? D6:D13 ?????? ???? ?????-??? ?????? Excel ?? ????? ????????? ??? ??????? ????

Excel ?? ????? ????????? ??? ??????

??? ?? ???? ?? ?? ??????? ?? ??? X's ?? Y ??, Excel ?? ????? ????????? ?? ?????? ?? ??? compute ???? ?? ??? ???? ??? ??? ?? ???? ?? ????? X's, Y ??, ?? ??? ?? ?????? ?? ??? X's, Y ?? ?? ???, XY ?? ?? ??? ?? ???????? ???? ??? ?????? ?? ?????? ?? ??????? ?? ?????? ??? ??? ????? ?? Excel ?? ????? ????????? ??? computational ????? ??? ??????? ??? ??? ????

Excel 2003 ?? Excel ?? ??? ?? ????????? ??? ??????

????????? ?? Excel 2003 ?? Excel ?? ??? ?? ????????? ??? ????? ???? ???? ?? ?? ???? ?? ?????? ?? ?? ??-??? ????????? ?? ????? ???? ??? ????, ?? sums X's ?? Y ?? ?? ???????? ???? ??? ?????? ?? ?????? ?? ???? ??????? ???? ??, X ?? ?????? (???) ?? ?? Y ?????? ??????? ??? ?? ???? ???? ???, ????? ??? ?? ???????? X ?? X ????? ?? ??? squared ???? ???? ???; ?? squared ???? summed ???? ???????? Y ?? Y ????? ?? ??? squared ???? ???? ??? ??; ?? squared ???? summed ???? ??? ??, ???????? (X ? X ?????) * (Y ? Y ?????) ???? ??????? ?? ???????? ???? ?? ??? ???? ??? ?? summed ???? ?? ??? sums ?? PEARSON ?? ????? ??? ??????? ???? ?????? ???? ???????? Y ???? (?? X ????), ??? ???????? ??? ?? ??? Y ????? (?? X ?????) ?? ??? ?? ?? ?? ??? ????? ??? ?? ??????? ?? ??? sums ?? ??? ?? ???????? ???? ???? ???????? ??????, ????, D12 ??? 10 ?? ?? ???? ???? ?? ??? ?? ?? ??? sums ???????? ???? ??? ?? ????? ?? ??? ?? ?????? ???? D2 ??? ????????? ?? ???????? ???? ???? ?????, Excel 2003 ?? Excel ?? ??? ?? ????????? ??? ?????? ??? ?? ????? numerically.

Conclusions

?? ??-??? approach guarantees ????? ?????????? ???????? ?? PEARSON Excel 2003 ?? Excel ?? ????? ????????? ??? ????? ???? ???? ?? ?? ??-??? approach ?? Excel ?? ??? ????????? ??? ??? Excel 2003 ?? Excel ?? ??? ?? ????????? ??? ?? ??????? ?????? Excel ?? ????? ????????? ??? ??????? ???????? ?? ????? ?? ??? ???? ???? ??????

CORREL ???? ??????????? ?? ?? ?? ????? approach PEARSON Excel 2003 ?? Excel ?? ??? ?? ????????? ?? ??? ????? ???? ???? ?? ?? ?? ???? ???? ??? ??? ?????, CORREL Excel ?? ????? ????????? ?? ??? ??? ????? ?????? ???

???? ????????? ?????? ???, ?? ???? ??? ???? ???? ?? ?? Excel ?? ????? ????????? ??? Excel 2003 ?? Excel ?? ??? ?? ????????? ??? ?????? ?? ?????? ?? ??? ?? ???? ????? ?? ???? ??????? ???? ?????? ?? ?? experiment illustrates ???????? ??????? exhibit ???? ?? ??? unlikely ??? ???????? ???????? ????? ???? ?? ??? Excel ?? ????? ????????? ??? ?? ???? ??? ??? ???? ?????? ????? ?? ?????? ?? ?????????? ?? variation ???? ????? ?? ??? ???? ??????? ???

The procedure that finds the sum of squared deviations about a sample mean by finding the sample mean, by computing each squared deviation, and then by summing the squared deviations is more accurate than the alternative procedure. (The alternative procedure is frequently referred to as the "calculator formula," because it is suitable for use by a calculator on a small number of data points.) The alternative procedure consists of the following steps:
  1. Find the sum of squares of all observations, the sample size, and the sum of all observations.
  2. Compute the sum of squares of all observations minus ((sum of all observations)^2)/sample size).
There are many other functions that have been improved in Excel 2003 and in later versions of Excel. These functions are improved because later versions of Excel replace the one-pass procedure with the two-pass procedure that finds the sample mean on the first pass and then computes the sum of squared deviations about the sample mean on the second pass.

A short list of such functions includes the following functions:
  • VAR
  • VARP
  • STDEV
  • STDEVP
  • DVAR
  • DVARP
  • DSTDEV
  • DSTDEVP
  • FORECAST
  • SLOPE
  • INTERCEPT
  • PEARSON
  • RSQ
  • STEYX
Similar improvements were made in each of the three Analysis of Variance tools in the Analysis ToolPak.

???

???? ID: 828129 - ????? ???????: 20 ??????? 2011 - ??????: 3.0
???? ???? ???? ??:
  • Microsoft Office Excel 2007
??????: 
kbformula kbexpertisebeginner kbinfo kbmt KB828129 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:828129

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com