3.8 茎叶图
茎叶图又称“枝叶图”，思路是将数组中的数值按位数进行比较，将数的大小基本不变或变化不大的位作为一个主干（茎），将变化大的位的数作为分枝（叶），列在主干的后面。如果数据是整数，“叶”就为个位数；如果是小数，“叶”就取十分位数；如果有几位小数，可以采取适当进位的方法，“叶”为进位后的最后一位小数。“茎”和“叶”的数字有一个排列顺序。按从小到大的顺序，“茎”从下往上排列，“叶”从左到右排列。当然，排列顺序也可以相反。常常在茎叶图的左侧统计出每一条分枝包含“叶”的个数。这样，可以清楚地看到每个分枝后面的几个数，每个数具体是多少。
茎叶图形似条形图，将茎叶图逆时针方向旋转90°，实际上就是一个直方图或柱形图。茎叶图是一个与直方图相类似的特殊工具，但又与直方图不同，茎叶图保留了原始资料的信息，直方图则失去了原始资料的信息。茎叶图的最大优点是保留了全部的原始数据，同时呈现出直方图的外观，展现出数据的分布形态，兼具次数分布表与直方图的双重优点。不仅适合表现单组数据，也非常适合对比两组数据。当然，用于制作直方图的原始数据不要太多，不宜超过100个。在Excel中，可以手动绘制茎叶图，也可以利用函数公式和辅助列自动绘制。下面以创建一个可以选择小数位数的单侧茎叶图模板为例进行介绍。
例3-21 在文件“第3章统计图.xlsx”的“茎叶图”工作表中，有30个数据，试制作一个可以增减数据和可以选择小数位数（0～2位）的单侧茎叶图模板。
解题思路：在Excel中，制作一个可以增减数据、可以选择小数位数（0～2位）的单侧茎叶图模板，就要将数据区域确定为动态区域，还要确定一个单元格以设置数据验证来选择小数位数，并且设置辅助列对数据自动排序，自动提取出“茎”和“叶”的数据，最后将“茎”和“叶”数据自动安排到茎叶图中。为了让茎叶图更美观，还要设置条件格式自动添加框线或隐藏函数公式产生的不必要的“0”值。
解题过程：模板布局，定义名称，设置单元格格式，设置和使用数据验证，输入数据和公式，设置条件格式自动添加“叶”框线，设置条件格式自动添加“茎”框线，验证模板。
（1）模板布局。如图3-115所示，A列为原始数据，C:E列为辅助列，F1单元格选择小数位数，G:AB列为茎叶图部分。
图3-115 模板布局
（2）定义名称。操作过程为：
➊ 单击“公式”选项卡。
➋ 在“定义的名称”组中单击“定义名称”按钮。
➌ 在弹出的“新建名称”对话框的“名称”框中输入“data”。
➍ 在“引用位置”框中输入公式“=OFFSET($A$2,,,COUNT($A:$A),)”。
➎ 单击“确定”按钮，完成设置。
操作过程及结果如图3-116所示。
图3-116 定义名称
【函数公式解析】
式中，COUNT函数获取A列数值的个数，作为OFFSET函数偏移的高度。这样，通过COUNT函数获取数值个数的变化，让OFFSET函数实现了动态引用，原始数据就可以随意增减，这就为打造一个统计图模板打下了基础。
（3）设置单元格格式。将F1单元格设置为自定义单元格格式“0"位小数"”。
（4）设置和使用数据验证。操作过程为：
➊ 单击F1单元格。
➋ 单击“数据”选项卡。
➌ 在“数据工具”组中单击“数据验证”按钮。
➍ 在弹出的“数据验证”对话框中单击“设置”选项卡。
➎ 在“验证条件”的“允许”下拉列表中选择“序列”选项。
➏ 在“来源”框中输入“0,1,2”（此处为半角逗号）。
➐ 单击“确定”按钮，完成数据验证的设置。
➑ 单击F1单元格。
➒ 在下拉列表中选择一个数字，例如“1”。
操作过程及结果如图3-117所示。
图3-117 设置和使用数据验证
（5）输入数据和公式。
在A2:A31区域输入30个数据“{0.07;0.24;0.95;0.98;1.02;0.98;1.37;1.4;0.39;1.02;1.44;1.58;0.54;1.08;0.61;0.72;1.2;1.14;1.62;1.68;1.85;1.2;0.81;0.82;0.84;1.29;1.26;2.1;0.91;1.31}”。
在C2单元格输入公式“=IFERROR(TEXT(SMALL(data,ROW(A1)),CHOOSE($F$1+1,"0","0.0","0.00")),"")”。
在D2单元格输入公式“=IF(C2="","",IF(C2=0,,IF((C2>0)*(LEN(C2)=1),"0",IF((LEN(C2)=2)*(IFERROR(FIND("-",C2),)),"-0",LEFT(C2,LEN(C2)-1)))))”。
在E2单元格输入公式“=RIGHT(C2,1)”。
将C2:E2区域的函数公式向下填充至需要的地方，例如E101单元格。
在H2单元格输入公式“=COUNTIF(J2:AB2,">=")”。
在I2单元格输入数组公式“{=IFERROR(INDEX(D$2:D$101,SMALL(IF(MATCH(D$2:D$101,D$2:D$101,)=ROW($1:$100),ROW($1:$100),2^20),ROW(A1))),"")}”。
在J2单元格输入数组公式“{=IFERROR(INDEX($E$2:$E$101,SMALL(IF($D$2:$D$101=$I2,ROW($1:$100),2^20),COLUMN(A$1)))&"",)}”。
将H2:Z2区域的函数公式向下填充至需要的地方，例如AB31单元格。
参照2.1.5小节，隐藏H2:AB31区域多余的0值。
结果如图3-118所示。
图3-118 输入数据和公式后的结果
【函数公式解析】
先解析C2单元格的公式“=IFERROR(TEXT(SMALL(data,ROW(A1)),CHOOSE($F$1+1,"0","0.0","0.00")),"")”。
式中，ROW函数返回引用的行号。
式中，SMALL函数返回数据集之中的第k个最小值。具体语法为：
SMALL(array,k)
array：必需，需要找到第k个最小值的数组或数值数据区域。
k：必需，要返回的数据在数组或数据区域里的位置（从小到大）。
式中，CHOOSE函数返回数值参数列表中的数值。具体语法为：
CHOOSE(index_num, value1, [value2],…)
index_num：必需，用于指定所选定的数值参数，必须是介于1到254的数字，或是包含1到254之间数字的公式或单元格引用；如果index_num为1，则CHOOSE返回value1；如果为2，则CHOOSE返回value2，以此类推。
value1：必需，后续值是可选的，为1～254个数值参数。CHOOSE函数将选择一个数值或一项要执行的操作。
式中，TEXT函数将数值转换为按指定数字格式表示的文本。具体语法为：
TEXT(value,format_text)
value：数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。
format_text：“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。
式中，IFERROR函数可捕获和处理公式中的错误，具体语法为:
IFERROR(value,value_if_error)
value（值）：必需，检查是否存在错误的参数。
value_if_error：必需，公式的计算结果错误时返回的值。
本式，ROW函数返回的行号为“1”，作为SMALL函数的第2个参数，SMALL函数的第“1”个最小值为“{0.07}”，作为TEXT函数的第1个参数。CHOOSE函数的第1个参数为“2”，返回后继参数“"0","0.0","0.00"”中的第2个位置，为“"0.0"”，即保留1位小数。TEXT函数把第1个参数“{0.07}”保留1位小数得到“0.1”。此结果为IFERROR函数的，本身是一个正确值，因此IFERROR函数的计算结果仍然为“0.1”；如果为错误值，IFERROR函数的计算结果为空。
再解析D2单元格的公式“=IF(C2="","",IF(C2=0,,IF((C2>0)*(LEN(C2)=1),"0",IF((LEN(C2)=2)*(IFERROR(FIND("-",C2),)),"-0",LEFT(C2,LEN(C2)-1)))))”，该公式确定“茎”的值。此公式为IF函数的多次嵌套。其中，LEN返回文本字符串中的字符个数；FIND函数用于在第2个文本串中定位第1个文本串，并返回第1个文本串的起始位置的值，该值从第2个文本串的第1个字符算起；LEFT从文本字符串的第1个字符开始返回指定个数的字符。
本式的逻辑为：如果C2单元格为空，则为空；如果C2单元格的值为“0”，则为“0”；如果C2单元格的值大于0且长度为1个字符，则为“"0"”；如果C2单元格的值的长度为2个字符且发现有符号“-”，则为“-0”，否则为C2单元格的值去掉右边1位数后的值。最后得到“0.”。
接着解析E2单元格的公式“=RIGHT(C2,1)”。RIGHT函数根据所指定的字符数返回文本字符串中最后一个或多个字符。这里返回“0.1”右边第1个字符，为“1”。
再解析H2单元格的公式“=COUNTIF(J2:AB2,">=")”。
式中，COUNTIF函数是一个统计函数，用于统计满足某个条件的单元格的数量。本式，COUNTIF函数统计非空文本（含空格）的个数。
接着解析I2单元格的公式“{=IFERROR(INDEX(D$2:D$101,SMALL(IF(MATCH(D$2:D$101,D$2:D$101,)=ROW($1:$100),ROW($1:$100),2^20),ROW(A1))),"")}”。
式中，MATCH函数在区域中搜索特定的项，然后返回该项在此区域中的相对位置。具体语法为：
MATCH(lookup_value,lookup_array,[match_type])
lookup_value（查找值）：必需，要在lookup_array中匹配的值，可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。lookup_array（查找区域）必需，要搜索的单元格区域。
match_type（搜索类型）：可选，为数字-1、0或1；如果为1或省略，则查找小于或等于lookup_value的最大值，lookup_array参数中的值必须以升序排序；如果为0，则查找完全等于lookup_value的第1个值，lookup_array参数中的值可按任何顺序排列。如果为-1，则查找大于或等于lookup_value的最小值，lookup_array参数中的值必须按降序排列。
本式，MATCH函数精确查找D2:D101区域在D2:D101区域首次出现的位置，为一个数组，该数组与ROW函数产生的行号数组进行比较，其结果作为IF函数的。若相等，则为行号数组值，否则为220（1048576）。IF函数得到的结果再作为SMALL函数的，SMALL函数依次查找到的最小值又作为INDEX函数的，INDEX函数从D2:D101区域查找，返回相应的值。最后使用IFERROR函数屏蔽错误值为空。
J2单元格公式与I2单元格公式的原理相同，不再赘述。只是COLUMN函数返回指定单元格引用的列号。
（6）设置条件格式自动添加“叶”框线。操作过程为：
➊ 选择M1:AB1区域。
➋ 单击“开始”选项卡。
➌ 在“样式”组中单击“条件格式”按钮。
➍ 在下拉菜单中选择“新建规则”命令。
➎ 在弹出的“新建格式规则”对话框中选择“选择规则类型”列表框的“使用公式确定要设置格式的单元格”选项。
➏ 在“为符合此公式的值设置格式”引用框中输入公式“=COUNTIF(L$2:L$41,">=")”。
➐ 单击“格式”按钮。
➑ 在弹出的“设置单元格格式”对话框中单击“边框”选项卡。
➒ 单击下框线。
➓ 单击“确定”按钮2次，完成设置。
操作过程及结果如图3-119所示。
图3-119 设置条件格式自动添加“叶”框线
（7）设置条件格式自动添加“茎”框线。选择I4:I31区域，设置过程与设置“叶”框线相同，条件格式公式为“=ISNUMBER(--$I4)”，结果如图3-120所示。
图3-120 自动添加“茎”框线
（8）验证模板。操作过程为：
➊ 将A2:A31的数据更换为“{38;42;45;49;51;52;53;56;58;60;62;64;65;65;68;71;74;75;76;78;78;80;81;83;85;90;91;93;77;78}”。
➋ 在F1单元格下拉列表中选择“0”，结果如图3-121所示。
图3-121 更换数据
总之，Excel 2016的图表丰富多样，富有表现力，让用户可以得心应手地表达和展示统计数据，让统计分析工作变得轻松方便，让统计分析结果赏心悦目。