PDM中使用EXL中的IFS函数来做标准件分类判断

日期:2023-02-20 14:57:54 发布者: ICT-Dawson 浏览次数:

在PDM中经常会有标准件的分类,那么有没有什么好的方法来进行分类?这里我们来试试EXL中的IFS函数来做分类,看看比你常使用的是否更好!!
一、问题原因
PDM中经常会有标准件的分类,例如

在初次整理标准件清单时 如果全部输入 05_机械传动件会有点多,我们可以用05代替

我们如何通过左边为简易的 数字表达,变为右边的详细内容呢?

二、问题解决方法
一级分类:
我们可以使用exl中的函数来解决这个问题,首先是 一分类的IFS函数
=IFS(J4="01","01_大宗原材料",J4="02","02_机电产品",J4="03","03_电子器件",J4="04","04_气动元器件",J4="05","05_机械传动件",J4="06","06_仪器仪表",J4="07","07_标准件",J4="08","08_五金件",J4="09","09_管件",J4="10","10_工具",J4="","")
在此函数中可以自动判别 J4格的值 ,如果是01 01_大宗原材料,以此类推即可自动添加一级分类
二级分类:
二级分类一般根据一级分类来做选择。当一级分类的数字不同,二级分类的数字含义也各不相同,例如:
=IFS(AND(J4="01",K4="01"),"01_板材",AND(J4="01",K4="02"),"02_棒材",AND(J4="01",K4="03"),"03_管材",AND(J4="01",K4="04"),"04_型材",AND(J4="01",K4="05"),"05_线材",AND(J4="02",K4="01"),"01_电机",AND(J4="02",K4="02"),"02_",AND(J4="02",K4="03"),"03_过滤机",AND(J4="02",K4="04"),"04_减速机",AND(J4="02",K4="05"),"05_冷水机",AND(J4="02",K4="06"),"06_烘干机",AND(J4="02",K4="07"),"07_风机",AND(J4="02",K4="08"),"08_超声波清洗机",AND(J4="02",K4="09"),"09_泵配件",AND(J4="03",K4="01"),"01_变频器",AND(J4="03",K4="02"),"02_变压器",AND(J4="03",K4="03"),"03_传感器",AND(J4="03",K4="04"),"04_继电器",AND(J4="03",K4="05"),"05_接触器",AND(J4="03",K4="06"),"06_显示屏",AND(J4="03",K4="07"),"07_控制仪表",AND(J4="03",K4="08"),"08_电控柜",AND(J4="03",K4="09"),"09_开关(按钮)",AND(J4="03",K4="10"),"10_",AND(J4="03",K4="11"),"11_电线电缆",AND(J4="03",K4="12"),"12_PLC",AND(J4="03",K4="13"),"13_电脑及配件",AND(J4="03",K4="14"),"14_电阻",AND(J4="03",K4="15"),"15_电源",AND(J4="03",K4="16"),"16_电磁阀",AND(J4="03",K4="17"),"17_附图定制加热器",AND(J4="03",K4="18"),"18_其他电子",AND(J4="04",K4="01"),"01_气缸",AND(J4="04",K4="01"),"01_气缸",AND(J4="04",K4="02"),"02_气缸附属关联件",AND(J4="04",K4="03"),"03_气动控制元件",AND(J4="04",K4="04"),"04_气动组件",AND(J4="04",K4="05"),"05_气动管件",AND(J4="04",K4="06"),"06_真空用元件",AND(J4="05",K4="01"),"01_齿轮齿条",AND(J4="05",K4="02"),"02_链轮类",AND(J4="05",K4="03"),"03_皮带轮_带类",AND(J4="05",K4="04"),"04_联轴器_轴座",AND(J4="05",K4="05"),"05_导轨",AND(J4="05",K4="06"),"06_光轴",AND(J4="05",K4="07"),"07_丝杆",AND(J4="05",K4="08"),"08_轴承",AND(J4="05",K4="09"),"09_滑台及组合系统",AND(J4="06",K4="01"),"01_显示仪表",AND(J4="06",K4="02"),"02_计量仪器",AND(J4="06",K4="03"),"03_在线分析仪",AND(J4="07",K4="01"),"01_挡圈",AND(J4="07",K4="02"),"02_垫圈
",AND(J4="07",K4="03"),"03_",AND(J4="07",K4="04"),"04_螺母",AND(J4="07",K4="05"),"05_螺丝",AND(J4="07",K4="06"),"06_",AND(J4="07",K4="07"),"07_喷嘴",AND(J4="07",K4="08"),"08_密封件",AND(J4="07",K4="09"),"09_弹簧",AND(J4="08",K4="01"),"01_合页",AND(J4="08",K4="02"),"02_拉手",AND(J4="08",K4="03"),"03_",AND(J4="08",K4="04"),"04_插销",AND(J4="08",K4="05"),"05_脚轮",AND(J4="08",K4="06"),"06_支教",AND(J4="08",K4="07"),"07_导轨",AND(J4="09",K4="01"),"01_三通",AND(J4="09",K4="02"),"02_二通",AND(J4="09",K4="03"),"03_弯头",AND(J4="09",K4="04"),"04_阀门",AND(J4="09",K4="05"),"05_活接",AND(J4="09",K4="06"),"06_法兰",AND(J4="09",K4="07"),"07_补芯",AND(J4="09",K4="08"),"08_堵头",AND(J4="09",K4="09"),"09_外丝接头",AND(J4="09",K4="10"),"10_内丝接头",AND(J4="09",K4="11"),"11_管卡",AND(J4="09",K4="12"),"12_异径接头",AND(J4="09",K4="13"),"13_管卡配件",AND(J4="10",K4="01"),"01_电动工具",AND(J4="10",K4="02"),"02_手动工具",AND(J4="10",K4="03"),"03_焊接工具",AND(J4="10",K4="04"),"04_钻头",AND(J4="10",K4="05"),"05_刀类",AND(J4="10",K4="06"),"06_量具",AND(J4="10",K4="07"),"07_夹具",AND(J4="10",K4="08"),"08_摸具",AND(J4="10",K4="09"),"09_机床附件",AND(J4="10",K4="10"),"10_油枪油壶",AND(J4="10",K4="11"),"11_其他",AND(J4="",K4=""),"")

通过此函数即可判断二级分类的具体数值
三级分类:
三级分类有所区别,除了判断一级 二级分类外还要根据数字的大小做分类,例如一个数字属于1-50之间是一个对应的分类,50-99是另一个对应的分类,我们的编码如下:
=IFS(AND(J4="01",K4="01",L4<=50),"1-50非金属材料",AND(J4="01",K4="01",L4>50),"51-99非金属材料",AND(J4="01",K4="02",L4<=50),"01-50非金属棒材",AND(J4="01",K4="02",L4>50),"51-99金属棒材",AND(J4="01",K4="03",L4<=50),"01-50非金属管材",AND(J4="01",K4="03",L4>50),"51-99金属管材",AND(J4="03",K4="09",L4<=50),"01-50开关",AND(J4="03",K4="09",L4>50),"51-99按钮",AND(J4="03",K4="11",L4<=50),"01-50电线",AND(J4="03",K4="11",L4>50),"51-99电缆",AND(J4="04",K4="04",L4<=50),"1-50空气组合元件",AND(J4="04",K4="04",L4>50),"51-99附件",AND(J4="05",K4="01",L4<=20),"01-20附图订制齿轮",AND(J4="05",K4="01",L4<=30,L4>20),"21-30附图订制齿条",AND(J4="05",K4="01",L4>30),"31-50其他齿轮",AND(J4="05",K4="02",L4<=20),"01-20附图订制",AND(J4="05",K4="02",L4>20),"21-60",AND(J4="05",K4="03",L4<=20),"01-20附图订制带轮",AND(J4="05",K4="03",L4<=50,L4>20),"21-50其他带轮",AND(J4="05",K4="03",L4>50),"51-99",AND(J4="05",K4="04",L4<=40),"01-40附图定制",AND(J4="05",K4="04",L4>40),"41-80轴座",AND(J4="05",K4="08",L4<=30),"01-30滚动轴承",AND(J4="05",K4="08",L4<=60,L4>30),"31-60滑动轴承",AND(J4="05",K4="08",L4>60),"61-99其他轴承",AND(J4="06",K4="01",L4<=30),"01-30电用",AND(J4="06",K4="01",L4<=70,L4>30),"31-50器具用",AND(J4="06",K4="01",L4>70),"71-99其他显示仪器",AND(J4="06",K4="02",L4<=20),"01-20流量计",AND(J4="06",K4="02",L4<=50,L4>20),"21-50计量器",AND(J4="06",K4="02",L4>50),"51-70",AND(J4="07",K4="09",L4<=30),"01-30附图定制弹簧",AND(J4="07",K4="09",L4>30),"31-99其他弹簧",AND(J4="09",K4="01",L4<=50),"01-50非金属三通",AND(J4="09",K4="01",L4>50),"51-99金属三通",AND(J4="09",K4="02",L4<=50),"01-50非金属二通",AND(J4="09",K4="02",L4>50),"51-99金属二通",AND(J4="09",K4="03",L4<=50),"01-50非金属弯头",AND(J4="09",K4="03",L4>50),"51-99金属弯头",AND(J4="09",K4="04",L4<=50),"01-50非金属阀门",AND(J4="09",K4="04",L4>50),"51-99金属阀门",AND(J4="09",K4="05",L4<=50),"01-50非金属活接",AND(J4="09",K4="05",L4>50),"51-99金属活接",AND(J4="09",K4="06",L4<=50),"01-50非金属法兰",AND(J4="09",K4="06",L4>50),"51-99金属法兰",AND(J4="09",K4="07",L4<=50),"01-50非金属补芯",AND(J4="09",K4="07",L4>50),"51-99金属补芯",AND(J4="09",K4="08",L4<=30),"01-30端盖",AND(J4="09",K4="08",L4>30,L4<=60),"31-60堵头",AND(J4="09",K4="08",L4>60),"61-99丝堵",AND(J4="09",K4="09",L4<=50),"01-50非金属外丝接头",AND(J4="09",K4="09",L4>50),"51-99金属外丝接头",AND(J4="09",K4="10",L4<=50),"01-50非金属内丝接头",AND(J4="09",K4="10",L4>50),"51-99金属内丝接头",AND(J4="10",K4="02",L4<=20),"01-20扳手",AND(J4="10",K4="02",L4<=30,L4>20),"21-30",AND(J4="10",K4="02",L4<=45,L4>30),"31-45",AND(J4="10",K4="02",L4<=55,L4>40),"46-55",AND(J4="10",K4="02",L4<=70,L4>55),"56-70",AND(J4="10",K4="02",L4<=80,L4>70),"71-80",AND(J4="10",K4="02",L4<=90,L4>80),"81-90",AND(J4="10",K4="02",L4<=99,L4>90),"91-99攻丝用具",AND(J4="10",K4="05",L4<=40),"01-40铣刀",AND(J4="10",K4="05",L4>40),"41-99其他类型刀",AND(J4="10",K4="06",L4<=10),"01-10",AND(J4="10",K4="06",L4<=20,L4>11),"11-20",AND(J4="10",K4="06",L4<=50,L4>20),"21-50尺类量具",AND(J4="10",K4="06",L4>50),"51-99其他量具",AND(J4="",K4=""),"")

注意:若想使用IFS 需要EXL版本为2016以上