图7.75中A列的省名和市名分别放在一个单元格的两行中,现要求在D列提取出所有市名,能否一次性完成?

Excel 能否引用每个单元格的第二行数据?-Excel22

图7.75 参赛人员资料表

解题步骤

单元格的第一行和第二行之间存在换行符,这是一个明显的规律,因此可以使用函数查找换行符的位置,然后使用函数提取该位置之后的所有字符,从而取得市名,具体操作步骤如下。

1.在D1中输入标题“市名”。

2.在D2中输录入以下公式:

Excel 能否引用每个单元格的第二行数据?-Excel22

3.双击D3单元格的填充柄,从而使公式向下填充到D11,公式会自动取得所有市名,效果如图7.76所示。

Excel 能否引用每个单元格的第二行数据?-Excel22

图7.76 提取市名

知识扩展

1.CHAR(10)代表换行符,FIND 函数是位置查找函数,表达式“FIND(CHAR(10),A2)”的含义是在A2单元格中查找换行符,返回换行位所在位置,如果A2单元格不存在换行符则会返回错误值。FIND函数的语法如下:

Excel 能否引用每个单元格的第二行数据?-Excel22

第一参数代表要查找的字符,第二参数代表被查找的字符,第三参数用于指定从第几个字符开始查找,它是可选参数,当忽略参数时表示从第一个字符开始查找。

整体而言,Find函数的功能是在第二参数中查找第一参数的值,查找位置由第三参数决定,返回值是第一参数在第二参中出现的位置。

2.MID函数用于从字符中指定位置开始提取指定长度的字符串,它的语法如下:

Excel 能否引用每个单元格的第二行数据?-Excel22

第一参数是字符串对象,第二参数代表起始位置,第三参数代表长度。如果要用一句话说明MID 函数的功能,就是从第一参数中提取部分字符,该字符的位置与长度分别由第二、第三参数决定。

=MID("中华人民共和国",3,2)——从第3位开始提取两个字,结果为“人民”;

=MID("中华人民共和国",1,7)——从第1位开始提取7个字,结果为“中华人民共和国”;

=MID("中华人民共和国",3,9)——从第3位开始提取9个字,结果为“人民共和国”,

提取长度大于实际的字符数量时会忽略多余的长度。

本例中MID的第二参数由 FIND函数计算而来,因为换行符的位置并不固定,只能用函数才能取得准确位置。

MID 的第三参数原本应该使用函数计算,从而取得市名长度,本例采取了更通用、更简便的方法——由于市名长度不会大于9位,同时又由于MID的第3参数会忽略超出的部分,因此直接采用9(换成99也行),只要大于等于市名的最大长度就可以提取出目标。

3.IF函数的功能是条件判断,符合指定条件时返回一个值,否则返回另一个值。本例中用IF函数判断A列的当前行是否有字符,如果没有则直接返回空文本,从而避免公式得到错误值。

在实际工作中,极少情况下是采用单个函数就能解决问题的,通常需要多个函数嵌套,用函数的计算结果作为另一个函数的参数,从而满足复杂的工作需求。