欢迎转发和点一下“在看”，文末留言互动!
置顶公众号或设为星标及时接收更新不迷路
小伙伴们好啊，今天要和大家分享一道数值提取并计算求和的题目。今天这道题目要用到好久没有使用的SUBSTITUTE函数了。它的经典套路应用也一直是我个人的最爱！
原题目是这样的：
题目要求计算每种品名在指定日期内的汇总金额。
观察一下源数据，它还是很有特点的，以“+”作为分隔符号。我们可以考虑把当前的品名从源数据中替换掉，再用SUBSTITUTE函数截取字符串，最后提取数值。
01
SUBSTITUTE函数经典套路
上面讲的就是这道题目的逻辑思路。下面就一起来看看如何书写这条公式吧。
在单元格I5中输入下列公式，三键确认后向下拖曳即可。
=SUM(TEXT(LEFTB(TRIM(MID(SUBSTITUTE(SUBSTITUTE(E$5:E$16,F5,""),"+",REPT(" ",99)),COLUMN($A:$D)*99-98,99)),2),"0;;;!0")*(D$5:D$16>=D$7)*(D$5:D$16<=$D$14))*G5经典的SUBSTITUTE函数套路再度出场。
SUBSTITUTE(E$5:E$16,F5,"")利用SUBSTITUTE函数首先把当前的产品名从源数据中替换为空值。这样方便后续提取数值。
SUBSTITUTE(SUBSTITUTE(E$5:E$16,F5,""),"+",REPT(" ",99)再次利用SUBSTITUTE函数把“+”替换为长度为99的空格。
MID(SUBSTITUTE(SUBSTITUTE(E$5:E$16,F5,""),"+",REPT(" ",99)),COLUMN($A:$D)*99-98,99)套路应用。这部分提取了源数据中以“+”作为分隔符的字符串。
TRIM(MID(SUBSTITUTE(SUBSTITUTE(E$5:E$16,F5,""),"+",REPT(" ",99)),COLUMN($A:$D)*99-98,99))去除多余的空格后，结果如下：
{"5本","直尺3把","","";"铅笔5支","4本","橡皮2个","";"书包4个","8本","","";"橡皮6个","直尺4把","","";"水杯2个","抽纸10包","钢笔3支","";"5本","直尺3把","","";"铅笔5支","4本","橡皮2个","";"书包4个","8本","","";"橡皮6个","直尺4把","","";"水杯2个","抽纸10包","钢笔3支","";"铅笔5支","4本","橡皮2个","";"书包4个","8本","",""}。
你看，这时候就可以考虑提取数值了。
LEFTB(TRIM(MID(SUBSTITUTE(SUBSTITUTE(E$5:E$16,F5,""),"+",REPT(" ",99)),COLUMN($A:$D)*99-98,99)),2)
利用LEFTB函数提取2个字节长度的字符串。结果如下：
{"5 ","直","","";"铅","4 ","橡","";"书","8 ","","";"橡","直","","";"水","抽","钢","";"5 ","直","","";"铅","4 ","橡","";"书","8 ","","";"橡","直","","";"水","抽","钢","";"铅","4 ","橡","";"书","8 ","",""}。
TEXT(LEFTB(TRIM(MID(SUBSTITUTE(SUBSTITUTE(E$5:E$16,F5,""),"+",REPT(" ",99)),COLUMN($A:$D)*99-98,99)),2),"0;;;!0")TEXT函数强制将文本转换为0，结果为：
{"5","0","0","0";"0","4","0","0";"0","8","0","0";"0","0","0","0";"0","0","0","0";"5","0","0","0";"0","4","0","0";"0","8","0","0";"0","0","0","0";"0","0","0","0";"0","4","0","0";"0","8","0","0"}，你看，剩下的就是当前品名所对应的数量了。
SUM(TEXT(LEFTB(TRIM(MID(SUBSTITUTE(SUBSTITUTE(E$5:E$16,F5,""),"+",REPT(" ",99)),COLUMN($A:$D)*99-98,99)),2),"0;;;!0")*(D$5:D$16>=D$7)*(D$5:D$16<=$D$14))*G5剩下的过程就很简单了。在添加剩余的条件，乘以单价，SUM函数求和后就可以了。
本期内容练习文件提取方式：
链接: https://pan.baidu.com/s/1JZKSSSr5_v9F1y_4zhSV_A?pwd=geb6 提取码: geb6
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
