Excel 如何合并多个单元格中的文本?

图7.17所示的报表如何才能转换成图7.18所示的报表?

其中图7.18中第二列是合并参赛队员姓名,中间用顿号隔开,第三列则用于计算3个队员的平均分。

图7.17 转换前的成绩表

图7.18 转换后的成绩表

解题步骤

没有什么内置工具能一次性完成这种表格样式转换,也没有任何函数可以实现这种转换,但是用函数配合基础操作技巧则可以完成需求,具体操作步骤如下。

1.在D1:E1区域输入“参赛队员”、“综合得分(平均值)”。

2.在D2单元格输入公式“=IF(A2<>A1,B2㊣"、"㊣B3㊣"、"㊣B4,0/0)”,在E2单元格输入公式“=TEXT(AVERAGE(C2:C4),"0.00")”。

3.选择D2:E2区域,然后双击填充柄,使其向下填充到第13列,此时D2:D13区域的公式会计算出每个班级对应的队员姓名,合并到一个单元格中,E2:E13 区域则会计算出每组队员的综合得分,效果如图7.19所示。

图7.19 填充公式

4.选择 D:E 区域,然后按组合键<Ctrl+C>复制区域,接着单击右键,在右键菜单中的粘贴选项中选择“值”,从而将D:E区域的公式转换成值。

5.单击功能区的“开始”→“查找和选择”→“定位条件”,弹出“定位条件”对话框。

6.单击“常量”,然后取消选择“数字”、“文本”、“逻辑值”复选框,设置界面如图 7.20所示。单击“确定”按钮后,Excel 会定位 D 列的所有错误值所在单元格,定位结果如图 7.21所示。

图7.20 设置定位条件

图7.21 定位结果

7.单击右键D2,然后从右键菜单中选择“删除”,弹出“删除”对话框。

8.将删除选项设置为“整行”,界面如图7.22所示,图7.23则是删除结果。

图7.22 设置删除选项

图7.23 删除结果

9.删除B列和C列,对数据区域添加边框,最终效果如图7.24所示。

图7.24 最终转换效果

知识扩展

1.&是一个运算符,用于将多个字符连接成一个字符串,例如:

="A"&"b"——运算结果是“Ab”

="上海"&2008——运算结果是“上海2008”

连接文本时,文本必须添加半角引号,连接单元格中的值或连接数字时,单元格地址和数字不需要加引号。

表达式“B2&"、"&B3&"、"&B4”表示用B2单元格的值连接顿号,再连接B3的值、连接顿号、连接B4的值。不能写成“B2&、&B3&、&B4”,也不能写成“"B2&、&B3&、&B4"”。

2.公式“=IF(A2<>A1,B2&"、"&B3&"、"&B4,0/0)”中的IF是条件判断函数,在此处用于判断 A 列当前行与上一行的字符是否相同,如果不同则合并姓名,相同则生成一个错误值。生成错误值的目的是与合并的姓名加以区分,便于后续删除错误值所在行。

3.表达式“0/0”的运算结果是错误值,当把公式转换成值后这个错误值不再是公式性质的错误值,而是常量性质的错误值。所以在定位对话框中需要选中“常量”,然后选择“错误”。

4.TEXT 函数在本例中的作用是将数值中的小数精确到两位,将第 3 位四舍五入。也可以改用Round函数来实现相同的功能。

5.合并区域中的文本还有一个更方便的函数PHONETIC,它能一次性合并任意区域中的所有文本,使用时比&运算符方便得多。例如,将A1:C1区域的所有值合并成一个字符串,使用&运算符比较麻烦,需要使用&连接每一个单元格地址,公式为“=A1&B1&C1”,而使用PHONETIC函数合并字符则可以一次性完成,区域大小不影响公式长度,效果如图7.25所示。

图7.25 利用PHONETIC合并区域中的文本

然而 PHONETIC 函数有较多的限制,主要体现在三方面。其一,它只能合并文本,不能合并数值;其二,如果单元格中包含公式,PHONETIC函数无法对它进行合并;其三,PHONETIC函数只能合并单元格中的文本,对于手工输入的字符串参数则无法合并,即“=PHONETIC(“大海”,“天空”)”无法执行合并,只能将它们写到单元格中,然后对单元格的值执行合并。

发布者

Excel22

专为职场萌新准备的免费全面的Excel入门及提高学习网站,也可作为Excel老司机杂耍各种函数的宝典 —— Excel22.com 网址超好记

发表评论

邮箱地址不会被公开。 必填项已用*标注