2.2 次数分布表
在统计学中，次数也叫频数。次数分布是在一批数据中，各个不同数值所出现的次数情况。依据次数如何产生，次数分布可分为单项式次数分布、组距式次数分布、相对次数分布和累积次数分布。将次数分布以表格的形式呈现出来，就是次数分布表。次数分布表可以用少量数字概括大量原始数据，揭示它们的意义，节约呈现数据的时间。
以前，常用画线记数“ 
”或写““正”字的方法来计数，容易出错，也很低效。在Excell中，使用函数公式会非常准确、高效。
2.2.1 单项式次数分布表
单项式次数分布是指按项目、类别或原始数据各数据点的次数分布。单项式次数多为离散型数据。
例2-6 某驾校100名考生理论考试的成绩如表2-1所示，试制成一个次数分布表并统计男、女生人数。
表2-1 驾校理论考试成绩
解题思路1：由于表中成绩集中在94～100分，且分数很集中，均为整数，因而无须分组，直接按实际成绩使用函数公式统计次数和人数。
解题过程：建立统计表，输入函数公式。
（1）建立统计表。为帮助读者深入理解函数的应用，本例介绍使用多个函数统计次数和人数。在文件“第2章统计表.xlsx”的“单项式次数分布表”工作表中，建立“驾校理论成绩”“多个函数按单条件统计各分数点人数”和“多个函数统计男女生人数”3个表，录入相关数据，如图2-22所示（隐藏了部分行）。
图2-22 驾校理论成绩统计表
成绩表中，已将最高分和最低分设置为有填充色的条件格式。
（2）输入函数公式。
在F3单元格输入函数公式“=COUNTIF($C$3:$C$102,E3)”。
在G3单元格输入函数公式“=COUNTIFS($C$3:$C$102,E3)”。
在H3:H9输入数组公式“{=COUNTIFS(C3:C102,E3:E9)}”。
在I3单元格输入函数公式“=SUMPRODUCT(($C$3:$C$102=E3)*1,$C$3:$C$102^0)”。
在J3:J9单元格输入数组公式“{=FREQUENCY(C3:C102,E3:E9)}”。
在K3单元格输入数组公式“{=SUM(($C$3:$C$102=E3)*1)}”。
在L3单元格输入数组公式“{=SUM(IF($C$3:$C$102=E3,1,))}”。
在M3单元格输入数组公式“{=MMULT(--(TRANSPOSE($C$3:$C$102)=E3),$C$3:$C$102^0)}”。
在N3:N9输入数组公式“{=MMULT(--(TRANSPOSE($C$3:$C$102)=E3:E9),$C$3:$C$102^0)}”。
将F3:G3区域的公式向下填充至G9单元格，将I3单元格的公式向下填充至I9单元格，将K3:M3区域的公式向下填充至M9单元格。
这样，第一张统计表的数据就全部得到了。从表中可以看出各个分值的分布情况，其中98分的人数最多，为21人。
统计结果如图2-23所示。
图2-23 多个函数按单条件统计各分数点人数的统计结果
在F13单元格输入函数公式“=COUNTIF($B$3:$B$102,E13)”。
在G13单元格输入函数公式“=COUNTIFS($B$3:$B$102,E13)”。
在H13:H14输入数组公式“{=COUNTIFS(B3:B102,E13:E14)}”。
在I13单元格输入函数公式“=SUMPRODUCT(($B$3:$B$102=E13)*1,$C$3:$C$102^0)”。
在K13单元格输入数组公式“{=SUM(($B$3:$B$102=E13)*1)}”。
在L13单元格输入数组公式“{=SUM(IF($B$3:$B$102=E13,1,))}”。
在M13单元格输入数组公式“{=MMULT(--(TRANSPOSE($B$3:$B$102)=E13),$C$3:$C$102^0)}”。
在N13:N14输入数组公式“{=MMULT(--(TRANSPOSE($B$3:$B$102)=E13:E14),$C$3:$C$102^0)}”。
将F13:G13区域的公式向下填充至G14单元格，将I13单元格的公式向下填充至I14单元格，将K13:M13区域的公式向下填充至M14单元格。
这样，第二张统计表的数据就全部得到了。从表中可以看出，男生55人，女生45人。
统计结果如图2-24所示。
图2-24 多个函数统计男、女生人数的统计结果
【函数公式解析】
按单条件进行次数统计，通常使用COUNTIF函数。适合于多条件计数的COUNTIFS函数，理所当然地可用于单条件计数。适合于分段统计次数的FREQUENCY函数，可用于按数值单条件计数，而不能用于文本单条件计数。至于SUMPRODUCT、SUM、SUM+IF、MMULT等函数，多用于多条件求和，也可用于多条件计数，当然可用于单条件求和与计数。这里只解析COUNTIF函数的经典用法，其他函数的用法将在后面的章节中陆续介绍。
COUNTIF函数是一个统计函数，用于统计满足某个条件的单元格的数量。其语法为:
COUNTIF(range, criteria)
range：必需，要进行计数的单元格组，区域可以包括数字、数组、命名区域或包含数字的引用，空白和文本值将被忽略。
criteria：必需，用于决定要统计哪些单元格的数量的数字、表达式、单元格引用或文本字符串。充分用好此条件，COUNTIF函数能够出色地完成单条件计数的工作。
解题思路2：数据透视表是一个非常出色的统计分析工具，一拖一放之间，就能出色地完成统计工作，非常快捷，无须设置复杂的函数。
解题过程：插入数据透视表时，可以先选择数据区域，再使用命令插入数据透视表，这样，在“创建数据透视表”对话框中，就不用再选择数据区域。或者先使用命令弹出“创建数据透视表”对话框，再选择数据区域。因本例标题行与数据区域之间无空行，二者在理论上是一个完整的区域，所以本例使用第一种方式插入数据透视表。
（1）插入数据透视表。操作过程为：
➊ 单击“插入”选项卡。
➋ 在“表格”组中单击“数据透视表”按钮。
➌ 在弹出的“创建数据透视表”对话框中，将鼠标放置于“选择一个表或区域”单选按钮右侧的“表／区域”框中，使用鼠标拖动选择A2:B102区域。
➍ 在“选择放置数据透视表的位置”组中单击“现有工作表”单选按钮。
➎ 将鼠标放置于“位置”框中使用鼠标单击P1单元格。
➏ 单击“确定”按钮，随即生成空白数据透视表并弹出“数据透视表字段”任务窗格。
➐ 在“数据透视表字段”任务窗格，将字段节区域中的“分数”字段拖放到“行”区域节，将“考生”或“性别”字段拖放到“值”区域节。
操作过程及结果如图2-25所示。
图2-25 插入数据透视表
（2）复制并设置第二个数据透视表。操作过程为：
➊ 单击第一个数据透视表任意单元格，例如P1单元格。
➋ 单击“数据透视表工具·分析”选项卡。
➌ 在“操作”组中单击“选择”按钮。
➍ 在下拉菜单中选择“整个数据透视表”菜单，按“Ctrl+C”组合键进行复制。
➎ 单击P11单元格，按“Ctrl+V”组合键进行粘贴。
➏ 在“数据透视表字段”任务窗格，将“行”“区域节”中的“分数”字段拖出去，再将“字段节”中“性别”字段拖到“行”“区域节”。
操作过程及结果如图2-26所示。
图2-26 设置第二个数据透视表
2.2.2 组距式次数分布表
当数据量较大时，应当把所有数据划分为若干分组区间，将数据按数值大小划归到相应的组别内，分别统计各个组别所包含的数据个数，并用列表形式呈现出来，这就构成了组距式次数分布表。按照分组的原则，组距式次数分布表可以分为等距次数分布表、不等距次数分布表、分项分段次数分布表、双列次数分布表等。下面介绍使用Excel函数和数据透视表的方法进行统计。
1．等距次数分布表
编制等距次数分布表一般有以下5个步骤。
（1）求全距。全距是一批数据中最大值与最小值两个数值之间的差距，由最大值减去最小值得到。
（2）定组数。定组数是要确定把这批数据分为多少个等距的区组。组数太多，往往会削弱对数据分组整理的功能；太少，又可能会湮没数据内包含的重要信息。一般来说，当一批数据的个数在200个以内时，组数可以取8～18组。如果数据来自一个正态的总体，则可以使用经验公式“K=1.87(N-1)2/5”或“K=1+3.3LOG(N)”（N为数据个数；K为组数，取整数）来确定组数。
（3）定组距。组距是任意一组数据起点和终点之间的距离，由全距除以组数得到。确定的组距要便于分组。组数与组距往往要通盘考虑，以便于发现数据规律和特征。
（4）写组限。组限也叫分组区间，是每个组的起止界限。起点值称为组下限，终点值称为组上限。在统计学中，组限有多种表达方式。在Excel中，为了计算方便，往往只写组下限或组上限。小数端组下限要低于这批数据的最小值，大数端组上限高于这批数据的最大值。
（5）统计次数。运用Excel函数公式进行统计。特别要注意，由于数据的用途不同，有时每组组限包括了组下限或组上限，有时又没有包括组下限或组上限。组下限或组上限被包含在该组数据之内，这个组下限或组上限就是组实下限或组实上限。
例2-7 100名学生的语文成绩如表2-2所示，试统计语文成绩的等距次数。
表2-2 学生的语文成绩
解题思路1：由于成绩集中在61～99分，分数较分散，因而分组后再进行统计。
解题过程：建立统计表，输入函数公式。
（1）建立统计表。在文件“第2章统计表.xlsx”的“等距次数分布表”工作表中，建立一个“等距分组过程”辅助表和一个“FREQUENCY分段统计表”，录入相关数据，如图2-27所示（隐藏了部分行）。
图2-27 等距次数分布示例表
成绩表中，已将最高分和最低分设置为有填充色的条件格式。
（2）输入函数公式。
在E2单元格输入公式“=MAX(B3:B102)-MIN(B3:B102)”，得到全距。
在E4单元格输入公式“=E2/E3”，得到组距。本例人数为100人，成绩为61～99分，组数为8组、组距为5时，8×5＝40，与全距值比较接近，本例组数确定为8组、组距确定为5。
在G3单元格输入公式“=J3-5&"<X＜＝"&J3”。将G3单元格的公式向下填充到G10单元格，得到各组的分组区间。本例各组数据不包括组下限数值，包括组上限数值。当然，分组区间可以直接输入。
在H3单元格输入公式“=(J3-$E$5+J3)/2”。将H3单元格的公式向下填充到H10单元格，得到各组的组中值。
在I3单元格输入公式“=J3-$E$5”。将I3单元格的公式向下填充到I10单元格（不带格式填充），得到各组的下限。
在K3:K10区域输入数组公式“{=FREQUENCY(B3:B102,J3:J10)}”或“{=FREQUENCY(B3:B102,J3:J9)}”，得到各组人数。
计算结果如图2-28所示。
图2-28 等距次数分布表的计算结果
【函数公式解析】
FREQUENCY函数计算数值在某个区域内出现的频率，然后返回一个垂直数组，必须以数组公式的形式输入。其语法为：
FREQUENCY(data_array,bins_array
data_array：必需，是要对其频率进行计数的一组数值或对这组数值的引用。如果不包含任何数值，则FREQUENCY返回一个零数组。
bins_array：必需，是要将data_array中的值插入的间隔数组或对间隔的引用。如果不包含任何数值，则FREQUENCY返回data_array中的元素个数。
函数FREQUENCY将忽略空白单元格和文本。
使用FREQUENCY函数，关键在于用好第2参数。第2参数起间隔划组的作用。当有2组时，就有1个间隔，相当于一刀两断；当有3组时，就有2个间隔……当有n组时，就有n-1个间隔。
当各组数据包括组上限数值而不包括组下限数值（X1＜X≤X2）时，就是按实上限分组。这种情况分为两种情形：
一般情况下，作为间隔数组的数值由小到大排列。这又有两种写法。
一是写出上限数组。这又包括写出全部实上限和不写最大实上限两种用法。如果不写最大实上限，也要在数组公式所在单元格区域中预留最大实上限对应得数的位置。本例的上限数组为“{65;70;75;80;85;90;95;100}”或“{65;70;75;80;85;90;95}”。
二是写出下限数组。此时不写出最小下限，但也要在数组公式所在单元格区域中预留最小下限的位置。本例的下限数组为“{65;70;75;80;85;90;95}”。
特殊情况下，作为间隔数组的数值由大到小排列。此时需要写出实上限数组。例如，本例的上限数组为“{100;95;90;85;80;75;70;65}”。
解题思路2：数据透视表具有分组功能，可以利用数据透视表进行分段式统计。
解题过程：插入数据透视表，创建组。
（1）插入数据透视表。操作过程为：
➊ 单击“插入”选项卡。
➋ 单击“表格”组中的“数据透视表”按钮。
➌ 在弹出的“创建数据透视表”对话框中，将鼠标放置于“选择一个表或区域”单选按钮右侧的“表／区域”框中，使用鼠标拖动选择A2:B102区域。
➍ 在“选择放置数据透视表的位置”组中单击“现有工作表”单选按钮。
➎ 将鼠标放置于“位置”框中，使用鼠标单击R1单元格。
➏ 单击“确定”按钮，随即生成空白数据透视表并弹出“数据透视表字段”任务窗格。
➐ 在“数据透视表字段”任务窗格，将字段节区域中的“语文”字段拖放到“行”区域节，将字段节区域中的“学生”字段拖放到“值”区域节。
操作过程及结果如图2-29所示。
图2-29 插入数据透视表
（2）创建组。操作过程为：
➊ 右击数据透视表“列标签”的任意单元格，例如R2单元格。
➋ 在快捷菜单中选择“组合”命令。
➌ 在弹出的“组合”对话框中将“步长”框中的值修改为“5”。
➍ 单击“确定”按钮，完成设置。
操作过程及结果如图2-30所示。
图2-30 创建组
2．不等距次数分布表
有时候，需要编制不等距次数分布表。编制不等距次数分布表无须求出一批数据的全距，而是根据需要直接确定组数和组距。很多时候，各组数据包括组下限数值，而不包括组上限数值。下面，通过一个例子介绍不等距次数分布表。
例2-8 100名学生的数学成绩如表2-3所示，试统计不合格、合格及优秀人数。
表2-3 学生的数学成绩
解题思路1：在百分制中，大于或等于0分且小于60分为不合格（不计数的0分学生不输入0），大于或等于60分且小于80分为合格，大于或等于80分且小于或等于100分为优秀。由于本题要求统计不合格、合格及优秀人数，因而次数分布为不等距。
解题过程：建立统计表，输入函数公式。
（1）建立统计表。为帮助读者深入理解函数的用法，本例介绍使用多个函数进行分段计数。在文件“第2章统计表.xlsx”的“不等距次数分布表”工作表中，建立“数学成绩表”“FREQUENCY分段统计表1”“FREQUENCY分段统计表2”“数据库函数条件区域”“多个函数按双条件统计人数”等5个表，录入相关数据，如图2-31所示（隐藏了部分行）。
图2-31 不等距次数分布示例表
成绩表中，已将最高分和最低分设置为有填充色的条件格式。
在“FREQUENCY分段统计表1”中，以上限值来书写间隔数组。
在“FREQUENCY分段统计表2”中，以下限值来书写间隔数组。
（2）输入函数公式。
在F3:F5区域输入数组公式“{=FREQUENCY(B3:B12,E3:E5)}”或“{=FREQUENCY(B3:B102,E3:E4)}”。
在J3:J5区域输入数组公式“{=FREQUENCY(B3:B102,I4:I5)}”。
在M5单元格输入函数公式“=DCOUNT(A2:B252,B2,L2:M3)”。
前3张统计表的数据就全部得到了，结果如图2-32所示。
图2-32 FREQUENCY分段统计及数据库函数同一字段双条件计数
使用数据库函数必须建立条件区域，如果要得到多个统计结果，就不太好布局条件区域和结果区域，因而使用数据库函数的时候不多，这里也仅介绍了DCOUNT函数使用同一字段两个条件计数的用法。
在F9单元格输入函数公式“=COUNTIF($B$3:$B$102,"<"&E9)”。
将F9单元格的公式向下填充到F11单元格，并将F11单元格公式中的“"<"&E11)-F10”更改为“">="&D11)”。
在G9单元格输入函数公式“=COUNTIFS($B$3:$B$102,">="&D9,$B$3:$B$102,"<"&E9)”。
将G9单元格的公式向下填充到G11单元格，并将G11单元格公式中的“"<"&E11”更改为“"<="&E11”。
在H9:H11区域输入数组公式“{=COUNTIFS(B3:B102,">="&D9:D11,B3:B102,"<"&E9:E11)}”。
在I9单元格输入函数公式“=SUMPRODUCT(($B$3:$B$102>=D9)*1,($B$3:$B$102<E9)*1)”。
在J9单元格输入数组公式“{=SUM(($B$3:$B$102>=D9)*($B$3:$B$102<E9))}”。
在K9单元格输入数组公式“{=SUM(IF(($B$3:$B$102>=D9)*($B$3:$B$102<E9),1,))}”。
在L9单元格输入数组公式“{=MMULT(--(TRANSPOSE($B$3:$B$102)>=D9),--($B$3:$B$102<E9))}”。
将I9:L9区域的公式向下填充到L11单元格，并将I11:L11区域公式中的“<E11”更改为“<=E11”。
在M9:M11区域输入数组公式“{=INDEX(MMULT(--(TRANSPOSE(B3:B102)>=D9:D11),--(B3:B102<TRANSPOSE(E9:E11))),ROW()-8,ROW()-8)}”。
这样，最后一张统计表的数据就全部得到了，结果如图2-33所示。
图2-33 多个函数应用于不等距次数分布表
【函数公式解析】
先接着上例，继续解析FREQUENCY函数的用法。本例为FREQUENCY函数的经典用法。本例数据分成3个组进行统计。不合格组（0＜X＜60）既不包括下限数值，也不包括上限数值；合格组（60≤X＜80）只包括下限数值，而不包括上限数值；优秀组（80≤X≤100）既包括下限数值，也包括上限数值。像这种中间各组数据包括组下限数值而不包括组上限数值（X1≤X＜X2）的情况，是按实下限分组。将全部数据分为几组的数值叫作分段点或间隔点。本例使用2个分段点“59.9”和“79.9”，就能将全部数据归入不合格、合格、优秀等3个组之中。为了区分，作为分段点的数值要比原始数据至少小一个单位。如果原始数据均为整数，则分段点数值就至少带一位小数。如果不嫌累赘，再多写几位小数也不是不可以。
按实下限分组时，间隔数组有两种情形。
一般情况下，作为间隔数组的数值由小到大排列。这又有两种写法。
一是写出上限数组。这又包括写出全部上限和不写最大上限两种用法。不写最大上限，就是只写间隔点，但也要在数组公式所在单元格区域中预留最大上限位置。本例的上限数组为“{59.9;79.9;100}”或“{59.9;79.9}”。
二是写出下限数组。此时不写出最小下限，但也要在数组公式所在单元格区域中预留最小下限位置。
再介绍数据库函数DCOUNT的用法。DCOUNT函数返回列表或数据库中满足指定条件的记录字段（列）中包含数字的单元格的个数。其语法为：
DCOUNT(database, field, criteria)
database：必需，构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含每一列的标签。
field：必需，指定函数所使用的列。使用列字段的单元格引用；或是输入两端带双引号的列标签，如“"数学"”“"产量"”等；或是代表列表中列位置的数字（不带引号）：“1”表示第1列，“2”表示第2列，依此类推。
criteria：必需，包含所指定条件的单元格区域。可以为参数criteria指定任意区域，只要此参数包含至少一个列标签，并且列标签下至少有一个在其中为列指定条件的单元格。
再补充COUNTIF函数的用法。COUNTIF函数天生是按单条件计数，配合其他函数或表达式，也能用于双条件计数。在F8单元格的公式“=COUNTIF($B$3:$B$102,"<"&E10)-F9”中，COUNTIF函数段得到小于80分的人数为“60”，减去F9单元格的值“2”，最后得数为“58”。
接下来隆重介绍COUNTIFS函数的用法。COUNTIFS函数天生按多条件计数，将条件应用于跨多个区域的单元格，然后统计满足所有条件的次数，为Excel 2007新增函数，是对COUNTIF函数的扩展，用法与COUNTIF函数类似。COUNTIFS函数的语法为：
COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)
criteria_range1：必需，在其中计算关联条件的第一个区域。
criteria1：必需，对要计数的单元格进行定义，条件的形式为数字、表达式、单元格引用或文本。
criteria_range2，criteria2，…：可选，附加的区域及其关联条件，最多允许127个区域／条件对。在G9单元格的公式“=COUNTIFS($B$3:$B$102,">="&D9,$B$3:$B$102,"<"&E9)”中，COUNTIFS函数使用了2个区域／条件对，是一个普通的函数公式。
COUNTIFS函数也能写成区域数组公式（也称多单元格数组公式）形式。在H9:H11区域的数组公式“{=COUNTIFS(B3:B102,">="&D9:D11,B3:B102,"<"&E9:E11)}”里，在第1个区域／条件对中，B3:B102区域的值是与D9:D11区域的值进行比较，而不是与一个单元格的值进行比较。输入函数公式前，要选择H9:H11区域，完成公式后要按“Ctrl+Alt+Enter”组合键结束。这个公式一次性作用于多个单元格，运算效率非常高。涉及的单元格引用为相对引用，不必写成绝对引用形式。
再介绍SUMPRODUCT函数的用法。SUMPRODUCT函数在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。其语法为:
SUMPRODUCT(array1,[array2],[array3],…)
array1：必需，其相应元素需要进行相乘并求和的第一个数组参数。
array2，array3，…：可选，为2～255个数组参数，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，将非数值型的数组元素作为0处理。在I9单元格的公式“=SUMPRODUCT(($B$3:$B$102>=D9)*1,($B$3:$B$102<E9)*1)”里，在第1个参数中，B3:B102区域的值与D9单元格的值进行比较，结果为一组逻辑值，这组逻辑值与“1”相乘，转换成一组数值。这组数值与其他2个参数的数值相乘，SUMPRODUCT函数求和得到“2”。显然，这是一个普通的函数公式。
再介绍SUM函数和SUM+IF函数的数组用法。在J9单元格的公式“{=SUM(($B$3:$B$102>=D9)*($B$3:$B$102<E9))}”里，B3:B102区域的值与D9单元格的值比较，得到一组逻辑值，B3:B102区域的值再与E9单元格的值比较，又得到一组逻辑值，两组逻辑值相乘，得到一组数值，最后SUM函数求和得到“2”。至于SUM+IF函数，先使用IF函数对两组逻辑值相乘得到的新数值数组进行逻辑判断，再使用SUM函数求和，不同于SUM函数的直接求和的思路和用法。
最后重点介绍MMULT函数的用法。MMULT函数返回两个数组的矩阵乘积，特别适合于数据量较大的情况。结果矩阵的行数与array1的行数相同，结果矩阵的列数与array2的列数相同。MMULT函数的语法为：
MMULT(array1, array2)
array1，array2：必需，是要进行矩阵乘法运算的两个数组；array1的列数必须与array2的行数相同，而且两个数组中都只能包含数值。TRANSPOSE是行、列之间转置的函数。在L9单元格的公式“{=MMULT(--(TRANSPOSE($B$3:$B$102)>=D9),--($B$3:$B$102<E9))}”里，在第1个参数中，TRANSPOSE函数先将B3:B102区域这个纵向区域转置成一个1行100列横向区域，这个横向区域再与D9单元格的值进行比较，结果为一组逻辑值，使用双减号“--”将这组逻辑值转换为一组数值。得到一个100行1列的数值数组。MMULT函数最后得到一个1行1列的值“2”。
MMULT函数也能写成区域数组公式形式。在M9:M11区域的数组公式“{=INDEX(MMULT(--(TRANSPOSE(B3:B102)>=D9:D11),--(B3:B102<TRANSPOSE(E9:E 11))),ROW()-8,ROW()-8)}”里，由于B3:B102区域的值是与D9:D11区域的值进行比较，B3:B102区域的值还与转置后的E9:E11区域的值进行比较，MMULT函数将得到一个3行3列的数组“{2,60,100;0,58,98;0,0,40}”。最后再用INDEX从这个3行3列数组的第几行第几列取出相应的值。输入整个公式前，要选择M9:M11区域，完成公式后要按“Ctrl+Alt+Enter”组合键结束。这个公式一次性作用于多个单元格，运算效率非常高。涉及的单元格引用为相对引用，不必写成绝对引用形式。
解题思路2：尽管0～60的组距是60，但由于60～80、80～100的组距都是20，且等距的两组集中在数据的大数端，所以还是可以利用数据透视表进行分项分段统计。
解题过程：插入数据透视表，创建组。
（1）插入数据透视表。操作过程为：
➊ 单击“插入”选项卡。
➋ 单击“表格”组中的“数据透视表”按钮。
➌ 在弹出的“创建数据透视表”对话框中，将鼠标放置于“选择一个表或区域”单选按钮右侧的“表／区域”框中，使用鼠标拖动选择A2:B102区域。
➍ 在“选择放置数据透视表的位置”组中单击“现有工作表”单选按钮。
➎ 将鼠标放置于“位置”框中，使用鼠标单击O1单元格。
➏ 单击“确定”按钮，随即生成空白数据透视表并弹出“数据透视表字段”任务窗格。
➐ 在“数据透视表字段”任务窗格，将字段节区域中的“数学”字段拖放到“行”区域节，将“学生”字段拖放到“值”区域节。
操作过程及结果如图2-34所示。
（2）创建组。操作过程为：
➊ 右击数据透视表“列标签”的任意单元格，例如O2单元格。
➋ 在快捷菜单中选择“组合”命令。
➌ 在弹出的“组合”对话框中将“起始于”框中的值修改为“0”或“60”。
➍ 将“终止于”框中的值修改为“100”。
➎ 将“步长”框中的值修改为“20”。
➏ 单击“确定”按钮，完成设置。
操作过程及结果如图2-35所示。
图2-34 插入数据透视表
图2-35 创建组
3．分项分段次数分布表
有时候，编制次数分布表，不仅需要按等距或不等距进行分组，还要求按单位类别进行分类。这时候编制的次数分布表就是分项分段分布表。而原始数据中的单位往往不是按单位类别排列在一起的。例如，一次期末考试，全级各班学生可能按上一次总分成绩从高到低编排座位，也可能是随机打乱进行安排，甚至可能每个学生前后左右不是本班的学生。对这类考试取得的成绩，无须进行排序操作，可以直接使用函数公式分项分段进行统计。
例2-9 已将某级250名学生的英语成绩录入文件“第2章统计表.xlsx”的“分项分段次数分布表”工作表中，如图2-36所示，如何按班级统计不合格人数及按10分的组距统计合格的各段人数？
解题思路1：由于本题要求统计不合格人数及按10分的组距统计合格的各段人数，因而次数分布为不等距；最高分这一组既包括组下限数值，也包括组上限数值；其他各组则只包括组下限数值，而不包括组上限数值（不计数的0分学生不输入“0”）。先按纵向为班、横向分段建立统计表，再使用COUNTIFS函数按指定的多条件计数。
解题过程：建立统计表，输入函数公式。
（1）建立统计表。本例介绍使用很常用的COUNTIFS函数和SUM函数进行多条件计数。在文件“第2章统计表.xlsx”的“分项分段次数分布表”工作表中，建立“COUNTIFS函数分项分段统计表”“SUM函数分项分段统计表”2个统计表，如图2-37所示（隐藏了部分行）。
图2-36 某级英语成绩
图2-37 分项分段统计示例表
（2）输入函数公式。
在E3单元格输入函数公式“=COUNTIFS($A$3:$A$252,$D12,$B$3:$B$252,"<60")”。
在F3单元格输入函数公式“=COUNTIFS($A$3:$A$252,$D12,$B$3:$B$252,"<70")-SUM($E12:E12)”。
将F3单元格的函数公式向右填充至I3单元格，并将G3、H3、I3单元格公式中的“<70”分别改为“<80”“<90”“<=100”。
在J3单元格中输入函数公式“=SUM(E3:I3)”。
将E3:J3区域的公式向下填充至J7单元格。
在E8单元格输入函数公式“=SUM(E3:E7)”。
将E8单元格的公式向右填充至J8单元格。参照例2-4的方法设置隐藏0值。
结果如图2-38所示。
图2-38 COUNTIFS函数分项分段统计表
在E12单元格输入数组公式“{=COUNTIFS($A$3:$A$252,$D3,$B$3:$B$252,"<60")}”。
在F12单元格输入数组公式“{=SUM(($A$3:$A$252=$D12)*($B$3:$B$252<70))-SUM($E12:E12)}”。
将F12单元格的函数公式向右填充至I12单元格，并将G12、H12、I12单元格公式中的“<70”分别改为“<80”“<90”“<=100”。
将J3:J7区域复制到J12:J17区域，将E8:J8区域复制到E17:J17区域。参照例2-4的方法设置隐藏0值。
结果如图2-39所示。
图2-39 SUM函数分项分段统计表
解题思路2：数据透视表胜任多条件统计，可以利用数据透视表进行分项分段统计。
解题过程：插入数据透视表，创建组。
（1）插入数据透视插入数据透视表。操作过程为：
➊ 单击“插入”选项卡。
➋ 单击“表格”组中的“数据透视表”按钮。
➌ 在弹出的“创建数据透视表”对话框中，将鼠标放置于“选择一个表或区域”单选按钮右侧的“表／区域”框中，使用鼠标拖动选择A2:B252区域。
➍ 在“选择放置数据透视表的位置”组中单击“现有工作表”单选按钮。
➎ 将鼠标放置于“位置”框中，使用鼠标单击L1单元格。
➏ 单击“确定”按钮，随即生成空白数据透视表并弹出“数据透视表字段”任务窗格。
➐ 在“数据透视表字段”任务窗格，将字段节区域中的“班级”字段拖放到“行”区域节，将“英语”字段分2次分别拖放到“列”区域节和“值”区域节。
操作过程及结果如图2-40所示。
图2-40 插入数据透视表
（2）创建组。操作过程为：
➊ 右击数据透视表“列标签”的任意单元格，例如M2单元格。
➋ 在快捷菜单中选择“组合”命令。
➌ 在弹出的“组合”对话框中将“起始于”框中的值修改为“60”。
➍ 单击“确定”按钮，完成设置。
操作过程及结果如图2-41所示。
图2-41 创建组
4．对二维表分项分段计数
一维表只有单一方向，二维表具有纵、横两个方向。二维表在组织数据时方便、快捷、直观、节省空间，应用非常广泛。很多时候需要对二维表分项分段计数。
例2-10 现有2个班6名学生的3科成绩，如表2-4所示。如何统计各科的不合格、合格及优秀人数？又如何统计各科各班或各班各科的不合格、合格及优秀人数？
表2-4 学生成绩表
解题思路1：按各科各班或各班各科统计不合格、合格及优秀人数，实质是多条件计数。前面介绍了使用多个函数进行多条件计数，这里使用经典、高效的COUNTIFS函数进行多条件计数。
解题过程：建立统计表，输入函数公式。
（1）建立统计表。在文件“第2章统计表.xlsx”的“二维表”工作表中，建立“学生成绩表”“成绩统计表1”“成绩统计表2”等3个表，录入相关数据，如图2-42所示。
图2-42 两个成绩统计表
（2）输入函数公式。
在I3单元格输入公式“=COUNTIFS(D$3:D$8,">=0",D$3:D$8,"<60")”。
将I3单元格的公式向下填充到I5单元格。将I4单元格公式中的“">=0"”“"<60"”分别修改为“">=60"”“"<80"”，将I5单元格公式中的“">=0"”“"<60"”分别修改为“">=80"”“"<=100"”。
将I3:I5区域的公式向右填充至K5单元格。
选择I3:K6区域，在“开始”选项卡“编辑”组中单击“求和”按钮 
。
在O3单元格输入公式“=COUNTIFS($A$3:$A$8,$N3,D$3:D$8,">=0",D$3:D$8,"<60")”。
将O3单元格的公式向下填充到O7单元格。将O5单元格公式中的“">=0"”“"<60"”分别修改为“">=60"”“"<80"”，将O7单元格公式中的“">=0"”“"<60"”分别修改为“">=80"”“"<=100"”。将O5单元格的公式向下填充到O6单元格，将O7单元格的公式向下填充到O8单元格。
在O9单元格输入函数公式“=SUM(O3:O8)”。
将O3:O9区域的公式向右填充至Q9单元格。
结果如图2-43所示。
图2-43 对二维表分项分段计数的结果
解题思路2：本例原始数据表为一个二维数据表，也可以使用数据透视表功能进行计数，但需要建立二级数据透视表才能正确统计。
解题过程：建立一级数据透视表，生成明细表，建立二级数据透视表，数据分组，取消总计列，复制数据透视表并多样化布局。
（1）建立一级数据透视表。
操作过程及结果如图2-44所示。
➊ 按Alt键松手后，依次按下键盘上的D、P键。
➋ 在弹出的“数据透视表和数据透视图向导--步骤1（共3步）”对话框中选择“多重合并计算数据区域”单选按钮。
➌ 选择“下一步”按钮。
➍ 在弹出的“数据透视表和数据透视图向导--步骤2a（共3步）”对话框中选择“自定义页字段”单选按钮。
图2-44 利用向导对二维表创建数据透视表
➎ 单击“下一步”按钮。
➏ 在弹出的“数据透视表和数据透视图向导--第2b步，（共3步）”对话框中选择A2:F8区域。
➐ 单击“添加”按钮，所选区域进入“所有区域”列表框中。
➑ 单击“下一步”按钮。
➒ 在弹出的“数据透视表和数据透视图向导--步骤3（共3步）”对话框中选择“现有工作表”单选按钮。
➓ 将鼠标放置于其框内，单击S1单元格。
单击“完成”按钮，完成对二维表创建数据透视表。在“数据透视表字段”窗格中，自动生成“行”“列”“值”3个字段，并默认将“行”字段放到“行”区域，将“列”字段放到“列”区域，将“值”字段放到“值”区域。列字段包括“数学”“语文”“外语”这几项，也有总计列和总计行。
注意
“Alt+D+P”方式是Office旧版本菜单序列，是打开Excel“数据透视表和数据透图向导”功能的键盘操作方法。此功能未在Excel 2016功能区显示，用户如果频繁使用，可以将它放到“自定义快速访问工具栏”。
（2）生成明细表。操作过程为：
➊ 右击W5单元格。
➋ 在快捷菜单中选择“显示详细信息”菜单选项，随即在新工作表中生成一个名为“表1”的一维明细表。也可以直接双击W5单元格生成一维明细表。
➌ 将新工作表的名称修改为“二维表分项分段计数”。
操作过程及结果如图2-45所示。
图2-45 生成明细表
（3）建立二级数据透视表。操作过程为：
➊ 在“二维表分项分段计数”工作表中，单击A1单元格。
➋ 单击“插入”选项卡。
➌ 单击“表格”组中的“数据透视表”按钮。
➍ 在“选择放置数据透视表的位置”组中单击“现有工作表”单选按钮。
➎ 将鼠标放置于“位置”框中，使用鼠标单击E1单元格。
➏ 单击“确定”按钮，随即生成空白数据透视表并弹出“数据透视表字段”任务窗格。
➐ 在“数据透视表字段”任务窗格，将字段节区域中的“列”字段拖放到“列”区域节，将字段节区域中的“值2”字段分别拖放到“行”“值”区域节，如图2-46所示。
（4）数据分组。操作过程为：
➊ 右击数据透视表中“行标签”下的任意单元格，例如E3单元格。
➋ 在快捷菜单中选择“组合”菜单。
图2-46 建立二级数据透视表
➌ 在弹出的“组合”对话框中，将“起始于”框中的值修改为“0”。
➍ 将“终止于”框中的值修改为“100”。
➎ 将“步长”框中的值修改为“20”。
➏ 单击“确定”按钮，完成设置。
操作过程及结果如图2-47所示。
图2-47 数据分组
（5）取消“总计”列。操作过程为：
➊ 单击“数据透视表工具·分析”选项卡。
➋ 在“数据透视表”组中，单击“总计”按钮。
➌ 在下拉菜单中选择“仅对列启用”命令。
➍ 将F2:H6区域数据居中排列。
操作过程及结果如图2-48所示。
图2-48 取消“总计”列
（6）复制数据透视表并多样化布局，操作过程为：
➊ 将数据透视表复制，分别在J1、E14、J14单元格粘贴3个数据透视表。
➋ 在J1单元格的数据透视表，在“数据透视表字段”窗格，将字段节中的“行”字段拖到“行”区域节的上面。
结果如图2-49所示。
图2-49 对J1单元格的数据透视表进行布局
➌ 在E14单元格的数据透视表，“数据透视表字段”窗格中，将字段节中的“行”字段拖到“行”区域节的下面。效果如图2-50所示。
➍ 在J14单元格的数据透视表，“数据透视表字段”窗格中，将“列”字段节中的“列”字段与“行”字段节中的“行”字段互换位置，将字段节中的“行”字段拖到“行”区域节的下面。
结果如图2-50所示。
图2-50 对J14单元格的数据透视表进行布局
5．双列次数分布表
双列次数分布表又称为相关次数分布表，是对有联系的两列变量使用同一个表表示其次数分布。这种表需要从横向和竖向两个方向对值进行分组。在横向和竖向上，分组区间都最好先小数再大数，以便于观察。下面通过一个例子介绍双列次数分布表。
例2-11 30人的视听反应时如表2-5所示。试编制双列次数分布表。
表2-5 30人的视听反应时（毫秒）
解题思路1：先按竖向为“视”、横向为“听”建立分段统计表，每组组限使用下限值来表示，最小的下限值要包括这批数据的最小值。根据这批数据的特征，“视”“听”两个变量的组距均定为20，组数为7组。再使用COUNTIFS函数按指定多条件计数。
解题过程：建立表格，输入函数公式。
（1）建立表格。在文件“第2章统计表.xlsx”的“双列次数分布”工作表中，建立“COUNTIFS函数应用于双列次数分布表”“SUM函数应用于双列次数分布表”，录入相关数据，如图2-51所示（隐藏了部分行）。
（2）输入函数公式。
在F3单元格输入函数公式“=COUNTIFS($B$3:$B$32,">=$"&$E3,$B$3:$B$32,"<"&$E4,$C$3:$C$32,">="&F$2,$C$3:$C$32,"<"&G$2)”。
将F3单元格的公式向右填充至L3单元格，再将L3单元格公式中的COUNTIFS函数的第4对参数“,$C$3:$C$32,"<"&M$2)”去掉。
在M3单元格输入公式“=SUM(F3:L3)”。
将F3:M3区域的公式向下填充至M9单元格。
在F10单元格输入函数公式“=SUM(F3:F9)”。
将F10单元格的公式向右填充至M10单元格。
在F14单元格输入数组公式“{=SUM(($B$3:$B$32>=$E14)*($B$3:$B$32<$E15)*($C$3:$C$32>=F$13)*($C$3:$C$32<G$13))}”。
图2-51 建立双列次数分布表
将F14单元格的公式向右向下填充至L9单元格，将M3:M9区域的公式复制到M14:M20区域，将F10:M10区域的公式复制到F21:M21区域。参照例2-4的方法设置隐藏0值。
结果如图2-52所示。
图2-52 双列次数分布表
注意
完成双列次数分布表，可以结合散点图进行分析，如图2-53所示。
图2-53 双列次数分布表
解题思路2：数据透视表对行标签和列标签都可以进行分组，因而可以用于编制双列次数分布表。
解题过程：插入数据透视表，对行标签创建组，对列标签创建组，更改汇总依据。
（1）插入数据透视表。操作过程为：
➊ 选择A2:C32区域。
➋ 单击“插入”选项卡。
➌ 单击“表格”组中的“数据透视表”按钮。
➍ 在弹出的“创建数据透视表”对话框中选择“现有工作表”单选按钮。
➎ 将鼠标放置于“位置”框中单击O1单元格。
➏ 单击“确定”按钮，随即生成空白数据透视表并弹出“数据透视表字段”任务窗格。
➐ 在“数据透视表字段”任务窗格，将字段节区域中的“视”字段拖放到“行”区域节，将“听”字段分2次分别拖放到“列”区域节和“值”区域节。
操作过程及结果如图2-54所示。
图2-54 插入数据透视表
（2）对行标签创建组。操作过程为：
➊ 单击数据透视表“行标签”的任意单元格，例如O3单元格。
➋ 单击“数据透视表工具·分析”选项卡。
➌ 在“分组”组中单击“组字段”按钮。
➍ 在弹出的“组合”对话框中将“起始于”框的值修改为“110”。
➎ 将“终止于”框的值修改为“250”。
➏ 将“步长”框的值修改为“20”。
➐ 单击“确定”按钮，完成设置。
操作过程及结果如图2-55所示。
图2-55 对行标签创建组
（3）对列标签创建组。操作过程为：
➊ 右击数据透视表“列标签”的任意单元格，例如P2单元格。
➋ 在快捷菜单中单击“组合”命令。
➌ 在弹出的“组合”对话框中将“起始于”框的值修改为“100”。
➍ 将“终止于”框的值修改为“240”。
➎ 将“步长”框的值修改为“20”。
➏ 单击“确定”按钮，完成设置。
操作过程及结果如图2-56所示。
图2-56 对列标签创建组
（4）更改值汇总依据。操作过程为：
➊ 右击值区域的任意单元格，例如P3单元格。
➋ 在快捷菜单中选择“值汇总依据”菜单的级联菜单选项“计数”。
➌ 适当调整列宽，将数据透视表区域的单元格格式调整为居中和垂直居中对齐。
操作过程及结果如图2-57所示。
图2-57 更改值汇总依据的结果
注意
数据透视表“区域节”中的“值”区域，如果是数值字段，则默认的值汇总方式为求和；如果是文本字段，则默认的值汇总方式为计数。
2.2.3 相对次数分布表
无论是单项式次数分布表，还是组距式次数分布表，所统计的次数均为绝对次数。有时候，绝对次数不能准确反映事物的本质。例如，某年A校上北大、清华28人，而B校上北大、清华30人。只看这两个数字，会理所当然地认为B校的教学质量略高于A校。但当年A校、B校的考试人数分别为800人、1000人，A校的北大、清华上线率就为28÷800=3.5%，B校的北大、清华上线率就为30÷1000=3%，显然，A校的教学质量略高于B校。因此，有时需要将次数转换为相对次数。
在统计学中，相对次数也叫频率。相对次数是各组的次数与总次数的比值。相对次数可以用小数来表示，通常会转换为百分数。相对次数越大，表示落入该组内的数据个数占全部数据个数的比例就越大；反之，就越小。当然，很多时候，次数分布表与相对次数分布表既可单独使用，也可以联合使用。下面，通过一个例子来介绍相对次数分布表。
例2-12 在文件“第2章统计表.xlsx”的“等距次数分布表”工作表中，例2-7已将100名学生的语文成绩进行了等距分组，统计出各组的人数，如何将各组人数转换为相对人数？
解题思路：先使用各组人数除以总人数得到各组的比值，再将百分数区域的单元格格式设置成百分比。
解题过程：建立表格，输入函数公式，设置格式。
（1）建立表格。在原表基础上增加1列“百分数”，如图2-58所示。
（2）输入函数公式。
图2-58 增加1列“百分数”
在L3单元格输入函数公式“=K3/SUUM($K$3:$K$10)”。
将L3单元格的公式向下填充至L10单元格。
（3）设置格式。操作过程为：
➊ 选择L3:L10区域。
➋ 单击“开始”选项卡。
➌ 在“数字”组中单击百分比按钮 
。
操作过程及结果如图2-59所示。
图2-59 设置百分比格式
2.2.4 累积次数及累积百分数分布表
数据经过分组统计后，在一般的次数分布表与相对次数分布表中，只标出各分组区间数据的次数或相对次数。如果想轻易地了解某个数值以下或以上的数据的次数或相对次数，就要累积次数或累积相对次数。累积时，可以根据需要从上往下累积或从下往上累积，最后一组的累积次数等于数据的总次数，最后一组的累积相对次数等于1或100%。有了累积次数表或累积百分数表，当从小数端往大数端累积时，就可以方便地了解某一分组区间上限以下的累积次数表或累积百分数；当从大数端往小数端累积时，就可以方便地了解某一分组区间下限以上的累积次数表或累积百分数。当然，如果要包括上限值或下限值本身，使用语言表达时，一般要使用“及其”二字，例如“80及其以下”。下面，通过一个例子来介绍累积次数及累积百分数分布表。
例2-13 在文件“第2章统计表.xlsx”的“等距次数分布表”工作表中，例2-7和例2-12已统计出各组人数及其百分数，如何再计算各组的累积人数及累积百分数，以方便查看某个数值以上的数据的次数或相对次数，或者查看某个数值以下的数据的次数或相对次数？
解题思路1：由于本例要查看某个数值以上或以下的数据的次数或相对次数，所以本例既要从上往下累积，也要从下往上累积。先计算出累积人数和累积百分数，再将累积百分数区域的单元格格式设置成百分比。
解题过程：建立表格，输入函数公式。
（1）建立表格。在原表基础上增加4列，如图2-60所示。
图2-60 增加4列
（2）输入函数公式。
在M3单元格输入函数公式“=SUM(K$3:K$10)-SUM(K$1:K2)”。
在O3单元格输入函数公式“=SUM(K$2:K3)”。
将M3单元格的公式向右填充至N3单元格，将O3单元格的公式向右填充至P3单元格，将M3:P3区域的公式向下填充至P10单元格。
按住Ctrl键，选择N3:N10、P3:P10两个不连续的区域，设置百分比格式。
操作过程及结果如图2-61所示。
图2-61 累积次数及累积百分数分布表
从表中可以看出，因“80”在“75<X≤80”一组中为实上限，所以80分及以下有39人。因“80”在“80<X≤85”一组中不是实下限，所以80分以上有61人。
解题思路2：利用数据透视表具有的按某一字段向下汇总次数及其百分比的功能来实现。
解题过程：布局数据透视表，修改值的显示方式。
（1）布局数据透视表。在2.2.2小节的“2．不等距次数分布表”中所建立的数据透视表的基础上，进行如下操作：
➊ 在“数据透视表字段”窗格，将“字段节”的“学生”字段分2次拖动到“区域节”中的“值”区域。
➋ 将T1、U1单元格中的值分别修改为“向下累积人数”“向下累积百分数”。
操作过程及结果如图2-62所示。
图2-62 对数据透视表重新布局
（2）修改值的显示方式。
➊ 右击“向下累积人数”字段的任意单元格，例如T1单元格。
➋ 在快捷菜单“值显示方式”的级联菜单中选择“按某一字段汇总”命令。
➌ 在弹出的“值显示方式（向下累积人数）”对话框中选择“基本字段”下拉列表中的汇总字段，本例默认为唯一的字段“语文”。
➍ 单击“确定”按钮，完成设置。
➎ 同理，为“向下累积百分数”字段选择的“值显示方式”为“按某一字段汇总的百分比”。
操作过程及结果如图2-63所示。
图2-63 修改值显示方式