上周在一家银行企业培训Excel的时候，学员小A咨询如何将下图所示的每个行长的日程：
按照当天日期合并到汇总表的一个单元格中：
我们来分解一下小A的需求：根据B1单元格中的当天日期，去到A列对应的姓名所在的工作表中，将那个日期的所有上午、中午、下午的行程全部调取出来，并且将多行文本合并到一个单元格当中。
我们一步步来实现这个需求。
由于每个人每天的行程数量有多有少，可能3行，也可以是2行，所以我们不能使用仅仅只返回符合条件的单行结果的VLOOKUP函数，而需要使用动态区域引用的OFFSET函数。我们先来看一下OFFSET函数的语法：
OFFSET（起始位置，向上/下偏移的行数，向左/右偏移的列数，行数，列数）
向上或向左偏移输入负数
本例中的函数是：
=OFFSET(陈行!$F$1,MATCH($B$1,陈行!A:A,0)-1,0,3,1)
要返回的区域的起始位置是陈行这张表的F1单元格，向下偏移的行数是B1单元格中输入的当天日期在陈行这张表中所在行减去1行，这里使用了查询位置的MATCH函数，MATCH的语法是：
MATCH(查询值，包含查询值的一行或一列，0）
最后一个参数0表示精确查询
OFFSET第3个参数0表示没有列的偏移，第4个参数3表示返回3行数据，小C说每个领导每天的日程安排都是分成上午、中午、下午三行来写的。如果行数不固定，也可以用下一个工作日的位置减去当日的日期计算出行数。第5个参数1表示一共返回1列。
这样我们就根据当天日期动态返回了陈行长的当日工作安排，那接下来要如何将这个公式中的工作表名称“陈行”根据A列的内容动态变化工作表呢？
=OFFSET(陈行!$F$1,MATCH($B$1,陈行!A:A,0)-1,0,3,1)
这里就需要用到间接引用函数INDIRECT，语法如下：
=INDIRECT（文本或单元格地址）
本例中，我们需要把INDIRECT函数替代固定的工作表名称：
=OFFSET(INDIRECT(A5&"!$F$1"),MATCH($B$1,INDIRECT(A5&"!A:A"),0)-1,0,3,1)
把原来公式中的陈行改成单元格的地址A5，再和后面固定不变的"!$F$1"用&符号合并，之后再用INDIRECT函数间接引用这张工作表的这个地址。这样工作复制到下一行就会变成姚行、容行的工作表的内容。
最后，我们用文本合并的函数TEXTJOIN，把OFFSET获取的动态3行的内容合并到一个单元格，TEXTJOIN函数的语法如下：
TEXTJOIN（分隔符，是否需要忽略空格，要连接的文本）
=TEXTJOIN(CHAR(10),,OFFSET(INDIRECT(A5&"!$F$1"),MATCH($B$1,INDIRECT(A5&"!A:A"),0)-1,0,3,1))
这里的分隔符使用CHAR(10)函数表示用回车分隔。
到这个步骤，我们就实现了小A的需求了。当我们遇到一个需求，需要多个函数组合才能实现，只需要将需求进行一步步分解，就可以拨开迷雾，找到答案。
网友评论