EXCEL的几个函数运用（答疑摘录）

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

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

首先想到利用通配符：

数据	利用通配符统计	结果	用sumprodcut统计	结果
623580000167788327	3	正确	3	正确
623580000167788328	3	正确	3	正确
623580000167788329	2	正确	2	正确
623580000167788330	2	正确	2	正确
623580000167788327	3	正确	3	正确
623580000167788328	3	正确	3	正确
623580000167788329	2	正确	2	正确
623580000167788330	2	正确	2	正确
623580000167788327	3	正确	3	正确
623580000167788328	3	正确	3	正确

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

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

数据	利用通配符统计	结果	用sumprodcut统计	结果
623580000167788327	3	统计错误	2	正确
623580000167788328	3	统计错误	1	正确
623580000167788329	2	统计错误	1	正确
623580000167788330	2	正确	1	正确
62358000016778832712	1	正确	1	正确
623580000167788327	3	统计错误	2	正确
62358000016778832801	1	正确	1	正确
623580000167788329A	1	正确	1	正确
623580000167788330FA	1	正确	1	正确
623580000167788328C	1	统计错误	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	工序	数量
A	F14544	4276						工序1	4276
B	F14544	4259						工序1	4259
V	F14545			2342				工序3	2342
C	F14546					6000		工序5	6000
D	F14546			2105				工序3	2105
F	F14548						2200	工序6	2200

公式设置：

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))}