关于对hr分析中常用excel函数公式的注解

函数说明语法MIN(number1,number2,...)MINA(value1,value2,...)FREQUENCY(data_array,bins_array)LARGE(array,k)MAX(number1,number2,...)MAXA(value1,value2,...)求样本数据中的最小值。求样本数据中的最小值。求某个区域中数据的频率分布。求样本数据中第k个最大值。求样本数据中的最大数值。求样本数据中的最大数值。{})17:6,115:6(MMLLFREQUENCY=)2,115:6(LLLARGE=)115:6(LLMAX=)115:6(LLMAXA=)115:6(LLMIN=)115:6(LLMINA=120.6123116.2122.5121.5120117.1112.2122.8120.7114.3119.5118.1120122.5112.7117.2120.4121.2114.8120.7111.5130.8113114.7119.7126.1130.5114.9125.2120.1117.4123.1120.5118.9113.2117121.8)115:6(LLMINA=124.1116115.4120.8118125116108.2119.7120.7117.7120129.1116.3112.4126.2122.4127.1108.2119.6122129.3114.3113.2121113125.2119.3132.5示例实际应用：频数身高（CM）组段频数2112.4109.922123.5111.929119.7113.999119.8115.9915120.2117.91518124.4119.91821112.3121.92114117.9123.91410123.8125.9104116.3127.943116.8129.933117.213430118.30116.8122.9130.8120.8116.4114.9120.1119.1132.5125.1125.6122.7120.3119.8132.5128126.6119124.4118.4108.2122.8120.5123.2118.2114.8108.2114.3124.1121.5某市1982年110名7岁男童的身高（CM）资料如下，试编制频数表。{})17:6,115:6(MMLLFREQUENCY=118.4125.8118.5122.1110.3120.6123116.2122.5121.5120117.1112.2122.8120.7114.3119.5118.1120122.5112.7117.2120.4121.2114.8120.7111.5130.8113114.7119.7126.1130.5114.9125.2120.1117.4123.1120.5118.9113.2117121.8124.1116115.4120.8118125116108.2119.7120.7117.7120129.1116.3112.4126.2122.4127.1108.2119.6122129.3114.3113.2121113125.2119.3132.5函数说明语法频率最多的数值平均血清滴定度GEOMEAN(number1,number2,...)HARMEAN(number1,number2,...)QUARTILE(array,quart)PERCENTILE(array,k)求中位数求样本数据的第K百分比数值点。AVERAGE(number1,number2,...)AVERAGEA(value1,value2,...)MEDIAN(number1,number2,...)MODE(number1,number2,...)求样本数据区域中出现频率最多的数值。求几何均数求样本数据的调和平均值。调和平均值的计算公式如下：求样本数据的四分位数。求取样本的算术平均值。求取样本的算术平均值。包括文本值。()25:4PLAVERAGE==jyyNH111()25:4PLAVERAGE=()25:4PLMEDIAN=()25:4PLMODE=()32:20JJGEOMEAN=)25:4(PLHARMEAN=)34,25:4(JPLQUARTILE=)42,25:4(JPLPERCENTILE=)14,25:4(MPLKPERCENTRAN=PERCENTRANK(array,x,significance)TRIMMEAN(array,percent)求某数值在一个样本数据中的百分比排位。求样本数据中间的平均值。)14,25:4(MPLKPERCENTRAN=)52,25:4(JPLTRIMMEAN=示例实际应用：平均值119.67429123.5119.8121.5130.5129.1119.7128120114.9116.3119.8126.6117.1125.2112.4119.67429120.2119112.2120.1126.2124.4124.4122.8117.4122.4112.3118.4120.7123.1127.1117.9122.8114.3120.5108.2中位数123.8120.5119.5118.9119.6119.8116.3123.2118.1113.2122116.8118.2120117129.3117.2114.8122.5121.8114.3118.3114.3112.7124.1113.2频率最多的数值116.8124.1117.2116121119.7122.9121.5120.4115.4113120.8118.4121.2120.8125.2116.4125.8114.8118119.3114.9118.5120.7125132.5平均血清滴定度120.1122.1111.5116122.71/55119.1110.3130.8108.2116.2125.1120.6113119.7119.7125.6123114.7120.7117.7调和平均值119.48341/201/401/401/801/3201/201/401/401/801/401/401/801/160四分位数108.200%116.4125%119.8250%122.8375%132.54100%数值点第K百分比122.80.75119.80.5116.30.25某市1982年110名7岁男童的身高（CM）资料如下:某疾病13名患者血清滴定度测定结果如下：百分比排位37%平均值舍去比例119.610.1119.600.2119.390.3119.450.4函数说明语法离均差平方和求样本数据中某数的标准化值。正态化数值的计算公式如下：求样本的标准差。函数STDEV的计算公式如下:求样本数据的均差。平均偏差的公式为：求样本数据的离均差平方和偏差平方和的计算公式为：求样本数据的峰度系数。峰值的计算公式如下：求样本数据的偏态系数。计算公式定义如下：AVEDEV(number1,number2,...)DEVSQ(number1,number2,...)KURT(number1,number2,...)SKEW(number1,number2,...)STANDARDIZE(x,mean,standard_dev)STDEV(number1,number2,...)?-XXn1()?-=2XxDEVAR()()()()()()()3213321124--------nnnsxxnnnnnj()()---3.21Sxxnnnj-=XZ()()122--nnxxn()25:4PLAVEDEV=()25:4PLDEVSQ=()25:4PLKURT=()25:4PLSKEW=()28,28,28KJLESTANDARDIZ=()25:4PLSTDEV=总体的标准差求样本的标准差。函数STDEVA的计算公式如下：求整个样本总体的标准差。函数STDEVP的计算公式STDEVP(number1,number2,...)求样本总体的标准差。函数STDEVPA的计算公式如下：求样本的方差。函数VAR的计算公式如下：求样本的方差。函数VARA的计算公式如下：求样本总体的方差。函数VARP的计算公式如下：STDEVPA(value1,value2,...)VAR(number1,number2,...)VARA(value1,value2,...)VARP(number1,number2,...)STDEVA(value1,value2,...)()()122--nnxxn()()122--nnxxn()222.nXXn-()222.nXXn??-()()122--??nnXXn()()122--??nnXXn()222nXXn-()25:4PLVAR=()25:4PLSTDEVP=()25:4PLVARP=求样本总体的方差。函数VARPA的计算公式如下：VARPA(value1,value2,...)()222nXXn-()222nXXn?-示例实际应用：均差3.730286123.5119.8121.5130.5129.1119.7128120114.9116.3离均差平方和119.8126.6117.1125.2112.42402.981120.2119112.2120.1126.2124.4124.4122.8117.4122.4112.3118.4120.7123.1127.1117.9122.8114.3120.5108.2123.8120.5119.5118.9119.6峰度系数116.3123.2118.1113.21220.088618116.8118.2120117129.3117.2114.8122.5121.8114.3118.3114.3112.7124.1113.2116.8124.1117.2116121122.9121.5120.4115.4113120.8118.4121.2120.8125.2116.4125.8114.8118119.3114.9118.5120.7125132.5偏态系数120.1122.1111.5116122.70.125929119.1110.3130.8108.2116.2125.1120.6113119.7119.7125.6123114.7120.7117.7标准化量均值标准差-1.5530421.352.60778717.3-1.2846118-0.7477619.4-0.287620.6-0.0575221.20.1725621.80.44098722.50.70941423.21.016187241.59138725.5标准差4.80682710名7岁男童体重（Kg）如下，求每个每个人体重的标准化值。某市1982年110名7岁男童的身高（CM）资料如下:总体的标准差4.783882方差23.10558总体的方差22.88553函数说明语法1求样本的正态分布函数,正态分布密度函数的计算公式如求正态分布函数的逆函数。求标准正态分布的累积函数,标准正态分布的密度函数计算公式如下：求标准正态分布累积函数的逆函数。NORMDIST(x,mean,standard_dev,cumulative)NORMINV(probability,mean,standard_dev)NORMSDIST(z)LOGINV(probability,mean,standard_dev)LOGNORMDIST(x,mean,standard_dev)求x的对数正态分布的累积函数,计算公式如下：求x的对数正态分布累积函数的逆函数,计算公式如下：NORMSINV(probability)()()--=22221.,,xef()22211,0,ZeZf-=()