TEXTJOIN函数高级用法:一对多查询so easy! |
您所在的位置:网站首页 › substitute函数高级用法 › TEXTJOIN函数高级用法:一对多查询so easy! |
要求:下图中,我们通过选择E4单元格中的部门,在F列中返回不同的查询结果。 具体操作步骤如下: 1、选中F4单元格,在编辑栏中输入公式:=TRIM(MID(SUBSTITUTE(TEXTJOIN("/",1,IF(B:B=$E$4,C:C,"")),"/",REPT(" ",99)),ROW(A1)*99-98,99)),按组合键“Ctrl + Shift + Enter”。将F4单元格中的公式下拉填充至F8单元格即可。 2、公式解析。 ①=TEXTJOIN("/",1,IF(B:B=$E$4,C:C,"")): TEXTJOIN函数说明:使用分隔符连接列表或文本字符串区域。 TEXTJOIN函数语法:=TEXTJOIN(delimiter, ignore_empty, text1, [text2], … ,[textn])。 TEXTJOIN参数说明: delimiter:分隔符(必需)。可以为文本字符串(空)或一个或多个用双引号括起来的字符,或对有效文本字符串的引用。如果提供了一个数字,它将被视为文本。 ignore_empty:忽略空白单元格(必需)。可选值有 TRUE 和 FALSE。如果为 TRUE 或 1,则忽略空白单元格;如果为 FALSE 或 0,则包含空白单元格。 text1:要加入的文本项(必需)。文本字符串或字符串数组。例如单元格区域。 [text2], … ,[textn]:要加入的其他文本项(可选)。文本项目最多可以包含252个文本参数,包括 text1,每一个都可以是文本字符串或字符串数组,例如单元格区域。 公式:=TEXTJOIN("/",1,IF(B:B=$E$4,C:C,"")),第一个参数“/”表示以 “/”来作为分隔符号;第2个参数 1 表示忽略空白单元格,也可以用 TRUE 来表示;第3个参数 IF(B:B=$E$4,C:C,"") 表示用 IF 函数来判断B列中的内容是否与 E4 单元格的内容相等,如果相等,返回C列中对应的内容,如果不相等,返回空的字符串。 如果E4="技术部",该公式返回的结果是:"黄子龙/李志强/黄伟峰";如果E4="客服部",该公式返回的结果是:"李思思/黄婉君",......。 ②SUBSTITUTE(TEXTJOIN("/",1,IF(B:B=$E$4,C:C,"")),"/",REPT(" ",99)): SUBSTITUTE函数说明:将字符串中的部分字符串以新字符串替换。 SUBSTITUTE函数语法:=SUBSTITUTE(text, old_text, new_text, [instance_num])。 SUBSTITUTE参数说明: text:必需。需要替换其中字符的文本,或对含有文本(需要替换其中字符)的单元格的引用。 old_text:必需。需要替换的文本。 new_text:必需。用于替换 old_text 的文本。 instance_num:可选。替换序号。指定要将第几个 old_text 替换为 new_text。如果指定了序号,则满足要求的 old_text 才会被替换。不指定,文本中出现的所有 old_text 都会被替换为 new_text。 REPT函数说明:根据指定的次数重复显示文本。可用该函数在一个单元格中重复填写一个文本字符串。 REPT函数语法:=REPT(text, number_times)。 REPT参数说明: text:必需。需要重复显示的文本。 number_times:必需。用于指定文本重复的次数,该值须为正数。 整条公式的意思是:将字符串中的所有字符“/”替换成空字符串,重复99次。返回的结果是: ③=MID(SUBSTITUTE(TEXTJOIN("/",1,IF(B:B=$E$4,C:C,"")),"/",REPT(" ",99)),ROW(A1)*99-98,99): MID函数说明:从文本字符串中指定的起始位置起返回指定长度的字符。 MID函数语法:=MID(text, start_num, num_chars)。 MID参数说明: text:必需。包含要提取字符的文本字符串。 start_num:必需。文本中要提取的第一个字符的位置。 num_chars:必需。从文本中返回字符的个数。 整条公式的意思是:根据第②步的公式可以得到结果“黄子龙 李志强 黄伟峰”,两个姓名之间的空格符号有99个,用MID函数从字符串的第1个字符开始提取,提取99个字符,这样子我们提取到的就是第一个姓名后面带了很多空格符号。 ④=TRIM(MID(SUBSTITUTE(TEXTJOIN("/",1,IF(B:B=$E$4,C:C,"")),"/",REPT(" ",99)),ROW(A1)*99-98,99)): TRIM函数说明:删除字符串中多余的空格,单词之间的空格除外。 TRIM函数语法:=TRIM(text)。 TRIM参数说明: text:必需。要从中移除空格的文本字符串。 整个公式的意思是:将前面3步公式返回的包含姓名和空格的字符串中,将字符串去除,只保留姓名。 3、动图演示如下。 本期教程跟大家分享到这里,想学更多的办公技巧,欢迎关注我哦! 如果文章对您有帮助,可以转发、点赞支持小编,创作不易,希望多多支持! |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |