1职场实例
小伙伴们大家好,今天我们来讲解一下Search函数配合通配符实现模糊查找的问题。这也是公众号的一位粉丝朋友的提问咨询。下面我们简单的用一组数据模拟一下实际工作场景,具体讲解一下操作过程。
如下图所示:
下图为一张科目编码金额表,A列为科目编码,一级科目编码一般为4位数字,比如1001代表库存现金,后面在增加数字,如100101即为一级科目的二级细分科目;B列为各个科目编码对应的金额值。我们想要在E2单元格,根据D2单元格的一级科目编码作为条件,统计对应一级科目编码的总金额,即模糊查找条件求和。
2解题思路
有的小伙伴不假思索的说了,我们可以通过SUMIF函数配合通配符“*”来实现这样的模糊查找条件求和问题。
我们在E2单元格输入函数公式:
=SUMIF(A2:A7,D2&”*”,B2:B7)
回车结束公式发现输出的结果为0。原因就是因为A2:A7单元格区域中的会计科目编码为数字,而SUMIF函数有一个缺憾就是仅支持在文本内容中使用通配符“*”星号等进行模糊查找条件求和。
对于这种模糊查找条件求和的问题,我们该如何快速的书写函数公式呢?
我们可以利用SEARCH函数配合通配符实现模糊查找求和。
SEARCH函数用于查找指定查找值在字符串中的起始位置。
函数语法:
=SEARCH(找什么,在哪找,从第几位开始找)
我们在E2单元格输入函数公式:
=SEARCH(D2&”*”,A2:A7)
其中的“D2&”*””即“100服务器托管网1*”,表示以1001开头的任意字符串。
“SEARCH(D2&”*”,A2:A7)”表示A2:A7单元格中查找以1001为开头的字符串。符合条件的返回对应的位置数字,否则返回错误值#VALUE!。
我们可以通过选中公式后按下F9键查看返回结果,ESC键返回原公式:
={1;#VALUE!;1;#VALUE!;1;#VALUE!}
我们继续在公式外部嵌套ISNUMBER函数:
=ISNUMBER(SEARCH(D2&”*”,A2:A7))
即利用ISNUMBER函数判断SEARCH函数的返回结果是否为数值,如果是数值,则返回逻辑值TRUE,也可以理解为返回数字“1”;如果不是数字,则返回逻辑值“FALSE”,也可以理解为返回数字“0”。
我们可以通过选中公式后按下F9键查看返回结果,ESC键返回原公式:
={TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
我们继续完善公式为:
=ISNUMBER(SEARCH(D2&”*”,A2:A7))*B2:B7)
即利用ISNUMBER函数返回的逻辑值与B2:B7单元格的数值相乘。返回A列含有会计编码“1001”的对应的1乘以B列单元格的值;或者返回A列不含会计编码“1001”的对应的0乘以B列单元格的值。
我们可以通过选中公式后按下F9键查看返回结果,ESC键返回原公式:
={1819;0;4405;0;309;0}
最后将公式完善为:
=SUMPRODUCT(ISNUMBER(SEARCH(D2&”*”,A2:A7))*B2:B7)
即使用SUMPRODUCT函数:
计算出上一步数组中{1819;0;4405;0;服务器托管网309;0}的乘积之和。
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
二分法是搜索算法中极其典型的方法,其要求输入序列有序并可随机访问。算法思想为 输入:有序数组nums,目的数值target 要求输出:如果target存在在数组中,则输出其index,否则输出-1 将原数组通过[left,right]两个索引划分范围,初值le…