关于对人事常用EXCEL函数公式的注解解读.docx

关于对人事常用EXCEL函数公式的注解解读.docx

《关于对人事常用EXCEL函数公式的注解解读.docx》由会员分享，可在线阅读，更多相关《关于对人事常用EXCEL函数公式的注解解读.docx（8页珍藏版）》请在冰豆网上搜索。

关于对人事常用EXCEL函数公式的注解解读

函数说明语法

CHOOSE

（index_num,value1,value2,…COLUMN

COLUMNSMATCH

HLOOKUPVLOOKUP1成组设计方差分析例题

编号11.82.32.921.42.13.231.52.12.742.12.12.851.92.62.761.72.5371.82.33.481.92.4

3

可以使用index_num返回数值参数清单

中的数值。

（reference返回给

定引用的列标。

（array返回数组

或引用的列数

甲组样本返回在指定方式下与指定数值匹配的数组中元素的相应位置。

在表格或数值数组

的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。

在表格或数值数组

的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值

乙组样本丙组样本

8（MCOLUMN=8,8,8,8,8,8,3（RQPONMPCHOOSE=

14:

3（RLCOLUMN=

1,3:

3,18（RLKMATCH=`

77,105:

90,77（BAJAAHVLOOKUP=`

2,55:

54,74（FALSEDBAHLOOKUP=

91.82.43.4

101.83.3

1123.5

12

13

合计19.720.833.974.4

均数1.7909092.3111113.0818182.394613

样本数1191131

合计均方35.6948.34105.33189.36

方差:

0.0409090.0336110.0856360.054798

分子=2.368816分母=1.048214

卡方值=2.259859C值=178.56

结果:

方差齐性好

成组设计方差分析结果

变异耒源SSvMSFPP

总10.830

组间9.26565724.63282884.54378<0.011.36E-12

组内1.534343280.054798

v1=3v2=28

f值0.05:

3.340389f值0.01:

5.452932

P<0.01结论

P=1.36E-12

各组均数样本数对比组两均数差组距aq值q界值P值

A与BXa-Xb0.050.01

3.081818111组/3组1.290909318.289833.58

4.64<0.012.31111191组/2组0.770707210.359162.954.02<0.011.790909112组/3组0.52020226.9920962.954.02<0.01

a1=3a2=2a3=2v=28

0.050.01

q界值（1组/3组:

3.584.64

Q界值（1组/2组:

2.954.02

Q界值（2组/3组:

2.954.02

P值（1组/3组:

<0.01

P值（1组/2组:

<0.01

P值（2组/3组:

<0.01

F界值表（P=0.05分子自由度

分母自由度123456781161.45199.50215.71224.58230.16233.99236.77238.88218.5119.0019.1619.2519.3019.3319.3519.37310.139.559.289.129.018.948.898.85

47.716.946.596.396.266.166.096.04

56.615.795.415.195.054.954.884.82

65.995.144.764.534.394.284.214.15

75.594.744.354.123.973.873.793.73

85.324.464.073.843.693.583.503.44

95.124.263.863.633.483.373.293.23

104.964.103.713.483.333.223.143.07

114.843.983.593.363.203.093.012.95

124.753.893.493.263.113.002.912.85

134.673.813.413.183.032.922.832.77

144.603.743.343.112.962.852.762.70

154.543.683.293.062.902.792.712.64

164.493.633.243.012.852.742.662.59

174.453.593.202.962.812.702.612.55

184.413.553.162.932.772.662.582.51

194.383.523.132.902.742.632.542.48

204.353.493.102.872.712.602.512.45

214.323.473.072.842.682.572.492.42

224.303.443.052.822.662.552.462.40

234.283.423.032.802.642.532.442.37

244.263.403.012.782.622.512.422.36

254.243.392.992.762.602.492.402.34

264.233.372.982.742.592.472.392.32

274.213.352.962.732.572.462.372.31

284.203.342.952.712.562.452.362.29

F界值表（P=0.01分子自由度

分母自由度1234567814052.184999.345403.535624.265763.965858.955928.335980.95298.5099.0099.1699.2599.3099.3399.3699.38334.1230.8229.4628.7128.2427.9127.6727.49421.2018.0016.6915.9815.5215.2114.9814.80516.2613.2712.0611.3910.9710.6710.4610.29613.7510.929.789.158.758.478.268.10712.259.558.457.857.467.196.996.84811.268.657.597.016.636.376.186.03910.568.026.996.426.065.805.615.471010.047.566.555.995.645.395.205.06119.657.216.225.675.325.074.894.74129.336.935.955.415.064.824.644.50139.076.705.745.214.864.624.444.30148.866.515.565.044.694.464.284.14158.686.365.424.894.564.324.144.00

168.536.235.294.774.444.204.033.89178.406.115.194.674.344.103.933.79188.296.015.094.584.254.013.843.71198.185.935.014.504.173.943.773.63208.105.854.944.434.103.873.703.56218.025.784.874.374.043.813.643.51227.955.724.824.313.993.763.593.45237.885.664.764.263.943.713.543.41247.825.614.724.223.903.673.503.36257.775.574.684.183.853.633.463.32267.725.534.644.143.823.593.423.29277.685.494.604.113.783.563.393.26287.645.454.574.073.753.533.363.23297.605.424.544.043.733.503.333.20

示例实际应用:

F界值表（P=0.05分子自由度

5.192163分母自由度123456

1161.45199.50215.71224.58230.16233.99

218.5119.0019.1619.2519.3019.33

310.139.559.289.129.018.94

47.716.946.596.396.266.16

1356.615.795.415.195.054.95

65.995.144.764.534.394.28

75.594.744.354.123.973.87

85.324.464.073.843.693.58

95.124.263.863.633.483.37

7104.964.103.713.483.333.22

114.843.983.593.363.203.09

分子自由度

32

11

q界值（1组/3组

3.58注:

利用该函数,在q界值表中寻找q界值,例中H77为表的自由读,AA90:

AJ105为q界值表数据区域,B77为组距。

具体应用见B68、C79、C80、C81、E68、E79、

E80、E81单元格。

注:

利用该函数,解决二二比较表中按均值排序自动寻找各组样本数。

A74为均值,B54:

D55为均值和样本数有关联排列的数据区域,2为列序号,FALSE为查找时要精确匹配。

具体应用见B74、B75和B76。

注:

利用该函数,解决按分子自由度寻找F界值表中列序号,K18为分子自由度,L3:

R31为F界值表部分数据区域,1为按升序排列查找范围等于或小于K18单元格中数值。

具体应用见B67。

.

结论

91011121416202430240.54241.88242.98243.90245.36246.47248.02249.05250.1019.3819.4019.4019.4119.4219.4319.4519.4519.468.818.798.768.748.718.698.668.648.626.005.965.945.915.875.845.805.775.75

4.774.744.704.684.644.604.564.534.504.104.064.034.003.963.923.873.843.813.683.643.603.573.533.493.443.413.383.393.353.313.283.243.203.153.123.083.183.14