设为首页收藏本站
我们爱民主、我们爱自由、我们也爱财富!我们尊孔子、我们尊老子、我们同尊管子!

安德管仲网管仲论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
热搜: 活动 交友 discuz
查看: 122|回复: 0

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

[复制链接]
jinwa 发表于 2020-7-29 11:17:44 | 显示全部楼层 |阅读模式

一、文本格式的数字长超过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))}

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册

x
您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|安德管仲网 ( 粤ICP备20002412号   

GMT+8, 2020-10-24 14:33 , Processed in 0.082948 second(s), 19 queries .

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表