最近有个微信群内接龙,格式是这样的:
接龙 |
1. 张三,今日上午卖3.4万元,中午卖1.34万元,下午卖2.54万元。 |
2. 李四:上午1.2万元,中午18万元,下午0.5万元。 |
3. 王五一 上午0.5万元 中午1.8万元 下午0.06万元 |
在不麻烦各位销售大佬统一格式的情况下,需要整理成如下表格:
销售人 | 上午销量 | 中午销量 | 下午销量 | 合计 |
张三 | 3.4 | 1.34 | 2.54 | 7.28 |
李四 | 1.2 | 18 | 0.5 | 19.7 |
王五一 | 0.5 | 1.8 | 0.06 | 2.36 |
在excel不使用宏的情况下(即不使用正则表达式)公式的表达如下:
1.处理销售人部分
销售人开头 | 销售人结尾 | |
1. 张三,今日上午卖3.4万元,中午卖1.34万元,下午卖2.54万元。 | =IFERROR(FIND(". ",A2,1),"") | =MIN(IFERROR(FIND({"同志",":",":",","," "},A2,B2+2),99)) |
2. 李四:上午1.2万元,中午18万元,下午0.5万元。 | 2 | 6 |
3. 王五一 上午1.2万元 中午18万元 下午0.6万元 | 2 | 7 |
简单说明一下, 销售人开头就是找._,微信群的开头都是规范的,比较好找。
之后的分隔符就各有各的发挥了,销售人结尾用了含数组的公式,把几种情况都列了一下{"同志",":",":",","," "}
=MIN(IFERROR(FIND({"同志",":",":",","," "},A2,B2+2),99))公式从销售人开头后开始查找所列的字符,取找到的最小值。
这样销售人部分就出来了。
2.以上午销量为例,先把整段的销量文字取出
销售人 | 上午 | |
1. 张三,今日上午卖3.4万元,中午卖1.34万元,下午卖2.54万元。 | 张三 | =MID($A2,FIND(E$1,$A2),FIND("万元",$A2,FIND(E$1,$A2))-FIND(E$1,$A2)) |
2. 李四:上午1.2万元,中午18万元,下午0.5万元。 | 李四 | 上午1.2 |
3. 王五一 上午1.2万元 中午18万元 下午0.6万元 | 王五一 | 上午1.2 |
=MID($A2,FIND(E$1,$A2),FIND("万元",$A2,FIND(E$1,$A2))-FIND(E$1,$A2))
也是找头找尾, E$1代表“上午”,思路就是把“上午”和“万元”之间的字符找出来,公式不难理解。
3.难点部分,把销量文字中的数值提取出来
销售人 | 上午 | 上午销量 | |
1. 张三,今日上午卖3.4万元,中午卖1.34万元,下午卖2.54万元。 | 张三 | 上午卖3.4 | =RIGHT(E2,MAX(LEN(IFERROR(RIGHT(E2,ROW(INDIRECT("1:"&LEN(E2))))+0,""))))+0 |
2. 李四:上午1.2万元,中午18万元,下午0.5万元。 | 李四 | 上午1.2 | 1.2 |
3. 王五一 上午1.2万元 中午18万元 下午0.6万元 | 王五一 | 上午1.2 | 1.2 |
={RIGHT(E2,MAX(LEN(IFERROR(RIGHT(E2,ROW(INDIRECT("1:"&LEN(E2))))+0,""))))+0}
公式为数组公式,使用键入后使用ctrl+shift+enter输入
分段解释一下,E2的内容是“上午卖3.4”,ROW(INDIRECT("1:"&LEN(E2)),就是从1到“上午卖3.4”的字符长度即6的数组,就是ROW(INDIRECT("1:6"),即{1,2,3,4,5,6}
IFERROR(RIGHT(E2,{1,2,3,4,5,6})+0,"")
如果取出的是纯数字,数字+0还是数字,如不是纯数字,+0会报错,报错后用空值替代
LEN(IFERROR(RIGHT(E2,{1,2,3,4,5,6})+0,""))
这样上面就变成了{1,2,3,0,0,0}
最后:RIGHT(E2,MAX({1,2,3,0,0,0}))=RIGHT(E2,3)=3.4
推导过程如下:
={RIGHT(E2,MAX(LEN(IFERROR(RIGHT(E2,ROW(INDIRECT("1:"&LEN(E2))))+0,""))))+0}
={RIGHT(E2,MAX(LEN(IFERROR(RIGHT(E2,ROW(INDIRECT("1:6")))+0,""))))+0}
=RIGHT(E2,MAX(LEN(IFERROR(RIGHT(E2,{1,2,3,4,5,6})+0,""))))
=RIGHT(E2,MAX({1,2,3,0,0,0}))
=RIGHT(E2,3)
=3.4
按照以上方法,就可以整理完成最后的表格。