EXCEL的几个函数运用(答疑摘录)

一、文本格式的数字长超过15位,条件格式重复值单元格高亮显示结果错误


设置B列条件格式,重复值单元格填充色黄色(高亮),结果全部为黄色。

首先想到利用通配符:

数据利用通配符统计结果用sumprodcut统计结果
6235800001677883273正确3正确
6235800001677883283正确3正确
6235800001677883292正确2正确
6235800001677883302正确2正确
6235800001677883273正确3正确
6235800001677883283正确3正确
6235800001677883292正确2正确
6235800001677883302正确2正确
6235800001677883273正确3正确
6235800001677883283正确3正确

C3=COUNTIF($B$3: $B$12,B3&"*"),但考虑如果出现长度不一样的文本可能会出现异常:


我们看到B3、B8统计为3,因为使用了通配符,把B7也统计进来了。

数据利用通配符统计结果用sumprodcut统计结果
6235800001677883273统计错误2正确
6235800001677883283统计错误1正确
6235800001677883292统计错误1正确
6235800001677883302正确1正确
623580000167788327121正确1正确
6235800001677883273统计错误2正确
623580000167788328011正确1正确
623580000167788329A1正确1正确
623580000167788330FA1正确1正确
623580000167788328C1统计错误1正确

所以在E3设置公式=SUMPRODUCT(--($B$3: $B$12=B3)),或者:

=SUMPRODUCT(($B$3: $B$12=B3)*1),则统计结果正确。


二、对产品的毛利率进行预警,设置IF函数比较麻烦且受7重嵌套限制

先看设置结果:


首先做好区间数据的管理:

预警词
-50%5%极差
5%10%
10%20%良好
20%50%优秀

然后在表格设置公式:

毛利率预警
-8%极差
5%
8%
15%良好
22%优秀
30%优秀
45%优秀

C3=LOOKUP(B3,$E$3: $E$6,$G$3: $G$6)


三、提取工序和完工数量信息

完成后的图示(JK列为提取结果):


解题思路:

如果D3:I3大于0,则是该工序有入库,取第二行的工序信息放在J3,同时把数量放在K3。

CP单号工序1工序2工序3工序4工序5工序6工序数量
AF145444276




工序14276
BF145444259




工序14259
VF14545

2342


工序32342
CF14546



6000
工序56000
DF14546

2105


工序32105
FF14548




2200工序62200

公式设置:

J3:{=INDEX($D$2: $I$2,MATCH(1,1/($D3: $I3>0),0))}

index(区域,第几个值),match(1,1/(大于0),0)遇到第一个大于零的值,返回所在的位置——第几个值。

K3:{=INDEX($D3: $I3,MATCH(1,1/($D3: $I3<>0),0))}

附件: 您需要登录才可以下载或查看附件。没有帐号?注册  -- >