大家好,这一章,我们会讲到:
REPT函数
TRIM函数
LEN,LEFT,RIGHT,SUBSITETUTE函数
IF函数
LOOKUP函数
XLOOKUP函数
IFERROR函数
在实际场景中的使用技巧合集,这些函数用得好,表格做到牢.
下方是一个常用的工程物料清单,虽然是物料清单为例子,但是我相信很多类似的专业表格都会存在一些同样的需求,所以即使你的其他的专业,不妨也看看这些函数技巧的使用方法.
在这一份物料清单中,大概内容是制作一个"拖车",需要的材料配置表.有一些材料需要采购,有一些需要自制.
在一般的物料清单中,都会存在BOM等级,如果没有,我也建议你加上这一项.
产品"拖车"作为制作主件,由分支组件进行构成.
由2个大件构成,底板和滚轮组件.
而滚轮组件又由一些分支部件进行组装.
这份表格只是一个主件的制作配置情况,当然还存在N个主件在同一个表中,道理都是一样,直接往下写其他的主件情况就行了.
主件构建逻辑图如下所示:
其实也对应到了材料清单当中的BOM等级.
并非只有材料清单才会使用到这种结构,很多专业的表格都会使用到这种统计结构,那么接下来,让我们看看,针对这个表格我们会做出一些什么样的需求吧.
REPT函数,在这个位置可以很巧妙的去对组件号进行分级排布.
譬如,这一份组件表,可能你要放进WORD当中,为了体现组件的等级,可能你会手动去在组件号前方按空格,达到下方C列的效果,就呵呵了.
=REPT(" ",3*A2)&B2
用这个公式可以把空格重复前方的等级次数,然后再用N个空格去连接组件号.
很多情况下,可能别人发给你的组件号,为了体现等级分级,可能原始模样如B列所示,前方有很多空格,如果要想要查找某一个组件号的相关信息,用VLOOKUP是查找不到的,
因为譬如:10112-1001和前方加了空格的10112-1001是不一样的,
所以你可以输入公式:
=TRIM(B2)
裁剪函数,进行空格的裁剪.
如下方B列,极有可能是从WORD文档,或者正规的PDF文档中所导入的原始信息表格,如B列这样的效果作为文档展示,很好,但是在Excel当中进行内容的匹配计算查找,都是很费劲的事情,
所以你可以输入公式:
=LEN(B2)-LEN(SUBSTITUTE(B2,".",""))
把B列变成A列的样子,方便进行Excel方面的数据编辑.
有的时候,数据统计并非我们自己编辑那样,规范考虑方方面面,如下图,组件号,和组件描述在同一个单元格,并且用逗号分隔,
这个时候,可以输入公式:
=LEFT(A2,FIND(",",A2)-1)
提取组件号
输入公式:
=RIGHT(A2,LEN(A2)-FIND(",",A2))
提取组件描述.
当然,如果你的版本允许,也可以使用快速填充来解决这个问题:
分别复制组件号,和组件描述到第一行,
然后CTRL+E快速填充
这个案例有点复杂,如下图,我想在C列找到B列的归属组件号.
比如,"拖车"由"底盘"和"滚轮组件"构成,所以"底盘"和"滚轮组件"归属号位置就应当写上"拖车"的组件号.
这个算法在一维统计的材料清单中有非常重大的意义.
你可以输入函数:
=IFERROR(LOOKUP(2,1/($A$2:A2=(A2-1)),$B$2:B2),"-")
或者你是新版本可以使用XLOOKUP函数:
=XLOOKUP(A2-1,$A$2:A2,$B$2:B2,"-",,-1)
注意其中的绝对引用位置.
关于XLOOKUP函数和LOOKUP倒序查找,我们之前的章节中是讲过的,你可以找找历史记录.
这个例子,进入到了整个物料清单的核心部分,
如何实现物料的自动计算,
输入公式:
=IF(A2=0,H2,IFERROR(LOOKUP(2,1/($A$1:A1=(A2-1)),$I$1:I1),"-")*H2)
注意绝对引用位置.
这个地方需要特别解释一下:
"拖车"由"底板"和"滚轮组件"构成,
以滚轮组件为例子,其中一个组件是侧边板,侧边板又由下方绿色涂色部分构成.
1个滚轮组件需求2个侧边板,4个需求就是8个侧边板,
1个侧边板需要2个碳钢,8个侧边板就需要16个碳钢.
以此类推..
当你做好这个公式,以后有产品的需求,直接在主件位置输入,需求数量,下方的配置需求部分都会发生同样的变化.
特别是这一份表格中有一些是自制的,有一些是额外采购的可以更好的掌控的生产或采购的情况.
最后可能在你的物料表中,可能不止一个"主件产品",当多个"主件产品"存在于表格中的时候,你可以做一个IF函数,当为0的BOM等级,标记一个主件方便筛查.
=IF(A2=0,"主件","")
以上,
如果你可以看懂整个内容,相信对于你的物料类型表格会有不小的帮助.
10个月宝宝每天需要喝多少奶粉？
