【原】Excel 函数公式/文本连接函数/不规则数据查找/查找函数综合运用实例

快速浏览

往期合集：【2023年3月】【2023年4月】【2023年5月】【2023年6月】【2023年7月】

实用案例

|收费管理系统|中医诊所收费系统|

|日期控件|简单的收发存|

|电子发票管理助手|Excel表格拆分神器|

|Excel多种类型文件合并|

收费使用项目

|财务管理系统|

内容提要

• 字符串连接函数
• 查找函数综合利用
  

大家好，我是冷水泡茶，今天在知乎有一邀请贴：

他的需求可以这样表述：

查找姓名列固定区域非空单元格的值。

这个问题如果简单处理的话，可以加个辅助列，但如果是只想用一个公式来查找，好像还是有点难度，我模拟了一点数据，我们就一起来看看吧：

实现方法一：添加辅助列

1、我们可以在下面的数据表区域的最右侧添加一个辅助列：具体机型

2、把左边具体机型区域的数据取到辅助列中。

3、利用VLOOKUP之类的查找函数查找对应姓名的机型（辅助列）

4、辅助列取数公式，可以有多种方法：

（1）CONCAT函数：因为数据区域只有一列有内容，其余都为空，第一感觉就是把它们都连起来。

=CONCAT(I2:L2)

（2）INDEX+MATCH函数：

=INDEX(I2:L2,1,MATCH(TRUE,INDEX((I2:L2<>""),0),0))

（3）LOOKUP函数：

=LOOKUP(1,1/(I2:L2<>""),I2:L2)

（4）PHONETIC函数：这个函数估计很多人没有用过。它原本用来提取东亚语言中的拼音，如果没有拼音则返回文本。注意，它只提取文本类型的值，其他如数字、公式、错误值它统统忽略。

=PHONETIC(I2:L2)

（5）用“&”符号连接：这个有点麻烦，只适合数据列较少的情况。

=I2&J2&K2&L2

5、最后，用VLOOKUP函数查找结果：

=VLOOKUP(A2,H:M,6,0)

实现方法二：公式法，只用一个公式达到目的

公式的难点在于，要匹配的值所在列是变化的，直接用VLOOKUP之类的查找函数有点难以下手。当我们找到姓名时，还得到对应行的数据区域中某一列查找非空单元格。

公式想了好久，要解决动态的数据区域问题，我想到了OFFSET函数。

OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L))

从姓名列，行向下位移，用MATCH函数查找$A2姓名在数据区域姓名列的位置来获得位移量；列向右位移1，返回区域为1行，n列，n用COLUMNS函数求得。

有了这个动态区域，我们就利用查找函数来查找对应的非空单元格。

1、用INDEX+MATCH，数组公式，Ctrl+Shift+Enter输入。

{=INDEX(OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L)),1,MATCH(TRUE,OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L))<>"",0))}

2、用LOOKUP:

=LOOKUP(1,1/(OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L))<>""),OFFSET($H$1,MATCH($A2,$H$2:$H$21,0),1,1,COLUMNS($I:$L)))

2个公式都很长，其实就是第一种方法中求辅助列的第二、三种方法。

总结

函数公式的综合运用，我们可以通过抽丝剥茧的方式一步一步来分析解决，重点就是把某个函数的参数也使用公式，层层嵌套，最终达成目标。

当然，有时候利用辅助列，可以简化公式的长度，并且也不用那么烧脑，也是一个很好的解决问题的方法。

正文完

喜欢就点个赞、点在看、留个言呗！分享一下更给力！感谢！