便利商店

试验一：EXCEL的基本统计功能制作统计汇总表实例一某24小时便利商店记录了某天内24小时的顾客人数，如图2-1所示。

请制作统计汇总表。

作法1、打开Excel新文件后，键入数据，如图2-1所示，并存于“集中指标.XLS”的文件中。

将该工作表命名为“便利商店”，以鼠标指向左下角的“Sheet1”，然后双击，“Sheet1”就会变成黑色，表示等待编辑，然后键入“便利商店”，即可将该工作表命名为“便利商店”。

2、选取“工具”菜单中的“数据分析”命令，如图2-2所示（如没发现“数据分析”命令，请参见本书导读的“安装分析工具库”，加以安装即可）。

定”按钮。

4、在图2-4内“输入区域”的文本框中输入所欲分析数据的地址，在此为B1：B25。

然后选取“逐列”（此为默认），表示数据是依照字段存放，并请选取“标志位于第一行”，因为在此，B1是标记“顾客”。

如果要将输出的结果置于数据一的窗体上，则可选取“输出区域”，并键入地址，如C1。

如果要将结果置于新工作表或新活页簿，则可选取“新工作表组”或“新工作簿”。

关于“汇总统计”部分，请务必选取，不然输出的结果就不会包含如平均数、平均数的标准误差、中位数、众数、标准差、方差、峰度、偏态、范围、最小值、最大值、总和、计数、第K个最大值、第K个最小值以及置信度等统计项目。

至于“平均数置信度”的默认值是95%，如果要修改，则先选取后，直接键入所要的置信度，如90%。

“第K大值”和“第K小值”指的是依照大小排列后，第K个最大值或最小值，默认值为1，也就是极大值和极小值。

由于希望将汇总统计输出置于新的工作表内，并将该工作表命名为“便利商店汇总统计”，因此选取了“新工作表”，键入“便利商店汇总统计”，并设定“平均数置信度”为95%，如图2-4所示，单击“确定”按钮后，即可获得如图2-5所示的结果。

在一天的24h内，平均每小时的顾客人数为21.58人，中值为13.5，模式为0，样本方差和标准偏差（将在下章说明其意义）分别是378.60和19.46。

最大值是57，最小值是0，区域（全距）为57。

峰值、偏斜度标准误差等留待以后说明。

制作排位与百分比表实例二身为便利商店的店长一定会想知道何时顾客较多，何时较少，以利人力调度。

换句话说，就是每个小时顾客人数的排序。

而且也会想知道如果由小到大排列，累积的百分比是多少。

如果是最大值，等级是1，（累积）百分比就是100%。

作法1、回到“便利商店”的画面，选取“工具”→“数据分析”→“排位与百分比排位”，如图2-6输入。

`2、单击右下角的“确定”按钮即可得到如图2-7所示的结果。

在图2-7中的“排位”这一字段内，发现原来编号（原定序点）第19点，顾客是57，排位是1，在“百分比”上也就是100%。

事实上，如果只是要知道每小时顾客人数的排序，可以用“RANK”函数，作法如下：1、光标移至“便利商店”的C2上，选取“插入”菜单中的“函数”命令，并选取“RANK”函数，如图2-8所示。

1、单击右下角的“确定”按钮后，得到如图2-9所示的对话框。

在“Number中键入B2，因为我们想知道置于B 2的分数所占的名次。

在“Ref”中键入“B$2：B$25”，因为这24小时的顾客人数置于B2至B25中，至于$符号，是用来固定地址，使之不会因为复制而变动地址。

在图2-9的左下角出现“计算结果=21”，表示该顾客人数排名21。

2、单击图2-9中的“确定”按钮后，即可得到如图2-10 所示的结果。

将光标移至C2，并置于C2的右下角，即可出现“+”，表示可以复制，将之拖曳至C25，即可完成复制，然后就可以得到如图2-11所示的结果，这就是顾客人数的排名。

制作成绩等级表实例三身为老师，常常为了计算学生的学业成绩费尽心机，除了要记录学生段考、期末考、随堂考的分数外，还要计算总平均，排定名次，并分为五等级：优、甲、乙、丙、丁。

作法：1、图2-12所示，输入15位学生的学号，两次段考、期末考和三次随堂考的成绩。

两次段考成绩各占学期总平均的20%，期末考占40%，三次随堂考共占20%。

2、在H3内键入“=B3*0.2+C3*0.2+D3*0.4+（E3+F3+G3）/3*0.2”，以计算第一位学生的总平均分数，然后复制至所有15位学生，即可得到所有学生的学期总平均分数。

3、接下来就是利用总平均分来排名次，在I3中键入“=RANK（H3，H$3：H$17）”，即可得到第一位学生的名次，然后将其复制至I17，就可以得到所有学生的名次，如图2-13所示。

如果事先界定总平均90分以上者为优等，80分以下者为甲等，70分以上者为乙等，60分以上者为丙等，60分以上者为丁等，那么就在J3中键入=IF （H3>=90，“优”，IF（H3>=70，“乙”，IF（H3>=60，“丙”，“丁”））））“”。

即可得到第一位学生的等级，然后复制至所有学生。

若要计算班上15位同学中，有多少位同学得优等、甲等、乙等、丙等和丁等，可以利用“COUNTIF”函数来计算，请在L3中键入“=COUNTIF（J$3：J$17，“优”）”，即可得到优等的人数共3人。

同理，在L4中键入“=COUNTIF（J$3：J$17，“甲”）”，则得到甲等人数共5人。

以次类推，结果如图2-14所示。

其他集中指标的计算以下利用Excel提供的函数，计算几何、调和、截尾、温塞平均数。

实例四研究者调查了20个家庭的月收入（单位：万元），如图2-15所示，请计算各种集中指标。

作法1、找一适当地址，如B22键入“=A VERAGE（B2:B21）”即可得算数平均数。

键入“GEOMEAN（B2:B21）”得几何平均数。

键入“HARMEAN（B2:B21）”得调和平均数。

2、除直接键入函数外，也可以通过粘贴函数来计算。

首先将光标移至适当地址，如B23中，然后选取“插入”菜单中的“函数”，选取“GEOMEAN”，如图2-16所示，并单击“确定”按钮，即可出现如图2-17所示的对话框，在Number1中键入“B2:B21”，确定后，就可以得到几何平均数。

3、同样也可以利用A VERAGE和HARMEAN求得平均数和调和平均数。

4、Excel并没有提供函数以计算截尾平均数和温塞平均数，不过有函数QUARTILE可以计算Q1和3Q。

首先在适当地址如B25中键入“=QUARTILE （B2:B21，1）”，其中1表示第一个四分位数。

同理，在B26中键入“=QUARTILE （B2:B21，3）”，其中3表示第三个四分位数。

可以利用插入函数QUARTILE的方式，并按如图2-18所示内容键入，即可得到1Q。

最后可得图2-19所示的结果，Q为10.25。

1Q为5.75，35、图2-19中，算数平均数为12.7。

这是数据中的最后一笔家庭的月收入高达100万元，使得算数平均数受到该值的影响而失真。

以下说明如何计算截尾平均数和温塞平均数。

在适当地址如C2中键入“=IF（B2<B$25，FALSE，IF（B2>B$26，FALSE，B2））”其中B$25放的就是1Q。

如果B2放的值Q，B$26放的就是3小于，或是大于3Q，就用FLASE取代它，否则就用原来的B2，然后复制至C21（复制的方法可先将光标移至B2的右下角，就会出现+号，然后拖曳至C21），如图2-20所示。

6、 然后计算这20个值的算数平均数，就是截尾平均数：“=A VERAGE （C2：C21）”，得7.9万元。

7、同样也可以计算温塞平均数，首先在适当地址如D2键入“=IF （B2<B$25，B$25，IF （B2>B$26，B$26，B2））”，这意味着B2的值若小于1Q 的话，就D2中存入1Q ；B2的值若大于3Q 的话，就在D2中存入3Q ；不然就放入B2，并复制至D21，如图2-21所示。

5、 计算这20个值的算数平均数，就是温塞平均数：“=A VERAGE （D2：D21）”，得7.95万元。

截尾平均数和温塞平均数相当接近，不过和原先的算数平均数则相去甚远。

试验二变异指标的计算 实例一为了了解小学生每天花在看电视的时间大概有多久，某老师抽样了20位小学四年级的学生，请他们记录每天看电视的时间。

得到如图3-3所示的数据（单位：分钟）。

请问（1）这七天的平均数、区域、四分位数、平均绝对离差、标准差、方差各为多少？（2）从这七天的平均数和标准差，你得到什么结论？（3）平均来说（不分假日和非假日），每位学生花多少时间看电视？作法1．如图3-3所示，将所有的数据置于“电视”的工作表内。

2．Excel 提供了函数A VERAGE 以计算平均数；MAX 计算最大值，MIN 计算最小值，两者相减为区域；QUARTILE 计算四分位数；STDEV 计算样本标准差；V AR 计算样本方差。

计算第一位学生的各种指标，该学生的数据放于B2:H2中，在适当地址如I2中键入“=A VERAGE （B2:H2）”，即可得到第一位学生收视时间的平均数。

在J2中键入“=MAX （B2:H2）-MIN(B2:H2)”，即可得到该学生的区域。

在K2中键入“=QUARTILE （B2:H2,3）-QUARTILE （B2:H2,1）”，即可得到该学生的四分位距（即13Q Q ）。

在L2中键入“=A VEDEV （B2:H2）”即可得到该学生的平均绝对离差。

在M2中键入“=STDEV （B2:H2）”即可得到该学生的样本标准差。

在N2中键入“=V AR（B2:H2）”即可得到该学生的样本方差。

然后复制至所有的学生，如图形3-4所示。

基本上可以看出平均绝对离差和标准并不相同，不过差不多。

同理，也可以计算学生们在这七天内收视时间的平均数，在B22中键入“A VERAGE（B2:B21）”，即可得到所有学生在星期一收视时间的平均数。

在B23中键入“=MAX（B2:B21）-MIN（B2:B21）”，即可得到星期一收视时间的区域。

以此类推。

可以求得星期一的四分位距、平均绝对离差、标准差和方差。

然后复制至星期日和平均数，如图3-5所示。

就这七天收视时间的平均数而言，随着周末接近看电视的时间有增加的趋势，例如从平时的半小时左右到周末的约一小时。

不过就标准差而言，周末（周六和周日）的标准差大概是平时的2倍到3倍。

换句话说，在周末的时候，学生们看电视的时间呈现很明显的差异，有的学生花很多的时间看电视。

但同样的也有很多学生几乎不看电视，因此标准差比平时非假日时大。

这可能是有些学生利用周末度假去，因此无法看电视。

而没去度假外出的学生，则比平常还要花更多的时间看电视。

不过这种“度假外出”的假设，仍需进一步的求证。

I22中46.85714分钟就是所有学生平均每天收视的时间的平均数。